Home of the Data Dude

Schema View for SQL Server Data Tools

2012-04-02T18:29:47Z

Today the team released the first version of the SQL Server Data Tools – Power Tools, which adds the ability to navigate the schema of your project inside the SQL Server Object Explorer. Blog post detailing the release: http://blogs.msdn.com/b/ssdt/archive/2012/04/02/first-release-of-ssdt-power-tools.aspx SSDT Power Tools installation location: http://visualstudiogallery.msdn.microsoft.com/9b0228c6-15d1-44de-9279-66dde12bf861?SRC=Featured Please check it out, @DataDude...(read more)

SQL Connections Spring 2012

2012-03-23T21:17:43Z

Next week, at the SQL Server Connections conference, I will be presenting “Database Development with SQL Server Data Tools”, a 60 minute overview how you can leverage SQL Server Data Tools (SSDT) for your daily database development and deployment tasks. When: Tuesday March 27th 2012, 10:00-11:00AM Abstract: SMS01: Database Development with SQL Server Data Tools Gert Drapers SQL Server Data Tools (SSDT) transforms traditional database development by introducing a declarative, model-based experience...(read more)

SQL Server Data Tools Released, Available Today!

2012-03-07T01:43:07Z

Today we released the first official release of the “SQL Server Data Tools” (SSDT) to the web. Even though this release accompanies SQL Server 2012 RTM, you can use SSDT against SQL Server 2005 or later, including SQL Azure. So download it now! and get started developing and deploying your SQL Server databases using SQL Server Data Tools....(read more)

SQL Server Developers Tools code-named “Juneau” becomes SQL Server Data Tools (SSDT)

2011-10-17T19:00:00Z

Last week at the SQL PASS SUMMIT 2011 in Seattle, Ted Kummert announced during his keynote that the final name of SQL Server Developers Tools code-named “Juneau” will be SQL Server Data Tools or SSDT for short. Additionally, the final name for SQL Server “Denali” will be SQL Server 2012. Besides the final name there were other important data points shared at the PASS Summit regarding SSDT: SSDT will be broadly available as a free component of the SQL Server platform for all SQL Server users. SSDT...(read more)

VSDB Common Pitfalls 1

2010-12-19T02:08:26Z

Symptom: The resulting database deployment script always creates the database This seems to be a common problem users are struggling with like illustrated by the following set of forum post . A user creates a database project, imports an existing database, builds and deploys. The resulting script always performs a CREATE DATABASE, why? The answer is very simple, by default there is no target database connection defined, the deployment detects this and instead of failing, it assumes the target database...(read more)

Let’s pick up blogging again…

2010-12-19T01:34:40Z

Sorry it has been really quiet on this blog for a while now, new job and new projects get in the way. However when going through the MSDN forum for Visual Studio Database Projects it seems there is a need for more hands-on examples and understanding about how the system works and behaves. Besides that it seems that the documentation is either missing, unclear or unread about many things, since it seems that everybody is having to solve many of the problems over and over again. Therefore it seemed...(read more)

DefaultDataPath

2009-12-01T01:48:10Z

VSDB uses the the ($DefaultDataPath) SQLCMD variable to represent the location where you would place your data and log file of your database. The deployment engine sets the value of SQLCMD variable by querying SQL Server using the following query: 1: DECLARE @ value nvarchar(512), 2: @rc int ; 3: EXEC @rc = [master].[dbo].[xp_instance_regread] N 'HKEY_LOCAL_MACHINE' ,N 'Software\Microsoft\MSSQLServer\MSSQLServer' ,N 'DefaultData' , @ value output -- , 'no_output'; ...(read more)

SQL Server 2008 R2

2009-11-24T21:26:20Z

Now that SQL Server 2008 R2 November CTP is available, I found some time to install it and test the existing Visual Studio 2008 Team System Database Edition GDR R2 release with it. The only thing that is really different from a SQL Server point of view besides two changes to the SQL parser, is that the version number changed from 10.00.xxxx to 10.50.yyyy (10.00.2531 for the November CTP). As we will see enough to confuse the VSDB 2008 GDR R2 release. First step, is to create a connection inside Solution...(read more)

Pre and Post Deployment Events

2009-10-13T21:33:39Z

Visual Studio makes it relatively easy to hook in to pre and post build events, by using the Project properties tab named Build Events. However when using Database Projects (.dbproj) you more often need to do something at deployment time. The good news is that the standard MSBuild framework, already defines these events, the bad news is that you manually have to update the project file yourself. The steps are pretty simple: Unload the project, by right clicking on the project node inside Solution...(read more)

Pre-Deployment Scripts

2009-09-14T23:48:00Z

Pre-deployment script do not change the outcome of deployments! The above statements is something that most users do not realize when using pre-deployment scripts. Since the pre-deployment script is included in to the deployment script, it therefore by definition runs after the comparison the the source model with the target database (model), and therefore cannot change the behavior of the deployment. For example: If you know you have an orphaned object that is blocking a certain upgrade scenario...(read more)

NESQL Presentation – Declarative Database Development

2009-09-14T21:57:03Z

Last week, September 10, I was invited by the New England SQL Server User Group to present about my favorite topic “ Declarative Database Development ”. I posted the slides of the presentation at DBProj.com . GertD @ www.DBProj.com...(read more)

MyDevConnections Volume 2 issue 1

2009-09-10T06:26:43Z

The DevConnections conference organization (Tech Conferences Inc.), just released the latest issue of the magazine accompanying the conferences, named: MyDevConnections . In this edition (volume 2, issue 1) you will find an article that I wrote about “Declarative Database Development – Define What You Want, Not How You Get There!” (see page 68-71). If you did not receive the printed edition of the magazine, don’t worry since you can download the PDF version. During the Spring 2009 SQL Connections...(read more)

SqlCmdVars.exe

2009-08-02T03:46:53Z

This post announces the availability of a small helper utility that will allow you to maintain your database.sqlcmdvars files from a command line tool. Description: What is the scenario? If you are using vsdbcmd.exe to deploy your schema, you include the Database.sqlcmdvars, however vsdbcmd.exe does not have an option to override the values of the SQLCMD variables used at the command line level like you can do when using MSBuild (assuming you chained the SQLCMD variable to a MSBuild property). This...(read more)

VSDB 2008 Version Numbers

2009-07-30T01:37:42Z

We frequently get asked how to identify the version a user is running with, so here is the list of Visual Studio Team System 2008 Database Edition releases. The information is retrieved using Help => About Microsoft Visual Studio inside the Visual Studio shell (devenv.exe). Alternatively you can copy the information to the clipboard using the “Copy Info” button. When you are on the latest versions it should look like this: Release Visual Studio version information Database Edition...(read more)

VSDBCMD.EXE Return Codes

2009-07-21T23:09:31Z

Quick one based on a forum question where somebody asked how to detect if VSDBCMD.EXE failed or succeeded inside a batch file. VSDBCMD.EXE does not return a very elaborate amount of information, there are just two return values 0 and 1, where zero indicates success and 1 failure. So in order to test for this you simply check the ERRORLEVEL inside your batch file. This is a simple wrapper that I use that shells out to VSDBCMD.EXE and passes all the parameters, so I do not have to place the Deploy...(read more)

Agile Database Techniques

2009-07-09T03:16:52Z

I am happy to announce that there is a new class dedicated to VSDB. “ Agile Database Techniques ”, is a 3-days course that provides students with the knowledge and skills to properly manage the SQL Server database development lifecycle in an agile environment. You will learn how to manage changes to the database structure, ensuring quality through T-SQL unit testing, and how to automate the building and deploying of SQL Server databases. Intended for developers and administrators already comfortable...(read more)

Assigning MSBuild Properties to SQLCMD Variables

2009-06-22T02:21:44Z

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: Add the variable to the Database.sqlcmdvars file Add a XML fragment to the database project (.dbproj) file Use the SQLCMD variable First you need to add a SQLCMD variable to the Database.sqlcmdvars file, in this example we will...(read more)

DBSchema file for SQL LiteSpeed XP’s

2009-06-11T03:10:38Z

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: Extended Stored Procedure name [dbo].[xp_append_file] [dbo].[xp_backup_database...(read more)

System Objects in TEMPDB

2009-06-11T02:17:09Z

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...(read more)

Declarative Database Development

2009-06-06T01:58:36Z

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...(read more)

RTM of VSDB 2008 GDR R2

2009-04-22T18:32:12Z

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...(read more)

Release Candidate of GDR QFE

2009-03-27T04:31:16Z

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...(read more)

SQL Connections Slides and Demo Posted

2009-03-26T23:55:54Z

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. SDB413: Automating Database Deployment Do you have a need to automate the deployment of your database...(read more)

DBPROJ vs. DBP

2009-03-25T15:52:09Z

Can the “real” database project please identify 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 many releases and is purely there for legacy support....(read more)

Up to SQL Connections Spring 2009 (Orlando, FL)

2009-03-20T21:46:25Z

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...(read more)