sp_certify_removable  
Author Message
dcleesfo





PostPosted: Tue Jan 06 12:54:36 CST 2004 Top

SQL Server Developer >> sp_certify_removable

Hello,

I'm running this:

begin






auto


begin
print 'failure to set to offline mode'
end
else
begin
print 'successfully set to offline mode'
end
end

The output is:
Server: Msg 208, Level 16, State 1, Procedure
sp_check_removable, Line 18
Invalid object name 'sysdatabases'.

successfully set to offline mode

this actually sets the db to "offline mode" however I
can't figure out how to supress the "Invalid object
name 'sysdatabases'" trace message. This block of code
runs in a stored proc and actually does the job of
setting the db to "offline". However if I enclose the
code that calls the stored proc in a try/catch block I
catch the trace error "Invalid object name 'sysdatabases'"

Any suggestions would be greatly appreciated.

Regards
Corbett

SQL Server192  
 
 
oj





PostPosted: Tue Jan 06 12:54:36 CST 2004 Top

SQL Server Developer >> sp_certify_removable Look like there is a bug in the sp_check_removable sproc. I'll report it to MS.

--
-oj
http://www.rac4sql.net




> Hello,
>
> I'm running this:
>
> begin




>

> auto
>

> begin
> print 'failure to set to offline mode'
> end
> else
> begin
> print 'successfully set to offline mode'
> end
> end
>
> The output is:
> Server: Msg 208, Level 16, State 1, Procedure
> sp_check_removable, Line 18
> Invalid object name 'sysdatabases'.
>
> successfully set to offline mode
>
> this actually sets the db to "offline mode" however I
> can't figure out how to supress the "Invalid object
> name 'sysdatabases'" trace message. This block of code
> runs in a stored proc and actually does the job of
> setting the db to "offline". However if I enclose the
> code that calls the stored proc in a try/catch block I
> catch the trace error "Invalid object name 'sysdatabases'"
>
> Any suggestions would be greatly appreciated.
>
> Regards
> Corbett


 
 
Vlad





PostPosted: Tue Jan 06 13:04:29 CST 2004 Top

SQL Server Developer >> sp_certify_removable you can try to fix sp_check_removable in master db if this is urgent. or
wait for a fix from MS

cheers,
</wqw>


 
 
oj





PostPosted: Tue Jan 06 14:38:22 CST 2004 Top

SQL Server Developer >> sp_certify_removable Since this is a bug, I suggest you contact PSS for a quick fix. You should not
be billed for this call.

You can also use the following script (disclaimer: USE AT YOUR OWN RISK).

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
use master
go
exec sp_configure 'allow',1
reconfigure with override
go


as








/* Verify that SA owns the database. */




begin
-- changing DBO to SA
-- modified to reference master database
update master.dbo.sysdatabases set sid = 0x01

update sysusers set sid = 0x01
where uid = 1
end
else
begin

return(1)
end

-- USE CORRECT non-dbo/guest CHECKING



return 1


-- Run UPDATE STATISTICS on all user tables if there are
-- no user defined filegroups

(select count(*) from sysfilegroups) = 1
begin

+ N' exec sp_updatestats ''RESAMPLE'' '

end



if (select count(*) from sysfilegroups) > 1
begin

begin
-- Mark any non-primary filegroups as READONLY
DECLARE ms_crs_fg CURSOR LOCAL STATIC
FOR SELECT groupname FROM sysfilegroups fg
WHERE fg.groupid > 1 -- not primary
AND fg.status & 0x8 = 0 -- not already readonly
AND (SELECT count (*) FROM sysfiles f WHERE
f.groupid = fg.groupid) > 0 -- has some files
OPEN ms_crs_fg


BEGIN


+ ' MODIFY FILEGROUP '

+ ' READONLY'


END
CLOSE ms_crs_fg
DEALLOCATE ms_crs_fg
end
else
begin
if exists (SELECT groupname FROM sysfilegroups fg
WHERE fg.groupid > 1 -- not primary
AND fg.status & 0x8 = 0 -- not already readonly
AND (SELECT count (*) FROM sysfiles f WHERE
f.groupid = fg.groupid) > 0) -- has some files
begin
raiserror(15358,-1,-1)
SELECT groupname FROM sysfilegroups fg
WHERE fg.groupid > 1 -- not primary
AND fg.status & 0x8 = 0 -- not already readonly
return (-1)
end
end

end
return(0)

GO
exec sp_MS_marksystemobject 'sp_check_removable'
go
exec sp_configure 'allow',0
reconfigure with override
go

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

--
-oj
http://www.rac4sql.net