Post reply

SQL Statement for Compatibility Check

  • March 18, 2011 at 11:26 am

    #247118

    Hello,

    I want to first apologize for my lack of research and code presented in this post. I recently was faced with a problem in a Java application when executing a report. The application presented an error stating, "Ambiguous column name Identification". I quickly found the line of Java code that was causing the issue. I failed to properly alias the Identification column in my query. Made a quick fix and all was good, locally that is. Another engineer tested the same report (without my fix) and found the report to work just fine. I immediately thought of compatibility mode in SQL Server. I had my development database set to mode of 80 in SQL 2K8 and the QA testers/other Engineer were in mode 100 on SQL 2K8. That's why the code change was approved by them... Apparently the statement was not compatible in SQL 2000...

    The bottom line:

    I have decided that my team needs to confirm all SQL statements against all modes (80,90,100) before putting the code into the application. Does anyone know a way to run a script against a database, let's say...

    SELECT IDENTIFICATION, CREATE_DATE, CREATE_USER FROM CUSTOMER

    and have the script return data in the results as well as letting us know that the script is good for mode 80,90, & 100???

  • March 18, 2011 at 11:42 am

    #1300703

    You can check the level of the current DB using this:

    SELECT compatibility_level

    FROM sys.databases

    WHERE database_id = DB_ID()

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • March 18, 2011 at 11:45 am

    #1300706

    and to change compatibility in SSMS so you can test a specific statement:

    ALTER DATABASE [SandBox] SET COMPATIBILITY_LEVEL = 80

    GO

    SELECT IDENTIFICATION, CREATE_DATE, CREATE_USER FROM CUSTOMER

    GO

    ALTER DATABASE [SandBox] SET COMPATIBILITY_LEVEL = 90

    GO

    SELECT IDENTIFICATION, CREATE_DATE, CREATE_USER FROM CUSTOMER

    GO

    ALTER DATABASE [SandBox] SET COMPATIBILITY_LEVEL = 100

    GO

    SELECT IDENTIFICATION, CREATE_DATE, CREATE_USER FROM CUSTOMER

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • March 18, 2011 at 12:07 pm

    #1300718

    Thank you very much. This will do it...

Viewing 4 posts - 1 through 3 (of 3 total)

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