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 6 (of 6 total)

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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy