RECONFIGURE inside trigger

Question

Post reply

RECONFIGURE inside trigger

bozzgo13

SSC Enthusiast

Points: 120
More actions
August 5, 2013 at 9:44 am

#280868

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 varchar(2000) ,
@methodName varchar(50),
@requestBody varchar(8000) = '',
@SoapAction varchar(255),
@UserName nvarchar(100),
@Password nvarchar(100),
@responseText varchar(8000);
set @uri = '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]
@uri,
@methodName,
@requestBody,
@SoapAction,
@UserName,
@Password,
@responseText output;
EXEC sp_configure 'Ole Automation Procedures', 0
RECONFIGURE
EXEC sp_configure 'show advanced options', 0
RECONFIGURE
END

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

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

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 1

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]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ScottPletcher SSC Guru Points: 100948 More actions · Answer 3

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) A socialist is someone who will give you the shirt off *someone else's* back.

Lowell SSC Guru Points: 323504 More actions · Answer 4

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

bozzgo13 SSC Enthusiast Points: 120 More actions · Answer 5

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.

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 6

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]

Steve Jones - SSC Editor SSC Guru Points: 734449 More actions · Answer 7

We have a piece coming next week on CLR procedures and code.