QUERY on two different servers

[mlosso]

If I have two SQL Server connections open to two different servers...can I run a query, for instance a INNER JOIN on tables on seperate servers?

[techgnome]

YEs.... and no.... or rather no and yes.

You can, but the user context that is running the query will have to have acces to both eservers.

If I remember right.... hte syntax is:

Server.Database.dbo.TableName

-tg

[mlosso]

Apprently I need to do linked servers to do this?

[techgnome]

I don't know for 100% sure.... but any time I've needed to do it, it has been through linked server connection, so I assume that one does.

-tg

[szlamany]

We have also always used LINKED SERVERS - even to go to obscure places like PERVASIVE...

[Shuja Ali]

Another way of doing this would be to use OPENDATASOURCE function. You should be able to check Books Online for a sample.

[szlamany]

I believe I used OPENDATASOURCE for that PERVASIVE DB but still needed the DSN connection string to be loaded as a LINKED SERVER - I will check when I get into work later...

[mlosso]

That would be awesome if yall could check on this for me.

[Shuja Ali]

That would be awesome if yall could check on this for me.

A simple example would be

SELECT   *
FROM      OPENDATASOURCE(
         'SQLOLEDB',
         'Data Source=ServerName;User ID=MyUID;Password=MyPass'
         ).Northwind.dbo.Categories 

As taken from BOL.

[mlosso]

Ya I saw that on MSDN. Working with it now, can't seem to get it work just right yet.

[mlosso]

Is it possible that OPENDATASOURCE CAN BE BLOCKED?

[Shuja Ali]

I don't know of whether it can be blocked. I don't think so. If you are using Right userId and Password then you should be able to use it properly.

[szlamany]

Have you tried LINKED SERVER yet?

Go into ENTERPRISE MANAGER - under the SECURITY branch in the object panel on the left side open LINKED SERVERS - you can right-click on that to add a NEW LINKED SERVER.

Fill in the various text boxes - security and what not - and that server will appear available to you.

For example - we linked to a SERVER called STRATACS - then in a QUERY in our regular local database/table we were able to:

SELECT * FROM STRATACS.TVBD.DBO.PARTYLOG.

Using LINKED SERVERS allows you to access tables with standard SELECTS and JOINS - that's a very nice benefit over OPENDATASOURCE.

For our PERVASIVE DB that we did use OPENQUERY with we also LINKED servers. Since it was not SQL SERVER we selected OTHER DATA SOURCE and chose OLEDB PROVIDER FOR ODBC as the PROVIDER NAME.

Then in STORED PROCEDURES we could:

SELECT * FROM OPENQUERY(LINKNAME, 'SELECT...')

This executes the 'SELECT ...' on the ODBC connection to that PERVASIVE DB.

[mlosso]

Reason I have not done Linked Server is because I must request access to do it, which could take some time. I need to do the OPENDATASOURCE using Windows Authentication...doesnt seem to be working..

[szlamany]

Should be a matter of specifying INTEGRATED SECURITY instead of that USERNAME or PASSWORD...

[mlosso]

Thats what I thought, but when I do specify SSPI, it says server cannot be found when I clearly am connected to it in Enterprise Manager...any special lashes or backslashes?

[szlamany]

This works:

Select *
   From OpenDataSource('SQLOLEDB'
             ,'DATASOURCE=servername;INTEGRATED SECURITY=SSPI').dbname.dbo.tablename