Automate running of script instead of compare

jgraves

We have a situation where we need to streamline/automate the running of SQL scripts for basic DML and not any sort of compare. Is SQL Multi Script the right Redgate tool for this?

Also, is there a way to manually add the code for SQL Monitor integration? That really helps us with visualization of changes. 

Thanks in advance, Jay

Russell D

Without knowing the use case in much detail: you can't automate SQL MultiScript - that's more for deploying the same thing to a lot of different places.

SCA would be the closest thing, but it's going to be deploying schema changes rather than just any old DML. You could use SOC pre/post scripts or the same thing in SCAVS to have it included in the process. 

For the SQL Monitor integration you could just copy the bit out of the SQL Compare deployment script that writes to the SQL Server log for SQM to pick up.  It will need some modification though as is because part of the script generation is the parameter @databaseName.

I would add though that we do not support this, and haven't tested third parties inserting things into this table within SQL Monitor. Its seems relatively safe but I certainly don't recommend it. It would be really useful to know the exact use case that you're trying to solve with this, if you could post it on the SQL Monitor uservoice, (https://sqlmonitor.uservoice.com), we'd like to explore this more. There is actually an API for this - but we just haven't made it public - I think we'd like to make it more useful to more people.

jgraves

Thanks and will post more detail in Uservoice. Interested in the API.

We have a lot of DML scripts we need to deploy and would like to do it with a pipeline to automate for testing (and generally to cut down on humans being involved in button-clicky work). 

I did successfully test using the SCA snippet for SQL Monitor integration. Are there any rules that must be followed for the message wording?

Thanks! Jay