October 20, 2015 at 9:08 am
I'm having a problem and have no idea how to even begin tracking it down. The environment is Server 2008 32-bit, SQL 2012 SP2 Standard 32-bit (11.00.5058), and Visual Studio 10 SP1 (10.0.40219.1).
I have a query that begins with MERGE <tablename> USING (SELECT blah, blah, blah While in SSMS, I can parse the query with no issues. I can execute the query and see the results I expect. But when I put the same query into an Execute SQL Task in SSIS, it won't parse. It gives me one of those very informative messages that I so love - Query failed to parse. Incorrect syntax near the keyword "MERGE".
I don't know if the 32-bit/64-bit thing has any bearing or not, but I have taken the exact same project folder, copied it to a 64-bit box, and it works fine in both SSMS and SSIS. That one is running Server 2012 R2, SQL 2012 SP2 Standard (11.00.5343), and Visual Studio 10 SP1 (10.0.40219.1).
October 20, 2015 at 9:16 am
If you don't parse the query, what happens if you then execute the package?
John
October 20, 2015 at 9:41 am
Running it in Debug, I get:
[Execute SQL Task] Error: Executing the query "MERGE regional r
using (select * from(
..." failed with the following error: "Incorrect syntax near the keyword 'MERGE'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
If run from a job that executes the package, I get basically the same thing:
attach or insert records:Error: Executing the query "MERGE regional r
using (select * from(
..." failed with the following error: "Incorrect syntax near the keyword 'MERGE'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
October 20, 2015 at 9:52 am
I'm wondering whether SSIS adds some stuff to the beginning of the batch - maybe SET statements or such like. MERGE requires the preceding statement to be terminated with a semi-colon. What happens if you put a semi-colon before the MERGE keyword?
John
October 20, 2015 at 10:00 am
That did it. I can't really say that I understand why it parses without the semicolon on one box, but not the other, but I'm not going to look a gift horse in the mouth. It parses correctly with the semicolon and the package works as designed. Weird...
Many thanks!
October 20, 2015 at 10:41 am
John Mitchell-245523 (10/20/2015)
I'm wondering whether SSIS adds some stuff to the beginning of the batch - maybe SET statements or such like. MERGE requires the preceding statement to be terminated with a semi-colon. What happens if you put a semi-colon before the MERGE keyword?John
Nice one John. Also quite disturbing. Because if SSIS is adding some statements before the MERGE, why are they not terminated properly?
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply