Author |
Topic |
jp2code
Posting Yak Master
175 Posts |
Posted - 2008-09-25 : 15:59:17
|
How would I convert the results of a string query in a stored procedure to text?We made a crude SQL Mailer for failed parts. When we call it, we simply input the part number.What we want is for our mailer to collect the records for this part, and use this as the body in our text message.How would I convert the query result into a text format?(Once we get it working, we can start cleaning it up)Here's the short version of what I have now:CREATE PROCEDURE sp_sendMail (@From varchar(100), @To varchar(100), @Subject varchar(15)) AS---Declare @imsg intDeclare @hr intDeclare @source varchar(255)Declare @description varchar(500)Declare @body varchar(4000)---exec @body = sp_GetRecord @Subjectexec @hr = sp_OACreate 'cdo.message', @imsg outexec @hr = sp_OASetProperty @imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value', '2'exec @hr = sp_OASetProperty @imsg,'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', 'localhost'exec @hr = sp_OAMethod @imsg, 'configuration.fields.UPDATE', NULLexec @hr = sp_OASetProperty @imsg, 'To', @Toexec @hr = sp_OASetProperty @imsg, 'From', @Fromexec @hr = sp_OASetProperty @imsg, 'Subject', @Subjectexec @hr = sp_OASetProperty @imsg, 'TextBody', @body -- can replace HtmlBody with HtmlBodyexec @hr = sp_OAMethod @imsg, 'Send', NULLexec @hr = sp_OADestroy @imsg--GO Avoid Sears Home Improvement |
|
X002548
Not Just a Number
15586 Posts |
|
jp2code
Posting Yak Master
175 Posts |
Posted - 2008-09-26 : 09:17:14
|
See this line:exec @body = sp_GetRecord @Subject Now I know that @body is a varchar array and the sp_GetRecord query is returning ...well, not that! What does it return? A result set? A data table? Maybe if I knew what it returned, I'd know how to collect it. Avoid Sears Home Improvement |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
jp2code
Posting Yak Master
175 Posts |
Posted - 2008-09-26 : 12:40:41
|
No one seems to understand what I'm asking because no one there or here has shown me how to get the returned value from a query into a variable. All the example shows is how to get a count. I want all of the records returned in something like a string array.Using the result set in the link everyone keeps pointing me to:CREATE PROCEDURE dbo.GetPeopleByLastName (@LastName NVARCHAR(50))ASSELECT ContactID, FirstName, LastNameFROM Person.ContactWHERE LastName = @LastNameORDER BY ContactID Now, I want to get all of this data:EXEC dbo.GetPeopleByLastName @LastName = 'Alexander' And use it as a field in an email body that will be sent out.How do I turn the result into a text field?Does anyone understand what I want? If so, is it possible? Avoid Sears Home Improvement |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-09-26 : 12:46:17
|
quote: Originally posted by jp2code No one seems to understand what I'm asking because no one there or here has shown me how to get the returned value from a query into a variable. All the example shows is how to get a count. I want all of the records returned in something like a string array.Using the result set in the link everyone keeps pointing me to:CREATE PROCEDURE dbo.GetPeopleByLastName (@LastName NVARCHAR(50))ASSELECT ContactID, FirstName, LastNameFROM Person.ContactWHERE LastName = @LastNameORDER BY ContactID Now, I want to get all of this data:EXEC dbo.GetPeopleByLastName @LastName = 'Alexander' And use it as a field in an email body that will be sent out.How do I turn the result into a text field?Does anyone understand what I want? If so, is it possible? Avoid Sears Home Improvement
you mean all the fields as a single text?as comma seperated values?Also are you using sql 2005? |
|
|
jp2code
Posting Yak Master
175 Posts |
Posted - 2008-09-26 : 13:09:16
|
SQL 2000.Comma Seperated Values or Single Text - I don't care really. I just want to see what I can get it to return. It may look really bad, in which case we'd have to find another way - like passing in the complete table as the message body. Avoid Sears Home Improvement |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-09-26 : 13:32:37
|
is this what you want?CREATE FUNCTION dbo.GetPeopleByLastName (@LastName NVARCHAR(50))ASBEGINDECLARE @text VARCHAR(8000)SELECT @text = COALESCE(@strOutput + ',', '') + CAST(ContactID AS varchar(10)) + ','+FirstName + ',' + LastNameFROM Person.ContactWHERE LastName = @LastNameORDER BY ContactIDRETURN @text ENDGO then use it like thisDECLARE @receivestring varchar(8000)EXEC @receivestring = GetPeopleByLastName 'Alexander'and use @receivestring for sending mail |
|
|
jp2code
Posting Yak Master
175 Posts |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-09-27 : 01:46:18
|
quote: Originally posted by jp2code I think that's what I'm after. Now I have to find out how to make it work! Avoid Sears Home Improvement
why are you getting any error? |
|
|
|