SQL Server MEREG Statement Before SQL Error

[nvalia]

Hi All,

We r on DS 8.7 on Winddows 2008 R2

We are using a Row Generator stage to generate 1 Single record and then using the SQL Server MERGE statement to load data from Staging to Final table. This works fine when I use a 1-Node Configuraion and use this MERGE in the Before SQL (node) statement .

But when I put this same MERGE in Before SQL statement (NOT at node level) in a 2 Node Config file with only 1 Input record from the RG stage, as above, it Fails with Error saying a semi colon is needed at the end of the MERGE statement..I have this ; but it still fails here?

Error Code = 10,713: Msg = [Microsoft][SQL Server Native Client 10.0][SQL Server]A MERGE statement must be terminated by a semi-colon (;). (CC_OdbcAdapter::preRun, file CC_OdbcAdapter.cpp, line 434)

Any thoughts Why?
Thanks,
NV

[ArndW]

I use the BEFORE/AFTER options with SQL Server quite a bit and they don't require an explicit semicolon.

I wonder if the MERGE command might be different, could you try adding ";GO;" to the end of the command line?

[nvalia]

Yes MERGE is different and we need to terminate it with a semi colon

But it is only failing when used in the Before SQL Statement with 2 Node Config file but not when used in a Before SQL (node) statement with a 1Node Config file. (In both cases the Semi Colin is provided at the end)

From my understanging the Before/After SQL statements are executed only once, irrespective of the nodes in the config file, so why should it be any different then Before SQL (node) statement in a 1Node config file?

[srinivas.g]

Put merge statement in before job subroitine under job properties.