acOutputStoredProcedure not printing Excel file

[ExcelMan]

I have an Access ADP project where I provide the user a Reports Screen
to set parameters and run reports. The user is allowed to select
whether the report prints an Access report, which is based on a stored
procedure, or outputs the stored procedure data to an Excel file.

For a particular report it runs fine until the user selects "Dec" as
the parameter for the month. In this case it refuses to save the
Excel file. However, if the user runs the report as an Access report
it runs fine -- even with the Dec parameter. If I run the stored
procedure directly in the SQL Server query window, it runs fine.

The call to the sproc is as follows:

DoCmd.OutputTo acOutputStoredProcedure,
exec dbo.uspFundSalesByRep '0', 12, 2008, 'ALL', 'ALL' ,
acFormatXLS, CurrentProject.Path & "\SalesByRep" & Format(Now(),
"mmddyyyyhhmmss") & ".xls"


The "12" in the exec statement represents "Dec". Again, it works fine
with any parameter 1-11 being passed in, but not 12. However with 12
being passed in it runs fine from the SQL Server query window, but
will not save the file. (Also, no errors are thrown by SQL Server or
Access even when 12 is passed in -- it just doesn't save the file).

Does anyone have any clue what might be causing this behavior?

Thanks.

Steven

 

[ExcelMan]

I have an Access ADP project where I provide the user a Reports Screen
to set parameters and run reports.  The user is allowed to select
whether the report prints an Access report, which is based on a stored
procedure, or outputs the stored procedure data to an Excel file.

For a particular report it runs fine until the user selects "Dec" as
the parameter for the month.  In this case it refuses to save the
Excel file.  However, if the user runs the report as an Access report
it runs fine -- even with the Dec parameter.  If I run the stored
procedure directly in the SQL Server query window, it runs fine.

The call to the sproc is as follows:

DoCmd.OutputTo acOutputStoredProcedure,
exec dbo.uspFundSalesByRep '0', 12, 2008, 'ALL', 'ALL' ,
acFormatXLS, CurrentProject.Path & "\SalesByRep" & Format(Now(),
"mmddyyyyhhmmss") & ".xls"

The "12" in the exec statement represents "Dec".  Again, it works fine
with any parameter 1-11 being passed in, but not 12.  However with 12
being passed in it runs fine from the SQL Server query window, but
will not save the file.  (Also, no errors are thrown by SQL Server or
Access even when 12 is passed in -- it just doesn't save the file).

Does anyone have any clue what might be causing this behavior?

Thanks.

Steven

P.S. The Stored Procedure outputs about 700 rows and 15 columns, so
there is no issue with the results fitting into a spreadsheet.

 

[Sylvain Lafontaine]

I suppose that there is an error in your SP or that there is some kind of
incompatibility. For example, a value returned with the DEC parameter might
be to big or to long. Modify your SP so that the values are stored in a
table instead and use another SP to simply retrieve these values. After
that, you can take a look at these values and find which is blocking the
saving of the Excel report.à

There are other possibilities as well; for example, the number of outputted
lines migh be greater than 655535 (or something like that). Check these
possibilities too when using the above table.

 

[ExcelMan]

I suppose that there is an error in your SP or that there is some kind of
incompatibility.  For example, a value returned with the DEC parameter might
be to big or to long.  Modify your SP so that the values are stored in a
table instead and use another SP to simply retrieve these values.  After
that, you can take a look at these values and find which is blocking the
saving of the Excel report.à

There are other possibilities as well; for example, the number of outputted
lines migh be greater than 655535 (or something like that).  Check these
possibilities too when using the above table.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)











- Show quoted text -

Sylvain,

Thanks for your response. However, the SP runs fine when I run it
from the query window in SQL Server. Doesn't that indicate there is
nothing wrong with the SP? And it only returns about 700 rows so that
is not the problem. What I can't figure out is why it runs
successfully in the query window, but not when I try to save it to
Excel -- and only when I pass the "12" parameter.

Thanks.

Steven

 

[Sylvain Lafontaine]

I suppose that there is an error in your SP or that there is some kind of
incompatibility. For example, a value returned with the DEC parameter
might
be to big or to long. Modify your SP so that the values are stored in a
table instead and use another SP to simply retrieve these values. After
that, you can take a look at these values and find which is blocking the
saving of the Excel report.à

There are other possibilities as well; for example, the number of
outputted
lines migh be greater than 655535 (or something like that). Check these
possibilities too when using the above table.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)











- Show quoted text -

Sylvain,

Thanks for your response. However, the SP runs fine when I run it
from the query window in SQL Server. Doesn't that indicate there is
nothing wrong with the SP?

No, it only indicates that it runs fine from the query window of the
SQL-Server.

And it only returns about 700
rows so that
is not the problem. What I can't figure out is why it runs
successfully in the query window, but not when I try to save it to
Excel -- and only when I pass the "12" parameter.

Thanks.

Steven

And what do you want me to say without saying your SP, your tables and your
data?

The first thing to try should be to add the option WITH RECOMPILE on your SP
to make sure that you don't have a time-out issue because of a bad query
plan. However, you could have a time-out from other causes; for example
with some kind of locking problem.

The second thing to try would be to store the values returned by the SP in
an intermediate table. From that, it should be easy to find what's going
wrong by having a SP that will return all or just a subset of this table and
see if there is one or more rows or one or more columns that block the
saving of the report when the DEC parameter has been used.

Another possibility would be that your SP is returning a multi-resultset or
an error message of a warning level. Check that you don't have any error or
warning message when you are running it in the Query Window. You can also
try to add the SET NOCOUNT ON option at the beginning of your SP.

 

[ExcelMan]

No, it only indicates that it runs fine from the query window of the
SQL-Server.


And what do you want me to say without saying your SP, your tables and your
data?

The first thing to try should be to add the option WITH RECOMPILE on yourSP
to make sure that you don't have a time-out issue because of a bad query
plan.  However, you could have a time-out from other causes; for example
with some kind of locking problem.

The second thing to try would be to store the values returned by the SP in
an intermediate table.  From that, it should be easy to find what's going
wrong by having a SP that will return all or just a subset of this table and
see if there is one or more rows or one or more columns that block the
saving of the report when the DEC parameter has been used.

Another possibility would be that your SP is returning a multi-resultset or
an error message of a warning level.  Check that you don't have any error or
warning message when you are running it in the Query Window.  You can also
try to add the SET NOCOUNT ON option at the beginning of your SP.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)- Hide quoted text -

- Show quoted text -

Sylvain,

I seem to have isolated the problem down to the following part of the
code:

In my Access form I ask the user to supply, among others, two
parameters -- a month (@Month) and a year (@Year). I then use these
two values inside the SP to calculate the first day of the month
following the month and year, such as:

DECLARE @DayAfterTarget smalldatetime
DECLARE @NextMonth As Int
DECLARE @NextYear As Int

IF @MonthNum = 12
BEGIN
SET @NextMonth = 1
SET @NextYear = @Year + 1
END
ELSE
BEGIN
SET @NextMonth = @MonthNum + 1
SET @NextYear = @Year
END

SET @DayAfterTarget = Cast(@NextMonth As varchar) + '/1/' + Cast
(@NextYear As varchar)

The code above seemed to work fine, except if the month was Dec.


I then changed tried to create a varchar that looked like YYYYMMDD by
using the following code:

IF @NextMonth < 10
SET @MonthPad = '0'
ELSE
SET @MonthPad = NULL

SET @DayAfterTarget = Cast(@NextYear As char(4)) + IsNull
(@MonthPad,'') + Cast(@NextMonth As varchar(2)) + '01'

This seemed to work for Dec, but not for other months.



And when I say "worked" remember I mean in terms of creating the EXCEL
file. In both cases the code works fine FOR ALL MONTHS WHEN I RUN THE
SP IN THE QUERY WINDOW OR JUST USE IT TO CREATE AN ACCESS REPORT. THE
PROBLEM ONLY ARISES DURING OUTPUT TO EXCEL.

I have even entered Select @DayAfterTarget statements (and also
selected out the intermediate variables) in the query window and the
date and intermediate variables always seems correctly formatted.


My questions for you now are:

1) What is the proper way to create a varchar that looks like a date
(w/o time) and assign it to a smalldatetime variable?
2) Does this new information give you any clue as to why the problem
only occurs with Excel?

Thanks.

Steven