Aaron Hoffman's Blog

Often when working with a new data set I'll run these queries to learn more about each column of data. https://gist.github.com/aaronhoffman/eb30805ee2f5cafc64152dd1def800bd For example, you can run a single query to union together aggregate data on each numeric column. Result looks something like this: Hope this helps, Aaron

We've been working with Azure WebJobs recently (more info: here and here ) and even though the built in logging to Azure Storage is great, it is difficult to query at times. In more recent version of the WebJobs SDK, the team exposed a TraceWriter collection via the JobHost configuration that allows consumers to write logs to a custom repository. The snippet linked below is a naive implementation of using that collection to write the logs to SQL Server. Feel free to adapt it to fit your needs (i.e. performance concerns, async, batching, etc.) Write Azure WebJob Logs to SQL: https://gist.github.com/aaronhoffman/3e319cf519eb8bf76c8f3e4fa6f1b4ae Hope this helps, Aaron

If you enable File System Web Server logs for your Azure App Service... Settings > Diagnostics logs > Web server logging > File System You'll start to see logs on the file system for your app service here: (you can find your FTP host and credentials in the publish profile file) /LogFiles/http/RawLogs Alternatively, you can see these logs through the Kudu UI: https://{yourappservicename}.scm.azurewebsites.net/DebugConsole/?shell=powershell Instead of downloading these one and a time and parsing through them, you can use the following library to assist in loading them into a relational database. You could even run this as a webjob within your app service. Get the code here: https://github.com/Stonefinch/AzureWebServerLogsToSql **Update: This repo now includes a web project that references the console app as a WebJob. You may also want to check out the LogParser tool by Microsoft https://blogs.msdn.microsoft.com/friis/2014/02/06/how-to-...

Microsoft Azure SQL only allows connections from whitelisted IP addresses. My ISP seemed to change my external/public IP address daily. The combination of these two things was very annoying for me. I assembled the powershell script below to make my life a little easier. It updates the firewall rule for each Azure SQL instance, and ensures they are all set to my current IP address. Note: There are two categories of Azure PowerShell scripts/cmdlets. There are Resource Management cmdlets and Service Management cmdlets. I will describe how to perform this task with both below: Service Management (the older way) 1. Install and Configure  https://github.com/Azure/azure-powershell#installation 2. Execute Get-AzurePublishSettingsFile to get the publishsettings file for the subscription you're going to be working with (a browser window will open, select the profile there. Note: you can add multiple publish settings.). 3. Execute Import-AzurePublishSettingsF...

I had a need to convert the HEX output from SQL Server Management Studio's Results to Grid output of a varbinary column  back to the byte array of the file it represented. I couldn't find an all-in-one script online, so I thought I'd create this C# gist. (original parse script here ) https://gist.github.com/aaronhoffman/ad16b27d14e2f5d7e16c Hope this helps, Aaron

I recently refactored some code that was performing date arithmetic in SQL Server. The code was attempting to retrieve the "same day next year" by adding 365 days to the date provided. I was refactoring this logic out of SQL Server and in to .NET, and I was concerned that .NET's DateTime arithmetic wouldn't match SQL Server. I was pleasantly surprised that the results were the same. However, I should call out that perhaps in your situation adding 365 days to a given date is not appropriate for getting the "same day next year" value. I also compared this logic when preforming a "Add years + 1" and the results were also the same. (However, to be clear, adding 365 days differed from adding 1 year, but both SQL and .NET preformed the same given the method used.) source code:  https://gist.github.com/aaronhoffman/3d997390dab7f69e6597 results table: Method Value StartDate .NET Result SQL Result AddDays 365 2/27/2011 2/27/2012 2/27/2012 AddYears ...

When working with data with the intent to visualize, there are times when I'll want to group by every column in a table, separately, one at a time, to determine the possible values in that column. I do this often enough I created a simple SQL Query to build these group by statements for me: declare @table_name varchar(200) = 'dbo.mytablename' select 'select ' + c.name + ', count(1) cnt from ' + @table_name + ' group by ' + c.name + ' order by 2 ' from sys.columns c where c.object_id = object_id(@table_name) gist:  https://gist.github.com/aaronhoffman/d49d3705fd716b0fe6cb Hope this helps, Aaron