SQL scripting questions

[BobSchaefer]

I'm trying to use the built-in SQL tools to run some scripts and would like some additional information on how the tool operates and how/if I can get more information out of it.

First, I'd like to be able to execute a script and get the results of the script returned to me out of the DLL, is this possible and if so, how?

Next, is it possible to have the DLL log the output to a file, or does it send the output (such as print statments) to some variable that I can store somewhere? Currently I'm using a Run Command call to run SQLCMD to do the heavy work I need and sending the output to a file, which will work, but if I can do something more integrated I would be happier. Also, is it possible to send updates to the progress bar while running scripts within the SQL DLL?

Thanks for any help!

Bob Schaefer

[mills]

You will want to use the MS SQL Server (plugin) command for scripting.

For logging, you can use "Write to Text File" or check out the WriteLog plugin.

You can control the progress bar by setting $PROGRESS$ to a value between 0 - 100. Alternatively, you could set $PROGRESSMODE$ to MARQUEE and set it to nothing to resume default behavior.

$PROGRESSTEXT$ affects the progress text, obviously.

These progress behaviors are documented in the help file (F1 in IDE) under pre-defined variables.

[BobSchaefer]

I apologize, I know most of what you listed, but you misunderstood what I'm trying to do.

I'm running the SQL script through the plugin, I want to know if I can do all of those things through the plugin or while the plugin is running. In other words, I have a script that goes off to SQL server and figures out where the default database would reside on the server, I want to grab that return value out of the plugin and put it into a local variable. Can I do that and if so how?

I also have a script that builds the database, this can take a while, so I want to know if, while the script is running, I can send updates from the plugin back into the installer and also log the output of the script to a file, again while the plugin is running. The SQL script I am supplied with puts a lot of information out to stdout while its running so that if something goes wrong, we can help troubleshoot what went wrong and why.

Does this make more sense what I'm trying to do?

[mills]

As you can see, the plugin has a field for "return result in variable". You can enter a variable name there to get the result. Just make sure you Set Variable before calling the plugin.

As for the progress/updates, the plugin isn't designed to work that way. As a solution, you could set the $PROGRESSMODE$ to MARQUEE display a dialog with a progress bar and a caption along the lines of "Installing SQL" or "Doing SQL work"...

If you can split your scripts into smaller scripts, you could display a different dialog for each section.

And of course, you can examine the SQL Server error log if need be...

[BobSchaefer]

Thanks for the details, the part about the progress was pretty much what I figured, and it sounds like for the longer parts, the plugin won't work, as I really need a way to write the output to a log file.

As for the storing results, I've tried what you suggest and all I ever see in the variable is "SUCCESS".

Here's a copy of the script I'd like to get the results from, maybe seeing the script you can tell me what I'm doing wrong:

Code: Select all

/*
=====================================

GET THE CURRENT OR DEFAULT PATH FOR STARDATABASE

=====================================
*/


DECLARE @db_id      int
DECLARE @db_name   sysname
SET      @db_name   = '$DBNAME$'

USE [master]

IF EXISTS (SELECT 1 FROM sys.databases WHERE name = @db_name)
BEGIN
   -- get current db path
   USE master
   SELECT @db_id = database_id FROM sys.databases WHERE name = @db_name
   --return values
   SELECT TOP 1  LEFT(physical_name, CHARINDEX(@db_name, physical_name)-1) FROM sys.master_files WHERE database_id = @db_id AND type = 0
END


Ignore the fact that if the database doesn't exist it won't do anything, that part is actually there, I just edited it out. What I want is the result of that select statement so that I can put it into a variable in my script to use later. Any thoughts on how I can do that?

Thanks!

Bob Schaefer

[BobSchaefer]

So, after playing with many different options and what not, I've finally figured out how to get the answer out of the MSSQL plug-in.

Here's what I ended up doing:

First, I marked the plug-in to Abort execution on script error, this causes the return variable to have the actual error message inside it.
Next, I changed my script to have the last line cause an error with the value that I want in it. In this case I simply did an EXEC @db_loc, which caused the plug-in to return an error along the lines of "Error Stored Procedure 'The path I want' was not found..."
I then did some string manipulation based on the fact that the path I want was enclosed in single quotes and was able to get the answer that I wanted out of the script.

Convoluted, yes, but it works and gets the result I need.