Distributor To Subscriber History - Subquery error

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
LVL 3
pg_indiaAsked:
Who is Participating?
 
Chris LuttrellConnect With a Mentor Senior Database ArchitectCommented:
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
 
Chris LuttrellSenior Database ArchitectCommented:
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
 
pg_indiaAuthor Commented:
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
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
pg_indiaAuthor Commented:
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
 
pg_indiaAuthor Commented:
Also what about the Primark key checks. Can I disable them also during replication?
0
 
Chris LuttrellSenior Database ArchitectCommented:
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
 
pg_indiaAuthor Commented:
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
 
Chris LuttrellSenior Database ArchitectCommented:
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
 
pg_indiaAuthor Commented:
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
 
pg_indiaAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.