June 26, 2006 at 8:12 pm
Hi,
I have installed SQL Server 2005 on servers A and B, created a 'linked server' in A to connect to B, this works fine ie able to do select from the remote table (in B) with expected results. But when tried the linked server in B to select from database table in A, the error message below comes up, in SQL Query:
7399..The OLE DB Provider “MSDASQL” for linked server <linked server name> report an error… Invalid use of schema or catalogue for OLE DB provider “MSDASQL” for linked server <linked server name>. A four-part name was supplied but the provider does not expose the necessary interfaces to use a catalogue or schema …
Any pointers much appreciated!
June 27, 2006 at 2:47 am
What operating system do both sql servers have?
In case of windows 2003 you have to be aware of:
June 27, 2006 at 10:05 pm
Thanks indeed for this Jo.
It is windows 2003 for both. Will try the workaround.
June 27, 2006 at 10:49 pm
No ( - should have read the workaround earlier) - as what's described there, is already in place ie msdtc service running as Network services, msdtc had been configured to allow network transaction (and firewall is off currently) ...
I should add that when using the "openquery" method, eg select * from openquery(<linked server>,'select * from <table>') this returned no error
June 28, 2006 at 2:30 pm
Have you checked security delegation for the linked servers?
A guess in the dark, have you rebooted the machines to make sure that MSDTC is started before sql server?
The windows servers have the same patch level?
June 28, 2006 at 5:43 pm
Jo,
as it turned out, it was due to the provider options on A: After disabling all the provider options (except for 'Allow inprocess' ) was able to proceed with success
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply