Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Database Collation Is Null


Database Collation Is Null

Author
Message
PHYData DBA
PHYData DBA
Mr or Mrs. 500
Mr or Mrs. 500 (560 reputation)Mr or Mrs. 500 (560 reputation)Mr or Mrs. 500 (560 reputation)Mr or Mrs. 500 (560 reputation)Mr or Mrs. 500 (560 reputation)Mr or Mrs. 500 (560 reputation)Mr or Mrs. 500 (560 reputation)Mr or Mrs. 500 (560 reputation)

Group: General Forum Members
Points: 560 Visits: 533
raulggonzalez (1/17/2014)
PHYData DBA (1/17/2014)
raulggonzalez (1/17/2014)
[quote]
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_name state_desc Collation
d**** OFFLINE Latin1_General_CI_AS
e**** OFFLINE Latin1_General_CI_AS
e**** OFFLINE Latin1_General_CI_AS
e**** OFFLINE Latin1_General_CI_AS
e**** OFFLINE Latin1_General_CI_AS
s**** OFFLINE Latin1_General_CI_AS
s**** OFFLINE Latin1_General_CI_AS
s**** OFFLINE Latin1_General_CI_AS
s**** OFFLINE Latin1_General_CI_AS
e**** OFFLINE Latin1_General_CI_AS
e**** OFFLINE Latin1_General_CI_AS
G**** OFFLINE Latin1_General_CI_AS
h**** OFFLINE Latin1_General_CI_AS

What happens when you run this? select DATABASEPROPERTYEX('@#$@#$@##$DB', 'Collation')
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21099 Visits: 18259
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

sneumersky
sneumersky
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2166 Visits: 487
It would have helped if I read "choose 3!". There goes my hot streak!
Hany Helmy
Hany Helmy
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2520 Visits: 1108
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
sqldoubleg
sqldoubleg
SSC Eights!
SSC Eights! (904 reputation)SSC Eights! (904 reputation)SSC Eights! (904 reputation)SSC Eights! (904 reputation)SSC Eights! (904 reputation)SSC Eights! (904 reputation)SSC Eights! (904 reputation)SSC Eights! (904 reputation)

Group: General Forum Members
Points: 904 Visits: 1345
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
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10731 Visits: 12019
raulggonzalez (1/20/2014)
[quote]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

sqlnaive
sqlnaive
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3687 Visits: 2774
EZ for me. :-)
Wildcat
Wildcat
SSC-Addicted
SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)

Group: General Forum Members
Points: 423 Visits: 1444
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)?
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21099 Visits: 18259
L' Eomot Inversé (1/20/2014)
raulggonzalez (1/20/2014)
[quote]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

jfgoude
jfgoude
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1172 Visits: 299
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
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