Tag: MSSQLServer

T-SQL, yes please!

How to form a parameterized SQL query to find duplicates in a table.

So one day, I needed to quickly check if an identity field in a view in a Microsoft SQL Server was actually unique or not. I was running into weird issues with Entity Framework throwing an error somewhat like this: Store update, insert, or delete statement affected an unexpected number of rows (2) Or somewhat like this: Type: DbUpdateConcurrencyExceptionStore update, insert, or delete statement affected an unexpected number of rows ([row count]). Entities may have been modified or deleted since entities were loaded. See http://go.microsoft.com/fwlink/?LinkId=472540…Continue reading How to form a parameterized SQL query to find duplicates in a table.

Entity Framework - am I right?

“Conversion overflow” when reading numeric data from MS SQL database using Entity Framework

This article explains how to fix a weird situation where your .NET application utilizing Entity Framework or Entity Framework Core simply crashes without any way to recover or catch the Exception. You only get errors somewhere along the lines of “conversion overflow”. Ah – what a fun little issue! Problem So when you’re trying to instantiate an entity with a property of type decimal, you’ll get one of the 2 following exceptions with varying Call Stacks: Conversion overflow or: Arithmetic overflow error converting numeric to data type numeric…Continue reading “Conversion overflow” when reading numeric data from MS SQL database using Entity Framework

Microsoft SQL Server logo

How to clean up duplicates from MS SQL Database?

This article explains how to query and remove duplicate entries from a Microsoft SQL database using T-SQL. The same model works both for Azure Databases (in Azure SQL Query Editor) and databases on MS SQL Server (using SSMS – SQL Server Management Studio). I had to do this a while back due to a bug in an API creating multiple entities with the same content – save for the Identity field. Fun! Background Why bother? That’s a (somewhat) valid question. A table in a SQL…Continue reading How to clean up duplicates from MS SQL Database?

Microsoft SQL Server logo

How to connect to your local MSSQL server using SSMS?

Every now and then you run into a situation, where you really need to run some SQL against your local development database. That database, at least in my case, is hosted on your local SQL Server Express. Connecting to a local SQL Server should be a walk in a park, right? Eh, well… While using a connection string to connect to said DB is easy, you can’t do that with the SQL Server Management Studio. I wish you could, but hey – it is what…Continue reading How to connect to your local MSSQL server using SSMS?

Microsoft SQL Server logo

Easily querying all the database and log file sizes on SQL Server

Quite often you get the need to simply quickly query all the database sizes on MS SQL Server. Most typically, for me, this need arises when a development machine gets low on disk space, and I need to check if some database’s log size has been growing, or if something else is wrong. In my daily work, I rarely encounter this requirement anymore. Azure has almost entirely obliterated the need for such diagnostics, since it’s so easy to spin up or scale up new development…Continue reading Easily querying all the database and log file sizes on SQL Server