1

In the past I have dropped json into a table in a nvarchar(Max) column.

This is structured data but it needed serving back to the client as json and came in from the client as json so it seemed a bit of a waste of time to map it out to a table and then recombine it again.

However doing this made me a little uneasy as you can't manipulate nvarchar(Max) columns in sql and lets face it sql is really poor at manipulating strings anyway.

Our database versioning process is entirely based around creating scripts to be run by sqlcmd. This would make it impossible to refactor the data structure (using sql) in the future, which isn't a good state to be in.

So the question is this: Is there anyway of shelling out of sqlcmd to run arbitrary programs? This would want to be something synchronous, so the normal flow of patches could resume after the other program was called. This would allow you write programs in languages more suited to manipulating strings.

3
  • >> you can't manipulate nvarchar(Max) columns in sql << Really? >> This would make it impossible to refactor the data structure (using sql) in the future << Why? Can you expand please? Apr 16, 2015 at 10:25
  • @spaghettidba I thought you couldn't do like searches etc on them. but I've just tried and found you can! Not sure where I have seen that before... possibly text columns?
    – JonnyRaa
    Apr 16, 2015 at 10:29
  • @spaghettidba I think the general problem still stands though - trying to refactor json using sql would be a total nightmare
    – JonnyRaa
    Apr 16, 2015 at 10:30

1 Answer 1

2

The !! <command> syntax lets you shell out.

The external command is handled synchronously (i.e. the next line of the SQL script will not execute until this command finishes). This is why the documentation has a note in a few places stating:

When you use the Query Editor in SQLCMD Mode, you must be careful not to execute interactive statements. The Query Editor cannot respond to operating system prompts.

and:

...because the Query Editor does not have the ability to respond to operating system prompts, you must be careful not to execute interactive statements.

You can test this rather easily by creating a SQL script of nothing more than:

!!pause

If you run this in SSMS, you will have to cancel the query else it will just keep running. Once it is canceled, you will see the following in the "Messages" tab:

Press any key to continue . . . Query was cancelled by user.

Please see this MSDN page for more information.

However, this introduces an external dependency on your build process, not to mention that SQLCMD mode is not enabled by default in SSMS and cannot be programmatically enabled (though it is possible to change the default setting to always be enabled). Hence you might be better off encapsulating the JSON functionality in a SQLCLR function or stored procedure.


2
  • 1
    Very nice. I haven't tried to see if is synchronous (eg waits for stuff to finish) but this just like what I was looking for. I don't use SSMS studio for running scripts anyway, this seems to work fine from sqlcmd without needing to do anything either in the command line or in a script
    – JonnyRaa
    Apr 16, 2015 at 12:54
  • @JonnyLeeds Yes, it is synchronous, which is why there are a few warnings about not using interactive commands. I will update my answer with that info. Apr 16, 2015 at 14:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.