backup job with proper error message

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[BackupProcess]    Script Date: 5/31/2018 4:34:46 AM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author: Naveen Gupta
-- Create date: 10-Nov-2015
-- Description: To Take the backup of required databases
-- =============================================

ALTER PROCEDURE [dbo].[BackupProcess]

AS

BEGIN

DECLARE @name VARCHAR(50) -- database name 
DECLARE @path VARCHAR(256) -- path for backup files 
DECLARE @fileName VARCHAR(256) -- filename for backup 
DECLARE @fileDate VARCHAR(20) -- used for file name
Declare @SQL varchar(1000)=''
declare @begintime nvarchar(100) ='05/31/2018'
declare @result varchar(max)
-- specify database backup directory
SET @path = 'D:\MSSQL\Backup\' 


-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','')


DECLARE db_cursor CURSOR FOR 
SELECT name
FROM master.dbo.sysdatabases where name in ('pOrbisNAFTA')
--WHERE name IN ('master','model','msdb','tempdb')  -- exclude these databases


OPEN db_cursor 
FETCH NEXT FROM db_cursor INTO @name 


WHILE @@FETCH_STATUS = 0 
BEGIN 
SET @fileName = @path + @name + '_' + @fileDate + '.BAK' 
select @fileName,@name
BEGIN Try
--BACKUP DATABASE @name TO DISK = @fileName  WITH NOFORMAT, NOINIT,  SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10
select @fileName,@name
BACKUP DATABASE @name TO DISK = @fileName  WITH NOFORMAT, NOINIT,  SKIP, NOREWIND, NOUNLOAD,   STATS = 10
SET @SQL = 'insert into ' + @name +'.dbo.EmailSchedule(Subject,emailto,emailcc,Body) select ''Backup'',''narenderp@damcogroup.com'',''GauravH@damcogroup.com'',''Backup of database ' + convert(varchar(100),@name) + ' has been done successfully.'''
EXEC(@sql)
END TRY
BEGIN CAtch

SELECT ERROR_MESSAGE(),ERROR_SEVERITY(), ERROR_STATE(),ERROR_PROCEDURE()
IF OBJECT_ID('tempdb.dbo.#Results') IS NOT NULL DROP TABLE #Results
CREATE TABLE #Results (LogDate datetime,ProcessInfo nvarchar(100),LogText nvarchar(4000))
INSERT #Results
EXEC  xp_readerrorlog  0, 1, N'Backup',@name,@begintime

SELECT @result = LogText from #Results where ProcessInfo = 'spid'+cast(@@SPID as varchar(6)) order by logdate desc

SET @SQL = 'insert into ' + @name +'.dbo.EmailSchedule(Subject,emailto,emailcc,Body) select ''Backup'',''narenderp@damcogroup.com'',''GauravH@damcogroup.com'',''Backup of database ' + convert(varchar(max),@result) + ' has been failed.'''
SELECT @result
EXEC(@sql)
END Catch
FETCH NEXT FROM db_cursor INTO @name 
END 
CLOSE db_cursor 
DEALLOCATE db_cursor

END

Read JSON

Compatibility must be 130 or above to run openjson command

http://jsonviewer.stack.hu/

https://stackoverflow.com/questions/37218254/sql-server-openjson-read-nested-json?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa



declare @json nvarchar(max)
set @json = '
[
   {
      "IdProject":"97A76363-095D-4FAB-940E-9ED2722DBC47",
      "Name":"Test Project",
      "structures":[
         {
            "IdStructure":"CB0466F9-662F-412B-956A-7D164B5D358F",
            "IdProject":"97A76363-095D-4FAB-940E-9ED2722DBC47",
            "Name":"Test Structure",
            "BaseStructure":"Base Structure",
            "DatabaseSchema":"dbo",
            "properties":[
               {
                  "IdProperty":"618DC40B-4D04-4BF8-B1E6-12E13DDE86F4",
                  "IdStructure":"CB0466F9-662F-412B-956A-7D164B5D358F",
                  "Name":"Test Property 2",
                  "DataType":1,
                  "Precision":0,
                  "Scale":0,
                  "IsNullable":false,
                  "ObjectName":"Test Object",
                  "DefaultType":1,
                  "DefaultValue":""
               },
               {
                  "IdProperty":"FFF433EC-0BB5-41CD-8A71-B5F09B97C5FC",
                  "IdStructure":"CB0466F9-662F-412B-956A-7D164B5D358F",
                  "Name":"Test Property 1",
                  "DataType":1,
                  "Precision":0,
                  "Scale":0,
                  "IsNullable":false,
                  "ObjectName":"Test Object",
                  "DefaultType":1,
                  "DefaultValue":""
               }
            ]
         }
      ]
   }
]';

