Missing Data in the databse tables

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)

Missing Data in the databse tables

Author

Topic

satya068
Posting Yak Master

233 Posts

Posted - 2010-03-19 : 05:16:18

Hi..i got struck in the middle
could any help me in this query pls?

after running the below script i am not getting any error message but...the data is not comming to the database tables.

i designed tables in both databases PAS_RDB and PAS_SCHEMA.

TABLE_NAME VARCHAR(100)
ROW_COUNT INT

TABLE_NAME VARCHAR(100)
ROW_COUNT INT

TABLE_NAME VARCHAR(100)
UPDATE_COUNT INT

these three table i created in both PAD_RDB and PAS_SCHEMA.

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)
DECLARE @LOOP_COUNT int
SET @LOOP_COUNT = 1

WHILE (@LOOP_COUNT < 3)
BEGIN

IF @LOOP_COUNT = 1 USE PAS_RDB
IF @LOOP_COUNT > 1 USE PAS_SCHEMA

DECLARE tables_cursor CURSOR FAST_FORWARD
FOR
SELECT name FROM sys.tables
OPEN tables_cursor

FETCH NEXT FROM tables_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, INSERT_COUNT)
SELECT ''' + @TableName + '''as TABLE_NAME, COUNT(*) as INSERT_COUNT ' +

'FROM [' + @TableName + '] where' + convert(varchar, @INSERT_DATE) +' >= '''+ convert(varchar, @TODAY) + ''' group by ' + @tablename + ''

print @sql

EXEC SP_EXECUTESQL @SQL

SELECT @SQL =
'insert into UPDATE_COUNT (TABLE_NAME, UPDATE_COUNT)
SELECT ''' + @TableName + '''as TABLE_NAME, COUNT(*) as UPDATE_COUNT' +

'FROM [' + @TableName + '] where' + convert(varchar, @UPDATE_DATE) +' >= '''+ convert(varchar, @TODAY) + ''' group by ' + @tablename + ''

print @sql
EXEC SP_EXECUTESQL @SQL

FETCH NEXT FROM tables_cursor INTO @TableName

END

CLOSE tables_cursor

DEALLOCATE tables_cursor

SET @LOOP_COUNT = @LOOP_COUNT + 1

END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-19 : 13:00:58

you cant change database context like below inside a procedure

IF @LOOP_COUNT = 1 USE PAS_RDB
IF @LOOP_COUNT > 1 USE PAS_SCHEMA

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources