January 17, 2014 at 7:42 am
Nice easy question, but the explanation does not mention that you will get a NULL value if you can't find the database name provided for any reason. This includes not having access to the database.
January 17, 2014 at 7:47 am
raulggonzalez (1/17/2014)
Thomas Abraham (1/17/2014)
raulggonzalez (1/17/2014)
Thanks for the question, but I have to disagree with one of the answers.I have a number of offline databases in one of my servers and the following query returns values for all of them
select name, state_desc, DATABASEPROPERTYEX(name, 'Collation')
from sys.databases
where state_desc = 'OFFLINE'
I can see that BOL states opposite, but if anybody else want to try and share the result, would be great
I took my test bed offline and got this result on SQLServer2008 (Version 655)
name state_desc (No column name)TWA_Practice OFFLINE NULL
So did I on SQL Server 2008R2 Enterprise and SQL Server 2012 Standard (NULL), but not in 2008R2 Standard (actual value)...
thanks for your feedback
Must be your connection settings. With default connection settings this is returned from all 5 of our SQL Server 2008 R2 Standard servers.
select DATABASEPROPERTYEX('@#$@#$@##$DB', 'Collation') returns NULL... 😎
January 17, 2014 at 8:51 am
PHYData DBA (1/17/2014)
raulggonzalez (1/17/2014)
Thomas Abraham (1/17/2014)
raulggonzalez (1/17/2014)
Thanks for the question, but I have to disagree with one of the answers.I have a number of offline databases in one of my servers and the following query returns values for all of them
select name, state_desc, DATABASEPROPERTYEX(name, 'Collation')
from sys.databases
where state_desc = 'OFFLINE'
I can see that BOL states opposite, but if anybody else want to try and share the result, would be great
I took my test bed offline and got this result on SQLServer2008 (Version 655)
name state_desc (No column name)TWA_Practice OFFLINE NULL
So did I on SQL Server 2008R2 Enterprise and SQL Server 2012 Standard (NULL), but not in 2008R2 Standard (actual value)...
thanks for your feedback
Must be your connection settings. With default connection settings this is returned from all 5 of our SQL Server 2008 R2 Standard servers.
select DATABASEPROPERTYEX('@#$@#$@##$DB', 'Collation') returns NULL... 😎
Connection settings are the default, I tried from my 2012 SSMS and using 2008R2 SSMS in a remote desktop, (default connection settings) and both return values for offline databases.
Well, this does not bother much, but would be nice to find out why... thanks for your feedback.
*My Product version 10.50.4000 (SP2)
January 17, 2014 at 8:56 am
raulggonzalez (1/17/2014)
Connection settings are the default, I tried from my 2012 SSMS and using 2008R2 SSMS in a remote desktop, (default connection settings) and both return values for offline databases.
Well, this does not bother much, but would be nice to find out why... thanks for your feedback.
*My Product version 10.50.4000 (SP2)
Would you mind posting to everyone exactly what you do get? "Values" is such a vague and inaccurate response.
January 17, 2014 at 9:16 am
PHYData DBA (1/17/2014)
raulggonzalez (1/17/2014)
Connection settings are the default, I tried from my 2012 SSMS and using 2008R2 SSMS in a remote desktop, (default connection settings) and both return values for offline databases.
Well, this does not bother much, but would be nice to find out why... thanks for your feedback.
*My Product version 10.50.4000 (SP2)
Would you mind posting to everyone exactly what you do get? "Values" is such a vague and inaccurate response.
Sure, this is my query and the results
select left(name,1) + '****' as database_name, state_desc, DATABASEPROPERTYEX(name, 'Collation') AS [Collation]
from sys.databases
where state_desc = 'OFFLINE'
database_namestate_descCollation
d****OFFLINELatin1_General_CI_AS
e****OFFLINELatin1_General_CI_AS
e****OFFLINELatin1_General_CI_AS
e****OFFLINELatin1_General_CI_AS
e****OFFLINELatin1_General_CI_AS
s****OFFLINELatin1_General_CI_AS
s****OFFLINELatin1_General_CI_AS
s****OFFLINELatin1_General_CI_AS
s****OFFLINELatin1_General_CI_AS
e****OFFLINELatin1_General_CI_AS
e****OFFLINELatin1_General_CI_AS
G****OFFLINELatin1_General_CI_AS
h****OFFLINELatin1_General_CI_AS
January 17, 2014 at 9:33 am
raulggonzalez (1/17/2014)
PHYData DBA (1/17/2014)
raulggonzalez (1/17/2014)
Connection settings are the default, I tried from my 2012 SSMS and using 2008R2 SSMS in a remote desktop, (default connection settings) and both return values for offline databases.
Well, this does not bother much, but would be nice to find out why... thanks for your feedback.
*My Product version 10.50.4000 (SP2)
Would you mind posting to everyone exactly what you do get? "Values" is such a vague and inaccurate response.
Sure, this is my query and the results
select left(name,1) + '****' as database_name, state_desc, DATABASEPROPERTYEX(name, 'Collation') AS [Collation]
from sys.databases
where state_desc = 'OFFLINE'
database_namestate_descCollation
d****OFFLINELatin1_General_CI_AS
e****OFFLINELatin1_General_CI_AS
e****OFFLINELatin1_General_CI_AS
e****OFFLINELatin1_General_CI_AS
e****OFFLINELatin1_General_CI_AS
s****OFFLINELatin1_General_CI_AS
s****OFFLINELatin1_General_CI_AS
s****OFFLINELatin1_General_CI_AS
s****OFFLINELatin1_General_CI_AS
e****OFFLINELatin1_General_CI_AS
e****OFFLINELatin1_General_CI_AS
G****OFFLINELatin1_General_CI_AS
h****OFFLINELatin1_General_CI_AS
What happens when you run this? select DATABASEPROPERTYEX('@#$@#$@##$DB', 'Collation')
January 17, 2014 at 12:07 pm
raulggonzalez (1/17/2014)
Stuart Davies (1/17/2014)
raulggonzalez (1/17/2014)
Thanks for the question, but I have to disagree with one of the answers.I have a number of offline databases in one of my servers and the following query returns values for all of them
select name, state_desc, DATABASEPROPERTYEX(name, 'Collation')
from sys.databases
where state_desc = 'OFFLINE'
I can see that BOL states opposite, but if anybody else want to try and share the result, would be great
Most odd - I have the same symptoms one one of my (2005) servers. 6 databases are off line - yet I get a collation for them.
Any ideas why?
Mine is a 2008R2 Standard SP2 ...
Wow!! I just tried on another 2008R2 Enterprise SP2 and it does return NULL ...
And SQL Server 2012 Standard Edition SP1 returns NULL...
This is pretty weird!
I see the same results. This underscores one of the main assumptions of QOTD. If not stated, we need to presume the intent of the question is for the latest version of SQL Server release.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 17, 2014 at 1:11 pm
It would have helped if I read "choose 3!". There goes my hot streak!
January 18, 2014 at 12:50 am
Nevyn (1/17/2014)
A bit too guessable. If it was going to be select 3, there should have been a few more options. Otherwise its 'one of these things is not like the other', which was easy with the options provided.
+1
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
January 20, 2014 at 1:54 am
What happens when you run this? select DATABASEPROPERTYEX('@#$@#$@##$DB', 'Collation')
That returns NULL...
I see the same results. This underscores one of the main assumptions of QOTD. If not stated, we need to presume the intent of the question is for the latest version of SQL Server release.
Sure, but considering the results differ from 2008R2 Standard to Enterprise, if that was the latest version, it'd be still a problem... I've no 2012 Enterprise handy to test it, but would be nice to know what results (Standard returns NULL)
Cheers
January 20, 2014 at 4:25 pm
raulggonzalez (1/20/2014)
Sure, but considering the results differ from 2008R2 Standard to Enterprise, if that was the latest version, it'd be still a problem... I've no 2012 Enterprise handy to test it, but would be nice to know what results (Standard returns NULL)
Cheers
2012 developer version returns NULL, so I imagine enterprise version will too. But is is somewhat disconcerting to see that 2008R2 differs between enterprise and standard (developer returns NULL in Sql 2008R2).
Tom
January 27, 2014 at 2:01 am
EZ for me. 🙂
February 4, 2014 at 11:45 am
raulggonzalez (1/17/2014)
Stuart Davies (1/17/2014)
raulggonzalez (1/17/2014)
Thanks for the question, but I have to disagree with one of the answers.I have a number of offline databases in one of my servers and the following query returns values for all of them
select name, state_desc, DATABASEPROPERTYEX(name, 'Collation')
from sys.databases
where state_desc = 'OFFLINE'
I can see that BOL states opposite, but if anybody else want to try and share the result, would be great
Most odd - I have the same symptoms one one of my (2005) servers. 6 databases are off line - yet I get a collation for them.
Any ideas why?
Mine is a 2008R2 Standard SP2 ...
Wow!! I just tried on another 2008R2 Enterprise SP2 and it does return NULL ...
And SQL Server 2012 Standard Edition SP1 returns NULL...
This is pretty weird!
Mine is SQL 2008 Standard SP3, and returns the collation name although it is offline. Something's wrong with BOL (or Microsoft)?
February 4, 2014 at 11:55 am
L' Eomot Inversé (1/20/2014)
raulggonzalez (1/20/2014)
Sure, but considering the results differ from 2008R2 Standard to Enterprise, if that was the latest version, it'd be still a problem... I've no 2012 Enterprise handy to test it, but would be nice to know what results (Standard returns NULL)
Cheers
2012 developer version returns NULL, so I imagine enterprise version will too. But is is somewhat disconcerting to see that 2008R2 differs between enterprise and standard (developer returns NULL in Sql 2008R2).
I think the main difference is most likely the service pack levels. My 2008R2 is enterprise and does not return NULL (in other words it returns the collation of the offline database) but it is sp1.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 27, 2014 at 2:57 am
A bit too guessable. If it was going to be select 3, there should have been a few more options. Otherwise its 'one of these things is not like the other', which was easy with the options provided.
Think exactly the same way
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply