Post reply

Query two DB's on two Servers

  • October 2, 2003 at 10:38 am

    #64949

    Hi. How can I connect to a second db on a different server with tsql. More to the point, I've got to be able to connect to another server in my union query.

    I appreciate any and all help.

    Thanks. I should also add that they're not linked.

    Edited by - peygham on 10/02/2003 11:36:47 AM

  • October 2, 2003 at 11:40 am

    #476840

    Create linked server the second server and query the table with four-part qualified name.

    select * from tabe1

    union all

    select * from linkedservername.dbname.dbo.table2

  • October 2, 2003 at 12:03 pm

    #476847

    Thank you. I'll try it as soon as my dba links those two.

    Thanks again,

  • October 3, 2003 at 9:17 am

    #476996

    You can use openrowset() to return a something (I am not sure what) that can be included in a select statement as if it were a table in your current database. You just have to supply the connection string.

    eg.

    to connect using ODBC:

    SELECT a.* FROM OPENROWSET('MSDASQL',

    'DRIVER={SQL Server};SERVER=servername;UID=username;PWD=password',

    somedatabase.dbo.sometable) as a

    to connect using OLE:

    SELECT A.*

    FROM OPENROWSET('SQLOLEDB','servername';'username';'password',

    'exec somedatabase.dbo.SomeStoredProcedureThatReturnsACursor') AS A

    you can put any query in the quotes.

    It is very useful for loading data from other databases, eg Access tables

    eg.

    INSERT INTO staff

    SELECT a.*

    FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

    'c:\program files\mortgagepro\products.mdb';'Admin';'', 'select * from staff where source > 245')

    AS a

    Peter Tillotson

  • October 3, 2003 at 9:23 am

    #477001

    Thanks Peter. I'm able to run my sql once a link is established. But without one, I can't run distributed queries.

    Edited by - peygham on 10/03/2003 09:23:36 AM

  • October 3, 2003 at 9:36 am

    #477006

    I believe OPENROWSET works without linking the databases, as long as the PC you are running the query on can see the other server on the network. I may be wrong but it seems to work on my network.

    Peter Tillotson

  • October 3, 2003 at 10:00 am

    #477011

    OPENROWSET is slooooooow. Comapred to a linked server at least. The connection has to be established each time you run the query, kind of an ad-hoc linked server. Also, SQL2K SP3shuts off the peasants from running an OPENROWSET, the executing ID must be an admin account. You can change this, but it's done on a driver-by-driver basis in the registery. By 'driver', I mean the driver for the remote database...SQL Server, Jet (Access, Excel), Oracle, etc.

    This article deals with SQL7 but this is still an issue on 2000, by design.

    http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/q266/0/08.asp&NoWebContent=1

    Also see "Configuring OLE DB Providers for Distributed Queries" under Administering SQL Server in BOL.

    NOTE that the keys are different if you are using a named instance of SQL

    -------

    at us, very deafly, a most stares

    collosal hoax of clocks and calendars

    eecummings


    -------
    at us, very deafly, a most stares
    collosal hoax of clocks and calendars

    eecummings

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply