An error has occurred during report processing. (rsProcessingAborted) Query execution failed for dataset 'dataset name'. (rsErrorExecutingCommand) Arithmetic overflow error converting nvarchar to data type numeric. (Microsoft SQL Server, Error: 8115)

Hi There,

User reported this error occurring while executing the SSRS report. First thing you need to do is run it from your end. If you are able to reproduce the error then open the report in Report Builder or VS to debug. Below steps show how to do it in report builder 3.0.

Click on the drop down at the right of the report name and click on Edit Report Builder.

Provide your credentials if you see this popup.

Then you will see the report to edit in Report Builder.

Expand the dataset  right click and from options select DataSet Properties. 

Click on Query Designer and execute the dataset there. Here you can find the same error message.

Error Message: 

TITLE: Microsoft SQL Server Report Builder

------------------------------

An error occurred while executing the query.

Arithmetic overflow error converting nvarchar to data type numeric.

------------------------------

ADDITIONAL INFORMATION:

Arithmetic overflow error converting nvarchar to data type numeric. (Microsoft SQL Server, Error: 8115)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.00.6251&EvtSrc=MSSQLServer&EvtID=8115&LinkId=20476

------------------------------

BUTTONS:

OK

------------------------------

Work Around: Verify the inserted/updated value that it is of correct length and data type.
If inserted/updated value are correct modify the definition of the table to accommodated new data type length.

Msg 1776, Level 16, State 0, Line 7 There are no primary or candidate keys in the referenced table '' that match the referencing column list in the foreign key ''.

Error: Msg 1776, Level 16, State 0, Line 7 There are no primary or candidate keys in the referenced table '' that match the referencing column list in the foreign key ''.

This error occurred when trying to create the FK from table2 (column1) with reference to the composite Primary Key of table1 (column1,column2).

There were two SQL tables with successful FK between, I had to update the primary key of table1(column1) by adding additional column (column2) to fix the duplicate insertion issue so I did create a composite primary key (column1, column2). Now when I try to create the same FK back I have got the error.

Cause: Trying to create FK with column1 to the PK of column1,column2.

Work Around: Add column2 in table2 and create FK with the same combination of table1.

OLE DB provider "OraOLEDB.Oracle" for linked server "TEST" returned message "ORA-01033: ORACLE initialization or shutdown in progress". (Microsoft SQL Server, Error: 7303)

TITLE: Microsoft SQL Server Management Studio
------------------------------

The test connection to the linked server failed.

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "TEST".
OLE DB provider "OraOLEDB.Oracle" for linked server "TEST" returned message "ORA-01033: ORACLE initialization or shutdown in progress". (Microsoft SQL Server, Error: 7303)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=12.00.4237&EvtSrc=MSSQLServer&EvtID=7303&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

Installed Oracle client and Oracle provider ( https://sqlserverrandoms.blogspot.com/2018/03/installing-oracle-provider-for-oledb-in.html) and still getting the above error.

Reviewed lot of blogs, verified the registry and found everything looks good. Finally a reboot of server fixed this issue and able to create the Oracle linked server.

Try this work around if you see the same issue.

Tracking long running SQL Server Agent jobs

Hi Guys,

It is very important to monitor the long running SQL jobs in production environments and it is cannot miss task of every SQL DBA.

I use below query to catch the long running jobs.

SQL job is created with this query and scheduled to run every 3hrs. which sends notifications of jobs which are running from more than 30mins. You can make it how ever is required to your environment.

USE [msdb]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[usp_LongRunningJobNotification]

     @Minutes int

AS

BEGIN

set nocount on

declare @ThresholdMinutes int

  select @ThresholdMinutes = case when isnull(@Minutes,0) < 1

                                       then 1

                                  when isnull(@Minutes,0) > 120

                                                              then 120

                                                         else @Minutes

                                                 end

if (

  select max(datediff(ss,ja.start_execution_date,getdate()))

    from msdb.dbo.sysjobactivity ja,msdb.dbo.sysjobs jm

   where 1=1

     and datediff(ss,ja.start_execution_date,getdate()) > @ThresholdMinutes*60

     and jm.job_id = ja.job_id

        and jm.category_id = 0

        and jm.enabled = 1

        and jm.name != 'BlockingHistoryCapture'

     and ja.stop_execution_date is null

     and ja.start_execution_date is not null

     and not exists(  select 'x'

                        from msdb.dbo.sysjobactivity new

                       where new.job_id = ja.job_id

                         and new.start_execution_date > ja.start_execution_date)

   ) > @ThresholdMinutes*60

begin -- If long running job

      create table ##LongRunningJobsTemp

         ( jobname nvarchar(128) not null,

           ExecutionDate varchar(19),

           MinutesExecuting varchar(8)

         )

        insert

                into ##LongRunningJobsTemp

        select rtrim(substring(jm.name,1,64)) Jobname,

               convert(varchar(10),ja.start_execution_date,101)+' '+convert(varchar(8),ja.start_execution_date,114) ExecutionDate,

               convert(varchar(8),getdate()-ja.start_execution_date,114) MinutesExecuting

          from msdb.dbo.sysjobactivity ja,msdb.dbo.sysjobs jm

         where 1=1

           and datediff(ss,ja.start_execution_date,getdate()) > @ThresholdMinutes*60

           and jm.job_id = ja.job_id

           and jm.category_id = 0

           and jm.enabled = 1

                 and jm.name != 'BlockingHistoryCapture'

           and ja.stop_execution_date is null

           and ja.start_execution_date is not null

           and not exists(  select 'x'

                              from msdb.dbo.sysjobactivity new

                             where new.job_id = ja.job_id

                              and new.start_execution_date > ja.start_execution_date)

      order by jm.name

      set nocount off

      if (select count(*) from ##LongRunningJobsTemp) > 0

         begin -- If temp table has any rows

               DECLARE @tableHTML  NVARCHAR(MAX) ;

SET @tableHTML =  '<html><head>' +

                             '<style>' +

                             'H1 {margin-left:100px;padding-left:7px;font-size:18pt;font-family:calibre;color:Black}' +

                  'th {font-family:calibre;background-color:#DB7093;border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:12pt;} ' +

                  'td {font-family:calibre;background-color:#DB7093;border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:12pt;} ' +

                  '</style>' +

                  '</head>' +

                             '<Body>'  +

    N'<H1>Long Running Job Alert!</H1>' +

    N'<table border="1">' +

    N'<th>JobName</th>' +

       N'<th>ExecutionDate</th>' +

       N'<th>MinutesExecuting</th>' +

       CAST ( ( SELECT td = lr.Jobname, '',

                                  td = Lr.ExecutionDate, '',

                    td = Lr.MinutesExecuting, ''

                                  FROM ##LongRunningJobsTemp as lr

              FOR XML PATH('tr'), TYPE

    ) AS NVARCHAR(MAX) ) +

    N'</table>' ;

declare @email_address varchar(100)

select @email_address =email_address from dbo.sysoperators where name='LongRunningJob_Notification'

                   exec msdb.dbo.sp_send_dbmail

                    @profile_name = 'JobMail',

                    @subject='ServerName: Long Running Job Alert!',

                    @recipients= @email_address,

                    @body=@tableHTML,

                    @body_format='HTML'

      drop table ##LongRunningJobsTemp

end -- If long running job

end

END

Note: Make sure you create your own operator and use it.

SQL Server (Starting with 2016): How Many Foreign Key Can You create on a Table?

A table can reference a maximum of 253 other tables and columns as foreign keys (outgoing references). 

SQL Server 2016 increases the limit for the number of other table and columns that can reference columns in a single table (incoming references), from 253 to 10,000. (Requires at least 130 compatibility level.) 

Following are the limitations:

• Greater than 253 foreign key references are supported for DELETE and UPDATE DML operations. MERGE operations are not supported.
• A table with a foreign key reference to itself is still limited to 253 foreign key references.
• Greater than 253 foreign key references are not currently available for columnstore indexes, memory-optimized tables, or Stretch Database.

References: Microsoft Books online

Installing Oracle Provider for OLEDB in SQL Server

Initially we do not see this provider in the drop down, which we open, create new linked server window.

We can find the provider in the below link, please find the appropriate version your system need.

http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html

You will see below steps during installation.

Updated the path as suggested above and clicked Install. 

That’s it, you will see Oracle Provider for OLEDB in the drop down.