So I’ve been using Visual Studio for Database Professionals (aka VSDBPro, aka DataDude) consistently this year for primarily new database development, both for Data Warehouses and OLTP Databases.  This will be the first in what hopefully will become a series of posts about Database projects.

In Database Professional there is are two types of projects, the database project and the database server project.  The database project would be the user database while the database server project would be the master database.  Neither one is specific to a particular server or instance, it’s a Visual Studio project which can be deployed on a single or multiple instances as you need.

You can also create dependencies between projects so you can have a database project which had a dependency on a database server project.  Why would you do this you ask?  The most straight forward example would be the login to user relationship.  You could have a database server project which creates your logins, and a database project which then creates the users in that user database mapped back to the logins in the server project.

You can reference a database project one of three ways:

1. Project within the solution
2. .schema file (DB projects don’t create .dll files)
3. Server and database variables

The first two are pretty straight forward as you would just select a project or a .schema from your workspace, so let’s look at how you would utilize variables for reference.  First here’s the screen to create a dependency:

Easy enough, just enter the variables and you would have:

You’ll notice in the variables the characters %(), this is what tells the Project that these are variables, and I didn’t enter the variables this way, I just entered ServerName and when I tabbed out of the filed the $() were automatically added.  So now you can see the variables in your command variables dialog (under project properties).  The first two are created with every project and you’ll see since I specified a value up above, the value is already used, instead of needing to set it at deployment time.

So now that we have the variables, how does this actually get used when we go to deployment?  I’m glad you asked, on deployment a .sql file is created that utilized sqlcmd mode.  So the output at deployment time time will be:

Notice that there is no value for the DefaultDataPath, as I never did set it at deployment time.  So there you have it, you can now parameterize your project dependencies!

So the other day I was working on a T-SQL query that I was using to take data from generic data types and insert it into a strongly typed table.  One of the columns was moving from an nvarchar(255) to an decimal data type.  As some of the rows had character data in it, I wrote something like this:

CASE ISNUMERIC(Col1)
    WHEN 1 THEN CAST(Col1 as decimal(9,4))
    ELSE NULL
END as Col1

However when I ran the query, I received this error:

Error converting data type nvarchar to numeric.

It took me awhile to figure this one out as you can see I”m using ISNUMERIC to check before I convert it to a number, seems pretty straight forward, so what’s the bid deal?

The big deal is that I needed to read the manual…according to the ISNUMERIC function in BOL:

ISNUMERIC returns 1 for some characters that are not numbers, such as plus (+), minus (-), and valid currency symbols such as the dollar sign ($). For a complete list of currency symbols, see Using Monetary Data.

It turned out I had a row that had a value of a single decimal point and no numbers which caused the problem.  So remember, understand what the functions you use actually do, not what you think they do and test!

Being that I have been involved in a project since the first of the year that pretty much drained me week-to-week, I sadly wasn’t able to keep up with my blog.  However now that I’ve moved onto to a new project I plan on getting back to posting blogs again. 

I continue to be very grateful to the SQL community for its hard work and support (I loved 24 Hours of PASS and the impromptu associated ustream – 24 hours of LaRock) and hopefully I can give back to the community a portion of what you all have given me.