You may receive an error message when you try to run distributed queries from a 64-bit SQL Server 2005 client to a linked 32-bit SQL Server 2000 server or to a linked SQL Server 7.0 server.
Error:
The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset “DBSCHEMA_TABLES_INFO” for OLE DB provider “SQLNCLI” for linked server “<LinkedServerName>”. The provider supports the interface, but returns a failure code when it is used.
Fix/WorkAround/Solution:
Use Windows Authentication mode
For a default instance
osql -E -S <LinkedServerName> -i <Location>\instcat.sql
For a named instance
osql -E -S <LinkedServerName>\<InstanceName> -i <Location>\instcat.sql
Use SQL Server Authentication mode
For a default instance
osql -U <AdminLogin> -P <AdminPassword> -S <LinkedServerName> -i <Location>\instcat.sql
For a named instance
osql -U <AdminLogin> -P <AdminPassword> -S <LinkedServerName>\<InstanceName> -i <Location>\instcat.sql
By default, this folder is C:\Program Files\Microsoft SQL Server\MSSQL\Install.
Example:
osql -U sa -P MyPassWord -S Database.IpAddress.com -i “C:\Program Files\Microsoft SQL Server\MSSQL\Install\instcat.sql”
Reference : Pinal Dave (https://blog.sqlauthority.com)
25 Comments. Leave new
Thanks Dave,
This was exactly my problem and the resolution appeared to work.
Thanks
Roy
I wanted to ask that where can I run this ? in the SQL server 2000 32 bit or in SQL server 2008 64 bit
Hi Dave,
Do you run this on the 64-big SQL Server 2005 Client or on the 32-bit SQL Server 2000?
Thanks,
Dikran
Thanks Dave, this is just what I need.
Still the problem is not solved. the script ran on 32 bit server successfully. but still getting the same error. where i did mistake?
what should i give as the instance name? same server name or the server name of 64 bit?
osql -U sa -P sa123$ -S -i \instcat.sql
I want to set the password for only database..how to do it.?
@ Subu,
Only Logins can have passwords, users CAN NOT have passwords in any way.
Login is a permission to enter into SQL Server. That is why you need a password at the entry ( i.e. for logins).
Once you enter into SQL Server, if you have access to any database you can access that database if you dont, you cannot access that database. So users will not have passwords.
Example to create a login:
use master
create login imran with password = ‘xyzabcdef’ , check_policy = off
Once you created a login, the login imran has permission to enter into SQL Server but imran still do not have access to any database, now you will create user and associate that user to login, something like this in the example.
use AdventureWorks
create user user_imran for login imran
Here I am asscociating user_imran ( name of the user ) to login imran.
Now Login: Imran has access to AdventureWorks database.
so Users can only be created when attached to logins. and users will not have any passwords, its just a name you associate to logins.
Exceptions:
There are always exceptions
1. You can create a user without associating it to any login.
Example:
use AdventureWorks
create user imrant without login
The above script create a user imrant with out login. You can make use of this user only when you enter into the sql server, you cannot use credentials of a user to enter into SQL Server. You must use your login credentials (login and password) to enter into the SQL Server and connect to AdventureWorks database and then you can make use of this user, as something like this
use AdventureWorks
execute as user=’imrant ‘
select user_name()
you will see result : imrant
revert — to revert back the user
2. You can have a password for a user, but that is called as Application Role.
Application role is just a like a database user with a password.
BUT, you cannot associate application user to any login.
You cannot enter into SQL Server using Application role credentials.
Application role is a vast concept, Let us take an example.
you have a front end application which uses SQL Server as its backend. The application uses AdventureWorks database.
You have one very important table, that you dont want users to access directly when then login into SQL Server. But the users need to insert data into this particular table. In this scenario when users should not have directly access to tables but only through applications, Application role play very important role.
1. You create an application role in AdventureWorks database. This application role is given write permissions on a specific table which no other user has.
2. Front end application is configured to use this application role which you created with username and password in step 1.
3. Users first login into application using their login name and password ( authenticated by SQL Server ).
4. These users do not have write permission on a specific table.
5. When users run the application, and try to insert any record into this specific tables ( through front end application), the application uses this application role ( this application role has permission to write this table), This application role then connects to that database and performs the write operation on that table.
Users even though do not have write permission to that table, can still write to the table using Application. if they login into SQL Server ( SSMS, Enterprize Manager) they cannot write to that table.
And also you cannot enter into SQL Server ( SSMS, Enterprize Manager) using Application role Credentials. So in those situations you can use application.
Hope this helps,
Imran.
Excelent explanation! Thanks for your effort!
Thanks a lot Dave it really worked.
I have found the below error message during RPC using linked server.
Server: Msg 17, Level 16, State 1, Line 1
SQL Server does not exist or access denied.
or
Login failed for sa
Please help me.
Excellent, it works fine for me as I had the same problem between sql server 2000 32 bits and sql server 2005 64 bits.
thanks indeed.
This solution worked for me, however I went a slightly different route.
I opened up C:\Windows\System32\instcat.sql on my 64bit 2005 install, copied it and pasted it into a sql window connected to my 32bit sql 2000 install.
Ran the script and then my link started working again.
Thanks
Followed kev_mck solution and ran the script on the 32bit SQL Server 2000 box
Followed kev_mck solution and ran the script on the 32bit SQL Server 2000 box
My linked Server started working again from SQL Server 2005 box to the SQL Server 2000 box.
Dear Pinalkumar Dave
I had this problem and with using your tip, my problem was totally solved. Thank you very much!
Best Regards
Alireza Kiaee
Thanks for getting me down the right path. While I couldn’t get the osql command to work (on either end of the link), I was able to use kev_mck’s solution and run the instcat.sql query directly in SSMS. Everything is working now!
Hi! I was surfing and found your blog post… nice! I love your blog. :) Cheers! Sandra. R.
I also had this problem.
One solutions is to install the latest SP for SQL2000.
The other solution (if updating is not an option:
Instead of “select * from [linkedserver].[database].[scheme].[table]”, you can use “select * from Openquery([linkedserver],’select * from [database].[scheme].[table]’)”
Rene, your select statement was exactly what I needed. Thanks!
can anyone please assist with the following, im executing a package via the agent from one server to another on the same domain but im getting this error. when execute it manuelly it works.
Step Name Execute Package
Duration 00:00:40
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: HC-DWSQL\SYSTEM. … Version 9.00.4035.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 11:49:35 AM Error: 2010-11-11 11:50:13.87 Code: 0xC0202009 Source: Weekly_JobSalvage Connection manager “HC_Marketing” Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: “Microsoft OLE DB Provider for SQL Server” Hresult: 0x80040E4D Description: “Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’.”. End Error Error: 2010-11-11 11:50:13.87 Code: 0xC00291EC Source: truncate table Execute SQL Task Description: Failed to acquire connection “HC_Marketing”. Connection may not be configured correctly or you may not have the right permissions on this connection. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 11:49:35 AM Finished: 11:50:13 AM Elapsed: 38.844 seconds. The package execution failed. The step failed.
OLE DB provider “SQLNCLI” for linked server “PRODUCTION” returned message “Cannot start more transactions on this session.”.
Msg 7395, Level 16, State 2, Line 1
Unable to start a nested transaction for OLE DB provider “SQLNCLI” for linked server “PRODUCTION”. A nested transaction was required because the XACT_ABORT option was set to OFF.
Could you please let me know how do I resolve this without XACT_ABORT ON.
Please assist as soon as possible.
Hi Pinal,
I am trying to apply SP4 on SQL Server 7.0, it hangs while showing the message box: “Validating Password. Please wait…”
I get the same result with the Windows Login option as well as SQL Server login.
Please help me with this
Hi all,
I have experienced the same issue accross a 32 bits sql server 2008 and a 32 bits sql 2000; this process hase been working for months but lately intermittently fails with same error message which is been discussed in this thread
The provider supports the interface, but returns a failure code when it is used.; Line:31 OLE DB provider “SQLNCLI10”
for linked server “NMGBDB” returned message “Unspecified error”. OLE DB provider “SQLNCLI10” for linked server “NMGBDB” returned message “Query timeout expired”.”.
Any ideas how to resolve this?