Help get this topic noticed by sharing it on Twitter, Facebook, or email.

SF_Replicate column subset split and combine

SF_Replicate generates a local empty table with all the columns in it from the SF table; at this point the proc could determine IF a full replication is going to generate a sql statement larger than the 10k character limit.

It would be nice if there was a parameter to tell SF_Replicate to go ahead and pull the table down in parts (use the built in ColumnSubset feature, for example) but then combine the table back into the base table name after the download.

We currently have two tables now that are too wide and while our SSIS package pull the tables down using _ColumnSubset??, we wrote a proc that dynamically puts them back into a single table. I do not expect to have to split a table into 3 _columnSubset pulls but it could happen AND I have an extra proc to call. It would be nice if SF_Replicate took care of this for me.

I am happy to share my existing proc in the meantime for other users and can attach to this feature request OR create a question in the other part of the forrum and attach it there ... (I tried to use the methodology of sf_replicate in our proc for consistancy)
2 people like
this idea
+1
Reply
  • Thanks for the great idea. In a future release we intend to offer a stored procedure that glues together ColumnSubset tables into a single table. This proc will be based on Patrick's work which he has allowed us to use as a starting point.

    At this time, we cannot commit to making this an automated part of sf_replicate.

    Customers will still have to manually construct the needed ColumnSubset tables that work for the problem table based on their knowledge of the column schema of the table.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. sad, anxious, confused, frustrated indifferent, undecided, unconcerned kidding, amused, unsure, silly happy, confident, thankful, excited

  • Hi, our company has reached the 10,000 character SOQL limit as well and I'm in the process of implementing your ColumnSubset solution.

    Patrick, any chance you can send me over your stored proc? I'd like to have a look at it as I'm struggling a bit on how to combine my two subset tables since the primary key is only in the one table.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. sad, anxious, confused, frustrated indifferent, undecided, unconcerned kidding, amused, unsure, silly happy, confident, thankful, excited

  • With Winter 13, salesforce has increased the limit to 20,000 on a pilot basis.

    I would first try to file a case with salesforce and see if you can get be part of the pilot. That would increase your limit to 20,000 and eliminate the need for column subsets.

    If it is not granted, then I have a copy of Patrick's procs (thanks Patrick) that I could send. Just send me an email to support at forceamp.com if you need them.

    I would strongly recommend trying to get into the pilot program instead.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. sad, anxious, confused, frustrated indifferent, undecided, unconcerned kidding, amused, unsure, silly happy, confident, thankful, excited

  • 1
    Geoff, Bill has the procs as he said above and I hope he tweaked them a bit to fit they way he likes to write his procs... however, if for some reason you cannot get them, ask again and I can send directly..(also, I will look over my SSIS package that does ALL the work to make sure I didn't leave anything out when I sent it to Bill)

    That being said, Bill, I am still formally requesting that you add to the base SF_Replicate proc internally to have it determine if the length limit will be exceeded (using your other sysfields call); if so, pull in parts and put back together --- I see it this way: your tool and SF_Replicate is supposed to be a one stop shop to duplicate the SF tables, let it overcome the SF limits without the user having to worry about it -- maybe even an input parameter to the proc, if set, do the split and combine, if not then let the code error if the table is too wide...)

    patrick
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. sad, anxious, confused, frustrated indifferent, undecided, unconcerned kidding, amused, unsure, silly happy, confident, thankful, excited

  • Sorry I'm new to this.... Similar question, would like to replicate only subset of tables to limit size. Following your advice above I took a try with

    Use "salesforce backups"
    Exec SF_Replicate 'SALESFORCE', 'Lead', 'WHERE CreatedDate >= LAST_YEAR'

    But this does not work - probably because the syntax is SOQL..
    (I did get another smaller table to replicate without the where clause.)

    Any advice?
    thank you in advance,
    Pia
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. sad, anxious, confused, frustrated indifferent, undecided, unconcerned kidding, amused, unsure, silly happy, confident, thankful, excited

  • Bill or Patrick,

    Can I get a copy of the proc to re-join from the ColumnSubset.

    Thanks!
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. sad, anxious, confused, frustrated indifferent, undecided, unconcerned kidding, amused, unsure, silly happy, confident, thankful, excited