I am trying to query a 32-bit SQL Server 2000 database from a 64-bit SQL Server 2008 database using a linked server.
I can easily set up a linked server just by using the GUI and selecting New Linked Server. I'm providing these parameters:
Linked server: my2000machine\my2000instan
Server Type: SQL Server
Then on the security tab, I select "Connection will be made using this security context: Remote Login: sa With password: mysapassword
After I click ok, I can browse the different databases on that instance using SSMS and expanding Server Objects->Linked Servers->my2000instance. I can view the table structure, but there's no option to view the table data.
When I try to query the database, I get an error.
select * FROM [my2000machine\my2000insta
The error is:
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI10" for linked server "my2000machine\my2000insta
nce". The provider supports the interface, but returns a failure code when it is used.
This same select statement does return results when I run it against a linked server for a SQL Server 2005 database. I created that linked server using the same method.
I saw another post on experts exchange that pointed to this link
but after I ran the code and created the linked server using the suggested method, I tried to add the security context and it gave an error that "login timeout expired"