SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Accessing column created within a transaction
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kairan_r
Starting Member

India
1 Posts

Posted - 06/25/2013 :  01:39:38  Show Profile  Reply with Quote
I have a scenario where in I need to add a few columns to a table in one database under a SQL transaction. I also need to create some SPs in another database which refer to the column created in the table above. During the creation of the SP the transaction on the previous DB is still open.
E.g.
Script1
use [Test1]
go
begin tran tran2
go

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
ALTER TABLE dbo.User1 ADD
email varchar(100) NULL
GO

Script2 (Executed under different connection)
USE [Test]
GO

begin tran tran1
go
create procedure [dbo].[GetUser]
as
begin
declare @var varchar(100)

select @var = Test1.dbo.User1.email from [Test1].dbo.User1 where 1=1
print @var
end

go

First I run script 1 and then script 2. When the script2 runs it simply hangs. I guess since the transaction on the [Test1] database in still on.
I also tried to the ‘ISOLATION LEVEL READ UNCOMMITTED’. Didn’t work.
So is there a way to access columns which are created but not committed. Or any other solution.

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/25/2013 :  01:41:42  Show Profile  Reply with Quote
can i ask the need for this requirement? why creating column inside a transaction? why cant column be created outside and then data being added/modified inside transaction?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/25/2013 :  01:45:33  Show Profile  Reply with Quote
Also the way you've written transaction you're ending batch immediately after it. Is this intentional?

begin tran tran2
go


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/25/2013 :  01:58:22  Show Profile  Reply with Quote
Also one more thing, adding a column to table is a DDL change, so SQL Server will be placing a Schema modification lock on the object. And until it finishes with current operation (addition of column) you wont be able to access the table. Changing the isolation level will not have any effect on schema modification lock as its incompatible with any of the other locks. So all other transaction will be in waiting state until you close the current transaction (rollback/commit)

More details here

http://aboutsqlserver.com/2012/04/05/locking-in-microsoft-sql-server-part-13-schema-locks/

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000