November 2, 2005 at 9:57 am
I'm attempting to replicate functionality that we had in SQL 2000 utilizing two datbases on SQL 2005 (RTM) in 8.0 compatibility (kinda) mode.
When running a stored procedure which accesses both databases on the same server utilizing a self referential linked server I get the following error:
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server ".\sql2005". The provider supports the interface, but returns a failure code when it is used.
For testing and to eliminate some potential security issues I am connecting the linked server under the sa account and have marked both datbases as trustworthy. The query is using 4 part names (for the remote datbase, 3 part names for the current datbase context) and doing some cross server joins and updates.
The only references that I can find on MSDN are related to connecting a sql2000 (pre sp3) database to sql2005 or sql200564bit to sql200032bit connections.
Can anyone help me in this regard. The recent release meands that there is little or no user experience literature on the net, and the documentation is frustratingly specific to individual settings and does not provied real use examples.
November 8, 2005 at 5:46 am
Hi,
Why are you using a self referential linked server?
November 8, 2005 at 6:41 am
The end user has the oportunity to install the target database on either the same server as the source database or a remote server. The script has to be able to operate under both conditions.
I have determined that some of the issues that I was seeing was related to having sqlexpress installed as part of vs2005, then installing sql2005 standard, then removing sql2005 standard. sqlexpress was left in a unstable (but mostly working) state.
I have since removed sqlexpress and reinstalled. I still get the beforementioned error sporadically, but am able to run the procedure through query analyzer. still get the error reliably when running under vs express manager.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply