SqlServer script troubles when using ODBCExecute

Apr 28, 2011 at 12:08 PM

Hi there,

I'm currently implementing a WiX installer to create and set-up a database (MS SQL Server Express 2008). Until recently I used to work with "SqlExtension", but after some of my users requested the ability to select the db server (or instance) themselves, I switched to AppSecInc MSI Extensions and "DataSource".

While the UI is working just fine now, I'm still having difficulties getting my SqlServer-Scripts to run again. The installation always breaks with the following Error:
"Error in SQLExecute(): SQLSTATE: 25000, Native error: 266, Message: Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1."

I did some research on this error and I think I understand its meaning, but according to the installation log file this problem occurs already at the first appearance of a "BEGIN TRANSACTION" (not a the file's end).

 

Here's an extract of the first part of my sql script:

/*
The following statement checks if the tables already exist (at least the first one) and blocks all following code in this case.
*/
IF EXISTS(SELECT * FROM sys.tables WHERE type = 'U' AND name = 'Recipes')
begin
set noexec on
end
GO
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
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
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
PRINT N'Creating [dbo].[PreparationSteps]'
GO
CREATE TABLE [dbo].[PreparationSteps]
(
[Id] [int] NOT NULL IDENTITY(1, 1),
[Name] [nvarchar] (50) COLLATE Latin1_General_CI_AS NOT NULL,
[Comment] [ntext] COLLATE Latin1_General_CI_AS NULL,
[Step] [int] NOT NULL,
[BaseProgramId] [uniqueidentifier] NOT NULL,
[RecipeFK] [int] NOT NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END

 [...]

set noexec off go

 

This is from the installation log file (I removed the "closing MSIHANDLE"-messages):

Execute_ODBC_Deferred: Executing "IF EXISTS(SELECT * FROM sys.tables WHERE type = 'U' AND name = 'Recipes')
begin
set noexec on
end" (CreateDB)
Execute_ODBC_Deferred: Executing "SET NUMERIC_ROUNDABORT OFF" (CreateDB)
Execute_ODBC_Deferred: Executing "SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON" (CreateDB)
Execute_ODBC_Deferred: Executing "IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors" (CreateDB)
Execute_ODBC_Deferred: Executing "CREATE TABLE #tmpErrors (Error int)" (CreateDB)
Execute_ODBC_Deferred: Executing "SET XACT_ABORT ON" (CreateDB)
Execute_ODBC_Deferred: Executing "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE" (CreateDB)
Execute_ODBC_Deferred: Executing "BEGIN TRANSACTION" (CreateDB)
MSI (s) (FC!F8) [12:56:44:515]: Note: 1: 2205 2:  3: Error 
MSI (s) (FC!F8) [12:56:44:515]: Note: 1: 2228 2:  3: Error 4: SELECT `Message` FROM `Error` WHERE `Error` = 1709 
MSI (s) (FC!F8) [12:56:44:515]: Product: FOOD.LOG enterprise Server -- Error in SQLExecute(): SQLSTATE: 25000, Native error: 266, Message: Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.

Execute_ODBC_Deferred: [Execute_ODBC_Deferred] std::exception: Error executing sql statement, terminated

And this is the snippet from my wxs-file:

[...]
    <Binary Id="CreateDB" SourceFile="Resources\CreateDB.sql" />
[...]
            <Component Id='com_sqlDatabase' Guid='{9AE7293E-DFE0-4B2F-B56B-8BA9313AD046}'>
              <DataSource:MSSQLDatabase Id="SqlDatabase" Name="FoodDB" ConnectionId="SQLServerConnection" CreateOnInstall="yes" DropOnUnInstall="no" CheckIfExists="yes">
                <DataSource:ODBCExecuteBinary Id="CreateDB" ExecuteOnInstall="yes" BinaryId="CreateDB" Type="SqlServer" />
            </DataSource:MSSQLDatabase>
[...]

 

Please note that this same script works perfectly with "SqlExtension".

 

I think I'm quite stuck now and I'd really appreciate some help with this.

Thanks,
Rolf

May 2, 2011 at 7:54 AM

Sorry to bother you guys with a "bump", but it's really urgent.

Thank you,
Rolf

May 2, 2011 at 8:40 AM

You can try running your script with ODBQ.

You will find that it will probably give you the same error.

The problem is that ODBQ\ODBCExecute parses the script and will execute the statement when it sees the delimiter (i.e "GO").

The workaround is to not have the delimiter after "BEGIN TRANSACTION", but instead after "COMMIT TRANSACTION" for example.

See dblock's explanation in this thread from a similar issue:

http://msiext.codeplex.com/workitem/11425

May 2, 2011 at 10:16 AM

Thank you for your response.

I tried to remove the delimiter as you said:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
/* REMOVED GO HERE */
PRINT N'Creating [dbo].[PreparationSteps]'
GO
CREATE TABLE [dbo].[PreparationSteps]
(
[Id] [int] NOT NULL IDENTITY(1, 1),
[Name] [nvarchar] (50) COLLATE Latin1_General_CI_AS NOT NULL,
[Comment] [ntext] COLLATE Latin1_General_CI_AS NULL,
[Step] [int] NOT NULL,
[BaseProgramId] [uniqueidentifier] NOT NULL,
[RecipeFK] [int] NOT NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END

But now I get another error:

Execute_ODBC_Deferred: Executing "BEGIN TRANSACTIONPRINT N'Creating [dbo].[PreparationSteps]'" (CreateDB)
MSI (s) (CC!F4) [11:02:40:777]: Note: 1: 2205 2:  3: Error 
MSI (s) (CC!F4) [11:02:40:777]: Note: 1: 2228 2:  3: Error 4: SELECT `Message` FROM `Error` WHERE `Error` = 1709 
MSI (s) (CC!F4) [11:02:40:777]: Product: FOOD.LOG enterprise Server -- Invalid message format

 

Seems as if "ODBQ" cannot distinguish between both statements now.

May 2, 2011 at 7:54 PM

ODBQ ignores line breaks, so insert a space somewhere in-between those statements.

Coordinator
May 2, 2011 at 11:54 PM

Fyi, this was a short term compromise when we were implementing the SQL parser. It could be fixed in theory. 

Generally, this is an ODBC driver, don't confuse it with what SQL Server Studio is using, which is a full blown parser that uses the Microsoft SQL Server driver with all kinds of proprietary (and quite nice) behavior.

May 3, 2011 at 9:22 AM

@icnocop: After removing the print-statement (breaks somehow) and ALL delimiters within the transaction my snippet seems to be working. I'm going to adapt the complete script now. Thanks again for your help!

@dblock: Thanks for clarification; I was a little surprised when my script worked in SQL SMS.