System Stored Procedure Missing

Question

I'm beginning in SQL Server dev. I'm working with 2 servers, one has a System Stored Procedure called "sp_send_dbmail" from the dbo schema on the System Database msdb but the second hasn't it.

When something is missing on one of theses servers, I just right click on a function/table/view then I click on "Script as ... CREATE", then I right click on the script and do Connections > Change Connections. I connect on the other server then I execute the create script to create the missing element from the first server to the second one.

But here I want to copy a System Stored Procedure, but I can't do "Script as ... CREATE" on a System Stored Procedure...

How can I do the trick ?

Thanks for your attention.

score 2 · Answer 1 · answered May 26 '16 at 08:59

2

use msdb
go
exec  sp_helptext sp_send_dbmail

use this statement in sql to get text of stored procedure

answered May 26 '16 at 08:59

Akshey Bhat

8,227
1
20
20

Thanks ! I did "Use msdb exec sp_helptext sp_send_dbmail" and i get the following error : "There is no text for object 'sp_send_dbmail'." – M. Ozn May 26 '16 at 09:03
its working on my local system. which version of SQL server are you using? – Akshey Bhat May 26 '16 at 09:05
I'm using the Microsoft SQL Server 2008 R2, with the Microsoft SQL Server Management Studio version 10.50.1600.1 – M. Ozn May 26 '16 at 09:07
I also have the same version. It might be a permissions issue. Are you logged in with db admin user? – Akshey Bhat May 26 '16 at 09:08
No i think not, all the db_* roles are uncheck on my login properties – M. Ozn May 26 '16 at 09:11
Ask your database administrator to give you permissions to view defintions of stored procs – Akshey Bhat May 26 '16 at 09:17

score 1 · Accepted Answer · answered May 26 '16 at 10:19

1

There is a system stored procedure called sp_send_dbmail that is installed when you configure Database Mail. It's possible that Database Mail has only been configured on one of the two servers you are working with.

Copying the sp_send_dbmail stored procedure to the other server will not enable Database Mail. You will need to follow the steps to enable Database Mail.

answered May 26 '16 at 10:19

Ed Harper

21,127
4
54
80

Thanks for your answer ! i sent it to my administrator – M. Ozn May 26 '16 at 11:48

System Stored Procedure Missing

2 Answers2