SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Trying to bypass via tsql a database when it is offline


Trying to bypass via tsql a database when it is offline

Author
Message
Patti Johnson
Patti Johnson
Mr or Mrs. 500
Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)

Group: General Forum Members
Points: 551 Visits: 950
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
ScottPletcher
ScottPletcher
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7809 Visits: 7144
Try this:

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

SQL DBA,SQL Server MVP(07, 08, 09)[size=2]Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.[/size]
Patti Johnson
Patti Johnson
Mr or Mrs. 500
Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)

Group: General Forum Members
Points: 551 Visits: 950
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.
ScottPletcher
ScottPletcher
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7809 Visits: 7144
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)[size=2]Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.[/size]
Patti Johnson
Patti Johnson
Mr or Mrs. 500
Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)

Group: General Forum Members
Points: 551 Visits: 950
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.
ScottPletcher
ScottPletcher
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7809 Visits: 7144
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)[size=2]Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.[/size]
Patti Johnson
Patti Johnson
Mr or Mrs. 500
Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)

Group: General Forum Members
Points: 551 Visits: 950
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
Erland Sommarskog
Erland Sommarskog
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2101 Visits: 872
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
Patti Johnson
Patti Johnson
Mr or Mrs. 500
Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)

Group: General Forum Members
Points: 551 Visits: 950
Thank you. I will try that and get back with you.
Patti Johnson
Patti Johnson
Mr or Mrs. 500
Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)Mr or Mrs. 500 (551 reputation)

Group: General Forum Members
Points: 551 Visits: 950
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search