September 2015 Entries


The most likely reason is that your profile has not been configured, or you are using an incorrect name under the @profile_name parameter.  To determine your profile settings, use this query:

SELECT [profile_id]
      ,[name]  
      ,[description]
      ,[last_mod_datetime]
      ,[last_mod_user]
FROM [msdb].[dbo].[sysmail_profile] 

If after you have checked the results of this query and you are using the correct profile name, check how you are passing the parameter values.

Don't do this:
exec msdb.dbo.sp_send_dbmail @subject, @body, @profile_name, @recipients 

...  it will still fail because you need to explicitly define the parameter values ...

For example,
@subject = 'Some topic or another'

So you would either have to do this:

exec msdb.dbo.sp_send_dbmail @subject = 'Some topic or another', @body = 'read this!' ...etc.,

or

declare @subj varchar(100)
set @subj 'Some topic or another'

exec msdb.dbo.sp_send_dbmail @subject=@subj ... etc.,...






This sql script will start an agent job on a remote server.  If you're running this as a step in another agent job, keep in mind that the job you are running it from will be determined to be successful, even if the remote job fails--as this is an asynchronous kick off only.


declare @returnCode int 
declare @JobName varchar(300) 
declare @ServerName varchar(200) 
declare @query varchar(8000) 
declare @cmd varchar(8000) 

set @JobName = 'TheJobNameYouWantToRun' 
set @ServerName = 'TheRemoteServerWhereTheJobIs' 

set @query = 'exec msdb.dbo.sp_start_job @JobName = ''' + @JobName + '''' 
set @cmd = 'osql -E -S ' + @ServerName + ' -Q "' + @query + '"' 

print ' @JobName = ' +isnull(@JobName,'NULL @JobName') 
print ' @ServerName = ' +isnull(@ServerName,'NULL @ServerName') 
print ' @query = ' +isnull(@query,'NULL @query') 
print ' @cmd = ' +isnull(@cmd,'NULL @cmd') 

exec @returnCode = master.dbo.xp_cmdshell @cmd 

if @returnCode <> 0 or @returnCode is null 
begin 
print 'xp_cmdshell @returnCode = '+isnull(convert(varchar(20),@returnCode),'NULL @returnCode') 
end 
You will need to enable the feature first.

Follow these steps:

EXEC sp_configure 'show advanced options', 1
GO
-- this updates whatever the currently configured value for advanced options
RECONFIGURE
GO
-- Now enable the command shell
EXEC sp_configure 'xp_cmdshell', 1
GO
--update the currently configured value with xp_cmdshell setting update.
RECONFIGURE
GO
If you are using the SSMS GUI, you may not be aware that behind the scenes, you are really issuing an ALTER DATABSE command.  It is likely that another process (or processes) were accessing the database you want to take offline.

If you're a DBA, or have sysadmin privileges, issue an sp_who2 command -- looking for the ALTER DATABSE process logged to you.  Kill the process. 

Once the process has been killed off, issue the ALTER DATABSE command yourself.

To take the database offline:

USE master
GO

ALTER DATABASE yourDBname
SET OFFLINE WITH ROLLBACK IMMEDIATE
GO

To get the database back online:

USE master
GO

ALTER DATABASE yourDBname
SET ONLINE
GO

For added protection of data you do not wish to be online:

ALTER DATABASE yourDBname SET RESTRICTED_USER