Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 spSOAPMethodCall (МТС коммуникатор)  [new]
r77
Member

Откуда: Краснодар
Сообщений: 270
Добрый день!

Помогите, пожалуйста, разобраться в ошибке.

Необходимо настроить смс-рассылку через "МТС коммуникатор".

есть хранимая процедура позволяющая обращаться к веб сервисам spSOAPMethodCall

взята она от сюда
https://www.sql.ru/forum/actualthread.aspx?bid=1&tid=576338&hl=spsoapmethodcall

вот текст:
ALTER PROCEDURE [dbo].[spSOAPMethodCall] (
	 @@URL		SysName
	,@@Header	XML	= NULL	OUTPUT
	,@@Body		XML	= NULL	OUTPUT
) AS BEGIN
	-- Для установки Proxy воспользуйтесь "proxycfg -u"

	DECLARE	 @OLEObject		Int
		,@HTTPStatus		Int
		,@ErrCode		Int
		,@ErrMethod		SysName
		,@ErrSource		SysName
		,@ErrDescription	SysName
		,@@SOAPAction		SysName
		,@Request		nvarchar(max) 

	;WITH XMLNAMESPACES (
		 'http://www.w3.org/2001/XMLSchema-instance'	AS [xsi]
		,'http://www.w3.org/2001/XMLSchema'		AS [xsd]
		,'http://schemas.xmlsoap.org/soap/envelope/'	AS [soap])
	SELECT	 @@SOAPAction	= @@Body.value('namespace-uri(/*[1])','SysName') + @@Body.value('local-name(/*[1])','SysName')
		,@Request = N'<?xml version="1.0" encoding="utf-8"?>'+Convert(nvarchar(max),(
	SELECT	 @@Header	AS [soap:Header]
		,@@Body		AS [soap:Body]
	FOR	XML Path('soap:Envelope'),Type))

	EXEC @ErrCode = sys.sp_OACreate 'MSXML2.ServerXMLHTTP', @OLEObject OUT
	IF (@ErrCode = 0) BEGIN
		EXEC @ErrCode = sys.sp_OAMethod @OLEObject ,'open'		,NULL ,'POST' ,@@URL ,'false'				IF (@ErrCode != 0) BEGIN SET @ErrMethod = 'open'		GOTO Error END
		EXEC @ErrCode = sys.sp_OAMethod @OLEObject ,'setRequestHeader'	,NULL ,'Content-Type'	,'text/xml; charset=utf-8'	IF (@ErrCode != 0) BEGIN SET @ErrMethod = 'setRequestHeader'	GOTO Error END
		EXEC @ErrCode = sys.sp_OAMethod @OLEObject ,'setRequestHeader'	,NULL ,'SOAPAction'	,@@SOAPAction			IF (@ErrCode != 0) BEGIN SET @ErrMethod = 'setRequestHeader'	GOTO Error END
		EXEC @ErrCode = sys.sp_OAMethod @OLEObject ,'send'		,NULL ,@Request						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 IN (200,500)) BEGIN
			DECLARE	@Response TABLE ( Response NVarChar(max) )
			INSERT	@Response
			EXEC @ErrCode = sys.sp_OAGetProperty @OLEObject ,'responseText'							IF (@ErrCode != 0) BEGIN SET @ErrMethod = 'responseText'	GOTO Error END

			;WITH XMLNAMESPACES (
				 'http://www.w3.org/2001/XMLSchema-instance'	AS [xsi]
				,'http://www.w3.org/2001/XMLSchema'		AS [xsd]
				,'http://schemas.xmlsoap.org/soap/envelope/'	AS [soap])
			SELECT	 @@Header	= R.X.query('/soap:Envelope/soap:Header/*')
				,@@Body		= R.X.query('/soap:Envelope/soap:Body/*')
			FROM	@Response CROSS APPLY (SELECT Convert(XML,Replace(Response,' encoding="utf-8"','')) AS X) R
			-- Fault
			;WITH XMLNAMESPACES (
				 'http://www.w3.org/2001/XMLSchema-instance'	AS [xsi]
				,'http://www.w3.org/2001/XMLSchema'		AS [xsd]
				,'http://schemas.xmlsoap.org/soap/envelope/'	AS [soap])
			SELECT	 @ErrMethod	= @@SOAPAction
				,@ErrSource	= @@Body.value('(/soap:Fault/faultcode)[1]'	,'SysName')
				,@ErrDescription= @@Body.value('(/soap:Fault/faultstring)[1]'	,'SysName')
			WHERE	@HTTPStatus = 500
		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)
			IF (@@TranCount > 0 AND XACT_STATE() != 0)
				ROLLBACK
			RETURN	@@Error
		END
	END ELSE BEGIN
		RAISERROR('Ошибка при создании OLE объекта "MSXML2.ServerXMLHTTP"',18,1)
		RETURN	@@Error
	END
END


требование поставщика услуги http://mcommunicator.ru/M2M/m2m_api.asmx?op=SendMessage

вызов:

DECLARE	 @URL		SysName
	,@Body		XML
-- Создание запроса
;WITH XMLNAMESPACES (DEFAULT 'http://mcommunicator.ru/M2M/')
SELECT	 @URL	= 'http://www.mcommunicator.ru/m2m/m2m_api.asmx'
	,@Body		= (
SELECT	 '1111'	AS msid
		,'test'	AS [message]
		,'1111' as naming
		,'1111' AS [login]
		,'202cb962ac59075b964b07152d234b70' as [password] --пароль в MD5
FOR	XML Path('SendMessage'),Type)
-- Вызов WebMethod-а
EXEC	dbo.spSOAPMethodCall
		 @URL
		,NULL	
		,@Body		OUT
-- Результат
;WITH XMLNAMESPACES (DEFAULT 'http://mcommunicator.ru/M2M')
SELECT	@Body.query('/SendMessageResponse/SendMessageResult/*')


ошибка:

Ошибка при выполнении метода "http://mcommunicator.ru/M2M/SendMessage" в "soap:Client": MSID_FORMAT_ERROR
14 дек 16, 14:39    [20002625]     Ответить | Цитировать Сообщить модератору
 Re: spSOAPMethodCall (МТС коммуникатор)  [new]
aleks2
Guest
1. Установить Fiddler
2. Запустить запрос с клиента SOAP. Например, из powershell.
3. Запустить запрос с вашей процедуры.
4. Сравнить запросы, отловив их Fiddler-ом.

PS. Клиент должен SOAP-запросы слать.
14 дек 16, 15:17    [20002919]     Ответить | Цитировать Сообщить модератору
 Re: spSOAPMethodCall (МТС коммуникатор)  [new]
r77
Member

Откуда: Краснодар
Сообщений: 270
С ошибкой выяснилось, не верный вызов был, надо:

DECLARE	 @URL		SysName
	,@Body		XML
-- Создание запроса
;WITH XMLNAMESPACES (DEFAULT 'http://mcommunicator.ru/M2M/')
SELECT	 @URL	= 'http://www.mcommunicator.ru/m2m/m2m_api.asmx/SendMessage'
	,@Body		= (
SELECT	 '1111'	AS msid
		,'test'	AS [message]
		,'1111' as naming
		,'1111' AS [login]
		,'202cb962ac59075b964b07152d234b70' as [password] --пароль в MD5
FOR	XML Path('SendMessage'),Type)
-- Вызов WebMethod-а
EXEC	dbo.spSOAPMethodCall
		 @URL
		,NULL	
		,@Body		OUT
-- Результат
;WITH XMLNAMESPACES (DEFAULT 'http://mcommunicator.ru/M2M')
SELECT	@Body.query('/SendMessageResponse/SendMessageResult/*')


Ошибки больше нет, но и ничего не возвращается - пустая строка...
14 дек 16, 15:37    [20003074]     Ответить | Цитировать Сообщить модератору
 Re: spSOAPMethodCall (МТС коммуникатор)  [new]
