Never use select into in transactions......

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Prakash Heda » Never use select into in transactions......

Never use select into in transactions......

Rate Topic

Display Mode

Topic Options

Author

Message

Prakash Heda

Posted Friday, October 12, 2007 10:48 AM

Old Hand

Group: General Forum Members
Last Login: Sunday, February 17, 2013 2:38 AM
Points: 369, Visits: 392

Comments posted to this topic are about the item Never use select into in transactions......

Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA

Post #410134

Hans Lindgren

Posted Monday, February 11, 2008 3:30 AM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, May 16, 2013 7:34 AM
Points: 1,196, Visits: 290

This is no longer true in SQL Server 2005...

Regards,
Hans Lindgren

Post #453766

simonxy

Posted Tuesday, February 14, 2012 6:02 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, April 11, 2013 7:39 AM
Points: 1, Visits: 91

I have sql2008 r2 with READ_COMMITTED_SNAPSHOT ON.
So, default database isolation level is read commited with row versioning.

If i try this:

--first connection
BEGIN TRAN
SELECT 1 AS test INTO dbo.test

--second connection
SELECT * FROM SYSOBJECTS
OR
SELECT * FROM SYSOBJECTS WITH (NOLOCK) WHERE NAME LIKE '%test%'
This wont work.

So, the sys.objects is not available even if I have snapshot isolation with row versioning.
Is there any solution? I would like to read from sys.objects and test table should not be visible there until commit of first connection.

lp,S

Post #1251855

« Prev Topic | Next Topic »

Permissions