Is it possible to pass a query to a SQL remote server?

[bombplayer]

BOL says that I can't unless I form it into a stored procedure. I have 13 satellite offices running a SQL 2000 database and one central SQL database. I need to figure out how to copy data from the 13 tables into the central. Anyone help?

 

[DLSQL]

There are a variety of options. You could use Linked Servers, DTS, or replication. It really depends on your needs, such as frequency, volume of data, does the data change frequently, uptime requirements, and do you need to transform the data in someway. I suggest you research these options in BOL, and then I am sure there are plenty of people to answer further questions.

DL
MCDBA, MCSD, MCT, etc.

 

[ClayG]

I have seen the following connection string work (in VB, it should work in VBS, and maybe could translate into a T-SQL OpenQuery). But I am not sure what needs to be setup on the server side - seems like something must be listening to the http port?

connection1.Open "Provider=MS Remote;" _
& "Remote Provider=SQLOLEDB;" _
& "Remote Server=

http://MYSERVER;"

_
& "Database=Northwind;" _
& "User Id=NWTest;Password=test;"
Set recset1 = New Recordset
recset1.CursorLocation = adUseClient
recset1.Open strQuery, connectio1, adOpenKeyset, adLockBatchOptimistic

 

[NoCoolHandle]

I am not sure that we are all thinking about the same thing...
to Quote

<snip>
The MS Remote provider tells ADO to communicate with the remote server (via the RDS DataFactory) and to use the remote provider that is installed on the remote server
</snip>

This is actually saying that if I was to use Remote Data Services in a Clienside Web page, the drivers for that datasource wouldn't need to be installed on the Web Server...

If all you need to do is hit an instance of SQL SErver that was located on a different Computer than the one that the code is running on (the Local server) then, no need to worry..
If the server was on a pc called SQLBOX then your connection string could simply be.

&quot;Provider=sqloledb;server=sqlbox;database=yourchoice;trusted_connection=yes&quot;

What linked servers (or openrowset and openkeyset functions) let you do is from the instance of sql your are querying, query a second or third server wihtout having to open a second client side conneciton and treat it as if it was infact getting data from the datasource that the current connection is referencing..

This can even allow you to join a table in the local database to an Oracle database on a Linx system..

HTH

Rob

 

[bombplayer]

Can you actually connect a remote server as a linked server? I have tried to no avail

 

[TheBugSlayer]

If you lonk all your remote servers to your server you should be able to access their data by fully qualifying the objects. Make sure you use the correct login to the remote server. I normally use the last opion on the Security tab in the linked server properties and it works fine.

 

[TheBugSlayer]

OPENQUERY works too, i.e:

select top 10 * from OPENQUERY([lnk-srv], 'select top 5 * from table1')