bcp in a trigger for delete

Question

bcp in a trigger for delete

Making Future

Mr or Mrs. 500

Points: 517
More actions
June 11, 2003 at 2:43 pm

#101517

Hi,
I am trying to send a copy of deleted record
into *.txt file. My questions are:
1. Is it possible to use bcp in a trigger?
2. Is the following code correct?
DECLARE @FileName varchar(30)
DECLARE @Selquery varchar(200)
DECLARE @bcpCmand varchar(200)
SET @FileName = 'E:\DeletedData\ABC01.txt'
SET @Selquery = 'SELECT * FROM myDB.dbo.DELETED'
SET @bcpCmand = 'bcp "' + @Selquery + ' " queryout '
SET @bcpCmand = @bcpCmand + @FileName + ' -c -SmySQLServer -T'
IF (SELECT COUNT(*) FROM DELETED) > 0
BEGIN
EXEC master.dbo.xp_cmdshell @bcpCmand
END
3. How can I append records to existing *.txt file?
Appreciate for any ideas or suggestions.

Viewing 5 posts - 1 through 4 (of 4 total)

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

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 1

Your proposed solution will not work, because the DELETED table is only a psuedo table available to the trigger. Your BCP will start using another thread and will not have access to the DELETED table.

You could copy the contents of the DELETED table to another permanent table, and then run your BCP against the permanent table.

Making Future Mr or Mrs. 500 Points: 517 More actions · Answer 2

You are right. In addition, using bcp to copy data from a local tempporary

table isn't possible, but global temporary tables do the work.

However, I run into another issue. The following is my code, which causes

Query Analyzer frozen.

DECLARE @FileName varchar(30),

@MyQuery varchar(200),

@bcpCmand varchar(200)

SELECT *

INTO ##tempHolding

FROM myDB.dbo.ABC01

SET @FileName = 'E:\DeletedData\ABC01.txt'

SET @MyQuery = 'SELECT * FROM myDB..##tempHolding WITH (NOLOCK)'

SET @bcpCmand = 'bcp "' + @MyQuery + '"queryout '

SET @bcpCmand = @bcpCmand + @FileName + ' -c -SSTATEPIS05 -T'

EXEC master..xp_cmdshell @bcpCmand

Any ideas?

Thanks

Edited by - making Future on 06/13/2003 07:48:01 AM

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 3

Try running the BCP command that your code generates in a Command Prompt.

Making Future Mr or Mrs. 500 Points: 517 More actions · Answer 4

Thanks for your reply.

It's okay for me to run the bcp command in a Command Prompt.

Although I changed the code as follows, the trigger seems hang in there forever.

DECLARE @FileName varchar(30)

DECLARE @bcpCmd varchar(200)

SELECT *

INTO ##tempTable

FROM ABC01

SET @FileName = 'E:\DeletedData\ABC01.txt'

SET @bcpCmd = 'bcp ##tempTable out '

SET @bcpCmd = @bcpCmand + @FileName + ' -c -T -S myServer -o E:\DeletedData\ABC01.txt'

EXEC master..xp_cmdshell @bcpCmd, no_output

DROP TABLE ##tempTable

I don't know what's wrong.