Problem running MS SQL Server filescript

[marcusi]

I am using InstallAware X2 and trying to run a MS SQL Server Script from a file with the special #FILESCRIPT# value. This is the command i use:

Code: Select all

~InstallAware Clipboard Data~
~MS SQL Server~
~{93862E2E-767F-40B6-ADDA-57E51F15BA71}~
~$SERVERNAME$,$INSTANCENAME$,$SQLDATABASE$,$SQLUSER$,$SQLPASSWORD$,SQLRESULT,#FILESCRIPT#$SUPPORTDIR$\pasdb.sql,FALSE,TRUE~
~mMSSQLIDE.dll\mMSSQLExec.dll~

Nothing happens when I run it. The result variable SQLRESULT returns SUCCESS but not a single line of code in pasdb.sql has been executed on the SQL Server database. If I run the contents of the file directly in SQL Server Management Studio it works.

If I try to use the exact same parameters (server, database, user etc) and create a simple table as the content of the sql script textbox it also works, like this for example:

Code: Select all

~InstallAware Clipboard Data~
~MS SQL Server~
~{93862E2E-767F-40B6-ADDA-57E51F15BA71}~
~$SERVERNAME$,$INSTANCENAME$,$SQLDATABASE$,$SQLUSER$,$SQLPASSWORD$,SQLRESULT,"CREATE TABLE Test (ID int)",FALSE,TRUE~
~mMSSQLIDE.dll\mMSSQLExec.dll~

I have included pasdb.sql in the Support Files (creatives) in the Project manager and also checked during debug that the file exists in the support directory.

Seems like the #FILESCRIPT# thingy doesn't work properly. Any ideas of what could be wrong?

[FrancescoT]

Dear Marcusi,

did you use "UNICODE" to store your SQL Script?

Alternatively, you may use the approach desctibed by the following link.
viewtopic.php?f=2&t=10060

Hope this helps you.

Regards

[marcusi]

Yes, the file is in unicode.

I've also been thinking about using the sqlcmd utility but it seems like extra work that I was hoping to escape. But this is what installaware recommends then?

I am still confused why the SQLRESULT variable doesn't return an error though. If the script is not executed for whatever reason it should return some kind of error in my opinion. Is it a bug? Is there a limitation on how many lines of code that can be executed?

[FrancescoT]

Dear Marcusi,

the use of sqlcmd doesn't require any extra work. The "Run Program" allows the use of script variables and because you need to consume your MS-SQL Script file into "Support Files" as you have already, the sqlcmd implementation doesn't require any extra effort. I do the same quite often, honestly.

Alternatively, you may try to see if storing your MS-SQL Script file as ANSI (instead of UNICODE) it does the trick.

I am still confused why the SQLRESULT variable doesn't return an error though. If the script is not executed for whatever reason it should return some kind of error in my opinion. Is it a bug?

No it's an error or a bug. This is simply because a SQL script doesn't return any ERROR CODE ... consequently in this case, the "MS SQL Server" plug-in doesn't return any error as well.

Hope this helps you.

Regards

[marcusi]

I tried to save the sql-script file in ANSI now and it started working! #FILESCRIPT# loads the contents of the file and executes all without a problem. Even the progressbar updates correctly which looks neat. It would be nice if the help file of InstallAware could be updated to say that the sql script must be stored in ANSI, especially since SQL Server stores files in unicode as default. Would save a lot of hassle.

One thing though. The PROGRESSTEXT variable updates with each sql statement that are currently executing in the sql script, which can look a bit strange to the customers I think. Is it possible to suppress these texts somehow (while still retain the "normal" progresstexts)?

[FrancescoT]

Dear Marcusi,

unfortunately it's not possible to control the progress-text displayed while executing a sql script.

Regards

[JerryFleck]

unfortunately, it's not possible to control the progress-text displayed while executing a SQL script.

Is this still true? My SQL scripts are producing total gibberish for the customer and a total UI disaster for my "professional" installation. Also, I don't recall $PROGRESSTEXT$ updating to mirror the ProgressLabel output during SQL scripts. Is there a way to control this behavior?

[FrancescoT]

Unfortunately there was no changes on this side.

However, you can always display a "progress text" before running your sql script, and if you have a single time consuming task, you can even split it into multiple tasks.

For example:

Code: Select all

Set Variable PROGRESSTEXT to Creating sql database...
Microsoft SQL Server Script : Connect to database  on instance \ as user sa
 
Set Variable PROGRESSTEXT to Setting up sql tables...
Microsoft SQL Server Script : Connect to database  on instance \ as user sa
 
Set Variable PROGRESSTEXT to Running sql scripts...
Microsoft SQL Server Script : Connect to database  on instance \ as user sa

Hope this helps you.