Link to home
Start Free TrialLog in
Avatar of jhbeggs
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.
Avatar of momi_sabag
momi_sabag
Flag of United States of America image

what do you mean can't access?
what are you trying to do?
did you try to select from them? what message did you receive?
Avatar of jhbeggs
jhbeggs

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.SqlException: Invalid object name 'linkedservername'.'tablename'.
Waiting for your reply.Thank you very much.
can you post here the insert statement?
Avatar of jhbeggs

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](staff_id,week_number) VALUES('@staff_id','@weeknumbe'r)
END
when using a linked server you have to specify the full table name:

linked_server_name.database_name.schema.table_name
Avatar of jhbeggs

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.
ASKER CERTIFIED SOLUTION
Avatar of momi_sabag
momi_sabag
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jhbeggs

ASKER

SQL EXPRESS 2005 will not allow the creation of the same username and password as in SQL 2000.