Competition: What’s your favorite Redgate tool? Enter now.

Presql not running

iderdikiderdik Posts: 3
I've specified a presql switch on my command line but it doesn't seem to run. Is there any trick or restrictions on what you can do in a presql file?

Comments

  • Never mind - I was using /presql= instead of /presql:
  • Brian DonahueBrian Donahue Posts: 6,590 New member
    Hi,

    Sorry nobody got around to you. Yes, PRESQL simply uses the functions that Packager already has built-in to run a SQL file before the package. I think it will fail silently, though. At any rate, if there are errors in the presql script, that doesn't stop the package from running. It's debatable whether a failure in the presql script should affect the package execution.
  • Yes, PRESQL simply uses the functions that Packager already has built-in to run a SQL file before the package. I think it will fail silently, though. At any rate, if there are errors in the presql script, that doesn't stop the package from running. It's debatable whether a failure in the presql script should affect the package execution.

    When you are using an installer to install a product that has a database, during these installs it is very important to know if there is an error during execution of the /presql: or /postsql: scripts. Silent failure is not helpful.

    Is there any way to return an error code if the /presql or /postsql scripts fail? Even detecting the presence of an error would be helpful, but more information is always better. Right now it fails silently, the error flashes by in the DOS window, and the window typically disappears before the error message can be read.

    Even leaving the error up to be seen in the DOS window instead of shutting it down immediately after execution halts would help. At least the user would know an error happened during the install without having to watch the screen every second of the install to see if an error message flashes by or not. The installer may not be able to detect it, but at least there would be SOME indication that things did not go well.

    Thanks,
    Joe Brooks
  • Brian DonahueBrian Donahue Posts: 6,590 New member
    Hi Joe,

    I think it wouldn't be too hard to modify SQL Packager's code template for this. If you send me an email, I can probably send you back a code template project that throws an error when presql fails.
  • Brian DonahueBrian Donahue Posts: 6,590 New member
    If you open %programfiles%\red gate\sql bundle 5\SQL Packager Code Templates\C#\PackageExecutor.cs, and you can do this using Visual Studio but you should also be able to use Notepad or some other text editor, go to line 513. This should have a block of code similar to:
    catch  (Exception e)
    				{
    					Console.WriteLine(e.Message);
    				}
    
    If you replace this with a SQL exception handler, it will throw an exception message whenever the presql and/or postsql scripts fail. This will bring Packager to a halt. So erase the code above and replace it with
    catch (SqlException se) 
    				{
    					throw new PackageException("Error in presql/postsql script: " +se.Message);
    				}
    

    If you have any problems editing the SQL Packager code template, let me know and I will send my code template project.
  • Two thing about altering the C# template:
    1) We are several persons using the tool, which makes it difficult to maintain such a template change.
    2) We do not want to alter a third party product. Only to extend its usage. This is due to support and maintenance.

    We have tried to harden the execution of postsql scripts, using RAISERROR or PRINT, but neither is caught by a executable package, not even in StdOut of the package.

    As we need the postsql functionality, we are very interested in a solution that is integrated with SQL Packager.
Sign In or Register to comment.