Archive

Posts Tagged ‘SQL’

Tell Microsoft To Fix The Sql Server Management Studio “Connect to Server” Dialog Position

October 17th, 2014 1 comment

If you use Sql Server Management Studio (SSMS) with multiple monitors, you likely run into the issue where the “Connect to Server” dialog window opens up either half or completely off the screen when SSMS is opened on a monitor that is not the primary one (see screenshot below).

Several bugs have been reported for this, and apparently MS thinks it is not really an issue since they have decided to close all of the bugs related to it as “Won’t Fix”. Here’s a quote:

We took a look at this bug and triaged it against several others and unfortunately, it did not meet the bar to be fixed and we are closing it as ‘won’t fix’.

Why they admit that it is a problem and close it as “Won’t Fix” instead of just leaving it open with a low priority is beyond me.

What’s even more surprising though is that these issues currently have less than 10 upvotes!  Let’s fix that. Like many people, I use SSMS daily, and this is easily my biggest beef with it, especially since the fix is so simple (literally 3 clicks on a Windows Forms or WPF app).

Please go to the following 3 Connect bugs and up-vote them so MS reconsiders fixing this.

1. https://connect.microsoft.com/SQLServer/feedback/details/755689/sql-server-management-studio-connect-to-server-popup-dialog

2. https://connect.microsoft.com/SQLServer/feedback/details/724909/connection-dialog-appears-off-screen

3. https://connect.microsoft.com/SQLServer/feedback/details/389165/sql-server-management-studio-gets-confused-dealing-with-multiple-displays

 

Here’s a screenshot of the problem. Here my secondary monitors are above my primary one, but the same problem occurs even if all monitors are horizontal to one another.

Sql Management Studio Multi-Monitor Bug

Using MSBuild to publish a VS 2012 SSDT .sqlproj database project the same way as a VS 2010 .dbproj database project (using command line arguments to specify the database to publish to)

March 18th, 2013 20 comments

Post and code updated on March 21, 2013, and again on March 22, 2013.

We recently upgraded from VS (Visual Studio) 2010 to VS 2012, and with it had to upgrade our .dbproj database project to a .sqlproj.  When making the switch I realized that .sqlproj database projects do not support specifying the database to deploy to as MSBuild command line arguments; instead you have to pass in the path to an xml file that has the necessary information.

So with the old .dbproj database project, you could deploy it to a database using:

MSBuild /t:Deploy /p:TargetDatabase="[DbName]";TargetConnectionString="Data Source=[Db.Server];Integrated Security=True;Pooling=False" /p:DeployToDatabase="True" "[PathToBranch]Database\Database.dbproj"

But with the new .sqlproj database project you have to do:

MSBuild /t:Publish /p:SqlPublishProfilePath="myPublishFile.publish.xml" "[PathToBranch]Database\Database.sqlproj"

Where “myPublishFile.publish.xml” contains the database server and name to publish to.

One other minor thing to note is that it is called “deploying” the database with .dbproj, and is called “publishing” the database with .sqlproj; so when I say Deploy or Publish, I mean the same thing.

We use TFS at my organization and while making new builds for our Test environment, we have the build process deploy the database solution to our various Test databases.  This would mean that for us I would either need to:

1 – create a new [DbName].publish.xml file for each database, check it into source control, and update the build template to know about the new file, or

2 – update the file contents of our myPublishFile.publish.xml file dynamically during the build to replace the Database Name and Server in the file before publishing to the database (i.e. read in file contents, replace string, write file contents back to file, publish to DB, repeat).

Option 1 means more work every time I want to add a new Test database to publish to.  Option 2 is better, but still means having to update my TF Build template and create a new activity to read/write the new contents to the file.

Instead, there is a 3rd option, which is to simply add the code below to the bottom of the .sqlproj file.  This will add some new MSBuild targets to the .sqlproj that will allow us to specify the database name and connection string using similar MSBuild command line parameters that we used to deploy the .dbproj project.

The code presented here is based on this post, but the author has closed the comments section on that post and has not replied to my emails about the bugs in his code and example, so I thought I would share my modified and enhanced solution.

  <!-- 
	Custom targets and properties added so that we can specify the database to publish to using command line parameters with VS 2012 .sqlproj projects, like we did with VS 2010 .dbproj projects.
	This allows us to specify the MSBuild command-line parameters TargetDatabaseName, and TargetConnectionString when Publishing, and PublishToDatabase when Building.
	I also stumbled across the undocumented parameter, PublishScriptFileName, which can be used to specify the generated sql script file name, just like DeployScriptFileName used to in VS 2010 .dbproj projects.
	Taken from: http://blog.danskingdom.com/using-msbuild-to-publish-a-vs-2012-ssdt-sqlproj-database-project-the-same-way-as-a-vs-2010-dbproj-database-project/
  -->
  <PropertyGroup Condition="'$(TargetDatabaseName)' != '' Or '$(TargetConnectionString)' != ''">
    <PublishToDatabase Condition="'$(PublishToDatabase)' == ''">False</PublishToDatabase>
    <TargetConnectionStringXml Condition="'$(TargetConnectionString)' != ''">
      &lt;TargetConnectionString xdt:Transform="Replace"&gt;$(TargetConnectionString)&lt;/TargetConnectionString&gt;
    </TargetConnectionStringXml>
    <TargetDatabaseXml Condition="'$(TargetDatabaseName)' != ''">
      &lt;TargetDatabaseName xdt:Transform="Replace"&gt;$(TargetDatabaseName)&lt;/TargetDatabaseName&gt;
    </TargetDatabaseXml>
    <TransformPublishXml>
        &lt;Project xmlns:xdt="http://schemas.microsoft.com/XML-Document-Transform" xmlns="http://schemas.microsoft.com/developer/msbuild/2003"&gt;
        &lt;PropertyGroup&gt;$(TargetConnectionStringXml)$(TargetDatabaseXml)&lt;/PropertyGroup&gt;
        &lt;/Project&gt;
    </TransformPublishXml>
    <SqlPublishProfilePath Condition="'$([System.IO.Path]::IsPathRooted($(SqlPublishProfilePath)))' == 'False'">$(MSBuildProjectDirectory)\$(SqlPublishProfilePath)</SqlPublishProfilePath>
    <!-- In order to do a transform, we HAVE to change the SqlPublishProfilePath -->
    <TransformOutputFile>$(MSBuildProjectDirectory)\Transformed_$(TargetDatabaseName).publish.xml</TransformOutputFile>
    <TransformScope>$([System.IO.Path]::GetFullPath($(MSBuildProjectDirectory)))</TransformScope>
    <TransformStackTraceEnabled Condition="'$(TransformStackTraceEnabled)'==''">False</TransformStackTraceEnabled>
  </PropertyGroup>
  <Target Name="AfterBuild" Condition="'$(PublishToDatabase)'=='True'">
    <CallTarget Targets="Publish" />
  </Target>
  <UsingTask TaskName="ParameterizeTransformXml" AssemblyFile="$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v$(VisualStudioVersion)\Web\Microsoft.Web.Publishing.Tasks.dll" />
  <Target Name="BeforePublish" Condition="'$(TargetDatabaseName)' != '' Or '$(TargetConnectionString)' != ''">
    <Message Text="TargetDatabaseName = '$(TargetDatabaseName)', TargetConnectionString = '$(TargetConnectionString)', PublishScriptFileName = '$(PublishScriptFileName)', Transformed Sql Publish Profile Path = '$(TransformOutputFile)'" Importance="high" />
    <!-- If TargetDatabaseName or TargetConnectionString, is passed in then we use the tokenize transform to create a parameterized sql publish file -->
    <Error Condition="!Exists($(SqlPublishProfilePath))" Text="The SqlPublishProfilePath '$(SqlPublishProfilePath)' does not exist, please specify a valid file using msbuild /p:SqlPublishProfilePath='Path'" />
    <ParameterizeTransformXml Source="$(SqlPublishProfilePath)" IsSourceAFile="True" Transform="$(TransformPublishXml)" IsTransformAFile="False" Destination="$(TransformOutputFile)" IsDestinationAFile="True" Scope="$(TransformScope)" StackTrace="$(TransformStackTraceEnabled)" SourceRootPath="$(MSBuildProjectDirectory)" />
    <PropertyGroup>
      <SqlPublishProfilePath>$(TransformOutputFile)</SqlPublishProfilePath>
    </PropertyGroup>
  </Target>

 

So after adding this code at the bottom of the .sqlproj file (above the </Project> tag though), you can now build and publish the database solution from the MSBuild command line using:

MSBuild /t:Build /p:TargetDatabaseName="[DbName]";TargetConnectionString="Data Source=[Db.Server];Integrated Security=True;Pooling=False" /p:PublishToDatabase="True" /p:SqlPublishProfilePath="Template.publish.xml" "[PathToBranch]\Database\Database.sqlproj"

Here you can see the 3 new parameters that we’ve added being used: TargetDatabaseName, TargetConnectionString, and PublishToDatabase.

When the TargetDatabaseName or TargetConnectionString parameters are provided we generated a new transformed .publish.xml file, which is the same as the provided “Template.publish.xml” file, but with the database and connection string values replaced with the provided values.

The PublishToDatabase parameter allows us to publish to the database immediately after the project is built; without this you would have to first call MSBuild to Build the database project, and then call MSBuild again to Publish it (or perhaps using “/t:Build;Publish” would work, but I didn’t test that).

If you want to simply publish the database project without building first (generally not recommended), you can do:

MSBuild /t:Publish /p:TargetDatabaseName="[DbName]";TargetConnectionString="Data Source=[Db.Server];Integrated Security=True;Pooling=False" /p:SqlPublishProfilePath="Template.publish.xml" "[PathToBranch]\Database\Database.sqlproj"

Be careful though, since if you don’t do a Build first, any changes that have been made since the last time the .sqlproj file was built on your machine won’t be published to the database.

Notice that I still have to provide a path to the template publish.xml file to transform, and that the path to this file is relative to the .sqlproj file (in this example the Template.publish.xml and .sqlproj files are in the same directory).  You can simply use one of the publish.xml files generated by Visual Studio, and then the TargetDatabaseName and TargetConnectionString xml element values will be replaced with those given in the command line parameters.  This allows you to still define any other publish settings as usual in the xml file.

Also notice that the PublishToDatabase parameter is only used when doing a Build, not a Publish; providing it when doing a Publish will not hurt anything though.

While creating my solution, I also accidentally stumbled upon what seems to be an undocumented SSDT parameter, PublishScriptFileName.  While the DeployScriptFileName parameter could be used in VS 2010 .dbproj projects to change the name of the generated .sql file, I noticed that changing its value in the .publish.xml file didn’t seem to have any affect at all (so I’m not really sure why Visual Studio puts it in there).  I randomly decided to try passing in PublishScriptFileName from the command line, and blamo, it worked!  I tried changing the <DeployScriptFileName> element in the .publish.xml file to <PublishScriptFileName>, but it still didn’t seem to have any effect.

So now if I wanted to deploy my database project to 3 separate databases, I could do so with the following code to first Build the project, and the Publish it to the 3 databases:

MSBuild /t:Build "[PathToBranch]\Database\Database.sqlproj"
MSBuild /t:Publish /p:TargetDatabaseName="[DbName1]";TargetConnectionString="Data Source=[Db.Server];Integrated Security=True;Pooling=False" /p:PublishScriptFileName="[DbName1].sql" /p:SqlPublishProfilePath="Template.publish.xml" "[PathToBranch]\Database\Database.sqlproj"
MSBuild /t:Publish /p:TargetDatabaseName="[DbName2]";TargetConnectionString="Data Source=[Db.Server];Integrated Security=True;Pooling=False" /p:PublishScriptFileName="[DbName2].sql" /p:SqlPublishProfilePath="Template.publish.xml" "[PathToBranch]\Database\Database.sqlproj"
MSBuild /t:Publish /p:TargetDatabaseName="[DbName3]";TargetConnectionString="Data Source=[Db.Server];Integrated Security=True;Pooling=False" /p:PublishScriptFileName="[DbName3].sql" /p:SqlPublishProfilePath="Template.publish.xml" "[PathToBranch]\Database\Database.sqlproj"

You could also instead just call MSBuild using the Build target with the PublishToDatabase parameter (which might actually be the safer bet); whatever you prefer.  I have found that once the database project is built once, as long as no changes are made to it then subsequent “builds” of the project only take a second or two since it detects that no changes have been made and skips doing the build.

If you have any questions or feedback, let me know.

Happy coding!

SQL Server script commands to check if object exists and drop it

September 14th, 2012 6 comments

Over the past couple years I’ve been keeping track of common SQL Server script commands that I use so I don’t have to constantly Google them.  Most of them are how to check if a SQL Server object exists before dropping it.  I thought others might find these useful to have them all in one place, so here you go:

--===============================
-- Create a new table and add keys and constraints
--===============================
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TableName' AND TABLE_SCHEMA='dbo')
BEGIN
	CREATE TABLE [dbo].[TableName] 
	(
		[ColumnName1] INT NOT NULL, -- To have a field auto-increment add IDENTITY(1,1)
		[ColumnName2] INT NULL,
		[ColumnName3] VARCHAR(30) NOT NULL DEFAULT('')
	)

	-- Add the table's primary key
	ALTER TABLE [dbo].[TableName] ADD CONSTRAINT [PK_TableName] PRIMARY KEY NONCLUSTERED
	(
		[ColumnName1], 
		[ColumnName2]
	)
	
	-- Add a foreign key constraint
	ALTER TABLE [dbo].[TableName] WITH CHECK ADD CONSTRAINT [FK_Name] FOREIGN KEY
	(
		[ColumnName1], 
		[ColumnName2]
	)
	REFERENCES [dbo].[Table2Name] 
	(
		[OtherColumnName1], 
		[OtherColumnName2]
	)
	
	-- Add indexes on columns that are often used for retrieval
	CREATE INDEX IN_ColumnNames ON [dbo].[TableName]
	(
		[ColumnName2],
		[ColumnName3]
	)
	
	-- Add a check constraint
	ALTER TABLE [dbo].[TableName] WITH CHECK ADD CONSTRAINT [CH_Name] CHECK (([ColumnName] >= 0.0000))
END

--===============================
-- Add a new column to an existing table
--===============================
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA='dbo'
	AND TABLE_NAME = 'TableName' AND COLUMN_NAME = 'ColumnName')
BEGIN
	ALTER TABLE [dbo].[TableName] ADD [ColumnName] INT NOT NULL DEFAULT(0)
	
	-- Add a description extended property to the column to specify what its purpose is.
	EXEC sys.sp_addextendedproperty @name=N'MS_Description', 
		@value = N'Add column comments here, describing what this column is for.' , 
		@level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',
		@level1name = N'TableName', @level2type=N'COLUMN',
		@level2name = N'ColumnName'
END

--===============================
-- Drop a table
--===============================
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TableName' AND TABLE_SCHEMA='dbo')
BEGIN
	EXEC('DROP TABLE [dbo].[TableName]')
END

--===============================
-- Drop a view
--===============================
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'ViewName' AND TABLE_SCHEMA='dbo')
BEGIN
	EXEC('DROP VIEW [dbo].[ViewName]')
END

--===============================
-- Drop a column
--===============================
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA='dbo'
	AND TABLE_NAME = 'TableName' AND COLUMN_NAME = 'ColumnName')
BEGIN

	-- If the column has an extended property, drop it first.
	IF EXISTS (SELECT * FROM sys.fn_listExtendedProperty(N'MS_Description', N'SCHEMA', N'dbo', N'Table',
				N'TableName', N'COLUMN', N'ColumnName'))
	BEGIN
		EXEC sys.sp_dropextendedproperty @name=N'MS_Description', 
			@level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',
			@level1name = N'TableName', @level2type=N'COLUMN',
			@level2name = N'ColumnName'
	END

	EXEC('ALTER TABLE [dbo].[TableName] DROP COLUMN [ColumnName]')
END

--===============================
-- Drop Primary key constraint
--===============================
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='PRIMARY KEY' AND TABLE_SCHEMA='dbo'
		AND TABLE_NAME = 'TableName' AND CONSTRAINT_NAME = 'PK_Name')
BEGIN
	EXEC('ALTER TABLE [dbo].[TableName] DROP CONSTRAINT [PK_Name]')
END

--===============================
-- Drop Foreign key constraint
--===============================
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='FOREIGN KEY' AND TABLE_SCHEMA='dbo'
		AND TABLE_NAME = 'TableName' AND CONSTRAINT_NAME = 'FK_Name')
BEGIN
	EXEC('ALTER TABLE [dbo].[TableName] DROP CONSTRAINT [FK_Name]')
END

--===============================
-- Drop Unique key constraint
--===============================
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='UNIQUE' AND TABLE_SCHEMA='dbo'
		AND TABLE_NAME = 'TableName' AND CONSTRAINT_NAME = 'UNI_Name')
BEGIN
	EXEC('ALTER TABLE [dbo].[TableNames] DROP CONSTRAINT [UNI_Name]')
END

--===============================
-- Drop Check constraint
--===============================
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='CHECK' AND TABLE_SCHEMA='dbo'
		AND TABLE_NAME = 'TableName' AND CONSTRAINT_NAME = 'CH_Name')
BEGIN
	EXEC('ALTER TABLE [dbo].[TableName] DROP CONSTRAINT [CH_Name]')
END

--===============================
-- Drop a column's Default value constraint
--===============================
DECLARE @ConstraintName VARCHAR(100)
SET @ConstraintName = (SELECT TOP 1 s.name FROM sys.sysobjects s JOIN sys.syscolumns c ON s.parent_obj=c.id
						WHERE s.xtype='d' AND c.cdefault=s.id 
						AND parent_obj = OBJECT_ID('TableName') AND c.name ='ColumnName')

IF @ConstraintName IS NOT NULL
BEGIN
	EXEC('ALTER TABLE [dbo].[TableName] DROP CONSTRAINT ' + @ConstraintName)
END

--===============================
-- Example of how to drop dynamically named Unique constraint
--===============================
DECLARE @ConstraintName VARCHAR(100)
SET @ConstraintName = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
						WHERE CONSTRAINT_TYPE='UNIQUE' AND TABLE_SCHEMA='dbo'
						AND TABLE_NAME = 'TableName' AND CONSTRAINT_NAME LIKE 'FirstPartOfConstraintName%')

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='UNIQUE' AND TABLE_SCHEMA='dbo'
		AND TABLE_NAME = 'TableName' AND CONSTRAINT_NAME = @ConstraintName)
BEGIN
	EXEC('ALTER TABLE [dbo].[TableName] DROP CONSTRAINT ' + @ConstraintName)
END

--===============================
-- Check for and drop a temp table
--===============================
IF OBJECT_ID('tempdb..#TableName') IS NOT NULL DROP TABLE #TableName

--===============================
-- Drop a stored procedure
--===============================
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE='PROCEDURE' AND ROUTINE_SCHEMA='dbo' AND
		ROUTINE_NAME = 'StoredProcedureName')
BEGIN
	EXEC('DROP PROCEDURE [dbo].[StoredProcedureName]')
END

--===============================
-- Drop a UDF
--===============================
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE='FUNCTION' AND ROUTINE_SCHEMA='dbo' AND 
		ROUTINE_NAME = 'UDFName')
BEGIN
	EXEC('DROP FUNCTION [dbo].[UDFName]')
END

--===============================
-- Drop an Index
--===============================
IF EXISTS (SELECT * FROM SYS.INDEXES WHERE name = 'IndexName')
BEGIN
	EXEC('DROP INDEX TableName.IndexName')
END

--===============================
-- Drop a Schema
--===============================
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'SchemaName')
BEGIN
	EXEC('DROP SCHEMA SchemaName')
END

--===============================
-- Drop a Trigger
--===============================
IF EXISTS (SELECT * FROM SYS.TRIGGERS WHERE NAME = 'TriggerName')
BEGIN
	EXEC('DROP TRIGGER TriggerName')
END

You may have noticed that I wrap the actual DROP statements in an EXEC statement.  This is because if you run the script once and it drops the schema object, if you try to run the script a second time SQL may complain that the schema object does not exist, and won’t allow you to run the script; sort of like failing a compile-time check.  This seems stupid though since we check if the object exists before dropping it, but the “SQL compiler” doesn’t know that.  So to avoid this we convert the drop statement to a string and put it in an EXEC, so that it is not evaluated until “run-time”, and since the IF EXISTS checks prevent that code from being executed if the schema object does not exist, everything works fine.

Happy coding!

Categories: SQL Tags: , , , , ,