You need to enable JavaScript to run this app.
优惠活动
大模型
产品
解决方案
定价
更多

跨环境下SQL_SRV查询结果不一致问题排查求助

avatar
开发者阿泽
2026-6-23
PHP sqlsrv查询远程机器返回结果不完整且不稳定，本地/SSMS运行正常

我在远程机器部署了PHP脚本，用于从Microsoft SQL Server拉取数据插入MySQL，配置cron每小时同步。但无论命令行还是cron运行脚本，SQL Server查询结果总是不完整，相同参数下返回行数每次都不一样；但在SQL Server Management Studio或本地测试脚本中运行相同查询，结果完全正确。

已排查的方向：

  • 用ini_set()设置max_execution_time=0、max_input_time=0、memory_limit=-1，无改善
  • 远程机器未安装防火墙，出站连接完全开放，排除网络入站限制问题
  • 查阅过类似问题方案，但均无效或不适用

本地测试脚本 

<?php

$connection_info = array(
    "UID"=>"UserID",
    "PWD"=>"password",
    "Database"=>"database_name"
);
$serverName = 'database_host';
try{
    $conn = sqlsrv_connect($serverName, $connection_info);
} catch(Exception $e){
    die("Not able to connect.");
}

echo "Connected!\n";

$sql = "
SELECT  
    SP.SaleInvoiceID,   
    SP.AssociationNumber,
    SP.Priority,    
    S.Abbreviation,
    GP.GlobalProductID,
    SI.OrderEntryID,
    SI.OriginalSaleInvoiceID,
    SI.AccountsReceivableID,
    SI.EmployeeID3,
    SI.InvoiceIDByStore AS [invoice],           
    (C.Customer_Name + ' ' + C.First_Name + ' ' + C.Last_Name) AS [customer],
    C.Id_Number as [customer_id],
    GP.ProductIdentifier AS [product_sku],
    RGP.ManufacturerPartNumber AS [model],
    CAST( L2.FieldText AS varchar ) AS [manufacturer],      
    CAST( L1.FieldText AS varchar ) AS [description],
    CAST( L3.FieldText AS varchar ) AS [category],
    SP.Quantity AS Qty, 
    SP.SerialNumber,
    SP.ContractNumber,
    SP.MobileNumber,
    GP.CategoryNumber,
    SP.UnitPrice,
    SP.UnitCost,
    SP.ListPrice,
    SI.SplitRate1,
    SI.SplitRate2,
    SI.EmployeeID1,
    SI.EmployeeID2,
    E1.Employee_Name AS [Employee1],
    E2.Employee_Name AS [Employee2],
    SI.DateCreated,
    SI.Comments 
FROM  [database_name].[dbo].[SaleInvoices] SI
INNER JOIN [database_name].[dbo].[SaleInvoicesAndProducts] SP
    ON SI.SaleInvoiceID = SP.SaleInvoiceID
INNER JOIN [database_name].[dbo].[iQclerk_GlobalProducts] GP
    ON SP.GlobalProductID = GP.GlobalProductID
INNER JOIN [database_name].[dbo].[LanguageTranslations] L1
    ON GP.ProductNameID = L1.ReferenceID
INNER JOIN [database_name].[dbo].[Stores] S
    ON S.StoreID = SI.StoreID1
INNER JOIN [database_name].[dbo].[Customer_Information] C
    ON SI.CustomerID1 = C.Id_Number
INNER JOIN [database_name].[dbo].[Employees] E1
    ON E1.Id_Number = SI.EmployeeID1    
INNER JOIN [database_name].[dbo].[Categories] CAT   
    ON CAT.CategoryNumber = GP.CategoryNumber
INNER JOIN [database_name].[dbo].[LanguageTranslations] L3
    ON CAT.CategoryPathID = L3.ReferenceID
LEFT OUTER JOIN [database_name].[dbo].[Employees] E2
    ON E2.Id_Number = SI.EmployeeID2
LEFT OUTER JOIN [database_name].[dbo].[RegularGlobalProducts] RGP
    ON RGP.GlobalProductID = GP.GlobalProductID 
LEFT OUTER JOIN [database_name].[dbo].[Manufacturers] M
    ON M.ManufacturerID = RGP.ManufacturerID
LEFT OUTER JOIN [database_name].[dbo].[LanguageTranslations] L2
    ON M.ManufacturerNameID = L2.ReferenceID
WHERE  SI.DateCreated
BETWEEN cast( '2024-03-04 00:00:00'  AS DateTime )
AND cast( '2024-05-09 23:59:59' AS DateTime )
AND L3.LanguageCode  = 'en-us'
";

$result = sqlsrv_query($conn, $sql, array(), array("Scrollable" => 'static'));
print("Data package received, retrieving data from package...\n");
$result_rows = array();
$i = 1;
while( $row = sqlsrv_fetch_array( $result, SQLSRV_FETCH_ASSOC ) ){
    print("Records retrieved: ".$i."\r");
    $i++;
    array_push($result_rows, $row);
}
print("\n");
print(sizeof($result_rows));

远程机器模块化脚本 

<?php
class SQLSRV{

    protected $dbConn;

    public function open_db($server, $user, $password, $database, $throwError=false)
    {
        global $_REGISTRY;
        ini_set("max_execution_time","0");
        ini_set("max_input_time","0");
        ini_set("memory_limit","-1" );

        $errorMsg =  "SQLSRV_ERROR: Cannot connect to SQL server database!\n";
        $serverName = $server;
        $connectionInfo = array(
            'UID' => $user,
            'PWD' => $password,
            'DATABASE' => $database
        );
        $this->dbConn = sqlsrv_connect($serverName, $connectionInfo);
        
        if (!$this->dbConn) {
            $_REGISTRY['STATE'] = 'FATAL';
            $_REGISTRY['ERROR_MESSAGE'] = 'DATABASE_CONNNECTION_ERROR';
            if($throwError) throw new \ErrorException( $errorMsg, 10001, 0,__file__, __line__ );
            else return $errorMsg;
        }
        return true;
    }


    public function query( $sql, $throwError=true )
    {
        
        if( !$sql ) return null;
        if(!$throwError) {
            sqlsrv_configure("WarningReturnAsErrors", 0);
        }
        $errorMsg = "SQLSRV_ERROR:  No database connection";    
        
        if(!$this->dbConn ) 
        {   
            if( $throwError) throw new \ErrorException( $errorMsg, 10001, 0,__file__, __line__ );
        else return $errorMsg;
        }

        $query = false;
        try { 
            $query = sqlsrv_query( $this->dbConn, $sql, array(), array("Scrollable" => 'static') );
        } catch( Exception $e ) {}
        
        $queryError = 'SQLSRV_ERROR: ' . sqlsrv_errors() . "\n\n<pre>$sql</pre>\n";
        if(!$throwError) {
            sqlsrv_configure("WarningReturnAsErrors", 1);
        }
        if (!$query) {   
            if($throwError) throw new \Exception( $queryError );
            else return $queryError; 
        } else {
            return $query;
        }
    }


    public function fetchData( $sql, $throwError=true )
    {
        $res = $this->query( $sql, $throwError );
        if($res) {
                $queryError = 'SQLSRV_ERROR: ' . sqlsrv_errors() . "\n\n$sql\n";
             file_put_contents('/var/tmp/sqlsrv.log', "fetchData Res: $queryError\n", FILE_APPEND );
             try{
                if(sqlsrv_num_rows($res)) {
                    $ar = array();
                    while( $row = sqlsrv_fetch_array( $res, SQLSRV_FETCH_ASSOC ) )
                    {
                        array_push($ar, $row);
                    }
                    return $ar;
                }
            } catch( \Exception $e ) { return false;}
            
        } else return false;
    }

}

$sql_srv_obj = new SQLSRV();
$sql_srv_obj->open_db('database_host', 'username', 'password', 'database_name');

$sql_query = "
SELECT  
    SP.SaleInvoiceID,   
    SP.AssociationNumber,
    SP.Priority,    
    S.Abbreviation,
    GP.GlobalProductID,
    SI.OrderEntryID,
    SI.OriginalSaleInvoiceID,
    SI.AccountsReceivableID,
    SI.EmployeeID3,
    SI.InvoiceIDByStore AS [invoice],           
    (C.Customer_Name + ' ' + C.First_Name + ' ' + C.Last_Name) AS [customer],
    C.Id_Number as [customer_id],
    GP.ProductIdentifier AS [product_sku],
    RGP.ManufacturerPartNumber AS [model],
    CAST( L2.FieldText AS varchar ) AS [manufacturer],      
    CAST( L1.FieldText AS varchar ) AS [description],
    CAST( L3.FieldText AS varchar ) AS [category],
    SP.Quantity AS Qty, 
    SP.SerialNumber,
    SP.ContractNumber,
    SP.MobileNumber,
    GP.CategoryNumber,
    SP.UnitPrice,
    SP.UnitCost,
    SP.ListPrice,
    SI.SplitRate1,
    SI.SplitRate2,
    SI.EmployeeID1,
    SI.EmployeeID2,
    E1.Employee_Name AS [Employee1],
    E2.Employee_Name AS [Employee2],
    SI.DateCreated,
    SI.Comments 
