Select rows from another server's database

[Christine]

Hi,

I need to import data from another server on recurring basis. Before inserting the data, I need to perform some checking e.g. check the last update date and time.

I am thinking of scheduling SQL job that run SQL Query. The SQL query will make use of cursor to check each row, to decide what to do with the imported data

Is it the right way to import the data? However I did not know how to select data from another server using T-SQL. Can help? I am using SQL 2000

Thanks in advance

Regards,
Christine

[MAK]

I recommend DTS.

1. Import data from another server to current server to a temporary table.

2. compare existing table with imported table.

3. Import the necessary data.

4. save the DTS package and schedule it as a job.

[skhanal]

You can select data from remote server using linked server. Check books online for detail on how to create a linked server.

[Christine]

Thanks guys!

[orion]

you can use stored procedure sp_addlinkedserver to add a linked server , then use a select clause as follow format to get data .
select * from servername.databasename..tablename

[VisintinTH]

Hello, there's another way, (even if the best would be DTS), very useful if you don't want (or can't) 'link' to the other server. Use 'Openrowset' as follows:
select RW.* from
openrowset('SQLOLEDB', 'remoteServerName';'remoteUserid';'remotePswd', 'select * from remoteDBName.dbo.remoteTableName with (nolock)') as RW
and in this way you can also appply any criteria during the query.

[MAK]

I wouldnt do that because.

1. for openrowset your MSDTC should be running on the servers. If I am going to do that, i better create a linked server so that i can access the data anytime

2. I would'nt expose the userid and password, when we need data on a recurring basis.

3. Hard to keep track of userids



DTS is more secure and can be password protected.

[cbuzzo]

That could be done with linked servers, but I think there's a better way with distributed queries. This is an example of a local view of the sysdatabases table in the master database of a remote server:

create view my_view_on_remote_server
as

select *
from opendatasource(
'SQLOLEDB',
'Data Source=remote_servername;
User ID=my_login;
Password=my_password'
).master.dbo.sysdatabases


César

[MAK]

Cbuzzo,

see my answer for VisintinTH.