Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

RECONFIGURE inside trigger Expand / Collapse
Author
Message
Posted Monday, August 5, 2013 9:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 7, 2013 12:11 AM
Points: 2, Visits: 1
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

Post #1480972
Posted Monday, August 5, 2013 3:56 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Saturday, September 20, 2014 7:07 AM
Points: 803, Visits: 720
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?


Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1481113
Posted Tuesday, August 6, 2013 3:19 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:22 AM
Points: 39,888, Visits: 36,235
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 2008, MVP
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

Post #1481213
Posted Tuesday, August 6, 2013 2:38 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 5:04 PM
Points: 2,102, Visits: 3,164
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)

"We came in spastic, Like tameless horses /
We left in plastic, As numbered corpses / ...
Remember Charlie, Remember Baker /
They left their childhood On every acre /
And who was wrong? And who was right? /
It didn't matter in the thick of the fight." : the inimitable Mr. Billy Joel, about the Vietnam War
Post #1481562
Posted Tuesday, August 6, 2013 2:50 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:40 PM
Points: 12,897, Visits: 32,105
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1481569
Posted Wednesday, August 7, 2013 12:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 7, 2013 12:11 AM
Points: 2, Visits: 1
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.
Post #1481674
Posted Wednesday, August 7, 2013 2:13 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Saturday, September 20, 2014 7:07 AM
Points: 803, Visits: 720
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?


Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1482052
Posted Thursday, August 8, 2013 5:51 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 9:42 PM
Points: 31,036, Visits: 15,466
We have a piece coming next week on CLR procedures and code.






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1482617
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse