Problem with BCP in trigger, how to do it?

Hello all,

I′m pretty new to T-SQL, so please bear with me.

I′m trying to output the temp "inserted" table avalible in the trigger to a text file.

When this trigger executes, the server seems to enter a never ending query.

If i comment the last three lines (declare... select... exec...) the trigger works fine,

so it seems to be a problem with the BCP part of the trigger.

What is wrong here? If you have better suggestions on how to

accoplish the same thing, please feel free to share!

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER TRIGGER [Skapa_Transfil]

ON [dbo].[Products]

AFTER UPDATE

AS

BEGIN

SET NOCOUNT ON;

insert into dbo.TempProducts (ProdNo, CountryOfOrigin)

select prodno, CountryOfOrigin

from inserted

declare @.sql varchar(8000)

select @.sql = 'bcp avk..tempProducts out c:\fil.txt -c -t, -U sa -P dalla -S'

exec master..xp_cmdshell @.sql

END

I've managed to replicate this behaviour. The cause seems to be that the TRIGGER is taking out an exclusive lock on the rows that are being inserted into TempProducts and therefore the BCP statement is unable to obtain a shared and so cannot read the data. I can't see a way around this unfortunately.

Would you be able to handle this logic in a stored procedure,similar to the following:

Code Snippet

create procedure insertandexport
@.int1 int, @.int2 int
as
insert into tempproducts
values (@.int1, @.int2)

if @.@.rowcount > 0
begin
declare @.sql varchar(8000)

select @.sql = 'bcp tempdb..TempProducts out c:\fil.txt -c -t, -U"User" -P"password" -S"YouServer"'

exec master..xp_cmdshell @.sql
end

HTH!|||

The trigger runs inside a transaction, so the "insert into" statement is also inside that transaction and could be blocking the table or index. The execution of bcp is out of that transaction and has to wait till the blocking has gone if it is running in "read committed" isolation level.

AMB

|||

Since it would be acceptable with some delay of the updates from tempProducts to fil.txt, would it be a good idea to do something like this?

The trigger keeps updating the tempProducts table whenever the trigger fires, like so:

Code Snippet

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER TRIGGER [Skapa_Transfil]

ON [dbo].[Products]

AFTER UPDATE

AS

BEGIN

SET NOCOUNT ON;

insert into dbo.TempProducts (ProdNo, CountryOfOrigin)

select prodno, CountryOfOrigin

from inserted

END

Then I schedule the following sql script to run using "sqlcmd -i exportfromtempproducts" once a minute or so.

Code Snippet

begin transaction

declare @.sql varchar(8000)

select @.sql = 'bcp avk..tempProducts out c:\fil.txt -c -t, -U sa -P dalla -S'

exec master..xp_cmdshell @.sql

go

use avk

go

delete

from tempProducts

go

commit transaction

I tried this and to me it seems to work. Since I run the bcp and delete in one transaction, the trigger would never be able to insert data into tempProducts between the bcp and the delete?

Thoughts someone?

|||

I think the key to this is setting your TRANSATION ISOLATION LEVEL to SNAPSHOT. This will guarentee that the you will only be working with the rows as they were at the start of the transaction. Otherwise, no exclusive locks will be put on the tempProducts table and so you could get rows inserted after the bcp statement which would then be deleted.

Simulate this behaviour by executing the stages in 2 separate query windows, step by step (ie being tran, run the bcp statement only, update more rows in products in the other window, come back and then run the delete statement etc).

Check Books Online for a more thorough explanation of isolation levels.

As an aside, will you be overwriting fil.txt every minute? Does that matter?

Let us know how you get on!

|||

I made some changes to the query in order to get different file names for each execution,

naming the file with date and time.

How would you go about to execute the query in "separate stages" as you mention above?

I′m used to working with break-points from VB, but I can′t seem to find any similar feature for T-SQL

EDIT: For clarification, I also did the

ALTER DATABASE AVK

SET ALLOW_SNAPSHOT_ISOLATION

Code Snippet

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

BEGIN TRANSACTION

DECLARE @.date char(8)

DECLARE @.time char(8)

DECLARE @.sql VARCHAR(8000)

SELECT @.date = CONVERT(char(8), getdate(),112)

SELECT @.time = CONVERT(char(8), getdate(),108)

SELECT @.time = REPLACE(@.time,':','')

SELECT @.time

DECLARE @.dt char(14)

SELECT @.dt = @.date + '_' + @.time

SELECT @.sql = 'bcp avk..tempProducts out "c:\AVK_' + @.dt + '.txt" -c -t, -U sa -P dalla -S'

EXEC master..xp_cmdshell @.sql

GO

USE AVK

GO

DELETE

FROM tempProducts

GO

COMMIT TRANSACTION

|||

For testing purposes, i did the following:

Paste the whole block into a query window in SSMS and then just highlght and execute the individual sections:

ie hightlight this and execute

Code Snippet

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

BEGIN TRANSACTION

DECLARE @.date char(8)

DECLARE @.time char(8)

DECLARE @.sql VARCHAR(8000)

SELECT @.date = CONVERT(char(8), getdate(),112)

SELECT @.time = CONVERT(char(8), getdate(),108)

SELECT @.time = REPLACE(@.time,':','')

SELECT @.time

DECLARE @.dt char(14)

SELECT @.dt = @.date + '_' + @.time

SELECT @.sql = 'bcp avk..tempProducts out "c:\AVK_' + @.dt + '.txt" -c -t, -U sa -P dalla -S'

EXEC master..xp_cmdshell @.sql

GO

Then go into your another query window (which is a separate transaction) and execute an update statement on products

Then return to the main window, highlight and execute the final section

Code Snippet

USE AVK

GO

DELETE

FROM tempProducts

GO

COMMIT TRANSACTION

This will give you the behaviour as if an update to your products tabla occured while your second transaction was running and will allow you to view how the different isolation levels affect your results.

As for debugging a la VB, i think you are able to use this facility for stored procedures in Visual Studio but not SSMS.

HTH!

|||

WOHO!

Works like a charm!

Tried updating 3 records, then running the BCP-part of the query. 3 rows out as expected.

The updated 3 more records, did a select * on tempProducts, which now contained 6 records.

Finally ran the delete part of the query, checked tempProducts again. And yep, my last three updates where still there!

Thanks a bunch for all the help!