|
I have an ASP.NET application accessing a SQL Server database. Worked fine last night, this morning I am getting the following error continuously:
System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.InvalidOperationException: 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 reached. at System.Data.SqlClient.SqlConnection.Open() at MyService.DataService.AuthenticateUser(String DomainUsername) in \\Eoctstliciis01\CREQSERVICE\DataService.asmx.vb:line 169 --- End of inner exception stack trace ---
I have checked my code over and over again and I am closing/disposing of all connections as soon as I’ve finished with them. I’m the only person accessing the application at the moment, but I keep getting this error. I’ve checked Process Info / Current Activity in SQL enterprise manager, and there are a normal number of connections open.
· How can I check or set the number of pooled connections I have available?
· Is there a way of increasing the pool size?
Any help would be much appreciated, as I'm currently in a state of panic!
Thanks,
John.
www.silveronion.com[^]
|
|
|
|
|
Following up on this problem, I think there was an issue with the server I was running this on. I moved the code to my local IIS server and it works fine.
I discovered that PerfMon lets me view the number of connection pools being used. .NET CLR Data / SQLClient:Current # pooled connections.
However – I did some testing by leaving connections open on purpose, the app falls over when it reaches 100 (as expected), but the pooled connection counter never seems to reset back to zero, even when I stop/start IIS. This may be related to the following:
BUG: SqlClient Performance Counters Do Not Reset
ID: Q314429
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q314429
I found this on a google search, but the KB Article no longer exists – So I don't know if Q314429 is still an issue
Is there a more reliable way to monitor the number of pooled connections?
Thanks,
John.
www.silveronion.com[^]
|
|
|
|
|
Hi,
I 'm Using ADOX from VC(I suppose ADO VB is same that ADO
VC) to create tables in Access 2000 and Access 97,
containing
several columns/fields with
the (Yes/No) or(datatime) data type. The format property
of these fields is blank and needs to be
set to (yes/no) or (format date).
I need to make this change by using code and not manually,
unfortunatly I can't see
any way of accessing the Format property of the field.
Is there somebody who know how can I chande Format
Property From ADO?
Or is there some SQL Statement that can do it? I.e.
Something like that:
"ALTER TABLE MyTable ALTER COLUMN DataTimeField DataTime
(here my format string)" --> ("Medium Date") or ("Long
Data")
or
"ALTER TABLE MyTable ALTER COLUMN YesOrNo YESNO(here my
format string)" --> ("Yes/No") or ("True/False")
Regards:
KalliMan.
|
|
|
|
|
I am a student considering purchasing Visual Studio.net 2003. However my main form of database connectivity is an ODBC connection to a mySql server. I know that the next version supports ODBC out of the box rather than having to download the ODBC.net driver from Microsoft. Here is my question? Does anyone know if in the new version Microsoft has updated the wizards for Database conectivity to use ODBC commands as well as the standard Microsoft Sql Server SQL commands? Somebody please help.
Aaron Flaugh
|
|
|
|
|
HotQuant, LLC, has posted a couple of articles to our site dealing with implementing ODBC in ASP .NET. Both articles contain lots of heavily documented source code. They are:
"Implementing ODBC in ASP .NET"
http://www.hotquant.com/proj/hqweb/odbc.aspx
"Encapsulating ODBC in ASP .NET"
http://www.hotquant.com/proj/hqweb/encap.aspx
You are welcome to link to these articles from your site. Hope they prove useful.
Regards,
Jason G. Williscroft
General Manager
HotQuant, LLC
|
|
|
|
|
I have a master detail form that has a text box for the search criteria for the master record when a button is pressed the textbox.text is used as a parameter to the sqldataadapter to populate the text box controls. The detail is also filled using the textbox.text as a parameter to its sqlDataadapter.
I am having to do a lot of maniputlation with the dataset and check that I have not yet bound the textboxs for the master list.
What is the best way to implement a search then edit master detail form.
|
|
|
|
|
This is probably a query that comes often... sorry if it is so...
Does anybody have any experience of using mySQL? Does it work? Is it reliable? Is there a proper ODBC or ADO encapsulation? Does it compare in terms of performance to SQL server? Any experience with multithreading and Visual C++ 6.0?
I do not actually need a monster of a database (something like a couple of hundreds of megabytes probably). The writing would be intensive, the reading much less.
Any caveat welcome... well any feedback would!
Thanks in advance!
BadJerry
|
|
|
|
|
I downloaded it to use with ADO and VC++. I found it sucked. I couldn't get it to work, or even figure out how to set it up. I was much happier to spend the money to use SQL Server or MS Access. Apparently, there is a reason MS dominates the market. Turns out free stuff is only free if your time is of no value. Just my limited experience with mySQL.
|
|
|
|
|
If you want something free, use MSDE, not MySQL.
"Do unto others as you would have them do unto you." - Jesus
"An eye for an eye only makes the whole world blind." - Mahatma Gandhi
|
|
|
|
|
MySQL is a perfectly viable database. Version 4 even has a mechanism to embed a server within your application. I would agree that using it isn't for the faint of heart, however, just because it doesn't have an Access/Sql Server-like interface doesn't make it suck. What sucks is when people give up and say it sucks without giving it a fair shake doing what it does best--storing data. Once you get past the setup, it is just as easy to use as the others. As far as speed goes, MySql can keep up with the best of them. In my opinion, the only shortfall with MySql is the lack of stored procedures and transactions (though I think transactions have been added in version 4). If you go to the mysql.com website, you'll find programming APIs for different languages including C++. It is called MySql++. I've used it before and it works great. You can get MySQL++ from here: http://www.mysql.com/downloads/api-mysql++.html. Make sure you download the binary for VC++ 6.
Good luck.
-Matt
------------------------------------------
The 3 great virtues of a programmer:
Laziness, Impatience, and Hubris.
--Larry Wall
|
|
|
|
|
Hi Matt,
I totally agree, MySQL is my fav DBMS and it only lacks Triggers and Stored Procedures. It's easy install (download and launch installer, then it runs as a NT service) and powerful.
However I wonder if you were able to handle a local database using MySQL++, local database : DB as a file (like MS Access .mdb files...).
JM. Molina
Web: http://goa.ifrance.com
|
|
|
|
|
As far as I know, there is only one way to do this--MySQL 4 supports embedding. You would have to embed the MySQL server in your app. It sounds like a pain to me, but I haven't looked at it except to know that it exists.
-Matt
------------------------------------------
The 3 great virtues of a programmer:
Laziness, Impatience, and Hubris.
--Larry Wall
|
|
|
|
|
Hi,
Thanks for the tip ! So MSDE is a database engine that you can use in your desktop application ? Is it VC++ 6 compliant ? Where can I download it ? I can't find it on MSDN (http://www.microsoft.com/sql/techinfo/development/2000/MSDE2000.asp).
Regards,
JM. Molina
Web: http://goa.ifrance.com
|
|
|
|
|
There is a managed MySql data provider at Sourceforge.net. It probably performs better than using odbc. Check it out
|
|
|
|
|
|
I have this code which reads in a single record from my table..
Dim conSQL As New SqlConnection(strConn)
currentrow = Me.DataGrid1.CurrentRowIndex
currentcol = Me.DataGrid1.Text
Me.DataGrid1.Select(currentrow)
'Define and get the Orders Master
Dim cmdSQL As New SqlCommand("SELECT * FROM Orders WHERE OrderID ='" & (CType(DataGrid1(currentrow, 0), String)) & "'", conSQL)
Dim Rdr As SqlDataReader
conSQL.Open()
Rdr = cmdSQL.ExecuteReader()
Rdr.Read()
TextBox1.Text = (CType(DataGrid1(currentrow, 0), String))
ioOrderID.Text = (Rdr("OrderID")).ToString
ioCustomerID.Text = (Rdr("CustomerID")).ToString
conSQL.Close()
(more code and more output -- and a change of CustomerID takes place)....
I need to UPDATE that Customerid back to my Orders file now..
How do I do this...
Gollnick
|
|
|
|
|
Hey, need to know if there is a way to query MS Access, if any columns in a given table are Auto-Increment, maybe using the OleDb Schema or any way at all. My only idea was to check each column in the table if it had a data type of Int64, which is a long in Access, which is what Auto-Increment uses, but this is not a very resonable way to do so.
Thanks in advance for any help.
|
|
|
|
|
I write my ODBC-driver which should work in ms access. But... Here's the log of the driver with my comments:
access loads driver
SQLAllocEnv
SQLSetEnvAttr: Attr = 200 - access sets ODBC version 3
SQLAllocConnect
SQLGetInfo: InfoType = 77 - gets ODBC version, wich driver supports
SQLSetConnectOption
SQLGetConnectAttr: Attribute = 30002 - don't know what is it by now
SQLSetConnectAttr: Attribute = 30002
SQLDriverConnect - connecting to the database server, all's ok
SQLGetFunctions: fFunction = 999
SQLGetInfo: InfoType = 23
SQLGetInfo: InfoType = 24
SQLGetInfo: InfoType = 9
SQLGetInfo: InfoType = 6 - access asks the name of driver dll and then refuses to work farther, error -7778 no special message (
SQLDisconnect
SQLFreeConnect
SQLFreeEnv
May be someone knows what can it be or at least the way to understand what is the problem?
konst
|
|
|
|
|
Hello,
Application
-----------
I've created a multi-tier application. Its architecture is:
On client machine --> the client application is an ActiveX EXE component (VBasic).
On server machine --> some COM+ server applications and a SQLServer database.
Use Case
--------
CLIENT1
--> asks SQLServer database for an object (= a row in objects table).
The state of this object is stored in a storage file.
--> if the object can be check out, it means nobody else have checked out the object,
the server will set the objects.checkedoutby = ClientID and will copy the stg file
in a shared folder on server machine.
CLIENT2
--> tries to check out the same object
--> the server must know (??? - MY QUESTION IS ABOUT THIS POINT) if the CLIENT1 application
is still running or a crash has occured and CLIENT1 was not able to check in the object.
Remarks
-------
1) CLIENT2 will receive a copy of the storage file if CLIENT1 is still running
2) CLIENT2 will have the possibility to restore the storage file from
an older version if a crash occured in the CLIENT1 application.
3) Many clients can log in using the same account (user name and password)
4) There can be more than one instances of the application on the same client machine
My solution
-----------
My solution is to create an instance of an "watchdog" ActiveX EXE on the client machine
when the client application is started. This "watchdog", from time to time (5 minutes by example),
will modify the value of a date field named NotificationDate in database.
When the client application shuts down, the "watchdog" will stop updating that field.
The stored procedure responsible for check out will use the value of NotificationDate field
to decide whether the CLIENT1 application that use this object is still running or a crash has occured.
Questions
---------
1) Is there a pattern for this problem?
2) What about my solution?
10x,
Ovidiu
|
|
|
|
|
I have used a different method successfull in the past.
The database table has the following columns tacked on the end:
LeaseUserId Varchar(10)
LeaseExpiryTime Datetime
Whenever a user wants to do an update, I set the LeaseExpiryTime to a time that is five minutes in the future. The update stored procedure is designed to raise an error if the LeaseUserId is not the current user, or if the LeaseExpiryTime has passed (cos the user doesn't "own" the lease any more). The client may explicitely extend the lease (if the user is taking a long time entering their information).
This mechanism is similar to the method that most server operating systems use to "lock" files.
Hope this is helpful.
Andy
|
|
|
|
|
Thanks Andy for your suggestion.
Notes:
1. The clients read the storage file in order to create some SELECT-SQL statements for retrieving data from SQL Server database - only read, no update. The SELECT-SQL statements are passed to a COM+ component, it execute the statement and send back to client a disconnected recordset.
2. ONLY the storage file is updated. When the user press <save>, the storage file from DataBase is updated.
3. In a real scenario, an object (a row of table objects) can be used for a long period of time (many hours); the user save the storage file from time to time.
10x,
Ovidiu
|
|
|
|
|
i'm using a stored procedure in sql server 2000 to insert records to a tabel in a database its code is :
ALTER PROCEDURE NewLicense
@LicenseOwner VARCHAR(20),
@StartDate DATETIME OUTPUT,
@EndDate DateTime OUTPUT,
@LicenseID bigint OUTPUT
As INSERT INTO LicenseInfo (LicenseOwner) VALUES (@LicenseOwner)
SET @LicenseID = SCOPE_IDENTITY()
SET @StartDate = GETDATE()
SET @EndDate = GETDATE()
the problem is that it returns me corrct values in the output parameters but
it put NULL values in the tabel in the database , I want to know where is the problem?
Alpha
|
|
|
|
|
There's nothing wrong with your stored procedure syntax.
I have a few questions:
- How are you calling the stored procedure?
- How is your table (LicenseInfo) defined?
- Does the table have a trigger / constraint that might change the value or prevent the value from being inserted?
You could try to run a trace to see what what actually happens.
|
|
|
|
|
first:
I'm calling the procedure from a c# code using SqlCommand object
Second :
the tabel design is the same of the design of the parameters in the procedure
Third:
i didn't put any default values for the StartDate and EndDate when Designing the tabel
the question now is :
does the SET Command modify in the tabel as it modifies in the parameter value?
|
|
|
|
|
No, the set command will only modify the variables. The data in the table will not be modified.
<br />
INSERT INTO LicenseInfo (LicenseOwner) VALUES (@LicenseOwner)<br />
<br />
SET @LicenseID = SCOPE_IDENTITY()<br />
SET @StartDate = GETDATE()<br />
SET @EndDate = GETDATE()<br />
In the above code, the only value that will get set in the new row in the table is the LicenseOwner. The other values in the table will get the default values (or NULL if no default is set).
To update the table values, you need to pass them into the table. Now, I'm assuming that LicenseID is an identity field, so that will be automatically set. This is how I would implement the procedure:
<br />
declare @MyDate datetime<br />
SET @MyDate = GETDATE() -- this will allow us to have one unique date instead of calling getdate() twice <br />
SET @StartDate = @MyDate<br />
SET @EndDate = @MyDate<br />
<br />
INSERT INTO LicenseInfo (LicenseOwner, StartDate, EndDate) <br />
VALUES (@LicenseOwner, @StartDate, @EndDate)<br />
<br />
SET @LicenseID = @@identity<br />
Hope this helps.
|
|
|
|