Visual Studio database project schema compare (.scmp) show delete all objects

Possibly save 1 hour of your time: If you are using schema compare in your database solution project within Visual Studio, the initial compare shows nothing on the project vs  your target database. It tells you the difference is to delete all objects such as tables and stored procedures.

The problem was that the scripts (.sql) files are added to the folder outside of Visual Studio. You need to right click on the folder and choose Add >> New Item. Then you can select under each SQL Server menu sub category and the SQL object.

sqlobject.png

Book Microsoft SQL Server 2016: A Beginner’s Guide, Sixth Edition

Advertisements

Sql Server database project template missing in Visual Studio

Possibly save 1 hour of your time: There is a good way to manage your database objects. You can create a solution project for your database as follows.

In Visual Studio, go to File >> New >> Project. You should see the template under Installed >> Templates >> Other Languages >> SQL Server >> SQL Server Database Project.

database project

If you cannot see the above template in Visual Studio 2015, then you need to get SSDT as follows.

Open Visual Studio (mine is 2015) >> choose Tools >> Extensions. Update Updates >> Product updates >> you may find the Microsoft SQL Server Data Tools (SSDT) update that contains the database project template. Select it and Update.

tools and extensions.png

You can see it is installing Microsoft SQL Server Data Tools v14.0.61707.300.

data tools.png

Alternatively, you can also download the tools from

https://msdn.microsoft.com/en-us/mt186501

Book: Microsoft SQL Server 2016: A Beginner’s Guide, Sixth Edition

Truncate local SQL Server database log file to save disk space

Possibly save 1 hour of your time: I notice my machine disk space is running low. So where can I save some space? Database log is usually a big one.

First, download a free program call WindirStat. This program will tell you in a very nice visual way of where most of your space are taken up.

So I found out from my machine there are two database log files in which both took up over 50 GB.

Solution: I backup the database first. Then I ran the following command on the specific database in SQL Server Management Studio 2012.

select * from sys.database_files --find the name of the log file

ALTER DATABASE your_database_name SET RECOVERY SIMPLE
DBCC SHRINKFILE('name_of_log', 0, TRUNCATEONLY)
ALTER DATABASE your_database_name SET RECOVERY FULL

Murach’s SQL Server 2016 for Developers

sql server seed identity reset

Possibly save 1 hour of your time: Sometimes after deleting some reference data in a table one want to change the identity to reseed at 1 or a certain value. The next time you insert a new record it will reseed starting at 1.

DBCC CHECKIDENT ('YourTableName', RESEED, 0);
GO

Introducing Microsoft SQL Server 2016: Mission-Critical Applications, Deeper Insights, Hyperscale Cloud

dacpac post deployment script not running

Possibly save 1 hour of your time: In your database project, you may set it to build as a dacpac. Then you can use this dacpac to deploy scripts to your database. However, after you setup a post deployment script, it still didn’t seem to execute after you deploy the dacpac to your target database. Here are some guidance:

  1. If you haven’t already, you can add post deployment script by right click any folder in your project and choose Add >> Script. This script should contain your script or point to another script using notation :r .\myfile.sql. Use doubt quotes to wrap file path/name with space.
  2. After add script, right click the file and ensure Build Action is set to PostDeploy.
  3. Now build the project.
  4. Go to bin folder and find the dacpac. You can right click on this file and unpack it somewhere. Once unpacked, please verify there is a file name postdeploy.sql with the script code inside. This should be it you script will be execute when you deploy your dacpac. Make sure you are targeting the right database and verify using the right database. I got burned once looking at a different staging database.

Command for deploying dacpac:

“C:\Program Files\Microsoft SQL Server\130\DAC\bin\sqlpackage.exe” /Action:Publish /SourceFile:”Your Database.dacpac” /TargetDatabaseName:YourDatabaseName /TargetServerName:localhost /p:ExcludeObjectTypes=”ApplicationRoles;DatabaseRoles;Permissions;RoleMembership;Users;Credentials;ServerRoleMembership;ServerRoles” /p:BlockOnPossibleDataLoss=False

Introducing Microsoft SQL Server 2016: Mission-Critical Applications, Deeper Insights, Hyperscale Cloud

Error: SQL71006: Only one statement is allowed per batch. A batch separator, such as ‘GO’, might be required between statements.

Possibly save 1 hour of your time: This error happens when I am adding a post deploy script as part of my database project and then do a build.

When you add this script file to your project, you need to right click the properties and set Build Action to None. This should solve the issue.

Exam Ref 70-764 Administering a SQL Database Infrastructure

Trigger and execute SQL Server Agent Job

Possibly save 2 hours of your time:

Recently, I was asked to find a generic way for non-technical administrators to be able to trigger or execute SQL Server Agent Job on their own on demand.

One solution is to build a windows authenticate administration website within our network domain. This web application will use windows authentication authenticate against specific windows user/group that has permission to trigger/execute a back end job.

public Exception ExecuteSqlAgentJob(string connectionString, string jobName)
{
Exception exception = null;

var dbConn = new SqlConnection(connectionString);
var execJob = new SqlCommand();
execJob.CommandType = CommandType.StoredProcedure;
execJob.CommandText = “msdb.dbo.sp_start_job”;
execJob.Parameters.AddWithValue(“@job_name”, jobName);
execJob.Connection = dbConn;

try
{
using (dbConn)
{
dbConn.Open();
using (execJob)
{
execJob.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
exception = ex;
}

return exception;
}

The application pool identity that runs this application should have access to:

  • the database which the SQL job targets
    • Go to specific database >> Security >> Users >> add login
  • msdb database
    • Go to msdb database >> Security >> Users >> add login
    • And give it SQLAgentOperatorRole membership
    • This gives permission to execute dbo.sp_start_job
    • Note: Members of SQLAgentUserRole and SQLAgentReaderRole can only start jobs that they own. Members of SQLAgentOperatorRole can start all local jobs including those that are owned by other users.