Linux, SQLCMD and Vim

Hello everyone!

If you came here you probably already know what is SQL Server SQLCMD utility, and you also know (and either love or hate) the Vim text editor.

Of course, nobody with senses would use the SQLCMD for day-to-day work on Windows (we have GUIs. Management Studio etc).

Yes - I'm one of those guys who stick to GNU/Linux operating system and generally prefer command line interface (CLI) over GUIs.

So let's briefly explain the problem that I had:

SQLCMD interactive mode works (kudos to MS, you can create a working Unix program!), but on the output side, it does not have any paging / text formatting capabilities. It also does not shine on input side, ignoring one of best inventions of the GNU/Linux world which is the readline library.

Basically, this means that working with SQLCMD in interactive mode for anything serious is a mistake. Authoring SQL or reading SQL output is too inconvenient, unless all your queries look like this.

SELECT oneoneone=1, two=3
GO

So, the process to edit any complex SQL query in your CLI text editor is

1. write your SQL query
2. save the SQL file
3. run the SQL file in sqlcmd, either interactive mode (using the :r macro) or batch mode (using the "-i" option).
4. try to browse through the output on screen
5. decide that output is too wide, line wrapping makes it unreadable and switch to  "-o" for output
6. open the output file in any decent text pager / editor (like vim or less)
7. Inspect the results. Go back to point 1.

Naturally, you could automate it using a bash script, along the lines of

/opt/mssql-tools/bin/sqlcmd <other options> -i my.sql | less


But my goal was to reduce development loop to this:

1. write your SQL query
2. run the SQL and see output in any decent text pager / editor
3. Inspect the results. Go back to point 1.

Long story short - here is the solution for Vim, you can add it to your .vimrc and see for yourself.

Usage: While editing SQL file in normal mode, press letter M on the keyboard and query is executed over SQLCMD and its results appear in bottom window pane.

function! RunBufferInSQLCMD()
  let s:file = expand('%:p')
  if strlen(s:file) > 0
    silent write
    let s:sqlcmd = '/opt/mssql-tools/bin/sqlcmd'
    let s:sqlsrv = 'your.database.windows.net'
    let s:sqldb = 'YourDB'
    let s:sqlusr = 'bobadmin@yours'
    let s:sqlpwd = 'ehkm'
    let s:ofile = substitute(system('mktemp'),"\n",'','')
    let s:mycmd = s:sqlcmd
                  \ . ' -S "' . s:sqlsrv
                  \ . '" -d "' . s:sqldb
                  \ . '" -U "' . s:sqlusr
                  \ . '" -P "' . s:sqlpwd
                  \ . '" -i "' . s:file
                  \ . '" -o "' . s:ofile
                  \ . '"'
    let s:myoutput = system(s:mycmd)
    if strlen(s:myoutput) > 0
      echoerr 'Command output: ' . s:myoutput
    endif
    execute 'silent rightbelow split ' . s:ofile
  else
    echoerr 'Not a file!'
  endif
endfunction

map M :call RunBufferInSQLCMD()<cr>


That's it for today. Nice Vimming!