Replication error

Hi,


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

Message
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....
SP_2018 .IT ConsultantAsked:
Who is Participating?
 
Eugene ZConnect With a Mentor Commented:
can you please post your replication topology? is it using sql2000?

if yes

http://www.microsoft.com/technet/support/ee/transform.aspx?ProdName=SQL%20Server&ProdVer=2000.80.760.0&EvtID=21386&EvtSrc=MSSQLServer&LCID=1033

Explanation
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.
0
 
SP_2018 .IT ConsultantAuthor 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..
0
 
Eugene ZCommented:
did you check my post?
btw: in what compatibility mode your replicated DB? 2000 or 2008?
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
SP_2018 .IT ConsultantAuthor Commented:
Compatibilty level is 100 both on publisher and subscriber end
0
 
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 ..
0
 
AnujSQL Server DBACommented:
Did you made any schema changes at the subscriber? the changes at subscriber could cause replication to fail.
0
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.