Error message "formula is too long"
 

Hello all,

I have an Excel spreadsheet that, when opened, executes some SQL queries
against a SQL Server 2000 DB. This function works correctly. This
spreadsheet is part of an automated process. This process starts Excel up
in the background, the data is pulled since the spreadsheet is opened, and
the results are saved to an HTML file.

The thing is, my users have requested I setup the spreadsheet such that if
someone opens up the spreadsheet, they can easily manually modify something
such that it will pull data from a date/time of their choice. I can think
of multiple ways of doing this. The preferrable way of doing this is to
have a cell where they could type the date/time they wish to look at. All
the queries on the spreadsheet would grab the datetime from this cell and
use this value within their queries. I did some testing on a blank Excel
spreadsheet with some small queries and figured out a way to do it: make use
of the CONCATENATE function. Like I said, on small queries I can make this
work. Using a copy of the production Excel spreadsheet, I modified one of
the queries to see if I it would work on one of them. The result: I get an
error messages saying the Formula is too long.

I could use some help with this. Consider the following query:

DECLARE @startdate datetime

DECLARE @enddate datetime

DECLARE @currenttime DATETIME

SET @currenttime = GETDATE()

SET @currenttime = DATEADD(ss,-1 * DATEPART(ss, @currenttime), @currenttime)

SET @currenttime = DATEADD(ms,-1 * DATEPART(ms, @currenttime), @currenttime)

SET @StartDate = DateAdd(hh, -2, @currenttime)

SET @EndDate = @currenttime

SELECT Top 1 DateTime, Convert(decimal(38,3), Value)

FROM AnalogHistory

WHERE TagName in ('hucTurbidity_001.PV')

AND wwRetrievalMode = 'Cyclic'

AND value IS NOT NULL

AND wwVersion = 'Latest'

AND wwResolution = 1000

AND DateTime @StartDate

AND DateTime <= @EndDate

AND DateTime in

(SELECT Datetime

FROM AnalogHistory

WHERE TagName in ('hucFilterHours_001.PV')

AND value 0.0

AND wwResolution = 1000

AND wwRetrievalMode = 'Cyclic'

AND wwVersion = 'Latest'

AND DateTime @StartDate

AND DateTime <= @EndDate)

ORDER BY Value DESC



I would like to change the above query to the following (A7 below is the
reference to teh cell with the date/time in it):



=CONCATENATE("DECLARE @startdate datetime

DECLARE @enddate datetime

DECLARE @currenttime DATETIME

SET @currenttime = '", A7, "'

SET @currenttime = DATEADD(ss,-1 * DATEPART(ss, @currenttime), @currenttime)

SET @currenttime = DATEADD(ms,-1 * DATEPART(ms, @currenttime), @currenttime)

SET @StartDate = DateAdd(hh, -2, @currenttime)

SET @EndDate = @currenttime

SELECT Top 1 DateTime, Convert(decimal(38,3), Value)

FROM AnalogHistory

WHERE TagName in ('hucTurbidity_001.PV')

AND wwRetrievalMode = 'Cyclic'

AND value IS NOT NULL

AND wwVersion = 'Latest'

AND wwResolution = 1000

AND DateTime @StartDate

AND DateTime <= @EndDate

AND DateTime in

(SELECT Datetime

FROM AnalogHistory

WHERE TagName in ('hucFilterHours_001.PV')

AND value 0.0

AND wwResolution = 1000

AND wwRetrievalMode = 'Cyclic'

AND wwVersion = 'Latest'

AND DateTime @StartDate

AND DateTime <= @EndDate)

ORDER BY Value DESC")



This produces the error message. Is there a way I can make it work? How
long can text be before this function starts to report this error message?



Any suggestions and thoughts on this are greatly appreciated.



Chris Smith

Error message "formula is too long"
 

Chris -

Your life would be greatly simplified if you made your query a Stored
Procedure, along the lines of what's shown below, and you could execute it by
simply using

exec QueryForChris A7

If you are using ADO, "exec QueryForChris" is your command text, A7 is the
value for the parameter, and you need to set the command type to
"adCmdStoredProc"

==================================================
CREATE PROCEDURE QueryForChris
@CurrentTime smalldatetime

AS
DECLARE @startdate datetime
DECLARE @enddate datetime

SET @StartDate = DateAdd(hh, -2, @currenttime)

SET @EndDate = @currenttime

SELECT Top 1 DateTime, Convert(decimal(38,3), Value)

FROM AnalogHistory

WHERE TagName in ('hucTurbidity_001.PV')

AND wwRetrievalMode = 'Cyclic'

AND value IS NOT NULL

AND wwVersion = 'Latest'

AND wwResolution = 1000

AND DateTime @StartDate

AND DateTime <= @EndDate

AND DateTime in

(SELECT Datetime

FROM AnalogHistory

WHERE TagName in ('hucFilterHours_001.PV')

AND value 0.0

AND wwResolution = 1000

AND wwRetrievalMode = 'Cyclic'

AND wwVersion = 'Latest'

AND DateTime @StartDate

AND DateTime <= @EndDate)

ORDER BY Value DESC

GO

Error message "formula is too long"
 

Thanks Duke!! Excellent suggestion. I'll give it a shot.

Chris Smith
"Duke Carey" wrote in message
...
Chris -

Your life would be greatly simplified if you made your query a Stored
Procedure, along the lines of what's shown below, and you could execute it
by
simply using

exec QueryForChris A7

If you are using ADO, "exec QueryForChris" is your command text, A7 is the
value for the parameter, and you need to set the command type to
"adCmdStoredProc"

==================================================
CREATE PROCEDURE QueryForChris
@CurrentTime smalldatetime

AS
DECLARE @startdate datetime
DECLARE @enddate datetime

SET @StartDate = DateAdd(hh, -2, @currenttime)

SET @EndDate = @currenttime

SELECT Top 1 DateTime, Convert(decimal(38,3), Value)

FROM AnalogHistory

WHERE TagName in ('hucTurbidity_001.PV')

AND wwRetrievalMode = 'Cyclic'

AND value IS NOT NULL

AND wwVersion = 'Latest'

AND wwResolution = 1000

AND DateTime @StartDate

AND DateTime <= @EndDate

AND DateTime in

(SELECT Datetime

FROM AnalogHistory

WHERE TagName in ('hucFilterHours_001.PV')

AND value 0.0

AND wwResolution = 1000

AND wwRetrievalMode = 'Cyclic'

AND wwVersion = 'Latest'

AND DateTime @StartDate

AND DateTime <= @EndDate)

ORDER BY Value DESC

GO