Passing paramaters to SQL files

[gibbie99]

Hi all,

For our installer we are making database modifications that require parameters, mainly the name of the database. So far i have this working with batch files calling SQLCMD. I can't figure out how to make the SQL script portion of IA to pass parameters.

Thanks

Rob

[giaviv]

Dear gibbie099,

This is done by calling the command MS SQL Server. This plug-in allows you to execute SQL scripts on an instance of Microsoft SQL Server.
In your case, in order to change a DB's name, you would need to run the following command:
"ALTER DATABASE oldName MODIFY NAME = newName".
I uploaded an example script that I built that changes a DB's name on a local SQL 2005 Express Server- let me know if its helpful!

Thanks

[BobSchaefer]

As a follow-on answer, you don't need to worry about parameters if you're using the MSSQL plugin. Where you would have your parameters in the script, just substitute the name of the variable directly in the script.

For example:

ALTER DATABASE $OLDNAME$ MODIFY NAME = $NEWNAME$

Where $OLDNAME$ and $NEWNAME$ are variables that you previously defined.

Does this help clarify further for you?

[gibbie99]

Yes this is very helpful.

However, when I create the MSI code and test the installer, I just get the return status instead of the full dialog from SQLCMD. What I am trying to do is generate a database with specific parameters and attach ownership to a specific user. However, it doesn't work and I don't have enough information to troubleshoot it. I need to know what the SQL server is saying.

Here is the msi code.


Set Variable DBNAME to xxxx
Set Variable RESULT to
Set Variable COMPUTER_NAME to
Get System Setting Logged on Computer Name into COMPUTER_NAME
Microsoft SQL Server Script : Connect to database MASTER on instance $COMPUTER_NAME$\. as user sa (get result into variable RESULT)
MessageBox: $TITLE$ SQL script result, $RESULT$

And here is the SQL.


USE MASTER
print '***********'
IF EXISTS (SELECT * FROM sys.databases WHERE name like '%$DBNAME$%)')
EXEC sp_detach_db '$DBNAME$', 'false'; -- if db exists, detach it.

create table #backupInformation
(LogicalName nvarchar(128),
PhysicalName nvarchar(128),
Type char(1),
FileGroupName nvarchar(128) ,
Size numeric(20,0) ,
MaxSize numeric(20,0),
FileId bigint,
CreateLSN numeric(25,0),
DropLSN numeric(25,0) NULL,
UniqueId uniqueidentifier,
ReadOnlyLSN numeric(25,0) NULL,
ReadWriteLSN numeric(25,0) NULL,
BackupSizeInBytes bigint,
SourceBlockSize int,
FileGroupId int,
LogGroupGUID uniqueidentifier NULL,
DifferentialBaseLSN numeric(25,0) NULL,
DifferentialBaseGUID uniqueidentifier,
IsReadOnly bit, IsPresent bit )

insert into #backupInformation exec('restore filelistonly from disk = ''''$TARGETDIR$\DATABASE\$DBNAME$.bak''')

DECLARE @logicalNameD varchar(255);
DECLARE @logicalNameL varchar(255);

select top 1 @logicalNameD = LogicalName from #backupInformation where Type = 'D';
select top 1 @logicalNameL = LogicalName from #backupInformation where Type = 'L';

DROP TABLE #backupInformation

RESTORE DATABASE [$(database)] FROM DISK = '$TARGETDIR$\DATABASE\$DBNAME$.bak'
WITH REPLACE,
MOVE @logicalNameD TO '$TARGETDIR$\DATABASE\$DBNAME$.mdf',
MOVE @logicalNameL TO '$TARGETDIR$\DATABASE\$DBNAME$_log.ldf'
GO

IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE name = N'CONNECTAPP')
CREATE LOGIN CONNECTAPP WITH PASSWORD = 'CONNECT'
GO

USE [$DBNAME$]
exec sp_changedbowner 'CONNECTAPP'
GO

[giaviv]

Does your SQL script work outside of IA? Did you try running it in a SQL management studio or another program of similar functionality? I am guessing that that is where your problem lies..

Thanks

[gibbie99]

Yes it works. Previously I had a batch file running the script. However since I am incorporating it into installaware I am passing IA variables. Therein lies the problem.

Is it possible to view the SQL output through installaware, ie through a message box or maybe write to a log file? If there is no way to debug this then I'll go back to using batch files.

Thanks

[giaviv]

Dear gibbie99,

Another IA user, BobSchaefer, had suggested the following:

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.

You can refer to his thread here:
http://www.installaware.com/forum/viewt ... f=2&t=6063

Thanks!

[BobSchaefer]

If the $RESULT$ variable is blank that means the script is not generating an error and is completing successfully. Are you seeing that it is not? From what I can see, and what little I know about that level of SQL code, everything looks fine, you are using the variables correctly. Also, make sure you have Abort execution on script error checked to make sure it returns any errors in the $RESULT$ variable.