Schott SQL

SSDT: Setting Different Permissions per Environment

noreply@blogger.com (Peter Schott) — Tue, 14 May 2013 20:16:00 +0000

One of the areas that SSDT doesn't address adequately is permissions. Many users have different users, logins, and permissions set up across their environments, but SSDT takes a "one size fits all" approach and assumes that you will have the exact same configuration in each environment. While that should be the case for your schema, this area requires some interesting and somewhat involved techniques to handle well in SSDT.

Attribution - I'm borrowing heavily from Jamie Thomson's ( blog | twitter ) post

A strategy for managing security for different environments using the Database Development Tools in Visual Studio 2010

Jamie addressed this problem for the older DB Projects (Datadude, VSTSDB, DBPro, etc.). He came up with an excellent, if somewhat complex, solution to effectively handle pushing different permissions to different environments. This is my attempt to update this and distill it into a "how to" for SSDT and SQL Projects, but I freely admit that this is based on Jamie's excellent work.

In general, the steps we'll want to take are:

Generate a set of SQL Logins that will be used on the server.
1. These should be run on the servers or local box already - we will not use these within the SQL Project itself though you can if you wish.
Generate the files for users/roles/permissions for each database project and environment.
Add the files to your SSDT SQL Project
Create and set a variable for your environments in your SSDT SQL Project.
Adjust your Security Wrapper file to handle your environments.
Adjust your post-deploy script to call the Security Wrapper.

First, let's look at what happens when you import Permissions by default into an SSDT SQL Project.

As you can see from the screenshot, we have a "Permissions" file containing granted permissions, a "RoleMemberships" file that adds users to various roles, a "Sales" file for the Sales schema, and 3 files each for the users - one for its schema, one for its login, and another for its user. Confusing, but not necessarily a problem until you need a release where User1 does not and will not exist on the server or where User2 belongs to a different domain than your production servers and can't and should not be created there because there's no trust relationship.

Let’s remove all of those files for objects other than Schemas from the project before we start. You’ll probably have to look at each file to verify before removing it from the project if you’re not sure which file is which. If you accidentally remove the files for the schemas, you’ll get errors and need to re-add a “Schema” to this security folder for each schema that is used.

Generate SQL Logins

This Microsoft KB article details the sp_help_revlogin stored procedure. Create this on your source database, run it, and use the output to re-create those logins elsewhere. This will copy all logins and users, but no permissions or database users.

Generate the Security Files through PowerShell

First, download the Powershell_GeneratePermissions.zip file. Extract that to a folder on your hard drive.

Note - the script assumes that you will use Windows authentication to connect to your SQL Server. The script will need to be adjusted if you need to use SQL Authentication.

Edit the "GeneratePermissions.ps1" file. Replace the "DB1", "DB2", etc with whatever database names you'll be using. Also uncomment any of the extra lines that you'll need in the file. (There is likely a better way to do this in Powershell and I'm more than open to some suggestions on how to build up this array in a more concise manner.)

The assumption is made that the databasename == the projectname. If that's not the case, adjust your DB/Project names accordingly.

Open a PowerShell prompt in that folder after editing/saving the file.

Run the file using something like this commandline:

.\GeneratePermissions.ps1 -SQLInstance "localhost" -Environment DEV

"localhost" should be replaced with your servername

DEV in this case indicates that this will be tied to a variable value of "DEV" when the project is published.

I'd recommend running this for every environment at one time if you can. It will make adding the files to the project much easier.

If you run for several environments, you'll see a folder structure something like the following for each project (in this case for Adventureworks2012):

Inside your "SecurityAdditions" folder, you'll see the following files and folders:

Each of these files will be used to add permissions for each environment. In this case, we have permissions set for our Development, QA, Local, Production, and Staging environments.

Now, let's add the "SecurityAdditionsWrapper.sql" file from the files we extracted earlier into your "SecurityAdditions" folder. Feel free to edit it as needed to match the environment(s) you want to use for your database project. By default, it's set to look for the above files plus a "Default" file to be a catch-all. Adjust that so all files/environments match the files you created and save it.

Add the files to your SSDT SQL Project

Copy your newly created folder(s) to the appropriate project. Open each project, and you will notice that the files still don't show up. Click the project file. You should see a menu item that's now enabled to "show all files".

You'll see a bunch of files that aren't part of the project under a "Scripts" folder:

We want to add them to the project. Right-click the "Scripts" folder and select the option to "Include in Project". Click the Project file again and turn off the option to show all files.

We need to set all file properties to "Not in Build" or there will be errors when trying to build the project.

Unfortunately, we can't select just the folder to exclude from the build. We need to select the files. You can ctrl+click to select multiple files. Select all of the files, open the Properties tab, and change the option from "Build" to "None"

Create and set your Variable to change environments

Open the Project Properties window

We want to change the SQLCMD variable to add a new variable called "DeployType". Set it's default value to "Local" for now, or whatever environment for which you have a set of security scripts.

Adjust your Security Wrapper files

The SecurityAdditionsWrapper.sql file in the code set I've created comes with settings for "Dev", "QA", "Staging", "Production", "Local", and "Default". It's likely that you will not use all of these in your setting. One drawback to using SQLCMD to wrap all these files together is that if you don't have a file or set of files, the script will start throwing errors because an expected file doesn't exist. Edit the SecurityAdditionsWrapper.sql file to remove any of the configurations you will not use or change the names to match the environment values you used earlier.

Adjust your Post-Deploy script

Finally, we need to open your Post-Deploy script.

Add the following to your script:

:r .\SecurityAdditions\SecurityAdditionsWrapper.SQL

This will include your SecurityAdditionsWrapper file as part of your post-deploy process. See my earlier post on selectively running pre/post deploy scripts if you want finer control.

Trick to Not Run Pre/Post SQL on Publish

noreply@blogger.com (Peter Schott) — Tue, 14 May 2013 20:06:00 +0000

We’ve run across the need to avoid running certain scripts when publishing a brand new copy of a database from a SQL or DB Project from time to time. Often, those pre/post deploy scripts are designed to be run only against an existing system and don’t make sense for a brand-new, empty database.

We’ve worked around this by using a SQLCMD variable called DeployType and setting that variable to “New” for any build operation that will be used only to create the initial database. The code snippet for our Post-Deploy script looks something like the following:

-- Place scripts here if they should only run for existing/working DB
IF ( '$(DeployType)' <> 'New' )
BEGIN --Run scripts
PRINT 'Deploying scripts.'
END

--Security - runs last
:r .\SecurityAdditions\SecurityAdditionsWrapper.sql

The above script will look at the DeployType variable. If it’s set to “New”, it will ignore all of your deploy scripts in that block. If it’s set to any other environment, it will run the appropriate scripts if any exist. You can use this to control which environments will use certain scripts as well.

SSDT, Publishing, and Referenced Databases

noreply@blogger.com (Peter Schott) — Thu, 24 Jan 2013 00:06:00 +0000

Had this catch me today in a series of databases that all work together. In order to resolve all of the cross-database dependencies, I've added database references to the project. That takes care of all of the "Database.dbo.Object" references outside of the database. The problem is that within the current database, you will get errors without that reference defined.

In my haste to get the projects working, I added a database reference to the current project. I would build my project and reference the generated *.dacpac files when publishing. The problem is that every time I built my project, the referenced dacpac file would overwrite the generated dacpac file.

Results from this problem - I noticed that my "DeployType" variable wasn't referenced anywhere, which struck me as odd because I use it in my Pre and Post Deploy scripts. I also started noticing that my dacpac file in my build folder was out of date, even after a successful build.

Workaround - do what I have done otherwise - replace all instances of CurrentDatabaseName.dbo. with just dbo. in all SQL files in the project. This also includes the various forms that this can take:

Databasename.dbo.
Databasename..
[Databasename].[dbo].
etc

Once I replaced all of those references and rebuilt the project successfully, my publish actions started working again. My Deploy scripts were included. I didn't get mysterious warnings about my variables not existing.

There may be a different way to work around self-referenced database objects within SSDT, but until I come across that, the workaround is to remove them from the code and not to just add a reference to the current database through a dacpac file.

SQL Server: Problems with corrupt files or assemblies

noreply@blogger.com (Peter Schott) — Wed, 14 Nov 2012 17:53:00 +0000

Yesterday I found and fixed a bunch of hard drive issues. One of those issues resulted in my “Microsoft.AnalysisServices” assembly for SQL Server 2012 becoming corrupt. That in turn resulted in VS2010 throwing errors when I tried to use SSDT or do just about anything else. My exact error message was:

Could not load file or assembly 'Microsoft.AnalysisServices, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The module was expected to contain an assembly manifest.

There was an MS help link as well, but it basically took me to a page that thanked me for letting them know they needed to work on their documentation. It was a little less than helpful.

I tried several things to fix the problem.

Re-apply SP1.
Uninstall just Analysis Services.
Repair SQL 2012
Uninstall SQL 2012 (This mostly worked except I still had issues with SSAS)
Reinstall SQL 2012 (This worked, but still had issues with Analysis Services)

All of the above resulted in the same error message at some point and a non-working install of SSAS 2012.

I finally came across an article that talked about fixing the Global Assembly Cache. While not everything applied directly, it did get me started.

I tried copying the gacutil files as mentioned in the article. This didn’t work. The program ran, but didn’t do anything. I had to use the actual location for gacutil.exe in order to do anything. This can be found in “c:\Program Files (x86)\Microsoft SDKs\Windows\” You’ll need to choose the appropriate version for your OS as well as the appropriate choice for x32 or x64. In my case, it was Windows 8. My full path to gacutil.exe was “C:\Program Files (x86)\Microsoft SDKs\Windows\v8.0A\bin\NETFX 4.0 Tools\x64”
I had to find my physical Microsoft.AnalysisServices.dll file, found under “C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\SQLServer2012\x64”
After that, I was able to run gacutil -if “C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\SQLServer2012\x64\Microsoft.AnalysisServices.dll”

After running that command, I uninstalled and re-installed SSAS under SQL 2012 using the standard add/remove features and everything is now working. I don’t know if anyone will encounter a similar problem with a bad assembly or assembly manifest, but hopefully this will help someone.

SSDT: Tips, Tricks, and Gotchas

noreply@blogger.com (Peter Schott) — Mon, 12 Nov 2012 13:00:00 +0000

I wanted to add a short post to make sure I highlight some things that will trip people up or otherwise cause issues.

When setting trigger order for triggers on a table, you could run into an issue with Publishing your database. The project will build successfully, but throw a "Parameter cannot be null" error for parameter "Key". This is an internal bug with the product as of at least SQL 2012 CU2. It's been entered as an internal bug within MS with a workaround. To workaround the issue, do not try to set the trigger order in the table definition, but rather put this code in a post-deploy script.

This is supposed to be fixed in a future release of SSDT so your experience may vary.

Importing encrypted objects may fail. If you want to store encrypted objects in your project, you'll likely need the code in order to create them in the project.
Unlike the Schema Compare option in VS 2010, there is no way to set the default Schema Compare options nor a way to filter out "skipped" objects in the schema compare.

I’d love some ideas on how better to handle this. You may be able to save the compare within the project for future re-use, but I had little success with this in VS 2010 Projects so have been reluctant to try that route again.

If your database uses FILESTREAM, you will not be able to debug using the default user instance that SSDT provides. You will need to point your debugging instance to a SQL Server install that supports FILESTREAM. See this forum post for more details.

Set this in the Project Properties and point to an actual instance of SQL Server that supports the feature(s) you want to use.

SQL Projects will substitute CONVERT for CAST in computed columns at the time of this writing. They also push default constraints with extra parentheses around the default value. If you clean these up from the target server, be aware that on your next Publish action, you could end up with extra work being done to make your target database use those parentheses or the CONVERT function.

To work around this, do a SQL Schema compare after a release to find any areas where the schema on the server differs from that in the project. For instance you may see a DEFAULT (getdate()) in your server, but have DEFAULT getdate() in your project. Add the parentheses to your project to avoid unnecessary changes.

Do you have any tips to share? Add them to the comments below.

SSDT: SQL Project Snapshots

noreply@blogger.com (Peter Schott) — Fri, 09 Nov 2012 13:00:00 +0000

SSDT allows for snapshots to be taken of the project at any point. Just right-click the project name and select the option to "Snapshot Project".

This builds a dacpac file into a folder within the project called "Snapshots" with a default name format of Projectname_yyyymmdd_hh-mi-ss.dacpac.
This file contains a build of the project at the time the snapshot was taken, including all objects and scripts.

Uses (by no means a complete list)

Save a specific version of your project to use for release
Save this version of the project before making changes to the underlying project
Use as a source for schema compare
See the differences between snapshots and/or the current project through schema compare
Baseline your project
Roll back to this state
- Schema Compare with the snapshot as the source and the Project as the target
- Import into a new project with this as the source.
- Import into current project, but be aware that this could easily produce a lot of duplicates

SSDT: Publishing Your Project

noreply@blogger.com (Peter Schott) — Thu, 08 Nov 2012 13:00:00 +0000

Build the project

In order to successfully publish your project, it must first be able to build successfully.

Start by building your project. Right-click the project and select "Build".

If the build is successful, the project can be published.

You may want to create a folder within your project to store saved Publish Profiles. These can be used later to easily publish the project to your servers.

Creating Publish Profiles

Right-click the project and select Publish. This will bring up the Publish Database dialog.

Choosing to publish or opening a saved publish profile will initiate a build of the project.

Choose your target database, set your advanced options (similar to Schema Compare options), and choose the "Save Profile As" option to save this to a location within your project. Selecting the "Add profile to project" option will create the publish profile in the root of the project. You may wish to either move the file to a folder storing all of your publish profiles or, if you saved it without adding to the project, show all files of the project so you can include the file in the project.

Some options you may want to consider:

"Always re-create database" - this will re-create the database. Any data in the database will be lost.
"Block incremental deployment if data loss might occur" - If there are any changes that could result in the publish action failing because of data loss, this option will stop the script from running.
"DROP objects in target but not in project" - This will remove anything in the database that doesn't exist in the project. Useful if you want consistency, but you may want to ensure this isn't checked if there could be objects in the database that were created, but didn't make it to the project.

Under the "Advanced Deployment Options"

Allow Incompatible Platform - Useful if you may publish to a different version of SQL Server than the one specified in the project
Include transactional scripts - Will run the entire update operation as a transaction. If any one part fails, the transaction will roll back. If you have cross-database dependencies, selecting this option could result in no changes being published if you're publishing to a new server. For a new publication, you may want to de-select this option to ensure a successful deploy of what can be published.
Script state checks - This option will ensure that the publish action will only work on the specified server and database.
Verify deployment - Checks the database and project before publishing to try to ensure there are no changes that will cause problems with the publication such as missing data for a foreign key.

Using Publish Profiles

Once you've set up your publish profiles, you can easily use these to push changes to that server and database without needing to specify additional parameters. The easiest way to use them is to double-click the Publish Profile within the project and choose to either "Generate Script" or "Publish".

Generate Script will generate a script for you to use to update the target at a later time (run in SQLCMD mode).

Publish will immediately attempt to push the changes to the target.

You can also use these at a later point to push changes through the SQLPackage.exe command line.

SQLPackage

To publish your package through a command line we use something like the following:

Code Snippet
 sqlpackage /a:publish /sf:.\sql\Local\Adventureworks2008.dacpac /pr:.\Publish\Local.publish.xml
 

The above will:

Use the "Publish" Action
Use the Source File named Adventureworks2008.dacpac, built in the sql\Local folder
Use the publish profile named "Local.publish.xml" (defined to push to the local SQL Server)

You may want to add SQLPackage.exe to your path. By default it is installed in:

C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin

You can override quite a few of the default settings through various command line arguments. This includes source, target, and variables. You can find a full list of the command line arguments at the SQL Package reference online.

Jenkins Automation for CI

We use Jenkins at my current workplace and set up a Jenkins job to do the following (With thanks to Matthew Sneeden for the assistance.):

Get the latest from our mainline repository
Build each SQLProj file.
- Building the SLN file will result in also attempting to publish the database
- msbuild .\Adventureworks.sqlproj /t:build /p:Configuration="Local"
  - This assumes that msbuild.exe is in your path.
- Configuration is mostly to control the location of the dacpac file generated.
Run SQLPackage w/ a specified Publish Profile for the appropriate environment and using the newly built dacpac as the source.

We are currently investigating how we can use Snapshot files to better control releases to our UAT and Production environments. This series will be updated when that information is available.

SSDT: Errors and Warnings

noreply@blogger.com (Peter Schott) — Wed, 07 Nov 2012 13:00:00 +0000

SSDT includes an Errors and Warnings window that is well worth your attention. Ideally, your project should have no errors or warnings.

However, sometimes coding errors slip in to your project or you get warnings that an object can't be found because it exists in another database. Sometimes a warning might appear because an object is missing completely, in this project or another one. In these cases, it's well worth checking this section to find out where you may have some issues.

Warnings are not necessarily a problem. SSDT will bring possible issues to your attention, but warnings will not stop a project from building and publishing unless you have set the option to treat warnings as errors or unless there really is an underlying problem that causes an issue during the publish phase.

For example, if I modify the Person.Address table to add a new column in the code, but forget to add a comma, I'll get an error something like this.

If you double-click on the line, the editor should open the appropriate file and take you pretty close to your problematic line. Correct the problem, save the file, and move on to the next error.

Some common warnings/errors

4151 - Unresolved database reference. This is often caused when one of the objects in a database references another. This can often be resolved by creating a database reference.
71562 - Unresolved database reference warning.
71502 - Another unresolved database reference warning.

How to globally suppress certain warnings

Right click the root of the project and select properties
Click on the "Build" tab
Enter in the numeric portion of the codes, separated by commas. Remove the "SQL" and any leading zeroes when entering the code.

SSDT: Updating a Project Manually

noreply@blogger.com (Peter Schott) — Tue, 06 Nov 2012 13:00:00 +0000

Sometimes it's necessary to modify the project manually. The change might require specific tweaking to include just a couple of new lines in a stored procedure or function. It might be just adding a column to a table. Maybe you know exactly what change needs to be made and would rather just edit it manually instead of comparing or importing. Whatever the reason, updating your project manually can be done without too much trouble.
If you know the name and location of the file you want to edit, just go straight to it and right-click it.

You have two ways to edit the file - using "View Code" will bring up a T-SQL script. Edit as you would any other T-SQL script and save. Remember that SSDT scripts create all base objects using CREATE scripts.
If you choose to View Designer, you'll see a new screen combining a design view with a T-SQL editor.

Here you can choose to edit within either window. The code will be kept in sync across the panels. You can right-click any of the Keys, Indexes, Triggers, Constraints in the upper window and choose to add a new one. You'll get a shell of the script to create a new object tied to this table. Modify its code to match what you want to do and save the file.

This is different behavior from the older Database Projects. Those would create separate files for each object by default. In SSDT, the scripts are all put together in one script unless you upgraded from a DB Project.
The only place SSDT supports "GO" to break up the batches is within these create scripts. You cannot use GO in a post or pre deploy script.
If you highlight a column in the table, you can give it a description in the Properties window. This is an easy way to propagate object/column descriptions into your SQL Server.
You can select the table's properties in the Properties window dropdown to edit its properties/description.

SQL Server Object Explorer
If you hit the F5 key inside of a project to debug the project, SSDT will build a local database, often hosted in an instance called (localdb)\Databasename. This will run the database project in a debug session. If you then open the SQL Server Object Explorer view, you can edit the project and underlying scripts.

This works in most instances where you’re using basic SQL Server functionality. If you’re using any more advanced features such as Filestream, you’ll want to change the location for this debug instance. You can change these in the project properties.

Double-clicking the HumanResources.EmployeePayHistory table above brings up the editor for the underlying table in the project.

SSDT: Updating a Project by Importing Scripts

noreply@blogger.com (Peter Schott) — Mon, 05 Nov 2012 13:00:00 +0000

Sometimes your developers will work on new SQL Objects and give you scripts to alter or create objects. SQL Projects support importing those scripts into your project. Start by choosing the Import Script option.

Find your script or scripts.

Select your options for import:

If you see the following text in your log, be sure to check this file to see if something was missed on import. You'll need to manually make these changes, if applicable. In a lot of cases, the statements not understood tend to be "GO" statements.

“In the script that you provided to the import operation, one or more statements were not fully understood. These statements were moved to the ScriptsIgnoredOnImport.sql file. Review the file contents for additional information.”

I've also found that "ALTER TABLE" statements are not well understood by SSDT within the imported scripts. If you get several scripts that include these and aren't understood, you can either compare the physical database to the project or manually update the project. (This has been acknowledged by Microsoft as “working as designed” even if we might wish that this could actually change the script for the object instead.)

SSDT: Updating by Using Schema Compare

noreply@blogger.com (Peter Schott) — Fri, 02 Nov 2012 13:00:00 +0000

Sometimes changes are made to a shared database that need to be brought into your SQL Project and there are no saved change scripts for those changes. Other times you may just want to see what will change when you publish a project. SSDT has included a Schema Compare option for SQL Projects, dacpac files, and databases.
If you are using VS2010, there are two options for SQL and Data.

The "Data" menu is used for the older DB Projects within VS2010. There's a useful data compare option in there, but the schema compare will not work for SQL 2012 or SQL Projects.
The "SQL" menu contains the Schema Compare item with a sub-item to do a new Schema Comparison

To update your project from a shared database, start a "New Schema Comparison". You'll see a screen something like this:

Setting your source/target is pretty straightforward and each will produce a screen something like the following:

You can choose to compare against an open project, a live database, or a Data-tier Application File (dacpac). In our case, we are going to select a live database as the source and our project as the target.
Once selected, you may want to change the options to exclude certain object types or ignore certain settings such as file placement. Unlike in VS2010, the options cannot be set to some default at this time. When you've set the options to your liking, click the "Compare" button.

If there are any differences, you'll see a list of them here. The comparison window shows where there are differences between the source and target. This can be really helpful to know whether or not to include this change in the update.
To exclude a change, clear the checkbox next to it in the upper section of the window. A green + indicates that this object will be created. A red - indicates that the object will be dropped. The blue pencil indicates that the object will be modified. At this time there is no way to hide any unchecked items that show in the compare.
Once you're satisfied with your selection, click the "Update" button to push those changes into your project.

SSDT: Pre and Post Deploy Scripts

noreply@blogger.com (Peter Schott) — Thu, 01 Nov 2012 13:00:00 +0000

At some point, you will likely need to release changes that contain more than just schema changes. You may need to update data in a table, remove data that could cause a problem, or insert some data to support a code release. To support these changes, SSDT provides support for Pre-Deploy and Post-Deploy scripts.
These scripts are not created by default in a new SSDT project. To organize your scripts, you may want to create a folder structure similar to the following.

If you right-click the Post or Pre Deploy folders, you can choose to add a Script item.

You will then get a choice of which type of script to include. Choose Pre or Post deployment, as appropriate, and name the file accordingly.

You can only have one Pre-Deployment script and one Post-Deployment script per project as part of the Build! All included scripts must be set to “Not in build” or you will likely have errors.

If you look at the properties of these scripts, you will see that the Build Action is set to PreDeploy or PostDeploy. Opening the Post-Deploy script will show this default text.

/*
Post-Deployment Script Template       
--------------------------------------------------------------------------------------  This file contains SQL statements that will be appended to the build script.  Use SQLCMD syntax to include a file in the post-deployment script.  Example:      :r .\myfile.sql  Use SQLCMD syntax to reference a variable in the post-deployment script.  Example:      :setvar TableName MyTable                SELECT * FROM [$(TableName)]     
--------------------------------------------------------------------------------------
*/

Using Pre and Post Deploy Scripts
These files are interpreted as SQLCMD scripts by SSDT. You can use most valid SQLCMD syntax within the script file to include other scripts and set variables. At the time of this writing, you cannot use the :ON ERROR IGNORE command within a script. This will hopefully be addressed in a future release of SSDT.
Pre-Deploy scripts will always run before the schema changes are run. If you alter the database in a way that changes the schema, you may encounter errors with the release. E.g., if you make schema changes to the Person.Person table, but drop that table in a Pre-Deploy script, your publish action will likely fail.
Likewise, Post-Deploy scripts will always run after the publish action. This makes them a great place to insert new data, make small adjustments to the system, or perhaps implement custom security and permissions. The caveat to using post-deploy scripts to make changes is that the changes need to be repeatable. If you write an insert statement for a new lookup value, that same insert will run next time unless you check for the existing value first.
To create a new script to run in pre or post deploy:

Right-click the appropriate folder (Pre-Deploy, Post-Deploy, other)
Choose to add a "script" and select the "Script (Not in build)" option
Give it a name, preferably one without spaces as it will make it easier to run the pre or post deploy script.
Add your code changes. You may want to give it some descriptive comments and sometimes a Print statement can be helpful if you want to see progress as you run a deploy script manually.
- Make sure that you can re-run this script without causing errors! Check for data that may already exist, use outer joins, wrap your code in Try/Catch - whatever you need to do to make sure that you can have this run again if necessary.
After saving the script, edit your pre or post-deploy script and add a new line in a non-commented area something like:

Run a script called "MyScript"

:r .\MyScript.sql

This assumes that your script is in the same folder as your pre or post-deploy scripts. Adjust the relative paths as needed for the script you created.

Save your deploy script.

The next time you publish your project, it will pick up this script and include it in your change set. If you choose to create a script on publish, you can see the full text of your scripts included in the pre or post-deploy section of your change script.

Cleanup of Pre and Post Deploy Scripts
There are several options for cleanup, but one of the best suggestions I've seen has been to generate project snapshots and then remove the script references from your pre/post deploy scripts and the script files themselves from the project. They will still be saved in the snapshot, but will not be in your project anymore. You may be able to manage this well through your Version Control System, but snapshots do have some advantages.
Pros:

Your project will remain somewhat clean.
Script files will be saved with their appropriate snapshot, ideally tied to a particular release.
Less concern about whether a script is replayed because it's removed.
Good for scripts that have a larger impact and should only be run one-time.

Cons:

It's a manual process and requires some attention to detail.
You need to look in the snapshots to see what scripts were used.
It may require that you have a more formalized release process.
You may need to publish several snapshots to bring the database up to a current version.

SSDT: External Database References

noreply@blogger.com (Peter Schott) — Wed, 31 Oct 2012 13:00:00 +0000

If your database references other databases, you'll need a *.dacpac file for each external database referenced in your code. For example, if you reference AdventureWorksDW2008 from Adventureworks2008, you'll need to add that as a database reference.

To add a Database Reference to your project

Right-click the "References" folder in your project or choose the "Project" menu and select the option to "Add Database Reference"

Browse for your dacpac file
Choose the appropriate database location. (Same, Different DB / Same Server, or Different DB / Different Server)
If your database name doesn't change, clear out the "Database Variable" column. It isn't immediately obvious, but doing this will let you keep referencing your database in the same manner you do currently.
- If this database name can vary, populate the Database Variable field. You'll need to replace this reference in your code with the variable from this column.
- If you're referencing a database on a linked server, this would probably be better served with a static database name and a "Server Variable". Make sure that server variable is set appropriately when you publish the database.
- This should be addressed with a future release of SSDT.
You may choose to suppress errors if there are unresolved references in the project. This is useful if your dacpac file could be out of date. You can still code against an object you know to exist, but isn't in the reference file yet.
If you use Linked Servers, you’ll probably want to use the Server Variable to ensure that you can release your code in different environments.

Some Best Practices and Lessons Learned

Make a shared folder to store your dacpac files in a common place. This should be easily accessible from all of your SQL Projects.
- If you use C:\DatabaseProjects as your base for all SQL Server projects, create a folder in there to store your shared dacpac files.
Because all paths are relative to the project, you may need to copy the master/msdb dacpac files that ship with SSDT into your shared folder. You can find these files in your Visual Studio folder in a path similar to the following.

Drive:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\Extensions\Microsoft\SQLDB\Extensions\SqlServer\VersionNumber\SqlSchemas

If you support multiple SQL Server editions, you may want to rename master/msdb files with their user-friendly version number. E.g., the master.dacpac file in your "90" folder could be renamed to master2005.dacpac
This should be addressed in a future release of SSDT and could already be fixed.

If you use variables, don’t forget to replace any TSQL with the variable in the form of [$(VariableName)].

Square braces may not be necessary, but in most cases, they’ll work as expected for DB or Server names.

If you need to reference a dacpac that is already in use, you’ll need to create a copy of it. This comes up more often if you’re referencing a replicated database for which you already have a production dacpac.
Choose good variable names for the Server variable name.
Don’t forget to set the variables in the Project and/or Publication properties!

SSDT: SQL Project Options

noreply@blogger.com (Peter Schott) — Tue, 30 Oct 2012 13:00:00 +0000

To set the properties for your SQL Project, right-click the Project and select Properties. You'll see a window containing several tabs to define your project.

Project Settings
Here you can set your normal target platform (SQL 2005, 2008, 2012, or Azure).
You can enable the T-SQL verification option, but this is mostly for Azure projects.
If you will generally output to a script, choose the "Create script" option. On the whole, just using the dacpac file will likely provide greater flexibility in the long run as it can be adapted to a different target. Generating a script will only be guaranteed to work against the target used to generate the script.

You can also set the dacpac file properties, which sadly do not do much when used against a database not using these as data-tier applications. There is some talk of enhancing the projects to use this version number and description somewhere within the database, but right now they are pretty much only there for you to populate if you wish.

You can also set the Database Settings for your project. These options are the normal ones you would set when setting options in SQL Server. You can choose to publish these to your targets so set these to your preferences. (More on that in a future post.)

I'm going to bypass the SQLCLR and SQLCLR Build tabs as I am not familiar with them and don’t currently use either in our system.

Build
The Build tab is the place to specify which warnings to ignore, the output path for your files when you build the project, and the name of the file. You may want to specify certain warnings to ignore, especially if you use cross-database projects or any references to system databases. Those can often result in warnings about missing objects, even though they'll work perfectly fine when you publish them.

Note that the warnings are set per configuration. If you use multiple configurations be sure to set the warnings in each one.

SQLCMD Variables
SQLCMD variables can be used throughout the project as placeholders for values passed in at publication time. For example, you may choose to change the way your scripts run based on your Development, Customer Acceptance, or Production environments. Define these variables here and use them wherever applicable in the project by inserting them as $(DeployType). SSDT will substitute them with the proper value on publication.

Build Events
Build events can be set up to run certain commands before and after the project is built. You can also choose to run the Post-Build event commands only when the project builds successfully or on every build.

Debug
Start Action and Target Connection String should make sense for those who need to change them. One item to note is that the debug action defaults to a SQL Express type instance of your database that is released to (localhost)\DBName. This will run within SSDT so you can try out your T-SQL before committing the changes in the project.
** Note that if you use FILESTREAM, you will not be able to use these user instances. You will need to change your debug database to a SQL Server install.
In your Deployment Options, you have a screen that looks something like this:

"Block incremental deployment" is one to be aware of. If you run into any constraints, refactoring, data type changes, NULL to NOT NULL, or similar changes, you could have your debug action stop unexpectedly. It can be really useful to avoid dropping columns that could contain data, but if you are aware of the changes, this option could also get in the way of normal releases. According to the MS documentation, this will only block the change if there is data in the table.
"DROP Objects in target but not in project" can be useful to make sure that everyone is running with what's in the project and that they don't have leftover objects that could interfere with testing. However, it's also possible to lose a bunch of work if you publish a project with this option against your local instance containing the stored procedure you've worked on for the last 2 weeks.
"Advanced" contains a lot of different options about what to compare and how to generate the changes. Some of the more interesting options include:

Allow incompatible platform - this will let you push your changes to a version of SQL Server other than the one specified in the project properties. The changes will fail if you try to do something not allowed in the target, but useful in case you have your project set to the lowest version supported.
"Drop xyz not in source" - these options control what will be dropped from the target if it's not defined in the source. The default options are likely good for most users.
"Ignore xyz" - defines what should be ignored when comparing the project against the target. You may want to ignore details about the partition schemes, or what permissions are set in the target
"Include Transactional Scripts" will set up the release script as transactions when publishing the database. This is useful when you want to make sure that if one part of the publish action fails, the change script is rolled back.
"Verify Deployment" - stops the publish actions if the script could run into some problems.

Reference Paths
This section is used to modify and maintain the various Server & Database variables used with cross-database references. See the "External Database References" page for more detail.

Code Analysis
This option can be useful to check for common issues such as using reserved words, non-standard characters in object names, or using "select *" in your queries. You can also specify whether to show these as errors or warnings. Set appropriately for your environment.

SSDT: Importing an Existing Database

noreply@blogger.com (Peter Schott) — Mon, 29 Oct 2012 13:00:00 +0000

To Import From an Existing Database
Import Database into your project by right-clicking the project name or selecting the Project Menu and selecting the Import -> Database option. Note that you can also import from SQL Scripts or an existing dacpac file. One of those may be necessary if you don't have direct access to your source server.

You'll then be asked to choose your project name, location, folder structure, and what to import. You can choose the defaults if you want. If you have an intricate permissions structure, you may want to import the permissions. If your logins, users, and permissions tend to vary by environment, you may want a different way to handle those. That will be covered in the "Permissions" section.
You'll have several options for folder structure. I'd choose the recommended structure unless you have a reason to choose something else. That will structure all of your objects under their appropriate schema, by object type. If you have a relatively simple database model, you may choose to structure your project by schema alone, object type alone, or just put everything in the root of the project. This only affects your project file structure, not your database structure.
Once you're satisfied with your settings, click the "Start" button to begin populating your project.

Importing From a Dacpac File
SSDT supports importing from a dacpac file. This can be useful if you're working on a project for a database to which you do not have direct access in order to import the schema directly. These files also can be used as external database references if you work in an environment where multiple databases interact.
In order to create a dacpac file, you have a couple of options.
You can generate a dacpac file from a SQL Server database using SSMS 2012. Right-click the database you wish to use. Select Tasks, then "Extract Data-tier Application…".

Set the file location and any other options you want to use.

Click next. Verify the settings. Click Next again. The system will now generate a dacpac file in the location you specified.
Note that choosing the "Export Data-tier Application" will create a bacpac file. This is used to backup all schema and data in a database so it can be published to an Azure database. You cannot use a bacpac file to import schema into your project.

Generating a dacpac using SQLPackage.exe
You can always use the SQLPackage command line to extract the DB Schema into a dacpac file. For a complete list of parameters, see http://msdn.microsoft.com/en-us/library/hh550080%28v=VS.103%29.aspx.
For a quick extraction from a trusted server, you can run something like the following:

Code Snippet

Sqlpackage.exe /a:extract /ssn:localhost /sdn:Adventureworks2008 /tf:Adventureworks2008.dacpac

This command uses an "Action" to Extract the file, with the SourceServerName of localhost, a SourceDatabaseName of Adventureworks2008, and a TargetFile of Adventureworks2008.dacpac

SSDT: Creating a New SQL Project

noreply@blogger.com (Peter Schott) — Sat, 27 Oct 2012 13:00:00 +0000

Download the latest SSDT package if you do not have the product installed already:
http://msdn.microsoft.com/en-us/data/tools.aspx

To create a new SQL Project, you can either start by creating a new Project or create a new project from an existing database.
When creating a brand new project, be sure to choose the "SQL Server Database Project".

This is not the same as the Database -> SQL Server Project template. Using this will create a usable DB Project, but not the newer SQL Project.

Once you've created a new, empty project, you'll need to populate the project in order to be able to use it. You have a couple of options. You can import from an existing database, import from a Dacpac file, or just start creating your objects if this is a completely new project.

To Start a New Project from a Connected Database
Open your "SQL Server Object Explorer" window. Connect to the database server containing the database for which you want to create a project. Right-click that database and choose the "Create New Project…" option.

You'll then be asked to choose your project name, location, folder structure, and what to import. You can choose the defaults if you want. If you have an intricate permissions structure, you may want to import the permissions. If your logins, users, and permissions tend to vary by environment, you may want a different way to handle those. That will be covered in the "Permissions" section.
You'll have several options for folder structure. I'd choose the recommended structure unless you have a reason to choose something else. That will structure all of your objects under their appropriate schema, by object type. If you have a relatively simple database model, you may choose to structure your project by schema alone, object type alone, or just put everything in the root of the project. This only affects your project file structure, not your database structure.
Once you're satisfied with your settings, click the "Start" button to begin populating your project.

SSDT: Why Use SQL Projects?

noreply@blogger.com (Peter Schott) — Fri, 26 Oct 2012 22:50:00 +0000

What Are SQL Projects and why use them?
SQL Server has always been lacking in a good solution to control versions of your database. We make changes directly to the database and if we're good, we remember to take backups. If we're really good, we save scripts so we can make those changes elsewhere. Too often, we resort to tools for schema differences or try to save up all of the scripts to replay them later. Both have some advantages. Schema differences guarantee that you can make two environments look the same. Scripts can be replayed in order to keep the systems in sync. However, a schema difference doesn't handle data changes well and can't handle making partial changes when not everything is ready to promote. Sets of scripts can also work well, but fail if you end up needing to push sub-sets of those scripts because certain functionality isn't going to be released yet.
Borrowing from Gert Drapers' excellent discussions on Database Development Challenges:

Databases are inherently stateful
- ALTERs are generally run instead of DROP/CREATE
- Dependencies can greatly complicate the changes and scripts
- Data needs to be persisted
Databases are often not integrated well with the application development life cycle
DB Versions are frequently not persisted or managed well
SQL Scripts may not handle different SQL Server versions well

Sequential Change Scripts

Required to run in a set order
- Script 1, Script 2, Script 3, …
- Can't do Script 1, Script 4, Script 9
Validating the end state after script runs vs. an expected end state can be complex
Could easily miss a script in the sequence without knowing
Handling a release where scripts need to be "skipped" can cause cascading dependency problems
Can require extensive use of IF NOT EXISTS statements
Manual process
Point in time model
Used by systems such as DBDiff

Declarative Model

The model is the desired end state
- Compare the current state of the target against the desired end state
- Use the differences to generate an upgrade plan
- Use the upgrade plan to generate a change script, correctly ordered to bring the target up to the desired end state
Generated / Programmatic process
Always current
Used by Red Gate, Microsoft, DB Ghost

Microsoft and Red Gate have both provided solutions to store versions of database schemas. While Red Gate offers a good product, it also requires some external tools to provide a complete solution for schema control. Until recently, Microsoft's solution wasn't accessible to everyone because it required a copy of Visual Studio to handle even the basics. This improved in Visual Studio 2010, but still required a separate purchase. With SQL Server 2012, Microsoft introduced SQL Server Data Tools and SQL projects.
With SQL projects, developers can store a version of their database objects in a source control system of their choice. Tables, stored procedures, views, and even permissions can be stored in such a way as to provide upgrades to databases and even build a new set of databases from scratch. This could even be used to build and release these databases continuously in an automated fashion to release new code.

Tales of a Lazy DBA–MySQL, SSIS, and “0” Dates

noreply@blogger.com (Peter Schott) — Fri, 16 Dec 2011 01:59:00 +0000

We’ve recently been tasked with converting a lot of MySQL Data into our system from a multi-tenant MySQL Database. We don’t have a fixed schedule to actually execute the imports because they’ll be on a “per customer” basis. Well, that sounded like a great task for SSIS. I set up the ODBC driver, connected, set the initial “sql_mode” options for our connections to (‘mssql, allow_invalid_dates’) and started to work.
First problem we ran into with an ADO.NET connection to MySQL and writing a SELECT * from schema.table was when we hit a MySQL “Date” column containing a value of ‘0000-00-00’. SSIS threw an error, not sure what to do. Thanks to some others who have solved this problem, I realized that within the MySQL Select statement, we could do something like:

CASE date_created WHEN '0000-00-00' THEN NULL else date_created END as date_created

to pass those dates over as NULL. That solves the implicit conversion to datetime (SQL 2005) and avoids the invalid dates. I ran something similar for a “Time” column to:

CAST(Time_Created as char(8) ) as Time_Created

So that solved one particular table export with about 20 or so CAST and CASE statements. Needless to say, I wasn’t looking forward to doing this for another 500 tables with a total of almost 6000 columns.
I finally set up a really basic query to generate most of the SELECT statements we would need to pull our MySQL data across without too much pain. Admittedly, a small step and I’d still need to copy/paste when I set up each new ADO.NET source, but it worked reasonably well. I’m adding the code snippet for MySQL here in case anyone else has a similar problem.

select
CONCAT(CASE WHEN ordinal_position = 1 THEN 'SELECT ' ELSE ', ' END,
 CASE Data_Type WHEN 'date'
 THEN CONCAT('CASE ',column_name,' WHEN ''0000-00-00'' THEN NULL else '
    ,column_name,' END as ',column_name)
 WHEN 'time' THEN CONCAT('CAST(',column_name,' AS CHAR(8) ) as ',column_name)
 ELSE column_name END, CASE WHEN Ordinal_Position <> MaxOrd THEN ''
 ELSE CONCAT('
    FROM MySchema.', c.table_name) END
    ) as Select_Column_Name
from information_schema.columns as c
JOIN (select table_name, MAX(ordinal_position) as MaxOrd
        from information_schema.columns
        WHERE Table_Schema = 'MySchema'
        GROUP BY Table_Name) as t1
    ON c.Table_Name = t1.Table_Name
where table_schema = 'MySchema'
order by c.table_name, ordinal_position LIMIT 0, 50000;

I’d love to hear other ideas if anyone has encountered this before and come up with a more elegant solution for translating “0” Date or Time data from MySQL into SQL Server.

Installing SQL Server, Sharepoint, PowerPivot on a single server

noreply@blogger.com (Peter Schott) — Thu, 15 Sep 2011 21:13:00 +0000

I've been trying to set up a single virtual machine for a proof of concept using SQL Server Denali CTP3, Sharepoint 2010, and PowerPivot. I want to get Project "Crescent" running through this as well to prove out some report concepts. I've had a lot of trouble finding the right steps to get everything installed and working correctly on a single box, especially since my Sharepoint knowledge right now is practically non-existent when it comes to BI. Every time I tried to get the Sharepoint / PowerPivot integration working, I hit a roadblock or managed to corrupt my Sharepoint install.

I had the opportunity to speak with Brian Knight ( blog | twitter ) and he pointed me to PowerPivotPro.com which, in turn, led me to PowerPivotGeek.com. They had a page of instructions for single-server installs with a pointer to an MS whitepaper and a private cached file of the whitepaper just in case. This gave me the information needed to get PowerPivot working on my VM.

A couple of notes from my personal experience:

Install Sharepoint SP1 right after installing Sharepoint 2010. SP1 is required when you're using Denali
Do NOT configure Sharepoint until the directions tell you to do so.
Use a Named Instance in order to get PowerPivot working.
If you plan to use Crescent, make sure that you set up SSAS with the "Tabular Data" option (or whatever the final name ends up being).

Once again, thanks to Brian for pointing me towards the right solution. Thanks to PowerPivotGeek for hosting these files. I imagine the instructions would work for Virtual Server, Virtual PC, VMWare, VirtualBox, or similar Virtual Machine apps. I hope this is helpful to anyone else trying to set up their own VM for testing out the Microsoft BI stack.

Service Broker–WAITFOR and Activated Queues

noreply@blogger.com (Peter Schott) — Wed, 29 Jun 2011 18:45:00 +0000

We’ve implemented Service Broker for handling a small portion of the transactions we want to catch and apply into our Operational Data Store. Mostly we wanted to make sure we accounted for actual DELETE operations and handled those records correctly. We can use Change Data Capture at this time on our source systems and Service Broker seemed to fit the bill. We still handle the remaining inserts and updates through SSIS.

While running Service Broker, we noticed a pretty constant CPU hum on the receiving server. That seemed odd, but we had a lot of trouble tracking it down. Regular Profiler traces didn’t show any running TSQL, “normal” Service Broker traces weren’t showing much, either. We just saw SQL Server running at a pretty constant 20% even with nothing seemingly happening.

I want to give a public thank you to Mark Hill (twitter) for doing a little extra digging and catching the root cause of this. In our tinkering with Service Broker, Queues, Activation, Stored Procs, and such we missed some very important information along the way.

We had written a stored procedure that would be used on the Receiving side of our Service Broker queue to run as the “Activated” proc. Inside the stored procedure, we included a WHILE loop to process anything that was in the queue. If nothing, exit. That seemed pretty simple – if something comes into the queue, process it and stop when nothing is left. End of story, right?

Sadly, this is where we missed a small, but important, fact about Service Broker. If you have an activated stored procedure that doesn’t use the WAITFOR command, Service Broker, we execute that stored proc as many times as possible looking for something to process. We looked at it, turned off the Activation on the queue and saw the CPU drop to nothing. We re-activated the queue and saw the CPU shoot up again. We tweaked the stored procedure after that and added a WAITFOR command with a timeout of 60000. After doing this, we saw the stored procedure run to process everything in the queue, then go idle, which was exactly the intended behavior in the first place.

I’m not going to pretend to be an expert on Service Broker. We had tried to code with the intention of being able to use this stored procedure as an Activated stored procedure or as one called from an external process to work through the queue. While that may be possible, it was unnecessary in our actual usage. Adding a WAITFOR command around our queue processing eliminated our extra, non-essential CPU usage and stopped trying to execute a stored procedure for no reason.

Resources

Pro SQL Server 2008 Service Broker ( Amazon | Apress )

WAITFOR (Also see this performance article – it’s the little details that get you)

Quickly script permissions in SQL 2005+

noreply@blogger.com (Peter Schott) — Thu, 17 Feb 2011 22:15:00 +0000

I wanted an easy way to generate a script to recreate all DB object and schema permissions for a database. Searching online, I found several different examples, but none quite did everything I needed. I put this together from some examples and added it to my toolkit. This script will generate the appropriate T-SQL to grant permissions to DB Objects and Schemas.

SELECT
state_desc + ' ' + permission_name +
' on ['+ ss.name + '].[' + so.name + ']
to [' + sdpr.name + ']'
COLLATE LATIN1_General_CI_AS as [Permissions T-SQL]
FROM SYS.DATABASE_PERMISSIONS AS sdp
JOIN sys.objects AS so
     ON sdp.major_id = so.OBJECT_ID
JOIN SYS.SCHEMAS AS ss
     ON so.SCHEMA_ID = ss.SCHEMA_ID
JOIN SYS.DATABASE_PRINCIPALS AS sdpr
     ON sdp.grantee_principal_id = sdpr.principal_id

UNION

SELECT
state_desc + ' ' + permission_name +
' on Schema::['+ ss.name + ']
to [' + sdpr.name + ']'
COLLATE LATIN1_General_CI_AS as [Permissions T-SQL]
FROM SYS.DATABASE_PERMISSIONS AS sdp
JOIN SYS.SCHEMAS AS ss
     ON sdp.major_id = ss.SCHEMA_ID
     AND sdp.class_desc = 'Schema'
JOIN SYS.DATABASE_PRINCIPALS AS sdpr
     ON sdp.grantee_principal_id = sdpr.principal_id
order by [Permissions T-SQL]
GO

I know that a lot of you may have something that does this already, but figured it can’t hurt to have another snippet available to generate permission statements. If you make any enhancements, let me know. I’d love to keep this up to date so it’s helpful to others.

SQL 2008 R2 – SSIS Data Export Oddities

noreply@blogger.com (Peter Schott) — Mon, 07 Feb 2011 22:27:00 +0000

I don’t know if anyone else has had these sorts of issues, but we work quite a bit with conversions of various source data into our SQL Server database. I recently was handed a backup from SQL 2008 R2. I figured that wasn’t a huge problem. Restore onto my local test box, use SSIS to push over to a SQL 2005 instance so it can work its way to Production. No big deal, right?

The first time I tried this using the SQL Native Client 10.0 on both sides, I ran into an errors with the mappings. I can’t quite figure that one out because from what I can see, there’s nothing at all in the source database using a feature that would not be available in SQL 2005.

I tried exporting to an MS Access MDB file. No luck from SQL 2008 R2 with the default settings because of an invalid size error on a varchar to longtext conversion.

I was able to successfully export using SNAC from SQL 2008 R2 to a SQL 2008 instance. So from there I thought I could upload directly to the SQL 2005 instance. No such luck using SNAC – again. I was able to export from there to an Access MDB file and pull that into SQL 2005. I don’t quite get why that worked, but figure I’ve got some odd mapping in the XML files defining the defaults that I’m missing.

I was recently asked to repeat this task and figured there had to be a better way. This time I restored the DB to my R2 instance – no issues. I then used the SNAC client to access my R2 instance, but set up an OLEDB connection to my SQL Server 2005 target. For some reason, the mappings are just different enough that this worked with no issues. I was able to transfer directly. I now wish I’d tried that the first time, but I’d already blown a couple of hours on it.

If anyone else has encountered that and knows why SNAC from 2008 R2 doesn’t seem to work directly to SNAC on 2005, I’d love to know the reasons as well. If not and you encounter something similar, maybe trying the OLEDB connections will work for you.

SQL 2008 Merge and PK/FK Constraints

noreply@blogger.com (Peter Schott) — Mon, 17 Jan 2011 18:24:00 +0000

We ran into this issue a while back. SQL 2008 has a “feature” that will not allow inserts into a table on either side of a Foreign Key relationship. If you attempt to insert into a table involved in the FK relationship, you get an error something like:

The target table 'TableName' of the INSERT statement cannot be on either side of a (primary key, foreign key) relationship when the FROM clause contains a nested INSERT, UPDATE, DELETE, or MERGE statement. Found reference constraint 'ConstraintName'.

This is documented in Connect 435031. There is one workaround documented in the ticket – drop the FK constraint, run the MERGE query, then re-create the constraint. Happily, this bug is still open as the requirement to drop a constraint just to run a MERGE statement kind of defeats the purpose of putting a constraint on the table in the first place. While dropping the constraints will work, there is another possible workaround. I was able to run this without any issues while leaving my FK Constraint in place.

Create a temp table that matches the definition of the table into which you want to perform your insert.
Instead of inserting into your base table like you normally would in a merge, write your code to INSERT #Tablename at the beginning of the MERGE process. You’ll still use your main table and staging table inside the MERGE INTO … USING query.
After the Merge, insert into the main table using the values in your Temp table.

Here’s a shell of an example. Line 11 below contains the main difference to the MERGE query from a “normal” MERGE.

   1: --1: Create Temp table

   2: CREATE TABLE #MyFactTable(

   3:     ID INT NULL

   4:     , CustomerName VARCHAR(100) NULL

   5:     , SourceID INT NULL

   6:     , OutputAction VARCHAR(100) NULL

   7: );

8:

   9: --2: INSERT into the temp table instead of your normal target table

  10: --   Merge query will be the same otherwise

  11: INSERT INTO #MyFactTable (ID, CustomerName, SourceID, OutputAction)

  12: SELECT so.ID, so.CustomerName, so.SourceID, so.output_action

  13: FROM (

  14:     MERGE INTO dbo.MyFactTable AS t

  15:     USING Staging.MyFactTable AS s

  16:     ON ( s.ID = t.ID

  17:         AND s.NewLoad = 0 )

18:

  19:     WHEN MATCHED AND ( s.SourceID <> t.SourceID )

  20:             AND s.NewLoad = 0

  21:     THEN UPDATE

  22:         SET RecordState = 0

  23:         , UpdatedDate = getdate()

24:

  25:     WHEN NOT MATCHED BY TARGET AND s.NewLoad = 0 THEN

  26:         INSERT (ID, CustomerName, SourceID)

  27:         VALUES (s.ID, s.CustomerName, s.SourceID)

  28:         OUTPUT $action AS OutputAction

  29:             , ID

  30:             , CustomerName

  31:             , SourceID

  32:         ) AS so (OutputAction, ID, CustomerName, SourceID)

  33:         WHERE OutputAction = 'UPDATE'  ;

34:

  35: --3: Perform the final insert into your target table

  36: INSERT INTO MyFactTable (ID, CustomerName, SourceID)

  37: SELECT DISTINCT ID, CustomerName, SourceID

  38: FROM #MyFactTable ;

39:

  40: --4: Clean up your temp objects.

  41: DROP TABLE #MyFactTable ;

I’d love to hear if anyone else has a workaround for this that doesn’t involve dropping and recreating the FK constraint. If this is an issue for you, please vote for the Connect Ticket.

Book Review: Windows Phone 7 Plain & Simple

noreply@blogger.com (Peter Schott) — Tue, 14 Dec 2010 04:00:00 +0000

Overall, Windows Phone 7 Plain & Simple is a good book for people new to Windows Phone 7 and especially for people new to Smart Phones. Michael does a great job illustrating how to use the phone and get up to speed quickly. He walks quickly through the general operation, setting up the phone, and entering text in the first couple chapters. The next several chapters discuss using the most popular features of WP7: calling, e-mail, text messages, calendar, browsing the web, and using the maps/navigation. Next we learn more about music, video, taking pictures and videos, and using the Marketplace. We end with a quick session on using WP7's Office Hub for documents, spreadsheets, and OneNote. This is followed by a discussion on using the Zune software to synchronize files between your phone and your PC. (Michael doesn't mention the Mac software to do this. It's capable, but more limited.)

Having followed the progress of WP7, I found that I knew a lot of the information in this book. There were still a couple interesting bits of information that were news to me, but not many. Michael didn’t write this for people like me who have followed WP7 for some time. He wrote for people who aren’t really familiar with smartphones and especially with the changes that WP7 brings. For those people, this will be a useful read and a quick reference while they get used to their device. I plan to show this to my family members who have WP7 devices and aren't quite sure what to do with them.

The positives:

The information is very well presented, easy to follow, and broken out into logical sections.
Michael's casual writing is well-suited for this book.
Readers will not feel intimidated by their new phone and are encouraged to try things out.
Michael presents some information that you may not easily find by reading articles online.

The negatives:

I don't see this as a book that will be a long-lasting reference book. Once the reader has used the phone for a month or so, the lessons from this book should be second-nature.
WP7 already has two planned updates at the time of this review. A minor update in January 2011 that will reportedly enable Copy/Paste functionality and a larger one in February 2011. The errata for this book will need to be updated to take these changes into account as some information may be outdated.

Conclusion:
If you can find Windows Phone 7 Plain & Simple for a reasonable price and are new to the platform, pick it up. It's a short, but informative, read. If you've followed WP7 for a while, this is not likely the book for you as you'll know most of the information here already. You may still want to pick up a copy to share with family or friends who are new to the phone. It could save you "support" calls.

Please visit the Windows Phone 7 Plain & Simple page at O’Reilly for more information about this book.

Disclaimer: I received a free copy of this book in electronic format in return for providing an honest review. I was not compensated in any other way.

Book Review: Cooking For Geeks

noreply@blogger.com (Peter Schott) — Tue, 30 Nov 2010 06:00:00 +0000

(I know this isn’t SQL-related, but I think this may be a great read for people with similar interests.)

This is not a cookbook. If you’re looking for a new collection of recipes, this is not the book for you. If you like shows like Good Eats or enjoy seeing how food is prepared and served, you’ll almost certainly love Cooking for Geeks. If you like experimenting in the kitchen and knowing why food turns out the way it does, pick up this book!

As a geek, I loved Jeff’s analogy: Recipes are code. Follow a recipe as written and you generally get good results. Forget the where clause and you could have unrecoverable errors. Introduce your own changes and you could get something great or you could get something horrible that requires a lot of cleanup. Recipes may have bugs or need corrections. Perhaps there’s more than one way to the same result. Oh, and don’t forget to comment your recipe. Otherwise you might not be able to recreate something fantastic.

Each chapter of Cooking for Geeks deals with different concepts, each with their own scientific background. Common utensils, ingredients, time/temperature, baking, additives (chemicals), and even some geeky fun with hardware or unusual cooking techniques – all are included in a way that not only gives some neat recipes, but the science behind the recipes.

To me, the most interesting parts were on baking and the chemical reactions that take place as heat is applied. It was great reading exactly why food turns out with all of its various nuances. That science got me thinking about ways to tweak the outcome of various recipes I follow and was just fun to read.

You can get your own copy of Cooking for Geeks direct from O’Reilly.

Disclaimer: I received an electronic review copy of this book, though I’d likely have wanted to read and review this anyway.