• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 434
  • Last Modified:

Collation error when importing

HI

I am trying to import data from a sql 2005 server instance to a table on sql 2008 via a linked server. The code is attached.
I am getting a collation error, see below. However I have imported other data from a view on the 2005 sql to the 2008 sql with no problems. I am unsure what I need to do to resolve this.

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the EXCEPT operation.
INSERT INTO [PJ Combined Sales].[dbo].[AndroRefunds]
           ([FiscalDate]
           ,[RestaurantCode]
           ,[Type]
           ,[Amount]
           ,[Description])
     
 SELECT 
       [TheDate] 
,[nStoreID]
      ,[nType]
      
      ,sum([nAmount])/100.00 as Refunds
      ,[strDetails]
      
  FROM [WORKSPJ1].[HeadOffice].[dbo].[t_CashRec]
  WHERE ([strDetails] LIKE  '%ref%') AND ([nType] = '1')
  AND NOT EXISTS 
  (SELECT x.[FiscalDate], x.[RestaurantCode]
FROM [PJ Combined Sales].[dbo].[AndroRefunds] as x
WHERE x.[Fiscaldate]= [TheDate] 
and x.[RestaurantCode] = [nStoreID])

GROUP BY [TheDate],[nStoreID],[nType],[strDetails]

EXCEPT
SELECT [FiscalDate],
[RestaurantCode],
 [Type],
 [Amount],
 [Description]

FROM [PJ Combined Sales].[dbo].[AndroRefunds]

Open in new window

0
jmauel
Asked:
jmauel
  • 2
1 Solution
 
JohnP_RealiniCommented:
Try using COLLATE DATABASE_DEFAULT where needed.

For example:

where x.Fiscaldate COLLATE DATABASE_DEFAULT = [TheDate]
0
 
jmauelAuthor Commented:
Thanks, I put it in after the final SELECT statement and that worked.  
0
 
JohnP_RealiniCommented:
Great! Glad I could help...

have a great day
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now