Hot answers tagged

27 votes
Accepted

Why is :r SQLCMD command marked as wrong in Post Deployment Script?

Assuming that the error happens during the build process, and that you do not have any actual syntax errors, and it is a simple :r path\to\file.sql then the error also occurs when the file being ...
user avatar
19 votes
Accepted

SSDT Schema Compare doesn't work while a BULK INSERT is in progress

The OBJECTPROPERTY call requires a schema stability (Sch-S) lock, which is only incompatible with a schema modification (Sch-M) lock. The BULK INSERT will take a Sch-M lock in some circumstances. ...
user avatar
  • 71.2k
19 votes
Accepted

Schema Migration: SQL Server Data Tools vs Liquibase and Flyway

SSDT is comparable to Liquibase/Flyway as it does what they do but by taking a different approach. With SSDT you have the development environment so you get things like go to definition, find ...
user avatar
18 votes
Accepted

SQL Server - Adding non-nullable column to existing table - SSDT Publishing

I'll share how I have done this in the past. It is designed to solve the specific limitation of pre-deployment scripts that you call out in your second point: Adding the column in a pre-...
user avatar
  • 27.2k
15 votes
Accepted

How to prevent SSDT publishing from dropping columns

There are three parts to this Question, so let's start with the "Block on Data Loss" option, dropped Tables, and then dropped Columns. "Block on Potential Data Loss" Option If you enable the "Block ...
user avatar
13 votes

"Unresolved reference to User" after importing as a VS DB project

I had the same problem and found this link User has an unresolved reference to Login If you create application specific logins (which you should) then you are going to come across this error ...
user avatar
13 votes
Accepted

SSDT Drop and Recreate Tables when nothing has changed

After a fair amount of frustration, finally found the cause, and posting as an answer as it may help others.... Finding the reason for why SSDT thought the scheme was different I think is the first ...
user avatar
  • 321
12 votes
Accepted

SSIS Control Flow: Precedence constraints not working as expected

Your precedent constraint defaults to an AND situation. The preceding task must return success and the expression must be true. In binary scenarios like this, only one of those two tasks is ever ...
user avatar
  • 15.5k
12 votes
Accepted

Option to uncheck all or invert selection in SSDT?

For those coming here from Google looking for a solution to this (like me), I found the answer here. All you have to do is right click on the section and ta-da, you can Include or Exclude all ...
user avatar
11 votes
Accepted

SQL Server: Using 4 part identifiers when database may be on the same server

Use a synonym, and just don't sync the synonyms. So when it is on a local server: CREATE SYNONYM dbo.FooBar FOR DBName.dbo.TableName; When it is remote: CREATE SYNONYM dbo.FooBar FOR ServerName....
user avatar
11 votes

Excluding a specific table from a SSDT deploy

You can use the AgileSqlClub SqlPackage Deployment Filter. Brief instructions reproduced from the original article by Ed Elliott: Download the filter from agilesqlclub.codeplex.com Put the DLL into ...
user avatar
  • 210
11 votes
Accepted

"Unresolved reference to User" after importing as a VS DB project

The easiest way is to not manage users through ssdt (most people don't). So you can just strip them out and don't deploy logins or users. There are three ways: the new options to ignore users/logins ...
user avatar
9 votes
Accepted

SSDT Schema Compare fails when comparing my DB Project to my Azure Server

TL;DR The query Visual Studio is executing is being blocked and the query times out. Turns out I've managed to work this out for our situation: timing out due to blocking! I ran a profiler trace ...
user avatar
  • 116
9 votes

Why is :r SQLCMD command marked as wrong in Post Deployment Script?

Valid Error This is because :r somescript.sql isn't valid sql. You are seeing a syntax error, which is precisely what the error message states. SQL80001: Incorrect syntax near ':'. SQLCMD Mode ...
user avatar
9 votes

Is there a way to easily validate that a SQL Script is compatible with a specific SQL Server version?

There is not a simple tool or existing script for this that I am aware of. In terms of scanning for the use of functions added in a specific version (or enabled by a specific compatibility level) ...
user avatar
8 votes
Accepted

Where can I get the master.dacpac file for SQL Server 2012?

You should be able to add a Database Reference. Click Project-> Add Database Reference. Click the radio button for System and then master. Code with references to objects in the master db should now ...
user avatar
  • 1,035
8 votes

How do I get SSMS to use the relative path of the current script with :r in sqlcmd mode like SSDT does?

Getting a relative path in SSMS is not that straight-forward since you are not executing the script; SSMS has loaded the script into memory and is executing its text. So the current directory/folder ...
user avatar
8 votes

SSIS 2012 metadata not refreshing on re-deploy

Honestly, you shouldn't be editing the package to point to another environment before deploying it. Please look into package configurations to learn about methods to deploy your packages without ...
user avatar
  • 15.4k
7 votes
Accepted

Property class Length Annotation PersistedResolvableAnnotation

I found the issue myself. I will document it in case anyone else is facing the issue in future. The following user definition: CREATE USER [Angela] WITHOUT LOGIN WITH DEFAULT_SCHEMA = [Angela]; ...
user avatar
  • 4,605
7 votes
Accepted

How to rollback a SQL Server transaction and use output param?

A more robust way to write this stored procedure would be: CREATE PROCEDURE dbo.sp AS BEGIN SET XACT_ABORT, NOCOUNT ON; BEGIN TRY BEGIN TRANSACTION; SELECT 1/0; -- An error! ...
user avatar
  • 71.2k
6 votes

"Unresolved reference to User" after importing as a VS DB project

Apparently, this issue is still occurring on VS2017 database projects as well. I've managed to solve it by first creating the login and then create the user. -- Windows Account CREATE LOGIN [...
user avatar
  • 161
6 votes
Accepted

SSDT on VS2013 for SQL Server 2012

That is correct. Forward compatibility is guaranteed. Backwards compatibility does not exist*. Having 4 different BIDS/SSDT instances on a machine is a pain I've known all too well but there's not ...
user avatar
  • 15.5k
5 votes

Automatic Version increment on Data-Tier Applications

I found this forum thread had an answer that worked well: Automatic Version increment on Data-Tier Applications Minimum steps are: Create Properties\AssemblyInfo.cs if it doesn't already exist, and ...
user avatar
  • 181
5 votes

How do I get SSMS to use the relative path of the current script with :r in sqlcmd mode like SSDT does?

I could be wrong, but I don't think that it is possible. Please see these links for an example of what others have done (none are ideal). https://stackoverflow.com/questions/8753541/how-to-get-the-...
user avatar
5 votes
Accepted

What is SSIS in relation to SQL Server?

SSIS (SQL Server Information Services) is a first-party (Microsoft) component/platform for Microsoft SQL Server. It isn't an add-on product, you will be given the option to include components when ...
user avatar
  • 9,614
5 votes
Accepted

What does @RC mean in SSDT SQL Server Unit Testing?

@RC = Return code. The boilerplate unit test code will assign the stored procedure return code value to this variable, where it can later be used in a test case assertion. Assuming there's a ...
user avatar
  • 24.3k
5 votes

SQL Server Data Tools -- Stopped connecting to Azure SQL DB

A recent update to Azure SQL Managed instance broke some SSDT functionality as described in this MS Q&A thread. As described in the comments, the data comparison tool issue is fixed with Visual ...
user avatar
  • 24.3k
4 votes

Script component source won't load when click "Edit Script..."

I stumbled across this blog post, which while the author didn't have the exact same problem as me, our issues seemed similar enough that his fix might work for me to. I downloaded the SSDT-BI 2012 ...
user avatar
4 votes
Accepted

SSDT Schema Comparison for Stored Procedure signed by a Certificate

Before getting into any specifics about Certificate creation and module signing, we can at least verify whether or not the two instances have the same certificate. Run the following on both instances: ...
user avatar
4 votes
Accepted

SSDT Publish Window: What does checkbox ENABLE mean?

The checkbox applies to each option "positively" so that e.g. ticking "script database collation" will generate the appropriate statements to alter collation settings. If not ticked, the deployment ...
user avatar

Only top scored, non community-wiki answers of a minimum length are eligible