Missing entry in report generated by SP_MAKEWEBTAS

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

General SQL Server Forums

New to SQL Server Programming

Missing entry in report generated by SP_MAKEWEBTAS

Author

Topic

panthagani
Yak Posting Veteran

58 Posts

Posted - 2006-12-28 : 19:15:14

I'm trying to create a .htm file (a report from query results) on a webserver using SQL code.

TBL_LOGGEDIN_USERS_PERHOUR has just two columns. It gives the number of users logging in for hour.

HOUR datetime
NUMBER_OF_USERS smallint

HOUR NUMBER_OF_USERS
2006-12-28 15:00:00.000 15
2006-12-28 14:00:00.000 29
2006-12-28 13:00:00.000 30
2006-12-28 12:00:00.000 23
2006-12-28 11:00:00.000 23
2006-12-28 10:00:00.000 21
2006-12-28 09:00:00.000 31
2006-12-28 08:00:00.000 30

This is the actual code which generates one .htm file every 24 hours with the count of hourly logins. So the entries (as per the below logic) start at 12:00 AM everyday and end at 11:00 PM. The catch is - the entries come up fine upto 10:00 PM. The '11:00 PM entry' is missing in all the .htm files. I tried several options and later, I 'force added' an ELSE IF condition especially for the 11:00 PM case, indicated in the code below but it doesn't seem to help...
Also, I'm not really sure if this is a row limitation in the .htm file - like x no: of rows per .htm file.

Am I missing something here?.. Please advise. Let me know if I'm not clear. Thank you.

CREATE PROC CONDOR_CLIENT_LOGINS_PERHOUR
AS
BEGIN

IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE ID = OBJECT_ID(N'[DBO].[TBL_CUSTOM_LOGINSPERHOUR]')
AND OBJECTPROPERTY(ID, N'ISUSERTABLE') = 1)
DROP TABLE [DBO].[TBL_CUSTOM_LOGINSPERHOUR]

CREATE TABLE [DBO].[TBL_CUSTOM_LOGINSPERHOUR] (
ROW1 NVARCHAR (30),
ROW2 NVARCHAR(30)
) ON [PRIMARY]

DECLARE @FLAG SMALLINT
DECLARE @REPFLAG SMALLINT
SET @FLAG = 0
SET @REPFLAG = 0

IF DATEPART(HH,GETDATE()) = 0
BEGIN

SELECT CONVERT(NVARCHAR,HOUR,100)AS [ROW1],
CONVERT(NVARCHAR,NUMBER_OF_USERS) AS [ROW2] INTO #TEMP_LOGINSPERHOUR1 FROM TBL_LOGGEDIN_USERS_PERHOUR
WITH (NOLOCK)
WHERE HOUR = DATEADD(DAY,(DATEDIFF(DAY,0,GETDATE())),0)
ORDER BY HOUR
SET @FLAG = 1

END
-- this is added for 11:00 PM entry START

ELSE IF DATEPART(HH,GETDATE()) = 23
BEGIN

SELECT CONVERT(NVARCHAR,HOUR,100)AS [ROW1],
CONVERT(NVARCHAR,NUMBER_OF_USERS) AS [ROW2] INTO #TEMP_LOGINSPERHOUR2 FROM TBL_LOGGEDIN_USERS_PERHOUR
WITH (NOLOCK)
WHERE HOUR >= DATEADD(DAY,(DATEDIFF(DAY,0,GETDATE())),0)
ORDER BY HOUR
SET @FLAG = 2

END

-- the above is added for 11:00 PM entry END

ELSE
BEGIN

SELECT CONVERT(NVARCHAR,HOUR,100)AS [ROW1],
CONVERT(NVARCHAR,NUMBER_OF_USERS) AS [ROW2] INTO #TEMP_LOGINSPERHOUR3 FROM TBL_LOGGEDIN_USERS_PERHOUR
WITH (NOLOCK)
WHERE HOUR >= DATEADD(DAY,(DATEDIFF(DAY,0,GETDATE())),0)
ORDER BY HOUR

END

INSERT INTO TBL_CUSTOM_LOGINSPERHOUR
SELECT 'HOUR', 'NUMBER OF CLIENT LOGINS'
UNION ALL
SELECT '' , ''
UNION ALL
SELECT '' , ''

IF @FLAG = 1
BEGIN
INSERT INTO TBL_CUSTOM_LOGINSPERHOUR
SELECT * FROM #TEMP_LOGINSPERHOUR1
SET @REPFLAG = 1
END
ELSE IF @FLAG = 2
BEGIN
INSERT INTO TBL_CUSTOM_LOGINSPERHOUR
SELECT * FROM #TEMP_LOGINSPERHOUR2
SET @REPFLAG = 2
END
ELSE
BEGIN
INSERT INTO TBL_CUSTOM_LOGINSPERHOUR
SELECT * FROM #TEMP_LOGINSPERHOUR3
END
SELECT '' , ''
UNION ALL
SELECT '' , ''

DECLARE @REPPATH AS NVARCHAR(50)

SET @REPPATH = '\\TKESTIIS5\LOGINSPERHOUR\' + REPLACE(CONVERT(NVARCHAR(10), GETDATE(), 101),'/', '') + '.HTM'
EXEC SP_MAKEWEBTASK @REPPATH , 'SELECT * FROM TBL_CUSTOM_LOGINSPERHOUR' ,
@COLHEADERS = 0 , @RESULTSTITLE = 'Client Logins Per Hour Report',
@WEBPAGETITLE = 'Client Logins Per Hour Report' ,@LASTUPDATED = 1

DROP TABLE TBL_CUSTOM_LOGINSPERHOUR

IF @REPFLAG = 1
DROP TABLE #TEMP_LOGINSPERHOUR1
ELSE IF @REPFLAG = 2
DROP TABLE #TEMP_LOGINSPERHOUR2
ELSE
DROP TABLE #TEMP_LOGINSPERHOUR3

END

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2006-12-29 : 09:58:27

I suspect that this is nothing more than a problem with the "timing" of scheduled jobs. I suspect that you are running your code before the 11PM row for the number of logins has executed so the 11PM row doesn't actually exist when you run the code.

You need to modify your code (after you simplify it back to a single SELECT), to run AFTER midnight and your date calculation should look like this to pickup all the log entries for the previous day...

WHERE HOUR >= DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)-1
  AND HOUR <  DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)

--Jeff Moden

panthagani
Yak Posting Veteran

58 Posts

Posted - 2006-12-29 : 13:23:42

Thanks Jeff. Here is some more background.. Actually, I have a first step in the job which populates the TBL_LOGGEDIN_USERS_PERHOUR table..

insert into TBL_LOGGEDIN_USERS_PERHOUR
select DATEADD(HOUR,(DATEDIFF(HOUR, 0,DATEADD(HOUR,-1,GETDATE()))),0) as [HOUR],count(nUserID) as [NUMBER OF LOGGEDIN USERS]
from tblLoggedinuser where nLoginType = 1 and (dLastPolledAt >= DATEADD(HOUR,(DATEDIFF(HOUR, 0,DATEADD(HOUR,-1,GETDATE()))),0)
and dLastPolledAt <= DATEADD(HOUR,(DATEDIFF(HOUR, 0, GETDATE())),0))

The second step in the job is the code I pasted in my first post. Since the job runs every hour - when it runs at 11:00 PM, since the first step is executed first, the row is already "put into" the TBL_LOGGEDIN_USERS_PERHOUR table.. by the time the .htm file is updated by the second step. A sample .htm file (for 12/28)is pasted below.. The 'last updated' shows that the job generated the .htm file ~ 49 sec 'after' 11:00 PM. By this time, the login count for 11:00 PM was 'already' present in the TBL_LOGGEDIN_USERS_PERHOUR table..

Need to figure out if I'm missing something which may be obvious..

Last updated: 2006-12-28 23:00:49.803

HOUR NUMBER OF CLIENT LOGINS

Dec 28 2006 12:00AM 23
Dec 28 2006 1:00AM 34
Dec 28 2006 2:00AM 37
Dec 28 2006 3:00AM 23
Dec 28 2006 4:00AM 32
Dec 28 2006 5:00AM 22
Dec 28 2006 6:00AM 44
Dec 28 2006 7:00AM 34
Dec 28 2006 8:00AM 30
Dec 28 2006 9:00AM 31
Dec 28 2006 10:00AM 21
Dec 28 2006 11:00AM 23
Dec 28 2006 12:00PM 23
Dec 28 2006 1:00PM 30
Dec 28 2006 2:00PM 29
Dec 28 2006 3:00PM 15
Dec 28 2006 4:00PM 31
Dec 28 2006 5:00PM 32
Dec 28 2006 6:00PM 19
Dec 28 2006 7:00PM 15
Dec 28 2006 8:00PM 17
Dec 28 2006 9:00PM 26
Dec 28 2006 10:00PM 26
-- END OF FILE

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2006-12-29 : 16:59:31

Guess I'm either missing it or you just confirmed what I was talking about... I think the latter of the two is true

If I use the date formulas from your per-hour calculations, like this...

DECLARE @SomeDateTime DATETIME
SET @SomeDateTime = '2006-12-28 23:00:49.803' --This is the run time you posted...

SELECT 
DATEADD(HOUR,(DATEDIFF(HOUR, 0,DATEADD(HOUR,-1,@SomeDateTime))),0) as [HOUR],

