Script runs fine in QA, not as a job

I'm running the following script, which works fin in Query Analyzer.
When I'm running it in a job, the job fails.
When I'm running as a stored proc, through a job that calls the SP, I get a
failure.
When I lauch the stored proc from Query Analyzer, I get (DBCC failed because
the following SET options have incorrect settings: 'ANSI_NULLS.') on one
table.
I added these 2 lines in the Stored Proc without any success.
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
How can I have this script run as a job?
Running SQL2000, sp3a on Windows 2000 Server.
My reindex script
DECLARE @.TableName VARCHAR(255)
DECLARE @.exec_string VARCHAR(255)
DECLARE TableCursor CURSOR FOR
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE UPPER(TABLE_TYPE) = 'BASE TABLE'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @.TableName
WHILE @.@.FETCH_STATUS = 0
BEGIN
select @.exec_string = 'dbcc dbreindex ([' + @.TableName + '])'
exec(@.exec_string)
--DBCC DBREINDEX([@.TableName])
FETCH NEXT FROM TableCursor INTO @.TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO
--
La Senza SupportThe mostly cause of the problem is security context. When you run the
code interactively in Query Analyzer you are running under your
security context. When you run it as a job you are using SQL Agent's
security context. Check what user is used to start the SQL Agent and
make sure it has the proper permissions to execute the job.
-Uncle Pete
La Senza Support wrote:
> I'm running the following script, which works fin in Query Analyzer.
> When I'm running it in a job, the job fails.
> When I'm running as a stored proc, through a job that calls the SP, I get a
> failure.
> When I lauch the stored proc from Query Analyzer, I get (DBCC failed because
> the following SET options have incorrect settings: 'ANSI_NULLS.') on one
> table.
> I added these 2 lines in the Stored Proc without any success.
> SET QUOTED_IDENTIFIER ON
> SET ARITHABORT ON
> How can I have this script run as a job?
> Running SQL2000, sp3a on Windows 2000 Server.
>
> My reindex script
> DECLARE @.TableName VARCHAR(255)
> DECLARE @.exec_string VARCHAR(255)
> DECLARE TableCursor CURSOR FOR
> SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
> WHERE UPPER(TABLE_TYPE) = 'BASE TABLE'
> OPEN TableCursor
> FETCH NEXT FROM TableCursor INTO @.TableName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> select @.exec_string = 'dbcc dbreindex ([' + @.TableName + '])'
> exec(@.exec_string)
> --DBCC DBREINDEX([@.TableName])
> FETCH NEXT FROM TableCursor INTO @.TableName
> END
> CLOSE TableCursor
> DEALLOCATE TableCursor
> GO
> --
> La Senza Support|||Have you thought to create a stored procedure which holds all that code?
You could call the stored procedure from within the job.
Also, keep in mind that the user that owns the job must have the appropriate
permissions to execute the stored procedure (and its underlying statements).
Keith Kratochvil
"La Senza Support" <LaSenzaSupport@.discussions.microsoft.com> wrote in
message news:BAE98C02-7721-499E-A79F-3B3AB0A36C99@.microsoft.com...
> I'm running the following script, which works fin in Query Analyzer.
> When I'm running it in a job, the job fails.
> When I'm running as a stored proc, through a job that calls the SP, I get
> a
> failure.
> When I lauch the stored proc from Query Analyzer, I get (DBCC failed
> because
> the following SET options have incorrect settings: 'ANSI_NULLS.') on one
> table.
> I added these 2 lines in the Stored Proc without any success.
> SET QUOTED_IDENTIFIER ON
> SET ARITHABORT ON
> How can I have this script run as a job?
> Running SQL2000, sp3a on Windows 2000 Server.
>
> My reindex script
> DECLARE @.TableName VARCHAR(255)
> DECLARE @.exec_string VARCHAR(255)
> DECLARE TableCursor CURSOR FOR
> SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
> WHERE UPPER(TABLE_TYPE) = 'BASE TABLE'
> OPEN TableCursor
> FETCH NEXT FROM TableCursor INTO @.TableName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> select @.exec_string = 'dbcc dbreindex ([' + @.TableName + '])'
> exec(@.exec_string)
> --DBCC DBREINDEX([@.TableName])
> FETCH NEXT FROM TableCursor INTO @.TableName
> END
> CLOSE TableCursor
> DEALLOCATE TableCursor
> GO
> --
> La Senza Support