Dynamic Query Problem

I have a sproc that runs as a job every day. Since the first of the year, it hasn't been running properly (it errors out). It builds an SQL statement dynamically, and then executes it.

If I try to run it with QA, I get the following message:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'FCST'.
UPDATE OPSPLAN SET Jan_07=Jan_fcst, Feb_07=Feb_fcst, ...

However, if I just copy the entire Update statement contained in the error message into the QA window, and execute it, it runs just fine.

What could I be missing?

UPDATE OPSPLAN SET Jan_07=Jan_fcst, Feb_07=Feb_fcst, Mar_07=Mar_fcst,
Apr_07=Apr_fcst, May_07=May_fcst, Jun_07=Jun_fcst, Jul_07=Jul_fcst,
Aug_07=Aug_fcst, Sep_07=Sep_fcst, Oct_07=Oct_fcst, Nov_07=Nov_fcst,
Dec_07=Dec_fcst
FROM (SELECT [YEAR], PLAN_SHIP.BOD_INDEX, BOD_HEADER.PRODUCT,
Jan_fcst, Feb_fcst, Mar_fcst, Apr_fcst, May_fcst, Jun_fcst, Jul_fcst,
Aug_fcst, Sep_fcst, Oct_fcst, Nov_fcst, Dec_fcst
FROM PLAN_SHIP INNER JOIN BOD_HEADER
ON PLAN_SHIP.BOD_INDEX = BOD_HEADER.BOD_INDEX
WHERE (SCEN_ID = 1) AND ([Year] = 2007)
) PS INNER JOIN OPSPLAN ON PS.BOD_INDEX = OPSPLAN.BOD_INDEX
WHERE OPSPLAN.SRCPLAN = 'SHIP'I'd sic the SQL Profiler on this fella. My suspicion is that the UPDATE is being mis-parsed, possibly because of a syntax error within the previous SQL statement. Profiler ought to give you some clues if that is the case. No outright answers, just clues, but that's more than you have now.

-PatP|||OK, I read up on it in BOL, and figured out how to get profiler running.
I found the line where that particular statement is executing.
I'm looking at StmtStarting and StmtCompleted. Is there something in
particular that I should be looking for?|||Since the string "FCST" does not appear independently in your code, but only in conjuction with a month and an underscore character, I'd say that somewhere and underscore character is being dropped.

Set the dynamic sql statement to print rather than execute, and bump up the Max characters setting in the Query Analyzer Options. Then see what code is actually being executed.|||Yeah, that "FCST" was throwing me, too. All of the instances in my string
are "fcst" not "FCST". Anyway, after pouring through the sproc over and over, I found a PRINT statement that was causing the completely valild statement to display under the error message. It wasn't the cause at all,
it was a previous statement. After I eliminated that, it was easy to narrow it down.

Thanks

Dynamic Queries Errors in Report Manager

I am trying to replicate the use of Dynamic Queries in SQL Server 2000
Reporting Services documented in the "Hitchhiker's Guide to SS 2000 RS."
I have done everything the book has told me to do. I can build and run the
project in test mode, and deploy to my test server
(http:\\localhost\ReportServer) without error.
I have already set up the datasouce for the application in the Report Server
when I try to run the deployed project. Every time I run the project I get
the following:
1) An error has occurred during report processing. (rsProcessingAborted) Get
Online Help
2) Query execution failed for data set 'dsCustomers'.
(rsErrorExecutingCommand) Get Online Help
3) Line 1: Incorrect syntax near '='.
I think it's pointing at my Data set in my data tab, but am not sure why it
will point there when it worked in test mode!
Does anyone know how to fix this problem?Hello,
I am guessing that by dynamic queries you mean queries with parameters.
I am using them with a teradata db and to get the parameters to work in
the query I have to precede the query with =" and end it with ". To
insert the parameters you put "+Parameter!Name.Value+".
I hope this helps,
Lilja|||Lilja, first I want to thank you for lending a hand. I appreciate it very
much.
Second, I want to restate you comments in my own words. For a dynamic query
with Parameters, I will start my statement with an equals sign (=).
To the left of the equal sign there will be nothing. To the right of the
equals I need to put the equation (which calls an assembly outside of my
project) in between double quotes: ="proj.class.method()".
Whenever I want to add a parameter to the equation I drop out of the string,
and add the literal with a + before and after the parameter:
="proj.class.method("+Parameter!Name.Value+")"
When I run the project in local debug mode, it works fine. When I change
the settings to Debug, give it a path "http://localhost/ReportServer" and
deploy it, I still get the same error. Is there something else that could be
the problem?
My equation runs over into a second line. Does it need the VB underscore to
carry the line over? ' _ '
Thank you very much again.
KurT
"kisa" wrote:
> Hello,
> I am guessing that by dynamic queries you mean queries with parameters.
> I am using them with a teradata db and to get the parameters to work in
> the query I have to precede the query with =" and end it with ". To
> insert the parameters you put "+Parameter!Name.Value+".
> I hope this helps,
> Lilja
>

Dynamic Cursor/ Dynamic SQL Statement

I've looked up Books Online on Dynamic Cursor/ Dynamic SQL Statement.

Using the examples given in Books Online returns compilation errors. See below.

Does anyone know how to use Dynamic Cursor/ Dynamic SQL Statement?

James

-- SQL -----

EXEC SQL BEGIN DECLARE SECTION;
char szCommand[] = "SELECT au_fname FROM authors WHERE au_lname = ?";
char szLastName[] = "White";
char szFirstName[30];
EXEC SQL END DECLARE SECTION;

EXEC SQL
DECLARE author_cursor CURSOR FOR select_statement;

EXEC SQL
PREPARE select_statement FROM :szCommand;

EXEC SQL OPEN author_cursor USING :szLastName;
EXEC SQL FETCH author_cursor INTO :szFirstName;

--Error-------
Server: Msg 170, Level 15, State 1, Line 23
Line 23: Incorrect syntax near ';'.
Server: Msg 1038, Level 15, State 1, Line 24
Cannot use empty object or column names. Use a single space if necessary.
Server: Msg 1038, Level 15, State 1, Line 25
Cannot use empty object or column names. Use a single space if necessary.
Server: Msg 170, Level 15, State 1, Line 27
Line 27: Incorrect syntax near ';'.
Server: Msg 170, Level 15, State 1, Line 30
Line 30: Incorrect syntax near 'select_statement'.
Server: Msg 170, Level 15, State 1, Line 33
Line 33: Incorrect syntax near 'select_statement'.
Server: Msg 102, Level 15, State 1, Line 35
Incorrect syntax near 'author_cursor'.
Server: Msg 170, Level 15, State 1, Line 36
Line 36: Incorrect syntax near ':'.using the following seem to be achieving what i wanted.
but i would still like to know how to use
1. PREPARE
2. EXECUTE
i.e. under what circumstances would you use those 2?
It must be there for a reason.

James

--Working SQL-----

DECLARE @.sql nvarchar(4000)
SET @.sql = 'DECLARE @.name nvarchar(128) ' +
'DECLARE test_cursor CURSOR FOR SElECT name FROM class_category ' +
'OPEN test_cursor ' +
'FETCH NEXT FROM test_cursor INTO @.name ' +
'BEGIN ' +
'PRINT @.name ' +
'FETCH NEXT FROM test_cursor INTO @.name ' +
'END '
EXECUTE sp_executesql @.sql|||You seem to be confusing the VB environment with the Transact-SQL environment. The VB code executes only on the client side, and that is where a PREPARE might make sense. The Transact-SQL code executes only on the server side, and you can't explicitly prepare code there (at least under normal circumstances).

-PatP

Dy

I have to get a count of records using dynamic sql.
I have the following and I'm getting errors (Syntax error converting the
varchar value 'SELECT ' to a column of data type int.):
[Code]
DECLARE @.sCalldate varchar(10) , @.sAgentId varchar(10), @.sProject varchar(10
)
DECLARE @.SQL varchar(8000)
DECLARE @.RC int
SELECT @.sCalldate ='20050712'
SELECT @.sAgentid ='0'
SELECT @.sProject = 'A'
SELECT @.SQL = 'SELECT ' + @.RC + ' = Count(*)
FROM dbo.tmPunchTime
WHERE scalldate = ' + @.sCallDate +
' AND ISNULL(sRawLogout, ''x'') = ''x'''
IF @.sAgentId <> '0'
BEGIN
SELECT @.SQL = @.SQL + ' AND sAgentId = @.sAgentId '
END
IF @.sProject<> '0'
BEGIN
SELECT @.SQL = @.SQL + ' AND sProject= @.sProject'
END
EXEC (@.SQL)
If I put the @.RC inside the quotes I get error: Must declare the variable
'@.RC'.
I tested by printing the statement and it prints properly just errors when
executing.
How can I make this work?
Thanks,
Ninel
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200507/1EXEC() has its own scope, so has no idea what @.RC is (nor does it "see"
sAgentID or @.sProject).
Try getting rid of the dynamic SQL altogether:
SELECT @.RC = Count(*)
FROM dbo.tmPunchTime
WHERE scalldate = @.sCallDate
AND COALESCE(sRawLogout, 'x') = 'x'
AND sAgentID = CASE @.sAgentID WHEN 'O' THEN sAgentID ELSE @.sAgentID
END
AND sProject = CASE @.sProject WHEN 'O' THEN sProject ELSE @.sProject
END
Or see http://www.aspfaq.com/2492
"ninel gorbunov via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:51A8B62C943CC@.webservertalk.com...
> I have to get a count of records using dynamic sql.
> I have the following and I'm getting errors (Syntax error converting the
> varchar value 'SELECT ' to a column of data type int.):
> [Code]
> DECLARE @.sCalldate varchar(10) , @.sAgentId varchar(10), @.sProject
> varchar(10)
> DECLARE @.SQL varchar(8000)
> DECLARE @.RC int
> SELECT @.sCalldate ='20050712'
> SELECT @.sAgentid ='0'
> SELECT @.sProject = 'A'
> SELECT @.SQL = 'SELECT ' + @.RC + ' = Count(*)
> FROM dbo.tmPunchTime
> WHERE scalldate = ' + @.sCallDate +
> ' AND ISNULL(sRawLogout, ''x'') = ''x'''
> IF @.sAgentId <> '0'
> BEGIN
> SELECT @.SQL = @.SQL + ' AND sAgentId = @.sAgentId '
> END
> IF @.sProject<> '0'
> BEGIN
> SELECT @.SQL = @.SQL + ' AND sProject= @.sProject'
> END
> EXEC (@.SQL)
> If I put the @.RC inside the quotes I get error: Must declare the variable
> '@.RC'.
> I tested by printing the statement and it prints properly just errors when
> executing.
> How can I make this work?
> Thanks,
> Ninel
>
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200507/1|||I have a similar problem.
USE MyDataBase
GO
if exists
(select * from sysobjects where id =
object_id(N'[dbo].[sp_MTV_Diskalert]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_MTV_Diskalert]
GO
create procedure sp_MTV_diskalert (
@.LowerLimit INT
, @.LinkedServerName SYSNAME
, @.NumberOfEmailsToSend INT
, @.EmailAddress1 NVARCHAR(30)
, @.EmailAddress2 NVARCHAR(30))
AS
--
BEGIN
--
SET XACT_ABORT ON
SET NOCOUNT ON
--
DECLARE @.RCPT VARCHAR(500)
, @.LIMIT INT
, @.SQLSTRING NVARCHAR(4000)
, @.STRING2 NVARCHAR(4000)
, @.EMAIL NVARCHAR(4000)
, @.ParmDefinition NVARCHAR(4000)
, @.ParmIn NVARCHAR(4000)
, @.ParmRet NVARCHAR(4000)
, @.MyCounter int
, @.TempEmailAddress NVARCHAR(30)
--
set @.MyCounter = 0
--
--The following stmt works
--
exec (select @.EmailAddress1 as name)
--
while ( @.MyCounter <> @.NumberOfEmailsToSend )
Begin
SET @.MyCounter = @.MyCounter + 1
SET @.TempEmailAddress = '@.EmailAddress'+ltrim(str(@.MyCounter))
SET @.SQLString = N'Select ' +@.TempEmailAddress+ ' as name'
-- print @.SQLstring
EXECUTE sp_executesql
@.SQLString
,@.params = N'@.email nvarchar(30) output'
,@.email=@.email output
END
END
go
EXEC Sealedair.dbo.sp_SAC_diskalert
250,DUNSQL02,1,'Michael.varriale@.sealedair.com','2','3','4','5','6','7','8',
'9','10'
Must declare the variable '@.EmailAddress1'.