March 29, 2013 at 10:56 am
I have a simple cursor and I need help fixing syntax.(I know that in this scenario I am using cursor properly).
The purpose of the cursor (copied below) is just to output the names of all databases on my server. Later I will use dynamic sql (not copied below) to display the respective metrics (user_scans, user_lookups) for each database from sys.dm_db_index_usage_stats.
For the cursor part I'm getting the following error:
Msg 16915, Level 16, State 1, Line 4
A cursor with the name 'getDatabaseName' already exists.
Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@DatabaseName".
Msg 137, Level 15, State 2, Line 6
Must declare the scalar variable "@DatabaseName".
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@DatabaseName".
my cursor script
use MiguelsApp
DECLARE @DatabaseName varchar(max)
DECLARE getDatabaseName CURSOR FOR
SELECT name, database_id, create_date
FROM sys.databases
where name not in ('master', 'tempdb', 'model', 'msdb');
GO
OPEN getDatabaseName
FETCH NEXT
FROM getDatabaseName INTO @DatabaseName
WHILE @@FETCH_STATUS = 0
BEGIN
print @DatabaseName
FETCH NEXT FROM getDatabaseName
INTO @DatabaseName
END
close getDatabaseName
deallocate getDatabaseName
Will someone please tell me where my syntax is wrong so it will run?
March 29, 2013 at 11:23 am
Remove the 'GO'. That breaks the script up into 2 separately executable pieces and you need this to be one execution stream.
Also, is there a specific reason to use a cursor? Most tasks can be accomplished using set-oriented sql with great gains in efficiency.
March 29, 2013 at 11:29 am
thanks for help. Yes, cursor will be find for this case. I removed 'GO' and now I get the following error.
Msg 16915, Level 16, State 1, Line 4
A cursor with the name 'getDatabaseName' already exists.
Msg 16924, Level 16, State 1, Line 8
Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.
running this
use MiguelsApp
DECLARE @DatabaseName varchar(max)
DECLARE getDatabaseName CURSOR FOR
SELECT name, database_id, create_date
FROM sys.databases
where name not in ('master', 'tempdb', 'model', 'msdb');
OPEN getDatabaseName
FETCH NEXT
FROM getDatabaseName INTO @DatabaseName
WHILE @@FETCH_STATUS = 0
BEGIN
print @DatabaseName
FETCH NEXT FROM getDatabaseName
INTO @DatabaseName
END
close getDatabaseName
deallocate getDatabaseName
do you see anything else wrong?
March 29, 2013 at 11:33 am
The cursor has been defined in one of the previous executions and never got closed and deallocated. Issue the close and deallocate commands by themselves or just get a new connection.
The number of columns in your select list in the cursor has to match the number of columns you are fetching. You are missing 2 columns in the fetch.
March 29, 2013 at 11:48 am
You were very helpful to me. I removed the two extra columns I don't need and ran cursor in a new window and now I have results. Thanks you.
what worked
use MiguelsApp
DECLARE @DatabaseName varchar(max)
DECLARE getDatabaseName CURSOR FOR
SELECT name
FROM sys.databases
where name not in ('master', 'tempdb', 'model', 'msdb');
OPEN getDatabaseName
FETCH NEXT
FROM getDatabaseName INTO @DatabaseName
WHILE @@FETCH_STATUS = 0
BEGIN
print @DatabaseName
FETCH NEXT FROM getDatabaseName
INTO @DatabaseName
END
close getDatabaseName
deallocate getDatabaseName
March 29, 2013 at 12:14 pm
just a thought
;with SD as (
SELECTname,
database_id,
create_date
FROM sys.databases
where name not in ('master','tempdb', 'model', 'msdb')
)
SELECTSD.name,
SD.create_date,
IUS.database_id,
IUS.index_id,
IUS.user_seeks,
IUS.user_scans,
IUS.user_lookups,
IUS.user_updates,
IUS.last_user_seek
FROM SD
LEFT OUTER JOIN sys.dm_db_index_usage_stats AS IUS
ON SD.database_id= IUS.database_id
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
April 1, 2013 at 5:32 pm
Yes, I agree. There's also an sp_msforeachdb function available....
But I needed to learn the cursor
April 1, 2013 at 11:18 pm
KoldCoffee (4/1/2013)
Yes, I agree. There's also an sp_msforeachdb function available....But I needed to learn the cursor
Actually, we can probably show you how to do this without using a cursor if you show us what you are trying to accomplish.
April 2, 2013 at 8:29 am
Yes, except that David Webb-200187, good man that he is, answered the question I had, which is wonderful. Sometimes you just need a relevant answer, yes?
Thank you David Webb-200187, for having soul.
April 14, 2013 at 10:01 pm
KoldCoffee (4/2/2013)
Yes, except that David Webb-200187, good man that he is, answered the question I had, which is wonderful. Sometimes you just need a relevant answer, yes?Thank you David Webb-200187, for having soul.
Considering that cursors are usually a bad thing and the others were trying to help you avoid them, I think the others have a soul, as well. 😉
Sometimes you just need a different answer than what you asked for so you can do it right. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
April 14, 2013 at 10:19 pm
LP has soul? If you say so :pinch:.
April 14, 2013 at 11:07 pm
KoldCoffee (4/14/2013)
LP has soul? If you say so :pinch:.
I don't know if I should take this as a joke or an insult.
April 14, 2013 at 11:13 pm
truth is that I would be honored to meet you all at a SQL Pass summit one day. Each of you have been tremendously helpful to my SQL learning.
Thank you Lynn. Lowell, i'm still thinking on Hierarchies too and your articles are among my pile of reads. Good night!
April 14, 2013 at 11:54 pm
Well, I won't be at Pass this year, but I will sure try to make it to PASS in 2014.
April 15, 2013 at 7:14 am
Well, I hope to see you there!
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply