OUTLOOK message into SQL database

Hi Everybody,
I'm doing a program to generate newsletter and in turn need to read the boun
ced email message from OUTLOOK and store these data into SQL db. These bounc
ed email addresses will need to be collected and for further processing.
But different ISP has different format of presenting their bounced email for
mat, eg. some will included the bounced email address in the subject, some i
n the content and some as attachment. Has anyone done this before, extractin
g specified information from OUTLOOK message and update into SQL db?
Would appreciate if somebody can show me some light.
Thanks in advance.
KristeI didnt directly used Outlook, i used Exchange server to get my message fro
m the folder
and put it in the SQL Server DB. Would beinteresting if you also have the ch
ance to get to your inbox on a Exchange server or via IMAP or if you only ha
ve the change to get the information from a pop3 pop or just outlook.
If you were sending out the information from the Account SQL Server is using
for sending E-Mails the XP
xp_readmail would be a choice for you. It reads the inbox messages of the ac
coutn of SQL Server (Agent).
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Kriste L" <whiteegg@.hotmail.com> schrieb im Newsbeitrag news:uKLxqxUUFHA.28
92@.TK2MSFTNGP14.phx.gbl...
Hi Everybody,
I'm doing a program to generate newsletter and in turn need to read the boun
ced email message from OUTLOOK and store these data into SQL db. These bounc
ed email addresses will need to be collected and for further processing.
But different ISP has different format of presenting their bounced email for
mat, eg. some will included the bounced email address in the subject, some i
n the content and some as attachment. Has anyone done this before, extractin
g specified information from OUTLOOK message and update into SQL db?
Would appreciate if somebody can show me some light.
Thanks in advance.
Kriste

'Other' segment in a pie chart

Hello everybody,

I am creating a pie chart report from a cube. This report may contain unknown number of segments. Here is the thing; if more than 1 data slice is generated with a value less than 5% of the total, then a segment labelled 'other' will be generated, and data from all slices with value < 5% will be added to this 'Other' segment.

Is it possible to implement this functionality with out writing a complex MDX query?

If not can anybody give me a sample MDX query which implements similar issue(i.e. 'Other-ing' rule.)

For your information, this feature can be implemented using a third pary software such as 'Dundas chart for Reporting Service'.

Hi,

Does anybody has some idea?

--Amde

|||

This is answered here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=638700&SiteID=1&mode=1

-- Robert

'Other' segment in a pie chart

Hello everybody,

I am creating a pie chart report from a cube. This report may contain unknown number of segments. Here is the thing; if more than 1 data slice is generated with a value less than 5% of the total, then a segment labelled 'other' will be generated, and data from all slices with value < 5% will be added to this 'Other' segment.

Is it possible to implement this functionality with out writing a complex MDX query?

If not can anybody give me a sample MDX query which implements similar issue(i.e. 'Other-ing' rule.)

For your information, this feature can be implemented using a third pary software such as 'Dundas chart for Reporting Service'.

Hi,

Does anybody has some idea?

--Amde

|||

This is answered here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=638700&SiteID=1&mode=1

-- Robert

OSQL Output File Garbage

Everybody,

I've been doing a lot of on-line research and cannot find
any reference to the exact problem I'm having.

Let me preface this question with the fact that I'm coming
from an Oracle background so my approach may not be the best
way to tackle this. However, from the research I have done
this approach seems reasonable. Also, I know about the
undocumented procedure sp_MSforeachtable. That can give me a
result similar to what I'm looking for but the format of the
output is not what I need.

Now the problem. I'm trying to write a reusable script to give
me a list of all the tables in a database that have 1 or more rows.
My approach is to a BAT file (see script 1 below) that calls OSQL
twice, once to call a SQL script (see script 2 below) that uses the
Information_Schema views to generate the SELECT COUNT(*) statements
and fill in all the tables names in the database, write this to a
temporary output file and the second OSQL command to read the
temporary output file and generate me the results formatted the
way I need.

The result of the first OSQL run is correct EXCEPT for 1> 2> 3> 4> 5>
6> 7> 8> 9> 10> 11> 12> 13> garbage at the beginning of the file.
Because of this garbage the 2nd OSQL command blows up! Anyone have
any idea what is generating this garbage?

If I manually edit out the garbage and then just run the 2nd OSQL
command
I get similar garbage in the final result file (see 2nd result file
below).

In Query Analyzer, when I run the GET_TABLE_COUNT.SQL Script manually
then take its output and copy and paste it to a new query window and
run that it works OK except for generating lots of blank lines where
the result of the tables that have zero rows are. I am suppressing
headings but am still getting the blank lines but at least it works!

Any ideas anybody? Thanks For Any Help
FYI -- SQL Server 2000 with SP3a.
Bob

================== Script 1 - BAT File to Call OSQL ===============

@.echo off
@.echo ************************************************** *************
@.echo .
@.echo get_table_count.bat
@.echo .
@.echo Before you run this script change to the drive and directory
@.echo where the input SQL script is located!
@.echo .
@.echo Input parameters:
@.echo 1) SQL Server userid
@.echo .
@.echo You will be prompted twice for your password!
@.echo .
@.echo The output is written to file TABLE_COUNT_RESULT.TXT
@.echo .
@.echo ************************************************** *************
pause
osql -U %1 -S devkc-db -d C3T_Architecture -i get_table_count.sql -o
temp_table_count_query.txt -h-1 -w500
osql -U %1 -S devkc-db -d C3T_Architecture -i
temp_table_count_query.txt -o table_count_result.txt -h-1 -w500
del temp_table_count_result.txt
@.echo on

================================================== ====================

================ Script 2 - GET_TABLE_COUNT.SQL Script ===============

set nocount on
select 'set nocount on'
select 'select ''Table Name Count'''
select 'select ''========== ====='''
select 'select '''
+ table_name
+ ''', count(*) from '
+ table_name
+ ' having count(*) > 0 '
from information_schema.tables
where table_type = 'BASE TABLE'
order by table_name

================================================== ====================

============ Partial Result of 1st OSQL Run ==========================

1> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> set nocount on

select 'Table Name Count'

select '========== ====='

select 'ACT_ASSERTION_RULE', count(*) from ACT_ASSERTION_RULE having
count(*) > 0
select 'ACT_ASSOC', count(*) from ACT_ASSOC having count(*) > 0
select 'ACT_DOC', count(*) from ACT_DOC having count(*) > 0

================================================== ====================

============ Partial Result of @.nd OSQL Run ==========================

1> 2> 3> 4> ... I edited out the intervening numbers for this message
... 664> 665> 666> 667> Table Name Count

========== =====

... I edited out lots of blank lines in the result for this message
before I get to the first table with 1 or more rows ...

ARCH 6

================================================== ====================You can remove numbering with the '-n' OSQL parameter.

However, you might consider using dynamic SQL to accomplish the task.
Example below:

SET NOCOUNT ON
CREATE TABLE #TableRowCounts
(
TableName nvarchar(261) NOT NULL,
TableRowCount bigint NOT NULL
)
DECLARE
@.TableName nvarchar(261),
@.SqlStatement nvarchar(500)
DECLARE TableList CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
SELECT
QUOTENAME(TABLE_SCHEMA) +
'.' +
QUOTENAME(TABLE_NAME) AS TableName
FROM INFORMATION_SCHEMA.TABLES
WHERE OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) +
'.' +
QUOTENAME(TABLE_NAME)),
'IsMSShipped') = 0
OPEN TableList
WHILE 1 = 1
BEGIN
FETCH NEXT FROM TableList INTO @.TableName
IF @.@.FETCH_STATUS = -1 BREAK
SET @.SqlStatement =
N'INSERT INTO #TableRowCounts
SELECT ''' + @.TableName + N''', COUNT(*)
FROM ' + @.TableName + N' WITH (NOLOCK)'
EXEC (@.SqlStatement)
END
CLOSE TableList
DEALLOCATE TableList

SELECT *
FROM #TableRowCounts
WHERE TableRowCount > 0
ORDER BY TableName

--
Hope this helps.

Dan Guzman
SQL Server MVP

--------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--------

"Bob" <rms@.robertsiegel.net> wrote in message
news:91f443f8.0311141523.10db27e5@.posting.google.c om...
> Everybody,
> I've been doing a lot of on-line research and cannot find
> any reference to the exact problem I'm having.
> Let me preface this question with the fact that I'm coming
> from an Oracle background so my approach may not be the best
> way to tackle this. However, from the research I have done
> this approach seems reasonable. Also, I know about the
> undocumented procedure sp_MSforeachtable. That can give me a
> result similar to what I'm looking for but the format of the
> output is not what I need.
> Now the problem. I'm trying to write a reusable script to give
> me a list of all the tables in a database that have 1 or more rows.
> My approach is to a BAT file (see script 1 below) that calls OSQL
> twice, once to call a SQL script (see script 2 below) that uses the
> Information_Schema views to generate the SELECT COUNT(*) statements
> and fill in all the tables names in the database, write this to a
> temporary output file and the second OSQL command to read the
> temporary output file and generate me the results formatted the
> way I need.
> The result of the first OSQL run is correct EXCEPT for 1> 2> 3> 4> 5>
> 6> 7> 8> 9> 10> 11> 12> 13> garbage at the beginning of the file.
> Because of this garbage the 2nd OSQL command blows up! Anyone have
> any idea what is generating this garbage?
> If I manually edit out the garbage and then just run the 2nd OSQL
> command
> I get similar garbage in the final result file (see 2nd result file
> below).
> In Query Analyzer, when I run the GET_TABLE_COUNT.SQL Script manually
> then take its output and copy and paste it to a new query window and
> run that it works OK except for generating lots of blank lines where
> the result of the tables that have zero rows are. I am suppressing
> headings but am still getting the blank lines but at least it works!
> Any ideas anybody? Thanks For Any Help
> FYI -- SQL Server 2000 with SP3a.
> Bob
> ================== Script 1 - BAT File to Call OSQL ===============
> @.echo off
> @.echo ************************************************** *************
> @.echo .
> @.echo get_table_count.bat
> @.echo .
> @.echo Before you run this script change to the drive and directory
> @.echo where the input SQL script is located!
> @.echo .
> @.echo Input parameters:
> @.echo 1) SQL Server userid
> @.echo .
> @.echo You will be prompted twice for your password!
> @.echo .
> @.echo The output is written to file TABLE_COUNT_RESULT.TXT
> @.echo .
> @.echo ************************************************** *************
> pause
> osql -U %1 -S devkc-db -d C3T_Architecture -i get_table_count.sql -o
> temp_table_count_query.txt -h-1 -w500
> osql -U %1 -S devkc-db -d C3T_Architecture -i
> temp_table_count_query.txt -o table_count_result.txt -h-1 -w500
> del temp_table_count_result.txt
> @.echo on
> ================================================== ====================
> ================ Script 2 - GET_TABLE_COUNT.SQL Script ===============
> set nocount on
> select 'set nocount on'
> select 'select ''Table Name Count'''
> select 'select ''========== ====='''
> select 'select '''
> + table_name
> + ''', count(*) from '
> + table_name
> + ' having count(*) > 0 '
> from information_schema.tables
> where table_type = 'BASE TABLE'
> order by table_name
> ================================================== ====================
>
> ============ Partial Result of 1st OSQL Run ==========================
> 1> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> set nocount on
> select 'Table Name Count'
> select '========== ====='
> select 'ACT_ASSERTION_RULE', count(*) from ACT_ASSERTION_RULE having
> count(*) > 0
> select 'ACT_ASSOC', count(*) from ACT_ASSOC having count(*) > 0
> select 'ACT_DOC', count(*) from ACT_DOC having count(*) > 0
> ================================================== ====================
>
> ============ Partial Result of @.nd OSQL Run ==========================
> 1> 2> 3> 4> ... I edited out the intervening numbers for this message
> ... 664> 665> 666> 667> Table Name Count
> ========== =====
> ... I edited out lots of blank lines in the result for this message
> before I get to the first table with 1 or more rows ...
> ARCH 6
> ================================================== ====================|||Dan,

Thanks for the answers. I completely missed the -n option in the BOL.

I also like your alternative. Someone I work with suggested using a
temporary table then just selecting what I want but your approach
seems even more sophicated. I'm out of the office today so haven't
had a chance to try either answer but will as soon as possible.

Thanks so much,
Bob

"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message news:<Rkftb.917$Rk5.180@.newsread1.news.atl.earthlink.net >...
> You can remove numbering with the '-n' OSQL parameter.
> However, you might consider using dynamic SQL to accomplish the task.
> Example below:
> SET NOCOUNT ON
> CREATE TABLE #TableRowCounts
> (
> TableName nvarchar(261) NOT NULL,
> TableRowCount bigint NOT NULL
> )
> DECLARE
> @.TableName nvarchar(261),
> @.SqlStatement nvarchar(500)
> DECLARE TableList CURSOR
> LOCAL FAST_FORWARD READ_ONLY FOR
> SELECT
> QUOTENAME(TABLE_SCHEMA) +
> '.' +
> QUOTENAME(TABLE_NAME) AS TableName
> FROM INFORMATION_SCHEMA.TABLES
> WHERE OBJECTPROPERTY(
> OBJECT_ID(
> QUOTENAME(TABLE_SCHEMA) +
> '.' +
> QUOTENAME(TABLE_NAME)),
> 'IsMSShipped') = 0
> OPEN TableList
> WHILE 1 = 1
> BEGIN
> FETCH NEXT FROM TableList INTO @.TableName
> IF @.@.FETCH_STATUS = -1 BREAK
> SET @.SqlStatement =
> N'INSERT INTO #TableRowCounts
> SELECT ''' + @.TableName + N''', COUNT(*)
> FROM ' + @.TableName + N' WITH (NOLOCK)'
> EXEC (@.SqlStatement)
> END
> CLOSE TableList
> DEALLOCATE TableList
> SELECT *
> FROM #TableRowCounts
> WHERE TableRowCount > 0
> ORDER BY TableName
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> --------
> SQL FAQ links (courtesy Neil Pike):
> http://www.ntfaq.com/Articles/Index...epartmentID=800
> http://www.sqlserverfaq.com
> http://www.mssqlserver.com/faq
> --------