Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI10" for linked server "XXXXXX"

I wanted to create a linked server from SQL Server 2005 to SQL Server 2000 and got the following error when I tried to query the remote server.

OLE DB provider "SQLNCLI10" for linked server "XXXXXX" returned message "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 "SQLNCLI10" for linked server "XXXXXX". The provider supports the interface, but returns a failure code when it is used.

So after some initial research found that there is a Microsoft support page for this error but requires SP3 or higher on the 2000 instance and also requires us to run a particular SQL file called 'instcat.sql in the INSTALL directory for MSSQL. But such a change would require backups of the master database. In case something went wrong.

Ref : http://connect.microsoft.com/SQLServer/feedback/details/465959/unable-to-query-linked-sql-server-2000

So I found this small workaround with less hassles.
Just create the following Stored Procedure and GRANT EXECUTE to the Public.

CREATE PROCEDURE sp_tables_info_rowset_64

@table_name SYSNAME,

@table_schema SYSNAME = NULL,

@table_type nvarchar(255) = NULL

AS

DECLARE @Result INT SET @Result = 0

EXEC @Result = sp_tables_info_rowset @table_name, @table_schema, @table_type

GO

Hope this helps........

Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’.

SQL Server 2005/2008: Linked Server issue

Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’.

After creating a linked server, I tried to run some queries against it. But I kept getting the error mentioned above. I was using an ID that was domain administrator so I was unable to think of any reason why my ID would be considered an anonymous login.

Upon further research I found several recommendations online that suggested that I should be using either a LOCAL SYSTEM Account or an ID that is not explicitly created on the remote server. But that was not the case, as my SQL Server agent and the SQL Server service account were using a domain account. So I created a login for my ID on the remote server. In spite of that I was still receiving the error.

So I thought of creating a mapping between a local login and a remote login

But I had mapped a local windows account to a remote windows account and I received the following error.

Msg 18456, Level 14, State 6, Server <server name>, Line 1
Login failed for user '<user name>'

State 6 mean I am trying to use a windows account to login to SQL Server using SQL Server authentication.

So as a final attempt I mapped a local windows account to a remote SQL Server login. Before doing that I created that SQL Server user on the remote server and granted it appropriate permissions.

That seemed to do the trick. Going forward for every local windows account that needed to access the linked server, I mapped it to the same SQL Server login on the remote server. 

SQ Server 2005/2008 : TRUNCATE Command over a linked server

So today I came across something that I thought seemed like a simple task. A client wanted to run certain commands on a remote server via a linked server. So I created a linked server for him and granted the required read/write permissions to his ID on the remote server.
But he reported that he was not able to run any TRUNCATE commands on the remote server.
Upon further investigation found that you need ALTER rights on the tables that you want to truncate. I assumed that TRUNCATE was a DML statement but that does not seem to be the case. It's considered as a DDL statement and the Login requires ALTER permissions on the table. So I was able to grant that access and his Login could truncate tables when he logged into the server. But when he executed the TRUNCATE commands on the linked server it would not let him do that. The error that he received was as follows.

Error
Msg 4701, Level 16, State 1, Line 1
Cannot find the object "TableXYZ" because it does not exist or you do not
have permissions.

After looking over the net for similar errors I found out that you can not run TRUNCATE statement on a remote server via a linked server. But fortunately someone had figured out a work around and I thought I will share this with you.

You can run a RPC for sp_executesql and run the truncate statement which seems to work. So first I change the settings for the linked server to run remote procedure by settings RPC to TRUE. Then executed the following command.

execute mylinkedserver.dbname.dbo.sp_executesql "TRUNCATE TABLE dbo.TableXYZ"