plain text mails

How can I set the default body_format in database mail. I would like to send a warning with plain text format (from the alerts), that i'll get on my mobile phone, but the message's format is html... always...and I don't get the sms-s.Hmm...I believe the default format is text. However, whenever you call sp_send_dbmail, you can specify the @.body_format param to be 'TEXT' so that you know it will be sent as text. To test, send a couple of test emails to an email account. If they are text, then you know you are on the right track. You could even put some html tags inside of the text body to ensure that it isn't formatted as html.|||i know this, but i would like to send an email from the ssms's GUI environment (alerts).
i get an error message...i can send email in plain text format only...but i can't modify the body_format value (in gui environment)...this is a smtp server problem ?...i don't know
) ... sorry my English is not too good...)
...just a comment, when i'm trying to send a mail from the database mail test ...it's success...but from the alert...unsuccess...|||One option is to run Profiler as you are trying to send an email from the GUI. That way, you can find out exactly what is being sent to the database. That may help.
Tim|||I traced "Send Test-Email" process.

I got this:

declare @.mailid int

EXECUTE [msdb].[dbo].[sp_send_dbmail]
@.profile_name = 'DBA'
,@.recipients = 'csaba.molnar_sms@.domainname.com'
,@.body = 'This is a test e-mail sent from Database Mail on FAR03.'
,@.subject = 'Database Mail Test'
,@.mailitem_id = @.mailid OUTPUT

select @.mailid

and I got a sms on my mobile...in unreadable format :
"VGhpcyBpcyBhIHRIc3QgZS1tYWIsIHNIbnQ..."blablabla

I think, th body_format value is wrong. Where can I set this value for the instance?

plain text mails

How can I set the default body_format in database mail. I would like to send a warning with plain text format (from the alerts), that i'll get on my mobile phone, but the message's format is html... always...and I don't get the sms-s.Hmm...I believe the default format is text. However, whenever you call sp_send_dbmail, you can specify the @.body_format param to be 'TEXT' so that you know it will be sent as text. To test, send a couple of test emails to an email account. If they are text, then you know you are on the right track. You could even put some html tags inside of the text body to ensure that it isn't formatted as html.|||i know this, but i would like to send an email from the ssms's GUI environment (alerts).
i get an error message...i can send email in plain text format only...but i can't modify the body_format value (in gui environment)...this is a smtp server problem ?...i don't know
) ... sorry my English is not too good...)
...just a comment, when i'm trying to send a mail from the database mail test ...it's success...but from the alert...unsuccess...|||One option is to run Profiler as you are trying to send an email from the GUI. That way, you can find out exactly what is being sent to the database. That may help.
Tim|||I traced "Send Test-Email" process.

I got this:

declare @.mailid int

EXECUTE [msdb].[dbo].[sp_send_dbmail]
@.profile_name = 'DBA'
,@.recipients = 'csaba.molnar_sms@.domainname.com'
,@.body = 'This is a test e-mail sent from Database Mail on FAR03.'
,@.subject = 'Database Mail Test'
,@.mailitem_id = @.mailid OUTPUT

select @.mailid

and I got a sms on my mobile...in unreadable format :
"VGhpcyBpcyBhIHRIc3QgZS1tYWIsIHNIbnQ..."blablabla

I think, th body_format value is wrong. Where can I set this value for the instance?

Pivoting currency text file

Hi!

I have a currency exchange rate flat file with this format:

date;USD;EUR;SEK;

01-01-2004;8.232;8.00;1.43;

02-01-2004;8.232;8.00;1.43;

..and so on.

I need to pivot this to:

01-01-2004;USD;8.232;

01-01-2004;EUR;8.00;

.. and so on..

Anyone got any tips on how to achieve this?

Try the UNPIVOT transformation.

It does exactly what you need to do. There is a useful walkthrough demo in BOL that explains what the UNPIVOT transformation does.

-Jamie

pivot table % and $ Formatting ?

Hi Guys

Just wondering after I've formatted certain facts say ie. Format String: Percentage and Format String: Currency all values are formatted correctly in AS however when I load the data in Excel pivot table all formatting is lost.

Is there anyway that I can allow formatting in pivot table ?

Thanks
TomSame problem here, and same interrogation.

Antoine.

pivot table % and $ Formatting ?

Hi Guys

Just wondering after I've formatted certain facts say ie. Format String: Percentage and Format String: Currency all values are formatted correctly in AS however when I load the data in Excel pivot table all formatting is lost.

Is there anyway that I can allow formatting in pivot table ?

Thanks
TomSame problem here, and same interrogation.

Antoine.

Pivot Query?

I have a table that's in the format:
ClientCode Form1 Form2 Form3
---
ABC 500 750 800
...
...
And I'm looking to rotate it so that I have the following fields:
ClientCode, FormType, Amount. However, the table currently has a lot of
data that will need to be moved to the new format. Ultimately I want to end
up with:
ClientCode FormType Amount
---
ABC Form1 500
ABC Form2 750
ABC Form3 800
I know there's a way to do this but I can't recall off the top of my head
the simplest way. Anyone have some direction for me? Thanks!Try this:
INSERT INTO NewTable (clientcode, formtype, amount)
SELECT clientcode, 'Form1', form1
FROM YourTable
WHERE form1 IS NOT NULL
UNION ALL
SELECT clientcode, 'Form2', form2
FROM YourTable
WHERE form2 IS NOT NULL
UNION ALL
SELECT clientcode, 'Form3', form3
FROM YourTable
WHERE form3 IS NOT NULL
--
David Portas
SQL Server MVP
--|||Exactly what I was looking for, thanks.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:k_idnTjk9PpqlpjcRVn-qQ@.giganews.com...
> Try this:
> INSERT INTO NewTable (clientcode, formtype, amount)
> SELECT clientcode, 'Form1', form1
> FROM YourTable
> WHERE form1 IS NOT NULL
> UNION ALL
> SELECT clientcode, 'Form2', form2
> FROM YourTable
> WHERE form2 IS NOT NULL
> UNION ALL
> SELECT clientcode, 'Form3', form3
> FROM YourTable
> WHERE form3 IS NOT NULL
> --
> David Portas
> SQL Server MVP
> --
>

Pivot Query Question - Can I have 2 columns in a Pivot?

I want two columns in Pivot format. Is it possible.

I have the following table:
CREATE TABLE CONDITION DETAILS
(
CONDITIONID INT,
NAME VARCHAR(100),
DESCRIPTION VARCHAR(50),
USERNAME VARCHAR(50),
NOTIFY_FREQUENCY VARCHAR(10))

INSERT INTO [CONDITION] ([CONDITIONID],[NAME],[DESCRIPTION],[USERNAME],[NOTIFY_FREQUENCY])VALUES(1,'Receipt of statements','First Person Notified','JC','Daily')
INSERT INTO [CONDITION] ([CONDITIONID],[NAME],[DESCRIPTION],[USERNAME],[NOTIFY_FREQUENCY])VALUES(1,'Receipt of statements','Second Person Notified','BG','Weekly')
INSERT INTO [CONDITION] ([CONDITIONID],[NAME],[DESCRIPTION],[USERNAME],[NOTIFY_FREQUENCY])VALUES(1,'Receipt of statements','Third Person Notified','BG','Monthly')

Now I have the following statement
SELECT * FROM
(SELECT CONDITIONID, NAME, DESCRIPTION, USERNAME
--, NOTIFY_FREQUENCY
FROM CONDITION) SOURCEQUERY
PIVOT (MIN(USERNAME) FOR [DESCRIPTION] IN ([First Person Notified], [Second Person Notified], [Third Person Notified])
) AS PIVOTTABLE

Output:
CONDITIONID NAME First Person Notified Second Person Notified Third Person Notified
1 Receipt of statements JC BG BG

I also want the Frequency to be displayed, I want output to be like
1 Receipt of Statements Daily JC Weekly BG Monthly BG

Can anyone suggest. Thanks.

The first thing that I want to do is to give kudos for such an outstanding job of perparing your question. Having the table definition and the insert statements for the test data made this much easier to set up for testing. Nice Work!

I have to confess that my work on this might not be as good of an effort as the question. I have never done a double-pivot so I really am not sure what is the best way to go about it. This seems to work. I would really like commentary from Umachandar or someone similar who is familar with a "best approach."

SELECT conditionId,
name,
rtrim(substring([First Person Notified], 11, 50)) as [1st Person],
rtrim(left([First Person Notified], 10)) as [1st Frequency],
rtrim(substring([Second Person Notified],11, 50)) as [2nd Person],
rtrim(left([Second Person Notified],10)) as [2nd Frequency],
rtrim(substring([Third Person Notified], 11, 50)) as [3rd Frequency],
rtrim(left([Third Person Notified],10)) as [3rd Person]
FROM ( SELECT CONDITIONID,
NAME,
DESCRIPTION,
cast (isnull(NOTIFY_FREQUENCY, '') as char(10))
+ username
as pivotStuff
FROM CONDITION
) SOURCEQUERY
PIVOT ( MIN(pivotStuff) FOR [DESCRIPTION]
IN ( [First Person Notified],
[Second Person Notified],
[Third Person Notified]
) ) AS PIVOTTABLE

-- conditionId name 1st Person 1st Frequency 2nd Person 2nd Frequency 3rd Frequency 3rd Person
-- -- - -- - -- - -- -
-- 1 Receipt of statements JC Daily BG Weekly BG Monthly

|||

Thanks. This sure works. Had thought about this solution but was not sure if it was right way to do, so had posted my query on to the forums.

Please let me know if there is a better way to achive the above results.