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 – 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

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

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

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

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