I am receiving the following question a lot: How do I propagate MSBuild properties to SQLCMD variables. In order to make the Visual Studio 2008 Team System Database Edition GDR release use the data from an MSBuild property inside a SQLCMD variable you need to do three things:

1. Add the variable to the Database.sqlcmdvars file
2. Add a XML fragment to the database project (.dbproj) file
3. Use the SQLCMD variable

First you need to add a SQLCMD variable to the Database.sqlcmdvars file, in this example we will add the $(ProjectDirectory) variable, which we want to use the reflect the MSBuild $(MSBuildProjectDirectory) property. Set the value to something that you can recognize as not being set, you cannot leave it empty, so I will use “notset”.

Now that we have set the variable, we need to add some information to the database project file to map the value from the MSBuild property to the SQLCMD variable we just defined.

In order to achieve this we need to add an ItemGroup to the project file. There is no UI support for doing this, so you manually need to edit the database project file. There are multiple ways of doing this, I will describe one, which assumes you have the project loaded inside Visual Studio as a starting point.

1. Right click on the database project node inside the Visual Studio Solution Explorer, this will show the following menu, where you choose the “Unload Project” option
   
2. Now the project is unloaded and the Solution Explorer windows only shows the project node as “(unavailable)”.
   
3. Now right click on the project node again and choose the “Edit <project name>.dbproj” options
   
4. Go to the end of the project file and add the following ItemGroup before the end of the </Project> element
   

      1:    <ItemGroup>

      2:      <SqlCommandVariableOverride Include="ProjectDirectory=$(MSBuildProjectDirectory)" />

      3:    </ItemGroup>

5. The ItemGroup defines an element of type SqlCommandVariableOverride and contains an attribute which is a string that has on the left hand side of the equation the SQLCMD variable name and on the right hand side the MSBuild property name. Now you have mapped the MSBuild property to the SQLCMD variable
   
6. Right click on the database node inside Solution Explorer again, now choose the “Reload Project” option. This will close the project file inside the XML editor and open the project again. If you did not close the editor, Visual Studio will tell you this action will close the project file inside the editor.
    
7. Now we can use the variable, and to test we first add the following statement to the Script.PreDeplomenent.sql file.
   

      1:  print N'SQLCMD ProjectDirectory = $(ProjectDirectory)'

8. To test it we need to deploy the project, to the database, not just to script, otherwise the pre-deployment script will not get executed. The result should like something like this inside the Output window:
   

Now that we know the variable assignment works, we can use the variable in other more interesting places.

In the next post I will cover how to make the variable assignment conditional.

GertD @ www.DBProj.com

Based on a customer request I created a .dbschema file which contains the definitions of all the extended stored procedures used by SQL LiteSpeed 5.0 that live inside the master database. Since schema import does not import the definition of extended stored procedures, the user could no resolve the reference to the extended stored procedures.

The following 39 extended stored procedures are defined inside the dbschema file:

You can download the dbschema files from the following SkyDrive location:
http://cid-526eb9977609d51e.skydrive.live.com/self.aspx/DBProj.com/DBSchema/SQLLiteSpeed/SQLLiteSpeed.dbschema.zip

There are 3 dbschema files, one for each version:

• SQLLiteSpeed-2000.dbschema
• SQLLiteSpeed-2005.dbschema
• SQLLiteSpeed-2008.dbschema

I hope this helps others who need to reference SQL LiteSpeed procedures.

GertD @ www.DBProj.com

Today a user send me a question how to resolve reference of system objects inside tempdb?

My first thought was this should get resolved by loading the master.dbschema file. However this did not resolve the issue, so time to ask for an example, which slightly altered looks something like this:

   1:  CREATE PROC [dbo].[testproc]

   2:  AS

   3:      SET NOCOUNT ON

   4:      -- code simplified for this example    

   5:      SELECT [object_id],

   6:                   [name]

   7:      FROM   [tempdb].[sys].[objects] 

When you add the stored procedure above to a project you will get the following 3 warnings:

When you add a reference to the master.dbschema file, found in:
%ProgramFiles%\Microsoft Visual Studio 9.0\VSTSDB\Extensions\SqlServer\<200?>\DBSchemas\master.dbschema

You will get the following 3 warnings, can you spot the difference or improvements?

Very good, there are no differences, this did not resolve anything.

The reason being that you are referencing a 3-part name, where the database name is not master. So the only way to resolve this issue is to change the database reference literal from master to tempdb. When you are adding the database reference to the master.dbschema file, it defaults to use "master” as the literal database variable.

You need to change the database literal variable from master in to tempdb. And then it will resolve the 3-part names to tempdb.sys.objects in this example.

After making this change the warnings are gone, as you can see in the error list.

You can always validate you database reference settings by double clicking on the reference inside Solution Explorer.

If you have to resort to using this trick, I advice you to copy the master.dbschema to tempdb.dbschema and optionally purge the objects in the dbschema that you do not need in order to speed up the loading of you project.

GertD @ www.DBProj.com

Define What You Want, Not How You Get There!

If Not Exists…

Database development is many aspects behind in comparison to regular application development. If you look how database development is integrated with regular application development or development processes it becomes even more obvious that there is room for improvement in this area. When asked, you will find that many database developers are spending their valuable time writing, maintaining, and testing database deployment scripts. The practices may vary, some write them by hand, some use schema comparison tools to generate the script and modify that, and some went all the way to create their own tools to generate these kinds of deployment scripts.

However, the outcome of these efforts is all the same, a set of SQL statements captured inside .SQL script files or embedded as SQL statements inside some application or installer program like WiX. In general they all look like this:

   1:  --

   2:  -- [dbo].[DataLoading]

   3:  --

   4:  IF (OBJECT_ID('[dbo].[DataLoading]', 'U') IS NULL)

   5:  BEGIN

   6:  CREATE TABLE [dbo].[DataLoading]

   7:  (

   8:      [TableName]    SYSNAME NOT NULL,

   9:      [MultiValue]    TINYINT NOT NULL,

  10:      [RowCount]    BIGINT    NOT NULL,

  11:      PRIMARY KEY CLUSTERED

  12:      (

  13:          [TableName], 

  14:          [MultiValue]

  15:      )

  16:  )

  17:  END

  18:  GO

Fig 1: A typical fragment of a manually written deployment script

Most scripts are significantly more complicated, especially when you start dealing with incremental version updates. If you ever wrote or maintained one of these scripts you know this is when the problems with this approach become clear. For the simple cases it is all straightforward—check if the object exists, if not create it—this is even true when adding constraints, etc., since the object is easily identifiable. How about adding a column, changing its data type, and renaming a column name or changing the column order inside an index without changing the index name? As soon as you have multiple changes on a single aspect of an object, ordering and the ability to detect which change needs to be made gets very complicated.

Some database developers therefore stamp each and every object, using an extend property or by maintaining a version table, with versioning information about the object. This works great, as long as you are the only one and/or only tool that makes the changes to the schema, since SQL Server can and will not enforce the versioning information.

The fact that SQL Server does not maintain and expose enough useful information when it comes to versioning of the database schema, nor can it make any guarantees that the schema has been changed or tempered with, only compounds the problem.

The next problem with this approach is that the execution time of the deployment will grow over time. What most database developers will do is maintain two scripts, a new deployment and an incremental deployment, in the same fashion as described above. When time progresses you are accumulating lots of changes, which all have to be executed in the same time order to guarantee the right results. This might also lead to having to touch or rebuild objects multiple times, because dependencies between objects cannot be honored between change scripts across time periods. In other words change script v4 to v5 needs to be independent of v7 to v8 and therefore cannot collapse operations together for efficiency sake.

This brings me to the last obstacle for this approach; your script can only reflect your knowledge of the system as you understand at the time you are writing the script. What do I mean by that? Say you are distributing an application; the user has a need for some additional reporting and adds VIEWs to the system inside their own schema, providing abstraction and isolation from your schema. Great practice! They are very happy and add an index over the view, which makes the VIEW schema bound. Now it is time for you to update your schema and you happen to have a need to update one of the tables that is covered by a schema-bound view from the user. You do not know about this view, so the reality is that most deployments will fail at this time.

The Alternative Approach

So what is the alternative you might ask? If you analyze the problems described above there are a couple of conclusions you can draw:

• SQL Server does not provide any guarantees about the schema version deployed.
• You cannot rely on your knowledge about the state of the system; the system might have changed underneath you, or might be on a different version or is in some weird state.
• The number of permutations to validate inside your deployment script is therefore endless and you cannot compensate for all possible cases.

The root cause, however, is the fact that you are encoding state transitions inside your deployment script that are based on a state of the system at time T. When you are deploying this at time T+5 the world might look and be in fact different and all you could do is “fail” the deployment since the outcome of your deployment is now in doubt.

Now what if you describe how you want your schema to look, instead of encoding inside a script how to implement it? This is what declarative database development is about. You define what you want, not how you get there or physically implement the database.

Let’s dive into how the Visual Studio 2008 Team System Database Edition GDR product leverages declarative database development to produce a model-driven deployment of database schemas.

Let There Be a Model

The first thing you need is a model representation of the database schema. The model describes all the objects inside your database schema; this is the “what you want” part. In order to create and fill the model, the system needs a description of the objects. Normally this is provided through a DSL (Domain Specific Language); VSDB uses the regular T-SQL DDL language to define the shape of schema objects, so there is no need to learn a new language or representation. So the model is fed a collection of DDL statements, which are living inside .SQL files, held together by a project file (.DBPROJ), which functions as the container for the source code files.

Fig 2: Database projects are containers of SQL files, which are compiled in to the schema model

The fact that the model is built based on a source-code representation of the database is a huge differentiator. This enables a true offline development experience and better integration with source code control systems and with aspects like build servers, etc. It also means that your model is allowed to be in an inconsistent or incomplete state; you can break dependencies inside the schema and the compiler will immediately flag those.

Fig 3: The Visual Studio error list indicates that your model is in an inconsistent state.

The underlying engine inside VSDB will perform the following actions on top of the source code when building the model:

