June 3, 2009 at 6:14 am
Hi,
I have to create a job in which I have to transfer the data of table to .txt file everyday.now to do this I am creating partition on my table.and have created another table of similar schema.so what happens-
1-Data transfer from main table (table A) to table having similar schema (Table B).
2-Data from Table B is transfered to .txt file using BCP command.
3- Truncate Table B
Now the problem is when I keep this code in BEGIN TRANSACTION and COMMIT TRANSACTION,system gets hanged but when I keep it out of the Transaction ,it works properly.To keep this code running in transaction what should I do..code is-
ALTER TABLE [dbo].TableA
SWITCH PARTITION 1 TO dbo.TableB
--copy data from TableB table to BCP files
EXEC master.dbo.xp_cmdShell 'bcp "SELECT * FROM LoggingStage.dbo.TableB" queryout "E:\abc.txt"'
-- remove data from scratch table
TRUNCATE TABLE dbo.LogSourceArchiveScratch
June 3, 2009 at 6:31 am
How long are these process individually taking(Step A,B,C)? Does ur Begin Tran and Commit TRAN includes all three steps?
June 3, 2009 at 11:26 pm
Hi,
It takes hardly 1 milli second without transaction....but with trasaction database gets hanged up
June 4, 2009 at 1:34 am
It's hanging because your BCP job is running in a different process and creating its own connection to SQL Server.
You are therefore blocking yourself... run sp_who2 and you should see 2 spids, one for your main procedure and another for your BCP, which you should see being blocked by the main procedure.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply