CODEWITHSUNDEEP
phpsql-serverstored-procedures
Luny
Luny

Reputation: 23

no result from stored procedure, when using temp table [mssql]

I have a stored proc in mssql. The SP works fine while executed directly. When i call it in PHP (PHP Version 5.5.13, ZF 1.12.20) i get no result just 

  General SQL Server error: Check messages from the SQL Server [4004] (severity 16)

When i remove the part with the temp table it works fine, but thats not an option for me, because i dont own the SP. I shorted the SP to become readable. It contains about 20 temp tables between 4 differnet SPs, but the problem occurs with every temp table. 

     $this->_connection = sqlsrv_connect($serverName, $connectionInfo);
     ...
     $this->_stmt = sqlsrv_prepare( $this->connection, 'EXECUTE spBtrgEPM9_test 104215, 37');
      ...
      $stmt->execute($aBindableSpParams);

            $aPlainResult = $stmt->fetchAll();

            $aResult = array();

            foreach ($aPlainResult AS $aRow) {
                $sRowClass = $this->getRowClass();

                if (!class_exists($sRowClass)) {
                    Zend_Loader::loadClass($sRowClass);
                }

                $aResult[] = new $sRowClass(array("data" => $aRow));
            }

            // Cache result
            self::$_aCache[$sSpSpec][$sMd5Hash] = $aResult;
        }

    return self::$_aCache[$sSpSpec][$sMd5Hash];

SP:

   ALTER proc [dbo].[spBtrgEPM9_test]
   @arbId int
  ,@voId int
  as begin

  set nocount on
  set dateformat dmy

  SET ANSI_WARNINGS ON
  SET ANSI_PADDING ON
  SET ANSI_NULLS ON
  SET QUOTED_IDENTIFIER ON
  SET ANSI_NULL_DFLT_ON ON
  SET CONCAT_NULL_YIELDS_NULL ON


   declare @xml_data xml


  Create table #fnDataTable
  (
   LastName varchar(250)
  ,FirstName varchar(250) 
  ,DateOfBirth date
  ,Gender int
  ,CustomerId int
  ,CustomerName varchar(250)
  ,CustomerNumber varchar(250)
  ,ContributionId int
  )

  insert into #fnDataTable(       
  LastName 
  ,FirstName 
  ,DateOfBirth 
  ,Gender 
  ,CustomerId 
  ,CustomerName 
  ,CustomerNumber 
  ,ContributionId 
  )
  exec CalcDB.dbo.GetSalesData @arbId,@voId


  declare @xmlValidierung varchar(max)

  Set @xmlValidierung = '<?xml version="1.0"?>'+ 
  convert(varchar(max),
   (select 
      (SELECT top 1 y.* FROM #fnDataTable y   FOR XML PATH('persondata'),       TYPE)

  FOR XML PATH(''), ROOT('rentenrechner')) 

  )



  select @xml_data= CONVERT(xml, @xmlValidierung)

    select   @xml_data as XML_DATA

I can not change the SP. I could maybe add some lines if there is a fast sollution.

Solution Part 1: I had two problems. I solved the problem with the temp tables. I had to explicit declare all fields as null. 

    Create table #fnDataTable
      (
       LastName varchar(250) null
      ,FirstName varchar(250)  null
      ,DateOfBirth date null
      ,Gender int null
      ,CustomerId int null
      ,CustomerName varchar(250) null
      ,CustomerNumber varchar(250) null
      ,ContributionId int null
      )

I still have the problem, that i cant handle the datatype XML

Upvotes: 2

Views: 658

Answers (1)

Mahantesh
Mahantesh

Reputation: 357

I also stuck in same problem, on lot of search I came to know that adding 'SET NOCOUNT ON' at the beginning and adding 'SET NOCOUNT OFF' at the last of the procedure, solved my problem.

Upvotes: 2

Related Questions