none
collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" RRS feed

  • Question

  • Hello Everyone,

     

    I am getting the following issue when u am running my query. Can anyone please tell me what i need to do on this to resolve.

     

    Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation

     

    THanks

    Anil Kumar

    Monday, November 1, 2010 9:36 PM

Answers

  • Your collation conflict may go away if you declare the temp table #list as

     CREATE TABLE #list
     (
     record_num INT IDENTITY(1,1),
     TempAcctNum NVARCHAR(40) Collate Database_Default,
     TempAcctName NVARCHAR(100) Collate Database_Default,
     TempNumOfCrds SMALLINT,
     TempSys2Acct NVARCHAR(10) Collate Database_Default,
     TempDelType TINYINT,
     TempStatusOfCrd VARCHAR(100) Collate Database_Default,
     TempLastDate VARCHAR(100) Collate Database_Default,
     TempSys2Acct1 NVARCHAR(10) Collate Database_Default,
     TempShrtName NVARCHAR(50) Collate Database_Default,
     TempAdd1 NVARCHAR(200) Collate Database_Default,
     TempAdd2 NVARCHAR(200) Collate Database_Default,
     TempCity NVARCHAR(100) Collate Database_Default,
     TempState NVARCHAR(100) Collate Database_Default,
     TempZipCode NVARCHAR(50) Collate Database_Default,
     TempOpenDate DATETIME,
     TempFax NVARCHAR(50) Collate Database_Default,
     TempUsr1 NVARCHAR(100) Collate Database_Default,
     TempUsr2 NVARCHAR(100) Collate Database_Default,
     TempUsr3 NVARCHAR(100) Collate Database_Default,
     TempUsr4 NVARCHAR(100) Collate Database_Default,
     TempMemo NTEXT,
     TempMail NVARCHAR(100) Collate Database_Default,
     TempNoSys2Status NVARCHAR(50) Collate Database_Default,
     TempDelete BIT,
     TempEdit BIT,
     TempContName VARCHAR(200) Collate Database_Default,
     TempPhone NVARCHAR(50) Collate Database_Default
     ) 
     
    
    Tom

    • Marked as answer by Anil Kumar P Friday, November 5, 2010 8:00 PM
    Wednesday, November 3, 2010 8:38 PM

All replies

  • If the fields you're compare use different collation, you'll get this error. You can explicitly tell the query which collation to use, e.g.

    select * from TableA A inner join TableB B on A.Field1 = B.Field2 COLLATE Latin1_General_CI_AI
    

     


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Proposed as answer by JPRL Thursday, November 4, 2010 12:03 AM
    Monday, November 1, 2010 10:13 PM
    Moderator
  • Post your SQL query to correct it for you. (Naom's response is the correct one.) Thanks,
    Ali Hamdar (alihamdar.com - www.ids.com.lb)
    Monday, November 1, 2010 10:16 PM
  • That means you are trying to compare two columns with different collations.  If you do this you must tell SQL Server which collation to use to do the comparison.  For example, the following will get the error you have

    Declare @Test Table (Foo1 varchar(10) Collate SQL_Latin1_General_CP1_CI_AS, Foo2 varchar(10) Collate Latin1_General_CI_AI)
    Select 'True' From @Test Where Foo1 = Foo2;
    

    But the following will not

    Declare @Test Table (Foo1 varchar(10) Collate SQL_Latin1_General_CP1_CI_AS, Foo2 varchar(10) Collate Latin1_General_CI_AI)
    Select 'True' From @Test Where Foo1 = Foo2 Collate Latin1_General_CI_AI;
    

    You may want to investigate why you have columns with different collations.  That is generally not a good idea (but sometimes it is useful).  If you do, you will need to be very careful writing code to avaiod this error.  You may want to read up on collation in BOL and there is lots of good info available by Googleing or Binging "SQL Server collation".

    Tom

    Monday, November 1, 2010 10:17 PM
  • Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation

    The following page deals with the issue. Easiest solution to remember: add the clause COLLATE DATABASE_DEFAULT on either side of the equal operation:

    http://sqlusa.com/bestpractices2005/collatedatabasedefault/

    BOL link:

    database_default

    Causes the COLLATE clause to inherit the collation of the current database

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Tuesday, November 2, 2010 6:19 AM
    Moderator
  • One or some of you columns within the tables you are joining have different collation.

    When you are matching columns try using:

    ON/WHERE/AND table1.col1 COLLATE DATABASE_DEFAULT = table2.col1 COLLATE DATABASE_DEFAULT
    

    For more info, you could visit following links:

    http://sqlwithmanoj.blogspot.com/2010/02/collation-conflict-in-sql-server.html

    http://sqlwithmanoj.blogspot.com/2010/05/change-database-collation.html


    ~Manu
    http://sqlwithmanoj.blogspot.com/
    Tuesday, November 2, 2010 6:33 AM
  • ON/WHERE/AND table1.col1 COLLATE DATABASE_DEFAULT = table2.col1 COLLATE DATABASE_DEFAULT
    
    

     

    You don't have to double up COLLATE DATABASE_DEFAULT. Sufficient on either side.
    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Tuesday, November 2, 2010 6:46 AM
    Moderator
  • select * from TableA A inner join TableB B on A.Field1 = B.Field2 COLLATE Latin1_General_CI_AI
    
    
    Unless you have a need for a specific collation, it is safer, simpler to use COLLATE DATABASE_DEFAULT.
    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Tuesday, November 2, 2010 7:08 AM
    Moderator
  • ON/WHERE/AND table1.col1 COLLATE DATABASE_DEFAULT = table2.col1 COLLATE DATABASE_DEFAULT
    
    

     

    You don't have to double up COLLATE DATABASE_DEFAULT. Sufficient on either side.
    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM

    @Kalman,

    Thanks for the correction... I used to use them both sides.

    And yes I also think its safe to use default collation.


    ~Manu
    http://sqlwithmanoj.blogspot.com/
    Tuesday, November 2, 2010 7:15 AM
  • You may want to investigate why you have columns with different collations.  That is generally not a good idea (but sometimes it is useful). 

    Multilingual database applications is one area where useful.


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Tuesday, November 2, 2010 7:41 AM
    Moderator
  • Following is my query

     

                      
                        
                        
    ALTER PROCEDURE [dbo].[stp_GetAccountData] -- stp_GetAccountData '1','StatusofCards','ASC',0 ,'1','3','100'                  
    @vGroupId VARCHAR(5),                 
    @vSortBy VARCHAR(30),                 
    @vSortDirection VARCHAR(20),               
    @vStatus VARCHAR(10),
    @Start_Record INT,
    @End_Record INT,
    @no_results_total INT OUTPUT                   
    AS                     
    BEGIN            
            
     DECLARE @vQuery VARCHAR(8000) 
     DECLARE @vTempQuery VARCHAR(8000)
     DECLARE @vStartRecordvar VARCHAR(10),@vEndRecordvar VARCHAR(10)
     
     CREATE TABLE #list
     (
     record_num INT IDENTITY(1,1),
     TempAcctNum NVARCHAR(40),
     TempAcctName NVARCHAR(100),
     TempNumOfCrds SMALLINT,
     TempSys2Acct NVARCHAR(10),
     TempDelType TINYINT,
     TempStatusOfCrd VARCHAR(100),
     TempLastDate VARCHAR(100),
     TempSys2Acct1 NVARCHAR(10),
     TempShrtName NVARCHAR(50),
     TempAdd1 NVARCHAR(200),
     TempAdd2 NVARCHAR(200),
     TempCity NVARCHAR(100),
     TempState NVARCHAR(100),
     TempZipCode NVARCHAR(50),
     TempOpenDate DATETIME,
     TempFax NVARCHAR(50),
     TempUsr1 NVARCHAR(100),
     TempUsr2 NVARCHAR(100),
     TempUsr3 NVARCHAR(100),
     TempUsr4 NVARCHAR(100),
     TempMemo NTEXT,
     TempMail NVARCHAR(100),
     TempNoSys2Status NVARCHAR(50),
     TempDelete BIT,
     TempEdit BIT,
     TempContName VARCHAR(200),
     TempPhone NVARCHAR(50)
     )  
     
     
     SET @vTempQuery = 'INSERT INTO #list (TempAcctNum, TempAcctName, TempNumOfCrds, TempSys2Acct, TempDelType, TempStatusOfCrd ,
     TempLastDate, TempSys2Acct1, TempShrtName, TempAdd1, TempAdd2, TempCity, TempState, TempZipCode, TempOpenDate, TempFax,
     TempUsr1, TempUsr2, TempUsr3, TempUsr4 , TempMemo, TempMail, TempNoSys2Status, TempDelete , TempEdit, TempContName,
     TempPhone'+ +')'                  
     
     IF(@vSortBy='StatusofCards') 
      BEGIN 
       SET @vSortBy=@vSortBy; 
      END 
     ELSE IF(@vSortBy='ContName') 
      BEGIN 
       SET @vSortBy=@vSortBy; 
      END 
      ELSE IF(@vSortBy='Phone') 
      BEGIN 
       SET @vSortBy=@vSortBy; 
      END 
      ELSE IF(@vSortBy='LastDate') 
      BEGIN 
       SET @vSortBy=@vSortBy; 
      END 
      ELSE IF(@vSortBy='Nosys2Status') 
      BEGIN 
       SET @vSortBy=@vSortBy; 
      END 
     ELSE 
      BEGIN 
      SET @vSortBy='Account'+'.'+@vSortBy  
      END 
     IF (@vGroupId=1)                   
     BEGIN                    
      print(1)  
     SET @vTempQuery = @vTempQuery + 'SELECT   dbo.Account.AcctNum, dbo.Account.Name, dbo.Account.NumberOfCards,dbo.Account.Sys2Acct, dbo.Account.DelType,             
     StatusofCards = CASE dbo.Account.DelType WHEN ''4'' THEN ''None Existed'' WHEN ''1'' THEN ''Reassigned'' WHEN ''3'' THEN ''Pending Deletion'' WHEN ''5'' THEN ''Deleted'' END,                     
        CONVERT(VARCHAR(10),dbo.Account.LastDate,110) AS LastDate, dbo.Account.Sys2Acct, dbo.Account.ShortName, dbo.Account.Address1, dbo.Account.Address2, dbo.Account.City,                     
        dbo.Account.State, dbo.Account.ZipCode, dbo.Account.OpenDate, dbo.Account.Fax, dbo.Account.User1, dbo.Account.User2, dbo.Account.User3,                     
        dbo.Account.User4, dbo.Account.Memo, dbo.Account.EMail,           
     CASE WHEN dbo.Account.Nosys2Status=1            
        THEN ''Valid''            
        ELSE ''Inval'' END AS ''Nosys2Status'',            
     ''True'' AS [Delete],''True'' AS [Edit],   
      Case WHEN ((UD.LastName + '' '' + UD.FirstName) <> ''Admin System'')    
     THEN UD.LastName + '' '' + UD.FirstName   
     ELSE '' ''   
     END AS ContName,   
        
     UD.Phone Phone                      
     FROM  dbo.Account JOIN UsersDetails UD ON dbo.Account.ContName= UD.UserId       
     WHERE  dbo.Account.Deleted = '''+ @vStatus +'''     
     UNION ALL     
     SELECT   dbo.Account.AcctNum, dbo.Account.Name, dbo.Account.NumberOfCards,dbo.Account.Sys2Acct, dbo.Account.DelType,             
     StatusofCards = CASE dbo.Account.DelType WHEN ''4'' THEN ''None Existed'' WHEN ''1'' THEN ''Reassigned'' WHEN ''3'' THEN ''Pending Deletion'' WHEN ''5'' THEN ''Deleted'' END,                     
        CONVERT(VARCHAR(10),dbo.Account.LastDate,110) AS LastDate, dbo.Account.Sys2Acct, dbo.Account.ShortName, dbo.Account.Address1, dbo.Account.Address2, dbo.Account.City,                     
        dbo.Account.State, dbo.Account.ZipCode, dbo.Account.OpenDate, dbo.Account.Fax, dbo.Account.User1, dbo.Account.User2, dbo.Account.User3,                     
        dbo.Account.User4, dbo.Account.Memo, dbo.Account.EMail,           
     CASE WHEN dbo.Account.Nosys2Status=1            
        THEN ''Valid''            
        ELSE ''Inval'' END AS ''Nosys2Status'',            
     ''True'' AS [Delete],''True'' AS [Edit],CAST(ContName As NVARCHAR(50)) ContName, Phone                      
     FROM     dbo.Account        
     WHERE  dbo.Account.ContName is Null and dbo.Account.Deleted = '''+ @vStatus +''' ORDER BY '+ +@vSortBy +' '+ @vSortDirection
     EXEC(@vTempQuery)  
     
     SET @no_results_total = @@ROWCOUNT
     IF(@End_Record=0)
     SELECT @Start_Record=1,@End_Record=@no_results_total
     
     SET @vStartRecordvar =CAST(@Start_Record AS VARCHAR(10))                
     SET @vEndRecordvar = CAST(@End_Record AS VARCHAR(10))
     
     SET @vQuery ='SELECT   dbo.Account.AcctNum, dbo.Account.Name, dbo.Account.NumberOfCards,dbo.Account.Sys2Acct, dbo.Account.DelType,             
     StatusofCards = CASE dbo.Account.DelType WHEN ''4'' THEN ''None Existed'' WHEN ''1'' THEN ''Reassigned'' WHEN ''3'' THEN ''Pending Deletion'' WHEN ''5'' THEN ''Deleted'' END,                     
        CONVERT(VARCHAR(10),dbo.Account.LastDate,110) AS LastDate, dbo.Account.Sys2Acct, dbo.Account.ShortName, dbo.Account.Address1, dbo.Account.Address2, dbo.Account.City,                     
        dbo.Account.State, dbo.Account.ZipCode, dbo.Account.OpenDate, dbo.Account.Fax, dbo.Account.User1, dbo.Account.User2, dbo.Account.User3,                     
        dbo.Account.User4, dbo.Account.Memo, dbo.Account.EMail,           
     CASE WHEN dbo.Account.Nosys2Status=1            
        THEN ''Valid''            
        ELSE ''Inval'' END AS ''Nosys2Status'',            
     ''True'' AS [Delete],''True'' AS [Edit],   
      Case WHEN ((UD.LastName + '' '' + UD.FirstName) <> ''Admin System'')    
     THEN UD.LastName + '' '' + UD.FirstName   
     ELSE '' ''   
     END AS ContName,   
        
     UD.Phone Phone                      
     FROM  dbo.Account JOIN UsersDetails UD ON dbo.Account.ContName= UD.UserId JOIN #list lst ON lst.TempAcctNum= AcctNum AND lst.record_num BETWEEN '+@vStartRecordvar+' AND '+@vEndRecordvar+'     
     WHERE  dbo.Account.Deleted = '''+ @vStatus +'''     
     UNION ALL     
     SELECT   dbo.Account.AcctNum, dbo.Account.Name, dbo.Account.NumberOfCards,dbo.Account.Sys2Acct, dbo.Account.DelType,             
     StatusofCards = CASE dbo.Account.DelType WHEN ''4'' THEN ''None Existed'' WHEN ''1'' THEN ''Reassigned'' WHEN ''3'' THEN ''Pending Deletion'' WHEN ''5'' THEN ''Deleted'' END,                     
        CONVERT(VARCHAR(10),dbo.Account.LastDate,110) AS LastDate, dbo.Account.Sys2Acct, dbo.Account.ShortName, dbo.Account.Address1, dbo.Account.Address2, dbo.Account.City,                     
        dbo.Account.State, dbo.Account.ZipCode, dbo.Account.OpenDate, dbo.Account.Fax, dbo.Account.User1, dbo.Account.User2, dbo.Account.User3,                     
        dbo.Account.User4, dbo.Account.Memo, dbo.Account.EMail,           
     CASE WHEN dbo.Account.Nosys2Status=1            
        THEN ''Valid''            
        ELSE ''Inval'' END AS ''Nosys2Status'',            
     ''True'' AS [Delete],''True'' AS [Edit],CAST(ContName As NVARCHAR(50)) ContName, Phone                      
     FROM     dbo.Account JOIN #list lst ON lst.TempAcctNum= AcctNum AND lst.record_num BETWEEN '+@vStartRecordvar+' AND '+@vEndRecordvar+'           
     WHERE  dbo.Account.ContName is Null and dbo.Account.Deleted = '''+ @vStatus +''' ORDER BY '+ +@vSortBy +' '+ @vSortDirection      
                        
     EXEC (@vQuery) 
                         
     END                   
     ELSE                   
     BEGIN                   
     /*This procedure is used for retrieve all site datas*/                     
      SET @vTempQuery = @vTempQuery +'SELECT   dbo.Account.AcctNum, dbo.Account.Name,    
     dbo.Account.NumberOfCards,dbo.Account.Sys2Acct, dbo.Account.DelType,             
      StatusofCards = CASE dbo.Account.DelType WHEN ''4'' THEN ''None Existed'' WHEN ''1'' THEN ''Reassigned'' WHEN ''3'' THEN ''Pending Deletion'' WHEN ''5'' THEN ''Deleted'' END,                     
        CONVERT(VARCHAR(10),dbo.Account.LastDate,110) AS LastDate, dbo.Account.Sys2Acct, dbo.Account.ShortName, dbo.Account.Address1, dbo.Account.Address2, dbo.Account.City,             
        dbo.Account.State, dbo.Account.ZipCode, dbo.Account.OpenDate, dbo.Account.Fax, dbo.Account.User1, dbo.Account.User2, dbo.Account.User3,                    
        dbo.Account.User4, dbo.Account.Memo, dbo.Account.EMail ,           
     CASE WHEN dbo.Account.Nosys2Status=1            
        THEN ''Valid''            
        ELSE ''Inval'' END AS ''Nosys2Status'',            
     [Delete],[Edit], Case WHEN ((UD.LastName + '' '' + UD.FirstName) <> ''Admin System'')    
     THEN UD.LastName + '' '' + UD.FirstName   
     ELSE '' ''   
     END AS ContName, UD.Phone Phone              
     FROM     dbo.Account JOIN SysGroup SG ON dbo.Account.AcctNum=SG.Record JOIN UsersDetails UD ON dbo.Account.ContName= UD.UserId  
                        
     WHERE  dbo.Account.Deleted = '''+ @vStatus +''' AND SG.GroupId='''+ @vGroupId +''' AND UPPER(SG.ModuleName)=''ACCOUNT'' AND SG.[View]=1                     
           
     UNION ALL     
     SELECT   dbo.Account.AcctNum, dbo.Account.Name, dbo.Account.NumberOfCards,dbo.Account.Sys2Acct, dbo.Account.DelType,             
      StatusofCards = CASE dbo.Account.DelType WHEN ''4'' THEN ''None Existed'' WHEN ''1'' THEN ''Reassigned'' WHEN ''3'' THEN ''Pending Deletion'' WHEN ''5'' THEN ''Deleted'' END,                     
        CONVERT(VARCHAR(10),dbo.Account.LastDate,110) AS LastDate, dbo.Account.Sys2Acct, dbo.Account.ShortName, dbo.Account.Address1, dbo.Account.Address2, dbo.Account.City,             
        dbo.Account.State, dbo.Account.ZipCode, dbo.Account.OpenDate, dbo.Account.Fax,  dbo.Account.User1, dbo.Account.User2, dbo.Account.User3,                    
        dbo.Account.User4, dbo.Account.Memo, dbo.Account.EMail ,           
     CASE WHEN dbo.Account.Nosys2Status=1            
        THEN ''Valid''            
        ELSE ''Inval'' END AS ''Nosys2Status'',            
     [Delete],[Edit],CAST(ContName AS NVARCHAR(50)) ContName, Phone           
     FROM     dbo.Account JOIN SysGroup SG ON dbo.Account.AcctNum=SG.Record 
                        
     WHERE   dbo.Account.ContName is Null and dbo.Account.Deleted = '''+ @vStatus +''' AND SG.GroupId='''+ @vGroupId +''' AND UPPER(SG.ModuleName)=''ACCOUNT'' AND SG.[View]=1                     
     ORDER BY '+ @vSortBy +' '+ @vSortDirection 
     EXEC(@vTempQuery)
     SET @no_results_total = @@ROWCOUNT
     IF(@End_Record=0)
     SELECT @Start_Record=1,@End_Record=@no_results_total
     
     SET @vStartRecordvar =CAST(@Start_Record AS VARCHAR(10))                
     SET @vEndRecordvar = CAST(@End_Record AS VARCHAR(10))                    
     
     SET @vQuery ='SELECT   dbo.Account.AcctNum, dbo.Account.Name, dbo.Account.NumberOfCards,dbo.Account.Sys2Acct, dbo.Account.DelType,             
      StatusofCards = CASE dbo.Account.DelType WHEN ''4'' THEN ''None Existed'' WHEN ''1'' THEN ''Reassigned'' WHEN ''3'' THEN ''Pending Deletion'' WHEN ''5'' THEN ''Deleted'' END,                     
      CONVERT(VARCHAR(10),dbo.Account.LastDate,110) AS LastDate, dbo.Account.Sys2Acct, dbo.Account.ShortName, dbo.Account.Address1, dbo.Account.Address2, dbo.Account.City,             
      dbo.Account.State, dbo.Account.ZipCode, dbo.Account.OpenDate, dbo.Account.Fax, dbo.Account.User1, dbo.Account.User2, dbo.Account.User3,                    
      dbo.Account.User4, dbo.Account.Memo, dbo.Account.EMail ,           
      CASE WHEN dbo.Account.Nosys2Status=1            
        THEN ''Valid''            
        ELSE ''Inval'' END AS ''Nosys2Status'',            
      [Delete],[Edit],    
      Case WHEN ((UD.LastName + '' '' + UD.FirstName) <> ''Admin System'')    
      THEN UD.LastName + '' '' + UD.FirstName   
      ELSE '' ''   
      END AS ContName,UD.Phone Phone              
     FROM     dbo.Account JOIN SysGroup SG ON dbo.Account.AcctNum=SG.Record JOIN UsersDetails UD ON dbo.Account.ContName= UD.UserId JOIN #list lst ON lst.TempAcctNum= AcctNum AND lst.record_num BETWEEN '+@vStartRecordvar+' AND '+@vEndRecordvar+'                                         
                        
     WHERE  dbo.Account.Deleted = '''+ @vStatus +''' AND SG.GroupId='''+ @vGroupId +''' AND UPPER(SG.ModuleName)=''ACCOUNT'' AND SG.[View]=1                     
           
     UNION ALL     
     SELECT dbo.Account.AcctNum, dbo.Account.Name, dbo.Account.NumberOfCards,dbo.Account.Sys2Acct, dbo.Account.DelType,             
      StatusofCards = CASE dbo.Account.DelType WHEN ''4'' THEN ''None Existed'' WHEN ''1'' THEN ''Reassigned'' WHEN ''3'' THEN ''Pending Deletion'' WHEN ''5'' THEN ''Deleted'' END,                     
      CONVERT(VARCHAR(10),dbo.Account.LastDate,110) AS LastDate, dbo.Account.Sys2Acct, dbo.Account.ShortName, dbo.Account.Address1, dbo.Account.Address2, dbo.Account.City,             
      dbo.Account.State, dbo.Account.ZipCode, dbo.Account.OpenDate, dbo.Account.Fax, dbo.Account.User1, dbo.Account.User2, dbo.Account.User3,                    
      dbo.Account.User4, dbo.Account.Memo, dbo.Account.EMail,            
      CASE WHEN dbo.Account.Nosys2Status=1            
        THEN ''Valid''            
        ELSE ''Inval'' END AS ''Nosys2Status'',            
     [Delete],[Edit], CAST(ContName AS NVARCHAR(50)) ContName, Phone            
     FROM dbo.Account JOIN SysGroup SG ON dbo.Account.AcctNum=SG.Record  JOIN #list lst ON lst.TempAcctNum= AcctNum AND lst.record_num BETWEEN '+@vStartRecordvar+' AND '+@vEndRecordvar+'                                        
                        
     WHERE   dbo.Account.ContName is Null and dbo.Account.Deleted = '''+ @vStatus +''' AND SG.GroupId='''+ @vGroupId +''' AND UPPER(SG.ModuleName)=''ACCOUNT'' AND SG.[View]=1                     
     ORDER BY '+ @vSortBy +' '+ @vSortDirection 
      EXEC (@vQuery)                      
     END                   
                       
                 
      print (@vQuery)           
                  
    SELECT UseAcctsOnSys FROM SystemSettings                     
    DROP TABLE #list                   
    END 
    
    Wednesday, November 3, 2010 8:21 PM
  • Your collation conflict may go away if you declare the temp table #list as

     CREATE TABLE #list
     (
     record_num INT IDENTITY(1,1),
     TempAcctNum NVARCHAR(40) Collate Database_Default,
     TempAcctName NVARCHAR(100) Collate Database_Default,
     TempNumOfCrds SMALLINT,
     TempSys2Acct NVARCHAR(10) Collate Database_Default,
     TempDelType TINYINT,
     TempStatusOfCrd VARCHAR(100) Collate Database_Default,
     TempLastDate VARCHAR(100) Collate Database_Default,
     TempSys2Acct1 NVARCHAR(10) Collate Database_Default,
     TempShrtName NVARCHAR(50) Collate Database_Default,
     TempAdd1 NVARCHAR(200) Collate Database_Default,
     TempAdd2 NVARCHAR(200) Collate Database_Default,
     TempCity NVARCHAR(100) Collate Database_Default,
     TempState NVARCHAR(100) Collate Database_Default,
     TempZipCode NVARCHAR(50) Collate Database_Default,
     TempOpenDate DATETIME,
     TempFax NVARCHAR(50) Collate Database_Default,
     TempUsr1 NVARCHAR(100) Collate Database_Default,
     TempUsr2 NVARCHAR(100) Collate Database_Default,
     TempUsr3 NVARCHAR(100) Collate Database_Default,
     TempUsr4 NVARCHAR(100) Collate Database_Default,
     TempMemo NTEXT,
     TempMail NVARCHAR(100) Collate Database_Default,
     TempNoSys2Status NVARCHAR(50) Collate Database_Default,
     TempDelete BIT,
     TempEdit BIT,
     TempContName VARCHAR(200) Collate Database_Default,
     TempPhone NVARCHAR(50) Collate Database_Default
     ) 
     
    
    Tom

    • Marked as answer by Anil Kumar P Friday, November 5, 2010 8:00 PM
    Wednesday, November 3, 2010 8:38 PM
  • What is your SQL Server version? If SQL 2005 and up, I suggest to make variables holding the queries to be nvarchar(max). In addition, don't perform string concatenation when not needed, use sp_ExecuteSQL.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Wednesday, November 3, 2010 8:43 PM
    Moderator
  • There's an article on my web site, http://www.sommarskog.se/dynamic_sql.html

    It will not going to help you with your collationn problems. But it may help you to write your dynamic SQL better.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
    (Just click the link you need.)
    Wednesday, November 3, 2010 11:27 PM
  • Thanks a lot. you really made my day...

    i was struggling with this from last 2 weeks. Excellent...

    Once again thanks a lot.

    Friday, November 5, 2010 8:01 PM