Muy buen post.
http://www.mssqltips.com/sqlservertip/2783/script-to-create-dynamic-pivot-queries-in-sql-server/
En este ejemplo creamos el Stored Procedure : spHTTPRequest quien va a encargarse de ejecutar el Web Service que pasemos por parametro, devolviendo el XML de respuesta.
Text --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- CREATE proc [dbo].[spHTTPRequest] @URI varchar(2000) = '', @methodName varchar(50) = '', @requestBody varchar(8000) = '', @SoapAction varchar(255), @UserName nvarchar(100), -- Domain\UserName or UserName @Password nvarchar(100), @responseText varchar(8000) output as SET NOCOUNT ON IF @methodName = '' BEGIN select FailPoint = 'Method Name must be set' return END set @responseText = 'FAILED' DECLARE @objectID int DECLARE @hResult int DECLARE @source varchar(255), @desc varchar(255) EXEC @hResult = sp_OACreate 'MSXML2.ServerXMLHTTP', @objectID OUT IF @hResult <> 0 BEGIN EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT SELECT hResult = convert(varbinary(4), @hResult), source = @source, description = @desc, FailPoint = 'Create failed', MedthodName = @methodName goto destroy return END -- open the destination URI with Specified method EXEC @hResult = sp_OAMethod @objectID, 'open', null, @methodName, @URI, 'false', @UserName, @Password IF @hResult <> 0 BEGIN EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT SELECT hResult = convert(varbinary(4), @hResult), source = @source, description = @desc, FailPoint = 'Open failed', MedthodName = @methodName goto destroy return END -- set request headers EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'Content-Type', 'text/xml;charset=UTF-8' IF @hResult <> 0 BEGIN EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT SELECT hResult = convert(varbinary(4), @hResult), source = @source, description = @desc, FailPoint = 'SetRequestHeader failed', MedthodName = @methodName goto destroy return END -- set soap action EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'SOAPAction', @SoapAction IF @hResult <> 0 BEGIN EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT SELECT hResult = convert(varbinary(4), @hResult), source = @source, description = @desc, FailPoint = 'SetRequestHeader failed', MedthodName = @methodName goto destroy return END declare @len int set @len = len(@requestBody) EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'Content-Length', @len IF @hResult <> 0 BEGIN EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT SELECT hResult = convert(varbinary(4), @hResult), source = @source, description = @desc, FailPoint = 'SetRequestHeader failed', MedthodName = @methodName goto destroy return END -- send the request EXEC @hResult = sp_OAMethod @objectID, 'send', null, @requestBody IF @hResult <> 0 BEGIN EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT SELECT hResult = convert(varbinary(4), @hResult), source = @source, description = @desc, FailPoint = 'Send failed', MedthodName = @methodName goto destroy return END declare @statusText varchar(1000), @status varchar(1000) -- Get status text exec sp_OAGetProperty @objectID, 'StatusText', @statusText out exec sp_OAGetProperty @objectID, 'Status', @status out select @status, @statusText, @methodName -- Get response text exec sp_OAGetProperty @objectID, 'responseText', @responseText out IF @hResult <> 0 BEGIN EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT SELECT hResult = convert(varbinary(4), @hResult), source = @source, description = @desc, FailPoint = 'ResponseText failed', MedthodName = @methodName goto destroy return END destroy: exec sp_OADestroy @objectID SET NOCOUNT OFF
Este es el ejemplo de llamada para un determinado Web-Service de descarga de cartera de RSA.
DECLARE @xmlOut VARCHAR(8000) DECLARE @RequestText AS VARCHAR(8000); SET @RequestText= '<soap:Envelope xmlns:xsi=''http://www.w3.org/2001/XMLSchema-instance'' xmlns:xsd=''http://www.w3.org/2001/XMLSchema'' xmlns:soap=''http://schemas.xmlsoap.org/soap/envelope/''> <soap:Body> <DescargarCarteraRSA xmlns=''http://tempuri.org/'' /> </soap:Body> </soap:Envelope>' EXEC spHTTPRequest 'http://200.58.107.204/wsIntegracion/service.asmx', 'POST', @RequestText, 'http://tempuri.org/DescargarCarteraRSA', '', '', @xmlOut OUT
Entre todos los temas que producen degradación de performance, están los Convert Implicit.
Muchas veces, los desarrolladores omiten revisar correctamente los tipos de datos al momento de programar un stored procedure o función.
También los arquitectos y quizas, DBA’s poco experimentados, realizan cambios en el diseño físico de ciertas tablas sin revisar correctamente los objetos relacionados a la misma.
El siguiente script, detalla todos los Convert Implicit de la base de datos y su Execution Plan para verificar el costo del mismo una vez solucionado el problema.
Espero les sirva de ayuda y referencia.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED DECLARE @dbname SYSNAME SET @dbname = QUOTENAME(DB_NAME()); WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan') SELECT stmt.value('(@StatementText)[1]', 'varchar(max)'), t.value('(ScalarOperator/Identifier/ColumnReference/@Schema)[1]', 'varchar(128)'), t.value('(ScalarOperator/Identifier/ColumnReference/@Table)[1]', 'varchar(128)'), t.value('(ScalarOperator/Identifier/ColumnReference/@Column)[1]', 'varchar(128)'), ic.DATA_TYPE AS ConvertFrom, ic.CHARACTER_MAXIMUM_LENGTH AS ConvertFromLength, t.value('(@DataType)[1]', 'varchar(128)') AS ConvertTo, t.value('(@Length)[1]', 'int') AS ConvertToLength, query_plan FROM sys.dm_exec_cached_plans AS cp CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS batch(stmt) CROSS APPLY stmt.nodes('.//Convert[@Implicit="1"]') AS n(t) JOIN INFORMATION_SCHEMA.COLUMNS AS ic ON QUOTENAME(ic.TABLE_SCHEMA) = t.value('(ScalarOperator/Identifier/ColumnReference/@Schema)[1]', 'varchar(128)') AND QUOTENAME(ic.TABLE_NAME) = t.value('(ScalarOperator/Identifier/ColumnReference/@Table)[1]', 'varchar(128)') AND ic.COLUMN_NAME = t.value('(ScalarOperator/Identifier/ColumnReference/@Column)[1]', 'varchar(128)') WHERE t.exist('ScalarOperator/Identifier/ColumnReference[@Database=sql:variable("@dbname")][@Schema!="[sys]"]') = 1