I have a problem where I am calling the BCP utility to write a table to a file. I then need to delete the rows of the table. but not all of them. This all works fine. I've been asked to place this into a transaction..incase a piece fails. When I do that...SQL server hangs. I must shutdown SQL Server.
Any idea why that would happen. I am using the xp_cmdshell stored procedure to invoke bcp utility within a stored procdure. The procedure is executed every 15 minutes to provide files to an outboard system.
My main issue is that I am writing out data based on a 'events' table which tracks the changes users make to a product table. from the time I have built the strings and inserted them into a temporary table and then output the file, then removed the 'events' - a user could change a field in a product I am writing out - which would be missed by the system. Not good.
The only thing I can think of is to lock the appropriate rows in the product table for the duration. WHich I'm not aware of the syntax.
Data is fluid ny bature...what's the difference if it's updated during your export, or immediatley after...the data will still be changed...
And is your issue a matter of concurrency? Why build a temp table?
Also why not schedule a batch window? Grab the date from the system....use that as the window close date, grab all rows added or updated between the last time the window close and this windoes close...
So if they update it during your export it wouldn't be part of this batch...
How long is your transaction...and how many rows on average do you export?
The output data is '*' delimited for another system and requires padding of numbers, calculation etc.. that's why I build the strings into a temporary table and then ship the temp table out using bcp.
We use triggers to track the events a user makes to a product. on certain fields a product event is created. and on others a price event. The price events are held util evening processing as they can only be sent out once a day. the product files are output every 15 minutes. There are four different files output at the same time, ecah containing slightly different data.
The events table prevents duplicates ie: we don't append. if a existing price event exists we don't add another row.
when I have finished the bcp the appropriate rows are removed from the events table.
sooo - between the time the row for the product has been read and the time I delete the events row for that product. and event could happen...no duplicate would be added..and the new data not written out and the system thinks it has.
I could snatch the rows from the events table into a temptable. delete them and if an error occurs add them back in to the events table...