Stored Procedures

Question

Stored Procedures

TO_friends

Newbie

Points: 2
More actions
May 29, 2009 at 4:34 pm

#204640

Hi to all,
I want to turn the following code which is from a .asp page, into a stored procedure so that it can be run by sql agent every 5 minutes;
set rsCatCount = Server.CreateObject("ADODB.Recordset")
rsCatCount.ActiveConnection = MM_connClass_STRING
rsCatCount.Source = "SELECT COUNT(*) AS CAT_COUNT FROM GB_AD WHERE AD_ACTV = 1 "
rsCatCount.CursorType = 0
rsCatCount.CursorLocation = 2
rsCatCount.LockType = 3
rsCatCount.Open()
rsCatCount_numRows = 0
Once it runs, I need to write the results to the database into a specific column according to where the count was generated.
Regards,

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

John H Marsh SSChampion Points: 11671 More actions · Answer 1

Hello,

I would say it is possible to write an SP to perform the logic you require, and an SQL Agent Job can be set to execute every five minutes.

What specifically is your question?

Regards,

John Marsh

www.sql.lu
SQL Server Luxembourg User Group

Mayank Khatri SSCertifiable Points: 7984 More actions · Answer 2

TO_friends (5/29/2009)
Hi to all,
I want to turn the following code which is from a .asp page, into a stored procedure so that it can be run by sql agent every 5 minutes;
set rsCatCount = Server.CreateObject("ADODB.Recordset")
rsCatCount.ActiveConnection = MM_connClass_STRING
rsCatCount.Source = "SELECT COUNT(*) AS CAT_COUNT FROM GB_AD WHERE AD_ACTV = 1 "
rsCatCount.CursorType = 0
rsCatCount.CursorLocation = 2
rsCatCount.LockType = 3
rsCatCount.Open()
rsCatCount_numRows = 0
Once it runs, I need to write the results to the database into a specific column according to where the count was generated.
Regards,

It seems to me that all you are doing is getting COUNT from table GB_AD where AD_ACTV=1.

so u just want the count from this table every 5 mnutes?

CREATE TABLE RecCount(CAT_COUNT int, TimeCount datetime)

CREATE PROCEDURE GetCount

AS

INSERT INTO RecCount(CAT_COUNT, TimeCount)

VALUES((SELECT COUNT(*) AS CAT_COUNT FROM GB_AD WHERE AD_ACTV = 1 ),datetime())

;

TO_friends Newbie Points: 2 More actions · Answer 3

Hi Ten Centuries,

You have the right idea, my problem is that I have no experience in SP.

Can you help me with starting a simple SP and then working on the current problem.

I would like to start by creating a SP that will do the following

SELECT COUNT(*) AS CAT_COUNT FROM GB_AD WHERE AD_ACTV = 1

and display this value.

Regards

John H Marsh SSChampion Points: 11671 More actions · Answer 4

Hello again,

I would suggest taking a look at a few of the SP related help topics in SQL Server Books Online, to get at least a basic understanding. For example: “Stored Procedure Basics” and “CREATE PROCEDURE (Transact-SQL)”.

There is a good example “A. Using a simple procedure” of how to create a SP that selects and display values.

Regards,

John Marsh

www.sql.lu
SQL Server Luxembourg User Group