Visual Studio Team System 2008 Database Edition GDR
R2

Release Notes

The following issues are addressed in this release.

Validation

1.      External
objects cannot be resolved when they are aliased. The following example causes
an error.

Create a db
project and add a table t1 (c1);

Create
another db project to reference the above project and add a proc as below:

CREATE PROCEDURE [p1]

AS

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

2.      Permission
type "SEND" should be a database level permission. The following example
causes an error.

<PermissionStatement
Action="GRANT">

<Permission>SEND</Permission>

<Grantee>usera</Grantee>

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

<Grantor>dbo</Grantor>

</PermissionStatement>

3.      Permission:
TSD03110: An object can only have either INCLUDE or EXCLUDE treatment on its
columns. The following example causes an error.

<PermissionStatement
Action="DENY">

<Permission>SELECT</Permission>

<Grantee>User1</Grantee>

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

<Columns Treatment="EXCLUDE">

<Column Name="A"/>

<Column Name="B"/>

</Columns>

</Object>

<Grantor>BedrockDBA</Grantor>

</PermissionStatement>

<PermissionStatement Action="DENY">

<Permission>SELECT</Permission>

<Grantee>User1</Grantee>

<Columns Treatment="EXCLUDE">

<Column Name="C"/>

<Column Name="D"/>

</Columns>

</Object>

</PermissionStatement>

4.      CTE
(Common Table Expression) names should respect the case-sensitivity setting.
The following example causes an error.

·
Create a
2005 database project

·
Change its
property to have "English (United States) (1033) - CI" as the
"Collation for database model"

·
Create a
table with the default template

·
Create a stored
procedure as below

CREATE VIEW v1

AS

WITH cte AS (SELECT *
FROM Table1)

SELECT * FROM CTE

·
Save all
files

5.      Adding
a permission with the object name defined as an empty string crashes Visual
Studio

<PermissionStatement
Action="GRANT">

<Permission>SELECT</Permission>

<Grantee>usera</Grantee>

<Object Name="" />

<Grantor>dbo</Grantor>

</PermissionStatement>

6.      OpenXML
generates warnings/errors in SQL Server 2000 database projects

·
Create a SQL
Server 2000 database project

·
Create a stored
procedure

CREATE PROC dbo.Bug

AS

DECLARE @xml nvarchar(4000)

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

DECLARE @doc int

exec sp_xml_preparedocument @doc output,
@xml

SELECT

c1,

c2

INTO #tmpTable

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

WITH (

c1 int,

c2 int

)

exec sp_xml_removedocument @doc

SELECT * FROM #tmpTable

DROP TABLE #tmpTable

·
Save the
procedure

7.      Table
sources should be accessible to sub queries of select columns. The following example
causes an unresolved reference error.

·
Create a SQL
Server 2000 database project;

·
Add two
tables and one stored procedure as below

create table t1 (c1 int);

go

create table t2 (c1 int);

go

create proc p1

as

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

·
Save all
changes

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

·
Create
new SQL Server 2005 Database Project

·
Add
a reference to a SQL CLR assembly

·
Add
a stored procedure to the project with the following body:

CREATE PROCEDURE [dbo].[Procedure1]

@param1 int = 0, @param2
int

AS

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

9.      TSD03127:
Cannot use CAST in a view
with SCHEMABINDING

·
Create a
view with the following definition:

CREATE VIEW dbo.[Test2] WITH SCHEMABINDING

AS

SELECT ur.col

FROM (

SELECT 1 as col

) ur

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

·
Build the
project

10.  Validating permissions is very
slow.

·
Create a permissions file with 1000 or more permission
elements

·
Save the file and let model validation complete

·
Change one permission element

·
Save the file

11.  Building
the database project causes error TSD03006 when the script
contains an XQuery clause.

12.
A script containing a left join and
union causes error "TSD02010: Incorrect syntax near UNION"

·
Add the below script in a stored procedure in
the database project

CREATE PROCEDURE [dbo].[Procedure2]

AS

select * from

(

(

select top 1 unit.unitname FROM [Call]

left join unit on [Call].unitid=unit.unitid

where CheckCall.unitid= 5

order by CallDate desc

)

union all

SELECT top 1 unit.unitname FROM [Call]

left join unit on [Call].unitid=unit.unitid

where Call.unitid= 7

order by CallDate desc

) b

·
Build

Schema Compare

1.
Visual Studio might crash when you compare
a composite project (that has a reference to a server project) with a database
on a server where the master database has lesser privileges.

·
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 the database that does not have permissions for master
database.

·
Perform the
schema compare between database project and database (using the just created
credentials)

2.
The option to only compare objects
defined in the source schema does not work. To reproduce this issue, perform
the following steps:

·
Create a SQL
Server 2008 database project

·
Add a table
to the database project using Add New Item

·
Deploy the
database project to a target database

·
Add another table
to the database project using template

·
Schema
Compare the project (target) to the deployed database (source)

·
Select the option
check box to compare objects defined in source

·
Refresh
Schema Compare

3.
Database role membership cannot be
ignored using the Schema Compare option. To reproduce this issue, perform the
following steps:

·
Create a SQL Server 2008 database

·
Add a user

·
Add a role

·
Add the user to the role

·
Create an empty SQL Server 2008 project

·
Schema Compare the database and the project
(project is the source and the database is the target) -> Note DROP actions in
the update script and grid (this is expected behavior)

·
Change the Schema Compare option to ignore users
and database roles objects.

·
Refresh Schema Compare.

4.
When using Schema compare, the Write Updates  command,
when applied to projects, comments out all composing children whose parent
objects are ignored. To reproduce this issue, perform the following steps:

·
Create a
database and add a table and a stored procedure to the database

·
Create a SQL
Server 2008 database project and add a (different) table, such as:

Create table t1

(

c1 int not null,

c2 int

)

·
Schema
compare the database (source) to the project (target)

·
In the Schema
Compare options, on the Object Types tab, in the Ignore the selected objects type list, select the Tables check box.

·
Refresh
Schema Compare

·
Write
updates to the project

·
The table
that existed in the project now looks like this, with each column commented
out:

Create table t1

(

/*c1 int not null*/,

/*c2 int*/

)

5.
System.IndexOutOfRangeException appears when you compare a database to itself

·
Create a SQL
Server 2008 database with the following script:

USE [master]

GO

CREATE DATABASE DatabaseWithEncryptedFunction

GO

USE [DatabaseWithEncryptedFunction]

GO

CREATE FUNCTION Function1() RETURNS @Table TABLE

(

c2 int,

c1 AS 5

) WITH ENCRYPTION

AS

BEGIN

RETURN

END

GO

·
Schema Compare
the database with itself

·
Visual Studio
crashes

Refactoring

1.
Preservation of intent does not handle the
scenario when a new object must be created that uses a name that was previously
refactored.

·
Create a SQL
Server 2005 project

·
Add a table:

Create table [dbo].[Table1]

(

c1 int not null,

c2 int not null,

c3 int not null

)

·
Refactor: c2 -> c2a

·
Add column:
c2 int not null

·
Deploy

·
Re-Deploy

2.
Incremental deploy fails after refactor -> rename of a table that has an extended
property on a column

·
Create a SQL
Server 2008 database project

·
Add this
table with an extended property:

CREATE TABLE [dbo].[Employee]

(

column_1 int NOT NULL,

column_2 int NULL

)

GO

EXECUTE sp_addextendedproperty
@name = N'MS_Description', @value = N'Date of birth.',

@level0type = N'SCHEMA', @level0name = N'dbo',

@level1type = N'TABLE', @level1name = N'Employee',

@level2type = N'COLUMN', @level2name =
N'column_1';

·
Deploy to
database

·
Refactor -> rename the table to Employee_1

·
Incrementally
deploy to the database

Static Code Analysis

1.
Static Analysis rule SR0014 throws an exception

·
Create a SQL
Server 2005 database project

·
Add a stored
procedure

CREATE PROCEDURE [dbo].[Procedure1]

@param1 int = 0,

@param2 int

AS

declare @a as nvarchar(max)

set @a= (

select a from

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

·
Run Static
Code Analysis on the database project

2.
Using LEFT or RIGHT method causes DataTypeCompatibility to throw an exception

·
Create a
database project

·
Add the
following stored procedure

CREATE PROCEDURE [dbo].[Procedure2]

@param1 int = 0,

@param2 int

AS

DECLARE @digits nvarchar(8)

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

RETURN 0

·
Run SCA

Deploy

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

CREATE PROC dbo.Bug

AS

DECLARE @sql nvarchar(max),

@param nvarchar(max),

@o1 int

set @param = N'@o1 int
output'

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

exec sp_executesql

@sql,

@param,

@o1
output

2.      Composite
Projects: Implementing tables and constraints in separate database projects
causes a Null Reference exception when you deploy.

·
Create a SQL
Server 2008 project [Constraint project]

·
Add a second
SQL Server 2008 project [Table project]

·
Set the
[Constraint project] to reference the [Table project] leaving the server and
database variables blank.

·
Add a table
to [Table project]:

CREATE TABLE [dbo].[Table1]

(

column_1 int NOT NULL,

column_2 int NULL

)

·
Add a
Default Constraint to [Constraint project]:

ALTER TABLE [dbo].[Table1]

ADD CONSTRAINT [DefaultConstraint1]

DEFAULT 0

FOR column_1

·
Set the connection
string for both of them to the same server, and set them to deploy to database.

·
Deploy

3.      Deploy
silently fails when it encounters unknown syntax from the target database
during reverse engineering.

4.      Incremental
deployment is dropping computed columns when updating column collations.

·
Create a new SQL Server 2008 database project

·
Add this table:

CREATE TABLE [dbo].[Table1]

(

customername nvarchar(30) COLLATE Sql_Latin1_General_CP1_CS_AS,

suppliername nvarchar(30) COLLATE Sql_Latin1_General_CP1_CS_AS,

unit_price int NOT NULL,

unit_quantity int NOT NULL,

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

)

·
Deploy to a database.

·
Check the database – the table was
deployed correctly.

·
Deploy again

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

6.      A
computed persisted function column blocks deployment if the function was changed.

·
Create a SQL
Server 2005 project

·
Add the
following script:

CREATE TABLE [dbo].[Table1]

(

column_1 int NOT NULL,

column_2 int NULL,

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

)

GO

CREATE INDEX [Index1]

ON [dbo].[Table1]

(column_3)

GO

CREATE FUNCTION [dbo].[BurgerMaster]

()

RETURNS INT

WITH SCHEMABINDING

AS

BEGIN

return 1+1

END

·
Deploy
the project

·
change
the 1+1 in the function to 1+2

·
Deploy

o
Get the following errors:

o
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'.

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

VSDBCMD

1.
NullReferenceException
when importing a database schema with VSDBCMD

2.
VSDBCMD fails if the schema being
deployed has multiple references to external .dbschema
files.

3.
VSDBCMD exits incorrectly with code=0
on unexpected failure.

4.
VSDBCMD depends on a registry key which
prevents it from running on systems that do not have SSMS or VS installed.

Project System

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

·
Create an
empty database project

·
In the
Project Properties, Deploy settings change the Configure deployment settings for to "My isolated development
environment"

·
Click 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'

2.
ALTER ANY DATABASE AUDIT and ALTER ANY
SERVER AUDIT permission types are missing after Import Script. After importing
the following script:

CREATE USER udb1 WITHOUT LOGIN

GRANT ALTER ANY DATABASE AUDIT to udb1

The permissions file should contain the following
statements:

<PermissionStatement
Action="GRANT">

<Permission>ALTER ANY DATABASE AUDIT</Permission>

<Grantee>u1</Grantee>

</PermissionStatement>

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

·
Create a SQL
Server 2008 Database Project ("A")

·
Build
Project A

·
Create a SQL
Server 2008 Database Project ("B")

·
Add a
database reference to project A's .dbschema file

·
Close
projects

·
Copy
projects to new location on disk and open

·
Change the
configuration from DEBUG to RELEASE

·
Deploy
Project B and note deploy crash

4.
Imported permissions
are always added to the model even if the user chooses to not add them.

Unit Test

1.      Problems
occur in the Create Unit Tests wizard when you create a unit test for a method
in a solution that contains a Database Edition GDR project, a Silverlight project, or a WIX project in Visual Studio 2008
Service Pack 1

·
Create an empty visual studio solution

·
Add a Database project

·
Import AdventureWorks

·
Add a Silverlight
project

·
Click a stored procedure in Schema View

·
On the context menu, click Create Unit Test

The solution to this issue is to
install this hotfix: http://code.msdn.microsoft.com/KB962866