SQL Server Performance

Error while processing a cube :(

Discussion in 'Analysis Services/Data Warehousing' started by Joozh, Mar 21, 2005.

  1. Joozh New Member

    I am getting the following error when trying to process my cube. Can someone please guide as to how this problem can be solved. Thanks

    Data source provider error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot resolve collation conflict for equal to operation.;37000;
  2. satya Moderator

    The error raised due to few columns have different collation, and also check the default collation of TEMPDB database.

    This happens when you declare variable tables. For some reason when declaring tables in memory and one of the columns is a charchar and the collation is NOT set to the database default collation.

    When creating the table in memory you have to set the collation as well. Cant remember exactly how this sql code looke like, you should be able to find. I think it looks something like this:
    declare @temptable TABLE (charcolumn varchar(100) collate --the collation name here) and books online will help you.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. Joozh New Member

    Thanks Satya.<br /><br />This is strange becuase the collation settings have not been changed and a similar activity on the the same machine but a different SQL data is working just fine without any problems.<br /><br />I am not sure whatyou mean by creating a table in memory <img src='/community/emoticons/emotion-6.gif' alt=':(' /> <br />Could it have any thing to do with the drop/create scripts for the table????<br /><br />
  4. satya Moderator

    I mean when its processing the cube it will have to use the memory to create the temp objects in order to proceed further.

    So its worth check the statement used to create such objects while processing the cube.
    As mentioned have a look around to specify the collation setting while creating such objects.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  5. Joozh New Member

    Hi Satya,<br /><br />Thanks again for your reply.<br /><br />I am posting back for the benefit of other readers of this forum:<br /><br />I took a clue from your first reply despite "THINKING" that I had not changed the collation settings at all I started going through each field of each table....<br /><br />Guess what, ...to my surprise came across a table for which the Collation property of some fields were not "database defualt" but instead 'had turned into' <img src='/community/emoticons/emotion-1.gif' alt=':)' /> "SQL_Latin1_General_CP1_C1_AS"<br /><br />I honestly have no clue as to how this happened but the good thing is that the problem is now solved. <br /><br />I changed the Collation property of all such fields from "SQL_Latin1_General_CP1_C1_AS" to "database default" i.e. Windows Collation --&gt; Latin1_General, and the problem was solved.<br /><br />Hope this reply can benefit some other members of this forum.<br /><br />Regards.<br />
  6. ralph New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Joozh</i><br /><br />Hope this reply can benefit some other members of this forum.<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Yes, probably me. <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />I had some troubles with collations the past days. Actually I still have them, but I do not have enough information yet for a good description. (In short: a database moved to another server via backup and restore, some import does not work on new srv due to collation conflict.)<br /><br />I read a lot last night and I learned a lot. But I could not find out whether there is a way to easily list the collations of <b>all</b> objects in a database. It consists of 138 user tables and I'd bet that there is a better way to find them but using <br />exec sp_help &lt;table&gt;<br />for each one.<br /><br />Can someone please give me a hint?<br /><br />Thanks in advance. <img src='/community/emoticons/emotion-1.gif' alt=':)' />
  7. rockmoose New Member

    This will show the collation painlessly;

    SELECT TABLE_NAME, COLUMN_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS

    rockmoose

Share This Page