Microsoft KB Archive/129570

= XL: SQLRetrieve Fails Using Oracle 7.0 ODBC Driver =

Article ID: 129570

Article Last Modified on 10/11/2006

-

APPLIES TO


 * Microsoft Excel 95a
 * Microsoft Excel 5.0c
 * Microsoft Excel 5.0 Standard Edition
 * Microsoft Excel 5.0a for Macintosh

-



This article was previously published under Q129570





SYMPTOMS
In Microsoft Excel, if you use the XLODBC.XLA function SQLRetrieve to retrieve consecutive sets of data on the same channel, the SQLRetrieve function may fail to return any data and one of the following error messages will occur:

Error returned by the Oracle driver SQORA7.DLL Version 1.11.0000:

={"S1002",0,"[Oracle][ODBC Oracle Driver]Invalid column number."}

-or-

Error returned by the Visigenic driver VSORAC32.DLL Version 2.00.0000.

={"S1002",0,"[Visigenic][ODBC Oracle driver]Invalid column number"}

For example, the second SQLRetrieve command called may fail if it is expected to return fewer columns of data than the first SQLRetrieve command called on the same channel.



RESOLUTION
This problem no longer occurs if you use the new Visigenic 32-bit Oracle 7 ODBC driver and Microsoft Excel for Windows 95 version 7.0.



STATUS
Microsoft has confirmed this to be a problem in the XLODBC.DLL that comes with Microsoft Excel version 5.0 for Windows NT.



MORE INFORMATION
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. The following macro example illustrates a situation in which the second SQLRetrieve command does not retrieve any data: Sub GetData

Dim Chan as Integer

' Establish a connection with the Oracle Data Source. Chan = SQLOpen("DSN=oracledata;UID=userid;PWD=password")

' Execute a query which is expected to return two columns of data. SQLExecQuery Chan, _ "SELECT EMPS.LAST_NAME, EMPS.FIRST_NAME FROM NORTHWIND.EMPS EMPS" SQLRetrieve Chan, ActiveSheet.Range("A1")

' Execute a second query which is expected to return only one column ' of data. SQLExecquery Chan, _ "SELECT EMPS.EMPLOYEE_ID FROM NORTHWIND.EMPS EMPS" SQLRetrieve Chan, Activesheet.Range("D1")

' Close the connection to the Oracle data source. SQLClose Chan

End Sub This macro fails because the second query returns fewer columns than the first query--the second query retrieves only one column of data (EMPLOY_ID) and the first query retrieves two columns of data (LAST_NAME, FIRST_NAME).

If you are unable to use Microsoft Excel version 7.0 (as recommended in the "Resolution" section of this article), use either of the following sample macros:

Sample 1
You could successfully execute the macro example above if you interchange the SQLExecquery lines so that one column is returned in the first SQLExecquery and two columns are returned in the second. For example: Sub GetData1

Dim Chan as Integer

' Establish a connection with the Oracle data source. Chan = SQLOpen("DSN=oracledata;UID=userid;PWD=password")

' Execute a query which is expected to return only one column of     ' data. SQLExecquery Chan, _ "SELECT EMPS.EMPLOYEE_ID FROM NORTHWIND.EMPS EMPS" SQLRetrieve Chan, Activesheet.Range("D1")

' Execute a second query which is expected to return two columns of     ' data SQLExecquery Chan, _ "SELECT EMPS.LAST_NAME, EMPS.FIRST_NAME FROM NORTHWIND.EMPS EMPS" SQLRetrieve Chan, Activesheet.Range("A1")

' Close the connection to the Oracle data source. SQLClose Chan

End Sub

Sample 2
You can also successfully execute the macro example GetData if you close the data source connection after the first query and then re-establish the connection prior to executing the second query. For example: Sub GetData2

Dim Chan as Integer

' Establish a connection with the Oracle data source. Chan = SQLOpen("DSN=oracledata;UID=userid;PWD=password")

' Execute a query which is expected to return two columns of data. SQLExecquery Chan, _ "SELECT EMPS.LAST_NAME, EMPS.FIRST_NAME FROM NORTHWIND.EMPS EMPS" SQLRetrieve Chan, Activesheet.Range("A1")

' Close the connection to the Oracle data source. SQLClose Chan

' Re-establish the connection to the Oracle data source. Chan = SQLOpen("dsn=oracledata;uid=userid;pwd=password")

' Execute a second query which is expected to return only one column ' of data. SQLExecquery Chan, _ "SELECT EMPS.EMPLOYEE_ID FROM NORTHWIND.EMPS EMPS" SQLRetrieve Chan, Activesheet.Range("D1")

' Close the connection to the Oracle data source. SQLClose Chan

End Sub

Additional query words: 5.00c addin

Keywords: kbbug kbcode kbfix kbprogramming KB129570

-

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

© Microsoft Corporation. All rights reserved.