THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

“Add as object” or “Add as script”? [SSDT]

In SQL Server Data Tools (SSDT) one way to create a new object is to right click on a folder in Solution Explorer and point to Add. Doing so will display this menu:

image

A number of options are offered for creating a brand new object (“function”, “table”, “stored proc” etc…) and one of them is “Script”. If your preference is to handcraft the DDL for every database object without resorting to a GUI then you may choose to use the “Script” option because, after all, every file is just a DDL script – why not write the thing from scratch? There is however one fundamental difference between the Script” option and all the other options that you should be aware of.

For the screenshot below I added a script and handcrafted a simple CREATE TABLE statement:

SNAGHTML760417d0

Notice that the table is defined in the dbo schema but it is not appearing in SQL Server Object Explorer under the dbo schema node. Why is that? The answer is actually pretty simple. If I select Script1.sql in Solution Explorer and hit F4 the properties window appears and we see this:

image

The Build Action property of the file is set to “None” which means that the file does not get included in a build and hence any objects defined within that file will not appear in our database. This is the default behaviour when you Add as script whereas the default behaviour when you explicitly add an object is to set Build Action=Build. That is the fundamental difference between adding a script and adding a specific object.

This is one of those nuances of SSDT that is an easy problem to spot when you know about it but can cause you to spend hours hunting around for a solution if you do not (believe me, I’ve been there). Be aware of it!

@Jamiet

Published Friday, January 04, 2013 3:15 PM by jamiet
Filed under: , ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Kyle Hale said:

When I right click and choose Add Script, the Add New Item dialog box gives me 4 options in SSDT:

Post-Deployment Script

Pre-Deployment Script

Script (Build)

Script (Not in Build)

And as you can imagine, choosing each one changes the Build Action property accordingly.

Do you not get that dialog when you choose Add->Script?

January 4, 2013 11:28 AM
 

jamiet said:

Kyle,

Good point, yes, you do get those 4 options. My point, not explained too well I guess, is that if you choose the default option (which I suspect many will do - because I've seen many colleagues do it) then it won't be in the build.

Thanks for allowing me to clarify.

-J

January 4, 2013 11:35 AM
 

AlexK said:

This is a typical example of GUI killing productivity. The task of coalescing text files into a single script and/or building a database is trivial - it is about 100 lines of C# code in two simple classes. It took me less than an hour to write, and it just works without issues for several years. It would probably take me more time to write a blog post like yours than to develop a working solution from scratch in C#. More to the point, if I want to change the way I work with sql scripts, I can do it right away...

January 4, 2013 4:01 PM
 

Bill Anton said:

interesting approach. I also prefer handcrafting the DDL for each object...and the table designer GUI is a pain in the arse.  Instead of starting with a script object, I change the default editor from...

Microsoft SQL Server Data Tools, Editor Selector

to...

Microsoft SQL Server Data Tools, T-SQL Editor

To do this, just right click on a table file in the object explorer and select "Open With"...then click/select the "Microsoft SQL Server Data Tools, T-SQL Editor" and set as default.  Screenshot: http://byobi.com/blog/wp-content/uploads/2013/01/Open-With....png

January 11, 2013 5:22 PM
 

Chuck said:

The instructions you gave on how to take a script out of the build were spot on. I kept trying to do this from the context of the editor and never got these properties. Thanks for clarifying that you need to F4 from the item in the solution explorer!

C

August 8, 2013 10:22 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement