Visual FoxPro 9连接SQL Server多表左外连接时遇未识别短语/关键字错误

使用Visual FoxPro 9连接MS SQL Server时，执行5表左外连接查询触发错误：Command contains unrecognized phrase/keyword，原代码如下：

SQLEXEC(lnConn4,"SELECT [TIARA$Sales Invoice Header].No_,;
        [TIARA$Sales Invoice Header].[Sell-to Customer No_],;
        [TIARA$Sales Invoice Header].[Bill-to Name],;
        [TIARA$Sales Invoice Header].[Bill-to Address],;
        [TIARA$Sales Invoice Header].[Posting Date],;
        [TIARA$Sales Invoice Header].[VAT Registration No_],;
        [TIARA$Sales Invoice Header].[External Document No_],;
        [TIARA$Sales Invoice Header].[Shortcut Dimension 1 Code],;
        [TIARA$Sales Invoice Header].[Bill-to City],;
        [TIARA$Sales Invoice Line].No_,;
        [TIARA$Sales Invoice Line].Description,;
        [TIARA$Sales Invoice Line].[Unit of Measure],;
        [TIARA$Sales Invoice Line].Quantity,;
        [TIARA$Sales Invoice Line].[Unit Price],;
        [TIARA$Sales Invoice Line].[VAT %],;
        [TIARA$Sales Invoice Line].[Line Discount %],;
        [TIARA$Sales Invoice Line].[Amount Including VAT],;
        [TIARA$Sales Invoice Line].[Invoice Price],;
        [TIARA$Sales Invoice Line].Amount,;
        TIARA$Item.[Manufacturer Code],;
        TIARA$Customer.[Territory Code],;
        [TIARA$Sales Shipment Header].Note,;
        [TIARA$Sales Shipment Header].[Order No_],;
        [TIARA$Sales Shipment Header].No_;
    FROM    [TIARA$Sales Invoice Header];
    LEFT OUTER JOIN [TIARA$Sales Invoice Line];
    ON   [TIARA$Sales Invoice Header].No_ = [TIARA$Sales Invoice Line].[Document No_];
    LEFT OUTER JOIN TIARA$Item;
    ON  [TIARA$Sales Invoice Line].No_ = TIARA$Item.No_;
    LEFT OUTER JOIN TIARA$Customer;
    ON  [TIARA$Sales Invoice Line].[Sell-to Customer No_] = TIARA$Customer.No_;
    LEFT OUTER JOIN [TIARA$Sales Shipment Header];
    ON  [TIARA$Sales Invoice Header].[Order No_]     = [TIARA$Sales Shipment Header].[Order No_];
    WHERE   [TIARA$Sales Invoice Header].No_ = THISFORM.TEXT1.TEXT;
    ORDER BY [TIARA$Sales Invoice Header].[Posting Date] DESC;
    INTO TABLE  c:\tiarasys\temp\invoice")

错误原因 #

1. SQL Server不识别VFP本地命令INTO TABLE：INTO TABLE是VFP本地导出表的命令，不能嵌入到发送给SQL Server的查询语句中。
2. 直接引用VFP表单控件：THISFORM.TEXT1.TEXT是VFP的控件引用语法，SQL Server无法解析该表达式。
3. 多表未使用别名，代码冗余且易读性差。

修正后的代码 #

* 先将表单控件值存入变量
lcInvoiceNo = THISFORM.TEXT1.TEXT

* 执行参数化SQL查询，结果返回VFP临时游标
lnResult = SQLEXEC(lnConn4, "SELECT 
        h.No_,
        h.[Sell-to Customer No_],
        h.[Bill-to Name],
        h.[Bill-to Address],
        h.[Posting Date],
        h.[VAT Registration No_],
        h.[External Document No_],
        h.[Shortcut Dimension 1 Code],
        h.[Bill-to City],
        l.No_ AS LineNo,
        l.Description,
        l.[Unit of Measure],
        l.Quantity,
        l.[Unit Price],
        l.[VAT %],
        l.[Line Discount %],
        l.[Amount Including VAT],
        l.[Invoice Price],
        l.Amount,
        i.[Manufacturer Code],
        c.[Territory Code],
        s.Note,
        s.[Order No_] AS ShipmentOrderNo,
        s.No_ AS ShipmentNo
    FROM [TIARA$Sales Invoice Header] h
    LEFT OUTER JOIN [TIARA$Sales Invoice Line] l
        ON h.No_ = l.[Document No_]
    LEFT OUTER JOIN TIARA$Item i
        ON l.No_ = i.No_
    LEFT OUTER JOIN TIARA$Customer c
        ON l.[Sell-to Customer No_] = c.No_
    LEFT OUTER JOIN [TIARA$Sales Shipment Header] s
        ON h.[Order No_] = s.[Order No_]
    WHERE h.No_ = ?lcInvoiceNo
    ORDER BY h.[Posting Date] DESC", "curInvoice")

* 查询成功则导出为本地表
IF lnResult > 0
    SELECT curInvoice
    COPY TO c:\tiarasys\temp\invoice.dbf
ELSE
    * 输出错误详情
    AERROR(laError)
    MESSAGEBOX("查询失败：" + laError[2], 16, "错误提示")
ENDIF

修正说明 #

• 使用参数化查询?lcInvoiceNo传递控件值，既避免SQL注入，又解决SQL Server无法识别VFP控件引用的问题。
• 移除INTO TABLE，改用VFP本地命令COPY TO将查询结果游标导出为指定表。
• 给每张表设置别名（h、l、i、c、s），简化代码结构，提升可读性。
• 对重复字段名添加别名（如LineNo、ShipmentOrderNo），避免游标中字段名冲突。

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