|
|
|
Author |
Message |
dcleesfo
|
Posted: 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
|
Posted: 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
|
Posted: 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
|
Posted: 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
|
|
|
|
|
|
|