Follow @endyourif rss Facebook LinkedIn

Entity Framework Stored Procedure Returns No Columns

Published on Feb 22, 2020

I was recently adding a new stored procedure to my Entity Framework EDMX (database first) when no columns were being recognized by EF. I was slightly confused by this, luckily I found this simple solution. At the top of your stored procedure definition, add SET FMTONLY OFF. I suggest only adding this setting temporarily; further explanation is below.

Add a column with a default value to an existing table in SQL Server

Published on Jan 29, 2020

For any given project that has been existence for any amount of time, at some point your table design will change and you will need to add a new column to your SQL table. More specifically, this value should be NOT NULL so you want/need to set a default value when adding the new column.

MSSQL Running an extra AND statement

Published on Jan 24, 2020

This had me baffled and shook me to the core as it went against everything I understand about an AND statement. I was using a standard CASE as I have done hundreds, if not thousands, of times that contained two AND statements. The first part of the statement returned false but, MSSQL still proceeded to execute the second statement. This may not seem like a big deal, but in this particular case I had an error producing data type mismatch problem. When the first part of the CASE was true the second part compared the data as a bigint whereas the actual value was an nvarchar, so it errored.

Let's take a look at an example.

Finding non-printable characters in MSSQL

Published on Aug 1, 2019

String fields in MSSQL accept non-printable characters such as tabs, line breaks, etc. When your application has not prevented this characters from being removed and you want to determine if these characters exist in your field, the UNICODE is your friend to answer this question.

Preventing race conditions with sp_getapplock

Published on Apr 17, 2019

I am a big proponent of storing calculated fields in the database opposed to calculating them each time they are displayed. They only change at specific times so you just need to cover those spots to calculate them. The danger with storing calculated fields is that concurrent calls have a chance incorrectly calculating the field. Languages such as C# offer the ability to lock objects to help with this. However, in a multi-server example, locking on a single server will not work. In this article I'm going to demonstrate how to use sp_getapplock to prevent race conditions.

Delete duplicate rows with MySQL or MSSQL

Published on Feb 27, 2019