May 7, 2007 at 1:33 pm
I have a simple query running that does select against a table, it finished running fine, but don't disconnect that window in the managment view. Now I open BCP to insert new rows. Problem: SQL Server thinkgs the statement that finished running is blocking BCP. Did you guys comes across this before? This has happened to us for the first time, actually a user pointed it out on 9.0.2050 build of SQL Server.
May 8, 2007 at 5:05 am
1) are you absolutely certain that it is the select statement that is blocking? use sp_who2 active to determine the blocking spid and dbcc inputbuffer to see the statement of the blocker.
2) try using (NOLOCK) hint on the select and then see if you get the same result.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 8, 2007 at 6:17 am
I guess the point being is that the select query finished running, but the blocking still occured. I know that nolock will avoid the problem, but some other users who write their own queries will never use lock hints.
SP_WHO2 is how i determined blocking and it was process that has FINISHED running....so how come it was blocking an insert?
May 8, 2007 at 7:04 am
I guess you turned SET IMPLICIT_TRANSACTIONS to ON in the query window (connection). When it's ON, whenever you start a SELECT, INSERT, UPDATE, DELETE .. etc statements, SQL server starts a transaction automatically but you need to COMMIT it by your code.
Add the statement SELECT @@TRANCOUNT after your select statement. If it prints >0, you either need to COMMIT it or set the IMPLICIT_TRANSACTIONS of the connection to OFF. (check the option)
May 8, 2007 at 8:34 am
This happened on the end user's workstation, so I have no idea of what they typed in. However I don't think it was the case. They are not that familiar with SQL to know these settings.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply