June 28, 2007 at 9:04 am
I am trying to implement this trgger and keep getting the following error--Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
i am not sure what is causing this error; if i execute the query it works fine...
Please help as this is so new to me...
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
ALTER TRIGGER Trigger_Insert_TitleOrd ON [dbo].[TitleOrdered]
FOR INSERT
AS
declare @Casedatauid int
declare @tableHtml VARCHAR(4000);
SET @tableHTML =
N'<H1>Title Work Request</H1>' +
N'<table border="1">' +
N'<tr><th>FileNumber</th></tr>' +
N'<TR><th>ClientNumber</th></TR>' +
N'<TR><th>Client</th></TR></tr>' +
N'<TR><th>Address</th></tr>' +
N'<TR><th>CountName</th></tr>' +
cast(( SELECT FileNumber = cd.casedatauid, '',+'<BR>',
ClientNumber = cd.ClientNumber, '',+'<BR>',
Client = cl.ShortName , '',+'<BR>',
Address = p.Address1+ ' '+ p.City +' '+ p.StateCode+' '+ p.ZipCode, '',+'<BR>',
CountName = co.CountyName
FROM casedata cd
inner JOIN property p on p.casedatauid=cd.casedatauid
LEFT JOIN CLIENTS cl ON cl.ClientNumber = cd.ClientNumber
LEFT JOIN COUNTIES co ON co.CountyUId = cd.CountyUId
WHERE cd.CaseDataUId= @CaseDataUId
)AS VARCHAR(4000) ) +
N'</table>' ;
SELECT @tableHtml
declare @body varchar(3000)
SET @body = @tableHtml
EXEC --master..xp_sendmail
msdb.dbo.sp_send_dbmail
@recipients = 'bmoorjani@test.com',
@Copy_recipients ='bmoorjani@test.com',
@subject = 'Title Work Request',
@body =@tableHTML,
@body_format='HTML';
GO
June 28, 2007 at 12:12 pm
you have to many columns in your query and yet you are trying to assign them to @tableHTML
see my example below
----------------------
declare
@greeting varchar(12)
--this will work because I am returning one column
set
@greeting = (Select 'Hello World')
select
@greeting
--this will not work, because I am returning 2 columns
set
@greeting = (Select 'Hello','World')
select
@greeting
-----
Consider using a cursor to build your body
June 28, 2007 at 12:14 pm
or rather just concatenate your columns with out bothering with the cursor
----------------------------
SELECT
'FileNumber = '+ cd.casedatauid + '<BR>' + ' ClientNumber = '+ cd.ClientNumber +
'<BR> Client = ' + cl.ShortName +'<BR> Address = ' +p.Address1 + ' '+ p.City +' '+ p.StateCode+' '+ p.ZipCode '<BR> CountName = ' + co.CountyName
FROM casedata cd
inner JOIN property p on p.casedatauid=cd.casedatauid
LEFT JOIN CLIENTS cl ON cl.ClientNumber = cd.ClientNumber
LEFT JOIN COUNTIES co ON co.CountyUId = cd.CountyUId
WHERE cd.CaseDataUId= @CaseDataUId
-------------
I may have missed a + somewherem, but i hope you get the picture. return all as one column
June 28, 2007 at 1:21 pm
so how exactly would you include that in the body of the message??
June 28, 2007 at 2:19 pm
you can assign the string to another variable which you then add it to your final variable
set @details = SELECT 'FileNumber = '+ cd.casedatauid + '<BR>' + ' ClientNumber = '+ cd.ClientNumber +
'<BR> Client = ' + cl.ShortName +'<BR> Address = ' +p.Address1 + ' '+ p.City +' '+ p.StateCode+' '+ p.ZipCode '<BR> CountName = ' + co.CountyName
FROM casedata cd
inner JOIN property p on p.casedatauid=cd.casedatauid
LEFT JOIN CLIENTS cl ON cl.ClientNumber = cd.ClientNumber
LEFT JOIN COUNTIES co ON co.CountyUId = cd.CountyUId
WHERE cd.CaseDataUId= @CaseDataUId
-----
set @tableHTML = .... + @details + ..
June 29, 2007 at 8:29 am
this for sure is a rather hasty approach and it does'nt seem to work..
June 29, 2007 at 8:44 am
what do you mean by hasty approach, your aim is to return all the columns as one column so that you can assign them to your variable. did you get an error with the last script.
if you are returning many rows consider using a cursor to go through the rows. How many rows are you returning?
June 29, 2007 at 9:15 am
Your problem is your SELECT syntax in the nested query. Your syntax is trying to assign multiple variables (FileNumber, ClientNumber, Client, etc), which you cannot do in a subquery. I suspect you were trying to assign column names (not needed, but probably just to keep the code readable). I beleive you want to have something like "SELECT cd.casedatauid as FileNumber, ...".
Also, I think you are going to have a problem concatenating Unicode and ANSI strings. You want Unicode for sp_send_dbmail, so I beleive you want @tableHTML to be NVarchar. You are correct that the literals should be prefaced with N (like N'<table border="1">' ), but you need to make the CAST() also to NVarchar().
And I do not think that you want the local variable @body ("declare @body varchar(3000) SET @body = @tableHtml"), that may only confuse the argument in sp_send_dbmail. If you want it in another variable, I suggest renaming the @body variable to something other than a argument name, just for readability.
Hope this helps.
Mark
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply