[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 356
  • Last Modified:

table join on diff database on diff server with diff security context

I have two databsse residing on two different servers with different usr authentication. I want to join tables between them. Can anybody help me doing so without linking servers which is not supported by remote servers I am using. the example is:-
A. Server = 74.55.199.22
     User/PW= misAdmin09 / 8myname8
     Owner=dbo
     DB=splmis0910
     Table=mstEmployees
B. Server = 75.125.2.16
     User/PW= misAdmin10 / 9myname9
     Owner=dbo
     DB=splmis1011
     Table=mstUsers

I want to link these tables on mstEmployee.EmpCode=mstUser.UserCode

Please let me now if it possible to do so without linking servers.

I am giving Select * From 75.125.2.16].splmis0910.dbo.mstemployee but getting error msg as
"Could not find server '74.55.199.22' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers."

Alok K Goyal

     
0
alokkgoyal
Asked:
alokkgoyal
  • 3
  • 2
5 Solutions
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
This should do:
Make sure your table names are correct and replace * with required columns..
SELECT * from
OPENROWSET('SQLOLEDB',
'DataSource=[74.55.199.22];Uid=misAdmin09;Pwd=8myname8;
database=splmis0910;
Persist Security Info=False',
'select * from dbo.mstEmployees') as a
inner join 
OPENROWSET('SQLOLEDB',
'DataSource=[75.125.2.16];Uid=misAdmin10;Pwd=9myname9;
database=splmis1011;
Persist Security Info=False',
'select * from dbo.mstUsers') as b on a.EmpCode=b.UserCode

Open in new window

0
 
Anthony PerkinsCommented:
>>without linking servers which is not supported by remote servers I am using. <<
Is OPENROWSET supported ?
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Yes Anthony, its supported by OPENROWSET..

"OPENROWSET Includes all connection information that is required to access remote data from an OLE DB data source. This method is an alternative to accessing tables in a linked server and is a one-time, ad hoc method of connecting and accessing remote data by using OLE DB. For more frequent references to OLE DB data sources, use linked servers instead."

Excerpt from MSDN:
http://msdn.microsoft.com/en-us/library/ms190312.aspx
0
 
Anthony PerkinsCommented:
My question was directed to the author using language that I thought would be clear to them. But let me rephrase the question to the author: Is OPENROWSET enabled on your SQL Server? Or to quote from BOL:

By default, SQL Server does not allow ad hoc distributed queries using OPENROWSET and OPENDATASOURCE. When this option is set to 1, SQL Server allows ad hoc access. When this option is not set or is set to 0, SQL Server does not allow ad hoc access.

Ad hoc distributed queries use the OPENROWSET and OPENDATASOURCE functions to connect to remote data sources that use OLE DB. OPENROWSET and OPENDATASOURCE should be used only to reference OLE DB data sources that are accessed infrequently. For any data sources that will be accessed more than several times, define a linked server.

Security Note:
Enabling the use of ad hoc names means that any authenticated login to SQL Server can access the provider. SQL Server administrators should enable this feature for providers that are safe to be accessed by any local login. For more information, see the DisallowAdhocAccess option in Accessing External Data.

0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> Is OPENROWSET enabled on your SQL Server?

Got your question now Anthony..
Sorry for the confusion.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now