Connect Excel to Multiple sessions in Teradata, possible?

Connectivity

Connect Excel to Multiple sessions in Teradata, possible?

Hey Guys...

I am connecting excel to teradata through ADODB connection and getting the results in the recordset.

The thing is, I have a slew of SQL's to be run around (10-15) which need not be sequentially. Using the below method, I am able to run each SQL query one after the other,

My question is, if it is possible to connect to multiple sessions of teradata and submit multiple SQL queries at once?

I am using the following template to run my SQL's one at a time now, please suggest a better method.

Sub try()

Dim cnnTeradata As ADODB.Connection
Dim connstr As String
Dim adoconn As New ADODB.Connection
Set cnnTeradata = CreateObject("ADODB.Connection")
cnnTeradata.Properties("Prompt") = 1
cnnTeradata.Open
cnnTeradata.CommandTimeout = 50000000
Set adoconn = cnnTeradata
connstr = adoconn.ConnectionString
Application.EnableCancelKey = xlDisabled 'Eliminates "Code Execution has been interrupted" Error

' Exports data from the Teradata to a active worksheet
'--------------------------------------------------------------------------------------
Dim db As New ADODB.Connection
Dim rs As New ADODB.Recordset
Application.ODBCTimeout = 0
db.ConnectionString = connstr
db.CommandTimeout = 5000
db.ConnectionTimeout = 5000
db.Open

sql1 = "Sel * from table"
sql2 = "Sel * from table1"
sql3 = "Sel * from table2"
With rs
.ActiveConnection = db
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.Open sql1
Range("B10").CopyFromRecordset rs
rs.Close 'Closes the Recordset object
End With

With rs
.ActiveConnection = db
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.Open sql2
Range("B11").CopyFromRecordset rs
rs.Close 'Closes the Recordset object
End With

With rs
.ActiveConnection = db
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.Open sql3
Range("B12").CopyFromRecordset rs
rs.Close 'Closes the Recordset object
End With
Set rs = Nothing
Set cnnTeradata = Nothing
Set adoconn = Nothing
db.Close
Set rs = Nothing
End Sub

1 REPLY

Re: Connect Excel to Multiple sessions in Teradata, possible?


Hi,



Yes. if you use threads.



I have a .net Windows Service that runs tasks on scheduled basis. when it times to run a tasks, it just gives away to thread. so if there are multiple tasks, they can run in parallel by 2 separate thread.



 



Hope this helps,



Satish