jhbeggs
asked on
How do I acess tables in SQL 2005 EXPRESS linked with SQL 2000 ?
I created a linked server on SQL 2000 to SQL EXPRESS 2005. I am able to view the linked tables in SQL 2000 server but i can't acess the tables.
ASKER
I can't access the contents of the tables.I am trying to insert values into the linked table from insert trigger on a table in sql 2000.But I am getting the following error:
System.Data.SqlClient.SqlE xception: Invalid object name 'linkedservername'.'tablen ame'.
Waiting for your reply.Thank you very much.
System.Data.SqlClient.SqlE
Waiting for your reply.Thank you very much.
can you post here the insert statement?
ASKER
I tried to insert values into a table in linked server after insert the values in a table in sql 2000 by using triggers.
CREATE TRIGGER trig_name ON [dbo].[tablename]
AFTER INSERT
AS
BEGIN
DECLARE @staff_id VARCHAR(100)
DECLARE @weeknumber (100)
DECLARE @staff_id VARCHAR(100)
SELECT @staff_id= (SELECT staff_id FROM Inserted)
SELECT @weeknumber = (SELECT weeknumber FROM Inserted)
INSERT INTO [TEST].[tbl_testexpense](s taff_id,we ek_number) VALUES('@staff_id','@weekn umbe'r)
END
CREATE TRIGGER trig_name ON [dbo].[tablename]
AFTER INSERT
AS
BEGIN
DECLARE @staff_id VARCHAR(100)
DECLARE @weeknumber (100)
DECLARE @staff_id VARCHAR(100)
SELECT @staff_id= (SELECT staff_id FROM Inserted)
SELECT @weeknumber = (SELECT weeknumber FROM Inserted)
INSERT INTO [TEST].[tbl_testexpense](s
END
when using a linked server you have to specify the full table name:
linked_server_name.databas e_name.sch ema.table_ name
linked_server_name.databas
ASKER
I specified the full table name and execute ,Then i get the following error.
Access to the remote server is denied because no login-mapping exists.
Access to the remote server is denied because no login-mapping exists.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
SQL EXPRESS 2005 will not allow the creation of the same username and password as in SQL 2000.
what are you trying to do?
did you try to select from them? what message did you receive?