2

We've just noticed 2 of our servers have been set up using SQL_Latin1_General_CP1_CI_AS and Latin1_General_CI_AS.

I've created a script which compares two sources of data again each other (as it's a simple SELECT statement I haven't included it, it uses the same columns but from different sources) when I tried to union them together I received the collate error.

Msg 468, Level 16, State 9, Line 2 Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the UNION operation.

One of the sources is a table which is being populated by an SSIS package, is there a way of adapting the SSIS project to convert the "SQL_Latin1_General_CP1_CI_AS" format to "Latin1_General_CI_AS" before it's sent to the destination table?

Thanks!

TJH
  • 189
  • 1
  • 5
  • 18
  • Hi Siva, I'm fetching the data from two different sources, then using a union on the two select statements. One of the sources has come from a different server which has the different collation type. Rather than try and fix the issue in the script using "collate database_default" I want to tidy up one of the source tables using SSIS to make sure they both match as "SQL_Latin1_General_CP1_CI_AS". Sorry if I'm not being clear. – TJH Nov 01 '12 at 16:44

2 Answers2

1

You can use the sentence COLLATE Latin1_General_CI_AS after each column in the table that use SQL_Latin1_General_CP1_CI_AS like this.

SELECT COLUMN_A COLLATE Latin1_General_CI_AS AS COL1 
FROM TABLE
Rednaxel
  • 938
  • 2
  • 16
  • 33
0

Your error message looks like your UNION is in a single SQL statement? e.g SELECT ... UNION ... SELECT

As Shiva described, within your SSIS Data Flow, you can use two (or more) OLE DB Source objects and combine them with a "Union All" transformation. Each OLE DB Source object will have just a single SELECT with no UNION clause. This design also has advantages in design/maintenance (unions on column names, no need to fill every column from every source) and runtime throughput.

I think you also need to use this technique on the OLE DB Source objects. http://blog.stevienova.com/2009/04/16/ssis-pulling-data-from-a-non-default-collation-db-to-a-default-collation-db/

Mike Honey
  • 14,523
  • 1
  • 24
  • 40
  • Hi Mike, thanks for the reply. I think I need to clarify a bit more, I'm using two separate databases at the moment where I'm trying to merge two select statements together using union. However each server is using a different format i.e "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" What I wanted to know was, if I used SSIS, can I clean that data from say the "Latin1_General_CI_AS" 2nd server, change it to the "SQL_Latin1_General_CP1_CI_AS" format then place it in a new table on the first server so I can then do a select statement from two tables on the same server? – TJH Nov 05 '12 at 12:21
  • Why bother? SSIS can happily select from the two original tables (with two OLE DB Source objects) and union them together. – Mike Honey Nov 06 '12 at 02:28