How Do You Close The Connection At The SQL Server?
Apr 19, 2008
How do you close the connection at the SQL Server? I have a backup I want to resotre to SQLExpress using SQL Management Studio Express. When I try, the SQL tells me he can't because the database is in use. I don't realy care about this I still want to restore the database. So I figured it's because there are some active connection to the database. My gues is I have to close those, but how? I also know I can restart SQLExpress and the connection will drop, but this server has more then one database, so I cant always do that. I'm looking for some T-SQL like DROP ALL CONNECTION.
we have roughly 22 people connected to one database. But after a while, their applications begin to drag due to in and out communication with the server. When i check the active connections on the sql server, some times i see 157 active connections, please how to i set a timeout or connection interval close, so as reduce the heavy load being put on the server. Or how can i automatically close connections when they get higher than 50 connections.
Hi, I've created a simple hit counter in my Session_Start event... Dim myDataSource As New SqlDataSourcemyDataSource.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString1").ToStringmyDataSource.UpdateCommand = "UPDATE Stats SET Hits = Hits + 1"myDataSource.Update() It works fine but do I need to close the connection after I've finished with it or is this ok? Thanks for your help.
If this is used eg. Return selectCommand.ExecuteReader(CommandBehaviour.Default) Here do i need to close the connection in finally explicitly???? Are there any links which can answer this kind of question ??? Thanks for any help in advance.....
How do i close a current connection to a database using t-sql?I fail some time to drop the database getting messages that it'scurrently in use.Using the wizard to delete the database, i could check the option toclose all connections to the db, but how do i do it using t-sql?best regards
Then I Open the Connection. Now, in some ways I need to drop a Table. Before I do so, I disconnect the DB but I'm using only the line
cn.Close();
I saw om the Watch attributes that its not a real disconnect to the DB. When I try to Drop the Table I get this message: The system timed out waiting for a lock. [ Session id = 25,Thread id = -2096711882,Process id = -2090594918,Table name = Movies,Conflict type = x lock (s blocks),Resource = DDL ]
And the Table doesn't drop.
Is it related to the constructor line I made? What is the best solution to Drop a table..and then to get connected again with the DB.
I write a .NET Windows Form that connect to SQLExpress datafile. After updating data, I want to zip the .mdf file and send email. However, I got an exeption that the .mdf file is used by other thread so I cant zip. Even I try to close all connection, I still cant zip.
Is there any way to detach/unlock .mdf file connecting by SQLExpress?
I designed a DTS package with eleven different connections. When I try to see the proberties of a connection the enterprise manager immediatly closes without giving me a warning. This happens only with two connections (in my example M1 and M2). This mistake has no influence to the execution of the package. In my opinion it is a fault of the DTS package desinger but may be that I made something wrong. Is there anybody who knows this mistake? Thanks for your help.
If CurrentProject.IsConnected Then MsgBox "Base ya Conectada.Procedemos a cerrarla primero", vbOKOnly If SQLConexion.State = adStateOpen Then SQLConexion.Close SQLConexion.Close End If SQLConexion.Open SQLSentencia AbreConexionSQLSERVER = 0
If I detect that the connection is opened while I am initializazing varriables, I want to close always the connection and then begins my aplicattion always opening the connection, but it always returns a error 91..
The following error message was displayed in the SQL server event log when the client application attempted to connect to sql server "The Tabular Data Stream (TDS) version 0x72020002 of the client library used to open the connection is unsupported or unknown. The connection has been closed. [CLIENT: XX.XX.XX.XX]"
Please i have a question, I want to use temporary tables to handle my forms child tables data.
My question is after i load the data from #temporary table to my dataset then i will close my database connection. Does my temporary tables gets dropped off as soon as i do the connection.close.
or do i need to drop the #temporary table by code.
Which one is right.
I know #temporary tables are specific to the users(session)
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!
I was having this problem:http://groups.google.com/group/micr...78?dmode=sourcewith it taking an inordinate amount of time to enumerate the databaseson my local SQL Server instance, so I went through all the localdatabases and unchecked "Auto Close" on the "Options" tab of theDatabase properties in Enterprise Manager.This cleared up the problem nicely. In EM or through SQL-DMO, thedatabases on the local server enumerate quite quickly now. However, anynew databases that are created have "Auto Close" checked, so eventuallythe original problem will resurface.Is there some way to tell SQL Server to create any new databases with"Auto Close" set to No?
My SQL server disk space is getting close to full capacity which is causing certain reports that we run via the SQL server to time out because I don't think there is enough space on the server.
Any tips on cleaning out a SQL server? Are there any folders that can absolutely be deleted to clear space? I know on a local computer that the %temp% folder can be cleaned out. I know when dealing with servers you do not want to make to many changes because it can cause major problems down the road.
I am unable to debug the first Script Task (not Script Component) since adding a second Script Task in one of my packages. The script completes successfully and goes green but the above error is displayed followed by another which is not mentioned here as I'm sure it's because of the first error. The second script task debugs fine so I know this is not the problem. But if I delete the second Script Task the first task starts debugging.
I have moved the breakpoint around in the first script and the only line that seems to work is the first line within the main method. It's irrelevant what this first line of code does. I know there is nothing wrong with the script tasks themselves as I have an exact copy in another package that debugs just fine.
I am trying to select rows from a SQL2000 database table and then write a random number back into each row. I have already looked into do it all in a SP but there are well documented limitations for the SQL RAND function when called in the same batch, so I need to somehow do in .Net what I already have working in classic ASP. I can't get the UPDATE (part two section) to compile. I don't know how to call the stored procedure inside the 'foreach' loop or extract the SP parameters. I have it working in classic asp but am having a lot of trouble converting to .Net 2.0. Is the below even close to working? // stored procedure to write externally generated random number value into database PROCEDURE RandomizeLinks@L_ID int,@L_Rank intASUPDATE Links SET L_Rank = @L_RankWHERE (L_ID = @L_ID) // Part One select links that need random number inserted. public DataTable GetRandLinks() { SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString1A"].ConnectionString); SqlCommand cmd = new SqlCommand("RandomizerSelect001", con); cmd.CommandType = CommandType.StoredProcedure; SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = cmd; DataSet ds = new DataSet(); try { da.Fill(ds, "Random001"); return ds.Tables["Random001"]; } catch { throw new ApplicationException("Data error"); } } // Part Two I need two write a random number back into each row protected void Button1_Click(object sender, EventArgs e) { GetRandLinks(); int LinkID; // this generates unassigned local variable "LinkID' error int LRank; // this generates unassigned local variable "LRank' error SqlConnection con2 = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString1A"].ConnectionString); SqlCommand cmd2 = new SqlCommand("RandomizeLinks", con2); cmd2.CommandType = CommandType.StoredProcedure; cmd2.Parameters.AddWithValue("@L_ID", LinkID); cmd2.Parameters.AddWithValue("@L_Rank", LRank); SqlDataAdapter da2 = new SqlDataAdapter(); int RowIncrement; RowIncrement = 0; DataTable dt = GetRandLinks(); foreach (DataRow row in dt.Rows) { System.Random myRandom = new System.Random(); int LinkRank = myRandom.Next(25, 250); LRank = LinkRank; da2.UpdateCommand = cmd2; RowIncrement++; } }
Hello, I'm trying to run the following snippet as Console app, and I get an error: "a reader is already open for this connection, and should be closed first" I tried to manually say: SqlDataReader.Close() in the begining of the code, but still get the error, Any suggecstions how to manually close the reader? thank you ---------- here's the code -----------using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; namespace ADO.NET { /// <summary> /// Summary description for Class1. /// </summary> class Class1 { /// <summary> /// The main entry point for the application. /// </summary> [STAThread] static void Main(string[] args) { SqlConnection cn = new SqlConnection ("SERVER=MyServer; INTEGRATED SECURITY=TRUE;" + "DATABASE=AdventureWorks"); SqlCommand cmd1 = new SqlCommand ("Select * from HumanResources.Department", cn); cmd1.CommandType = CommandType.Text; try { cn.Open(); SqlDataReader rdr = cmd1.ExecuteReader(); while (rdr.Read()) { if (rdr["Name"].ToString() == "Production") { SqlCommand cmd2 = new SqlCommand("SELECT * FROM " + "HumanResources.Employee WHERE DepartmentID = 7", cn); cmd2.CommandType = CommandType.Text; SqlDataReader rdr2 = cmd2.ExecuteReader(); while (rdr2.Read()) { } rdr2.Close(); } } rdr.Close(); } catch (Exception ex) { Console.WriteLine (ex.Message); } finally { cn.Close(); } } } }
I am inheriting all my webpages from a BasePage which is storing my sqlconnection to the database. I open the connection in the "protected override void OnInit()" method (is this right?) but I don't know where I should write the close the connection. Can anyone give me any suggestions? Regards EO
Hi everybody,How is it possible to close any open connections from the connection pool explicitly like on the log off page? So when the users log off from the application I want to close all connections that were opened during the use of application.asp.net forums is the best....thanks,Murthy here
Hi,since yesterday I have this message everytime I try to open Sql Server"Enterprise manager"; I reinstalled the outfit completely without successany solutionthanksFernand
Problem: Connections to SOAP endpoints never close even if app initiating call was shutdown several days earlier. Create Endpoint statement included at bottom
Questions: 1 Why? 2. Is there a configuration option I need to set?
[1] Client side: Multiple clients from XMLSpy running on XP Pro to WebSphere Process Server running on AIX
[2] Server side:
What is the MS SQL version? SQL Server 2005 SP2
What is the SKU of MS SQL? Enterprise
What is the SQL Server Protocol enabled? Shared Memory | and TCPIP
Does the server start successfully? YES If SQL Server is a named instance, is the SQL browser enabled? YES
What is the account that the SQL Server is running under? Domain Account Do you make firewall exception for your SQL server TCP port if you want connect remotely through TCP provider? NO
Do you make firewall exception for SQL Browser UDP port 1434? In SQL2000, you still need to make firewall exception for UDP port 1434 in order to support named instance. NO [2a] Tool Used to Connect: Multiple clients from XMLSpy running on XP Pro to WebSphere Process Server running on AIX
[3] Platform:
What is the OS version?Windows 2003 SP2 Do you have third party antivirus, anti-spareware software installed? McAfee. Exceptions have been set in accordance with MS SQL Server documentation
ENDPOINT:
CREATE ENDPOINT [TestEndpoint]
AUTHORIZATION [AuthID]
STATE=STARTED
AS HTTP (PATH=N'/SqlSSL/testEndpoint', PORTS = (SSL), AUTHENTICATION = (BASIC),
Given that the conversation states are as follows: (Thanks Rushi!)
Event Initiator Endpoint state Target Endpoint state
BEGIN DIALOG SO -- from Initiator to Target
SEND message(s) CO -- from Initiator to Target
Target receives a fragment CO SI of the first message sent or receives out of order message
Target received entire CO CO first message
END conversation at CO DO target
Initiator receives EndDialog DI DO message from target
Target receives ACK for the DI CD EndDialog message sent
END conversation at CD CD Initiator
When does the 30 minute timer start for clearing the conversation from the sys.conversation_handles table? Is it the same for both sides (initiator and Target) ie, the end conversation at the Initiator. I guess it must be just in case a resend is necessary.
Here's a code test I did place in the Form_Load of my C#.NET app.
SqlConnection cnn = new SqlConnection();
cnn.ConnectionString = "bla bla...";
cnn.Open();
cnn.Close();
cnn.Dispose();
In SQL SERVER PROFILER, the Audit Login takes place as soon as I hit the .Open() but when I hit the .Close() OR the .Dispose() there's no Audit Logout. The Audit Logout is issued only when I quit the application.
Hi I have double checked my code and cannot pin down why I am getting the error "There is already an open DataReader associated with this Command which must be closed first. " on the lne: Line 95: DR_IndJobPostings = oComm_IndPostings.ExecuteReader(); I have closed the DR_IndJobPostings object after every use of it as see n on line number 175
----------- Code--------------1 /// <summary> 2 /// Will generate and email job alerts based on the frequency 3 /// </summary> 4 /// <param name="frequency">WEEKLY or MONTHLY</param> 5 /// <param name="oServerIN">Instance of Server from ASPX page</param> 6 public void hk_DoAlertByFreq(string frequency, HttpServerUtility oServerIN) 7 { 8 SqlConnection oConn = new SqlConnection(ConfigurationSettings.AppSettings["CString"]); 9 oConn.Open(); 10 11 SqlCommand oComm; 12 13 emailSystems oEmail = new emailSystems(); 14 HttpServerUtility oServer = oServerIN; 15 16 bool validCall = false; 17 bool industryHasPostings = false; 18 string sEmail = ""; 19 string sEmailTemplate = ""; 20 string sVacListForEmail = ""; 21 22 int IJPost_VacId = 0; 23 int IJPost_EmpId = 0; 24 string IJPost_Req = ""; 25 string IJPost_KeyRes = ""; 26 string IJPost_VacTitle = ""; 27 string IJPost_VacJobTitle = ""; 28 string IJPost_VacUrl = ""; 29 30 int loopCounter1 = 0; 31 32 string CandEmailAddress = ""; 33 34 oComm = new SqlCommand(); 35 oComm.Connection = oConn; 36 oComm.CommandType = CommandType.Text; 37 38 SqlCommand oComm_IndPostings = new SqlCommand(); 39 oComm_IndPostings.Connection = oConn; 40 41 SqlDataReader DR_Industries; 42 SqlDataReader DR_IndJobPostings; 43 SqlDataReader DR_AlertList; 44 45 if (frequency == "WEEKLY" || frequency == "MONTHLY") 46 { 47 validCall = true; 48 } 49 50 if (validCall) 51 { 52 if (frequency == "WEEKLY") 53 { 54 sEmailTemplate = oEmail.readTextFile("/email_templates/weeklyJobAlert.txt"); 55 } 56 57 if (frequency == "MONTHLY") 58 { 59 sEmailTemplate = oEmail.readTextFile("/email_templates/monthlyJobAlert.txt"); 60 } 61 62 sSql = "" + 63 "SELECT [id],[industry] FROM S_Utils_Industries " + 64 "WHERE [active] = 1"; 65 oComm.CommandText = sSql; 66 DR_Industries = oComm.ExecuteReader(); 67 68 if (DR_Industries.HasRows) 69 { 70 // 71 // Loop through each active industry 72 // 73 while (DR_Industries.Read()) 74 { 75 industryHasPostings = false; 76 iCurrentIndustryId = (int)DR_Industries.GetSqlInt32(0); 77 sCurrentIndustryText = DR_Industries.GetSqlString(1).ToString(); 78 79 // Get all active vacancy postings for this 80 // industry 81 sSql = "SELECT [id]," + 82 "[emp_id], " + 83 "[vac_Requirements]," + 84 "[vac_KeyResp]," + 85 "[vac_VacTitle]," + 86 "[vac_VacJobTitle]," + 87 "FROM [S_Vacancies] " + 88 "WHERE [vac_VacIndustry_Id] = " + iCurrentIndustryId.ToString() + " AND " + 89 "[status] = 1 AND " + 90 "[vac_ListingStart] >= '" + gf.SqlDateTimeFormat(DateTime.Today,1) + "' AND " + 91 "[vac_ListingEnd] < '" + gf.SqlDateTimeFormat(DateTime.Today, 1) + "'"; 92 93 oComm_IndPostings.CommandText = sSql; 94 95 DR_IndJobPostings = oComm_IndPostings.ExecuteReader(); 96 97 // 98 // If there are job vacancy postings for the industries 99 // 100 if (DR_IndJobPostings.HasRows) 101 { 102 industryHasPostings = true; 103 sEmail = sEmailTemplate; 104 105 // 106 // Loop through the job postings for this industry 107 // 108 while (DR_IndJobPostings.Read()) 109 { 110 IJPost_VacId = (int)DR_IndJobPostings.GetSqlInt32(0); 111 IJPost_EmpId = (int)DR_IndJobPostings.GetSqlInt32(1); 112 IJPost_Req = DR_IndJobPostings.GetSqlString(2).ToString(); 113 IJPost_KeyRes = DR_IndJobPostings.GetSqlString(3).ToString(); 114 IJPost_VacTitle = DR_IndJobPostings.GetSqlString(4).ToString(); 115 IJPost_VacJobTitle = DR_IndJobPostings.GetSqlString(5).ToString(); 116 IJPost_VacUrl = "http://www.mann-power.net/vJDetails_FromFront.aspx?vid=" + IJPost_VacId.ToString() + "&from=myjobs"; 117 118 sVacListForEmail += IJPost_VacTitle + @" 119 120 Job title: " + IJPost_VacJobTitle + @" 121 122 Key Responsibilities 123 " + IJPost_KeyRes + @" 124 125 Requirements 126 " + IJPost_Req + @" 127 128 " + IJPost_VacUrl + @" 129 130 ============================================================ 131 132 "; 133 } 134 135 136 sEmail = sEmail.Replace("{VACANCYLIST}", sVacListForEmail); 137 138 // If there are job postings for this industry 139 // get all the people who signed up for a job alert 140 if (industryHasPostings) 141 { 142 sSql = "SELECT [S_JobAlerts].[IndustryId]," + 143 "[S_JobAlerts].[candidateEmail] " + 144 "[S_Cv_Status].[Cv_Online], " + 145 "[S_Cv_Status].[usingShortResume], " + 146 "[S_Cv_Status].[iHasHadIntro] " + 147 "FROM [S_JobAlerts] " + 148 "INNER JOIN [S_Cv_Status] ON " + 149 "[S_Cv_Status].[user_id] = [S_JobAlerts].[candidateUserId] " + 150 "WHERE ([S_JobAlerts].[frequency] = '" + frequency + "') AND " + 151 "[S_JobAlerts].[IndustryId] = " + iCurrentIndustryId.ToString() + ""; 152 153 oComm.CommandText = sSql; 154 155 DR_AlertList = oComm.ExecuteReader(); 156 157 // If there are candidates who signed up 158 // for a job alert 159 if (DR_AlertList.HasRows) 160 { 161 // 162 // Loop through each job alert for this industry 163 // 164 while (DR_AlertList.Read()) 165 { 166 CandEmailAddress = DR_AlertList.GetSqlString(1).ToString(); 167 oEmail.sendSingleMail("john.cogan@staffmann.co.za", "Mann-power Job alert", sEmail); 168 } 169 170 } 171 DR_AlertList.Close(); 172 173 } 174 } 175 DR_IndJobPostings.Close(); 176 177 } 178 179 } 180 DR_Industries.Close(); 181 182 183 oConn.Close(); 184 } // END: if (validCall) 185 186 }
should u always close everys sqldatasource connection string and the dataset associated with it, if so how do u do that (ie the proper way) and is it possible to to do it in the session area of global.aspx, NEt 2.0 or should it be done when exiting the page containing the data stuff, also same question for NET 1.0 datasets and adapters and strings,
Lets say I am selling parts for cars, trucks, suvs, vans, etc. I want a user to search for a part by selecting a vehicle type, then a vehicle make, and then a vehicle model. So Vehicle Makes have to be related to Vehicle Types (I.E: Land Rover doesn’t make a car) and vehicle models need to be related to vehicle make (I.E Toyato doesn’t make a mustang Ford does). So I am okay with those relationships individually but what is confusing me is how to ensure that a Mustang doesn’t show up as a Ford Car.
Lets say I have: vehicle_typevehicle_type_cdvehicle_type_desc
vehicle_makevehicle_make_cdvehicle_make_descvehicle_modelvehicle_model_cdvehicle_model_descThen I might have an intersect between vehicle_type and vehicle_make because certain makes don’t have certain types of vehicles. BMW doesn’t have a truck, Lexus doesn’t have a truck, so when a user selects truck as a make lexus nad BMW shouldn’t show up in the drop down.Vehicle_type_makeVehicle_type_make_keyVehicle_type_cdVehicle_make_cd I might have the same relationship between make and model:
keyboard shortcuts for management studio http://msdn2.microsoft.com/en-us/library/ms174205.aspx
Anyone know of a way to setup a keyboard shortcut to close the current window your working in ? Normally with other editors (such as visual studio) I use ctl-w to close the current window. Cant do that with sql studio.
