1

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
  • 1
    Profile name not valid errors are generally related to issues with sending emails. Is it possible that you either have a trigger on the table, or that the job error is simply trying to send an email and that's the error that you are trying to troubleshoot? – Nic Dec 5 '16 at 6:51
  • My Opinion,To add on to @Nic,I have faced similar kind of issue with DB Mail.Please configure the DB Mail correctly and create the profile and next on to the operator.Then issue fixed.It is mainly with configuring profile and enableing it in SQL Agent. – SQLBoy Dec 5 '16 at 7:02
  • It was strange, I dont have db mail configure or any operators. I had to drop and recreate the subscription and its working fine for now. – jesijesi Dec 5 '16 at 8:55
  • it happened again, Have no idea how to troubleshoot further – jesijesi Dec 13 '16 at 3:38
  • Thrice in the last three days – jesijesi Dec 14 '16 at 2:30
3

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.

| improve this answer | |

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.