March 21, 2010 at 10:50 pm
I have a script that utilizes a cursor to move heaps(tables without clustered indexes) from the PRIMARY filegroup to a secondary filegroup. In the cursor I execute 3 different sql statements (ALTER TABLE statements) via sp_executesql. I would like the cursor to wait for completion of the sp_executesql statements before fetching the next row and starting the next set of moves. Is there a way to do this? See code below.
-- Make sure to set @debug in code below.
-- @debug = 1 for test mode - prints statements.
-- @debug = 0 for run mode - executes statements.
IF OBJECT_ID('tempdb.dbo.#MyCursorSource') IS NOT NULL
DROP TABLE dbo.#MyCursorSource ;
GO
IF OBJECT_ID('tempdb.dbo.##MyCursorStatus') IS NOT NULL
DROP TABLE ##MyCursorStatus ;
CREATE TABLE ##MyCursorStatus
(
SQLRUN VARCHAR(MAX) ,
STAT VARCHAR(MAX) ,
ErrorNum VARCHAR(10) ,
ErrorMess VARCHAR(MAX) ,
ErrorProc VARCHAR(MAX)
) ;
GO
SELECT i.object_id ,
o.name AS "TableName" ,
i.index_id ,
f.name AS "FGName" ,
i.type_desc ,
SQL1 = CASE WHEN i.type_desc = 'HEAP'
THEN 'ALTER TABLE ' + o.name
+ ' ADD MyTemp_ID INT IDENTITY CONSTRAINT PK_MyTemp_ID PRIMARY KEY CLUSTERED (MyTemp_ID) ON DATA;'
END ,
SQL2 = CASE WHEN i.type_desc = 'HEAP'
THEN 'ALTER TABLE ' + o.name
+ ' DROP CONSTRAINT PK_MyTemp_ID;'
END ,
SQL3 = CASE WHEN i.type_desc = 'HEAP'
THEN 'ALTER TABLE ' + o.name + ' DROP COLUMN MyTemp_ID;'
END
INTO #MyCursorSource
FROM sys.indexes i WITH ( NOLOCK )
INNER JOIN sys.filegroups f WITH ( NOLOCK ) ON i.data_space_id = f.data_space_id
INNER JOIN sys.objects o WITH ( NOLOCK ) ON i.object_id = o.object_id
WHERE i.type_desc = 'HEAP'
AND o.type = 'U'
AND f.name = 'PRIMARY' ;
DECLARE @tablename AS VARCHAR(255) ,
@sql1 AS NVARCHAR(MAX) ,
@sql2 AS NVARCHAR(MAX) ,
@sql3 AS NVARCHAR(MAX) ,
@debug AS INT ;
-- If @debug is set to 1 then only prints sql statements. If set to 0 it executes statements.
SET @debug = 1 ;
DECLARE C CURSOR FAST_FORWARD FOR
SELECT TableName, SQL1, SQL2, SQL3 FROM #MyCursorSource ORDER BY TableName ;
OPEN C
FETCH NEXT FROM C INTO @tablename, @sql1, @sql2, @sql3 ;
WHILE @@FETCH_STATUS = 0
BEGIN
IF @debug = 1
BEGIN
PRINT @sql1 ;
PRINT @sql2 ;
PRINT @sql3 ;
END
IF @debug = 0
BEGIN
BEGIN TRY
INSERT INTO ##MyCursorStatus
( SQLRUN ,
STAT
)
VALUES ( @SQL1 ,
'Running at ' + CAST(GETDATE() AS VARCHAR(30))
) ;
EXEC sp_executesql @sql1 ;
INSERT INTO ##MyCursorStatus
( SQLRUN ,
STAT
)
VALUES ( @SQL1 ,
'Successful at '
+ CAST(GETDATE() AS VARCHAR(30))
) ;
INSERT INTO ##MyCursorStatus
( SQLRUN ,
STAT
)
VALUES ( @SQL2 ,
'Running at ' + CAST(GETDATE() AS VARCHAR(30))
) ;
EXEC sp_executesql @sql2 ;
INSERT INTO ##MyCursorStatus
( SQLRUN ,
STAT
)
VALUES ( @SQL2 ,
'Successful at '
+ CAST(GETDATE() AS VARCHAR(30))
) ;
INSERT INTO ##MyCursorStatus
( SQLRUN ,
STAT
)
VALUES ( @SQL3 ,
'Running at ' + CAST(GETDATE() AS VARCHAR(30))
) ;
EXEC sp_executesql @sql3 ;
INSERT INTO ##MyCursorStatus
( SQLRUN ,
STAT
)
VALUES ( @SQL3 ,
'Successful at '
+ CAST(GETDATE() AS VARCHAR(30))
) ;
END TRY
BEGIN CATCH
INSERT INTO ##MyCursorStatus
( STAT ,
ErrorNum ,
ErrorMess ,
ErrorProc
)
VALUES ( 'ERROR OCCURRED' , -- STAT - varchar(max)
CAST(ERROR_NUMBER() AS VARCHAR(10)) , -- ErrorNum - varchar(10)
ERROR_MESSAGE() , -- ErrorMess - varchar(max)
ISNULL(ERROR_PROCEDURE(), 'Not Within Procedure') -- ErrorProc - varchar(max)
)
PRINT 'Error Number: '
+ CAST(ERROR_NUMBER() AS VARCHAR(10)) ;
PRINT 'Error Message: ' + ERROR_MESSAGE() ;
PRINT 'Error Proc: ' + ISNULL(ERROR_PROCEDURE(),
'Not Within Proc') ;
RETURN;
END CATCH
END
FETCH NEXT FROM C INTO @tablename, @sql1, @sql2, @sql3 ;
END
CLOSE C ;
DEALLOCATE C ;
DROP TABLE #MyCursorSource ;
DROP TABLE ##MyCursorStatus ;
March 22, 2010 at 2:18 am
sp_executesql will not run the command asynchronously. It will wait for completion before returning.
Why do you believe that that is not the case ?
March 22, 2010 at 7:06 am
I agree that it executes them synchronously, but the cursor will run through all of the rows rather than pausing between rows for completion which makes monitoring the cursor a bit more difficult. The main reason for the question was to find a way to monitor progress in real time.
March 23, 2010 at 4:28 am
SouthernConfusion (3/22/2010)
I agree that it executes them synchronously, but the cursor will run through all of the rows rather than pausing between rows for completion...
Executing synchronously means that each sp_executesql command has definitely completed by the time control returns to your code.
If you are looking to monitor progress, either embed RAISERROR('This is the current status', 0, 1) WITH NOWAIT statements within your code, or use one of the cursor-monitoring tools from a separate connection. Cursor monitoring tools include @@CURSOR_ROWS, sys.dm_exec_cursors, sp_cursor_list, and sp_describe_cursor.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply