Linked Servers - Provider types.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Linked Servers - Provider types.

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-09-08 : 07:17:33

Ric writes "Hi SQLTeam.com,

I have linked 2 instances of SQL Server, using this syntax:
Exec sp_addlinkedserver
@server='RIC',
@srvproduct='',
@provider='SQLOLEDB',
@provstr='DATA SOURCE=RIC;USER ID=sa;PASSWORD=sa;'

When I use the following query, data is returned which is good.
SELECT * FROM RIC.distbo001.dbo.branch

However, I wish to query another database on the linked server, and when I try this:
SELECT * FROM RIC.whbk001.dbo.purchase_order_header

I get the following message:
Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'RIC' does not contain table '"whbk001"."dbo"."purchase_order_header"'. The table either does not exist or the current user does not have permissions on that table.
OLE DB error trace [Non-interface error: OLE DB provider does not contain the table: ProviderName='RIC', TableName='"whbk001"."dbo"."purchase_order_header"'].

whbk001 is the only database on RIC I cannot access(!)

I have tried changing the provider from SQLOLEDB to MSDASQL but then I couldn't access any of the databases on RIC.

Can you help? Is there any way to determine the provider of a SQL database? Or have I been diverted by the error message?

Many Thanks In Advance,
Ric"

Thrasymachus
Constraint Violating Yak Guru

483 Posts

Posted - 2005-09-08 : 09:15:31

I do not think your issue is provider related.

First things first. Please tell me that your sa password is not sa. I also would not use your sa account for this. The sa account should not be used except in special circumstances. Never in application code.

Now to the problem at hand. I would create an account on your local server that has only the access and the permissions over the databases it needs and then I would use sp_addlinkedsrvlogin to map that login to a login on your remote server that has database access in each of the databases you need access to. You can do this latter part by using sp_helplogins.

====================================================
Regards,
Sean Roussy

"pimpin ain't easy, but someone has to do it" -- Pimpin Whitefolks(?)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources