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

andersom

I'm having some command line scripting issues. Specifically, I am issuing the following command line:

SQLCompare.exe /verbose /exclude:user /exclude:role /exclude:schema /options:iw,iweo,iu,iup,irpt /server1:the_real_trsprodk /server2:ladbtrsmo01\latrsmom /database1:cms_warehouse /database2:cms_warehouse /sync

And recieving the error:

Error: Error Comparing the_real_trsprodk/cms_warehouse vs
ladbtrsmo01\latrsmom/cms_warehouse : Incorrect syntax near the keyword 'create'.
Incorrect syntax near 'end'.

However when I remove the /sync from the command and replace it with /scriptfile:out.sql, I can then run the out.sql without error in a SQL Management Studio query window.

Is this a known issue? I'm currently using SQL Compare Command Line V6.0.0.1410

JonathanWatts

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

andersom

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?

JonathanWatts

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

andersom

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

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

JonathanWatts

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

andersom

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