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.
jhbeggsAsked:
Who is Participating?
 
momi_sabagCommented:
well,
you need to solve this issue... :-)
you need to create at the remote server the same login that you are using in your local server
are you using windows authentication or sql authentication?
0
 
momi_sabagCommented:
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?
0
 
jhbeggsAuthor Commented:
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.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
momi_sabagCommented:
can you post here the insert statement?
0
 
jhbeggsAuthor Commented:
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
0
 
momi_sabagCommented:
when using a linked server you have to specify the full table name:

linked_server_name.database_name.schema.table_name
0
 
jhbeggsAuthor Commented:
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.
0
 
jhbeggsAuthor Commented:
SQL EXPRESS 2005 will not allow the creation of the same username and password as in SQL 2000.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.