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
Hi,
This is again Krishna.
The Stored procedure Name and is empupdate and same name i used in SQL not addemp1.
Thanks,
krishna
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.
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
Hi Krishna,
Did you get a solution for the problem you are facing here. i am facing same problem now.