SQL Server 2005 as linked server in SQL Server 2000

  • Hi,

    We have 2 production SQL server 2000 standard edition servers called Server A and Server B

    Server A has Server B as Linked server.

    Now, We upgraded server B to sql server 2005 using side-by-side upgrade method in server C.

    and then I deleted the Server B(linked server in Server A) from linked server list and added the Server C as linked server in Server A.

    and Iam getting the below error when one of the Stored procedures is running.

    SQL Server does not exist or access denied. [SQLSTATE 42000] (Error 17). The step failed.

    So my question is:

    1. Can we add sql server 2005 as linked server in sql server 2000.

    how can I resolve this issue

  • Hi,

    Now your server 2000 and 2005 are in different Box, say server 2000 is your client, and 2005 is your target server. So, first, try to find osql.exe on your client box, "c:\program files\microsoft sql server\80\tools\binn", osql.exe /S /E, or "osql.exe /S\ /E"( the latter is for named instance). would you also get the same error? For remote connection specific you need following checklist:

    A. Enable "File and printer sharing" in Firewall if your server is behind that.

    B. Add program "sqlbrowser.exe" or udp port "1434" in your firewall exception list.

    C. Add tcp port "1433" of default instance or of named instance in firewall, check you errorlog to figure out which port server is listening on. If you want your connection over TCP and make sure your server has TCP enabled.

    D. Try ping your remote server machine, name and ipaddress.

    E. Try to telnet

    F. Try to access a share of your remote server.

    If above list all passed, you should be able to connect to linked server unless other problem exists, normally, you could see event log or server log to get clue.

    Good Luck!

    GURSETHI

  • You didn't provide much detail about the edition of SQL you upgraded to, so have a look at this topic as well, if you're trying to link to a 64-bit SQL 2005 server from a 32-bit SQL 2000 server.

  • Yes i think you can add 2005 as a linked server to 2000 if u have given the proper credentials..........

    Tanx 😀

  • Yes, the upgraded one is now in 64 bit.In this case can we link a 64 bit sql server 2005 as a linked server in 32 bit sql server 2000?

  • Hi again

    Yes, you can link to the 64-bit SQL 2005 from 32-bit SQL 2000, but you will have to read through the topic I posted earlier, to find the stored procedure that you must run on the master database in SQL 2000.

    R

  • It looks like the problem is about security. You allready had some advice about firewall settings. Take a look to the Security Settings in the linked server properties:

    You can use the same user (select the 3rd radio boton in the option blow the screen).

    or you can use a specific user (select the 4th radio botton and put the name and password for the user).

    The first two are not recomended to be used.

    I hope this can help you.

    Alberto

    Alberto De Rossi
    Microsoft Certified Solutions Associate - SQL Server
    Microsoft MVP - Data Platform
    Power BI User Group Lima - Community Leader

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply