What are the challenges you face when working across database platforms? Take the survey
Options

Maximum Uncommitted Changes?

I have a scenario where I need to change (increase) the column size on two columns (ColA and ColB for discussion) on every table in a database. I have several databases to perform this task on. I wrote a script to perform this task dynamically based upon Information_Schema. I have clones of the target databases on my desktop workstation. I have one database that is not connected to SQL Source Control (DB_A, 294 tables), and another that is linked to SQL Source Control (DB_B, 403 tables). When I ran my script on DB_A, it ran perfectly, modifying the target fields in all 294 tables (588 changes). When I ran this same script on DB_B, it got 60 changes in and hung. After 30 minutes, I terminated the script. I checked the database and can see 30 tables marked as having pending changes. If I try to manually run the "alter table - alter column" statement on the next table in the sequence, the query hangs and the table is not modified.

I am wondering if SQL Source Control has a limit for Maximum Uncommitted Changes? Why would this work perfectly on a database that is not linked to SQL Source Control, and hang on an otherwise similar database that is linked to SQL Source Control?

My script is as follows:
set nocount on
declare @index int = 0<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ,@query varchar(max)
declare @tabCols table<br>(<br>&nbsp;[ID] int identity(1,1)<br>,[TABLE_CATALOG] varchar(128)<br>,[TABLE_SCHEMA] varchar(128)<br>,[TABLE_NAME] varchar(128)<br>,[COLUMN_NAME] varchar(128)<br>)
insert into @tabCols<br>(<br>&nbsp; [TABLE_CATALOG]<br>, [TABLE_SCHEMA]<br>, [TABLE_NAME]<br>, [COLUMN_NAME]<br>)<br>select [col].[TABLE_CATALOG]<br>&nbsp;&nbsp;&nbsp;&nbsp; , [col].[TABLE_SCHEMA]<br>&nbsp;&nbsp;&nbsp;&nbsp; , [col].[TABLE_NAME]<br>&nbsp;&nbsp;&nbsp;&nbsp; , [col].[COLUMN_NAME]<br>&nbsp; from [INFORMATION_SCHEMA].[COLUMNS] [col]<br>&nbsp; inner join [INFORMATION_SCHEMA].[TABLES] [tab]<br>&nbsp; on (([tab].[TABLE_CATALOG] = [col].[TABLE_CATALOG])<br>&nbsp; and ([tab].[TABLE_SCHEMA] = [col].[TABLE_SCHEMA])<br>&nbsp; and ([tab].[TABLE_NAME] = [col].[TABLE_NAME]))<br>&nbsp; where ([col].[COLUMN_NAME] in ('ColA','ColB'))<br>&nbsp;&nbsp;&nbsp; and ([col].[CHARACTER_MAXIMUM_LENGTH] < 128)<br>&nbsp;&nbsp;&nbsp; and ([tab].[TABLE_TYPE] = 'BASE TABLE')<br>&nbsp; order by [col].[TABLE_CATALOG]<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; , [TABLE_SCHEMA]<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; , [TABLE_NAME]<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; , [COLUMN_NAME]
select max([ID]) as [Change Count]<br>&nbsp; from @tabCols
while ((select min([ID])<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; from @tabCols<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; where ([ID] > @index)) is not null)<br>begin<br>&nbsp; select @index = min([ID])<br>&nbsp;&nbsp;&nbsp; from @tabCols<br>&nbsp;&nbsp;&nbsp; where ([ID] > @index)
&nbsp; select @query = 'alter table [' + [TABLE_CATALOG] + '].[' + [TABLE_SCHEMA] + '].[' + [TABLE_NAME] + '] alter column [' + [COLUMN_NAME] + '] varchar(128)'<br>&nbsp;&nbsp;&nbsp; from @tabCols<br>&nbsp;&nbsp;&nbsp; where ([ID] = @index)
&nbsp; print @query<br>&nbsp; exec(@query)<br>end
set nocount off

Sign In or Register to comment.