Why won't this sql run as a job

[timscronin]

I have the sql below that I can run from query anlayzer, but it will not run as a job. Any ideas?

SET QUOTED_IDENTIFIER OFF

/* Start with master DB */
USE master

/* Create Variables */
DECLARE @DBName CHAR(64)
DECLARE @TableName CHAR(64)
DECLARE @FQTableName CHAR(64)
DECLARE @TempVar CHAR(256)

/* Create DB List */
DECLARE DBCursor CURSOR FOR
SELECT name
FROM master..sysdatabases where name = 'development'

OPEN DBCursor

FETCH NEXT
FROM DBCursor
INTO @DBName

/* Create Database Loop */
WHILE @@FETCH_STATUS = 0
BEGIN
/* Retrieve Table List */
PRINT 'Retrieving Table List for DB ' + @DBName

EXEC ('SELECT name AS TableName INTO ##TableNames FROM [' + @DBName + ']..sysobjects WHERE type = ''U''')

/* Open Table List */
DECLARE TableCursor CURSOR FOR
SELECT TableName
FROM ##TableNames

OPEN TableCursor

FETCH NEXT
FROM TableCursor
INTO @TableName

/* Create Table Loop */
WHILE @@FETCH_STATUS = 0
BEGIN
/* Add DB Name to Table Name */
SELECT @FQTableName = QUOTENAME(RTRIM(@DBName)) + '..' + QUOTENAME(RTRIM(@TableName))
SELECT @TableName = RTRIM(@DBName) + '..' + RTRIM(@TableName)
/* fix from ms */
SET ARITHABORT ON
SET QUOTED_IDENTIFIER ON
-- DBCC CHECKTABLE(mytable)

/* ReIndex Table */
PRINT 'ReIndexing Table ' + @TableName
DBCC DBREINDEX(@TableName)

/* Update Statics on Table */
PRINT 'Updating Statistics on Table ' + @TableName
EXEC ('UPDATE STATISTICS ' + @FQTableName)

/* Get Next Table Name */
FETCH NEXT
FROM TableCursor
INTO @TableName
END

/* Close Table Cursor */
CLOSE TableCursor
DEALLOCATE TableCursor

/* Remove Tempory Table */
DROP TABLE ##TableNames

/* Get Next Table Name */
FETCH NEXT
FROM DBCursor
INTO @DBName
END

/* Close DB Curosor */
CLOSE DBCursor
DEALLOCATE DBCursor

/* Finished */

 

[JohnBates]

timscronin,

Exactly what happens when you try to run it as a job? do you get any error codes?

Is this sql in a stored procedure? I think (I may be wrong about this) the best way to run this as a job would be to have this in a Step in your Job: exec yourStoredProc

all the sql code would be in yourStoredProc

HTH, John

 

[timscronin]

Still will not work I get the following error message

Executed as user: ADC\sqlexec. Retrieving Table List for DB Development [SQLSTATE 01000] (Message 0) ReIndexing Table Development..cms_appointments [SQLSTATE 01000] (Message 0) DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528) Updating Statistics on Table Development..cms_appointments [SQLSTATE 01000] (Message 0) Could not complete cursor operation because the set options have changed since the cursor was declared. [SQLSTATE 42000] (Error 16958). The step failed.

 

[SQLBill]

What makes you think it won't run as a job? Have you tried it? If so, you haven't provided any error messages or anything else to indicate it failed.

I'm not positive, but I believe there might be a limit on the length of a job step. Try John's suggestion of making it a stored procedure and running the sp via the job.

-SQLBill

Posting advice: FAQ481-4875

 

[timscronin]

Yes, the error message I posted was from when I tried to run it as a job, and as a stored proc, I get the same error message

 

[SQLBill]

Looks like you and I posted at the same time.

Please look in the SQL Server Error log and the Windows Event Viewer logs and see if there are any other error messages that apply to this issue.

-SQLBill

 

[timscronin]

None in either, on the MS site there is a vague reference to the error, but it applied to sql 6.5