software

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 gt; @StartDate

AND DateTime lt;= @EndDate

AND DateTime in

(SELECT Datetime

FROM AnalogHistory

WHERE TagName in ('hucFilterHours_001.PV')

AND value gt; 0.0

AND wwResolution = 1000

AND wwRetrievalMode = 'Cyclic'

AND wwVersion = 'Latest'

AND DateTime gt; @StartDate

AND DateTime lt;= @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(quot;DECLARE @startdate datetime

DECLARE @enddate datetime

DECLARE @currenttime DATETIME

SET @currenttime = 'quot;, A7, quot;'

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 gt; @StartDate

AND DateTime lt;= @EndDate

AND DateTime in

(SELECT Datetime

FROM AnalogHistory

WHERE TagName in ('hucFilterHours_001.PV')

AND value gt; 0.0

AND wwResolution = 1000

AND wwRetrievalMode = 'Cyclic'

AND wwVersion = 'Latest'

AND DateTime gt; @StartDate

AND DateTime lt;= @EndDate)

ORDER BY Value DESCquot;)
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
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, quot;exec QueryForChrisquot; is your command text, A7 is the
value for the parameter, and you need to set the command type to
quot;adCmdStoredProcquot;

==================================================
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 gt; @StartDate

AND DateTime lt;= @EndDate

AND DateTime in

(SELECT Datetime

FROM AnalogHistory

WHERE TagName in ('hucFilterHours_001.PV')

AND value gt; 0.0

AND wwResolution = 1000

AND wwRetrievalMode = 'Cyclic'

AND wwVersion = 'Latest'

AND DateTime gt; @StartDate

AND DateTime lt;= @EndDate)

ORDER BY Value DESC

GO

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

Chris Smith
quot;Duke Careyquot; gt; wrote in message
...
gt; Chris -
gt;
gt; Your life would be greatly simplified if you made your query a Stored
gt; Procedure, along the lines of what's shown below, and you could execute it
gt; by
gt; simply using
gt;
gt; exec QueryForChris A7
gt;
gt; If you are using ADO, quot;exec QueryForChrisquot; is your command text, A7 is the
gt; value for the parameter, and you need to set the command type to
gt; quot;adCmdStoredProcquot;
gt;
gt; ==================================================
gt; CREATE PROCEDURE QueryForChris
gt; @CurrentTime smalldatetime
gt;
gt; AS
gt; DECLARE @startdate datetime
gt; DECLARE @enddate datetime
gt;
gt; SET @StartDate = DateAdd(hh, -2, @currenttime)
gt;
gt; SET @EndDate = @currenttime
gt;
gt; SELECT Top 1 DateTime, Convert(decimal(38,3), Value)
gt;
gt; FROM AnalogHistory
gt;
gt; WHERE TagName in ('hucTurbidity_001.PV')
gt;
gt; AND wwRetrievalMode = 'Cyclic'
gt;
gt; AND value IS NOT NULL
gt;
gt; AND wwVersion = 'Latest'
gt;
gt; AND wwResolution = 1000
gt;
gt; AND DateTime gt; @StartDate
gt;
gt; AND DateTime lt;= @EndDate
gt;
gt; AND DateTime in
gt;
gt; (SELECT Datetime
gt;
gt; FROM AnalogHistory
gt;
gt; WHERE TagName in ('hucFilterHours_001.PV')
gt;
gt; AND value gt; 0.0
gt;
gt; AND wwResolution = 1000
gt;
gt; AND wwRetrievalMode = 'Cyclic'
gt;
gt; AND wwVersion = 'Latest'
gt;
gt; AND DateTime gt; @StartDate
gt;
gt; AND DateTime lt;= @EndDate)
gt;
gt; ORDER BY Value DESC
gt;
gt; GO
gt;
gt;
gt;