April 30, 2010 at 3:36 pm
I built a dynamic SQL statement (SS 2005) that has a cursor in it. Then I executed that dynamic SQL statement within another cursor. It would just quit after one step even though @@fetch_status was just fine, i.e. 0.
Can you not do this? Am I trying to do something "illegal" here?
This is driving me nuts...
April 30, 2010 at 3:46 pm
First, without seeing the code, you aren't going to get very much help.
Second, why do you think you need a cursor, let alone a dynamicly built cursor? Please tell us what you are trying to accomplish. Providing CREATE TABLE statements, sample data as INSERT INTO statements, expected results would also be very helpful.
April 30, 2010 at 6:08 pm
Whisper9999 (4/30/2010)
I built a dynamic SQL statement (SS 2005) that has a cursor in it. Then I executed that dynamic SQL statement within another cursor. It would just quit after one step even though @@fetch_status was just fine, i.e. 0.Can you not do this? Am I trying to do something "illegal" here?
This is driving me nuts...
Please read and heed the article at the first link in my signature line below. People will trip over each other trying to help you if you follow the suggestions there...
Otherwise, all I can say is, yes, you're probably doing something "illegal" and it's very likely that you're doing something very wrong because you're not using just one cursor (which is usually bad enough) but two. That usually constitutes some RBAR on sterioids.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 3, 2010 at 12:27 am
Compare your code to this simple example:
SET NOCOUNT ON;
CREATE TABLE
#Example
(A CHAR(1) PRIMARY KEY);
INSERT #Example (A) VALUES ('A');
INSERT #Example (A) VALUES ('B');
INSERT #Example (A) VALUES ('C');
DECLARE @sql NVARCHAR(MAX);
SET @sql =
'
DECLARE @a CHAR(1);
DECLARE curA CURSOR LOCAL DYNAMIC FOR SELECT A FROM #Example WHERE A <= @Param;
OPEN curA;
WHILE (1 = 1)
BEGIN
FETCH curA INTO @a;
IF @@FETCH_STATUS = -2 CONTINUE;
IF @@FETCH_STATUS = -1 BREAK;
PRINT ''--->'' + @a
END;
CLOSE curA; DEALLOCATE curA;
';
DECLARE @a CHAR(1);
DECLARE curA CURSOR LOCAL DYNAMIC
FOR SELECT A FROM #Example;
OPEN curA;
WHILE (1 = 1)
BEGIN
FETCH curA INTO @a;
IF @@FETCH_STATUS = -2 CONTINUE;
IF @@FETCH_STATUS = -1 BREAK;
PRINT @a
EXECUTE sp_executesql
@sql,
N'@Param CHAR(1)',
@Param = @a;
END;
CLOSE curA; DEALLOCATE curA;
GO
DROP TABLE
#Example;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 3, 2010 at 12:05 pm
Below is what I was running. I think it should work because when I replace the "exec(@sql)" with print @sql, it runs through all databases and prints out sql statements that are legit and that run successfully if I copy and paste them into a Mgt Studio window.
declare @dbname varchar(100)
declare @dbname_old varchar(100)
declare @sql varchar(4000)
set @dbname_old = ''
declare file_cursor cursor for
select name from master..sysdatabases
where name not in ('master','model','tempdb')
and name not like 'user%'
open file_cursor
while @@fetch_status = 0
begin
fetch next from file_cursor into @dbname
if @dbname<>@dbname_old
begin
print 'top'
print 'fetch status:'
print @@fetch_status
print 'dbname old'
print @dbname_old
print 'dbname'
print @dbname
select @dbname_old=@dbname
set @sql = 'use ' + @dbname + '; ' +
'select @@servername ServerName, db_name() DbName, b.[name] TableName, a.[rows] [RowCount] into #temcm_rowcount2 from sysindexes a, sysobjects b where a.id = b.id and indid in(''0'',''1'') ; ' +
'DECLARE @TableName VARCHAR ( 100 ); ' +
'DECLARE tableCursor CURSOR ' +
'FOR ' +
'select [name] ' +
'from dbo.sysobjects ' +
'where OBJECTPROPERTY ( id , N''IsUserTable'' ) = 1 ' +
'FOR READ ONLY; ' +
'CREATE TABLE #TempTable (' +
'tableName varchar ( 100 ), ' +
'numberofRows varchar ( 100 ), ' +
'reservedSize varchar ( 50 ), ' +
'dataSize varchar ( 50 ), ' +
'indexSize varchar ( 50 ), ' +
'unusedSize varchar ( 50 ) ); ' +
'OPEN tableCursor; ' +
'FETCH NEXT FROM tableCursor INTO @TableName ' +
'WHILE (@@Fetch_Status > = 0 ) ' +
'BEGIN ' +
'INSERT #TempTable ' +
'EXEC sp_spaceused @TableName ' +
'FETCH NEXT FROM tableCursor INTO @TableName ' +
'END; ' +
'CLOSE tableCursor; ' +
'DEALLOCATE tableCursor; ' +
'update #temptable ' +
'set reservedSize = REPLACE(reservedSize, '' KB'', ''''), ' +
'dataSize = REPLACE(dataSize, '' KB'', ''''), ' +
'indexSize = REPLACE(indexSize, '' KB'', ''''), ' +
'unusedSize = REPLACE(unusedSize, '' KB'', ''''); ' +
'insert into msdb.dbo.tbl_DBA_RowCount2 ' +
'(a.ServerName, a.DbName, a.TableName, a.[RowCount], ' +
'b.reservedSize, b.dataSize, b.indexSize, b.unusedSize) ' +
'select a.ServerName, a.DbName, a.TableName, a.[RowCount], ' +
'b.reservedSize, b.dataSize, b.indexSize, b.unusedSize ' +
'from #temcm_rowcount2 a left join #TempTable b on ( ' +
'a.TableName = b.TableName); ' +
'select * from #TempTable ' +
'DROP TABLE #TempTable; ' +
'DROP TABLE #temcm_rowcount2; '
print 'bttm'
print 'fetch status:'
print @@fetch_status
print 'dbname old'
print @dbname_old
print 'dbname'
print @dbname
exec (@sql)
end
end
close file_cursor
deallocate file_cursor
May 3, 2010 at 12:08 pm
Thx. I tried using "LOCAL DYNAMIC" and that didn't fix it, but I am going to try some of the other things you did differently as well.
May 3, 2010 at 12:12 pm
Try sp_msforeachtable and sp_msforeachdb
http://www.sqlservercurry.com/2009/04/8-common-uses-of-undocumented-stored.html
May 3, 2010 at 12:12 pm
What are you trying to accomplish and wht is the desired results?
May 3, 2010 at 12:24 pm
Thx to Paul White. This worked (once I put in the cursor commands from his query):
declare @dbname varchar(100)
declare @dbname_old varchar(100)
declare @sql varchar(4000)
set @dbname_old = ''
declare file_cursor cursor LOCAL DYNAMIC for
select name from master..sysdatabases
where name not in ('master','model','tempdb')
and name not like 'user%'
open file_cursor
while (1=1)
begin
fetch file_cursor into @dbname
--if @dbname<>@dbname_old
begin
print 'top'
print 'fetch status:'
print @@fetch_status
print 'dbname old'
print @dbname_old
print 'dbname'
print @dbname
if @@FETCH_STATUS = -2 CONTINUE
if @@FETCH_STATUS = -1 BREAK
select @dbname_old=@dbname
set @sql = 'use ' + @dbname + '; ' +
'select @@servername ServerName, db_name() DbName, b.[name] TableName, a.[rows] [RowCount] into #temcm_rowcount2 from sysindexes a, sysobjects b where a.id = b.id and indid in(''0'',''1'') ; ' +
'DECLARE @TableName VARCHAR ( 100 ); ' +
'DECLARE tableCursor CURSOR LOCAL DYNAMIC ' +
'FOR ' +
'select [name] ' +
'from dbo.sysobjects ' +
'where OBJECTPROPERTY ( id , N''IsUserTable'' ) = 1 ' +
'FOR READ ONLY; ' +
'CREATE TABLE #TempTable (' +
'tableName varchar ( 100 ), ' +
'numberofRows varchar ( 100 ), ' +
'reservedSize varchar ( 50 ), ' +
'dataSize varchar ( 50 ), ' +
'indexSize varchar ( 50 ), ' +
'unusedSize varchar ( 50 ) ); ' +
'OPEN tableCursor; ' +
'FETCH NEXT FROM tableCursor INTO @TableName ' +
'WHILE (@@Fetch_Status > = 0 ) ' +
'BEGIN ' +
'INSERT #TempTable ' +
'EXEC sp_spaceused @TableName ' +
'FETCH NEXT FROM tableCursor INTO @TableName ' +
'END; ' +
'CLOSE tableCursor; ' +
'DEALLOCATE tableCursor; ' +
'update #temptable ' +
'set reservedSize = REPLACE(reservedSize, '' KB'', ''''), ' +
'dataSize = REPLACE(dataSize, '' KB'', ''''), ' +
'indexSize = REPLACE(indexSize, '' KB'', ''''), ' +
'unusedSize = REPLACE(unusedSize, '' KB'', ''''); ' +
'insert into msdb.dbo.tbl_DBA_RowCount2 ' +
'(a.ServerName, a.DbName, a.TableName, a.[RowCount], ' +
'b.reservedSize, b.dataSize, b.indexSize, b.unusedSize) ' +
'select a.ServerName, a.DbName, a.TableName, a.[RowCount], ' +
'b.reservedSize, b.dataSize, b.indexSize, b.unusedSize ' +
'from #temcm_rowcount2 a left join #TempTable b on ( ' +
'a.TableName = b.TableName); ' +
'select * from #TempTable ' +
'DROP TABLE #TempTable; ' +
'DROP TABLE #temcm_rowcount2; '
print 'bttm'
print 'fetch status:'
print @@fetch_status
print 'dbname old'
print @dbname_old
print 'dbname'
print @dbname
exec (@sql)
--print @sql
end
end
close file_cursor
deallocate file_cursor
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply