Knowledge Base

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, Username, 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

Microsoft KB Archive/177056

Contents