SQLSTATE[IMSSP]: The active result for the query contains no fields.

[dipens]

I have this very simple stored procedure execution on my server setup (SQL Server 2012 on a Windows Server connected to Ubuntu 16.04 running Symfony 3.3 on PHP 7.1 using these drivers ofcourse)

$connection = $em->getConnection()->getWrappedConnection();
$stmt = $connection->prepare('StoredProcedureName @a= :a, @b= :b, @c= :c');
$stmt->execute(array(
    'a'=> 'a',
    'b'=> 'b',
    'c'=> 'c',
));
$stmt->nextRowset();
$result = $stmt->fetchAll();

This works fine when there are records being returned. but sometimes depending on the parameters, the stored procedure returns no records. That's when I get this error:

SQLSTATE[IMSSP]: The active result for the query contains no fields.

I have already looked into here, here, here, and in issues here too but couldn't find a solution. I have already set SET NOCOUNT ON in the beginning of the stored procedure.

Is there anything I am missing here or is this a bug?

[david-puglielli]

Hi @dipens, could you provide a complete repro script for this error, the stored procedure you are using, and parameters that trigger the error? Thanks

[dipens]

Sure @david-puglielli and I apologize for the delay in my reply. Here is the complete repro.

CREATE  PROCEDURE [dbo].StoredProcedureName @a int, @b nvarchar(50),@c nvarchar(50)
AS
SET NOCOUNT ON

DECLARE @d nvarchar(30), @f int, @e INT, @h INT
SELECT @d=d,@e=e, @f=f FROM tblA WHERE a=@a

If @d='d'
BEGIN

	DECLARE @g INT

	UPDATE tblA set 
	AStatus = 1,
	Where a = @a
		
	exec [dbo].AnotherStoredProcedure @a=@a

	exec [dbo].YetAnotherStoredProcedure @a=@a OUTPUT,@j='j'

	Update tblb set bStatus='b' where b=@b

	UpDate tblc set cStatus='c' WHERE c=@c

	exec [dbo].OneMoreStoredProcedure @g=@g,@f=@f,@e=@e,@h=@h OUTPUT,@b=@b,@c=@c,@j='j'

	SELECT @g as newPK, @h as hPK

END
ELSE
BEGIN
	UPDATE tblA set 
	AStatus = 1,
	Where a = @a
END
GO

The PHP script is the same as mentioned above.

As you can see in the stored procedure, there is an IF...ELSE condition in which the first IF returns one record at the end. This is where the method is working fine and everything is being returned as it should. However in the second part, there is nothing being returned except for the simple UPDATE statement.

Please let me know if any other information is required. Thank you for your time.

[david-puglielli]

Hi @dipens, I am having trouble reproducing this issue, and I suspect it's because I'm not using the correct table design. Could you please provide the table schema for the tables referenced in the stored procedure? Thanks.

[dipens]

Hello @david-puglielli I have tried this with a very basic test table as well and the issue still exists so I doubt its the table design that's causing this.

However, I do realize now that I posted code which was relevant to PHP but not Symfony (where I am facing this). This is my Symfony version of the code: (I apologize for this)

$rsm = new ResultSetMapping();
$rsm->addScalarResult('u', 'newPK', 'newPK');
$rsm->addScalarResult('u', 'PK', 'PK');
$query = $em->createNativeQuery('StoredProcedureName @a= :a, @b= :b, @c= :c', $rsm);
$query->setParameter('a', 'a');
$query->setParameter('b', 'b');
$query->setParameter('c', 'c');
$result = $query->getArrayResult();
var_dump($result);

And nevertheless, as requested, I am also providing you table design with the very barebone Stored Procedure:

Stored Procedure:

CREATE PROCEDURE [dbo].[TestStoredProc] @a nvarchar, @b nvarchar(50),@c nvarchar(50)
AS
SET NOCOUNT ON
DECLARE @d nvarchar(30)
SELECT @d=d FROM tblA WHERE a=@a
BEGIN
	If @d='d'
	BEGIN
			SELECT 1 as newPK, 1 as PK
	END
	ELSE
	BEGIN
		UPDATE tblA set 
		AStatus = 'a'
		Where a = @a
	END
END
GO

Table Structure:

CREATE TABLE [dbo].[tblA](
	[a] [nvarchar](50) NULL,
	[d] [nvarchar](50) NULL,
	[AStatus] [nvarchar](50) NULL,
	[id] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_tblA] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

I have tried with this barebone structure and I am able to reproduce the issue.

Please let me know if any more information is required. Thank you for your time and I apologize for the confusion above.

[david-puglielli]

Thank you @dipens, I have one more question for you. Does the issue occur without symfony? For example using the PHP snippet you provided in the first post?

Without using symfony, I am getting a different error, using the following:

$connection = new PDO( "sqlsrv:Server=$server;database=$db;" , $uid, $pwd);
$stmt = $connection->prepare('StoredProcedureName @a= :a, @b= :b, @c= :c');
$stmt->execute(array(
    'a'=> 'a',
    'b'=> 'b',
    'c'=> 'c',
));
print_r($stmt->errorInfo());
$stmt->nextRowset();
$result = $stmt->fetchAll();
print_r($result);

The output is the following:

Array
(
    [0] => 42000
    [1] => 8114
    [2] => [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Error converting data type nvarchar to int.
)
Array
(
)

The error message is from the print_r($stmt->errorInfo()); statement, and the empty array is the $result. Does this error message occur for you?

[dipens]

Hello @david-puglielli, I tried this and this is what I got:

Array
(
    [0] => 00000
    [1] => 
    [2] => 
)
Array
(
)

for both type of inputs (in the if loop I used @d='d' and @d = 'b' for the else part) which means no output was returned what so ever.

However while testing this if I removed the line $stmt->nextRowset(); it did return me the records for the if part correctly like this:

Array
(
    [0] => 00000
    [1] => 
    [2] => 
)
Array
(
    [0] => Array
        (
            [newPK] => 1
            [0] => 1
            [shipPK] => 1
            [1] => 1
        )

)

However when I tried to run the else part, it threw the same error again:

SQLSTATE[IMSSP]: The active result for the query contains no fields.

Could this line $stmt->nextRowset(); be the reason I am getting into this problem?

[david-puglielli]

Since there is only one row in the result set, calling nextRowset() is not expected to return anything, but I still cannot reproduce the error message SQLSTATE[IMSSP]: The active result for the query contains no fields. I can reproduce all your other results however.

Could you let us know what version of the PHP drivers are you using, and what version of the ODBC driver too?

[dipens]

Here is the driver information:

msodbcsql/xenial,now 13.0.1.0-1 amd64 [installed]
mssql-tools/xenial,now 14.0.1.246-1 amd64 [installed]
unixodbc-dev-utf16/xenial,now 2.3.1-2 amd64 [installed]
unixodbc-utf16/xenial,now 2.3.1-2 amd64 [installed,automatic]

[david-puglielli]

Curiously, I can get the error message when hitting the 'else' part of the procedure, but only if I do not call nextRowset.

What version of the PDO_SQLSRV driver are you using? You can find out by running php --ri pdo_sqlsrv.

[dipens]

Extension 'pdo_sqlsrv.' not present.

Not sure how that happened. All I did was follow instructions from here

However in my phpinfo() I get this version:

4.0.8.0

[david-puglielli]

I think you accidentally left the period at the end - php --ri pdo_sqlsrv

[dipens]

Sorry you are right.

This is the result

pdo_sqlsrv

pdo_sqlsrv support => enabled
ExtensionVer => 4.0.8.0

Directive => Local Value => Master Value
pdo_sqlsrv.log_severity => 0 => 0
pdo_sqlsrv.client_buffer_max_kb_size => 10240 => 10240

[david-puglielli]

Ah, I see you are using an old version. Your version does not include more recent changes to the behaviour when fetching result sets. Please download the latest preview release here or the latest production release here.

[dipens]

Sorry I didn't realize there was a new version out already.

I have updated to the new version, rebooted the server, restarted Apache already.

However, the issue still persists. i.e.
When I don't use, nextRowset() I get my results for the if part as expected. However for the else part I still get the same error.

SQLSTATE[IMSSP]: The active result for the query contains no fields.

No results whatsoever when using nextRowset().

[david-puglielli]

The behaviour you observe is identical to what I get now, but it is a bug - the error message is expected when the result set is empty, but the lack of error message after calling nextRowset() is a problem. We will address this in due course.

Thank you @dipens for letting us know about this!

[jansor]

Hi, maybe late question, I have gradually always updated on our dev-area to the latest version, since i updated to Latest Version this exact same error happens to us. When i downgrade back to the previous version, our system works fine.

Sorry for my English is not the best but, is this considered a bug and could you clarify "We will address this in due course."

[yukiwongky]

@dipens You're getting SQLSTATE[IMSSP]: The active result for the query contains no fields. because you're trying to fetchAll() in a null result set.

In the If @d='d' part of your stored procedure, the query you're running is SELECT 1 as newPK, 1 as PK. If you call fetchAll() now, you should retrieve an array('newPK' => 1, 'PK' => 1). If you call nextRowset(), the cursor will now move to the next result set, which is null since you don't have any query after SELECT 1 as newPK, 1 as PK. Now if you call fetchAll(), you'll get the error SQLSTATE[IMSSP]: The active result for the query contains no fields.

In the else part of your stored procedure, the query you're running is UPDATE tblA set AStatus = 'a' Where a = @a. Since you have SET NOCOUNT ON, an update will return a null result set. Again now if you're calling fetchAll(), you'll get the error SQLSTATE[IMSSP]: The active result for the query contains no fields.

So say you don't know whether or not the stored procedure will go to the SELECT query or the UPDATE query, and you don't want to get the error SQLSTATE[IMSSP]: The active result for the query contains no fields. You can use PDOStatement::columnCount() to check if there is anything in the result set before calling fetchAll(). For example:

if ($stmt->columnCount > 0) {
  $result = $stmt->fetchAll()
}

[dipens]

@v-kaywon I get what you said which is what I and @david-puglielli were discussing before. But however note that in my original code, I use Symfony, Doctrine, Dbal and the likes and not core PHP. You can find that comment here.

My original code looks something like this:

$rsm = new ResultSetMapping();
$rsm->addScalarResult('u', 'newPK', 'newPK');
$rsm->addScalarResult('u', 'PK', 'PK');
$query = $em->createNativeQuery('StoredProcedureName @a= :a, @b= :b, @c= :c', $rsm);
$query->setParameter('a', 'a');
$query->setParameter('b', 'b');
$query->setParameter('c', 'c');
$result = $query->getArrayResult();
var_dump($result);

In this case, I don't have an option to check, the if ($stmt->columnCount > 0). I have already posted this to doctrine at #6631 but according to them, the issue isn't with how doctrine is processing the records, but how the drivers are processing it.

Can you please look into when you have the time? Thanks.

[yukiwongky]

@dipens , may I ask you what behavior are you expecting? Let's say we're not using the stored procedure, and we are just running the query UPDATE tblA set AStatus = 'a' Where a = @a. So your code will look like this (please forgive me with any syntax error as I'm not farmiliar with Symfony).

$rsm = new ResultSetMapping();
$query = $em->createNativeQuery('UPDATE tblA set AStatus = 'a' Where a = @a', $rsm);
$query->setParameter('a', 'a');
$result = $query->getArrayResult();
var_dump($result);

What are you expecting the result to be? Since the query is just an update, the result set should be null, and you're trying to getArrayResult() from a null result set. Is it a wrong behavior to return an error when you're trying to getArrayResult() from a null result set? To me I think it is a correct behavior to return an error in this case.

If you're looking for a way to not return an error, I advise you to change your stored procedure to something like the following:

CREATE  PROCEDURE [dbo].StoredProcedureName @a int, @b nvarchar(50),@c nvarchar(50)
AS
SET NOCOUNT ON

DECLARE @d nvarchar(30), @f int, @e INT, @h INT
SELECT @d=d,@e=e, @f=f FROM tblA WHERE a=@a

If @d='d'
BEGIN

	DECLARE @g INT

	UPDATE tblA set 
	AStatus = 1,
	Where a = @a
		
	exec [dbo].AnotherStoredProcedure @a=@a

	exec [dbo].YetAnotherStoredProcedure @a=@a OUTPUT,@j='j'

	Update tblb set bStatus='b' where b=@b

	UpDate tblc set cStatus='c' WHERE c=@c

	exec [dbo].OneMoreStoredProcedure @g=@g,@f=@f,@e=@e,@h=@h OUTPUT,@b=@b,@c=@c,@j='j'

	SELECT @g as newPK, @h as hPK

END
ELSE
BEGIN
	UPDATE tblA set 
	AStatus = 1,
	Where a = @a
	
	SELECT 'updated'
END
GO

Now the ELSE block of you're stored procedure actually has a result set which is updated. This way when you call getArrayResult() you'll get updated in your result set and you know the ELSE path to taken in your store procedure.

[dipens]

Hello @v-kaywon In that case, I am not expecting anything. I am just expecting it to not return an error. On my old server where I had freeTDS installed, this wasn't an issue.

The workaround that you showed, I could always do that. But I have a lot of stored procedures to go through to change this because earlier I didn't need to worry about returning something and freeTDS would not return me any error if there were no records returned.

[yukiwongky]

@dipens In the PDO_SQLSRV driver, we can set the ERRMODE to SILENT (PDO::ERRMODE_SILENT) to ignore any error. However from a quick search I see that Doctrine sets the ERRMODE to EXCEPTION by default, and there is no way to change it back to SILENT. If you cannot change you stored procedure then perhaps you can try to implement your own error handler so the error doesn't affect your script execution?

Please understand that different drivers behave differently and different customers want different features. You may expect this to not return an error but others may. Our driver has many API's that allow you to workaround your problem but it is unfortunate that Doctrine does not support them.

[dipens]

@v-kaywon Fair enough. Now atleast that I know the problem and workaround for this, and since its not a bug, the only option that I have is handling it on the Stored Procedure level.

Thanks for your time. Closing this issue.

[diegoolipa]

        $dbh = DB::connection()->getPdo();
        $sth = $dbh->prepare("SET NOCOUNT ON; DECLARE @Mensaje NVARCHAR(200) = 'Siu'; DECLARE @Error INT = 0;  EXEC [CrearPersonaUno] '$request->nombre', @Mensaje OUTPUT, @Error OUTPUT; SELECT @Mensaje AS Mensaje, @Error AS Error");
        $sth->execute();
        $resultado = $sth->fetchAll();
        dd($resultado );

The trick is to use "SET NOCOUNT ON" before calling the procedure. I found this here