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

Create CSVs from SQL Server

-

 So you want to automate export some data from SQL to CSV files? 

For one-off things, there are a couple approaches you can use SSMS for, however there are some major drawbacks of doing things there, most notably, it's all manual; you can't script any of this stuff out. Still, I'll discuss a few methods in SSMS before the cool stuff which is with BCP or Powershell

SSMS

These methods work fine for one-time exports that you don't need to automate and you don't need a high level of control of the output.

As a Result Set

One simple way is to set the output to either text (CTRL-T) or to file (CTRL-F). If you set it to text, copy/paste the results into a text file, or if you're using results to file, just name the file anything .csv (instead of .rpt). Make sure to clean up any header (like column names, and the big row of hyphens)  and footer (like the rowcount, if you didn't set nocount on). You can adjust some of the settings those methods use to generate the output by right clicking the query window, selecting query options and going to Results/Text. Most notably, you can set the delimiter by changing the Output format dropdown, and maxing out the number of characters displayed in each column to 8000.

This has the limitation that you have to be in SSMS, you're limited in what you can do in terms of output formatting, you have to run the process manually, and if you have fields longer than 8000 characters, you'll truncate your data.

Using a plugin

There are a couple of SSMS plugins which also aid in copying data out. Redgate's SQL Prompt has the ability to right click a result set and export it to excel. Mladen Prajdik's SSMS Tools Pack also provides some similar functionality, as does Apex SQL. I've used both of these, and they're both great. 

The drawbacks here are that you have to get a license for these products. Redgate is prohibitively expensive, but most jobs I've worked at have licenses for the developers, so it's not a problem. SSMS Tools Pack is much more reasonably priced, and very solid; I've owned a personally bought license to the product for years, and I absolutely love it (that's not a paid advertisement, I just really like his plugin). I think ApexSQL Complete might be the free one of the bunch, although I haven't personally worked with it.

SSIS & Import/Export Wizard

You can of course also automate the generation of SQL data using SSIS and the Import Export Wizard. I'm not going to go into details here because of their main drawback, which is, they're not very lightweight to set up. Sure, you can automate generation of hugely complex data sets and schedule them with things like SQL Agent, but given the configuration and management complexity, these are best suited to limited numbers of known exports you need high configurability over, and you plan to execute regularly as part of an ETL process.

Scripting

That leaves scripting options which fit nicely between the high-effort configurability of something like SSIS, and the ad-hoc nature of copying result sets out of SSMS.

There are two scripting approaches I'd like to introduce to you: BCP and a Powershell approach.

BCP

The Bulk Copy Utility is a battle proven utility for importing and exporting data to and from sql using the command line. Unfortunately, it can be kind of confusing to use as there are a lot of flags it supports, a lot of features you sometimes need to use, and very lengthy and often confusing documentation. 

Fortunately, simply exporting data from BCP is pretty straight forward.
Here's an example of how to create a csv file of all rows in sys.objects on a localhost server.

bcp "select * from sys.objects" queryout "c:\data\bcpout.csv" -S localhost -T -d master -c -t "|"

Check out the full documentation of the flags at your disposal on the MSDN BCP documentation page but basically, after bcp:
  • First argument is a sql command you want to export from.
    • You can also select from a table directly, but it's not that much harder to just write the query you want. Again, feel free to investigate that if you want.
  • queryout indicates you want to output the results somewhere, and it must be followed by the path to the file you want to generate.
    • Note, if the file already exists, it will be overwritten
  • -S is the name of the SQL Server you want to draw from
  • -T tells it to use trusted security (aka. Integrated Security = true)
    • You can use -u and -p args if you're using a username and password
  • -d specifies the database to use (aka Initial Catalog)
  • -c specifies the character encoding of the file.
    • This is an extremely confusing topic, as there are four different settings you can specify, and they all do things slightly differently. -c is a pretty safe option to use if you're exporting data.
    • If you're interested in the other options, you can check out this subheader on Best Practices using -c and -n. Just beware, you may find yourself deep down a rabbit hole
  • -t specifies the field delimiter.
    • in this example, I chose a pipe, but I think the default is tab
So pros, it's a well established tool that is very efficient and configurable and you can script stuff out using any command line interface with BCP installed. Cons, it's just kind of an overwhelming tool. If something goes wrong, trying to figure out why can often be a pain in the butt. Still, a great option if you have the tool installed.

Powershell

Another alternative to BCP is to use Powershell. The concepts are almost identical, it's just in how they're invoked.

The basic idea is to execute a query using Invoke-SqlCmd, then pipe the results to Export-CSV, where you can format the results using similar controls as exist in BCP.

I like this approach because Powershell's verbose nature makes it a little clearer to figure out what's going on (e.g. the documentation on Export-CSV is a lot easier to understand than that of BCP since it's only job is to create a CSV, whereas BCP is a monolithic app which combines the SQL stuff and the CSV stuff in the same service). Also, if you're in Powershell and you type "-" followed by CTRL+Space you can see human readable names of arguments which are a lot clearer than the more bash-like arguments of BCP.

I created a script on my computer which I can run with named arguments, plus any remaining arguments are passed to the Export-CSV command (in case you want to further configure it.)

param (
    [String]$Server,
    [String]$OutFile,
    [String]$Query
)

Invoke-SqlCmd -ServerInstance $Server -Query $Query | Export-CSV -NoTypeInformation -Path $OutFile @args

A sample invocation might look like this:

.\create-csv.ps1 -Server "localhost" -OutFile "c:\data\psout.csv" -Query "select top 100 * from sys.objects" -Delimiter '|'

Note, Everything after the named arguments (i.e. -Server, -OutFile, and -Query) are passed to Export-CSV. So while I've just specified -Delimiter, if I also wanted to pass -QuoteFields information, I could do that as well. This process of passing "the rest of the args" is called splatting.

BCP vs Powershell: Which is better?

Beats me. They both do what I need, at least in simple cases. I think I have more confidence in troubleshooting what's going on with the powershell version than I do with bcp, but both do what I need them to. I also like getting more familiar with bcp since it's so powerful. But really, the choice is up to you.

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