Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

please help with cursor syntax Expand / Collapse
Author
Message
Posted Friday, March 29, 2013 10:56 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, December 8, 2014 8:19 AM
Points: 704, Visits: 1,656
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?
Post #1437020
Posted Friday, March 29, 2013 11:23 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 4:12 PM
Points: 867, Visits: 7,563
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.




And then again, I might be wrong ...
David Webb
Post #1437029
Posted Friday, March 29, 2013 11:29 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, December 8, 2014 8:19 AM
Points: 704, Visits: 1,656
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?
Post #1437030
Posted Friday, March 29, 2013 11:33 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 4:12 PM
Points: 867, Visits: 7,563
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.




And then again, I might be wrong ...
David Webb
Post #1437033
Posted Friday, March 29, 2013 11:48 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, December 8, 2014 8:19 AM
Points: 704, Visits: 1,656
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
Post #1437037
Posted Friday, March 29, 2013 12:14 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 9:45 AM
Points: 1,943, Visits: 20,161
just a thought

;with SD as (
SELECT name,
database_id,
create_date
FROM sys.databases
where name not in ('master','tempdb', 'model', 'msdb')
)

SELECT SD.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
Post #1437046
Posted Monday, April 1, 2013 5:32 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, December 8, 2014 8:19 AM
Points: 704, Visits: 1,656
Yes, I agree. There's also an sp_msforeachdb function available....

But I needed to learn the cursor
Post #1437666
Posted Monday, April 1, 2013 11:18 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 9:47 AM
Points: 20,857, Visits: 32,876
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1437698
Posted Tuesday, April 2, 2013 8:29 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, December 8, 2014 8:19 AM
Points: 704, Visits: 1,656
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.
Post #1437929
Posted Sunday, April 14, 2013 10:01 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:12 AM
Points: 35,770, Visits: 32,434
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1442161
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse