2

I am trying to create a stored procedure and I get this error:

Msg 208, Level 16, State 6, Procedure SP_MergeStagedPoliticalPartyAgents, Line 1 [Batch Start Line 0]
Invalid object name 'SP_MergeStagedPoliticalPartyAgents'.

SQL is as follows, minus the content

CREATE OR ALTER PROCEDURE SP_MergeStagedPoliticalPartyAgents
AS
BEGIN
    -- Content removed for brevity
END

If I alter the name in anyway, i.e. adding an extra s, or removing the s at the end. It works 100% fine, so my question is what is it with this particular name that I am using that is causing it to fail?

Does SQL Server have a name validation regex that this name is violating? Is it a reserved name?

Weirder addition IMO: for the sake of testing if I go:

CREATE PROCEDURE SP_MergeStagedPoliticalPartyAgents
AS
BEGIN
    -- Content removed for brevity
END

It runs fine and creates the stored procedure on top of that from then onwards the CREATE OR ALTER PROCEDURE SP_MergeStagedPoliticalPartyAgents statement works fine.

It seems as if a stored procedure with this name doesn't exist it will fail on the CREATE OR ALTER PROCEDURE SP_MergeStagedPoliticalPartyAgents but pass on the CREATE SP_MergeStagedPoliticalPartyAgents if it is being initialized for the first time.

Note:

  • I already have other stored procedures created in the same mannerism that are fine
  • It is not a permissions issue as I am able to create stored procedures fine.
  • Running the script via VS causes the same issue so it is not related to SMSS
  • It also isn't the content. If I replace the content with a basic select * from table it still has the issue.
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Heinrich
  • 2,144
  • 3
  • 23
  • 39
  • what statements do you have before it? – Mitch Wheat Mar 19 '21 at 03:56
  • 1
    There might be some clues here: https://stackoverflow.com/questions/7870501/invalid-object-name-stored-procedure and https://stackoverflow.com/questions/26774216/invalid-object-name-sql-server-stored-procedure – zedfoxus Mar 19 '21 at 03:59
  • @MitchWheat Literally nothing. Its is the only thing in the script. Executed in its own context. – Heinrich Mar 19 '21 at 04:01
  • @zedfoxus Yea my current workaround is a lazy if exist drop then re-create. It is happy with that. – Heinrich Mar 19 '21 at 04:03
  • 1
    Side note: you should **not** use the `sp_` prefix for your stored procedures. Microsoft has [reserved that prefix for its own use (see *Naming Stored Procedures*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! – marc_s Mar 19 '21 at 05:04
  • @marc_s I did check for a potential duplicate system SP as well it did cross my mind, but not the case. Didn't know it would have a performance impact that is neat to know thanks :) – Heinrich Mar 19 '21 at 05:16

2 Answers2

5

Just after I posted the issue, I found the solution.

https://bornsql.ca/blog/remember-this-if-you-want-to-use-sp_/

Create or alter

You can quite easily create a stored procedure with the prefix, both in the master database and any user databases you have, using CREATE PROCEDURE. However what Erik shows is that if you already have a stored procedure with a name that has the sp_ prefix in the master database, and then use CREATE OR ALTER syntax to create a stored procedure in a user database with the same name, you’ll get an “Invalid object name” error in the user database:*

I found that someone had created these stored procedures in the master database also.

Sid
  • 61
  • 1
1

I've got the exact same issue. SQL 2019 (15.0.4053.23)

I have run the exact same stored procedure on another database with no issues, yet on this particular database it errors. Changing the name works, removing the ALTER and only using CREATE works. if I CREATE the procedure and then use CREATE OR ALTER, it works.

CREATE OR ALTER PROCEDURE dbo.sp_bt_annl_stmt_members_select
AS
SELECT * FROM sysusers

Msg 208, Level 16, State 6, Procedure sp_bt_annl_stmt_members_select, Line 1 [Batch Start Line 0] Invalid object name 'dbo.sp_bt_annl_stmt_members_select'.

CREATE PROCEDURE dbo.sp_bt_annl_stmt_members_select
AS
SELECT * FROM sysusers

Commands completed successfully.

Running the CREATE OR ALTER again works

CREATE OR ALTER PROCEDURE dbo.sp_bt_annl_stmt_members_select
AS
SELECT * FROM sysusers

Commands completed successfully.

Sid
  • 61
  • 1