April 21, 2014 at 9:32 pm
I want to capture and manipulate any SCRIPT which runs "DROP PROC" in my SQL server .
To explain further , How I can capture the PROC in drop statement and pass it as parameter in xp_cmdshell command .
A raw snippet : -
Declare @proc varchar(100)
Declare @sqlcmd varchar(max)
DROP PROC xyz
{
... capture the PROC xyz using DDL trigger
@proc = SET the captured proc from DDL trigger
}
SET @sqlcmd = @proc + ' <something other parms>'
EXEC xp_cmdshell '@sqlcmd'
Can anyone please propose some solution for the part { ....} above to implement .
April 21, 2014 at 10:08 pm
RantaSanta (4/21/2014)
I want to capture and manipulate any SCRIPT which runs "DROP PROC" in my SQL server .To explain further , How I can capture the PROC in drop statement and pass it as parameter in xp_cmdshell command .
A raw snippet : -
Declare @proc varchar(100)
Declare @sqlcmd varchar(max)
DROP PROC xyz
{
... capture the PROC xyz using DDL trigger
@proc = SET the captured proc from DDL trigger
}
SET @sqlcmd = @proc + ' <something other parms>'
EXEC xp_cmdshell '@sqlcmd'
Can anyone please propose some solution for the part { ....} above to implement .
You can write a DDL trigger to capture drop proc.
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
April 22, 2014 at 4:13 am
Here's the Books Online entry on DDL Triggers.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply