Migrate SQLPrompt Snippets to VSCode

-
 I love snippets; love em. And I have a whole bunch in RedGate SQL Prompt. Now I want to be able to use those in VSCode as well, but boy do I dread having to retype all of them. Solution? Python! First arg is the path where your SQLPrompt snippets are Second arg is the directory where you want it to spit out a "sql.json" file with all your snippets. """ A script to translate sqlprompt snippet files to vscode formatted snippets """ import os import json import glob import io import argparse class SQLPromptPlaceholder :     """Represents the values of a SQLPrompt placeholder"""     def __init__ ( self , name , default_value ):         self . name = name         self . default_value = default_value class SQLPromptSnippet :     """Represents the content of a SQLPrompt snippet"""     @ staticmethod     def from_file ( filename ):         """Generates an instance fr
Post a Comment

Getting PowerShell to throw an exception in SQL Agent job

-

 By default, powershell scripts failing in a SQL agent job, won't stop the agent job. In otherwords, things will go wrong in your script, and SQL Agent will happily move on to the next step in the job. That is not so good. The options at your disposal would seem to be:

  • Exit 1
  • Write-Error
  • $ErrorActionPreference="Stop"
  • throw

Stuff that doesn't work

The first two options might come to mind, but they don't work. Details below.

Exit

exit 1 (or any code != 0) would seem to give the calling application (Agent) the info that the task failed, by it's return code. However Agent doesn't respect this. So that's not an option.

Write-Error

Write-Error stops execution when you run a powershell script through a powershell window, but again, for SQL Agent, it doesn't pick up on that. Yes, it will stop the script, but Agent will happily continue to the next step

Stuff that Works

To get Agent to recognize a powershell script failure, and actually fail the agent job, you have two options.

Set ErrorActionPreference

At the start of your script, you can set the preference variable $ErrorActionPreference="Stop".

This will allow write-error to abort the script. It will do nothing for exit 1. It's simple, but does require you to add an extra line to all your scripts specifying that setting.

Throw

Finally, you can always Throw an error explicitly. That will get the job done. I think this is going to be my go-to, since I can just throw where I need, and not have to change preferences at the top of the script, simply to enable a sort of syntactic sugar.

Comments

Post a Comment

Popular posts from this blog

Migrate SQLPrompt Snippets to VSCode

-
 I love snippets; love em. And I have a whole bunch in RedGate SQL Prompt. Now I want to be able to use those in VSCode as well, but boy do I dread having to retype all of them. Solution? Python! First arg is the path where your SQLPrompt snippets are Second arg is the directory where you want it to spit out a "sql.json" file with all your snippets. """ A script to translate sqlprompt snippet files to vscode formatted snippets """ import os import json import glob import io import argparse class SQLPromptPlaceholder :     """Represents the values of a SQLPrompt placeholder"""     def __init__ ( self , name , default_value ):         self . name = name         self . default_value = default_value class SQLPromptSnippet :     """Represents the content of a SQLPrompt snippet"""     @ staticmethod     def from_file ( filename ):         """Generates an instance fr
Read more

Fixing Git "Unexpected Disconnect while reading sideband packet"

-
 I ran into an issue the other day (which my co-worker just ran into as well) when doing a git push, where it fails, saying "RPC failed; HTTP 500 curl 22 The Requested URL returned error: 500 send-pack: unexpected disconnect while reading sideband packet". I found almost no useful articles on the matter; most just talked about adding tracing flags or saying "your internet sucks", neither of which addressed the issue. What fixed it for me was setting the http.postBuffer size to something very large, like git config --global http.postBuffer 157286400 This allowed larger files/repositories or something like that to be posted successfully.
Read more

Left-Padding Zeroes

-
 I recently ran across a case where a time value was stored as an Int, with the idea that the first two numbers are the hours, second two the minutes, and third two the seconds. This is all well and good if the hours are double digits (e.g. 144415), as you can just split the string every two characters. If the hours are single digit (e.g. 93251) doing that won't give you the right result. You'll wind up splitting out "93", "25", 1" when what you really want is "09", "32", "51". You also run into this stuff sometimes when you're trying to left-pad a string for a year, month, or date to a pretty format. None of these cases are particularly compelling, and there are probably other solutions for these sorts of issues, but what I DO want to share with you, is a couple ways you can left-pad a string with characters.  FWIW, I've tried doing left-padding with the FORMAT function, and maybe I just suck with it, but I haven&#
Read more