Showing posts with label executed. Show all posts
Showing posts with label executed. Show all posts

Sunday, March 25, 2012

cursor error must declare scalar variable

when the following SELECT is executed via A cursor to SQL 2005 it works

SELECT * FROM LCTRY
WHERE LANGUAGE = 'EN' AND CTRY_CODE = :CTRY

BUT

SELECT * FROM LCTRY
WHERE CTRY_CODE = :CTRY AND LANGUAGE = 'EN'

PRODUCES AN ERROR 000137 MUST DECALRE SCALAR VARIABLE
'@.P1AND'

The application works with MySQL.Hi,

It could be that you are missing single quotes around one of your parameters.

Cheers
C

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

Thursday, March 22, 2012

Currently processed SQL Query.

Is there any way to obtain the SQL query that is currently executed from
within SQL Server?
Thanks.Q1 Is there any way to obtain the SQL query that is currently executed from within SQL Server? Thanks.
A1 Yes several ways, for example, you may execute sp_Who in query analyzer (check the status and cmd columns of the result set); or see / refresh "current activity" in EM, or even use profiler. Example for sp_Who:

exec sp_Who|||That, i exactly want to have is a string variable with the sql query that is
currently executed, in order to use it in a trigger. Is there such a global
variable in SQL Server or can i obtain this from a function or smth? Can
i do this with sp_who? If yes how can i retrieve the cmd column of it's resulting set?

Thank you very much and sorry for not explaining well my question in
my first post?|||Q1 That, i exactly want to have is a string variable with the sql query that is currently executed, in order to use it in a trigger. Is there such a global variable in SQL Server or can i obtain this from a function or smth?
Q2 Can i do this with sp_who? If yes how can i retrieve the cmd column of it's resulting set?
Q3 Thank you very much and sorry for not explaining well my question in my first post?

A1 There is no global variable I know of that holds the currently executing stored procedure / tsql as a whole. The best option for capturing such information in its entirety on the fly is probably to use profiler (it should be possible with trace flags as well, but that would be more involved). Note: It is possible for stored procedures to have large output variables (nvarchar / varchar) that could potentially hold and pass 4000 to 8000 chars to a calling procedure (which may or may not address your needs, but you would in any case have issues with procedures exceeding 8000 chars in length using that approach).

A2 Not with any current release, it is possible to use sp_who to identify the desired currently active connection (and then view the input buffer for that connection, to see the 'currently executing string'), however, that only gives the first 255 char currently in the input buffer.

A3 You are welcome.

Wednesday, March 21, 2012

Current SQL Statements for database instance

Hi,
In Oracle I have the option as DBA to view what current SQL Statements
is being executed for the current sessions on a database instance. How
do I do this in SQL Server 2005?
The reason is that I have to tune up SQL statements run by binary
executable files for which I don't have the source.
Best regards.Morten
You can turn on SQL Server Profiler and set up a trace to see what is going
on
select
s.session_id,
s.login_name,
s.status session_status,
s.cpu_time session_cpu,
s.logical_reads session_logical_reads,
s.reads session_reads,
s.writes session_writes,
sql_details.objectid session_current_procedure,
object_name(sql_details.objectid) session_current_procedure_name,
sql_details.text session_current_sql_text
from sys.dm_exec_sessions s
join sys.dm_exec_requests req
on s.session_id = req.session_id
cross apply sys.dm_exec_sql_text(req.sql_handle) sql_details
where s.session_id <> @.@.spid
"Morten Abildgaard" <mortenabildgaard@.gmail.com> wrote in message
news:1193213575.893092.10980@.i38g2000prf.googlegroups.com...
> Hi,
> In Oracle I have the option as DBA to view what current SQL Statements
> is being executed for the current sessions on a database instance. How
> do I do this in SQL Server 2005?
> The reason is that I have to tune up SQL statements run by binary
> executable files for which I don't have the source.
> Best regards.
>|||Thanks a bunch, Uri!
The SQL Server Profiler util was exactly what I was looking for. I
just thought it was an integrated part of the Management Studio.
On 24 Okt., 10:43, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Morten
> You can turn on SQL Server Profiler and set up a trace to see what is going
> on
> select
> s.session_id,
> s.login_name,
> s.status session_status,
> s.cpu_time session_cpu,
> s.logical_reads session_logical_reads,
> s.reads session_reads,
> s.writes session_writes,
> sql_details.objectid session_current_procedure,
> object_name(sql_details.objectid) session_current_procedure_name,
> sql_details.text session_current_sql_text
> from sys.dm_exec_sessions s
> join sys.dm_exec_requests req
> on s.session_id = req.session_id
> cross apply sys.dm_exec_sql_text(req.sql_handle) sql_details
> where s.session_id <> @.@.spid
> "Morten Abildgaard" <mortenabildga...@.gmail.com> wrote in message
> news:1193213575.893092.10980@.i38g2000prf.googlegroups.com...
> > Hi,
> > In Oracle I have the option as DBA to view what current SQL Statements
> > is being executed for the current sessions on a database instance. How
> > do I do this in SQL Server 2005?
> > The reason is that I have to tune up SQL statements run by binary
> > executable files for which I don't have the source.
> > Best regards.

Current Process

Hi
I've been told that I can use the following SQL expressions to find out the
current SQL being executed under a certain SPID, but for some reason I
cannot get this to work.
Is there something wrong, in what I have written?
--*************************************
DECLARE @.Handle binary(20)
SELECT @.Handle = sql_handle FROM master.dbo.sysprocesses WHERE spid = 55
SELECT * FROM ::fn_get_sql(@.Handle)
--*************************************
Kind Regards
Ricky
(WIN2K/SQL2K-SP4)It should work if the specified SPId is still running.
Try this
DECLARE @.Handle binary(20)
SELECT @.Handle = sql_handle FROM master.dbo.sysprocesses WHERE spid = @.@.SPID
SELECT * FROM ::fn_get_sql(@.Handle)
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"ricky" <ricky@.ricky.com> wrote in message
news:O08GR61mGHA.4212@.TK2MSFTNGP04.phx.gbl...
> Hi
> I've been told that I can use the following SQL expressions to find out
> the
> current SQL being executed under a certain SPID, but for some reason I
> cannot get this to work.
> Is there something wrong, in what I have written?
> --*************************************
> DECLARE @.Handle binary(20)
> SELECT @.Handle = sql_handle FROM master.dbo.sysprocesses WHERE spid = 55
> SELECT * FROM ::fn_get_sql(@.Handle)
> --*************************************
> Kind Regards
> Ricky
> (WIN2K/SQL2K-SP4)
>