Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Курсы валют, T-SQL, Web Service, Console Application...  [new]
tsqler
Guest
Понадобился сабж.
Нашел у центробанка Web Service:
http://www.cbr.ru/DailyInfoWebServ/DailyInfo.asmx

Задача - забирать данные курсов и добавлять их в табличку на сервере.

Сначала решил сделать CLR-stored procedure.
Написал код сначала в консольном приложении, проверил, что работает, перенес в Sql Server Project, Stored Procedure. Сделал Deploy, все ок.

При запуске (execute GetKURS) получаю:
Unhandled Exception: System.Net.WebException: Unable to connect to the remote server ---> System.Net.Sockets.SocketException: Попытка установить соединение была безуспешной, т.к. от другого компьютера за требуемое время не получен нужный отклик...

Причем все это и на локальной машине, и на удаленном сервере. И там, и там я сисадмин. И там, и там включал на БД TRUSTWORTHY. UNSAFE в проекте поставил.
В гугле нашел много подобных проблем, но ни одного решения.

Ладно, думаю... Что время терять. Сделаю просто Console Application и буду вызывать ее, через xp_cmdshell.

Проверяю консольное приложение, захожу cmd->C:\CBRRates.exe, все отлично отрабатывает.
Выполняю на локальном SQL Server запрос:
exec xp_cmdshell 'C:\CBRRates.exe'

Получаю ту же(!) ошибку, что и ранее с CLR-процедурой, только дробленную на строки.
Unhandled Exception: System.Net.WebException: Unable to connect to the remote server ---> System.Net.Sockets.SocketException: Попытка установить соединение была безуспешной, т.к. от другого компьютера за требуемое время не получен нужный отклик, или было 
разорвано уже установленное соединение из-за неверного отклика уже подключенного компьютера
   at System.Net.Sockets.Socket.DoConnect(EndPoint endPointSnapshot, SocketAddress socketAddress)
   at System.Net.Sockets.Socket.InternalConnect(EndPoint remoteEP)
   at System.Net.ServicePoint.ConnectSocketInternal(Boolean connectFailure, Socket s4, Socket s6, Socket& socket, IPAddress& address, ConnectSocketState state, IAsyncResult asyncResult, Int32 timeout, Exception& exception)
   --- End of inner exception stack trace ---
   at System.Net.HttpWebRequest.GetRequestStream()
   at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
   at CBRRates.CBR.DailyInfo.GetCursOnDate(DateTime On_date)
   at CBRRates.Program.GetUSDRateItems()
   at CBRRates.Program.Main(String[] args)

Т.е. при запуске консольного приложения из SQL Server оно (приложение) уже не хочет загружать данные. Почему? Что не так? Очень надеюсь на вашу помощь!
6 фев 08, 13:14    [5251392]     Ответить | Цитировать Сообщить модератору
 Re: Курсы валют, T-SQL, Web Service, Console Application...  [new]
Ken@t
Member

Откуда: 大地
Сообщений: 3264
А если так прямо с сервера ?
-- курс валют центробанка на сегодня
declare @xmlString nvarchar(4000), @url varchar(255), @retVal INT, @oXML INT, @loadRetVal INT, @h int
declare @d1 datetime
set @d1 = GetDate()

select @url = 'http://www.cbr.ru/scripts/XML_daily.asp?date_req=' + Convert(char(10), @d1, 103)
	EXEC @retVal = sp_OACreate 'MSXML2.DOMDocument', @oXML OUTPUT
	EXEC @retVal = sp_OASetProperty @oXML, 'async', 0
	EXEC @retVal = sp_OAMethod @oXML, 'load', @loadRetVal OUTPUT, @url
	EXEC @retVal = sp_OAMethod @oXML, 'xml', @xmlString OUTPUT
	EXEC sp_OADestroy @oXML

	exec sp_xml_preparedocument  @h output, @xmlString
select cast(floor(cast(@d1 as float)) as smalldatetime) as Data, CharCode, Nominal, Convert(money, replace(Value, ',', '.')) 'Value'
from OpenXML (@h, '//Valute', 0)
with ( Name varchar(99) './Name', Nominal int './Nominal', Value varchar(10) './Value', CharCode varchar(9) './CharCode' )

	exec sp_xml_removedocument @h

(С)не помню чей
6 фев 08, 13:18    [5251430]     Ответить | Цитировать Сообщить модератору
 Re: Курсы валют, T-SQL, Web Service, Console Application...  [new]
tsqler
Guest
Ken@t
А если так прямо с сервера ?

Спасибо.

Но все-таки интересует, почему же приложение, загружающую информацию по протоколу http, не загружает эту самую информацию, будучи запущенным SQL Server'ом?
6 фев 08, 13:25    [5251495]     Ответить | Цитировать Сообщить модератору
 Re: Курсы валют, T-SQL, Web Service, Console Application...  [new]
Ken@t
Member

Откуда: 大地
Сообщений: 3264
из под учётной записи SQL Server
6 фев 08, 13:27    [5251518]     Ответить | Цитировать Сообщить модератору
 Re: Курсы валют, T-SQL, Web Service, Console Application...  [new]
tsqler
Guest
Ken@t
из под учётной записи SQL Server


Ken@t, спасибо!

А каким образом можно запустить xp_cmdshell не из под учетной записи SQL Server, а из под учетной записи осуществляющего запрос человека?
p.s. доменная авторизация
6 фев 08, 13:34    [5251576]     Ответить | Цитировать Сообщить модератору
 Re: Курсы валют, T-SQL, Web Service, Console Application...  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10234
Блог
tsqler
Ken@t
из под учётной записи SQL Server


Ken@t, спасибо!

А каким образом можно запустить xp_cmdshell не из под учетной записи SQL Server, а из под учетной записи осуществляющего запрос человека?
p.s. доменная авторизация


sp_xp_cmdshell_proxy_account
6 фев 08, 13:59    [5251767]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: Курсы валют, T-SQL, Web Service, Console Application...  [new]
Ч. Денис
Member

Откуда: Москва
Сообщений: 59
Всех с Новым годом!
сегодня сбер подкинул сюрприз- добавил новые валюты в ежедневную xml рассылку , xml файл стал более 6 кбайт (еще вчера был менее 4), так что скриптик не работает (как я понимаю) из-за @xmlString nvarchar(4000)

что делать?
спасиб
12 янв 10, 12:51    [8170428]     Ответить | Цитировать Сообщить модератору
 Re: Курсы валют, T-SQL, Web Service, Console Application...  [new]
--__Александр__--
Member

Откуда:
Сообщений: 2631
Перейти на более продвинутые методы
http://scherbinin.blog.ru/58439859.html

-----------------
open your mind
12 янв 10, 13:12    [8170626]     Ответить | Цитировать Сообщить модератору
 Re: Курсы валют, T-SQL, Web Service, Console Application...  [new]
Павел Калугин
Member

Откуда: Москва
Сообщений: 23
--__Александр__--
Перейти на более продвинутые методы

Простите а как эти продвинутые методы к 2000-му серверу приживить?
13 янв 10, 09:59    [8175246]     Ответить | Цитировать Сообщить модератору
 Re: Курсы валют, T-SQL, Web Service, Console Application...  [new]
_Nata_2007
Member

Откуда:
Сообщений: 32
Тоже столкнулась с тем, что xml, получаемый с сайта центробанка стал большего размера.
Использовала примерно тот же скрипт, что приведен выше (сервер MS SQL 2000).

Нашла следующий выход:
использую ссылку для получения динамики котировок валюты, задаю одну и ту же начальную и конечную даты и код валюты.
Пример для курса доллара
http://www.cbr.ru/scripts/XML_dynamic.asp?date_req1=13/01/2010&date_req2=13/01/2010&VAL_NM_RQ=R01235

Аналогично, для евро. Коды валют можно посмотреть здесь http://www.cbr.ru/scripts/XML_val.asp?d=0

Не могу сказать, что самый лучший вариаент, но пока другого не нашла
13 янв 10, 16:47    [8178931]     Ответить | Цитировать Сообщить модератору
 Re: Курсы валют, T-SQL, Web Service, Console Application...  [new]
Andrey Ts
Member

Откуда: С-Пб
Сообщений: 516
--__Александр__--
Перейти на более продвинутые методы
http://scherbinin.blog.ru/58439859.html

-----------------
open your mind

Присоединяюсь к остальным, у кого перестал прием курса работать...
Метод не работает (на SQL 2005, хотя курс до 1 янв. получал на SQL2000)

Сведения: 0x4004300B в Задача "Поток данных", DTS.Pipeline: компонент "Назначение "SQL Server"" (151) записано строк: 0.
Ошибка при выполнении задачи: Задача "Поток данных"
Предупреждение: 0x80019002 в Package: Код предупреждения служб SSIS: DTS_W_MAXIMUMERRORCOUNTREACHED. Метод Execution завершен успешно, но число возникших ошибок (3) достигло максимально допустимого (1), что привело к сбою. Это происходит, когда количество ошибок достигает значения, определенного в свойстве MaximumErrorCount. Измените свойство MaximumErrorCount или устраните ошибки.
Пакет служб SSIS "Package.dtsx" завершен: Ошибка.
13 янв 10, 17:03    [8179080]     Ответить | Цитировать Сообщить модератору
 Re: Курсы валют, T-SQL, Web Service, Console Application...  [new]
demax77
Member

Откуда:
Сообщений: 18
Что, нет никаких вариантов решения под MS SQL 2000 ?
15 янв 10, 13:56    [8190837]     Ответить | Цитировать Сообщить модератору
 Re: Курсы валют, T-SQL, Web Service, Console Application...  [new]
Павел Калугин
Member

Откуда: Москва
Сообщений: 23
demax77 читай двумя постами выше. Проверено, работает.
15 янв 10, 14:05    [8190898]     Ответить | Цитировать Сообщить модератору
 Re: Курсы валют, T-SQL, Web Service, Console Application...  [new]
demax77
Member

Откуда:
Сообщений: 18
Это от _Nata_2007 ?
Криво как то. К тому же ищется по каким то внутренним кодам. А можно как то искать по USD например?
15 янв 10, 16:16    [8192203]     Ответить | Цитировать Сообщить модератору
 Re: Курсы валют, T-SQL, Web Service, Console Application...  [new]
Glory
Member

Откуда:
Сообщений: 104760
demax77
А можно как то искать по USD например?

Мда
"Пример для курса доллара
http://www.cbr.ru/scripts/XML_dynamic.asp?date_req1=13/01/2010&date_req2=13/01/2010&VAL_NM_RQ=R01235"

"Коды валют можно посмотреть здесь http://www.cbr.ru/scripts/XML_val.asp?d=0"
15 янв 10, 16:18    [8192213]     Ответить | Цитировать Сообщить модератору
 Re: Курсы валют, T-SQL, Web Service, Console Application...  [new]
demax77
Member

Откуда:
Сообщений: 18
Имелось ввиду передача параметру кода доллара (USD)
т.е. сейчас так VAL_NM_RQ=R01235, а надо так: VAL_NM_RQ='USD'
15 янв 10, 19:34    [8193341]     Ответить | Цитировать Сообщить модератору
 Re: Курсы валют, T-SQL, Web Service, Console Application...  [new]
_Nata_2007
Member

Откуда:
Сообщений: 32
demax77,

Параметры, к сожалению, не мы придумываем, а господа из Центробанка.

Те сервисы, которые они предоставляют, предполагают такую передачу параметров.
Вот они подробно описаны http://cbr.ru/scripts/Root.asp?Prtid=SXMLю
Изменить что-то здесь не в наших силах.

И в связи с ограничением размера nvarchar(4000), я так понимаю, у нас других вариантов нет.

Если кто-нибудь подскажет другой вариант, я лично скажу огромное спасибо.

А так.. хорошо. что хоть какой-то способ существует... Причем, ничего сложного в том. чтобы один раз прописать в процедуре код валюты, я лично не вижу.
15 янв 10, 21:39    [8193772]     Ответить | Цитировать Сообщить модератору
 Re: Курсы валют, T-SQL, Web Service, Console Application...  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
>nvarchar(4000) + SQL 2000

Проблемы две. Переменная и считывание значения из OLE объекта.
1. Переменная:
Обверните получение данных в отдельную процедуру типа dbo.spHTTPCall - URL на входной параметр и результат на выходной типа NText.
2. Считывание:
Вроде OLE Automation не умеет работать с Text/VarChar(max) типами напрямую, видимо придёца для такой кривой дороги писать свой ласапед с квадратными колёсами.
Попробуйте так:
DECLARE	@Response TABLE ( Response NText )
INSERT	@Response EXEC @ErrCode = sys.sp_OAGetProperty @OLEObject ,'responseText'
или монстроидальный вариант чтения через файл, код вырежте из отседова, но только там есть две не учтённые вещи (банальной проверки на длину 4000/8000 и в имя файла хотябы добавить номер процесса @@SPID т.к. sys.sp_OACreate генерит одинаковые идентификаторы :) ) - черновик же.
15 янв 10, 23:05    [8194218]     Ответить | Цитировать Сообщить модератору
 Re: Курсы валют, T-SQL, Web Service, Console Application...  [new]
_Nata_2007
Member

Откуда:
Сообщений: 32
Mnior,
если честно, то сложноватый метод... буду думать..

а что касается моего решения с xml, описанного выше, то возникла проблема...
если дата получения курса выходной день - то мы получаем значение null...

курим над решением дальше...
16 янв 10, 16:52    [8195976]     Ответить | Цитировать Сообщить модератору
 Re: Курсы валют, T-SQL, Web Service, Console Application...  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Чё ту сложного? Итак разжевали до немагу.
CREATE PROCEDURE [dbo].[spHTTPCall] (
	 @@URL		SysName
	,@@Responce	NText	OUTPUT
) AS BEGIN
	-- Для установки Proxy воспользуйтесь "proxycfg -u"
	DECLARE	 @OLEObject		Int
		,@HTTPStatus		Int
		,@ErrCode		Int
		,@ErrMethod		SysName
		,@ErrSource		SysName
		,@ErrDescription	SysName

	EXEC @ErrCode = sys.sp_OACreate 'MSXML2.ServerXMLHTTP', @OLEObject OUT
	IF (@ErrCode = 0) BEGIN
		EXEC @ErrCode = sys.sp_OAMethod @OLEObject ,'open',NULL ,'GET' ,@@URL ,'false'	IF (@ErrCode != 0) BEGIN SET @ErrMethod = 'open'	GOTO Error END
		EXEC @ErrCode = sys.sp_OAMethod @OLEObject ,'send',NULL ,NULL			IF (@ErrCode != 0) BEGIN SET @ErrMethod = 'send'	GOTO Error END
		EXEC @ErrCode = sys.sp_OAGetProperty @OLEObject ,'status' ,@HTTPStatus OUT	IF (@ErrCode != 0) BEGIN SET @ErrMethod = 'status'	GOTO Error END
		IF (@HTTPStatus = 200) BEGIN
			DECLARE	@Response TABLE ( Response NText )
			INSERT	@Response
			EXEC @ErrCode = sys.sp_OAGetProperty @OLEObject ,'responseText'		IF (@ErrCode != 0) BEGIN SET @ErrMethod = 'responseText'GOTO Error END
			SELECT @@Responce = Response FROM @Response
		END ELSE
			SELECT	 @ErrMethod	= 'send'
				,@ErrSource	= 'spSOAPMethod'
				,@ErrDescription= 'Ошибочный статус HTTP ответа "' + Convert(VarChar,@HTTPStatus) + '"'
		GOTO Destroy
	Error:	EXEC @ErrCode = sys.sp_OAGetErrorInfo @OLEObject ,@ErrSource OUT ,@ErrDescription OUT
	Destroy:EXEC @ErrCode = sys.sp_OADestroy @OLEObject
		IF (@ErrSource IS NOT NULL) BEGIN
			RAISERROR('Ошибка при выполнении метода "%s" в "%s": %s',18,1,@ErrMethod,@ErrSource,@ErrDescription)
			RETURN	@@Error
		END
	END ELSE BEGIN
		RAISERROR('Ошибка при создании OLE объекта "MSXML2.ServerXMLHTTP"',18,1)
		RETURN	@@Error
	END
END
GO
CREATE PROCEDURE [dbo].[spCBRRates] (
	 @@Date	SmallDateTime
	,@@XML	NText	= NULL	OUTPUT
) AS BEGIN
	DECLARE	 @ErrCode	Int
		,@Handle	Int
		,@URL		SysName
	SELECT	 @URL = 'http://www.cbr.ru/scripts/XML_daily.asp?date_req=' + Convert(Char(10),@@Date,103)
	EXEC @ErrCode = dbo.spHTTPCall @URL, @@XML			IF (@ErrCode != 0) RETURN @@Error
	EXEC @ErrCode = sys.sp_xml_preparedocument @Handle OUT, @@XML	IF (@ErrCode != 0) BEGIN RAISERROR('Ошибка парсирования XML',18,1) RETURN @@Error END

	-- А тут допиливаете нужную вам логику
--	INSERT	...
	SELECT	...
	FROM	OpenXML(@Handle,'...')
	WITH	( ... )

	EXEC @ErrCode = sys.sp_xml_removedocument @Handle
END
GO
В остальных случаях обращайтесь к деду-морозу, чтоб решил все ваши проблемы ... как нибуть так.

_Nata_2007
возникла проблема...
если дата получения курса выходной день - то мы получаем значение null...

курим над решением дальше...
Это проблема? Бросили бы вы лучше курить - вдруг поможет.
17 янв 10, 02:25    [8197124]     Ответить | Цитировать Сообщить модератору
 Re: Курсы валют, T-SQL, Web Service, Console Application...  [new]
_Nata_2007
Member

Откуда:
Сообщений: 32
Mnior
В остальных случаях обращайтесь к деду-морозу, чтоб решил все ваши проблемы ... как нибуть так....


Спасибо за скрипт! Я догадывалась, что Дед-Мороз существует :)

Mnior
Это проблема? Бросили бы вы лучше курить - вдруг поможет.


Буду бросать.. Жизнь-то нервная...пока не получается :)
17 янв 10, 13:06    [8197568]     Ответить | Цитировать Сообщить модератору
 Re: Курсы валют, T-SQL, Web Service, Console Application...  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10234
Блог
Для SQL Server 2005 и выше лучше всего сделать в виде CLR-сборки, имхо
17 янв 10, 13:55    [8197679]     Ответить | Цитировать Сообщить модератору
 Re: Курсы валют, T-SQL, Web Service, Console Application...  [new]
Aleksey-K
Member

Откуда: Москва
Сообщений: 3116
Knyazev Alexey
Для SQL Server 2005 и выше лучше всего сделать в виде CLR-сборки, имхо

А мне кажется, что использования SSIS более правильным. Тем более, все равное надо запускать по расписанию эту задачу.
Я использую именно такой подход: Весь пакет состит из четырех Task Control Flow:
1. SQL Task - Получаю дату с SQL сервера на какую получать котировки валют и запоминаю ее в переменную пакета;
2. Web Service Task - читаю в переменную отчета XML данные с Web сервиса ЦБ (http://www.cbr.ru/DailyInfoWebServ/DailyInfo.asmx?WSDL) с помощью метода GetCursOnDate (как описал --__Александр__-- в http://scherbinin.blog.ru/58439859.html)
3. XML Task выполняет XPATH и из полученного XML документа формирует его подмножество, которое уже нормально "понимает" SQL сервер и записывает его в еще одну переменную отчета;
4. SQL Task - вызывает хранимую процедуру с передачей ей даты (Task 1) и XML переменную (Task 3). Хранимая процедура с помощью метод типов данных XML query, nodes и value извлекает информацию из XML документа и записывает их в реляционные таблицы моей базы данных.
С уважением, Алексей
17 янв 10, 22:27    [8199180]     Ответить | Цитировать Сообщить модератору
 Re: Курсы валют, T-SQL, Web Service, Console Application...  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10234
Блог
Aleksey-K
Knyazev Alexey
Для SQL Server 2005 и выше лучше всего сделать в виде CLR-сборки, имхо

А мне кажется, что использования SSIS более правильным. Тем более, все равное надо запускать по расписанию эту задачу.
Я использую именно такой подход: Весь пакет состит из четырех Task Control Flow:
1. SQL Task - Получаю дату с SQL сервера на какую получать котировки валют и запоминаю ее в переменную пакета;
2. Web Service Task - читаю в переменную отчета XML данные с Web сервиса ЦБ (http://www.cbr.ru/DailyInfoWebServ/DailyInfo.asmx?WSDL) с помощью метода GetCursOnDate (как описал --__Александр__-- в http://scherbinin.blog.ru/58439859.html)
3. XML Task выполняет XPATH и из полученного XML документа формирует его подмножество, которое уже нормально "понимает" SQL сервер и записывает его в еще одну переменную отчета;
4. SQL Task - вызывает хранимую процедуру с передачей ей даты (Task 1) и XML переменную (Task 3). Хранимая процедура с помощью метод типов данных XML query, nodes и value извлекает информацию из XML документа и записывает их в реляционные таблицы моей базы данных.
С уважением, Алексей


а вот мой вариант
18 янв 10, 06:43    [8199676]     Ответить | Цитировать Сообщить модератору
 Re: Курсы валют, T-SQL, Web Service, Console Application...  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Knyazev Alexey
а вот мой вариант
В примере у вас WebService. По мне легче вызывать через dbo.spSOAPMethodCall - не нужно ничего компилить и мороки меньше:
DECLARE	 @URL		SysName
	,@Header	XML
	,@Body		XML
-- Создание запроса
;WITH XMLNAMESPACES (DEFAULT '<WebService URI>')
SELECT	 @URL	= '<WebService URL>'
	,@Body		= (
SELECT	 <WebMethod Param1 Value>	AS [WebMethod Param1 Name]
	,<WebMethod ParamN Value>	AS [WebMethod ParamN Name]
FOR	XML Path('<WebMethod Name>'),Type)
-- Вызов WebMethod-а
EXEC	dbo.spSOAPMethodCall
		 @URL
		,@Header	OUT	
		,@Body		OUT
-- Результат
SELECT	 @Header
	,@Body
Конечно контроля чуть меньше, но он всё равно мало чё даёт.
18 янв 10, 18:33    [8204658]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить