Using TMSSqlRow to Populate stop words list on SQL Server

[Anonymous]

I have a text file list of bad words for a profanity filter and was trying to load them.
I am trying to execute the following SQL using a TMSSqlRow component
"alter fulltext stoplist BadWords add ? LANGUAGE 'English' "
I have ticked use Prepared Statement and 1 parameter in my list which is to consume the bad words from the connector from the TMap
When I execute it I can see all words moving along the pipeline as the iterations of reading the file and passing them through the tMap are incrementing
Unfortunately, I get no stop words added to the list and the error I get is "Invalid parameter index 0.' for each iteration
I have a connection, a tinputfileDelimeted a tMap and the tMSSQLRow to do this small piece of work
What am I missing? 

[Anonymous]

can you please share the screenshot of your job?

[Anonymous]

Hi Here is the image

I am only interested in the activated part I may use the inactivated part once I understand what causes the problem

Cheers

Peter

[vapukov]

Did You check Advanced settings?

[Anonymous]

I have set "Use prepared statement" and have created a parameter 0 for the badwords value from the row being iterated forward from the map. 
Tried it with the "Propogate query recordset" checked and unchecked 








Hopefully, someone can see the error in what I have done

Cheers

[Anonymous]

2 points:
1. Problem is that in between tMap and tMsSQLRow component, you need to use a tFlowtoIterate component will will allow you to iterate through the output of the tMap
2.  tFlowtoIterator will create globalMap variable for you to access the bad words which you can directly use in place of "?" in your query (" "alter fulltext stoplist BadWords add ? LANGUAGE 'English' ""  ). That way you do not have to use prepared statement at all and you will get rid of that error
let me know if you are able to code it else I will create a sample program for you.

[Anonymous]

Thanks Sankalp :cool:
That has solved my problem I have been able to execute that process to create a stopwords list in SQL Server from existing words lists available around the net

Cheers
Peter