We just released a new CTP of the upcoming 2008 update, Microsoft® Visual Studio Team System 2008 Database Edition GDR July CTP is here!

You can download it from MSDN:
http://www.microsoft.com/downloads/details.aspx?FamilyID=bb3ad767-5f69-4db9-b1c9-8f55759846ed&displaylang=en

Most important changes:

• Project upgrade is now in place. For those of you who were kind enough to donate their database projects, thank you!
• Mixed projects are no longer supported, from now on server projects represent only server creatable objects and user objects that need to be deployed to “master”. Another change is that server options will not get deployed; we only validate the settings as pre-requisites for a deployment. As such server options (sp_configure) have been added to the project system.
• The interpreter now understands temporary tables, table variables and select into column sources.
• The Static Code Analysis MSBuild task was added
• XSD Reference user interface support is enable, but it does not yet create XML Schema Collections
• And lots of fixes since CTP14

Please file bugs using Microsoft Connect and post questions on the MSDN forum.

Thanks,
-GertD

Jeff Levinson recently wrote a great article in Visual Studio Magazine covering the upcoming GDR release: Introducing the VSTS Database Edition GDR and Jeffrey Juday just published an article on Developer.com about Database Unit Testing: Introducing Visual Studio Team System 2008 Database Unit Testing

-GertD

Some users have been reporting problems installing the VSDB Power Tools on Vista or Windows Server 2008 (applies to both 32 and 64 bit versions).

When they run the Power Tools installer they are confronted with the following mysterious error message:

Error code 2738 implies that the VBScript engine is not enabled. On some Vista and Windows Server 2008 installations the VBScript engine is disabled. The workaround is to enable the VBScript engine and run the setup again. After that you can disable the VBScript engine again if desired.

How to enable the VBScript engine:

1. Start an elevated command prompt.
   
2. Run the following command :
   
   1. On a 32-bit OS run:
      regsvr32 %windir%\System32\vbscript.dll
      
   2. On a 64-bit OS run:
      regsvr32 %windir%\SysWOW64\vbscript.dll
      

Now run the Power Tool installer from an elevated process.

How to disable the VBScript engine:

1. Start an elevated command prompt.
   
2. Run the following command :
   
   
   1. On a 32-bit OS run:
      
      regsvr32 %windir%\System32\vbscript.dll /U
      
   2. On a 64-bit OS run:
      regsvr32 %windir%\SysWOW64\vbscript.dll /U

I hope this helps, the next version of the Power Tools installer will no longer rely on VBScript.

-GertD

On Wednesday July 9th, I will be presenting for the Pacific Northwest SQL Server User Group. If you are in the neighborhood, feel free to join us. The meeting will be held at the Microsoft Campus, building 35. The topic du jour will be about what is in the GDR.

For more info see this link: http://groups.msn.com/pnwsql/nextmeetinginfo.msnw

"SQL Server Schema Development & Deployment Using DataDude vNext"

The latest release of Visual Studio Team System 2008 Database Edition, not only enables support for SQL Server 2008 and all its new meta data, it includes a completely revamped build and deployment engine and new tools for increasing the quality of your database schemas. This session will guide you through all new aspects and features from creating your initial enlistments, building deployment packages, how to create configuration files for your deployment packages and how to test and validate your deployments.

For more information about the Visual Studio Team System 2008 Database Edition GDR release check out the following link: http://blogs.msdn.com/gertd/archive/2008/06/03/vsts-2008-database-edition-gdr-june-ctp.aspx

-GertD

This coming fall, we will be having a full day, hands-on workshop at SQL Connection in Las Vegas, covering the upcoming Visual Studio Team Edition 2008 Database Edition GDR release.

The goal of the workshop is to get you acquainted with the new functionality and to go through a complete end-to-end database development life cycle, from setup, incubation of databases in to the system, making changes and deploying changes using the new deployment engine.

Details you can find on the workshop section SQL Connections conference site.

We hope to see you there!

-GertD

This panel discussion was recorded at Tech*Ed this week, it is a panel discussion about Visual Studio Team System at large. Participants Brian Harry (Technical Fellow of Team Foundation Server), Neelesh Kamkolkar (Senior Product Manager VSTS Test Edition), Peter Provost (Program Manager VSTS Architect Edition), Habib Heydarian (Lead Program Manager VSTS Developer Edition), Norman Guadagno (Director of VSTS Marketing) and myself discuss futures and strategy of Visual Studio Team System.

Check it out,
-GertD

Yesterday, during Bill Gates his Tech Ed keynote, Brian Harry had the honor to show the first glimpse of the results of our partnership with IBM, DB2 support inside Visual Studio Team System Database Edition. As you might know or have read in one of my previous blog posts, we are changing the underlying architecture of the VSTS Database Edition product, to be provider based. This change enables other vendors or 3rd parties, by creating a Database Schema Provider (DSP), to start managing and deploying their schemas from inside Visual Studio Team System "Rosario" using the same database project system and user experience we currently have in place to manage and deploy your SQL Server schemas.

One of the vendors we have been working with from the start to validate the provider and extensibility model is IBM and yesterday was the first time we could publicly show the results of this collaboration.

As you can read in the overall Tech Ed press release: http://www.microsoft.com/presspass/press/2008/jun08/06-03TechEdDevPR.mspx

• An alliance with IBM that will further simplify application development for enterprise teams working in heterogeneous environments. IBM and Microsoft have agreed to work together to integrate IBM DB2 database access with Visual Studio Team System Database Edition.

This events marks a significant milestone for the product, and is the enabling technology to include other data store in to the application life cycle.

-GertD

We just released a new Channel 9 interview that covers the Visual Studio Team System 2008 Database Edition GDR functionality that we introduced today.

http://channel9.msdn.com/posts/briankel/New-GDR-Announced-for-Visual-Studio-Team-System-2008-Database-Edition/

-GertD

Today, at Tech*Ed 2008 Developer, we announced the immediate availability of the first public CTP of the Visual Studio Team System 2008 Database Edition GDR.

GDR stands for General Distribution Release. The GDR will be an out-of-band release enabling support for SQL Server 2008. It also introduces the new product architecture, which for those of you who are keeping up with the code names, is work that is a direct result of the "Rosario" project.

What is in the GDR?

In addition to enabling SQL Server 2008 database projects, the GDR release incorporates many of the previously released Power Tools functionality as well as several new features. The new features include explicit separation of Build and Deploy, separation of Database and Server project, improved project reference support, T-SQL Static Code Analysis and integration with SQL-CLR projects.

Architectural changes

• No more DesignDB; one of the most important architectural changes is that the Database Edition no longer requires a local SQL Server instance to host the "Design Database" to open and validate a database project.
• Single model, everything is now loaded in a single model representation. The model now represents all object types, which removes the need to have certain objects represented as Pre or Post Deployment scripts. This means that for example logins, rules, defaults, asymmetric keys, symmetric keys, certificates, etc. are now schema objects and therefore fully participate in schema comparison and build/deploy. Pre or Post Deployment scripts still exist, but are only used to perform none schema object related operations.
• The model is no longer 100% memory resident, in today's version all model information has to reside in memory, this change dramatically lowers the memory consumption of the product.
• Provider based, the underlying implementation of the project system and schema model are changed to be provider based. Providers are refer to as "Database Schema Providers" or DSP's for short. The GDR release will ship with 3 providers supporting SQL Server: 2000, 2005 and 2008. However if you watch the Tech*Ed keynote this morning, you saw that IBM is working on a provider to support DB2 and there are others that are working on providers for other database management systems.
  • Providers are not restricted to relational database systems, in the future we will be adding providers that support dimensional and hierarchal data stores.

Project System

• Database & Server project separation
  • We separated out the existing database project in to two project flavors: database and server. The server project represent those schema objects that are server wide, for example logins, linked servers, server side DDL triggers etc. Database projects represent the objects inside the user database. The split enables a team to define a standard a configuration for their SQL servers and reference it from their Database Projects. The goals is to have a single point of definition and to be able to share this definition between projects and deployments.
    • The separation is implemented using a property inside the project file. There are effectively 3 modes: server, database and hybrid. The hybrid project represents the overloaded project model as it exists today where server and database objects are mixed inside a single project. This model is there to support existing project upgrades.
• Partial projects
  • Allow code sharing between projects by including files from a different project, where the code is included "as-is" from the originating project and the source code control ownership remains with the originating project. This enables code reuse between projects and while resulting in a single deployment unit.
• Composite projects
  • Composite projects, enables database projects and/or .dbschema files to contribute to another project. This enables the separation of development roles and responsibilities and composition of Databases using multiple projects. It extends the existing database reference implementation, by allowing to contribute in to the same database, where the existing database projects have to represent 3 or 4 part name references.
• Single sourcing of external artifacts
  • You can now create a reference to a SQL-CLR (VB.NET or C#) project, or the binary output of a project and turn it in to a ASSEMBLY inside the project. This enables single sourcing of artifacts that are developed outside the context of the database project. For the final release we will also allow references to XSD files which will become XML Schema Collections inside the database schema.

SQL Server 2008 Support

• The GDR adds a new project for supporting SQL Server 2008. The June CTP has support of the new SQL Server 2008 data types, both intrinsic (date, datetime2, datetimeoffset, time), build-in SQL-CLR types (geography, geometry, hierarchyid), support for the new DML MERGE syntax and support for table typed parameters. The remaining SQL Server 2008 syntax and functionality will be added in future CTP's.

Build & Deploy

• One of the other main changes in the GDR is the separation of the build and deployment process. Build now produces a single portable artifact file, a .DBSCHEMA file. The DBSCHEMA file is an XML representation of your complete schema. The DBSCHEMA is then fed in to the redistributable deployment engine.
• The deployment engine now uses the exact same database model as Schema Compare, which guarantees the same results between the two parts of the system.

Refactoring

• The biggest change in refactoring is the addition of a patented implementation of a feature named "Preservation of Intent". This enables the deployment of refactoring changes as intended by the user. For users this means that renames are deployed as renames not as drop/add statements, move aschemas as move schemas etc.
• Besides that we are adding new refactoring types that we previously in the Power Tools:
  • Wildcard Expansion
  • Move Schema
  • Fully Qualify
• And we are enabling extensibility for refactoring which enables users to develop and deploy custom refactoring types and targets.
  • Types are the refactoring operations, where targets are the artifacts you want to apply the refactoring operation to. An example of a refactoring type is: table split or upper case all keywords. An example of a refactoring target is an Reporting Services RDL file, which contains references to database schema objects, which if you change thos, you want to update them as part of the refactoring operation.

Schema Compare

• Schema Compare now uses the same underlying database model as build and deploy, guaranteeing fidelity between the results. This allows the user to compare any combination of Database Projects (.dbprj), live Databases or .DBSCHEMA files.  So comparing project to project, project with a DBSCHEMA file, or a DBSCHEMA file with a live database.
• We added the ability to limited the schema comparison based on object types (Object Type Filtering) and we added additional Ignore filtering options.
• We also enabled the substitution of SQLCMD variables, allowing correct comparison when using SQLCMD variables for references or in other parts of your code.

T-SQL Static Code Analysis

• T-SQL Static Code Analysis, and the accompanying MSBuild task, are now part of the main product.
• And we added the ability to develop and deploy your own custom T-SQL Static Code Analysis rules.

Dependency Viewer

• The Power Tools to view the dependency relationship between objects inside your schema, is now part of the base product.

Database Unit Testing

• Database unit testing now supports execution of tests using different ADO.NET providers and it also enables unit testing when using multiple database projects.

• Data Generation

• We made some general design-time enhancements Data Generation like adding support for undo/redo and separation of the population status into new display window.
• The Foreign key generator can now be replaced by custom generator.
• And we made some runtime enhancements, by default the Data Generator now uses the SqlBulkCopy interface to populate target tables, which results in a general performance improvement during data load time.
• We also introduced the concept of Data Sinks which allow the user to register different outputs, for example we can generate data to files instead of to a live database, so data can be loaded out-of-band using BCP or BULK INSERT. 
• And last but not least we were able to significantly trim the size of the .DGEN file. 

Public extensibility:

• The last piece we changed in the GDR is to expose more extensibility points in to the system. In the GDR we are not allowing new providers to be plugged in this will be available in the next release, but we do allow users to extend the system by writing their own:
• Data Generators
• (Statistical) Data Distributions
• Test Conditions
• T-SQL Static Code Analysis Rules
• Refactoring Types
• Refactoring Target
• We also made the deployment engine a redistributable component, so you can deploy DBSCHEMA files programmatically.

So where to get the GDR?

All of this is part of the Visual Studio Team System 2008 Database Edition GDR June CTP which is available right now! Please see the download page.

Make sure you have the prerequisites installed, which means installing Visual Studio 2008 SP1 beta.

Feedback, questions, bugs, suggestions

Please post questions to the MSDN Visual Studio Team System - Database Professionals forum. For bugs, ideas and suggestions please use the Microsoft Connect site https://connect.microsoft.com/VisualStudio and fill them using the Visual Studio Team System 2008 Database Edition GDR link.

Thanks,
-GertD
Group Engineering Manager
Visual Studio Team System Database Edition

In case you are coming to the Developer edition of Tech·Ed next week, we have a lot of exiting things for you. As you can see below there are many sessions, hosted in both the developer and database tracks. Breakout sessions, chalk talks, panel discussions and hands-on lab, all covering Visual Studio Team System Database Edition.

It all starts in BillG his keynote, where Brian Harry will show some of the partner work we have been involved in, stay tuned for more details on that!

Besides all the sessions you will be able to talk to the team members 1-1 on the booth. We have a presence in the Visual Studio Team System area and we are also present on the SQL Server booth to demonstrate the integration with SQL Server 2008.

We hope to see you at Tech·Ed,

-GertD

Session list:

Sessions details:

DVP310 Agile Database Development (level 300)

Tuesday, June 3 1:15 PM - 2:30 PM, S330 C 

Speaker(s): Richard Hundhausen

Session Type: Breakout Session

Agile remains a popular buzzword these days. Some teams strive to become more agile, while others tend to avoid it. Where do you weigh in? Regardless, it's not something that has traditionally been applicable to SQL Server database development, until recently. With the proper configuration and use of Microsoft Visual Studio Team System Team Foundation Server and the Database Editions tools, it is possible to satisfy your clients' changing needs should they extend to the database.

TLA310 End-to-End Database Development Using Microsoft Visual Studio Team System 2008 Database Edition (level 300)

Tuesday, June 3 3:00 PM - 4:15 PM, S330 A 

Speaker(s): Mairead O'Donovan

Session Type: Breakout Session

The Database Edition of Visual Studio Team System 2008 provides a comprehensive set of tools and features that integrates the Database Development Lifecycle into the overall Application Development Lifecycle. In this session, we cover the principles that tie the Database Development model into the larger ALM, and take a tour through the features of the Database Edition with an emphasis on how each leg of the Lifecycle is supported by Database-specific tools and features. Special attention is paid to the enhancements added as part of the VSTS 2008 release, as well as the extended functionality added when you install the optional Database Edition Power Tools. This session is a mix of theory and practice, with live demonstrations of the fundamental features found at each level of the Database Development Lifecycle.

DAT326 Supporting Microsoft SQL Server 2008 within Microsoft Visual Studio Team System Database Edition (level 300)

Thursday, June 5 10:15 AM - 11:30 AM, S230 E (DEV) 

Speaker(s): Gert Drapers, Jamie Laflen

Session Type: Breakout Session

One of the most anticipated aspects of the upcoming Database Edition for Visual Studio Team System release, code name "Rosario", is the support for SQL Server 2008. This session covers how the new architecture, features, and functionality of the new Visual Studio Team System Database Edition version will enable the support of SQL Server 2008 new data types, new syntax and other functionality. In addition, this session provides an overview of all the new functionality which applies to SQL Server 2000, 2005, and 2008.

DAT01-TLC Managing Database References in Microsoft Visual Studio Team System 2008 Database Edition (level 400)

Thursday, June 5 1:00 PM - 2:15 PM, Green Theater 1 

Speaker(s): Gert Drapers, Jamie Laflen

Session Type: Interactive Theaters

Visual Studio Team System 2008 Database Edition enhances the ability of the Database Project to understand and manage database references across multiple databases from within a single project, or across several projects. This chalk talk focuses on how best to best manage three- and four-part name references inside a Visual Studio Team System Database Edition project.

TEO Panel _25 Testing the SQL Database (panel discussion)

Thursday, June 5 3:00 PM - 4:00 PM 

Speaker(s): Gert Drapers, Jamie Laflen, Adam Machanic, David Reed, John Welch

Session Type: TechEd Online Sessions

It goes almost without saying that testing is a core aspect of any properly structured software development effort. Yet when it comes to the database layer, we often see developers shy away from a proper testing regimen. The prevailing attitude is often that database testing is either not necessary or simply too difficult to properly accomplish. But the truth is, database testing is just as doable, and certainly just as valuable, as testing of any other layer. In this panel we will discuss why you want to test your database, and the various types of database testing that you should be doing as you develop and deploy your application. We will cover testing scenarios to handle functionality, regression, performance, scalability, and other key metrics as they relate to your database infrastructure. Without testing, your database's runtime behaviors are effectively an unknown. Come to this panel to learn how not to leave things to chance.

DAT02-TLC Microsoft Visual Studio Team System 2008 Database Edition: Integrating Database Developers in Your Software Management Life Cycle (level 200)

Thursday, June 5 4:30 PM - 5:45 PM, Green Theater 1 

Speaker(s): Brian Randell

Session Type: Interactive Theaters

For many applications, the database is the foundation. Yet for many teams, the “database professional” has limped along without the tools necessary to do their job. Managing database change and evolution is critical to successful software projects. In this session, learn how Visual Studio Team Edition for Database Professionals can help you integrate database design, creation, and evolution in your overall software development life cycle using the entire Team System. Learn how the product integrates with other team editions and team members including features such as source code control, work item tracking, test case management, and build.

TLA320 The Microsoft Visual Studio Team System Database Project Ecosystem: Integrating ALM and Database Tools via Extensibility (level 300)

Friday, June 6 8:30 AM - 9:45 AM, S230 A 

Speaker(s): Gert Drapers, Brent Gross (IBM Corp.)

Session Type: Breakout Session

Discover how Visual Studio Team System code name "Rosario": Database Edition empowers developers to actively participate in, and bring agility to, the database development process. If you work with databases, you will want see how the features offered by the latest addition to Visual Studio Team System (VSTS) integrate the database developer into the Application Development Lifecycle. This exciting product is changing the way database developers work with databases forever, by moving the "One Version of the Truth" for database schema from the production server into source control. This presentation focuses on extending the "Rosario" release of Database Edition to enable non SQL Server databases to plug into the VSTS Database Edition platform. Now database developers can manage their database schema changes for Microsoft SQL Server databases as well as non SQL Server databases within the same environment. In addition to enabling an extension platform for non SQL Server databases, learn also how the Visual Studio Team System code name "Rosario": Database Edition has enabled a database development tools extensibility platform for third-party companies to plug in additional database development tools into the VSTS Database Edition product.

DAT03-TLC Customization of Data Generation Using Microsoft Visual Studio Team System 2008 Database Edition (level 400)

Friday, June 6 10:15 AM - 11:30 AM, Green Theater 1 

Speaker(s): Jamie Laflen

Session Type: Interactive Theaters

Are the data generators that ship with Visual Studio not meeting your needs? Do you need to generate data from outside the development environment? This talk drills into making the most of the 2008 data generation API to answer these and other questions.

The reference projects needed to resolve objects that reside inside an MSDB database have been posted on the MSDN Code Gallery site.

You can find them on: http://code.msdn.microsoft.com/dbpromsdbsamples

We did not post .dbmeta files but stub projects, because .dbmeta files require the collation to match with your environment. This would result in a multitude of files do download. So instead we provided projects, you do need to make sure you set the project collation to match with your own environment. After you have done this you can create your own .dbmeta file if you do not want to reference a project.

NOTE: Since the projects are a stub projects, in other words the content of objects like procedure have been removed, you cannot and should not use these project to deploy to your msdb database!

-GertD

Visual Studio 2005 Team System Database Professional post Service Release 1 (SR1) roll-up package 1, is now available.

For a list of issues that are fixed, please see knowledge base article KB941278.

You can download the roll-up from the Microsoft Connect site at:
http://connect.microsoft.com/VisualStudio/Downloads/DownloadDetails.aspx?DownloadID=11229

-GertD

We have had a lot of requests from users asking us to make T-SQL Static Code Analysis available at build time and from the command line. The latest Visual Studio Team System 2008 Database Edition Power Tool release, adds this ability to the product through a new MSBuild task. In this blog post I will go through the step that you need to follow to enable and use T-SQL Static Code Analysis from the command line.

Step 0: Install the Power Tools

You first need to install the latest Power Tools for Visual Studio Team System 2008 Database Edition.

Step 1: Create a new database project or open an existing one

We need a database project, either use an existing one, or simple create a test project, from example using the pubs database. That is what I will use for this example.

Step 2: Unload the project

We will need to add a entry to the project file to import the MSBuild .targets file for Power Tools. In order to do this you have to unload the project so that you can edit project file using the XML editor. Right click on the project node inside the Solution Explorer and choose "Unload Project".

Step 3: Import the Microsoft.VisualStudio.TeamSystem.Data.PowerTools.Tasks.targets file

The next step is to modify the project file to add an import statement to include the Microsoft.VisualStudio.TeamSystem.Data.PowerTools.Tasks.targets file which contains the MSBuild task for T-SQL Static Code Analysis. Right click on the unloaded project in Solution Explorer and choose "Edit <project name.dbproj>".

This will open the .dbproj file inside the Visual Studio XML editor. Now we need to add an import statement to load the .target file. If you search for "<Import" inside the project file you will find the regular import which loads the normal MSBuild tasks for the database project like the SqlBuildTask and the SqlDeployTask.

   1: <!--Import the settings-->

   2: <Import Project="$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v9.0\TeamData\Microsoft.VisualStudio.TeamSystem.Data.Tasks.targets" />

We need to add a reference to the Power Tools targets file.

   1: <!--Import the settings-->

   2: <Import Project="$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v9.0\TeamData\Microsoft.VisualStudio.TeamSystem.Data.Tasks.targets" />

   3: <Import Project="$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v9.0\TeamData\Microsoft.VisualStudio.TeamSystem.Data.PowerTools.Tasks.targets" />

Step 4: Reload the project

After you made the project change, including the new targets file, it is time to reload the project. Right click on the unloaded project node inside Solution Explorer and choose "Reload Project". This will reload the project.

NOTE: Make sure you have no errors when loading the project, because this will be an indication that your edits were not correct.

Step 5: Start a "Visual Studio 2008 Command Prompt"

The next step is to run T-SQL Static Code Analysis from the command line, in order to do so we need a command prompt that has the PATH set correctly so we can call MSBuild.exe. Visual Studio creates a shortcut in the menu structure that will launch a command prompt with the correct settings.

When you started the command prompt navigate to the directory where you project file is located that you changed in the previous step.

Step 6: Run T-SQL Static Code Analysis from the command-line

From the command prompt run: msbuild pubs.dbproj /t:SqlAnalysis

NOTE: The results file is created in the output directory, which by default is the SQL directory

Step 7: Look at the results

When executing the project using the SqlAnalysis target, an XML file is created that contains the results, named StaticCodeAnalysis.Results.xml. This file contains the information that is normally displayed in the Visual Studio "Error List".

When you open the file in VS it will look like this:

   1: <?xml version="1.0" encoding="utf-16" standalone="no"?>

   2: <Problems>

   3:   <Problem>

   4:     <Rule>Microsoft.Design#SR0010</Rule>

   5:     <ProblemDescription>You should avoid using the following join syntaxes, which have been deprecated: Table1.Id = Table2.Id, Table1.Id *= Table2.Id, Table1.Id =* Table2.Id. You should use the newer syntax, which uses the JOIN keyword.</ProblemDescription>

   6:     <SourceFile>c:\work\pubs\Schema Objects\Stored Procedures\dbo.reptq2.proc.sql</SourceFile>

   7:     <Line>4</Line>

   8:     <Column>14</Column>

   9:     <Severity>Warning</Severity>

  10:   </Problem>

  11:   <Problem>

  12:     <Rule>Microsoft.Design#SR0010</Rule>

  13:     <ProblemDescription>You should avoid using the following join syntaxes, which have been deprecated: Table1.Id = Table2.Id, Table1.Id *= Table2.Id, Table1.Id =* Table2.Id. You should use the newer syntax, which uses the JOIN keyword.</ProblemDescription>

  14:     <SourceFile>c:\work\pubs\Schema Objects\Stored Procedures\dbo.reptq2.proc.sql</SourceFile>

  15:     <Line>4</Line>

  16:     <Column>23</Column>

  17:     <Severity>Warning</Severity>

  18:   </Problem>

  19:   <Problem>

  20:     <Rule>Microsoft.Design#SR0010</Rule>

  21:     <ProblemDescription>You should avoid using the following join syntaxes, which have been deprecated: Table1.Id = Table2.Id, Table1.Id *= Table2.Id, Table1.Id =* Table2.Id. You should use the newer syntax, which uses the JOIN keyword.</ProblemDescription>

  22:     <SourceFile>c:\work\pubs\Schema Objects\Views\dbo.titleview.view.sql</SourceFile>

  23:     <Line>4</Line>

  24:     <Column>15</Column>

  25:     <Severity>Warning</Severity>

  26:   </Problem>

  27:   <Problem>

  28:     <Rule>Microsoft.Design#SR0010</Rule>

  29:     <ProblemDescription>You should avoid using the following join syntaxes, which have been deprecated: Table1.Id = Table2.Id, Table1.Id *= Table2.Id, Table1.Id =* Table2.Id. You should use the newer syntax, which uses the JOIN keyword.</ProblemDescription>

  30:     <SourceFile>c:\work\pubs\Schema Objects\Views\dbo.titleview.view.sql</SourceFile>

  31:     <Line>4</Line>

  32:     <Column>23</Column>

  33:     <Severity>Warning</Severity>

  34:   </Problem>

  35:   <Problem>

  36:     <Rule>Microsoft.Performance#SR0005</Rule>

  37:     <ProblemDescription>You might cause a table scan when you use an expression that starts with "%" in the LIKE predicate. </ProblemDescription>

  38:     <SourceFile>c:\work\pubs\Schema Objects\Stored Procedures\dbo.reptq3.proc.sql</SourceFile>

  39:     <Line>6</Line>

  40:     <Column>73</Column>

  41:     <Severity>Warning</Severity>

  42:   </Problem>

  43:   <Problem>

  44:     <Rule>Microsoft.Performance#SR0007</Rule>

  45:     <ProblemDescription>You have specified a nullable column that is not wrapped with the ISNULL function. This practice can cause the final result to be evaluted as NULL for the predicate.</ProblemDescription>

  46:     <SourceFile>c:\work\pubs\Schema Objects\Stored Procedures\dbo.byroyalty.proc.sql</SourceFile>

  47:     <Line>4</Line>

  48:     <Column>7</Column>

  49:     <Severity>Warning</Severity>

  50:   </Problem>

  51:   <Problem>

  52:     <Rule>Microsoft.Performance#SR0007</Rule>

  53:     <ProblemDescription>You have specified a nullable column that is not wrapped with the ISNULL function. This practice can cause the final result to be evaluted as NULL for the predicate.</ProblemDescription>

  54:     <SourceFile>c:\work\pubs\Schema Objects\Stored Procedures\dbo.reptq3.proc.sql</SourceFile>

  55:     <Line>6</Line>

  56:     <Column>7</Column>

  57:     <Severity>Warning</Severity>

  58:   </Problem>

  59:   <Problem>

  60:     <Rule>Microsoft.Performance#SR0007</Rule>

  61:     <ProblemDescription>You have specified a nullable column that is not wrapped with the ISNULL function. This practice can cause the final result to be evaluted as NULL for the predicate.</ProblemDescription>

  62:     <SourceFile>c:\work\pubs\Schema Objects\Stored Procedures\dbo.reptq3.proc.sql</SourceFile>

  63:     <Line>6</Line>

  64:     <Column>27</Column>

  65:     <Severity>Warning</Severity>

  66:   </Problem>

  67: </Problems>

Step 8: Clean up the results

If you want to clean up the resulting file produced by the static code analysis run, simply run:

msbuild pubs.dbproj /t:ScaClean

This will remove the results file.

Conclusion:

Following the steps outlined before enable you to run T-SQL Static Code Analysis from the command line, you can use the same step to integrate this in to your Team Build environment, but you have to make sure that you install the Power Tools on the build server before you can use this.

-GertD
"DataDude" Group Engineering Manager

VSTS-DB, both the 2005 and 2008 version, makes an assumption with regards to database compatibility levels, which can cause problems in certain use cases.

The assumption is that the DesignDB instance used for validation of the schema inside the project is set to a 80 compatibility level when using a SQL Server 2000 project and to 90 when you are using a SQL Server 2005 project. If you have a database schema that was migrated from SQL Server 2000 to 2005 and is relying on the fact that the database compatibility level must be set to 80 to keep your code running, this will cause TSD4001 errors when you import/open that schema in to a SQL Server 2005 project and ultimately prevent you from building and deploying your project, since TSD4xxxx are always errors, which come from the DesignDB instance.

Lets look at two common cases that some of you have reported that cause problems:

The above list examples are not exhaustive and do not represent the complete list of difference in database compatibility levels. Please see the SQL Server Books Online topic on sp_dbcmptlevel, it contains a list of differences. (Look for the label "Differences Between Lower Compatibility Levels and Level 90".)

Resolution:

So what do you need to do when you are running in to this problem? First you need to determine which version of Visual Studio Team System Database Edition you have and if you need to install the latest QFE or not.

Visual Studio 2005

If you are using the Visual Studio 2005 based version, you need to make sure that you install the latest QFE roll-up, which is documented in the following KB article:

KB 941278:  Visual Studio 2005 Team System Database Professional Post Service Release 1 (SR1) Rollup Package #1

Visual Studio 2008

If you are using a Visual Studio 2008 RTM based version, you are OK and do not need to install an update; however if you are using a Visual Studio 2008 pre-release version (CTP, beta or RC) you do need to update to the RTM version, since the fix went in at the last moment.

Workaround

Now that you have identified which version you need, how do you work around the problem? We have added a way to set the database compatibility level inside the project, however there is no UI support to set this option. You need to follow the following steps.

Step 1: Unload the project

We will need to add a property to the project file, in order to do this you have to unload the project so that you can edit project file using the XML editor. Right click on the project node inside the Solution Explorer and choose "Unload Project".

Step 2: Add the CompatLevel property to the project file

The next step is to modify the project file to add the CompatLevel property.

Right click on the unloaded project in Solution Explorer and choose "Edit <project name.dbproj>".

This will open the project file inside the XML editor. You need to make sure you are adding the property to the right configuration block. By default there is only one configuration named 'Default'. Which is declared at the top of the project file, below you see the first 18 lines.

   1: <