Finding your way around an unfamiliar database

It can sometimes be difficult to find where various values are being stored in a database you are unfamiliar with, this is a T-SQL script I wrote some time ago when I was trying to find my way around a TechOne database and it came in handy back then and it recently came in handy once again when I was querying a Connectwise database.

What this script does is it cursors through all the tables and all the columns on those tables and counts the occurrences of the value you are searching for, and then gives you a result set listing which columns on which tables contain that value.


Warning: Think carefully before running this against your databases, this could cause performance issues.

The following query is very handy for finding which stored procedures, views & functions are referencing various values:

P = Stored procedure
V = View
TF = Table function
FN = Scalar function
AF = Aggregate function (CLR)
TR = SQL DML Trigger

You can find a full list of xtype values here:

Developing JSLink SharePoint forms with workflows and associated issues – Part 1

I’ll start by saying I am not a fan of workflows, I find they generally introduce a lot of unnecessary problems, I much prefer to just develop a proper system with backend code and not some pointy clicky workflow BS.

However we can’t alway choose which technologies we develop a solution in, sometimes inappropriate technologies a thrust upon us from above.

I am a fan of JSLink though and in my opinion it’s a much better  forms solution in SharePoint than InfoPath ever was, I have seen some truly horrifying InfoPath forms in my time and I have been trying to talk people out of using InfoPath for years so I was very happy the day it was announced that InfoPath was deprecated.

Anyway I’ve developed a few solutions using JSLink and various workflow technologies now, so I’ve decided to write a bit about some of the approaches that I have found to work quite well, I’m going to break this up into a number of articles and the first topic will be how to handle workflow tasks.

How to handle with workflow tasks

So one approach I’ve found works is instead of directing the user to the workflow task, direct them to the form for the item the task is related to (the original list item), then once the user has done what they need to do in the original item complete the workflow task via a REST web service call.

Now when you use this approach it is important that you save the original list item before you complete the task items otherwise you will get save conflicts, and this is because when you complete the workflow task it will fire up the workflow which will then alter the item you are working on.

This is where it starts getting tricky, when you save the list item you will automatically get redirected to either the list view or the “Source” url preventing your from making the necessary REST calls to complete the workflow task.

So to get around this we need to override the default redirection behaviour of the form which works like this:

Once you have overridden the default redirect behaviour and saved the form your can now complete the workflow task like this:

Note: I have not included querying of the tasks list to find the associated tasks, you will need to do this to get the id of the associated task, in my example above I have hardcoded this id as 1.

Using this approach I find is much more intuitive for the user, and we are not using the workflow task to collect information from the user we are simply using the task to indicate that there is action required of the user, any data that is collected from the user is captured in the original list item where it is needed.

Note: In these examples I have not given much consideration to code structure and method breakdown, however these are very important elements of any application, and you should give careful consideration to this when developing your applications.

Yin & Yang Dreaming Deeply

I started playing around with uploading images to a couple of days ago, which is based on Googles Artificial Neural Network image recognition software.

For those who have not been following these developments Deep Dream is an artificial neural network train by showing it millions of images for the purposes of image recognition, and then taking that software and telling it once it finds something it has been trained to recognise to then enhance those features, if you haven’t read about this then I highly recommend that you go check it out: Inceptionism: Going Deeper into Neural Networks

Anyway this is an artwork which I have put together based on some images produced by uploading bland featureless black and white images and them combining them and adding my own enhancements.

And for all the Python developers out there the code has been open sourced and made available on github:

I’m going to have to start learning Python I think…

Powershell Export SQL results to CSV

I have a bunch of SQL scripts for which I need to export the results to CSV, this is something I need to do on a semi-regular basis and its generally a handy thing to be able to automate without having to create a SSIS package or write a console application.

So here is a Powershell script i have written which will read the SQL queries in from .sql files in the input directory and output the results to csv files with the same name as the source script.

WARNING: This script doesn’t handle SQL comments very well, so make sure you remove them from your SQL scripts before using this script to run them, also for some reason wordpress keeps messing up the connection string in the SqlConnection so Datace should be Data Source.