uanble to run the query

[gshilpa24]

Hi Folks,
I have a query written and embedded in SQL and attached to a query in report studio. I am trying to run the query in the database to get the output.
i am getting a syntax error as shown below. :(

SET @RefGlobalDate = # prompt('TRADate','SMALLDATETIME')#
set @Manager ='%'+ # prompt('Manager','char(12)')# +'%'

I get the error as incorrect syntax near prompt. I am nt very much aware of the syntax in Microsoft SQL Server 2005. If any of you have worked with this database please let me know what is the correct way of passing parameters. Parameters passed here are Date and manager_id which is character type data.

please help me.

Thanks in advance.

[technomorph]

I'm not clear as to where exactly you're running the SQL. Report Studio, the database? Either way, the code you've written will probably fail because you won't be able to use #prompt...# in SQL Server, and I doubt you can directly reference SQL Server variables from within Report Studio.

Maybe this will help:

http://www-01.ibm.com/support/docview.wss?uid=swg21367642

[gshilpa24]

Hi,

Thank you for the help. I am running this query in SQL Server 2005 management studio database.
I cud run the query by hardcoding the date and the manager id.

Thank you.
Sg

[cognostechie]

Like technomorph said, the database would not recognise the 'Prompt' because the 'Prompt' is a Cognos Macro. When you use the Prompt in Cognos, Cognos takes the value of the Prompt from the user and hard codes the value in the filter condition of the SQL, then sends the SQL to the databse for execution.