Cursor variable not declared issue

Ok, I missed the boat somewhere.
I prepared the query below to go though all the columns and tables in
the database and return the count of distinct values for each column and
label them with the table and column names
When I run the query, I receive the following error from the part
labeled #1:
Server: Msg 137, Level 15, State 2, Line 44
Must declare the variable '@.tbl_name'.
What I dont understand is why this error is occurring. I defined the
variable and populated it. I commented out part #1 and tried PRINT
@.tbl_name @.col_name which returned appropriate values.
I have a workaround of commenting out the part labeled #1 and instead
insert the following which produced a list of queries that I copied and
pasted into a new QA window and executed.
PRINT 'select count(distinct ' + @.col_name + ') ' + '"' + @.tbl_name +
'.' + @.col_name + '"' + ' from ' + @.tbl_name
I dont understand why I cannot substitute the variables in a query as I
wish to.
I am also considering building a command string and using exec
sp_executesql.
I welcome comments and suggestions on this matter.
-- -- --
DECLARE @.tbl_name varchar(255), @.col_name varchar(255)
DECLARE CURS_sys_tables_and_cols CURSOR FOR
select sysobjects.name, syscolumns.name from syscolumns, sysobjects
where sysobjects.id = syscolumns.id
and (sysobjects.xtype='U' or sysobjects.xtype='S')
and sysobjects.name NOT like 'SYS%'
and sysobjects.name NOT IN ( LIST OF TABLES I DONT WANT)
order by sysobjects.name
OPEN CURS_sys_tables_and_cols
FETCH NEXT FROM CURS_sys_tables_and_cols
INTO @.tbl_name, @.col_name
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- #1
PRINT @.tbl_name + '.' + @.col_name
Select count(distinct @.col_name) from @.tbl_name
PRINT '--'
FETCH NEXT FROM CURS_sys_tables_and_cols
INTO @.tbl_name, @.col_name
END
CLOSE CURS_sys_tables_and_cols
DEALLOCATE CURS_sys_tables_and_cols
*** Sent via Developersdex http://www.examnotes.net ***SJM,
I think you'll need to use Dynamic SQL to use a variable for the table name
in your query i.e., EXEC or sp_executesql.
Check it out in the SQL BOL and at Erland's article:
http://www.sommarskog.se/dynamic_sql.html
HTH
Jerry
"SJM" <nospam@.devdex.com> wrote in message
news:eIAwXiRxFHA.624@.TK2MSFTNGP11.phx.gbl...
> Ok, I missed the boat somewhere.
> I prepared the query below to go though all the columns and tables in
> the database and return the count of distinct values for each column and
> label them with the table and column names
> When I run the query, I receive the following error from the part
> labeled #1:
> Server: Msg 137, Level 15, State 2, Line 44
> Must declare the variable '@.tbl_name'.
> What I don't understand is why this error is occurring. I defined the
> variable and populated it. I commented out part #1 and tried PRINT
> @.tbl_name @.col_name which returned appropriate values.
> I have a workaround of commenting out the part labeled #1 and instead
> insert the following which produced a list of queries that I copied and
> pasted into a new QA window and executed.
> PRINT 'select count(distinct ' + @.col_name + ') ' + '"' + @.tbl_name +
> '.' + @.col_name + '"' + ' from ' + @.tbl_name
> I don't understand why I cannot substitute the variables in a query as I
> wish to.
> I am also considering building a command string and using exec
> sp_executesql.
> I welcome comments and suggestions on this matter.
> -- -- --
> DECLARE @.tbl_name varchar(255), @.col_name varchar(255)
> DECLARE CURS_sys_tables_and_cols CURSOR FOR
> select sysobjects.name, syscolumns.name from syscolumns, sysobjects
> where sysobjects.id = syscolumns.id
> and (sysobjects.xtype='U' or sysobjects.xtype='S')
> and sysobjects.name NOT like 'SYS%'
> and sysobjects.name NOT IN ( LIST OF TABLES I DON'T WANT)
> order by sysobjects.name
> OPEN CURS_sys_tables_and_cols
> FETCH NEXT FROM CURS_sys_tables_and_cols
> INTO @.tbl_name, @.col_name
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
>
> -- #1
> PRINT @.tbl_name + '.' + @.col_name
> Select count(distinct @.col_name) from @.tbl_name
> PRINT '--'
>
> FETCH NEXT FROM CURS_sys_tables_and_cols
> INTO @.tbl_name, @.col_name
> END
> CLOSE CURS_sys_tables_and_cols
> DEALLOCATE CURS_sys_tables_and_cols
>
> *** Sent via Developersdex http://www.examnotes.net ***|||
Indeed, I thought I might need to build strings and use sp_executesql.
Thanks for the pointer to the article, I missed it in my google
searches.
*** Sent via Developersdex http://www.examnotes.net ***

Cursor in an SP

I am doing a compare last history query that I'm seeing only a cursor as a
way to do.
Data to return: custID, CustRegion, # orders, Total volume,
#monthsExpectedUse
1) Look at all orders by customer within a date range. ( I get col 1)
Greates cursor
2) Establish prior order per customer outside of the dates, and give time
diff for their ( I get cols 3,4,5)
How do I combine inital data @.CustAccount, @.qty, @.TotVolume, @.MonthsUse into
a final return set?It might be that a cursor is the best way, but that is rarely the case, and
even more rare that it is the only way. Could you provide detailed specs,
sample data, and desired results? This way, we can probably come up with an
alternative to a cursor which will be much more efficient and easier to
maintain. Please see http://www.aspfaq.com/5006
"Stephen Russell" <srussell@.transactiongraphics.com> wrote in message
news:uQf6lajrFHA.3264@.TK2MSFTNGP12.phx.gbl...
>I am doing a compare last history query that I'm seeing only a cursor as a
>way to do.
> Data to return: custID, CustRegion, # orders, Total volume,
> #monthsExpectedUse
> 1) Look at all orders by customer within a date range. ( I get col 1)
> Greates cursor
> 2) Establish prior order per customer outside of the dates, and give time
> diff for their ( I get cols 3,4,5)
> How do I combine inital data @.CustAccount, @.qty, @.TotVolume, @.MonthsUse
> into a final return set?
>|||
*** Sent via Developersdex http://www.examnotes.net ***|||> *** Sent via Developersdex http://www.examnotes.net ***
Nice one. You might try a newsreader, they're a bit more reliable.|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OvknW5jrFHA.2588@.tk2msftngp13.phx.gbl...
> Nice one. You might try a newsreader, they're a bit more reliable.
Crud!|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OvknW5jrFHA.2588@.tk2msftngp13.phx.gbl...
> Nice one. You might try a newsreader, they're a bit more reliable.
Can a select with in a select return 2 columns?|||> Can a select with in a select return 2 columns?
Once again, you will need to be more specific. If you post table structure,
sample data, and what you are trying to do, it will be much easier than
answering word problems...|||"Stephen Russell" <srussell@.transactiongraphics.com> wrote in message
news:OWokKDkrFHA.3080@.TK2MSFTNGP15.phx.gbl...
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:OvknW5jrFHA.2588@.tk2msftngp13.phx.gbl...
> Can a select with in a select return 2 columns?
I want : returns 2 columns here not 1
Select a.col1, a.col2, (select b.col3, b.col4 from table orders b where b.id
= a.col1 and b.id2 = a.col2)
From orders a
Left join ....
Where ...
Group by ...
Order by ...|||I do not understand what "returns 2 columns here not 1" means.
***PLEASE*** GO READ http://www.aspfaq.com/5006
"Stephen Russell" <srussell@.transactiongraphics.com> wrote in message
news:Owz2IIkrFHA.2552@.TK2MSFTNGP10.phx.gbl...
> "Stephen Russell" <srussell@.transactiongraphics.com> wrote in message
> news:OWokKDkrFHA.3080@.TK2MSFTNGP15.phx.gbl...
> I want : returns 2 columns here not 1
> Select a.col1, a.col2, (select b.col3, b.col4 from table orders b where
> b.id = a.col1 and b.id2 = a.col2)
> From orders a
> Left join ....
> Where ...
> Group by ...
> Order by ...
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

CURSOR AS PROCEDURE OUTPUT

I've written this GenericCursor procedure that return a CURSOR as output parameter, where the CURSOR is opened by a dynamic SQL statement executed via sp_executesql:

CREATE PROCEDURE dbo.GenericCursor
@.genericCursor CURSOR VARYING OUTPUT
, @.CMD Nvarchar(1024)
AS
BEGIN
DECLARE @.CMDx Nvarchar(1024);
SET @.CMDx = 'SET @.genericCursor = CURSOR FORWARD_ONLY STATIC FOR ' + @.CMD + '; OPEN @.genericCursor;'
exec sp_executesql @.CMD,
N'@.utentiCursor CURSOR out',
@.genericCursor out
END

Everityng works fine: I want to use GenericCursor procedure and it works, but when I try to CLOSE and then DEALLOCATE the CURSOR it raises an error (as in the example):

CREATE PROCEDURE testGenericCursor
AS
BEGIN
DECLARE @.MyCursor CURSOR;
DECLARE @.CMDx Nvarchar(1024);
SET @.CMDx = 'SELECT idUtente FROM Utenti'
EXEC dbo.UtentiCursor @.MyCursor OUTPUT, @.CMDx;
WHILE (@.@.FETCH_STATUS = 0)
BEGIN;
FETCH NEXT FROM @.MyCursor;
END;
-- CLOSE @.MyCursor;
-- DEALLOCATE @.MyCursor;
END


Why CLOSE and DEALLOCATEare not usable? They generate the following errors:

Msg 16950, Level 16, State 2, Line 13
The variable '@.MyCursor' does not currently have a cursor allocated to it.
Msg 16950, Level 16, State 2, Line 14
The variable '@.MyCursor' does not currently have a cursor allocated to it.

I don't think you ever put the cursor into your output parameter in the first place (unless you just made a copy\paste error when putting your code into the post).

The problem is here:

DECLARE @.CMDx Nvarchar(1024);
SET @.CMDx = 'SET @.genericCursor = CURSOR FORWARD_ONLY STATIC FOR ' + @.CMD + '; OPEN @.genericCursor;' <<<< This won't work (see below)
exec sp_executesql @.CMD, <<<< Shouldn't this be @.CMDx?

Your going to have to pass @.genericCursor into sp_executesql as an output parameter as well, something like this:

DECLARE @.CMDx Nvarchar(1024);
SET @.CMDx = 'SET @.genericCursor = CURSOR FORWARD_ONLY STATIC FOR ' + @.CMD + '; OPEN @.genericCursor;'
exec sp_executesql @.CMDx, N'@.genericCursor cursor varying output', @.genericCursor output

|||

The CLOSE and DEALLOCATE are in a different scope than the cursor...they don't know each other exist.

Your code revised:

CREATE PROCEDURE dbo.GenericCursor

@.genericCursor CURSOR VARYING OUTPUT

, @.CMD Nvarchar(1024)

AS

BEGIN

DECLARE @.CMDx Nvarchar(1024);

SET @.CMDx = 'SET @.genericCursor = CURSOR FORWARD_ONLY STATIC FOR ' + @.CMD + '; OPEN @.genericCursor;'

exec sp_executesql @.CMD,

N'@.utentiCursor CURSOR out',

@.genericCursor out

CLOSE @.genericCursor

DEALLOCATE @.genericCursor

END

CREATE PROCEDURE testGenericCursor

AS

BEGIN

DECLARE @.MyCursor CURSOR;

DECLARE @.CMDx Nvarchar(1024);

SET @.CMDx = 'SELECT name FROM dbo.sysobjects;'

EXEC dbo.GenericCursor @.MyCursor OUTPUT, @.CMDx;

WHILE (@.@.FETCH_STATUS = 0)

BEGIN;

FETCH NEXT FROM @.MyCursor;

END

END

EXECdbo.testGenericCursor

This is an example from the EXECUTE (Transact-SQL) topic in Books Online that uses EXEC:

http://msdn2.microsoft.com/en-us/library/ms188332.aspx

USE AdventureWorks;

GO

DECLARE tables_cursor CURSOR

FOR

SELECT s.name, t.name

FROM sys.objects AS t

JOIN sys.schemas AS s ON s.schema_id = t.schema_id

WHERE t.type = 'U';

OPEN tables_cursor;

DECLARE @.schemaname sysname;

DECLARE @.tablename sysname;

FETCH NEXT FROM tables_cursor INTO @.schemaname, @.tablename;

WHILE (@.@.FETCH_STATUS <> -1)

BEGIN;

EXEC ('ALTER INDEX ALL ON ' + @.schemaname + '.' + @.tablename + ' REBUILD;');

FETCH NEXT FROM tables_cursor INTO @.schemaname, @.tablename;

END;

PRINT 'The indexes on all tables have been rebuilt.';

CLOSE tables_cursor;

DEALLOCATE tables_cursor;

GO

A good link for dynamic SQL:

http://www.sommarskog.se/dynamic_sql.html#cursor0

|||He is using cursor variables, which can be passed between scopes just like he is doing in dbo.GenericCursor. He just has to modify his sp_executesql call to pass the cursor variable as an output parameter.|||

Thanks to evryone out there: I noticed I've posted the wrong code :-P

> SO THIS MEANS YOU ARE SO GOOD THAT YOU'VE UNDERSTOOD IT EVEN IF IT WAS WRONG!!!

Just to summarize what the matter is, this is the final working version:

CREATE PROCEDURE dbo.GenericCursor

@.genericCursor CURSOR VARYING OUTPUT

, @.CMD Nvarchar(1024)

AS

BEGIN

DECLARE @.CMDx Nvarchar(1024);

SET @.CMDx = 'SET @.genericCursor = CURSOR FORWARD_ONLY STATIC FOR ' + @.CMD + '; OPEN @.genericCursor;'

exec sp_executesql @.CMDx,

N'@.genericCursor cursor output',

@.genericCursor out

END

--

CREATE PROCEDURE testGenericCursor

AS

BEGIN

DECLARE @.MyCursor CURSOR;

DECLARE @.name as varchar(100);

DECLARE @.CMDx Nvarchar(1024);

SET @.CMDx = 'SELECT TOP 50 name FROM dbo.sysobjects;'

EXEC dbo.GenericCursor @.MyCursor OUTPUT, @.CMDx;

FETCH NEXT FROM @.MyCursor INTO @.name;

WHILE (@.@.FETCH_STATUS = 0)

BEGIN;

FETCH NEXT FROM @.MyCursor INTO @.name;

SELECT @.name

END

CLOSE @.MyCursor

DEALLOCATE @.MyCursor

END

--

EXEC dbo.testGenericCursor

current keyID from sql update

When I return from executing a sql storedprocedure where I insert a record, where can I find the keyID?Inside of the stored procedure you should set up an OUTPUT parameter called e.g. @.keyID to communicate the newly added ID back to your page. After the INSERT you would do this to capture the newly added ID:

SELECT @.keyID = Scope_Identity()

Terri|||Got it! Thanks

curious result

How can I write a query to return non distinct results?Your question is a bit vague but maybe this example will help.
Duplicate last names in the Aithors table:

SELECT au_lname, COUNT(*)
FROM Pubs.dbo.Authors
GROUP BY au_lname
HAVING COUNT(*)>1

If you need more help please read the following article about the best
way to post your problem.

http://www.aspfaq.com/etiquette.asp?id=5006

--
David Portas
SQL Server MVP
--|||this works except I'm needing distinct combinations of several fields
such as

SELECT x, y, z, COUNT(*)
FROM table
GROUP BY x
HAVING COUNT(*)>1

David Portas wrote:

>Your question is a bit vague but maybe this example will help.
>Duplicate last names in the Aithors table:
>SELECT au_lname, COUNT(*)
> FROM Pubs.dbo.Authors
> GROUP BY au_lname
> HAVING COUNT(*)>1
>If you need more help please read the following article about the best
>way to post your problem.
>http://www.aspfaq.com/etiquette.asp?id=5006
>|||William Kossack (kossackw@.njc.org) writes:
> this works except I'm needing distinct combinations of several fields
> such as
> SELECT x, y, z, COUNT(*)
> FROM table
> GROUP BY x
> HAVING COUNT(*)>1

And that query is good for you? Or are you asking for more assistance?
In the latter case, please post:

o CREATE TABLE statment for the your table
o INSERT statement with sample data.
o The desired result given the sample.

if we have to guess what you are looking for, odds are that our
guesses will be wrong.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Curious DTSWizard behavior

Can someone try and explain to me how a sql query can run fine and return data, yet when I try to run it in dtswizard, while the "preview" view shows the data, actually running it returns zero rows?

This is on SQL2005 express and since I can't get dtsrun or dtexec to work, I'm using auto-it to simulate my actually stepping through the process. Very kludgy, but "when all you've got is a hammer..."Very difficult to debug a DTS issue without looking over your shoulder. More details on the query might help. Are you getting any error messages? Does the target object exist under different ownerships?|||I can post the query here if that would help, I just find it odd that the preview shows me data while the actual process does not. If I didn't make myself clear, I'm just running the dtswizard to create an Excel file and not actually saving the dts package since I have no way of running the package.|||I can't promise that posting the query will help, but I can tell you that you will not get a lot of responses on this forum unless you do post the query.|||Fair enough. Here it is...

use Hayesonline

SELECT
o.name
, Coalesce(Sum(CASE WHEN 1 = d THEN 1 END), 0) AS [01] -- Show explicit zero in first column
, Sum(CASE WHEN 2 = d THEN 1 END) AS [02]
, Sum(CASE WHEN 3 = d THEN 1 END) AS [03]
, Sum(CASE WHEN 4 = d THEN 1 END) AS [04]
, Sum(CASE WHEN 5 = d THEN 1 END) AS [05]
, Sum(CASE WHEN 6 = d THEN 1 END) AS [06]
, Sum(CASE WHEN 7 = d THEN 1 END) AS [07]
, Sum(CASE WHEN 8 = d THEN 1 END) AS [08]
, Sum(CASE WHEN 9 = d THEN 1 END) AS [09]
, Sum(CASE WHEN 10 = d THEN 1 END) AS [10]
, Sum(CASE WHEN 11 = d THEN 1 END) AS [11]
, Sum(CASE WHEN 12 = d THEN 1 END) AS [12]
, Sum(CASE WHEN 13 = d THEN 1 END) AS [13]
, Sum(CASE WHEN 14 = d THEN 1 END) AS [14]
, Sum(CASE WHEN 15 = d THEN 1 END) AS [15]
, Sum(CASE WHEN 16 = d THEN 1 END) AS [16]
, Sum(CASE WHEN 17 = d THEN 1 END) AS [17]
, Sum(CASE WHEN 18 = d THEN 1 END) AS [18]
, Sum(CASE WHEN 19 = d THEN 1 END) AS [19]
, Sum(CASE WHEN 20 = d THEN 1 END) AS [20]
, Sum(CASE WHEN 21 = d THEN 1 END) AS [21]
, Sum(CASE WHEN 22 = d THEN 1 END) AS [22]
, Sum(CASE WHEN 23 = d THEN 1 END) AS [23]
, Sum(CASE WHEN 24 = d THEN 1 END) AS [24]
, Sum(CASE WHEN 25 = d THEN 1 END) AS [25]
, Sum(CASE WHEN 26 = d THEN 1 END) AS [26]
, Sum(CASE WHEN 27 = d THEN 1 END) AS [27]
, Sum(CASE WHEN 28 = d THEN 1 END) AS [28]
, Sum(CASE WHEN 29 = d THEN 1 END) AS [29]
, Sum(CASE WHEN 30 = d THEN 1 END) AS [30]
, Sum(CASE WHEN 31 = d THEN 1 END) AS [31]
FROM dbo.Offices AS o
LEFT JOIN (SELECT a.officeID
, DatePart(d, o.DateCompleted) AS d
FROM dbo.Orders AS o
JOIN dbo.Appraisers AS a
ON (o.AppraiserID = a.AppraiserID)
WHERE Convert(CHAR(8), GetDate(), 121) + '01' <= o.dateCompleted
AND o.DateCompleted < DateAdd(month, 1, Convert(CHAR(8), GetDate(), 121) + '01')
AND 1 = o.StatusID) AS z
ON (z.officeID = o.officeID)
GROUP BY o.name
ORDER BY o.name ASC|||Are there any other tables named Offices, Orders, or Appraisers, under ownerhips other than DBO?

What is the datatype of the Orders.dateCompleted column? Is it datetime or is it varchar?|||DBO owns everything in the system.

orders.datecompleted is of type datetime.

csvde from Execute Process Task not returning groupType

When I run csvde from within an SSIS package's Execute Process Task, it does not return the groupType attribute, but when I run it directly from a command prompt, it does return that attribute. The csvde swiches are set the same in both cases:

-t 3268 -u -f allGroups.csv -d "DC=corp,DC=microsoft,DC=com" -r "(objectClass=group)" -l "groupType,mail,member"

The header line in the output file produced by the package is:

DN,member,mail,member;range=0-1499

while the header line in the file produced when csvde is run directly is:

DN,member,groupType,mail,member;range=0-1499

Has anyone else encountered this behavior?

Thanks,

Ron Rice

How do you have the Execute Process task setup?

Also, you may want to specify a full path to the -f flag.|||

Phil,

Unfortunately, making the -f switch a full path did not change the results.

I also tried changing the order of the attributes in the -l switch to "mail,groupType,member". At first I thought this had worked, because it did return the groupType attribute, but then I noticed that the "mail" attribute was not returned after making this change!

So for whatever reason, when I execute csvde from a package, the first attribute in the -l switch is not returned.

Thanks,

Ron

|||

Drop the double quotes around the -l flag parameters.

-l mail,groupType,member

|||

Phil,

Removing the double quotes from around the -l switch list of attributes did fix the problem. Thanks!

I wonder why csvde would behave differently when run in an Execute Process task versus being run directly from a command prompt. Also, I tried running it using T-SQL and xp_cmdshell, and that had the same problem. And I seem to remember running into issues concerning double quotes when I ran the bcp command from an Execute Process task, as well.

Whatever the ultimate cause of the problem, now that I know the work-around I am a happy camper!

Ron

|||

Rice31416 wrote:

Whatever the ultimate cause of the problem, now that I know the work-around I am a happy camper!

Ron

It's not really a work around. According to the csvde page, double quotes are not required. (See the examples at the bottom.)

http://technet2.microsoft.com/WindowsServer/en/Library/1050686f-3464-41af-b7e4-016ab0c4db261033.mspx