DATEADD(HOUR,(DATEDIFF(HOUR, 0,DATEADD(HOUR,-1,@SomeDateTime))),0) AS FirstCriteria,
DATEADD(HOUR,(DATEDIFF(HOUR, 0, @SomeDateTime)),0) AS SecondCriteria

... here's what I get...

HOUR                     FirstCriteria            SecondCriteria          
------------------------ ------------------------ ------------------------
2006-12-28 22:00:00.000  2006-12-28 22:00:00.000  2006-12-28 23:00:00.000

(1 row(s) affected)

See any problem there?

--Jeff Moden

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2006-12-29 : 17:08:51

Just in case you missed it, what that means is that the code for the first job is fine... the code for the second job is not because the logins for the 11-12PM have not yet been accumulated! You must run the 2nd job at 00:00:49... not 23:00:49. And, the code for the second job MUST be setup to look at the previous day's information. Just like I said...

--Jeff Moden

panthagani
Yak Posting Veteran

58 Posts

Posted - 2006-12-31 : 16:09:28

I noticed the erroneous part and modified the code to collect the logins for 11:00 PM to 12:00 AM as well. Here's the sample. I am looking at making it still simpler, but for now, it serves the purpose.

IF DATEPART(HH,GETDATE()) = 0
BEGIN

SELECT CONVERT(NVARCHAR,HOUR,100)AS [ROW1],
CONVERT(NVARCHAR,NUMBER_OF_USERS) AS [ROW2] INTO #TEMP_LOGINSPERHOUR1 FROM TBL_LOGGEDIN_USERS_PERHOUR
WITH (NOLOCK)
WHERE HOUR >= DATEADD(DAY,(DATEDIFF(DAY,0,GETDATE()-1)),0)
AND HOUR < DATEADD(DAY,(DATEDIFF(DAY,0,GETDATE())),0)
ORDER BY HOUR
SET @FLAG = 1

END

ELSE
BEGIN

SELECT CONVERT(NVARCHAR,HOUR,100)AS [ROW1],
CONVERT(NVARCHAR,NUMBER_OF_USERS) AS [ROW2] INTO #TEMP_LOGINSPERHOUR2 FROM TBL_LOGGEDIN_USERS_PERHOUR
WITH (NOLOCK)
WHERE HOUR >= DATEADD(DAY,(DATEDIFF(DAY,0,GETDATE())),0)
ORDER BY HOUR

END

And, the part of of code which now, "includes" the 11:00 PM to 12:00 AM logins in the same day's report is as below.

IF DATEPART(HH,GETDATE()) = 0
BEGIN
SET @REPPATH = '\\TKESTIIS5\LOGINSPERHOUR\' + REPLACE(CONVERT(NVARCHAR(10), GETDATE()-1, 101),'/', '') + '.HTM'
EXEC SP_MAKEWEBTASK @REPPATH , 'SELECT * FROM TBL_CUSTOM_LOGINSPERHOUR' ,
@COLHEADERS = 0 , @RESULTSTITLE = 'Talisma Client Logins Per Hour Report',
@WEBPAGETITLE = 'Talisma Client Logins Per Hour Report' ,@LASTUPDATED = 1
END

ELSE
BEGIN
SET @REPPATH = '\\TKESTIIS5\LOGINSPERHOUR\' + REPLACE(CONVERT(NVARCHAR(10), GETDATE(), 101),'/', '') + '.HTM'
EXEC SP_MAKEWEBTASK @REPPATH , 'SELECT * FROM TBL_CUSTOM_LOGINSPERHOUR' ,
@COLHEADERS = 0 , @RESULTSTITLE = 'Talisma Client Logins Per Hour Report',
@WEBPAGETITLE = 'Talisma Client Logins Per Hour Report' ,@LASTUPDATED = 1
END

Jeff - Thanks again, for your help with this one!

-Vijay

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2006-12-31 : 17:26:55

Instead of maintaining 2 pieces of code in the second half...

DECLARE @Offset
SET @Offset = CASE
WHEN DATEPART(HH,GETDATE()) = 0
THEN -1
ELSE 0
END

SET @REPPATH = '\\TKESTIIS5\LOGINSPERHOUR\' + REPLACE(CONVERT(NVARCHAR(10), GETDATE()+@Offset, 101),'/', '') + '.HTM'
EXEC SP_MAKEWEBTASK @REPPATH , 'SELECT * FROM TBL_CUSTOM_LOGINSPERHOUR' ,
@COLHEADERS = 0 , @RESULTSTITLE = 'Talisma Client Logins Per Hour Report',
@WEBPAGETITLE = 'Talisma Client Logins Per Hour Report' ,@LASTUPDATED = 1

Might be able to do something similar on the first part.

--Jeff Moden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources