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 =
     User/PW= misAdmin09 / 8myname8
B. Server =
     User/PW= misAdmin10 / 9myname9

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].splmis0910.dbo.mstemployee but getting error msg as
"Could not find server '' 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

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
This should do:
Make sure your table names are correct and replace * with required columns..
SELECT * from
Persist Security Info=False',
'select * from dbo.mstEmployees') as a
inner join 
Persist Security Info=False',
'select * from dbo.mstUsers') as b on a.EmpCode=b.UserCode

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony PerkinsCommented:
>>without linking servers which is not supported by remote servers I am using. <<
Is OPENROWSET supported ?
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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:
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.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> Is OPENROWSET enabled on your SQL Server?

Got your question now Anthony..
Sorry for the confusion.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.