For the SQL string to get correctly resolved some format rules must be followed:
1- Any Wix properties must be inside square brackets [].
2- Any square brackets that belong to SQl indeces that must be escaped: "[" -> "[\[]" AND "]" ->"[\]]".
3- Each Sql command mus tbe terminated by a semicolon ";".
4- If you use "Execute (N' bla bla ' );" then any string INSIDE this command must have an additional starting and trailing single quote.
5- If you use "Execute (N' bla bla ');" then any double quotation must be escaped by using 'ampersandquot;' instead of ' " '.
The following snippet illustrates each of the previous rules
<Sql:SqlString Id="FillAdjustmentDefinitionsTable" ExecuteOnInstall="yes" ExecuteOnReinstall="yes" Sequence="3" SqlDb="MasterDB" ContinueOnError="no"
SQL="USE DentalDB ;
SET ANSI_PADDING ON ;
Execute(N'CREATE TABLE #tempAdjustmentDefinitionTable(ThisLine xml);
bulk insert #tempAdjustmentDefinitionTable from ' '[APPLICATION]InitialDB\AdjustmentDefinitions.xml' '
with ( codepage=' 'ACP ' ')
INSERT INTO &qout;AdjustmentDefinition"
(
Type,Description,Amount
)
Select
ThisLine.value(' '(AdjustmentDefinition/@Type) [\[]1[\]] ' ', ' 'int' ') ,
ThisLine.value(' '(AdjustmentDefinition/@Description) [\[]1[\]]' ', ' 'nvarchar(100)' '),
ThisLine.value(' '(AdjustmentDefinition/@Amount) [\[]1[\]] ' ', ' 'money' ')
from #tempAdjustmentDefinitionTable
DROP TABLE #tempAdjustmentDefinitionTable ; ' ); "></Sql:SqlString>
No comments:
Post a Comment