Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Hommer
Aged Yak Warrior
808 Posts |
 Posted - 2015-04-15 : 17:17:01
|
| Hi,This is on the uniqueidentifier column. Both tables are on the same database and have the same COLLATE Latin1_General_CI_AS.But my plain update and insert results are all upper case.Then when I tried to force by usingInsert (cols1, cols2) Values (source.col1, source.col2) COLLATE Latin1_General_CI_ASandUpdate set target.col1=source.col1, target.col2=source.col2 target.col1=source.col1 COLLATE Latin1_General_CI_ASI got Msg 447, Level 16, State 0, Line 32Expression type uniqueidentifier is invalid for COLLATE clause.So what is the trick here? Thanks! |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2015-04-15 : 20:41:33
|
| uniqueidentifiers display as upper case by default. They are stored as 16-byte binary, not characters so case is irrelevant in the table (as is collation). If you want to see the guids as lower case try select lower(myguidcol) |
 |
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2015-04-16 : 09:16:08
|
| Thank you for the reply. They show upper case by default in my SSMS too, but in Toad, show lower case.And on their way out, it is a vendor app compares them, so I cannot force the case, and the app treats upper <> lower.I am back to square one. |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2015-04-16 : 09:52:23
|
| OK, but how they show in SSMS or TOAD is actually irrelevant. What counts is what your application does with them. Since a GUID is stored as 16-byte binary, when you retrieve it in an application (including vendor applications), it comes in as a binary value. upper/lower case does not apply to binary values.Please explain what you mean by "on their way out". |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2015-04-17 : 07:11:27
|
| Long shot: Sounds like something is casting/covering the GUID to String (and its the "wrong" case!) |
|
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2015-04-17 : 09:36:58
|
quote:
Originally posted by gbritton
OK, but how they show in SSMS or TOAD is actually irrelevant. What counts is what your application does with them. Since a GUID is stored as 16-byte binary, when you retrieve it in an application (including vendor applications), it comes in as a binary value. upper/lower case does not apply to binary values.Please explain what you mean by "on their way out".
"on their way out"=showing in the vendor app, i.e. not from a select statement so I can add lower or upper as you have suggested. Thanks! |
|
|
|
|
|
|
|
|
how to make update not change case