PHP PDO Incorrect syntax near GO, MSSQL & sql_srv

Question

I am having trouble executing a long MSSQL script using PHP and PDO.

It contains some batch statements separated by GO. The script runs if its executed in Management Studio.

I have ensured line endings are not causing the issue.

I have also tried to enable beginTransaction() before the request is executed. Which returns the following error: SQLSTATE[IMSSP]: This function is not implemented by this driver.

I'm using IIS8 and PHP 5.4.16 and the pdo_sqlsrv driver

First part of the script:

USE foo;

IF object_id(N'ToBit', N'FN') IS NOT NULL
  DROP Function dbo.ToBit

GO


CREATE FUNCTION dbo.ToBit(
    @InputString varchar(250)
)
RETURNS BIT
AS BEGIN
    DECLARE @OutputBit BIT
    SET @OutputBit = CASE 
                 WHEN (@InputString = 'yes') THEN 1
                 WHEN (@InputString = 'true') THEN 1
                 WHEN (@InputString = '1') THEN 1
                 ELSE 0
              END
    RETURN @OutputBit
END

Is it down to the driver? I can't see why GO would require beginTransaction() being called? Other than that I'm out of ideas.

Update: I think I might have found an answer here. Will update if I find a soloutio. Incorrect syntax near 'GO'

score 3 · Accepted Answer · answered Jan 06 '17 at 14:06

Have found an answer here by Jon Galloway

GO isnt valid T-SQL, its a command used by SQLCMD and other utilities, and parsed before execution.

It looks like there are a few options.

1) Execute the script using OSQL / command line

2) Split the script at each GO separator, then run them in sequence

3) If using .NET you can look at using SQL Server Management Objects: Server.ConnectionContext.ExecuteNonQuery()

This parsers T-SQL statements and "gets" the GO statement as a batch separator.

PHP PDO Incorrect syntax near GO, MSSQL & sql_srv

1 Answers1