Microsoft KB Archive/240137

= You may receive a return of -1 from SQL Server ODBC Driver version 3.70.0623 in Visual FoxPro for Windows =

Article ID: 240137

Article Last Modified on 3/17/2005

-

APPLIES TO


 * Microsoft Visual FoxPro 3.0 Standard Edition
 * Microsoft Visual FoxPro 3.0b Standard Edition
 * Microsoft Visual FoxPro 5.0 Standard Edition
 * Microsoft Visual FoxPro 5.0a
 * Microsoft Visual FoxPro 6.0 Professional Edition
 * Microsoft ODBC Driver for Microsoft SQL Server 3.7

-



This article was previously published under Q240137



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 four ways to address this issue:
 * Use version 3.6 or version 2000.85.1117.00 of the SQL Server ODBC driver.
 * Define the field in the FoxPro table that is to be written to the SQL TEXT column as character 254, rather than MEMO.
 * Add a "SELECT 1" to the SQL stored procedure.
 * Use ASYNCHRONOUS processing.

Of these, the use of ASYNCHRONOUS processing is most favorable because:
 * Keeping the SQL driver at an earlier version may prove impossible as new products are installed that may update it.
 * Developers might need to write data from memo fields.
 * Adding a "SELECT 1" to the SQL stored procedure forces it to return a 1 (success) each time it is called, regardless of whether it actually works. Note that because of the way ASYNCHRONOUS processing works (FoxPro does not wait for an SQLEXEC to finish before continuing processing), code that follows an SQLEXEC command might fail if the code is dependant on the return of that call. A loop, checking for success of the SQLEXEC call, might be necessary.



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.



Steps to Reproduce Behavior
This section assumes that the user has at least an intermediate working knowledge of Microsoft SQL Server and Microsoft Visual FoxPro.

NOTE: Before beginning, be sure to have SQL Server 6.5 or later available and have version 3.70.0623 of SQLSRV32.DLL on the test client machine.  Copy the code in step 6 below into a new FoxPro program on a machine with Visual FoxPro and version 3.70.0623 of SQLSRV32.DLL. Adjust the FIRST #DEFINE, adding a valid SQL SERVER name. Run the program once. This creates a sample table (named TEST_TABLE) and a stored procedure (named TEST_TABLE_PROC) in the PUBS database on the Microsoft SQL Server. Change the "#DEFINE MAKE_SQL_CONTENT" to .F. and run the program again. This runs the test code only. Note the error that occurs. The browse window contains data from the server that was written there successfully, despite the error. Change the "#DEFINE ASYNC_2_USE" to .T. to turn on ASYNCHRONOUS processing.</li>  Re-run the program. Note that the error does not occur, as ASYNCHRONOUS is now set to .T. at the time the stored procedure is run. ************* START CODE *************** LOCAL llOldAsync, lnHandle, lcString, lnRetVal
 * 1) DEFINE SQLSERVER "<SERVER NAME HERE>"  &&  Substitute your own Microsoft SQL Server name here.
 * 2) DEFINE ASYNC_2_USE .F.
 * 3) DEFINE MAKE_SQL_CONTENT .T.

CLEAR =SQLSETPROP(0, 'asynchronous', .F.) && Set a default lnHandle = SQLSTRINGCONN("driver={sql server};server="+SQLSERVER+";database=pubs;uid=sa;pwd=") IF (lnHandle < 0) MESSAGEBOX (MESSAGE, 16, [Error!]) RETURN .F. ENDIF ? "Connection Successful"

IF MAKE_SQL_CONTENT =SQLEXEC (lnHandle, [DROP TABLE test_table]) lnRetVal = SQLEXEC (lnHandle, [CREATE TABLE test_table (int_column int, text_column text)]) IF (lnRetVal < 0) MESSAGEBOX (MESSAGE, 16, [Error!]) RETURN .F.   ENDIF ? "SQL Table Created" =SQLEXEC (lnHandle, [DROP PROCEDURE test_table_proc]) lcString = [CREATE PROCEDURE test_table_proc (@tnint_column int, @tttext_column text) AS ] lcString = lcString + [BEGIN INSERT INTO test_table VALUES (@tnint_column, @tttext_column) END] lnRetVal = SQLEXEC (lnHandle, lcString) IF (lnRetVal < 0) MESSAGEBOX (MESSAGE, 16, [Error!]) RETURN .F.   ENDIF ? "SQL Stored Procedure Created"
 * -- Create test_table on server in PUBS database
 * -- Create a stored procedure to insert data into the test table we just made.

ELSE

CREATE CURSOR data_for_table (int_column INT, text_column MEMO) INSERT INTO data_for_table VALUES (1, TTOC(DATETIME)) lcString = [{CALL test_table_proc (?data_for_table.int_column, ?data_for_table.text_column)}] llOldAsync = SQLGETPROP(lnHandle, 'asynchronous') SQLSETPROP(lnHandle, 'asynchronous', ASYNC_2_USE) lnRetVal = 0 lnRetVal = SQLEXEC (lnHandle, lcString) IF (lnRetVal < 0) MESSAGEBOX (MESSAGE, 16, [Error!]) ENDIF ? "Return Value from SQL Server data put: " + ALLT(STR(lnRetVal)) SQLSETPROP(lnHandle, 'asynchronous', llOldAsync) =SQLEXEC (lnHandle, 'select * from test_table', 'ccur') BROWSE NOWAIT
 * -- Create front-end cursor to store data
 * -- Send data to the server
 * !* May have to do this if you use async = .t. and return 0 occurs
 * !*    DO WHILE lnRetVal = 0
 * !*        lnRetVal  = SQLEXEC (lnHandle, lcString)
 * !*        IF (lnRetVal < 0)
 * !*            MESSAGEBOX (MESSAGE, 16, [Error!])
 * !*        ENDIF
 * !*        ?lnRetVal
 * !*    ENDDO

ENDIF =SQLDISCONNECT (lnHandle) This code should cause the following error when ASYNCHRONOUS process if set to .F.:
 * END CODE ***************

Connectivity error: Unable to retrieve specific error information. Driver is probably out of resources

</li></ol>

Keywords: kbsqlprog kbcodesnippet kbprb kbpending KB240137

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.