Closing The Sessions In SQL CE
Feb 27, 2008
Hi Friends,
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
Thanks in advance.
Regards,
Sasi.
View 2 Replies
ADVERTISEMENT
Jul 30, 2006
Hi,
Here is my code on how i connect to my database
Dim rst As New ADODB.Recordset Dim conn As New ADODB.Connection Dim vresponse As String ' On Error GoTo err_proc Try conn.Open(GetConn(getconnid)) Select Case getmode Case 0 'execute then return first field rst = conn.Execute(getsql) If rst.EOF = False And rst.BOF = False Then Try vresponse = IIf(IsDBNull(rst.Fields.Item(0).Value), "", rst.Fields.Item(0).Value) ' vresponse = rst.Fields.Count Catch ex As Exception vresponse = "Error :" + ex.Message End Try End If Case 1 'execute only If bTransactional = True Then conn.BeginTrans() End If conn.Execute(getsql) vresponse = "OK" If bTransactional = True Then conn.CommitTrans() End If End Select
Return vresponse.Trim
* line 1)
rst = Nothing conn = Nothing
Exit Function
supposed to be *line 1) terminates the connection/session form SQL server. But when i check to the SQL Query Analyzer, there is still some connections related to this command. So when i run again this commands, it creates another connection. How can i terminate/end my sessions in SQL.
Thanks
View 1 Replies
View Related
Jan 12, 2000
Hi all!
First of all, thanks to those who gave me suggestions to use sql-dmo
to generate sql scripts of a database.
I've found it extremely useful!
Here's my new problem.
I have VB code that connects to a database, extracts the schema
and generates the sql.
After some work with this script, I have some new scripts to alter the database and I have tried running these scripts in a separate project
with the ExecuteImmediate method of the Database object, which works well.
However, when I put this code into the first project, to reconnect to the database and execute the batch command,
I get SQL-DMO error -2147199229 (80045203)
...whatever that means..
Can anyone help me?
Thanks
View 1 Replies
View Related
Jan 19, 2007
hi all
i am using .net web application with sql server as my database, i want to login by authentication from my database which i ave done but now i want to add session but dont know the code so if anyone can help me with stepwise process to assign username in the seeion also to create and destroy the session.urgent help required
View 1 Replies
View Related
Apr 25, 2008
hi is it possible to insert a session into a database E.G. i'm sure this code is fine but i duno why its not inserting the session into the database the form parameter is going in fine but the session aian't
UserName.Text = Session("User")<asp:sqldatasource id="SqlDataSource1" runat="server"
selectcommand="SELECT Party.Party FROM Party"
insertcommand="INSERT INTO Vote(UserName,Vote) VALUES (User,Party)"
ConnectionString="<%$ ConnectionStrings:VotingConnectionString %>"
ProviderName="<%$ ConnectionStrings:VotingConnectionString.ProviderName %>">
<InsertParameters>
<asp:SessionParameter Name="User" Type="String" SessionField="User" />
<asp:FormParameter Name="Party" Type="String" FormField="PartyList" />
</InsertParameters>
</asp:sqldatasource>
View 7 Replies
View Related
Mar 13, 2001
How can many users share the same .adp front end, just like it as if it was a .mdb file? The problem I've ran was the following...user A logs in, user B tries to use the same .adp front end, but gets a message that is already in use therefore can't get in. Thanks in advance!
View 1 Replies
View Related
May 19, 2008
I have a process that seems to leave many orphaned sessions over a period of time. The software is by a 3rd party vendor and they can't seem to fix it. It is safe to end the orphaned sessions and I do that on a regular basis. My questions is: What is the best way to do this via T-SQL?
I can select the orphaned sessions by using a simple query on the sys.dm_exec_sessions table. How do I then run a kill command for each session_id from that query?
I appreciate any info/advice anyone might have!
View 5 Replies
View Related
Sep 6, 2006
Hey
I have a problem in controlling the report session.
I created a Reporting Model using SQL reporting Services 2005 with Forms authentication on which I implemented the security Filter based on the function GetUserID() to report only against data that belong to the login User, and I deployed the Model.
Using report Builder application I created a report that contain in the first column the user name (UserName) from entity €œUsers€?, and in the remaining columns data from other entities related to table users, I saved the report on the report server.
I logged in into report manager with user €œUser1€? and I ran the report, the result was the same as expected (in the first column €œUser1€? appeared and in the remaining column other data related to this user appeared) every thing went good
After that I logged in with the user €œUser2€? and I ran the report and here was the surprise the same data that appeared for "User1" appeared for "User2". but what was Expected is different data that belong to "User 2"
After some research that says that this problem may be caused by a session issue (the session created for the first user who enters the report server will remain for the other users that enter after him), I reset IIS and I logged in with €œUser2€?, and I ran the report the correct data for €œUser2€? appeared So it may be a session issue.
My question is:
Is there any way to control the session content and the session expiration?
Please note I used all the solution provided in my research such as URL parameter rs:ClearSession = true, and the Report Execution Options in report manager but non of this helped me.
I will be thankful for any one who could help me in this urgent issue.
Thank you
BOB
View 3 Replies
View Related
Apr 4, 2007
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(); }
View 2 Replies
View Related
Sep 24, 2007
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.
View 1 Replies
View Related
Nov 15, 2004
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."
Thank you.
View 4 Replies
View Related
Feb 23, 1999
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?
View 1 Replies
View Related
Mar 7, 2008
Hello:
I have the following script wrtten in VB that opens a connection to an Analysis Services db in a script task within my control flow:
Dim oConnectionSR As ConnectionManager
oConnectionSR = Dts.Connections("OLAPServer.Sales")
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.
Thanks for your help!
Tim
View 4 Replies
View Related
Nov 1, 2007
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?
Many thanks,
Rich
View 6 Replies
View Related
Mar 16, 2004
I'm using SQL server 7 on Win NT. I have Access 97 as a front end, with linked tables though ODBC to SQL Server. Everytime I open a table in Access, a session appears when I type sp_who2. I close that table in Access, but I when I type sp_who2 the table session is still present. Does anyone know a cause for this?
I am researching why sometimes when we close are queries and tables in Access we have sessions in SQL server that becomes orphans/ghost. I try to kill the session but can't, so therefore I have to recycle the database.
Any help would be appreciated.
View 2 Replies
View Related
Aug 29, 2011
When I probe sys.dm_exec_sessions (joining with other DMVs to get active-session info) I get the login_name column to be blank for a certain session.
Yet, for the same spid, the Login shows up properly (not blank) when I execute "sp_who2 active".
It also shows up properly in sys.sysprocesses.
What could be the reason for the faulty output of sys.dm_exec_sessions ?
View 9 Replies
View Related
Feb 15, 2008
I just have a simple question on font dependencies in reference to deployed reports. In my company, we run citrix presentation servers feeding terminal sessions out to users in remote areas.
My question is, in a terminal session, do the fonts used in the reports need to be installed on the users' local machine (who is connecting via terminal session) or is it enough to just have it on the servers feeding out the terminal session?
For example, we create a report with Arial Narrow font. Our servers hosting citrix presentation server did not have this font installed, so we installed this font on the servers (thus they're displaying correctly). Obviously the server hosting our reporting services has the font. Now a user connects to the server, is granted a terminal session, launches reporting services, report is generated with the correct font displayed, and finally prints the report.
Will the report print in Arial Narrow if the users' local machine doesn't have this font install? I remember reading somewhere that the fonts do not embed, hence the reason for installing the font on our citrix servers.
I realize this is kinda out of the scope of this forum, but perhaps someone on here uses terminal services and can answer this for me. I do plan to test this out when I get home tonight. I'd test it right now, but my computer at home isn't on therefore I can't remote home (plus wake-on-lan isn't configured) to do an actual test. Just thought I'd get some insights from other people.
Thanks in advance!
View 3 Replies
View Related
Apr 6, 2006
Hi
I have been looking at the new DMV's prefixed with dm_exec_....and found a limitation with them.
Books online says sysprocesses is replaced with sys.dm_exec_connections, sys.dm_exec_requests and sys.dm_exec_sessions. The problem I came accross is identifying any sessions connected to a specific database which were idle. This is the sort of thing you need to know if you tried to restore a database and it says the database is in use.
I wonder if this is a bug or by design?
View 8 Replies
View Related
Jun 26, 2007
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
View 1 Replies
View Related
Feb 27, 2008
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
View 5 Replies
View Related
Mar 16, 2008
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();
}
}
View 7 Replies
View Related
Dec 12, 2005
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(); }
View 2 Replies
View Related
Apr 20, 2006
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.
Thanks,
Greg
View 4 Replies
View Related
Feb 2, 2015
Is there a way to find the max number of sessions that were on a sql server since its last restart? I know how to get how many total connections there were since last restart, and the current number of sessions, but not the max number of sessions that were running since last restart. Also, opinion on the optinum number of allowable sessions, or a good way to figure out the balance?
View 0 Replies
View Related
Mar 8, 2015
So I've been monitoring long-running transactions on a SQL Server that hosts a couple of vendor-supplied databases that look after our factory.Today I noticed a pair that have confused my Excel spreadsheet (that I've been using to analyze these transactions).So here's the weird thing that I spotted. Given this query:
SELECT p.spid, p.login_time, at.transaction_begin_time, datediff(second, p.login_time, at.transaction_begin_time) as [difference]
FROM sys.sysprocesses AS p INNER JOIN
sys.dm_tran_session_transactions AS st ON st.session_id = p.spid INNER JOIN
sys.dm_tran_active_transactions AS at ON st.transaction_id = at.transaction_id
[code]....
I had a look in the event log on the server, which had just been rebooted at around that time. It seems that the clock got changed on boot-up, with the size of it quite surprising. This meant that these processes were able to start their transactions *before* they logged on. Hopefully this doesn't cause any other weird problems.So I've requested an investigation about time synchronization on our virtualization hosts... and in the mean time, have set the SQL Server services to 'delayed start'.
View 0 Replies
View Related
Jul 23, 2005
I have a table EugeneTest(id_num, fname, minit, lname)where field "id_num" is type IDENTITY and has UNIQUE constraintLet's say 2 user execute this code at the same time:DECLARE @return integeruse EugeneTestINSERT employees( fname, minit, lname)VALUES( 'Eugene3', 'F', 'Josephs')SET @return = @@IDENTITYIs is not possible to get duplicated value in id_num column becuase ofUNIQUEconstraint, but is it possible the both user get the same @@IDENTITYnumber( for example, if @@IDENTITY is evaluated and kept somewhere in thebuffer before the data actually get written to the disk )Thanks, Eugene
View 5 Replies
View Related
Feb 9, 2006
Hi,
Can you help me how to find out sessions in SQL Server 2000.
Also is there any way to see the outstanding sessions for a database?
Thanks in advance,
Vasu.
View 4 Replies
View Related
Nov 21, 2015
The benifit of UPDLOCK is that it avoids deadlock in case both sessions run the below query at the same time.The table has clustered index on ID column
----session 1 --------
begin transaction
select * from a1
update a1
set id = 22
where id = 2
----session 2 --------
begin transaction
select * from a1
update a1
set id = 22
where id = 2
Now to avoid deadlock in the above scenario we should use (UPDLOCK) hint in the select statement.Now my question is that deadlock will be avoided in this case when both the sessions use UPDLOCK hint. If only one session uses UPDLOCk and other does not then there will be deadlock .For example session 1 uses UPDLOCK hint this will hold the U lock on the row, but the session 2 does not use this hint and apply shared lock on the same row. Now there will be deadlock when session 1 tries to update the record and is blocked by shared locks of session 2. same will be the case with session 2 and both will wait for each other and hence dead lock.so what steps can be taken to avoid deadlocks in this case. I do not want to use Snapshot isolation.
View 3 Replies
View Related
Jun 20, 2006
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.
View 1 Replies
View Related
Jul 9, 2015
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]
[code]....
View 2 Replies
View Related
Sep 18, 2007
Hello,
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.
Thanks for the help.
-Gumbatman
View 3 Replies
View Related
Jan 31, 2008
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.
Any advice would be gratefully received
Thanks
Nick
View 1 Replies
View Related
Jul 4, 2007
Hi all
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 .
Thanks
Rob
View 10 Replies
View Related
