Link to home
Start Free TrialLog in
Avatar of CUOL-MIS
CUOL-MISFlag for United States of America

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),datepart(month,getdate())) + '/1/' + convert(char(4),datepart(year,getdate())))

/* 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),datepart(month,getdate())) + ''/1/'' + convert(char(4),datepart(year,getdate())))' + 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
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of CUOL-MIS

ASKER

Thank you for the quick response! That did the trick.