Release Candidate of GDR QFE

아티클
03/26/2009

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

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

Schema Compare

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

Refactoring

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

T-SQL Static Code Analysis

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

Schema Deployment

Some output clauses for parameters in a STORED PROCEDURE body are omitted during deployment.
Composite Projects that have segmented tables and constraints causes an exception during deployment.
Deployment silently fails when it encounters unknown or invalid syntax from the target database.
Deployment is dropping computed columns when updating column collations
Deployment is blocked when computed persisted function column on function change.
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

VSDBCMD.exe throws exception when importing some database schemas.
VSDBCMD.exe throws exception when schema being deployed has multiple references to external .DBSCHEMA files.
VSDBCMD.exe exits incorrectly with code=0 when it encounters an unexpected exception.
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

SQLCMD Variables and Deployment properties files are not being persisted in the .user file when "My isolated development environment" is selected.
ALTER ANY DATABASE AUDIT and ALTER ANY SERVER AUDIT permissions are not imported from script.
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

Comments

Anonymous
March 26, 2009
The comment has been removed
Anonymous
April 01, 2009
Aaron Bjork on Reports in TFS 2010.it's getting easier Data Dude on SQL Connections Slides and Demo
Anonymous
April 06, 2009
The comment has been removed
Anonymous
April 06, 2009
The comment has been removed
Anonymous
April 22, 2009
The comment has been removed
Anonymous
April 22, 2009
E’ disponibile sul Download Center la seconda versione della GDR della Database Edition di VSTS 2008
Anonymous
April 23, 2009
The comment has been removed
Anonymous
July 21, 2009
Introducing such a topic you'd like to congratulate you've let us know. Have good work.
Anonymous
October 14, 2009
The comment has been removed

다음을 통해 공유

Release Candidate of GDR QFE

Fixes in this RC release

Schema Validation

Schema Compare

Refactoring

T-SQL Static Code Analysis

Schema Deployment

VSDBCMD Command Line Utility

Database Project System

How to get the RC?

Installing the RC

Project file upgrade

Detailed explanation of each fixed problem

Schema Validation

Aliased external objects do not resolve.

Permission type "SEND" is a database level permission.

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

CTE names should respect the case-sensitivity.

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

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

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

"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.

TSD03127: Cannot use CAST in a view with SCHEMABINDING

Validating permissions is very slow.

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.

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

Database role membership cannot be ignored using the Schema Compare option

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

Refactoring

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

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

Static Code Analysis

Static Analysis Rule SR0014 throws en exception

Using LEFT Or RIGHT method causes DataTypeCompatibility to throws an exception

Deploy

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

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

Incremental Deploy is dropping computed columns when updating column collations

Computed Persisted Function column blocks deploy on function change

Database Project System

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

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

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

Summary

Comments

추가 리소스