Start a new topic

How to detect SQL errors in scheduled jobs?

This page on scheduling exports says, 


"Although the script examples can be used as a base for designing your own reports, the scripts lack proper error handling/logging and should be improved to handle this."


I thought if I passed -stoponerror or -stoponsqlwarning to dbviscmd, I would get a nonzero error code and could print stderr to see what went wrong with my query. That's not how it works. Do I have to read the logs every time to be sure my SQL ran OK?


For anyone who wants to see, here's the gist of the Python I'm using to export:


  

command = ['cmd', '/c', 'dbviscmd.bat', '-connection', connection,
		   '-stoponerror','-stoponsqlwarning', '-sqlfile', str(sqlfilepath)]

with subprocess.Popen(command, stdout=logfile, stderr=PIPE,
					  cwd=r"C:\Program Files\DbVisualizer") as p:
	err = p.stderr.read().decode('utf-8')
	if err:
		print("Error while running the following external command. Exiting.")
		print()
		print(p.args)
		print()
		print(err, file=sys.stderr)
		sys.exit() 

  

1 Comment

Hi!


Sorry for the late answer on this. I'm not that into python but I did test the following VB script.

Adding a failure in the export.sql gave me the echo with return code 3 as expected when there is an error and -stoponerror is specified. If -stoponerror is not used return code will be 4. 

You are though correct that errors are not printed to stderror it is printed to the log file. 

 

Dim oShell
Set oShell = WScript.CreateObject ("WScript.Shell")

' Execute DbVisualizer. The intWindowStyle is set to "0" below. Set it to "1" if you need to debug. 

strReturn = oShell.Run ("""C:\dbvis\1017\dbviscmd.bat"" -connection H2 -stoponerror -sqlfile C:\Users\ulf\Desktop\dbvisvb\export.sql", 0, true)
If strReturn <> 0 Then
      WScript.Echo "Failing running DbVisualizer command " & strReturn
Else 
     ' WScript.Echo "Succeeding running DbVisualizer command" %strReturn
End If
Set oShell = Nothing

 

Login or Signup to post a comment