getting the results of execute sql task

• Sridhar-137443

  Hall of Fame

  Points: 3762

  More actions

  November 29, 2004 at 4:05 pm

  #60019

  Hi,

  Can i use more than one SELECT SQL statement in Execute Package Task? I want to use three select statements inside Execute PAckage Task and get the values of these three select statement results into global variables. after that I want to compare these three global variables using activex script. will it be possible? or do i need to retrieve the results of select statements inside activex script itself using vb script and compare there? one problem with this approach is i am using windows authentication to connect to the database. I do not know how to specify windows authentication inside the active x script. can you please let me know how to do this?

  Thanks,

  Sridhar!!

• bellis

  SSCarpal Tunnel

  Points: 4667

  More actions

  November 30, 2004 at 8:46 am

  #531873

  You can accomplish this task in an ActiveX Script using ADO...

   Set myConn = CreateObject("ADODB.Connection")

   Set myRS = CreateObject("ADODB.Recordset")

   myConn.ConnectionString = "Provider=SQLOLEDB;Data Source=yourServerName;

                                               Initial Catalog=yourDatabase;Trusted_Connection=Yes"

   myConn.ConnectionTimeout = 0

   myConn.Open

   mySQL = "SELECT DISTINCT yourField FROM yourTable OR WHATEVER"

   myRS.Open mySQL, myConn

   myRS.MoveFirst

   DTSGlobalVariables("yourGlobalVarName_1").Value = myRS(0)

  Do this for each SELECT you need and assign to a global variable.

• Sridhar-137443

  Hall of Fame

  Points: 3762

  More actions

  November 30, 2004 at 8:49 am

  #531875

  Hi Bellis,

  Thanks for the reply. If i use active scripts for creating connections, I think the problem would be bad. Am i right?

  Thanks,

  Sridhar!!

• bellis

  SSCarpal Tunnel

  Points: 4667

  More actions

  November 30, 2004 at 8:51 am

  #531876

  What problems are you referring to?

• Sridhar-137443

  Hall of Fame

  Points: 3762

  More actions

  November 30, 2004 at 8:58 am

  #531877

  Hi Bellis,

  The problem might be it runs slow. one problem i got when tried to run the script you sent is the connection timed out problem. what should be the general value for that?

  Thanks,

  Sridhar!!

• bellis

  SSCarpal Tunnel

  Points: 4667

  More actions

  November 30, 2004 at 9:15 am

  #531887

  myConn.ConnectionTimeout = 0

  sets the timeout value to "unlimited"

• Sridhar-137443

  Hall of Fame

  Points: 3762

  More actions

  November 30, 2004 at 9:30 am

  #531892

  Hi Bellis,

  Thanks for the immediate response. Can you please post the sample script to execute more than one sql statements inside active x script. right now i'm trying like this but it is giving errors.

  Function Main()

  Set myConn = CreateObject("ADODB.Connection")

  Set myRS = CreateObject("ADODB.Recordset")

  myConn.ConnectionString = "Provider=SQLOLEDB;Data Source=OLYMPOS\RMSWORK; Initial Catalog=Rubys;Trusted_Connection=Yes"

  'myConn.ConnectionTimeout = 0

  myConn.Open

  mySQL1 = "SELECT MIN(period), MAX(period) FROM [dbo].[RDataload]"

  mySQL2 = "SELECT MAX(period) FROM [dbo].[RDataloadAll]"

  mySQL3 = "SELECT MAX(mperiod) FROM [dbo].[MainAll]"

  mySQL4 = "SELECT MAX(mPeriod) FROM [dbo].[MainAgg]"

  myRS.Open mySQL1, myConn

  myRS.MoveFirst

  DTSGlobalVariables("minPeriod_rdataload").Value = myRS(0)

  DTSGlobalVariables("maxPeriod_rdataload").Value = myRS(1)

  myConn.Close

  myRS.Open mySQL2, myConn

  myRS.MoveFirst

  DTSGlobalVariables("maxPeriod_rdataloadall").Value = myRS(0)

  myConn.Close

  myRS.Open mySQL3, myConn

  myRS.MoveFirst

  DTSGlobalVariables("maxPeriod_mainall").Value = myRS(0)

  myConn.Close

  myRS.Open mySQL4, myConn

  myRS.MoveFirst

  DTSGlobalVariables("maxPeriod_mainagg").Value = myRS(0)

  myConn.Close

  MsgBox "The date is " & DTSGlobalVariables("minPeriod_rdataload").Value

  MsgBox "The date is " & DTSGlobalVariables("maxPeriod_rdataload").Value

  MsgBox "The date is " & DTSGlobalVariables("maxPeriod_rdataloadall").Value

  MsgBox "The date is " & DTSGlobalVariables("maxPeriod_mainall").Value

  MsgBox "The date is " & DTSGlobalVariables("maxPeriod_mainagg").Value

  Main = DTSTaskExecResult_Success

  End Function

• bellis

  SSCarpal Tunnel

  Points: 4667

  More actions

  November 30, 2004 at 9:36 am

  #531894

  Do not close your connection after creating each recordset -- use the same connection, so you get something like...

  myRS.Open mySQL1, myConn

  myRS.MoveFirst

  DTSGlobalVariables("minPeriod_rdataload").Value = myRS(0)

  DTSGlobalVariables("maxPeriod_rdataload").Value = myRS(1)

  'myConn.Close

  myRS.Open mySQL2, myConn

  myRS.MoveFirst

  DTSGlobalVariables("maxPeriod_rdataloadall").Value = myRS(0)

  'myConn.Close

  myRS.Open mySQL3, myConn

  myRS.MoveFirst

  DTSGlobalVariables("maxPeriod_mainall").Value = myRS(0)

  'myConn.Close

  myRS.Open mySQL4, myConn

  myRS.MoveFirst

  DTSGlobalVariables("maxPeriod_mainagg").Value = myRS(0)

  'myConn.Close

  Then after execution of each query to populate your global variables, close the recordset object as well as the connection (clean up).

• Sridhar-137443

  Hall of Fame

  Points: 3762

  More actions

  November 30, 2004 at 9:38 am

  #531895

  Thanks alot. I tried like the above and it worked.

• bellis

  SSCarpal Tunnel

  Points: 4667

  More actions

  November 30, 2004 at 9:46 am

  #531900

  Just out of curiosity, how much time did it take for the queries to execute?

• Sridhar-137443

  Hall of Fame

  Points: 3762

  More actions

  November 30, 2004 at 11:06 am

  #531919

  It doesn't take much time. It took around 1 minute.

  Thanks,

  Sridhar!!