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.
| 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. |
 |
|
|
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 |
|
|
|
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) |
|
|
|
sql_chaser
Starting Member
33 Posts |
Posted - 2015-03-11 : 14:01:35
|
| Thanks a lot..It worked... |
|
|
|
|
|
|
Sp prefixed Stored procedure issue