Solved

Distributor To Subscriber History -  Subquery error

Posted on 2009-05-19
10
1,235 Views
Last Modified: 2012-05-07
I have a publisher and subscription running on SQL Server 2008. The publication runs fine and is able to generate the snapshot but it is the subscriber which is creating problem. On going through Replication Monitor -> All subscription ->Distributor to Subscriber History, under the 'Actions in the selected sessions', the last few lines are

-The job failed.  The Job was invoked by Schedule 85 (Replication agent schedule.).  The last step to run was step 3 (Detect nonlogged agent shutdown.).
-Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
-Applied script 'BorREO_9.pre'

I am not sure why this error is coming. Is this related to the filter query ; All the tables are filter using the IN Clause in similar manner. As shown below: SELECT <published_columns> FROM [dbo].[BorLiabilities] WHERE [LoanId]  in (select top 30 percent loanID from [dbo].[LoanData] order by loanid desc)

but still everytime the subscriber runs after the line "Applied script 'BorREO_9.pre'", it throws the error. Anyone can guide me to the solution page or put in the solution here. I am a newbie so please detail step by step process. In case you need to any, please buzz me.

Regards,
Puneet
0
Comment
Question by:pg_india
  • 6
  • 4
10 Comments
 
LVL 26

Expert Comment

by:Chris Luttrell
Comment Utility
I saw a similar error just last week and they had one place where they had a query that did something like this
... value >= (select table1.column+table2.column from table1 inner join table2 on table1.col = table2.col)
where they expected it to always evaluate to a single value but then ran into a special case where it did not.  Can you double check that all the filters are using an IN clause and someone did not sneak another operator in?
0
 
LVL 3

Author Comment

by:pg_india
Comment Utility
Thanks for the quick response!!!

Though I checked the filters but couldn't find any of the syntax you suggested. I googled on EE and found that maybe triggers on tables can be a problem. Just wanted to double check before removing the tables or modifying the triggers - can trigger cause this error?
Does publisher does bulk insert while inserting data?

I am not able to see the exact trigger name or table name which is throwing this error? How can I see the DB OBJECT which is throwing this error...any guesses?
0
 
LVL 26

Accepted Solution

by:
Chris Luttrell earned 500 total points
Comment Utility
I think I have run accross a lot of the same articles you have that suggest it is a trigger behind a table.  Publish/Subscribe will definately fire triggers if not specifically coded "Not For Replication", see http://msdn.microsoft.com/en-us/library/ms152529.aspx.
I am not sure how to track down where your error is occuring.  Does 'BorREO_9.pre' not give you some indication of a table that is being operated on and look at it's triggers?  Hope you can find it.
0
 
LVL 3

Author Comment

by:pg_india
Comment Utility
I was able to figure out the table name which is causing this problem. How to mark the trigger as "NOT for replication"? Do I need to run the trigger in publisher or subscriber?
0
 
LVL 3

Author Comment

by:pg_india
Comment Utility
Also what about the Primark key checks. Can I disable them also during replication?
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 26

Expert Comment

by:Chris Luttrell
Comment Utility
For triggers either when creating http://msdn.microsoft.com/en-us/library/ms189799.aspx
or altering http://msdn.microsoft.com/en-us/library/ms176072.aspx
just add NOT FOR REPLICATION before the AS in your create/alter statement.

I was not sure if you meant the Identity Columns or Foriegn keys when you said Primary Key checks.  If it is Identity values then you cna read about how to handle them here http://msdn.microsoft.com/en-us/library/ms152543.aspx
Foriegn Keys can be disabled for Replication, see how here: http://msdn.microsoft.com/en-us/library/ms180843.aspx
0
 
LVL 3

Author Comment

by:pg_india
Comment Utility
I am deleting the data from the Subscriber database, but somehow the transaction log is getting full at subscriber and the subscriber is giving error : "The transaction log for database 'C' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases"

Currently I have marked the transaction log as SIMPLE but is there a way I can remove the transaction logging in snapshot replication?

About identity column, I want publisher to copy identity column in subscriber also(as it is 1 publisher and 1 subscriber only) so that I can use same value while referring to an entity in both databases? How can I keep identity columns with same value in both databases?

Regards
Puneet
0
 
LVL 26

Expert Comment

by:Chris Luttrell
Comment Utility
The common practice, if you are not doing inserts on the subscriber, is to make the key just a primary key on the subscriber side and not an Identity, that makes it easier.  I believe it describes that in the link I posted above about handling Identity values.
0
 
LVL 3

Author Comment

by:pg_india
Comment Utility
Although I could never make the replication to work but I think CGLuttrell guided me in the right direction as far as this problem goes and hence awarding the points and closing this question.

Thanks for the help. I wish I learn more on replication before trying my hands........
0
 
LVL 3

Author Closing Comment

by:pg_india
Comment Utility
Although I could never make the replication to work but I think CGLuttrell guided me in the right direction as far as this problem goes and hence awarding the points and closing this question.

Thanks for the help. I wish I learn more on replication before trying my hands........
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now