locked
Procedure name can not be start with sp_ RRS feed

  • Question

  • Hi,

    I have setup a Transactional replication in SQL Server 2008 R2. I am using only Tables articles for the replication. Between Distributor to Subscriber i am getting beloow error:

    Procedure name can not be start with sp_ (Source: MSSQLServer, Error number: 50000)

    I again cross verified, if i am using any procedure, I am not using any procedure for replication.

    Screen Shot:

    Any help would be really appreciated.


    Sunday, September 10, 2017 4:36 PM

Answers

  • Hi Sheetal Prasad S,

     

    Based on the error message, it looks like that Distribution Agent is trying to run some Procedure name start with sp_ which has caused this problem.

     

    Could you please use SQL Server Profiler to catch the query run on the Subscriber? You can refer to this article to learn -> How To: Use SQL Profiler: https://msdn.microsoft.com/en-us/library/ff650699.aspx

     

    Best Regards,

    Teige

     


    MSDN Community Support<br/> Please remember to click &quot;Mark as Answer&quot; the responses that resolved your issue, and to click &quot;Unmark as Answer&quot; if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact <a href="mailto:MSDNFSF@microsoft.com">MSDNFSF@microsoft.com</a>.

    Monday, September 11, 2017 2:36 AM
  • Hi Shashank,

    Thanks, But i am using only tables for replication, Not a single stored procedure used for replication. Also If it is caused by some triggers, I have disabled all the triggers at Subscriber.

    Right now, replication setup is going on and there is no procedure with the sp_ . I will still look into it in details.

    Note: The replications is initiated from a Backup. I dont think this could be an issues.

    Thanks and regards,

    Sheetal.

    Monday, September 11, 2017 1:57 PM
  • I agree with Shanky, there a database level trigger wich raises the error; you can find the code for it here: http://www.c-sharpcorner.com/blogs/restrict-sp-naming-convention-using-ddl-trigger1

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Monday, September 11, 2017 2:19 PM

All replies

  • Hi Sheetal Prasad S,

     

    Based on the error message, it looks like that Distribution Agent is trying to run some Procedure name start with sp_ which has caused this problem.

     

    Could you please use SQL Server Profiler to catch the query run on the Subscriber? You can refer to this article to learn -> How To: Use SQL Profiler: https://msdn.microsoft.com/en-us/library/ff650699.aspx

     

    Best Regards,

    Teige

     


    MSDN Community Support<br/> Please remember to click &quot;Mark as Answer&quot; the responses that resolved your issue, and to click &quot;Unmark as Answer&quot; if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact <a href="mailto:MSDNFSF@microsoft.com">MSDNFSF@microsoft.com</a>.

    Monday, September 11, 2017 2:36 AM
  • Error 50000 is user based error it seems like someone was trying to create procedure with prefix sp_ and this was denied. Can you please also check this

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    • Proposed as answer by Olaf HelperMVP Tuesday, September 12, 2017 7:35 AM
    Monday, September 11, 2017 5:17 AM
  • Hi Thanks for the reply. When i did profiler, I was able to get below information. Not able to understand since object name for the root cause is not mentioned.

    exec sp_executesql N'EXECUTE msdb.dbo.sp_sqlagent_log_jobhistory @job_id = @P1, @step_id = @P2, @sql_message_id = @P3, @sql_severity = @P4, @run_status = @P5, @run_date = @P6, @run_time = @P7, @run_duration = @P8, @operator_id_emailed = @P9, @operator_id_netsent = @P10, @operator_id_paged = @P11, @retries_attempted = @P12, @session_id = @P13, @message = @P14',N'@P1 uniqueidentifier,@P2 int,@P3 int,@P4 int,@P5 int,@P6 int,@P7 int,@P8 int,@P9 int,@P10 int,@P11 int,@P12 int,@P13 int,@P14 nvarchar(4000)','7B89667A-699D-4511-A765-5F2D02BF83D8',1,0,0,4,20170911,95750,83857,0,0,0,0,4,N'
    2017-09-11 13:04:52.976 Copyright (c) 2008 Microsoft Corporation
    2017-09-11 13:04:52.976 Microsoft SQL Server Replication Agent: distrib
    2017-09-11 13:04:52.976 
    2017-09-11 13:04:52.976 The timestamps prepended to the output lines are expressed in terms of UTC time.
    2017-09-11 13:04:52.976 User-specified agent parameter values:
    -Publisher XXXXXXXXXXXXXXXXXX
    -PublisherDB XXXXXXXXX
    -Publication XXXXXXXXX_REPORTING
    -Distributor XXXXXXXXXXXXXXXXXX
    -SubscriptionType 1
    -Subscriber XXXXXXXXXXXXXXXX
    -SubscriberSecurityMode 1
    -SubscriberDB XXXXXXXXX
    -Continuous
    -XJOBID 0x7A66897B9D691145A7655F2D02BF83D8
    -XJOBNAME XXXXXXXXXXXXXXXXXX-XXXXXXXXX-XXXXXXXXX_REPORTIN-XXXXXXXXXXXXXXXX-XXXXXXXXX-A281119E-81BF-41B1-9011-DB16B67C6E3D
    -XSTEPID 1
    -XSUBSYSTEM Distribution
    -XSERVER XXXXXXXXXXXXXXXX
    -XCMDLINE 0
    -XCancelEventHandle 0000000000000578
    -XParentProcessHandle 0000000000000798
    2017-09-11 13:04:52.976 Startup Delay: 990 (msecs)
    2017-09-11 13:04:53.976 Connecting to Subscriber ''XXXXXXXXXXXXXXXX''
    2017-09-11 13:04:53.992 Connecting to Distributor ''XXXXXXXXXXXXXXXXXX''
    2017-09-11 13:04:54.023 Parameter values obtained from agent profile:
    -bcpbatchsize 2147473647
    -commitbatchsize 100
    -commitbatchthreshold 1000
    -historyverboselevel 1
    -keepalivemessageinterval 300
    -logintimeout 15
    -maxbcpthreads 1
    -maxdeliveredtransactions 0
    -pollinginterval 5000
    -querytimeout 1800
    -skiperrors 
    -transactionsperhistory 100
    2017-09-11 13:04:54.055 Initializing
    2017-09-11 13:06:46.996 Agent message code 50000. Procedure name can not be start with sp_
    2017-09-11 13:06:47.027 Category:COMMAND
    Source:  Failed Command
    Number:  
    Message: if @@trancount > 0 rollback tran
    2017-09-11 13:06:47.027 Category:NULL
    Source:  Microsoft SQL Server Native Client 10.0
    Number:  50000
    Message: Procedure name can not be start with sp_
    2017-09-11 13:06:47.027 Category:NULL
    Source:  Microsoft SQL Server Native Client 10.0
    Number:  3609
    Message: The transaction ended in the trigger. The batch has been aborted.
    '


    Thanks and regards,

    Sheetal.

    Monday, September 11, 2017 1:31 PM
  • The last part of message says transaction ended in trigger so the operation was aborted. Like I said there is trigger which rolledback the operation because, i believe, as per company policy/best practice you are not allowed to create any proc starting with sp_

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Monday, September 11, 2017 1:39 PM
  • Hi Shashank,

    Thanks, But i am using only tables for replication, Not a single stored procedure used for replication. Also If it is caused by some triggers, I have disabled all the triggers at Subscriber.

    Right now, replication setup is going on and there is no procedure with the sp_ . I will still look into it in details.

    Note: The replications is initiated from a Backup. I dont think this could be an issues.

    Thanks and regards,

    Sheetal.

    Monday, September 11, 2017 1:57 PM
  • I agree with Shanky, there a database level trigger wich raises the error; you can find the code for it here: http://www.c-sharpcorner.com/blogs/restrict-sp-naming-convention-using-ddl-trigger1

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Monday, September 11, 2017 2:19 PM
  • Someone has created a policy on the database which does not allow stored procs with the name starting with "sp_".  You either need to remove this policy, or modify it to ignore the replication user.

    Please see:

    https://www.mssqltips.com/sqlservertip/2298/enforce-sql-server-database-naming-conventions-using-policy-based-management/

    • Proposed as answer by Olaf HelperMVP Tuesday, September 12, 2017 7:35 AM
    Monday, September 11, 2017 2:40 PM
  • Hi Tom and Shashank,<o:p></o:p>

    Thanks a lot for your help! There was a Database Trigger to restrict SP_ Naming convention, I will disable it and try again.<o:p></o:p>

    Appreciate your help.<o:p></o:p>

    Regards,<o:p></o:p>

    Sheetal.<o:p></o:p>


    Monday, September 11, 2017 3:31 PM