Skip to Content

run sql custom function

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

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

2 Answers

  • Best Answer
    Apr 04, 2016 at 12:56 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 04, 2016 at 01:18 PM

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

    Regards

    Arun Sasi

    Add comment
    10|10000 characters needed characters exceeded