| Author |
 Topic  |
|
|
amorg53666
Starting Member
USA
3 Posts |
 Posted - 08/05/2009 : 02:37:21
|
I am writing a query for my database that checks to see the last time a table was updated. Tables that are updated are coming from a table that has a list of tables. The column for this table is ID and TableName (kind of like a dictionary table that I can add tables to at any time in the event that I need to keep an eye on more stuff).
The goal is to create a table that keeps track of this table, as well as add a column called LastUpdated, which tells me when the table was last updated. This script would be a scheduled event that I would run every 6 hours to make sure things are running as they should.
Here's what I got so far:
******************************************************************
USE [LiveDB]
GO
--drop table if exists
IF OBJECT_ID( N'SysDRTables_CoCH_LastUpdated', N'U') IS NOT NULL
drop table [dbo].[SysDRTables_CoCH_LastUpdated]
GO
--create the table
CREATE TABLE
[dbo].[SysDRTables_CoCH_LastUpdated](
[ID] [int] IDENTITY(1,1) NOT NULL,
[TableName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[LastUpdated] [datetime],
CONSTRAINT [PK_SysDRTables_CoCH_LastUpdated] PRIMARY KEY CLUSTERED
(
[ID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
--Cursor
DECLARE @id int, @tab varchar(50), @updated datetime
DECLARE test_cursor CURSOR FOR
select ID, TableName from SysDRTables_CoCH
OPEN test_cursor
FETCH NEXT FROM test_cursor INTO @id, @tab
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC('SET @updated = (SELECT max(RowUpdateDateTime) FROM ' + @tab + ' (NOLOCK))')
EXEC('INSERT INTO SysDRTables_CoCH_LastUpdated (ID, TableName, LastUpdated) VALUES (@id, @tab, @updated')
FETCH NEXT FROM test_cursor INTO @id, @tab
END
CLOSE test_cursor
DEALLOCATE test_cursor
**********************************************************************
I keep getting the following error(s):
Msg 137, Level 15, State 1, Line 1
Must declare the scalar variable "@updated".
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@id".
Any assistance you can offer would be great. I am using SQL 2000, but I think this would work on 2005 (when I end up upgrading the whole database, or whatever the latest and greatest is when I decide to do so).
Thank you in advance with any suggestions.
|
|
|
amorg53666
Starting Member
USA
3 Posts |
Posted - 08/05/2009 : 02:38:42
|
| One last thing....I know that the conditional deletion and the recreation of the table is fine...but just something in the cursor section. |
 |
|
|
rajdaksha
Aged Yak Warrior
595 Posts |
Posted - 08/05/2009 : 02:55:09
|
Hi
Can you check the colored part
EXEC('INSERT INTO SysDRTables_CoCH_LastUpdated (ID, TableName, LastUpdated) VALUES (@id, @tab, @updated')]
I think it should be like this....
DECLARE @id int, @tab varchar(50), @updated datetime,@SQL VARCHAR(max)
set @id = 10
set @tab = '10'
set @updated = getdate()
SET @SQL = 'INSERT INTO SysDRTables_CoCH_LastUpdated (ID, TableName, LastUpdated) VALUES ('+CONVERT(VARCHAR(4),@id)+','+@tab+','''+CONVERT(VARCHAR(10),@updated,120)+''')'
SELECT @SQL
EXEC(@SQL)
INSERT INTO SysDRTables_CoCH_LastUpdated (ID, TableName, LastUpdated) VALUES (10,10,'2009-08-05')
-------------------------
R..
http://code.msdn.microsoft.com/SQLExamples/
http://msdn.microsoft.com/hi-in/library/bb500155(en-us).aspx
|
Edited by - rajdaksha on 08/05/2009 02:55:36 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 08/05/2009 : 03:29:48
|
Use sp_executesql instead of exec, to get value back from the executed query.
N 56°04'39.26"
E 12°55'05.63" |
|
|
|
amorg53666
Starting Member
USA
3 Posts |
Posted - 08/05/2009 : 04:46:03
|
quote:
Originally posted by rajdaksha
Hi
Can you check the colored part
EXEC('INSERT INTO SysDRTables_CoCH_LastUpdated (ID, TableName, LastUpdated) VALUES (@id, @tab, @updated')]
I think it should be like this....
DECLARE @id int, @tab varchar(50), @updated datetime,@SQL VARCHAR(max)
set @id = 10
set @tab = '10'
set @updated = getdate()
SET @SQL = 'INSERT INTO SysDRTables_CoCH_LastUpdated (ID, TableName, LastUpdated) VALUES ('+CONVERT(VARCHAR(4),@id)+','+@tab+','''+CONVERT(VARCHAR(10),@updated,120)+''')'
SELECT @SQL
EXEC(@SQL)
INSERT INTO SysDRTables_CoCH_LastUpdated (ID, TableName, LastUpdated) VALUES (10,10,'2009-08-05')
-------------------------
R..
http://code.msdn.microsoft.com/SQLExamples/
http://msdn.microsoft.com/hi-in/library/bb500155(en-us).aspx
The only issue I have with this is that you're setting the value to @id and @tab to values, where in reality the select statement should set them in the FETCH clause.
I can see the benefit of creating the query and setting it in @sql, so I will add this.
Oh...and @tab is short for table, so that's why it's a varchar. |
|
|
|
rajdaksha
Aged Yak Warrior
595 Posts |
Posted - 08/05/2009 : 04:55:45
|
Hi
I think first statment is right colored part...but you missed in second part
EXEC('SET @updated = (SELECT max(RowUpdateDateTime) FROM ' + @tab + ' (NOLOCK))')
EXEC('INSERT INTO SysDRTables_CoCH_LastUpdated (ID, TableName, LastUpdated) VALUES (@id, @tab, @updated')
I think it should be..like this
('+@id+','+@tab+','+ @updated+'))'
-------------------------
R..
http://code.msdn.microsoft.com/SQLExamples/
http://msdn.microsoft.com/hi-in/library/bb500155(en-us).aspx
|
Edited by - rajdaksha on 08/05/2009 04:57:25 |
|
|
| |
Topic |
|
|
|
having issues with DECLARE
