Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
MuadDBA
628 Posts |
 Posted - 2007-12-27 : 14:13:06
|
REplication latency is getting to be a big issue for us, so I was looking into ways to improve performance. One of the ways I found was to use the sp_scriptdynamicupdproc to handle updates to heavily indexed tables dynamically instead of always updating every column.Unfortunately, I keep running into the same problem. As soon as I place the proc for the article on the subscriber, I get this error:Incorrect syntax near the keyword 'where'. And replication stops. Then I have to put the original proc back and restart the distribution agent.Any ideas? |
|
|
MuadDBA
628 Posts |
Posted - 2007-12-27 : 14:22:02
|
| Also, looks like the command that fails is not the command I expect (MCALL sp_msupd_xxx) but instead it's just CALL sp_msupd_xxx, can this have anything to do with it? |
 |
|
|
MuadDBA
628 Posts |
Posted - 2007-12-27 : 14:51:41
|
| Huh, upon further research, it appears that using the sp_dynamicupdproc creates a completely worthless stored procedure that will never process any updates to any of the PK fields. The default sp_msupd_xxx handles updates to the PK fields just fine.WTF? The whole point of this proc is to have it handle updating the table dynamically, but what's the use if it won't handle all instances of an update? |
|
|
|
MuadDBA
628 Posts |
Posted - 2007-12-27 : 15:38:34
|
| and yes, I have trace flag 8207 set. |
|
|
|
|
|
|
problem with sp_scriptdynamicupdproc