The query specification is incorrect

[krishna1478@gmail.com]

Hi,
when i validate the SQL in Cognos 8.1 Report studio(from Query explorer) for updating the records in SQL server 2005 it is showing the error like Query specification is incorrect(RQP-DEF-0192 At least one expression in the Select clause is missing the AS clause to make it a proper alias).
Hear,
1). I am directly connecting to the data base (Framework manager is not used)
2) In SQL server 2005 i wrote a stored proceder for updating the record

In cognos in Report studio Query explorer i wrote,

{ exec addemp1 #prompt('empid','integer')#,#prompt('Emp_name','varchar(30)')# }

to get the records and create the prompts.


And Stored procedure is

CREATE PROCEDURE empupdate
   (@empid int output,@Emp_name nvarchar(50) output)
AS
BEGIN
   
    update empdetails set Emp_name=@Emp_name where empid=@empid
    select empid=@empid,Emp_name=@Emp_name
END
GO

Please kindly help to solve this error ASAP.

Thanks,
Krishna

[krishna1478@gmail.com]

Hi,
This is again Krishna.

The Stored procedure Name and is empupdate and same name i used in SQL not addemp1.

Thanks,
krishna

[MFGF]

Hi,

Just to clarify - you are calling an update stored procedure from within a Report Studio report by hard-coding the call in SQL?

The report is expecting a result set to be returned in a projection list, with each returned item of data explicitly named.  You could try modifying the SQL to call the SP as a derived table within a query providing the projection list - something along the lines of

{Select SP.empid AS empid,
           SP.Emp_name as Emp_name
FROM (exec addemp1 #prompt('empid','integer')#,#prompt('Emp_name','varchar(30)')# ) SP) }

Not sure whether this would work, but possibly worth a try?

MF.

[krishna1478@gmail.com]

Thanks for you are replay...

But that is not my requirement.

I want to update data in database from a Report by running the stored procedure.

Thanks,
Krishna

[Nani16904]

Hi Krishna,

Did you get a solution for the problem you are facing here. i am facing same problem now.