collation
-
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
-
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 EngineerThis 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.
-
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
-
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?
-
How are you importing the file? Can you please describe the process. Also, are you using any temp tables in the process?
-
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.
-
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 EngineerThis 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.
-
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.
-
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
-
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!
-
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