Function not returning all data.

  • 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

     

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

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

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

  • 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

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

  • This was removed by the editor as SPAM

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

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