run sql custom function

[former_member214617]

hi guys

Please tell me how to run the sql function to update the sql table through data services..

see the below sql statement which is running fine in sql management studio, but not through data services

UPDATE Article SET ArticleCategories = (SELECT dbo.[SplitAndRemoveDuplicates](';', ArticleCategories))

I have tried

Print(exec( sql( database_connection', 'UPDATE Article SET ArticleCategories = (SELECT dbo.[SplitAndRemoveDuplicates](';', ArticleCategories))'),'',8 ));

[Script:Script2680]
The script <Script2680> contains an invalid expression. Additional information: <Identifier <SplitAndRemoveDuplicates> is unrecognized. Check its spelling and usage. If it is a variable, it needs a preceding '$'; if a constant, it must be quoted; if a column, verify that such a column exists in a table.>. (BODI-1111242)

Tried creating function call in query transform as well.. but getting an error..

any help please

[former_member211387 · Accepted solution]

you can do it using the standard sql function.

Syntax is sql (<Datastore for the target database>, <SQL Statement>);

for your case if will be something like the below

sql('<>Enter Datastore for the target database' , 'UPDATE Article SET ArticleCategories = (SELECT dbo.SplitAndRemoveDuplicates(\';\', ArticleCategories))');

Raghu

[former_member198401]

Can you tell me what is the output of SplitAndRemoveDuplicates function which you have created.

Regards

Arun Sasi