Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Trying to bypass via tsql a database when it is offline Expand / Collapse
Author
Message
Posted Wednesday, September 4, 2013 11:13 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 10:08 AM
Points: 229, Visits: 805
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
Post #1491451
Posted Wednesday, September 4, 2013 12:26 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:13 AM
Points: 2,044, Visits: 3,060
Try this:

IF DATABASEPROPERTYEX('archer', 'Status') = 'ONLINE'
BEGIN
...
END


SQL DBA,SQL Server MVP('07, '08, '09)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1491477
Posted Wednesday, September 4, 2013 1:24 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 10:08 AM
Points: 229, Visits: 805
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.

Post #1491498
Posted Wednesday, September 4, 2013 1:33 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:13 AM
Points: 2,044, Visits: 3,060
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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1491508
Posted Wednesday, September 4, 2013 1:45 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 10:08 AM
Points: 229, Visits: 805
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.
Post #1491516
Posted Wednesday, September 4, 2013 1:56 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:13 AM
Points: 2,044, Visits: 3,060
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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1491523
Posted Wednesday, September 4, 2013 2:18 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 10:08 AM
Points: 229, Visits: 805
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.

Post #1491529
Posted Wednesday, September 4, 2013 4:02 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, August 21, 2014 3:04 PM
Points: 801, Visits: 715
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




Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1491566
Posted Thursday, September 5, 2013 6:21 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 10:08 AM
Points: 229, Visits: 805
Thank you. I will try that and get back with you.
Post #1491744
Posted Thursday, September 5, 2013 11:42 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 10:08 AM
Points: 229, Visits: 805
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.
Post #1491897
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse