All Forums Third Party Software
mberndt 1 post Joined 05/11
04 May 2011
How many Teradata sessions does Micorsoft Query use?


does somebody know how many session/sqls MS Query sends over ODBC to teradata? Currently I refresh an Excel Pivot (with external Data on Teradata) with a VBA script and it takes very long time and on teradata I see a lot off sessions... I don't understand why. The second quession is: Can I optimze the request?

Thanks a very lot


PS.: That's a part off the VBA-Code:

SQLtxt = ActiveWorkbook.Sheets("SQL - Pivot").Cells(2, 1).Value

ActiveSheet.PivotTableWizard SourceType:=xlExternal, SourceData:=StringToArray(SQLtxt), Connection:="ODBC;DSN=MMO3;UID=XXX;PWD=XXX"
ActiveSheet.PivotTables("Pivot") .PivotCache.OptimizeCache = True

Function StringToArray(Query As Variant) As Variant

Const StrLen = 127 ' Set the maximum string length for
' each element in the array to return
' to 127 characters.
Dim NumElems As Integer
Dim Temp() As String

' Divide the length of the string Query by StrLen and
' add 1 to determine how many elements the String array
' Temp should contain, and redimension the Temp array to
' contain this number of elements.

NumElems = (Len(Query) / StrLen) + 1
ReDim Temp(1 To NumElems) As String

' Build the Temp array by sequentially extracting 127
' segments of the Query string into each element of the
' Temp array.

For i = 1 To NumElems
Temp(i) = Mid(Query, ((i - 1) * StrLen) + 1, StrLen)
Next i

' Set the function StringToArray to the Temp array so it
' can be returned to the calling procedure.

StringToArray = Temp

End Function

You must sign in to leave a comment.