Script Won't Run in Job But Runs in Query Analyzer

I have a script that is basically a cursor that uses
sp_addrolemember to add all logins on the server to the
db_datareader role in every database. I use a variable
for each login. The script is in a job and scheduled to
run daily. I receive the following error: Msg 913, Sev
16: Could not find database ID %. Database may not be
activated yet or may be in transition. The problem is
that the EXACT same script will run in Query Analyzer with
NO errors. Why wouldn't the script give the same error in
Query Analyzer?
Thanks.I would have to see the script in order to (possibly) suggest anything...
:-)
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Brandon Tolleson" <anonymous@.discussions.microsoft.com> wrote in message
news:617901c3e5d7$b9b63000$a401280a@.phx.gbl...
> I have a script that is basically a cursor that uses
> sp_addrolemember to add all logins on the server to the
> db_datareader role in every database. I use a variable
> for each login. The script is in a job and scheduled to
> run daily. I receive the following error: Msg 913, Sev
> 16: Could not find database ID %. Database may not be
> activated yet or may be in transition. The problem is
> that the EXACT same script will run in Query Analyzer with
> NO errors. Why wouldn't the script give the same error in
> Query Analyzer?
> Thanks.|||DECLARE @.lname varchar(100)
DECLARE @.uname varchar(100)
Declare @.mystr varchar(1000)
Declare @.dbname varchar(100)
DECLARE MacroVal CURSOR FOR Select name from syslogins
where name like 'MyDomain\
%' order by name
OPEN MacroVal
FETCH NEXT FROM MacroVal INTO @.lname
WHILE(@.@.FETCH_STATUS <> -1)
BEGIN
IF (@.@.FETCH_STATUS <> -2)
BEGIN
Set @.uname = substring(@.lname,charindex('\',@.lname)
+1,50)
DECLARE DBName CURSOR FOR Select name from
sysdatabases order by name
OPEN DBName
FETCH NEXT FROM DBName INTO @.dbname
WHILE(@.@.FETCH_STATUS <> -1)
BEGIN
IF (@.@.FETCH_STATUS <> -2)
BEGIN
Set @.mystr = 'USE '+@.dbname+' If not
exists (select * from sysusers where SUSER_SNAME(sid)
= ''' +@.lname+''') Exec
sp_grantdbaccess '''+@.lname+''', '''+@.uname+''''
if @.uname not like 'student%'
BEGIN
Print (@.mystr)
Exec (@.mystr)
Set @.mystr = 'USE '+@.dbname+' If
exists (select * from sysusers where SUSER_SNAME(sid)
= '''+@.lname+''' and name <> ''dbo'') BEGIN EXEC
sp_addrolemember ''db_datareader'', '''+@.uname+''' EXEC
sp_addrolemember''db_backupoperator'', '''+@.uname+''' END'
Print (@.mystr)
Exec (@.mystr)
END
END
FETCH NEXT FROM DBName INTO @.dbname
END
CLOSE DBName
DEALLOCATE DBName
END
FETCH NEXT FROM MacroVal INTO @.lname
END
CLOSE MacroVal
DEALLOCATE MacroVal
GO
>--Original Message--
>I would have to see the script in order to (possibly)
suggest anything...
>:-)
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Brandon Tolleson" <anonymous@.discussions.microsoft.com>
wrote in message
>news:617901c3e5d7$b9b63000$a401280a@.phx.gbl...
>> I have a script that is basically a cursor that uses
>> sp_addrolemember to add all logins on the server to the
>> db_datareader role in every database. I use a variable
>> for each login. The script is in a job and scheduled to
>> run daily. I receive the following error: Msg 913, Sev
>> 16: Could not find database ID %. Database may not be
>> activated yet or may be in transition. The problem is
>> that the EXACT same script will run in Query Analyzer
with
>> NO errors. Why wouldn't the script give the same error
in
>> Query Analyzer?
>> Thanks.
>
>.
>|||There doesn't seem to be a problem with the script per se. I just ran it on my SQL Server and the
outcome was success. Did you specify an output file for the jobstep? This can sometimes give more
info about the problem. In my output file, I only had messages such as:
USE master If not exists (select * from sysusers where SUSER_SNAME(sid) = 'TIBORK\Tibor') Exec
sp_grantdbaccess 'TIBORK\Tibor', 'Tibor' [SQLSTATE 01000]
Granted database access to 'TIBORK\Tibor'. [SQLSTATE 01000]
You might want to go though the job in Agent so you didn't get any unfortunate linebreaks anywhere.
Although, the error message you posted didn't indicate that this is the problem.
My guess is that some database isn't accessible when you run the script. This is what the error
message say. You might want to search KB for the error message. Or possibly open a case with MS
support.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Brandon Tolleson" <anonymous@.discussions.microsoft.com> wrote in message
news:617901c3e5d7$b9b63000$a401280a@.phx.gbl...
> I have a script that is basically a cursor that uses
> sp_addrolemember to add all logins on the server to the
> db_datareader role in every database. I use a variable
> for each login. The script is in a job and scheduled to
> run daily. I receive the following error: Msg 913, Sev
> 16: Could not find database ID %. Database may not be
> activated yet or may be in transition. The problem is
> that the EXACT same script will run in Query Analyzer with
> NO errors. Why wouldn't the script give the same error in
> Query Analyzer?
> Thanks.|||I restarted the server and the job ran successfully. ?
Go figure.
>--Original Message--
>There doesn't seem to be a problem with the script per
se. I just ran it on my SQL Server and the
>outcome was success. Did you specify an output file for
the jobstep? This can sometimes give more
>info about the problem. In my output file, I only had
messages such as:
>USE master If not exists (select * from sysusers where
SUSER_SNAME(sid) = 'TIBORK\Tibor') Exec
>sp_grantdbaccess 'TIBORK\Tibor', 'Tibor' [SQLSTATE 01000]
>Granted database access to 'TIBORK\Tibor'. [SQLSTATE
01000]
>You might want to go though the job in Agent so you
didn't get any unfortunate linebreaks anywhere.
>Although, the error message you posted didn't indicate
that this is the problem.
>My guess is that some database isn't accessible when you
run the script. This is what the error
>message say. You might want to search KB for the error
message. Or possibly open a case with MS
>support.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at: http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Brandon Tolleson" <anonymous@.discussions.microsoft.com>
wrote in message
>news:617901c3e5d7$b9b63000$a401280a@.phx.gbl...
>> I have a script that is basically a cursor that uses
>> sp_addrolemember to add all logins on the server to the
>> db_datareader role in every database. I use a variable
>> for each login. The script is in a job and scheduled to
>> run daily. I receive the following error: Msg 913, Sev
>> 16: Could not find database ID %. Database may not be
>> activated yet or may be in transition. The problem is
>> that the EXACT same script will run in Query Analyzer
with
>> NO errors. Why wouldn't the script give the same error
in
>> Query Analyzer?
>> Thanks.
>
>.
>