Sql-server – SQL Server replication error “profile name is not valid”

sql servertransactional-replication

Today I started getting the below error message on one of the distribution agent. The publication has one push and on pull subscription. The error appeared on the push subscription only. The publication server has 5 other publication each having pull and push subscription to the same servers and all of them works fine.

I tried changing the distributor agent profile and restarted the distribution job but didn't work. 

Message
2016-12-05 00:41:45.429 Copyright (c) 2014 Microsoft Corporation
2016-12-05 00:41:45.429 Microsoft SQL Server Replication Agent: distrib
2016-12-05 00:41:45.429 
2016-12-05 00:41:45.429 The timestamps prepended to the output lines are expressed in terms of UTC time.
2016-12-05 00:41:45.429 User-specified agent parameter values:
            -Subscriber SubscriberServer
            -SubscriberDB DBName
            -Publisher Publisher1
            -Distributor DistributerServer
            -DistributorSecurityMode 1
            -Publication DBName_HA_Pub
            -PublisherDB DBName
            -Continuous
            -XJOBID 0x0D2A9EB2B6FBDF4CAE11F0A700401787
            -XJOBNAME Publisher_instance_-DBName-DBName_HA_Pub-SubscriberServer-48
            -XSTEPID 2
            -XSUBSYSTEM Distribution
            -XSERVER DistributerServer
            -XCMDLINE 0
            -XCancelEventHandle 0000000000006888
            -XParentProcessHandle 00000000000083E8
2016-12-05 00:41:45.429 Startup Delay: 668 (msecs)
2016-12-05 00:41:46.101 Connecting to Distributor 'DistributerServer'
2016-12-05 00:41:46.179 Parameter values obtained from agent profile:
            -bcpbatchsize 2147473647
            -commitbatchsize 100
            -commitbatchthreshold 1000
            -historyverboselevel 2
            -keepalivemessageinterval 300
            -logintimeout 15
            -maxbcpthreads 1
            -maxdeliveredtransactions 0
            -pollinginterval 5000
            -querytimeout 1800
            -skiperrors 
            -transactionsperhistory 100
2016-12-05 00:41:46.179 Connecting to Subscriber 'SubscriberServer'
2016-12-05 00:41:46.226 Initializing
2016-12-05 00:41:46.288 
42000 profile name is not valid 14607
2016-12-05 00:41:46.288 
42000 profile name is not valid 14607
2016-12-05 00:41:46.288 
42000 profile name is not valid 14607
2016-12-05 00:41:46.304 Disconnecting from Subscriber 'SubscriberServer'
2016-12-05 00:41:46.304 Connecting to Subscriber 'SubscriberServer'
2016-12-05 00:41:46.304 Error executing a batch of commands. Retrying individual commands.
2016-12-05 00:41:51.351 Disconnecting from Subscriber 'SubscriberServer'
2016-12-05 00:41:51.351 Connecting to Subscriber 'SubscriberServer'
2016-12-05 00:41:51.367 Error executing a batch of commands. Retrying individual commands.
2016-12-05 00:42:01.390 Disconnecting from Subscriber 'SubscriberServer'
2016-12-05 00:42:01.390 Connecting to Subscriber 'SubscriberServer'
2016-12-05 00:42:01.406 Error executing a batch of commands. Retrying individual commands.
2016-12-05 00:42:16.423 Disconnecting from Subscriber 'SubscriberServer'
2016-12-05 00:42:16.423 Connecting to Subscriber 'SubscriberServer'
2016-12-05 00:42:16.454 Error executing a batch of commands. Retrying individual commands.
2016-12-05 00:42:36.474 Disconnecting from Subscriber 'SubscriberServer'
2016-12-05 00:42:36.474 Connecting to Subscriber 'SubscriberServer'
2016-12-05 00:42:36.505 Error executing a batch of commands. Retrying individual commands.
2016-12-05 00:43:01.524 Disconnecting from Subscriber 'SubscriberServer'
2016-12-05 00:43:01.524 Connecting to Subscriber 'SubscriberServer'
2016-12-05 00:43:01.540 Error executing a batch of commands. Retrying individual commands.
2016-12-05 00:43:01.556 Agent message code 14607. profile name is not valid
2016-12-05 00:43:01.571 Category:COMMAND
Source:  Failed Command
Number:  
Message: if @@trancount > 0 rollback tran
2016-12-05 00:43:01.571 Category:NULL
Source:  Microsoft SQL Server Native DBName 11.0
Number:  14607
Message: profile name is not valid

Best Answer

It seems that you initialized your transactional replication using database backup. With database backup, there is no schema difference between the publisher and subscriber.

There might be trigger exists in your publisher and restored in your subscriber, trying to send email using invalid profile name from your subscriber.

Related Question