Post reply

collation

  • February 9, 2004 at 8:49 am

    #163008

    i have two sqlserver 2000, on server 1 the collation is SQL_Latin1_General_CP1_CI_AS which is accent sensitive. On server 2 the collation type is FRENCH_CI_AS whis is also accent sensitive and the database on both server has the collation type SQL_Latin1_General_CP1_CI_AS .

    Now we load a textfile with accents into the server 1 and the queries results shows accent correctly, on server 2 not.

    What is the purpose of this? Could somebody help?

    Thanks

  • February 9, 2004 at 12:57 pm

    #493298

    Fred,

    I think we need more information on this. Are you saying that on Server1 you used SQL_Latin1_General_CP1_CI_AS as the default when you set up SQL? And that on Server2 you used FRENCH_CI_AS as the default when setting up SQL but that both have a database with the collation of SQL_Latin1_General_CP1_CI_AS?

    Finally what/how are you importing the data?




    Gary Johnson
    Microsoft Natural Language Group
    DBA, Sr. DB Engineer

    This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

  • February 9, 2004 at 9:57 pm

    #493323

    I have had a simalar problem in the past.  Server1 had default collation of SQL_Latin1_General_CP1_CI_AS and Server2 had a default collation of Latin1_General_CI_AS but the database had the SQL collation.  Stored procedures which ran on Server1 would error on Server2 with collation conflicts.  I eventually tracked the problem down to the fact that these sp's were using temporary tables, which as we know, are created in tempdb and therefore were created with the Server collation.  The developers tried changing their code to specify the collation when they created the temp tables, but there was so much code to be changed we opted to reinstall SQL Server instead.

    Is there any way your load process could be using a temp table?

    Angela

  • February 10, 2004 at 8:37 am

    #493400

    Yes it s exactly that concerning the collation of both servers.

    we import a text file.

    I give you an example of query results

    server1:

    Mener à terme l'étude

    server2:

    Mener Ó terme l'Útude

    correct is server1

    Anybody can help?

     

  • February 10, 2004 at 3:37 pm

    #493509

    How are you importing the file?  Can you please describe the process.  Also, are you using any temp tables in the process?

  • February 11, 2004 at 1:13 am

    #493561

    Hello Angela,

    we import the file with a DTS. Strange is that the developper told me on server 1 he runs the dts manually from his machine, on server 2 the dts is running as a job every morning.

  • February 11, 2004 at 1:23 am

    #493563

    Fred,

    I would double check the DTS package and make sure that the transformations being done are indeed using nvarchar, nchar or ntext. Also make sure that if the package makes a table that it uses the correct collation on all the character type fields. My guess is that the package is creating a table to hold the data while some process is being done to it in the temp DB and it's losing the collation there.

    Good Luck!




    Gary Johnson
    Microsoft Natural Language Group
    DBA, Sr. DB Engineer

    This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

  • February 11, 2004 at 1:43 am

    #493569

    Gary,

    thank you for the response. Strange is also that yesterday i migrate the DB from server 2 (bad one) to server 1 with a different name and the same trouble appears.

  • February 11, 2004 at 2:49 pm

    #493718

    Fred,

    Have you checked what Gary has suggested?  Please tell us what you have found out from the developer in response to Gary's questions.

    Angela

  • February 12, 2004 at 2:14 am

    #493762

    Angela, Gary

    when we run on server 2 the dts manually to import tha data, the collation is correct.

    When the dts runs as a job the collation is wrong.

    It s very strange!

     

     

  • February 16, 2004 at 9:34 am

    #494210

    we are testing since some days the difference.

    And we found out that it is not a problem of collation or tempdb but it depends wich account started the job. On both servers when the developper started the dts the results are correct on both servers.

    When the job is starting with the sql agent account the result are wrong

    It seems that the regional settings from the account are determining he results

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply