Trigger and Queryout

Question

Post reply

Trigger and Queryout

lincolnzsilva

SSC Rookie

Points: 39
More actions
December 15, 2008 at 2:43 pm

#198998

Good afternoon to everyone. I'm new to SQL Server administration.
I need create a trigger to generate a file when a new record is inserted in one table.
The trigger I created:
DECLARE @ani varchar(27)
DECLARE @fate varchar(3)
DECLARE @callid varchar(32)
SET @ani = (SELECT ani FROM inserted)
SET @fate = (SELECT finalfate FROM inserted)
SET @callid = (select callid FROM inserted)
IF @fate = 'Q14'
BEGIN
exec xp_cmdshell 'bcp "SELECT ANI FROM [BIC].[dbo].[tbDump] where CALLID = ''@callid''" queryout "c:\dialer\banana.txt" -S localhost -T -c'
END
PRINT @callid
PRINT @ani
PRINT @fate
In the where clause, I already used ''@callid'' (double single quotes), \"@callid\" (backslash and double quotes), @callid (no quotes), and whatever else you can imagine. Whatever I try to use, the trigger do not return the expected record (the callid fild of the QDump is the PK, so I only will have one record returned).
What quotation do I need to use in the where clause?
I will need change the BCP out file name as well (for example to @phone.txt).
The "PRINTS" are just for troubleshoot.
Thanks in Advance
Lincoln

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

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

lincolnzsilva SSC Rookie Points: 39 More actions · Answer 1

Just one more information. If I try to use the @ani without any quotation, I get the following error:

Error = [Microsoft][SQL Native Client][SQL Server]Must declare the scalar variable "@ani".

Regards

Lincoln

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

If I may, I'd like to make a suggestion. Don't do it this way. If for some reason the call to xp_cmdshell fails, your insert into the table will be rolled back and the data lost.

I'd recommend looking at using SQL Server Service Broker to create an asyncronous process that creates the file you need created upon inserting into the table. This allows the insert to complete successfully, and you still create the file for each record.

A good place to start with Service Broker is Books Online. If you have any questions, there are several people on this site, unfortuately not me yet, that can assist you with further knowledge of how it works.

lincolnzsilva SSC Rookie Points: 39 More actions · Answer 3

Hi Lynn,

Thanks for your reply.

I'll search about the Service Broker.

You told that if the trigger fail, the insert will fail too?

If this is right, you can be sure that I will change it to Service Broker, but I'm trying to do this triger work since last friday....rsrsrs

Regards

Lincoln

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Your going to have to write dynamic sql in your trigger to get this to work the way you are going at the moment.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Give this a try, but I would seriously look at Service Broker.

DECLARE @ani varchar(27)

DECLARE @fate varchar(3)

DECLARE @callid varchar(32)

DECLARE @BCPCmd varchar(2000)

SET @ani = (SELECT ani FROM inserted)

SET @fate = (SELECT finalfate FROM inserted)

SET @callid = (select callid FROM inserted)

IF @fate = 'Q14'

BEGIN

set @BCPCmd = 'bcp "SELECT ANI FROM [BIC].[dbo].[tbDump] where CALLID = ''' + @callid + '''" queryout "c:\dialer\banana.txt" -S localhost -T -c'

exec xp_cmdshell @BCPCmd

END

PRINT @callid

PRINT @ani

PRINT @fate

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

And, yes, if the trigger fails, the insert that triggered the insert will also fail.