gsmmc
Posts: 7
Joined: Wed Feb 28, 2007 11:42 pm

SQL Script Execution

Regarding SQL Scripts,

It would be nice to have an option in the "In case of error" section for "Continue Anyway"...

If for example I expect an error or two to occur in the script under certain conditions but the errors are inconsequential, the entire script should still execute to completion.

"Go to the next statement in the script file" is not working properly for me because I have no statement seperator so defining a semi-colon as the statement seperator in AdvancedInstaller is not working. Selecting this option just yeilds error after error of broken SQL statements and I have no other option available to continue executing the script...

The nature of transact-sql is for an entire transaction to fail anyway so you should never need to go to the next statement in a script file... it should go to the next transaction (e.g. the end of the failed transaction, often specified as "GO" in syntax).


Thanks,
GS
gigi
Posts: 2103
Joined: Tue Apr 11, 2006 9:55 am
Contact: Website

Hi,

You should use GO as "Statement Separator" in Advanced Installer "SQL Script" page. Please let us know if this does not solves your problem.

Regards,
Gigi
_______________
Gheorghe Rada
Advanced Installer Team
http://www.advancedinstaller.com
gsmmc
Posts: 7
Joined: Wed Feb 28, 2007 11:42 pm

Hi gigi,
Thanks for the reply.

For some reason, using GO as the statement seperator is still not executing the script. The script works fine when run in SQL 2005 Management Studio or when passed using osql.exe but is not being executed within AdvancedInstaller.

I believe it may not actually be related to the script execution itself though, I think it's not working because SQL Server 2005 Express named instances run on a dynamic port (unlike MSDE which ran on port 1433). Unfortunately I'm required to enter a port number and I won't know what port the SQL 2005 Express Instance is running on until it's installed :)

Let me know if you know how to get around this,
Thanks!
GS
gigi
Posts: 2103
Joined: Tue Apr 11, 2006 9:55 am
Contact: Website

Hi,
Unfortunately I'm required to enter a port number and I won't know what port the SQL 2005 Express Instance is running on until it's installed
You can configure the port number from UI. You need to add the "SQLConnectionDlg" in the "Dialogs" page. Also you can modify this dialog by adding a new info text explaining to the user what the "Port" field means.

Now in the "SQL Scripts->SQL Connection" page type [PORT_PROP] in the "Port" field. In this way the user can specify the Server port at install time.

Hope this helps.

Regards,
Gigi
_______________
Gheorghe Rada
Advanced Installer Team
http://www.advancedinstaller.com
gsmmc
Posts: 7
Joined: Wed Feb 28, 2007 11:42 pm

Thanks!
I'll give this a try
gsmmc
Posts: 7
Joined: Wed Feb 28, 2007 11:42 pm

FYI, it is the script execution afterall...
After turning on verbose and stepping through the script at the point of failure I was able to determine this.

Just thought i'd drop in to let you know,
I'll post an example script that works in SQL Server Management Studio but fails in AdvancedInstaller as soon as i get some time...

GS
Rumburak
Posts: 7
Joined: Wed Mar 21, 2007 12:46 pm

I have the same Problem with SQL Server 2005 and SQL Server 7, so I know the problem doesnt come with the Server-version.
When I use the statement separator "GO" I get this error message:
SQLExecDirect {SQL Error}: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'TABLE'.
This is my SQL-Script:

Code: Select all

USE DATEN
GO

SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT OFF
GO

IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
GO
CREATE TABLE #tmpErrors (Error int)
GO
SET XACT_ABORT ON
GO
Rumburak
Posts: 7
Joined: Wed Mar 21, 2007 12:46 pm

I found the reason:
You must not use "#" in SQL-Scripts!
IMHO its a bug in AI.
gigi
Posts: 2103
Joined: Tue Apr 11, 2006 9:55 am
Contact: Website

Hi,

In order to investigate the problem please send the AIP file and all required SQL scripts (that you added them in the "SQL Scripts" page) or a small similar test case reproducing this issue to support at advancedinstaller dot com.

Waiting for your reply.

Regards,
Gigi
______________
Gheorghe Rada
Advanced Installer Team
http://www.advancedinstaller.com
Rumburak
Posts: 7
Joined: Wed Mar 21, 2007 12:46 pm

Return to “Common Problems”