0

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
3
Views
7 Years
Discussion Span
Last Post by iiavvk
0

>>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.

0

>>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.

Attachments
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
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.