Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

Database Collation Is Null Expand / Collapse
Author
Message
Posted Friday, January 17, 2014 9:33 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 9:50 AM
Points: 253, Visits: 218
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')
Post #1532136
Posted Friday, January 17, 2014 12:07 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:39 PM
Points: 20,485, Visits: 14,143
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1532219
Posted Friday, January 17, 2014 1:11 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, March 27, 2014 2:39 PM
Points: 2,141, Visits: 486
It would have helped if I read "choose 3!". There goes my hot streak!
Post #1532257
Posted Saturday, January 18, 2014 12:50 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 8:57 AM
Points: 937, Visits: 390
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
Post #1532358
Posted Monday, January 20, 2014 1:54 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 8:37 AM
Points: 525, Visits: 772
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
Post #1532524
Posted Monday, January 20, 2014 4:25 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 7:47 AM
Points: 8,296, Visits: 8,750
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
Post #1532815
Posted Monday, January 27, 2014 2:01 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 6:04 AM
Points: 3,517, Visits: 2,606
EZ for me.
Post #1534890
Posted Tuesday, February 04, 2014 11:45 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, March 18, 2014 1:26 PM
Points: 405, Visits: 1,431
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)?
Post #1537896
Posted Tuesday, February 04, 2014 11:55 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:39 PM
Points: 20,485, Visits: 14,143
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1537900
Posted Thursday, February 27, 2014 2:57 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 7:07 AM
Points: 1,144, Visits: 298
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
Post #1545773
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse