Function not returning all data.

Question

Function not returning all data.

David C Young

Right there with Babe

Points: 732
More actions
August 2, 2021 at 6:17 pm

#3916270

I inherited this code. Written by a 3rd party.
I have 3 servers, 1 publisher 2 subscribers. Thomson Reuters just upgrade our Prolaw app to new servers with sql 2019. I have several functions that do exactly what they should on the publisher but on the subscribers I am only getting partial data return. I run the code and it gets the correct data but when executed as a function data elements are getting dropped.
Code returns
DESS R. SMITH ) Date of Birth: 10/21/06 ) Case No. 3GG-20-00104 CN ) JUNIOR B. SMITH ) Date of Birth: 09/14/08 ) Case No. 3GG-20-00105 CN )
As Function - select dbo.f_CINACaption('ce14deca-6d29-41f3-bb83-3c460a64e684')
JUNIOR B. SMITH ) Date of Birth: 09/14/08 ) Case No. 3GG-20-00105 CN )
-------
ALTER FUNCTION [dbo].[f_CINACaption](@Events varchar(36))
RETURNS VARCHAR(1000) AS
BEGIN
DECLARE @fieldnames varchar(1000)
SELECT @fieldnames=COALESCE(@fieldnames+char(13)+char(10),'') + upper(c.fullname) + char(9) + ')' + char(13)+char(10) + 'Date of Birth: ' + coalesce(right('0'+convert(varchar,month(qdateofbirth),101),2)+'/'+right('0'+convert(varchar,day(qdateofbirth),101),2)+'/'+right('0'+convert(varchar,year(qdateofbirth),101),2),'') + char(9) + ')' + char(9) + 'Case No. ' + coalesce((select case when (select count(*) from contactsqrelationships cq, contactsqrelations2 cqq where cqq.contactsqrelationships=cq.contactsqrelationships and cq.contacts=c.contacts and QCurrentCase='Y')>1 then 'Multiple Open Cases' else min(QCourtCaseNo) end from contactsqrelationships cq, contactsqrelations2 cqq where cqq.contactsqrelationships=cq.contactsqrelationships and cq.contacts=c.contacts and QCurrentCase='Y'),'Missing Case Number') + char(13)+char(10)+char(9) + ')'
from contacts c, matterscontacts mc, eventmatters em, eventscontacts ec, documentrecipients dr
where em.events=@Events
and mc.matters=em.matters
and mc.contacts=c.contacts
and MToCClass='CINA Child'
and ec.contacts=c.contacts
and ec.events=em.events
and dr.documentrecipients=ec.eventscontacts and dr.isaddressee='Y'
order by qdateofbirth
RETURN @fieldnames
END
August 9, 2021 at 5:28 am

This was removed by the editor as SPAM

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

Transactional replication only replicates schema changes to the tables / procedures in play.

Functions do not get replicated when they change on the publisher. As such you need to go and update the code on the subscribers.

Compare the function code between pub and sub and ensure they are identical, if not alter the Subs to match.

David C Young Right there with Babe Points: 732 More actions · Answer 2

Functions are identical at the subscriptions. I have deleted them and recreated them using the create as and copied from the publisher to the subscribers.

Ant-Green SSC Guru Points: 113507 More actions · Answer 3

And you have run the function as adhoc code on the subscribers and do you get the full result set or only partial.

Thinking maybe filters on the articles so anything last a certain date or some data items do not replicate.

If the functions are identical and replication is in sync then you should get the same output.

So would work it back from the raw code, see what tables is missing the data and work it that way.

I would also look at rewriting that function, the join syntax alone is all deprecated code, they should be written as proper INNER/OUTER joins etc.

kratosrules99 SSC Veteran Points: 219 More actions · Answer 4

Ant-Green wrote:

And you have run the function as adhoc code on the subscribers and do you get the full result set or only partial.
Thinking maybe filters on the articles so anything last a certain date or cps some data items do not replicate.
If the functions are identical and replication is in sync then you should get the same output.
So would work it back from the raw code, see what tables is missing the data and work it that way.
I would also look at rewriting that function, the join syntax alone is all deprecated code, they should be written as proper INNER/OUTER joins etc.

Thanks a lot

David C Young Right there with Babe Points: 732 More actions · Answer 5

The code itself inside the function produces the correct results. It seems like the RETURN from the function is where the issue is. If I have 3 results only the middle one is returned. I converted it to a Table Value Function but the app that is receiving the data can't accept the table output. Converted the function to a Stored Procedure and that works. Just unfortunate as a I have a lot of these to convert.