SQL SERVER – 2005 Change Database Compatible Level – Backward Compatibility

sp_dbcmptlevel
Sets certain database behaviors to be compatible with the specified version of SQL Server.

Example:
----SQL Server 2005 database compatible level to SQL Server 2000
EXEC sp_dbcmptlevel AdventureWorks, 80;
GO
----SQL Server 2000 database compatible level to SQL Server 2005
EXEC sp_dbcmptlevel AdventureWorks, 90;
GO

Version of SQL Server database can be one of the following:

  • 60 = SQL Server 6.0
  • 65 = SQL Server 6.5
  • 70 = SQL Server 7.0
  • 80 = SQL Server 2000
  • 90 = SQL Server 2005

The sp_dbcmptlevel stored procedure affects behaviors only for the specified database, not for the entire server. sp_dbcmptlevel provides only partial backward compatibility with earlier versions of SQL Server. A database containing an indexed view cannot be changed to a compatibility level lower than 80.

The best practice to change the compatibility level of database is in following three steps.

  • Set the database to single user access mode by using
    ALTER DATABASE SET SINGLE_USER
  • Change the compatibility level of the database.
  • Put the database in multiuser access mode by using
    ALTER DATABASE SET MULTI_USER

Reference : Pinal Dave (http://blog.SQLAuthority.com) , MSDN Article.

About these ads

123 thoughts on “SQL SERVER – 2005 Change Database Compatible Level – Backward Compatibility

  1. No that is not possible.

    Once you go 2005 you can not go back to previous application server. However, you can stay on previous application server and change compatibility as mentioned in this ticket.

    Regards,
    Pinal Dave (SQLAuthority.com)

  2. I have developed an sql script in sql server2005.It works fine with sql server2005 but fails with sql server2000.
    What should I do to make it work?
    please suggest.
    thanks in advance.

  3. Hi!

    I have installed SQL 2000 and SQL 2005 in my computer. I tried to use xml data type in ‘Microsoft SQL Server Management’, but it caused an error :”Cannot find data type xml.”

    So someone told me that i had to check the version and when I cheked I realized I was working with SQL 2000:
    “Microsoft SQL Server 2000 – 8.00.194 (Intel X86)
    Aug 6 2000 00:57:48
    Copyright (c) 1988-2000 Microsoft Corporation
    Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)”

    So, how can I change it for working with SQL 2005?. It’s like I’m working with SQL 2005 interface but internally it works as SQL 2000. Can you help me please?

  4. 8.00.194 is with no service pack which is more problematic and perform upto date service pack which is SP4 + hotfixes. To use SQL 2005 references you have to upgrade to 2005 and before that go with UPgrade advisor.

  5. Hi Pinal,

    Could provide some hint on the problems I encountered. I am using SQL SERVER 2005, my database never requred to change compatibility level until I use Pivot clause recently (so I change the compatibility level to 90). The funny thing is after changing database compatibility level to 90, one of my original stored procedures is not working properly any more. The error message below:
    ‘Conversion failed when converting the varchar value ‘N ‘ to data type int.’

    Many thanks for your kind help
    Ruiduan

  6. Hi Pinal,
    Can we assure SQL 2000 databases will work as expected in SQL 2005 in 80 compatibility. Is there any known issues for 80 mode in SQL 2005 server

  7. Hey Pinal,

    I am trying to run a query wherein, in a join condition I am equating a GUID column with a VARCHAR column, this query worked fine in SS 2000 but has started failing in SS 2005 (also have an index on this varchar column). So, I tried by removing the index on the column and the query worked in SS2005. Then after reading your article it makes me believe, that, even if I don’t remove the index and set the compatibility level to 80 for SS 2000 the query should start working fine. But you have also mentioned some of the features of SS 2005 might not work, so my 2 questions are –

    1) By setting the compatibility to 80 (SS 2000) in SS 2005, will the query work like it did before in SS 2000?
    2) If the compatibility is changed to 80, what features in SS 2005 will cease to work?

    Thanks in advance..

    Deepak Bhagwat.

  8. We have migrated our application from SQL 2000 to SQL 2005.
    Our application uses DTS packages which uses global variables. This is not working fine. Your suggestions are greatly appreciated.

  9. Hi,

    I tried to restore a database which is backed up from sql 2k5 and compability level is 80 to sql 2k. There is an error:

    The backed-up database has on-disk structure version 611. The server supports version 539 and cannot restore or upgrade this database. RESTORE DATABASE is terminating abnormally.

    The database was running on SQL 2k orginally. It had deattached, and then attached to SQL 2K5. Compability level was 80 and never changed. Now I’m trying to move it to SQL 2K again. But deattach/attach or backup/restore is give error.

    Any ideas ?
    thanks

  10. Hello Gurus,

    Comming from an Oracle environment into Microsoft, after had been assigned a task that is to

    See the possibility to migrate our MS SQL Server 2005 running in compatibility level mode 70 into mode 90.

    Been told that problem can be raised doing this, does anyone have any info what problem can be raised doing this.

    Our company is doing very heavy work based on Micosoft access application link connected to the SQL server,

    can there be problems here.

    Thx for your advices and hints in advance.

    Best regards

    Hank

  11. Hi,
    Can anyone please tell me the way to migrate data from sybase to sql server 2000.Am not able to find sybase odbc driver for DTS.

    Tips are all appreciated..

    Thanks in Advance…

  12. Hi Pinal,

    I have a problem with SL 2000 version 8.00.194
    I am transferring my tables and views from SQL 2005 to SQL 200 through DTS.
    After successfull creation of Database and its tables, I am trying to the modify the columns and relations. But the edit/modify option is not enabled in SQL 2000.

    Can you help me out for this. How to edit or modify the column properties and relationship.

    Thanks
    Mayank

  13. Hi Pinal,

    I need to change the Sql2000 MDF file to Sql2005 MDF. is there any feasible reason to change the file from sql2000 to sql2005?. Could you please tell me in detail regarding of this process?

    Thanks
    Suresh

  14. hi
    i am using SQL Server 8.00.194 . can i transfer the tables & databases from SQL Server 8.00.194 to SQL Server 2005.
    is SQL Server 8.00.194 comitible with SQL Server 2005

    i have both SQL Server 8.00.194 and SQL Server 2005 installed in my system.

    Thanks in Advance

  15. Hi Pinal,
    i am working in sql server 2005 …and we hav a huge database in sql2005 of panchkula.and we hav installed it nicely just 1 year back…and now after a year our programmers are facing some problems rearding to connect between database and frontend files ….sometime even they tried to connect the database remotely but they get the TIME OUT EXPRIED ERROR on their systems…but when i went to serever room to check the problem then that works fine on server with all connections ….this problem is increasing on daily basis..please give me a suggestion to handle this..

    thanks..and ur suggestions will be highly appericated….

    please reply as soon as possible…

    puneet singla…huda,panchkula

  16. Hi to all!!!
    actually i have an problem that one of my application is not working properly in sql server 05 so i decided that we are go through sql server 2000.
    but i had i problem that in my system sql 2000 occur and i take a backup from sql 05 from another system…
    i am not able to restore or attache that data base in my sql 2000.
    if any solution is there plz send me……..
    i try this..
    EXEC sp_dbcmptlevel gate_pass1, 90;
    but it not working..
    pl z help me out
    alok

  17. Alok,
    You should transfer your database from 2005 to 2000 “manually” “piece-by-piece”
    Tables and constrains should be recreates, indexes rebuilt, viewes re-created and data taken acress. you can use SSIS for it.
    As the author mentiones, once you are on 2005 you are on it — there’s no easy way back, especially when you changed the data, structure, I won’t even mention if you created some SSIS packages, or used new 2005 features, such as BI commands, XML data fields etc — here you are completely stuffed.

  18. How to reorg a database with Compatibility Level 70 on SQL Server 2005 using the maintanace plan or command line? (once sql2005 skip the reorg of the index when it is a 7.0 database and I cannot change its compatibility level because the application)

  19. Hi,

    Currently we have SQL server 2000 database for past 3 to 4 years. Now planning to move SQL Server 2005. The SQL 2000 database will be restored to 2005 server.
    My questions are,

    1. The SQL 2000 queries, Stored proc and views will work with SQL server 2005 without any changes?

    2. Is there any issues after conversion?

    Please help me in this area.

    Thanks,
    Rama

  20. hello,
    while tryin to connect Sql Server Studio to server, i get the followin error message:

    TITLE: Connect to Server
    ——————————

    Cannot connect to HIMANSHU-PC\SQLEXPRESS.

    ——————————
    ADDITIONAL INFORMATION:

    An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

    Please suggest me what to do so, to establish a successful connection.

  21. Pinal you mentioned —

    “Change database compatible level to 80.
    Restore the database in sql server 2000 server. Some features of the SQL Server 2005 may not function properly.”

    My understanding is its not suppossed to work – as SQL 2005 DB has a different filestructure which SQL2k cant understand.

  22. I have a coldfusion 7mx website that is attached to a ms SQL 2000 DB and all works fine, I copied the DB to sql 2005 and now in order to use the same queries I have to add the schema name to the query (was) database.tablename or just tablename and (now) it needs to be database.schema.tablename. I really do not want to find update a few hundred queries what is the best way to fix this?

    thanks

    -d

  23. – Why why sql 2005 server behave like that —

    Like command is not doing its job ….??
    ——————————————————-
    1- for simple query like this it is not executing :

    select cname from customer
    where cname Like ‘%i’

    if there is a name ali in the cname col it should return ali

    coz the last char in ali is ” i ”

    ————————————————-

    2- where as this query work fine :

    select cname from customer
    where cname Like ‘i%’

    this will return all name start with i .. it work fine but above one will not work why ??

    please help brothers >>>

    thank you

  24. Pingback: SQL SERVER - 2005 - Change Compatibility Level - T-SQL Procedure Journey to SQL Authority with Pinal Dave

  25. I need to change the compatibility setting in my MS SQL 5 database to UTF for a specific table. What is the best method of changing this?

  26. Hello.

    Somebody knows if its possible to change the Compatibility Level automatically (to SQL 2005) from Copy Database Wizard (or some parameters in the comand line of the job?)

    I need to copy some databases from SQL 2000 to SQL 2005 and set Compatibility Level to SQL 2005 automatically.

    Tanks!

  27. Hi,
    I have upgraded from 2000 to 2005. Now I want to execute built in encryption function EncryptByPassPhrase() but Im getting the error ”EncryptByPassphrase’ is not a recognized function name.” Is there any compatibility issue.

  28. hi,
    i installed sql server 2000 first then i install sql server 2005 with same local server.in sql server 2005 still its pointing to compatibility80 so i delete sql server 2000 so client component are delted of sql server 2000 but server is still there so still my localhost is pointing to compatibility 80.
    EXEC sp_dbcmptlevel,90 gets fails it says it supports till80.
    Valid values of database compatibility level are 60, 65, 70, or 80.this is the error message need urgetn help

  29. I thought that the compatibility level was to address the way that the TSQL was parsed in statements, but I can’t seem to make it work.

    I have a legacy app that is sending a statment to the server that looks like this:

    Select Foo
    From Bar ( index=abc)

    In SQL server 7.0 this works fine, but SQL server 2005 requires that index hints in queries be phrased differently.
    I thought that I could set the database to 7.0 compatibility mode and it would parse the statement without errors, but that’s not the case.

    So what’s the point of the compatibility level if not for parsing legacy SQL scripts?

  30. Hi,

    I have installed sql sever 2005 on my machine but on my live server sql server 2000 is installed. So how can I find that my stored procedure written with 2005 version is working with 2000 version.

    Please help me

  31. Tried to restore a SQL Server 2008 database backup to a SQL Server 2005 server. Unfortunately it did not work, even after changing the compatibility level. Please advise.

  32. I have exactly the same problem. My SQL 2008 db is running in 2005 compatibility mode. The backup from that db cannot be restored on my 2005 server. Shouldn’t that be possible? Is there another way to “copy” a sql 2008 db to a 2005 server?
    I appreciate your help. Thanks!

  33. Hi, I have upgraded from 2000 to 2005. Now I notice that all my views containing ORDER BY clause are returning rows in random order. I found that this is a known issue and so I’ve applied the post SP2 fix (My SQL in SP2) related to this problem (3175_316204_intl_i386_zip).
    The problem still remain.
    Can you help me ?
    Thanks

    M.

  34. @Mauro

    By default SQL Server will not allow you to create a view with Order by clause unless you use Top statement. By doing this sql server will not give you syntax error, BUT there is no guarentee that you will get ordered results using the combination of Top and Order by in a view.

    Resolution :
    You said, applying Service pack 2 solves the problem ??

    No, Actually Cummulative Pack 2 of Service Pack2 solves the problem. Meaning after SP2 released, there were cummulative packs as well ( I think current Cummulative pack is 10, which is now called as SP3, anyways). So you need to apply Sp2 and then apply cummulative pack sp2. Hopefully this will solve your issue.

    Download Link : http://support.microsoft.com/kb/936305/
    ( You need to register to get Cummulative Pack ).

    Hope this helps.
    Regards,
    IM.

  35. @Mohammed

    Thank you for your reply.
    Next week I will try to install the fix. If this not work I’ll
    go ahead with plan N.2 (in other words I’ll change the accounting software in order to fix that problem).

    Do you know a freeware tool that I can use to analize the
    performace of my server ?

    Regards,
    Mauro

  36. @Mauro,

    Regarding, Freetool to monitor Performance of your Server ?

    Look at these links.
    1. http://www.sqlservercentral.com/articles/Miscellaneous/2959/

    2. http://www.sqlservercentral.com/articles/Tools/64908/

    3. http://weblogs.sqlteam.com/mladenp/archive/2007/11/20/Free-SQL-Server-tools-that-might-make-your-life-a.aspx

    What do you mean by analyzing performance of my server.

    You mean SQL Server or Machine on which SQL Server is installed.

    To Monitor SQL Server 2005, you have a very good performance dashboard report. This report uses DMV’s so there is no overload on the system when this report is running, this report gives you very good information about SQL Server, I personally liked this report, this is available from Microsoft ( free download).

    You need to run installation and then you have to run a script in database. ( read instructions on download page).

    Download Link : http://www.microsoft.com/downloads/details.aspx?FamilyId=1d3a4a0d-7e0c-4730-8204-e419218c1efc&displaylang=en

    How to Install and use SQL Server 2005 Performance Dashboard Report please check this link (screen shot tutorial)

    Tutorial Link : http://www.mssqltips.com/tip.asp?tip=1553

    Regards,
    IM.

  37. I am using an application programmed in sql 2005, this application works perfect in computer where the sql server is installed, but its not working when I share the application to other normal computers.

    Can someone help me for solution?

  38. @ Gezim,

    On the computer where SQL Server 2005 is installed, check the following,

    1. SQL Server Browser Service should be running. to check this, go to Start-> Run -> type services.msc a page will open, look for SQL Server Browser Service, if service is stopped then start the service.

    2. Check if this issue is because of Windows Firewall, for a minute turn off Windows Firewall and then test you application from other computers, and see if this works. If Windows Firewall is problem, then you have to make an exception.

    3. Check if SQL Server allows local anf remote connection in SQL Server Surface Area Configuration. to check this, do the following,
    Click Start – > All Programs -> Microsoft SQL Server 2005 – > Configuration Tools – > SQL Server Surface Area Configuration- > Click on links below ( SQL Server Surface Area Configurations Services and Connections ), Expand Your Instance Name – > Expand Database Engine -> Click Remote Connections ( On right hand side of the page) click Local and remote connections and click using Both TCP/IP and Named Pipes.

    Last but not the least, this could be a login issue as well, Are you using Windows Authentication or SQL Server Authentication, if you are using Windows Authentication then make sure Windows A/c through which you are trying to login into application, that windows Login has access to SQL Server.

    If not then create that Windows login in SQL Server.

    If these things does not work, Please post your complete error message here.

    Regards,
    IM.

  39. Hi
    iam working on CLR Triggers for Sqlserver2005.my table contains Image field ,when trigger fires it is showing error

    Cannot use text, ntext, or image columns in the ‘inserted’ and ‘deleted’ tables.
    The context transaction which was active before entering user defined routine, trigger or aggregate “MyTriggerName” has been ended inside of it, which is not allowed. Change application logic to enforce strict transaction nesting.

  40. Pingback: SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

  41. Hi Pinal

    I’m Ikadewi as newbie for SQL Server 2005,

    I’d like ask, to solve the problem of “Cannot add diagram to SQL Server 2005 DB: Database diagram support objects cannot be installed because this database does not have a valid owner “.

    I used this guidance from geeknets : to change the “Compatibility Level” to SQL Server 2005 (90) since in my database properties can’t direct change through drop down list so I used your guidance : EXEC sp_dbcmptlevel , 90;
    GO from SQLQuery command pane.

    But then I still have the same problem before I changed the compatibility level.

    Do you have any solutions beside above tips since I need to see the database diagram in order to study the relation table in database.

    Your feedback review will be much appreciated.
    Thank you

    Regards,
    ikadewi

  42. Hi.. I need move a database from ss2000 to ss2005. I detached the database from 2000 and copied to 2005 and attached to 2005.
    The database i ok, but the aplication that use the database give me a error in conversion from varchar to datetime.
    The compatibility level in the database is 80.
    possible problem of the compatibility with ss2005 ?
    thank’s

  43. Hello there, thank you for the information.

    I wonder how could I upgrade the SQL Server 7 to 2000 or 2005, will the databases affected? Please advice. Thank you.

  44. Hi Pinal,

    I am working on changing the compatibility level from 80 to 90. I am trying to see what is going to be affected on changing.

    We use lot of Order by Clause which is going to be affected, Also we use sysobjects which will be affected, but I like to know if there is any tool that you can suggest that will scan the entire db and give me the report.

    Since my DB is already upgraded to SQL 2005 and set to 80, I can’t use any upgrade advisor for SQL 2005. Suppose if I use the Upgrade advisor for SQL 2008 (we don’t intent to upgrade to SQL 2008) will the results will be affected since the results will be based on SQL 2008.

    I appreciate your help. Thanks in Advance

    – Vijay Anand Kannan

  45. good day, i have a problem, I install sql 2005 in my computer but it doesn’t has the opcion compatible level 90 only 80 and old versions.

    how do I can activate this opcion?

    thank’s

  46. Hi I have a SQL 2005 db replicated to another with SQL 2008. I need to change the database compatibility from 2000 to 2005. Are there any knowed issues that may affect my db?

  47. Hi,

    We have upgraded SQL Server 2000 to SQL Server 2005 and everything is working fine. Now we would like to change the compatibility mode to 90.

    Could you advice the steps to perform after changing the compatibility mode to 90?

    Thank you

  48. Hi Pinal,

    I want to set up a merge replication from SQL 2005 to SQL 2000. It is giving the below error. Whether I have to change the compatibility of SQL 2005 to SQL 2000 compatibility.

    All merge publications in a database must have the same compatibility level. Publication ”does not exist. Changed database context to ”. (.Net SqlClient Data Provider)
    Error Number: 21528 Severity: 16 State: 1Procedure: sp_addmergepublication Line Number: 250

  49. my database create on MS-SQL-2000 and attach to MS-SQL-2005, when i run the application on 2000 it works properly but in 2005 it will not work, it give outofmemory.exception.

  50. Hello Pinalji,

    Very nice article. I was trying to integrate “ELMAH” in our web sites. There was some error and at the end I identified that I need to change compatibility level of the database. I searched in the google and luckly, got very first link of you in the google list. It helped me in very needed situation.

    Thanks a lot.

  51. Hello.
    I have a 2k5 server and a database imported from 2K, attached in compatibility level 80.
    My question is the following – is there a physical cost to consider when executing those commands?
    For example, the database contains a lot of XML-type columns. My concern is that the server would perform some resource-intensive operations on the database (akin to performing a collation change) that would take the important database out of operation for a longer time.
    Or is the operation entirely technical and always performed in a matter of seconds?

  52. Hi,

    I changed my SQL 2005 Database dbcmptlevel to 90 using following command

    sp_dbcmptlevel WMS001,90

    Then I received following error message while compailing a Procedure Using WITH clouse

    Msg 319, Level 15, State 1, Procedure spSelectCustomerNew, Line 29
    Incorrect syntax near the keyword ‘with’. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

    Why it is Happening…or what is the actual reason for this and what is the solution.?

    • Hi,

      Please Find the following Proc

      CREATE PROCEDURE [dbo].[spSelectCustomer]
      @Id decimal(18, 0),
      @Name varchar(200),
      @Status varchar(50),
      @Index int,
      @Phone varchar(50),
      @StateName varchar(200),
      @ZipCode varchar(200),
      @Count int OUTPUT

      AS

      SET NOCOUNT ON
      DECLARE @StartIndex int
      Declare @EndIndex int
      Declare @RowCount int
      Declare @RowCountSeed int
      set @RowCountSeed=(select isnull(SearchCount,0) from SystemSettings)

      table_loop:

      SET @StartIndex=(@Index*@RowCountSeed)+1
      SET @EndIndex=(@Index*@RowCountSeed)+@RowCountSeed

      WITH [Customer ORDERED BY ROWID] AS
      (SELECT ROW_NUMBER() OVER (order by C.[CompanyName]) AS ROWID, * FROM [Customer] as C
      left join State as S on C.State=S.S_ID
      WHERE (C.Id=@Id or @Id=0)
      AND (C.[Phone] like dbo.fnGetSearchString(@Phone) or isnull(@Phone,”)=”)
      AND (C.[CompanyName] like dbo.fnGetSearchString(@Name) or isnull(@Name,”)=”)
      AND (C.[Zip] like dbo.fnGetSearchString(@ZipCode) or isnull(@ZipCode,”)=”)
      AND (S.[S_Name] like dbo.fnGetSearchString(@StateName) or isnull(@StateName,”)=”)
      and C.IsDeleted = 0 and (Status=@Status or isnull(@Status,”)=”))

      SELECT
      C.[Id],
      isnull(C.[CompanyName],”) as [Name],
      isnull(C.[AccountNo],”) as [Account],
      isnull(C.[Phone],”) as [Phone],
      isnull(S.[S_Name],”) as [State],
      isnull(C.[Zip],”) as [ZipCode],
      isnull(C.[City],”) as [City]

      FROM [Customer ORDERED BY ROWID] as C
      left join State as S on C.State=S.S_ID
      WHERE ROWID between @StartIndex and @EndIndex

      set @RowCount=@@rowcount

      set @Count=(select count(*) from [Customer] as C
      left join State as S on C.State=S.S_ID
      where (id=@Id or @Id=0) AND ([Phone] like dbo.fnGetSearchString(@Phone) or isnull(@Phone,”)=”)
      AND ([CompanyName] like dbo.fnGetSearchString(@Name) or isnull(@Name,”)=”)
      AND (C.[Zip] like dbo.fnGetSearchString(@ZipCode) or isnull(@ZipCode,”)=”)
      AND (S.[S_Name] like dbo.fnGetSearchString(@StateName) or isnull(@StateName,”)=”)
      and IsDeleted = 0 and (Status=@Status or isnull(@Status,”)=”)
      )

      if @RowCount=0 and @Count > 0
      begin
      set @Index=@Index-1
      GOTO table_loop
      end
      select @Count as RCount

    • When I put a ( ; ) between the following line it will working fine

      SET @EndIndex=(@Index*@RowCountSeed)+@RowCountSeed

      ; — see this

      WITH [Customer ORDERED BY ROWID] AS
      (SELECT ROW_NUMBER() OVER (order by C.[CompanyName]) AS ROWID, *

  53. When I put a ( ; ) between the following line it will working fine

    SET @EndIndex=(@Index*@RowCountSeed)+@RowCountSeed

    ; — see this

    WITH [Customer ORDERED BY ROWID] AS
    (SELECT ROW_NUMBER() OVER (order by C.[CompanyName]) AS ROWID, *

  54. Hi,

    I am having a application where the front end is in MS-Access and using SQL 6.5 as the db.
    Now that i want to upgrade the database from 6.5 to 2008. so just wanted to know, like is there any pros and cons about migrating the db and the application in 2008.

    Thanks & Regards

    Anup Kumar

  55. Hi Anup,

    You will have to complete the process in two steps. First upgrade to SQL 2000 and then to 2008.
    You can use SQL Server’s utilities like upgrade advisor and upgrade wizard to perform this task.

    Regards,
    Pinal Dave

    • Thanks Pinal for the response… it helped me a lot.

      again related to the above migration process, one more small query:

      is there any major/minor changes in DB that has to be done after migration?

      in many of the web site that i have visited, says there might be some compatibility issue while migration of the DB. (i mean to say Stored procedure, views, constraints, etc..).

      Thanks & Regards

      Anup Kumar

      • Hello Anup,

        Before upgrading check the possible issue using upgrade advisor tool provided by Microsoft.
        There are different issue for different versions and you must take care those issues in advance for successful migration.

        Regards,
        Pinal dave

  56. Regarding Best Practice for resetting compatibility mode to previous version. Can you tell us why the db needs to be in single user mode? Is it to prevent data corruption, make the action execute faster or atll, or some other reason?

    Thanks you!

  57. use venkat;
    ALTER DATABASE venkat SET SINGLE_USER
    exec sp_dbcmptlevel venkat,90;
    if i run above query ,it will show the following error..

    Msg 15416, Level 16, State 1, Procedure sp_dbcmptlevel, Line 92
    Usage: sp_dbcmptlevel [dbname [, compatibilitylevel]]

    Valid values of database compatibility level are 60, 65, 70, or 80.

    How do change the compatibility level of that Database?

  58. Hi Pinal,

    Can we make a SQL Server 2008 database compatible to 2005 serber by settings the compatibility level ? I tried generating the script from 2008 server with schema and data and then tried to retore it on a 2005 server, but was not fully successful.

    If there is any other solutions, plz let me know.

    Regards,
    Anoop

  59. i have a db on sql server 2005 express and this db work on a LAN workgroup … some sql statments take a very long time to return the required data …… how can i reduce this time?

    hints:
    1. the database server ia a dell pc (not server) OS windows XP with 3G ram.

    2. the sql statments takes a very long time when i use a special table in my sql statment, this table contains about 1,500,000 rows and data size about 500 MB.

    waiting ur help
    Thanks
    Amir M. Guirguis

  60. Amir..what does your quesiton have to do with this blog post? Nothing. you should seek help in the right place. This isn’t it the right place. Ask taht at sqlserver central or on an msdn site.

  61. I upgraded my database from 2000 to 2005 and now wen i try to restore it from a backup to 2008 it gives me an error saying the cast is invalid.

    I tried chenging the compatibility but I do not have an option to set compatibility to 90.

  62. Hi,

    I was doing some research and wanted to know if it’s possible to run the sql update advisor on SQL 2008. I’d like to see what the effects would be for changing the compatibility from 80 to 100? Anyone tried this? Or is the SSUA meant for checking only sql 2000 or sql 2005 ?

    Thanks

  63. Hi Pinal,

    We are facing some issues after restoring the SQL 2000 database into SQL 2005.. Some procedures are not functioning properly, that means we are getting a custom error pages when we are accessing certain site pages.. Is it a known issue? Is there any fix for it? Can I fix it by Changing database compatible level to 80 ? Please advise me as soon as possible..

    Thank you, Pinal

    Kind Regards,
    Sujith

  64. Hi Madhivanan,

    Sorry for the delay, one of the error code is given below


    where UserId= CAST (@UserId as UniqueIdentifier(16))

    Please have a check and advise me a fix,

    Thank you,

    Sujith

  65. Forgot to tell you one thing, I have Changed the database compatible level to 80, but still getting the error page..

    Thanks and regards,
    Sujith

  66. Are there any variables I can look at like DB size etc to estimate how long I can expect this query to execute? I am changing from 80 to 90, and it has now been executing for almost 10 minutes

    ALTER DATABASE mydb SET SINGLE_USER

    EXEC sp_dbcmptlevel mydb, 90
    GO

    ALTER DATABASE mydb SET MULTI_USER

  67. i upgraded sql server 2000 dev ed, to 2005 dev ed with default instance. after upgrade when iam changing
    compatibility of northwind database it showing below error. please can u tell the solution for this issue

    Msg 15416, Level 16, State 1, Procedure sp_dbcmptlevel, Line 92
    Usage: sp_dbcmptlevel [dbname [, compatibilitylevel]]

    Valid values of database compatibility level are 60, 65, 70, or 80.

    Regards,
    Kranthi

  68. I am using SQL Server 6.5. I want to migrate to SQL Server 2008. What are the best options available. Can I make scripts of Tables and Store Procedures and execute it in SQL Server 2008.

  69. Hi, I have a question about 2005 compability level in SQL 2008. We have an application, Access – SQL, that work perfectly on both SQL 2005 and SQL 2008 but NOT on SQL 2008 in 2005 compability mode. Do you now why?

  70. Hi pinal,
    Can you tell meaning of each number sections of version in 9.0.1399.06 of sql server 2005 RTM as i only know that 9.0 stands for sql server 2005. so please tell me what 1399.06 stands for …?

    Regards,
    Vikas.

  71. I was trying to run TRY..CATCH block in SQL Server 2005 but it was giving me error. When I checked my version using @@VERSION it is showing “Microsoft SQL Server 2000 – 8.00.194″. But when I clicked on Help-> About-> it is showing 9.00.1399.00..Why So?? Am I using SQL Server 2000 database. If yes then how to change it to 2005 to use EXCEPTION HANDLING feature???

  72. @vikas sahu

    Those number tells you about your edition and service pack details. Run below command and you’ll come to know about it-

    SELECT SERVERPROPERTY(‘productversion’), SERVERPROPERTY (‘productlevel’), SERVERPROPERTY (‘edition’)

    For more details, please visit-

    http://support.microsoft.com/kb/321185

  73. Hi,

    I wonder if someone would be able to explain why the following 2005 code can run on DB with compatibility mode set to 80 (so 2000) on a SQL2005 server? Since xml is 2005 and doesn’t exist neither this code runs on SQL2000.

    declare @test xml
    set @test = ”

    Is this a “normal” behaviour SQL2005 cpat 80 mode or a feature?

    Is there any trick to really set a DB to full compatibility mode 80 (2000) instead of a mixed 2000-2005 mode? All this to make sure devs don’t write 2005 code against a 2000 DB which can be ported into a SQL2000 server. Thanks

    Regards,
    Miguel

  74. Please help me ….
    i am working on JOBBOSS software, with the backend of sql server 2005. where should i get the detailed information which is stored in the database for editing or modification…

  75. hi Pinal,
    Actually am shifting from sql 7.0 to sql server 2008. but now the problem arises when the formwe DB is restored in latter one.. i tried the sp_dbcmptlevel but in vain.. as 7.0 doed not support 80,90,or 100.. and i have also tried to first restore it to 2005 and executed the cmd and again restore to 2008 but this also not worked/…!!

    CAn u plz help by any possible solutionss…

    txs in advance… :)

  76. Hi Pinal,

    Can you please advise me to migrate MS SQL 2000 to another MS SQL 2000 server.. There is some hardware issues, actually we need to move SQL server to another server,, It is having around 95 DBs, so can you please tell me the best way to migrate the server?

    Please excuse me if I am posting this on the wrong area..

    Thanks you,
    Sujith

  77. Okay so this is funny.

    We have a procedure that uses MERGE statement and works fine in 2008.
    One of our clients uses 2005, and it was failing for them.

    On our current 2008 Servers, we set the compatibility level to 90 and even 80 later, (in hope of trapping the error because of the merge statement) but the merge procedure compiles and executed fine in each of the cases.

    I double checked the compatibility level is sys.databases, and opened up a brand new connection, set SIngle user prior to changing the compatibility level etc.

    I am stumped..

    What’s going on here ?
    Any ideas, insights ?

    Manu

  78. Hi Pinal, I took backup of a database on SQL 2008 R2 server after changing it’s compatibility level back to 90.

    Now I want to restore that DB on a SQL 2005 server, is it possible? I tried and it’s giving me error.

    Thanks,
    Vikas Verma

  79. I would like to automate (via T-SQL) a backup and restore between a SQL Server 2008 (Production server) and SQL Server 2008 R2 (Report server). I know how to do it via the graphical interface, however I have hard time to figure out via T-SQL

    Thanks
    Martin

  80. ‘JONALD
    ‘when i want to update data,,..the error is “Fialed to Update”

    Private Sub btnok_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnok.Click

    Connection_open()

    cmd = New SqlCommand(” UPDATE TBLNAME SET FirstName = ‘” & txtfirstname.Text & “‘, MiddleName = ‘” & txtmiddlename.Text & “‘, LastName = ‘” & txtlastname.Text & “‘, Program = ‘” & maskedprogram.Text & “‘ where StudentID = ‘” & txtstudentID.Text & “‘ “, conn)

    j = cmd.ExecuteNonQuery

    If j > 0 Then

    MsgBox(“Update success!”)

    Else
    MsgBox(“Failed to update!”)

    End If
    connection_close()

    txtstudentID.Clear()
    txtfirstname.Clear()
    txtmiddlename.Clear()
    txtlastname.Clear()
    maskedprogram.Clear()
    txtstudentID.Focus()

    End Sub

  81. Hi, I am a business analyst working on SQL2000 to SQL2008R2 upgrade. We used Upgrade Advisor to identify the issues which require the code level changes. But how can i make sure if this were all the issues ?

    Is there anything which an Upgrade Advisor cannot catch ?

    Please help

  82. Hi i have a similar problem. I an using sql server 2008. i make upgard scripts for a 2005ver. db. when i execute scripts like Declare @var int =0, they are executed on my environment but on client environment which has sql server 2005 installed, it throws error. i know how to correct this. but there any way to make settings of sql server 2008 make us know whether the script will run or not on sql server 2008.

  83. I changed my server to SQL 2005 from 2000 using EXEC sp_dbcmptlevel TQM, 90;

    It allowed me to use the PIVOT command but caused a strange problem.

    On some old stored procecures, a previous developer had written a query like so:

    SELECT
    s.firstname,
    c.carreg,
    .s.surname /*notice .s.surname*/

    FROM
    staff s
    INNER JOIN
    car c ON c.StaffID = s.StaffID

    I normally wouldnt write .s.surname because the table staff was aliased to s not .s

    However thats how the code was written.

    When i changed the compatibility level to 2005, the stored procedure didnt work anymore and when i changed it back to 2000 – it did…just thought it was worth mentioning.

    So i removed the extra .’s from all stored procedures and its all fine but a strange bug…

  84. Hi i have a similar problem. I am using sql server 2008. i make upgarde scripts for a 2005ver. db. when i execute scripts like Declare @var int =0; they are executed on my environment(sql server 2008) but on client environment which has sql server 2005 installed, it throws error. i know how to correct this. but there any way to make settings of sql server 2008 make us know whether the script will run or not on sql server 2005.

  85. –new syntax
    ALTER DATABASE test2008
    SET COMPATIBILITY_LEVEL = 100

    — Valid values of the database compatibility level are 90 (for 2005), 100 (for 2008), or 110 (for 2012).

    • Hi Gleb

      Its not the Database compatiblity i need, Its the SQL SERVER Management studio compatiblity . when i run a script like
      DECLARE @var nvarchar(max) = ‘test data’

      On a DATABASE any compatiblity level , It does not give error if db is in sql studio 2008.
      but it gives error if the DB is on sql server management studio 2005.

      so i need a setting for my query window in sql server management studio 2008 to behave like sql server management studio 2005.

      Thanks
      Vipul

  86. Hi Gleb
    Its not the Database compatiblity i need, Its the SQL SERVER Management studio compatiblity . when i run a script like
    DECLARE @var nvarchar(max) = ‘test data’

    On a DATABASE any compatiblity level , It does not give error if db is in sql studio 2008.
    but it gives error if the DB is on sql server management studio 2005.

    so i need a setting for my query window in sql server management studio 2008 to behave like sql server management studio 2005.

    Thanks
    Vipul

  87. Pingback: SQL SERVER – Weekly Series – Memory Lane – #031 | Journey to SQL Authority with Pinal Dave

  88. hello sir i developed a dotnet software with sql server 2005 as database,i want to install setup on the system which is not having sql server installed on that system.. . i am not able to do any database related operation with the setup because database is not there .. what is the solution for that? Please help me …
    [email removed]
    thank you sir

  89. Hi Sir I want to restore database of sql server 2008 to sql server 2005 with data created within it. How is it possible Please tell me…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s