I have had to deal with "GO" statements at a rather deeper level when I
started working with WIX - I was receiving similar errors and I raised the
same questions you did back then. I have learnt from my mistakes to include
a "GO" statement after every executable statement; just like Mike said, you
need a GO after all your initial server properties (ANSI NULLs, QUOTED IDs,
etc.), at the end of the SPROC, etc.
Also, I have had major issues with using text editors to manipulate the SQL
script files (editors like Notepad 2, Textpad, PSPad, etc.). I have found
that the best thing to do is create and save all your .sql scripts in
Management Studio's Query Analyzer. Also, I don't use the SqlString element;
I think SqlScript is the best alternative.
Besides that, I just deployed a huge SQL batch file (containing more
than 700 lines of pure SQL) last week using WIX without any problems
whatsoever. And believe me, I have all sorts of things in there - percents,
square brackets, "GOs", escaped backslashes, and more. All I had to do was
ensure that each executable statement ended with a GO.
On 5/7/07, Kevin Burton <[EMAIL PROTECTED]> wrote:
Thank you for your response.
I will look into adding "GO" statements. I was just taking the script
generated by SMO. Maybe it needs to be modified.
As far as the version. I guess I was wrong. I was going from memory. It
was the latest. I went back and the DLL's and .EXEs all seem to indicate
version 5206.
The problem with the error messages that I am receiving is that they don't
make sense. That is why I thought Wix was somehow modifying the script that
was being passed in. The error message clearly states that the "MerchantStatus"
is missing but this error message was during a CREATE PROCEDURE which has
nothing to do with the table creation and the existing table does have that
column defined.
*From:* Mike Dimmick [mailto:[EMAIL PROTECTED]
*Sent:* Monday, May 07, 2007 4:38 AM
*To:* 'Kevin Burton'; wix-users@lists.sourceforge.net
*Subject:* RE: [WiX-users] <SqlScript> and escape sequences.
A stored procedure definition must be the only thing in the query batch.
There should be a GO command after SET QUOTED_IDENTIFIER ON and after the
END (note that BEGIN/END are not necessary around a stored procedure body
because of the requirement that it's the only thing in the batch). If there
are GO commands there, then there's a problem with splitting the script at
that point.
I would not have expected to see that much query text in the error message
if there were GO commands surrounding the stored procedure definition and if
the script-splitting were working.
WiX version 2.0.5280.0? Where did you find that? According to the WiX
build numbering scheme that would have to be the 80th day of the month!
There's a build 4820 which is the latest on
http://sourceforge.net/project/showfiles.php?group_id=105970&package_id=114109but
there are weekly releases (builds 5206, 5213) posted at
http://wix.sourceforge.net/releases/. I can't see any problems with
string-splitting, by inspection, in build 4820.
The code 0x80040E14 from your other problem is DB_E_ERRORSINCOMMAND, "One
or more errors occurred during processing of command." It does just look
like the MerchantStatus column is non-existent at the point that the
statement batch was executed.
--
Mike Dimmick
------------------------------
*From:* [EMAIL PROTECTED] [mailto:
[EMAIL PROTECTED] *On Behalf Of *Kevin Burton
*Sent:* 07 May 2007 01:05
*To:* 'Mike Dimmick'; wix-users@lists.sourceforge.net
*Subject:* Re: [WiX-users] <SqlScript> and escape sequences.
I also get errors like:
Error 26204. Error -2147217900: failed to execute SQL string, error
detail: Must declare the scalar variable "@BirthdayEmailID"., SQL key:
CreateBuySeasonsStoredProcedures SQL string: IF EXISTS (SELECT * FROM
sys.objects WHERE object_id =
OBJECT_ID(N'[dbo].[addBirthdayClubReminder]') AND type in (N'P', N'PC'))
. . .
IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'[dbo].[GetConflictingEmails]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetConflictingEmails]
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[GetConflictingEmails]
-- Add the parameters for the stored procedure here
@LowAge int,
@HighAge int,
@Gender tinyint,
@BirthdayEmailID int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT * from BirthdayEmail
where (not BirthdayEmailID = @BirthdayEmailID) and (((@LowAge >= LowAge
and @LowAge <= HighAge) or (@HighAge <= HighAge and @HighAge >= LowAge)) and
((@Gender = 3 or Gender = 3 or Gender = @Gender)))
END
IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'[dbo].[getGenders]') AND type in (N'P', N'PC'))
Like I said these errors don't make any sense so I am assuming the script
that is executing is different from what I expect. This script runs fine
with SQL Server Management Studio.
Kevin
*From:* Mike Dimmick [mailto:[EMAIL PROTECTED]
*Sent:* Sunday, May 06, 2007 12:53 PM
*To:* 'Kevin Burton'; wix-users@lists.sourceforge.net
*Subject:* RE: [WiX-users] <SqlScript> and escape sequences.
What version of WiX are you using? I'm looking at the CA code for
SqlScript for both current releases (2.0.5213.0, 3.0.2813.0) and can't see
where it replaces properties in the script. The function
ScaSqlStrsReadScripts in scasqlstr.cpp (src\ca\serverca\scasched) does a
lot of manipulation to remove comments and split the script at GO commands,
but doesn't appear to either process [] or to call MsiFormatRecord.
You're correct that *SqlString* substitutes properties: it uses
WcaGetRecordFormattedString to retrieve the SQL text, which performs the
format processing (substituting properties, etc).
You can also use "" to delimit identifiers in SQL, as long as the
QUOTED_IDENTIFIER option is set to ON, which it is by default when using OLE
DB (which WiX uses to talk to SQL Server). However, when dumping a script
using Enterprise Manager, each stored procedure is surrounded by SET
statements indicating how the option was set when the stored procedure was
created, so you may need to remove these statements if they set
QUOTED_IDENTIFIER to OFF.
--
Mike Dimmick
------------------------------
*From:* [EMAIL PROTECTED] [mailto:
[EMAIL PROTECTED] *On Behalf Of *Kevin Burton
*Sent:* 06 May 2007 01:12
*To:* wix-users@lists.sourceforge.net
*Subject:* [WiX-users] <SqlScript> and escape sequences.
I have a <SqlScript> task in my Wix file (.wxs) and as best as I can tell
it treats all of the [xxx] as properties so the SQL script does not run.
Does anyone have a solution on how I can run a valid SQL script with WIX?
Ideally I would like Wix to send the stream as is to the SQL engine and
execute it without trying to interpret each line and trying to substitute
properties. I can see <SqlString> substituting but when it comes to the file
I don't see the usefulness. Anyway, is there another task that I can use to
escape all of the required characters in the file before sending it to
<SqlScript>?
Kevin
-------------------------------------------------------------------------
This SF.net email is sponsored by DB2 Express
Download DB2 Express C - the FREE version of DB2 express and take
control of your XML. No limits. Just data. Click to get it now.
http://sourceforge.net/powerbar/db2/
_______________________________________________
WiX-users mailing list
WiX-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/wix-users
-------------------------------------------------------------------------
This SF.net email is sponsored by DB2 Express
Download DB2 Express C - the FREE version of DB2 express and take
control of your XML. No limits. Just data. Click to get it now.
http://sourceforge.net/powerbar/db2/
_______________________________________________
WiX-users mailing list
WiX-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/wix-users