Владислав Колосов
Member

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

использование OLE механизмов в процедурах не слишком надёжно. На свой страх и риск, как говорится.
16 дек 16, 10:21    [20010197]     Ответить | Цитировать Сообщить модератору
 Re: spSOAPMethodCall (МТС коммуникатор)  [new]
Lokli
Member

Откуда:
Сообщений: 28
Владислав Колосов,
А можно подробнее про надежность и страхи с рисками?
23 май 17, 09:16    [20503125]     Ответить | Цитировать Сообщить модератору
 Re: spSOAPMethodCall (МТС коммуникатор)  [new]
aleks2
Guest
Lokli
Владислав Колосов,
А можно подробнее про надежность и страхи с рисками?


1. Страхи и риски - тормозящий и лежащий сервак.
2. Геморрой с отладкой.
3. Гораздо проще и быстрее - запуск внешнего скрипта powershell или кода C#, который запросит с сервера список рассылки и отфутболит "куда надо".
4. При необходимсти загрузит на сервант ответы.

Джигиты делают так
# загрузка данных из 
# параметры
param(
  [string]$VerbosePreference = "continue",  # вариант SilentlyContinue, Stop, Continue, Inquire, Ignore, Suspend" 
  [string]$ReraiseException = '1' # возбуждать исключения, вместо возврата ExitCode
)

try { $host.ui.RawUI.WindowTitle = "Загрузка цен из "; } catch { }

[bool]$ReraiseException = ($ReraiseException -eq '1')

#######################################################################
function Execute-SOAPRequest-ToFile 
( 
        [String] $URL,
        [String] $Action,
        [Xml]    $SOAPRequest,
        [String] $File,
        [int]    $Timeout = 60000

) 
{ 
        $soapWebRequest = [System.Net.WebRequest]::Create($URL)
        $soapWebRequest.Timeout = $Timeout
        $soapWebRequest.Headers.Add("SOAPAction" , $Action ) 

        $soapWebRequest.ContentType = 'text/xml;charset=utf-8' 
        $soapWebRequest.Accept      = $soapWebRequest.ContentType 
        $soapWebRequest.Method      = 'POST' 
        
        $requestStream = $soapWebRequest.GetRequestStream() 
        $SOAPRequest.Save($requestStream) 
        $requestStream.Close() 
        
        $responseStream = $soapWebRequest.GetResponse().GetResponseStream() 
        
        $fileStream =  new-object System.IO.FileStream( $File, [System.IO.FileMode]::Create, [System.IO.FileAccess]::Write )
        $responseStream.CopyTo($fileStream)
        $responseStream.Close()
        $fileStream.Close()

        return $true 
} 
########################################################################

# Фиксированные параметры

Add-Type -AssemblyName System.Web

$scriptFolder = [System.IO.Path]::GetDirectoryName($myInvocation.MyCommand.Definition)

$errorLogFile = $scriptFolder + "\Data.err"
$xmlErrorLogFile = $scriptFolder + "\SQLXMLBulkLoad.error.log"

$step = 0;

If ( Test-Path $errorLogFile ){
	Remove-Item $errorLogFile
}

$connStr_Avalon = "Server=.\MORETRAVEL;Database=Avalon2009;Integrated Security=True;"
$connStr_xmlBulkLoad = "provider=SQLOLEDB;data source=.\moretravel;database=tempdb;integrated security=SSPI"

Write-Verbose $connStr_Avalon
Write-Verbose $connStr_xmlBulkLoad

# для списка загрузки
$conn = New-Object System.Data.SqlClient.SqlConnection
$conn.ConnectionString = $connStr_Avalon
$conn.Open()
$command = $conn.CreateCommand()

# Генерация схемы
Write-Verbose ''
Write-Verbose 'Генерируем схему...'

# для загрузки файлов в ms sql и генерации схемы
$xmlbulkloader = New-Object -Com SQLXMLBulkLoad.SQLXMLBulkLoad
$xmlbulkloader.ConnectionString = $connStr_xmlBulkLoad
$xmlbulkloader.ErrorLogFile     = $xmlErrorLogFile
$xmlbulkloader.CheckConstraints = 0
$xmlbulkloader.XMLFragment      = 0
$xmlbulkloader.SchemaGen        = 1
$xmlbulkloader.SGDropTables     = 1
$xmlbulkloader.ForceTableLock   = 1
$xmlbulkloader.BulkLoad         = 0

$command.CommandText = “dbo.aleanGetSchemaData”
$command.CommandType = [System.Data.CommandType]'StoredProcedure'
$recordset = $command.ExecuteReader([System.Data.CommandBehavior]::SingleResult)

while ( $recordset.Read() ) {
      
    $schema = $scriptFolder + '\' + $recordset.Item('schema').trim()

    $str = [string]::Format( 'схема: schema = {0};', $schema )
    Write-Verbose $str

    Try{
      
        # грузим схему в базу
        $xmlbulkloader.Execute( $schema, "" )

    } catch {
               
        $errcnt = $errcnt + 1
        $ErrorMessage = $_.Exception.Message
        Write-Verbose $ErrorMessage

        $str = "схема: " + $schema
        Add-Content $errorLogFile -value $str
        Add-Content $errorLogFile -value $ErrorMessage
        If ( Test-Path $xmlErrorLogFile ){
            add-content $errorLogFile -value ( get-content $xmlErrorLogFile )
        }
        
        if ($ReraiseException) { throw $_.Exception }

        exit -1

    }
}
$recordset.Dispose();
Write-Verbose 'Схема сгенерирована.'

Write-Verbose ''
Write-Verbose 'Загружаем данные...'

# Загрузка данных
$xmlbulkloader.SchemaGen      = 0
$xmlbulkloader.SGDropTables   = 0
$xmlbulkloader.ForceTableLock = 1
$xmlbulkloader.BulkLoad       = 1

$command.CommandText = "dbo.aleanGetLoadData"
$command.CommandType = [System.Data.CommandType]'StoredProcedure'
$res = $command.Parameters.Add("@step", [System.Data.SqlDbType]::Int)

$step= $step + 1

$command.Parameters['@step'].Value = $step
$recordset = $command.ExecuteReader([System.Data.CommandBehavior]::SingleResult)

try {

  if ( $recordset.HasRows ) {

    while ( $recordset.HasRows ) { # Цикл по шагам загрузки


        #$str = [string]::Format( 'Шаг:  {0}', $step )
        #Write-Verbose $str

        $rcount = 0

        while ( $recordset.Read() ) { # Цикл по данным шага

          $rcount = $rcount + 1
          $str = [string]::Format( 'Шаг:  {0}. Строка:  {1}', $step, $rcount )
          Write-Verbose $str

          [String]$server      = $recordset.Item('server')

          if( $server -eq '' ) {

               Write-Verbose "Строка пропущена. Видимо нечего грузить."

            } else {
              
              [String]$Action      = $recordset.Item('Action')
              [XML]$envelope       = $recordset.Item('envelope')
              [String]$schema      = $recordset.Item('schema').trim()
              [String]$file        = $scriptFolder + '\' + $recordset.Item('file').trim()

              [boolean]$convertXML               = $recordset.Item('convertXML')
              $xmlbulkloader.IgnoreDuplicateKeys = $recordset.Item('IgnoreDuplicateKeys')

              if ( $schema -ne '' ) {
                $schema = $scriptFolder + '\' + $schema
              }

              $str = [string]::Format( 'Server = {0}; Action = {1}; file = {2}; schema = {3}', $Server, $Action, $file, $schema )
              Write-Verbose $str

              $str = [string]::Format( 'Envelope = {0}', $Envelope.OuterXml )
              Write-Verbose $str

              # грузим файл с интернету
              Write-Verbose "Грузим файл с интернету..."

              $res = Execute-SOAPRequest-ToFile -URL $Server -Action $Action -SOAPRequest $envelope -File $file

              if( $convertXML ) { # Маразм, но сервер возвращает экранированный XML

                    Write-Verbose "Конвертируем XML..."
                    $newfile = $file + ".xml"
                    $StreamWriter = [System.IO.StreamWriter] $newfile
                    [System.Web.HttpUtility]::HtmlDecode( [IO.File]::ReadAllText($file), $StreamWriter )
                    $StreamWriter.Close()
                    $StreamWriter.Dispose()
                    $file = $newfile
      
              }

              # грузим файл в базу
              Write-Verbose "Грузим в базу..."
              if ( $schema -ne '' ) {

                $res = $xmlbulkloader.Execute( $schema, $file )
      
              } else {
      
                Write-Verbose "загрузка пропущена!"
      
              }


            }

            Write-Verbose "завершено."
        }

        $step= $step + 1

        $recordset.Dispose()
        $command.Parameters['@step'].Value = $step
        $recordset = $command.ExecuteReader([System.Data.CommandBehavior]::SingleResult)

    }

  } else {
  
    Write-Verbose "Нет данных."
    if ($ReraiseException) { throw "Нет данных." }
    exit -2

  }

} catch {
  
  $errcnt = $errcnt + 1

  $ErrorMessage = $_.Exception.Message
  Write-Verbose $ErrorMessage

  $str = "шаг: " + $step
  Add-Content $errorLogFile -value $str
  Add-Content $errorLogFile -value $ErrorMessage

  if ($ReraiseException) { throw $_.Exception }

  exit -3

}

Write-Verbose ""
Write-Verbose "Очистка."
$command.Dispose()
$conn.Dispose()
$res = [System.Runtime.InteropServices.Marshal]::ReleaseComObject( $xmlbulkloader )
$xmlbulkloader = $null

Write-Verbose "Завершено."

exit 0
#############################################################################
23 май 17, 10:22    [20503306]     Ответить | Цитировать Сообщить модератору
 Re: spSOAPMethodCall (МТС коммуникатор)  [new]
Lokli
Member

Откуда:
Сообщений: 28
aleks2
1. Страхи и риски - тормозящий и лежащий сервак.
2. Геморрой с отладкой.
3. Гораздо проще и быстрее - запуск внешнего скрипта powershell или кода C#, который запросит с сервера список рассылки и отфутболит "куда надо".
4. При необходимсти загрузит на сервант ответы.


1. Ложиться сервак чей будет: внешний web или sql?
2. Не вижу геммороя с отладкой, т.к. алгоритм отработан в другой среде.
3. Чем проще и на сколько быстрее?
4. Я получу от web-сервера в ответ xml, который тут же sql'ем обработаю.
23 май 17, 12:46    [20503928]     Ответить | Цитировать Сообщить модератору
 Re: spSOAPMethodCall (МТС коммуникатор)  [new]
aleks2
Guest
Lokli
aleks2
1. Страхи и риски - тормозящий и лежащий сервак.
2. Геморрой с отладкой.
3. Гораздо проще и быстрее - запуск внешнего скрипта powershell или кода C#, который запросит с сервера список рассылки и отфутболит "куда надо".
4. При необходимсти загрузит на сервант ответы.


1. Ложиться сервак чей будет: внешний web или sql?
2. Не вижу геммороя с отладкой, т.к. алгоритм отработан в другой среде.
3. Чем проще и на сколько быстрее?
4. Я получу от web-сервера в ответ xml, который тут же sql'ем обработаю.


1. sql, вестимо.
2. Какить хочите - воня ваша.
3. Опять же, зависит от. Но внешний код не ограничен в возможностях использования библиотек.
4. см. п 1. Потоковые парсеры XML- быстрее. На порядок.

ЗЫ. Короче, любой левый процесс, работающий внутри SQL - потенциальная засада.
Иначе бы SQL Agent был бы не нужен. Да и SSIS - внешний процесс.
23 май 17, 13:09    [20504031]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить