Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Run SQL Script using PHP Driver - Possible??

Answered Run SQL Script using PHP Driver - Possible??

  • Friday, November 27, 2009 6:06 PM
     
     
    Sign In to Vote
    0
    Hello all,

    Is there actually a way to run a SQL script (file.sql) using the PHP Driver?

    I have searched and searched and all I could get is using SQLCMD to run sql scripts, but I don't want to use SQLCMD - I actually swicthed from it since it was another program that was running unnecesailyy.

    Can I use sqlsrv_query to do this?

    Thanks all for any help


     

All Replies

  • Monday, November 30, 2009 10:56 AM
    Moderator
     
     Answered
    Sign In to Vote
    0
    No, but this should not be a tough job for you to retrieve the T-SQL statements from the file first. You can first read the T-SQL script from file.sql via the function fopen and then use the function sqlsrv_query to execute the T-SQL script. For example:
    $myFile = "file.sql";
    $fh = fopen($myFile, 'r');
    $tsql= fread($fh, filesize($myFile));
    fclose($fh);
    $serverName = "(local)";
    $connectionInfo = array("Database"=>"AdventureWorks");
    $conn = sqlsrv_connect( $serverName, $connectionInfo);

    /* Execute the query. */
    if( sqlsrv_query( $conn, $tsql))
    {
          echo "Statement executed.\n";
    }
    else
    {
          echo "Error in statement execution.\n";
          die( print_r( sqlsrv_errors(), true));
    }

    /* Free connection resources. */
    sqlsrv_close( $conn);




    Best regards,
    Charles Wang
    Please remember to mark the replies as answers if they help and unmark them if they provide no help
     
  • Thursday, December 24, 2009 7:57 PM
     
     
    Sign In to Vote
    0
    Sorry for the very late reply, but that is a fantastic idea! I will be using that.

    Seasons greetings all. :)
     
    Thanks,
    GMan
     
  • Wednesday, December 30, 2009 9:40 AM
     
     
    Sign In to Vote
    0
    There's a very useful php function, file_get_contents() , that reads a whole file into a string in one call.
     
  • Wednesday, February 15, 2012 1:17 AM
     
     
    Sign In to Vote
    0

    Hi, were you able to run your scripts? If so, how? I think there's a limit to running a single query using sqlsrv_query.

    I'm trying to run a script file that contains an ALTER PROC, but I get errors.

    Hope you still get to read this :)

    Rafa

     
  • Wednesday, February 15, 2012 1:11 PM
     
      Has Code
    Sign In to Vote
    0

    Some commands like ALTER PROC or CREATE PROC must be the first and only commands in a statement.

    If your statement works, it will. also work in sqlsrv_query.

    If you want to run several such commands in one statement you could try this:

    $tsql = <<<SQL
EXEC(?);
EXEC(?);
EXEC(?);
SQL;

$conn = sqlsrv_connect($server, $options);

$params = array(
'ALTER PROC myProc as ...',
'ALTER PROC myOtherProc as ...',
'CREATE PROC myNewProc as ...');

$stmt = sqlsrv_query($conn, $tsql, $params);

if ( $stmt )
{
    do 
    {
        // something...
    } while (sqlsrv_next_result($stmt)) ;
}

    Rob