PHP + SQL - 使用SQL Server的“创建到”脚本通过PHP创建表

Question

In SQL Server you seem to be able to get the CREATE TABLE script by right clicking a table, 'Script Table As...' and selecting 'Create To'. I've copied the output script and while it does seem to work and create the table when pasted onto the 'New Query' window, it does not work when used in PHP. Example code:

$sqlcommand = 
"CREATE TABLE [dbo].[departments](
    [department_pk_fk] [varchar](25) NOT NULL,
    [des] [varchar](100) NULL,
    CONSTRAINT [PK_departamentos] PRIMARY KEY CLUSTERED
(
    [department_pk_fk] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
    ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
";
$site_api->execQuery($sqlcommand);

Anyone have any useful input about why this would happen?

Edit: execQuery code:

public function execQuery($q,$scalar=FALSE,$returns=FALSE) {  
//return an assoc array or a scalar
    global $Cfg, $site_api;

    $this->query=$q;


    try {
      $result_array = array();
        $result = $this->conn->query($this->query);
        if (is_bool($result)===TRUE) {  //if reslt is true or false then there is nothing to do
            return $result;
        } else {
            if (!$scalar) {
                if ($result->rowCount()==0)
                    return null;
                //SQLSRV
                while($row = $result->fetchAll(SQLSRV_FETCH_ASSOC)) {
                    $result_array[]=$row;
                }
                $result->closeCursor();
                if($returns) return $result_array[0];
            } else {
                $row = $result->fetchAll(SQLSRV_FETCH_NUMERIC);
                if($returns) return $row[0];
            }
        }
    }
    catch (Exception $e) {
        print "Error Code <br>".$e->getCode();
        print "Error Message <br>".$e->getMessage();
        print "Strack Trace <br>".nl2br($e->getTraceAsString());
    }
}`

Answer 1

Your CREATE statement is correct, just remove the keyword GO from your script. The GO keyword is not part of T-SQL, it is SQL Server utilitiy statement.

Because I don't know how your class is written, I've made a simple script for test using PHP Driver for SQL Server (SQLSRV_FETCH_ASSOC constant is specific for this driver). It works correct without GO statement and gives error with this statement (PHP 7.1 and SQL Server Express 2012).

<?php
$server = 'server\instance,port';
$cinfo = array(
    "Database"=>'database'
);

$conn = sqlsrv_connect($server, $cinfo);
if( $conn === false )
{
    echo "Error (sqlsrv_connect): ".print_r(sqlsrv_errors(), true);
    exit;
}

$sql = 
    "CREATE TABLE [dbo].[departments](
        [department_pk_fk] [varchar](25) NOT NULL,
        [des] [varchar](100) NULL,
        CONSTRAINT [PK_departamentos] PRIMARY KEY CLUSTERED
        ([department_pk_fk] ASC) WITH (
            PAD_INDEX = OFF, 
            STATISTICS_NORECOMPUTE = OFF, 
            IGNORE_DUP_KEY = OFF, 
            ALLOW_ROW_LOCKS = ON, 
            ALLOW_PAGE_LOCKS = ON
        ) ON [PRIMARY]
    ) ON [PRIMARY]";
$stmt = sqlsrv_query($conn, $sql);
if( $stmt === false ) {
    echo "Error (sqlsrv_query): ".print_r(sqlsrv_errors(), true);
    exit;
}

sqlsrv_free_stmt($stmt);
sqlsrv_close($conn);

echo 'OK'."</br>";
?>

If you don't know how the SQL statement is verified, try to remove schema name (dbo), brackets for object names ([]), because there can be some additional syntax corrections in class method.

Other things, that can cause problems are permissions.

You can always use sqlsrv_errors() to get additional information about specific errors.