Member Avatar for iiavvk

HI There,

I am facing a different kind of issue, which is giving the below error,

Microsoft OLE DB Provider for SQL Server error '80040e21'

Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

/CISL/Web/Calls/BNTANT Response Time/BNTResponseTime_Graph.asp, line 246

The stored procedure involved is used to generate a report based on days range ( like 30 days, 60 days, 90 & 180 days)

The procedure which i execute in this asp page is executing fine when i execute from Query analyser. But the issue is the error is thrown when i select 180 days range and that too only when i run it first time from front end. If i run the second time it works fine from then on.

If i run the 60 days report first and try the 180 days report second time, it works fine.

If i try first the 180 days report after a long time (of 1 to 2 hrs) it gives error.

Could any one help me on this ASAP?

Many Thanks in Advance!!!

Regards,
pradeep

  • 2 Contributors
  • 2 Replies
  • 163 Views
  • 22 Hours Discussion Span
  • Latest Post Latest Post by iiavvk
Member Avatar for sknake

>>If i try first the 180 days report after a long time (of 1 to 2 hrs) it gives error.

You're probably choking the SQL Server. You should not generate a report that takes 1+ hours on-demand from a web interface. Schedule it in some sort of queue and notify the user when it is complete. The problem is likely inside of your database. Post the stored procedure in question.

Member Avatar for iiavvk

>>If i try first the 180 days report after a long time (of 1 to 2 hrs) it gives error.

You're probably choking the SQL Server. You should not generate a report that takes 1+ hours on-demand from a web interface. Schedule it in some sort of queue and notify the user when it is complete. The problem is likely inside of your database. Post the stored procedure in question.

------------------------------------------

It is not actually running for 1 hr. What i meant is, if we try the report generation task after 1 or 2 hrs for 180 days we are getting the error.

So the error is coming soemtimes and sometimes everything is working fine.

Please find attahced the ASP code and Procedure being used.

ASP Code used:

'Establish database connection
	Set oConnectionFile = oFileSystem.OpenTextFile(CONNECTION_FILE,1,False)
	sConnectionString = oConnectionFile.ReadLine
	oConnection.Open sConnectionString


Connection_File has path to the text file content "PROVIDER=SQLOLEDB.1;PASSWORD=xxxxxxx;PERSIST SECURITY INFO=TRUE;USER ID=xxxxxxx;INITIAL CATALOG=CISL;DATA SOURCE=(local)"   --------Available in TXT file
.
.
.
Set oConnection = Server.CreateObject("ADODB.Connection")
Set oCommand = Server.CreateObject("ADODB.Command")
Set oRs = Server.CreateObject("ADODB.Recordset")
Set oRsGraph = Server.CreateObject("ADODB.Recordset")
.
.
.
Set oCommand = Server.CreateObject("ADODB.Command")
oRsGraph.Open oCommand,,adOpenKeyset,adLockOptimistic  -- Error occurs in this line
--------------------------
CREATE     PROCEDURE [dbo].[SP_INNER_BNTResponseTime_PeakPerPlatform] 
	@SiteID as varchar(20), --Selected Sites
	@StartDate as varchar(12), --Start date
	@EndDate as varchar(12), --End date
	@MeasureID as varchar(3), --Measure ID
	@ReportType as varchar(4) --Report type - P=Profile, S=Summary
AS
Declare
	@Sql as varchar(8000),
	@NEWLINE as char(1)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
SET @NEWLINE = CHAR(10)
IF (RTrim(LTrim(@ReportType)) = 'P')
BEGIN
	SET @Sql = 
		'SELECT tblRawStats_Calls.[DateTime], Sum(tblRawStats_Calls.Stat) as Platform ' + @NEWLINE +
		'FROM tblRawStats_Calls' + @NEWLINE 
	SET @Sql = @Sql +
		'Where tblRawStats_Calls.Field_ID = 277' + @NEWLINE +
		'AND tblRawStats_Calls.Site_ID  = 0'  + @NEWLINE +
		'AND tblRawStats_Calls.[DateTime] >= '''+ @StartDate +''' AND tblRawStats_Calls.[DateTime] < '''+ Convert(varchar,(Convert(datetime,@StartDate) + 1),112) +'''' + @NEWLINE +
		'AND tblRawStats_Calls.Measure_ID = '''+ RTrim(LTrim(@MeasureID)) + '''' + @NEWLINE +
		'GROUP By tblRawStats_Calls.[DateTime]' + @NEWLINE 
END
IF (RTrim(LTrim(@ReportType)) = 'S')
BEGIN
	
		SET @Sql = 
			'select Convert(varchar,test.dateTime,112) as [DateTime], max(test.Platform)as maxVal ' + @NEWLINE +
			'INTO #tblPlatformData'+@NEWLINE +
			'FROM'+@NEWLINE +
			'(SELECT tblRawStats_Calls.[DateTime], sum(tblRawStats_Calls.Stat) as Platform'+ @NEWLINE +
			'FROM tblRawStats_Calls'+@NEWLINE +
			'Where tblRawStats_Calls.Field_ID = 277'+ @NEWLINE +
			'AND tblRawStats_Calls.Site_ID = 0'+ @NEWLINE +
			'AND tblRawStats_Calls.Measure_ID = ''' + RTrim(LTrim(@MeasureID)) + '''' + @NEWLINE +
			'AND tblRawStats_Calls.[DateTime] >='''+ @StartDate  + ''' AND tblRawStats_Calls.[DateTime] < Convert(varchar,(Convert(datetime,'''+ @EndDate + ''')),112)'+ @NEWLINE +
			'GROUP By tblRawStats_Calls.[DateTime]) test'+ @NEWLINE +
			'GROUP By Convert(varchar,test.dateTime,112)'+ @NEWLINE +
			'INSERT tblAccessTypeSummaryData' + @NEWLINE +
			'SELECT Convert(varchar,#tblPlatformData.[DateTime],112) as [DateTime],'' '' as Field_Desc, #tblPlatformData.maxVal as CPS'+@NEWLINE +
			'FROM #tblPlatformData'
						
END
	Print(@Sql)
	Exec (@Sql)
		SET @Sql =  
			'SELECT tblAccessTypeSummaryData.[DateTime] as Date,  tblAccessTypeSummaryData.CPS as Platform ' + @NEWLINE +
			'FROM tblAccessTypeSummaryData' + @NEWLINE +
			'TRUNCATE TABLE tblAccessTypeSummaryData'			
			Exec(@sql)		

SET ANSI_WARNINGS ON







GO
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.