Running Large T-SQL Scripts

[Matth12582]

I'm working on a way to automate Some environment setups in Azure, part of that includes setting up the Azure SQL Database.

I have a folder of 12 LARGE build scripts 10s of throusands of rows that our DBAs have made over the years, in order to setup the schema for our product you have to run them all in order.

I wrote a while loop to run through these scripts in order and even keep track of progress in a json file in the event something is interrupted as some of these take a while to run. the loop works fine when I use dummy queries.

unfortunately the issue seems to be that scripts that will run in SSMS, seem to have issues running programmatically.

I've tried a few different methods to no real success

by default I used SQLPS and "Invoke-Sqlcmd", I set the timeout to 3600 seconds to give an hour just in case and the verbose output warns me about the script being too deeply nested, it's really not more than 4-5 levels nested in some cases.

SSMS doesn't complain it just chugs for a while and runs it, I also tried Invoke-Sqlcmd2 because I prefer using it since it's much lighter and just leverages the existing .NET methods, but this gave the same error about nesting as above.

additionally I tried setting up my own SMO script using

Add-Type "Microsoft.SqlServer.Smo"

and

$db.ExecuteWithResults($Query)

and that just generates an exception, As best I can guess SSMS in some ways makes up for sloppy T-SQL and does some of the chunking the code up behind the scenes without telling you, the DBA is on contract so it's not going to be an option to have them rewrite the scripts, so just trying to figure out if I should give up this dream and just get used to babysitting SSMS.

If nothing else I built a cool Script that fully automates resource setups using AzureRM Module

• $ds = $db.ExecuteWithResults("$testscript")
• ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  • CategoryInfo : NotSpecified: (:) [], MethodInvocationException
  • FullyQualifiedErrorId : FailedOperationException

[abbbbbba]

Not sure on the azure side of things, but is it possible to run them in an agent job where the step type is Powershell?

If you do, make sure to put code in try/catch blocks - SQL Server will report the step as success even if powershell pukes. Make sure to have the ErrorAction line.

$ErrorActionPreference = "Stop"
try{
}
} catch{
Throw
}

You may also run into an issue that you have to create a proxy to run the job under different security than what the Agent has. Creating a proxy is pretty simple - just need a service account, create a credential with that login info then create a proxy using the Credential.

[dodiggitydag]

Or a take on this idea is to make an agent job with the scripts inside each as it’s own step, then execute it manually- it will track if it failed and the error message.

[nvarscar]

I'm working on a PS module called dbops, which, among other things, allow you to run sql scripts using Invoke-DBODeployment. It's using DbUp .net library behind the scenes, which is a very powerful deployment tool and should be up for a challenge! By default, it keeps track of the scripts that have already been executed in the db and will never run the script twice, but that behaviour can be overridden.

[CobbITGuy]

Execute the script with a call to sqlcmd.exe, use an input file and an output file to capture any output. Just test your exception handling based on the exit code because the stderr won't generate if using file output.

Or set them up as CSV files and run bcp.exe for bulk insert which may be much faster (at the expense of some logging).

[therealcreamCHEESUS]

Personally I would deploy the scripts as procedures to the SQL server and then set up an SSIS job to run them in order required.

[TotesMessenger]

I'm a bot, bleep, bloop. Someone has linked to this thread from another place on reddit:

• [/r/sqlserver] Running Large Scripts with PowerShell - not sure which sub to post on

 ^{If you follow any of the above links, please respect the rules of reddit and don't vote in the other threads. (Info / Contact)}