Variables in Update activity

[eriku40433679]

Hi guys,

I wanted to know if it's possible to use variables(instance or otherwise) in the Update step. I'm getting errors when MSSQL tries to generate the prepared statement. I'm fairly certain this should be possible, so I'm not sure why I'm getting errors.

07/05/2017 10:32:41 AM ODBC error: [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near ')'. SQLState: 37000

07/05/2017 10:32:41 AM ODBC error: [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s) could not be prepared. SQLState: 37000

07/05/2017 10:32:41 AM SQL statement 'INSERT INTO wkf188035_86_1 (iRecTrnState, iPKey1, sPKey1_1, sPKey1_2, iPKey1_3, sPKey_1, sPKey_2, iPKey_3, sFld1065550762, sFld1066795947, iFld1067254700, iSrc1) SELECT   0, case when W0.iIClientId <> 0 then W0.iIClientId else NULL end, C1

07/05/2017 10:32:41 AM .sName, C1.sCampaignName, C1.iClientId, NULL, NULL, NULL, substring(cast(NULL as varchar(255)), 1, 255), substring(cast(NULL as varchar(255)), 1, 255), W0.iIClientId, W0.iIClientId FROM wkf188035_85_1 W0 LEFT JOIN [AAAAA].[dbo].XXXXX

07/05/2017 10:32:41 AM C1 JOIN [BBBB].[dbo].[YYYYYY] C2 ON (C2.iClientId = W0.iIClientId)' could not be executed.

P.s. I've removed the database and table names from the log for propriety's sake.

Thanks in advance!

--Erik

[Vipul_Raghav]

Hi Erik,

I believe you are getting this error because you are trying to Cast NULL as a varchar and then performing substring on it.

Seems the variables you have used in update activity are NULL.

vars.xxx are valid from the activity they are defined in till the next activity.

Try defining them as instance.vars.xxx so that they are valid throughout the workflow.

Regards,

Vipul Raghav

[eriku40433679]

Thanks for taking the time to reply Vipul. I tried to use instance vars instead but there was no change in the update activity. I tried to manually set it back to just normal strings and I'm still getting the same error message, so there might be something else at play here.

The Javascript activity defines my variables that I pass on to the rest of the workflow.

I placed a logInfo to both the enrichment and the update activities and they're both getting the values just fine with the original vars.xxx assignment

Here's the log from the Update activity with the string expression:

07/05/2017 1:34:00 PM ODBC error: [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near ')'. SQLState: 37000

07/05/2017 1:34:00 PM ODBC error: [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s) could not be prepared. SQLState: 37000

07/05/2017 1:34:00 PM SQL statement 'INSERT INTO wkf188035_118_1 (iRecTrnState, iPKey1, sPKey1_1, sPKey1_2, iPKey1_3, sPKey_1, sPKey_2, iPKey_3, sFld1065550762, sFld1066795947, iFld1067254700, iSrc1) SELECT   0, case when W0.iIClientId <> 0 then W0.iIClientId else NULL end, C

07/05/2017 1:34:00 PM 1.sName, C1.sCampaignName, C1.iClientId, NULL, NULL, NULL, substring('dev_OP29', 1, 255), substring('defaultList', 1, 255), W0.iIClientId, W0.iIClientId FROM wkf188035_117_1 W0 LEFT JOIN [BBB].[dbo].XXX C1 JOIN [Campaigns].[dbo].[

07/05/2017 1:34:00 PM YYY] C2 ON (C2.iClientId = W0.iIClientId)' could not be executed.

No more casting nulls but still getting an Incorrect Syntax Error for some reason. The update doesn't even use @campaignName or @listName anymore.

Cheers!

--Erik

[Vipul_Raghav]

Hi Erik,

I just tested this on a local instance and it worked fine.

Only difference is I didn't had an FDA table to update into as evident from your screenshots.

WIll it be possible for you to share the schema details. As this is a FDA table, can you please see if the srcSchema root tag is containing an attribute called view="true"

Can you also try the same thing on a local campaign table rather than FDA. We will then be able to isolate if the problem is related to FDA.

Regards,
Vipul

[eriku40433679]

Hi Vipul,

That's correct, we are linking two FDA tables to each other. Both have view="false", and indeed linking local copies of these schema worked. Can I make it so that the temporary table generated for this workflow is in the FDA database and will that solve the issue with the javascript variables?

I knew that there's a limitation in linking FDA and local tables, but I didn't know this extends to variables too, so thanks for suggesting the steps you mentioned above. It has been very informative.

For this specific workflow, we can truncate the table and just repopulate it every time the workflow is run, so we can get away without the enrichment activity, but I'd like to know if there's a workaround for when we do need it.

Thanks again.

Cheers!

--Erik

[Vipul_Raghav]

Hi Erik,

This could possibly be a bug or I might be completely wrong.

Let me setup a local FDA and come back to you.

Regarding creating a table in FDA database, with FDA when the temporary table is in DB1 and you wish to update table in FDA database DB2, campaign will automatically copy the contents of DB1 temp table to a temp table in DB2 and then process updates/inserts.

You can see this in effect by enabling SQL logging on your workflows.

Regards,
Vipul

[eriku40433679]

Hi Vipul,

Interesting. I'll take a closer look.

Seems this is also the case when it comes to query activities against an FDA table? Can you confirm? We're on an old build, version 8594, if that helps.

Cheers!

--Erik

[Vipul_Raghav · Accepted solution]

Hi Erik,

Then it is definitely the old build Blame on it.

Difficult for me to setup such an old one. I was going to test it on 8850.

Regards,
Vipul

[eriku40433679]

Hi Vipul,

Hahahaha! XD

We just cloned our tables on a dev instance and tried the script variables in a Query activity against an FDA table and it is working fine. Not too sure what's happening with the actual instance we're using, so it definitely seems to be an environmental issue.

Sweet! How can I get my hands on 8850? I might build myself a new sandbox.

Cheers, and thanks.

--Erik

[Vipul_Raghav]

HI Erik,

Please log a case with Adobe Support, sharing the build number you want and the OS you wish to host it on.

They will grant you access to the installers

Regards,
Vipul

[eriku40433679]

Will do. Thanks for all the help Vipul!

Cheers!

--Erik

[ddds29763561399y]

1. <script>alert(1)</script>