August 31, 2007 at 2:32 am
I am building a TSQL Job procedure to send large messages with SQL2000 xp_sendmail. The output text size is about 8000-9000 chars. I am having problems making either of the two BooksOnline examples work, because I can't see how to use a text type variable:
(1)BooksOnline gives an example write to an attached file using @attach_results and @query. Problem is my @query is more than a simple SELECT, I want to reformat the results using CAST, spaces,etc to produce nice neat columns. How do I get this to work? I can't store the concatination result in a text as you can't use text type for a local variable.
(2)BOL gives another example purportedly avoiding VARCHAR limitations by writing to a temporary table. It contains the line
INSERT ##texttab values ('Put your long message here.')
...but how do I get my concatinated, reformatted text results in place of 'Put your long message here.' ?
I don't care which method I use, I would appreciate any help with the precise syntax to achieve this. Is there a third method?
Many thanks, Rob Pearce
August 31, 2007 at 6:42 am
Hi Robin,
it is probably worth posting this question to the SQL Server 2000 forums (http://www.sqlservercentral.com/forums/messages.aspx?forumid=9)
This one is for SQL Server 2008 (Katmai). Also, since I assume that eventually your 2000 database will be migrated to 2008, it is worth noting that xp_sendmail will be likely removed from SQL Server 2008, and it may be worth exploring alternatives.
Regards,
Andras
August 31, 2007 at 8:27 am
The way to do it would be to insert rows into the temp table and manipulate them, then select out all the rows as your message. Not sure how this will work in text, but it worked in HTML.
We used to build a report as
insert MyReportTable select '
insert MyReportTable select '
'
insert MyReportTable select '
' + CustomerSalesID + '
'
from MYSales
...
Etc. We'd just select * from MyReportTable for the final report and drop that into a message.
September 3, 2007 at 3:35 am
Thanks for your input Steve, I tried the following:
SET LANGUAGE British
GO
DECLARE @msgstr VARCHAR(80)
DECLARE @cmd VARCHAR(80)
DECLARE @PMID INT
DECLARE @forename VARCHAR(30)
CREATE TABLE ##texttab (c1 text)
SET @msgstr = 'THE FOLLOWING QUOTES ARE CURRENTLY MARKED AS PENDING:'
INSERT ##texttab SELECT @msgstr
DECLARE C2 CURSOR FOR SELECT ProjMgrID FROM surdba.SVY_QUOTES WHERE StatusID=6
OPEN C2
FETCH NEXT FROM C2 INTO @PMID
WHILE @@FETCH_STATUS = 0
BEGIN
IF @PMID > 1000
SELECT @forename = ISNULL(Forename,' ') FROM surdba.SVY_PERSONNEL_GENERAL WHERE EmployeeID=@PMID
ELSE
SET @forename = ' '
INSERT ##texttab values (RTRIM(@forename))
FETCH NEXT FROM C2 INTO @PMID
END
CLOSE C2
DEALLOCATE C2
SET @msgstr = @msgstr + CHAR(13) + ' - This information is autogenerated from the Survey database.'
SET @cmd = 'SELECT * FROM ##texttab'
EXEC master.dbo.xp_sendmail @recipients = 'Robin Pearce',
@subject = 'ALL PENDING QUOTES',
@query = @cmd,
@no_header = 'TRUE'
DROP TABLE ##texttab
GO
This seems to try writing out the data which should be around 80 records but I get about a page of blank spaces after each Insert, followed by error message:
"[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead (WrapperRead()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.Connection Broken"
...which is presumably text capacity blowout(?). Can you see where I have gone wrong? I want to get the text working, unfortunately I don't have time to learn HTML!
Many thanks for your help,
Robin Pearce
September 3, 2007 at 3:40 am
Update: I exchanged "EmployeeID=@PMID" for "EmployeeID = @PMID". The error message has gone but I still get the blank paging with no sign of my footnote "This information is..."
Robin
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply