locked
Create Script form Table reports error RRS feed

  • Question

  • I have created a table with following statement:

    SELECT [Id] = 1
    , [NewDate] = CAST( DATEFROMPARTS(1900,1,1) AS Date )
    INTO __Test
    
    SELECT * FROM __Test
    

    The select statement returns one record.

    I can open the table in the designer, but if i try to to do a "Script Table as" --> "Create to" --> "New window", SSMS prompts with the message:

    Column NewDate in object __Test contains type Date, which is not supported in the target server version, SQL Server 2005.

    So i tested then compatibility_level:

    SELECT compatibility_level  FROM sys.databases WHERE name = DB_NAME();

    This statement returns 110.

    What else can i do to create the script?


    Kind regards, Henk


    • Edited by HeSitated Tuesday, August 20, 2013 10:15 AM typing error
    Tuesday, August 20, 2013 10:04 AM

Answers

  • You can find this setting in SSMS -> Tools -> Options -> SQL Server Object Explorer -> Scripting -> Script for Server Version

    - Vishal

    SqlAndMe.com

    • Marked as answer by HeSitated Thursday, August 22, 2013 6:55 PM
    Tuesday, August 20, 2013 10:24 AM

All replies

  • You can find this setting in SSMS -> Tools -> Options -> SQL Server Object Explorer -> Scripting -> Script for Server Version

    - Vishal

    SqlAndMe.com

    • Marked as answer by HeSitated Thursday, August 22, 2013 6:55 PM
    Tuesday, August 20, 2013 10:24 AM
  • SSMS prompts with the message:

    Column NewDate in object __Test contains type Date, which is not supported in the target server version, SQL Server 2005.

    Hello Henk,

    And which version of SSMS are you using, is it 2008 or higher? Or is it still 2005?

    Because SSMS prompts the message, not the database engine while executing the statement.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, August 20, 2013 11:02 AM
  • Hi Olaf,

    And which version of SSMS are you using, is it 2008 or higher? Or is it still 2005?

    Because SSMS prompts the message, not the database engine while executing the statement.

    Although SQL Server 2005 is installed on this machine, i'm using Microsoft SQL Server Management Studio 2012 (version: 11.0.2100.60)


    Kind regards, Henk

    Tuesday, August 20, 2013 11:18 AM
  • Hello,

    It seems that you connect to SQL Server 2005 instance from SSMS 2012. Did you try the suggestion as Vishal post above: SSMS >Tools > Options > SQL Server Object Explorer > Scripting for server 2005 option?
    You also try to change  the compatibility level
    ALTER DATABASE DB-name
    SET COMPATIBILITY_LEVEL = 90
    GO

    Regards,
    Fanny Liu


    Fanny Liu
    TechNet Community Support

    Wednesday, August 21, 2013 9:10 AM
  • You can find this setting in SSMS -> Tools -> Options -> SQL Server Object Explorer -> Scripting -> Script for Server Version

    - Vishal

    SqlAndMe.com

    Hi Vishal,

    I saw your post yesterday evening for the firsttime....

    Your reply was the right answer, thanks!


    Kind regards, Henk

    Thursday, August 22, 2013 6:57 PM