walshy002000 walshy002000 - 2 months ago 19
PHP Question

Syntax error when executing batch of statements via PDO but fine via SSMS

I'm writing code to create a MSSQL database from scratch using PHP PDO. I have the database created, and now need to execute a bunch of statements loaded from a .SQL file to create the tables, data, etc. The first few lines are

EXEC sp_dbcmptlevel 'myDbName', 120
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
BEGIN
EXEC [myDbName].[dbo].[sp_fulltext_database] @action = 'enable'
END
GO
ALTER DATABASE [myDbName] SET ANSI_NULL_DEFAULT OFF
GO
EXEC sp_dbcmptlevel 'myDbName', 120

...


If I run the .SQL file directly in SSMS, it runs perfectly. If I load it and execute it via PHP PDO, I get

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Incorrect syntax near 'GO'.' in ...


Looking at the profiler, my query is being prefaced/wrapped with

declare @p1 int
set @p1=NULL
exec sp_prepexec @p1 output,NULL,N'EXEC sp_dbcmptlevel ''myDbName', 120
...


and it appears that with the prepared execution wrapper, the server doesn't like it. I'm executing the script that I load from the SQL file with

$db = new PDO('sqlsrv:Server='.$serverName.';Database='.$databaseName, $username, $password);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$db->setAttribute(PDO::SQLSRV_ATTR_ENCODING, PDO::SQLSRV_ENCODING_UTF8);
$db->prepare($mySqlScript)->execute();


How can I fix the syntax error to execute the entire set of commands PHP loads from the SQL file?

Answer

You had to split your script by the "GO" clause into multiple statements, then run them one by one.

Something like:

$script = file_get_contents('script.sql');
$statements = explode( 'GO', $script );
foreach( $statements as $statement ) {
  // here execute the $statement
  $sth = $dbh->prepare($statement);
  $sth->execute();
}

This is what SSMS do internally, splits the statements by the "GO's" before execute them.