CODEWITHSUNDEEP
sqlvbaexceladodb
Torc
Torc

Reputation: 1322

Is there a limit to the query length being passed to ADODB?

I am currently testing a framework that stores a SQL query in a cell on an excel workbook and then passes the cell value to a variable. This variable then gets executed through rs.Open query, cn where:

Dim rs As ADODB.Recordset 
Dim cn As ADODB.Connection

and I have tried these for "query" :

Dim query as String
Dim query as Variant

or even playing with fixed length queries:

Dim query as String * 9999

In playing with the output of the query string, it cuts off somewhere around 8000. Google is coming up short for me, so I am coming here for answers. Is there a specific limit to what a variable can pass to recordset.Open()?

NB: The connection string I am using for this particular query is : ConnectionString = "User ID=User;Password=PW;Data Source=DB;Provider=OraOLEDB.Oracle"

But if you know whether there is a system specific limit for Oracle, NetezzaSQL, or MS SQL Server (SQLOLEDB.1) please let me know. I will need to specify the limitations of each in order to define which new queries will need to be converted into stored procedures.

Here is the essentials of the code:

Sub RunQueryTable()

Dim cn As ADODB.Connection
Dim RS As ADODB.Recordset
Dim iCols As Integer
Dim DB As String, User As String, PW As String
Dim SQLTable As Worksheet
Dim ConnectionString as String
Dim query As String 'Or Dim query As Variant 'Or Dim query As String * 9999
Dim i As Long
etc, etc.

Set SQLTable = Sheet32
Set cn = New ADODB.Connection
Set RS = New ADODB.Recordset

DB = _____
User = ______
PW = ____

ConnectionString = "User ID=" & User & _
                   ";Password=" & PW & _
                   ";Data Source=" & DB & _
                   ";Provider=OraOLEDB.Oracle"

query = SQLTable.Cells(i, 3).Text

cn.Open (ConnectionString)
RS.CursorType = adOpenForwardOnly
RS.Open (query), cn

For iCols = 0 To RS.Fields.count - 1
    Worksheets("Output").Cells(1, iCols + 1).Value = RS.Fields(iCols).Name
Next
Worksheets("Output").Cells(2, "A").CopyFromRecordset RS

RS.Close
cn.Close

End Sub

The specific error I am getting from VBA is : "ORA-00923: FROM keyword not found where expected" because the query is getting cut off.

Upvotes: 3

Views: 3952

Answers (1)

Tim Williams
Tim Williams

Reputation: 166456

Here's your problem:

query = SQLTable.Cells(i, 3).Text

Consider:

Range("A1").Value=string(12000,"*")
? len(range("a1").value) '>> 12000
? len(range("a1").text) '>> 8221

Upvotes: 3

Related Questions