错误的原因是BACKUP DATABASE
返回参考消息,并且您的存储过程具有多个结果集。我用测试脚本重现了您的错误(尽管在您的问题中我看不到您如何准备存储过程)。您可以尝试以下解决方案之一(我编写了简单的脚本,可以轻松地在函数中实现这些脚本)。
调用PDOStatement :: nextRowset来获取每个结果集(在这种情况下,需要再进行两次调用)
OUTPUT
在您的存储过程中使用参数。在这种情况下,您需要获取所有结果集,然后再获取输出参数的值。
存储过程:
create procedure [dbo].[sp_IWBackup]
as
begin
declare
@route varchar(500),
@answer int = 0
set nocount on
set @route = 'I:\route'+(replace((replace(convert(varchar, getdate(), 21),':',';')),'.',';'))+'Full.bak'
set @answer = 1
begin try
backup database databasename to disk = @route
end try
begin catch
set @answer = 0
end catch
select @answer as answer
end
PHP:
<?PHP
$server = 'server\instance,port';
$database = 'database';
$uid = 'uid';
$pwd = 'pww';
try {
$dbh = new PDO("sqlsrv:server=$server;Database=$database", $uid, $pwd);
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch( PDOException $e ) {
die("Error connecting to sql Server. ".$e->getMessage());
}
try {
$sql = "{CALL sp_IWBackup}";
$stmt = $dbh->prepare($sql);
$stmt->execute();
$stmt->nextRowset();
$stmt->nextRowset();
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
foreach ($row as $key => $value) {
echo $key.": ".$value."<br>";
};
}
} catch( PDOException $e ) {
die( "Error executing stored procedure: ".$e->getMessage());
}
$stmt = null;
$dbh = null;
?>
存储过程:
create procedure [dbo].[sp_IWBackupOut]
@answer int OUTPUT
as
begin
declare
@route varchar(500)
set nocount on
set @route = 'I:\route'+(replace((replace(convert(varchar, getdate(), 21),':',';')),'.',';'))+'Full.bak'
set @answer = 1
begin try
backup database databasename to disk = @route
end try
begin catch
set @answer = 0
end catch
end
PHP:
<?PHP
$server = 'server\instance,port';
$database = 'database';
$uid = 'uid';
$pwd = 'pww';
try {
$dbh = new PDO("sqlsrv:server=$server;Database=$database", $uid, $pwd);
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch( PDOException $e ) {
die("Error connecting to sql Server. ".$e->getMessage());
}
try {
$sql = "{CALL sp_IWBackupOut (?)}";
$stmt = $dbh->prepare($sql);
$answer = -1;
$stmt->bindParam(1, $answer, PDO::PARAM_INT|PDO::PARAM_INPUT_OUTPUT, PDO::sqlSRV_PARAM_OUT_DEFAULT_SIZE);
$stmt->execute();
do {
} while ($stmt->nextRowset());
echo "answer: ".$answer;
} catch( PDOException $e ) {
die( "Error executing stored procedure: ".$e->getMessage());
}
$stmt = null;
$dbh = null;
?>