FROM  [database_name].[dbo].[SaleInvoices] SI
INNER JOIN [database_name].[dbo].[SaleInvoicesAndProducts] SP
    ON SI.SaleInvoiceID = SP.SaleInvoiceID
INNER JOIN [database_name].[dbo].[iQclerk_GlobalProducts] GP
    ON SP.GlobalProductID = GP.GlobalProductID
INNER JOIN [database_name].[dbo].[LanguageTranslations] L1
    ON GP.ProductNameID = L1.ReferenceID
INNER JOIN [database_name].[dbo].[Stores] S
    ON S.StoreID = SI.StoreID1
INNER JOIN [database_name].[dbo].[Customer_Information] C
    ON SI.CustomerID1 = C.Id_Number
INNER JOIN [database_name].[dbo].[Employees] E1
    ON E1.Id_Number = SI.EmployeeID1    
INNER JOIN [database_name].[dbo].[Categories] CAT   
    ON CAT.CategoryNumber = GP.CategoryNumber
INNER JOIN [database_name].[dbo].[LanguageTranslations] L3
    ON CAT.CategoryPathID = L3.ReferenceID
LEFT OUTER JOIN [database_name].[dbo].[Employees] E2
    ON E2.Id_Number = SI.EmployeeID2
LEFT OUTER JOIN [database_name].[dbo].[RegularGlobalProducts] RGP
    ON RGP.GlobalProductID = GP.GlobalProductID 
LEFT OUTER JOIN [database_name].[dbo].[Manufacturers] M
    ON M.ManufacturerID = RGP.ManufacturerID
LEFT OUTER JOIN [database_name].[dbo].[LanguageTranslations] L2
    ON M.ManufacturerNameID = L2.ReferenceID
WHERE  SI.DateCreated
BETWEEN cast( '2024-03-04 00:00:00'  AS DateTime )
AND cast( '2024-05-09 23:59:59' AS DateTime )
AND L3.LanguageCode  = 'en-us'
";


$data = $sql_srv_obj->fetchData($sql_query);

排查建议

  • 更新sqlsrv驱动版本：远程机器的sqlsrv扩展版本可能存在bug，建议升级到官方最新稳定版，对比本地环境的驱动版本。
  • 移除静态游标配置：远程脚本中sqlsrv_query使用了"Scrollable" => 'static'，静态游标需要一次性加载全部结果集，在网络不稳定时容易出现截断。改为默认的向前游标（删除该参数），逐行读取即可满足同步需求。
  • 完善错误日志：当前日志中sqlsrv_errors()返回的是数组，直接拼接会显示为Array，改为序列化后写入日志：
    $errors = sqlsrv_errors();
$queryError = 'SQLSRV_ERROR: ' . json_encode($errors) . "\n$sql\n";
    这样能获取具体的错误信息，比如网络中断、数据类型转换问题。
  • 修正CAST长度：查询中CAST(L2.FieldText AS varchar)未指定长度，SQL Server默认varchar长度为30，会导致长文本截断，改为CAST(L2.FieldText AS varchar(MAX))，其他CAST字段同理。
  • 分批拉取数据：如果结果集过大，一次性拉取容易超时或内存溢出，改为按日期分批次查询，比如每次拉取1天的数据，循环处理。
  • 检查cron环境配置：cron运行时的PHP环境变量可能与命令行不同，在cron脚本开头添加php -i > /var/tmp/php_cron_env.log，对比命令行php -i的输出，确认sqlsrv相关配置一致。

内容的提问来源于stack exchange，提问作者Spencer Ke

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分)， 限时 9.9 元起

产品介绍相关文档
相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分)， 限时 9.9 元起

产品介绍相关文档
火山引擎 最新活动
全面开放
Doubao-Seedance-2.5全新模型升级

模型再升级,30秒超长叙事, 模态参考扩容

HOT
方舟Codingplan

模型自由，工具不限，最新支持 Deepseek-V4 系列、GLM-5.3 系列

HOT
方舟Agent plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列

HOT
新用户特惠专场

大模型19元起，Al应用9.9元畅享，新人首购爆款尽享优惠

最近更新时间：2026.06.23 21:15:53