July 29, 2005 at 2:56 am
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
July 29, 2005 at 4:34 am
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
July 29, 2005 at 5:45 am
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
July 29, 2005 at 9:34 am
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
July 29, 2005 at 9:51 am
Thanks Dilip,
It looks good. However let me give it a try.
Cheers
Rajesh
July 30, 2005 at 1:19 am
Hi Dilip,
It is working perfetly fine and loads of thanks.
Cheers
Rajesh
July 30, 2005 at 3:47 am
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