Declare Scalar Variable??

I've got a report that is pretty simple but for some reason I keep getting
the following error when I try to run it:
An error occured during local report processing.
An error has occured during report processing.
Query execution failed for data set "Dataset 1"
Must declare the scalar variable "@.TABLENAME".
This is the actual dataset I'm trying to run:
EXEC dbo.Report_TSQL_By_ID_Archive @.TABLENAME, @.SQL_ID, @.DB_ID
This is set up as COMMAND TYPE of TEXT.
Here's the actual stored procedure being called:
ALTER proc [dbo].[Report_TSQL_by_ID_Archive]
----
-- Description: Report all transactions from a given trace table by SQL_ID
-- Revision History:
----
@.TABLENAME varchar(128),
@.SQL_ID int,
@.DB_ID int,
@.Sort varchar(20) = 'CPU'
as
set nocount on
--DECLARE @.Table VARCHAR(128)
--Set @.TABLENAME = N'MTGSMNEG034_' + CONVERT(VARCHAR(24), DATEADD(day, -1,
getdate()), 110)
exec ('
select StartTime, Reads, CPU, Duration, spid, [SQL] = convert( varchar(4000), substring( TextData, 1, 4000 ) )
from [' + @.TABLENAME + '] t
join [' + @.TABLENAME + '_id] i on t.RowNumber = i.RowNumber
where i.id = ' + @.SQL_ID + '
and i.databaseID = ' + @.DB_ID)-- + '
--order by ' + @.Sort + 'desc
--')
GO
I've got another report that is literally the exact same thing, except there
is no @.TABLENAME parameter in the stored procedure and it runs just fine. I'm
also able to run the stored procedure by itself just fine.
If anyone has any idea as to what the issue may be, that would be fantastic!!
Thanks!On May 4, 2:53 pm, A. Robinson <ARobin...@.discussions.microsoft.com>
wrote:
> I've got a report that is pretty simple but for some reason I keep getting
> the following error when I try to run it:
> An error occured during local report processing.
> An error has occured during report processing.
> Query execution failed for data set "Dataset 1"
> Must declare the scalar variable "@.TABLENAME".
> This is the actual dataset I'm trying to run:
> EXEC dbo.Report_TSQL_By_ID_Archive @.TABLENAME, @.SQL_ID, @.DB_ID
> This is set up as COMMAND TYPE of TEXT.
> Here's the actual stored procedure being called:
> ALTER proc [dbo].[Report_TSQL_by_ID_Archive]
> ----
> -- Description: Report all transactions from a given trace table by SQL_ID
> -- Revision History:
> ----
> @.TABLENAME varchar(128),
> @.SQL_ID int,
> @.DB_ID int,
> @.Sort varchar(20) = 'CPU'
> as
> set nocount on
> --DECLARE @.Table VARCHAR(128)
> --Set @.TABLENAME = N'MTGSMNEG034_' + CONVERT(VARCHAR(24), DATEADD(day, -1,
> getdate()), 110)
> exec ('
> select StartTime, Reads, CPU, Duration, spid, [SQL] => convert( varchar(4000), substring( TextData, 1, 4000 ) )
> from [' + @.TABLENAME + '] t
> join [' + @.TABLENAME + '_id] i on t.RowNumber = i.RowNumber
> where i.id = ' + @.SQL_ID + '
> and i.databaseID = ' + @.DB_ID)-- + '
> --order by ' + @.Sort + 'desc
> --')
> GO
> I've got another report that is literally the exact same thing, except there
> is no @.TABLENAME parameter in the stored procedure and it runs just fine. I'm
> also able to run the stored procedure by itself just fine.
> If anyone has any idea as to what the issue may be, that would be fantastic!!
> Thanks!
I don't think you have the syntax correct on the Reporting Services
side.
This link outlines it:
http://msdn2.microsoft.com/en-us/library/aa337435.aspx
Let me know if this is what you're looking for. I have some scripts I
use to pass parameters into stored procedures at home. I can take a
look into it if the link isn't clear or if it doesn't work.|||I'm using the exact same syntax throughtout my project and all the reports
work fine. For example, this is the syntax in another report I'm using:
EXEC dbo.Report_TSQL_By_ID @.SQL_ID, @.DB_ID
This report works fine with no problems at all...
"Ayman" wrote:
> On May 4, 2:53 pm, A. Robinson <ARobin...@.discussions.microsoft.com>
> wrote:
> > I've got a report that is pretty simple but for some reason I keep getting
> > the following error when I try to run it:
> >
> > An error occured during local report processing.
> > An error has occured during report processing.
> > Query execution failed for data set "Dataset 1"
> > Must declare the scalar variable "@.TABLENAME".
> >
> > This is the actual dataset I'm trying to run:
> > EXEC dbo.Report_TSQL_By_ID_Archive @.TABLENAME, @.SQL_ID, @.DB_ID
> >
> > This is set up as COMMAND TYPE of TEXT.
> >
> > Here's the actual stored procedure being called:
> >
> > ALTER proc [dbo].[Report_TSQL_by_ID_Archive]
> > ----
> > -- Description: Report all transactions from a given trace table by SQL_ID
> > -- Revision History:
> > ----
> > @.TABLENAME varchar(128),
> > @.SQL_ID int,
> > @.DB_ID int,
> > @.Sort varchar(20) = 'CPU'
> > as
> > set nocount on
> >
> > --DECLARE @.Table VARCHAR(128)
> >
> > --Set @.TABLENAME = N'MTGSMNEG034_' + CONVERT(VARCHAR(24), DATEADD(day, -1,
> > getdate()), 110)
> >
> > exec ('
> > select StartTime, Reads, CPU, Duration, spid, [SQL] => > convert( varchar(4000), substring( TextData, 1, 4000 ) )
> > from [' + @.TABLENAME + '] t
> > join [' + @.TABLENAME + '_id] i on t.RowNumber = i.RowNumber
> > where i.id = ' + @.SQL_ID + '
> > and i.databaseID = ' + @.DB_ID)-- + '
> > --order by ' + @.Sort + 'desc
> > --')
> > GO
> >
> > I've got another report that is literally the exact same thing, except there
> > is no @.TABLENAME parameter in the stored procedure and it runs just fine. I'm
> > also able to run the stored procedure by itself just fine.
> >
> > If anyone has any idea as to what the issue may be, that would be fantastic!!
> >
> > Thanks!
> I don't think you have the syntax correct on the Reporting Services
> side.
> This link outlines it:
> http://msdn2.microsoft.com/en-us/library/aa337435.aspx
> Let me know if this is what you're looking for. I have some scripts I
> use to pass parameters into stored procedures at home. I can take a
> look into it if the link isn't clear or if it doesn't work.
>|||...and the link here is addressing the issue of binding input parameters to
user defined functions...unfirtunately that's not what I'm doing.
"Ayman" wrote:
> On May 4, 2:53 pm, A. Robinson <ARobin...@.discussions.microsoft.com>
> wrote:
> > I've got a report that is pretty simple but for some reason I keep getting
> > the following error when I try to run it:
> >
> > An error occured during local report processing.
> > An error has occured during report processing.
> > Query execution failed for data set "Dataset 1"
> > Must declare the scalar variable "@.TABLENAME".
> >
> > This is the actual dataset I'm trying to run:
> > EXEC dbo.Report_TSQL_By_ID_Archive @.TABLENAME, @.SQL_ID, @.DB_ID
> >
> > This is set up as COMMAND TYPE of TEXT.
> >
> > Here's the actual stored procedure being called:
> >
> > ALTER proc [dbo].[Report_TSQL_by_ID_Archive]
> > ----
> > -- Description: Report all transactions from a given trace table by SQL_ID
> > -- Revision History:
> > ----
> > @.TABLENAME varchar(128),
> > @.SQL_ID int,
> > @.DB_ID int,
> > @.Sort varchar(20) = 'CPU'
> > as
> > set nocount on
> >
> > --DECLARE @.Table VARCHAR(128)
> >
> > --Set @.TABLENAME = N'MTGSMNEG034_' + CONVERT(VARCHAR(24), DATEADD(day, -1,
> > getdate()), 110)
> >
> > exec ('
> > select StartTime, Reads, CPU, Duration, spid, [SQL] => > convert( varchar(4000), substring( TextData, 1, 4000 ) )
> > from [' + @.TABLENAME + '] t
> > join [' + @.TABLENAME + '_id] i on t.RowNumber = i.RowNumber
> > where i.id = ' + @.SQL_ID + '
> > and i.databaseID = ' + @.DB_ID)-- + '
> > --order by ' + @.Sort + 'desc
> > --')
> > GO
> >
> > I've got another report that is literally the exact same thing, except there
> > is no @.TABLENAME parameter in the stored procedure and it runs just fine. I'm
> > also able to run the stored procedure by itself just fine.
> >
> > If anyone has any idea as to what the issue may be, that would be fantastic!!
> >
> > Thanks!
> I don't think you have the syntax correct on the Reporting Services
> side.
> This link outlines it:
> http://msdn2.microsoft.com/en-us/library/aa337435.aspx
> Let me know if this is what you're looking for. I have some scripts I
> use to pass parameters into stored procedures at home. I can take a
> look into it if the link isn't clear or if it doesn't work.
>|||Is there a reason you are not using a command type of stored procedure? If
you do this then RS automatically determines the parameters and the
parameter data type and creates the report parameters for you. That would
solve your problem.
But, given what you have below the issue is that for whatever reason the
query parameter @.TABLENAME is not mapped to your report parameter. On the
dataset tab click on the ..., parameters tab and make sure the @.TABLENAME
parameter is mapped to the report parameter.
This error is what you get when this mapping has not occured.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"A. Robinson" <ARobinson@.discussions.microsoft.com> wrote in message
news:974C277C-B4F0-4940-A7E9-E8CDCD33D27F@.microsoft.com...
> I've got a report that is pretty simple but for some reason I keep getting
> the following error when I try to run it:
> An error occured during local report processing.
> An error has occured during report processing.
> Query execution failed for data set "Dataset 1"
> Must declare the scalar variable "@.TABLENAME".
> This is the actual dataset I'm trying to run:
> EXEC dbo.Report_TSQL_By_ID_Archive @.TABLENAME, @.SQL_ID, @.DB_ID
> This is set up as COMMAND TYPE of TEXT.
>
> Here's the actual stored procedure being called:
> ALTER proc [dbo].[Report_TSQL_by_ID_Archive]
> ----
> -- Description: Report all transactions from a given trace table by SQL_ID
> -- Revision History:
> ----
> @.TABLENAME varchar(128),
> @.SQL_ID int,
> @.DB_ID int,
> @.Sort varchar(20) = 'CPU'
> as
> set nocount on
> --DECLARE @.Table VARCHAR(128)
> --Set @.TABLENAME = N'MTGSMNEG034_' + CONVERT(VARCHAR(24), DATEADD(day, -1,
> getdate()), 110)
> exec ('
> select StartTime, Reads, CPU, Duration, spid, [SQL] => convert( varchar(4000), substring( TextData, 1, 4000 ) )
> from [' + @.TABLENAME + '] t
> join [' + @.TABLENAME + '_id] i on t.RowNumber = i.RowNumber
> where i.id = ' + @.SQL_ID + '
> and i.databaseID = ' + @.DB_ID)-- + '
> --order by ' + @.Sort + 'desc
> --')
> GO
> I've got another report that is literally the exact same thing, except
> there
> is no @.TABLENAME parameter in the stored procedure and it runs just fine.
> I'm
> also able to run the stored procedure by itself just fine.
> If anyone has any idea as to what the issue may be, that would be
> fantastic!!
> Thanks!
>
>
>|||Thanks!
I actually discovered the problem about five minutes after I posted my
question!
"Bruce L-C [MVP]" wrote:
> Is there a reason you are not using a command type of stored procedure? If
> you do this then RS automatically determines the parameters and the
> parameter data type and creates the report parameters for you. That would
> solve your problem.
> But, given what you have below the issue is that for whatever reason the
> query parameter @.TABLENAME is not mapped to your report parameter. On the
> dataset tab click on the ..., parameters tab and make sure the @.TABLENAME
> parameter is mapped to the report parameter.
> This error is what you get when this mapping has not occured.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "A. Robinson" <ARobinson@.discussions.microsoft.com> wrote in message
> news:974C277C-B4F0-4940-A7E9-E8CDCD33D27F@.microsoft.com...
> > I've got a report that is pretty simple but for some reason I keep getting
> > the following error when I try to run it:
> >
> > An error occured during local report processing.
> > An error has occured during report processing.
> > Query execution failed for data set "Dataset 1"
> > Must declare the scalar variable "@.TABLENAME".
> >
> > This is the actual dataset I'm trying to run:
> > EXEC dbo.Report_TSQL_By_ID_Archive @.TABLENAME, @.SQL_ID, @.DB_ID
> >
> > This is set up as COMMAND TYPE of TEXT.
> >
> >
> > Here's the actual stored procedure being called:
> >
> > ALTER proc [dbo].[Report_TSQL_by_ID_Archive]
> > ----
> > -- Description: Report all transactions from a given trace table by SQL_ID
> > -- Revision History:
> > ----
> > @.TABLENAME varchar(128),
> > @.SQL_ID int,
> > @.DB_ID int,
> > @.Sort varchar(20) = 'CPU'
> > as
> > set nocount on
> >
> > --DECLARE @.Table VARCHAR(128)
> >
> > --Set @.TABLENAME = N'MTGSMNEG034_' + CONVERT(VARCHAR(24), DATEADD(day, -1,
> > getdate()), 110)
> >
> > exec ('
> > select StartTime, Reads, CPU, Duration, spid, [SQL] => > convert( varchar(4000), substring( TextData, 1, 4000 ) )
> > from [' + @.TABLENAME + '] t
> > join [' + @.TABLENAME + '_id] i on t.RowNumber = i.RowNumber
> > where i.id = ' + @.SQL_ID + '
> > and i.databaseID = ' + @.DB_ID)-- + '
> > --order by ' + @.Sort + 'desc
> > --')
> > GO
> >
> > I've got another report that is literally the exact same thing, except
> > there
> > is no @.TABLENAME parameter in the stored procedure and it runs just fine.
> > I'm
> > also able to run the stored procedure by itself just fine.
> >
> > If anyone has any idea as to what the issue may be, that would be
> > fantastic!!
> >
> > Thanks!
> >
> >
> >
> >
> >
> >
>
>

Deallocating cursor if exception occured.

Hi all,
i am facing one problem while executing stored procedure.
What i am doing is i am opening one cursor and then depending on values
fetch inside the cursor i am inserting data into one table.
But sometimes my insert query failes due to violation in primary key.
In that case sp is not directly stopping its execution.
So my question is if such situation occured how will i deallocate my
cursor. I am using sql server 2000.
How to handle such exception to execute next part of stored procedure.
Thanks in advance.
trialproduct2004@.yahoo.com wrote:
> Hi all,
> i am facing one problem while executing stored procedure.
> What i am doing is i am opening one cursor and then depending on values
> fetch inside the cursor i am inserting data into one table.
>
I suggest you do that with a WHERE clause rather than a cursor.
INSERT INTO tbl1 (co1, col2, ...)
SELECT col1, col2, ...
FROM tbl2
WHERE ... ?

> But sometimes my insert query failes due to violation in primary key.
> In that case sp is not directly stopping its execution.
Ditto. Better to fix your code rather than handle the errors it causes.
Change your INSERT query to avoid inserting duplicate rows.
Cursors should be a last resort only. At least 99.9% of the time you
can and should avoid them.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx