Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.
COURSE of the MONTH
10 days, 6 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Replication error
Posted on 2013-01-04
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....
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....
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
3-
2
6 Comments
Active today
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.
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.
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..
The ETL logic is in the form of a stored procedure which is scheduled to run every hour..
Active today
did you check my post?
btw: in what compatibility mode your replicated DB? 2000 or 2008?
btw: in what compatibility mode your replicated DB? 2000 or 2008?
Active today
ok - it is time to post the code\ changes are you doing to this table
as you should know --replicated tables have some limitations ..
as you should know --replicated tables have some limitations ..
Featured Post
Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Why is this different from all of the other step by step guides? Because I make a living as a DBA and not as a writer and I lived through this experience.
Defining the name:
When I talk to people they say different names on this subject stuff l…
In this article we will learn how to fix “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses
Course of the Month10 days, 6 hours left to enroll
765 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.