CUOL-MIS
asked on
Invalid object name 'sysdatabases'.
I am trying to creat a job to run the following script at month end. This was working until a total server rebuild. Now I get the following error the Invalid object name 'sysdatabases'. [SQLSTATE 42S02] (Error 208). I did not write the script but this is it. sysdatabases does exist in the master DB. Could this be some permission issue?
DECLARE @statement NVARCHAR(1000)
DECLARE @dbname NVARCHAR(64)
DECLARE @rundate DATETIME
/*
This cursor will collect all the databases names that begin with "PB_" .
*/
DECLARE dbnames cursor for select name from sysdatabases where name like 'PB_0506_1%'
select @rundate = (select convert(varchar(2),datepar t(month,ge tdate())) + '/1/' + convert(char(4),datepart(y ear,getdat e())))
/* Open the cursor to begin working with each database name */
open dbnames
/* Fetch the first database name from the cursor before going into the while loop */
FETCH next from dbnames into @dbname
/* Loop through the cursor until the fetch status is not zero or done with all database names */
WHILE @@FETCH_STATUS = 0
BEGIN
if ( select count(*) from eom.dbo.usertotals where dbname = @dbname and rundate=@rundate) > 0 goto skipped
set @statement = N'declare @totalusers INT' + char(13)
+ N'declare @rundate datetime' + char(13)
+ N'declare @bpusers INT' + char(13)
+ N'select @totalusers = (select count(customernumber) from ' + @dbname + '.dbo.usersecurity)' + char(13)
+ N'select @rundate = (select convert(varchar(2),datepar t(month,ge tdate())) + ''/1/'' + convert(char(4),datepart(y ear,getdat e())))' + char(13)
+ N'insert into eom.dbo.usertotals values(''' + @dbname + N''',@rundate,@totalusers, 0,NULL)' + char(13)
+ N'select @bpusers = (select count(customernumber) from ' + @dbname + '.dbo.usermisc where billpayaccess=''Y'')' + char(13)
+ N'update eom.dbo.usertotals set bpusers = @bpusers where rundate=@rundate and dbname=''' + @dbname + N'''' + char(13)
exec (@statement)
skipped:
/* Get the next database name and continue the loop. */
FETCH next from dbnames into @dbname
END
close dbnames
deallocate dbnames
go
DECLARE @statement NVARCHAR(1000)
DECLARE @dbname NVARCHAR(64)
DECLARE @rundate DATETIME
/*
This cursor will collect all the databases names that begin with "PB_" .
*/
DECLARE dbnames cursor for select name from sysdatabases where name like 'PB_0506_1%'
select @rundate = (select convert(varchar(2),datepar
/* Open the cursor to begin working with each database name */
open dbnames
/* Fetch the first database name from the cursor before going into the while loop */
FETCH next from dbnames into @dbname
/* Loop through the cursor until the fetch status is not zero or done with all database names */
WHILE @@FETCH_STATUS = 0
BEGIN
if ( select count(*) from eom.dbo.usertotals where dbname = @dbname and rundate=@rundate) > 0 goto skipped
set @statement = N'declare @totalusers INT' + char(13)
+ N'declare @rundate datetime' + char(13)
+ N'declare @bpusers INT' + char(13)
+ N'select @totalusers = (select count(customernumber) from ' + @dbname + '.dbo.usersecurity)' + char(13)
+ N'select @rundate = (select convert(varchar(2),datepar
+ N'insert into eom.dbo.usertotals values(''' + @dbname + N''',@rundate,@totalusers,
+ N'select @bpusers = (select count(customernumber) from ' + @dbname + '.dbo.usermisc where billpayaccess=''Y'')' + char(13)
+ N'update eom.dbo.usertotals set bpusers = @bpusers where rundate=@rundate and dbname=''' + @dbname + N'''' + char(13)
exec (@statement)
skipped:
/* Get the next database name and continue the loop. */
FETCH next from dbnames into @dbname
END
close dbnames
deallocate dbnames
go
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER