Reconnect Azure DevOps Server to SQL after server name change 20 August 2020 Donovan Work (0) In this post I show you how to update the connection string of Azure DevOps to connect to SQL after server name change. [More]
Fun with SQL Server Containers 07 October 2018 Donovan Work (1) In this post I show how to get started with SQL Server containers running on Windows. [More]
2015 Ignite New Zealand demo prep: Step 6 24 October 2015 Donovan-Brown Work (0) In this post we will setup the back end. [More]
How to setup a SonarQube server in Azure 17 June 2015 Donovan-Brown Work (8) SonarQube allows youto track and manage your technical debt. At the center of a SonarQubeinstallat [More]
I can’t use SqlPackage to deploy my dacpac to Azure Sql Database 08 February 2015 Donovan-Brown Work (0) Problem: Each time I try to use a dacpac to deploy to my Azure Sql Database it times out with the following error: Publishing to database ''mydatabase_dev' on server 'mydatabase.database.windows.net'. Initializing deployment (Start) Initializing deployment (Failed) SqlPackage.exe : *** An error occurred during deployment plan generation. Deployment cannot continue. At line:1 char:1 + & 'C:\Program Files (x86)\Microsoft SQL Server\120\DAC\bin\SqlPackage.exe' /Acti ... + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (*** An error oc...annot continue.:String) [], RemoteException + FullyQualifiedErrorId : NativeCommandError Failed to import target model mydatabase_dev. Detailed message Unable to reconnect to database: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. Unable to reconnect to database: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The wait operation timed out Solution: Select a Service Tier of Standard or higher. Explanation: When I compared this database to others that I have successfully deployed to I noticed this database was using the BASIC Service Tier. On all the databases where I have been able to deploy I had the STANDARD Service Tier selected. Once I upgraded my database to Standard I was able to deploy successfully. The post below sounds like there might be resolution coming in an update “Improved reliability when targeting Basic and Standard Performance Tiers in Microsoft Azure SQL Databases.” http://tinyurl.com/SqlV12Preview Until then just don't select basic as your service tier.
How to connect SQL Server Data Tools 2012 to TFS 2013 12 October 2014 Donovan-Brown Work (0) Problem: I installed SQL Server 2012 Standard Edition with SP1 with SQL Server Data Tools and I need to version my projects in TFS 2013. Solution: The IDE for SQL Server Data Tools is Visual Studio 2010 Shell. By installing Microsoft Visual Studio Team Explorer 2010, Microsoft Visual Studio 2010 Service Pack 1 and Visual Studio 2010 SP1 Team Foundation Server Compatibility GDR you will be able to connect to TFS 2013. You can read more about the compatibility between Team Foundation clients and Team Foundation Server here. Please note that when you connect to a more recent version of TFS than that of the client, you only have access to those features supported by your client. You cannot access any features that Visual Studio 2010 does not support.
How to install SQL Server Compact 4.0 Prerequisites for my setup project 13 January 2014 Donovan-Brown Work (0) Problem: I am missing SQL Server Compact 4.0 Prerequisites for my setup project. Solution: Use the Microsoft Web Platform Installer to install Visual Studio SP1 Tools for SQL Server Compact 4.0.
How do I grant permissions to ApplicationPoolIdentity in SQL Server 26 December 2013 Donovan-Brown Work (0) Problem: My IIS Application Pool is using ApplicationPoolIdentity and does not have permissions to my SQL Server database. Solution: Open Sql Server Management Studio (SSMS) and create a new login for "IIS AppPool\{YourAppPoolNam}". On the User Mapping page give the user access to the database and save and close the dialog.
How do I validate data in my database during a web test 25 August 2011 Donovan-Brown Work (0) Problem: The only way I can verify the success of my web test is to read a value from a database. Solution: Create a custom validation rule that can validate the value in the database. Code: using System; using System.ComponentModel; using System.Data.SqlClient; using Microsoft.VisualStudio.TestTools.WebTesting; namespace TestUtil { [DisplayName("SQL Validation Rule")] [Description("Executes the query and compares the first column of the first row to the Expected value.")] public class SQLValidationRule : ValidationRule { public SQLValidationRule() { IgnoreCase = true; } [Description("The query to execute and extract the first column from. In Select * from x where column={0}. You can leave the where clause if it is not needed.")] public string Query { get; set; } [DisplayName("Connection String")] [Description("The full connection string to the database")] public string ConnectionString { get; set; } [DisplayName("Where Clause Context Parameter Name")] [Description("The name of an optional context parameter to use if there is a where clause in the query.")] public string WhereClauseContextParameterName { get; set; } [DefaultValue(true)] [DisplayName("Ignore Case")] [Description("When set to true the case of the word is not used")] public bool IgnoreCase { get; set; } [DisplayName("Expected Value")] [Description("The value to compare the first column too.")] public string ExpectedValue { get; set; } public override void Validate(object sender, ValidationEventArgs e) { string where = null; if(!string.IsNullOrEmpty(WhereClauseContextParameterName)) where = e.WebTest.Context[WhereClauseContextParameterName].ToString(); string result = ExecuteQuery(ConnectionString, Query, where); e.IsValid = string.Compare(result, ExpectedValue, IgnoreCase) == 0; } private string ExecuteQuery(string connectionString, string query, string where) { SqlConnection conn = new SqlConnection(connectionString); string cmdText = string.Format(query, where); SqlCommand cmd = new SqlCommand(cmdText, conn); try { conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); if(dr.Read()) return dr.GetValue(0).ToString(); } catch(Exception e) { System.Diagnostics.Debug.WriteLine(e.Message); } finally { conn.Close(); } return null; } } } Explanation: Creating a custom validation rule for web test is extremely simple. Simply create a new public class that derives from Microsoft.VisualStudio.TestTools.WebTesting.ValidationRule and override the Validate method. If you create the class in your test project it will become immediately available the next time you try to add a validation rule to a web request. If you created the class in a separate class library simply add a reference to that class library in your test project. This particular validation rule has the five following properties: Query – This is the query to be executed on the database connection. Only the first column of the first record is used in the comparison of this validation rule. The query can have a single where condition in the where clause that uses a context parameter value. For example “Select Name from Table1 where ID={0}”. At runtime the validation rule will look up the value of the provided context parameter and replace {0} with the value stored in the context parameter. Using a context parameter is completely optional. Connection String – The connection string to a SQL Server database. This can also be a context parameter entered in {{ContextParameterName}} format. Otherwise you may simple enter a literal string. Where Clause Context Parameter Name – The context parameter to be used to replace the {0} if any of the query. Ignore Case – Identifies if case should be ignored or not during the string comparison. Expected Value – The value to compare the first column of the first row too. This can also be a context parameter entered in {{ContextParameterName}} format. Otherwise you may simple enter a literal string. You can download the file below. SQLValidationRule.cs (2.48 kb)
I can't run my scripts created by DBPro in SSMS 26 July 2011 Donovan-Brown Work (0) Problem When I try and run my deploy scripts from DBPro in SSMS I get syntax errors where variables are used. Solution Switch on SQL CMD Mode before you execute the SQL file. To enable the SQL CMD Mode select SQL CMD Mode from the Query menu in SSMS.