Hola, chicos,
¡Buenas tardes!
En esta publicación, le demostraré cómo realizar solicitudes POST y GET (de hecho, puede ser de cualquier tipo) en una base de datos SQL Server usando SQL CLR.
Si no sabes qué es CLR o tienes dudas sobre cómo usarlo, publicarlo, etc., ver esta publicación aquí.
Ya había hecho algunas publicaciones aquí en el blog demostrando cómo hacer esto usando OLE Automation, pero como CLR es una alternativa mucho más segura y flexible, decidí demostrar cómo hacer esto usando CLR (C#).
Código fuente del procedimiento stpWs_Requisicao:
using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Net;
using System.IO;
using System.Text;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void stpWs_Requisicao(SqlString Ds_Url, SqlString Ds_Metodo, SqlString Ds_Parametros, SqlString Ds_Codificacao, out SqlString Ds_Retorno_OUTPUT)
{
var parametros = (Ds_Parametros.IsNull) ? "" : Ds_Parametros.Value;
var metodo = (Ds_Metodo.IsNull) ? "GET" : Ds_Metodo.Value.ToUpper();
var url = Ds_Url.Value;
var feedData = string.Empty;
var encoding = (Ds_Codificacao.IsNull) ? "UTF-8" : Ds_Codificacao.Value;
try
{
var request = (HttpWebRequest) WebRequest.Create(url);
request.Method = metodo;
request.ContentType = "application/x-www-form-urlencoded";
if (metodo == "POST" && parametros.Length > 0)
{
var data = parametros;
var dataStream = Encoding.UTF8.GetBytes(data);
request.ContentLength = dataStream.Length;
var newStream = request.GetRequestStream();
newStream.Write(dataStream, 0, dataStream.Length);
newStream.Close();
}
var response = (HttpWebResponse) request.GetResponse();
var stream = response.GetResponseStream();
var streamReader = new StreamReader(stream, Encoding.GetEncoding(encoding));
feedData = streamReader.ReadToEnd();
response.Close();
stream?.Dispose();
streamReader.Dispose();
}
catch (Exception ex)
{
if (SqlContext.Pipe != null) SqlContext.Pipe.Send(ex.Message);
}
Ds_Retorno_OUTPUT = feedData;
}
}Ejemplo de uso
Una vez que el procedimiento ya se haya creado y el ensamblaje se haya publicado en su servidor, simplemente use SP para realizar sus solicitudes normalmente, como en los ejemplos a continuación:
DECLARE
@Url VARCHAR(MAX) = 'http://viacep.com.br/ws/29090090/xml',
@Retorno VARCHAR(MAX)
EXEC CLR.dbo.stpWs_Requisicao
@Ds_Url = @Url, -- nvarchar(max)
@Ds_Metodo = N'GET', -- nvarchar(max)
@Ds_Parametros = N'', -- nvarchar(max)
@Ds_Codificacao = N'ISO-8859-1', -- nvarchar(max)
@Ds_Retorno_OUTPUT = @Retorno OUT -- nvarchar(max)
SELECT CAST(@Retorno AS xml)
Cómo crear bibliotecas SQLCLR solo con T-SQL
Haga clic aquí para ver el contenido
Si desea utilizar SQLCLR, este tema se utiliza para instalar los objetos necesarios para poder seguir los ejemplos de este artículo, sin tener que crear un nuevo proyecto de Visual Studio e implementarlo manualmente.
La forma más sencilla y robusta de realizar solicitudes HTTP a través de SQL Server es, sin duda, utilizando el SQLCLR (haga clic aquí si ni siquiera sabes qué es SQLCLR).
Cómo crear stpWs_Requisicao en su entorno (SQL Server 2012 a 2016):
Si está utilizando las versiones 2012, 2014 o 2016 de SQL Server, puede utilizar el siguiente script para crear el ensamblado (DLL) en la base de datos deseada y el procedimiento almacenado “stpWs_Requisicao”, que se utiliza para realizar la solicitud HTTP API.
USE [Base_Teste]
GO
-- Apago os objetos desse post, caso já tenham sido criados
IF (OBJECT_ID('dbo.stpWs_Requisicao') IS NOT NULL) DROP PROCEDURE dbo.stpWs_Requisicao;
IF (EXISTS(SELECT NULL FROM sys.assemblies WHERE [name] = @clrName)) DROP ASSEMBLY [SQLCLR_Requisicao_Web]
-- Crio o assembly no banco
CREATE ASSEMBLY [SQLCLR_Requisicao_Web]
AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300EB85E45D0000000000000000E00022200B013000001200000006000000000000AA300000002000000040000000000010002000000002000004000000000000000600000000000000008000000002000000000000030060850000100000100000000010000010000000000000100000000000000000000000583000004F00000000400000D802000000000000000000000000000000000000006000000C000000202F00001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000B0100000002000000012000000020000000000000000000000000000200000602E72737263000000D8020000004000000004000000140000000000000000000000000000400000402E72656C6F6300000C00000000600000000200000018000000000000000000000000000040000042000000000000000000000000000000008C30000000000000480000000200050034240000EC0A000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001B3005003003000001000011000F02280600000A2D090F02280700000A2B0572010000700A0F01280600000A2D090F01280700000A2B0572030000700B0F00280700000A0C7E0800000A0D0F03280600000A2D090F03280700000A2B05720B00007013040F04280600000A2D090F04280700000A2B05720100007013050F05280600000A2D090F05280700000A2B05720100007013060F07280600000A2D090F07280700000A2B05720100007013070F06280900000A2D090F06280A00000A2B011613080F08280B00000A2D090F08280C00000A2B021F1E13091104280D00000A130A110A2C07720B0000701304000008280E00000A740A000001130B110B076F0F00000A00110B110920E80300005A6F1000000A000872170000706F1100000A16FE0416FE01130C110C2C390017281200000A0020F00F0000281300000A007E02000004252D17267E01000004FE0605000006731400000A258002000004281500000A00001106280D00000A16FE01130D110D2C0C00110B11066F1600000A00001105280D00000A16FE01130E110E2C0C00110B11056F1700000A00001107280D00000A16FE01130F110F2C77001107178D1800000125161F7C9D6F1800000A131011108E6913111613122B4A00111011129A178D1400000125167229000070A2166F1900000A13131113169A6F1A00000A13141113179A6F1A00000A1315110B6F1B00000A111411156F1C00000A000011121758131211121111FE04131611162DAA001108131711172C02000007722F000070281D00000A2D0D077239000070281D00000A2C0B066F1E00000A16FE022B0116131811182C5100061319281F00000A11196F2000000A131A110B111A8E696A6F2100000A00110B6F2200000A131B00111B111A16111A8E696F2300000A00111B6F2400000A0000DE0D111B2C08111B6F2500000A00DC00110B6F2600000A740C000001131C00111C6F2700000A131D00111D14FE03131E111E2C2B00111D1104282800000A732900000A131F00111F6F2A00000A0D00DE0D111F2C08111F6F2500000A00DC0000DE0D111D2C08111D6F2500000A00DC00DE0D111C2C08111C6F2500000A00DC00DE1013200011206F2B00000A732C00000A7A0E0909282D00000A81070000012A417C000002000000770200001B000000920200000D0000000000000002000000D50200000C000000E10200000D0000000000000002000000B80200003A000000F20200000D0000000000000002000000AE02000054000000020300000D0000000000000000000000E30000002F02000012030000100000000E0000012202282E00000A002A2E730400000680010000042A00000013300100070000000200001100170A2B00062A0042534A4201000100000000000C00000076342E302E33303331390000000005006C00000070030000237E0000DC0300007805000023537472696E67730000000054090000440000002355530098090000100000002347554944000000A80900004401000023426C6F620000000000000002000001571502000902000000FA01330016000001000000200000000300000002000000050000000E0000002E0000000500000002000000010000000300000001000000000096020100000000000600B101EE030600D101EE0306008701DB030F000E0400000600C204FA020A009B01B3030A0023021D040A0006031D040A0023001D040E00FB04C9040600F30256000E002501C90406008703560006004E03FA0206006C01EE030E004A02420506004F0143040E00110343040E00B204420506002602FA020E00FF04C9040E009F03C9040E00F100C90406008203FA0206007A04FA020E002F03C9040E001B03A50006001A022A050600E500FA020E002901C90406009403560006004303FA02000000004D0000000000010001000100100032040000150001000100032110009D00000015000100030036003500DC0016000100E00050200000000096006703E40001000824000000008618CE0306000B001124000000009118D403FD000B000824000000008618CE0306000B0020240000000083000A0001010B0000000100D002000002007803000003008D0400000400580300000500E604000006000601000007006405000008009B04000009000A0502000A006000000001003900000002003E000000030043000000040048000900CE0301001100CE0306001900CE030A003100CE0306007900CE0306003900B0023D003900EF014100A1005E0545004100B0023D004100EF013D004900B0023D004900EF014800A10056054C00A9005F015100A900CE005700A9001E050100A1000F025C00B100F9016100B100BB0266008100CE036C00B1006E027200A900150157005100F0045700A100E0047800A100E0047F00A10001034100A900A6048800D900A1008D00A10036059300A1002D024800E1002C009900E10071049E00A9003802A400A900E902A90059006601AE00590041010600E90047010600A9003501B600F100D702A900E1001702BB006900CE03C100F900C40041007100D90041000101CE0357003900D404C9002900CE030600200023003D012E000B000C012E00130015012E001B00340163002B003D011000CF000480000000000000000000000000000000007E000000040000000000000000000000D300940000000000040000000000000000000000D300720000000000040000000000000000000000D300FA0200000000030002000000003C3E395F5F305F30003C73747057735F5265717569736963616F3E625F5F305F300053716C496E743332006765745F55544638003C3E39003C70303E003C70313E003C70323E003C70333E003C4D6F64756C653E0053797374656D2E494F0044735F5265746F726E6F5F4F55545055540053797374656D2E446174610053514C434C525F5265717569736963616F5F576562006D73636F726C6962003C3E63004164640053797374656D2E436F6C6C656374696F6E732E5370656369616C697A65640052656164546F456E64007365745F4D6574686F64006765745F4D6573736167650049446973706F7361626C6500536563757269747950726F746F636F6C547970650044735F436F6E74656E7454797065007365745F436F6E74656E74547970650048747470576562526573706F6E736500476574526573706F6E736500436C6F736500446973706F7365005835303943657274696669636174650043726561746500577269746500436F6D70696C657247656E6572617465644174747269627574650044656275676761626C654174747269627574650053716C50726F63656475726541747472696275746500436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465006765745F56616C7565007365745F457870656374313030436F6E74696E756500496E6465784F6600476574456E636F64696E670053716C537472696E67006765745F4C656E677468007365745F436F6E74656E744C656E6774680052656D6F7465436572746966696361746556616C69646174696F6E43616C6C6261636B007365745F536572766572436572746966696361746556616C69646174696F6E43616C6C6261636B0053514C434C525F5265717569736963616F5F5765622E646C6C006765745F49734E756C6C007365745F536563757269747950726F746F636F6C0044735F55726C00476574526573706F6E736553747265616D004765745265717565737453747265616D0053797374656D005472696D0053716C426F6F6C65616E0058353039436861696E004E616D6556616C7565436F6C6C656374696F6E00576562486561646572436F6C6C656374696F6E004170706C69636174696F6E457863657074696F6E0044735F436F64696669636163616F0073747057735F5265717569736963616F0044735F4D65746F646F00436861720053747265616D52656164657200546578745265616465720053657276696365506F696E744D616E61676572004D6963726F736F66742E53716C5365727665722E536572766572002E63746F72002E6363746F720053797374656D2E446961676E6F73746963730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300446562756767696E674D6F6465730053797374656D2E446174612E53716C54797065730053746F72656450726F636564757265730053797374656D2E53656375726974792E43727970746F6772617068792E5835303943657274696669636174657300476574427974657300537472696E6753706C69744F7074696F6E730044735F506172616D6574726F730044735F48656164657273006765745F486561646572730053736C506F6C6963794572726F7273004F626A6563740053797374656D2E4E6574006F705F496D706C696369740053706C69740044735F416363657074007365745F4163636570740048747470576562526571756573740051745F536567756E646F735F54696D656F7574007365745F54696D656F75740053797374656D2E54657874006F705F457175616C6974790053797374656D2E4E65742E53656375726974790049734E756C6C4F72456D70747900466C5F417574656E746963615F50726F7879000000010007470045005400000B5500540046002D0038000111680074007400700073003A002F002F0000053A003A00000950004F005300540000075000550054000000000091D3434B91A6944B9944F25BAF4B39B0000420010108032000010520010111112C07210E0E0E0E0E0E0E0E0208021229020202021D0E08081D0E0E0E0202020E1D05122D1231122D0212351239032000020320000E02060E03200008040001020E05000112550E042001010E042001080E040001010205000101115D052002011C180500010112410620011D0E1D030820021D0E1D0E11650420001269052002010E0E050002020E0E04000012710520011D050E042001010A042000122D072003011D050808042000127905000112710E07200201122D1271050001111D0E0307010208B77A5C561934E0890306120C0306124118000A01111D111D111D111D111D111D1121111D112510111D030000010A2004021C12451249114D0801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773010801000701000000000401000000000000000000EB85E45D00000000020000001C0100003C2F00003C110000525344538863FD7F0DBEED47BBE2FD478149B89501000000433A5C55736572735C6469726365755C736F757263655C7265706F735C53514C434C525F5265717569736963616F5F5765625C53514C434C525F5265717569736963616F5F5765625C6F626A5C44656275675C53514C434C525F5265717569736963616F5F5765622E70646200000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000008030000000000000000000009A3000000020000000000000000000000000000000000000000000008C300000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF2500200010000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001001000000018000080000000000000000000000000000001000100000030000080000000000000000000000000000001000000000048000000584000007C02000000000000000000007C0234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000000000000000000000000000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004DC010000010053007400720069006E006700460069006C00650049006E0066006F000000B801000001003000300030003000300034006200300000002C0002000100460069006C0065004400650073006300720069007000740069006F006E000000000020000000300008000100460069006C006500560065007200730069006F006E000000000030002E0030002E0030002E003000000054001A00010049006E007400650072006E0061006C004E0061006D0065000000530051004C0043004C0052005F005200650071007500690073006900630061006F005F005700650062002E0064006C006C0000002800020001004C006500670061006C0043006F0070007900720069006700680074000000200000005C001A0001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000530051004C0043004C0052005F005200650071007500690073006900630061006F005F005700650062002E0064006C006C000000340008000100500072006F006400750063007400560065007200730069006F006E00000030002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000030002E0030002E0030002E0030000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000003000000C000000AC3000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = EXTERNAL_ACCESS;
-- Crio a Stored Procedure "stpWs_Requisicao"
CREATE PROCEDURE [dbo].[stpWs_Requisicao] @Ds_Url NVARCHAR (MAX), @Ds_Metodo NVARCHAR (MAX), @Ds_Parametros NVARCHAR (MAX), @Ds_Codificacao NVARCHAR (MAX), @Ds_Accept NVARCHAR (MAX), @Ds_ContentType NVARCHAR (MAX), @Fl_Autentica_Proxy BIT, @Ds_Headers NVARCHAR (MAX), @Qt_Segundos_Timeout INT, @Ds_Retorno_OUTPUT NVARCHAR (MAX) OUTPUT
AS EXTERNAL NAME [SQLCLR_Requisicao_Web].[StoredProcedures].[stpWs_Requisicao]
Cómo crear stpWs_Requisicao en su entorno (SQL Server 2017+):
Si está utilizando una versión mayor o igual a 2017 de SQL Server, puede utilizar el siguiente script para crear el ensamblado (DLL) en la base de datos deseada y el procedimiento almacenado “stpWs_Requisicao”, que se utiliza para realizar la solicitud HTTP API. Este script es diferente al anterior, pues a partir de la versión 2017, SQL Server ahora requiere la firma de ensamblados incluso en Modo Seguro, debido a cambios en la seguridad de .NET Framework.
USE [Base_Teste]
GO
-- Habilito o modo TRUSTWORTHY para conseguir criar o assembly no modo EXTERNAL_ACCESS
ALTER DATABASE Base_Teste SET TRUSTWORTHY ON
GO
-- Gero a hash para marcar o assembly como confiável
DECLARE
@clrName nvarchar(4000) = 'SQLCLR_Requisicao_Web',
@asmBin varbinary(max) = 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300EB85E45D0000000000000000E00022200B013000001200000006000000000000AA300000002000000040000000000010002000000002000004000000000000000600000000000000008000000002000000000000030060850000100000100000000010000010000000000000100000000000000000000000583000004F00000000400000D802000000000000000000000000000000000000006000000C000000202F00001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000B0100000002000000012000000020000000000000000000000000000200000602E72737263000000D8020000004000000004000000140000000000000000000000000000400000402E72656C6F6300000C00000000600000000200000018000000000000000000000000000040000042000000000000000000000000000000008C30000000000000480000000200050034240000EC0A000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001B3005003003000001000011000F02280600000A2D090F02280700000A2B0572010000700A0F01280600000A2D090F01280700000A2B0572030000700B0F00280700000A0C7E0800000A0D0F03280600000A2D090F03280700000A2B05720B00007013040F04280600000A2D090F04280700000A2B05720100007013050F05280600000A2D090F05280700000A2B05720100007013060F07280600000A2D090F07280700000A2B05720100007013070F06280900000A2D090F06280A00000A2B011613080F08280B00000A2D090F08280C00000A2B021F1E13091104280D00000A130A110A2C07720B0000701304000008280E00000A740A000001130B110B076F0F00000A00110B110920E80300005A6F1000000A000872170000706F1100000A16FE0416FE01130C110C2C390017281200000A0020F00F0000281300000A007E02000004252D17267E01000004FE0605000006731400000A258002000004281500000A00001106280D00000A16FE01130D110D2C0C00110B11066F1600000A00001105280D00000A16FE01130E110E2C0C00110B11056F1700000A00001107280D00000A16FE01130F110F2C77001107178D1800000125161F7C9D6F1800000A131011108E6913111613122B4A00111011129A178D1400000125167229000070A2166F1900000A13131113169A6F1A00000A13141113179A6F1A00000A1315110B6F1B00000A111411156F1C00000A000011121758131211121111FE04131611162DAA001108131711172C02000007722F000070281D00000A2D0D077239000070281D00000A2C0B066F1E00000A16FE022B0116131811182C5100061319281F00000A11196F2000000A131A110B111A8E696A6F2100000A00110B6F2200000A131B00111B111A16111A8E696F2300000A00111B6F2400000A0000DE0D111B2C08111B6F2500000A00DC00110B6F2600000A740C000001131C00111C6F2700000A131D00111D14FE03131E111E2C2B00111D1104282800000A732900000A131F00111F6F2A00000A0D00DE0D111F2C08111F6F2500000A00DC0000DE0D111D2C08111D6F2500000A00DC00DE0D111C2C08111C6F2500000A00DC00DE1013200011206F2B00000A732C00000A7A0E0909282D00000A81070000012A417C000002000000770200001B000000920200000D0000000000000002000000D50200000C000000E10200000D0000000000000002000000B80200003A000000F20200000D0000000000000002000000AE02000054000000020300000D0000000000000000000000E30000002F02000012030000100000000E0000012202282E00000A002A2E730400000680010000042A00000013300100070000000200001100170A2B00062A0042534A4201000100000000000C00000076342E302E33303331390000000005006C00000070030000237E0000DC0300007805000023537472696E67730000000054090000440000002355530098090000100000002347554944000000A80900004401000023426C6F620000000000000002000001571502000902000000FA01330016000001000000200000000300000002000000050000000E0000002E0000000500000002000000010000000300000001000000000096020100000000000600B101EE030600D101EE0306008701DB030F000E0400000600C204FA020A009B01B3030A0023021D040A0006031D040A0023001D040E00FB04C9040600F30256000E002501C90406008703560006004E03FA0206006C01EE030E004A02420506004F0143040E00110343040E00B204420506002602FA020E00FF04C9040E009F03C9040E00F100C90406008203FA0206007A04FA020E002F03C9040E001B03A50006001A022A050600E500FA020E002901C90406009403560006004303FA02000000004D0000000000010001000100100032040000150001000100032110009D00000015000100030036003500DC0016000100E00050200000000096006703E40001000824000000008618CE0306000B001124000000009118D403FD000B000824000000008618CE0306000B0020240000000083000A0001010B0000000100D002000002007803000003008D0400000400580300000500E604000006000601000007006405000008009B04000009000A0502000A006000000001003900000002003E000000030043000000040048000900CE0301001100CE0306001900CE030A003100CE0306007900CE0306003900B0023D003900EF014100A1005E0545004100B0023D004100EF013D004900B0023D004900EF014800A10056054C00A9005F015100A900CE005700A9001E050100A1000F025C00B100F9016100B100BB0266008100CE036C00B1006E027200A900150157005100F0045700A100E0047800A100E0047F00A10001034100A900A6048800D900A1008D00A10036059300A1002D024800E1002C009900E10071049E00A9003802A400A900E902A90059006601AE00590041010600E90047010600A9003501B600F100D702A900E1001702BB006900CE03C100F900C40041007100D90041000101CE0357003900D404C9002900CE030600200023003D012E000B000C012E00130015012E001B00340163002B003D011000CF000480000000000000000000000000000000007E000000040000000000000000000000D300940000000000040000000000000000000000D300720000000000040000000000000000000000D300FA0200000000030002000000003C3E395F5F305F30003C73747057735F5265717569736963616F3E625F5F305F300053716C496E743332006765745F55544638003C3E39003C70303E003C70313E003C70323E003C70333E003C4D6F64756C653E0053797374656D2E494F0044735F5265746F726E6F5F4F55545055540053797374656D2E446174610053514C434C525F5265717569736963616F5F576562006D73636F726C6962003C3E63004164640053797374656D2E436F6C6C656374696F6E732E5370656369616C697A65640052656164546F456E64007365745F4D6574686F64006765745F4D6573736167650049446973706F7361626C6500536563757269747950726F746F636F6C547970650044735F436F6E74656E7454797065007365745F436F6E74656E74547970650048747470576562526573706F6E736500476574526573706F6E736500436C6F736500446973706F7365005835303943657274696669636174650043726561746500577269746500436F6D70696C657247656E6572617465644174747269627574650044656275676761626C654174747269627574650053716C50726F63656475726541747472696275746500436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465006765745F56616C7565007365745F457870656374313030436F6E74696E756500496E6465784F6600476574456E636F64696E670053716C537472696E67006765745F4C656E677468007365745F436F6E74656E744C656E6774680052656D6F7465436572746966696361746556616C69646174696F6E43616C6C6261636B007365745F536572766572436572746966696361746556616C69646174696F6E43616C6C6261636B0053514C434C525F5265717569736963616F5F5765622E646C6C006765745F49734E756C6C007365745F536563757269747950726F746F636F6C0044735F55726C00476574526573706F6E736553747265616D004765745265717565737453747265616D0053797374656D005472696D0053716C426F6F6C65616E0058353039436861696E004E616D6556616C7565436F6C6C656374696F6E00576562486561646572436F6C6C656374696F6E004170706C69636174696F6E457863657074696F6E0044735F436F64696669636163616F0073747057735F5265717569736963616F0044735F4D65746F646F00436861720053747265616D52656164657200546578745265616465720053657276696365506F696E744D616E61676572004D6963726F736F66742E53716C5365727665722E536572766572002E63746F72002E6363746F720053797374656D2E446961676E6F73746963730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300446562756767696E674D6F6465730053797374656D2E446174612E53716C54797065730053746F72656450726F636564757265730053797374656D2E53656375726974792E43727970746F6772617068792E5835303943657274696669636174657300476574427974657300537472696E6753706C69744F7074696F6E730044735F506172616D6574726F730044735F48656164657273006765745F486561646572730053736C506F6C6963794572726F7273004F626A6563740053797374656D2E4E6574006F705F496D706C696369740053706C69740044735F416363657074007365745F4163636570740048747470576562526571756573740051745F536567756E646F735F54696D656F7574007365745F54696D656F75740053797374656D2E54657874006F705F457175616C6974790053797374656D2E4E65742E53656375726974790049734E756C6C4F72456D70747900466C5F417574656E746963615F50726F7879000000010007470045005400000B5500540046002D0038000111680074007400700073003A002F002F0000053A003A00000950004F005300540000075000550054000000000091D3434B91A6944B9944F25BAF4B39B0000420010108032000010520010111112C07210E0E0E0E0E0E0E0E0208021229020202021D0E08081D0E0E0E0202020E1D05122D1231122D0212351239032000020320000E02060E03200008040001020E05000112550E042001010E042001080E040001010205000101115D052002011C180500010112410620011D0E1D030820021D0E1D0E11650420001269052002010E0E050002020E0E04000012710520011D050E042001010A042000122D072003011D050808042000127905000112710E07200201122D1271050001111D0E0307010208B77A5C561934E0890306120C0306124118000A01111D111D111D111D111D111D1121111D112510111D030000010A2004021C12451249114D0801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773010801000701000000000401000000000000000000EB85E45D00000000020000001C0100003C2F00003C110000525344538863FD7F0DBEED47BBE2FD478149B89501000000433A5C55736572735C6469726365755C736F757263655C7265706F735C53514C434C525F5265717569736963616F5F5765625C53514C434C525F5265717569736963616F5F5765625C6F626A5C44656275675C53514C434C525F5265717569736963616F5F5765622E70646200000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000008030000000000000000000009A3000000020000000000000000000000000000000000000000000008C300000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF2500200010000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001001000000018000080000000000000000000000000000001000100000030000080000000000000000000000000000001000000000048000000584000007C02000000000000000000007C0234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000000000000000000000000000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004DC010000010053007400720069006E006700460069006C00650049006E0066006F000000B801000001003000300030003000300034006200300000002C0002000100460069006C0065004400650073006300720069007000740069006F006E000000000020000000300008000100460069006C006500560065007200730069006F006E000000000030002E0030002E0030002E003000000054001A00010049006E007400650072006E0061006C004E0061006D0065000000530051004C0043004C0052005F005200650071007500690073006900630061006F005F005700650062002E0064006C006C0000002800020001004C006500670061006C0043006F0070007900720069006700680074000000200000005C001A0001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000530051004C0043004C0052005F005200650071007500690073006900630061006F005F005700650062002E0064006C006C000000340008000100500072006F006400750063007400560065007200730069006F006E00000030002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000030002E0030002E0030002E0030000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000003000000C000000AC3000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000,
@hash varbinary(64);
SET @hash = HASHBYTES('SHA2_512', @asmBin);
-- Apago os objetos desse post, caso já tenham sido criados
IF (OBJECT_ID('dbo.stpWs_Requisicao') IS NOT NULL) DROP PROCEDURE dbo.stpWs_Requisicao;
IF (EXISTS(SELECT NULL FROM sys.trusted_assemblies WHERE [description] = @clrName)) EXEC sys.sp_drop_trusted_assembly @hash = @hash;
IF (EXISTS(SELECT NULL FROM sys.assemblies WHERE [name] = @clrName)) DROP ASSEMBLY [SQLCLR_Requisicao_Web]
-- Crio o assembly no banco
CREATE ASSEMBLY [SQLCLR_Requisicao_Web]
AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300EB85E45D0000000000000000E00022200B013000001200000006000000000000AA300000002000000040000000000010002000000002000004000000000000000600000000000000008000000002000000000000030060850000100000100000000010000010000000000000100000000000000000000000583000004F00000000400000D802000000000000000000000000000000000000006000000C000000202F00001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000B0100000002000000012000000020000000000000000000000000000200000602E72737263000000D8020000004000000004000000140000000000000000000000000000400000402E72656C6F6300000C00000000600000000200000018000000000000000000000000000040000042000000000000000000000000000000008C30000000000000480000000200050034240000EC0A000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001B3005003003000001000011000F02280600000A2D090F02280700000A2B0572010000700A0F01280600000A2D090F01280700000A2B0572030000700B0F00280700000A0C7E0800000A0D0F03280600000A2D090F03280700000A2B05720B00007013040F04280600000A2D090F04280700000A2B05720100007013050F05280600000A2D090F05280700000A2B05720100007013060F07280600000A2D090F07280700000A2B05720100007013070F06280900000A2D090F06280A00000A2B011613080F08280B00000A2D090F08280C00000A2B021F1E13091104280D00000A130A110A2C07720B0000701304000008280E00000A740A000001130B110B076F0F00000A00110B110920E80300005A6F1000000A000872170000706F1100000A16FE0416FE01130C110C2C390017281200000A0020F00F0000281300000A007E02000004252D17267E01000004FE0605000006731400000A258002000004281500000A00001106280D00000A16FE01130D110D2C0C00110B11066F1600000A00001105280D00000A16FE01130E110E2C0C00110B11056F1700000A00001107280D00000A16FE01130F110F2C77001107178D1800000125161F7C9D6F1800000A131011108E6913111613122B4A00111011129A178D1400000125167229000070A2166F1900000A13131113169A6F1A00000A13141113179A6F1A00000A1315110B6F1B00000A111411156F1C00000A000011121758131211121111FE04131611162DAA001108131711172C02000007722F000070281D00000A2D0D077239000070281D00000A2C0B066F1E00000A16FE022B0116131811182C5100061319281F00000A11196F2000000A131A110B111A8E696A6F2100000A00110B6F2200000A131B00111B111A16111A8E696F2300000A00111B6F2400000A0000DE0D111B2C08111B6F2500000A00DC00110B6F2600000A740C000001131C00111C6F2700000A131D00111D14FE03131E111E2C2B00111D1104282800000A732900000A131F00111F6F2A00000A0D00DE0D111F2C08111F6F2500000A00DC0000DE0D111D2C08111D6F2500000A00DC00DE0D111C2C08111C6F2500000A00DC00DE1013200011206F2B00000A732C00000A7A0E0909282D00000A81070000012A417C000002000000770200001B000000920200000D0000000000000002000000D50200000C000000E10200000D0000000000000002000000B80200003A000000F20200000D0000000000000002000000AE02000054000000020300000D0000000000000000000000E30000002F02000012030000100000000E0000012202282E00000A002A2E730400000680010000042A00000013300100070000000200001100170A2B00062A0042534A4201000100000000000C00000076342E302E33303331390000000005006C00000070030000237E0000DC0300007805000023537472696E67730000000054090000440000002355530098090000100000002347554944000000A80900004401000023426C6F620000000000000002000001571502000902000000FA01330016000001000000200000000300000002000000050000000E0000002E0000000500000002000000010000000300000001000000000096020100000000000600B101EE030600D101EE0306008701DB030F000E0400000600C204FA020A009B01B3030A0023021D040A0006031D040A0023001D040E00FB04C9040600F30256000E002501C90406008703560006004E03FA0206006C01EE030E004A02420506004F0143040E00110343040E00B204420506002602FA020E00FF04C9040E009F03C9040E00F100C90406008203FA0206007A04FA020E002F03C9040E001B03A50006001A022A050600E500FA020E002901C90406009403560006004303FA02000000004D0000000000010001000100100032040000150001000100032110009D00000015000100030036003500DC0016000100E00050200000000096006703E40001000824000000008618CE0306000B001124000000009118D403FD000B000824000000008618CE0306000B0020240000000083000A0001010B0000000100D002000002007803000003008D0400000400580300000500E604000006000601000007006405000008009B04000009000A0502000A006000000001003900000002003E000000030043000000040048000900CE0301001100CE0306001900CE030A003100CE0306007900CE0306003900B0023D003900EF014100A1005E0545004100B0023D004100EF013D004900B0023D004900EF014800A10056054C00A9005F015100A900CE005700A9001E050100A1000F025C00B100F9016100B100BB0266008100CE036C00B1006E027200A900150157005100F0045700A100E0047800A100E0047F00A10001034100A900A6048800D900A1008D00A10036059300A1002D024800E1002C009900E10071049E00A9003802A400A900E902A90059006601AE00590041010600E90047010600A9003501B600F100D702A900E1001702BB006900CE03C100F900C40041007100D90041000101CE0357003900D404C9002900CE030600200023003D012E000B000C012E00130015012E001B00340163002B003D011000CF000480000000000000000000000000000000007E000000040000000000000000000000D300940000000000040000000000000000000000D300720000000000040000000000000000000000D300FA0200000000030002000000003C3E395F5F305F30003C73747057735F5265717569736963616F3E625F5F305F300053716C496E743332006765745F55544638003C3E39003C70303E003C70313E003C70323E003C70333E003C4D6F64756C653E0053797374656D2E494F0044735F5265746F726E6F5F4F55545055540053797374656D2E446174610053514C434C525F5265717569736963616F5F576562006D73636F726C6962003C3E63004164640053797374656D2E436F6C6C656374696F6E732E5370656369616C697A65640052656164546F456E64007365745F4D6574686F64006765745F4D6573736167650049446973706F7361626C6500536563757269747950726F746F636F6C547970650044735F436F6E74656E7454797065007365745F436F6E74656E74547970650048747470576562526573706F6E736500476574526573706F6E736500436C6F736500446973706F7365005835303943657274696669636174650043726561746500577269746500436F6D70696C657247656E6572617465644174747269627574650044656275676761626C654174747269627574650053716C50726F63656475726541747472696275746500436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465006765745F56616C7565007365745F457870656374313030436F6E74696E756500496E6465784F6600476574456E636F64696E670053716C537472696E67006765745F4C656E677468007365745F436F6E74656E744C656E6774680052656D6F7465436572746966696361746556616C69646174696F6E43616C6C6261636B007365745F536572766572436572746966696361746556616C69646174696F6E43616C6C6261636B0053514C434C525F5265717569736963616F5F5765622E646C6C006765745F49734E756C6C007365745F536563757269747950726F746F636F6C0044735F55726C00476574526573706F6E736553747265616D004765745265717565737453747265616D0053797374656D005472696D0053716C426F6F6C65616E0058353039436861696E004E616D6556616C7565436F6C6C656374696F6E00576562486561646572436F6C6C656374696F6E004170706C69636174696F6E457863657074696F6E0044735F436F64696669636163616F0073747057735F5265717569736963616F0044735F4D65746F646F00436861720053747265616D52656164657200546578745265616465720053657276696365506F696E744D616E61676572004D6963726F736F66742E53716C5365727665722E536572766572002E63746F72002E6363746F720053797374656D2E446961676E6F73746963730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300446562756767696E674D6F6465730053797374656D2E446174612E53716C54797065730053746F72656450726F636564757265730053797374656D2E53656375726974792E43727970746F6772617068792E5835303943657274696669636174657300476574427974657300537472696E6753706C69744F7074696F6E730044735F506172616D6574726F730044735F48656164657273006765745F486561646572730053736C506F6C6963794572726F7273004F626A6563740053797374656D2E4E6574006F705F496D706C696369740053706C69740044735F416363657074007365745F4163636570740048747470576562526571756573740051745F536567756E646F735F54696D656F7574007365745F54696D656F75740053797374656D2E54657874006F705F457175616C6974790053797374656D2E4E65742E53656375726974790049734E756C6C4F72456D70747900466C5F417574656E746963615F50726F7879000000010007470045005400000B5500540046002D0038000111680074007400700073003A002F002F0000053A003A00000950004F005300540000075000550054000000000091D3434B91A6944B9944F25BAF4B39B0000420010108032000010520010111112C07210E0E0E0E0E0E0E0E0208021229020202021D0E08081D0E0E0E0202020E1D05122D1231122D0212351239032000020320000E02060E03200008040001020E05000112550E042001010E042001080E040001010205000101115D052002011C180500010112410620011D0E1D030820021D0E1D0E11650420001269052002010E0E050002020E0E04000012710520011D050E042001010A042000122D072003011D050808042000127905000112710E07200201122D1271050001111D0E0307010208B77A5C561934E0890306120C0306124118000A01111D111D111D111D111D111D1121111D112510111D030000010A2004021C12451249114D0801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773010801000701000000000401000000000000000000EB85E45D00000000020000001C0100003C2F00003C110000525344538863FD7F0DBEED47BBE2FD478149B89501000000433A5C55736572735C6469726365755C736F757263655C7265706F735C53514C434C525F5265717569736963616F5F5765625C53514C434C525F5265717569736963616F5F5765625C6F626A5C44656275675C53514C434C525F5265717569736963616F5F5765622E70646200000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000008030000000000000000000009A3000000020000000000000000000000000000000000000000000008C300000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF2500200010000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001001000000018000080000000000000000000000000000001000100000030000080000000000000000000000000000001000000000048000000584000007C02000000000000000000007C0234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000000000000000000000000000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004DC010000010053007400720069006E006700460069006C00650049006E0066006F000000B801000001003000300030003000300034006200300000002C0002000100460069006C0065004400650073006300720069007000740069006F006E000000000020000000300008000100460069006C006500560065007200730069006F006E000000000030002E0030002E0030002E003000000054001A00010049006E007400650072006E0061006C004E0061006D0065000000530051004C0043004C0052005F005200650071007500690073006900630061006F005F005700650062002E0064006C006C0000002800020001004C006500670061006C0043006F0070007900720069006700680074000000200000005C001A0001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000530051004C0043004C0052005F005200650071007500690073006900630061006F005F005700650062002E0064006C006C000000340008000100500072006F006400750063007400560065007200730069006F006E00000030002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000030002E0030002E0030002E0030000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000003000000C000000AC3000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = EXTERNAL_ACCESS;
-- Adiciono o assembly como confiável
EXEC sys.sp_add_trusted_assembly @hash = @hash,
@description = @clrName;
-- Desativo o modo TRUSTWORTHY do banco por motivos de segurança
ALTER DATABASE Base_Teste SET TRUSTWORTHY OFF
GO
-- Crio a Stored Procedure "stpWs_Requisicao"
CREATE PROCEDURE [dbo].[stpWs_Requisicao] @Ds_Url NVARCHAR (MAX), @Ds_Metodo NVARCHAR (MAX), @Ds_Parametros NVARCHAR (MAX), @Ds_Codificacao NVARCHAR (MAX), @Ds_Accept NVARCHAR (MAX), @Ds_ContentType NVARCHAR (MAX), @Fl_Autentica_Proxy BIT, @Ds_Headers NVARCHAR (MAX), @Qt_Segundos_Timeout INT, @Ds_Retorno_OUTPUT NVARCHAR (MAX) OUTPUT
AS EXTERNAL NAME [SQLCLR_Requisicao_Web].[StoredProcedures].[stpWs_Requisicao]
Próximos pasos
Ahora que ha aprendido cómo realizar solicitudes desde la propia base de datos, puede implementar los siguientes SP en su base de datos, esta vez, utilizando el poder de CLR:
- Consumiendo la API de Google Maps usando OLE Automation en SQL Server
- Consultar el seguimiento de objetos de Correios utilizando SQL Server
- Consumir la API de Google Maps para obtener información de una dirección o código postal en SQL Server
El cambio es muy sencillo, sólo cambia estas instrucciones:
EXEC sys.sp_OACreate 'MSXML2.ServerXMLHTTP', @obj OUT
EXEC sys.sp_OAMethod @obj, 'open', NULL, 'GET', @Url, false
EXEC sys.sp_OAMethod @obj, 'send'
EXEC sys.sp_OAGetProperty @obj, 'responseText', @resposta OUT
EXEC sys.sp_OADestroy @obj
SET @xml = @resposta COLLATE SQL_Latin1_General_CP1251_CS_AS
para esto:
EXEC CLR.dbo.stpWs_Requisicao
@Ds_Url = @Url, -- nvarchar(max)
@Ds_Metodo = N'GET', -- nvarchar(max)
@Ds_Parametros = N'', -- nvarchar(max)
@Ds_Codificacao = N'ISO-8859-1', -- nvarchar(max)
@Ds_Retorno_OUTPUT = @Retorno OUT -- nvarchar(max)
SET @XML = CAST(@Retorno AS XML)
Mucho más sencillo, ¿verdad?
¡Un abrazo y nos vemos en el próximo post!
servidor sql clr integración integración publicación obtener solicitudes base de datos de solicitudes base de datos c# csharp sqlclr
servidor sql clr integración integración publicación obtener solicitudes base de datos de solicitudes base de datos c# csharp sqlclr
Dirceu Resende
Arquitecto de Bases de Datos y BI · Microsoft MVP · MCSE, MCSA, MCT, MTA, MCP
Comentarios (0)
Cargando comentarios...