Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Sp prefixed Stored procedure issue

Author  Topic 

sql_chaser
Starting Member

33 Posts

Posted - 2015-03-05 : 20:10:30
I'm trying to move a database from 2008 to 2014 and encountered an issue. The database restore didn't have any issue but there are stored procedures written with sp_ prefixed. Now when trying to alter the procedure it's giving error.Is there any configuration I can disable to alter these stored procedures.Thanks a lot

robvolk
Most Valuable Yak

15732 Posts

Posted - 2015-03-05 : 21:29:34
What error(s) are you getting?

Best practice is to never use sp_ as a prefix for stored procedures. It has special meaning in SQL Server and can cause problems, as you seem to be experiencing.
Go to Top of Page

sql_chaser
Starting Member

33 Posts

Posted - 2015-03-05 : 21:47:52
Yes that's true but unfortunately there were lots of stored procedures already written. I'm not able to change it as there are lots of dependencies.The error I'm getting is
MSG 208, level 16, state 1, procedure DDL_TABLE_TRIGGER
Invalid object name, msdb.dbo.DDL_TABLE_LOG
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2015-03-06 : 07:18:04
Ah, you have a user table in msdb that wasn't copied over to your new server. (also not a best practice, but heck I do it too) You can script out user tables from SSMS and run the SQL on the new server. (Right click msdb, choose "Tasks", "Generate Scripts", then follow the wizard)
Go to Top of Page

sql_chaser
Starting Member

33 Posts

Posted - 2015-03-11 : 14:01:35
Thanks a lot..It worked...
Go to Top of Page
   

- Advertisement -