PRB: Invalid Return from SQL Server ODBC Driver Version 3.70.0623

ID: Q237290

The information in this article applies to:

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:

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.


    4. Of these, the first workaround is most favorable. Developers may need to write data from memo fields, and adding a "SELECT 1" to the SQL stored procedure will force it to return a 1 (success) each time it is called.


STATUS

This behavior is by design.


MORE INFORMATION

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. 
LOCAL lnHandle, lcString, lnRetVal
lnHandle = SQLCONNECT ()
IF (lnHandle < 0)
	MessageBox (MESSAGE(), 16, [Error!])
	RETURN
ENDIF
WAIT WINDOW [Creating Table test_table...] NOWAIT
lcString = [DROP TABLE test_table]
lnRetVal = SQLEXEC (lnHandle, lcString)
IF (lnRetVal < 0)
	=MessageBox ([The table does not exist. Press OK to create it], 16, [Error!])
ENDIF
lcString = [CREATE TABLE test_table (int_column int, text_column text)]
lnRetVal = SQLEXEC (lnHandle, lcString)
IF (lnRetVal < 0)
	=MessageBox (MESSAGE(), 16, [Error!])
	RETURN
ENDIF

*-- Create a stored procedure to insert data into the test table created
*-- above
WAIT WINDOW [Creating Stored Procedure test_table_proc...] NOWAIT
lcString = [DROP PROCEDURE test_table_proc]
lnRetVal = SQLEXEC (lnHandle, lcString)
IF (lnRetVal < 0)
	=MessageBox ([The procedure does not exist. Press OK to create it], 16, [Error!])
ENDIF
lcString = [CREATE PROCEDURE test_table_proc (@int_column int, @text_column text) AS ]
lcString = lcString + [BEGIN INSERT INTO test_table VALUES (@int_column, @text_column) END]
lnRetVal = SQLEXEC (lnHandle, lcString)
IF (lnRetVal < 0)
	=MessageBox (MESSAGE(), 16, [Error!])
	RETURN
ENDIF
WAIT CLEAR
=SQLDISCONNECT (lnHandle)
  2. Run the following code to reproduce the error (select the same DSN as in step 1, when prompted): 


    3. 
LOCAL lnHandle, lcString, lnRetVal
lnHandle = SQLCONNECT ()
IF (lnHandle < 0)
	MessageBox (MESSAGE(), 16, [Error!])
	RETURN
ENDIF

*-- Create front-end cursor to store data
CREATE CURSOR data_for_table (int_column int, text_column memo)
INSERT INTO data_for_table VALUES (1, 'Sample Data')

*-- Send data to the server
lcString = [{CALL test_table_proc (?data_for_table.int_column, ?data_for_table.text_column)}]
lnRetVal = SQLEXEC (lnHandle, lcString)
IF (lnRetVal < 0)
	MessageBox (MESSAGE(), 16, [Error!])
ENDIF
SQLDISCONNECT (lnHandle )
    This code should cause the following error:
    Connectivity error: Unable to retrieve specific error information. Driver is probably out of resources

© 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