Monday, May 14, 2007

What a headache. . . linked servers and remote connections.

What a headache this has been. I've been trying to setup an SSIS package to run nightly for updating some data on a couple of our servers in development. After researching as to why I wasn't able to run the remote connection from our Sql Server 2k5 to Sql Server 2000. I finally generated this error:
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "FOO". The provider supports the interface, but returns a failure code when it is used.

and found the solution here

A little package that should have been run when the service pack was updated, but it didn't. I noticed that this is an issue with SP3 and SP4... too bad they didn't catch it in SP3 for the SP4 update. (though to be fair, they may not have known the package wasn't executing on the SP until people caught it with 64bit Sql Server)

Oh well. Hopefully, this will fix the error.

1 comment:

Mike said...

After running the sql packages, and seemingly updating/creating a ton of stuff, it was a success!

My 64bit Sql Server 2k5 can now talk to the 32bit Sql Server 2000 db. Yeah!