SQL Azure Database: {SQL_Error}: {SQL_STATE: 42000}

[yogesh12]

Hello: Greetings:

We are creating a package for Windows Azure Cloud system having "SQL Azure Database". The package has a SQL Script that creates a DB.

While we are creating a "New SQL Connection" we do not have an option to select "SQL Azure Database" as the "Connection Type". So we selected "Microsoft SQL Server / MSDE" & created the connection.

On SQLConnectionDlg we have set all the parameters & in published Events, have set the Events-Arguments as
[SqlConnectionString]=Driver=[\{]SQL Server[\}];Server=[SERVER_PROP];Port=[PORT_PROP];Uid=[USERNAME_PROP];Pwd=[PASSWORD_PROP];
DOACTION = TestODBCConnection

During installation the TestODBCConnection is successful & we did move to the next dialog in the sequence, but during the SQL Script execution we are getting an Error as below:

[SQLExecDirect] {SQL_Error}: {SQL_STATE: 42000} [Microsoft][ODBC SQL Server Driver][SQL Server]The CREATE DATABASE statement must be the only statement in the batch.

Please suggest what should be my next step on resolving the issue so that the SQL script gets successfully executed when bundled in the installer

Note:1: When we execute the script manually it executes without any error but same is not the case when its bundled in the installer

Note:2: "SQL Server 2008" database & "SQL Azure Database" are 2 different products of Microsoft & the generated SQL script for database creation for Azure is adhering to the stated steps as mentioned in the section "SQL SERVER – Generate Database Script for SQL Azure" on "http://blog.sqlauthority.com/2010/06/04 ... sql-azure/"

Thanks,
Yogesh

[CiprianComsa]

Hi Yogesh,

I'm not sure why this happens, it could be caused by Statement separator (SQL Batch file separator) defined in SQL Script page (on the sql script file properties).
Please note, we don't have support for multiple separators (at once) between SQL statements.

Regards,
Ciprian
__________________________________________________________________________________________________________________________________________________
Ciprian Comsa
Advanced Installer Team
http://www.advancedinstaller.com/

[Dan]

Hello,

The support for running scripts on Microsoft SQL Azure has been added in version 11.0 of Advanced Installer released on March 13th, 2014.

Best regards,
Dan

[yogesh12]

Thank You Dan

[Dan]

You're welcome,

If there is anything else I can help with, please let me know.

Best regards,
Dan