SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 having issues with DECLARE
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

amorg53666
Starting Member

USA
3 Posts

Posted - 08/05/2009 :  02:37:21  Show Profile  Reply with Quote
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  Show Profile  Reply with Quote
One last thing....I know that the conditional deletion and the recreation of the table is fine...but just something in the cursor section.
Go to Top of Page

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 08/05/2009 :  02:55:09  Show Profile  Send rajdaksha a Yahoo! Message  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30219 Posts

Posted - 08/05/2009 :  03:29:48  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Use sp_executesql instead of exec, to get value back from the executed query.


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

amorg53666
Starting Member

USA
3 Posts

Posted - 08/05/2009 :  04:46:03  Show Profile  Reply with Quote
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.
Go to Top of Page

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 08/05/2009 :  04:55:45  Show Profile  Send rajdaksha a Yahoo! Message  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000