Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
To connect to this server you must use SQL Server Management Studio or SQL Server Management Objects (SMO)

Answered To connect to this server you must use SQL Server Management Studio or SQL Server Management Objects (SMO)

  • Monday, May 08, 2006 5:23 PM
     
     
    Sign In to Vote
    1

    What could be wrong?

    I get this error when a stored procedure is executing sp_OAMethod from SQL Server 2000 SP4.

    EXEC @rc = master..sp_OAMethod @Obj, 'Execute'

    The linked server is SQL Server 2005 x64.  Already installed is SQLServer2005_BC_x64.msi version April 15, 2006.  Local server is SQL Server 2000 SP4. 

    I can execute the sproc successfully in Query Analyzer from my local dev box which has both 2000 and 2005 installed.  Profiler in 2005 indicates success for the whole sproc. 

    It fails when I execute in Query Analyzer while connected on SQL 2000 SP4.  Tried this with 3 different machines SQL Server 2000 SP4.  The server do not have any 2005 component installed.

    According to Profiler in 2005, the last event that happen is

    SQL: BatchCompleted

    if (object_id('master.dbo.sp_MSSQLDMO80_version') is not null) exec master.dbo.sp_MSSQLDMO80_version else if
    (object_id('master.dbo.sp_MSSQLDMO70_version') is not null) exec master.dbo.sp_MSSQLDMO70_version else select 0

    Below is the trapped Query Analyzer message.

    Error Source: Microsoft Data Transformation Services (DTS) Package 
    Error Description:Package failed because Step 'dts_name_removed' failed. 
    Error code: 80040428 \
    Error Help File:sqldts80.hlp 
    Error Help Context ID:700 


    Step Error Source: Microsoft SQL-DMO (ODBC SQLState: 42000) 
    Step Error Description:[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). 
    Step Error code: 8004D903 
    Step Error Help File:SQLDMO80.hlp 
    Step Error Help Context ID:1131 

    Thanks

     

All Replies

  • Monday, May 08, 2006 9:26 PM
     
     Answered
    Sign In to Vote
    0

    It looks like your DTS package is using an object that is not supported against a 2005 server. I would guess that this is one of the Transfer Tasks, which only knows about 2000 objects, and hence can not be made to operate against the richer 2005 server

    One workaround would be to move to SSIS.

     
  • Thursday, May 11, 2006 3:01 PM
     
     
    Sign In to Vote
    0

    Thanks for your reply.

    I narrowed the scenario to where it fails.  It is an issue between SQL Server 2000 and 2005.  In my case, it is failing when the stored procedure is executed in SQL 2000 and trying to dynamically DTS over to SQL 2005 using OLE Automation sprocs. 

    However, it is successful if the SQL 2000 box has also an installed instance of SQL 2005.  I can run successfully the same code in Query Analyzer connected to either the 2000 or 2005 instance.  But it fails if the box has only SQL 2000.

    In our production, I cannot simply install a SQL 2005 along side SQL 2000.  At this time we have no plan on upgrading the production SQL 2000 box - it is shared by many other apps.  So this leaves me to think that some shared OLE dlls were overwritten when the 2005 was installed.  What are they?  Can I just simply copy them over to SQL 2000 box and reregister?

    I can send the test code if anyone is interested.

    Thanks.

     
  • Friday, May 12, 2006 8:25 PM
     
     Answered
    Sign In to Vote
    0
    As it turned out, SQL Server 2000 SQLDMO is broken when connecting to a SQL Server 2005.  One of the workaround is to install SQL Server 2005 Workstation component which includes the new SQLDMO.  This fixed my issue.
     
  • Tuesday, August 29, 2006 2:42 AM
     
     
    Sign In to Vote
    0
    Hi. I got the same issue here. Is there anyway we can obtain and install the SQL Server 2005 Workstation component separately? Thanks for any tips

     
  • Tuesday, August 29, 2006 2:26 PM
     
     
    Sign In to Vote
    0

    You can install the SQL Management Objects from the sql 2005 feature pack at http://www.microsoft.com/downloads/details.aspx?familyid=DF0BA5AA-B4BD-4705-AA0A-B477BA72A9CB&displaylang=en.

    This gives you the smo library, without the tools UI. This should prove sufficient in your case.

    jkh

     
  • Tuesday, August 29, 2006 11:13 PM
     
     
    Sign In to Vote
    0
    Thank you very much! The one I need to download would be Microsoft SQL Server 2005 Backward Compatibility Component, is that correct?


     
  • Wednesday, August 30, 2006 2:23 PM
     
     
    Sign In to Vote
    0

    Microsoft SQL Server 2005 Management Objects Collection is the one you want.

    jkh

     
  • Monday, November 20, 2006 3:12 PM
     
     
    Sign In to Vote
    0

    Thanks for the pointers but I've downloaded and installed the SMO (together with prereqs MSXML6.0 and the Native Client) and the SQL 2000 Ent Mgr still won't connect, with the same message (ie. subject). The Management Studio itself is not loaded (I can't see anything in the Microsoft SQL Server\90\Tools\Binn dir other than the VSShell but there's nothing useful in there) so unless I have it already, I'm stuffed! Rather, my client is stuffed if an admin user doesn't have access to the original SQL 2005 Install disks - they need to be able to manage the Server, I wasn't aware they need new licences for this. How do you make the 2005 SMO actually work?

    Hope somebody can help, Phil.

     
  • Thursday, June 28, 2007 7:22 PM
     
     
    Sign In to Vote
    0
    This approach did not work in my case either. Contrary to the other folks in this thread I am directly invoking the DMO object model from my application while trying to access a remote SQL server 2005 database from a client machine that has only SQL server 2000 installed. Applying the upgrades available from http://www.microsoft.com/downloads/details.aspx?familyid=DF0BA5AA-B4BD-4705-AA0A-B477BA72A9CB&displaylang=en as suggested did not solve the problem.
     
  • Thursday, September 13, 2007 8:27 PM
     
     
    Sign In to Vote
    0

    Have you found a solution?  I just hit the same.

     

    Thanks for any type of response!

     

     
  • Friday, October 05, 2007 1:31 AM
     
     
    Sign In to Vote
    0

    I have also received this error message though not via DTS. The exact error message I am receiving is:


    Error: -2147165949

    Description: [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).

     

    This error occurs in some old custom application code we have. The function in question uses SQLDMO (Microsoft SQLDMO Object Library - C:\Program Files\Microsoft SQL Server\80\Tools\Binn\sqldmo.dll). This is obviously the SQL Server 2000 (80) version of the file. (2000.085.2004.00). Distributed Management Objects OLE DLL for SQL Enterprise Manager.

     

    It works fine when connecting to 2000 servers but not 2005.


    I installed the "Microsoft SQL Server 2005 Backward Compatibility Components" from "Feature Pack for Microsoft SQL Server 2005 - April 2006" on the client machine and this resolved the issue. Specifically i installed the "SQL Distributed Management Objects (SQL-DMO) feature and disabled all other features. http://www.microsoft.com/downloads/details.aspx?familyid=DF0BA5AA-B4BD-4705-AA0A-B477BA72A9CB&displaylang=en

     

    Cheers

    Tim

     
  • Wednesday, April 30, 2008 1:15 PM
     
     
    Sign In to Vote
    0

    We just move from 2000 to 2005 and ever time I try to open store procedure or function I was getting this error message...

    [Microsoft][ODBC SQL Server Drive] To connect to this server you must use SQL Server Management Studio or SQL Server Management Objects (SMO)

    So I installed "Microsoft SQL Server 2005 Backward Compatibility Components" from "Feature Pack for Microsoft SQL Server 2005 - April 2006" on the client machine and this resolved the issue. Specifically i installed the "SQL Distributed Management Objects (SQL-DMO) feature .....,

    how I am getting following error message

     

     

    [SQL-DMO]You must use SQL Server 2005 management tools to connect to this server

     

    How can I disabled all other features.

     

    Thanks,

     

     
  • Wednesday, April 30, 2008 1:34 PM
    Moderator
     
     
    Sign In to Vote
    0

    Hello,

     

    Please, could you create a new topic , in which you may recall this topic ?

     

    This topic is considered as answered , so you risk to have no answer

     

    Have a nice day

     

     
  • Tuesday, October 13, 2009 2:28 PM
     
     Proposed
    Sign In to Vote
    0
    Hi All
    I know this thread is old but this is how I solved my connection issue:

    1. Download Microsoft SQL Server 2005 Backward Compatibility Components (http://www.microsoft.com/downloads/details.aspx?FamilyID=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en)

    2. Install SQL Server 2005 Support files

    3. Install SQL Server 2005 Native Client (I included the SDK feature during setup)

    4. Install SQL Server 2005 Backward Compatibility Components (only select the SQL 2000 runtime and DMO features. De-select the other features)

    5. This is the interesting step:
    a) In my case there were 2 sqldmo.dll files with different versions (C:\Windows\system32 and C:\Program Files\Microsoft SQL Server\80\Tools\binn)
    b) Delete the old version in C:\Windows\System32
    c) unregister the dll: regsvr32 /u sqldmo.dll - in C:\Program Files\Microsoft SQL Server\80\Tools\binn
    d) register the dll: regsvr32 sqldmo.dll - in C:\Program Files\Microsoft SQL Server\80\Tools\binn

    6. Install app and test connection

    As I said this worked for me...and I did not find a lot of info about this issue. :P

    Cheers Andrew
     
  • Friday, April 22, 2011 1:40 PM
     
     
    Sign In to Vote
    0

    another solution:

     

    symptom
    [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).

    solution
    suspect some dirty registry entries.
    do a search for "microsoft sql server" from HKEY_LOCAL_MACHINE level.
    press F3 until you find some nonsense like below

    e.g.
    InprocServer32=Z5GO?Rbe*=u)Qg*28KGG>0d5hqKYq+ADOfo)0gxXf

    delete the entry above...
    i don't know how many occurrences i delete (probably 10) and it worked after that.

     
  • Wednesday, July 27, 2011 12:46 AM
     
     
    Sign In to Vote
    0

    I have also received this error message though not via DTS. The exact error message I am receiving is:


    Error: -2147165949

    Description: [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).

     

    This error occurs in some old custom application code we have. The function in question uses SQLDMO (Microsoft SQLDMO Object Library - C:\Program Files\Microsoft SQL Server\80\Tools\Binn\sqldmo.dll). This is obviously the SQL Server 2000 (80) version of the file. (2000.085.2004.00). Distributed Management Objects OLE DLL for SQL Enterprise Manager.

     

    It works fine when connecting to 2000 servers but not 2005.


    I installed the "Microsoft SQL Server 2005 Backward Compatibility Components" from "Feature Pack for Microsoft SQL Server 2005 - April 2006" on the client machine and this resolved the issue. Specifically i installed the "SQL Distributed Management Objects (SQL-DMO) feature and disabled all other features. http://www.microsoft.com/downloads/details.aspx?familyid=DF0BA5AA-B4BD-4705-AA0A-B477BA72A9CB&displaylang=en

     

    Cheers

    Tim


    This worked great, thanks!!
     
  • Wednesday, July 27, 2011 12:47 AM
     
     
    Sign In to Vote
    0
    This worked great, thank you!!
     
  • Tuesday, May 01, 2012 2:09 PM
     
     
    Sign In to Vote
    0

    easier solution is to install sql 2005 backward computability

    http://www.microsoft.com/en-us/download/details.aspx?id=15748

    win 32 SQLServer2005_BC.msi