Trying to bypass via tsql a database when it is offline

  • IF EXISTS (SELECT name FROM sys.databases

    WHERE name = N'archer' and state = 0)

    BEGIN

    use archer

    SET NOCOUNT ON

    DECLARE @objectid int

    DECLARE @indexid int

    DECLARE @partitioncount bigint

    DECLARE @schemaname nvarchar(130)

    DECLARE @objectname nvarchar(130)

    DECLARE @indexname nvarchar(130)

    DECLARE @partitionnum bigint

    DECLARE @partitions bigint

    DECLARE @frag float

    DECLARE @command nvarchar(4000)

    -- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function

    -- and convert object and index IDs to names.

    SELECT

    object_id AS objectid,

    index_id AS indexid,

    partition_number AS partitionnum,

    avg_fragmentation_in_percent AS frag

    INTO #work_to_do

    FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')

    WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0

    -- Declare the cursor for the list of partitions to be processed.

    DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do

    -- Open the cursor.

    OPEN partitions

    -- Loop through the partitions.

    WHILE (1=1)

    BEGIN;

    FETCH NEXT

    FROM partitions

    INTO @objectid, @indexid, @partitionnum, @frag

    IF @@FETCH_STATUS < 0 BREAK

    SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)

    FROM sys.objects AS o

    JOIN sys.schemas as s ON s.schema_id = o.schema_id

    WHERE o.object_id = @objectid;

    SELECT @indexname = QUOTENAME(name)

    FROM sys.indexes

    WHERE object_id = @objectid AND index_id = @indexid

    SELECT @partitioncount = count (*)

    FROM sys.partitions

    WHERE object_id = @objectid AND index_id = @indexid

    -- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.

    IF @frag < 30.0

    SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE'

    IF @frag >= 30.0

    SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD'

    IF @partitioncount > 1

    SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10))

    EXEC (@command)

    PRINT N'Executed: ' + @command

    END

    -- Close and deallocate the cursor.

    CLOSE partitions;

    DEALLOCATE partitions

    -- Drop the temporary table.

    DROP TABLE #work_to_do;

    END

    It still executes even if the state of the archer database is 1. I don't know what is wrong. Can anyone help me?

    Thanks a bunch.

    Patti

  • Try this:

    IF DATABASEPROPERTYEX('archer', 'Status') = 'ONLINE'

    BEGIN

    ...

    END

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • I have added what was suggested:

    IF DATABASEPROPERTYEX('archer', 'Status') = 'ONLINE'

    BEGIN

    use archer

    SET NOCOUNT ON

    END

    It looks good until you execute and I get this error:

    Msg 954, Level 14, State 1, Line 3

    The database "Archer" cannot be opened. It is acting as a mirror database.

  • Patti Johnson (9/4/2013)


    I have added what was suggested:

    IF DATABASEPROPERTYEX('archer', 'Status') = 'ONLINE'

    BEGIN

    use archer

    SET NOCOUNT ON

    END

    It looks good until you execute and I get this error:

    Msg 954, Level 14, State 1, Line 3

    The database "Archer" cannot be opened. It is acting as a mirror database.

    IF DATABASEPROPERTYEX('archer', 'Status') = 'ONLINE'

    AND EXISTS(SELECT 1 FROM sys.database_mirroring WHERE database_id = DB_ID('archer') AND

    (mirroring_role IS NULL OR mirroring_role <> 2))

    BEGIN

    ...

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • Thank you so much for your help. I've been digging and digging so I appreciate your time.

    IF DATABASEPROPERTYEX('archer', 'Status') = 'ONLINE'

    AND EXISTS(SELECT 1 FROM sys.database_mirroring WHERE database_id = DB_ID('archer') AND

    (mirroring_role IS NULL OR mirroring_role <> 2))

    BEGIN

    use archer

    SET NOCOUNT ON

    END

    Msg 954, Level 14, State 1, Line 6

    The database "Archer" cannot be opened. It is acting as a mirror database.

  • Patti Johnson (9/4/2013)


    Thank you so much for your help. I've been digging and digging so I appreciate your time.

    IF DATABASEPROPERTYEX('archer', 'Status') = 'ONLINE'

    AND EXISTS(SELECT 1 FROM sys.database_mirroring WHERE database_id = DB_ID('archer') AND

    (mirroring_role IS NULL OR mirroring_role <> 2))

    BEGIN

    use archer

    SET NOCOUNT ON

    END

    Msg 954, Level 14, State 1, Line 6

    The database "Archer" cannot be opened. It is acting as a mirror database.

    Hmm, OK, let's ignore any db that is involved in mirroring then:

    IF DATABASEPROPERTYEX('archer', 'Status') = 'ONLINE'

    AND EXISTS(SELECT 1 FROM sys.database_mirroring WHERE database_id = DB_ID('archer') AND

    mirroring_role IS NULL)

    BEGIN

    use archer

    SET NOCOUNT ON

    END

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • IF DATABASEPROPERTYEX('archer', 'Status') = 'ONLINE'

    AND EXISTS(SELECT 1 FROM sys.database_mirroring WHERE database_id = DB_ID('archer') AND

    mirroring_role IS NULL)

    BEGIN

    use archer

    SET NOCOUNT ON

    END

    Msg 954, Level 14, State 1, Line 6

    The database "Archer" cannot be opened. It is acting as a mirror database.

    :crazy:

  • It does not help how many conditions you add to the IF statement. The USE statement is interpreted at compile time, why you always get the error. Here is a quick example:

    IF 1 = 9

    BEGIN

    USE nosuchdb

    END

    Output:

    Msg 911, Level 16, State 1, Line 3

    Database 'nosuchdb' does not exist. Make sure that the name is entered correctly.

    One workaround is to wrap it all in EXEC(), but that would make the code impossible to read and maintain. Another is put this in a stored procedure, which you then can call with:

    IF databasepropertyex(...) etc

    BEGIN

    EXEC archer.dbo.do_some_reindexingstuff

    END

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • Thank you. I will try that and get back with you.

  • Is there a way to make this stored procedure able to provide a parameter like the database name? I want to be able to provide the database name and make it so that the stored procedure can be used against other databases besides Archer.

  • Yes and no.

    That is, you can put your procedure in master and call it sp_somename. Then you can invoke it as

    EXEC archer..sp_somename

    The procedure will then execute in the context of the database you specified.

    However, this is not supported and it could break, for instance if Microsoft ships a system procedure with name you use. Or it just stops working.

    Note here that the procedure name must start with sp_.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • That is very good news. I found a way of doing the procedure not knowing that you can do it the way you had mentioned. I will try it your way now and determine which is better. Thanks alot for your assistance.

    Patti

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply