How to you use dbmail with BCP
-
Hi Jeff, One last help if its possible for you,
Could you please let me know how we can get the below SP result in HTML.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date,,>
-- Description:<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[RPT_CardStats]
AS
BEGIN
CREATE TABLE #Results
(
WorkOrderID INT,
ProductID INT,
Name VARCHAR(50),
OrderQty INT,
DueDate DATETIME,
ExpectedRevenue DECIMAL(9,2)
)
selectdistinct
BIN,
card_program
,max(CARD_ISSUED_BUT_NOT_ACTIVE) as CARD_ISSUED_BUT_NOT_ACTIVE
,max(CADR_VALID_PIN_OFFSET_NULL) as CADR_VALID_PIN_OFFSET_NULL
,max(LOST_CARD) as LOST_CARD
,max(STOLEN_CARD) as STOLEN_CARD
,max(CLOSED_ACCOUNT) as CLOSED_ACCOUNT
,max(FAULTRY_CARD) as FAULTRY_CARD
,max(RETURNED_CARD) as RETURNED_CARD
,max(STOPPED_CARD) as STOPPED_CARD
,max(SUBSTITUTED_CARD) as SUBSTITUTED_CARD
,max(CARD_ISSUED_NOT_ACTIVE_PIN_OFFSET_NOT_NULL) as CARD_ISSUED_NOT_ACTIVE_PIN_OFFSET_NOT_NULL
from
(
selectsubstring(pan, 0, 7) BIN,
card_program
,isnull((select count(pan) where card_status=2),0) as 'CARD_ISSUED_BUT_NOT_ACTIVE'
,isnull((select count(pan) where card_status=3),0) as 'CADR_VALID_PIN_OFFSET_NULL'
,isnull((select count(pan) where card_status=0 and hold_rsp_code = '41'),0) as 'LOST_CARD'
,isnull((select count(pan) where card_status=0 and hold_rsp_code = '43'),0) as 'STOLEN_CARD'
,isnull((select count(pan) where card_status=0 and hold_rsp_code = '45'),0) as 'CLOSED_ACCOUNT'
,isnull((select count(pan) where card_status=0 and hold_rsp_code = '80'),0) as 'FAULTRY_CARD'
,isnull((select count(pan) where card_status=0 and hold_rsp_code = '81'),0) as 'RETURNED_CARD'
,isnull((select count(pan) where card_status=0 and hold_rsp_code = '57'),0) as 'STOPPED_CARD'
,isnull((select count(pan) where card_status=8 and hold_rsp_code is NULL),0) as 'SUBSTITUTED_CARD'
,isnull((select count(pan) where card_status=0 and hold_rsp_code is NULL),0) as 'CARD_ISSUED_NOT_ACTIVE_PIN_OFFSET_NOT_NULL'
from dbo.pc_cards
group by substring(pan, 0, 7), card_program,card_status,hold_rsp_code
) a
group by bin, card_program
END
-
I ran the demo code and it works as advertised (i.e. emails a file with an xls extension) but when I open the attachment in Excel 2010 I receive this popup:
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
Jeff Moden (4/8/2012)
Yes, we could use SSIS and all different manner of tools but, being the hardcore data-troll that I am, I'll use some hardcore T-SQL and a little XML magic, instead.😛 aww c'mon Jeff... I doubt I qualify for your definition of a hardcore data-troll but could there be at least one out there that uses SSIS?
If we're leveraging file extensions to fool Excel into opening a file for us then I think we have gone off the grid a bit. If you want to use Excel and you really do not want to mess with SSIS then the least we could do is use xp_cmdshell to create a copy of a staged Excel-"template" for ourselves and use OPENDATASOURCE with an Excel provider to populate it.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
opc.three (4/9/2012)
I ran the demo code and it works as advertised (i.e. emails a file with an xls extension) but when I open the attachment in Excel 2010 I receive this popup:Heh.. it's funny. It worked perfectly with Excel '97. 🙂 Guess it's a bit smarter than 2010.
BTW... what happened when you clicked the "Yes" button?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
opc.three (4/9/2012)
Jeff Moden (4/8/2012)
Yes, we could use SSIS and all different manner of tools but, being the hardcore data-troll that I am, I'll use some hardcore T-SQL and a little XML magic, instead.😛 aww c'mon Jeff... I doubt I qualify for your definition of a hardcore data-troll but could there be at least one out there that uses SSIS?
I gave up swearing for Lent and SSIS is a 4 letter word. 🙂
If someone would like to post the steps for doing this in SSIS, I wouldn't say a single word about it except "nice job".
....the least we could do is use xp_cmdshell to create a copy of a staged Excel-"template" for ourselves....
True enough. I was actually trying to avoid that because JET drivers don't work in a 64 bit environment and I've not yet taken the time to load the necessary ACE drivers.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply