SET NOCOUNT ON is not working under ubuntu

Question

I am using mssqlserver with yii framework I have created one stored-procedure please see below code.

 //Call Store procedure to get data
 $sql = "EXECUTE IESReportData @assessmentId=:assessmentId, @queId=:queId,@instanceId=:instanceId";
 //set database connection and start the yii query builder to be executed.
 $connection = Yii::app()->db;
 $command = $connection->createCommand($sql);
 $command->bindValue(":assessmentId", $assessmentId);
 $command->bindValue(":queId", "");
 $command->bindValue(":instanceId", "$instanceId");
 $Reportresults = $command->queryAll();

This works fine under ubuntu environment but it gives error below under windows environment.

Fatal error: Uncaught exception 'CDbException' with message 'CDbCommand failed to execute the SQL statement: SQLSTATE[IMSSP]: The active result for the query contains no fields.

with some R & D I found that we need to SET NOCOUNT ON so i have changed below statement

$sql = "SET NOCOUNT ON EXECUTE IESReportData @assessmentId=:assessmentId, @queId=:queId,@instanceId=:instanceId";

This works fine in window, but it provide null result under ubuntu environment.

Please help me.

Answer 1

below is example for setting nocount on in stored procedure

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
-- Add the parameters for the stored procedure here
<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,
<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END`enter code here`
GO