Microsoft KB Archive/177056

= The return parameter variable name for SQL Server causes an error in Visual FoxPro for Windows 7.0 or earlier versions =

Article ID: 177056

Article Last Modified on 3/19/2005

-

APPLIES TO


 * Microsoft Visual FoxPro 5.0 Standard Edition
 * Microsoft Visual FoxPro 5.0a
 * Microsoft Visual FoxPro 6.0 Professional Edition
 * Microsoft Visual FoxPro 7.0 Professional Edition

-



This article was previously published under Q177056



SYMPTOMS
You can call a Microsoft SQL Server stored procedure through the SQL Server ODBC Driver in Microsoft Visual FoxPro for Windows 7.0 or earlier versions. When you supply a single-character variable name for the return parameter or for the OUTPUT parameter, an error occurs.



RESOLUTION
Use a variable name with more than one character.

In the example below, change the Callsp.prg variable name Z to more than one character, for example, Z1.



STATUS
This behavior is by design.



MORE INFORMATION
With SQL ODBC driver version 3.50.0300 or later, the following error message is generated:

[Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification.

With SQL ODBC driver version 2.65.0240, the following error message is generated:

[Microsoft][ODBC SQL Server Driver]Error in assignment.

Steps to Reproduce Behavior
  Copy the following code into a file called Setup.prg.

Note The user,, must have permissions to perform these operations on the database. ********Start of Setup.prg*********** MyConnStr="'driver=sql server;server=myserver;uid=UserName;pwd=StrongPassword; "+ ; "database=Pubs'" *String to create the SQL Server Stored Procedure in Pubs SP_String="Create Proc KBtemp @Param1 char(20)OUTPUT " + ; "as Select @Param1='Return Success'" CREATE DATABASE SP_Test CREATE CONNECTION SP_Test CONNSTRING &MyConnStr MyConn=SQLCONNECT('SP_Test') IF MyConn > 0 then rSucc1=SQLEXEC(MyConn, SP_String) IF rSucc1 == -1 then MESSAGEBOX("Creating Stored Procedure Failed") AERROR(MyError) ?"Create Stored Proc Error: " DISPLAY MEMORY LIKE MyError ENDIF SQLDISCONNECT(MyConn) ELSE MESSAGEBOX("SQL Connection Failed") ENDIF *******End of Setup.prg************* Note Replace the server, user ID and password in MyConnStr with the appropriate server name, user ID, and password on your system.

  Copy the following code to a file called Callsp.prg: *******Start of CallSP.prg************ *Variable for the return parameter Z=SPACE(25) SP_Call="{Call KBTemp (?@Z)}" MyConn=SQLCONNECT('SP_Test') IF MyConn > 0 then rSucc1=SQLEXEC(MyConn,SP_Call) IF rSucc1<>-1 then MESSAGEBOX(Z) ELSE MESSAGEBOX("Calling Stored Procedure Failed") AERROR(MyError) ?"Calling Stored Proc Error: " DISPLAY MEMORY LIKE MyError ENDIF SQLDISCONNECT(MyConn) ELSE MESSAGEBOX("SQL Connection Failed") ENDIF ******End of CallSP.prg*************  Run Setup.prg. Run Callsp.prg.

NOTE: Check the name of the stored procedure, KBTemp, with your Database Administrator to ensure it is correct to use.

Additional query words: variable SQl odbc

Keywords: kbprb KB177056

-

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

© Microsoft Corporation. All rights reserved.