Post reply

bcp in a trigger for delete

  • 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.

  • June 12, 2003 at 1:34 am

    #460975

    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.

  • June 12, 2003 at 11:02 am

    #461108

    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

  • June 13, 2003 at 1:34 am

    #461215

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

  • June 13, 2003 at 1:18 pm

    #461377

    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.

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

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