XML и курс валют

CREATE PROCEDURE dm_LoadEURfromCBR
AS

declare @xmlString varchar(8000)
declare @val_1 datetime
declare @val_2 datetime
declare @url varchar(255)

select @val_2 = GetDate()
select @val_1 = GetDate()-1

select @url = ‘http://www.cbr.ru/scripts/XML_dynamic.asp?date_req1=’+
Convert(char(10), @val_1, 103)+’&date_req2=’+
Convert(char(10), @val_2, 103)+’&VAL_NM_RQ=R01239’

exec dm_LoadXMLFromFile
@url,
@xmlString output

declare @h int

exec sp_xml_preparedocument @h output, @xmlString

INSERT INTO dm_Valute (NumCode,[date],Value)
select
NumCode=978,
Convert(datetime, [Date], 103) ‘Date’,
— [Date],
— Nominal,
Convert(money, replace(Value, ‘,’, ‘.’)) ‘Value’
from
OpenXML (@h, ‘//Record’, 0)
with
(
[Date] char(10) ‘@Date’,
— Nominal int ‘./Nominal’,
Value varchar(10) ‘./Value’
)

exec sp_xml_removedocument @h
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE dm_LoadUSDfromCBR
AS

declare @xmlString varchar(8000)
declare @val_1 datetime
declare @val_2 datetime
declare @url varchar(255)

select @val_2 = GetDate()
select @val_1 = GetDate()-1

select @url = ‘http://www.cbr.ru/scripts/XML_dynamic.asp?date_req1=’+
Convert(char(10), @val_1, 103)+’&date_req2=’+
Convert(char(10), @val_2, 103)+’&VAL_NM_RQ=R01235’

exec dm_LoadXMLFromFile
@url,
@xmlString output

declare @h int

exec sp_xml_preparedocument @h output, @xmlString

INSERT INTO dm_Valute (NumCode,[date],Value)
select
NumCode=840,
Convert(datetime, [Date], 103) ‘Date’,
— [Date],
— Nominal,
Convert(money, replace(Value, ‘,’, ‘.’)) ‘Value’
from
OpenXML (@h, ‘//Record’, 0)
with
(
[Date] char(10) ‘@Date’,
— Nominal int ‘./Nominal’,
Value varchar(10) ‘./Value’
)

exec sp_xml_removedocument @h

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE dm_LoadXMLFromFile
(
@tcFileName VARCHAR(255),
@tcXMLString VARCHAR(8000) OUTPUT
) AS
BEGIN
— Scratch variables used in the script
DECLARE @retVal INT
DECLARE @oXML INT
DECLARE @errorSource VARCHAR(8000)
DECLARE @errorDescription VARCHAR(8000)
DECLARE @loadRetVal INT

— Initialize the XML document
EXEC @retVal = sp_OACreate ‘MSXML2.DOMDocument’, @oXML OUTPUT
IF (@retVal <> 0)
BEGIN
— Trap errors if any
EXEC sp_OAGetErrorInfo @oXML, @errorSource OUTPUT, @errorDescription OUTPUT
RAISERROR (@errorDescription, 16, 1)

— Release the reference to the COM object
EXEC sp_OADestroy @oXML
RETURN
END

EXEC @retVal = sp_OASetProperty @oXML, ‘async’, 0
IF @retVal <> 0
BEGIN
— Trap errors if any
EXEC sp_OAGetErrorInfo @oXML, @errorSource OUTPUT, @errorDescription OUTPUT
RAISERROR (@errorDescription, 16, 1)

— Release the reference to the COM object
EXEC sp_OADestroy @oXML
RETURN
END

— Load the XML into the document
EXEC @retVal = sp_OAMethod @oXML, ‘load’, @loadRetVal OUTPUT, @tcFileName
IF (@retVal <> 0)
BEGIN
— Trap errors if any
EXEC sp_OAGetErrorInfo @oXML, @errorSource OUTPUT, @errorDescription OUTPUT
RAISERROR (@errorDescription, 16, 1)

— Release the reference to the COM object
EXEC sp_OADestroy @oXML
RETURN
END

— Get the loaded XML
EXEC @retVal = sp_OAMethod @oXML, ‘xml’, @tcXMLString OUTPUT
IF (@retVal <> 0)
BEGIN
— Trap errors if any
EXEC sp_OAGetErrorInfo @oXML, @errorSource OUTPUT, @errorDescription OUTPUT
RAISERROR (@errorDescription, 16, 1)

— Release the reference to the COM object
EXEC sp_OADestroy @oXML
RETURN
END

— Release the reference to the COM object
EXEC sp_OADestroy @oXML

END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

BEGIN TRANSACTION
DECLARE @JobID BINARY(16)
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]’) < 1
EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]’

— Delete the job with the same name (if it exists)
SELECT @JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N’LoadCurRateFromCBR’)
IF (@JobID IS NOT NULL)
BEGIN
— Check if the job is a multi-server job
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @JobID) AND (server_id <> 0)))
BEGIN
— There is, so abort the script
RAISERROR (N’Unable to import job »LoadCurRateFromCBR» since there is already a multi-server job with this name.’, 16, 1)
GOTO QuitWithRollback
END
ELSE
— Delete the [local] job
EXECUTE msdb.dbo.sp_delete_job @job_name = N’LoadCurRateFromCBR’
SELECT @JobID = NULL
END

BEGIN

— Add the job
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N’LoadCurRateFromCBR’, @owner_login_name = N’SWEDWOOD\dmitry.malchikov’, @description = N’Load Currency Rate from www.cbr.ru Using stored procedures: dm_LoadEURfromCBR, dm_LoadUSDfromCBR, dm_LoadXMLfromFile’, @category_name = N'[Uncategorized (Local)]’, @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

— Add the job steps
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N’Load USD Rate’, @command = N’exec dm_LoadUSDfromCBR’, @database_name = N’ScaSystemDB’, @server = N», @database_user_name = N», @subsystem = N’TSQL’, @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N», @on_success_step_id = 0, @on_success_action = 3, @on_fail_step_id = 0, @on_fail_action = 3
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 2, @step_name = N’Load EUR Rate’, @command = N’exec dm_LoadEURfromCBR’, @database_name = N’ScaSystemDB’, @server = N», @database_user_name = N», @subsystem = N’TSQL’, @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N», @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

— Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

set @MaxDateDM = (SELECT max([date])
FROM ScaSystemDB.dbo.dm_Valute WHERE NumCode=978)
set @Curs = (SELECT Value
FROM ScaSystemDB.dbo.dm_Valute WHERE NumCode=978 AND [date] = @MaxDateDM)

IF @MaxDateDM>@MaxDateSY —Check if SYCH has Rate no rate at this date
BEGIN TRANSACTION upd_euro
INSERT INTO swedwood.dbo.SYCH9900
VALUES (1, -1, -1, @MaxDateDM, ‘9999-12-31 00:00:00.000’, @Curs, @Curs, 2, 1, ‘/’,99305961 — CAST(@MaxDateDM AS bigint),0)
UPDATE swedwood.dbo.SYCH9900
SET SYCH005 = @MaxDateDM WHERE SYCH001=1 and SYCH004 = @MaxDateSY
DELETE FROM ScaSystemDB.dbo.dm_Valute WHERE NumCode=978 and [date]=@MaxDateDM
GO
COMMIT TRANSACTION upd_euro

DECLARE @MaxDateSY datetime, @MaxDateDM datetime, @Curs real
set @MaxDateSY = (SELECT max(SYCH004)
FROM swedwood.dbo.SYCH9900 WHERE SYCH001=1)

set @MaxDateDM = (SELECT max([date])
FROM ScaSystemDB.dbo.dm_Valute WHERE NumCode=978)
set @Curs = (SELECT Value
FROM ScaSystemDB.dbo.dm_Valute WHERE NumCode=978 AND [date] = @MaxDateDM)

SELECT *
—FROM ScaSystemDB.dbo.dm_Valute
—WHERE NumCode=978 and [date]=@MaxDateDM
FROM swedwood.dbo.SYCH9900
WHERE SYCH001=1
ORDER BY SYCH004 DESC

insert into SYCH9900
SELECT * FROM SYCH0100
WHERE SYCH001=1 and SYCH004=’2005-03-24 00:00:00.000′

delete from SYCH9900

SELECT *
from ScaSystemDB.dbo.dm_Valute
where [date]<getdate()-2