SQL Clone
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
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4103 Visits: 537
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 Guru
SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)

Group: General Forum Members
Points: 145223 Visits: 18652
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
Learn Extended Events

sneumersky
sneumersky
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

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

Group: General Forum Members
Points: 7277 Visits: 1122
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
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: 2109 Visits: 1377
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
Tom Thomson
Tom Thomson
SSC Guru
SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)

Group: General Forum Members
Points: 50924 Visits: 13159
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
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: 10971 Visits: 2774
EZ for me. :-)
Wildcat
Wildcat
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2828 Visits: 1445
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 Guru
SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)SSC Guru (145K reputation)

Group: General Forum Members
Points: 145223 Visits: 18652
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
Learn Extended Events

jfgoude
jfgoude
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1852 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