With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.
Course Of The Month
9 days, 5 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
This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Suggested Solutions
| Title | # Comments | Views | Activity |
|---|---|---|---|
| SQL Server Extended Events: list of deadlocks growing too long - how to cleanup? | 23 | 58 | |
| CAST issue with SQL 2000 | 15 | 41 | |
| Changing existing fields in a Stored Procedure and getting them reflected in Crystal Reports | 3 | 50 | |
| T-SQL: Wrong Result | 7 | 34 |
I have a large data set and a SSIS package. How can I load this file in multi threading?
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 ?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses
Course of the Month9 days, 5 hours left to enroll
738 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.