SQL Server Performance

SQL-DMO & 2K5 tip...

Discussion in 'Contribute Your SQL Server Scripts' started by Haywood, Sep 29, 2006.

  1. Haywood New Member

    I'm posting this because I haven't had time to learn SMO and port my DMO based stuff to it yet and am still having to use the 'old' kung-foo on the new servers.

    I learned to use DMO within t-sql from the MSDN site, Scripting Guys & Technet the last couple years. All the code that I've seen, used and written when creating a SQLServer object never had a call to the DisConnect method and it was assumed that the sp_OA_Destroy would always disconnect for you by destroying the object explicitly.

    That behavior has changed in 2005 to some degree. On two servers out of a dozen 2K5 installs I've seen the SQLServer object still connected via DMO. Finally thinking like a developer I realized that all this time I was relying on the sp_OA_Destroy to cleanup my connections for me.

    I present to you, corrected code that doesn't leave connections on 2K5, just in case anyone else is using it in the same manner I do (maintenance routines)...




    DECLARE
    @object int
    , @hr int
    , @return varchar(255)
    , @src varchar(255)
    , @desc varchar(255)
    , @BackupDirectory varchar(255)



    -- Get backup directory path from DMO query.
    --
    -- Create an SQLServer dmo object.
    EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @object OUT
    IF @hr <> 0
    BEGIN
    EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
    SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
    RETURN
    END

    -- Set trusted connection property.
    EXEC @hr = sp_OASetProperty @object, 'LoginSecure', TRUE
    IF @hr <> 0
    BEGIN
    EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
    SELECT hr=convert(varbinary(4),@hr), Source=LEFT(@src,50), Description=LEFT(@desc,100)
    RETURN
    END

    -- Connect to the server.
    EXEC @hr = sp_OAMethod @object, 'Connect', NULL, @@SERVERNAME
    IF @hr <> 0
    BEGIN
    EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
    SELECT hr=convert(varbinary(4),@hr), Source=LEFT(@src,50), Description=LEFT(@desc,100)
    RETURN
    END

    -- Get the default BackupDirectory.
    EXEC @hr = sp_OAGetProperty @object, 'Registry.BackupDirectory', @BackupDirectory OUT
    IF @hr <> 0
    BEGIN
    EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
    SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
    RETURN
    END

    -- Disconnect from the server. <-- Make sure you disconnect or connections get hung on 2K5.
    EXEC @hr = sp_OAMethod @object, 'DisConnect'
    IF @hr <> 0
    BEGIN
    EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
    SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
    RETURN
    END

    -- Cleanup the object that was created.
    EXEC @hr = sp_OADestroy @object
    IF @hr <> 0
    BEGIN
    EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
    SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
    RETURN
    END
  2. satya Moderator

    A good one which helps to take out other methods to tackle the issue, thanks.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.

Share This Page