Replication error


I have a scheduled SQL ETL job which has a stored procedure ,but it keeps on failing due to below error.

Executed as user: domain/user. Schema change failed on object '[dbo].[tablename]'. Possibly due to active snapshot or other schema change activity. [SQLSTATE 42000] (Error 21386).  The step failed.

This table is also getting replicated(transactional replication) to our reporting server.

Please advise....
Sonali PDatabase Admin / Linux AdminAsked:
Who is Participating?
Eugene ZConnect With a Mentor Commented:
can you please post your replication topology? is it using sql2000?

if yes

For certain replication operations, such as adding or dropping a column, the replication process must ensure that no snapshot process is performing work on the specified publication. This message indicates that the attempt to get an application lock via sp_getapplock on the specified publication for the exclusive operation failed because either a Snapshot Agent is working on the specified publication or another schema change is being performed from another connection.
User Action
Before executing the replication process, stop all Snapshot Agents for the specified publication. Verify that no one else is currently executing sp_repladdcolumn or sp_repldropcolumn for the specified publication.
Sonali PDatabase Admin / Linux AdminAuthor Commented:
The replication is between two  servers on SQL 2008 .
The ETL logic is in the form of a stored procedure which is scheduled to run every hour..
Eugene ZCommented:
did you check my post?
btw: in what compatibility mode your replicated DB? 2000 or 2008?
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

Sonali PDatabase Admin / Linux AdminAuthor Commented:
Compatibilty level is 100 both on publisher and subscriber end
Eugene ZCommented:
ok - it is time to post the code\ changes are you doing to this table
as you should know --replicated tables have some limitations ..
AnujSQL Server DBACommented:
Did you made any schema changes at the subscriber? the changes at subscriber could cause replication to fail.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.