How to execute distributed query without setting linked server?

  • Hi,

    I want to write a query that will allow me to involve two different tables on different databases on different servers.

    for eg. select *

    from server1.db1.table1

    where field1 in (select * from server2.db2.table2).

    Is there any way to use this query without setting up linked server?

    Thnx.!

    :rolleyes:

  • perhaps OPENROWSET could help:

    From Books Online:

    SELECT a.*

    FROM OPENROWSET('SQLNCLI', 'Server=Seattle1;Trusted_Connection=yes;',

    'SELECT GroupName, Name, DepartmentID

    FROM AdventureWorks.HumanResources.Department

    ORDER BY GroupName, Name') AS a;

  • Hi Slawek,

    I can't use this option I dont have permission on other server apart from select.

    But I found another way to achieve this.

    May be if you are interested in looking at a way then see below post -

    http://www.sqlservercentral.com/Forums/Topic984899-364-1.aspx

    Regards,

    Sacheen

    :rolleyes:

  • Hmm,

    I might be wrong but OPENROWSET has nothing to do with special or elevated permissions on remote server, but it requires Ad Hoc Remote Queries to be allowed on local server (plus perhaps some other registry settings).

    In other words - Ad Hoc Remote Queries needs to be enabled on server from which OPENROWSET will make a connection to some remote server.

    You do not need any special permissions on remote server, apart from connect and select.

    For details refer here: http://msdn.microsoft.com/en-us/library/ms190312.aspx

    "OPENROWSET permissions are determined by the permissions of the user name that is being passed to the OLE DB provider."

    When it comes to mentioned to problem referred in http://www.sqlservercentral.com/Forums/Topic984837-391-1.aspx. - this can be solved without using dynamic SQL:

    WITH WhereSet AS (

    SELECT ColumnA FROM OPENROWSET(

    'SQLNCLI',

    'Server=remoteserver;Trusted_Connection=No;Uid=someusername;pwd=someuserpassword',

    'SELECT somesinglecolumn AS ColumnA FROM databaseonremoteserver.schema.table')

    )

    SELECT columnfromlocaltable

    FROM localtablename

    WHERE someothercolumn IN (SELECT ColumnA FROM WhereSet)

    That is what Raunak meant writing about CTE I believe.

    On local server you do not need any permissions because OPENROWSET does not read any local object. However someusername account should have CONNECT granted on remoteserver, should be an user in databaseonremoteserver with SELECT granted on schema.table

    Regards,

    Slawek

Viewing 4 posts - 1 through 3 (of 3 total)

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