good morning I have a problem when trying to send a variable from the @ query, this variable is sent by a trigger, the variable goes well with the procedure, the print and show what I send, the problem occurs when I try to take that variable to select not I take the variable, just take the value of what I write ...
The mail is working fine without problems if you only write text .. I also take data from a normal query, but if I include a variable fails.
I chose to put the variable in the select so '+ @ q +' and displays the error Msg 102, Level 15, State 1, Procedure Facturacion_Tope, Line 29 Incorrect syntax near '+'.
and remove the single quote symbol + and is not a fixed value and variable
code attached
error 209 --prints well 209 - prints well HAY VA --prints well 209 --prints well Msg 22050, Level 16, State 1, Line 0 Error formatting query, probably invalid parameters Doomed Error 14661 on line 504, Query execution failed: Msg 137, Level 15, State 2, Server CDPALWIN01\CDPALSQL02, Line 1 Must declare the scalar variable "@q". Msg 3903, Level 16, State 1, Procedure DEVDATFAC, Line 180 The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. The statement has been terminated.
USE [sistemas] GO /** Object: StoredProcedure [dbo].[Facturacion_Tope] Script Date: 11/15/2012 07:32:29 **/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
ALTER PROCEDURE [dbo].[Facturacion_Tope]
@ENVIO INT
AS
print @ENVIO
BEGIN
SET NOCOUNT ON;
Declare @q INT
--here shows the value of consultation print @ENVIO
set @q = (select OID from sistemas..DATFACTUR_DINAMICA WHERE OID = @ENVIO)
--here shows the value of consultation print @q
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'correo_sistemas',
@recipients = '[email protected]',
-- @execute_query_database = 'sistemas',
@query = 'select * from sistemas..DATFACTUR_DINAMICA WHERE OID = "@q"',
@query_attachment_filename = 'Consulta.txt',
-- @body = 'Caida en: '@query+'',
@body_format = 'HTML',
@subject = 'Numero de factura';
END
