HY007 error on job but not on direct procedure call

[SUAU Romain]

Hello,

I have trouble on a stored procedure aimed to clean database configuration.
On the developpement servers, developpers are able to create databases, and
very often I can find parameters like "Select into !bulkcopy" or "Truncate
log on checkpoint". After having many times asked them why they did this and
why they shouldn't do it if not necessary I decided to put a job which
checks and repair the configuration. This job is aimed to work on SQL7 +
SQL2000 platforms with hundreds of databases.

My trouble is that the stored procedure works well, but if I call it from a
scheduled job, it generates an error in the middle of informational errors
and the procedure aborts :
Associated statement is not prepared [SQLSTATE HY007] (Error 0)

For example on a server with more than 3 databases with the wrong modes the
job log is :
Base [test] en mode select into/bulkcopy [SQLSTATE 42000] (Error 50000)
Associated statement is not prepared [SQLSTATE HY007] (Error 0)
Base [test2] en mode select into/bulkcopy [SQLSTATE 42000] (Error 50000).
The step failed.

So the [test3] database is not checked as the others...

Here is the procedure code :

CREATE PROCEDURE clean_database_model AS

DECLARE @current_name varchar(255)

DECLARE cc CURSOR FOR
SELECT name FROM dbo.sysdatabases
WHERE name != 'msdb' AND name != 'master' AND name != 'model' AND
name!='tempdb'
OPEN cc
FETCH NEXT FROM cc INTO @current_name
WHILE @@fetch_status = 0
BEGIN
IF ((SELECT status & 4 FROM master..sysdatabases WHERE name =
@current_name ) = 4)
BEGIN
EXEC sp_dboption @current_name, 'select into/bulkcopy', FALSE
RAISERROR ("Base [%s] en mode select into/bulkcopy", 16, 1,@current_name)
END
IF ((SELECT status & 8 FROM master..sysdatabases WHERE name =
@current_name ) = 8)
BEGIN
EXEC sp_dboption @current_name, 'trunc. log on chkpt', FALSE
RAISERROR ("Base [%s] en mode truncate log on chkpt", 16,
1,@current_name)
END
FETCH NEXT FROM cc INTO @current_name
END
CLOSE cc
DEALLOCATE cc
GO

I can't use "databasepropertyex" if I want to have the same code on 7 & 2K
servers.

May you help me to solve this strange problem.
Regards

SUAU Romain

[SUAU Romain]

[Erland Sommarskog]

[posted and mailed, please reply in news]

SUAU Romain (rs...@free.fr) writes:
> My trouble is that the stored procedure works well, but if I call it
> from a scheduled job, it generates an error in the middle of
> informational errors and the procedure aborts :
> Associated statement is not prepared [SQLSTATE HY007] (Error 0)

Not sure what is going on, but SET NOCOUNT ON has cured many strange
errors so try that.

--
Erland Sommarskog, SQL Server MVP
som...@algonet.se
Books Online (updated!) for SQL 2000 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[SUAU Romain]

Thanks for the information. Here is the working procedure :

CREATE PROCEDURE clean_database_model AS
SET NOCOUNT ON

DECLARE @current_name varchar(255)
DECLARE @clean_error int

SELECT @clean_error = 0

DECLARE cc CURSOR FOR
SELECT name FROM dbo.sysdatabases
WHERE name != 'msdb' AND name != 'master' AND name != 'model' AND

name!='tempdb' AND name!='bdv'

OPEN cc
FETCH NEXT FROM cc INTO @current_name
WHILE @@fetch_status = 0
BEGIN
IF ((SELECT status & 4 FROM master..sysdatabases WHERE name =
@current_name ) = 4)
BEGIN
EXEC sp_dboption @current_name, 'select into/bulkcopy', FALSE

RAISERROR ('Base [%s] en mode select into/bulkcopy', 1, 1,@current_name)
SELECT @clean_error = @clean_error + 1

END
IF ((SELECT status & 8 FROM master..sysdatabases WHERE name =
@current_name ) = 8)
BEGIN
EXEC sp_dboption @current_name, 'trunc. log on chkpt', FALSE

RAISERROR ('Base [%s] en mode truncate log on chkpt', 1, 1,@current_name)
SELECT @clean_error = @clean_error + 1

END
FETCH NEXT FROM cc INTO @current_name
END
CLOSE cc
DEALLOCATE cc

IF @clean_error > 0
BEGIN
RAISERROR ('%d correction(s) effectuee(s)', 16, 1, @clean_error)
END
GO