sp_databases - List of Databases in MS SQL database?

[DennisDawg]

I see that there is a plug in for MS SQL instances which returns the available "MS SQL Servers." I see there is a plug in for "MS SQL Server" with which one can connect to and instance of MS SQL Server and connect to a database and run SQL scripts. However the scripts cannot return a value, it is ignored and only the SUCCESS or failure of the script is returned in the variable indicated in "Return result in variable." Therefore a stored procedure such as sp_databases cannot be used to determine what databases are on a server, because the return value will not be passed on to the MSICode, only SUCCESS, which is of little use.

We'd very much prefer that the user not have to type in the name of a database after having had the opportunity to select from a list when picking a server. Is there a way of obtaining a list of databases on a server using MSICode?

[BobSchaefer]

I have to agree with your complaint about the SQL Server plugin being not overly useful. However, I did find a way to work around the problem of not returning values, you can do this in a very twisted way. in the SQL Server plugin, check the abort execution on error checkbox and as the last line of your script, make a command that's illegal but contains the values you're looking for. The error will be returned in the return result variable, and you can then strip the values you want out of the error message. In my case, I was looking for the default path for the database, so as the last statement I did an exec of the path value, which threw an error complaining about not finding a stored proc called the path I wanted.

It's convoluted and backwards, but it got me the values I wanted.

Hopefully this helps you out somewhat.

[DennisDawg]

I am going to work on this today. I tried yesterday and could not find a way to get the results from sp_databases into the error data from EXEC something later in the script.

It is very odd to need to cause an error to get data and I will not feel good about it even if I get it to work.

InstallAware should have to option of returning the actual data from the SQL script. Not being able to get the data from the execution of the SQL renders the implementation here a bit limp so to speak.

[mills]

I've added this to our feature requests. I can't say if or when we'd have this implemented.

Another possible approach could be to get the results output to a file. See:
http://www.sqlteam.com/article/exportin ... p_cmdshell

[DennisDawg]

Thank you Mills. The SQL in MS SQL Server plug in should return whatever the script returns, whatever the user wants it to. InstallAware should offer the built in return only as an option, not the default even. The people making installations for their software and using SQL are smart enough to handle this on their own and should be able to do so. Just my 2 cents.

[DennisDawg]

I have to agree with your complaint about the SQL Server plugin being not overly useful. However, I did find a way to work around the problem of not returning values, you can do this in a very twisted way. in the SQL Server plugin, check the abort execution on error checkbox and as the last line of your script, make a command that's illegal but contains the values you're looking for. The error will be returned in the return result variable, and you can then strip the values you want out of the error message. In my case, I was looking for the default path for the database, so as the last statement I did an exec of the path value, which threw an error complaining about not finding a stored proc called the path I wanted.
.

Bob, in my testing today I used your idea and had some success using your method. I post here just to complete the thread with something that works 100% based on your suggestion (checking abort execution on error and creating an error). I hate threads that end without a solution when I search.

I do not know just now if there is a less kluge filled way to do this (feel free to make it better and post) but here is what I have working today. For whatever reason (or lack thereof on my part) I could not get sp_databases to work here. So I used something older but it did work on MS SQL 2008. Here is the snippet from the MSIScript (getting the server selected and all that is the same as in the example in the /Samples folder).

Code: Select all

Set Variable AVAILABLEDATABASES to
Microsoft SQL Server Script : Connect to database  on instance $SERVERNAME$\$INSTANCENAME$ as user $SQLUSER$ (get result into variable AVAILABLEDATABASES)
Parse String $AVAILABLEDATABASES$ into Variables AVAILABLEDATABASES and DELETE (Split at first occurrence of pattern)
Parse String $AVAILABLEDATABASES$ into Variables DELETE and AVAILABLEDATABASES (Split at first occurrence of pattern)
Parse String $AVAILABLEDATABASES$ into Variables AVAILABLEDATABASES and DELETE (Split at first occurrence of pattern)
Display Dialog: sqldatabase, wait for dialog to return (modal)

The SQL in Microsoft SQL Server Script is then:

Code: Select all

USE master;
GO

DECLARE @tblTempDB table( name varchar(50)  )
DECLARE @dbList varchar(3000)
DECLARE   @return_value varchar(3000)
         
BEGIN
    SET @dbList = '' 
 
     INSERT INTO @tblTempDB
        SELECT name
        FROM sys.databases
         
      IF @@ROWCOUNT > 0
         UPDATE @tblTempDB
            SET @dbList = ( @dbList + name + CHAR(10))
           
      SET @return_value = substring( @dbList, 1, ( len( @dbList ) - 1 ))
      EXEC @return_value       
 
END

@return_value has the list of databases, each on a new line. Trying to execute this as a stored procedure (as was suggested by Bob and so nothing here is new) returns this with the added text “The name” at the front and “ is not a valid identifier” at the front and back. The string desired is in single quotes. Returned also is the the SQL that cased the error, delimited from the rest by DELINEATOR.

The first Parse String line removes the SQL using the DELINEATOR as the “Pattern or position” in the Parse String dialog box. This leaves just the value of @return_value and the added text in AVAILABLEDATABASES.

The next two Parse String lines then just take off the front and the back using the single quote as the “Pattern or position” in the Parse String dialog box. AVAILABLEDATABASES then holds just the databases.

The dialog sqldatabase has a dropdown with its Items set to $AVAILABLEDATABASES$ and the list of databases then appear there.

Thank you Bob!!!!

[BobSchaefer]

One thing to reduce stuff, you don't need a variable in the throw away field of the parse string command. So where you have the $DELETE$ variable, you can just leave that blank and it will happily throw that part away. Other than that, it looks like you've put together a good solution for what you need, and I may have to steal that later as well!

[mills]

Thank you both for your insights. Many of our customers use SQL and will likely find this useful. It's much appreciated!

[Solnake]

I have tried to use this solution but get just Success as result value.

Does this solution works?

[FrancescoT]

... it is simply a Microsoft SQL Server Script... just run the same using the MS SQL Managment Studio tool.

Then the rest of the IA script only parses the returnd string.

Regards

[Solnake]

I did the same described at this topic but receive just success as result. When executed script inside ms sql studio - yes, it rize exception with list of db. but inside installer - just success.

[FrancescoT]

... this happens because a MS SQL script doesn't return any value.

I am not an MS SQL expert, but I suppose that you should use a stored procedure instead of a sql script.

Regards.