March 20, 2009 at 12:10 pm
Merge and Transactional Replication prevent the TRUNC command from working (I forget the specific error messages).
Are there any other SQL (or DBCC) commands that cannot execute on a Publisher database in SQL Server 2005?
Commentary (skip if pressed for time): Murphy was an optimist - I would love to hear TRUNC is the only one, I just don't expect that answer. I went looking for a list of commands with this behavior and did not find one (doesn't prove such a list doesn't exist, just proves my search skills are limited).
Thanks in advance!
March 23, 2009 at 1:21 pm
steve smith (3/20/2009)
Merge and Transactional Replication prevent the TRUNC command from working (I forget the specific error messages).Are there any other SQL (or DBCC) commands that cannot execute on a Publisher database in SQL Server 2005?
Commentary (skip if pressed for time): Murphy was an optimist - I would love to hear TRUNC is the only one, I just don't expect that answer. I went looking for a list of commands with this behavior and did not find one (doesn't prove such a list doesn't exist, just proves my search skills are limited).
Thanks in advance!
Sorry for the delay. I have been busy 😀
Here are a couple more:
You can't change a primary key on transactional replication. So dropping and recreating it is not an option.
You can't drop a table that is replicated, nor a database that is published.
* Noel
March 23, 2009 at 1:50 pm
Noel, that's true. However, snapshot replication is much more forgiving. By dropping the article (i.e., table) out of the replication, you can then modify the keys. In this case, in order to do the database maintenance - redefining the key values - expanding the field - removing the affected table from replication allowed the maintenance to work. Afterwards, adding the article back into the replication calls for a new snapshot (which makes perfect sense since we've changed the publication), which is what we get with every cycle of replication anyway.
It seems that the biggest challenge is to get oneself looking at replication in the right way; once you have the correct perspective, all of these contraints make sense. Come at it from a different perspective, however, and it's extremely difficult to understand, which is why the software vendor tried to place the burden on my end to know what has to be done.
Also, lesson learned - have your test environment duplicate or mimic ALL aspects of the production environment. It's no fun (and a lot of stress) having to experiment on the production machines with a 3rd party program without documentation! Fortunately, everyone is working on the same side for the same goal, rather than being caught up in fingerpointing exercises.
March 23, 2009 at 2:26 pm
Noel, that's true. However, snapshot replication is much more forgiving. By dropping the article (i.e., table) out of the replication, you can then modify the keys. In this case, in order to do the database maintenance - redefining the key values - expanding the field - removing the affected table from replication allowed the maintenance to work. Afterwards, adding the article back into the replication calls for a new snapshot (which makes perfect sense since we've changed the publication), which is what we get with every cycle of replication anyway.
The same can be done for TRUNCATE 😉
* Noel
March 24, 2009 at 7:30 am
Point well taken. Thanks Noel!
March 15, 2011 at 12:50 pm
This is a NEW Question about this subject.
I am dealing with a Merge-Replicated Database. I have a need to make changes to 2 Stored Procedures through an external program, however, when I do I get the following errors:
Error messages:
Incorrect syntax near 'usp_MyFireRules_Valid_Counts'. (Source: MSSQLServer, Error number: 102)
Get help: http://help/102
Must declare the scalar variable "@totalerror_cnt". (Source: MSSQLServer, Error number: 137)
Get help: http://help/137
Must declare the scalar variable "@inckey". (Source: MSSQLServer, Error number: 137)
Get help: http://help/137
Does this mean that I MUST regenerate a Snapshot anytime a Stored Procedure is CHANGED in order for the Stored Procedure to be updated through the Replication, OR is there another way to force an Updated Stored Procedure to have the changes Merge-Replicated??? Am I missing something, or am I being "too simplistic" to think that a SQL Object should be updated if it has changed???
-Mat
March 16, 2011 at 7:54 am
Mat,
You may be better served to post a new thread, especially since only NoelD and I posted on this one. Also, you may want to post the original code that the error messages are objecting to. Others have, in their signature blocks, helpful hints on how to most effectively post questions on the forum.
1. Are you using SQL Server 2005? (this IS a 2005 forum)
2. If you are 'making these changes from an external program', can we safely assume that the program is running under an ID with sufficient privileges?
3. If it's a merge replication, are you able to suspend the replication (only for the affected tables) long enough to introduce the coding change?
4. Do you have a testing environment to do this in, or is this a production environment?
and the list could go on, but there is very little information to go on based on your question.
Steve
March 16, 2011 at 12:52 pm
Steve,
Yes, I posted also a "New Thread".
Here are answers to your questions:
1. Are you using SQL Server 2005? (this IS a 2005 forum)
Yes, SQL2005 SP4
2. If you are 'making these changes from an external program', can we safely assume that the program is running under an ID with sufficient privileges?
Yes, has "sa" privileges
3. If it's a merge replication, are you able to suspend the replication (only for the affected tables) long enough to introduce the coding change?
The change is in 2 Stored Procedures, and apparently when the SPs get compiled (when the Replication is Synchronizing) is when they cause the error.
4. Do you have a testing environment to do this in, or is this a production environment?
Yes, I do have a Test Environment, I have removed those 2 SPs from the Snapshop & Replication for now, but that does not solve the problem.
and the list could go on, but there is very little information to go on based on your question.
-Mat
March 16, 2011 at 1:54 pm
I may be missing something, but what new thread? I only see two posts for your account, and they're both here.
Per your original post, you have a syntax error in your create/alter script for the stored procedure. What leads you to believe that it's a replication problem?
You should be able to include schema changes in replication, just by selecting that option in the replication set-up. But that may not help if you have a syntax error in the create script.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 16, 2011 at 6:25 pm
GSquared,
>I may be missing something, but what new thread? I only see two >posts for your account, and they're both here.
There should have been a "New Post" wit Tags of "Stored-procedure" and "replication" that started a thread; I hope it got up there...
>Per your original post, you have a syntax error in your create/alter >script for the stored procedure. What leads you to believe that it's a >replication problem?
What leads me to believe it is a "Replication Problem" is that the Stored Procedure EXECutes just fine in the Main Database (I tried it by Right Clicking on it and providing the parameter that it requires), it returned a value as it should without any errors. The only time I get these errors is when I have "Altered" the Stored Procedure and then the Replication Task fires off. We have been using this SP (with Altering it) for the last 4 years outside of Replication without any problems.
>You should be able to include schema changes in replication, just by >selecting that option in the replication set-up.
I thought so too, until now...
> But that may not help if you have a syntax error in the create script.
I do not beleive there is a "syntax error in the create script", as I said this process has been occuring without errors for 4 years - until trying to replicate the database where the Stored Procedure is being "Altered".
If you have a "Merge-Replicated" Database,
1.) create a Stored Procedure that requires a Parameter (our is supplied by a program)
2.) Generate a Snapshop & Subscribe to it
3.) Watch it Publish the Snapshot to the Subscriber
4.) Watch it update every minute (or whatever the Push Publis update time is) and it replicates without any problems
5.) Now "Alter the Stored Procedure" by adding an additional T-SQL statement
6.) Now you will notice that when the replication updates the Subscriber the Error 102 & 137 will pop up when it gets to that Stored Procedure.
At least this is what is happening on mine.
-Mat
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply