I'm using ODBC driver from WinXP SP2 with SQL Server 2005 Express (on my machine for test) and SQL Server 2005 Standard (on the production machine).
With SQL Server 2005 Express I've no problem, but with SQL Server 2005 Standard, in some query, I get "Connection is busy with results for another hstmt".
I'm working with VisualStudio 2003 with standard CDatabase and CRecordSet MFC classes.
I looked for an advice in the Microsoft KB but I found only Service Pack for SQL Server 2000 !
I am getting this strange error while working on SQL server through JDBC. I am using a connection-pooling mechanism. Have written my own connection pool. The pool holds a number of connections and has methods for requesting and returning connections from the clients. The issue is - when I get a connection from the pool and execute a select query, it works fine. Then I return the connection to the pool (at the top). On next request for connection, this same connection object is provided. When another select query is fired on this connection, I get an error - "connection is busy with results for another hstmt"... The error occurs at the time of executing the query and not while obtaining the statement object from the connection object. If I don't use the connection pool and create new connection every time, everything works fine (except the performance!)... I'll be grateful if anyone can shed some light on this..... Cheers!
Hi, When i tried to retreive Datas from Sql Server continously(connection pooling), I got an Exception like "java.sql.SQLException: [Microsoft][ODBC SQL Server Driver]Connection is busy with results for another hstmt". After restarting the Tomcat server, it starts to work fine.I don know what is happening behind the screen. Let me know the nature of this Exception and the Solution to this kind of Exception.. Plz as soon as possible..
Hi I am using SQL server 2000 and vb.net 2003. We have used threads in our application. Our application is running well but Sometimes we get the error message "connection is busy with results for another hstmt" We are using adodb connection to open recordsets.
I wrote a web app using an ASP front end (not .NET) connecting to a SQLServer 2000 (no SP) back end. Both the web server and the databaseserver are Windows 2003 boxes. The app was running fine two weeks agobefore I went on vacation. While I was gone my net admin appliedWindows 2003 SP1 to the web server. Upon my return I was informed thatthe app is no longer working, getting the following error:[Microsoft][ODBC SQL Server Driver]Connection is busy with results foranother hstmtSince that is presumably the only thing that changed in my absence Ibelieve the SP somehow messed up the way the ODBC was working. I triedmoving the SQL Server database to a different Windows 2003 box whichalso received the SP1 update and also includes SP3 for SQL Server, butget the same error.I am hitting the database like so:set GetData = CreateObject("ADODB.Command")GetData.ActiveConnection = SQLConnGetData.CommandText = "<stored procedure call>"Set DataRS= GetData.Execute (,,adCmdText)I appear to get the error when I execute the second call. I amlearning ASP as I go, so maybe this is a trivial problem.
Hello, I am having above mentioned error while inserting records in database.This arises sometimes after 10 records or 20 records.I dont have any object such as hstmt. before this insert query ,there is another query where I get one colume value from another table. please can anybody tell me reason of this kind of error. Thanks in advance
I have looked at other threads regarding errors similar to this, but I think mine is a bit different.
I am using SQL2005 Standard Edition and my application is coded with C# using ADO.Net. OLEDB connection is used. The error occurs when the application has only one thread accessing the database. It does not happen consistently, so it is very puzzling.
I am wondering if anyone could offer any tip to diagnose this.
Error: 0xC0202009 at Load SQL Deals, Insert SQL Deals [12494]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Connection is busy with results for another command".
Why isn't the connection manager managing this?
How can I trace the cause? The data flow component (insert SQL deals) is just a simple INSERT statment.
I am having multiple threads in my c program to execute same procedurewith differnt parameter values every time.I have created different functions to execute the same.However when SQLExecute is run on one of the thread it returns anerror saying[Microsoft][ODBC SQL Server Driver]Connection is busy with results foranother hstmtHow do I call the same stored procedure on different threads toexecute at the same time.
Hello! My group uses scalar-valued functions to denote the FTP drives on our SQL Server 2005 database servers. This allows many (over 100) import processes to refer to that function to find the flat files they need, and if the FTP drive is changed, then only the function needs to be updated, not 100 SP's.
The function is defined this way. Currently, F: is our FTP drive:
CREATE FUNCTION [dbo].[fnFTPPath] () RETURNS varchar(200) AS BEGIN
return 'f:' END
Now the question: Can a Flat File Connection Manager be used in an SSIS ETL package that refers to the output of this function, plus the FTP folder name, so we can continue updating the FTP drive name in just one place? If so, how is this done?
Hi, I'm new to c# and sql2005 express. I'm getting an error on the simplest of activities. From C#, i create a database, create a table, then try to delete the database and get a database busy error. I think its because sql has some connections to the database its not releasing. I have already tried closing the connection, disposing of the object and forcing garbage collection for the c# app to no avail.
can someone explain to me wat is going on?
Here is the only function i am calling for c#. I pass in T-SQL command strings such as 'Ceate Database myDB','Create Table myTbl','Drop Database myDB', etc.. :
private bool ExecuteSQLNonQry(string sqlStr) { SqlConnection myConn = new SqlConnection(ConnectionString); SqlCommand SqlCmd = new SqlCommand(sqlStr, myConn);
I have a table that currently holds about 5 million records. We add an average of 5,000 new records per day, all of them from overnight batch jobs. I guess it's not that big, but there are two text columns that hold a couple KB each, so the total size isn't exactly small either. The data is created from medical billing data we receive overnight. We get two reports- patient demographic information and a physician's dictation relating to that patient. This data is always one to one, and the purpose of this table is to store the data as we originally receive, which is why both reports are in the same table. After we extract the details from the report, (which are by this point always reduced to text documents) we need to keep not only the data but the original documents, hence the two text columns. We considered moving the large columns to their own table, which would just have an ID field and the column, but the powers that be really wanted all this in the same table. Nothing new goes into the table during day- it's all SELECT statements.
I need to add a column to this table. It's just a small char(7) column, NULLS allowed, of course. We bill for several clients, and reports from different clients become available at different times, so there's really no down time overnight. Altering the table during the day is out of the question. So how can I add a column while the table is active?
My best idea so far is to use SELECT *, NULL AS NewColumn INTO NewTable to create a copy of the table (using a cast to get the correct datatype) during the day, when no new data is going in, and replacing the old table with the new by simply changing the names right after everyone goes home. But this could still cause slowdowns while it builds the copy, and leave the problem of re-creating indexes (there are several). There ought to be some graceful way to tell it to add the column to the existing table and play nice with ongoing traffic.
What will be best procedure for the following situation.
Heavy traffic database on daily basis. G growth every day. so Full backups every nights are needed. Vendor recommends not taking Log backups but copy just log files over other location. will this help avoid degrading the performance during business hours.
if i don't take log backups, i am not able to recover Point in time if needed. also log files can then grow faster and then i will have to shrink it more often.
I get this message in a balloon that pops up frequently when I am trying to work in the SSIS designer. For example, I get it when working in a DataFlow task, trying to open editors for the OleDb Source and Destination. Is anyone else getting this? What could be the cause, it seems to get sluggish. Thanks.
Clearly, my code isn't written as well as it should be. I don't understand enough about data access and could use some help.I have several database tables, but one primary table that is the most accessed. Generally, I need to build a list from the data based on some filter. I'm using a repeater control, since all I need to display per record is a name, maybe a city or birthday, and possibly a little graphic, and my customer doesn't want a grid type of display. The filter is determined by the page requested. The exception is a search page where the user builds the filter and a grid is used to display the results.The results always contain a link to a page that has more detail on the selected record. What is the best way to handle this? I'm still trying to get a handle on different ways to get data and I'm not doing much with caching. Would it make sense to keep the data in memory from the page that displays the list (or search page) to the detail page? What if the detail page is accessed directly, say from a bookmark? How do I cache this?I'm currently using strongly typed datasets.Below is an example of what I'm doing - this is from the code behind of one of the list pages - members with birthdays this month. Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load Dim theMonth As String = DateTime.Now.ToString("MMMM") Me.LabelMonth.Text = theMonth Dim MemberAdapter As New WAPTableAdapters.membersTableAdapter Repeater1.DataSource = MemberAdapter.GetBirthday("Female", DatePart("m", Today)) Repeater1.DataBind() Repeater2.DataSource = MemberAdapter.GetBirthday("Male", DatePart("m", Today)) Repeater2.DataBind() End Sub Protected Sub Repeater1_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.RepeaterItemEventArgs) Handles Repeater1.ItemDataBound If e.Item.ItemType = ListItemType.Item OrElse e.Item.ItemType = ListItemType.AlternatingItem Then Dim LabelIcon As Label = CType(e.Item.FindControl("LabelIcon"), Label) Dim person As WAP.membersRow = CType(CType(e.Item.DataItem, System.Data.DataRowView).Row, WAP.membersRow) If System.IO.File.Exists(Server.MapPath("~/images/picts/" & person.FILE2 & ".jpg")) Then LabelIcon.Visible = True End If End If End Sub Protected Sub Repeater2_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.RepeaterItemEventArgs) Handles Repeater2.ItemDataBound If e.Item.ItemType = ListItemType.Item OrElse e.Item.ItemType = ListItemType.AlternatingItem Then Dim LabelIcon As Label = CType(e.Item.FindControl("LabelIcon"), Label) Dim person As WAP.membersRow = CType(CType(e.Item.DataItem, System.Data.DataRowView).Row, WAP.membersRow) If System.IO.File.Exists(Server.MapPath("~/images/picts/" & person.FILE2 & ".jpg")) Then LabelIcon.Visible = True End If End If End Sub It seems pretty simple and straightforward to me, but these pages shouldn't be crashing when the site gets busy, so I have to be doing something wrong. Diane
A relatively simple question but I have no been able to find an answer.
We have a SQL Server mirror Active/passive with High availability. So the usual Witness with failover.
However, we are using the Mirror server for reporting and letting the users query without limit. We sometimes have problems (slowness) with the Principle server and I'm wondering the following.
If the mirror server in high availability mode is maxed out will this affect the principle server because it takes longer to commit the data on both servers?
It seems obvious but I would like a clear response.
Hi, I started getting a delay when I try to connect to a SQL Server 2005 server from Management Studio. I get a little pop-up from the quick launch telling me : Is Busy. SQL Server Management is waiting for an internal operation to complete. If you encounter this problem... report it to Microsoft. Has anybody experienced anything similar? If so, have you been able to fix it and how? Thanks in advance
I have the standard version of SQL2005 and have a backup maintenance plan that runs every Sunday consisting of the following steps.
1. Check Database Integrity
2. Rebuild Index
3. Back Up Database (Full)
The backup itself works fine, but sometimes during the backup I am unable to insert new records into the database that I am backing up.
I believe from all the documentation and other comments I have read that the database should always be available for inserts or updates even during the backup operation. The error I receive from VB.NET application is:
System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
The problem only occurs during a full backup, not differentials or tranaction log backups. Sometimes after catching the above error I can insert into another table, but at other times I am not able to insert into the desired or backup table and simply have to write the data to a file and insert it manually at a later time.
This problem does not consistently occur since a backup may take 10 minutes and during that period maybe 5 of 6 records are inserted correctly, but one will fail.
Why doesn't the database always allow inserts during the backup? It should, shouldn't it?
I have three very simliar packages that I edited. I added an Exec SQL Task to truncate a table. These packages are not large or complex. When I simply try to open two out of the three packages, Visual Studio hangs and I get the balloon saying it's busy. I see in the bottom left hand corner, "Validating Load Phase". This never changes. My question is how can I get into this package? I hadn't yet checked it into SourceSafe If I even attempt to single click on any step in the package, it will hang. I've also had a co-worker attempt to open the packages on his computer, and he gets the same thing, so it's definitely the SSIS package. Any help would be appreciated!
Frequently, when I'm connecting to my server through SSMS i get "Is Busy: Microsoft SQL Server is waiting to complete an internal operation...."
And it tells me that if I get that message a lot, I should let Microsoft know.
So -- Microsoft, I'm letting you know.
Can anyone tell me why this is happening? I've searched MSDN and the knowledge base...I can only find one reference, and that is to a database diagram issue.
It takes a while to connect when this happens. So far, I haven't found a common denominator. It happens if I'm logged into my server and I try to connect to another instance, and it happens when I'm using my desktop...
Hi there, Just starting out so bear with me thanks. I am developing a ASP.NET web application with supporting ASP.NET Web services. The app sends a webmethod a bunch of values and the web service takes those values and through several stored procedures, updates a SQL database.
I initialise the DB connection right at the top of the web method following the method attributes :
.... ByVal strState As String, _ ByRef nominalExists As Boolean _ ) As String
Dim oConn As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("DBconnString")) Dim oCmd As SqlCommand = New SqlCommand Dim workParam As SqlParameter Dim myTrans As SqlTransaction Dim isError As Boolean Dim returnURL As String ....
The function continues into a SELECT CASE routine...
Right... So when the desired CASE fires I enter into the following type of code. Purpose being to fire off several different Stored procedures under the same transaction and the same database connection.
Dim oCheckDBReader As SqlDataReader Try oConn.Open() oCmd.Connection = oConn oCmd.CommandType = CommandType.Text oCmd.CommandText = "SOME SELECT STATEMENT" oCheckDBReader = oCmd.ExecuteReader If oCheckDBReader.HasRows Then nominalExists = True oCmd.Connection = Nothing oCheckDBReader.Close() oCmd.Dispose()
.... Catch myTrans.Rollback() .... Finally oCmd.Dispose() oConn.Dispose() .... End Try
Can someone please spare some time to tell me:
a) Whether I am going about all of this the right way b) Explain why I am getting the following error
System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.InvalidOperationException: The SqlCommand is currently busy Open, Fetching. at System.Data.SqlClient.SqlCommand.set_Transaction(SqlTransaction value) at System.Data.SqlClient.SqlTransaction.GetServerTransactionLevel() at System.Data.SqlClient.SqlTransaction.Rollback() at
Thanks a million to all that assist... Hope to hear from you soon....
In a very busy SQL2000 enterprise edition server with 8GB memory and 6 cpus sp3, I could not install a update trigger, unless all the appl connections are dropped. For this 24 HR running svr, could do it.
then I try to run a query as follows:
if exists (select rfABC.* A from rfABC inner join remoteSvr.XYZDB.dbo.vwIP L on A.Address = L.address and A.metro <> L.metro begin print ' ---- Yes metroID <> LAMetro, start job exec.... -----'
insert into tempCatchMetroIDGPRS select rfABC.*, metro, getdate() from rfABC inner join remoteSvr.XYZDB.dbo.vwIP L on rfABC.Address = L.address and rfABC.metro <> L.metro
update rfABC A set A.metro = L.metro from rfABC A inner join remoteSvr.XYZDB.dbo.vwIP L on A.Address = L.address and A.metro <> L.metro end else begin print ' ---- no metroID <> LAMetro, skip job exec.... -----' end
------------------------------ this query hang there could not execute. When I took off the if ... else condition, it run with like 0 second. Wondered if a 'busy' (which updates the IP address continueously) could cause above issues...
I am working on a SSIS solution that has 4 packages. All 4 have run before and I have not modified any of them, however I keep getting the error message and it keeps locking up on one of my packages. Does anyone have any advice or a remedy for this?
Mirroring has been working fine on 2 identical servers. We even tested failover and that worked....but today something happened (still investigating) and suddenly the Principal is disconnected and we cannot force failover with:
ALTER DATABASE [LeoCTI] SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
Error: The command failed because the database mirror is busy! Try again later!
How can I bring the principal back online? It won't let me do anything, won't let me turn off mirroring etc. etc.
We have been bothered with this problem for a while. Usually I happens in the early moring. Later on after the error is gone on auser, the error never happens again on any user for the day. Is this a web Server problem or an aspx.vb coding error? Thanks,Jeffrey Server Error in '/SSSSS' Application.
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.Source Error:
Line 270: oDBCommand.Connection.Open() Line 271: Line 272: Dim myReader As SqlDataReader _ Line 273: = oDBCommand.ExecuteReader(CommandBehavior.CloseConnection) Line 274:Source File: E:SSSSSScheduling.aspx.vb Line: 272 Stack Trace:
[SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +862234 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +739110 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1956 System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +31 System.Data.SqlClient.SqlDataReader.get_MetaData() +62 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +297 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +903 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +132 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32 System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +122 System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) +62 Soma.Scheduling.GetNextAutoAppointment() in E:SSSSSAScheduling.aspx.vb:272 Soma.Scheduling.Page_Load(Object sender, EventArgs e) in E:SSSSSScheduling.aspx.vb:61 System.Web.UI.Control.OnLoad(EventArgs e) +99 System.Web.UI.Control.LoadRecursive() +47 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1061
I'm now getting this with great regularity. Dell PWS390,2gig,Big HD,experienced user C++...Refactor recently installed although I do not use it. CLEARLY, the hang comes when one highlites a line and then cursors down to highlight additional lines...I believe this invokes refactoring --- while pre-refactoring install would get a hang occasionally, I now get one frequently. I am uninstalling refactor....
hi, like, if i need to do delete some items with the id = 10000 then also need to update on the remaining items on the with the same idthen i will need to go through all the records to fetch the items with the same id right? so, is there something that i can use to hold those records so that i can do the delete and update just on those records and don't need to query twice? or is there a way to do that in one go ?thanks in advance!
I have four tables: Customer (CustomerId INT, CountyId INT), County (CountyId INT), Search(SearchId INT), and SearchCriteria (SearchCriteriaId INT, SearchId INT, CountyId INT, [others not related to this]).
I want to search Customer based off of the Search record, which could have multiple SearchCriteria records. However, if there aren't any SearchCriteria records with CountyId populated for a given Search, I want it to assume to get all Customer records, regardless of CountyId.
Right now, I'm doing it this way.
DECLARE @SearchId INT = 100 SELECT * FROM Customer WHERE CountyId IN ( SELECT CASE WHEN EXISTS(SELECT CountyId FROM SearchCriteria WHERE SearchId = @SearchId) THEN SearchCriteria.CountyId
[Code] .....
This works; it just seems cludgy. Is there a more elegant way to do this?
