Wednesday, October 19, 2011

Search Through Stored Procedure

How to find if particular table is being used in the stored procedure?
How to search in stored procedures?


SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%Docs%'
AND OBJECT_DEFINITION(OBJECT_ID) LIKE '%FileLoc%'
go

Monday, October 10, 2011

Using sp_send_dbmail from a SQL-Agent job gives error EXECUTE permission denied.

Executed as user: domain\user1. The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'. [SQLSTATE 42000] (Error 229). The step failed.

To solve this issue, execute the below line
ALTER DATABASE [SampleDB] SET TRUSTWORTHY ON

Ref: http://technet.microsoft.com/en-us/library/ms187861.aspx
http://blog.arjanfraaij.com/2011/06/using-spsenddbmail-from-sql-agent-job.html