More Error Information When Using SQLCMD Mode in SSMS

• chris.o.smith

  SSCommitted

  Points: 1674

  More actions

  June 25, 2019 at 2:16 pm

  #3657380

  Hi,

  I'm trying to learn a bit more about SQLCMD mode in SSMS.  I've got a script that runs a series of other scripts, like this:

  :SETVAR Objpath "c:\..."

  :SETVAR oltp "servername"

  :ON ERROR EXIT --End the SQLCMD script if we encounter an error

  :CONNECT $(oltp)

  --Tables and views

  :r $(Objpath)"\...Table1.sql"

  :r $(Objpath)"\...Table2.sql"

  :r $(Objpath)"\...Table3.sql"

  ...

  This works as expected, but I can't figure out how to get more error handling information.  Is it possible to have SQLCMD tell me the name of the script that had the error?

  For example, if I change "Table3" to a non-existent script, like "Tabel3", I get a generic error message:

  A fatal scripting error occurred.

  The file specified for :r command was not found.

  When the list of scripts gets large, this makes it tough to know where the problem was. I could add print statements in between every script that is executed, but that seems very tedious.  Thanks for your help.

• Site Owners

  SSC Guru

  Points: 80342

  More actions

  June 26, 2019 at 3:10 pm

  #3657777

  Thanks for posting your issue and hopefully someone will answer soon.

  This is an automated bump to increase visibility of your question.