Post reply

Without linked servers

  • February 28, 2012 at 7:36 am

    #252351

    Hi All,

    How we can join two different table in different servers without linked server. As I dnt have linked server for them .

    Suppose we have instance A ( SQL server) , database B , schema C , table D .

    And we have another server W (SQL server) , database X, schema Y, table Z.

    Both table D & table Z have common column M . So how we can join them without linked server?.

    Thanks all

  • February 28, 2012 at 7:42 am

    #1452604

    openrowset or openquery, which is a kind of temporary linked server...

    is the question how to do that, or is this a question more along the lines of connectivitiy options?

    SELECT *

    FROM OPENROWSET('SQLOLEDB','Server=yourservernamehere;Trusted_Connection=Yes;Database=Master',

    'Set FmtOnly OFF; EXEC dbo.sp_Who')

    SELECT *

    FROM OPENROWSET('SQLOLEDB','Server=DEV223;Trusted_Connection=No;UID=Noobie;Pwd=NotARealPassword;Database=Master',

    'Set FmtOnly OFF;EXEC dbo.sp_Who')

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • February 28, 2012 at 8:02 am

    #1452615

    Two other options are

    1) replication

    2) SSIS or similar which can hold connections to both servers

  • February 28, 2012 at 8:25 am

    #1452634

    Out of curiosity: why no linked servers allowed?

    -- Gianluca Sartori

  • February 28, 2012 at 8:52 am

    #1452661

    Since two of us asked the same question at the same time, this is the URL to the other thread:

    http://www.sqlservercentral.com/Forums/Topic1258885-391-1.aspx

  • February 28, 2012 at 10:01 am

    #1452709

    hi

    i am working as a developer .so when i tried to use this query

    SELECT *

    FROM OPENROWSET('SQLOLEDB','Server=yourservernamehere;Trusted_Connection=Yes;Database=Master', 'Set FmtOnly OFF; EXEC dbo.sp_Who')

    SELECT *

    FROM OPENROWSET('SQLOLEDB','Server=DEV223;Trusted_Connection=No;UID=Noobie;Pwd=NotARealPassword;Database=Master', 'Set FmtOnly OFF;EXEC dbo.sp_Who')

    it showed me this error

    SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure.

  • February 28, 2012 at 11:13 am

    #1452748

    GrassHopper, You need the DBA to create a linked server for you in order for you to use Openquery in your select stmt.

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

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