Best Way To Pervent Unused And Left Open Connection To Sql
Mar 31, 2007
Hello every one i had a design question.
What would be the best way to approach a problem such as a perventing unsed and left open sql connections
i have written a class called DataBase Manger but my question is should this class be static or none-static. right now the way i have is something like this
now every time in any class or method i need to create a new Database manager and call the run proc object. Would it be a better desing to make this class static? so it would be just
DataBaseManger.RunProc(blah,blah)
or is it a better OO design to have it the way i have it. is is more secure in a webapplication? can these connections be hijacked?
thanks in adanvce, any help would greatly be apperiated. or any link to any related tutorials
I am unsure if i am managing my connections in the correct way... but as it stands we have them in a .dtsConfig file that each package references in order to get the relevant details..
This is working fine, except that in total our ETL project uses 6 different connections, and none of the packages utilise all 6.
This leads to an error message upon opening up EVERY package stating that there are connection strings in the .dtsConfig that are unused, yeah, thats the idea!
The way i have gotten around this, is to just include all 6 connection managers in every package, but my question is, is there a better way to achieve this same result??
are we going about this in a bit of a cackhanded way?
For some reason, when I try to delete old DB connections from the connection manager, it causes compilation errors because it is not removing the references from the meta data. I get Object ID errors where it is still trying to find the connection after it has been deleted. I have tried going to the code behind and searching then deleting the invalid reference, but that still does not work. I have a connection for 2 totally different databases that are no longer being used in the SSIS package and I would like to get rid of them.
I got an approach like that: 1) Read something from DB - check the value, if true stop if false go on2) Read the second Value (another SQL Statement) - check the value etc. Now I could open the connection at 1) and if I have to go to 2) I leave the connection open and use the same connection at 2). Is it ok to do that? The other scenario would be opening a connection at 1), immediately close it after I read the value and open a new connection at 2). Thanks for the input!
Hello!We have a maintenance job on MSSQL server scheduled for the weekend.some times we arrive at the beginning of a week and see that a user lefthis screen open on a SQL record and the job is still waiting blocked bythat user. Please give me an idea how to overcome this.Thanks in advance*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
I am accessing SQL2005 with C# code using OleDbConnection.
A try and catch block catches the following error once a while between the Open() and Close() of the connection:
ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.
I do not even have any idea where to start to debug this. The ExecuteNonQuery() runs a delete SQL query. It works 99.9% of the time. I do not see anything wrong when this error happens.
Hi, I had an old web application created during asp.net 1.1 and it have a connection problem with the sql server 2005 when it is mirgrated to a new webserver with dotnet framework 2.0 platform. I have enabled the remote access(TCP/IP and named pipes) in sql server 2005, did all the neccessary things, check whether the TCP/IP is enabled, named pipe is enabled... I created another web application using VS 2005. The database connection works perfectly well.This are the connectionString from the old web application.<appSettings> <add key="ConnectionString" value="Server=127.0.0.1;Database=somedb;User id=user; Password=somepassword; Trusted_Connection=False; POOLING=FALSE"/></appSettings> Thankyou in advance!
Hello everybody I would like to know more about the number of possible connection to a sql server is it by pool ? or there is max for all the database ? all the server ? how I can get the number of connection open ? Thx in advance
Hi, I hope this is right place to ask for help.If it is not, Please show me where and if it is please Help me I using Window Vista bussiness, Microsoft visual 2005 professional edition, Microsoft SQL server express edtion I have created table on Database. My sql server name is KYAW-HQ **but in the bar it shown like as KYAW-HQSQLEXPRESS My data base name is kyaw I have already Enabled Name Pipes and TCP/IP setting in sql studio management tool express I using window authentication to log in to sql server management studio express.
After I click the button the error has shown like below
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server).
Please tell me what i have to do. If someone read me, please tell me and advice me how to fix above problem. Thanks
This code sample stops when I try to open a connection (see below). Ther error message reads "pipe provider , error 40". The help suggests that the credentials are wrong. I don´t think so, could there be another problem? Tough question but, I would appreciate any help.
Hi, Has somebody had a problem like this before? Exception Details: System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) That's part of the code: SqlConnection con; string sql; SqlCommand cmd; con=new SqlConnection("Data Source=elf/SQLEXPRESS;Initial Catalog=logos;Integrated Security=SSPI;"); sql="SELECT max(id_kandydat) from Kandydat"; con.Open(); cmd=new SqlCommand(sql,con); int id = Convert.ToInt32(cmd.ExecuteScalar()); con.Close(); SqlDataSource7.InsertCommand="INSERT INTO Kandydat_na_Kierunek(id_kandydat, id_kierunku, id_stan) VALUES ("+"'"+id+"'"+","+"'"+DropDownList1.SelectedValue+"'"+","+'1'+");"; I don't really get it. I'm nearly 100% sure that before some changes in my project this had worked perfectly. Does anybody have any idea how to make it work again? I would be very grateful for any help. And, yesy, I know it is quite common error, but proposed solution found didn't help.Regards,N.
Hello, I have always closed my db connections asap after opening them and have always thought this was the best practice. But now I am wondering if I can just leave the connection open. I created a windows service which runs on a timer and executes every 5 seconds. The problem is that the code takes longer than that to process and the code may be executing three times at once. Im worried that since I use the same connection each time the code executes that a connection opened by the first run through may be closed or reopened by the second or third running of the code resulting in an error. The connection is opened and closed about 10 times each time the timer event fires. Should I open the connection once and leave it open when the service is started and close it when it is stopped or on any error?
i m getting this exception "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)" i want to know that this exception is from my end or on Server End. because 5 to 6 month my application works fine but in these days i m getting this exception 5 to 20 times a day your help will really appricate...
hi to all, I create a web application in asp.net with sql server 2000, I use always close connection in .net code as my consern but when run my application then visit page to page give a error "General network error " message . then i check lots of connection is sleeping state in process info in sql manegment(current activity) Now i open the particular processId in Process info show open connection with any storeprocedure name and show .net sqlclient data provoider referance as open connection which is fetch the data from sql then i check in code find really the connection which is got data by that store procedure is not close properly and i close it. This is ok but one problem is that some procesId show blank info with .net sql client data provoider. so how can i find where these conneciton is open in my applicaton because this processId doesn't give any clue for it , it show nothing only blank but status is sleeping. If any help have this context then quick feedback. Thanks
One issue has got me stuck while getting to build an application(ASP.NET/SQLSERVER). Whenever I try to connect to SQL Server 2005 (installed on local host) using Visual Web Developer 2005 Databse Explorer, I get the following error message:
"An error has occured while establishing a connection to the server. when connection to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - could not open a connection to SQL Server)"
NB: - on SQL Servers' Surface Area Configuration, Remote connections are set to "Local and Remote connnections" and "Using both TCP/IP and named pipes".
I am developing a web service that uses a sql data connection.
The sql database is on a server, and the development version of my web service is on my pc.
I can connect/access the data accross my network when I run the service on my PC, but as soon as I put the web service onto the server(with the sql) it displays, the service wont function - error: 40 - could not open a connection....
I presume that sql is configured correctly as I am recieving data back when I request it accross the network. I dont understand why it wont work when I have the service on the server with the sql?
I'm trying to update an SQL DB from data in Quickbooks. I am trying to find if I can open a dsn connection in a stored procedure, called from an ASP page, to the quickbooks db using odbc. The driver I have for odbc does not allow for linked servers. I'm very new to stored procedures and such so please bear with me. Thxs.
hello ,i write this code string sql="SELECT * FROM Customers"; SqlConnection connection = CollectionManager.getHotelConnection(); SqlCommand command = new SqlCommand(sql,connection); command.CommandType = CommandType.Text; command.ExecuteNonQuery(); result = command.ExecuteScalar(); connection.Open(); ... and when the debug goes to connection.Open () it throws me this
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
but i have all enabled ,browser running normally ,name pipes tcp ip enable all ,but it makes me this thsi all the time what mayb e huppens there?
I have created a data reader to loop thru a table and build an update statement. Now I need to execute this statement in the datareader loop but the connection object is in use.
I tried creating another connection object but get an error: "The context connection is already in use."
Is there a way to open another connection while the main context connection is open. Just to make it clear, I do not want to read all the rows into memory first and then call update, it has to happen for each iteration of the datareader.
Hi, I've created an "ASP.NET Web Application" project in Visual Studio and I want to simply open a connection to a database in SQL Server. All I have done is adding these 3 lines below without modifying any other generated codes by Visual Studio.(I've done this within code behind in Page_Load() not by <script> tag within HTML code) public class WebForm1 : System.Web.UI.Page { private void Page_Load(object sender, System.EventArgs e) { // Put user code to initialize the page here SqlConnection c = new SqlConnection(); c.ConnectionString = "workstation id=BABAK;integrated security=SSPI;initial catalog=db1;persist security info=False"; c.Open(); } ... } But I get this error : Server Error in '/projects/fortest/tDB2' Application.-------------------------------------------------------------------------------- Login failed for user 'BABAKASPNET'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Login failed for user 'BABAKASPNET'. Source Error: Line 24: SqlConnection c = new SqlConnection();Line 25: c.ConnectionString = "workstation id=BABAK;integrated security=SSPI;initial catalog=db1;persist security info=False";Line 26: c.Open();Line 27: }Line 28: Source File: c:inetpubwwwrootprojectsfortest db2webform1.aspx.cs Line: 26 What's wrong ?
I posted this on the Dotnetnuke forums but no answer for it, so since it is a SQL error I guess maybe someone here might know. I installed the core forum that comes with the installation, the page runs great until I add the forum module. I get this error:
A critical error has occurred.An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) The portal is in godaddy if this can help. Thanks, Erick
I receive this error when choosing the data connection in TableAdapter Wizard for me to reference the ASPNETDB.mdf database.
Failed to open a connection to the database
"The header for file 'C:InetpubwwwrootWebsites4App_DataASPNETDB.MDF is not valid databasefile header. The FILE SIZE property is incorrect.An attempt to attach an auto-named database exists, or specified file cannotbe opened, or it is located on UNC share."
Check the connection and try again.
I already have the connection at config but the error persists.
hI I have a website which is on a web server owned by another companyI have webpages which should connect from the web server to sql server 2000 which is based at my companyhowever I am getting the error messageAn error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) When the website was on citrix it connected fine, however once it has moved to a web server it does notI have checked and tcp/ip and named pipes are both enabled any1 any ideasthanks in advance!! :-)
Hi, I have a web apllication using asp.net, I have two connectionstrings (cadenaCon and cadenaCon2) in web.config. <connectionStrings> <add connectionString="Server=SUNBOGW-MROMEROSQLEXPRESS;Database=ModulosWebkkk;UID=sa;PWD=martha;" name="cadenaCon" providerName="System.Data.SqlClient" /> <add connectionString="Server=SUNBOGW-MROMEROSQLEXPRESS;Database=ModulosWeb;UID=sa;PWD=martha;" name="cadenaCon_bak" providerName="System.Data.SqlClient" /></connectionStrings>In the class file I have this sub and it works well Public Sub New() conexion = New SqlConnection(ConfigurationManager.ConnectionStrings("cadenaCon").ConnectionString.ToString)End Sub My problem is I like to try to connect with cadenaCon and if this connection fails use the other. I used a try but it don´t work, Public Sub New() Try conexion = New SqlConnection(ConfigurationManager.ConnectionStrings("cadenaCon").ConnectionString.ToString) Catch ex As Exception Throw New Exception(ex.Message) conexion = New SqlConnection(ConfigurationManager.ConnectionStrings("cadenaCon_bak").ConnectionString.ToString) Finally End TryEnd Sub Please I aprecitate a sugestion. Thanks Martha
HiI am connecting to an external web server running sql 2005 absolutly fine when testing a site using my localhost on my laptop. How ever, when i deploy the site to my server i get the error 40 code - the external database its trying to connect to is on a different server to both my own localhost and my own server.here is my connection string from my web.config: <add name="plightConnectionString" connectionString="Data Source=111.222.333.444;Initial Catalog=plight;Persist Security Info=True;User ID=xxxxxxx;Password=xxxxxxxx;" providerName="System.Data.SqlClient" /> I've googled the error but all answers require alterations to the server where the sql database is installed, and it works from my localhost anyway - is there anything i need to do to my own server which is running the website? Regards
I am used to writing applications that hit the database "every so often" and am happy with opening and closing the connection to SQL Server for each one.
I am now writing an application that monitors a table where rows are written to it by a 3rd party application at possibly several rows per second. My job is to "pickup" those rows, analyse the data and move them to different tables. This will be done with a timer which is currently set to tick every second.
My question is: At what stage should I start to think about keeping open a permanent connection to the database ?
I'm using VS2005 and SQL2000. I created a database name "myDatabase" in SQL Server 2000 and built a website that connet to this database. When I'm running this website in VS2005 IDE (press F5 or Ctrl-F5), everything is OK. I publiced this site. When I connect to database on my computer, everything is OK also, but when I connect to database on another computer, an error occurred like this :
"An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)"
Now, I don't know how to fix it. Pls help me as soon as possible. Regards.
I have created a new aspx site on ‘server 1’ which connects to an sql server 200 on ‘server 2’ ‘Server 1’ is a test server and all works fine. I then move the site onto ‘server 3’ and receive the error below.
This must be a problem with ‘server 3’. I have installed .net version 2 on this server and there are currently no aspx sites running of this server. There must be smothering not configured right. There are other sites on the server that are talking to the SQL server.
Any help would be great. Thanks
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:
[SqlException (0x80131904): An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +735059 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup) +820 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628 System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +359 System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28 System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424 System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66 System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105 System.Data.SqlClient.SqlConnection.Open() +111 System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +121 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +137 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83 System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1770 System.Web.UI.WebControls.Repeater.GetData() +50 System.Web.UI.WebControls.Repeater.CreateControlHierarchy(Boolean useDataSource) +232 System.Web.UI.WebControls.Repeater.OnDataBinding(EventArgs e) +53 System.Web.UI.WebControls.Repeater.DataBind() +72 System.Web.UI.WebControls.Repeater.EnsureDataBound() +55 System.Web.UI.WebControls.Repeater.OnPreRender(EventArgs e) +12 System.Web.UI.Control.PreRenderRecursiveInternal() +77 System.Web.UI.Control.PreRenderRecursiveInternal() +161 System.Web.UI.Control.PreRenderRecursiveInternal() +161 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1360
i m using sql server 2005 express edition and sql server 2005 management studio for express edition.when i m trying to establish connection from visual studio i m getting an error "An error has occured while establishing a connection to the server.when connecting to SQL server 2005,this failure may be caused by the fact that under the default settings SQL server does not allow remote connections.(provider:named pipe provider,error :40-could not open a connection to sql Server)"