select
    Projects.IdProject, Projects.Name as NameProject,
    Structures.IdStructure, Structures.Name as NameStructure, Structures.BaseStructure, Structures.DatabaseSchema,
    Properties.* 
from   openjson (@json)
with
(
    IdProject uniqueidentifier,
    Name nvarchar(100),
    structures nvarchar(max) as json
)
as Projects
cross apply openjson (Projects.structures)
with
(
    IdStructure uniqueidentifier,
    Name nvarchar(100),
    BaseStructure nvarchar(100),
    DatabaseSchema sysname,
    properties nvarchar(max) as json
) as Structures
cross apply openjson (Structures.properties)
with
(
    IdProperty uniqueidentifier,
    NamePreoperty nvarchar(100) '$.Name',
    DataType int,
    [Precision] int,
    [Scale] int,
    IsNullable bit,
    ObjectName nvarchar(100),
    DefaultType int,
    DefaultValue nvarchar(100)
)
as Properties
************************************


USE [JS]
GO
/****** Object:  StoredProcedure [dbo].[DashboardJSON]    Script Date: 5/10/2018 6:07:27 AM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO





-- =============================================
-- Author: Naveen Gupta
-- Create date: 07-May-2018
-- Description: To Find out the relevant values for Localization table from Dashboard JSON
-- =============================================

--EXEC DashboardJSON '{"Employees":[{"uid":"ctrl_2yvnm1g77","data":"{\"kpioverview\":{\"title\":\"KPI Overview\",\"resourceId\":\"DashNAFTA_42_90_kpioverview_ctrl_2yvnm1g77\",\"height\":\"\",\"show_gap_column\":false,\"show_traffic_light\":false,\"show_Value\":false,\"Define_Orange\":\"\",\"show_benchmark_analysis\":false,\"benchmark_analysis_columns\":\"1\",\"show_trend_graph\":false,\"show_map\":false,\"filter_top1_percent\":\"\",\"filter_top2_percent\":\"\",\"filter_bottom1_percent\":\"\",\"filter_bottom2_percent\":\"\",\"kpicolumns\":{\"col1\":\"Last\",\"col2\":\"DOECountryAvg\",\"col3\":\"DOERegionAvg\",\"col4\":\"DOEVolumeGroupAvg\"},\"kpimap\":{\"hierarchy\":\"\",\"allowedallcheck\":\"true\",\"filter\":\"alldealer\"},\"kpi\":[{\"kpitext\":\"Total CNHI Parts Inventory - em % Ativo Total\",\"id\":\"assets.total.cnhi.inventories.parts.perc.value_P\",\"order\":\"0\",\"reverse_logic\":false,\"red_value\":\"100.00\",\"orange_value\":\"100.00 to 500.00\",\"green_value\":\"500.00\",\"resourceId\":\"DashNAFTA_42_90_kpioverview_ctrl_2yvnm1g77_assets.total.cnhi.inventories.parts.perc.value_P\"}]}}"},{"uid":"ctrl_1gyawpvos","data":"{\"dealerprofile\":{\"title\":\"Dealer Profile Overview\",\"resourceId\":\"DashNAFTA_42_90_DealerProfile_ctrl_1gyawpvos\",\"height\":\"\",\"profilefields\":\"NumberofSubmissionLocations\"}}"},{"uid":"ctrl_7k68gz4pc","data":"{\"myreports\":{\"title\":\"My Reports\",\"resourceId\":\"DashNAFTA_42_90_Report_ctrl_7k68gz4pc\",\"height\":\"\",\"count\":\"100\"}}"},{"uid":"ctrl_bdxsuhnve","data":"{\"newsflash\":{\"title\":\"Newsflash Widget\",\"resourceId\":\"DashNAFTA_42_90_News_ctrl_bdxsuhnve\",\"height\":\"\",\"count\":\"10\",\"maxwords\":\"\"}}"}]}'
--EXEC DashboardJSON '[{"uid":"ctrl_2yvnm1g77","data":"{\"kpioverview\":{\"title\":\"KPI Overview\",\"resourceId\":\"DashNAFTA_42_90_kpioverview_ctrl_2yvnm1g77\",\"height\":\"\",\"show_gap_column\":false,\"show_traffic_light\":false,\"show_Value\":false,\"Define_Orange\":\"\",\"show_benchmark_analysis\":false,\"benchmark_analysis_columns\":\"1\",\"show_trend_graph\":false,\"show_map\":false,\"filter_top1_percent\":\"\",\"filter_top2_percent\":\"\",\"filter_bottom1_percent\":\"\",\"filter_bottom2_percent\":\"\",\"kpicolumns\":{\"col1\":\"Last\",\"col2\":\"DOECountryAvg\",\"col3\":\"DOERegionAvg\",\"col4\":\"DOEVolumeGroupAvg\"},\"kpimap\":{\"hierarchy\":\"\",\"allowedallcheck\":\"true\",\"filter\":\"alldealer\"},\"kpi\":[{\"kpitext\":\"Total CNHI Parts Inventory - em % Ativo Total\",\"id\":\"assets.total.cnhi.inventories.parts.perc.value_P\",\"order\":\"0\",\"reverse_logic\":false,\"red_value\":\"100.00\",\"orange_value\":\"100.00 to 500.00\",\"green_value\":\"500.00\",\"resourceId\":\"DashNAFTA_42_90_kpioverview_ctrl_2yvnm1g77_assets.total.cnhi.inventories.parts.perc.value_P\"}]}}"},{"uid":"ctrl_1gyawpvos","data":"{\"dealerprofile\":{\"title\":\"Dealer Profile Overview\",\"resourceId\":\"DashNAFTA_42_90_DealerProfile_ctrl_1gyawpvos\",\"height\":\"\",\"profilefields\":\"NumberofSubmissionLocations\"}}"},{"uid":"ctrl_7k68gz4pc","data":"{\"myreports\":{\"title\":\"My Reports\",\"resourceId\":\"DashNAFTA_42_90_Report_ctrl_7k68gz4pc\",\"height\":\"\",\"count\":\"100\"}}"},{"uid":"ctrl_bdxsuhnve","data":"{\"newsflash\":{\"title\":\"Newsflash Widget\",\"resourceId\":\"DashNAFTA_42_90_News_ctrl_bdxsuhnve\",\"height\":\"\",\"count\":\"10\",\"maxwords\":\"\"}}"}]'
--EXEC DashboardJSON '[{"uid":"ctrl_2yvnm1g77","data":"{\"kpioverview\":{\"title\":\"KPI Overview\",\"resourceId\":\"DashNAFTA_42_90_kpioverview_ctrl_2yvnm1g77\",\"height\":\"\",\"show_gap_column\":false,\"show_traffic_light\":false,\"show_Value\":false,\"Define_Orange\":\"\",\"show_benchmark_analysis\":false,\"benchmark_analysis_columns\":\"1\",\"show_trend_graph\":false,\"show_map\":false,\"filter_top1_percent\":\"\",\"filter_top2_percent\":\"\",\"filter_bottom1_percent\":\"\",\"filter_bottom2_percent\":\"\",\"kpicolumns\":{\"col1\":\"Last\",\"col2\":\"DOECountryAvg\",\"col3\":\"DOERegionAvg\",\"col4\":\"DOEVolumeGroupAvg\"},\"kpimap\":{\"hierarchy\":\"\",\"allowedallcheck\":\"true\",\"filter\":\"alldealer\"},\"kpi\":[{\"kpitext\":\"Total CNHI Parts Inventory - em % Ativo Total\",\"id\":\"assets.total.cnhi.inventories.parts.perc.value_P\",\"order\":\"0\",\"reverse_logic\":false,\"red_value\":\"100.00\",\"orange_value\":\"100.00 to 500.00\",\"green_value\":\"500.00\",\"resourceId\":\"DashNAFTA_42_90_kpioverview_ctrl_2yvnm1g77_assets.total.cnhi.inventories.parts.perc.value_P\"}]}}"},{"uid":"ctrl_1gyawpvos","data":"{\"dealerprofile\":{\"title\":\"Dealer Profile Overview\",\"resourceId\":\"DashNAFTA_42_90_DealerProfile_ctrl_1gyawpvos\",\"height\":\"\",\"profilefields\":\"NumberofSubmissionLocations\"}}"},{"uid":"ctrl_7k68gz4pc","data":"{\"myreports\":{\"title\":\"My Reports\",\"resourceId\":\"DashNAFTA_42_90_Report_ctrl_7k68gz4pc\",\"height\":\"\",\"count\":\"100\"}}"},{"uid":"ctrl_bdxsuhnve","data":"{\"newsflash\":{\"title\":\"Newsflash Widget\",\"resourceId\":\"DashNAFTA_42_90_News_ctrl_bdxsuhnve\",\"height\":\"\",\"count\":\"10\",\"maxwords\":\"\"}}"},{"uid":"ctrl_kh4v33nzo","data":"{\"kpioverview\":{\"title\":\"KPI Overview 2\",\"resourceId\":\"DashNAFTA_42_90_kpioverview_ctrl_kh4v33nzo\",\"height\":\"700\",\"show_gap_column\":true,\"show_traffic_light\":true,\"show_Value\":true,\"Define_Orange\":\"\",\"show_benchmark_analysis\":true,\"benchmark_analysis_columns\":\"1\",\"show_trend_graph\":true,\"show_map\":false,\"filter_top1_percent\":\"\",\"filter_top2_percent\":\"\",\"filter_bottom1_percent\":\"\",\"filter_bottom2_percent\":\"\",\"kpicolumns\":{\"col1\":\"Last\",\"col2\":\"DOECountryAvg\",\"col3\":\"DOERegionAvg\",\"col4\":\"DOEVolumeGroupAvg\"},\"kpimap\":{\"hierarchy\":\"\",\"allowedallcheck\":\"true\",\"filter\":\"alldealer\"},\"kpi\":[{\"kpitext\":\"% of Parts GP for Parts Person Test\",\"id\":\"turnover.ratio.parts.gp.for.parts.compensation.value_P\",\"order\":\"0\",\"reverse_logic\":false,\"red_value\":\"1000.00\",\"orange_value\":\"1,000.00 to 5,000.00\",\"green_value\":\"5000.00\",\"resourceId\":\"DashNAFTA_42_90_kpioverview_ctrl_kh4v33nzo_turnover.ratio.parts.gp.for.parts.compensation.value_P\"},{\"kpitext\":\"<59 PTO Tractors - Actual Inventory Units\",\"id\":\"ue.det.under.59.pto.tractor.inv.unit.value.850_U\",\"order\":\"0\",\"reverse_logic\":false,\"red_value\":\"100.00\",\"orange_value\":\"100.00 to 5,000.00\",\"green_value\":\"5000.00\",\"resourceId\":\"DashNAFTA_42_90_kpioverview_ctrl_kh4v33nzo_ue.det.under.59.pto.tractor.inv.unit.value.850_U\"}]}}"},{"uid":"ctrl_x2nm3g7ef","data":"{\"kpipiechart\":{\"title\":\"KPI Pie-Chart Test\",\"resourceId\":\"DashNAFTA_42_90_PieChart_ctrl_x2nm3g7ef\",\"height\":\"\",\"kpi\":\"turnover.ratio.parts.gp.for.parts.compensation.value,turnover.ratio.wholegoods.gp.for.sales.compensation.value\"}}"}]'
ALTER PROCEDURE [dbo].[DashboardJSON] @json nvarchar(max)
AS
BEGIN

Declare @JSONValue nvarchar(max)
Declare @JSONValueMain nvarchar(max)
Declare @JSONValue1 nvarchar(max)
Declare @ID nvarchar(max), @Value nvarchar(max)
Declare @Localization as Table (ID nvarchar(max), Value nvarchar(max))
SET @json = '{"MainData":' + @json + '}'

DECLARE JSONDataMain CURSOR FOR
SELECT Value FROM OPENJSON(@json,'$.MainData') --where [key]='data'
OPEN JSONDataMain
FETCH NEXT FROM JSONDataMain INTO @JSONValueMain
WHILE @@FETCH_STATUS = 0
BEGIN
------------------------
SET @JSONValueMain = '{"MainData":' + @JSONValueMain + '}'
DECLARE JSONData CURSOR FOR
SELECT Value FROM OPENJSON(@JSONValueMain,'$.MainData') where [key]='data'
OPEN JSONData
FETCH NEXT FROM JSONData INTO @JSONValue
WHILE @@FETCH_STATUS = 0
BEGIN
SET @ID = NULL
SET @Value = NULL
select  @ID = Value from  OPENJSON(@JSONValue, '$.kpioverview') Where [key]='resourceId'
select  @Value = Value from  OPENJSON(@JSONValue, '$.kpioverview') Where [key]='title'
IF @ID IS NOT NULL and @Value is not null
Insert into @Localization Select @ID, @Value
Declare @i int = 0
DECLARE JSONData1 CURSOR FOR
select Value FROM OPENJSON(@JSONValue,'$.kpioverview.kpi')
OPEN JSONData1
FETCH NEXT FROM JSONData1 INTO @JSONValue1
WHILE @@FETCH_STATUS = 0
BEGIN
Declare @sql nvarchar(max)
SET @ID = NULL
SET @Value = NULL

--set @sql = 'Select  Value from  OPENJSON(' + @JSONValue1 + ', ''$.kpioverview.kpi[' + Convert(varchar(5),@i) + '']''') Where [key]=''kpitext'''
set @sql = ' select @ID =  Value FROM OPENJSON(''' + @JSONValue + ''' ,''$.kpioverview.kpi[' + Convert(varchar(5),@i) + ']'') Where [key]=''resourceId'''
exec sp_executeSQl @sql, N'@ID nvarchar(max) output', @ID output

set @sql = replace(replace(@sql,'resourceId','kpitext'),'@ID','@Value')
exec sp_executeSQl @sql, N'@Value nvarchar(max) output', @Value output
IF @ID IS NOT NULL and @Value is not null
Insert into @Localization Select @ID, @Value
SET @i = @i + 1
FETCH NEXT FROM JSONData1 INTO @JSONValue1
END
ClOSE JSONData1
DEALLOCATE JSONData1

SET @ID = NULL
SET @Value = NULL
select  @ID = Value from  OPENJSON(@JSONValue, '$.dealerprofile') Where [key]='resourceId'
select  @Value = Value from  OPENJSON(@JSONValue, '$.dealerprofile') Where [key]='title'
IF @ID IS NOT NULL and @Value is not null
Insert into @Localization Select @ID, @Value


SET @ID = NULL
SET @Value = NULL
select  @ID = Value from  OPENJSON(@JSONValue, '$.myreports') Where [key]='resourceId'
select  @Value = Value from  OPENJSON(@JSONValue, '$.myreports') Where [key]='title'
IF @ID IS NOT NULL and @Value is not null
Insert into @Localization Select @ID, @Value

SET @ID = NULL
SET @Value = NULL
select  @ID = Value from  OPENJSON(@JSONValue, '$.newsflash') Where [key]='resourceId'
select  @Value = Value from  OPENJSON(@JSONValue, '$.newsflash') Where [key]='title'
IF @ID IS NOT NULL and @Value is not null
Insert into @Localization Select @ID, @Value

SET @ID = NULL
SET @Value = NULL
select  @ID = Value from  OPENJSON(@JSONValue, '$.kpipiechart') Where [key]='resourceId'
select  @Value = Value from  OPENJSON(@JSONValue, '$.kpipiechart') Where [key]='title'
IF @ID IS NOT NULL and @Value is not null
Insert into @Localization Select @ID, @Value








FETCH NEXT FROM JSONData INTO @JSONValue
END
ClOSE JSONData
DEALLOCATE JSONData
FETCH NEXT FROM JSONDataMain INTO @JSONValueMain
END
ClOSE JSONDataMain
DEALLOCATE JSONDataMain
Select ID ResourceID, Value From @Localization
--SELECT Value
--FROM OPENJSON(@json,'$.Employees[0]') where [key]='data'

--declare @json2 nvarchar(max)='{"kpioverview":{"title":"KPI Overview","resourceId":"DashNAFTA_42_90_kpioverview_ctrl_2yvnm1g77","height":"","show_gap_column":false,"show_traffic_light":false,"show_Value":false,"Define_Orange":"","show_benchmark_analysis":false,"benchmark_analysis_columns":"1","show_trend_graph":false,"show_map":false,"filter_top1_percent":"","filter_top2_percent":"","filter_bottom1_percent":"","filter_bottom2_percent":"","kpicolumns":{"col1":"Last","col2":"DOECountryAvg","col3":"DOERegionAvg","col4":"DOEVolumeGroupAvg"},"kpimap":{"hierarchy":"","allowedallcheck":"true","filter":"alldealer"},"kpi":[{"kpitext":"Total CNHI Parts Inventory - em % Ativo Total","id":"assets.total.cnhi.inventories.parts.perc.value_P","order":"0","reverse_logic":false,"red_value":"100.00","orange_value":"100.00 to 500.00","green_value":"500.00","resourceId":"DashNAFTA_42_90_kpioverview_ctrl_2yvnm1g77_assets.total.cnhi.inventories.parts.perc.value_P"}]}}'
--select * FROM OPENJSON('{"kpioverview":{"title":"KPI Overview","resourceId":"DashNAFTA_42_90_kpioverview_ctrl_2yvnm1g77","height":"","show_gap_column":false,"show_traffic_light":false,"show_Value":false,"Define_Orange":"","show_benchmark_analysis":false,"benchmark_analysis_columns":"1","show_trend_graph":false,"show_map":false,"filter_top1_percent":"","filter_top2_percent":"","filter_bottom1_percent":"","filter_bottom2_percent":"","kpicolumns":{"col1":"Last","col2":"DOECountryAvg","col3":"DOERegionAvg","col4":"DOEVolumeGroupAvg"},"kpimap":{"hierarchy":"","allowedallcheck":"true","filter":"alldealer"},"kpi":[{"kpitext":"Total CNHI Parts Inventory - em % Ativo Total","id":"assets.total.cnhi.inventories.parts.perc.value_P","order":"0","reverse_logic":false,"red_value":"100.00","orange_value":"100.00 to 500.00","green_value":"500.00","resourceId":"DashNAFTA_42_90_kpioverview_ctrl_2yvnm1g77_assets.total.cnhi.inventories.parts.perc.value_P"}]}}','$.kpioverview.kpi[0]')
--select Value FROM OPENJSON('{"kpitext":"Total CNHI Parts Inventory - em % Ativo Total","id":"assets.total.cnhi.inventories.parts.perc.value_P","order":"0","reverse_logic":false,"red_value":"100.00","orange_value":"100.00 to 500.00","green_value":"500.00","resourceId":"DashNAFTA_42_90_kpioverview_ctrl_2yvnm1g77_assets.total.cnhi.inventories.parts.perc.value_P"}' ,'$.kpioverview.kpi[0]') Where [key]='resourceId'






END