We've built a web site with more than 500,000 unique visitors per month.
We are not having any performance issues.
However, I've asked my ISP to send me the no of open database connections.
He told me that there's around 80 open database connections.
For info, we are using dotnet with DAB 3.0 and SQL server 2005.
Is the no of connections normal ?
Just a quick question about connection management. My application willnever need more than 1 or 2 connections about at any given time. Also, I donot expect many users to be connected at any given time. For efficiency, Iwould like to keep connections alive throughout the lifetime of the objectsrequiring them, rather than opening a new connection, executing code andthen closing it again. What is the most efficient way of doing this?Should I perform the open/close or just one open when I create the objectand a close when I dispose of it?
View 1 Replies View RelatedHi ,
I want to open and close sql connection only once and want to use in every function without open or close this connection in class file in asp.net 2003 .
how can it possible .
How can I know how many connections are open at a given point of time while I am testing an ASP.Net application? The application uses SQL Server 2000 as its database.
Would be very pleased if anyone knows a solution to my prob.
I connect via ODBC (ADO) within Active Server Pages with an SQL Server 6.5 database.
The connection is opened at the top of the pages that need the database, and closed at the end of each individual page. Thing is, the connections for one single user are max. around 3 connections (checked that in Performance Monitor), no matter how many times the user opens his browser with the application. However, when say 20 users log on, the max. number of user connections as configured is reached within no time! The connections are not being closed (even with connection pooling on, they increase like hell!).
The only way to close them seems to be stopping the MSSQLServer service, but that's something ya don't want, of course.
It's jut plain crazy to increase the number of user connections to around 250 for about max. 80 user connections at one time...
I would really appreciate your input.
Thanks in advance!
Best regards,
Wim
Hi,
Please, I like to know how can I check what is the number of connections already opened for an specific Database on SQL Server 2005, programmatically.
Thanks in advance.
Ivan
Hi I'm trying to have 2 datareaders open at the same time with MultipleActiveResultSets=True but still getting an error saying There is already an open DataReader associated with this Command which must be closed first. Do you see what I might be doing wrong? Thanks.
<configuration> <connectionStrings> <add connectionString="user id=username;data source=servername;persist security info=True;initial catalog=corporate_mel;password=password;MultipleActiveResultSets=True;" name="corporate_mel" providerName="System.Data.SqlClient" />
Dim strConnection As String Dim sqlConn As SqlConnection strConnection = System.Configuration.ConfigurationManager.ConnectionStrings("corporate_mel").ConnectionString sqlConn = New SqlConnection(strConnection) sqlConn.Open()
This is the actual code where I'm trying to use 2 datareaders:
' Retrieve Data from database based on selections chosen in ListBox
Dim cmdCommittee As New SqlCommand("Select * from committees_tbl where committee_id in" & _
"(" & strCommitteesRemoveLast & ") order by committee_name", sqlConn)
' setup a datareader
Dim drCommittee As SqlDataReader = cmdCommittee.ExecuteReader()
' Loop through datareader and insert rows
' into the xref_person_committees_tbl
While drCommittee.Read()
Dim strCommitteeName As String = drCommittee("committee_name") 'retrieve committee_name from datareader
' Create a sql string
Dim strAddCommittee As String = String.Empty
strAddCommittee = "Insert into xref_person_committees_tbl (committee_name) values ('" & strCommitteeName & "')"
'Response.Write(strAddCommittee & "<br>")
' Create a sql command to process the insert
Dim sqlAddCommittee As New SqlCommand(strAddCommittee, sqlConn)
Dim drNewCommittee As SqlDataReader = sqlAddCommittee.ExecuteReader()
End While
Hi,
I'm trying to run a data driven subscription on a report in the rs2005,
and using a data source to oracle,
I found out that when the subscription runs it opens a connection to oracle for each report and it means that i have more than fifteen open connections every time which causes a performance problems
did anyone encounter this kind of problem
pls help
Hi Guys, how many connections does sql 2005 express allow to .mdf file. I stumpled on info on a microsoft site (actually a forum post on msdn) that sql server express allows only a single connection to an .mdf file.
Iam intrested in this because i developed reporting services reports in BIS and iam having problems. If i run a report from report manager, i have to wait for close to 20 minutes before i can be able to run my asp.net application agian. Else if i insert data or retrieve data from my asp.net application, then i have to wait again for 20 minutes or more before i can be able to run my reports else i will get an error that a connection can not be made to the database or that the database is being used by another process.
So in all cases, i have to wait for 20--30 minutes before a connection can be made to the .mdf file. i.e after running a report or after inserting or retrieving data from the database through my asp.net application.
Iam using sql sever 2005 express with advanced services and visual web developer on windows server 2003.
Any ideas or help.
Hi I always get good reply from u all, thank you,
I have copied my asp.net website from one server to another. they administrator made necessary modification on IIS manager . and able to see the website on browser. but now i can't loging to system using old password.
I tryed to create new password then also it gives error
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)
Here I have used asp.net login control and membership class. Do I need to make nay changes in code.
I have already modified server name in connection string in web config file.
any one can say what is the problem and how to solve this.
thanks
Pat
Hi ,
I am struck with one issue.Plz help me out if you have any solution for this one.
Details
Front End - Visual Studio 2005
Back End -SQL Server 2000 (Remote database)
Error i am getting is
{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)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at 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) at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open() at dbcAccess.clsSQL.GetConnect() in C:Documents and SettingskerlsgMy DocumentsVisual Studio 2005ProjectsdbcAccessdbcAccessclsSQL.cs:line 296}
{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)
Hi,I have SQL Server Express Edition. I tried working out some ASP.NET Labs in my local system. Here is the link of the Virtual Lab which I tried. http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?EventID=1032286906&EventCategory=3&culture=en-US&CountryCode=USI recieve this error in my local system. 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 tried working out solutions from various websites. But the no solution is effective. Could anyone help me in solving this issue.
View 3 Replies View RelatedI get the below error only when my IDE open. It connects well when it is found closed.
[SqlException (0x80131904): Cannot open user default database. Login failed.Login failed for user 'JPASPNET'.]
I could solve this by giving the logged in windows user to impersonate under IIS window > WEBSITE > ASP.NET tab > EDIT CONFIG > APPLICATION tab
But I wish someone could give me the proper solution.
I almost tried all from giving ASPNET user as a administrator to configuring the same in Express management tool.
Environment: XP pro, VWD and SQL Express
Hi there,
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!
Post installation of SQL Server 2014 Express edition, I am able to connect to the Database Instance.
But while opening a new query window in SSMS or opening a table getting the error:
Package 'RadLangSvc.Package, RadLangSvc, Version 12.0.0.0, Culture=Neutral, Public Token=89845dcd8080cc91' failed to load
Object reference not set to an instance of an object. (mscorlib)..Have already tried installing the componentsDACProjectSystemSetup_enu.msi, TSql LanguageService_enu.msi, DACFramework_enu.msi from path VS 2010 WCU DAC.
Hi there,
Here we have got a
asp.net application that was developed when database was
sitting on SQL server 6.5. Now client has moved all of their databases
to SQL server 2000. When the database was on 6.5 the previous
development team has used oledb connections all over. As the databases
have been moved to SQL server 2000 now i am in process of changing the
database connection part. As part of the process i have a login
authorization code.
Private Function Authenticate(ByVal username As String, ByVal password As String, ByRef results As NorisSetupLib.AuthorizationResult) As Boolean
Dim conn As IDbConnection = GetConnection()
Try
Dim cmd As IDbCommand = conn.CreateCommand()
Dim sql As String = "EDSConfirmUpdate" '"EDSConfirmUpdate""PswdConfirmation"
'Dim cmd As SqlCommand = New SqlCommand("sql", conn)
cmd.CommandText = sql
cmd.CommandType = CommandType.StoredProcedure
NorisHelpers.DBHelpers.AddParam(cmd, "@logon", username)
NorisHelpers.DBHelpers.AddParam(cmd, "@password", password)
conn.Open()
'Get string for return values
Dim ReturnValue As String = cmd.ExecuteScalar.ToString
'Split string into array
Dim Values() As String = ReturnValue.Split(";~".ToCharArray)
'If the return code is CONTINUE, all is well. Otherwise, collect the
'reason why the result failed and let the user know
If Values(0) = "CONTINUE" Then
Return True
Else
results.Result = Values(0)
'Make sure there is a message being returned
If Values.Length > 1 Then
results.Message = Values(2)
End If
Return False
End If
Catch ex As Exception
Throw ex
Finally
If (Not conn Is Nothing AndAlso conn.State = ConnectionState.Open) Then
conn.Close()
End If
End Try
End Function
''' -----------------------------------------------------------------------------
''' <summary>
''' Getting the Connection from the config file
''' </summary>
''' <returns>A connection object</returns>
''' <remarks>
''' This is the same for all of the data classes.
''' Reads a specific
connection string from the web.config file for the service, creates a
connection object and returns it as an IDbConnection.
''' </remarks>
''' -----------------------------------------------------------------------------
Private Function GetConnection() As IDbConnection
'Dim conn As IDbConnection = New System.Data.OleDb.OleDbConnection
Dim conn As IDbConnection = New System.Data.SqlClient.SqlConnection
conn.ConnectionString = NorisHelpers.DBHelpers.GetConnectionString(NorisHelpers.DBHelpers.COMMON)
Return conn
End Function
in the above GetConnection() method i
have commented out the .net dataprovider for oledb and changed it to
.net dataprovider for SQLconnection. this function works fine. But in
the authenticate method above at the line
Dim ReturnValue As String = cmd.ExecuteScalar.ToString
for some reason its throwing the below error.
Run-time exception thrown : System.Data.SqlClient.SqlException - @password is not a parameter for procedure EDSConfirmUpdate.
If i comment out the
Dim conn As IDbConnection = New System.Data.SqlClient.SqlConnection
and uncomment the .net oledb provider,
Dim conn As IDbConnection = New System.Data.OleDb.OleDbConnection
then it works fine.
I also have changed the webconfig file as below.
<!--<add
key="Common" value='User ID=**secret**;pwd=**secret**;Data
Source="ESMALLDB2K";Initial Catalog=cj_common;Auto
Translate=True;Persist Security Info=False;Provider="SQLOLEDB.1";'
/>-->
<add key="Common" value='User ID=**secret**;pwd=**secret**;Data Source="ESMALLDB2K";Initial Catalog=cj_common;' />
Please help. Thanks in advance.
Hi,
I am using SSRS Microsoft SQL Server Reporting Services Designers
Version 9.00.1399.00. I want to open linked report in new window.
I tried whats mentioned in http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=240172&SiteID=1 but i get an error on Window.Open method.
How do I solve the problem?
Thanks
Hi I have some simple questions of for MS SQL. Say I have a database with a table Called Company. In this table I have Employee's with these columns(EmployeeID<PK>, FirstName, LastName) Now I am inserting some data like this.EmployeeID FirstName, LastName------------------------------------------------------- 1 Bob Smith2 Joe Mitter Now Say I have this situation(this will look weird for this example but for another examples and the stuff I am working on it make sense). Say I have a new Employee Called Jessica White. Jessica must be the second record. So I need to insert it between the 1st and 2nd record. Like I said I am very noob at doing this stuff so I am not sure how I would do it. I first was going to my table and then went to modify. This brought up a blank grid with all my column names. I then started to add all my data in. I found out later to make my life easier I need to enter in certain spots null rows in it to help with formating(otherwise I would have had to figure out a way to do make null rows with c# code). I then went back to this grid and I tried to add a record before the data just like you if you used excel. I quickly found out you can not do that. This resulted me having to recreate the table and reput the all the data back in(this told me I was doing something very very wrong). I was then thinking of writing it with a query and I been playing around with it and still running into problems like say I had a table called test with a coloum called id<PK> and testss(yes bad names just playing around though).So if I did something like this:INSERT INTO testVALUES(2)INSERT INTO testVALUES(1)SELECT * FROM testNow I am trying to figure out how to insert something between rows 1 and 2. Would I have to drop the table or delete all the data and then run a saved copy of my script with the added change or what?My second question is what is a good site that has examples of all the commands(I don't want to read a book on it I have had a oracle sql course and well it was just boring and I did not learn too much.). I think the best way for me to learn is just continue doing my site and picking it up on the file and when I got a problem read about it and ask on the forums. Thanks
View 11 Replies View RelatedVery strange event. Installed new machine into NT 4 network, which has a PDC and a BDC. We have copies of all SQL databases on both the PDC and the BDC. I installed SQL 7 on new workstation. Throught Control Panel/ODBC connections I can reach the SQL files on bith the PDC and BDC. Howver when I try to use Enterprise Manager, I can connect to the tables on the PDC, but not the BDC. SQL lists both machines in the server group, but only allows me to access tables on PDC! Here is the Event information that I recived on the BDC:
The computer IVR1 tried to connect to the server NTBACKUPSERVER using the trust relationship established by the MASSCOM domain. However, the computer lost the correct security identifier (SID) when the domain was reconfigured.
How do I fix this?
HI all,
I have a problem here. I am having two computers both are loaded windows NT 4.0 AND SERVICE PACK 4 AND ALSO I INSTALLED SQL SERVER 7.0.
Now i want to connect those two servers , so pls anyone suggest me how i have to do.I connected both servers thru HUB.
Pls suggest me..
thank u..
--ram
I figure anyone who has done VB and SQL Server might have run into this.
>
> I am set up as a DBO on SQL Server with access to db1 and db2. My default
> is set to db2. From my local machine, I can log through VB(DSN-less) in
> to
> either one without a problem. BUT, if I go to another machine and try to
> log in through VB(DSN-less) to db1, it rejects my login. Here is my
> connect
> string :
>
> UID=xxxxxxx;PWD=pedro;DATABASE=db2;DRIVER={SQL
> Server};SERVER=Athens;DSN=``;
>
> The only thing that changes when I log into db1 is the database parameter-
> "DATABASE=db1".
>
> Am I doing something wrong here? Is there something that needs to be
> setup
> differently in SQL Server? Do I need to put something else into the
> connect
> string?
>
> This happens for the other developers as well, not just me.
>
> Any help would be GREATLY appreciated.
I am looking for some ideas on the following DTS challenge: I need to import the data from an Access 97 database into a set of tables(about 25). The location of the database can be anywhere on the users drive(s) and I will need to get the location of the database from a registry setting and use it to define the connection.
Thanks
We have a case where a user is using Excel sheet to connect the database on SQL2K and generally his connection runs in the upper 150 in numbers. He generally gets more than 150 connections open.
I know that it's not a good thing, but is there a way to limit it or even kill it after it reaches max?
What are the disadvantages of opening too many connections?
I'm getting the classic message "The timeout period elapsed prior to obtaining a connection from the pool" etc when connecting to my SQL Server 2005 Express from a .Net application.
Then I try connecting, simultaneously, from a simple ASP.net thing I wrote just for testing and this works fine. So, then the connection pool can't be full, can it? Or, does each application have its own pool??
Ok, yeah, I'm being lazy...
Any comments?
Hi
I have heard that SQL Server can support upto 10,000 users,
What if my application , connects to the sql & does not do antything then , why does the SQL server slow down with just 700 users.
I mean just the connection is established & NOTHING further is done.
Thanks
hi,
have been looking for ages for somewhere that might be able to answer my question so thought i'd try here.
anyhows, my place of work have goldmine 6.7 running on a server. the problem is that some of the client machines seem to be taking 2 connections to the server when logged into goldmine and some only 1.
when looking at the open sessions it shows a second for some users accessing pipesqlquery. all users show this but only some show it as a second connection.
please any ideas
Is there any type of performance gain to using DSN connections? Or any real advantage to using them over DSN-Less connections?
--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia
As most of you are aware, when you close a VB.NET connectrion to a SQLserver, the connection doesn't actually drop right then and there.From my tests, using VB.NET 2003 and SQL2K, the connection doesn'ttimeout and drop off for 6 1/2 minutes. How can one force thisconnection to immediately drop off with code?
View 12 Replies View RelatedHiI am a dba for sql server recently we are facing problems with the no ofconnections.we have a database called ( x ) every day almost million users is using thatdatabse after some time the cpu showing 100 percent utilization an errorthrowing like odbc error connection so in that case no one is able to connectto the server.when we pause it then restart the server for some time it looksgood then again it will go to 100 % utilization.but there is no blockingoccuringif anyone knows this problem please helpyour help would be appreciated.Thanks--Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forum...eneral/200510/1
View 4 Replies View RelatedHi I would like to know about connections of sa, where SQL store thenumber of connections
View 1 Replies View Relatedim writing a service that will be on a app serverthis is one of many service's to process Recordsets from Sql Server...1 service may have 20 Processes to Completeexamplemain class PreProcess()method CleanNames();method updateNames();methodr ValidateContracts();Various things must be completedSuedo Example belowMy question isin the main class make my connection and keep it for all sub membersor connect in each memberA. This Way opens connection...closes connectmethod cleanNames()1 get connection2 get recordset3. Close Connection4 process Recordset5. Open Connection4 Commit Changes6. Close Connection()finished cleanames()ormethod cleanNames()1 get connection2 get recordset3 process Recordset4 Commit Changes5. Close Connection()finished cleanames()orthis final way is in the parent Class or Calling Functionsends in the connections holds it open till the service iscompleted of all jobs its Suppose to dothen releases the connectionmethod cleanNames(oconn) connection as parameter1 get recordset2 process Recordset3 Commit Changesfinished cleanames()ThanksDaveP
View 1 Replies View RelatedI have a c# winapp and use SqlHelper to CRUD operation wint an sql server 2005 database, every time mi data access layer opens an connections, I do Close() and Dispose() after the transaction, and I execute SP_WHO and the database server shows me that my winapp has generated a lot of connections, I dont understand why since i clean every connection, Also, I m not using pooling since my connection string. Has someone has faced this before??
Best Regards
Joseph