Accessing data in a SQL server located in a different server

[IzoD]

Hi
I have an application that reads and writes to a SQL server database. I have a customer who has his data written to another SQ Server database. The links between the different tables and the notations resulted in a stored procedure that he has to extract the data in the format that is desired.

I need to port that data into my database. I tried DTS and I could access only the tables and not the stored procedure. If you could let me know the process by which I can write a procedure to access the tables from within my database I would appreciate that too.

thanks in advance
Suresh

 

[cmmrfrds]

If you want to link to your client's server, you can do that in enterprise manager. Expand the server group and look under the security folder. You will see an item called linkedservers and you can link the server then you can read the tables off the clients server provided you have the correct permissions, etc..

 

[vaidyanathanpc]

Hi,
Try using sp_addlinkedserver 'remoteservername'
i.e execute sp_addlinkedserver 'remoteservername' thru query analyzer. Make sure that the user that u r using to connect is also there on the remote server.
Then u can access the data on the remote server using
servername.databasename.dbo.objectname

Say the remote servername is 'server1' and u want to access a table 'table1' on database 'DB1' on 'server1'.. do the following
connect as sa or a dba and execute
exec sp_addlinkedserver 'server1'
suppose u r using the user 'user1' then create the user 'user1' on 'server1' too.
connect to ur database using 'user1' and u can access data on server1 using
select * from server1.db1.dbo.table1

P.C. Vaidyanathan