Print Procedure Parameter

Question

Post reply

Print Procedure Parameter

dhlotter

SSC Veteran

Points: 278
More actions
July 2, 2010 at 4:10 am

#97389

Hi
I have an issue printing code from a procedure. When passing the parameter to sp_send_dbmail, I receive an email with all the information contained in the body text. See below code. I have a database mail account and profile that is the same as my servername
DECLARE @TABLEHTMLNVARCHAR(MAX),
@EMAILSUBJECTNVARCHAR(100),
@serverNVARCHAR(50),
@HEADERNVARCHAR(200)
SELECT @EMAILSUBJECT= @server + ' - Server Daily Health Checks'
SELECT @HEADER= CONVERT(NVARCHAR(30),GETDATE(),120) +'
'
--*************************** DATABASES
SELECT @TABLEHTML = '</table>' +
'
Databases
<table id="AutoNumber1" border="1" cellspacing="0" cellpadding="2" width="100%" id="AutoNumber1" style="BORDER-COLLAPSE: collapse" borderColor="#111111">
<tr>
<td bgColor="#000080">Database Name</td>
<td bgColor="#000080">Created By</td>
<td bgColor="#000080">Created Date</td>
<td bgColor="#000080">DB Size (Gb)</td>
<td bgColor="#000080">State</td>
<td bgColor="#000080">Recovery Model</td>
</tr>'
SELECT @TABLEHTML = @TABLEHTML +
CASE WHEN create_date > GETDATE()-1 THEN + '<td>'+ ISNULL('&RED ' + name, '') +'</td>' ELSE '<td>' + ISNULL('&GREEN ' + name, '') +'</td>' END +
'<td>' + SUSER_SNAME(owner_sid) +'</td>'+
'<td>' + CONVERT(VARCHAR(25),create_date,120) +'</td>' +
'<td>' + ISNULL(CONVERT(VARCHAR(10), AA.[Total SIZE GB]), '') +'</td>'+
'<td>' + ISNULL(state_desc, '') +'</td>'+
'<td>' + ISNULL(recovery_model_desc, '') +'</td></tr>'
FROM sys.databases MST
INNER JOIN (SELECT b.name [LOG_DBNAME], CONVERT(DECIMAL(22,2),SUM(CONVERT(DECIMAL(22,2),(a.size * 8)) /1024)/1024) [Total SIZE GB]
FROM sys.sysaltfiles A
INNER JOIN sys.databases B ON A.dbid = B.database_id
GROUP BY b.name) AA ON AA.[LOG_DBNAME] = MST.name
ORDER BY MST.name ASC
SELECT @TABLEHTML = @TABLEHTML + '</table>'
--PRINT @TABLEHTML
EXEC msdb.dbo.sp_send_dbmail
@profile_name= @@SERVERNAME,
@recipients= 'email@address.com',
@subject= @EMAILSUBJECT,
@body= @TABLEHTML,
@body_format= 'HTML' ;
The reason for this is to serve as some sort of health check. An example of the results are shown below, which turns out to be a nicely formatted HTML email containing this information.
</table>
Databases
<table id="AutoNumber1" border="1" cellspacing="0" cellpadding="2" width="100%" id="AutoNumber1" style="BORDER-COLLAPSE: collapse" borderColor="#111111">
<tr>
<td bgColor="#000080">Database Name</td>
<td bgColor="#000080">Created By</td>
<td bgColor="#000080">Created Date</td>
<td bgColor="#000080">DB Size (Gb)</td>
<td bgColor="#000080">State</td>
<td bgColor="#000080">Recovery Model</td>
</tr><td>&GREEN master</td><td>sa</td><td>2003-04-08 09:13:36</td><td>0.00</td><td>ONLINE</td><td>SIMPLE</td></tr><td>&GREEN model</td><td>sa</td><td>2003-04-08 09:13:36</td><td>0.00</td><td>ONLINE</td><td>FULL</td></tr><td>&GREEN msdb</td><td>sa</td><td>2010-04-02 17:35:08</td><td>0.01</td><td>ONLINE</td><td>SIMPLE</td></tr><td>&RED Nortwind</td><td>ZA\Hermann.Lotter</td><td>2010-07-02 11:45:33</td><td>0.00</td><td>ONLINE</td><td>FULL</td></tr><td>&RED tempdb</td><td>sa</td><td>2010-07-01 16:05:27</td><td>0.01</td><td>ONLINE</td><td>SIMPLE</td></tr><td>&RED Test</td><td>ZAame.surname</td><td>2010-07-02 11:45:15</td><td>0.00</td><td>ONLINE</td><td>FULL</td></tr></table>
The email part works fine no matter how many databases are attached to the server you run this on, but when I print the results (PRINT @TABLEHTML), SSMS truncates most of the code especially when it gets more than a certain amount.
Is this a variable that can be set? Or alternatively can I pass this parameter somehow to VBScript? (I unfortunately need to pass this to VBScript) Any ideas community? Thanks in advance for any suggestions.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

prashant.bhatt SSC-Addicted Points: 476 More actions · Answer 1

have u tried using the

SELECT @TABLEHTML

Replace the mail code withe the above statement and try executing the procedure

and check what u get

Prashant Bhatt
Sr Engineer - Application Programming

dhlotter SSC Veteran Points: 278 More actions · Answer 2

Hi Prashant

No idea why I did not think of that but it works fine. Thanks so much, I kinda feel like kicking myself for not thinking of something so simple.

Ciao

Lowell SSC Guru Points: 323518 More actions · Answer 3

but when I print the results (PRINT @TABLEHTML), SSMS truncates most of the code especially when it gets more than a certain amount.

this is a setting in SSMS; by default the longest string you can print is 256 chars until you change it:

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

dhlotter SSC Veteran Points: 278 More actions · Answer 4

and that is also what i was looking for. thanks so much all!