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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy