August 5, 2013 at 9:44 am
Hello SQL fans
I am working on one project and got to the point where I have a problem and need your help.
I have a after insert trigger and in it I want to execute Ole Automation Procedures.
The problem is, that sql doesn't allow RECONFIGURE statement within a trigger.
All I want is to execute this lines of code before procedure executen
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'Ole Automation Procedures', 1
RECONFIGURE
and disabling it after my stored procedure.
Here is my code
create TRIGGER tr_ORS_CostDrvAfterInsert
ON _tmp1
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
declare
@uri-2 varchar(2000) ,
@methodName varchar(50),
@requestBody varchar(8000) = '',
@SoapAction varchar(255),
@UserName nvarchar(100),
@Password nvarchar(100),
@responseText varchar(8000);
set @uri-2 = 'http://MyURI.php';
set @methodName = 'GET';
set @requestBody = '';
set @SoapAction = 'Method';
set @UserName = '';
set @Password = '';
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'Ole Automation Procedures', 1
RECONFIGURE
exec [dbo].[sp_ORS_MS_Request]
@methodName,
@requestBody,
@SoapAction,
@UserName,
@Password,
@responseText output;
EXEC sp_configure 'Ole Automation Procedures', 0
RECONFIGURE
EXEC sp_configure 'show advanced options', 0
RECONFIGURE
END
August 5, 2013 at 3:56 pm
Thus, you got a good incentive to reconsider your solution.
I recommend writing a CLR stored procedeure instead of using sp_OAxxxxx.
But you should not make SOAP requests inside a trigger. In a trigger you should do actions that are atomically part of the statement. That is, if the action fails, the statement fails. If the remote host to which you SOAP requests is down, should the statement that fired your trigger fail?
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 6, 2013 at 3:19 am
The only advice I can offer on that is don't do it. Find another solution. SOAP requests from SQL are not recommended, the OLE automation are fraught with problems and then sticking the whole lot in a trigger...
Look at CLR, also consider service broker for async communications. Add a message to the queue in the trigger and let the queue's activation procedure handle the processing.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 6, 2013 at 2:38 pm
Agreed, a trigger is not at all the best place for this type of thing.
RECONFIGURE is not allowed to be part of a transaction. A modification trigger is inherently part of a transaction. So, RECONFIG is impossible in this context. Thus, you may have to enable the options permanently.
Also, it's best to avoid all excess overhead in triggers, including declaring unnecessary variables. Thus, maybe something like this:
CREATE TRIGGER tr_ORS_CostDrvAfterInsert
ON dbo._tmp1
AFTER INSERT
AS
SET NOCOUNT ON;
declare
@responseText varchar(8000);
exec [dbo].[sp_ORS_MS_Request]
'http://MyURI.php',
'GET',
'',
'Method',
'',
'',
@responseText output;
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 6, 2013 at 2:50 pm
also, sp_oa methods can only return 4000 nchars at a time, so your method needs to loop until the responses are complete, and return a nvarchar(max); you probably alreayd have that in your sp_ORS_MS_Request, but if it wasn't consdiered you might get a truncated response.
Lowell
August 7, 2013 at 12:18 am
ok tnx for your answers. I am thinking of using CLR stored procedure. Does anyone have any good example how to do it? tnx in advance.
August 7, 2013 at 2:13 pm
Review this search result from Google for samples on how to write a CLR procedure.
You are not going to call the procedure from your trigger, I hope?
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 8, 2013 at 5:51 pm
We have a piece coming next week on CLR procedures and code.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy