1

I'm using ADODB in Excel 2007 VBA to connect to a Teradata 14.0 server using the Teradata ODBC 15.0 driver. Everything works as expected, except for when I submit very large queries through ADODB.Recordset.Open.

Sporadically, when I attempt recordset.open with a query that is 5000+ characters it throws ODBC Driver does not support the requested properties Error -2147217887.

The error appears after what seems like a set amount of time (30 seconds or so) when I don't get a loaded recordset object back.

The code is straightforward:

Sub getData()
    Dim strSQL As String
    Dim scRS As ADODB.Recordset
    Dim adoConn As ADODB.Connection

   strSQL = "Very Large SQL"

    'open the connection
    Set adoConn = New ADODB.Connection

    On Error GoTo adoExit
    adoConn.Open "SessionMode=Teradata;Driver=Teradata;DBCName=<SERVERIP>;Database=<DATABASE>;CharSet=UTF16;Uid=<USERID>;Pwd=<PASSWORD>"

    'get the data
    Set scRS = New ADODB.Recordset
    scRS.Open strSQL, adoConn, adOpenKeyset, adLockOptimistic

    ...

End Sub

Edited to add: I've tried variations of nearly anything that sounded like it might affect packet sizes/timeouts/character sets (which I thought might affect total bytes)/etc in the connection string using http://www.info.teradata.com/htmlpubs/DB_TTU_13_10/index.html#page/Connectivity/B035_2509_071A/2509ch03.05.06.html as a guide. And still I get sporadic timeouts.

JNevill
  • 46,980
  • 4
  • 38
  • 63
  • what's your `adoConn.ConnectionTimeout`? Maybe try increasing that...Have you tried shortening the query? Have you tried running any shorter query? Also, have a look at the [ConnectionStrings for TerraData](http://www.connectionstrings.com/teradata/) maybe there is a property not supported somehow? Also check your `adoConn.state` –  Nov 12 '14 at 16:20
  • I see...I don't really have any experience with TerraData so I feel a bit helpless at this point also I can't really repro this which makes it even harder to debug. One more suggestion - can you play with the last 2 parameters on the `rs.Open` - try different ones maybe –  Nov 12 '14 at 16:30
  • Sounds like you might be surpassing some limit on the length of the SQL statement, or something like that. I have ZERO familiarity with TerraData, so I'm just guessing, but sounds like a pretty long Select statement. Can you break your select statement up into multiple statements? Maybe store the results of each in a `#TemporaryTable` then when you're done, just open a recordset from that table using `SELECT * FROM #TemporaryTable`. Or something like that. – CBRF23 Nov 21 '14 at 02:00
  • Without seeing your SQL statment I don't know what it looks like, but I found this [List of TeraData SQL limits](http://www.info.teradata.com/htmlpubs/DB_TTU_14_00/index.html#page/Database_Management/B035_1094_111A/appb.059.16.html). Maybe that will help shed some light on why it's happening? – CBRF23 Nov 21 '14 at 02:04
  • The statement itself isn't just one long big statement, it's actually like 15 statements concat'd together. I can run the big horrible cat'd statement directly in TD without any issues, it's only when pushing through ADODB with ODBC that I have the problem. I could split them up and run them in multiple serialized chunks, but them I lose the parallel processing that TD gives me. I can run them all individually, serialized in under 90 seconds. I would like it to be somewhere in the 20 second range which is what the parallelized, cat'd statement gives me. – JNevill Nov 21 '14 at 18:34
  • Long shot. Don't crucify me. Have you considered the .Net Provider for Teradata in place of ODBC? – Rob Paller Nov 22 '14 at 01:59
  • @RobPaller I may be mistaken, but I don't believe I can use the .net provider in VBA. I'm using ADODB and ODBC simply because ADODB is a cinch in VBA, and the Teradata ODBC driver is already installed on every pc in company. I was thinking about testing the issue through the OLEDB driver which I have installed, but then it would be limited to the few PCs where people have access to install such a thing, so that's pretty much out too. – JNevill Nov 22 '14 at 21:59
  • How are you submitting this directly against Teradata to test the SQL? – Rob Paller Nov 23 '14 at 01:42
  • Through Atanasuite or Teradata Studio. – JNevill Nov 23 '14 at 01:44
  • Both AtanaSuite and Teradata Studio use a different client to communicate with the database - not ODBC. Can the SQL be parameterized and stored as a macro that in turn is referenced by your VBA code instead? This should allow you to maintain multi statement execution and a similar behavior in the result set that is returned by the macro – Rob Paller Nov 23 '14 at 01:59
  • I explored using a macro, however the SQL generated is dynamic which means I could have anywhere from 1 to 15 different fields in the WHERE clause and of which could have 1 to many different elements in an IN() list. There's a way to make it work by passing the list of values for each field in a character seperated list and using STRTOK_SPLIT_TO_TABLE to parse it and use an INNER JOIN to limit, but it's horribly ugly and very expensive, so I scrapped it. I'm thinking at this point I may need to convert the SQL statements to a number of views and call those instead to keep the chr count down :( – JNevill Nov 24 '14 at 13:19
  • It's been 5 years since I wrote this. The solution I ended up using was to dump these queries into a stored proc which can return up to 16 record sets. I then have a whole mess of parameters I submit to the procedure and it returns everything. Still have no idea why ODBC was being such a snot. – JNevill Nov 27 '19 at 13:54

1 Answers1

0

Had the same problem while trying to run a composite query. Try to add: ;QueryTimeout=0 at the end of your connection string (the one that follows adoConn.Open)

Yaki
  • 1
  • 2