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() 

  


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

 


1 person likes this

Thanks for the reply. For some reason I couldn't find this thread by searching the forum for Fireslinger, even with Google site:support.dbvis.com. Is there a way to see all of my threads?


Your reassurance helped me dig deeper into my problem: I was launching dbviscmd with cmd /k, so that always returned 0. My new handle_error looks like this:

 

def handle_err(process: 'result from subprocess.Popen)'):
    out, err = process.communicate()
    print()
    if process.returncode:
        print(f"Error: process returned error code {process.returncode}. Check program log and then quit.",
              file=sys.stderr)
        return process.returncode
    elif err:
            print("Error while running the following external command. Exiting.")
            print()
            print(process.args)
            print()
            print(err, file=sys.stderr)
            sys.exit()

 

I meant to add that I did, of course, go to my profile and use the feature that shows you all of your own articles and tickets. None of the tabs showed this one.

Login or Signup to post a comment