PHP连接MS SQL执行查询无返回行，排查无果求助

老兄，我一眼就瞅到你代码里的关键问题——你的SQL语句开头加了USE NetworkInfo，但你在sqlsrv_connect的时候已经通过ConnectionInfo指定了Database参数，这直接导致了查询上下文的冲突！

为什么会出现这个问题？ #

当你用sqlsrv_connect指定目标数据库后，连接已经默认处于NetworkInfo库的上下文里了。这时候再在查询里写USE NetworkInfo，sqlsrv驱动偶尔会出现上下文切换异常，导致你的查询实际上没在正确的数据库里执行，自然就返回不了行。

分步解决办法 #

1. 移除多余的USE语句
   把你的$SQL_String改成这样，删掉开头的USE NetworkInfo：

   $SQL_String= "SELECT DISTINCT dbo.site.name AS Site, dbo.site.colloquial_name AS 'Colloquial Name', dbo.site.former_name AS 'Former Name', dbo.hrlocation.location_datis AS 'HR Site', dbo.site.address AS Address, dbo.site.city AS City, dbo.site.state AS State, dbo.site.zip AS ZIP, dbo.site.main_line AS 'Main Line', dbo.site.main_ext AS 'Main Ext' FROM dbo.site JOIN dbo.hrlocation ON dbo.site.id = dbo.hrlocation.site_id";
   

2. 修复行数获取的问题（可选但实用）
   你代码里用sqlsrv_num_rows($stmt)获取行数，但默认的sqlsrv游标类型（SQLSRV_CURSOR_FORWARD）不支持这个函数，所以会返回空值。如果需要准确拿到行数，要在sqlsrv_query里指定可滚动的游标：

   $stmt = sqlsrv_query( $conn, $SQL_String, array(), array("Scrollable" => SQLSRV_CURSOR_KEYSET));
   

3. 修正输出的小bug
   你代码里的echo 'Returned rows: True' . sqlsrv_has_rows($stmt) . '<br />';拼接逻辑错了，应该改成这样才能正确显示是否有行返回：

   echo 'Returned rows: ' . (sqlsrv_has_rows($stmt) ? 'True' : 'False') . '<br />';
   

修改后的完整代码示例 #

$ConnectionInfo = array( "Database"=>"NetworkInfo", "UID"=>$UID, "PWD"=>$PWD); 
$conn = sqlsrv_connect( $Instance, $ConnectionInfo); 

if( $conn ) { 
    echo "Connection established to $Instance. <br />"; 
} else{ 
    echo "Connection could not be established. <br />"; 
    print_r($ConnectionInfo); 
    die( print_r( sqlsrv_errors(), true)); 
} 

echo '<br />'; 

$SQL_String= "SELECT DISTINCT dbo.site.name AS Site, dbo.site.colloquial_name AS 'Colloquial Name', dbo.site.former_name AS 'Former Name', dbo.hrlocation.location_datis AS 'HR Site', dbo.site.address AS Address, dbo.site.city AS City, dbo.site.state AS State, dbo.site.zip AS ZIP, dbo.site.main_line AS 'Main Line', dbo.site.main_ext AS 'Main Ext' FROM dbo.site JOIN dbo.hrlocation ON dbo.site.id = dbo.hrlocation.site_id";

// 指定游标类型以支持行数获取
$stmt = sqlsrv_query( $conn, $SQL_String, array(), array("Scrollable" => SQLSRV_CURSOR_KEYSET)); 

echo '<h3>$stmt</h3>'; 
echo 'Var_dump:'; 
var_dump($stmt); 
echo '<br />'; 

if( $stmt === false ) { 
    echo "Error in statement preparation/execution.<br>"; 
    die( print_r( sqlsrv_errors(), true)); 
} else { 
    echo 'Returned type: ' . gettype($stmt) . '<br />'; 
    echo 'Returned rows: ' . (sqlsrv_has_rows($stmt) ? 'True' : 'False') . '<br />'; 
    echo 'Number of rows: ' . sqlsrv_num_rows($stmt) . '<br />'; 

    // 新增遍历结果的代码，方便查看数据
    while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC) ) {
        echo "Site: ".$row['Site']."<br>";
        echo "Colloquial Name: ".$row['Colloquial Name']."<br><br>";
        // 其他字段可按需添加输出
    }
}

这样调整后，你的查询应该就能正常返回那26条结果了！

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