/sync fails with invalid syntax, but /scriptfile works fine

• 
  Hi there,
  
  Can you please post the entire output so that we can see at what point the error occurred? You can truncate the object list within the output if it is too long.
  
  Thanks,
  
  Jonathan

  JonathanWatts July 11, 2007 18:48

  - 0 +
• 
  the complete output is:
  

  E:\Red Gate\Jobs>"E:\Red Gate\SQL Compare 6\SQLCompare.exe" /verbose /exclude:us
  er /exclude:role /exclude:schema /options:iw,iweo,iu,iup,irpt /server1:the_real_
  trsprodb /server2:ladbtrsmo01\latrsmom /database1:trsncp /database2:trsncp /sync
  
  SQL Compare Command Line V6.0.0.1410
  ==============================================================================
  Copyright c Red Gate Software Ltd 1999-2007
  
  Serial Number:
  
  SQL Compare running with option: IgnoreWhiteSpace (OK).
  SQL Compare running with option: IgnoreWithElementOrder (OK).
  SQL Compare running with option: IgnoreUsers (OK).
  SQL Compare running with option: IgnoreUserProperties (OK).
  SQL Compare running with option: IgnoreReplicationTriggers (OK).
  Registering databases
  Creating mappings
  Comparing
  Replaying user actions
  Comparing database the_real_trsprodb.trsncp with database ladbtrsmo01\latrsmom.t
  rsncp...
  Creating SQL
  Synchronizing databases
  Error: Error Comparing the_real_trsprodb/trsncp vs ladbtrsmo01\latrsmom/trsncp :
  Incorrect syntax near the keyword 'create'. Incorrect syntax near 'end'.
  
  E:\Red Gate\Jobs>
  

  
  that's with the verbose option. Is there a debug option that I could use that would give more output?

  andersom July 12, 2007 10:16

  - 0 +
• 
  Hi there,
  
  Does this work with the UI synchronising? If it does fail in the UI what object and object type does it fail on?
  
  I understand that this isn't always possible, but is it going to be possible to provide snapshots of the databases in question?
  
  Regards,
  
  Jonathan

  JonathanWatts July 12, 2007 10:29

  - 0 +
• 
  The UI gave the same error, but with more detail:
  

  The following error message was returned from the SQL Server:
  
  [156] Incorrect syntax near the keyword 'create'.
  Incorrect syntax near 'end'.
  
  The following SQL command caused the error:
  
  create procedure "sp_MSins_rspart_contrib" @c1 char(12),@c2 char(3),@c3 float,@c4 char(4),@c5 char(4),@c6 char(4),@c7 float,@c8 float,@c9 float,@c10 float,@c11 float,@c12 float,@c13 char(4),@c14 char(4),@c15 varchar(4),@c16 char(4),@c17 char(4),@c18 char(4),@c19 float,@c20 float,@c21 float,@c22 float,@c23 char(4),@c24 int,@c25 int,@c26 char(4),@c27 char(4),@c28 char(4),@c29 char(4),@c30 char(64),@c31 char(64),@c32 varchar(4),@c33 varchar(4),@c34 datetime,@c35 varchar(4),@c36 varchar(4),@c37 varchar(4),@c38 varchar(4),@c39 varchar(4),@c40 varchar(4),@c41 varchar(4),@c42 varchar(4),@c43 datetime,@c44 varchar(4),@c45 datetime,@c46 datetime,@c47 varchar(4),@c48 varchar(4),@c49 varchar(4),@c50 varchar(4),@c51 varchar(4),@c52 varchar(4),@c53 float,@c54 float,@c55 float,@c56 float,@c57 varchar(4),@c58 float,@c59 float,@c60 float,@c61 float,@c62 varchar(4),@c63 varchar(4),@c64 float,@c65 float,@c66 varchar(255),@c67 varchar(255),@c68 varchar(4),@c69 varchar(4),@c70 varchar(4),@c71 varchar(4),@c72 varchar(4),@c73 varchar(4),@c74 varchar(4),@c75 varchar(4),@c76 varchar(4),@c77 varchar(4),@c78 varchar(4),@c79 char(4),@c80 varchar(4),@c81 char(4),@c82 varchar(4),@c83 varchar(100),@c84 char(4),@c85 char(4),@c86 char(4),@c87 uniqueidentifier,@c88 datetime,@c89 datetime,@c90 char(4),@c91 char(4),@c92 char(4),@c93 char(4),@c94 char(4),@c95 char(4),@c96 char(4),@c97 char(4),@c98 char(4),@c99 char(4),@c100 char(4),@c101 char(4),@c102 char(4)
  
  AS
  BEGIN
  
  
  insert into "rspart_contrib"( 
  "client_id", "plan_id", "irs_annual_max_amt", "irs_max_pct", "contrib_max_freq", "contrib_max_tax_status", "contrib_min_pct", "contrib_max_pct", "contrib_min_amt", "contrib_max_amt", "contrib_min_aftertax_amt", "contrib_max_aftertax_amt", "allocation_direction_by", "rollover_contrib_status", "rollover_alloc_by", "rollover_wd_freq", "part_alloc_chg_freq", "elect_contrib_chg_freq", "contrib_min_aftertax_pct", "contrib_max_aftertax_pct", "max_contrib_allowed_amt", "max_contrib_allowed_pct", "auto_enroll_type", "auto_enroll_pct", "auto_enroll_fund", "auto_enroll_apply", "direct_transfers", "direct_investments", "investment_allocation", "investment_allocation_other", "investment_changes_other", "elect_limit_increase", "catchup_allowed", "catchup_effective", "susp_elect_contrib", "direct_ro_401a_403a", "direct_ro_403b", "direct_ro_457", "part_ro_401a_403a", "part_ro_403b", "part_ro_457", "part_ro_ira", "egtrra_ro_effective", "exclude_ro_cashout", "cashout_distribution", "cashout_term_part", "inservice_elect", "elective_allowed", "auto_enrollment", "contrib_mp", "mp_contrib_type", "mp_contrib_nonintegr_spec", "mp_prc_part", "mp_dollar_part", "mp_part_year_first", "mp_part_years", "mp_contrib_integr_spec", "mp_prc_excess_ss", "mp_prc_eligible_comp", "mp_comp_prc", "mp_dollar_excess", "mp_early_distrib", "mp_provis_cont", "default_max", "total_contrib_limit", "chg_elect_contrib_addendum_eng", "auto_enroll_addendum_eng", "direct_elective", "direct_match", "direct_nonmatch", "direct_rollover", "direct_aftertax", "excl_period_mp", "after_tax_contrib", "elective_pct", "elective_amt", "aftertax_pct", "aftertax_amt", "Term_Disn_Fees", "mp_discontinued", "elective_addendum", "elect_contrib_discontinued", "direct_transfers_other", "catchup_dollar", "catchup_percent", "catchup_onetime", "msrepl_tran_version", "provider_withhold_date", "auto_enroll_eff_date", "direct_ro_401k", "direct_ro_Other401k", "direct_ro_403a", "direct_ro_ProfitSharing", "direct_ro_MoneyPurchase", "direct_ro_TraditionalIRA", "direct_ro_Roth401K", "part_ro_401k", "part_ro_Other401k", "part_ro_403a", "part_ro_ProfitSharing", "part_ro_MoneyPurchase", "part_ro_Roth401K"
   )
  
  values ( 
  @c1, @c2, @c3, @c4, @c5, @c6, @c7, @c8, @c9, @c10, @c11, @c12, @c13, @c14, @c15, @c16, @c17, @c18, @c19, @c20, @c21, @c22, @c23, @c24, @c25, @c26, @c27, @c28, @c29, @c30, @c31, @c32, @c33, @c34, @c35, @c36, @c37, @c38, @c39, @c40, @c41, @c42, @c43, @c44, @c45, @c46, @c47, @c48, @c49, @c50, @c51, @c52, @c53, @c54, @c55, @c56, @c57, @c58, @c59, @c60, @c61, @c62, @c63, @c64, @c65, @c66, @c67, @c68, @c69, @c70, @c71, @c72, @c73, @c74, @c75, @c76, @c77, @c78, @c79, @c80, @c81, @c82, @c83, @c84, @c85, @c86, @c87, @c88, @c89, @c90, @c91, @c92, @c93, @c94, @c95, @c96, @c97, @c98, @c99, @c100, @c101, @c102
   )
  
  
  END
  
  GO
  create procedure "sp_MSins_rspart_contrib";2 @c1 char(12),@c2 char(3),@c3 float,@c4 char(4),@c5 char(4),@c6 char(4),@c7 float,@c8 float,@c9 float,@c10 float,@c11 float,@c12 float,@c13 char(4),@c14 char(4),@c15 varchar(4),@c16 char(4),@c17 char(4),@c18 char(4),@c19 float,@c20 float,@c21 float,@c22 float,@c23 char(4),@c24 int,@c25 int,@c26 char(4),@c27 char(4),@c28 char(4),@c29 char(4),@c30 char(64),@c31 char(64),@c32 varchar(4),@c33 varchar(4),@c34 datetime,@c35 varchar(4),@c36 varchar(4),@c37 varchar(4),@c38 varchar(4),@c39 varchar(4),@c40 varchar(4),@c41 varchar(4),@c42 varchar(4),@c43 datetime,@c44 varchar(4),@c45 datetime,@c46 datetime,@c47 varchar(4),@c48 varchar(4),@c49 varchar(4),@c50 varchar(4),@c51 varchar(4),@c52 varchar(4),@c53 float,@c54 float,@c55 float,@c56 float,@c57 varchar(4),@c58 float,@c59 float,@c60 float,@c61 float,@c62 varchar(4),@c63 varchar(4),@c64 float,@c65 float,@c66 varchar(255),@c67 varchar(255),@c68 varchar(4),@c69 varchar(4),@c70 varchar(4),@c71 varchar(4),@c72 varchar(4),@c73 varchar(4),@c74 varchar(4),@c75 varchar(4),@c76 varchar(4),@c77 varchar(4),@c78 varchar(4),@c79 char(4),@c80 varchar(4),@c81 char(4),@c82 varchar(4),@c83 varchar(100),@c84 char(4),@c85 char(4),@c86 char(4),@c87 uniqueidentifier,@c88 datetime,@c89 datetime,@c90 char(4),@c91 char(4),@c92 char(4),@c93 char(4),@c94 char(4),@c95 char(4),@c96 char(4),@c97 char(4),@c98 char(4),@c99 char(4),@c100 char(4),@c101 char(4),@c102 char(4)
  as
  if exists ( select * from "rspart_contrib"
  where "client_id" = @c1 and "plan_id" = @c2
  )
  begin
  update "rspart_contrib" set "irs_annual_max_amt" = @c3,"irs_max_pct" = @c4,"contrib_max_freq" = @c5,"contrib_max_tax_status" = @c6,"contrib_min_pct" = @c7,"contrib_max_pct" = @c8,"contrib_min_amt" = @c9,"contrib_max_amt" = @c10,"contrib_min_aftertax_amt" = @c11,"contrib_max_aftertax_amt" = @c12,"allocation_direction_by" = @c13,"rollover_contrib_status" = @c14,"rollover_alloc_by" = @c15,"rollover_wd_freq" = @c16,"part_alloc_chg_freq" = @c17,"elect_contrib_chg_freq" = @c18,"contrib_min_aftertax_pct" = @c19,"contrib_max_aftertax_pct" = @c20,"max_contrib_allowed_amt" = @c21,"max_contrib_allowed_pct" = @c22,"auto_enroll_type" = @c23,"auto_enroll_pct" = @c24,"auto_enroll_fund" = @c25,"auto_enroll_apply" = @c26,"direct_transfers" = @c27,"direct_investments" = @c28,"investment_allocation" = @c29,"investment_allocation_other" = @c30,"investment_changes_other" = @c31,"elect_limit_increase" = @c32,"catchup_allowed" = @c33,"catchup_effective" = @c34,"susp_elect_contrib" = @c35,"direct_ro_401a_403a" = @c36,"direct_ro_403b" = @c37,"direct_ro_457" = @c38,"part_ro_401a_403a" = @c39,"part_ro_403b" = @c40,"part_ro_457" = @c41,"part_ro_ira" = @c42,"egtrra_ro_effective" = @c43,"exclude_ro_cashout" = @c44,"cashout_distribution" = @c45,"cashout_term_part" = @c46,"inservice_elect" = @c47,"elective_allowed" = @c48,"auto_enrollment" = @c49,"contrib_mp" = @c50,"mp_contrib_type" = @c51,"mp_contrib_nonintegr_spec" = @c52,"mp_prc_part" = @c53,"mp_dollar_part" = @c54,"mp_part_year_first" = @c55,"mp_part_years" = @c56,"mp_contrib_integr_spec" = @c57,"mp_prc_excess_ss" = @c58,"mp_prc_eligible_comp" = @c59,"mp_comp_prc" = @c60,"mp_dollar_excess" = @c61,"mp_early_distrib" = @c62,"mp_provis_cont" = @c63,"default_max" = @c64,"total_contrib_limit" = @c65,"chg_elect_contrib_addendum_eng" = @c66,"auto_enroll_addendum_eng" = @c67,"direct_elective" = @c68,"direct_match" = @c69,"direct_nonmatch" = @c70,"direct_rollover" = @c71,"direct_aftertax" = @c72,"excl_period_mp" = @c73,"after_tax_contrib" = @c74,"elective_pct" = @c75,"elective_amt" = @c76,"aftertax_pct" = @c77,"aftertax_amt" = @c78,"Term_Disn_Fees" = @c79,"mp_discontinued" = @c80,"elective_addendum" = @c81,"elect_contrib_discontinued" = @c82,"direct_transfers_other" = @c83,"catchup_dollar" = @c84,"catchup_percent" = @c85,"catchup_onetime" = @c86,"msrepl_tran_version" = @c87,"provider_withhold_date" = @c88,"auto_enroll_eff_date" = @c89,"direct_ro_401k" = @c90,"direct_ro_Other401k" = @c91,"direct_ro_403a" = @c92,"direct_ro_ProfitSharing" = @c93,"direct_ro_MoneyPurchase" = @c94,"direct_ro_TraditionalIRA" = @c95,"direct_ro_Roth401K" = @c96,"part_ro_401k" = @c97,"part_ro_Other401k" = @c98,"part_ro_403a" = @c99,"part_ro_ProfitSharing" = @c100,"part_ro_MoneyPurchase" = @c101,"part_ro_Roth401K" = @c102
  where "client_id" = @c1 and "plan_id" = @c2
  end
  else
  begin
  insert into "rspart_contrib" ( "client_id","plan_id","irs_annual_max_amt","irs_max_pct","contrib_max_freq","contrib_max_tax_status","contrib_min_pct","contrib_max_pct","contrib_min_amt","contrib_max_amt","contrib_min_aftertax_amt","contrib_max_aftertax_amt","allocation_direction_by","rollover_contrib_status","rollover_alloc_by","rollover_wd_freq","part_alloc_chg_freq","elect_contrib_chg_freq","contrib_min_aftertax_pct","contrib_max_aftertax_pct","max_contrib_allowed_amt","max_contrib_allowed_pct","auto_enroll_type","auto_enroll_pct","auto_enroll_fund","auto_enroll_apply","direct_transfers","direct_investments","investment_allocation","investment_allocation_other","investment_changes_other","elect_limit_increase","catchup_allowed","catchup_effective","susp_elect_contrib","direct_ro_401a_403a","direct_ro_403b","direct_ro_457","part_ro_401a_403a","part_ro_403b","part_ro_457","part_ro_ira","egtrra_ro_effective","exclude_ro_cashout","cashout_distribution","cashout_term_part","inservice_elect","elective_allowed","auto_enrollment","contrib_mp","mp_contrib_type","mp_contrib_nonintegr_spec","mp_prc_part","mp_dollar_part","mp_part_year_first","mp_part_years","mp_contrib_integr_spec","mp_prc_excess_ss","mp_prc_eligible_comp","mp_comp_prc","mp_dollar_excess","mp_early_distrib","mp_provis_cont","default_max","total_contrib_limit","chg_elect_contrib_addendum_eng","auto_enroll_addendum_eng","direct_elective","direct_match","direct_nonmatch","direct_rollover","direct_aftertax","excl_period_mp","after_tax_contrib","elective_pct","elective_amt","aftertax_pct","aftertax_amt","Term_Disn_Fees","mp_discontinued","elective_addendum","elect_contrib_discontinued","direct_transfers_other","catchup_dollar","catchup_percent","catchup_onetime","msrepl_tran_version","provider_withhold_date","auto_enroll_eff_date","direct_ro_401k","direct_ro_Other401k","direct_ro_403a","direct_ro_ProfitSharing","direct_ro_MoneyPurchase","direct_ro_TraditionalIRA","direct_ro_Roth401K","part_ro_401k","part_ro_Other401k","part_ro_403a","part_ro_ProfitSharing","part_ro_MoneyPurchase","part_ro_Roth401K" ) values ( @c1,@c2,@c3,@c4,@c5,@c6,@c7,@c8,@c9,@c10,@c11,@c12,@c13,@c14,@c15,@c16,@c17,@c18,@c19,@c20,@c21,@c22,@c23,@c24,@c25,@c26,@c27,@c28,@c29,@c30,@c31,@c32,@c33,@c34,@c35,@c36,@c37,@c38,@c39,@c40,@c41,@c42,@c43,@c44,@c45,@c46,@c47,@c48,@c49,@c50,@c51,@c52,@c53,@c54,@c55,@c56,@c57,@c58,@c59,@c60,@c61,@c62,@c63,@c64,@c65,@c66,@c67,@c68,@c69,@c70,@c71,@c72,@c73,@c74,@c75,@c76,@c77,@c78,@c79,@c80,@c81,@c82,@c83,@c84,@c85,@c86,@c87,@c88,@c89,@c90,@c91,@c92,@c93,@c94,@c95,@c96,@c97,@c98,@c99,@c100,@c101,@c102 )
  end
  
  
  The following messages were returned from the SQL Server:
  
  [0] Creating [dbo].[sp_MSins_rspart_contrib]
  

  
  Note that this occurs on multiple DBs in our environment. I'm not sure if all of the errors occur on replication procs as above though.

  andersom July 12, 2007 11:10

  - 0 +
• 
  Also, to answer your question; it will not be possible to send DB snapshots in this case due to sensitive data.

  andersom July 12, 2007 11:12

  - 0 +
• 
  Hi there,
  
  Okay, I can see the problem, you have a numbered stored procedure.
  
  Unfortunately this is a limitation within the SQL Compare engine, whilst SQL Compare can compare numbered stored procedures the execution of scripts containing them fails as the execution block cannot contain a GO batch delimiter which between the two parts of the stored procedure.
  
  Currently the only workaround is to execute the script manually.
  
  Regards,
  
  Jonathan

  JonathanWatts July 12, 2007 12:27

  - 0 +
• 
  Ok, thanks for the response. I should be able to work around the limitation by outputting to a script file and calling osql.

  andersom July 12, 2007 13:19

  - 0 +

Add comment

Please sign in to leave a comment.