Processing Queries via Email

In SQL 2000, we processed queries and return results to user. In SQL 2005, we
have not been successful in doing so. I do not see any way using DB Mail to
process incoming mail.
We have set up SQL Mail and are able to send mail with no problem. We had
planned to upgrade servers from SQL 2000 to SQL 2005 and use SQL Mail until
we could migrate to whatever process would provide the same functionality.
So, two questions:
1. Has anyone been successful using SQL Mail on SQL 2005 to process incoming
queries and returning results to user?
2. What provides the equivalent functionality in SQL 2005? Is it done via
Reporting Service?
If someone can point me in the right direction for either question, I would
be very grateful.
Hi Roger
AFAIK there is no method of reading a mailbox with database mail, therefore
you would have to retain your current process of reading the mailbox or
possibly use the CLR to do this (for example see
http://support.microsoft.com/kb/813349 ). You could use a different (possibly
more reliable) method of delivery such as a web service instead which can be
hosted by the database server.
John
"RogerT" wrote:

> In SQL 2000, we processed queries and return results to user. In SQL 2005, we
> have not been successful in doing so. I do not see any way using DB Mail to
> process incoming mail.
> We have set up SQL Mail and are able to send mail with no problem. We had
> planned to upgrade servers from SQL 2000 to SQL 2005 and use SQL Mail until
> we could migrate to whatever process would provide the same functionality.
> So, two questions:
> 1. Has anyone been successful using SQL Mail on SQL 2005 to process incoming
> queries and returning results to user?
> 2. What provides the equivalent functionality in SQL 2005? Is it done via
> Reporting Service?
> If someone can point me in the right direction for either question, I would
> be very grateful.

Processing Queries via Email

In SQL 2000, we processed queries and return results to user. In SQL 2005, w
e
have not been successful in doing so. I do not see any way using DB Mail to
process incoming mail.
We have set up SQL Mail and are able to send mail with no problem. We had
planned to upgrade servers from SQL 2000 to SQL 2005 and use SQL Mail until
we could migrate to whatever process would provide the same functionality.
So, two questions:
1. Has anyone been successful using SQL Mail on SQL 2005 to process incoming
queries and returning results to user?
2. What provides the equivalent functionality in SQL 2005? Is it done via
Reporting Service?
If someone can point me in the right direction for either question, I would
be very grateful.Hi Roger
AFAIK there is no method of reading a mailbox with database mail, therefore
you would have to retain your current process of reading the mailbox or
possibly use the CLR to do this (for example see
http://support.microsoft.com/kb/813349 ). You could use a different (possibl
y
more reliable) method of delivery such as a web service instead which can be
hosted by the database server.
John
"RogerT" wrote:

> In SQL 2000, we processed queries and return results to user. In SQL 2005,
we
> have not been successful in doing so. I do not see any way using DB Mail t
o
> process incoming mail.
> We have set up SQL Mail and are able to send mail with no problem. We had
> planned to upgrade servers from SQL 2000 to SQL 2005 and use SQL Mail unti
l
> we could migrate to whatever process would provide the same functionality.
> So, two questions:
> 1. Has anyone been successful using SQL Mail on SQL 2005 to process incomi
ng
> queries and returning results to user?
> 2. What provides the equivalent functionality in SQL 2005? Is it done via
> Reporting Service?
> If someone can point me in the right direction for either question, I woul
d
> be very grateful.

Processing Queries via Email

In SQL 2000, we processed queries and return results to user. In SQL 2005, we
have not been successful in doing so. I do not see any way using DB Mail to
process incoming mail.
We have set up SQL Mail and are able to send mail with no problem. We had
planned to upgrade servers from SQL 2000 to SQL 2005 and use SQL Mail until
we could migrate to whatever process would provide the same functionality.
So, two questions:
1. Has anyone been successful using SQL Mail on SQL 2005 to process incoming
queries and returning results to user?
2. What provides the equivalent functionality in SQL 2005? Is it done via
Reporting Service?
If someone can point me in the right direction for either question, I would
be very grateful.Hi Roger
AFAIK there is no method of reading a mailbox with database mail, therefore
you would have to retain your current process of reading the mailbox or
possibly use the CLR to do this (for example see
http://support.microsoft.com/kb/813349 ). You could use a different (possibly
more reliable) method of delivery such as a web service instead which can be
hosted by the database server.
John
"RogerT" wrote:
> In SQL 2000, we processed queries and return results to user. In SQL 2005, we
> have not been successful in doing so. I do not see any way using DB Mail to
> process incoming mail.
> We have set up SQL Mail and are able to send mail with no problem. We had
> planned to upgrade servers from SQL 2000 to SQL 2005 and use SQL Mail until
> we could migrate to whatever process would provide the same functionality.
> So, two questions:
> 1. Has anyone been successful using SQL Mail on SQL 2005 to process incoming
> queries and returning results to user?
> 2. What provides the equivalent functionality in SQL 2005? Is it done via
> Reporting Service?
> If someone can point me in the right direction for either question, I would
> be very grateful.

Processing measure groups together using same dimensions

Hi,

2 separate measure groups of a cube are being processed with 2 separate SSIS packages and these measure groups use same dimensions of the cube. SSIS packages are used to handle the partitions. Would there be a conflict if the SSIS packages both attempt to Process the same dimensions (update/full process) at the same time?

How does Analysis Services 2005 handle this? Does one wait for the other and then the 2nd package skips the Dim process or reprocesses it?

In such cases, is there a need to have a SSIS package that processes only the dimensions 1st and then the other 2 packages start off parallely to process the 2 measure groups without touching the dims.

Regards

You can process the dimension first in one step and after this processing have succeded you can process the two measure groups partitions in parallell.

Your last alternative sounds correct.

Regards

Thomas Ivarsson.

processing cube with partitions

Hi,

I have a cube with 2 partitions. Are these 2 partitions being processed everytime I have a ProcessData and Process Index? Coz i worry that if it's just processing 1 partition every time, i might have new data in one extraction that should reside in Partition 1 and another set of data (also in the same extraction) that reside on Partition 2.

cherriesh

If you are issuing your ProcessData and ProcessIndex commands against the cube, then it will cascade down and process all the partitions in that cube.|||

ic... but is there an option which partition i will just process?

cherriesh

|||Sure, you can process just one partition at a time (or multiple in parallel). There are multiple ways to do this. You can right click on a partition in SSMS and process it or you can build an XMLA command to process one or more partitions, you can do it from the partitions screen in BIDS.|||

is it a standard to just process only a partition of a cube every time (mine is partitioned based on This Year and Previous Years...) or by using Process Data and Process Index instead (meaning, process all)? What do you usually do?

|||

Usually you try to only process the smallest amount of data possible. I have done both extremes, sometimes I have to process the entire cube everytime, other times I only do a recent partition. It depends on the nature of the data and the source systems.

Note: ProcessData and ProcessIndex can be done at a partition level too. In fact when you do a process operation at the cube level, all that happens is that SSAS performs the specified processing command on each partition in the cube.

|||

Hi,

How to do it in the partition level?

thanks a lot!

cherriesh

|||

I don't have access to an Adventure Works database at the moment, so I don't know if the object Id's below are valid, but the basic structure of the XMLA command to process a partition would look like the following.

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

<Parallel>

<Process xmlns:xsd="http://www.w3.org/2001/XMLSchema"

xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance

xmlnsdl2=http://schemas.microsoft.com/analysisservices/2003/engine/2

xmlnsdl2_2=http://schemas.microsoft.com/analysisservices/2003/engine/2/2>

<Object>

<DatabaseID>Adventure Works DW</DatabaseID>

<CubeID>Adventure Works</CubeID>

<MeasureGroupID>Sales</MeasureGroupID>

<PartitionID>Sales 2003</PartitionID>

</Object>

<Type>ProcessFull</Type>

<WriteBackTableCreation>UseExisting</WriteBackTableCreation>

</Process>

</Parallel>

</Batch>

You can also navigate to the partition in the object browser in SSMS, right click on one and process it and you should be able to use the processing task in SSIS to do just a single partition. There are probably a couple of other methods of doing this too, essentially the all end up sending an XMLA command to the SSAS server. In my example I am using ProcessFull, but ProcessData and ProcessIndex will work at the partition level.

processing cube with partitions

Hi,

I have a cube with 2 partitions. Are these 2 partitions being processed everytime I have a ProcessData and Process Index? Coz i worry that if it's just processing 1 partition every time, i might have new data in one extraction that should reside in Partition 1 and another set of data (also in the same extraction) that reside on Partition 2.

cherriesh

If you are issuing your ProcessData and ProcessIndex commands against the cube, then it will cascade down and process all the partitions in that cube.|||

ic... but is there an option which partition i will just process?

cherriesh

|||Sure, you can process just one partition at a time (or multiple in parallel). There are multiple ways to do this. You can right click on a partition in SSMS and process it or you can build an XMLA command to process one or more partitions, you can do it from the partitions screen in BIDS.|||

is it a standard to just process only a partition of a cube every time (mine is partitioned based on This Year and Previous Years...) or by using Process Data and Process Index instead (meaning, process all)? What do you usually do?

|||

Usually you try to only process the smallest amount of data possible. I have done both extremes, sometimes I have to process the entire cube everytime, other times I only do a recent partition. It depends on the nature of the data and the source systems.

Note: ProcessData and ProcessIndex can be done at a partition level too. In fact when you do a process operation at the cube level, all that happens is that SSAS performs the specified processing command on each partition in the cube.

|||

Hi,

How to do it in the partition level?

thanks a lot!

cherriesh

|||

I don't have access to an Adventure Works database at the moment, so I don't know if the object Id's below are valid, but the basic structure of the XMLA command to process a partition would look like the following.

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

<Parallel>

<Process xmlns:xsd="http://www.w3.org/2001/XMLSchema"

xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance

xmlnsdl2=http://schemas.microsoft.com/analysisservices/2003/engine/2

xmlnsdl2_2=http://schemas.microsoft.com/analysisservices/2003/engine/2/2>

<Object>

<DatabaseID>Adventure Works DW</DatabaseID>

<CubeID>Adventure Works</CubeID>

<MeasureGroupID>Sales</MeasureGroupID>

<PartitionID>Sales 2003</PartitionID>

</Object>

<Type>ProcessFull</Type>

<WriteBackTableCreation>UseExisting</WriteBackTableCreation>

</Process>

</Parallel>

</Batch>

You can also navigate to the partition in the object browser in SSMS, right click on one and process it and you should be able to use the processing task in SSIS to do just a single partition. There are probably a couple of other methods of doing this too, essentially the all end up sending an XMLA command to the SSAS server. In my example I am using ProcessFull, but ProcessData and ProcessIndex will work at the partition level.

Processed Date for a Cube

Hi

I want to be able to display to the users the date/time a cube was last processed. We have SSAS 2005 and presenting the cube through Excel 2003 (with the SSAS 9 OLAP driver).

Thanks

LastProcessed is a property on the object avaliable only through AMO. Dont think Excel will show you this property.

You can possible wirte a AMO code yourself and use Excel programming model to show it.

Another way is to create a dimension with a single memeber based on the column in the relational database where you would keep the current date and during processing you update this dimension to pickup a new value.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Thanks for the suggestions.

I created a table with the current date and use this.

Procedure to search a table

Hi All,

I have the following table:

Code Block

Request
requestid
customername
age
sex
address
status

[status =
0 deleteme
1 addme
2 updatme

3 processed]

I need to write a stored proc that will do the following :

Input : requestid

Logic : check the customers who already have a status of 'deleteme' and have now filed a status of 'addme'. For this, inside the stored proc, i will retreive the customername and address using the requestid and then check in the table for all the customers who have the samename, address and status=0. How can i detect this.

Output : 0 for no entry found and requestid if a request of 'deleteme' for the same person was found.

For eg:

Code Block

requestid,customername,age,sex,address,status
22352,Jack,23,M,'Texas',1

I need to check if a person with the name of Jack and address Texas is there in the db with the status of 0.

Plz note: My Request table can contain more than 100000 records at a time. Please sugest accordingly

Plz help. Thanks

Here it is,

Code Block

Create Table #sample (

[requestid] int ,

[customername] Varchar(100) ,

[age] Varchar(100) ,

[sex] Varchar(100) ,

[address] Varchar(100) ,

[status] int

);

Insert Into #sample Values('20352','Jack','23','M','Texas','0');

Insert Into #sample Values('22352','Jack','23','M','Texas','1');

Insert Into #sample Values('22353','Jack','23','M','Austin','0');

Insert Into #sample Values('22362','Buck','23','M','Austin','0');

Insert Into #sample Values('22392','Buck','23','M','Austin','1');

select

*

from

#sample [add]

Where

Exists

(

select

[requestid]

,[customername]

,[address]

from

#sample [del]

where

[del].[status] = 0

and [del].[customername] = [add].[customername]

and [del].[address] = [add].[address]

and [del].[requestid] < [add].[requestid]

and [add].[status] = 1

)

|||

Hi,

Thanks for query. I ran the query and it returns all people who have status as 1.

I want all people who have status as 0 and not 1. How can i make this in a stored proc so that i accept only request id and then search.

thanks.

|||

Expected one.. Use the following query..

Code Block

select

*

from

#sample [add]

Where

Exists

(

select

[requestid]

,[customername]

,[address]

from

#sample [del]

where

[del].[status] = 1

and [del].[customername] = [add].[customername]

and [del].[address] = [add].[address]

and [del].[requestid] > [add].[requestid]

and [add].[status] = 0

)

|||

thanks so strange..i tried that previously on the query you gave first..it gave no records..no when i try yours, it gave the needed results..

nyways thanks.