Microsoft KB Archive/237290

-

The information in this article applies to:


 * Microsoft Visual FoxPro for Windows, versions 3.0, 3.0b, 5.0, 5.0a, 6.0
 * Microsoft ODBC Driver for SQL Server, version 3.7

-

SYMPTOMS
When executing a SQL Server stored procedure and passing parameters through SQL pass through, a return of -1 (error) might be received, even though the stored procedure is successfully executed. This occurs when writing data from a FoxPro MEMO field to a SQL TEXT column.

CAUSE
SQL Server Transact-SQL supports two methods of building SQL statements, at run time, in scripts, stored procedures, and triggers:


 * The EXECUTE statement, which does not support parameter substitution in the executed string.
 * The stored procedure sp_executesql, which does support parameter substitution in the executing string.

When parameter place holders are detected in the SQL statement passed to the SQL Server ODBC driver version 3.70, sp_executesql is called and the statement to be executed is passed as a parameter.

The SQL statement, passed as a parameter to sp_executesql is not compiled until the sp_executesql statement is executed. The contents of the statement are then compiled and executed as an execution plan separate from the execution plan of the batch that called sp_executesql. The sp_executesql batch cannot reference variables declared in the batch calling sp_executesql. Local cursors or variables in the sp_executesql batch are not visible to the batch calling sp_executesql, and changes in database context last only until the end of the sp_executesql statement.

The SQL Server driver version 3.70 uses sp_executesql for parameterized queries and there is no way to turn off the SQLPrepare logic of the driver. It tries to generate a temporary table, but the temp table releases outside of the context of the stored procedure.

RESOLUTION
There are three ways to address this issue:


 * 1) Use version 3.6 of the SQL Server ODBC driver.
 * 2) Define the field in the FoxPro table that is to be written to the SQL TEXT column as character 254, rather than MEMO.
 * 3) Add a "SELECT 1" to the SQL stored procedure.

STATUS
This behavior is by design.

Steps to Reproduce Behavior
This section assumes the user has at least a moderate working knowledge of SQL server and knows how to create an ODBC DSN.

NOTE: Before beginning, be sure to have a SQL Server 6.5 or later available and have version 3.70.0623 of SQLSRV32.DLL on the test client machine. Create a DSN to the PUBS sample database on the SQL Server.


 * 1) Run the following code to create the table and stored procedure on the SQL Server. Select a valid DSN created when prompted:
 * 2) Run the following code to reproduce the error (select the same DSN as in step 1, when prompted):

© Microsoft Corporation 1999, All Rights Reserved.

Contributions by Trevor Hancock, Microsoft Corporation

Additional query words:

Keywords         : kbSQL kbVFp300 kbVFp500 kbVFp500a kbVFp600 kbGrpFox kbGrpMDAC Version          : WINDOWS:3.0,3.0b,3.7,5.0,5.0a,6.0 Platform         : WINDOWS Issue type       : kbprb Last Reviewed: July 20, 1999