Collation error when importing

Posted on 2011-10-27
Last Modified: 2012-05-12

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]
      ,sum([nAmount])/100.00 as Refunds
  FROM [WORKSPJ1].[HeadOffice].[dbo].[t_CashRec]
  WHERE ([strDetails] LIKE  '%ref%') AND ([nType] = '1')
  (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]

SELECT [FiscalDate],

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

Open in new window

Question by:jmauel
    LVL 3

    Accepted Solution

    Try using COLLATE DATABASE_DEFAULT where needed.

    For example:

    where x.Fiscaldate COLLATE DATABASE_DEFAULT = [TheDate]

    Author Comment

    Thanks, I put it in after the final SELECT statement and that worked.  
    LVL 3

    Expert Comment

    Great! Glad I could help...

    have a great day

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    Suggested Solutions

    There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
    SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    755 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

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now