SQL Store Procedure - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

SQL Store Procedure

Author

Topic

satya068
Posting Yak Master

233 Posts

Posted - 2010-08-18 : 04:18:02

Hi..

i have written a store procedure and i scheduled to run daily morning 8:10am.
when i check my table at 8:30am,i am finding duplicates to the previous day and for the current date i am getting expected data,this current date getting duplicates the next day morning.

EMP_APP_ELECTIVES 904561 1049 4954 2010-08-18 00:00:00.000
EMP_APP_ELECTIVES 903512 1018 4235 2010-08-17 00:00:00.000
EMP_APP_ELECTIVES 903512 1018 4235 2010-08-17 00:00:00.000
EMP_APP_ELECTIVES 902494 8 312 2010-08-16 00:00:00.000
EMP_APP_ELECTIVES 902494 8 312 2010-08-16 00:00:00.000
EMP_APP_ELECTIVES 902486 26 761 2010-08-15 00:00:00.000
EMP_APP_ELECTIVES 902486 26 761 2010-08-15 00:00:00.000
EMP_APP_ELECTIVES 902460 910 3997 2010-08-14 00:00:00.000
EMP_APP_ELECTIVES 902460 910 3997 2010-08-14
00:00:00.000

i am wondering is there anything i have to change in my scheduled SP?

Thanx

Satya

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2010-08-18 : 04:23:51

post your sp

--------------------
keeping it simple...

satya068
Posting Yak Master

233 Posts

Posted - 2010-08-18 : 04:25:29

USE [PAS_RDB]
GO
/****** Object: StoredProcedure [dbo].[SP_FULL_PAS_DAILY_RECORD_COUNT] Script Date: 08/18/2010 09:25:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SP_FULL_PAS_DAILY_RECORD_COUNT] AS
-- =============================================
-- Procedure to be scheduled to be run DAILY
-- counting numbers of records for each table
-- specified in FULL_PAS_DAILY_RECORD_COUNT.
-- These numbers of records will be used to
-- check overnight FULL PAS jobs
--
-- Change History
---==============
-- SS 07/04/2010 Initial Creation
-- =============================================

DECLARE @TableName sysname,
@SQL nvarchar(max),
@SQL1 nvarchar(max),
@SQL2 nvarchar(max),
@INSERT_DATE datetime,
@UPDATE_DATE datetime,
@INSERT_COUNT int,
@UPDATE_COUNT int

DECLARE @TODAY VARCHAR(10)
SET @TODAY = CONVERT(varchar(10), getdate(), 121)

DECLARE @DATABASE varchar(255)
TRUNCATE TABLE RECORD_COUNT
TRUNCATE TABLE INSERT_COUNT
TRUNCATE TABLE UPDATE_COUNT
DECLARE table_cursor CURSOR FAST_FORWARD
FOR
SELECT name FROM sys.tables
WHERE (NAME like 'RDB_PAS%'
and name not like 'RDB_PAS%DATA_TO_LOAD'
and name NOT like 'RDB_PAS_APP_ELECTIVES_NEW_WAYS')
OPEN table_cursor

FETCH NEXT FROM table_cursor INTO @TableName
WHILE @@FETCH_STATUS = 0

BEGIN

SELECT @SQL =
'Insert into RECORD_COUNT (TABLE_NAME, ROW_COUNT) SELECT ''' + @TableName + '''as TABLE_NAME, COUNT(*)as ROW_COUNT ' +

'FROM ['+ @TableName+']'

print @SQL

EXEC SP_EXECUTESQL @SQL

PRINT 'got to step 1'

--SELECT @SQL1 = 'insert into INSERT_COUNT (TABLE_NAME, NO_OF_INSERTS)' +
--'SELECT ''' + @TableName + '''as TABLE_NAME, Sum(case when INSERT_DATE >= ' + convert(varchar, @TODAY) + ' then 1 else 0 end) as INSERT_COUNT' +
--' FROM [' + @TableName + ']'

SELECT @SQL1 = 'insert into INSERT_COUNT (TABLE_NAME, NO_OF_INSERTS)' +
' Select ''' + @TableName + ''' as TABLE_NAME,count(convert(varchar,INSERT_DATE,101)) from ' + @TableName + '
where dateadd(day,datediff(day, 0, INSERT_DATE), 0) >= ''' + convert(varchar,@TODAY,101) + '''
--WHERE convert(varchar,INSERT_DATE,101) >= ''' + convert(varchar,@TODAY,101) + ''''
--SELECT count(*) as INSERT_COUNT FROM RDB_PAS_APPOINTMENTS WHERE INSERT_DATE >= DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)
--AND INSERT_DATE < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),1)

print @SQL1
EXEC SP_EXECUTESQL @SQL1
PRINT 'got to step 3'

SELECT @SQL2 = 'insert into UPDATE_COUNT (TABLE_NAME, NO_OF_UPDATES)' +
' Select ''' + @TableName + ''' as TABLE_NAME,count(convert(varchar,UPDATE_DATE,101)) from ' + @TableName + '
where dateadd(day,datediff(day, 0, UPDATE_DATE), 0) >= ''' + convert(varchar,@TODAY,101) + '''
--WHERE convert(varchar,UPDATE_DATE,101) >= ''' + convert(varchar,@TODAY,101) + ''''
--SELECT @SQL2 = 'insert into UPDATE_COUNT (TABLE_NAME, NO_OF_UPDATES)' +
--'SELECT ''' + @TableName + '''as TABLE_NAME, Sum(case when UPDATE_DATE >= ' + convert(varchar, @TODAY) + ' then 1 else 0 end) as UPDATE_COUNT' +
--' FROM [' + @TableName + ']'

print @SQL2
EXEC SP_EXECUTESQL @SQL2

FETCH NEXT FROM table_cursor INTO @TableName

END
CLOSE table_cursor
DEALLOCATE table_cursor

IF (Object_ID(N'TOTAL_RECORD_COUNT')IS NOT NULL)
DROP TABLE TOTAL_RECORD_COUNT

SELECT RECORD_COUNT.TABLE_NAME,RECORD_COUNT.ROW_COUNT,INSERT_COUNT.NO_OF_INSERTS,UPDATE_COUNT.NO_OF_UPDATES,dateadd(d,datediff(d,0,getdate()),0)
as RUN_DATE
INTO TOTAL_RECORD_COUNT
from
RECORD_COUNT
left outer join INSERT_COUNT on RECORD_COUNT.TABLE_NAME = INSERT_COUNT.TABLE_NAME
left outer join UPDATE_COUNT on RECORD_COUNT.TABLE_NAME = UPDATE_COUNT.TABLE_NAME
order by 1

--TRUNCATE TABLE PAS_AUDIT.dbo.FULL_PAS_DAILY_RECORD_COUNT

INSERT INTO PAS_AUDIT.dbo.FULL_PAS_DAILY_RECORD_COUNT
SELECT * FROM TOTAL_RECORD_COUNT

Satya

satya068
Posting Yak Master

233 Posts

Posted - 2010-08-18 : 04:26:21

quote:
Originally posted by satya068

USE [PAS_RDB]
GO
/****** Object: StoredProcedure [dbo].[SP_FULL_PAS_DAILY_RECORD_COUNT] Script Date: 08/18/2010 09:25:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SP_FULL_PAS_DAILY_RECORD_COUNT] AS
-- =============================================
-- Procedure to be scheduled to be run DAILY
-- counting numbers of records for each table
-- specified in FULL_PAS_DAILY_RECORD_COUNT.
-- These numbers of records will be used to
-- check overnight FULL PAS jobs
-- =============================================

DECLARE @TableName sysname,
@SQL nvarchar(max),
@SQL1 nvarchar(max),
@SQL2 nvarchar(max),
@INSERT_DATE datetime,
@UPDATE_DATE datetime,
@INSERT_COUNT int,
@UPDATE_COUNT int

DECLARE @TODAY VARCHAR(10)
SET @TODAY = CONVERT(varchar(10), getdate(), 121)

DECLARE @DATABASE varchar(255)
TRUNCATE TABLE RECORD_COUNT
TRUNCATE TABLE INSERT_COUNT
TRUNCATE TABLE UPDATE_COUNT
DECLARE table_cursor CURSOR FAST_FORWARD
FOR
SELECT name FROM sys.tables
WHERE (NAME like 'RDB_PAS%'
and name not like 'RDB_PAS%DATA_TO_LOAD'
and name NOT like 'RDB_PAS_APP_ELECTIVES_NEW_WAYS')
OPEN table_cursor

FETCH NEXT FROM table_cursor INTO @TableName
WHILE @@FETCH_STATUS = 0

BEGIN

SELECT @SQL =
'Insert into RECORD_COUNT (TABLE_NAME, ROW_COUNT) SELECT ''' + @TableName + '''as TABLE_NAME, COUNT(*)as ROW_COUNT ' +

'FROM ['+ @TableName+']'

print @SQL

EXEC SP_EXECUTESQL @SQL

PRINT 'got to step 1'

--SELECT @SQL1 = 'insert into INSERT_COUNT (TABLE_NAME, NO_OF_INSERTS)' +
--'SELECT ''' + @TableName + '''as TABLE_NAME, Sum(case when INSERT_DATE >= ' + convert(varchar, @TODAY) + ' then 1 else 0 end) as INSERT_COUNT' +
--' FROM [' + @TableName + ']'

SELECT @SQL1 = 'insert into INSERT_COUNT (TABLE_NAME, NO_OF_INSERTS)' +
' Select ''' + @TableName + ''' as TABLE_NAME,count(convert(varchar,INSERT_DATE,101)) from ' + @TableName + '
where dateadd(day,datediff(day, 0, INSERT_DATE), 0) >= ''' + convert(varchar,@TODAY,101) + '''
--WHERE convert(varchar,INSERT_DATE,101) >= ''' + convert(varchar,@TODAY,101) + ''''
--SELECT count(*) as INSERT_COUNT FROM RDB_PAS_APPOINTMENTS WHERE INSERT_DATE >= DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)
--AND INSERT_DATE < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),1)

print @SQL1
EXEC SP_EXECUTESQL @SQL1
PRINT 'got to step 3'

SELECT @SQL2 = 'insert into UPDATE_COUNT (TABLE_NAME, NO_OF_UPDATES)' +
' Select ''' + @TableName + ''' as TABLE_NAME,count(convert(varchar,UPDATE_DATE,101)) from ' + @TableName + '
where dateadd(day,datediff(day, 0, UPDATE_DATE), 0) >= ''' + convert(varchar,@TODAY,101) + '''
--WHERE convert(varchar,UPDATE_DATE,101) >= ''' + convert(varchar,@TODAY,101) + ''''
--SELECT @SQL2 = 'insert into UPDATE_COUNT (TABLE_NAME, NO_OF_UPDATES)' +
--'SELECT ''' + @TableName + '''as TABLE_NAME, Sum(case when UPDATE_DATE >= ' + convert(varchar, @TODAY) + ' then 1 else 0 end) as UPDATE_COUNT' +
--' FROM [' + @TableName + ']'

print @SQL2
EXEC SP_EXECUTESQL @SQL2

FETCH NEXT FROM table_cursor INTO @TableName

END
CLOSE table_cursor
DEALLOCATE table_cursor

IF (Object_ID(N'TOTAL_RECORD_COUNT')IS NOT NULL)
DROP TABLE TOTAL_RECORD_COUNT

SELECT RECORD_COUNT.TABLE_NAME,RECORD_COUNT.ROW_COUNT,INSERT_COUNT.NO_OF_INSERTS,UPDATE_COUNT.NO_OF_UPDATES,dateadd(d,datediff(d,0,getdate()),0)
as RUN_DATE
INTO TOTAL_RECORD_COUNT
from
RECORD_COUNT
left outer join INSERT_COUNT on RECORD_COUNT.TABLE_NAME = INSERT_COUNT.TABLE_NAME
left outer join UPDATE_COUNT on RECORD_COUNT.TABLE_NAME = UPDATE_COUNT.TABLE_NAME
order by 1

--TRUNCATE TABLE PAS_AUDIT.dbo.FULL_PAS_DAILY_RECORD_COUNT

INSERT INTO PAS_AUDIT.dbo.FULL_PAS_DAILY_RECORD_COUNT
SELECT * FROM TOTAL_RECORD_COUNT

Satya

Satya

satya068
Posting Yak Master

233 Posts

Posted - 2010-08-18 : 04:27:02

quote:
Originally posted by satya068

USE [PAS_RDB]
GO
/****** Object: StoredProcedure [dbo].[SP_FULL_PAS_DAILY_RECORD_COUNT] Script Date: 08/18/2010 09:25:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SP_FULL_PAS_DAILY_RECORD_COUNT] AS
-- =============================================
-- Procedure to be scheduled to be run DAILY
-- counting numbers of records for each table
-- specified in FULL_PAS_DAILY_RECORD_COUNT.
-- These numbers of records will be used to
-- check overnight FULL PAS jobs
------ =============================================

DECLARE @TableName sysname,
@SQL nvarchar(max),
@SQL1 nvarchar(max),
@SQL2 nvarchar(max),
@INSERT_DATE datetime,
@UPDATE_DATE datetime,
@INSERT_COUNT int,
@UPDATE_COUNT int

DECLARE @TODAY VARCHAR(10)
SET @TODAY = CONVERT(varchar(10), getdate(), 121)

DECLARE @DATABASE varchar(255)
TRUNCATE TABLE RECORD_COUNT
TRUNCATE TABLE INSERT_COUNT
TRUNCATE TABLE UPDATE_COUNT
DECLARE table_cursor CURSOR FAST_FORWARD
FOR
SELECT name FROM sys.tables
WHERE (NAME like 'RDB_PAS%'
and name not like 'RDB_PAS%DATA_TO_LOAD'
and name NOT like 'RDB_PAS_APP_ELECTIVES_NEW_WAYS')
OPEN table_cursor

FETCH NEXT FROM table_cursor INTO @TableName
WHILE @@FETCH_STATUS = 0

BEGIN

SELECT @SQL =
'Insert into RECORD_COUNT (TABLE_NAME, ROW_COUNT) SELECT ''' + @TableName + '''as TABLE_NAME, COUNT(*)as ROW_COUNT ' +

'FROM ['+ @TableName+']'

print @SQL

EXEC SP_EXECUTESQL @SQL

PRINT 'got to step 1'

--SELECT @SQL1 = 'insert into INSERT_COUNT (TABLE_NAME, NO_OF_INSERTS)' +
--'SELECT ''' + @TableName + '''as TABLE_NAME, Sum(case when INSERT_DATE >= ' + convert(varchar, @TODAY) + ' then 1 else 0 end) as INSERT_COUNT' +
--' FROM [' + @TableName + ']'

SELECT @SQL1 = 'insert into INSERT_COUNT (TABLE_NAME, NO_OF_INSERTS)' +
' Select ''' + @TableName + ''' as TABLE_NAME,count(convert(varchar,INSERT_DATE,101)) from ' + @TableName + '
where dateadd(day,datediff(day, 0, INSERT_DATE), 0) >= ''' + convert(varchar,@TODAY,101) + '''
--WHERE convert(varchar,INSERT_DATE,101) >= ''' + convert(varchar,@TODAY,101) + ''''
--SELECT count(*) as INSERT_COUNT FROM RDB_PAS_APPOINTMENTS WHERE INSERT_DATE >= DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)
--AND INSERT_DATE < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),1)

print @SQL1
EXEC SP_EXECUTESQL @SQL1
PRINT 'got to step 3'

SELECT @SQL2 = 'insert into UPDATE_COUNT (TABLE_NAME, NO_OF_UPDATES)' +
' Select ''' + @TableName + ''' as TABLE_NAME,count(convert(varchar,UPDATE_DATE,101)) from ' + @TableName + '
where dateadd(day,datediff(day, 0, UPDATE_DATE), 0) >= ''' + convert(varchar,@TODAY,101) + '''
--WHERE convert(varchar,UPDATE_DATE,101) >= ''' + convert(varchar,@TODAY,101) + ''''
--SELECT @SQL2 = 'insert into UPDATE_COUNT (TABLE_NAME, NO_OF_UPDATES)' +
--'SELECT ''' + @TableName + '''as TABLE_NAME, Sum(case when UPDATE_DATE >= ' + convert(varchar, @TODAY) + ' then 1 else 0 end) as UPDATE_COUNT' +
--' FROM [' + @TableName + ']'

print @SQL2
EXEC SP_EXECUTESQL @SQL2

FETCH NEXT FROM table_cursor INTO @TableName

END
CLOSE table_cursor
DEALLOCATE table_cursor

IF (Object_ID(N'TOTAL_RECORD_COUNT')IS NOT NULL)
DROP TABLE TOTAL_RECORD_COUNT

SELECT RECORD_COUNT.TABLE_NAME,RECORD_COUNT.ROW_COUNT,INSERT_COUNT.NO_OF_INSERTS,UPDATE_COUNT.NO_OF_UPDATES,dateadd(d,datediff(d,0,getdate()),0)
as RUN_DATE
INTO TOTAL_RECORD_COUNT
from
RECORD_COUNT
left outer join INSERT_COUNT on RECORD_COUNT.TABLE_NAME = INSERT_COUNT.TABLE_NAME
left outer join UPDATE_COUNT on RECORD_COUNT.TABLE_NAME = UPDATE_COUNT.TABLE_NAME
order by 1

--TRUNCATE TABLE PAS_AUDIT.dbo.FULL_PAS_DAILY_RECORD_COUNT

INSERT INTO PAS_AUDIT.dbo.FULL_PAS_DAILY_RECORD_COUNT
SELECT * FROM TOTAL_RECORD_COUNT

Satya

Satya

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2010-08-18 : 04:32:02

check in your sp that this part doesn't show the duplicates
run the script inside not as an sp so you can isolate
use group by to remove duplicates

quote:

SELECT RECORD_COUNT.TABLE_NAME,RECORD_COUNT.ROW_COUNT,INSERT_COUNT.NO_OF_INSERTS,UPDATE_COUNT.NO_OF_UPDATES,dateadd(d,datediff(d,0,getdate()),0)
as RUN_DATE
INTO TOTAL_RECORD_COUNT
from
RECORD_COUNT
left outer join INSERT_COUNT on RECORD_COUNT.TABLE_NAME = INSERT_COUNT.TABLE_NAME
left outer join UPDATE_COUNT on RECORD_COUNT.TABLE_NAME = UPDATE_COUNT.TABLE_NAME
order by 1

--------------------
keeping it simple...

satya068
Posting Yak Master

233 Posts

Posted - 2010-08-18 : 04:38:40

Hi jen,

Do you want me to insert this script in my SP which i scheduled to run every day morning?

Satya

satya068
Posting Yak Master

233 Posts

Posted - 2010-08-18 : 04:41:02

quote:
Originally posted by satya068

Hi jen,

Do you want me to insert this script in my SP which i scheduled to run every day morning?

Satya

Satya

satya068
Posting Yak Master

233 Posts

Posted - 2010-08-18 : 04:43:45

quote:
Originally posted by satya068

Hi jen,

is it ok if i group by run_date?
Satya

Satya

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2010-08-18 : 06:05:25

you need to check what the sproc is doing...how many inserts, how many selects...are they correct, no duplicates...etc

if you want help...post the ddl and dml scripts so we can simulate the scenario

--------------------
keeping it simple...

satya068
Posting Yak Master

233 Posts

Posted - 2010-08-18 : 07:09:33

Thanx jen,

Satya

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources