June 24, 2013 at 2:42 am
while in transaction I am creating a global temporary table and then Insert some data into it
later in the transaction i am using a xp_cmdshell to bcp out the table
I am getting a lock
dbcc inputbuffer : set fmtonly on select * from mytable set fmtonly off
I tried to bcp out the table using a query and I got a lock also
I resolved the problem by using a regular table .
why this happend ?
June 24, 2013 at 5:10 am
Using xp_cmdshell is like having another process accessing your table, which is locked by your open transaction.
That shouldn't work with a regular table neither, unless you didn't use an explicit begin transaction.
June 24, 2013 at 5:13 am
When you used a normal table, was this also created within your transaction? Or did it already exist and you simply populated it in your transaction and then performed a dirty read against it from the xp_cmdshell/bcp command.
Your issue will be due to the locks placed on the system tables when you create the table within the transaction.
Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply