Alerts issue :How to solve with out getting those after exporting

i have a report with 2 fields(name,zipcode).
After exporting to excel, it generates Alerts for Zipcode Field.
some of the Zipcode field values like this (34213,23423,123123-1234,34311-1212)
In zipcode filed after exporting to excel, Iam getting alerts on 34213,23423 but not on 123123-1234.?
These alerts saying "The Number in this cell is formatted as text or preceded by apostrophe"
How can i remove alerts?
Will this be done with the help of rdl with out changing the Excel?
Can any one help on this.Even though we are exporting these as text Excel is assuming the 5 digit zip
codes should be a number.
To avoid this you will have to unset the "Number stored as text" error
checking option in Excel.
From Excel you will need to open Tools : Options : Error Checking and the
uncheck "Number stored as text"
> some of the Zipcode field values like this
(34213,23423,123123-1234,34311-1212)
> In zipcode filed after exporting to excel, Iam getting alerts on
34213,23423 but not on 123123-1234.?
> These alerts saying "The Number in this cell is formatted as text or
preceded by apostrophe"
> How can i remove alerts?
> Will this be done with the help of rdl with out changing the Excel?
> Can any one help on this.
>

Aggregate() vs. A Set in the WHERE caluse

Hi,

I recently wrote some VBA code for Excel, which allowed a user to specify a list of members from a particular dimension and then produce a report which aggregated some measures with the members of a different dimension on the rows. It did this by creating an MDX query with the listed members fed into the aggregate function and stored in a calculated member which was then added to the WHERE clause.

eg

WITH MEMBER [Dimension].[Selected_Members] As Aggregate({[Dimension].[Member1], [Dimension].[Member2],.....})
SELECT [Measures].[Measure1] ON COLUMNS,
NON EMPTY [AnotherDimension].Members ON ROWS
FROM [Cube]
WHERE ([Dimension].[Selected_Members])

We found that as the number of members specified increased, the report slowed down dramatically. So, remembering that AS2005 allows you to specify sets in the WHERE clause of your MDX we decided to change it to something like this:

SELECT [Measures].[Measure1] ON COLUMNS,
NON EMPTY [AnotherDimension].Members ON ROWS
FROM [Cube]
WHERE ({[Dimension].[Member1], [Dimension].[Member2],.....})

We were quite surprised to see a very significant improvement in speed and the same results.

So, it would appear that the end result is exactly the same, but I would like to understand what the difference is and why one is so much faster than the other.

Many thanks,

Stuart

|||

I am surprised to see performance difference between these two examples. I would think that the execution plans should be exactly the same. Perhaps there is something going on in the cube.

Please note, that Aggregate cannot always be exchanged to set in WHERE clause, the results could become different. But if the set to Aggregate over is as static as in your example, it should usually be a safe rewrite.

Aggregate function don't work with calculated measure.

Hi,

I can't get my AS2000 calculated measure to work with the Aggregate function.

We are using Excel as our frontend for our cubes and Excel is using the Aggregate function when the user selects multiple items for a filter dimension.

Here is the expression for my calculated measure:

Sum({Descendants([Period].[Quarter].CurrentMember, [Month])}, IIF([Currency].CurrentMember.Properties("Fixed") = "1", [Amount Fixr], [Amount Flor]) * ValidMeasure([Rate]))

I have tried different solve orders for the measure: -7000, -1, 0, 1.

When I use solve orders < 0 I get following error: "The aggregate function cannot operate on measure ..."

When I use solve orders >=0 I get an empty result set.

Any idea how to get it to work?

Thanks, Christer

One idea is to move this calculation from calculated measure to calculated member in utility dimension, and then use SOLVE_ORDER=-1.|||

I'm already using a kinf of utility dimension (my account dimension is configured with formulas) and I'm pointing to the calculated measure from these formulas. But if I understand you correctly I should move the expression from the calculated measure into to my account formula and set the solve_order to -1, correct?

Thanks, Christer

|||Yes. In AS2000 this should be enough.|||

Ok - thanks I will try it! By the way how do I set the solve_order for my formulas? I have tried to put into the formula expression <expression>, solve_order=-1, but that gives me an formula error.

Thanks

|||If you are using CustomRollup formulas, you will need to create yet another column in the dimension table and point to it CustomMemberOptions. The syntax for it is something like FORMAT_STRING='Standard', SOLVE_ORDER=-1

Aggregate (SUM) a column and then use the Result?

Hi,

I am importing some data from Excel. I have to SUM one of the columns, and then use the result of the sum to calculate the percentages of each row. How can I use the Aggregate to give me a total of a column, so that i can use the total in another task and use formulas to calculate the percentages? i have tried to use multicast and join, but I get an extra row with the sum, which is not what I want; I want to use the sum for all the data.

Thanks

What about using an Execute SQL task in the control flow to put that value into a variable; then you can use that variable in the data flow and perform the calculation using a derived column. This thread explains how to run queries against an excel file: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=772416&SiteID=1

|||

Fawad wrote:

Hi,

I am importing some data from Excel. I have to SUM one of the columns, and then use the result of the sum to calculate the percentages of each row. How can I use the Aggregate to give me a total of a column, so that i can use the total in another task and use formulas to calculate the percentages? i have tried to use multicast and join, but I get an extra row with the sum, which is not what I want; I want to use the sum for all the data.

Thanks

Seems to me you're going about this the wrong way. You need to calculate the SUM first and THEN use it in your data-flow. Hence, this is two executables (i.e. tasks) in your package. Rafael gave an example of doing this.

-Jamie

|||Hi Jamie,

I understand the flow:

Load Data
Calculate the Sum
Calculate new columns based on the sum (mostly count_value/total to get the rate)
Insert the final data into database.

What I cannot figure out is how to do the sum as an aggregate and then use it to do the computation.

Fawad
|||Hi Jamie,

I understand the flow that I require:

Import data from Excel
Sum the one column
Use this sum to calculate new column (mostly rates)
Load in the database

What I cannot figure out is how to use the result of the sum from the aggregate task.

Fawad
|||

Jamie Thomson wrote:

Fawad wrote:

Hi,

I am importing some data from Excel. I have to SUM one of the columns, and then use the result of the sum to calculate the percentages of each row. How can I use the Aggregate to give me a total of a column, so that i can use the total in another task and use formulas to calculate the percentages? i have tried to use multicast and join, but I get an extra row with the sum, which is not what I want; I want to use the sum for all the data.

Thanks

Seems to me you're going about this the wrong way. You need to calculate the SUM first and THEN use it in your data-flow. Hence, this is two executables (i.e. tasks) in your package. Rafael gave an example of doing this.

-Jamie

Hi Jamie,

I understand the flow
Load from Excel file
Use aggregate to calculate the SUM
Use the SUM to calculate the values for other columns (mostly rates)
Insert the data into a DB

What I do not understand is how to use the result of the Sum (its one value, think of select sum(field) from table) to calculate the other values.

Fawad
|||

Fawad wrote:

Jamie Thomson wrote:

Fawad wrote:

Hi,

I am importing some data from Excel. I have to SUM one of the columns, and then use the result of the sum to calculate the percentages of each row. How can I use the Aggregate to give me a total of a column, so that i can use the total in another task and use formulas to calculate the percentages? i have tried to use multicast and join, but I get an extra row with the sum, which is not what I want; I want to use the sum for all the data.

Thanks

Seems to me you're going about this the wrong way. You need to calculate the SUM first and THEN use it in your data-flow. Hence, this is two executables (i.e. tasks) in your package. Rafael gave an example of doing this.

-Jamie

Hi Jamie,

I understand the flow
Load from Excel file
Use aggregate to calculate the SUM
Use the SUM to calculate the values for other columns (mostly rates)
Insert the data into a DB

What I do not understand is how to use the result of the Sum (its one value, think of select sum(field) from table) to calculate the other values.

Fawad

Right. So Rafael's suggestion is exactly how you should attempt to do this.

-Jamie

|||

Fawad,

My sugestion was to use an Execute SQL task (control flow) to get the SUM value into a variable; it would be a query like Select Sum(yourcolumn) from [YourExcelSheet]. this step has to be done before the dataflow. Then the dataflow would have that value avilable and it could be used in a derived column.

Agent job hanging on xp_sendmail

I have a batch job running under SQL Server Agent that reads info from a
table, uses an excel sheet as a template, and creates an output excel
spreadsheet, that gets then sent to users.
xp_sendmail @.recipients='literaturerequests@.mycompan
y.com',
@.subject='Literature Request',
@.message='Literature Requests - contact xxxx or
yyyy if there are problems or questions ',
@.attachments='\\myserver\stage_data\lite
rature_request.XLS',
@.copy_recipients='sqladministrator@.mycom
pany.com'
For some reason, this job every once in a while will hang on the step
above. This is all the code that's in that one step, so it's not doing
anything else.
The job simply hangs and will not complete, and will not generate any
error messages. Stopping Agent and re-starting does not have any
impact. I am preparing to stop SQL on that server and re-start - but I
don't know if that'll fix the problem.
The version of SQL we're on is:
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005
23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Standard
Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
O/S is Windows 2003, SP1
Any ideas as to why this is hanging? Everything else on this server
appears to be functioning normally.
Any help appreciated. I am posting this in several forums, because I'm
not sure where this question actually belongs - whether it's a
programming or dts issue, or a setup issue.
Thanks,
SC> xp_sendmail @.recipients='literaturerequests@.mycompan
y.com',
> @.subject='Literature Request',
> @.message='Literature Requests - contact xxxx or yyyy
> if there are problems or questions ',
> @.attachments='\\myserver\stage_data\lite
rature_request.XLS',
> @.copy_recipients='sqladministrator@.mycom
pany.com'
> For some reason, this job every once in a while will hang on the step
> above. This is all the code that's in that one step, so it's not doing
> anything else.
Perhaps a prompt is being raised to enter credentials to access the share.
Or perhaps the network is somewhere short of reliable. Are you sure the
user SQL Server Agent is running as has full access to the share? Is it
possible it works when the user is logged in, but not otherwise? Would it
be possible to isolate the share as the source by temporarily creating the
XLS file on the local SQL Server machine, and see how long you can run
without error? Have you also isolated Outlook application/profile problems
and/or Exchange authentication issues by attempting to use a more simple
delivery method ( e.g. xp_smtp_sendmail - see http://www.aspfaq.com/2403 )?|||No need to cross-post. If it is in the wrong group, an MVP will usually
direct you to where it goes. .setup or .server would be appropriate in this
case.
This is a well-known problem with SQL Mail and SQL Agent Mail. What is
happening is the MAPI interface is stuck. This is usually because Outlook
needed to pop a dialog box but was not running on the console. There is a
long story why SQL uses MAPI yet there isn't a MAPI client that runs as a
service, but we won't get into that here.
You have a couple of options. You can leave the console of the server
logged in as the SQL service account and let Outlook run all the time or you
can replace the native SQL Mail components with SMPT-based equivalents. I
usually use the latter option. It isn't formally recommended by Microsoft,
but every support engineer in PSS knows about the replacement XPs. They
aren't exact drop in replacements, you will have to recode some stuff, but
they work very well.
Here is the link:
http://www.sqldev.net/xp/xpsmtp.htm
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Blasting Cap" <goober@.christian.net> wrote in message
news:uWdXR9mJGHA.1088@.tk2msftngp13.phx.gbl...
>I have a batch job running under SQL Server Agent that reads info from a
>table, uses an excel sheet as a template, and creates an output excel
>spreadsheet, that gets then sent to users.
> xp_sendmail @.recipients='literaturerequests@.mycompan
y.com',
> @.subject='Literature Request',
> @.message='Literature Requests - contact xxxx or yyyy
> if there are problems or questions ',
> @.attachments='\\myserver\stage_data\lite
rature_request.XLS',
> @.copy_recipients='sqladministrator@.mycom
pany.com'
> For some reason, this job every once in a while will hang on the step
> above. This is all the code that's in that one step, so it's not doing
> anything else.
> The job simply hangs and will not complete, and will not generate any
> error messages. Stopping Agent and re-starting does not have any impact.
> I am preparing to stop SQL on that server and re-start - but I don't know
> if that'll fix the problem.
> The version of SQL we're on is:
> Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38
> Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows
> NT 5.2 (Build 3790: Service Pack 1)
>
> O/S is Windows 2003, SP1
> Any ideas as to why this is hanging? Everything else on this server
> appears to be functioning normally.
>
> Any help appreciated. I am posting this in several forums, because I'm
> not sure where this question actually belongs - whether it's a programming
> or dts issue, or a setup issue.
> Thanks,
> SC
>|||Aaron Bertrand [SQL Server MVP] wrote:
>
<<Perhaps a prompt is being raised to enter credentials to access the
share. >>
This should not be the case. The profile generating the email has full
administrator rights to that box.
<<Or perhaps the network is somewhere short of reliable.>>
I cannot rule this out, yet, but, I can ping the server, the server can
ping anywhere I want - all fine - but the step goes in & just hangs on it.
<<Are you sure the user SQL Server Agent is running as has full access
to the share?>>
Yes - the same account that I use to generate the email is the same one
that controls sql agent.
<<Is it possible it works when the user is logged in, but not otherwise? >>
No - it has run the entire month of January without them being logged
in, and only hangs up each time I run it today.
<<Would it be possible to isolate the share as the source by temporarily
creating the
> XLS file on the local SQL Server machine, and see how long you can run
> without error?>>
I don't know what that would accomplish - in looking at the history,
it's run many times throughout January without a hiccup until today.
<< Have you also isolated Outlook application/profile problems
> and/or Exchange authentication issues by attempting to use a more simple
> delivery method ( e.g. xp_smtp_sendmail - see http://www.aspfaq.com/2403 )? >>[/c
olor]
I have not done this, but will look into it. It only does it
intermittently, and stopping SQL and re-starting, or restarting the
server seems to be the only thing that fixes it. I know it is occurring
on two other servers that use the same xp_sendmail stored procedure, but
use different account logins.
Thanks for the link - I will explore that option - hopefully it isn't
re-inventing the wheel to put that piece in.
BC|||> I don't know what that would accomplish - in looking at the history, it's
> run many times throughout January without a hiccup until today.
So maybe today the share is having a problem! If you put the file locally
and it runs, it will rule that out, will it not? And if it suddenly works,
doesn't that give you some information too?
A|||Geoff,
I'm curious as to the history of why SQL insists on using a MAPI client -
any pointers on where to find more historical info as to the whys and
wherefores?
Thanks
"Geoff N. Hiten" wrote:

> No need to cross-post. If it is in the wrong group, an MVP will usually
> direct you to where it goes. .setup or .server would be appropriate in th
is
> case.
> This is a well-known problem with SQL Mail and SQL Agent Mail. What is
> happening is the MAPI interface is stuck. This is usually because Outlook
> needed to pop a dialog box but was not running on the console. There is a
> long story why SQL uses MAPI yet there isn't a MAPI client that runs as a
> service, but we won't get into that here.
> You have a couple of options. You can leave the console of the server
> logged in as the SQL service account and let Outlook run all the time or y
ou
> can replace the native SQL Mail components with SMPT-based equivalents. I
> usually use the latter option. It isn't formally recommended by Microsoft
,
> but every support engineer in PSS knows about the replacement XPs. They
> aren't exact drop in replacements, you will have to recode some stuff, but
> they work very well.
> Here is the link:
> http://www.sqldev.net/xp/xpsmtp.htm
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Blasting Cap" <goober@.christian.net> wrote in message
> news:uWdXR9mJGHA.1088@.tk2msftngp13.phx.gbl...
>
>|||Nothing that is written down. When Microsoft rewrote SQL Server at the 7.0
release,MAPI was the corporate messaging standard. Someone within Microsoft
was supposed to provide a service-level MAPI client, which never
materialized. MAPI didn't fly and was eventually semi-abandoned, but SQL
2000 had to support it for legacy reasons. By the time it was clear that
MAPI was not the wave of the future for mail, SQL 2000 was locked in,
feature-wise. Given that SQL 2000 was the oldest current release server
product in the MS inventory until SQL 2005 was released late last year, it
makes sense that it had more hooks to obsolete technology.
Nothing malicious, just normal feature introduction and removal compounded
by different product life cycles.
You want another example, did you know you could set up a 6.5 SQL server to
accept a query via email, execute it, and return the results. Imagine how
bad a security hole that would be today.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"B@.DJJ" <BDJJ@.discussions.microsoft.com> wrote in message
news:B19D645B-D373-40B3-8591-92649579CDDF@.microsoft.com...
> Geoff,
> I'm curious as to the history of why SQL insists on using a MAPI client -
> any pointers on where to find more historical info as to the whys and
> wherefores?
> Thanks
> "Geoff N. Hiten" wrote:
>

Agent job hanging on xp_sendmail

I have a batch job running under SQL Server Agent that reads info from a
table, uses an excel sheet as a template, and creates an output excel
spreadsheet, that gets then sent to users.
xp_sendmail @.recipients='literaturerequests@.mycompany.com',
@.subject='Literature Request',
@.message='Literature Requests - contact xxxx or
yyyy if there are problems or questions ',
@.attachments='\\myserver\stage_data\literature_req uest.XLS',
@.copy_recipients='sqladministrator@.mycompany.com'
For some reason, this job every once in a while will hang on the step
above. This is all the code that's in that one step, so it's not doing
anything else.
The job simply hangs and will not complete, and will not generate any
error messages. Stopping Agent and re-starting does not have any
impact. I am preparing to stop SQL on that server and re-start - but I
don't know if that'll fix the problem.
The version of SQL we're on is:
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005
23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Standard
Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
O/S is Windows 2003, SP1
Any ideas as to why this is hanging? Everything else on this server
appears to be functioning normally.
Any help appreciated. I am posting this in several forums, because I'm
not sure where this question actually belongs - whether it's a
programming or dts issue, or a setup issue.
Thanks,
SC
> xp_sendmail @.recipients='literaturerequests@.mycompany.com',
> @.subject='Literature Request',
> @.message='Literature Requests - contact xxxx or yyyy
> if there are problems or questions ',
> @.attachments='\\myserver\stage_data\literature_req uest.XLS',
> @.copy_recipients='sqladministrator@.mycompany.com'
> For some reason, this job every once in a while will hang on the step
> above. This is all the code that's in that one step, so it's not doing
> anything else.
Perhaps a prompt is being raised to enter credentials to access the share.
Or perhaps the network is somewhere short of reliable. Are you sure the
user SQL Server Agent is running as has full access to the share? Is it
possible it works when the user is logged in, but not otherwise? Would it
be possible to isolate the share as the source by temporarily creating the
XLS file on the local SQL Server machine, and see how long you can run
without error? Have you also isolated Outlook application/profile problems
and/or Exchange authentication issues by attempting to use a more simple
delivery method ( e.g. xp_smtp_sendmail - see http://www.aspfaq.com/2403 )?
|||No need to cross-post. If it is in the wrong group, an MVP will usually
direct you to where it goes. .setup or .server would be appropriate in this
case.
This is a well-known problem with SQL Mail and SQL Agent Mail. What is
happening is the MAPI interface is stuck. This is usually because Outlook
needed to pop a dialog box but was not running on the console. There is a
long story why SQL uses MAPI yet there isn't a MAPI client that runs as a
service, but we won't get into that here.
You have a couple of options. You can leave the console of the server
logged in as the SQL service account and let Outlook run all the time or you
can replace the native SQL Mail components with SMPT-based equivalents. I
usually use the latter option. It isn't formally recommended by Microsoft,
but every support engineer in PSS knows about the replacement XPs. They
aren't exact drop in replacements, you will have to recode some stuff, but
they work very well.
Here is the link:
http://www.sqldev.net/xp/xpsmtp.htm
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Blasting Cap" <goober@.christian.net> wrote in message
news:uWdXR9mJGHA.1088@.tk2msftngp13.phx.gbl...
>I have a batch job running under SQL Server Agent that reads info from a
>table, uses an excel sheet as a template, and creates an output excel
>spreadsheet, that gets then sent to users.
> xp_sendmail @.recipients='literaturerequests@.mycompany.com',
> @.subject='Literature Request',
> @.message='Literature Requests - contact xxxx or yyyy
> if there are problems or questions ',
> @.attachments='\\myserver\stage_data\literature_req uest.XLS',
> @.copy_recipients='sqladministrator@.mycompany.com'
> For some reason, this job every once in a while will hang on the step
> above. This is all the code that's in that one step, so it's not doing
> anything else.
> The job simply hangs and will not complete, and will not generate any
> error messages. Stopping Agent and re-starting does not have any impact.
> I am preparing to stop SQL on that server and re-start - but I don't know
> if that'll fix the problem.
> The version of SQL we're on is:
> Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38
> Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows
> NT 5.2 (Build 3790: Service Pack 1)
>
> O/S is Windows 2003, SP1
> Any ideas as to why this is hanging? Everything else on this server
> appears to be functioning normally.
>
> Any help appreciated. I am posting this in several forums, because I'm
> not sure where this question actually belongs - whether it's a programming
> or dts issue, or a setup issue.
> Thanks,
> SC
>
|||Geoff,
I'm curious as to the history of why SQL insists on using a MAPI client -
any pointers on where to find more historical info as to the whys and
wherefores?
Thanks
"Geoff N. Hiten" wrote:

> No need to cross-post. If it is in the wrong group, an MVP will usually
> direct you to where it goes. .setup or .server would be appropriate in this
> case.
> This is a well-known problem with SQL Mail and SQL Agent Mail. What is
> happening is the MAPI interface is stuck. This is usually because Outlook
> needed to pop a dialog box but was not running on the console. There is a
> long story why SQL uses MAPI yet there isn't a MAPI client that runs as a
> service, but we won't get into that here.
> You have a couple of options. You can leave the console of the server
> logged in as the SQL service account and let Outlook run all the time or you
> can replace the native SQL Mail components with SMPT-based equivalents. I
> usually use the latter option. It isn't formally recommended by Microsoft,
> but every support engineer in PSS knows about the replacement XPs. They
> aren't exact drop in replacements, you will have to recode some stuff, but
> they work very well.
> Here is the link:
> http://www.sqldev.net/xp/xpsmtp.htm
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Blasting Cap" <goober@.christian.net> wrote in message
> news:uWdXR9mJGHA.1088@.tk2msftngp13.phx.gbl...
>
>
|||Nothing that is written down. When Microsoft rewrote SQL Server at the 7.0
release,MAPI was the corporate messaging standard. Someone within Microsoft
was supposed to provide a service-level MAPI client, which never
materialized. MAPI didn't fly and was eventually semi-abandoned, but SQL
2000 had to support it for legacy reasons. By the time it was clear that
MAPI was not the wave of the future for mail, SQL 2000 was locked in,
feature-wise. Given that SQL 2000 was the oldest current release server
product in the MS inventory until SQL 2005 was released late last year, it
makes sense that it had more hooks to obsolete technology.
Nothing malicious, just normal feature introduction and removal compounded
by different product life cycles.
You want another example, did you know you could set up a 6.5 SQL server to
accept a query via email, execute it, and return the results. Imagine how
bad a security hole that would be today.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"B@.DJJ" <BDJJ@.discussions.microsoft.com> wrote in message
news:B19D645B-D373-40B3-8591-92649579CDDF@.microsoft.com...[vbcol=seagreen]
> Geoff,
> I'm curious as to the history of why SQL insists on using a MAPI client -
> any pointers on where to find more historical info as to the whys and
> wherefores?
> Thanks
> "Geoff N. Hiten" wrote:

Agent job hanging on xp_sendmail

I have a batch job running under SQL Server Agent that reads info from a
table, uses an excel sheet as a template, and creates an output excel
spreadsheet, that gets then sent to users.
xp_sendmail @.recipients='literaturerequests@.mycompany.com',
@.subject='Literature Request',
@.message='Literature Requests - contact xxxx or
yyyy if there are problems or questions ',
@.attachments='\\myserver\stage_data\literature_request.XLS',
@.copy_recipients='sqladministrator@.mycompany.com'
For some reason, this job every once in a while will hang on the step
above. This is all the code that's in that one step, so it's not doing
anything else.
The job simply hangs and will not complete, and will not generate any
error messages. Stopping Agent and re-starting does not have any
impact. I am preparing to stop SQL on that server and re-start - but I
don't know if that'll fix the problem.
The version of SQL we're on is:
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005
23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Standard
Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
O/S is Windows 2003, SP1
Any ideas as to why this is hanging? Everything else on this server
appears to be functioning normally.
Any help appreciated. I am posting this in several forums, because I'm
not sure where this question actually belongs - whether it's a
programming or dts issue, or a setup issue.
Thanks,
SC> xp_sendmail @.recipients='literaturerequests@.mycompany.com',
> @.subject='Literature Request',
> @.message='Literature Requests - contact xxxx or yyyy
> if there are problems or questions ',
> @.attachments='\\myserver\stage_data\literature_request.XLS',
> @.copy_recipients='sqladministrator@.mycompany.com'
> For some reason, this job every once in a while will hang on the step
> above. This is all the code that's in that one step, so it's not doing
> anything else.
Perhaps a prompt is being raised to enter credentials to access the share.
Or perhaps the network is somewhere short of reliable. Are you sure the
user SQL Server Agent is running as has full access to the share? Is it
possible it works when the user is logged in, but not otherwise? Would it
be possible to isolate the share as the source by temporarily creating the
XLS file on the local SQL Server machine, and see how long you can run
without error? Have you also isolated Outlook application/profile problems
and/or Exchange authentication issues by attempting to use a more simple
delivery method ( e.g. xp_smtp_sendmail - see http://www.aspfaq.com/2403 )?|||No need to cross-post. If it is in the wrong group, an MVP will usually
direct you to where it goes. .setup or .server would be appropriate in this
case.
This is a well-known problem with SQL Mail and SQL Agent Mail. What is
happening is the MAPI interface is stuck. This is usually because Outlook
needed to pop a dialog box but was not running on the console. There is a
long story why SQL uses MAPI yet there isn't a MAPI client that runs as a
service, but we won't get into that here.
You have a couple of options. You can leave the console of the server
logged in as the SQL service account and let Outlook run all the time or you
can replace the native SQL Mail components with SMPT-based equivalents. I
usually use the latter option. It isn't formally recommended by Microsoft,
but every support engineer in PSS knows about the replacement XPs. They
aren't exact drop in replacements, you will have to recode some stuff, but
they work very well.
Here is the link:
http://www.sqldev.net/xp/xpsmtp.htm
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Blasting Cap" <goober@.christian.net> wrote in message
news:uWdXR9mJGHA.1088@.tk2msftngp13.phx.gbl...
>I have a batch job running under SQL Server Agent that reads info from a
>table, uses an excel sheet as a template, and creates an output excel
>spreadsheet, that gets then sent to users.
> xp_sendmail @.recipients='literaturerequests@.mycompany.com',
> @.subject='Literature Request',
> @.message='Literature Requests - contact xxxx or yyyy
> if there are problems or questions ',
> @.attachments='\\myserver\stage_data\literature_request.XLS',
> @.copy_recipients='sqladministrator@.mycompany.com'
> For some reason, this job every once in a while will hang on the step
> above. This is all the code that's in that one step, so it's not doing
> anything else.
> The job simply hangs and will not complete, and will not generate any
> error messages. Stopping Agent and re-starting does not have any impact.
> I am preparing to stop SQL on that server and re-start - but I don't know
> if that'll fix the problem.
> The version of SQL we're on is:
> Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38
> Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows
> NT 5.2 (Build 3790: Service Pack 1)
>
> O/S is Windows 2003, SP1
> Any ideas as to why this is hanging? Everything else on this server
> appears to be functioning normally.
>
> Any help appreciated. I am posting this in several forums, because I'm
> not sure where this question actually belongs - whether it's a programming
> or dts issue, or a setup issue.
> Thanks,
> SC
>|||Geoff,
I'm curious as to the history of why SQL insists on using a MAPI client -
any pointers on where to find more historical info as to the whys and
wherefores?
Thanks
"Geoff N. Hiten" wrote:
> No need to cross-post. If it is in the wrong group, an MVP will usually
> direct you to where it goes. .setup or .server would be appropriate in this
> case.
> This is a well-known problem with SQL Mail and SQL Agent Mail. What is
> happening is the MAPI interface is stuck. This is usually because Outlook
> needed to pop a dialog box but was not running on the console. There is a
> long story why SQL uses MAPI yet there isn't a MAPI client that runs as a
> service, but we won't get into that here.
> You have a couple of options. You can leave the console of the server
> logged in as the SQL service account and let Outlook run all the time or you
> can replace the native SQL Mail components with SMPT-based equivalents. I
> usually use the latter option. It isn't formally recommended by Microsoft,
> but every support engineer in PSS knows about the replacement XPs. They
> aren't exact drop in replacements, you will have to recode some stuff, but
> they work very well.
> Here is the link:
> http://www.sqldev.net/xp/xpsmtp.htm
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Blasting Cap" <goober@.christian.net> wrote in message
> news:uWdXR9mJGHA.1088@.tk2msftngp13.phx.gbl...
> >I have a batch job running under SQL Server Agent that reads info from a
> >table, uses an excel sheet as a template, and creates an output excel
> >spreadsheet, that gets then sent to users.
> >
> > xp_sendmail @.recipients='literaturerequests@.mycompany.com',
> > @.subject='Literature Request',
> > @.message='Literature Requests - contact xxxx or yyyy
> > if there are problems or questions ',
> >
> > @.attachments='\\myserver\stage_data\literature_request.XLS',
> > @.copy_recipients='sqladministrator@.mycompany.com'
> >
> > For some reason, this job every once in a while will hang on the step
> > above. This is all the code that's in that one step, so it's not doing
> > anything else.
> >
> > The job simply hangs and will not complete, and will not generate any
> > error messages. Stopping Agent and re-starting does not have any impact.
> > I am preparing to stop SQL on that server and re-start - but I don't know
> > if that'll fix the problem.
> >
> > The version of SQL we're on is:
> >
> > Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38
> > Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows
> > NT 5.2 (Build 3790: Service Pack 1)
> >
> >
> > O/S is Windows 2003, SP1
> >
> > Any ideas as to why this is hanging? Everything else on this server
> > appears to be functioning normally.
> >
> >
> > Any help appreciated. I am posting this in several forums, because I'm
> > not sure where this question actually belongs - whether it's a programming
> > or dts issue, or a setup issue.
> >
> > Thanks,
> >
> > SC
> >
>
>|||Nothing that is written down. When Microsoft rewrote SQL Server at the 7.0
release,MAPI was the corporate messaging standard. Someone within Microsoft
was supposed to provide a service-level MAPI client, which never
materialized. MAPI didn't fly and was eventually semi-abandoned, but SQL
2000 had to support it for legacy reasons. By the time it was clear that
MAPI was not the wave of the future for mail, SQL 2000 was locked in,
feature-wise. Given that SQL 2000 was the oldest current release server
product in the MS inventory until SQL 2005 was released late last year, it
makes sense that it had more hooks to obsolete technology.
Nothing malicious, just normal feature introduction and removal compounded
by different product life cycles.
You want another example, did you know you could set up a 6.5 SQL server to
accept a query via email, execute it, and return the results. Imagine how
bad a security hole that would be today.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"B@.DJJ" <BDJJ@.discussions.microsoft.com> wrote in message
news:B19D645B-D373-40B3-8591-92649579CDDF@.microsoft.com...
> Geoff,
> I'm curious as to the history of why SQL insists on using a MAPI client -
> any pointers on where to find more historical info as to the whys and
> wherefores?
> Thanks
> "Geoff N. Hiten" wrote:
>> No need to cross-post. If it is in the wrong group, an MVP will usually
>> direct you to where it goes. .setup or .server would be appropriate in
>> this
>> case.
>> This is a well-known problem with SQL Mail and SQL Agent Mail. What is
>> happening is the MAPI interface is stuck. This is usually because
>> Outlook
>> needed to pop a dialog box but was not running on the console. There is
>> a
>> long story why SQL uses MAPI yet there isn't a MAPI client that runs as
>> a
>> service, but we won't get into that here.
>> You have a couple of options. You can leave the console of the server
>> logged in as the SQL service account and let Outlook run all the time or
>> you
>> can replace the native SQL Mail components with SMPT-based equivalents.
>> I
>> usually use the latter option. It isn't formally recommended by
>> Microsoft,
>> but every support engineer in PSS knows about the replacement XPs. They
>> aren't exact drop in replacements, you will have to recode some stuff,
>> but
>> they work very well.
>> Here is the link:
>> http://www.sqldev.net/xp/xpsmtp.htm
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>>
>>
>> "Blasting Cap" <goober@.christian.net> wrote in message
>> news:uWdXR9mJGHA.1088@.tk2msftngp13.phx.gbl...
>> >I have a batch job running under SQL Server Agent that reads info from a
>> >table, uses an excel sheet as a template, and creates an output excel
>> >spreadsheet, that gets then sent to users.
>> >
>> > xp_sendmail @.recipients='literaturerequests@.mycompany.com',
>> > @.subject='Literature Request',
>> > @.message='Literature Requests - contact xxxx or
>> > yyyy
>> > if there are problems or questions ',
>> >
>> > @.attachments='\\myserver\stage_data\literature_request.XLS',
>> > @.copy_recipients='sqladministrator@.mycompany.com'
>> >
>> > For some reason, this job every once in a while will hang on the step
>> > above. This is all the code that's in that one step, so it's not doing
>> > anything else.
>> >
>> > The job simply hangs and will not complete, and will not generate any
>> > error messages. Stopping Agent and re-starting does not have any
>> > impact.
>> > I am preparing to stop SQL on that server and re-start - but I don't
>> > know
>> > if that'll fix the problem.
>> >
>> > The version of SQL we're on is:
>> >
>> > Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005
>> > 23:18:38
>> > Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on
>> > Windows
>> > NT 5.2 (Build 3790: Service Pack 1)
>> >
>> >
>> > O/S is Windows 2003, SP1
>> >
>> > Any ideas as to why this is hanging? Everything else on this server
>> > appears to be functioning normally.
>> >
>> >
>> > Any help appreciated. I am posting this in several forums, because I'm
>> > not sure where this question actually belongs - whether it's a
>> > programming
>> > or dts issue, or a setup issue.
>> >
>> > Thanks,
>> >
>> > SC
>> >
>>

Agent job hanging on xp_sendmail

I have a batch job running under SQL Server Agent that reads info from a
table, uses an excel sheet as a template, and creates an output excel
spreadsheet, that gets then sent to users.
xp_sendmail @.recipients='literaturerequests@.mycompan
y.com',
@.subject='Literature Request',
@.message='Literature Requests - contact xxxx or
yyyy if there are problems or questions ',
@.attachments='\\myserver\stage_data\lite
rature_request.XLS',
@.copy_recipients='sqladministrator@.mycom
pany.com'
For some reason, this job every once in a while will hang on the step
above. This is all the code that's in that one step, so it's not doing
anything else.
The job simply hangs and will not complete, and will not generate any
error messages. Stopping Agent and re-starting does not have any
impact. I am preparing to stop SQL on that server and re-start - but I
don't know if that'll fix the problem.
The version of SQL we're on is:
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005
23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Standard
Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
O/S is Windows 2003, SP1
Any ideas as to why this is hanging? Everything else on this server
appears to be functioning normally.
Any help appreciated. I am posting this in several forums, because I'm
not sure where this question actually belongs - whether it's a
programming or dts issue, or a setup issue.
Thanks,
SC> xp_sendmail @.recipients='literaturerequests@.mycompan
y.com',
> @.subject='Literature Request',
> @.message='Literature Requests - contact xxxx or yyyy
> if there are problems or questions ',
> @.attachments='\\myserver\stage_data\lite
rature_request.XLS',
> @.copy_recipients='sqladministrator@.mycom
pany.com'
> For some reason, this job every once in a while will hang on the step
> above. This is all the code that's in that one step, so it's not doing
> anything else.
Perhaps a prompt is being raised to enter credentials to access the share.
Or perhaps the network is somewhere short of reliable. Are you sure the
user SQL Server Agent is running as has full access to the share? Is it
possible it works when the user is logged in, but not otherwise? Would it
be possible to isolate the share as the source by temporarily creating the
XLS file on the local SQL Server machine, and see how long you can run
without error? Have you also isolated Outlook application/profile problems
and/or Exchange authentication issues by attempting to use a more simple
delivery method ( e.g. xp_smtp_sendmail - see http://www.aspfaq.com/2403 )?|||No need to cross-post. If it is in the wrong group, an MVP will usually
direct you to where it goes. .setup or .server would be appropriate in this
case.
This is a well-known problem with SQL Mail and SQL Agent Mail. What is
happening is the MAPI interface is stuck. This is usually because Outlook
needed to pop a dialog box but was not running on the console. There is a
long story why SQL uses MAPI yet there isn't a MAPI client that runs as a
service, but we won't get into that here.
You have a couple of options. You can leave the console of the server
logged in as the SQL service account and let Outlook run all the time or you
can replace the native SQL Mail components with SMPT-based equivalents. I
usually use the latter option. It isn't formally recommended by Microsoft,
but every support engineer in PSS knows about the replacement XPs. They
aren't exact drop in replacements, you will have to recode some stuff, but
they work very well.
Here is the link:
http://www.sqldev.net/xp/xpsmtp.htm
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Blasting Cap" <goober@.christian.net> wrote in message
news:uWdXR9mJGHA.1088@.tk2msftngp13.phx.gbl...
>I have a batch job running under SQL Server Agent that reads info from a
>table, uses an excel sheet as a template, and creates an output excel
>spreadsheet, that gets then sent to users.
> xp_sendmail @.recipients='literaturerequests@.mycompan
y.com',
> @.subject='Literature Request',
> @.message='Literature Requests - contact xxxx or yyyy
> if there are problems or questions ',
> @.attachments='\\myserver\stage_data\lite
rature_request.XLS',
> @.copy_recipients='sqladministrator@.mycom
pany.com'
> For some reason, this job every once in a while will hang on the step
> above. This is all the code that's in that one step, so it's not doing
> anything else.
> The job simply hangs and will not complete, and will not generate any
> error messages. Stopping Agent and re-starting does not have any impact.
> I am preparing to stop SQL on that server and re-start - but I don't know
> if that'll fix the problem.
> The version of SQL we're on is:
> Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38
> Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows
> NT 5.2 (Build 3790: Service Pack 1)
>
> O/S is Windows 2003, SP1
> Any ideas as to why this is hanging? Everything else on this server
> appears to be functioning normally.
>
> Any help appreciated. I am posting this in several forums, because I'm
> not sure where this question actually belongs - whether it's a programming
> or dts issue, or a setup issue.
> Thanks,
> SC
>|||Geoff,
I'm curious as to the history of why SQL insists on using a MAPI client -
any pointers on where to find more historical info as to the whys and
wherefores?
Thanks
"Geoff N. Hiten" wrote:

> No need to cross-post. If it is in the wrong group, an MVP will usually
> direct you to where it goes. .setup or .server would be appropriate in th
is
> case.
> This is a well-known problem with SQL Mail and SQL Agent Mail. What is
> happening is the MAPI interface is stuck. This is usually because Outlook
> needed to pop a dialog box but was not running on the console. There is a
> long story why SQL uses MAPI yet there isn't a MAPI client that runs as a
> service, but we won't get into that here.
> You have a couple of options. You can leave the console of the server
> logged in as the SQL service account and let Outlook run all the time or y
ou
> can replace the native SQL Mail components with SMPT-based equivalents. I
> usually use the latter option. It isn't formally recommended by Microsoft
,
> but every support engineer in PSS knows about the replacement XPs. They
> aren't exact drop in replacements, you will have to recode some stuff, but
> they work very well.
> Here is the link:
> http://www.sqldev.net/xp/xpsmtp.htm
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Blasting Cap" <goober@.christian.net> wrote in message
> news:uWdXR9mJGHA.1088@.tk2msftngp13.phx.gbl...
>
>|||Nothing that is written down. When Microsoft rewrote SQL Server at the 7.0
release,MAPI was the corporate messaging standard. Someone within Microsoft
was supposed to provide a service-level MAPI client, which never
materialized. MAPI didn't fly and was eventually semi-abandoned, but SQL
2000 had to support it for legacy reasons. By the time it was clear that
MAPI was not the wave of the future for mail, SQL 2000 was locked in,
feature-wise. Given that SQL 2000 was the oldest current release server
product in the MS inventory until SQL 2005 was released late last year, it
makes sense that it had more hooks to obsolete technology.
Nothing malicious, just normal feature introduction and removal compounded
by different product life cycles.
You want another example, did you know you could set up a 6.5 SQL server to
accept a query via email, execute it, and return the results. Imagine how
bad a security hole that would be today.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"B@.DJJ" <BDJJ@.discussions.microsoft.com> wrote in message
news:B19D645B-D373-40B3-8591-92649579CDDF@.microsoft.com...[vbcol=seagreen]
> Geoff,
> I'm curious as to the history of why SQL insists on using a MAPI client -
> any pointers on where to find more historical info as to the whys and
> wherefores?
> Thanks
> "Geoff N. Hiten" wrote:
>

After export to Excel, cells background color disappear

After I exported a report to Excel, some of the cells' background color disappear.

Only the columns without data were displayed without background color, although I'd specified color at design time.

Hi

Typically the color must be retained. can you please post some code sample here for us to help you out.

Thanks

VJ

|||

hi vj, i don't have sample code as there's no code involved. i just specified the cell background to dark green.

this discolorisation problem only occur on group cell. for example, the first column contain group header risk category. second column contain risk id. some risk id belong to the same risk category. so for every risk id, risk category is only displayed for the first occurrence. for the other risks, the risk category is empty. as a result the background color defaults back to white.

i hope i explained the situation. i can send you the spreadsheet so you can see what i mean.

|||

Hi,

From your description, the exporting to excel is a built-in function which enables you to export the current report to an excel formatted file.

When you are going to export a report to the excel formatted file, it converts the fields which filled with data to the cells in excel with the format, but for those fields without data, it wouldn't.

Based on my knowledge, since this is a built-in function, it's difficult for us to set the background through codes or any programmatically ways.

Thanks.

|||

that's what i thought nai-dong.

is there any workaround for this.