Solved

Distributor To Subscriber History -  Subquery error

Posted on 2009-05-19
10
1,275 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
[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
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
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.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

751 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