Problemas con un Procedimiento Almac..

jmendezg · #1 (**permalink**) 02/03/2015, 14:52

Que tal,

tengo un problema que me esta rompiendo la cabeza, espero puedan orientarme....

Bueno hasta hace poco estuve viendo la forma de enviar correos "masivos" y dentro del correo enviar datos en una tabla construida mediante sql... Y me salio tal y como esperaba todo genial !!

PERO LO HICE EN UN AMBIENTE DE PRUEBA .. usando el AdventureWorks2008R2 ...

Desde ayer por la noche intente pasarlo a producción usando tablas de mi BD, sin embargo me eh topado con este problema... despues de analizar mucho ... llego a la conclusión que el problema esta en el procdimiento almacenado... veamos.

Procedmiento almacenado para el ambiente de pruebas, CORREO MASIVO

Código SQL:

Ver originalALTER PROCEDURE [dbo].[GetMailsToBeSent]
AS
BEGIN
DECLARE @contador INT
SET @contador = 1
while @contador  <= 10
BEGIN
DECLARE @fecha DATE
DECLARE @tableHTML  NVARCHAR(MAX) ;
SET @fecha=(SELECT CONVERT(VARCHAR(10),getDate(),120))
SET @tableHTML  =
   N'<head>' +
N'<style type="text/css">h2, body {font-family: Arial, verdana;} table{font-size:11px; border-collapse:collapse;} td{background-color:#F1F1F1; border:1px solid black; padding:3px;} th{background-color:#99CCFF;}</style>' +
   N'<h2><font color="#0000ff" size="4">Work Order Report  ' + CONVERT(VARCHAR,@contador)+ '</font></h2>'+
   N'</head>' +
N'<body>' +
N' <hr> ' +
N' ' +
    N'<table border="1">' +
    N'<tr><th>Work Order ID</th><th>Product ID</th>' +
    N'<th>Name</th><th>Order Qty</th><th>Due Date</th>' +
    N'<th>Expected Revenue</th>
    </tr>' +
    CAST ( ( SELECT top 20  td = wo.WorkOrderID,       '',
                    td = p.ProductID, '',
                    td = p.Name, '',
                    td = wo.OrderQty, '',
                    td = wo.DueDate, '',
                    td = (p.ListPrice - p.StandardCost) * wo.OrderQty
              FROM AdventureWorks2008R2.Production.WorkOrder AS wo
              JOIN AdventureWorks2008R2.Production.Product AS p
              ON wo.ProductID = p.ProductID
              WHERE p.ProductID = 722 + @contador
           --   WHERE DueDate > '2006-04-30'
           --     AND DATEDIFF(dd, '2006-04-30', DueDate) < 2 
              ORDER BY DueDate ASC,
                       (p.ListPrice - p.StandardCost) * wo.OrderQty DESC
              FOR XML PATH('tr'), TYPE 
    ) AS NVARCHAR(MAX) ) +
    N'</table>' ;
 
UPDATE MailsToBeSent SET  body = @tableHTML, fecha = @fecha WHERE MailID = @contador
SET @contador = @contador +1
END
   SELECT * FROM MailsToBeSent
 
END

Como pueden darse cuenta aquí uso hasta while y me ejecuta genial!!

Procedimiento almacenado para la BD produccion:

Código SQL:

Ver originalALTER  PROCEDURE proc_enviarCorreo2
AS
BEGIN
 
 
DECLARE @fecha DATE  
DECLARE @tableHTML  VARCHAR(4000) ;  
--set @fecha= (select CONVERT(VARCHAR(10), GETDATE(), 105))
SET @tableHTML  =  
   N'<head>' +  
N'<style type="text/css">h2, body {font-family: Arial, verdana;} table{font-size:11px; border-collapse:collapse;} td{background-color:#F1F1F1; border:1px solid black; padding:3px;} th{background-color:#99CCFF;}</style>' +  
   N'<h2><font color="#0000ff" size="4">Work Order Report   </font></h2>'+  
   N'</head>' +  
N'<body>' +  
N' <hr> ' +  
N' ' +  
    N'<table border="1">' +  
    N'<tr><th> ID</th><th> Fecha </th>' +  
    N'<th>Nombre de Base </th><th> Nombre Del Backup </th><th> Tipo</th>' +  
    N'
    </tr>' +  
    CAST ( ( SELECT top 20  td = wo.id,       ''
              --      td =  CONVERT(VARCHAR(10), fecha, 105) , '',  
             --       td = Base, '',  
              --      td = nombre_bk, '',  
        --            td = Tipo, ''
           FROM backups  AS wo  
          --    where CONVERT(VARCHAR(10), fecha, 105)  = @fecha
            FOR XML PATH('tr'), TYPE   
   ) AS NVARCHAR(MAX) ) +  
    N'</table>' ;  
--print @tableHTML
UPDATE EnviarCorreo  SET  body = @tableHTML
 
SELECT * FROM EnviarCorreo  
END

Despues de quitarle un monton de cosas para llegar en "donde" es el cuello de botella...
y llego a la conclusion que el cuello de botella es en la construcción de la variable @tableHTML, apesar que le pongo q jale solo un campoo... hasta le puse un indice a ese campo .... cuando lo ejecuto desde el SSIS simplemente cae. pero cuando uso el otro procedimiento de la otra BD en el mismo SSIS .. ejecuta sin problemas.

mi duda es.... estas funcionas requieren de algun tipo de configuracion o algo?
estoy pensando que quizás si... no lo se..

Código SQL:

Ver originalFOR XML PATH('tr'), TYPE   
   ) AS NVARCHAR(MAX) ) +

Alguna sugerencia, comentario u.u .. saludos,

Libras · #2 (**permalink**) 02/03/2015, 15:18

y cual es la causa de que caiga????

jmendezg · #3 (**permalink**) 02/03/2015, 15:35

Cita:

Iniciado por Libras

y cual es la causa de que caiga????

Me sale este error en el SSIS.
[Failure in Script Task while sending mails.] Error: Valor de tiempo de espera caducado. El período de tiempo de espera caducó antes de completar la operación o el servidor no responde.

Ahora ... si yo le quito esta parte a la variable @TABLEHTML

Código SQL:

Ver originalCAST ( ( SELECT top 20  td = wo.id,       '',  
              --      td =  CONVERT(VARCHAR(10), fecha, 105) , '',  
                    td = Base, '',  
                    td = nombre_bk, '',  
                    td = Tipo, ''
              FROM backups  AS wo  
          --    where CONVERT(VARCHAR(10), fecha, 105)  = @fecha
              FOR XML PATH('tr'), TYPE   
    ) AS NVARCHAR(MAX) ) +

Ejecuta normal, me envia correo.. pero sin datos.

Libras · #4 (**permalink**) 02/03/2015, 15:42

ya revisaste cual es tu tiempo de espera para los objetos de tu ssis???

jmendezg · #5 (**permalink**) 02/03/2015, 15:47

Cita:

Iniciado por Libras

[URL="https://social.msdn.microsoft.com/Forums/es-ES/5f51483f-906f-4bd5-91ae-91280240cadd/ejecutar-miles-de-sqls-sin-que-de-valor-de-tiempo-de-espera-caducado?forum=sqlserveres"]ya revisaste cual es tu tiempo de espera para los objetos de tu ssis???[/URL]

Sii, ya lo eh cambiado...

en realidad...de todas formas no debería tomar tanto tiempo ya que la tabla a la cual consulto solo tiene un registro (de prueba) ... ni uso while...

Sigo sin entender porque en el ambiente del adventureworks si ejecuta bien rapido .. con while y todo :(

Quiero agregar que si Ejecuto solo el TASK -SCRIPT - del SSIS ... se procesa correctamente ..

Libras · #6 (**permalink**) 02/03/2015, 15:56

esa parte de codigo, realmente lo que te hace es esto no:

<tr>id</tr>

porque no lo haces con un query simple?? porque la necesidad de meter funciones de xml???

jmendezg · #7 (**permalink**) 02/03/2015, 16:04

Cita:

Iniciado por Libras

esa parte de codigo, realmente lo que te hace es esto no:

<tr>id</tr>

porque no lo haces con un query simple?? porque la necesidad de meter funciones de xml???

Lo que sucede esque el XML (tengo entendido) que lo arma de esta forma:

Código HTML:

Ver original<tr><td>1</td><td>BD_</td><td>D:\Cargas\TRANSACCIONES\BD__backup_2015_02_24_105501_7416736.bak</td><td>DIFERENCIAL</td></tr>

Revisé varias formas de armar el HTML y todas las que vi eran de esa forma :/

Libras · #8 (**permalink**) 02/03/2015, 16:36

prueba con algo como esto:

Código SQL:

Ver originalSELECT 
'<tr><td>' + columna1 + '</td><td>' + columna2 + '</td><td>' + columnaN + '</td></tr>'
FROM tabla

y ve que resultado te arroja

P.S: No se ocupa configuracion especial para manejar tipos XML

jmendezg · #9 (**permalink**) 02/03/2015, 17:02

Cita:

Iniciado por Libras

prueba con algo como esto:

Código SQL:

Ver originalSELECT 
'<tr><td>' + columna1 + '</td><td>' + columna2 + '</td><td>' + columnaN + '</td></tr>'
FROM tabla

y ve que resultado te arroja

P.S: No se ocupa configuracion especial para manejar tipos XML

Amigo Libras, sale error lo que me dejaste...

aunque tú logica es muy buena.. pero el SQL no lo reconoce de tal forma //

Código SQL:

Ver originalMens. 207, Nivel 16, Estado 1, Línea 2
El nombre de columna 'id' no es válido.
Mens. 207, Nivel 16, Estado 1, Línea 2
El nombre de columna 'Base' no es válido.
Mens. 207, Nivel 16, Estado 1, Línea 2
El nombre de columna 'Tipo' no es válido.

Por otro lado para comentar que encontré el problema despues de muchos descartes... aunque es algo sin sentido..... Ejemplo en mi paquete ETL el envio de correos se encuentra en el ultimo paso ... ya que con eso confirmo que todo esta ok..

pero que sucede...si en la ETL uno de los pasos usa la "misma" conexion que usa el script para enviar correos.. SALE ERROR Y SE CAE... Si ninguno de ellos USA LA CONEXION... todo corre .. normal y ok.

es extraño... si alguien tiene una explicación logica.. seria genial porque tengo dudas... EL PROBLEMA ESTA RESULETO PERO NO DE LA MEJOR MANERA.

Gracias :D

Libras · #10 (**permalink**) 02/03/2015, 17:35

a que te refieres con la misma conexion???

jmendezg · #11 (**permalink**) 03/03/2015, 10:30

Cita:

Iniciado por Libras

a que te refieres con la misma conexion???

Pasa que en el SSIS tengo un Data source como conexion... y en el script tengo otro ya que ejecuto un procedimiento..

al parecer genera conflicto.. al conectar la misma Base de datos por Script cuando lo tengo en el Data Source.

Libras · #12 (**permalink**) 03/03/2015, 10:37

y no has probado usando 2 datasources diferentes????

jmendezg · #13 (**permalink**) 03/03/2015, 11:18

Cita:

Iniciado por Libras

y no has probado usando 2 datasources diferentes????

Lo que pasa que el Script Task no se conecta desde un datasource si no de una instancia que sea hace dentro del mismo..

Y sobre crear 2 datasources diferentes para que lo haría ? si al fin de cuentas ese data source estaria señalando a la misma BD.

Libras · #14 (**permalink**) 03/03/2015, 11:45

porque 2 datasources, quizas uno de tus datasources bloquea la base/tabla y por eso tienes el problema que mencionas