Trouble Shooting for “No global profile is configured”

Problem

A weekly maintenance plan failed this weekend. I checked the history of the maintenance job, and found the following error message:

01/29/2017 21:13:00, MaintenancePlan.Sundays, Error,, SQL001, MaintenancePlan.Sundays,,, The job failed.  The Job was invoked by Schedule 39 (! DBA – MaintenancePlan.Sundays).  The last step to run was step 1 (Sundays).,00:00:04,0,0,,,,0

01/29/2017 21:13:00, MaintenancePlan.Sundays, Error,1, SQL001, MaintenancePlan.Sundays, Sundays,, Executed as user: NT Service\SQLSERVERAGENT. Microsoft (R) SQL Server Execute Package Utility  Version 12.0.5000.0 for 64-bit  Copyright (C) Microsoft Corporation. All rights reserved.    Started:  9:13:00 PM  Progress: 2017-01-29 21:13:01.80     Source: {06474EBF-18F1-49F5-960B-B11B5686CA69}      Executing query “DECLARE @Guid UNIQUEIDENTIFIER      EXECUTE msdb..sp…”.: 100% complete  End Progress  Error: 2017-01-29 21:13:02.40     Code: 0xC002F210     Source: Notify Operator Task Execute SQL Task     Description: Executing the query “EXECUTE msdb.dbo.sp_notify_operator @name=N’The DB…” failed with the following error: “No global profile is configured. Specify a profile name in the @profile_name parameter.”. Possible failure reasons: Problems with the query “ResultSet” property not set correctly parameters not set correctly or connection not established correctly.  End Error  Warning: 2017-01-29 21:13:02.40     Code: 0x80019002     Source: Sundays      Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.  End Warning  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  9:13:00 PM  Finished: 9:13:02 PM  Elapsed:  2.464 seconds.  The package execution failed.  The step failed.,00:00:04,0,0,,,,0

Solution

The reason is that the database mail profile we have configured, is not the global/default any more. Change the profile to “Default” will fix the problem.

Steps:

1. Right click Database Mail, select Configure Database Mail from the right-click menu.

configure-db-mail

2. Select Next on the Database Mail Configuration Wizard window. configure-db-mail21

3. Select Manage profile security in the window.

configure-db-mail22

4. Select Yes, then Next in the Default Profile field.

configure db mail41.jpg

5. Select Finish to complete the configuration in the next window

 

Trouble Shooting for Error 7311 and Error 7412

Problem

We have an ETL job which failed recently, an alert was sent to us:

SQL Server Job System: ‘XXX-XXXXX’ completed on \\SQL001

JOB RUN:            ‘XXX-XXXXX’ was run on 1/1/2017 at 0:00:00 PM

DURATION:         0 hours, 10 minutes, 1 seconds

STATUS:               Failed

MESSAGES:         The job failed.  The Job was invoked by Schedule XX (Morning).  The last step to run was step 1 (YYYYY_YYY).

Solution

Start SSMS, expand SQL Server Agent – Jobs, right click on the job ‘XXX-XXXXX’, select “View History” from the right-click menu.

The job runs daily, 60% succeeded, 40% failed. For the failed jobs, there are this error message in the log”

“Cannot obtain the schema rowset “DBSCHEMA_TABLES_INFO” for OLE DB provider “SQLNCLI10” for linked server “SQL002”. The provider supports the interface<c/> but returns a failure code when it is used. [SQLSTATE 42000] (Error 7311)  OLE DB provider “SQLNCLI10” for linked server “SQL002” returned message “Unspecified error”. [SQLSTATE 01000] (Error 7412)  OLE DB provider “SQLNCLI10” for linked server “SQL002” returned message “Query timeout expired”. [SQLSTATE 01000] (Error 7412).  The step failed.,00:10:00,16,7412,,,,0″

Since it sometimes works properly, sometimes not, so adjusting the parameter for remote query timeout Server Configuration Option of the linked server may fix the issue. The default value is 600 (seconds). I changed it to 3000 (seconds), resolved the issue. It can be done by two methods:

Method 1 Using SQL Server Management Studio

  • In Object Explorer, right-click the linked server and select Properties.
  • Click the Connections node.

Under Remote server connections, in the Remote query timeout box, type 3000.

 

Method 2 Using T-SQL

USE Database1;

GO;

EXEC sp_configure ‘remote query timeout’, 3000 ;

GO RECONFIGURE ;

GO

 

Trouble Shooting for Error 8152 and Error 3621

Problem

We have an ETL job which failed recently, an alert was sent to us:

SQL Server Job System: ‘XXX-XXXXX’ completed on \\SQL001

JOB RUN:            ‘XXX-XXXXX’ was run on 1/1/2017 at 0:00:00 PM

DURATION:         0 hours, 3 minutes, 37 seconds

STATUS:               Failed

MESSAGES:         The job failed.  The Job was invoked by Schedule XX (Night).  The last step to run was step 1 (EXECUTE [DBXXXXX].[dbo].[SPXXX]).

Solution

Start SSMS, expand SQL Server Agent – Jobs, right click on the job ‘XXX-XXXXX’, select “View History” from the right-click menu.

This error message can be found in the log:

“START:[Table001] [SQLSTATE 01000] (Message 50000)  String or binary data would be truncated. [SQLSTATE 22001] (Error 8152)  The statement has been terminated. [SQLSTATE 01000] (Error 3621).  The step failed.,02:06:11,16,3621,,,,0”

This error is usually caused by the inserting data (VARCHAR or CHAR data type) is longer than the length of the column in the destination table. In our case, this statement in SP [DBXXXXX].[dbo].[SPXXX] caused the problem:

INSERT INTO [DBXXXXX].[dbo].[Table001]

(
[column1],[column2],[column3]…

)

(

SELECT 

[column1],[column2],[column3]…

from [SQL002].[DBYYYYY].[dbo].[Table001]

where identity_column > @V1

The length of columnA in destination table is 20, is 2 in source table. The solution is to change the length of columnA  in destination to 20. Then the job ran successfully.