• Parse the source code, this will enforce the syntactic validity and transform the source code into a programmatically accessible representation, which is used to store the object inside the model.
• Interpretation, will wire up all the object relationships.
• Validation rules, this is the stage where semantic validation is performed, for example an object that has an instead of trigger is not allowed to be part of a transactional replication publication. Two in themselves valid and correct DDL statements, but together mutually exclusive.
• Optionally you can enforce more rules over the model through the use of T-SQL Static Code Analysis.

When the model is in a valid and consistent state, you can now perform a build. This will perform all the steps described above, excluding running T-SQL Static Code Analysis since this is an optional step. When no errors are detected, it will persist the resulting model to disk, as a .DBSCHEMA file. The .DBSCHEMA file is effectively an XML serialized format of the database model, which functions as your database schema “blueprint,” this is the manifest that describes what you want inside the database.

How Do I Deploy this Puppy?

Now that you have a model representation, the question is how does it help solve the deployment problem? The easiest way to explain this is to describe the deployment process using the following diagram.

Fig 4: The VSDB deployment engine in action

1. The first step is to load the source model, if this is provided in the form of a .DBSCHEMA file this is simply deserializing the model.
2. The second step is to produce a model representation of the target database. This is done by importing the schema the same way you created the initial project representation, except it does not persist the resulting source code representation in this case.
3. Now that there are two models, compare the two models, taking into account the deployment settings and additional information like the .REFACTORINGLOG file. The deployment settings determine the rules and settings used to compare and to generate the deployment script. The rafactoringlog file is providing additional information to deploy changes that cannot be determined using a difference-based deployment engine. One of the key examples is a rename column operation, which cannot be distinguished from a drop-and-add column of the same type with a different name; however, since there is state associated with the change, it is very important to preserve this information.
4. The result of the model comparison is a list of differences. The deployment engine will then represent each atomic change as an individual action. Then the optimizer will take the collection of actions, fold them together where possible, separate them if needed, and place them in the correct dependency order.
5. This information is then used to generate the deployment script, which is a regular .SQL script that can be executed normally using tools like SQLCMD.EXE or SSMS (SQL Server Management Studio).
6. Optionally you can tell the deployment engine to directly update the target database, but by default only the deployment script is generated, since most people want to validate that it meets the expectations before actually executing.

The end result is that you create a deployment script taking into consideration the state of the schema as it exists in time, based on the “blueprint” of the schema that you provided.

The Icing on the Cake

Since most SQL Server deployments do not have Visual Studio installed, or you simply do not have access from your Visual Studio environment to the database that needs to get updated, there is a fully standalone command-line-based deployment engine that allows you to deploy the results of a build. This command-line engine is XCOPY deployable, so you can copy it onto a memory stick or server share and deploy your database from there.

   1:  "%programfiles%\microsoft visual studio 9.0\vstsdb\deploy\vsdbcmd.exe" 

   2:  /a:Deploy 

   3:  /manifest:MyDatabase.deploymanifest 

   4:  /p:DeploymentConfigurationFile=MyDatabase.sqldeployment 

   5:  /p:SqlCommandVariablesFile=MyDatabase.sqlcmdvars 

   6:  /cs:"Data Source=MyServer\sql2008;Integrated Security=true"

Fig 5: Deploying from the command line using VSDBCMD.EXE

Using VSDBCMD.EXE is as easy as pointing it to the .DEPLOYMENTMANIFEST file, which is a container that holds all the build output together, but the tool allows you to override every part of the manifest.

This includes which deployment settings or variable files are to be used by the deployment engine.

You can also use VSDBCMD.EXE to create a model file from an existing database by using the import option like this:

   1:  "%programfiles%\microsoft visual studio 9.0\vstsdb\deploy\vsdbcmd.exe" 

   2:  /a:Import 

   3:  /cs:"Data Source=(local);Integrated Security=true;Initial Catalog=MySourceDB" 

   4:  /dsp:sql 

   5:  /model:MySourceDB.dbschema

Fig 6: Creating a model representation from a target database using VSDBCMD.EXE

The resulting file can now be used as the import file for the Schema Comparison tool, which will allow you to perform the visual comparison between two database schemas presented as a .DBSCHEMA file, a Database Project, or a connection to a database. This is an ideal support tool if you need to troubleshoot the differences between two database schemas.

Summary

Declarative database development and Visual Studio 2008 Team System Database Edition GDR can help you streamline your processes and integration of database development tasks and ease the deployment of your database schema, through a great standalone and redistributable deployment engine.

GertD @ www.DBProj.com

The QFE rollup release for the Visual Studio Team System 2008 Database Edition GDR release is now available.

You can download the SETUP.EXE from the regular location. (http://www.microsoft.com/downloads/details.aspx?FamilyID=bb3ad767-5f69-4db9-b1c9-8f55759846ed)

If you have the VS2008 RTM version of the Database Edition or the RTM version of the VSDB 2008 GDR release installed, this setup will automatically upgrade your installation. If you have the RC installed of the QFE rollup, you have to uninstall first and then re-install the GDR.

For more detail on what got fixed in the QFE rollup, please see my previous posting: http://blogs.msdn.com/gertd/archive/2009/03/26/release-candidate-of-gdr-qfe.aspx

The final version number of the is 9.1.40413.00, so you can check in your Help->About  to see if you have the latest version installed.

GertD @ www.DBProj.com

Yesterday, March 25th, the development team released an RC of the upcoming QFE of the Visual Studio 2008 Team System Database Edition GDR bits.

I was surprised to read the new naming “Visual Studio 2008 Database Edition GDR v2 RC1”. Anyhow what is in a name? It is about the content if you would ask me. This RC contains many important fixes for issues that users have reported since the release in November 2008.

Fixes in this RC release

Schema Validation

1. Aliased external objects do not resolve
2. Permission type "SEND" is a database level permission
3. Permissions with multiple INCLUDE or EXCLUDE treatment on its columns generates invalid error
4. Common Table Expressions (CTE) names should respect the model case-sensitivity
5. Adding a permission with the object name defined as a zero length string crashes VS
6. OpenXML() generates invalid warnings or errors in SQL Server 2000 database projects
7. Sub-queries defined at the column level should be able to reference table sources of the SELECT statement
8. References to some SQL-CLR Assemblies cannot be resolved causing invalid reference errors
9. Schema bound VIEWs containing a CAST function produce invalid error
10. Validation of Database.sqlpermissions is very slow

Schema Compare

1. Visual Studio crashes when comparing a composite project (which has reference to server project) to a database on server with insufficient privileges.
2. Schema Compare option to limit comparison to only objects defined in the source does not work.
3. Schema Compare option to ignore Database Role Membership does not work.
4. Schema compare write updates to project comments out all children objects when the parent object is ignored in the comparison.

Refactoring

1. Preservation of Intent doesn't handle creating a new object using a name that was previously rename refactored.
2. Deployment fails after a rename refactoring operation on a table that has an extended property on a column.

T-SQL Static Code Analysis

1. SR0014: Maintain compatibility between data types rule throws an exception when analyzing some schemas.

Schema Deployment

1. Some output clauses for parameters in a STORED PROCEDURE body are omitted during deployment.
2. Composite Projects that have segmented tables and constraints causes an exception during deployment.
3. Deployment silently fails when it encounters unknown or invalid syntax from the target database.
4. Deployment is dropping computed columns when updating column collations
5. Deployment is blocked when computed persisted function column on function change.
6. Deployment fails with "An item with the same key has already been added" after the project is copied to a new location on disk or when the solution configuration is changed with a specific sequence.

VSDBCMD Command Line Utility

1. VSDBCMD.exe throws exception when importing some database schemas.
2. VSDBCMD.exe throws exception when schema being deployed has multiple references to external .DBSCHEMA files.
3. VSDBCMD.exe exits incorrectly with code=0 when it encounters an unexpected exception.
4. VSDBCMD.exe depends on a registry key which prevents it from running on systems that do not have SSMS or VS installed.

Database Project System

1. SQLCMD Variables and Deployment properties files are not being persisted in the .user file when "My isolated development environment" is selected.
2. ALTER ANY DATABASE AUDIT and ALTER ANY SERVER AUDIT permissions are not imported from script.
3. Copying a solution containing a database project with database references to external dbschema files to a new location may causes deployment to encounter an exception.

How to get the RC?

The RC bits are made available through the Microsoft Connect Site, but you have to request access if you are not a registered user. You can do this by sending mail to the team using this link: <Send access request for RC to team>. Access to the RC is limited so you might be out of luck if too many people signed up already.  

Installing the RC

If you get access, you receive a link to a new SETUP.EXE file. This setup will upgrade your existing GDR installation, or if you do not have the GDR installed it will immediately install the latest version, so there is no need to install RTM version if the GDR bits first and then the QFE. You can uninstall the RC and re-install the RTM version, if for any reason you are running in to issues.

To validate if you have the RC installed you can check the version number which is: 9.1.40320.00

Project file upgrade

This QFE may require making an update to your project file (.DBPROJ), but the change is compatible with the RTM GDR release, so you can safely use this is a mixed environment of RTM and QFE users. The change is that a PATH gets

Detailed explanation of each fixed problem

Below you will find a more detailed set up steps that are representative for the problems that are addressed in the RC.

Schema Validation

Aliased external objects do not resolve.

Can't resolve external objects when aliased. The following steps demonstrates the problem:

• Create a SQL Server database project and add a table t1 (c1);
• Create a second SQL Server database project to reference the above project and add a stored procedure as below:

   1:  CREATE PROCEDURE [p1]

   2:  AS

   3:  SELECT a1.c1 FROM (SELECT c1 FROM [$(d1)].dbo.t1) a1

Permission type "SEND" is a database level permission.

The following fragment demonstrates the problem.

   1:  <PermissionStatement Action="GRANT">

   2:      <Permission>SEND</Permission>

   3:      <Grantee>usera</Grantee>

   4:      <Object Name="//abc.com/abc" Type="SERVICE" />

   5:      <Grantor>dbo</Grantor>

   6:  </PermissionStatement>

Permission: TSD03110: An object can only have either INCLUDE or EXCLUDE treatment on its columns.

The following fragment demonstrates the problem:

   1:  <PermissionStatement Action="DENY">

   2:      <Permission>SELECT</Permission>

   3:      <Grantee>User1</Grantee>

   4:      <Object Name="Table1" Schema="dbo" Type="OBJECT">

   5:        <Columns Treatment="EXCLUDE">

   6:          <Column Name="A"/>

   7:          <Column Name="B"/>

   8:        </Columns>      

   9:      </Object>

  10:      <Grantor>BedrockDBA</Grantor>

  11:  </PermissionStatement>

  12:    

  13:  <PermissionStatement Action="DENY">

  14:      <Permission>SELECT</Permission>

  15:      <Grantee>User1</Grantee>

  16:      <Object Name="Table1" Schema="dbo" Type="OBJECT">

  17:        <Columns Treatment="EXCLUDE">

  18:          <Column Name="C"/>

  19:          <Column Name="D"/>

  20:        </Columns>

  21:      </Object>

  22:  </PermissionStatement>

CTE names should respect the case-sensitivity.

The following steps will demonstrate the problem:

• Create a SQL Server 2005 database project
• Change the "Collation for database model" property to "English (United States) (1033) - CI"
• Create a table using the default template
• Create a stored procedure as below:

   1:  CREATE VIEW v1

   2:  AS

   3:  WITH cte AS (SELECT * FROM Table1)

   4:  SELECT * FROM CTE

• Save all files

Adding a permission with the object name defined as an empty string crashes VS

The following fragment demonstrates the problem:

   1:  <PermissionStatement Action="GRANT">

   2:      <Permission>SELECT</Permission>

   3:      <Grantee>usera</Grantee>

   4:      <Object Name="" />

   5:      <Grantor>dbo</Grantor>

   6:  </PermissionStatement>

OpenXML() generates warnings/errors in SQL Server 2000 database projects

The following steps will demonstrate the problem:

• Create a SQL Server 2000 database project
• Create a stored procedure as below:

   1:  CREATE PROC dbo.Bug

   2:  AS

   3:   

   4:  DECLARE @xml nvarchar(4000)

   5:  SET @xml = N'<root><item c1="1" c2="2" /></root>'

   6:   

   7:  DECLARE @doc int

   8:   

   9:  EXEC sp_xml_preparedocument @doc output, @xml

  10:   

  11:  SELECT 

  12:  c1,

  13:  c2

  14:  INTO #tmpTable

  15:  FROM OPENXML(@doc, '//item', 1)

  16:  WITH 

  17:  (

  18:  c1 int,

  19:  c2 int

  20:  )

  21:   

  22:  EXEC sp_xml_removedocument @doc

  23:   

  24:  SELECT * FROM #tmpTable

  25:   

  26:  DROP TABLE #tmpTable

• Save it

Table sources should be accessible to sub queries of select columns.

The following steps demonstrate the problem, generating an unresolved reference error.

• Create a SQL Server 2000 database project
• Create two tables and one stored procedure as below:

   1:  create table t1 (c1 int)

   2:  go

   3:   

   4:  create table t2 (c1 int)

   5:  go

   6:   

   7:  create proc p1

   8:  as

   9:  select (select * from (select t1.c1 from t2) tmp) from t1 

  10:  go     <?xml:namespace prefix = o />

• Save it

"TSD03006: Procedure has an unresolved reference to Assembly" error for referenced assembly. Defining a stored procedure that uses a SQL CLR assembly causes an error.

The following steps will demonstrate the problem:

• Create new SQL Server 2005 database project
• Add reference to a SQL-CLR assembly
• Add Stored procedure to project with following body:

   1:  CREATE PROCEDURE [dbo].[Procedure1]

   2:  @param1 int = 0, @param2 int

   3:  AS

   4:  EXTERNAL NAME [abc.def.hij].[abc.efg.hij.foo].[bar]

TSD03127: Cannot use CAST in a view with SCHEMABINDING

The following steps will demonstrate the problem:

• Create a view as below:

   1:  CREATE VIEW dbo.[Test2] WITH SCHEMABINDING

   2:  AS

   3:  SELECT ur.col

   4:  FROM ( SELECT 1 as col ) ur

   5:  WHERE CAST(ur.col as varchar(10)) = '0'

•  Build the project

Validating permissions is very slow.

To reproduce the problem, perform the following steps:

• Creating a permissions file with 1000 permission elements
• Save and let model validation complete
• Change one permission element
• Save

Schema Compare

Crash while comparing a composite project (which has reference to server project) with a db on server with master db having less privileges.

The following steps demonstrate the problem:

• Create a server project and a database project in a solution.
• Import a database in database project, and a master database in server project.
• Create a login and a sql user in database which does not have permission for master database.
• Do the schema compare between database project and database (using just created credentials)

Option to only compare objects defined in the source does not work.

The following steps demonstrate the problem:

• Create a 2008 Database Project
• Add Table to project using template
• Deploy to Database
• Add another Table to project using template
• Schema Compare the Project (Target) to the Deployed Database (Source)
• Enable option to compare objects defined in source
• Refresh Schema Compare

Database role membership cannot be ignored using the Schema Compare option

The following steps demonstrate the problem:

• Create a SQL Server 2008 database
• Add a user
• Add a role
• Add the user to the role
• Create an empty SQL Server 2008 database project
• Perform a Schema Compare between the database and project (project is source and database is target)
  • Note drops in update script and grid, this behavior is expected
• Change the Schema Compare option to ignore users and database roles objects
• Refresh Schema Compare

Schema compare write updates to projects comments out all composing children whose parent objects are ignored

The following steps demonstrate the problem:

• Create a SQL Server 2008 database and add a table and a stored procedure to the database
• Create a SQL Server 2008 database project and add a (different) table, for example:

   1:  CREATE TABLE t1

   2:  (

   3:  c1 int not null,

   4:  c2 int

   5:  )

• Perform a Schema Compare, using  the database as the source and the project as the target
• In Schema Compare options, under the Object Types, tab, check to “Ignore Table Objects”
• Refresh Schema Compare
• Write updates to the project
• The table that existed in the project now looks like this, with each column commented out:

   1:  CREATE TABLE t1

   2:  (

   3:  /*c1 int not null*/,

   4:  /*c2 int*/

   5:  )

   6:   

Refactoring

Preservation of Intent doesn’t handle creating a new object using a name that was previously refactored

The following steps demonstrate the problem:

• Create a SQL Server 2005 database project
• Add a table, as below:

   1:  CREATE TABLE [dbo].[Table1]

   2:  (

   3:  c1 int not null,

   4:  c2 int not null,

   5:  c3 int not null

   6:  )

   7:   

• Rename refactor column c2 in to c2a
• Add a new column: c2 int not null
• Deploy
• Re-Deploy

Incremental deploy fails after rename refactoring of a table that has an extended property on a column

The following steps demonstrate the problem:

• Create a sql 2008 db project
• Add this table with an extended property:  

   1:  CREATE TABLE [dbo].[Employee]

   2:  (

   3:   column_1 int NOT NULL,

   4:   column_2 int NULL

   5:  )

   6:  GO

   7:   

   8:  EXECUTE sp_addextendedproperty 

   9:  @name = N'MS_Description', 

  10:  @value = N'Date of birth.',

  11:  @level0type = N'SCHEMA', 

  12:  @level0name = N'dbo', 

  13:  @level1type = N'TABLE', 

  14:  @level1name = N'Employee', 

  15:  @level2type = N'COLUMN', 

  16:  @level2name = N'column_1';<?xml:namespace prefix = o /> 

• Deploy the project to a database
• Rename Refactor the table to Employee_1
• Incrementally deploy to the database  

Static Code Analysis

Static Analysis Rule SR0014 throws en exception

The following steps demonstrate the problem:

• Create a SQL Server 2005 database project
• Add a stored procedure as below:

   1:  CREATE PROCEDURE [dbo].[Procedure1]

   2:   @param1 int = 0, 

   3:   @param2 int

   4:  AS

   5:  declare @a as nvarchar(max)

   6:  set @a= (

   7:  select a from 

   8:  (select  'asdf' as a )as b  for xml auto ) 

• Run T-SQL Static Code Analysis

Using LEFT Or RIGHT method causes DataTypeCompatibility to throws an exception

The following steps demonstrate the problem:

• Create a SQL Server database project
• Add the following stored procedure

   1:  CREATE PROCEDURE [dbo].[Procedure2]

   2:   @param1 int = 0, 

   3:   @param2 int

   4:  AS

   5:   DECLARE @digits nvarchar(8)

   6:   SET @digits = RIGHT(( '00000000' + @digits ), 8)

   7:  RETURN 0

• Run T-SQL Static Code Analysis

Deploy

Output clause in parameters is omitted during build. The following is not built and deployed properly

The following script fragment demonstrate the problem:

   1:  CREATE PROC dbo.Bug

   2:  AS

   3:  DECLARE @sql nvarchar(max),

   4:          @param nvarchar(max),

   5:          @o1 int

   6:   

   7:  set @param = N'@o1 int output'

   8:   

   9:  set @sql = N'set @output = 1'

  10:   

  11:  exec sp_executesql

  12:          @sql,

  13:          @param,

  14:          @o1 output

Composite Projects: Separated Tables and Constraints causes a Null Reference exception on deploy

The following steps will demonstrate the problem:

• Create a SQL Server 2008 database project, which we will call the “Constraint Project”
• Add a second SQL Server 2008 database project, which we will call the “Table Project”
• Set the “Constraint Project” to reference the “Table Project” while leaving the server and database variables blank
• Add a table to “Table Project”, as below:

   1:  CREATE TABLE [dbo].[Table1]

   2:  (

   3:  column_1 int NOT NULL, 

   4:  column_2 int NULL

   5:  )

• Add a Default Constraint to “Constraint Project”, as below:

   1:  ALTER TABLE [dbo].[Table1]

   2:  ADD CONSTRAINT [DefaultConstraint1] 

   3:  DEFAULT 0

   4:  FOR column_1

• Set the “Target connection” string for both projects to the same server, and set the “Deploy action:” to “Create a deployment script (.sql) and deploy to the database.“
• Deploy the solution

Incremental Deploy is dropping computed columns when updating column collations

The following steps will demonstrate the problem:

• Create a new SQL Server 2008 database project
• Add a table, as below:

   1:  CREATE TABLE [dbo].[Table1]

   2:  (

   3:  customername nvarchar(30) COLLATE Sql_Latin1_General_CP1_CS_AS,

   4:  suppliername nvarchar(30) COLLATE Sql_Latin1_General_CP1_CS_AS,

   5:  unit_price int NOT NULL, 

   6:  unit_quantity int NOT NULL,

   7:  [line_price] AS ([unit_price] * [unit_quantity])

   8:  )

• Deploy to a database
• Check the database – the table was deployed correctly
• Deploy again
Deployment fails with “An item with the same key has already been added” after the project is copied to a new location on disk or when the solution configuration is changed with a specific sequence.

Computed Persisted Function column blocks deploy on function change

The following steps will demonstrate the problem:

Create a SQL Server 2005 database project
• Add the following objects to the projects:

   1:  CREATE TABLE [dbo].[Table1]

   2:  (

   3:  column_1 int NOT NULL, 

   4:  column_2 int NULL,

   5:  column_3 as [dbo].[BurgerMaster]() persisted

   6:  )

   7:  GO

   8:   

   9:  CREATE INDEX [Index1]

  10:  ON [dbo].[Table1]

  11:  (column_3)

  12:  GO

  13:   

  14:  CREATE FUNCTION [dbo].[BurgerMaster]()

  15:  RETURNS INT

  16:  WITH SCHEMABINDING 

  17:  AS 

  18:  BEGIN

  19:  return 1+1

  20:  END

• Deploy the project
• Change the 1+1 in the function to 1+2
• Deploy
• Get the following errors:
• Database2.dbschema(0,0)Error TSD01268: .Net SqlClient Data Provider: Msg 5074, Level 16, State 1, Line 1 The index 'Index1' is dependent on column 'column_3'.
• Database2.dbschema(0,0)Error TSD01268: .Net SqlClient Data Provider: Msg 4922, Level 16, State 9, Line 1 ALTER TABLE DROP COLUMN column_3 failed because one or more objects access this column.

Database Project System

SQLCMD and Deployment properties files are not being persisted in the .user file when the user chooses "My isolated development environment."

The following steps will demonstrate the problem:

• Create an empty database project
• In the Project Properties, Deploy settings change the "Configure deployment settings for" to "My isolated development environment"
• the New... button for the SqlCmd variable settings file
• Click the Save button
• Add a sqlcmd variable $(Skippy) with value set to "InUserFile"
• Deploy
• Open the deployment script and note that there is not a SETVAR statement $(Skippy) = 'InUserFile'

ALTER ANY DATABASE AUDIT and ALTER ANY SERVER AUDIT permission types are missing after Import Script

The following steps will demonstrate the problem:

• Import a script with the following content:

   1:  CREATE USER udb1 WITHOUT LOGIN

   2:   

   3:  GRANT ALTER ANY DATABASE AUDIT to udb1

• The permissions file should contain, but it does not

   1:  <PermissionStatement Action="GRANT">

   2:        <Permission>ALTER ANY DATABASE AUDIT</Permission>

   3:        <Grantee>u1</Grantee>

   4:  </PermissionStatement>

Copying a solution with database references then deploying causes deploy to crash

The following steps will demonstrate the problem:

• Create a SQL Server 2008 database project ("A")
• Build project A
• Create a SQL Server 2008 database project ("B")
• Inside project B, add a database reference to the .DBSCHEMA file of project A
• Close projects
• Copy the projects to new location on disk and re-open
• Change the configuration from DEBUG to RELEASE
• Deploy project B and note the crash during deploy 

Summary

The summary is that this RC contains some very important fixes which you do not want to miss out on, I have been using this build for the last 5 days and am very impressed with the quality and stability of it. I have not run in to any new issues so far.

Great work!

GertD @ www.DBproj.com

It was great to be able to meet many new users of the Database Edition product at the SQL Connection Spring 2009 conference this week. Thank you for attending the sessions. There were lots of great questions, critical feedback and input for new features and improvements, they are all noted!

As promised the slides and demos of the two “DataDude” sessions are now posted on the www.DBProj.com website.

Enjoy,
-GertD @ www.DBProj.com

Can the “real” database project please indentify itself?

Life can be confusing and we did not help you making it much easier by having two types of Database Projects inside Visual Studio. For the longest time there has been a “Database Project” inside Visual Studio. This project type has the file extension of .DBP and is available under the Other Project Types\Database\Database Project node inside the New Project dialog. It has not evolved for my releases and is purely there for legacy support.

This project type (.DBP) does not provided you with any of the Database Edition functionality. However if you have a Visual Studio edition that does not support the Database Edition functionality, like Visual Studio 2008 Professional, this is all you have. Sorry!

The simplest check is to look at the file extension of your project file, if it is a .DBP project, this is the old Database project file.

A second way to identify an old database project is based on the layout projected inside Solution Explorer:

If you are looking of the latest Database Project functionality, you want to make sure you have the GDR release installed. This adds a new root node inside the New Project dialog, named “Database Projects”, underneath you will find nodes/folders per provider. By default you will have a SQL Server 2000, SQL Server 2005 and SQL Server 2008 folder. All these projects share the common file extension of .DBPROJ.

Hopefully this makes it a little bit more clear which one is which, and which one it is you should be using.

-GertD @ www.DBProj.com

Next week I will be presenting at SQL Connections in Orlando where I will be covering some fun topics on DataDude and general SQL Server. Here is the list.

SQL Connections Spring 2009 (Orlando, FL)

• SDB413: Automating Database Deployment
  Do you have a need to automate the deployment of your database schema? Learn how to leverage DBSCHEMA files in combination with the command line VSDBCMD.EXE deployment engine. The latest Visual Studio 2008 Team System Database Edition GDR release provides a redistributable deployment engine that allows you to create a database deployment unit, which you can integrate in to your own “database setup”.
  
• SDV417: Roll Your Own DataDude Rules
  Learn how to extend T-SQL Static Code Analysis by writing your own rules! Static code analysis allows you to enforce your own rules in the context of your own SQL Server schema. Now you can extend the existing rule set with your own set of rules, by implementing some .NET classes. 
  
• SDV406: SQL Server Internals - Memory Management
  There are three key things to understand when it comes to SQL Server scalability and performance: execution, I/O and memory. This session will take you through the internals of SQL Server memory management in order to gain the necessary knowledge to better understand the underlying SQL Server behaviors. Once you understand how SQL Server allocates and uses its memory you will increase your effectiveness when dealing with performance tuning, scaling, and configuration problems.
  
• SDB301: Power - to the Command - Shell
  Manage your SQL Server installations from the command line using Microsoft PowerShell. Learn how to leverage the new PowerShell script shell in combination with the SQL Server PowerShell provider and CmdLets to manage your SQL Server environments from within a flexible and powerful scripting environment. If you have a need to automate your SQL Server management tasks, PowerShell is your new friend!

I hope to see you in Orlando and for those who cannot make it I will post the slides and demos on www.dbproj.com after the conference.

-GertD

If you are running in to the following problem when creating unit tests when using database projects, there now is a fix!

Consider the following scenario, you have you database project (.dbproj) open and requesting the creation of a Database Unit Test by right clicking on for example a stored procedure objects inside Schema View. This will active the Create Unit Tests wizard. However, the following problems occur in the Create Unit Tests wizard:

• There are no type lists in the Types panel.
• The Output project list is missing.
• When you click the Settings button, the wizard closes immediately.
• The OK button is unavailable.

This problem is in more detail described in the following KB article, http://support.microsoft.com/kb/962866 

A fix is now available at MSDN Code Gallery, see http://code.msdn.microsoft.com/KB962866

-GertD

Is your Visual Studio Team System 2008 Database Edition GDR installation confused?
Does your menu suffer from multiple instance disorder?

Here is what you can do about it.

1. Make sure that Visual Studio (devenv.exe) is closed.
2. Start an elevated command prompt.
3. Run the following two commands:
   

   "%ProgramFiles%\Microsoft Visual Studio 9.0\DBPro\DBProRepair.exe" RemoveDBPro2008 "%ProgramFiles%\Microsoft Visual Studio 9.0\Common7\IDE\devenv.exe" /ResetUserData

Now restart Visual Studio again, and the problem should be cured.

-GertD

Jamie Laflen and Barclay Hill wrote a great, must read article for MSDN Magazine, detailing the Visual Studio Team System 2008 Database Edition GDR functionality. Besides that it has a great intro describing the base principles of offline database schema development.

Check out MSDN Magazine March 2009

Database Development
Introducing New Features In The VSTS Database Edition GDR
http://msdn.microsoft.com/en-us/magazine/dd483214.aspx

Enjoy the great read!
-GertD

Today Quest Software announced that they will offer a Database Schema Provider (DSP) for Oracle that plugs in to the Visual Studio Team System 2010 release.

You can read the announcement at the Quest Software website: http://www.quest.com/newsroom/news-releases-show.aspx?contentid=9102, you can find more detail, like screen shots and sign up for the beta here: http://www.teamfuze.net/index.jspa

For more detail also see Terry Clancy his posting about this: http://blogs.msdn.com/terryclancy/archive/2009/02/24/quest-software-announces-oracle-database-schema-provider-for-visdual-studio-team-system-2010.aspx

-GertD

The Visual Studio Team System Database Edition team has created a new blog site to keep you informed and update about the latest developments.

Please check out: http://blogs.msdn.com/vstsdb

Thanks,
-GertD

Some of you already know or noticed through sites like LinkedIn or Facebook that per the beginning of this month I started in a new role inside Microsoft. I moved in to an Software Architect role, working on the new Directory Services stack. Hence I needed a new place to publish my DataDude contributions to the community, this new place is DBProj.com

The goal of the site is very simple, provide as much as possible information about the “Visual Studio Team System 2008 Database Edition GDR” product. Yes, you are reading it correctly, the site will solely focus on the “GDR” release. When a next release will become available that builds on top of the GDR architecture, Visual Studio 2010, I will expand the scope to include that as well. Since it is no longer my primary job, I need to focus on something, I hope you understand.

Anyhow, from now on I will publish on the DBProj.com.

This is the end of a great 3-year journey, but that journey would not have been possible without the support of a great team of testers, program managers and developers.

Thank you all! This site is a contribution to your work and dedication!

Thanks,
-GertD forever a “DataDude”