SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Return Query Results as string
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jp2code
Posting Yak Master

USA
175 Posts

Posted - 09/25/2008 :  15:59:17  Show Profile  Visit jp2code's Homepage  Reply with Quote
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 int
Declare @hr int
Declare @source varchar(255)
Declare @description varchar(500)
Declare @body varchar(4000)
---
exec @body = sp_GetRecord @Subject
exec @hr = sp_OACreate 'cdo.message', @imsg out
exec @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', NULL
exec @hr = sp_OASetProperty @imsg, 'To', @To
exec @hr = sp_OASetProperty @imsg, 'From', @From
exec @hr = sp_OASetProperty @imsg, 'Subject', @Subject
exec @hr = sp_OASetProperty @imsg, 'TextBody', @body -- can replace HtmlBody with HtmlBody
exec @hr = sp_OAMethod @imsg, 'Send', NULL
exec @hr = sp_OADestroy @imsg
--
GO



Avoid Sears Home Improvement

X002548
Not Just a Number

15586 Posts

Posted - 09/25/2008 :  16:14:59  Show Profile  Reply with Quote
ummm, where's the query?



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

jp2code
Posting Yak Master

USA
175 Posts

Posted - 09/26/2008 :  09:17:14  Show Profile  Visit jp2code's Homepage  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 09/26/2008 :  09:41:17  Show Profile  Visit SwePeso's Homepage  Reply with Quote
See this for further clues
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=111472



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

jp2code
Posting Yak Master

USA
175 Posts

Posted - 09/26/2008 :  12:40:41  Show Profile  Visit jp2code's Homepage  Reply with Quote
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))
AS
SELECT ContactID,
    FirstName,
    LastName
FROM Person.Contact
WHERE LastName = @LastName
ORDER 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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 09/26/2008 :  12:46:17  Show Profile  Reply with Quote
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))
AS
SELECT ContactID,
    FirstName,
    LastName
FROM Person.Contact
WHERE LastName = @LastName
ORDER 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?
Go to Top of Page

jp2code
Posting Yak Master

USA
175 Posts

Posted - 09/26/2008 :  13:09:16  Show Profile  Visit jp2code's Homepage  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 09/26/2008 :  13:32:37  Show Profile  Reply with Quote
is this what you want?
CREATE FUNCTION dbo.GetPeopleByLastName 
(@LastName NVARCHAR(50))
AS
BEGIN
DECLARE @text VARCHAR(8000)

SELECT @text = COALESCE(@strOutput + ',', '')  + CAST(ContactID AS varchar(10)) + ','+FirstName + ',' + LastName
FROM Person.Contact
WHERE LastName = @LastName
ORDER BY ContactID
RETURN @text 
END
GO


then use it like this

DECLARE @receivestring varchar(8000)

EXEC @receivestring = GetPeopleByLastName 'Alexander'

and use @receivestring for sending mail
Go to Top of Page

jp2code
Posting Yak Master

USA
175 Posts

Posted - 09/26/2008 :  15:36:28  Show Profile  Visit jp2code's Homepage  Reply with Quote
I think that's what I'm after. Now I have to find out how to make it work!


Avoid Sears Home Improvement
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 09/27/2008 :  01:46:18  Show Profile  Reply with Quote
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?
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000