Connection pooling problem

Can anybody help me to solve this problem

I have a web application. In this app i have one class genral for database transections. I create new object of this class on every page in page load event & dispose it on UnLoad event. After sometime this app gives connection pooling exception.

Means connection are not closed, but i disposed the object like

tmpDatabase.Dispose()
tmpDatabase = Nothing

Plz can anybody tell me the mistake i m doing.

ThanxPersonally, I would contruct the object as late as possible (where needed) and destroy the object as soon as possible, and let connection pooling work as designed.

Perhaps there is som error condition that you are not handling, so that the dispose is sometimes not reached.|||I can see using Break point, it destroying the object. Means cursor reaching to the line where the object is destroyed.|||Please Help me

This is big problem for my application. I dont know what i doing wrong.|||"This is big problem for my application. I dont know what i doing wrong."

We do not either. In general, there is some reason why your connections are not being closed. Could be an error condition not properly handled, could be a design problem. Absent seeing much of the code, and absent you taking my advice to make the Open and Close very close to each other, leaving the connection open for as short a time as possible rather than creating one for a page load/unload cycle, I do not think anyone can help you.

connection object to client ce

yo ce folks... Anyone had any trouble using a dataset for return values in a datagridview for the client app?

trying to make this work...

' Open the Connection For SQL Compact Edition

Dim cn As New SqlServerCe.SqlCeConnection("Data Source = " & Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData) & "\ffgsCRM.sdf")

Dim cmdProduct As SqlServerCe.SqlCeCommand

' create dataset with table

cn.Open()

cmdProduct = cn.CreateCommand

cmdProduct.CommandText = "SELECT ProductDesc AS Description, ProductCost AS Cost, ProductListPrice AS [List Price] FROM Product"

Dim daProduct As New SqlServerCe.SqlCeDataAdapter(cmdProduct)

Dim dtProduct As New Data.DataTable("Product")

daProduct.Fill(dtProduct)

Dim ds As New DataSet

ds.Tables.Add(dtProduct)

'' FILL DATAGRIDVIEW

Me.dbGridProducts.DataSource = ds

I have this working:

' search product description

Dim reqProductSearch As String

reqProductSearch = tstxtSearchDescription.Text

' Open the Connection For SQL Compact Edition

Dim _conn = New SqlCeConnection("Data Source = " & Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData) & "\ffgsCRM.sdf")

Dim cmd As New SqlCeCommand()

cmd.Connection = _conn

cmd.CommandText = "select ProductDesc as Description, ProductCost as Cost, ProductListPrice as [List Price] from Product where ProductDesc like '%" & reqProductSearch & "%' ORDER BY ProductDesc"

_conn.Open()

Dim resultSet As SqlCeResultSet

resultSet = cmd.ExecuteResultSet(ResultSetOptions.Scrollable Or ResultSetOptions.Updatable)

'' FILL DATAGRIDVIEW

dbGridProducts.DataSource = resultSet

With dbGridProducts

.Columns("Description").Width = 550

End With

Thanks,

Bill

Hi,
did you try binding the table to the grid rather the dataset ? Did you get an error message on the first solution ? And why are you creating a dataset first just for binding ?
Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

hi... on the first solution when i'm getting

(ArgumentException was unhandled) Child list for field ProductDesc cannot be created when i use either ProductDesc or just Description as it would be in a a regular dataset like in SQLExpress... etc...

Dim ds As New DataSet

ds.Tables.Add(dtProduct)

Me.dbGridProducts.DataSource = ds

Me.dbGridProducts.DataMember = "Description"

this fails... i need the dataset for grouping and such and would like to know how it's done with CE...

Thanks,

Bill

|||

got my brain back... DataTable and Dataset examples... :)

'To Open the Connection To Connect to SQLCE

Dim ConsqlCombo As New SqlCeConnection

ConsqlCombo.ConnectionString = "Data Source = " & Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData) & "\ffgsCRM.sdf"

'SQL STATEMENT......

Dim reqSQLCombo As New SqlCeCommand

reqSQLCombo.CommandText = "select ProductDesc as Description, ProductCost as Cost, ProductListPrice as [List Price] from Product where ProductMfgID = '" & reqSearch & "'"

reqSQLCombo.CommandType = CommandType.Text

reqSQLCombo.Connection = ConsqlCombo

'CREATE NEW SQL DATA ADAPTER

Dim sqldaCombo As New SqlCeDataAdapter(reqSQLCombo)

Dim sqldsCombo As New DataSet

' FILL DATASET

sqldaCombo.Fill(sqldsCombo, "ProductDesc")

'' FILL DATAGRID

dbGridProducts.DataSource = sqldsCombo.Tables("ProductDesc")

dbGridProducts.DataMember = "ProductDesc"

connection object to client ce

yo ce folks... Anyone had any trouble using a dataset for return values in a datagridview for the client app?

trying to make this work...

' Open the Connection For SQL Compact Edition

Dim cn As New SqlServerCe.SqlCeConnection("Data Source = " & Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData) & "\ffgsCRM.sdf")

Dim cmdProduct As SqlServerCe.SqlCeCommand

' create dataset with table

cn.Open()

cmdProduct = cn.CreateCommand

cmdProduct.CommandText = "SELECT ProductDesc AS Description, ProductCost AS Cost, ProductListPrice AS [List Price] FROM Product"

Dim daProduct As New SqlServerCe.SqlCeDataAdapter(cmdProduct)

Dim dtProduct As New Data.DataTable("Product")

daProduct.Fill(dtProduct)

Dim ds As New DataSet

ds.Tables.Add(dtProduct)

'' FILL DATAGRIDVIEW

Me.dbGridProducts.DataSource = ds

I have this working:

' search product description

Dim reqProductSearch As String

reqProductSearch = tstxtSearchDescription.Text

' Open the Connection For SQL Compact Edition

Dim _conn = New SqlCeConnection("Data Source = " & Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData) & "\ffgsCRM.sdf")

Dim cmd As New SqlCeCommand()

cmd.Connection = _conn

cmd.CommandText = "select ProductDesc as Description, ProductCost as Cost, ProductListPrice as [List Price] from Product where ProductDesc like '%" & reqProductSearch & "%' ORDER BY ProductDesc"

_conn.Open()

Dim resultSet As SqlCeResultSet

resultSet = cmd.ExecuteResultSet(ResultSetOptions.Scrollable Or ResultSetOptions.Updatable)

'' FILL DATAGRIDVIEW

dbGridProducts.DataSource = resultSet

With dbGridProducts

.Columns("Description").Width = 550

End With

Thanks,

Bill

Hi,
did you try binding the table to the grid rather the dataset ? Did you get an error message on the first solution ? And why are you creating a dataset first just for binding ?
Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

hi... on the first solution when i'm getting

(ArgumentException was unhandled) Child list for field ProductDesc cannot be created when i use either ProductDesc or just Description as it would be in a a regular dataset like in SQLExpress... etc...

Dim ds As New DataSet

ds.Tables.Add(dtProduct)

Me.dbGridProducts.DataSource = ds

Me.dbGridProducts.DataMember = "Description"

this fails... i need the dataset for grouping and such and would like to know how it's done with CE...

Thanks,

Bill

|||

got my brain back... DataTable and Dataset examples... :)

'To Open the Connection To Connect to SQLCE

Dim ConsqlCombo As New SqlCeConnection

ConsqlCombo.ConnectionString = "Data Source = " & Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData) & "\ffgsCRM.sdf"

'SQL STATEMENT......

Dim reqSQLCombo As New SqlCeCommand

reqSQLCombo.CommandText = "select ProductDesc as Description, ProductCost as Cost, ProductListPrice as [List Price] from Product where ProductMfgID = '" & reqSearch & "'"

reqSQLCombo.CommandType = CommandType.Text

reqSQLCombo.Connection = ConsqlCombo

'CREATE NEW SQL DATA ADAPTER

Dim sqldaCombo As New SqlCeDataAdapter(reqSQLCombo)

Dim sqldsCombo As New DataSet

' FILL DATASET

sqldaCombo.Fill(sqldsCombo, "ProductDesc")

'' FILL DATAGRID

dbGridProducts.DataSource = sqldsCombo.Tables("ProductDesc")

dbGridProducts.DataMember = "ProductDesc"

Connection object

Hi, I have a question, regaring connection with SQL from VB.
Which connect is the faster with DSN or DSN less ?From WROX

...DSN-less connections were slightly faster than System DSN connections. The increase in performance was nothing monumental; the greatest performance boost was a mere 13% faster with 64 concurrent requests. For one, two, or four concurrent requests, there was virtually no performance improvement. In fact, no noticeable improvement is seen in a DSN-less connection over a System DSN until there are 10 or more concurrent connections.

The reason is that when ADO attempts to connect to a database using a System DSN is must perform a lookup in the registry. This lookup, while not overly expensive, does add up, especially if there are many concurrent connections.|||I would go with what achorozy says but the overriding consideration should be how you want to control the connection parameters.

You have to get the data into the application to connect - whether this is via a DSN, hard coded, loaded into a global on initialisation...
Just depends on what you want to be configurable and how you want to configure it.
Make sure that all database access is via a common module then you will find it easy to change in the future so the decision doesn't become irrevokable.

Connection Manager Property Expression Editor

Is it possible to use a property, say name, of an object ( say the connection object) in the "Property Expression" of that object? I would like to modify the Connection String property of a flat file connection manager to append date to it. To do this I need to be able to use the Name property of the connection manager in the Property Expression editor. How ever I get an error that it does not recognize name, it almost seems to suggest I can only use variables. I find it hard to believe since it seems like common requirement to be able to use properties of an object (connection manager) in modifying other properties of the object. Any help would be greatly appreciated.
Thanks.

I do not think it is possible. However, I guess you could base both properties on the same variable that will contain the name.

Thanks.

sqlsql

Connection Manager Property Expression Editor

Is it possible to use a property, say name, of an object ( say the connection object) in the "Property Expression" of that object? I would like to modify the Connection String property of a flat file connection manager to append date to it. To do this I need to be able to use the Name property of the connection manager in the Property Expression editor. How ever I get an error that it does not recognize name, it almost seems to suggest I can only use variables. I find it hard to believe since it seems like common requirement to be able to use properties of an object (connection manager) in modifying other properties of the object. Any help would be greatly appreciated.
Thanks.

Your suspicion is correct -- only variables and literals can be used in property expressions. In dataflow expressions, you also have access to columns in expressions. However, there is no support for object properties.

You might be able to use a script task to populate a variable with the value you require, and then use that in the expression.

Thanks

Mark

|||

Mark:

Is this (being able to use a property in the expression) something that can be considered in the future releases of SSIS? I am currently using a script to iterate thru my connections and changing the location, but since it can be done much easily using property expressions would like to get feedback if this is a reasonable enhancement request. How do I go about to request this feature?

Thanks.

|||Yeah, I don't understand your desires for this feature... Perhaps an example of what you would like to do is in order here...|||

Toofan wrote:

How do I go about to request this feature?

Try http://connect.microsoft.com/, and find the SQL area. You can post feedback, bugs and suggestions on there.

Name certainly would be a nice property to have acces to (read-only).

|||

DarrenSQLIS :

Thank you for the link and the feedback.

Phil:
My original post had an example where this feature would be great. Here is the bit more explanation, consider a package that exports data from several different flat files (for loading different targets). The source files are created daily with creation date appended to them (file_name_YYMMDD). The connection names are named so that just adding the date would give us the file name. If I can use the "Name" property in the Property Expression editor, then I would be able to set the Connection String property to the file name without requiring a script. Right now I use a script to iterate thru the connections and change the connection string in every run. Hope that helps and any feedback or workarounds (besides scripting, since I am already doing that) are welcome.

I added a feature request:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=282481

Thanks.

Connection Manager Property Expression Editor

Is it possible to use a property, say name, of an object ( say the connection object) in the "Property Expression" of that object? I would like to modify the Connection String property of a flat file connection manager to append date to it. To do this I need to be able to use the Name property of the connection manager in the Property Expression editor. How ever I get an error that it does not recognize name, it almost seems to suggest I can only use variables. I find it hard to believe since it seems like common requirement to be able to use properties of an object (connection manager) in modifying other properties of the object. Any help would be greatly appreciated.
Thanks.

Your suspicion is correct -- only variables and literals can be used in property expressions. In dataflow expressions, you also have access to columns in expressions. However, there is no support for object properties.

You might be able to use a script task to populate a variable with the value you require, and then use that in the expression.

Thanks

Mark

|||

Mark:

Is this (being able to use a property in the expression) something that can be considered in the future releases of SSIS? I am currently using a script to iterate thru my connections and changing the location, but since it can be done much easily using property expressions would like to get feedback if this is a reasonable enhancement request. How do I go about to request this feature?

Thanks.

|||Yeah, I don't understand your desires for this feature... Perhaps an example of what you would like to do is in order here...|||

Toofan wrote:

How do I go about to request this feature?

Try http://connect.microsoft.com/, and find the SQL area. You can post feedback, bugs and suggestions on there.

Name certainly would be a nice property to have acces to (read-only).

|||

DarrenSQLIS :

Thank you for the link and the feedback.

Phil:
My original post had an example where this feature would be great. Here is the bit more explanation, consider a package that exports data from several different flat files (for loading different targets). The source files are created daily with creation date appended to them (file_name_YYMMDD). The connection names are named so that just adding the date would give us the file name. If I can use the "Name" property in the Property Expression editor, then I would be able to set the Connection String property to the file name without requiring a script. Right now I use a script to iterate thru the connections and change the connection string in every run. Hope that helps and any feedback or workarounds (besides scripting, since I am already doing that) are welcome.

I added a feature request:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=282481

Thanks.

connection

how to overcome this error "SELECT permission denied on object 'UserDetails', database 'LOGIN', schema 'dbo'."

you have to give your user, or all users (public) rights to select from this table

USE LOGIN;GRANT SELECT ON OBJECT::dbo.USERDETAILS TO PUBLIC;GO

|||

for this question

"(how to overcome this error "SELECT permission denied on object 'UserDetails', database 'LOGIN', schema 'dbo'.")"

u have given answer the answer as

you have to give your user, or all users (public) rights to select from this table

USE LOGIN;
GRANT SELECT ON OBJECT::dbo.USERDETAILS TO PUBLIC;
GO

Where I have to write the code given by u

my requirement is asp.net2.0 using sqlserver.

I have created a Login database in sqlserver2005 and trying to access the connection but it is giving the error as

"SELECT permission denied on object 'UserDetails', database 'LOGIN', schema 'dbo'."

plz say how to set permission in sqlserver to access the data"LOGIN" ,schema 'dbo'"

Connecting to Webservice from DTS

I created a custom DTS object that does its work by calling a
webservice. When you create the object in DTS, it lets you set the
user/pw you want to use to authenticate to the webservice. The problem
I am having is that once I authenticate to the webservice within DTS,
it always uses the same authentication when contacting the service.

So, for example, I drop a new instance of my object into a package. It
connects to the webservice as me. I set up the parameters of the task,
including telling it to connect as User="test". If I run it, my code
sets the credentials correctly on the webservice, but the webservice
still gets called as me.

If I exit out of SQL Server and go back in, and run it, it correctly
connects as User="test". But then if I try to edit the task, and give
my own user and password, it still always tries to connect as "test".

It seems whoever I initially connect to the webservice as, that is the
information that DTS will use for the entire session.

Is there any way to do what I want to do?

thanks"Backslider" <backslider@.mail.com> wrote in message
news:1108159058.882465.266100@.f14g2000cwb.googlegr oups.com...
> I created a custom DTS object that does its work by calling a
> webservice. When you create the object in DTS, it lets you set the
> user/pw you want to use to authenticate to the webservice. The problem
> I am having is that once I authenticate to the webservice within DTS,
> it always uses the same authentication when contacting the service.
> So, for example, I drop a new instance of my object into a package. It
> connects to the webservice as me. I set up the parameters of the task,
> including telling it to connect as User="test". If I run it, my code
> sets the credentials correctly on the webservice, but the webservice
> still gets called as me.
> If I exit out of SQL Server and go back in, and run it, it correctly
> connects as User="test". But then if I try to edit the task, and give
> my own user and password, it still always tries to connect as "test".
> It seems whoever I initially connect to the webservice as, that is the
> information that DTS will use for the entire session.
> Is there any way to do what I want to do?
> thanks

I have no idea - you might want to ask in microsoft.public.sqlserver.dts, to
see if someone there knows about DTS caching properties inappropriately.
Since you seem to be saying that your own custom task is not behaving
correctly, you'll probably have to give more details about exactly how
you've implemented it, to make it clear why you believe the problem lies
with DTS and not your task.

Simon