none
sp_addarticle procedure @filter_clause problem RRS feed

  • Question

  • I have an issue with @filter_clause parameter of sp_addarticle procedure. I try to set a very long filter value (about 10000 symbols), and then i have an error:

    Msg 102, Level 15, State 1: Incorrect syntax near 'O'.

    Msg 21745, Level 16, State 1, Procedure sp_MSrepl_articleview, Line 272

    Cannot generate a filter view or procedure. Verify that the value specified for the @filter_clause parameter of sp_addarticle can be added to the WHERE clause of a SELECT statement to produce a valid query.

    First message are different when i change filter value and it looks like @filter_clause parameter has an restrinction on the length.

    Can i avoid this error?

    Additionaly this procedure has parameter - @filter, and i think that error occurs when sp_addarticle trying to create filter procedure.

    Can i create filter procedure manually and set it to @filter parameter, is this the same as setting @filter_clause parameter?


    • Edited by Antonio999 Wednesday, January 9, 2013 12:32 PM
    • Moved by Naomi N Thursday, January 10, 2013 2:18 AM Better answer can be here (From:Transact-SQL)
    Wednesday, January 9, 2013 12:27 PM

Answers


  • It works, need to set @type parameter to 'logbased manualview' (for manually created view) or to 'logbased manualboth' (for manually created view and filter procedure)
    Thursday, January 10, 2013 1:35 PM

All replies

  • Following is the correct forum for your question:

    SQL Server Replication


    Kalman Toth
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016

    • Edited by Kalman Toth Wednesday, November 8, 2017 6:57 PM
    Wednesday, January 9, 2013 1:21 PM
  • I found a solution when i create article view manually before and use @sync_object parameter error not occurs, but if i use sp_articlefilter procedure after with the same @filter_clause it raise the same error.

    If i create filter before manually (like view) then the error does not appear. Do i need to fill @filter_clause parameter in this case (if i create filter and view manually) or it is not needed?

    Thursday, January 10, 2013 6:28 AM
  • It doesn't work :(

    If i use parameter @filter_clause article is created without errors, but during replication i have the same error.

    If i don't use the parameter, filter does not apply (view is recreated without clause, because the result is all table rows).


    • Edited by Antonio999 Thursday, January 10, 2013 9:27 AM
    Thursday, January 10, 2013 6:59 AM

  • It works, need to set @type parameter to 'logbased manualview' (for manually created view) or to 'logbased manualboth' (for manually created view and filter procedure)
    Thursday, January 10, 2013 1:35 PM