SSMS – How to turn off “Prevent saving changes that require table to be re-created”

Problem You’re trying to save a table change in SSMS and you get the error message: Saving changes is not permitted. The changes that you have made require the following tables to be dropped and re-created. You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes … Read more SSMS – How to turn off “Prevent saving changes that require table to be re-created”

KeyNotFoundException: The given key was not present in the dictionary

Problem The following exception is thrown when you try to get a value from a dictionary using a key that doesn’t exist in the dictionary: KeyNotFoundException: ‘The given key was not present in the dictionary.’ Consider the following the example: When the user enters a city name that isn’t in the dictionary, such as “New … Read more KeyNotFoundException: The given key was not present in the dictionary

SqlTypeException: SqlDateTime overflow

Problem I am executing a SQL query and trying to set a datetime column to DateTime.MinValue. I’m getting the following exception: System.Data.SqlTypes.SqlTypeException: ‘SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.’ The problem is that DateTime.MinValue is 1/1/0001 12:00:00 AM and the SQL Server DateTime minimum value is 1/1/1753 12:00 AM. Solution … Read more SqlTypeException: SqlDateTime overflow

Using ORDER BY with CASE – Conversion failed when converting date and/or time from character string

Problem When I execute a SQL query I’m getting the following error: Conversion failed when converting date and/or time from character string. The query is using ORDER BY with CASE with a passed in parameter called @OrderBy. Solution Use a separate CASE statement for each possible value (instead of one CASE with multiple WHENs).

‘CREATE/ALTER PROCEDURE’ must be the first statement in a query batch

Problem You have a SQL query like this: While executing this you get the following error: ‘CREATE/ALTER PROCEDURE’ must be the first statement in a query batch Solution The error message says it all: the CREATE PROCEDURE statement cannot be executed after other queries in the same batch of queries. The solution is to execute … Read more ‘CREATE/ALTER PROCEDURE’ must be the first statement in a query batch

Event-driven .NET: How to use query notifications in SQL Server to monitor database changes

How do you check for new records in a database and react to the new data? You really only have two options: Poll for changes every X seconds Use query notifications to be notified when new data is inserted In this article I’ll show you how to configure query notifications so that your application receives … Read more Event-driven .NET: How to use query notifications in SQL Server to monitor database changes

Find the distance between two coordinates using SQL and C#

Imagine you’re in the middle of Millennium Park in Chicago. You want some coffee, but want a good deal on it. You open an app and see the nearest coffee shops offering deals to anyone with this app. How did this app know the distance from you to the coffee shops? This article will show … Read more Find the distance between two coordinates using SQL and C#

How to install MySQL on Windows

We’re going to install MySQL Community Edition, which is the free edition of MySQL. It includes a database server (MySQL Server) and a GUI database client (MySQL Workbench), among other tools. Go to MySQL Community Downloads. Click on MySQL Installer for Windows. Download the web community installer. Run the installer you downloaded. On the Setup … Read more How to install MySQL on Windows

How to sort data into groups then further sort within each group using SQL

I recently came across a complex sorting problem that required sorting the data into groups, then further sorting the data within each group. Requirement My requirement is to sort a list of orders for users in charge of approving orders. Here is a list of orders with their status and department: Here is a list … Read more How to sort data into groups then further sort within each group using SQL

The specified sa password does not meet strong password requirements

Problem When entering the sa password while installing SQL Server you get the following error: “The specified sa password does not meet strong password requirements.“ Solution If the computer is on a domain Go to Administrative Tools / Domain Security Policy :: Security Settings | Account Policies | Password Policy Set “Minimum password length” = Not … Read more The specified sa password does not meet strong password requirements