running SQL Commands from VBScript

can you post some code?
you should create a recordset object

>>you should create a recordset object <<
Not necessarily.  The count can be returned as an output parameter from the Stored Procedure.

I have a connection object oConn created and I am using the following function to get the record count by running strtSQL command.

Public Function getrecordcount()
Dim Recs
On Error Resume Next

'Create and Open Recordset
Set rsMemberInfo = CreateObject("ADODB.Recordset")
strtSQL = "Select Count(*) from Members"

'Execute using Connection Execute
set rsMemberInfo = oConn.Execute(strtSQL)

If err.Number > 0 Then
        getrecordcount = -1
        Exit Function
End If
getrecordcount = Recs
End Function

I want to know how I can display the result from the recordset rsMemberInfo, I tried using rsMemberInfo.Item(0), but nothing is displayed.

If the strtSQL is something else like "Select field1, field2 from Members" the result is in the recordset, not for the SQL command returning COUNT.

Any suggestions?

Make the following changes:

Public Function getrecordcount()
' Dim Recs                               Comment this out
' On Error Resume Next            Comment this out

'Create and Open Recordset
Set rsMemberInfo = CreateObject("ADODB.Recordset")
strtSQL = "Select Count(*) from Members"

On Error Resume Next        ' Add this
'Execute using Connection Execute and assuming the oConn is actually instantiated and open
set rsMemberInfo = oConn.Execute(strtSQL)
If err.Number <> 0 Then    ' Change this
   getrecordcount = -1
Else
   getrecordcount = rsMemberInfo.Fields(0).Value
End If
rsMemberInfo.Close                    ' Add this
Set rsMemberInfo = Nothing        ' Add this

End Function

>>I want to know how I can display the result from the recordset rsMemberInfo, I tried using rsMemberInfo.Item(0), but nothing is displayed.

try rsMemberInfo(0), there you will have the result of the count(*).



>>If the strtSQL is something else like "Select field1, field2 from Members" the result is in the recordset, not for the SQL command returning COUNT.

can't understand, explain a bit more.

acperkins, i didn't see your last comment until I wrote mine.

>>acperkins, i didn't see your last comment until I wrote mine.<<
No problem.  In any case, rsMemberInfo.Item(0) in this context (and because of the nature of default properties in VB6) is the same as:
rsMemberInfo(0)
rsMemberInfo.Fields(0)
rsMemberInfo.Fields.Item(0)
rsMemberInfo.Fields(0).Value
rsMemberInfo.Fields.Item(0).Value
(and a few others I may have missed)

So I don't think that is the case here. On Error Resume Next has the basd side-effect of hiding problems and should only be used sparingly.

diegojserrano,

<offtopic>
By the way, "Argentine National Technological University", I assume that is not the same as ITBA and if not what city?
</offtopic>

<offtopic>
By the way, "Argentine National Technological University", I assume that is not the same as ITBA and if not what city?

No, ITBA is Buenos Aires Technological Institute.
The National Technological University (UTN in spanish) is spread across the country, with different campus in the major cities. I belong to the Cordoba's Campus, which is the second city after Buenos Aires.
Thanks for the question.
How do you know ITBA?
</offtopic>

<offtopic>
>>How do you know ITBA?<<
I went there.

<.offtopic>

<offtopic>
But we should take this off line as it is going off topic.   See my profile for my email address.
</offtopic>

sorry rajmohans, we went out of your problem, could you solve it?

I will give it a try and let you know, by the way, will this work if I run a stored procedure in place of a direct SQL command,  

strtSQL = "db.storedprocedure"

with a "select reccount" at the end of procedure where reccount is a calculated variable within stored procedure.

Thanks for your help.

>> will this work if I run a stored procedure in place of a direct SQL command<<
That is in fact the preferred way.  A little more trouble to setup, but far more efficient as you no longer need to instantiate a Recordset object as you can use an output parameter to return the count as in:

Create Procedure usp_GetMemberCount
       @RowCount integer OUTPUT
As

SET NOCOUNT ON

Select @RowCount = Count(*) From Members

I believe I have to use the recordset object anyway to get the reccount value returned by stored procedure, when I execute the stored procedure from the VBScript.

Execute method does not have an option to get the value returned by the stored procedure directly, it returns only the records affected which works perfectly if I run any update, insert or delete query, but not with select * or select count(*) queries.

So, I am going to update my VBScript as you suggested and modify stored procedure to use OUTPUT parameter and get the reccount value using a recordset object in the script.

Will let you know tomorrow on how it works, thanks again.

>>So, I am going to update my VBScript as you suggested and modify stored procedure to use OUTPUT parameter and get the reccount value using a recordset object in the script.<<
In order to get an output parameter you must use a Command object, so in your case it would look like this:

Dim cn
Dim cmd

' Create connection and command objects
Set cn = CreateObject("ADODB.Connection")

' Set connection properties and open
cn.ConnectionString = "your connection string goes here"
cn.Open

' Set command properties
Set cmd = CreateObject("ADODB.Command")
With cmd
    Set .ActiveConnection = cn
    .CommandText = "usp_GetMemberCount"
    .CommandType = 4      ' adCmdStoredProc
    ' Define stored procedure params and append to command.
    .Parameters.Append .CreateParameter("@RETURN_VALUE", 3, 4, 0)
    .Parameters.Append .CreateParameter("@RowCount", 3, 3, 0, Null)
    ' Execute the command
    .Execute , , 128                 ' adExecuteNoRecords
    Response.Write .Parameters("@RowCount").Value
End With

I missed the following lines at the end of that last example:
Set Cmd = Nothing

cn.Close
Set cn = Nothing

Also, you can also take a short-cut (though I would not recommend it) as follows:

Create Procedure usp_GetMemberCount

As

Declare @RowCount integer

SET NOCOUNT ON

Select @RowCount = Count(*) From Members

Return @RowCount

Than your VBScript would look like this:
Dim cn
Dim cmd

' Create connection and command objects
Set cn = CreateObject("ADODB.Connection")

' Set connection properties and open
cn.ConnectionString = "your connection string goes here"
cn.Open

' Set command properties
Set cmd = CreateObject("ADODB.Command")
With cmd
    Set .ActiveConnection = cn
    .CommandText = "usp_GetMemberCount"
    .CommandType = 4      ' adCmdStoredProc
    ' Define stored procedure params and append to command.
    .Parameters.Append .CreateParameter("@RETURN_VALUE", 3, 4, 0)
    ' Execute the command
    .Execute , , 128                 ' adExecuteNoRecords
    Response.Write .Parameters("@RETURN_VALUE").Value
End With
Set Cmd = Nothing

cn.Close
Set cn =  Nothing

I think I am confused.

I have a recordset object created and I am running my stored procedure (with a select count(*) at the end) using Execute method.

I would expect the recordset.fields(0).Value to return the value of "select count(*)" from the SP.

I tried modifying my script as per your suggestions, timestamped 4.44 pm yesterday and it does not return anything.

Please help.

Try posting your stored procedure and your code and I will attempt to duplicate.

This is the part of script where I call tempPurge function

'*******************************************************************************************
' Run SP usp_tempPurge to Purge
'*******************************************************************************************
Call AppendToMessageBody("Purge begins")

If tempPurge() <> -1 Then
      Call AppendToMessageBody("Purge Complete ")
Else
      WriteToLog "# Purge Failed " & " Date/Time : " & Now, False
      WriteToLog "      Reason : (" & err.Number & ") " & err.Description, True
      Call AppendToMessageBody("Purge Failed ")
      Call AppendToMessageBody("Reason : (" & err.Number & ") " & err.Description)
End If
WScript.Echo "------------------------------------------------------------"
 

'*******************************************************************************************
' tempPurge - Runs usp_tempPurge purging
'*******************************************************************************************
Public Function tempPurge()
Dim Recs
On Error Resume Next

' Recordset Variables
Dim rstempPurge, strtSQL

'Create and  Recordset
Set rstempPurge = CreateObject("ADODB.Recordset")
strtSQL = "A774899.usp_rstempPurge"

'Execute using Connection Execute
set rstempPurge = oConn.Execute(strtSQL)

if err.Number <> 0 then
      rstempPurge = -1
       WriteToLog "# Error in rstempPurge() " & " Date/Time : " & Now, True
      WriteToLog "      Reason : (" & err.Number & ") " & err.Description, True
      Call AppendErrorToMessageBody("rstempPurge Fails ")
      Call AppendToMessageBody("Reason : (" & err.Number & ") " & err.Description)
      WScript.Quit CONST_Error
      Exit Function
end if

numSelected = rstempPurge(0)
rstempPurge = rstempPurge(1)
numPurged = rstempPurge(1)

rstempPurge.close
set rstempPurge = Nothing

End Function


***************** This is the SP ******************


CREATE PROCEDURE dbo.usp_tempPurge
As

DECLARE @Cnt int
DECLARE @CnttobePurged int
DECLARE @CntPurged int

SELECT @CntPurged = 0

-- Get Count of Records to be purged
SELECT      @Cnt = count(*)
FROM ((((dbo.temp LEFT JOIN dbo.CallCodes ON dbo.temp.CntctCde1 = dbo.CallCodes.CallCode)
              LEFT JOIN dbo.CallCodes AS CallCodes_1 ON dbo.temp.CntctCde2 = CallCodes_1.CallCode)
              LEFT JOIN dbo.CallCodes AS CallCodes_2 ON dbo.temp.CntctCde3 = CallCodes_2.CallCode)
              LEFT JOIN dbo.CallCodes AS CallCodes_3 ON dbo.temp.CntctCde4 = CallCodes_3.CallCode)
              LEFT JOIN dbo.CallCodes AS CallCodes_4 ON dbo.temp.CntctCde5 = CallCodes_4.CallCode
WHERE       (((dbo.temp.CallDate)< '02/01/2004') AND
      ((dbo.CallCodes.ID) Is Null Or (dbo.CallCodes.ID)=1 Or (dbo.CallCodes.ID)=5) AND
      ((CallCodes_1.ID) Is Null Or (CallCodes_1.ID)=1 Or (CallCodes_1.ID)=5) AND
      ((CallCodes_2.ID) Is Null Or (CallCodes_2.ID)=1 Or (CallCodes_2.ID)=5) AND
      ((CallCodes_3.ID) Is Null Or (CallCodes_3.ID)=1 Or (CallCodes_3.ID)=5) AND
      ((CallCodes_4.ID) Is Null Or (CallCodes_4.ID)=1 Or (CallCodes_4.ID)=5))

SELECT @CnttobePurged = @Cnt

SET ROWCOUNT 1500

WHILE @Cnt > 0

BEGIN
BEGIN TRANSACTION
-- Purge Records
DELETE dbo.temp
FROM ((((dbo.temp LEFT JOIN dbo.CallCodes ON dbo.temp.CntctCde1 = dbo.CallCodes.CallCode)
              LEFT JOIN dbo.CallCodes AS CallCodes_1 ON dbo.temp.CntctCde2 = CallCodes_1.CallCode)
              LEFT JOIN dbo.CallCodes AS CallCodes_2 ON dbo.temp.CntctCde3 = CallCodes_2.CallCode)
              LEFT JOIN dbo.CallCodes AS CallCodes_3 ON dbo.temp.CntctCde4 = CallCodes_3.CallCode)
              LEFT JOIN dbo.CallCodes AS CallCodes_4 ON dbo.temp.CntctCde5 = CallCodes_4.CallCode
WHERE       ((dbo.temp.CallDate)< '02/01/2004') AND
      ((dbo.CallCodes.ID) Is Null Or (dbo.CallCodes.ID)=1 Or (dbo.CallCodes.ID)=5) AND
      ((CallCodes_1.ID) Is Null Or (CallCodes_1.ID)=1 Or (CallCodes_1.ID)=5) AND
      ((CallCodes_2.ID) Is Null Or (CallCodes_2.ID)=1 Or (CallCodes_2.ID)=5) AND
      ((CallCodes_3.ID) Is Null Or (CallCodes_3.ID)=1 Or (CallCodes_3.ID)=5) AND
      ((CallCodes_4.ID) Is Null Or (CallCodes_4.ID)=1 Or (CallCodes_4.ID)=5)

SELECT @CntPurged = @CntPurged + @@ROWCOUNT
COMMIT TRANSACTION
SELECT @Cnt = @Cnt - 1500
END

SET ROWCOUNT 0

if @@trancount > 0
   COMMIT TRANSACTION

-- Get Count of Records
Select @CnttobePurged as Rows_to_be_purged, @CntPurged as Rows_Purged
go

SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS OFF
GO

It worked.. SP returned the values when I included SET NOCOUNT ON in stored procedure, thanks a lot.

If I could ask a follow-up, the stored procedure completes successfully if I run it from Query analyzer, but when run through VBScript, using ADO's execute method as below, it breaks in middle and exits.

set rstempPurge = oConn.Execute(strtSQL), please see temppurge() above for detailed code.

Thanks

>> it breaks in middle and exits. << 
What is the error message?

Also, please comment out:
On Error Resume Next

or you'll never know what errors there are.

error is   Reason : (500) Variable is undefined, not sure if it is coming from the

Select @CnttobePurged as Rows_to_be_purged, @CntPurged as Rows_Purged
go

I am not following you.  What happens when you run the Stored Procedure from Query Analyzer? Does it run successfully?  If yes than have you commented out the "On Error Resume Next" as I suggested?

When I run the stored procedure from Query Analyzer it runs successfully. But when I call SP from VBScript as below, using Execute method, the stored procedure does not run to completion sometimes, and also I don't see any values returned in the recordset.

'*******************************************************************************************
' tempPurge - Runs usp_tempPurge purging
'*******************************************************************************************
Public Function tempPurge()
Dim Recs
On Error Resume Next

' Recordset Variables
Dim rstempPurge, strtSQL

'Create and  Recordset
Set rstempPurge = CreateObject("ADODB.Recordset")
strtSQL = "A774899.usp_tempPurge"

'Execute using Connection Execute
set rstempPurge = oConn.Execute(strtSQL)

if err.Number <> 0 then
     rstempPurge = -1
      WriteToLog "# Error in rstempPurge() " & " Date/Time : " & Now, True
      WriteToLog "      Reason : (" & err.Number & ") " & err.Description, True
      Call AppendErrorToMessageBody("rstempPurge Fails ")
      Call AppendToMessageBody("Reason : (" & err.Number & ") " & err.Description)
     WScript.Quit CONST_Error
      Exit Function
end if

numSelected = rstempPurge(0)
rstempPurge = rstempPurge(1)
numPurged = rstempPurge(1)

rstempPurge.close
set rstempPurge = Nothing

End Function
 
I think, I will rewrite the SP to use OUTPUT parameters, please tell me how to execute SP from VBScript.

Again, do your self a favor and comment out the line:
On Error Resume Next
This is masking the real problem.

When you have done that, please post here the error number and description exactly as it appears.

in fact, I have the line commented in my script, sorry I copied that from earlier posting here.

Well,  the error says,       Reason : (500) Variable is undefined

Are you sure?  The reason I ask this is that if it was commented the code after:
set rstempPurge = oConn.Execute(strtSQL)

would have never been executed and your would have never seen
       Reason : (500) Variable is undefined

Which evidently comes from the following line:
WriteToLog "      Reason : (" & err.Number & ") " & err.Description, True

Again if the line: "On Error Resume Next" is commented, you would never see the error with the format your describe, namely:
       Reason : (500) Variable is undefined

Instead it would look something like this:

Variable is undefined
(0x1F4, Source='Microsoft VBScript runtime Error')

the error is formatted within my script and that is what I sent, I will be curious why this error occurs when I have declared all the variables I use in the script.

>>the error is formatted within my script and that is what I sent<<
That is my point, if you were not using On Error Resume Next, as you said you were, your code where "the error is formatted" would never execute, it would crash when it hit the offending line and display a message such as:

Variable is undefined
(0x1F4, Source='Microsoft VBScript runtime Error')

But further than that, I cannot help you,  Perhaps someone else can step in.

I have that line commented as below and I see the errors written in my log file.

'Execute using Connection Execute
' On Error Resume Next
set rstempPurge = oConn.Execute(strtSQL)

if err.Number > 0 then
     rstempPurge = -1
      WriteToLog "# Error in rstempPurge() " & " Date/Time : " & Now, True
      WriteToLog "      Reason : (" & err.Number & ") " & err.Description, True
      Call AppendErrorToMessageBody("rstempPurge Fails ")
      Call AppendToMessageBody("Reason : (" & err.Number & ") " & err.Description)
     WScript.Quit CONST_Error
      Exit Function
end if

Well, thanks a lot for helping.

You are right, even though I commented the "On Error Resume Next" right above the oConn.Execute, there was one in the very beginning of my script, that caused the script to continue running.

Also the "Variable is undefined" was coming from a previous function. When I placed a err.clear right above oConn.Exec, it ran like a charm, but still Timeout Expired error is there. Any suggestions?

Try setting the CommandTimeout to a value higher than the default 30 seconds or if you are really desperate to 0 (infinite), prior to doing the execute as in:
oConn.CommandTimeout = 120

Great... it works like a charm now...  I cannot thank you enough for helping with this.