Crystal Reports & SQL Connections

[jkb17]

Hello, all.

I have an asp.net application (using vb.net 1.1). In my application, I call a series of reports (~ 15-20 reports) and have them executed and exported to PDF format.

This process works fine; however, when you look at sysprocesses on the SQL Server (either via sp_who or sp_who2) there is a considerable spike in the number of SQL Connections.

Has anyone encountered this issue before?

I am using SQL Server 2000 w SP4. VB.NET 1.1 in asp.net environment. Reports are connecting to the server via MS ODBC and driven off of stored procedure output.

thx

Jim

 

[dvannoy]

there is a considerable spike in the number of SQL Connections

how many are we talking?

you said your processing 15-20 reports corrcect? are you using a dataset for each one? if so, then that could be normal.

 

[jkb17]

Each report creates its own connection. The problem is that they are not released.

Each report has its own stored procedure bound to the report itself. The asp.net code just executes each one in a loop so that the PDFs are readily available (internally) on our Intranet.

 

[dvannoy]

when you run sp_who how many connections are there? how many users do you have? also, what's the status of those connections ? "Awaited Command" or what? are you closing your connection after you SP's run?

cn.close
cn.Dispose

 

[jkb17]

Crystal calls the proc so I'm not using the connection object.

Its listed as "AWAITING COMMAND."

One connection is created per report. If the users run this each day, I will get an increase of 15 more connections.

The system is lightweight and lose useage except pulling down these PDFs.

 

[dvannoy]

OK,

you are using the pull method then.. I only use the push method so I can control issues like this. I use a DataSet with my connection string and I have no problems with any connections being left open etc.. you know, you may not even have a problem. that could be normal for what you app is doing. I would leave it and let sql server manage it and see what happens.

if your really worried about it, I would post it in the sql forum and see what those guys have to say. They would probably ask you if your front end connections are being closed.

 

[jkb17]

Interesting. So, I have a question:

I can execute a data set and attach that to the report at runtime then instead. Can I pull the stored procedure name (ie, data source) from the report so that I can dynamically execute the Crystal report object.

I think I may call Crystal on this one.

 

[dvannoy]

hold on...I'll give you an example..

 

[dvannoy]

take out what you don't need...


Imports System.Data.SqlClient
Imports CrystalDecisions
Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Web
Imports CrystalDecisions.Shared
Imports System.Configuration.ConfigurationSettings

Inherits System.Web.UI.Page
Dim crReportDoc As New YourReportName

Page_Load

Dim YourDataSetAs New DataSet

Dim cn As New SqlConnection(ConfigurationSettings.AppSettings("Name in WebConfig"))

Dim cmd As New SqlCommand

With cmd
.Connection = cn
.CommandType = CommandType.StoredProcedure
.CommandText = "Your SP Name"
End With

Dim da As New SqlDataAdapter(cmd)

cn.Open()

Dim crDatabase As CrystalDecisions.CrystalReports.Engine.Database
Dim crTables As CrystalDecisions.CrystalReports.Engine.Tables
Dim crTable As CrystalDecisions.CrystalReports.Engine.Table

da.Fill(YourDataSet, "Your SP")

crDatabase = crReportDoc.Database
crTables = crDatabase.Tables
For Each crTable In crTables
crTable.SetDataSource(YourDataSet)
Next

YourCRReport.ReportSource = crReportDoc

cn.Close()


hope this helps