Hot answers tagged

26

This should be handled by adding a reference to the master database. Previously, we've had issues related to the relative path that's used to reference the master.dacpac (as described here). If I remember correctly, we pulled a copy of the master.dacpac file into the local project and referenced it using the last option shown in the dialog above. This is ...


23

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 imported does not exist. Please check the location of the file. If you did not provide an absolute path, then the path will be relative to the solution folder (at ...


19

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. These are listed in the "Table Locking and Logging During Bulk Import" section of Guidelines for Optimizing Bulk Import in Books Online: If the destination table ...


17

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 references and intelli-sense as well as the ability to compile a project into a dacpac and then deploy that dacpac to a database. The SSDT way (and redgate sql compare ...


13

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-deployment script will fail the publish when it automatically tries to create the same column, a second time (even if the pre- deployment script is written to be ...


12

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 incremental deployment if data loss might occur" option: on the "Debug" tab of "Project Properties" using the /p:BlockOnPossibleDataLoss=True command-line ...


11

Have you tried executing sqlpackage.exe with /p:BlockOnPossibleDataLoss=false in the command line? I just used SQLPackage.exe to create a .dacpac file of my test database, then I pubished it with the /p:BlockOnPossibleDataLoss=false option, and it overwrote my database.


11

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.DBName.dbo.TableName; Now all your code just has to reference dbo.FooBar no matter where it is.


11

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 the same folder as sqlpackage.exe Add these command line parameters to your deployment: /p:AdditionalDeploymentContributors=AgileSqlClub....


11

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 going to happen. By adding the next level of constraint in - both tied to the message box step, the final step is waiting for the "Enable CLR on server" to be ...


11

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 point of call. SQL Scheme compare is your friend here. (In VS under Tools => SQL => New Scheme Comparison...) Firstly thanks to @jadarnel27, was very helpful so ...


10

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 write a deployment contributor to pull them out use my deployment contributor http://agilesqlclub.Codeplex.com Ed


10

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 when trying to build your solution. To correct this error, select include 'Non-Application-scoped' object types in the options (gear icon at top) when you do ...


9

I've managed to get it working: I created a new database project master. In there I created a folder Server Object and a file LinkedServer.sql. In the SQL file i added the linked server: GO EXECUTE sp_addlinkedserver @server = N'LinkedServer', @srvproduct = N'sqlserver', @provider = N'SQLNCLI', @datasrc = N'LinkedServer.domain'; After adding the ...


9

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 with a filter on application name of Microsoft SQL Server Data Tools, Schema Compare to capture the Visual Studio schema compare events. It would reach a certain ...


9

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 The post deployment script is executed under SQLCMD Mode. The sqlcmd utility lets you enter Transact-SQL statements, system procedures, and script files at ...


9

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) something could be written using the documentation from MS though that might not catch calls via ad-hoc SQL if your stored procedure and other use that pattern for ...


8

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 editing the package itself. Likely your connection strings and other settings should just be a variable read from a configuration file. Since you are using SQL ...


8

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 build correctly.


8

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 is the default process starting folder. You can see this by running the following in SQLCMD mode in SSMS: !! PWD However, I did find a kinda-sorta way to do ...


7

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]; created the following code in the model.xml that avoids publishing, maybe because the schema (Angela) used there is invalid because it simply does not exist: &...


7

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 objects depending on the existing state of the objects. In this case, section means the Delete, Change, and Add parent folders in the schema compare window.


7

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! /* Other good code omitted*/ COMMIT TRANSACTION; END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; ...


6

I found using SqlPackage.exe will not drop security objects. I was having the same issue as well. Going forward, we are going to deploy database changes using DACPACs. We need a way to exclude users, roles and logins from the update process so they are not dropped from the target SQL Server. Our database is installed at hundreds of clients sites and many ...


6

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 much you can do. There are ways of constructing SSIS packages such that your solution can target whatever version of SQL Server will be hosting them but that's ...


5

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-relative-path-of-file-in-sqlcmd-mode-in-ssms http://boardreader.com/thread/Specifying_relative_path_to_r_command_in_lmlz__f0c78408-e001-48a2-8369-338c9534f496....


5

I am using Visual Studio 2010 and SQL 2008 database projects, but the idea remains the same. In your solution go to the project and do the following steps: right click the project -> Properties Project Settings (1st tab in this setting) go to section Catalog Properties file: make sure a settings file is already selected (by default there's a file \...


5

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 make sure it's included in the project and contains at least this code: using System.Reflection; [assembly: AssemblyVersion("1.0.*")] Or however you want your ...


5

After OP's Edit to the question : Try to use Visual Studio 2010 SP1 + VS Tools for SQL CE 4 -- or -- SQL Server Management Studio 2008 R2 supports for SQL Server Compact 3.5 database. (ATTENTION from SSMS version 2012 and newer, Sql Server Compact databases are no longer supported by SSMS; You must use SSMS 2008 R2 or OLDER) Note : SQL Server Management ...


5

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 installing SQL server. Don't think of it as a wrapper for T-SQL, think of it as a place to run T-SQL (and do all kinds of other very useful things). (SSIS is the ...


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