Solved

Distributor To Subscriber History -  Subquery error

Posted on 2009-05-19
10
1,251 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

810 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