SQL CE 2.0: Calling Engine.Dispose() After Every Query?
May 19, 2006
After making several hundred queries against a SQL CE 2.0 database (through NetCF/ADO.NET), I begin getting a SqlCeException: "Not enough storage is available to complete this operation."
Microsoft speaks to this situation in the following hotfix:
http://support.microsoft.com/?kbid=827837
When I contacted them to receive the hotfixed ssce20.dll, they described the problem as SqlCeDataReader and SqlCeDataAdapter not releasing their memory resources after they went out of scope. In addition to using the hotfixed binary, they also advised me to call SqlCeEngine.Dispose() after every query to force SQL CE to release resources, as shown in the "finally" block of the code below.
I have a couple of questions about this:
(1)
Will this cause a lot of performance overhead for me, especially if my application makes frequent queries using the following code?
(2)
Can a cache an instance of SqlCeEngine and call Dispose() on that cached instance repeatedly, so I can avoid having to instantiate a new SqlCeEngine each time?
I tried to call a exe from sql server through trigger with xp_cmdshell, But its not working as expected, shows preemptive_os_pipeops in process under the activity monitor and the process got hang.
Hi, We are planning to use the BTS BRE for our business rules and calling these from a data flow transformation (e.g. for every row in a flat file during import). One way would be to use a script component. However, the question is that the script component would have to create and destroy BRE objects (e.g. a BRE Policy object) for every row in the flat file. Is there a way to instantiate objects and whole on to them for the lifetime of the package or a container within a package?
Any suggestions regarding achieving the above most efficiently would be much appreciated.
Given the following code:System.Data.SqlClient.SqlCommand sc = new System.Data.SqlClient.SqlCommand();sc.CommandText = "MySP";sc.CommandType = System.Data.CommandType.StoredProcedure;sc.Connection = new System.Data.SqlClient.SqlConnection("MyConnectionString"); sc.Connection.Open();sc.ExecuteNonQuery(); sc.Connection.Close();sc.Connection.Dispose();sc.Dispose();Is the call to sc.Connection.Dispose() necessary?J
I have an Execute SQL Task that returns a dataset to variable DfltValData. A dataflow follows that with a script component that access that dataset (read only variable) (see code below) and everything is fine. Now, after that, there's another dataflow with a script component, with the same code as below, trying to access DfltValData. Here is where the problem is, the DfltValData object does not contains any row. Whats happening and how to solve this?
Thanks!
Dim olead As New Data.OleDb.OleDbDataAdapter Dim dt As New Data.DataTable Dim row As System.Data.DataRow
i am using visual web developer 2005 and SQL Express 2005 with VB as the code behindi am using the following code to update the database table Dim update As New SqlDataSource() update.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString").ToString() update.UpdateCommandType = SqlDataSourceCommandType.Text update.UpdateCommand = "UPDATE orderdetail SET fromdesignstatus = 2 ,progresspercentage = 15 , fromdesignlink = '" + designlink + "' WHERE order_id =" + ordersid.ToString() update.Update() update.Dispose() update = Nothing i am using update.Dispose() and update = nothing to release the memoryis it really necessary to use both the commandsif not , in my case which one is enough and what is the reasonplease help me
I've got an application uses a WPF GUI, is built in VS2008 final, targets .Net Framework 3.5, and uses a Sql Ce 3.5 database for local storage. After a specific WPF window is rebound the application hangs. Hitting pause on the debugger reveals the offending method is a call to SqlCeConnection.Dispose(). Via Reflector I can see this method calls SqlCeConnection+ObjectLifeTimeTracker.Close() which in turn calls GC.WaitForPendingFinalizers(). Within WPF there are some objects (specifically TextBox) which need to have their resources freed on the main application thread. The finalizer thread is waiting on the main application thread, which is in turn waiting on the finalizer thread, resulting in a deadlock.
You can see some additional discussion of the topic including our temporary resolution in this thread.
My question is why is there a call to GC.WaitForPendingFinalizers() buried within the SqlCeConnection.Dispose() call tree?
public void Dispose() { if (this.connection != null) { this.connection.Dispose(); } } // ... }
This only happens when I'm calling Application.Exit(); and Dispose is called through the destructor of the DatabaseManager class. When I'm disposing the connection during normal work the call works as intended. BTW I'm using SQL Server Mobile 3.0.5214.0 on a PPC 2003 AKU2 (Symbol PPT8846 industrial device).
I have created testreport.rdl using SQL Server 2012 SSRS. How to execute/call this through SQL query analyser, so that this will be executed and display output.
I use a database that has user names stored in Encrypted format usingthe following API.Declare Sub Encrypt2 Lib "QPRO32.DLL" (ByVal Work As String, ByValPASSWORD As String)Every time i require the user name i have to again decrypt the nameusing the same function.My problem is that when i fetch a large number of records i have toloop through every record and call the encrypt function for eachrecord.Instead of binding the recordset to my control i need to loopthrough and fill my controlA MSHFlexGrid in Vb6.0.Is there a way out to this problem that will make my record populatiogfaster withoutout changing the current Encrypted users.Thanx in Advance
Can anyone tell me why I am getting this error when I try to view my report:
Query Engine Error: '21000:[Microsoft][ODBC SQL Server Driver][SQL Sever] Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <=,>,>= or when the subquery is used as an
Create Index ind_Item_Name on Item(I_Name); Create Index ind_Item_BC on Item(I_BC); Create Index ind_Item_Company on Item(I_Company); Create Index ind_Item_CompanyFound on Item(I_CompanyFound); create Index ind_Item_i1 on Item(I_Company,I_CompanyFound); create Index ind_Item_i2 on Item(I_CompanyFound,I_Company);
Now this query DOES NOT use index: select I_Name, I_Code, I_MatID, I_BC, I_Company,I_Info1, I_Acquired, I_CompanyFound, 0 as I_Found from Item where (I_Company='102' or I_CompanyFound='102' )
While this one use:
select I_Name, I_Code, I_MatID, I_BC, I_Company,I_Info1, I_Acquired, I_CompanyFound, 0 as I_Found from Item where (I_Company='102' ) UNION select I_Name, I_Code, I_MatID, I_BC, I_Company,I_Info1, I_Acquired, I_CompanyFound, 0 as I_Found from Item where (I_CompanyFound='102' )
Both return the same rows. Is this a bug? I found the following: http://support.microsoft.com/kb/223423
I've a complex view on a SQL 2014 Enterprise Edition. If I query the view with:
SELECT * FROM myComplexView it takes 14 seconds to completes if I want a subset of the result and I run the query with a WHERE clause: SELECT * FROM myComplexView WHERE [Season]='A16'
The query never completes (I've waited 10 minutes and then cancelled the task).
Any sql script or powershell script which outputs late running jobs? Currently I am using the below script to find out currently running jobs along with duration. But my requirement is to add few more columns to the query which indicates whether jobs is running fine or running behind expected time.
-currently using query to pull running jobs SELECT ja.job_id, j.name AS job_name, ja.start_execution_date, ISNULL(last_executed_step_id,0)+1 AS currently_executing_step_id,
I am learning the Optimizer from the book "Querying Microsoft SQL Server 2012" for certificate exam 70-461. I really cannot understand how it explains the number of possible ways to execute a query joining three tables. the pseudo-query is:
SELECT A.col5, SUM(C.col6) AS col6sum FROM TableA AS A INNER JOIN TableB AS B ON A.col1 = B.col1 INNER JOIN TableC AS C ON B.col2 = c.col2 WHERE A.col3 = constant 1 AND B.col4 = constant2 GROUP BY A.col5;
The book says:"Start with the FROM part. Which tables should SQL Server join first, TableA and TableB or TableB and TableC? And in each join, which of the two tables joined should be the left and which one the right table? The number of all possibilities is six, if the two joins are evaluated linearly, one after another."
Q1: How could it be six possibilities? From my understanding, lets say, if the SQL Server has to join A and B first, and then join C, in this case I can think of 4 possibilities, which are:
1. When A Join B, Left: A, Right: B. When Join C, Left: result of A join B, Right: C
2. When A Join B, nbsp; When Join C, nbsp;When A Join B, nbsp; When Join C, nbsp;When A Join B, nbsp; When Join C, "line-height:13.5px;">
Q2: The section following the previous question says there are 4 different types of join.."This already gives four options for each join. So far, there are 6 x 4 = 24 different options for only the FROM part of this query."
How can it be 6 x 4? My understanding is 4 is only for 1 join, but in our case, there are 2 joins, so it should be 6 x 4 x 4.
Create Index ind_Item_Name on Item(I_Name); Create Index ind_Item_BC on Item(I_BC); Create Index ind_Item_Company on Item(I_Company);
It is populated with 50 000 records. Searching on indexed columns is fast, but I've run into the following problem: I need to get all distinct companies in the table. I've tried with these two queries, but they both are very slow!
1. "select I_Company from item group by I_Company " - This one takes 19 seconds
2. "select distinct(I_Company) from item" -This one takes 29 secons
When I ran them through the SQL Management Studio and checked the performance plan, I saw that the second one doesn't use index at all ! So I focused on the first... The first one used index (it took it 15% of the time), but then it ran the "stream aggregate" which took 85% of the time ! Actully 15% of 19 seconds - about 2 seconds is pretty much enough for me. But it looks that aggregate function is run for nothing! So is it possible to force the query engine of the SSCE not to run it, since there is actually no aggregate functions in my select clause? According to SQL CE Books online: Group By
"Specifies the groups (equivalence classes) that output rows are to be placed in. If aggregate functions are included in the SELECT clause <select list>, the GROUP BY clause calculates a summary value for each group." It seems the aggregate is run every time, not only when there is an aggregate function.
I have a holding tree with more than 200 companies, and several layers. Each company is defined by:
- a unique ID - a direct parent ID --> the company immediately above in the tree - an ultimate parent ID --> the company that owns the entire tree, it is the same for all
Now I am looking for a function that returns the list of all companies below a given one in the tree, and there could be several layers underneath. Doing that for the ultimate parent is easy because all companies now who their ultimate parent is, but I am struggling to build a function that works for an intermediary parent in a dynamic way.
TABLE [dbo].[Tbl_Products]( [Product_ID] [int] IDENTITY(1,1) NOT NULL, [Product_Name] [nvarchar](50) NOT NULL, [Catagory_ID] [int] NOT NULL, [Entry_Date] [date] NOT NULL,
[Code] ....
I am using this query to get ( Product name from tbl_products , Buy Price - Total Price- Total Quantity from Tbl_Details )
But am getting a multiple result if the order purchase has more than 1 item :
SELECT DISTINCT B.Product_Name,A.AllPieceBoxes, A.BuyPrice,A.TotalPrice,A.BuyPrice FROM Tbl_Products B INNER JOIN Tbl_PurchaseHeader C ON C.ProductId=B.Product_ID INNER JOIN Tbl_PurchaseDetails A ON A.PurchaseOrder=C.purchaseOrder WHERE A.PurchaseOrder=3
My Integrity job started failing recently with the following error. Msg 701, Level 17, State 123, Line 1 There is insufficient system memory in resource pool 'default' to run this query. Process Exit Code 1. The step failed.
select @@ version Microsoft SQL Server 2008 R2 (SP2) - 10.50.4033.0 (X64) Jul 9 2014 16:04:25 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor) System is having 4GB RAM and SQL is using most of it. It has 2 processors.
DECLARE @Teams AS TABLE(Team VARCHAR(3)) INSERT INTO @Teams SELECT 'IND' UNION SELECT 'SA' UNION SELECT 'AUS' select Team from @Teams where Team > 'AUS'
[code]....
co-relation between comparison operators in WHERE Clause and the respective output.
I have two queries yielding the same result that I wanted to compare for performance. I did enter both queries in one Mangement Studio query window and execute them as one batch with the actual query plan included.Query 1 took 8.2 seconds to complete and the query plan said that the cost was 21% of the batchQuery 2 took 2.3 seconds to complete and the query plan said that the cost was 79% of the batch.The queries were run on my local development machine. I was the only user. No other programs were running at the time of this test. The results are repeatable.I understand that the query with the lowest cost is not necessarily the fastest query. On the other hand, the difference is quite big. The query that has approx. 80% of the cost takes 20% of the time and the other way around. I have two questions:
Is such a discrepancy normal?Can conclusions be drawn from the cost distribution? For instance, does the query that takes 8.2 seconds but only costs 21% scale better?