I have created a SQL Server 2008 Agent job that runs against all databases on the server. I am using the undocumented MS procedure, sp_MSforEachDB
. I am specifying only certain databases to be processed and when these databases are found, if ERROR_NUMBER = 208
, I want a table to be created and processing to continue to the next database. I also want to omit the system databases so the tabel doesn't get created in a system database. I though by specifying the names of the databases I want to process, I could create a table where none existed. The code I show below works if the 'focus' is set on a particular database. Also, when I run it, I get a table created in the master database, which I do not want to do. What I am trying to do with the code I created is:
1. select all the databases on the SQL server with name like "xyz_%"
2. if table "SESSIONS" exists, then delete all records older than 5 minutes
3. if table "SESSIONS" doesn't exist, then create it and continue processing -
this is where I get the 208 error - invalid object because this table does not exist
When the job sees there is no Sessions table, the job stops and no other databases are processed. I need all the named databases in 1 to be processed. If the SESSIONS table is not present, I get a 208 error that says "Invalid object name 'master.SchemaName.sessions'. If I set the 'focus' to the database without the sessions table, my code runs and the tables is created but I still get an error 208 - Invalid object name 'master.SchemaName.sessions'. No other error are displayed.
If someone could look at my code to see where my problem is, that would be great.
I have verified that I am getting the 208 error by using the Try Catch below. I also get error 2760 on the 2nd Try Catch that says the specified schema doesn't exist or I don't have access. I am an admin on this server.
I have been using SQL for a few years and I don't consider myself an expert. I have researched this on Google to come up with the code I have below. Any help would be appreciated.
declare @ERROR INT
BEGIN TRY
exec sp_MSforeachdb 'IF "[?]" NOT IN ("master", "model","msdb", "tempdb")
BEGIN
DELETE FROM [?].schema.sessions WHERE name like ''xyz_%''
AND sessionStart <DATEADD(mi, -5,GETDATE())
END'
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS error_number, ERROR_MESSAGE() AS error_message
SELECT @ERROR = ERROR_NUMBER()
If @ERROR = 208
BEGIN TRY
/* create the Sessions table */
BEGIN
CREATE TABLE [SCHEMA].[SESSIONS](
[authuser] [varchar](30) NULL,
[sessionID] [char](36) NULL,
[sessionStart] [datetime] NULL)
grant select,delete on reviewadmin.sessions to public;
END
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS error_number, ERROR_MESSAGE() AS error_message
END CATCH
END CATCH
You could try using
DB_ID()
, I believe theDB_ID()
is always 1-4 (unless you have a distribution database). In theory, just check to see if DB_ID() is greater than 4:Also it doesn't look like you're using the correct database in the error handler - surely your try catch that creates the table should be in the call to
sp_MSforeachdb
as well?I mean in your text you're running for each db, including this in a try catch:
One more thing - at the moment you seem to have hard coded the value
reviewadmin.sessions
in your permission granting code - I assume you meant that to be your new[?].[Schema].[Sessions]
table - again which should be in the call tosp_MSforeachdb
so it knows which database to run on.I hope I've given you enough to go on, I'm not at a machine where I can write & test the whole thing I'm afraid!
If you just want databases with the name
xyz_%
:Note we can now skip the check for DB_ID > 4, since none of the system databases match your naming convention anyway.
OK, I've rethought this. Rather than trying and then catching the error if it fails, how about this new plan:
This should hopefully do it: