Question
Eben Raja Earnest on Thu, 17 Jan 2013 16:58:10
Hi,
We do have an daily indexreorganise task done and we have an email sent on completion to selected users.
Now for some days now we are not receiving emails from the SQL server agent job. However other maintenance plans configured on the same server are working fine, mean they send emails well
this is the code
------------------------------------------------------------------------------------
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'ABC’,
@recipients = 'CENSYSGSCAPP@xxx.com;Team@xx.com;xxxx@sss.com;xxx@sss.com;xxx@aa.com',
@subject = 'Daily index reorganization process is finished on DB Pord Server',
@body = 'Please check the output of the index rebuild process as follows: ',
@query = 'EXEC ABC.dbo.IndexOptimize @Databases = ''ABC'',
@FragmentationHigh_LOB = ''NOTHING'',
@FragmentationHigh_NonLOB = ''NOTHING'',
@FragmentationMedium_LOB = ''INDEX_REORGANIZE'',
@FragmentationMedium_NonLOB = ''INDEX_REORGANIZE'',
@FragmentationLow_LOB = ''NOTHING'',
@FragmentationLow_NonLOB = ''NOTHING'',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@PageCountLevel = 1000',
@attach_query_result_as_file = 0
--------------------------------------------------------------------------------------------------------------------------
Now the error comes as :
Already ensured that the service account domain\goodwork is present under SQL security logins with Admin previlige
Went through the below link of MSDN
https://connect.microsoft.com/SQLServer/feedback/details/361954/sp-send-dbmail-fails-when-query-parameter-and-ole-automation-is-used-in-same-batch
But am not able to find what exactly i am to do, can someone help me out.
Should i change any part of this T-SQL above. IF so can some one help me
BR
Eben
Replies
Ben Seaman on Thu, 17 Jan 2013 17:02:05
What does dbo.IndexOptimize actually return? If it's a lot of data perhaps it is causing an overflow in the max size of an email message body size. Have you tried using the @attach_query_result_as_file option set to 1?
Eben Raja Earnest on Thu, 17 Jan 2013 18:34:26
Actually this particular procedure was taken from an forum some 3 years before and the SP code is as below and the o/p will be something typical as below
Since am stumped not tomakechanges to the way the maintenance runs , except for if we can change the SP to dump data into an temp table and have that table o/p sent as notepad , something like that .
Anway am also going to check what will happen if i change@attach_query_result_as_file option set to 1
Please check the output of the index rebuild process as follows:
DateTime: 2013-01-13 16:16:00
Procedure: [ABC].[IndexOptimize]
Parameters: @Databases = 'ABC', @FragmentationHigh_LOB = 'NOTHING', @FragmentationHigh_NonLOB = 'INDEX_REBUILD_ONLINE', @FragmentationMedium_LOB = 'INDEX_REORGANIZE', @FragmentationMedium_NonLOB = 'INDEX_REORGANIZE', @FragmentationLow_LOB = 'NOTHING',
@FragmentationLow_NonLOB = 'NOTHING', @FragmentationLevel1 = 5, @FragmentationLevel2 = 30, @PageCountLevel = 1000
DateTime: 2013-01-13 16:16:00
Database: [ABC]
Status: ONLINE
Updating [dbo].[tblShipmentSplit]
[PK_tblShipmentSplit], update is not necessary...
[_WA_Sys_splSplittedID_015F0FBB], update is not necessary...
[IX_tblShipmentSplit_splShpID], update is not necessary...
[IX_tblShipmentSplit_splSplittedID], update is not necessary...
0 index(es)/statistic(s) have been updated, 4 did not require update.
this will go on for each of the table that meets the criteria.;
Below is the Stored procedure.
----------------------------------------------------------------------------------------
USE [ABC]
GO
/****** Object: StoredProcedure [dbo].[IndexOptimize] Script Date: 01/17/2013 18:11:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[IndexOptimize]
@Databases varchar(max),
@FragmentationHigh_LOB varchar(max) = 'INDEX_REBUILD_OFFLINE',
@FragmentationHigh_NonLOB varchar(max) = 'INDEX_REBUILD_OFFLINE',
@FragmentationMedium_LOB varchar(max) = 'INDEX_REORGANIZE',
@FragmentationMedium_NonLOB varchar(max) = 'INDEX_REORGANIZE',
@FragmentationLow_LOB varchar(max) = 'NOTHING',
@FragmentationLow_NonLOB varchar(max) = 'NOTHING',
@FragmentationLevel1 tinyint = 5,
@FragmentationLevel2 tinyint = 30,
@PageCountLevel int = 1000
AS
SET NOCOUNT ON
----------------------------// Declare variables //-- -----------------------------------
DECLARE @dbid INT
DECLARE @StartMessage varchar(max)
DECLARE @EndMessage varchar(max)
DECLARE @DatabaseMessage varchar(max)
DECLARE @ErrorMessage varchar(max)
DECLARE @CurrentID int
DECLARE @CurrentDatabase varchar(max)
DECLARE @CurrentCommandSelect01 varchar(max)
DECLARE @CurrentCommandSelect02 varchar(max)
DECLARE @CurrentCommandSelect03 varchar(max)
DECLARE @CurrentCommand01 varchar(max)
DECLARE @CurrentCommand02 varchar(max)
DECLARE @CurrentCommandOutput01 int
DECLARE @CurrentCommandOutput02 int
DECLARE @CurrentIxID int
DECLARE @CurrentSchemaID int
DECLARE @CurrentSchemaName varchar(max)
DECLARE @CurrentObjectID int
DECLARE @CurrentObjectName varchar(max)
DECLARE @CurrentIndexID int
DECLARE @CurrentIndexName varchar(max)
DECLARE @CurrentIndexType int
DECLARE @CurrentIndexExists bit
DECLARE @CurrentIsLOB bit
DECLARE @CurrentFragmentationLevel float
DECLARE @CurrentPageCount bigint
DECLARE @CurrentAction varchar(max)
DECLARE @CurrentComment varchar(max)
DECLARE @tmpDatabases TABLE ( ID int IDENTITY PRIMARY KEY, DatabaseName varchar(max), Completed bit)
DECLARE @tmpIndexes TABLE ( IxID int IDENTITY PRIMARY KEY, SchemaID int, SchemaName varchar(max), ObjectID int, ObjectName varchar(max), IndexID int, IndexName varchar(max), IndexType int, Completed bit)
DECLARE @tmpIndexExists TABLE ([Count] int)
DECLARE @tmpIsLOB TABLE ([Count] int)
DECLARE @Actions TABLE ([Action] varchar(max))
INSERT INTO @Actions([Action]) VALUES('INDEX_REBUILD_ONLINE')
INSERT INTO @Actions([Action]) VALUES('INDEX_REBUILD_OFFLINE')
INSERT INTO @Actions([Action]) VALUES('INDEX_REORGANIZE')
INSERT INTO @Actions([Action]) VALUES('STATISTICS_UPDATE')
INSERT INTO @Actions([Action]) VALUES('INDEX_REORGANIZE_STATISTICS_UPDATE')
INSERT INTO @Actions([Action]) VALUES('NOTHING')
DECLARE @Error int
SET @Error = 0
----------------------------// Log initial information //-- -----------------------------
SET @StartMessage = 'DateTime: ' + CONVERT(varchar,GETDATE(),120) + CHAR(13) + CHAR(10)
SET @StartMessage = @StartMessage + 'Procedure: ' + QUOTENAME(DB_NAME(DB_ID())) + '.' + QUOTENAME(OBJECT_NAME(@@PROCID)) + CHAR(13) + CHAR(10)
SET @StartMessage = @StartMessage + 'Parameters: @Databases = ' + ISNULL('''' + @Databases + '''','NULL')
SET @StartMessage = @StartMessage + ', @FragmentationHigh_LOB = ' + ISNULL('''' + @FragmentationHigh_LOB + '''','NULL')
SET @StartMessage = @StartMessage + ', @FragmentationHigh_NonLOB = ' + ISNULL('''' + @FragmentationHigh_NonLOB + '''','NULL')
SET @StartMessage = @StartMessage + ', @FragmentationMedium_LOB = ' + ISNULL('''' + @FragmentationMedium_LOB + '''','NULL')
SET @StartMessage = @StartMessage + ', @FragmentationMedium_NonLOB = ' + ISNULL('''' + @FragmentationMedium_NonLOB + '''','NULL')
SET @StartMessage = @StartMessage + ', @FragmentationLow_LOB = ' + ISNULL('''' + @FragmentationLow_LOB + '''','NULL')
SET @StartMessage = @StartMessage + ', @FragmentationLow_NonLOB = ' + ISNULL('''' + @FragmentationLow_NonLOB + '''','NULL')
SET @StartMessage = @StartMessage + ', @FragmentationLevel1 = ' + ISNULL(CAST(@FragmentationLevel1 AS varchar),'NULL')
SET @StartMessage = @StartMessage + ', @FragmentationLevel2 = ' + ISNULL(CAST(@FragmentationLevel2 AS varchar),'NULL')
SET @StartMessage = @StartMessage + ', @PageCountLevel = ' + ISNULL(CAST(@PageCountLevel AS varchar),'NULL')
SET @StartMessage = @StartMessage + CHAR(13) + CHAR(10)
RAISERROR(@StartMessage,10,1) WITH NOWAIT
---------------------------// Select databases //-- ---------------------------------------
IF @Databases IS NULL OR @Databases = ''
BEGIN
SET @ErrorMessage = 'The value for parameter @Databases is not supported.' + CHAR(13) + CHAR(10)
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
INSERT INTO @tmpDatabases (DatabaseName, Completed)
SELECT @Databases AS DatabaseName, 0 AS Completed
/******** Can be commented ***********/
FROM dbo.DatabaseSelect (@Databases)
ORDER BY DatabaseName ASC
/******** Can be commented ***********/
IF @@ERROR <> 0 OR @@ROWCOUNT = 0
BEGIN
SET @ErrorMessage = 'Error selecting databases.' + CHAR(13) + CHAR(10)
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
---------------------------// Check input parameters //-- ----------------------------------
IF @FragmentationHigh_LOB NOT IN(SELECT [Action] FROM @Actions WHERE [Action] <> 'INDEX_REBUILD_ONLINE')
BEGIN
SET @ErrorMessage = 'The value for parameter @FragmentationHigh_LOB is not supported.' + CHAR(13) + CHAR(10)
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
IF @FragmentationHigh_NonLOB NOT IN(SELECT [Action] FROM @Actions WHERE [Action] <> 'INDEX_REBUILD_ONLINE' OR SERVERPROPERTY('EngineEdition') = 3)
BEGIN
SET @ErrorMessage = 'The value for parameter @FragmentationHigh_NonLOB is not supported.' + CHAR(13) + CHAR(10)
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
IF @FragmentationMedium_LOB NOT IN(SELECT [Action] FROM @Actions WHERE [Action] <> 'INDEX_REBUILD_ONLINE')
BEGIN
SET @ErrorMessage = 'The value for parameter @FragmentationMedium_LOB is not supported.' + CHAR(13) + CHAR(10)
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
IF @FragmentationMedium_NonLOB NOT IN(SELECT [Action] FROM @Actions WHERE [Action] <> 'INDEX_REBUILD_ONLINE' OR SERVERPROPERTY('EngineEdition') = 3)
BEGIN
SET @ErrorMessage = 'The value for parameter @FragmentationMedium_NonLOB is not supported.' + CHAR(13) + CHAR(10)
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
IF @FragmentationLow_LOB NOT IN(SELECT [Action] FROM @Actions WHERE [Action] <> 'INDEX_REBUILD_ONLINE')
BEGIN
SET @ErrorMessage = 'The value for parameter @FragmentationLow_LOB is not supported.' + CHAR(13) + CHAR(10)
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
IF @FragmentationLow_NonLOB NOT IN(SELECT [Action] FROM @Actions WHERE [Action] <> 'INDEX_REBUILD_ONLINE' OR SERVERPROPERTY('EngineEdition') = 3)
BEGIN
SET @ErrorMessage = 'The value for parameter @FragmentationLow_NonLOB is not supported.' + CHAR(13) + CHAR(10)
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
IF @FragmentationLevel1 <= 0 OR @FragmentationLevel1 >= 100 OR @FragmentationLevel1 >= @FragmentationLevel2 OR @FragmentationLevel1 IS NULL
BEGIN
SET @ErrorMessage = 'The value for parameter @FragmentationLevel1 is not supported.' + CHAR(13) + CHAR(10)
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
IF @FragmentationLevel2 <= 0 OR @FragmentationLevel2 >= 100 OR @FragmentationLevel2 <= @FragmentationLevel1 OR @FragmentationLevel2 IS NULL
BEGIN
SET @ErrorMessage = 'The value for parameter @FragmentationLevel2 is not supported.' + CHAR(13) + CHAR(10)
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT SET @Error = @@ERROR
END
IF @PageCountLevel < 0 OR @PageCountLevel IS NULL
BEGIN
SET @ErrorMessage = 'The value for parameter @PageCountLevel is not supported.' + CHAR(13) + CHAR(10)
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
-----------------------------// Check error variable //-- ------------------------------------------
IF @Error <> 0 GOTO Logging
-------------------------- --// Execute commands //-- ----------------------------------------------
WHILE EXISTS (SELECT * FROM @tmpDatabases WHERE Completed = 0)
BEGIN
SELECT TOP 1 @CurrentID = ID, @CurrentDatabase = DatabaseName FROM @tmpDatabases WHERE Completed = 0 ORDER BY ID ASC
-- Set database message
SET @DatabaseMessage = 'DateTime: ' + CONVERT(varchar,GETDATE(),120) + CHAR(13) + CHAR(10)
SET @DatabaseMessage = @DatabaseMessage + 'Database: ' + QUOTENAME(@CurrentDatabase) + CHAR(13) + CHAR(10)
SET @DatabaseMessage = @DatabaseMessage + 'Status: ' + CAST(DATABASEPROPERTYEX(@CurrentDatabase,'status') AS varchar) + CHAR(13) + CHAR(10)
RAISERROR(@DatabaseMessage,10,1) WITH NOWAIT
IF DATABASEPROPERTYEX(@CurrentDatabase,'status') = 'ONLINE'
BEGIN
-- Select indexes in the current database
SET @CurrentCommandSelect01 = 'SELECT sys.schemas.[schema_id], sys.schemas.[name], sys.objects.[object_id], sys.objects.[name], sys.indexes.index_id, sys.indexes.[name], sys.indexes.[type], 0 FROM ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes
INNER JOIN ' + QUOTENAME(@CurrentDatabase) + '.sys.objects ON sys.indexes.[object_id] = sys.objects.[object_id] INNER JOIN ' + QUOTENAME(@CurrentDatabase) + '.sys.schemas ON sys.objects.[schema_id] = sys.schemas.[schema_id] WHERE sys.objects.type = ''U'' AND
sys.objects.is_ms_shipped = 0 AND sys.indexes.[type] IN(1,2) ORDER BY sys.schemas.[schema_id] ASC, sys.objects.[object_id] ASC, sys.indexes.index_id ASC'
INSERT INTO @tmpIndexes (SchemaID, SchemaName, ObjectID, ObjectName, IndexID, IndexName, IndexType, Completed)
EXECUTE(@CurrentCommandSelect01)
WHILE EXISTS (SELECT * FROM @tmpIndexes WHERE Completed = 0)
BEGIN
SELECT TOP 1 @CurrentIxID = IxID, @CurrentSchemaID = SchemaID, @CurrentSchemaName = SchemaName, @CurrentObjectID = ObjectID, @CurrentObjectName = ObjectName, @CurrentIndexID = IndexID, @CurrentIndexName = IndexName, @CurrentIndexType
= IndexType FROM @tmpIndexes WHERE Completed = 0 ORDER BY IxID ASC
-- Does the index exist?
SET @CurrentCommandSelect02 = 'SELECT COUNT(*) FROM ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes INNER JOIN ' + QUOTENAME(@CurrentDatabase) + '.sys.objects ON sys.indexes.[object_id] = sys.objects.[object_id] INNER JOIN ' +
QUOTENAME(@CurrentDatabase) + '.sys.schemas ON sys.objects.[schema_id] = sys.schemas.[schema_id] WHERE sys.objects.type = ''U'' AND sys.indexes.index_id > 0 AND sys.schemas.[schema_id] = ' + CAST(@CurrentSchemaID AS varchar) + ' AND sys.schemas.[name] =
''' + @CurrentSchemaName + ''' AND sys.objects.[object_id] = ' + CAST(@CurrentObjectID AS varchar) + ' AND sys.objects.[name] = ''' + @CurrentObjectName + ''' AND sys.indexes.index_id = ' + CAST(@CurrentIndexID AS varchar) + ' AND sys.indexes.[name] = '''
+ @CurrentIndexName + ''' AND sys.indexes.[type] = ' + CAST(@CurrentIndexType AS varchar)
INSERT INTO @tmpIndexExists ([Count]) EXECUTE(@CurrentCommandSelect02)
IF (SELECT [Count] FROM @tmpIndexExists) > 0
BEGIN
SET @CurrentIndexExists = 1
END
ELSE
BEGIN
SET @CurrentIndexExists = 0
END
IF @CurrentIndexExists = 0
GOTO NoAction
-- Does the index contain a LOB?
IF @CurrentIndexType = 1
SET @CurrentCommandSelect03 = 'SELECT COUNT(*) FROM ' + QUOTENAME(@CurrentDatabase) + '.sys.columns INNER JOIN ' + QUOTENAME(@CurrentDatabase) + '.sys.types ON sys.columns.system_type_id = sys.types.user_type_id WHERE
sys.columns.[object_id] = ' + CAST(@CurrentObjectID AS varchar) + ' AND (sys.types.name IN(''xml'',''image'',''text'',''ntext'') OR (sys.types.name IN(''varchar'',''nvarchar'',''varbinary'',''nvarbinary'') AND sys.columns.max_length = -1))'
IF @CurrentIndexType = 2
SET @CurrentCommandSelect03 = 'SELECT COUNT(*) FROM ' + QUOTENAME(@CurrentDatabase) + '.sys.index_columns INNER JOIN ' + QUOTENAME(@CurrentDatabase) + '.sys.columns ON sys.index_columns.[object_id] = sys.columns.[object_id]
AND sys.index_columns.column_id = sys.columns.column_id INNER JOIN ' + QUOTENAME(@CurrentDatabase) + '.sys.types ON sys.columns.system_type_id = sys.types.user_type_id WHERE sys.index_columns.[object_id] = ' + CAST(@CurrentObjectID AS varchar) + ' AND sys.index_columns.index_id
= ' + CAST(@CurrentIndexID AS varchar) + ' AND (sys.types.[name] IN(''xml'',''image'',''text'',''ntext'') OR (sys.types.[name] IN(''varchar'',''nvarchar'',''varbinary'',''nvarbinary'') AND sys.types.max_length = -1))'
INSERT INTO @tmpIsLOB ([Count]) EXECUTE(@CurrentCommandSelect03)
IF (SELECT [Count] FROM @tmpIsLOB) > 0
BEGIN
SET @CurrentIsLOB = 1
END
ELSE
BEGIN
SET @CurrentIsLOB = 0
END
-- Is the index fragmented?
SET @dbid = DB_ID(@CurrentDatabase);
SELECT @CurrentFragmentationLevel = avg_fragmentation_in_percent, @CurrentPageCount = page_count
FROM sys.dm_db_index_physical_stats(@dbid, @CurrentObjectID, @CurrentIndexID, NULL, 'LIMITED')
WHERE alloc_unit_type_desc = 'IN_ROW_DATA' AND index_level = 0
-- Decide action
SELECT @CurrentAction = CASE WHEN @CurrentIsLOB = 1 AND @CurrentFragmentationLevel >= @FragmentationLevel2 AND @CurrentPageCount >= @PageCountLevel THEN @FragmentationHigh_LOB
WHEN @CurrentIsLOB = 0 AND @CurrentFragmentationLevel >= @FragmentationLevel2 AND @CurrentPageCount >= @PageCountLevel THEN @FragmentationHigh_NonLOB
WHEN @CurrentIsLOB = 1 AND @CurrentFragmentationLevel >= @FragmentationLevel1 AND @CurrentFragmentationLevel < @FragmentationLevel2 AND @CurrentPageCount >= @PageCountLevel THEN @FragmentationMedium_LOB
WHEN @CurrentIsLOB = 0 AND @CurrentFragmentationLevel >= @FragmentationLevel1 AND @CurrentFragmentationLevel < @FragmentationLevel2 AND @CurrentPageCount >= @PageCountLevel THEN @FragmentationMedium_NonLOB
WHEN @CurrentIsLOB = 1 AND (@CurrentFragmentationLevel < @FragmentationLevel1 OR @CurrentPageCount < @PageCountLevel) THEN @FragmentationLow_LOB
WHEN @CurrentIsLOB = 0 AND (@CurrentFragmentationLevel < @FragmentationLevel1 OR @CurrentPageCount < @PageCountLevel) THEN @FragmentationLow_NonLOB
END
-- Create comment
SET @CurrentComment = 'IndexType: ' + CAST(@CurrentIndexType AS varchar) + ', '
SET @CurrentComment = @CurrentComment + 'LOB: ' + CAST(@CurrentIsLOB AS varchar) + ', '
SET @CurrentComment = @CurrentComment + 'PageCount: ' + CAST(@CurrentPageCount AS varchar) + ', '
SET @CurrentComment = @CurrentComment + 'Fragmentation: ' + CAST(@CurrentFragmentationLevel AS varchar)
IF @CurrentAction IN('INDEX_REBUILD_ONLINE','INDEX_REBUILD_OFFLINE','INDEX_REORGANIZE','INDEX_REORGANIZE_STATISTICS_UPDATE')
BEGIN
SELECT @CurrentCommand01 = CASE WHEN @CurrentAction = 'INDEX_REBUILD_ONLINE' THEN 'ALTER INDEX ' + QUOTENAME(@CurrentIndexName) + ' ON ' + QUOTENAME(@CurrentDatabase) + '.' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName)
+ ' REBUILD WITH (SORT_IN_TEMPDB = ON, ONLINE = ON)'
WHEN @CurrentAction = 'INDEX_REBUILD_OFFLINE' THEN 'ALTER INDEX ' + QUOTENAME(@CurrentIndexName) + ' ON ' + QUOTENAME(@CurrentDatabase) + '.' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName) +
' REBUILD WITH (SORT_IN_TEMPDB = ON, ONLINE = OFF)'
WHEN @CurrentAction IN('INDEX_REORGANIZE','INDEX_REORGANIZE_STATISTICS_UPDATE') THEN 'ALTER INDEX ' + QUOTENAME(@CurrentIndexName) + ' ON ' + QUOTENAME(@CurrentDatabase) + '.' + QUOTENAME(@CurrentSchemaName) + '.'
+ QUOTENAME(@CurrentObjectName) + ' REORGANIZE'
END
EXECUTE @CurrentCommandOutput01 = [dbo].[CommandExecute] @CurrentCommand01, @CurrentComment, 2
SET @Error = @@ERROR
IF @ERROR <> 0
BEGIN
SET @CurrentCommandOutput01 = @ERROR
END
END
IF @CurrentAction IN('INDEX_REORGANIZE_STATISTICS_UPDATE','STATISTICS_UPDATE')
BEGIN
SET @CurrentCommand02 = 'UPDATE STATISTICS ' + QUOTENAME(@CurrentDatabase) + '.' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName) + ' ' + QUOTENAME(@CurrentIndexName)
EXECUTE @CurrentCommandOutput02 = [dbo].[CommandExecute] @CurrentCommand02, @CurrentComment, 2
SET @Error = @@ERROR
IF @ERROR <> 0
BEGIN
SET @CurrentCommandOutput02 = @ERROR
END
END
NoAction: -- Update that the index is completed
UPDATE @tmpIndexes SET Completed = 1
WHERE IxID = @CurrentIxID -- Clear variables
SET @CurrentCommandSelect02 = NULL
SET @CurrentCommandSelect03 = NULL
SET @CurrentCommand01 = NULL
SET @CurrentCommand02 = NULL
SET @CurrentCommandOutput01 = NULL
SET @CurrentCommandOutput02 = NULL
SET @CurrentIxID = NULL
SET @CurrentSchemaID = NULL
SET @CurrentSchemaName = NULL
SET @CurrentObjectID = NULL
SET @CurrentObjectName = NULL
SET @CurrentIndexID = NULL
SET @CurrentIndexName = NULL
SET @CurrentIndexType = NULL
SET @CurrentIndexExists = NULL
SET @CurrentIsLOB = NULL
SET @CurrentFragmentationLevel = NULL
SET @CurrentPageCount = NULL
SET @CurrentAction = NULL
SET @CurrentComment = NULL
DELETE FROM @tmpIndexExists
DELETE FROM @tmpIsLOB
END
END
-- Update that the database is completed
UPDATE @tmpDatabases
SET Completed = 1 WHERE ID = @CurrentID
-- Clear variables
SET @CurrentID = NULL
SET @CurrentDatabase = NULL
SET @CurrentCommandSelect01 = NULL
DELETE FROM @tmpIndexes
END
EXEC sp_updatestats
--------------------------------// Log completing information //-- --------------------------------------
Logging:
SET @EndMessage = 'DateTime: ' + CONVERT(varchar,GETDATE(),120)
RAISERROR(@EndMessage,10,1) WITH NOWAIT
----------------------------------------------------------------------------------------------------
Tom Phillips on Thu, 17 Jan 2013 19:14:16
What do you get when you run this:
DECLARE @query varchar(max) SET @query = 'EXEC ABC.dbo.IndexOptimize @Databases = ''ABC'', @FragmentationHigh_LOB = ''NOTHING'', @FragmentationHigh_NonLOB = ''NOTHING'', @FragmentationMedium_LOB = ''INDEX_REORGANIZE'', @FragmentationMedium_NonLOB = ''INDEX_REORGANIZE'', @FragmentationLow_LOB = ''NOTHING'', @FragmentationLow_NonLOB = ''NOTHING'', @FragmentationLevel1 = 5, @FragmentationLevel2 = 30, @PageCountLevel = 1000' EXEC (@query)
Eben Raja Earnest on Thu, 17 Jan 2013 20:17:33
If i issue the same on query analyzer, yes it executes and i can see messages flowing, but since i don't have an downtime i cant have it run for an hour now to get the results.
Last time when i ran using the same old T-SQL that i pasted on my first post directly from my query analyzer, it did ran for 55 minutes and bumped out with error , which was difficult at least for me to find what was wrong. attaching the error
One more thing was we see this happen after we established Mirroring on this DB, however we have and staging env were mirroring is present on this same db and still reorganise works there and email comes.
Shall i go forward and change the T-SQL of the job with the query you have given meas below and see if it will function when it runs on its schedule time ?
am not good in scripting hence request to know if the below is correct.
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'ABC’,
@recipients = 'CENSYSGSCAPP@xxx.com;Team@xx.com;xxxx@sss.com;xxx@sss.com;xxx@aa.com',
@subject = 'Daily index reorganization process is finished on DB Pord Server',
@body = 'Please check the output of the index rebuild process as follows: ',
DECLARE @query varchar(max)
SET @query = 'EXEC ABC.dbo.IndexOptimize @Databases = ''ABC'',
@FragmentationHigh_LOB = ''NOTHING'',
@FragmentationHigh_NonLOB = ''NOTHING'',
@FragmentationMedium_LOB = ''INDEX_REORGANIZE'',
@FragmentationMedium_NonLOB = ''INDEX_REORGANIZE'',
@FragmentationLow_LOB = ''NOTHING'',
@FragmentationLow_NonLOB = ''NOTHING'',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@PageCountLevel = 1000'
EXEC (@query)
Eben Raja Earnest on Thu, 17 Jan 2013 20:18:16
set option to 1 doesn't work , am getting same error message,.
Ben Seaman on Fri, 18 Jan 2013 11:11:40
Can you right-click on the job in SSMS and script it and post the script?
Eben Raja Earnest on Fri, 18 Jan 2013 12:20:01
USE [msdb]
GO
/****** Object: Job [IndexReorganize_daily] Script Date: 01/18/2013 12:16:26 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 01/18/2013 12:16:26 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'IndexReorganize_daily',
@enabled=1,
@notify_level_eventlog=2,
@notify_level_email=2,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa',
@notify_email_operator_name=N'DBA', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [IndexReorganize_SendMail] Script Date: 01/18/2013 12:16:27 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'IndexReorganize_SendMail',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=4,
@on_success_step_id=2,
@on_fail_action=4,
@on_fail_step_id=2,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'EXEC msdb.dbo.sp_send_dbmail
@profile_name = ''Singh'',
@recipients = ''CENSYSGSCAPP@xyz.com;SqlDBATeam@xx.com;bo@xs.com;ws@xs.com;ash@xs.com'',
@subject = ''Daily index reorganization process is finished on DB MWF Pord Server'',
@body = ''Please check the output of the index rebuild process as follows: '',
@query = ''EXEC ABC.dbo.IndexOptimize @Databases = ''''ABC'''',
@FragmentationHigh_LOB = ''''NOTHING'''',
@FragmentationHigh_NonLOB = ''''NOTHING'''',
@FragmentationMedium_LOB = ''''INDEX_REORGANIZE'''',
@FragmentationMedium_NonLOB = ''''INDEX_REORGANIZE'''',
@FragmentationLow_LOB = ''''NOTHING'''',
@FragmentationLow_NonLOB = ''''NOTHING'''',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@PageCountLevel = 1000'',
@attach_query_result_as_file = 0',
@database_name=N'msdb',
@output_file_name=N'D:\IndexReorganizeLog\ReOrganizeLog_daily.txt',
@flags=2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [MDWSMF Reorganize] Script Date: 01/18/2013 12:16:27 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'MDWSMF Reorganize',
@step_id=2,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'EXEC msdb.dbo.sp_send_dbmail
@profile_name = ''Singh'',
@recipients = ''CENSYSGSCAPP@xyz.com;SqlDBATeam@xx.com;bo@xs.com;ws@xs.com;ash@xs.com'',
@subject = ''Daily index reorganization process is finished on DB MDWSMF Pord Server'',
@body = ''Please check the output of the index rebuild process as follows: '',
@query = ''EXEC DEFGF.dbo.IndexOptimize @Databases = ''''DEFG'''',
@FragmentationHigh_LOB = ''''NOTHING'''',
@FragmentationHigh_NonLOB = ''''NOTHING'''',
@FragmentationMedium_LOB = ''''INDEX_REORGANIZE'''',
@FragmentationMedium_NonLOB = ''''INDEX_REORGANIZE'''',
@FragmentationLow_LOB = ''''NOTHING'''',
@FragmentationLow_NonLOB = ''''NOTHING'''',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@PageCountLevel = 1000'',
@attach_query_result_as_file = 0',
@database_name=N'msdb',
@output_file_name=N'D:\IndexReorganizeLog\MDWSMF Reorganize.txt',
@flags=2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'IndexReorganize_daily',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20080721,
@active_end_date=99991231,
@active_start_time=190000,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
Eben Raja Earnest on Sun, 20 Jan 2013 10:27:43
Tried this query and got the same error
Job 'test reorganise' : Step 1, 'Step1:' : Began Executing 2013-01-18 20:15:00
Msg 22050, Sev 16, State 1: Error formatting query, probably invalid parameters [SQLSTATE 42000]
Mohammad Nizamuddin on Sun, 20 Jan 2013 15:24:43
Hi,
You can try this code, i hope this will work, to get the entire code click on link below
Script For Setting Database Mail and Sending Email From SQL Server 2008
/*Run this script after setting proper configurations*/ -- ENABLE DATABASE MAIL FEATURE IN SYSTEM CONFIGURATION use master go sp_configure 'show advanced options',1 go reconfigure with override go sp_configure 'Database Mail XPs',1 go reconfigure go -- DECLARE DATABASE MAIL ACCOUNT PARAMETERS Declare @qmail_account_name as varchar(64); Declare @qmail_account_description as varchar(64); Declare @qmail_account_email_address as varchar(64); Declare @qmail_account_display_name as varchar(64); Declare @qmail_account_username as varchar(64); Declare @qmail_account_password as varchar(64); Declare @qmail_account_mailserver_name as varchar(64); -- DECLARE PROFILE PARAMETERS declare @qmail_profile_profile_name as varchar(64); declare @qmail_profile_description as varchar(64); -- DECLARE PRINCIPLES declare @qprincipal_name as varchar(16); -- SET SMTP ACCOUNT DETAILS, USERNAME AND PASSWORD HERE set @qmail_account_mailserver_name = 'smtp.bizmail.yahoo.com' -- your smtp address set @qmail_account_username='something@something.com' -- username set @qmail_account_email_address = 'myEmailName@something.com' -- email address set @qmail_account_password='password for user (something@something.com)' -- password for username set @qmail_account_name = 'YourAccountName (e.g MyAppName_EmailAccount)' set @qmail_account_description = 'Mail account for administrative e-mail.' set @qmail_account_display_name = 'MyApplicationName Automated Mailer' -- PROFILE PARAMETERS ASSIGNMENT set @qmail_profile_profile_name = 'MyAppNameEmailServiceProfile' set @qmail_profile_description = 'Profile used for database mail jobs' --PROFILE TYPE set @qprincipal_name= 'public' if ( @qmail_account_mailserver_name = '' or @qmail_account_username='' or @qmail_account_email_address = '' or @qmail_account_password='' ) begin Select 'Please enter SMTP details' as [Comments] end else Begin if exists(select * from msdb.dbo.sysmail_account where[name]=@qmail_account_name) begin exec msdb.dbo.sysmail_delete_account_sp @account_name =@qmail_account_name end EXECUTE msdb.dbo.sysmail_add_account_sp @account_name = @qmail_account_name, @description = @qmail_account_description, @email_address = @qmail_account_email_address, @display_name = @qmail_account_display_name, @username=@qmail_account_username, @password=@qmail_account_password, @mailserver_name = @qmail_account_mailserver_name if exists(select * from msdb.dbo.sysmail_profile where[name]=@qmail_profile_profile_name) begin exec msdb.dbo.sysmail_delete_profile_sp @profile_name =@qmail_profile_profile_name end EXECUTE msdb.dbo.sysmail_add_profile_sp @profile_name = @qmail_profile_profile_name, @description = @qmail_profile_description EXECUTE msdb.dbo.sysmail_add_profileaccount_sp @profile_name = @qmail_profile_profile_name, @account_name = @qmail_account_name, @sequence_number = 1 EXECUTE msdb.dbo.sysmail_add_principalprofile_sp @profile_name = @qmail_profile_profile_name, @principal_name = @qprincipal_name, @is_default = 1 ; end Step2: Create a Sproc which will send email notification. Below is the script for that only you need to change your email To/CC, Subject, Body from your application tables or you can set anything you want. Please make sure Email profile name is proper it should be same as you have configured during Database Email Setup. if exists (select 1 from sys.objects where name = 'p_SendEmailNotification' andtype = 'p') drop procedure p_SendEmailNotification go Create Procedure [dbo].[p_SendEmailNotification] as Begin Declare @ErrorCode as int BEGIN TRANSACTION declare @MailPriority varchar(8); declare @sendAddressTo varchar (256); declare @sendAddressCC varchar(256); declare @MailBodyFormat varchar(8); declare @DBMailProfile varchar(32); set @MailBodyFormat ='HTML' set @DBMailProfile = 'Email Profile Name which you have created during DB Mail setup (e.g MyAppNameEmailServiceProfile)' set @MailPriority = 'Normal' select @sendAddressTo = 'someone@someone.com,abc@abc.com' select @sendAddressCC ='someone@someone.com,xyz@xyz.com' EXEC msdb.dbo.sp_send_dbmail @profile_name = @DBMailProfile, @recipients=@sendAddressTo, @copy_recipients = @sendAddressCC, @importance = @MailPriority, @subject = 'Email Subject', @body = 'Email Body....hi this is test email', @body_format = @MailBodyFormat ; set @ErrorCode = @@ERROR if(@ErrorCode <> 0) begin goto ERR_HANDLER end COMMIT TRANSACTION Set @ErrorCode = @@Error if @ErrorCode<>0 begin goto ERR_HANDLER end ERR_HANDLER: if @ErrorCode<>0 Begin ROLLBACK TRANSACTION End End
Eben Raja Earnest on Mon, 21 Jan 2013 05:00:02
If you can see, i had scripted out the job, there are 2 steps both entitled to send an email, and both of them using the same profile.
Now when the second step is able to run and send the email successfully, why is the first step failing after running for 60 minutes and with that error formatting query.
BR
Eben
Mohammad Nizamuddin on Mon, 21 Jan 2013 05:56:58
If you can see, i had scripted out the job, there are 2 steps both entitled to send an email, and both of them using the same profile.
Now when the second step is able to run and send the email successfully, why is the first step failing after running for 60 minutes and with that error formatting query.
BR
Eben
What I will suggest is :-
1) In your first step when it is executing the query, in the query add sql code to write messages (just for debugging purpose) at almost all steps, latter you can comment this and see where exactly it is failing.....
2) Also check msdb database for email processing tables, just query it and see whether a record is created in this the table or not