I’ve previously posted about how to cope with nullable values retrieved from a SqlDataReader but it’s also necessary to account for nulls when passing values through to a SQL operation such as a stored procedure. sp__set_completed.sql If you want you’re stored procedure to accept nulls then you first need to Read more…
I have a table in Azure Table Storage that I need to keep as a replica of a local SQL table. Adding/updating rows from the local table is simple enough but it is slightly more complicated to find rows that have been removed from the local table and therefore need Read more…
If you’re using VARCHAR(MAX) columns in SQL then you may occasionally want to view the stored values in SQL Server Management Studio (SSMS) as part of the debugging process. Because a VARCHAR(MAX) field can store up to 2GB of data SSMS truncates the data returned to 8200 characters in the Read more…
If you want to get a random string in SQL then you could use newid to generate a GUID and take the alphanumeric characters from it, however while these should obviously be unique from each other they don’t contain the full range of characters that may be desired and sequential Read more…
I use IdentityServer to manage access to a few client APIs with the configuration details stored in SQL. The one downside to using IdentityServer is that there doesn’t yet seem to be a good option for administration of credentials, though Skoruba.IdentityServer4.Admin certainly looks promising. In order to add new clients Read more…
Below is a SQL query for getting database sizes borrowed from this post. Unfortunately it doesn’t work on Azure SQL databases unless you’ve been granted VIEW_DATABASE_STATE permissions.
IdentityServer4 is an OpenID Connect and OAuth 2.0 framework for ASP.NET Core 2 which can be used to manage authentication for web applications. In my case I wanted to set up OAuth 2.0 authentication using a SQL backend for an API, this isn’t too tricky when you know what you’re Read more…
Using SQL Task Results in a Script Task in SSDT In SSDT if you want to access the results of a SQL query in a script task you can do this by outputting the results as a full result set into a variable of type Object and then load this Read more…
Exporting an Azure SQL Databases Using .NET It’s possible to export an Azure SQL database to a BACPAC file using the Azure portal and PowerShell among other methods but the documentation makes no mention of doing this directly in .NET, though happily this is also possible. The process detailed below Read more…
Thread Safe DataTable Inserts and SqlBulkCopy As part of a project where I was running multiple threads making API calls like this I was making a stored procedure call to log the results to an SQL database for each result returned. This worked fine when a low number of threads Read more…