1

I have been troubleshooting a replication problem, according to the picture below:

the replication Problem

with reference to the picture above,

the bit that says Sessions of the distribution agent I can get using the following query:

--================================================================================
-- finally the contents 
-- sessions of the distribution agent:
--================================================================================
use [master]
exec [distribution]..sp_MSenum_distribution_s 
@name = N'MYSERVER-Product-Product-MYSUBSCRIPTION-184', 
@hours = -1, 
@session_type = 1

Actions in the selected session

--========================================================
-- get the actions in the selected session:
-- for each row on the above data, we can get the details:
--========================================================
-- example of the last session - without errors
use [master]
exec [distribution]..sp_MSenum_distribution_sd 
@name = N'MYSERVER-Product-Product-MYSUBSCRIPTION-184', 
@time = N'20170411 14:16:03.920'

enter image description here

-- example of the second session - with errors
use [master]
exec [distribution]..sp_MSenum_distribution_sd 
@name = N'MYSERVER-Product-Product-MYSUBSCRIPTION-184', 
@time = N'20170410 15:50:27.650'

enter image description here

Now this bit of code below gives me the errors I am looking for:

Error details or message of the solected session:

And here goes the Question too:

How can I get the number 5468694 from the result above and use it in the query below?

--========================================================================
-- Error details or message of the solected session:
--=========================================================================
    use [master]
    exec [distribution]..sp_MSenum_distribution_sd 
    @name = N'SQLAPPLON1-Product-Product-REPLON1-184', 
    @time = N'20170410 15:50:27.650'

    use [master]
    exec [distribution]..sp_MSget_repl_error 
    @id = 5468694

enter image description here

1

This isn't a verified query as I'm not running replication, but after scripting out definitions for both sp_MSenum_distribution_sd and sp_MSget_repl_error via sp_helptext and smashing together the underlying logic, I came up with the following that may give you what you're looking for.

DECLARE @name NVARCHAR(100), @time DATETIME

SET @name = N'SQLAPPLON1-Product-Product-REPLON1-184', 
    @time = N'20170410 15:50:27.650'

SELECT  msre.source_type_id, 
        msre.source_name, 
        msre.error_code,    
        msre.error_text, 
        sys.fn_replformatdatetime(msre.time) AS 'time',
        error_type_id, 
        CASE WHEN msre.xact_seqno IS NULL OR msre.xact_seqno = 0x0
            THEN CONVERT(BIT, 0) ELSE CONVERT(BIT, 1) END AS has_xact_seqno,
        msre.xact_seqno, 
        msre.command_id,
        rh.error_id
FROM    [distribution].dbo.MSrepl_errors msre 
        INNER JOIN [distribution].dbo.MSlogreader_history rh WITH (READPAST)
            ON msre.id = rh.error_id
WHERE   rh.agent_id = (SELECT TOP 1 ID 
                       FROM MSlogreader_agents 
                       WHERE name = @name)
        AND rh.start_time = @time 
        AND rh.comments not like N'<stats state%'
        AND rh.error_id <> 0
        -- rows with error_type_id are placeholders
        AND msre.error_type_id IS NOT NULL
ORDER BY 5 ASC

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.