Using a temporary table with sqlsrv_query function in PHP yields no results
php, sql-server
Solution
It's been a while but in case someone is still having this problem: all you have to do is to call SET NOCOUNT ON at the beginning of the stored procedure. This will prevent the "rows affected" messages to be sent to the client and be mistaken for the output of the procedure.
Problem
I have a long query for SQL Server 2005 that I'm attempting to find the result set for using the sqlsrv_query function in PHP. Here's my example query. I've removed the variable declarations and SET statements as well as most of the WHEN conditions in the CASE statements for brevity. ``` DECLARE @TotalTable TABLE ( SourceName varchar(50), GrossRevenue float, TotalOrderCount int, FBAOrderCount int, PostageExpense float, MarketplaceFees float, PickupExpense float, COGS float, AvgOrder float ); INSERT @TotalTable(SourceName,GrossRevenue,TotalOrderCount,FBAOrderCount,PostageExpense,MarketplaceFees,PickupExpense,COGS,AvgOrder) SELECT cl.Name, SUM(oi.Price) + SUM(oi.NativeShippingFee), COUNT(*) AS 'Order Count', SUM(CASE WHEN o.ActualPostage = '0.00' THEN 1 ELSE 0 END) AS 'FBA Orders', SUM(o.ActualPostage) AS 'Postage Expense', CAST(SUM(CASE WHEN o.ActualPostage = '0.00' AND mcd.VisibleName = 'Amazon US' THEN --FBA order on Amazon (pt.weight * @FBAWeightCost) + @FBAPickPackCost + (oi.Price * @AmzComm) + @AmzVcf ELSE 0 END) AS DECIMAL(10,2)) AS 'Marketplace Fees', CASE WHEN cl.Name = 'Source - Monroe County Recycler' THEN (@PickupMonroeCounty * @TimesToMonroeCounty) * @JohnPerMile ELSE 0 END AS 'Pickup Expense', CASE WHEN cl.Name = 'Source - Monroe County Recycler' THEN @TotalCostMonroeCounty ELSE 0 END AS 'COGS', CAST(ROUND(SUM(oi.Price) / COUNT(*),2) AS DECIMAL(10,2)) AS 'Avg Order' FROM [Order] o JOIN [OrderItem] oi ON oi.OrderId = o.Id JOIN [InventoryItem] ii ON ii.Id = oi.InventoryItemId JOIN [ProductsTraits] pt ON pt.ASIN = ii.ASIN JOIN [Classifier] clr ON clr.InventoryItemId = ii.Id JOIN [Classification] cl ON cl.Id = clr.ClassificationId JOIN [MarketConfigurationData] mcd ON mcd.MarketHandle = o.MarketHandle WHERE cl.Name LIKE 'Source - %' AND o.Status = '2' GROUP BY cl.Name ORDER BY SUM(oi.Price) + SUM(oi.NativeShippingFee) DESC SELECT SourceName, GrossRevenue, TotalOrderCount, FBAOrderCount, PostageExpense, MarketplaceFees, COGS, PickupExpense, AvgOrder, GrossRevenue - PostageExpense - MarketplaceFees - COGS - PickupExpense AS 'Profit' FROM @TotalTable ORDER BY 'Profit' DESC ``` This query when run in SQL Server Management Studio display 19 rows of data as expected. However, when run via this PHP code I get no results. ``` $sSql = <<<EOD THIS IS WHERE THAT LONG QUERY STRING GOES EOD; $oConn = connectDb(SERVER,USER,PASSWORD); $result = sqlsrv_query($oConn,$sSql) or die(print_r(sqlsrv_errors(), true)); print_r(sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)); ``` I've confirmed I can get test results to display when running simple queries via this method so it has something to do with how PHP is passing the query to SQL. Any thoughts?