Calling SQL from Active x script task in DTS

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Calling SQL from Active x script task in DTS

Author

Topic

mcrors_calhoun
Starting Member

22 Posts

Posted - 2006-10-17 : 06:00:15

I am trying to create an array in ActiveX dynamically by calling a Select count(*) from a table in my database. I am having some trouble doing this. The code I am using is

Dim num_rec
Dim con
Set con = New ADODB.Connection

'ConnectionString property value
con.ConnectionString = "Provider=SQLOLEDB;UID=sa;pwd=;" & _
"Data Source=TEST3;Initial Catalog=Wyeth"
con.Open

num_rec = con.Execute "SELECT count(*) FROM DIM_REPS"

con.close

Dim reps(num_rec)

I am unable to even parse the code with out it giving me an error on the line
num_rec = con.Execute "SELECT count(*) FROM DIM_REPS"
Ihaven't done this before so I am unsure of where I am going wrong really.

Can some one help please???

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-17 : 06:13:53

Try this:

Dim num_rec()
Dim con
Dim rs
Set con = New ADODB.Connection
Set rs = New ADODB.Recordset

'ConnectionString property value
con.ConnectionString = "Provider=SQLOLEDB;UID=sa;pwd=;" & _
"Data Source=TEST3;Initial Catalog=Wyeth"
con.Open

set rs = con.Execute("SELECT count(*) FROM DIM_REPS")
num_rec = rs(0).value
rs.close
con.close

Redim reps(num_rec)

Harsh Athalye
India.
"Nothing is Impossible"

mcrors_calhoun
Starting Member

22 Posts

Posted - 2006-10-17 : 06:18:54

Thanks, that seems to parse ok, but on running it it gives the error "class not defined: 'ADODB'"
Do I have to call CreateObject(ADOBD.Connection) first, or something like this???

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-17 : 06:37:34

Yes, try using CreateObject() like this:

Set con = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

Harsh Athalye
India.
"Nothing is Impossible"

nr
SQLTeam MVY

12543 Posts

Posted - 2006-10-17 : 07:12:47

http://www.nigelrivett.net/DTS/DTSExecuteStoredProcedure.html

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-17 : 07:24:32

nr,
I am just asking out of curiosity.

Is it necessary to use recordset object in following statement:

Set objRS = objCMD.Execute

Can't we directly execute command object since we are not using recordset anyway.

Harsh Athalye
India.
"Nothing is Impossible"

nr
SQLTeam MVY

12543 Posts

Posted - 2006-10-17 : 07:30:37

Try it and see. It should be ok.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources