unclosed quotation mark after the character strin

dataservices job design
1

Hi -

I am faced with a weird error in data services. We have INITIAL and DELTA loads for a particular dimension table is designed separately. They have been running fine for quite sometime. When I am doing a delta load, I ran into this error message "[Microsoft][ODBC SQL Server Driver][SQL Server]Unclosed
quotation mark after the character string "

And when I looked in the error to I found very odd data posted in the log. All the text fields are padded on the left with capitol letter N.

IF EXISTS (SELECT 1 FROM “DBO”.“TABLE_NAME” WHERE “UNIQUE_ID” = 11111)
UPDATE “DBO”.“TABLE_NAME” SET “COLUMN_1” = N’data1’, “COLUMN2” = N’data2’
WHERE “UNIQUE_ID” = 11111
ELSE INSERT INTO “DBO”.“TABLE_NAME” (“UNIQUE_ID”, “COLUMN_1”, "COLUMN_2)
VALUES (11111, N’data1’, N’data2’ )

so obviously the sql is failing. Wondering if anyone ran into this error. This is only specific to the columns where there is nvl(COLUMN_1, $G_DEFAULT_VALUE) is mapped. Other fields that does not have this kind of mapping are showing the data without captiol N infront of them. Auto correct load and table comparison transforms are both yielding the same error. But the INITIAL load dataflow, which is doing truncate reload is running fine.

Environment:
Data Services: 12.2.2
Repo: SQL server 2008

Anyone faced this error, or have theories please let me know. This kind of things are just reducing the confidence on the tool.

Thanks.

SatyaBODS (BOB member since 2014-02-11)

2

update:
Doesn’t have to do with nvl in the mapping, the same is getting generated without nvl function too. The behavior is limited to varchar datatype fields.

SatyaBODS (BOB member since 2014-02-11)

3

ELSE INSERT INTO “DBO”.“TABLE_NAME” “UNIQUE_ID”, “COLUMN_1”, "COLUMN_2)

COLUMN_2 is missing a quotation mark. Why? No clue.

The N something is related to nvarchar MS SQL and is normal.

Johannes Vink :netherlands: (BOB member since 2012-03-20)

4

Is this a SQL that is generated by DS ?

I see some Single Quotes/Double Quotes that are uniquely used by Microsoft Word ’ , ”

ganeshxp :us: (BOB member since 2008-07-17)

5

Try turning off the quoted identifiers.

eganjp :us: (BOB member since 2007-09-12)

6

Hi - I copy pasted the sql in Microsoft word and edited it to be able to post it in the group. Those microsoft wordy things are the things that are pointed, missing quote on one column is also the result of editing it.

Thanks. I didn’t know prefixing data with N like it did is normal.

SatyaBODS (BOB member since 2014-02-11)

7

Don’t edit the SQL, except to format it for readability. :hb:

eganjp :us: (BOB member since 2007-09-12)

8

12.2.2 is old version ,could be a bug in that

can you try adding another query transform before the target and do one to one mapping to target do any conversion etc, before this query

what other target options are set ?

manoj_d (BOB member since 2009-01-02)

9

Sorry, I had to edit the sql, as there was long list of columns with customer information.

Turned out it is data related bug. As I understand a varchar field has text data in it and is appended with a NULL at the end.
Here is what the oracle ‘dump’ function gave for this data:
postal_code field value looks like this ‘0000’, but the dump function gave this result
Problematic data with dump(field_name):
Result:
Typ=1 Len=5: 48,48,48,48,0

dump(‘0000’)
Result:
Typ=96 Len=4: 48,48,48,48

The additional 0 position at the end seem to have caused the issue. The update sql statement seem to have abruptly ended without quotation due to this. User corrected data in the source system and the job should run fine now.

Let me know if you have questions, I have’t seen anything like this before. It was just very obscure to debug.

Thanks for you input

PS: The initial (truncate reload) loads just ignored this column :shock:

SatyaBODS (BOB member since 2014-02-11)