解释:
您正在使用 PHP Driver for SQL Server,因此以下解释是解决您问题的另一个选项:
- 看起来这个问题可能是驱动程序特定的问题。SQL Server 使用批处理填充连接的输出缓冲区,其中包含由批处理创建的结果集 - 例如有关受影响行数的信息(例如
INSERT\DELETE\UPDATE
语句)或进度状态(从BACKUP\RESTORE DATABASE
语句返回)。这些结果集必须由 PHP 脚本处理。似乎这种行为是设计上的,PHP 脚本应该刷新所有未处理的结果集。在获取结果集之后,SQL Server 完成批处理的执行。您需要使用的适当函数/方法是 sqlsrv_next_result()
(用于驱动程序的 SQLSRV 版本)和 PDOStatement::nextRowset()
(用于驱动程序的 PDO_SQLSRV 版本)。
- 对于驱动程序的 SQLSRV 版本,您需要使用
sqlsrv_configure("WarningsReturnAsErrors", 0);
更改错误和警告处理方式。
示例:
我可以重现问题,并提供以下解决方案:
使用 SQLSRV 版本的驱动程序:
<?php
// Server information
$server = "server\instance,port";
$database = "database";
$uid = "username";
$pwd = "password";
// Configuration
sqlsrv_configure("WarningsReturnAsErrors", 0);
// Connection
$cinfo = array(
"UID" => $uid,
"PWD" => $pwd,
"Database" => $database
);
$conn = sqlsrv_connect($server, $cinfo);
if ($conn === false) {
echo "Unable to connect.";
die( print_r( sqlsrv_errors(), true));
}
// Statement
$sql = "
DECLARE @date VARCHAR(19)
SET @date = CONVERT(VARCHAR(19), GETDATE(), 126)
SET @date = REPLACE(@date, ':', '-')
SET @date = REPLACE(@date, 'T', '-')
DECLARE @fileName VARCHAR(100)
SET @fileName = ('d:\backup\BackUp_' + @date + '.bak')
BACKUP DATABASE dbname
TO DISK = @fileName
WITH
FORMAT,
STATS = 1,
MEDIANAME = 'SQLServerBackups',
NAME = 'Full Backup of dbname';
";
$stmt = sqlsrv_query($conn, $sql);
if ($stmt === false) {
echo "Unable to execute query.";
die( print_r( sqlsrv_errors(), true));
}
// Clear buffer
while (sqlsrv_next_result($stmt) != null){};
echo "Success";
// End
sqlsrv_free_stmt($stmt);
sqlsrv_close($conn);
?>
使用 PDO_SQLSRV 版本的驱动程序:
<?php
// Server information
$server = "server\instance,port";
$database = "database";
$uid = "username";
$pwd = "password";
// Connection
try {
$conn = new PDO("sqlsrv:server=$server;Database=$database", $uid, $pwd);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch( PDOException $e ) {
die( "Error connecting to SQL Server".$e->getMessage());
}
// Statement
$sql = "
DECLARE @date VARCHAR(19)
SET @date = CONVERT(VARCHAR(19), GETDATE(), 126)
SET @date = REPLACE(@date, ':', '-')
SET @date = REPLACE(@date, 'T', '-')
DECLARE @fileName VARCHAR(100)
SET @fileName = ('d:\backup\BackUp_' + @date + '.bak')
BACKUP DATABASE dbname
TO DISK = @fileName
WITH
FORMAT,
STATS = 1,
MEDIANAME = 'SQLServerBackups',
NAME = 'Full Backup of dbname';
";
try {
$stmt = $conn->prepare($sql);
$stmt->execute();
} catch (PDOException $e) {
die ("Error executing query. ".$e->getMessage());
}
// Clear buffer
try {
while ($stmt->nextRowset() != null){};
echo "Success";
} catch (PDOException $e) {
die ("Error executing query. ".$e->getMessage());
}
// End
$stmt = null;
$conn = null;
?>
sqlsrv_query
会被存储过程返回的行数和进度消息所困扰。尝试在存储过程顶部添加set nocount on
,如果这不起作用,则尝试切换到sqlsrv_prepare
而不是sqlsrv_query
。 - AlwaysLearning