Sending SQL Results in an email with tab deliminators, but varying field length is causing not to line up

I am sending the results of an SQL in the body of an email, using a script task.
This is working great, but since the fields are of varying length, by tab deliminators aren't keeping everything lined up.
Is there another way to format the data?  Is there a way to base the number of tabs on the length?  (I am new to this, so actual code would be appreciated)

This is what's being sent:
Order #                        Part #                        Qty BO/Received      
R101066254      DDE EA0034461002-CORE                        0      
X101253305      10R2602-CORE                        0            
X101253313      DDE R23535540-CORE                        0      
X101253315      3558045RX-CORE                        0
Tony OswaldSystems Admin Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
The only two things that come to mind..
Getting the length of values, and SPACE() the difference, to line things up.
Sending it Rich Text, and using HTML <table><tr><td> etc.  I can supply an example tonight if you want to go down that road.

Hope this helps.
Jim
0
Tony OswaldSystems Admin Author Commented:
HTML sounds like a better route...  an example would be great, thanks!!
0
ralmadaCommented:
To send this as HTML use the following:  (please adjust columns and table name)

declare @tableHTML nvarchar(max); 
 set @tableHTML = '<table border=1><th>Order #</th><th>Part #</th><th>Qty BO/Received</th>' 
select @tableHTML = @tableHTML + '<tr>' +
				'<td>' + Ordercolumn + '</td>' +
				'<td>' + Partcolumn + '</td>' + 
				'<td>' + cast(Qtycolumn as varchar(max)) + '</td>' + 

				'</tr>' 
from yourtable
 
set @tableHTML = @tableHTML + '</table>' 
 
select @tableHTML

Open in new window

0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Tony OswaldSystems Admin Author Commented:
is this in a script task?
new to this, a bit more clarification, please.
0
ralmadaCommented:
I think this will help with the implementation. Check the usage of the sp_send_dbmail in Todd's comment here:

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/d6ef3738-4cac-459c-82da-a068b0bbe615/need-to-email-results-of-sql-query-in-email-body-from-ssis-package
0
Tony OswaldSystems Admin Author Commented:
Need to do it in SSIS, because it's a reoccurring job, not a one-time thing...
0
ralmadaCommented:
Well, I thought you had your SSIS package created already, so I was just giving you the SQL script to get an HTML table.

If you're struggling with SSIS then I would suggest the following:

https://sites.google.com/site/alledocuments/sql/sending-scheduled-sql-query-results-via-html-e-mail-using-ssis
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Tony OswaldSystems Admin Author Commented:
I must not be following the link correctly, it sounds like I am to just create the html in sql on the server.  I need to do it in SSIS.

Yes, my package is already created, and working.

So, as I asked before, the HTML just goes into a script task?  Or where?
0
ralmadaCommented:
The link will explain you how to do that in SSIS. So it's different from the approach I was giving at the begining. I would strongly suggest you follow it, that will give you a good idea on how to do this.
0
Tony OswaldSystems Admin Author Commented:
where can I get the xsl file to be modifed?  I tried the link for the adventureworks, and couldn't find it.
0
ralmadaCommented:
This is coming from SQLServerCentral website as mentioned in my previous link:

http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/62678/

(You need to sign-up first, but it's free)

I've attached both files for your convenience
Orders.xsl
SQL-Query-for-Order-Totals.sql
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Here you go, sorry for the delay.

The below code is a scalar function that will create a single varchar with HTML-formatted table.  This is edited from a previous project to protect everything proprietary, so not all of the variables/tables will be of interest to you, but you'll get the idea T-SQL wise.

The image below that is a mockup of sample output when the above T-SQL is used as the body of an email.

if I get enough interest I may write an EE article on this methodology.

Hope this helps.
Jim
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER FUNCTION [dbo].[ufn_ssis_import_results_email_body](@id int) RETURNS varchar(8000) AS
begin

/*
Returns a string that reports the results of a sample SQL logging table

*** WARNING:  There is a 4,000 character limitation on SSIS for passing variables into the body of an email message ***

DECLARE @id int = 211
*/	 

-- TROUBLESHOOTING ONLY, set to 0 for production, 1 for extra troubleshooting sets. 
DECLARE @debug bit = 0

Declare @msg as varchar(8000)

-- Declare @cr varchar(1) = CHAR(10), @tab varchar(1) = CHAR(9)  -- Plain Text
Declare @cr varchar(4) = '<br>', @tab varchar(5) = '<tab>'


DECLARE @rows_source bigint, @rows_inserted bigint, @rows_updated bigint, @rows_deleted bigint, @rows_no_action bigint, @rows_failed_validation bigint
DECLARE @rows_source_c varchar(20), @rows_inserted_c varchar(20), @rows_updated_c varchar(20), @rows_deleted_c varchar(20), @rows_no_action_c varchar(20), @rows_failed_validation_c varchar(20)

DECLARE @step int, @name varchar(1000), @start_dt datetime, @end_dt datetime, @success bit, @organization_name varchar(30)

-- Get the ETL dates and success flag
SELECT @start_dt = start_dt, @end_dt = end_dt, @success = success_flg FROM LOG_EXECUTIONS WHERE id = @id

/*
-- Get the client name
SELECT @organization_name = COALESCE(o.organization_name, '{Client Name Not Available}') 
FROM D_ORGANIZATION o
	JOIN LOG_EXECUTIONS ex ON o.id = ex.organization_id
WHERE ex.id = @id
*/

SELECT @organization_name = 'Add Me'

/*
IF @debug = 1
	SELECT @start_dt AS start_dt, @success AS success FROM LOG_EXECUTIONS WHERE id = @id
*/

-- Message header 
IF @success = 1
	begin
	SELECT @msg = @organization_name + ' data files were successfully loaded into the STARS Dashboard on '+ CAST(@start_dt AS varchar(20)) + '.' + @cr
	END
ELSE
	BEGIN
	SELECT @msg = @organization_name + ' data files attempted to load into the STARS Dashboard on '+ CAST(@start_dt AS varchar(20)) + ', but an error occured:' + @cr	

	SELECT @msg = @msg + 'Message: ' + CAST(error_message AS varchar(500))
	FROM dbo.LOG_ERROR_LOG
	WHERE execution_id = @id

	GOTO ex

	END

/*
IF @debug = 1
	SELECT @msg
*/
	  
-- Get the total rows inserted/updated/etc. as a bigint. 
SELECT 
	@rows_source = SUM(COALESCE(rows_source,0)), 
	@rows_inserted = SUM(COALESCE(rows_inserted, 0)),
	@rows_updated = SUM(COALESCE(rows_updated, 0)),
	@rows_deleted = SUM(COALESCE(rows_deleted, 0)),
	@rows_no_action = SUM(COALESCE(rows_no_action, 0)), 
	@rows_failed_validation = SUM(COALESCE(rows_failed_validation,0))
FROM dbo.LOG_EXECUTION_STEPS 
WHERE execution_id = @id AND step >= 100

-- Get the above as a varchar, with thousands separators. 
SELECT 
	@rows_source_c = SUM(COALESCE(rows_source, 0)),
	@rows_inserted_c = SUM(COALESCE(rows_inserted, 0)),
	@rows_updated_c = SUM(COALESCE(rows_updated, 0)),
	@rows_deleted_c = SUM(COALESCE(rows_deleted, 0)),
	@rows_no_action_c = SUM(COALESCE(rows_no_action, 0)),
	@rows_failed_validation_c = SUM(COALESCE(rows_failed_validation, 0))
FROM dbo.LOG_EXECUTION_STEPS 
WHERE execution_id = @id AND step >= 100

/*
IF @debug = 1
	SELECT 'Variables', 
		@rows_source as rows_source, @rows_inserted AS rows_inserted, @rows_updated AS rows_updated, @rows_deleted AS rows_deleted, @rows_no_action AS rows_no_action, @rows_failed_validation AS rows_failed_validation,
		@rows_source_c as rows_source_c, @rows_inserted_c AS rows_inserted_c, @rows_updated_c AS rows_updated_c, @rows_deleted_c AS rows_deleted_c, @rows_no_action_c AS rows_no_action_c, @rows_failed_validation_c AS rows_failed_validation_c
*/

/*
IF @debug = 1
	SELECT @msg
*/

-- Details
SELECT @msg = @msg + @cr + '*** Details ***' + @cr

-- Get 
Declare @row_validation_first int, @row_staging_first int, @row_insert_first int, @rows_cleanup_first int

SELECT @row_validation_first = MIN(step) FROM LOG_EXECUTION_STEPS WHERE EXECUTION_ID = @id
SELECT @row_staging_first = MIN(step) FROM LOG_EXECUTION_STEPS WHERE EXECUTION_ID = @id AND step >= 50
SELECT @row_insert_first = MIN(step) FROM LOG_EXECUTION_STEPS WHERE EXECUTION_ID = @id AND step >= 100
SELECT @rows_cleanup_first = MIN(step) FROM LOG_EXECUTION_STEPS WHERE EXECUTION_ID = @id AND step >= 500

-- HEADER
SELECT @msg = @msg + '<table border="2" cellspacing="0" cellpadding="1">'
SELECT @msg = @msg + '<tr><th>Task Name</th><th>Rows in Source</th><th>Inserted</th><th>Updated</th><th>Deleted</th><th>Validation Failures</th><th>No Action</th><th>Load Time</th></tr>'

-- TOTALS ROW
SELECT @msg = @msg +
	'<tr>' +
	--'<td></td>' +
	'<td>TOTAL</td>' +
	'<td>' + @rows_source_c + '</td>' +
  	'<td>' + @rows_inserted_c + '</td>' +  
	'<td>' + @rows_updated_c + '</td>' +
	'<td>' + @rows_deleted_c + '</td>' +
	'<td>' + @rows_failed_validation_c  + '</td>' +
	'<td>' + @rows_no_action_c + '</td>' +
	'<td>' + DATEDIFF(d, @start_dt, @end_dt) + '</td>' + 
	'</tr>'


-- DETAIL ROWS
DECLARE cur CURSOR FOR 
SELECT step, name, rows_source, rows_inserted, rows_updated, rows_deleted, rows_no_action, rows_failed_validation, start_dt, end_dt
FROM SAMPLE_TABLE_NAME
WHERE execution_id = @id
ORDER BY id

OPEN cur

FETCH NEXT FROM cur 
INTO @step, @name, @rows_source, @rows_inserted, @rows_updated, @rows_deleted, @rows_no_action, @rows_failed_validation, @start_dt, @end_dt

WHILE @@FETCH_STATUS = 0
BEGIN

	-- Sub-row headers 
	IF @step = @row_validation_first
		SELECT @msg = @msg + '<tr><td align="center" colspan="8"><b>Validations</b></td></tr>'

	IF @step = @row_staging_first
		SELECT @msg = @msg + '<tr><td align="center" colspan="8"><b>Staging</b></td></tr>'

	IF @step = @row_insert_first
		SELECT @msg = @msg + '<tr><td align="center" colspan="8"><b>Insert</b></td></tr>'

	IF @step = @rows_cleanup_first
		SELECT @msg = @msg + '<tr><td align="center" colspan="8"><b>Cleanup</b></td></tr>'

	SELECT @msg = @msg + '<tr>'

	SELECT @msg = @msg +
		-- '<td>' + CAST(@step AS varchar(MAX)) + '</td>' +
		'<td>' + @name + '</td>' +
		'<td>' + @rows_source + '</td>' +
  		'<td>' + @rows_inserted + '</td>' +
		'<td>' + @rows_updated + '</td>' +
		'<td>' + @rows_deleted + '</td>' +
		'<td>' + @rows_failed_validation  + '</td>' +
		'<td>' + @rows_no_action + '</td>' +
		'<td>' + DATEDIFF(d, @start_dt, @end_dt) + '</td>'

	SELECT @msg = @msg + '</tr>'

    FETCH NEXT FROM cur 
    INTO @step, @name, @rows_source, @rows_inserted, @rows_updated, @rows_deleted, @rows_no_action, @rows_failed_validation, @start_dt, @end_dt
END 
CLOSE cur;
DEALLOCATE cur;

SELECT @msg = @msg + '</table>'

-- Make sure the return value is less than 4,000 characters
IF LEN(@msg) > 3990  
	SET @msg = LEFT(@msg, 3900) + '{error: The length of this message is greater than 4,000 characters, please contact support to fix.}'

ex:
	RETURN @msg
	end

--SELECT @msg
--GO

Open in new window


Output
0
Tony OswaldSystems Admin Author Commented:
Trying the XML & XSL, receiving an error...
[Execute SQL Task] Error: Executing the query "Select..." failed with the following error: "No mapping exists from DbType 130 to a known SqlDbType.".
Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Here are my settings:
 sql settings
0
ralmadaCommented:
check your query syntax. It might have a syntax error there.
0
Mark WillsTopic AdvisorCommented:
In the SQL Execute Task Editor, toward the bottom, it shows the SQL statement.

Try doing a copy and paste of that into a query window...

See what happens / what errors you get from there.

If you like, and it isn't too revealing, you could post it here so we can see it...
0
Tony OswaldSystems Admin Author Commented:
query works by itself, and puts it in XML format...

Select   Slsid,   Part,   QtyShip,   QtyBackorder,   Empid,    Date,   AlertType
From TC_Clean_Cores_Credited  
Where   DateDiff(hh,Date, GetDate()) <= 1 And
  brnid = ?
for xml auto, elements, type, root('Cores')

<Cores><TC_Clean_Cores_Credited><Slsid>X101253551:01</Slsid><Part>101F/TDA XK2124707QPMB-CORE</Part><QtyShip>0.000000000000000e+000</QtyShip><QtyBackorder>0.000000000000000e+000</QtyBackorder><Empid>1027</Empid><Date>2013-08-29T07:43:31.730</Date><AlertType>3</AlertType></TC_Clean_Cores_Credited>
...
</Cores>
0
ralmadaCommented:
what about the resultset property?, make sure it's aligned to your query.
0
Tony OswaldSystems Admin Author Commented:
result name = 0
variable name is a package variable varXML, defined as a string
0
Mark WillsTopic AdvisorCommented:
it is likely to be your parameter not being set correctly.

Using ADO it is very heavily type data and DBType 130 is basically a String data type and by the looks, brnid could be an int.

so, maybe test a couple of things...
1) check out how you have set up your parameters
2) could put a check of numeric in your where clause (or maybe try converting)
3) quickest and easiest test is to change that query (only for testing purposes) and put a literal digit or comment out that brnid=?

the last one is probably quickest and will isolate the param as being the problem, then you can track back from there.

And if using sql2012, not limited to 4000 as a max size anymore (noticed a comment above).
0
Tony OswaldSystems Admin Author Commented:
I did try removing the parameter, and received the same error...
Any other suggestions?
Any way to track what the actual problem is?
Again, it works fine just running the query on the server.
0
Mark WillsTopic AdvisorCommented:
Well, coming in late, I am not entirely sure how your SSIS package is constructed...

You say it was working, just a problem with tab spacing, so my impression was limited to how you might have changed - passing params / user defined function etc...

Now, if it is not the input param, then the query should be running and fails elsewhere. Do you know if the query actually executes ? (you can run step by step in SSIS)

So, what bits have you changed ? (sorry about that question)

It is still a mismatch of data type, so, it could also be the XML (output)
0
Tony OswaldSystems Admin Author Commented:
Can you post the conversion of the email script in C#?
0
Tony OswaldSystems Admin Author Commented:
how do I change the CSS to center or right justify just the quantity column?
0
ralmadaCommented:
I'm not sure those additional questions are related to your current problem. To be fair you should open another question for those.

In terms of the present question, I feel the link I've provided is what you're looking for. I would suggest you try it exactly as it is explained and then start making the changes you require to adapt it to your current environment.

http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/62678/
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.