Solved

CREATE TYPE + COLLATE

Posted on 2014-11-13
5
195 Views
Last Modified: 2016-02-11
Hi Guys

I'm trying to execute the below in 2008R2, and receive an errmsg 'Incorrect syntax near the keyword 'COLLATE'.'
The msdn CREATE TYPE page references COLLATE, but doesn't give an example.  Does anyone know the fix?

CREATE TYPE SFID FROM nvarchar(18) 
COLLATE SQL_Latin1_General_CP1_CS_AS

Open in new window


I'm building an SSIS package where the source is salesforce.com data, and they have a table PK of nvarchar(18).   Plus, the target database I've inherited has these nvarchar(18) columns but with collations SQL_Latin1_General_CP1_CI_AS, SQL_Latin1_General_CP1_CS_AS, and Latin1_General_CS_AS, and I'm trying to standardize it.

Thanks in advance.
Jim
0
Comment
Question by:Jim Horn
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 5

Assisted Solution

by:TONY TAYLOR
TONY TAYLOR earned 300 total points
ID: 40440726
The COLLATE should be on the table definition, not on the column definition.  In the above you are referencing a type that is column based rather than table based.

You can build it as a table, but that is NOT what you are doing above.

Reference:
http://msdn.microsoft.com/en-us/library/ms175007.aspx
0
 
LVL 65

Author Comment

by:Jim Horn
ID: 40440797
Adding the collation on the column is not a problem (below code middle block)
ALTER TABLE FROMSF_OBJECT_ASSESSMENT
DROP CONSTRAINT PK_FROMSF_OBJECT_Assessment
GO

ALTER TABLE FROMSF_OBJECT_ASSESSMENT
ALTER COLUMN id nvarchar(18) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL 
GO

ALTER TABLE FROMSF_OBJECT_ASSESSMENT
ADD CONSTRAINT PK_FROMSF_OBJECT_Assessment PRIMARY KEY (id) 
GO

Open in new window

Problem is I can't add COLLATE to a TYPE.  (Unless that is not possible...)
0
 
LVL 5

Expert Comment

by:TONY TAYLOR
ID: 40440829
I agree Jim, but a step beyond that is that a TYPE can have a COLLATE if it is a TABLE TYPE...

CREATE TYPE dbo.MyTableType AS TABLE ( 
	MyVarChar varchar(50) COLLATE SQL_Latin1_General_CP1_CS_AS, 
	MyInt int 
) 

Open in new window


So ultimately, it is based on the TABLE and assigned to the COLUMN.

Why?  I have no idea, ask Microsoft!!  :)
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 200 total points
ID: 40443115
>> Problem is I can't add COLLATE to a TYPE.  (Unless that is not possible.) <<

Yep, it's not possible.  The syntax makes clear that COLLATE applies only for a TYPE of table, not for one on based on a base data type.
0
 
LVL 65

Author Closing Comment

by:Jim Horn
ID: 40443158
Got it.  Thanks guys.
0

Featured Post

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question