Execution Error - 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)

Execution Error

Author

Topic

satya068
Posting Yak Master

233 Posts

Posted - 2010-03-25 : 04:44:03

Hi...

Theis is the part of script which i am using,actually when i execuetd the script fot the first time all the records inserted into new table TOTAL_RECORD_COUNT.

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

When i executed second time an error message

'Insert into RECORD_COUNT (TABLE_NAME, ROW_COUNT) SELECT 'RDB_PAS_EAS_DETAILS_GEC'as TABLE_NAME, COUNT(*)as ROWS_COUNT FROM [RDB_PAS_EAS_DETAILS_GEC]

(1 row(s) affected)
Msg 2714, Level 16, State 6, Line 55

There is already an object named 'TOTAL_RECORD_COUNT' in the database.'

WHen ever i executed this query i need the table should be truncated and new data should enter into the table.

could you bale to help me in this query...thanx in advance.

Satya

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-25 : 04:48:09

[code]
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
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

satya068
Posting Yak Master

233 Posts

Posted - 2010-03-25 : 05:10:29

Fred..
this script worked.
i am really thanx once again.

ther is a different problem i am getting now..there are 2 rows whic are repeating at the end of the table,when i executed each time that two rows are repeating.
this the message i am getting each time total increasing by 2

Insert into RECORD_COUNT (TABLE_NAME, ROW_COUNT) SELECT 'RDB_PAS_EAS_DETAILS_GEC'as TABLE_NAME, COUNT(*)as ROWS_COUNT FROM [RDB_PAS_EAS_DETAILS_GEC]

(1 row(s) affected)

Insert into RECORD_COUNT (TABLE_NAME, ROW_COUNT) SELECT 'RDB_PAS_URGENCY_CODE'as TABLE_NAME, COUNT(*)as ROWS_COUNT FROM [RDB_PAS_URGENCY_CODE]

(1 row(s) affected)

Msg 16916, Level 16, State 1, Line 51
A cursor with the name 'table_cursor' does not exist.
Msg 16916, Level 16, State 1, Line 53
A cursor with the name 'table_cursor' does not exist.
Msg 16916, Level 16, State 1, Line 54
A cursor with the name 'table_cursor' does not exist.

(1089 row(s) affected)

(1089 row(s) affected)

can i know y this happening ,this is my script

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 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, 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 table_cursor INTO @TableName

CLOSE table_cursor
DEALLOCATE table_cursor
END

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

Satya

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-25 : 05:18:29

close and deallocate has to be done AFTER the 'END' of your loop.

No, you're never too old to Yak'n'Roll if you're too young to die.

satya068
Posting Yak Master

233 Posts

Posted - 2010-03-25 : 05:29:05

Fred..

syntax error i done all the posibilites to my knowledge

Msg 102, Level 15, State 1, Line 56
Incorrect syntax near 'TOTAL_RECORD_COUNT'.
Msg 102, Level 15, State 1, Line 10
Incorrect syntax near 'END'.

i moved the close and deallocate to the end

IF (Object_ID(N'TOTAL_RECORD_COUNT')IS NOT NULL)
DROP TABLE TOTAL_RECORD_COUNT -------syntax error at this point
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

CLOSE table_cursor
DEALLOCATE table_cursor
END ------syntax error at this point

Satya

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-25 : 05:32:18

get rid of END STATEMENT.

satya068
Posting Yak Master

233 Posts

Posted - 2010-03-25 : 05:37:52

i deleted the end
now only 1 syntax error

Msg 102, Level 15, State 1, Line 56
Incorrect syntax near 'TOTAL_RECORD_COUNT'.

do i need to chaNge anything before are after DROP TABLE TOTAL_REOCRD_COUNT

Thanx

Satya

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-25 : 05:38:04

[code]
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 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, 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 table_cursor INTO @TableName

CLOSE table_cursor
DEALLOCATE table_cursor
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

[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-25 : 05:39:51

You cannot delete the END because it is part of your
WHILE
Begin
...
End

No, you're never too old to Yak'n'Roll if you're too young to die.

satya068
Posting Yak Master

233 Posts

Posted - 2010-03-25 : 05:49:03

Fred
...it worked.
i got expected output.

thanx for ur great help

Satya

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-25 : 05:54:02

welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

satya068
Posting Yak Master

233 Posts

Posted - 2010-03-25 : 07:31:50

fred,

the total rows in the table are repeating suppose in my 1st run there are 1000 rows,n when ru the secod time it getting double i.e 2000 rows.gradually increasing on my each execution of the script.

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 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, 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 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

how to clear the old data?

thanx

Satya

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-25 : 07:56:51

...
DECLARE @DATABASE varchar(255)
truncate table record_count
truncate table insert_count
truncate table update_count
DECLARE table_cursor CURSOR FAST_FORWARD
...

No, you're never too old to Yak'n'Roll if you're too young to die.

satya068
Posting Yak Master

233 Posts

Posted - 2010-03-25 : 08:27:21

Thanx fred,

this time it worked perfectly.

Satya

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources