Pages

Mar 27, 2018

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.


Mar 26, 2018

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.

Mar 25, 2018

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


Mar 23, 2018

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.

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.