I am experiencing a strange problem on a new installation of SQL Server 2000, installed on Windows 2003 server. SQL server has been service packed to SP3a
The users connect to the SQL database via an MS Access project front end, when they log out of the front end the SQL server still show them as connected in Management > Current activity > process info. I can kill the processes left behind but i am confused as to why this is happening.
The database was recently moved from an SQL installation on a Windows 2000 server to this new Windows 2003 server, the problem didnt occur on the old server and no upgrades have been applied to the database or the front end since the move, which makes me think something is not right about the install.
How do I set up a job so that I close all open database connections? I think we have a leak in our code which causes our DB to go down (max connections used) roughly once every month, so we just restart the SQL server. Until we can find the exact problem I'd like to do this.
For simplicities sake let's say my database name is just "test."
I have an app that people frequently disconnect without a logout and the connection remains open in SQL Server. Does anyone know of a script or program that will go through and kill inactive connections?
This script is used by a script task within each of three child packages that are called by a parent package. Each call is made to a different OLAP cube. The first package makes the connection and runs fine. When the second package runs, it still sees the connection value from the first package and fails.
What is the best way to close the connection once it is no longer needed? I have tried the dispose() method, and that did not work.
Hi Guys I am facing a problemI am getting error Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reachedI have checked my whole code and no Connection leakage is there... but still Connections are sleeping... I have increse my pool size to 500 and now i have chaos of 500 sleeping connections... How ever I have found that even after Opening Enterprize manager i can not kill Process... How ever my administrator can kill the process from his enterprize manager... Is there some problem with my credentials or do i need to find out the leakage in my code...How ever I have checked twice or thrice but my code is Ok and i have Closed connection properly every where... I am using SQL Server 2000 and ASP.Net 1.1
HELP!!!!! HELP!!!!! SQL Server ODBC connections to virtual server that has SQL Server 2k comes back with a 'Specified SQL Server does not exist'. Can telnet to the server, can ping it as well. THe node is up and running, another virtual instance running from the same node is fine.
Hi guys Apologies if this is the wrong place to be posting this but I have posted on a few forums and have not had any usable answers to this question, so I am hoping you guys will be able to help me. I have 2 servers that are on the same domain. My ASP.NET application is running off the first which has .NET 2.0 installed, and I have an SQL Server 2000 db on the other. Whenever I try to connect I get the following error: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 connection works when I run the project in VS Development Server on my work computer, but as soon as I upload to the server I get this error. I have scoured Google but all of the sites I come across are for SQL Server 2005 and are just not suitable for solving the issue on 2000. Any help would be greatly appreciated as I cannot continue my current project until this issue is resolved. Hermiod
My question is about allowing and securing connections to SQL Server 2000 over the internet. The company that I work for has an application server that several of our clients connect to via the internet using secure .NET remoting. Basically, the clients have a desktop application that they run that creates a remoting connection to our server software and we handle the server/database part. Anyway, one of our clients now wants to use Crystal Reports to run ad hoc queries on their data that is hosted on our SQL 2000 database server behind our firewall. Obviously, opening up a port in our firewall and allowing someone to run ad hoc queries on the database makes us all more than a little nervous about security.
Has anyone else here had to deal with this sort of situation before? We'd like to set up a secure, encrypted connection for this one client, but still keep it locked down for everyone else. Is it as simple as enabling encryption and generating SSL certificates for the client machine and our server? I've only been able to find a few resources that help with bits and pieces of the problem, never anything tackling the issue as a whole. If anyone has any thoughts, experiences, links, etc. to share it would be greatly appreciated. We are a small company and no one here has experience with this sort of thing.
What are the maximum number of user connections achievable for sql server 2000 (w/ win 2000 adv server), and how are they managed - compared with sql server 7.0 (w/ win nt 4.0)? Our group is looking at upgrading our dbase server from sql server 7.0 to sql server 2000.
This is the weirdest this I have ever seen in a long time. I have MS SQL Server running on a server and use Enterprise Manager a lot. Well, the damndest thing happens when I log onto the server from the console and run Enterprise Manager. If I go into Enterprise Manager, and go to a database and then select a table and right-click, and run the "Open Table" option; the entire Enterprise Manager application mysterously closes.
This only happens from the server console and through Remotely Anywhere...it does not happen when I log onto the server from Remote Desktop.
Has anyone ever seen this before? Does anyone know a fix for this?
We have 4 sql servers which service 4 load balanced web servers (with sticky sessions) and we recently brought one of the four SQL servers online.
In the last week the new SQL Server 2000 Standard (SP4) has been exhibing orphaned connections and attempts to use invalid connections out of the connection pool on the app servers. This manifests itself in General network Errors and Timeouts (when the sql is not really doing much other than simple table requests).
Does anyone have any experience in what might be causing this on a new server. I've asked our networking people to verify that the network routing and firewall are set the same as the other three servers. The application is the same acrossed all of the app servers so I've ruled out any issues with the application not closing connections.
I have a Microsoft Cluster running on Server 2003 Entrprise. SQL 2000 8.00.2039 (SP4). 5gb physical memory installed.
With the databases online we run a test failover from the Cluster administrator. It takes about 30-40 seconds and completes without generating any Server Event log errors nor SQL log errors. Everything looks good from an administrative stand point.
However, when we test with running a series of queries to the databases, then failover, we notice that it can take up to 3 or 4 minutes before some of the databases will respond. Connections are not refused, they just sit there.
How can we troubleshoot this or does anyone have a similiar experience with this scenario?
I know it's "best practice" to dispose ado.net objects, but does it make a big difference if just the connection is closed? In other words, is the code below good enough or should the DataAdapter & Command be explicitly closed?using (SqlConneciton cn = new SqlConnection(connstr)){ SqlDataAdapter da = new SqlDataAdapter(sql,cn);DataSet ds = new DataSet();da.Fill(ds); SqlCommand cmd = new SqlCommand(someOtherSql,cn);cmd.ExecuteNonQuery(); }
hello to alli am finding my self in confusing problem. everyay log file of my application increase by 10 GB.every time i found more than 100 connections open in SDQL Server 2005 under my database. i an using SQLHELPER Class by microsoft.i ma not putting my code in try-catch block. example of one of my frequently used function is as follows: protected Int64 GetMemberID() { String SqlSelect = "SELECT * FROM MemberMaster WHERE MemberUserName= '" + Session["UserName"].ToString() + "'"; SqlDataReader dr1 = SqlHelper.ExecuteReader(ConfigurationManager.ConnectionStrings["CowcallConnectionString2"].T oString(), CommandType.Text, SqlSelect); while (dr1.Read()) { CurrentMemberID = Int64.Parse(dr1["MemberID"].ToString().Trim()); } return CurrentMemberID; } well i doubt i am opening connection and do not closing it. moreover i do not know how to close connection in above code when i am using sql helper class.please give me yours suggestion regarding my code even if it is not solving above two problems.
We need a small suggestion regarding releasing the resources utilized before closing the session we established with the SQL CE database. To the best of our knowledge we are releasing the resources properly but still some how some resources get locked and we were not able to open a new session after closing the existing session.
Are there any functions or methods available to identify the existing resources, rowsets and other components who have connection with the existing session, kindly help us in this regard since we struck up mainly with this issue. We work with EVC++ 3.0 and SQL CE 2.0
I have a VB (sorry!) app using SQL Express DB. It was all created using the wizard so I didn't have to manually deal with opening connections etc.
Now, I need to be able to email the .mdf file from within the application, but I can't do this because the file is in use.
I have tried closing all forms with DB connections and then doing it but it takes around 6 minutes before the file becomes "free" and attachable to the email.
Can anyone suggest a way of releasing the file so that I can email it?
I have setup my loop to loop through textboxes and fill the according textboxes with data it retrives, if seems to work fine, but there is a problem with opening and closing the connection below is my codeint i = 0;for (i = 1; i <= 3; i++) { //This gets the stock ID from the textbox.string stock_ID = ((TextBox)Panel1.FindControl("txtID" + i.ToString())).Text; //This is the sql statement.string sql = "SELECT [n_or_sh], [title], [cost_price], [selling_price] FROM tbl_stock WHERE stock_ID = " + stock_ID;
//This creates a sql command which executes the sql statement.SqlCommand sqlCmd = new SqlCommand(sql, myConn);
myConn.Open(); //This is a reader for the results to go in.SqlDataReader dr = sqlCmd.ExecuteReader(); //This reads the first result from the sqlReader dr.Read(); //This sets the title label text to the value of the description column.TextBox currentBox1 = (TextBox)Panel1.FindControl("txtDesc" + i); string strtxtDesc = currentBox1.Text; strtxtDesc = dr["title"].ToString(); } // end of loop myConn.Close(); } // end of button click i have tried putting the myConn.Close() in different places but it dosnt seem to work! any advice or tips ? RegardsJez
Hi most of my code follows the following format in a lot of my pages, my question is; Am i approaching it the right way in terms of performance, sql injection attacks and anything someone more knowledgeable than myself can think off, please feel free to criticise the code. Thank youprotected void Page_Load(object sender, EventArgs e) {string strID = Request.QueryString["id"]; SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["streamConnectionString"].ConnectionString);SqlCommand command = new SqlCommand("stream_EventsByID", conn); command.CommandType = CommandType.StoredProcedure;command.Parameters.Add("@eventID", SqlDbType.Int).Value = Request.QueryString["id"]; conn.Open(); SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection); eventList.DataSource = reader; eventList.DataBind(); conn.Close(); } }
I have a DAL that I'm trying to implement - the issue is that I want to call a reader from the DAL, but I'm not sure how to close it. I got best practices from MSDN (located here: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqlclientsqldatareaderclasstopic.asp ) regarding the SqlDataReader, but can't seem to figure out how to close when using DAL. Here is sample code in C#: NOTE: Everything WORKS just fine, however in the DAL for GetRoutes, I can't seem to figure out how to close the reader (see comments in function): mydatapage.aspx...//Populating some Drop Down List:private void ddlRoutes_SelectedIndexChanged(object sender, System.EventArgs e) { SqlDataReader dr = DAL.GetRoutes(ddlRoutes.SelectedValue.ToString()); while (dr.Read()) { . . . }} DataAccessLayer.cs...public static SqlDataReader GetRoutes(string sIdx){ cnn = new SqlConnection(ConnectionString); cnn.Open(); SqlCommand cmd = new SqlCommand(); SqlParameter par = new SqlParameter(); cmd.Connection=cnn; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "sp_GetRoutes"; return cmd.ExecuteReader(); //Everything works above, however I would think that something //like this SHOULD work, but doesn't: // // SqlDataReader dr; // dr = cmd.ExecuteReader() // return dr; // cnn.close(); }
If Someone could please show me some example VB code where i can open the my Sqlconnection in the Page_Load subroutien... and then close that SqlConnection in the Page_Unload routine. I want to be able to execute Sql command without having to re-open and re-close the connection.
I'm new to ASP.NET and I've searched before posting. I have a simple form containing a FormView control. The FormView uses a SQLDataSource. This works fine and in 30 seconds I have a working form without writing any code.A form built this way causes problems with connection pooling because I am not closing the connection. What is the best method for me to remedy this? Every resource I've found explains that I have to explicitly close the connection. How do I do that in this case? Dispose the SQLDataSource in the FormView DataBound event? Rewrite the form so I can control opening and closing of the connection?Thanks in advance.
I have a purchased product that has a DB with several thousand tables in dbo. I can't change anything with the default dbo schema other than security. I have a table called dbo.ACCOUNT. It has information from multiple sites on it. I have a reporting team that need only access the information for SITE 1 on the ACCOUNT table and anything in the other 1000+ tables in the dbo schema.
I created a role (Analytics) and granted select access to dbo. I created a schema (Analytics) and assigned it the role. I created a view in the new schema called Analytics.ACCOUNT with a WHERE SITE = 1 clause on it. If they use the view, they see only the things they should. My problem is they can still do a select * from dbo.ACCOUNT and see everything they shouldn't see. How can I close that hole? I am not seeing a solution. If there is no way to fix this with roles/schemas/views, what else can I do?
Some of my pain points. I have this issue with about 30 tables. Creating custom table(s) to isolate data for each site will cost me another 2T of storage. Loading those custom tables every day would be cumbersome. Keeping the DDL in sync with the source table would be cumbersome. With the way I have it set up now, if I deny access to the source tables the view breaks because it needs access to the source tables to dynamically build the view. Here is the code I ran to create everything.
USE[DB] --create test user login CREATE LOGIN [Analytics_ETL] WITH PASSWORD=N'XXXXXXXXXXX' GO --create user in test database CREATE USER [Analytics_ETL] FOR LOGIN [Analytics_ETL] WITH DEFAULT_SCHEMA=[Analytics]
I have a package which runs several child packages. All works well and everything runs, but when it runs each of the children packages, it opens it, runs it and then it stays open. When the whole thing is done, there are about 25 or so open packages. Should they close after they run? Is there a setting I need to do this?
The point I am in SSIS is that I have gotten a decent feel for creating packages, but everything is still in debug mode. I need to take the next step to learn how to have this stuff run automatically or from a procedure outside the SSIS interface. Does that make any sense? If so, where can I learn about that.
I have a couple of windows forms which share tables or parts of tables.
When I edit a tables data on one form (form2), where it ,s data is linked to another form ( form1), when I go back to form1 I find that the fields have not been updated until I close the form and reopen it.
Is there a way to refresh the field on form1 by using a button in the menu bar and what would the code behind look like. I use vb .
I'm writing a bit of code to open, refresh external data, save and close an Excel Spreadsheet, using the bit of code below:-
Private Sub CommandButton1_Click()
Application.Workbooks.Open ("X:sheet1.xls")
ActiveWorkbook.RefreshAll
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub
pretty noddy bit of code really. Problem is that it comes back with a message:-
"This action will cancel a pending reftesh data command. Continue?"
which requires my interverntion. If I supress messages using:-
"Application.DisplayAlerts = False"
the message does not appear but the spreadsheet closes without saving. If I put a delay (wait command) to pause the code for long enough for the refresh to complete it still doesn't save the refresh. I have also tried using "On Error" to force the code back to the "Save" statement until the refresh is complete and the error ceases, but again to no avail. It seems that the code is saving and closing before the refresh is complete.
I find it inconceivable that it's not possible to open, refresh, save and close a spreadsheet using vba. Can anyone help?
i have a loop that can run say 30k times. in there i am using the try / catch / finally blocks to handle ADO.NET Transactions (thanks CADDRE!)Should i open and close the SQL Connection for each record? or open it and close it outside of the loop only once ?thanks in advance, mcm
Ever since I installed Internet Explorer 7 I have recieved an error message when closing the browser tabs. It doesn't matter if I am closing one or all of the open tabs. Here is the message I recieve,
"Access violation at address 02050200 in module iesdsg.dll Read of address 25202E67"
This message didn't occur until I installed IE7. When I uninstall IE7 the message does not show up. Any insight would be appreciated.
is there a way to avoid closing and reopening tabs in mgt studio when one task conflicts with another? For instance, I sometime look at a stored proc by scripting it as a create to a new query window. If I need to alter the db it is on, in a way that conflicts with a separate connection that is looking at a stored proc, I have to close the tab in which I was viewing the sp, run the alter, close that tab and then rescript the sp as a create in a new window.
I would like to create calculated measure which should give me closing holdings at all levels(week ,Month,Quarter and year levels.) whichever i pull in the browse pane.
Below calculated measure would show the holdings for whatever the level you have specified.
([Measures].[Holdings], ClosingPeriod( [DIM BI DATE].[Calendar].[WEEK], [DIM BI DATE].[Calendar].CurrentMember ))
But this measure shows the value for only week attribute.
for month,([Measures].[Holdings],ClosingPeriod( [DIM BI DATE].[Calendar].[Month], [DIM BI DATE].[Calendar].CurrentMember ))
But my aim is to create a calculated measure to give closing value for any level.
I am preparing a monthly cash flow statement and for doing so, I need to determine the monthly opening and closing cash balance. To simplify, this is what I did.
1. Dragged months to the columns labels
2. Created a slicer for selecting the Financial Year. In this slicer, I chose 2015-16 i.e. April 1, 2015 to March 31, 2016.
Since I have data only for two months of this Financial Year i.e. April and May, only these two months show up in the column labels.
To determine the monthly opening cash balance, I added the following measure
Much to my surprise, I got the result as seen in the image below. Cells B5, C6 and D5 are blank. On going through my Bank Book, I realised that
1. There is no figure in cell B5 (Opening balance of April) because there was no transaction on the last day of March i.e. March 31. The last transaction was on March 28
2. There is no figure in cell C6 (Closing balance of May) because there was no transaction on the last day of May i.e. May 31. The last transaction was on May 30
3. There is no figure in cell D5 (Opening balance of June) - same reason as mentioned in point 2 above.
As seen in the image, I have also computed the monthly "Last date of previous month" and "last date of current month" but do not know how to make use of them in computing the opening and closing Cash balances.
Please also note that there can be multiple transactions on the last day of any month. For e.g., let's say the last day of transaction in May 2015 was May 30 (not may 31) but there were multiple transactions on this last day (both inflow and outflow).
So I cannot simply determine the last day of transaction and take MAX/MIN/SUM that day. I have to take the final balance on that day.
How to solve this problem i.e. in determining the opening and closing cash balances.
I'm in a SSIS Package Design tab working on a data flow. It was working fine up to now, but all of a sudden I keep getting this error that says, "Microsoft Visual Studio has encountered a problem and needs to close." And it has that "Send Error Report" and "Don't Send" button at the bottom. This started happening when I tried to add a Data Viewer on a Data Flow Path. And now every time I do this to try to debug my data flow, it gives me this message and closes my project.
However, the package executes without any package errors, but I as I described above, I can't add a Data Viewer because it keeps giving me this message and closing my package.
