On 4 Jan, 13:39, "Sylvain Lafontaine" <sylvain aei ca (fill the
blanks, no spam please)> wrote:
> "ExcelMan" <sfar...@sjfcg.com> wrote in message
>
> news:7f5606d4-201d-442d-ba16-(E-Mail Removed)...
> On 4 Jan, 11:37, "Sylvain Lafontaine" <sylvain aei ca (fill the
> blanks, no spam please)> wrote:
>
>
>
>
>
> > 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)
>
> > "ExcelMan" <sfar...@sjfcg.com> wrote in message
>
> >news:e860cd22-0024-42c5-bac3-(E-Mail Removed)....
>
> > >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- Hide quoted text -
>
> > - 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 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
Similar Threads
