August 9, 2011 at 8:29 am
Help,
I'm experiencing a weird replication issue that I've never seen before. Here's the environment:
The Publisher is a SQL 2005 server. The publication is created without any issues or warning messages. The Distributor and Subscriber is a SQL 2008 server. In fact, I already have several databases replicating from the Publisher to this Subscriber. All is working fine, except for the latest publication I have created. When I create the Pull subscription on the Subscriber the Distribution agent fires off a warning email every minutes which says "Invalid column name: Reason". One of the tables in the new publication has a column named Reason. As far as I know this is not a reserved word. In fact, if I script the table creation and run it on the Subscriber database and the table is created without issues.
What is going on here? The only way to stop the error emails is to remove the new subscription to the new publication. Help!
August 9, 2011 at 12:46 pm
This happens only in this publication? Do you have a user defined data type in this table? I do not think it is the column name that is the problem but it might be the column type.
Just a thought.
-Roy
August 9, 2011 at 1:03 pm
Roy Ernest (8/9/2011)
This happens only in this publication? Do you have a user defined data type in this table? I do not think it is the column name that is the problem but it might be the column type.Just a thought.
Yes, only in this publication. The column is an nvarchar(250) nulls allowed and no default value. It looks like a totally normal column. I don't have any issues creating the publication, but when I create the pull subscription is when the errors begin and they continue every minute until I delete the subscription.
August 9, 2011 at 1:40 pm
Can you try running this query?
select
name
from
sys.columns
where
object_id = object_id('Tablename')
-Roy
August 9, 2011 at 2:04 pm
orderApplicationReasonId
orderNumber
Reason
August 9, 2011 at 2:30 pm
Now I am beat. Sorry. Dont have any clue why it should throw that error. Maybe someone else can help you. As far as I know, REASON is not keyword.
-Roy
August 9, 2011 at 2:38 pm
Is it possible to test it by renaming the column to reason2 and try adding it?
-Roy
August 9, 2011 at 2:43 pm
Roy Ernest (8/9/2011)
Is it possible to test it by renaming the column to reason2 and try adding it?
Unfortunately not, its a production table.
August 9, 2011 at 2:52 pm
Since it happens every minute it sounds like it's the actual pull process that is failing. Have you looked at the relevant stored procs to make sure they have created correctly? The fact that the publication, subscription and I assume the snapshot are being created without an issue points to the stored proc insert code.
I had an odd incedent where two successive stored procs had different table names, but for some reason they had the same column names. I was getting the same error as the columns didn't exist in the destination table. I was never able to prove it was a bug or that someone didn't modify the stored proc. But knowing the environment I couldn't see anyone who would have the know how to do modify the code.
I would check all stored procs that have the column "Reason" in their code.
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
August 9, 2011 at 2:56 pm
On that note make sure that it's 'reason' and not "reason" in the SP's
SET QUOTED_IDENTIFIER ON could make that blow up.
It might be worth it to set up profiler and see what the replication agent is trying to do with the column "Reason"
August 9, 2011 at 3:48 pm
What is the @pre_creation_cmd for this article? Is it "drop"? If not, can it be changed to that? Do you have option to initialize from a backup?
August 16, 2011 at 9:22 am
I'm assuming that the source of the problem is becuase the Publisher is SQL2K5 and the Distributor and Subscriber are SQL2K8. To get around the problem we're just going to move the subscriber to a SQL2K5 servers instead.
August 16, 2011 at 10:22 am
Mick Opalak (8/16/2011)
I'm assuming that the source of the problem is becuase the Publisher is SQL2K5 and the Distributor and Subscriber are SQL2K8. To get around the problem we're just going to move the subscriber to a SQL2K5 servers instead.
I don't think so. From the BOL article titled "Using Multiple Versions of SQL Server in a Replication Topolog", there's a line that says "For all types of replication, the Distributor version must be no earlier than the Publisher version. (Frequently, the Distributor is the same instance as the Publisher.)" which I read as the the distributor's version must be at least the publisher's version. The article obviously goes into more depth than that, but having a mixed version topology is certainly valid.
November 25, 2015 at 8:28 am
I am not sure if this is the exact same issue, but I was getting the same error. In my organization, every time there is a schema change we deploy it on the production system by first bringing down P2P Transactional replication, then patching the main location, then pushing a backup of that DB to the other nodes, and adding them back into the P2P topology. Don't ask why - I've already fought that fight. Any time a new column was added to an existing table, we got the "Invalid Column Name" error. This was occurring even after replication was completely brought down, Distribution disabled, Distribution database dropped, and SP_Removedbreplication was run on the database. Apparently it was holding onto the old list of replicating columns, and not adding the new columns. The workaround I found was to rebuild the publication, with all the articles needed, then go into the Publication and drop all the articles, then, finally, go back in and re-add the articles. I am not sure where it was keeping the old column list, since the sysarticlecolumns didn't even exist on the database when I was patching the schema, but this method seems to work.
November 27, 2015 at 3:04 pm
siugoalie78 (11/25/2015)
I am not sure if this is the exact same issue, but I was getting the same error. In my organization, every time there is a schema change we deploy it on the production system by first bringing down P2P Transactional replication, then patching the main location, then pushing a backup of that DB to the other nodes, and adding them back into the P2P topology. Don't ask why - I've already fought that fight. Any time a new column was added to an existing table, we got the "Invalid Column Name" error. This was occurring even after replication was completely brought down, Distribution disabled, Distribution database dropped, and SP_Removedbreplication was run on the database. Apparently it was holding onto the old list of replicating columns, and not adding the new columns. The workaround I found was to rebuild the publication, with all the articles needed, then go into the Publication and drop all the articles, then, finally, go back in and re-add the articles. I am not sure where it was keeping the old column list, since the sysarticlecolumns didn't even exist on the database when I was patching the schema, but this method seems to work.
Reminds me of trying to transfer logins from one machine to another. The engine behind the scenes is looking at some identifyer and not the name of the object. Perhaps this is the same with replication, you need to build a new article to represent the newer version of that table/entity.
----------------------------------------------------
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply