Solved

Distributor To Subscriber History -  Subquery error

Posted on 2009-05-19
10
1,242 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
ID: 24427532
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
ID: 24427593
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
ID: 24427876
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
ID: 24446598
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
ID: 24446784
Also what about the Primark key checks. Can I disable them also during replication?
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 24446938
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
ID: 24478972
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
ID: 24492194
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
ID: 24593142
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
ID: 31583292
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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

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.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

911 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

24 Experts available now in Live!

Get 1:1 Help Now