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

Posted on 2011-05-09
Last Modified: 2012-05-11
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
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.
Question by:web-dba
    LVL 57

    Accepted Solution

    You need to run Instcat.sql code as specified in the Resolution link below:

    like the script in your SQL Server 2000
    c:\Program Files\Microsoft SQL Server\MSSQL\Install>osql -pUSER -pPASSWORD -s[SERVER] -iinstcat.sql
    LVL 3

    Expert Comment

    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.

    Author Closing Comment

    This fixed the problem, thank you!
    LVL 57

    Expert Comment

    by:Raja Jegan R
    And glad to help you out.

    Author Comment

    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.
    LVL 57

    Expert Comment

    by:Raja Jegan R
    Good that you have shared over here..
    Might be useful for someone who will be visiting this question sometime later..

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now