Post reply

Running DTS package on 2000 server connecting to 2005 server

  • May 18, 2007 at 12:02 pm

    #117287

    Hi

    We have some DTS packages running on reporting server (2000) that pulls data from several production tables every night.

    Production server got upgraded to 2005 few days back and this job was missed during testing.

    When I try to change the source server name or anytyhing

    I get the following error.

    'you must use 2005 management tools to connect to this server'.

    or

    Error:  -2147467259 (80004005); Provider Error:  0 (0)

       Error string:  [Microsoft][ODBC SQL Server Driver][SQL Server]To connect to this server you must use SQL Server Management Studio or SQL Server Management Objects (SMO).

    How do I make this work?

    Thanks in advance

     

  • May 19, 2007 at 12:22 pm

    #707650

    I believe you are trying to connect to sql server 2005 using Enterprise Manager which is not allowed....

    where as you can open dts package and change your connection...

     

    MohammedU
    Microsoft SQL Server MVP

  • May 20, 2007 at 10:28 pm

    #707722

    You will most likely need to download the DTS tools for SQL 2005. You can get these from:

    http://download.microsoft.com/download/4/4/D/44DBDE61-B385-4FC2-A67D-48053B8F9FAD/SQLServer2005_DTS.msi (x86 version)

     

    Kindest Regards,

    Catherine Eibner
    cybner.com.au

  • May 21, 2007 at 8:10 am

    #707833

    Recap:

    Package resides on sql server 2000 which is destination.

    This package is suppossed to xfer data from sql server 2005 to sql server 2000.

    I understand that I can not connect to 2005 using management studio. Can someone guide me as to what steps are requited for this scenario to work. Do I need to get 2005 management studio installed on 2000 server? will this resolve the connectivity issue?

    Sorry I am a newbie i 2005 and there is a lot to learn and it is overwhelmig.

    Thanks,

    Shaili

     

  • May 22, 2007 at 10:14 am

    #708121

    You may need to check your ODBC driver on the SQL reporting Server and make sure the SQL Native Client is available since SQL Server 2005 is using SQL Native Client to connect to.

  • May 22, 2007 at 11:58 am

    #708144

    Make sure to check the connection string to the SQL Server 2005 database.  The 2000 server may not have the latest MDAC and therefore can not access the 2005 server.  I'm replicating from a 2005 to a 2000 server, so communication should be allowed between the two. 

    Marvin Dillard
    Senior Consultant
    Claraview Inc

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

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