SQL Server – Foreach Table

To execute a query against all tables in a database, you can use the sp_msForeachTable stored proc, like this: Note: The ? is a placeholder for the table name. In this article I’ll show two examples of using sp_msForeachTable. Example – Get record counts for all tables This query saves record counts to a temp … Read more SQL Server – Foreach Table

SQL Server – Get the next id using OUTPUT instead of IDENTITY or SEQUENCE

There are multiple ways to increment ids in SQL Server: IDENTITY columns SEQUENCEs (added in SQL Server 2012) Atomic UPDATEs with the OUTPUT clause In this article I’ll show the third way – using an atomic UPDATE with OUTPUT. Tables – Ids and Users I have the following two tables – Ids and Users: Stored … Read more SQL Server – Get the next id using OUTPUT instead of IDENTITY or SEQUENCE

PowerShell – Saving SQL query results to a CSV file

Here’s how to execute a SQL query and export the results to a CSV file: If Invoke-SqlCmd is missing, install the SqlServer module If it’s complaining about not having Invoke-SqlCmd available, you will need to install the SQL Server PowerShell module. 1 – Run PowerShell as administrator 2 – Add Microsoft’s PSGallery as a trusted … Read more PowerShell – Saving SQL query results to a CSV file

SQL – Use HAVING to filter GROUP BY

WHERE filters individual rows. HAVING filters aggregated rows. When you use GROUP BY, you are grouping rows together into aggregated rows. How do you filter the aggregated rows? You use the HAVING clause. Example of using HAVING Let’s say we want to see all NFL divisions where all teams have played in the Super Bowl … Read more SQL – Use HAVING to filter GROUP BY

C# – How to use IN with Dapper

Let’s say you have a SQL Query that uses IN and you want to execute it using Dapper. Your query looks something like this: Here’s how you’d execute that with Dapper: Then you’d call it like this: There are two key things to notice about this: You have to exclude the parentheses. I specified one … Read more C# – How to use IN with Dapper

SSMS – Change how many rows are returned by SELECT Top 1000 Rows

When you right-click a table in SSMS, you can “Select Top 1000 Rows” and “Edit Top 200 Rows.” You can change how many rows are returned by changing the defaults. Tools SQL Server Object Explorer Change the values under Table and View Options Change these values to whatever makes sense in your situation. Note: if … Read more SSMS – Change how many rows are returned by SELECT Top 1000 Rows

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).