Looped insert stops prior to meeting condition

[forecasting]

I am trying to insert a time series into a table. The table consists of two fields, a time stamp that increments 15 minutes for each record and a second time stamp that is 15 minutes later than the first stamp.

TT15_Stamp TT15_Stend
1/1/2001 00:15 1/1/2001 00:30
1/1/2001 00:30 1/1/2001 00:45

The following code will perform correctly when I execute it from the SQL Query Analyzer. However, when I execute the code from ADO, it will only insert 114 records at a time. I am using the ADODB.Command method.

Have I bumped into a timing issue? I set .CommandTimeout to 60, but that made no difference.

DECLARE @mindate varchar(30)
SET @mindate = '1/1/2003'
DECLARE @datestring varchar(30)
SET @datestring = CONVERT(CHAR,(SELECT ISNULL((SELECT MAX(TT15_stamp) FROM TimTab15),
(SELECT ISNULL((SELECT MIN(STAMP) FROM DLMA_BLDG1ST_FLREASTMA_AHU_1), @mindate)))),101)
WHILE @datestring < DATEADD(mm, 1, GETDATE())
BEGIN
INSERT INTO TimTab15(TT15_stamp, TT15_stend)
VALUES (DATEADD(mi, 15, @datestring), DATEADD(mi, 30, @datestring))
SET @datestring = (SELECT MAX(TT15_stamp) FROM TimTab15)
END

 

[SonOfEmidec1100]

Im sure even the standard timeout of 30secs would create more than 114 records. Maybe its to do with using style 101 for the convert, this does not extract the time, to me it sort of seems as though you need the time in @datestring ?

 

[forecasting]

Once @datestring gets beyond the intitalization, it pulls the record from the database and includes the time. So I don't think that is the cause.

I never could figure out why it wouldn't work. Eventually, I converted the sql to a stored procedure and executed that. All of the records get inserted properly. Go figure...

Thanks for looking SonOfEmidec1100