Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  General SQL Server Forums  Script Library  Never use select into in transactions

Author  Topic  heda_p Starting Member 5 Posts Posted - 2002-06-07 : 05:41:07 If select into clause is used into transactions till the transaction commits sysobjects table can not acceses directly. It can be accessed only with 'nolock' hint. /* To verify how blocking occurs I have create these three baches 1st batch is starting a transaction, creating a table, performing select into operation and waiting for 1 minute( this waiting time could be used for other selects or updates) 2nd batch is the query on sysobjects table 3rd batch is the query on sysobjects table with (nolock) option How to run: 1. Open three query analyzer windows. 2. Copy and paste each batch in seperate windows. 3. fire all the three baches simultanously started with 1st batch. Check sp_who for blocked processes */ --------------------------------------------BATCH 1 STARTED-------------------------------------------- BEGIN TRANSACTION IF OBJECT_ID('DIM_CUSTOMER_TMP1') IS NOT NULL DROP TABLE DIM_CUSTOMER_TMP1 CREATE TABLE DIM_CUSTOMER_TMP1 (TEST VARCHAR(200)) IF OBJECT_ID('TMP1') IS NOT NULL DROP TABLE TMP1 SELECT * INTO TMP1 FROM DIM_CUSTOMER_TMP1 WAITFOR DELAY '000:01:00' IF OBJECT_ID('TMP1') IS NOT NULL DROP TABLE TMP1 IF OBJECT_ID('DIM_CUSTOMER_TMP1') IS NOT NULL DROP TABLE DIM_CUSTOMER_TMP1 COMMIT TRANSACTION --------------------------------------------BATCH 1 FINISHED-------------------------------------------- --------------------------------------------BATCH 2 STARTED-------------------------------------------- SELECT NAME FROM SYSOBJECTS WHERE NAME LIKE '%SYSINDEXES%' --------------------------------------------BATCH 2 FINISHED-------------------------------------------- --------------------------------------------BATCH 3 STARTED-------------------------------------------- SELECT NAME FROM SYSOBJECTS WITH (NOLOCK) WHERE NAME LIKE '%SYSINDEXES%' --------------------------------------------BATCH 3 FINISHED-------------------------------------------- Cheers, Prakash