.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
Sharon Maxwell
Post New Web Links

SQL server connection - keep it opened all the time, or open/close on demand ?

Posted By:      Posted Date: October 21, 2010    Points: 0   Category :Sql Server
 

Hi,

I have a VB6 app that gets data from several devices and has to store it in a SQL server 2005 database.
It may have to store small amounts of data (a few KB at most) somewhere between 5 and 500 times per day.
My app only sends data to DB, doesn't read anything.

My question is what's better in this case:

a) the app opens a connection to DB at startup and closes it at exit, or
b) when data arrives, the app opens a connection, sends data, then closes the connection

Thanks




View Complete Post


More Related Resource Links

Open SQL Server Connection Troubleshooting

  
Hello, This is regarding ASP.Net (VB) 2.0 and SQL Server 2008.  My project's (there is only one in my solution) has a "Start Action" set to a "Specific Page" (..\Forms\frmMain.aspx).  Occasionally (though not always) a large number of connections (30+) will be opened prior to the frmMain.PreInit event.  These connections stay open for quite some time.  I am closing all of my connections with extreme prejudice.  In my "finally" clause of my try...catch I have myconnection.close.  Immediately after the "End Try" I have myconnection.dispose (redundant, I know).  I have accounted for all connections.  I placed a breakpoint on frmMain.PreInit and then (sometimes) a large number of connections (used SSMS to run sp_who and sp_who2) will have just opened.  What would open a large number of connections prior to the starting page's preinit event?  I am closing (and then disposing) all of my connections.  I thought that I must be missing a myconnection.close in the "finally" clause, but I have looked repeatedly and cannot find anywhere that a connection is open and then not closed.  Also, I do not believe that I have even opened a connection prior to the frmMain's preinit event.        

how many open connection is recommended to leave - in sql server 2008 Enterprise Edition

  
hi i have my C# program that work with sql-server 2008 Enterprise Edition i have 40 users that connect to this database. i dont close the connection. how many open connection is recommended to leave ? or is it better to close connection ? thank's in advance

Named Pipes Provider, error: 40 - Could not open a connection to SQL Server

  

This was originally posted in the SSIS Forum, but a member of the IS team suggested it be moved here.  "Most recently I got this error (Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) so does this mean that SQL Server is still trying to use named pipes even if I only have TCP/IP enabled in SQL Server Configuration Manager?"

I'm having the same issue, and here's our scenario:

  1. Installed SQL Server 2005 Developer Edition on a machine with WinXP SP2 and enabled remote connections over TCP/IP
  2. Installed SQL Server 2005 Standard Edition on a machine with Win2003 SP1 (remote connections over TCP/IP enabled by default)
  3. Attempted to 'Copy Database' from Developer Edition TO Standard Edition using 'Detach and Attach' method and recieved the error on the Win2003 machine: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server
  4. A

provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server

  
after lots  of goes and back, I have the same problem but it is related to my Visual studio too. Because I can access to the same database  for new projects but when I use an existing project with a new default page I get the same error.
I am using visual studio 2005 connecting to MSSQL 2008 in server that has an instance of SQLServer express 2005 too
the code for boht new and existing project is bellow

I appricate any help

 

protected void Page_Load(object sender, EventArgs e)

{

 

SqlConnection cn =

Max Idle Time for a Connection in SQL Server 2005

  

Can you please let me know where I can set the MAX IDLE TIME for the SQL SERVER CONNECTION.

IS this the value set for the KEEp Alive propoerty in the sql server configuration Manager.

Thanks.


Is there any script for Open/Close sql-server 2008 ?

  
hi

Is there any script (any batch file) for Open/Close sql-server 2008 ?

thank's in advance

provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server

  

On windows 2008 using sql 2k8 standard edition. I have a couple of apps that run on the server. One works perfectly and attaches to sql with no problem. The second app that uses THE EXACT SAME CONNECTION STRING and gives me the following error,

provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server

How can one app work and the other not? What can I check for?

Thanks,


Error rsReportServerDatabaseLogonFailed : The report server cannot open a connection to the report s

  

Hello, All

I face issue on Sql server 2008 for deployee report first time on report server and report manager,,,,,

Error rsReportServerDatabaseLogonFailed : The report server cannot open a connection to the report server database. The log on failed.

Deploy complete -- 1 errors, 0 warnings

========== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ==========

========== Deploy: 0 succeeded, 1 failed, 0 skipped ==========

Anyone please help me as soon as possible............


Named Pipes Provider: Could not open a connection to SQL Server [2]

  
Hi

I'm a complete sql/asp.net newbie and want to try this tutorial:
http://beta.asp.net/GuidedTour/

First I installed:
Visual Web Developer 2005 Express Edition Beta 2

systemsettings\software shows the "sql server 2005 express edition ctp (sqlexpress)" installed

mmc\service shows me the SQL Server (SQLExpress) is running

Following the guided tour I use the commandline, type cmd, and type in the commandbox:
 
"C:\Program Files\Microsoft SQL Server\90\Tools\binn\SQLCMD90" -S "localhost\SqlExpress"

Instead of localhost I also tried computername.smallbusiness.local (thats my fully domain name).

However I always receive the error:

Named Pipes Provider: Could not open a connection to SQL Server [2]

Thank you very much for all your help


Open vs Close Connection

  

Hi Guys,

I have a question related to  leave a SQL connection open or close it.

Here is the scenario...

I have an Intranet page that monitors a Robot, that webpage needs to be up 24hours/7days a week.

Robot sends information to a SQL database every couple of seconds or minutes depending on the work it performs.

My page has an AJAX Timer that triggers every 5 sec, in that page I have to query the database (simple SELECT - no more then 15 lines) and show my info in a gridview.

The question is: Should I open the connection and leave it open or should i open and close every 5 secs?

   


SQL Server 2005 CPU use 100% and Connection Time Out

  

Hi,

I have a thin C# application with SQL server 2005 standard Edition . Database size is 15GB . The server has 16 GB RAM and 2.6 Ghz Xeon processor, with 4 cores. Application has lot of stored procedures performs some calculations and transfer the data from one database named a to second database named b. Users can also get the reports from report database .

1: The application is much slow when it perform analysis (some calculations ) 

2: CPU usage goes to 100% and sometimes application crashes with this message.

timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

I have to optimize the application . I have found some duplicate non cluster indexes.

for example if i have primary key PK index (clustered) on the fields 1, 2 ,3

there  is another index which is non clustered on the same fields (1,2,3) 

there are around 10 to 15 tables with the same duplicate non clustered indexes.

I have few Questions :

How can i  configure the sql server 2005 to perform parallel executions of queries to utilize the all cores of CPU.

2: How can i know which query or store procedure is taking too much execution time and getting system resources.

3: How to know which index is fragmented&n

SQL 2008 could not open a connection to SQL server [53]

  

3 computers connect fine, but two others cannot connect to the DB.

 


named pipes provide: could not open a connection to SQL server [2]

  
I am trying to connect to an SQL Database on SQL 2008, through an ODBC link but keep getting the above error. I do not know how to fix it. I am trying to make the connection on the server?  dont know whether that makes any difference

Essential SQL Server Date, Time and DateTime Functions

  
The essential date and time functions that every SQL Server database should have to ensure that you can easily manipulate dates and times without the need for any formatting considerations at all.

Date/Time Conversions Using SQL Server

  
There are many instances when dates and times don't show up at your doorstep in the format you'd like it to be, nor does the output of a query fit the needs of the people viewing it. One option is to format the data in the application itself. Another option is to use the built-in functions SQL Server provides to format the date string for you.

connection to SQL Server files (*.mdf) require SQL server express 2005 to function properly.

  

I dont have the SQL EXPRESS installed instead I have SQL Standard Edition.

 I have two SQL Server instances installed.

 1- UserLT (this is sql 2000)
2- UserLT\SQL2005 (this is SQL 2005 named instance)

But when i try to add a database to my VS website project I get the following error:

Connection to SQL Server files (*.mdf) require SQL server express 2005 to function properly. please verify the installation of the component or download from the URL: go.microsoft.com/fwlink/?linkId=4925

I went in Tools>Opetions>DataBase tools>Data Connection>Sql Server Instance Name (blank for default)

and changed the "SQLEXPRESS" to "USERLT\SQL2005".

But I still get the same error message. Any ideas how i can resolve this issue?


A connection could not be made to the report server http://localhost/ReportServer.

  

Hi,

  • I created a Report using Business Intelligence Studio. Its working working well, i can able to view records. But when i try to deploy the report an error occurs specifying that "A connection could not be made to the report server http://localhost/ReportServer. (Microsoft Report Designer)".
  • I open the IIS 7 and find out virtual directories "Reports" and "ReportServer" are available.
  • I check on the browser and type "http://localhost/reports".  I find the browser direct to this url: http://localhost/reports/Pages/Folder.aspx and find error specifying the following one:

         Error:The report server is not responding. Verify that the report server is running and can be accessed from this computer.

  • when i type http://localhost/reportmanager, i found the error:

    HTTP Error 404.0 - Not Found

    The resource you are looking for has been removed, had its name changed, or is temporarily unavailable.

    <
Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend