December 7, 2012 at 3:16 pm
Good afternoon everyone!
I'm not a regular developer so please excuse my question for being easy for what would be a good developer.
I'm trying to create a statement that returns database names for all servers. However, I only want the principal database names on mirrored servers. However, I receive the error: Incorrect syntax near '='.
Any advice is greatly appreciated!
Select
d.name,
d.compatibility_level
FROM
sys.database_mirroring M inner join SYS.DATABASES d
on m.database_id = d.database_id
where
CASE
WHEN B.mirroring_state is NOT NULL THEN
mirroring_role_desc ='Principal'
and D.database_id >=5
WHEN B.mirroring_state is NULL THEN
D.database_id >=5
END as MirroringState
ORDER BY A.NAME
¤ §unshine ¤
December 7, 2012 at 3:34 pm
sunshine-587009 (12/7/2012)
Good afternoon everyone!I'm not a regular developer so please excuse my question for being easy for what would be a good developer.
I'm trying to create a statement that returns database names for all servers. However, I only want the principal database names on mirrored servers. However, I receive the error: Incorrect syntax near '='.
Any advice is greatly appreciated!
Select
d.name,
d.compatibility_level
FROM
sys.database_mirroring M inner join SYS.DATABASES d
on m.database_id = d.database_id
where
CASE
WHEN B.mirroring_state is NOT NULL THEN
mirroring_role_desc='Principal'
and D.database_id >=5
WHEN B.mirroring_state is NULL THEN
D.database_id >=5
END as MirroringState
ORDER BY A.NAME
I believe you are misunderstanding the function of the CASE expression. Like any other expression (column name, variable, function, calculation) , CASE only returns a single scalar value. You are trying to use it to generate SQL statements dynamically, which is quite a different matter. Try this instead:
where D.database_id >=5
and mirroring_role_desc like CASE WHEN B.mirroring_state is NOT NULL then 'Principal' else null end
By the way, where is your B.mirroring_state supposed to come from? There is no table in your query that is aliased "B".
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
December 7, 2012 at 3:38 pm
Point to Master.
December 7, 2012 at 3:53 pm
Thanks Dixie! My oops on the referencing aliases.
When I run it on CMS it only brings me back the principal databases on the mirrored servers which is great!
But I also want all database names on the rest of the servers that are not mirrored. 🙁
¤ §unshine ¤
December 7, 2012 at 3:55 pm
Here's my updated script with correct aliases and your suggestion.
Select
d.name,
d.compatibility_level
FROM
sys.database_mirroring M inner join SYS.DATABASES d
on m.database_id = d.database_id
where D.database_id >=5
and mirroring_role_desc like CASE WHEN M.mirroring_state is NOT NULL then 'Principal'
else
null end
ORDER BY D.NAME
¤ §unshine ¤
December 7, 2012 at 4:03 pm
I think I have the case statement in the wrong place?
My logic would be:
If it is mirrored:
Select database names from mirrored servers that are principal role
But if it is not:
select all database names.
¤ §unshine ¤
December 7, 2012 at 4:08 pm
sunshine-587009 (12/7/2012)
Here's my updated script with correct aliases and your suggestion.Select
d.name,
d.compatibility_level
FROM
sys.database_mirroring M inner join SYS.DATABASES d
on m.database_id = d.database_id
where D.database_id >=5
and mirroring_role_desc like CASE WHEN M.mirroring_state is NOT NULL then 'Principal'
else '%'
end
ORDER BY D.NAME
Select
d.name,
d.compatibility_level
FROM
sys.database_mirroring M inner join SYS.DATABASES d
on m.database_id = d.database_id
where D.database_id >=5
and mirroring_role_desc like CASE WHEN M.mirroring_state is NOT NULL then 'Principal'
else '%' -- like ANYTHING
end
ORDER BY D.NAME
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
December 7, 2012 at 4:22 pm
Still only brings back the principal databases from mirrored servers.
¤ §unshine ¤
December 7, 2012 at 4:43 pm
Select
d.name,
d.compatibility_level,
m.mirroring_state,
mirroring_role_desc
FROM
sys.database_mirroring M inner join SYS.DATABASES d
on m.database_id = d.database_id
where D.database_id >=5
and ISNULL(mirroring_role_desc,'') like CASE WHEN M.mirroring_state is NOT NULL then 'Principal'
else '%' -- like anything (except nulls)
end
ORDER BY D.NAME
The problem was the mirroring_role_desc column contained NULL values. The LIKE comparison wouldn't return NULLs, they have to be tested for with an IS NULL comparison. The solution is to use ISNULL to set NULL mirroring_role_desc values to blank ('') and then the LIKE '%' test can work against the result.
When trying to debug problem queries, I always include any columns in the output set that I am testing against. After the correct rows are being delivered, the extraneous columns can be removed.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
December 7, 2012 at 9:04 pm
I feel compelled to add this. As a general rule, you should not test against functions like ISNULL, because that can prevent the optimizer from taking advantage of a suitable index in the query plan. In the case at hand, there aren't enough rows to make a huge difference. I presume this query is only going to be run occassionally.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
December 10, 2012 at 10:05 am
That is correct. But, all the ones I test are failing to bring back the desired result. I'll try with if statements.
¤ §unshine ¤
December 10, 2012 at 10:54 am
I got it! Just a regular statement with no ifs or cases. Thank you all so much for your help! This is what worked.
SELECT d.name, d.compatibility_level
FROM sys.database_mirroring AS M INNER JOIN
sys.databases AS d ON M.database_id = d.database_id
WHERE (d.database_id >= 5) AND (M.mirroring_ROLE=1) or (d.database_id >= 5) AND(M.mirroring_ROLE IS NULL)
ORDER BY D.NAME
¤ §unshine ¤
December 10, 2012 at 11:07 am
Think maybe changing from [mirroring_state] to [mirroring_role] had something to do with it? Your where clause could be logically rewritten as
WHERE (d.database_id >= 5)
AND (M.mirroring_ROLE=1 or M.mirroring_ROLE IS NULL)
Would you please explain how that is producing different sets of results on different servers?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
December 19, 2012 at 3:25 pm
Ok. I'll try that. Thank you!
When I run it on a mirrored server in which it has a mixture of Principal and mirrored databases, it brings back only the names of the databases in which the role is principal.
When I run it on a regular non-HA server, it brings back all databases.
Normally when you try to run something on all databases, you will receive an error message on the ones that are in the mirror role stating that it cannot run it on that database, because it is not in the principal state. I'd like to avoid these errors. 🙂
¤ §unshine ¤
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply