none
Why not to test Sql Server Version inside instawdb.sql

    Question

  • Hello,

    I have installed AdventureWorks2008 (OLTP) since a week on a fresh Sql Server Express 2008
    As i am curious , i have looked on the file instawdb.sql

    in the header ( in comment )

    File: instawdb.sql

    Summary: Creates the AdventureWorks 2008 OLTP sample database.

    Date: June 14, 2008

    Updated: October 17, 2008

    SQL Server Version: 10.00.1600.22

    I have noticed that the script has been created on a Sql Server 2008

    But i have seen some tests about the existence of the environment variables ( necessary for the Bulk Insert for example ), but none for the test of the Sql Server Version.
    As it is possible to use this scripts ( with the .csv files to load data ) outside a "normal install" ( for example the database has been suppressed ), i think that it would be a good think to have a test about the version ( which is displayed ) and of course associated with a RAISEERROR if version < 10 ( preceeding KATMAï ) before the execution of the CREATE DATABASE and of course the call to sp_reconfigure to enable the FILESTREAM.

    If this script is executed versus Sql Server 2005, it will fail because of the presence of FILESTREAM which is a new feature which appeared with Sql Server 2008

    Another small question : why in the part "Type creation", the data type is not defined as [schema].[datatype] like in the script which can be created with Sql Server Management Studio or with a program using SMO (i know that the default schema is dbo but in fact, i am disturbed by the fact that for the creation of the fonction ufnLeadingZeros the part [dbo]. is appearing ) ?

    The last question : why the whole creation of the extended properties of all objects is found at the end of the script ( for myself, i prefer the "build" from SSMS with the call of sp_addextendedproperty just after the creation of the object : database,table,function... ) ?

    These questions must not be understood as cristicisms ( it won't nice from for one of the first threads of this new forum ), but only asks of explanations on the best way to build scripts creating and loading databases.

    Have a nice day


    Please remember to click 'Mark as Answer' on the post that helped you. Unmark if it provides no help
    Saturday, January 31, 2009 1:28 PM

Answers

  • Howdy, Papy. Those are great suggestions and observations. All of the work items that we're tracking for AdventureWorks can be found on CodePlex: http://www.codeplex.com/MSFTDBProdSamples/WorkItem/AdvancedList.aspx File anything you think we should change.

    1. Testing the server version didn't seem like a high priority to us, since installing via script in SSMS is NOT the primary mode that we expect users to follow. (We test for the other dependencies because they're required.) If users install the SQL Server 2008 sample databases and try to create them on 2005... Well, file a bug on CodePlex and we'll triage it!
    2. Interesting observations on the declarations. AdventureWorks was created and is maintained by hand... not generated by a scripting widget. Bug those on CodePlex so that we can triage them for Samples Refresh 2 (SR2) and AdventureWorksKilimanjaro.
    3. The extended properties are there for documentation and tool support.

    Thanks!!


    David Reed
    • Marked as answer by Papy Normand Wednesday, February 04, 2009 8:41 PM
    Monday, February 02, 2009 4:18 PM

All replies

  • Howdy, Papy. Those are great suggestions and observations. All of the work items that we're tracking for AdventureWorks can be found on CodePlex: http://www.codeplex.com/MSFTDBProdSamples/WorkItem/AdvancedList.aspx File anything you think we should change.

    1. Testing the server version didn't seem like a high priority to us, since installing via script in SSMS is NOT the primary mode that we expect users to follow. (We test for the other dependencies because they're required.) If users install the SQL Server 2008 sample databases and try to create them on 2005... Well, file a bug on CodePlex and we'll triage it!
    2. Interesting observations on the declarations. AdventureWorks was created and is maintained by hand... not generated by a scripting widget. Bug those on CodePlex so that we can triage them for Samples Refresh 2 (SR2) and AdventureWorksKilimanjaro.
    3. The extended properties are there for documentation and tool support.

    Thanks!!


    David Reed
    • Marked as answer by Papy Normand Wednesday, February 04, 2009 8:41 PM
    Monday, February 02, 2009 4:18 PM
  • Hello David,

    Thank you very much for your answer
    Some unpleasant comments about your reply:

    1. "if  users install the SQL Server 2008 sample databases and try to create them on 2005... Well, file a bug on CodePlex and we'll triage it!"
    I has discovered that to get the value 10 ( or 100 ) for the major of the version in T-SQL is not evident ( even on  string ).
    FILESTREAM on Sql Server 2005 will bug . Your answer is logical...

    2. I will post a bug report on Codeplex as quickly as possible.I think i have discovered another bug related to Filestream ( but maybe it is because i have problems with SMO and Filestream is very new for me )
    Anyway, your script is well documented and clear, so i am "shelling" it.And when i am not understanding, i am returning towards SMO to explore the database

    3. For the extended properties, it was only a suggestion

    Last thing : i suppose that your team has created the script from the script for Sql Server 2005 and modified it to include most features new for 2008. Not an evident work...( in 4 days, i have explored only 20% of the script )

    My remarks will be never to criticize, only to better your databases.

    Have a nice day


    Please remember to click 'Mark as Answer' on the post that helped you. Unmark if it provides no help
    Wednesday, February 04, 2009 8:41 PM
  • No problemo, Papy. The AdventureWorks2008 family of databases began life as the AdventureWorks databases, but the data model (especially with regards to normalization in the OLTP database) was changed significantly to support features of Entity Framework. Unfortunately, Entity Framework doesn't support a large number of SQL Server 2008 features (the new data types in particular) and the multiple inheritance sample for AdventureWorks2008 and others are on-the-shelf until such time as EF supports the full feature set.
    David Reed
    Wednesday, February 04, 2009 10:19 PM