Today I wanted to import the last bits of data from the old server. The new server is a 2005 64 bits SQL server and the old server is a 2000 32 bits. I’m no DBA and everything is internal so I don’t really care about service packs and I can’t remember which has which allthough I’m sure the 2000 box has the latest SP but since they aren’t even connected to the internet updating isn’t easy.
So today I setup a linked server between the two. I used my desktop to connect to the new server and then setup the linked server. And surprise surprise it didn’t work. This was more or less the error I got.
**OLE DB provider “SQLNCLI” for linked server “servername” returned message “Communication link failure”.
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
Msg 18456, Level 14, State 1, Line 0
Login failed for user ‘NT AUTHORITYANONYMOUS LOGON’.**
I actually never solved this problem. I just gave ANONYMOUS LOGON rights on the database and deleted it shortly after. It is internal remember. That solved that error message. On to the next error message.
**OLE DB provider “SQLNCLI” for linked server “servername” returned message “Unspecified error”.
OLE DB provider “SQLNCLI” for linked server “servername” 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 “SQLNCLI” for linked server “servername”. The provider supports the interface, but returns a failure code when it is used.**
This one was solved by doing a google. And finding this.
At the same time Ted (onpnt) gave me this solution and he the asked why I hadn’t told him it was a 64 bit 2005 and the 2000 was 32 bit. Well Ted you didn’t ask ;-). So it’s all Teds fault it took me more then an hour to figure this out. Well that’s my story anyway.
The solution is to create this procedure.
tsql
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
because I was using the ANONYMOUS LOGON I had to give that exec rights on that newly created SP. After that everything worked like it should.
A little bird also told me that the windows authentication didn’t work because of to many jumps. Next time I’ll try to solve that if possible.
I would like to thank George and Ted for their patience and help in solving this problem.
Chris is awesome.
