How to execute a SP using SP_OAmethod and ADODB not with SQLDMO.

  • Hi ,

    If anybody know how to call a SP using sp_oamethod and ADODB then please....

    I know how to excute a DML using the same mothod as shown below. This function

    ALTER Function fn_GenerateTableKey

      (

     @sTable as varchar(100),

        @lKeyCount as integer

      )

    RETURNS INTEGER

    AS

    BEGIN

    /*

        This procedure uses ADO (COM version not .NET) to perform the key generation

        outside of the currently running transaction to avoid locking the TableKeys

        table. 

    */

      DECLARE @sConnectionString varchar(150)

     set @sConnectionString = 'Provider=SQLOLEDB;Server=LDS000334;Database=WORKDB;UID=sa;PWD=sqlserver;Persist Security Info=True'

      DECLARE @lKey int,

              @lOldKey int,

              @cn int,

              @rs int,

              @nAttempt smallint,

              @hResult int,

              @sErrorSource varchar(255),

              @sErrorDesc varchar(255),

              @sSQL varchar(255),

        @cmd varchar(200)

      SELECT @lKey = 0

      -- Create connection object.

      EXEC @hResult = sp_OACreate 'ADODB.Connection', @cn OUT, 1

      IF @hResult <> 0

      BEGIN

        EXEC sp_OAGetErrorInfo @cn, @sErrorSource OUT, @sErrorDesc OUT

        RETURN -2

      END

      -- Connect to database.

      EXEC @hResult = sp_OAMethod @cn, 'Open', NULL, @sConnectionString

      IF @hResult <> 0

      BEGIN

        EXEC sp_OAGetErrorInfo @cn, @sErrorSource OUT, @sErrorDesc OUT

        RETURN -4

      END

      -- Main retry loop.

      -- We're trying to update the keys table and then re-read it to make sure another session hasn't

      -- updated at the same time.  If we read back a different key, then we're in contention with another

      -- session and we should try again.

      -- The Update is executed on the ADODB connection so that it runs outside of our transaction and will

      -- not cause a lock on the TableKeys table.

     SELECT @lOldKey = lKey FROM TableKeys WHERE sTable = @sTable

     SELECT @nAttempt = 1

     WHILE @nAttempt <= 25

        BEGIN

        -- Increment Key.

        SELECT @sSQL = "UPDATE TableKeys SET lKey = lKey + " + Cast(@lKeyCount as varchar) + " WHERE sTable = '" + @sTable + "'"

        EXEC @hResult = sp_OAMethod @cn, 'Execute', @rs OUT, @sSQL

        IF @hResult <> 0

        BEGIN

          EXEC sp_OAGetErrorInfo @cn, @sErrorSource OUT, @sErrorDesc OUT

          RETURN -5

        END 

        -- Re-read and test.

     SELECT @lKey = lKey FROM TableKeys WHERE sTable = @sTable

        IF @lKey = (@lOldKey + @lKeyCount)

          BEGIN

            BREAK

          END

        ELSE

          BEGIN

            SELECT @nAttempt = @nAttempt + 1,

            @lOldKey = @lKey

            -- We should pause here to try and "lose" the session we're in contention with.

      Set @cmd = 'osql -E -d ' + db_name() + ' /Q "exec TimeDelay 200,' + cast(@nAttempt as varchar(5)) + '" '

      EXEC master.dbo.xp_cmdshell @cmd

            CONTINUE

          END

     END

      -- Disconnect from database.

      EXEC @hResult = sp_OAMethod @cn, 'Close'

      IF @hResult <> 0

      BEGIN

        EXEC sp_OAGetErrorInfo @cn, @sErrorSource OUT, @sErrorDesc OUT

        RETURN -97

      END

      -- Destroy connection.

      EXEC @hResult = sp_OADestroy @cn

      IF @hResult <> 0

      BEGIN

        EXEC sp_OAGetErrorInfo @cn, @sErrorSource OUT, @sErrorDesc OUT

        RETURN -99

      END

      -- If we failed then return -1, otherwise return key.

      IF @nAttempt > 25

      BEGIN

        SELECT @lOldKey = -1

      END

     

      RETURN @lOldKey

    END

     

    Cheers

    Rajesh

     

     

  • Hey buddy, just a quick tip, y not create another object of type i.e. sp_OACreate 'ADODB.Command' and then try set the property using sp_OASetProperty for CommandType=adcmdstoredprocedure

    and commandtext="ur proc name"..

    By the way, just curious to know y u going the long way..cant u make 2 separate transactions and do it within sql instead of using ADO object model..Just a suggestion ;-)...Good luck and let me know the results

    Regards,

    Dilip

  • Hi Dilip,

    Well, my knowledge in VB or .NET is very limited that is the reason why you could see this particular thread. I know I have to use ADODB.Command and other lots of stuff and hence I am struggling a bit. If anybody could write the code and post it here would be very much appreciated.

    If you have noticed my script, it increases the concurrency without having any blocking issues. We are not in a position to use IDENTITY columns or GUID due to the peculiar behaviour of our application. So we are generating the PRIMARY keys by calling the above function.  I know there is going to be a slight performance degradation but that is acceptable rather than hanging the system due to bloking issues.

    Cheers

    Rajesh

     

  • Here you go...

    Step1

    I created a sample stored proc in my northwind database which inserts one record into region table

    --**************************************

    create procedure usp_InsRegion

    as

    set nocount on

    insert into dbo.region values('5','Asia')

    --**************************************

    Step 2 Execute it the OLE way

    --*************************************

    DECLARE @cn int,

    @hResult int,

    @sErrorSource varchar(255),

    @sErrorDesc varchar(255),

    @sSQL varchar(255),

    @sconnectionstring varchar(255),

    @objcommand int

    set @sconnectionstring = 'Provider=SQLOLEDB;Data Source=epi-dilip;Initial Catalog=Northwind;UID=sa;PWD=;'

    set @sSQL = 'usp_InsRegion'

    -- Create connection object.

    EXEC @hResult = sp_OACreate 'ADODB.Connection', @cn OUT, 1

    IF @hResult 0

    BEGIN

    EXEC sp_OAGetErrorInfo @cn, @sErrorSource OUT, @sErrorDesc OUT

    --RETURN -2

    END

    -- Connect to database.

    EXEC @hResult = sp_OAMethod @cn, 'Open', NULL, @sConnectionString

    IF @hResult 0

    BEGIN

    EXEC sp_OAGetErrorInfo @cn, @sErrorSource OUT, @sErrorDesc OUT

    -- RETURN -4

    END

    --Create command object

    EXEC @hresult = sp_OACreate 'ADODB.Command', @objCommand OUTPUT ,1

    IF @hResult 0

    BEGIN

    EXEC sp_OAGetErrorInfo @cn, @sErrorSource OUT, @sErrorDesc OUT

    -- RETURN -4

    END

    --Set connection string for command object

    EXEC @hresult = sp_OASetProperty @objCommand, 'ActiveConnection', @sconnectionstring

    IF @hResult 0

    BEGIN

    EXEC sp_OAGetErrorInfo @cn, @sErrorSource OUT, @sErrorDesc OUT

    -- RETURN -4

    END

    --Set the commandtext property i.e. stored proc name

    EXEC @hresult = sp_OASetProperty @objCommand, 'CommandText', @sSQL

    IF @hResult 0

    BEGIN

    EXEC sp_OAGetErrorInfo @cn, @sErrorSource OUT, @sErrorDesc OUT

    -- RETURN -4

    END

    --Set the command type as adcmdstoredproc constant value is 4

    EXEC @hresult = sp_OASetProperty @objCommand, 'CommandType', 4

    IF @hResult 0

    BEGIN

    EXEC sp_OAGetErrorInfo @cn, @sErrorSource OUT, @sErrorDesc OUT

    -- RETURN -4

    END

    -- Invoke execute method

    EXEC sp_OAMethod @objCommand, 'Execute',NULL, NULL

    --********************************************************

    You can very well use this as an example to extend your existing logic..Hope that helps

    Good luck

    Regards,

    Dilip

  • Thanks Dilip,

    It looks good. However let me give it a try.

    Cheers

    Rajesh

     

     

  • Hi Dilip,

    It is working perfetly fine and loads of thanks.

    Cheers

    Rajesh

  • Rajesh,

    You are welcome,buddy.

    I'm not sure if I understand your business logic correctly but what I get from ur post is you are looking for a different connection to SQL altogether so that u can avoid any locking issues.

    Easier way would be to ask your developer to develop a assembly/component in .net/vb or any language and call the business logic which would be embedded in your sql stored procedure. Just a suggestion..Good luck and all the best.

    Regards,

    Dilip

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

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