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 : BOL
pinaldave
Thanks Dave,
This was exactly my problem and the resolution appeared to work.
Thanks
Roy
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.
Thanks a lot Dave it really worked.