MS SQL Server Script Error

Got a problem you cannot solve? Try here.
JerryFleck
Posts: 12
Joined: Sun Jan 17, 2010 9:20 pm
Contact:

MS SQL Server Script Error

Postby JerryFleck » Sat Aug 28, 2021 7:33 pm

I recently upgraded from version X6 to X13 and am having a problem I can't resolve. I've recompiled and rebuild the project in the new version. Otherwise, I didn't make any material changes in the project but now when I ran a specific SQL script within the project I'm getting an unexplained error. All of my other SQL scripts are working fine.

So I'm running the plugin Microsoft SQL Server Script which starts with the following text:

Code: Select all

DECLARE @name sysname, @datafile nvarchar(256), @logfile nvarchar(256), @cmd nvarchar(1000);
SET @name = N'$SQLDatabaseName$';
SET @datafile = N'$SQLServerDataFile$';
SET @logfile = N'$SQLServerLogFile$';


The error I get returned in my SQLServerResult variable looks like this:
Must declare the scalar variable "@name".DELINEATORSET @name = N'dbMASS Rocket'


So the InstallAware variable, 'SQLDatabaseName,' is being loaded properly within the script but for some reason, when it tries to execute the script it doesn't recognize the DECLARE statement variables. Has anybody seen anything like this before? I've tried everything I can think of with no progress.

FrancescoT
Site Admin
Posts: 5360
Joined: Sun Aug 22, 2010 4:28 am

Re: MS SQL Server Script Error

Postby FrancescoT » Mon Aug 30, 2021 11:17 am

Do you have any chance to post a very minimal project that replicates the issue?
Francesco Toscano
InstallAware Software

White Papers (HowTos) - http://www.installaware.com/publication ... papers.htm
Publications - http://www.installaware.com/publications-review.htm
InstallAware Help -F1 anywhere in the InstallAware IDE

JerryFleck
Posts: 12
Joined: Sun Jan 17, 2010 9:20 pm
Contact:

Re: MS SQL Server Script Error

Postby JerryFleck » Mon Aug 30, 2021 9:29 pm

I've been able to isolate it to this.
When the script goes from:

Code: Select all

DECLARE @name sysname
SET @name = N'$SQLDatabaseName$';

it runs successfully.

to:

Code: Select all

DECLARE @name sysname;
SET @name = N'$SQLDatabaseName$';

it fails.

It's adding a semicolon to the end of a line that causes it to fail.

I found that I could add additional variable definitions and SET commands as long as the last SET command was the first line that ended with a semicolon. e.g.

Code: Select all

DECLARE @name sysname, @datafile NVarChar(256), @logfile NVarChar(256), @cmd nvarchar(1000)

SET @name = N'$SQLDatabaseName$'
SET @datafile = N'$SQLServerDataFile$'
SET @logfile = N'$SQLServerLogFile$';


It's as if the semicolon is behaving as a GO command that ends the batch and then the variables aren't defined for the following code.

JerryFleck
Posts: 12
Joined: Sun Jan 17, 2010 9:20 pm
Contact:

Re: MS SQL Server Script Error

Postby JerryFleck » Tue Aug 31, 2021 11:38 am

I also found that I did have a couple of additional scripts that were running later in the installation process, after this one, that have the same issue. Removing the semicolons from the early lines of the scripts resolved the problem. Semicolons later in the script seem to be okay. One additional note, I'm using SQL Server 2014 SP2.

PaavoN
Posts: 27
Joined: Mon Apr 20, 2009 4:45 pm

Re: MS SQL Server Script Error

Postby PaavoN » Tue Oct 12, 2021 3:19 pm

I have noticed the same problem with semicolon ending the current batch in newer IA versions. This is a really annoying feature/bug as some SQL commands require you to use semicolon before the command (like CTE for example). So if you have CTE inside IF-BEGIN-END block it will cause an error.

I solved the problem by copying mMSSQLExec.dll from X8 to "C:\Program Files (x86)\InstallAware X14\Plug-Ins\MS SQL Server" replacing the one that sits there. X8 seemed to be the last version which works correctly with semicolons.

JerryFleck
Posts: 12
Joined: Sun Jan 17, 2010 9:20 pm
Contact:

Re: MS SQL Server Script Error

Postby JerryFleck » Tue Oct 12, 2021 4:07 pm

PaavoN wrote:I have noticed the same problem with semicolon ending the current batch in newer IA versions. This is a really annoying feature/bug as some SQL commands require you to use semicolon before the command (like CTE for example). So if you have CTE inside IF-BEGIN-END block it will cause an error.

I solved the problem by copying mMSSQLExec.dll from X8 to "C:\Program Files (x86)\InstallAware X14\Plug-Ins\MS SQL Server" replacing the one that sits there. X8 seemed to be the last version which works correctly with semicolons.


I agree. I found it to be very annoying and problematic. I have scripts that create stored procedures that are impacted by this issue. All my installation scripts needed to be reviewed and retested after incorporating the modifications required. Not just the installations scripts, but also all impacted stored procedures that were modified to accommodate this issue.

Thank you for sharing your experience and the workaround you discovered. This issue needs to be addressed by the InstallAware developers!

FrancescoT
Site Admin
Posts: 5360
Joined: Sun Aug 22, 2010 4:28 am

Re: MS SQL Server Script Error

Postby FrancescoT » Wed Oct 13, 2021 6:13 am

Dear Jerry,

can you please share a VERY MINIMAL project that replicates the issue you are reporting?

Please send it as compressed archive to: support@installaware.com, and please also include any SQL SCRIPT which is necessary to replicate the behavior.

Thanks!
Francesco Toscano
InstallAware Software

White Papers (HowTos) - http://www.installaware.com/publication ... papers.htm
Publications - http://www.installaware.com/publications-review.htm
InstallAware Help -F1 anywhere in the InstallAware IDE


Return to “Technical Support”

Who is online

Users browsing this forum: No registered users and 50 guests