Cusrror Problem - 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)

Cusrror Problem

Author

Topic

satya068
Posting Yak Master

233 Posts

Posted - 2010-03-26 : 06:57:33

Hi..when i executed this script it was stopping after RECORD_COUNT it was not processing further from which i highlited in the below script,even i am not getting any error message also..could any able to find out the solution pls?

i am getting null in my final table for the columns no_of_inserts and no_of_updates.

might be any cusrror prblem?

use PAS_RDB
DECLARE @TableName sysname,
@SQL 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
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 ROWS_COUNT ' +

'FROM ['+ @TableName+']'

print @sql

EXEC SP_EXECUTESQL @SQL

SELECT @SQL =

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

print @sql
EXEC SP_EXECUTESQL @SQL

SELECT @SQL =

'insert into UPDATE_COUNT (TABLE_NAME, NO_OF_INSERTS)
SELECT ''' + @TableName + '''as TABLE_NAME,Sum(case when(' + convert(varchar,@UPDATE_DATE ) + ' >= convert(varchar, @TODAY ) then 1 else 0 end) as INSERT_COUNT' +
'FROM [' + @TableName + ']''group by ' + @tablename + ''

print @sql
EXEC SP_EXECUTESQL @SQL
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
go
SELECT RECORD_COUNT.TABLE_NAME,RECORD_COUNT.ROW_COUNT,INSERT_COUNT.NO_OF_INSERTS,UPDATE_COUNT.NO_OF_UPDATES
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

thanx

Satya

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-03-26 : 07:16:38

Can you please post table scrip too.

Vaibhav T

satya068
Posting Yak Master

233 Posts

Posted - 2010-03-26 : 07:39:09

Hi..
vaibhav

ur asking about Insert_count n update_count table script wright?

Satya

satya068
Posting Yak Master

233 Posts

Posted - 2010-03-26 : 07:41:44

Hi Viabhav,

This is Insert_count

USE [PAS_RDB]
GO
/****** Object: Table [dbo].[INSERT_COUNT] Script Date: 03/26/2010 11:39:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[INSERT_COUNT](
[TABLE_NAME] [varchar](100) NULL,
[NO_OF_INSERTS] [int] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

This is for UPDATE_COUNT
USE [PAS_RDB]
GO
/****** Object: Table [dbo].[UPDATE_COUNT] Script Date: 03/26/2010 11:40:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[UPDATE_COUNT](
[TABLE_NAME] [varchar](100) NULL,
[NO_OF_UPDATES] [int] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Satya

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-26 : 08:35:26

This won't work for tables that are not in the default schema.

There are 10 types of people in the world, those that understand binary, and those that don't.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-03-26 : 09:11:26

Where you are passing parameter @Insert_date and @UPDATE_DATE

Vaibhav T

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources