SQL Server Performance

SQL SERVER COLLATION issue

Discussion in 'ALL SQL SERVER QUESTIONS' started by Antonio Benildus Muerling, Apr 25, 2012.

  1. Hi Anyone,

    Please note that my solution provider, had addressed an issue for what I raised an issue, and my issue is, all of a sudden my application had got halted without working, returning an error, and the error is as follows

    /********************************************************************************************************************/
    -2147217900Cannot resolve the collation conflict between "SQL_Latin_General_CPI_CI_AS" and "Latin1_General_CI_AS" in the equal to operaion
    /********************************************************************************************************************/

    so this issue was raised at the provider , and the sooner they gave me an answer saying please bring the working database as per to the master database

    working database - SQL_Latin1_General_CP1_CI_AS
    master database - Latin1_General_CI_AS

    Please see attached file for the error

    Thanks,

    AB

    Attached Files:

  2. why does no one reply to me thread..
  3. Shehap MVP, MCTS, MCITP SQL Server

    For such cases, it is almost probable some query is joining some columns from user DB with other with either Master DB or Temp DB like a table variable or temp table for examples..

    Therefore , you could work out 2 approaches :

    · To standardize your user DB according to server collation (master DB collation ) (Recommended if no mind from APP itself hitting this user DB)

    · Or to use with collatecommands like collate Latin1_General_CI_AS Or collateSQL_Latin1_General_CP1_CI_ASfor string columns only not numerical columns
  4. Hi Shehap,

    can I do the option one as follows

    ALTER DATABASE master
    COLLATE LATIN1_General_CI_AS
    GO

    and can you elaborate on the option 2 what you have mentioned about

    Thanks,

    AB
  5. Shehap MVP, MCTS, MCITP SQL Server

    It should be for user DB as ALTERDATABASE DBName COLLATE LATIN1_General_CI_AS.

    For the 2nd option , it could be something like below

    SELECT [empid]
    ,[empname] collate SQL_Latin1_General_CP1_CI_AS
    ,[salary]--Invalid to use collatte command here since it is of numeric data type
    ,[deptid]--Invalid to use collatte command here since it is of numeric data type
    FROM [Workshops].[dbo].[Employees]
  6. Hi Shehap,

    the following error occured, while trying to collate it as per to the master database

    Msg 5030, Level 16, State 2, Line 1

    The database could not be exclusively locked to perform the operation.

    Msg 5072, Level 16, State 1, Line 1

    ALTER DATABASE failed. The default collation of database 'iMIS' cannot be set to Latin1_General_CI_AS.
    Thanks,

    Antonio
  7. Luis Martin Moderator

    You have to set that database to single user. After that should work.
  8. Hi Luis,

    Thanks for your help, even the below after setting it to multi user it still does not work

    ALTER DATABASE someDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE




    ALTER DATABASE someDatabase COLLATE SQL_Latin1_General_CP437_BIN




    ALTER DATABASE someDatabase SET MULTI_USER

    Thanks,

    Antonio Benildus Muerling
  9. Hi All,

    Please note that I altered it trying with 'Latin1_General_CI_AS', the above is a template that I have showed, sorry for the inconvenience.

    Thanks,

    Antonio Benildus Muerling
  10. Shehap MVP, MCTS, MCITP SQL Server

    For such production DBs having intensive workload of end users , it is preferred to run the below query to alter any DB feature that are looking for single user

    settransactionisolationlevelserializable

    ALTERDATABASE DBName SETSINGLE_USERWITHROLLBACKIMMEDIATE

    ALTERDATABASE DBName COLLATE Latin1_General_CI_AS

    ALTERDATABASE DBName SETMULTI_USER

    Please work out it and let me know your feedback
  11. Hi Shehap,

    Thanks for the help, but I have tried this, and did not work out you know, see my reply to Luis, as I have pasted the code in that reply with what I tried, pleade advice me if this a different set of code, than what I have pasted, please help me in doing this, cause I can see right on top you have mentioned saying that settransactionisolationlevelserializable is that something that I have to set up with a value or something.

    Thanks,

    AB
  12. Hi All,

    Thanks a million in reading this post

    Is there anyone please, to help me with the collation issue that am having, it indeed would be a great help, I am holding so many things and my backlog grows, please can you help me in sorting this..
    issue is the system database which is the master database has a different collation that the working database, which prevents from working together, and am not in a position to work with the application to insert records, via the front end application, so as I am unable in updating records as well..

    I have tried, doing altering the database setting up the single mode and still it does not work - this is for your information

    Thanks,

    AB
  13. Luis Martin Moderator

    My suggestion is:
    1) Detach users database with new collation. (also backup).
    2) Use some script to get all sql users, jobs, etc.
    3) Rebuild master to set new collation. (check book on line for that).
    4) Generate old users.
    5) Attach or restore.
  14. amahalingam New Member

    It is not recommended to change the collation of the database that is in USE. You should have been prudent while creating the database.
    Given the problem,
    it is difficult to change the collation of the database to Latin1_General_CI_AS? when you do so you will confront the errors like...?
    ALTER DATABASE Northwind COLLATE Latin1_General_CI_AS
    Msg 5075, Level 16, State 1, Line 1701
    The object 'CK_Birthdate' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.

    If you are still desparate to make this change then...?

    Create a new empty database with the desired collation
    Script out the schema/index/view/proc definition from the old database
    change the collation in the scripts to the desired collation
    Create the objects in the new database
    Export the data from old database to a file and Import the data to the new database.

    Believe me this is going to be a real challenge. Good Luck.
  15. Hi All,

    this is now working very well, and the measures taken were.

    Cause of the problem
    1) initially this was working fine with one sql server instance. (
    2) this was copied to sql server defautl instance, and I do not the reason. (working database and the master are the same)
    which created the issue, as a result of above 2)

    Resolution
    1) this was copied back to the original place, after checking we have enough licences.
    2) have tested several tests, working fine.

    all maintenace plans, of the automated backups, indexing have been implemented

    Thanks,

    AB

Share This Page