Stored Procedure fails when performing Update

Question

Hi everyone,

I'm using the SQL Server Driver for PHP to connect to an SQL Server 2008 Express. Right now, I'm trying to replace all SELECT, UPDATE and INSERT statements by stored procedures. This is working fine for SPs that just contain a SELECT statement. But now I tried to do one with an update, and I keep getting the error message "Executing SQL directly; no cursor.". I can call the SP fine from Management Studio with the same parameter values.

Any ideas?

Cheers Alex

EDIT: here's one update procedure. The funny part is, the procedure is actually executed fine and updates the data like it's supposed to. But it still returns an error, resulting in an exception.

First, the PHP code that fails:

if (! $this->Result = sqlsrv_query($this->Conn, $strQuery, $arrParameters, array("Scrollable"=>SQLSRV_CURSOR_STATIC)))
{
    $this->sendErrorMail($strQuery, $arrParameters);
    throw new Exception(4001);
}

SQL

USE [testsite]  
GO  
/****** Object:  StoredProcedure [dbo].[Items_countDownload]    Script Date: 09/09/2010 18:03:28 ******/  
SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  
-- =============================================  
-- Author:      Alexis Hildebrandt  
-- Create date: 2010-09-09  
-- Description: Increases the download count by 1  
-- =============================================  
ALTER PROCEDURE [dbo].[Items_countDownload]  
    @Id INT  
AS  
BEGIN  
    -- SET NOCOUNT ON added to prevent extra result sets from  
    -- interfering with SELECT statements.  
    SET NOCOUNT ON;  

    DECLARE @DownloadCount INT = 0, @MaxCount INT = 0, @Id2 INT = 0  

    DECLARE itemCursor CURSOR SCROLL
    FOR
        SELECT Id, Downloads
        FROM Items
        WHERE Id = @Id
        OR SKU IN 
        (
            SELECT SKU FROM Items WHERE Id = @Id
        )
    FOR UPDATE OF Downloads

    OPEN itemCursor

    FETCH NEXT FROM itemCursor
    INTO @Id, @DownloadCount;

    -- Find the largest Download count across all versions of the item
    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF @MaxCount < @DownloadCount
            SET @MaxCount = @DownloadCount;
        FETCH NEXT FROM itemCursor
        INTO @Id, @DownloadCount;
    END

    -- Increase the download count by one for all versions
    FETCH FIRST FROM itemCursor
    INTO @Id, @DownloadCount;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        UPDATE Items 
        SET Downloads = @MaxCount + 1
        WHERE CURRENT OF itemCursor
        FETCH NEXT FROM itemCursor
        INTO @Id, @DownloadCount;
    END

    CLOSE itemCursor;
    DEALLOCATE itemCursor;
END

Answer 1

look into the permissions: Executing SQL directly; no cursor

Answer 2

Try removing "Scrollable"=>SQLSRV_CURSOR_STATIC option from the call. The procedure you're calling doesn't return any open cursors to PHP code.