Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 468
  • Last Modified:

Trying to query a 32-bit SQL Server 2000 database from a 64-bit SQL Server 2008 database using a linked server

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\my2000instance
  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\my2000instance].myuserdatabase.dbo.[myusertable]

The error is:

Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI10" for linked server "my2000machine\my2000instance". 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
http://social.msdn.microsoft.com/forums/en-US/sqldatabaseengine/thread/0782364d-dcb0-4c45-8343-5bfc4406ead4/
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"

Any ideas?

Thank you.
0
web-dba
Asked:
web-dba
  • 3
  • 2
1 Solution
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
You need to run Instcat.sql code as specified in the Resolution link below:

http://support.microsoft.com/kb/906954

like the script in your SQL Server 2000
c:\Program Files\Microsoft SQL Server\MSSQL\Install>osql -pUSER -pPASSWORD -s[SERVER] -iinstcat.sql
0
 
GSGDBACommented:
Hi Web dba,

"Login timeoutexpired"

1) Check whether you are able to ping SQL 2000 server from 2008.
If not, try add the server name and ip address in DNS.
2) Try to use a windows NT id for the access.
Below is a link which explain the same case.
http://stackoverflow.com/questions/841339/having-trouble-adding-a-linked-sql-server
0
 
web-dbaAuthor Commented:
This fixed the problem, thank you!
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Welcome..
And glad to help you out.
0
 
web-dbaAuthor Commented:
I also tried this alternate solution on another instance and it worked too.  I wouldn't have found if you hadn't pointed me to instcat.sql in the first place.  http://weblogs.asp.net/uber1024/archive/2007/07/07/linking-64-bit-sql2k5-to-32-bit-sql2k-or-how-to-avoid-wasting-10-days-waiting-for-straight-answers.aspx
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Good that you have shared over here..
Might be useful for someone who will be visiting this question sometime later..
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now