I have recently moved over from SQL Server 2000 to 2005 and am now having an issue with my application with what appears to be, the number of recordsets that I can open/close on a single connection.
Here is a snippet of what I'm doing, in VB 2005 using ADO... (assuming the connection is already open and working)
Dim RS1 As RecordSet.
DIM RS2 As RecordSet
RS1.Open("SELECT...")
Do While Not RS1.EOF
count = count + 1
Console.Writeline(count)
RS2.Open("SELECT...")
..some processing...
RS2.Close
Loop
RS2 = Nothing
RS1.Close
RS1 = Nothing
Now as I said this all works fine when I connect to an SQL 2000 server but on SQL 2005 it bombs out when 'count' is approximately 1940 with an exception saying that the login failed. If I have Server Management Studio open, that connection will then freeze and throw up an error about how only one usage of each socket address is normally permitted - I think thats more a red herring though.
Any ideas? I've been through all the server settings and can not seem to find anything about recordset limits or timeouts. The only way I've been able to get around this problem at present is to open a new connection object for each iteration to be used by RS2.
Thanks everyone. Any pointers would be much appreciated.
Hey Everyone, I have what seems to be a unique problem, which I am not sure if it is an ASP problem or an SQL Server issue. I'm making a website that pulls its content from ntext fields within SQL Server 2000. Heres my problem, when I pull the data into the page it cuts off the text from the field at a certain spot. I pulled the data into a variable and did some tests on it, if I run length on it I get 1023 and also if I run length on just the recordset field I also get 1023. But if I check the SIZE of the recordset I get a return of 2046. I have had this problem before with an Access database as well but it seems as if the amount of characters that it cut off at with the access database was in the 200's. I've self taught myself ASP so this could very easily be something I have missed. Same for both Access and SQL Server.
pCadors->Open (_T"select foo from bar", pCadoCon->m_lpDispatch, eCursor); //SQL: a select statement here
The CADOConnection and CADORs are ADO connection, RecordSet wrapper classes created with ClassWizard.
I successfully open an ADO connection using the OEM driver by this DSN less way, and then passed it to ADO Recordset. From ODBC trace, I found that under the cover of the ADO, this connection is used to execute the select statement, but after that it automatically opens an other connection. I do not know why and is it possible to disable this?
My package needs to be a High Performance (target: 150,000 rows in 30 minutes) ETL solution. We are using all MS technologies - SSIS, SQL 2005, BIDS, etc. I need to loop the recordset executed by a Stored Proc in a Execute SQL Task in a Source Script Component.
If I use an ADO.NET Connection Manager, here is the code in the Source Script Component Public Overrides Sub CreateNewOutputRows()
Code 1
Dim sqlAdapter As New SqlDataAdapter
Dim dataRow As Data.DataRow
Dim ds As DataSet = CType(Me.Variables.rsSomeResultset, DataSet)
sqlAdapter.Fill(ds)
Iget: Error: System.InvalidCastException: Unable to cast object of type 'System.Object' to type 'System.Data.DataSet'.
Code 2
Dim oledbAdapter As New OleDb.OleDbDataAdapter Dim dataTable As DataTable
Error: System.ArgumentException: Object is not an ADODB.RecordSet or an ADODB.Record. Parameter name: adodb
It works all right when I use an OLEDB Connection Manager with the second code sample.
Question: In order to extract the maximum performance, wouldn't it be preferred to use ADO.NET with SqlClient Provider in an all SQL Server 2005 environment? Or will an OLEDB Connection provide comparable or equal performance?
If so, what code can I use? Since the recordset returned by the Stored Proc (in the Execute SQL Task) can only be captured in a System.Object variable and you can only use the overload of the Fill() method of the OleDbDataAdapter to accept an ADO Recordset or Record object.
I used adodb connection and recordset in script task. but i have an error saying adodb is not defined. how do i add it to reference? or, is adodb can run in script task or only ado.net?
What is the connection limit of SQL Server express. Currently I am using Access database and it allows < 300 concurrent connections. The database is supposed to be accessed through a web page. If there are hundreds of users hitting the page and updating the database, will there be any synchronization issue. How many concurrent connections can be opened?
Can someone help me with the following problem: I'm a SQL server DBA and we sell intern SQL-Server resources to the application teams based on needed storage and number of users. I have several servers shared by different application teams.
Now I'm looking for a method to limit the number of users working together on a specified Database. Because when a project manager wants resources for 100 users I want to be sure that only 100 users can connect to that particular database at the same time.
I know I can limit the number of connections on server level but I need this on Db level too.
This is my code... public partial class test : System.Web.UI.Page {protected void Page_Load(object sender, EventArgs e) {SqlDatabase database = (SqlDatabase)DatabaseFactory.CreateDatabase("ConnectionString");DbCommand command = database.GetSqlStringCommand("SELECT UserName FROM Users WHERE UserID = '6264'"); using (IDataReader reader = database.ExecuteReader(command)) {if (reader.Read()) {lblTest.Text = "test"; } } } }
This is my error: Server Error in '/' Application.
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. 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.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.Source Error:
Line 21: //DbCommand command = database.GetStoredProcCommand("CorrUsers_GetByUserId_s"); Line 22: DbCommand command = database.GetSqlStringCommand("SELECT UserName FROM Users WHERE UserID = '6264'"); Line 23: database.ExecuteReader(command); Line 24: using (IDataReader reader = database.ExecuteReader(command)) Line 25: {Source File: d:webwwwrootchacha078dev.cha.toplingo.com est.aspx.cs Line: 23 Stack Trace:
[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.] System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +1261381 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105 System.Data.SqlClient.SqlConnection.Open() +111 Microsoft.Practices.EnterpriseLibrary.Data.Database.GetNewOpenConnection() +195 Microsoft.Practices.EnterpriseLibrary.Data.Database.GetOpenConnection(Boolean disposeInnerConnection) +106 Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteReader(DbCommand command) +62 test.Page_Load(Object sender, EventArgs e) in d:webwwwrootchacha078dev.cha.toplingo.com est.aspx.cs:23 System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +15 System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +33 System.Web.UI.Control.OnLoad(EventArgs e) +99 System.Web.UI.Control.LoadRecursive() +47 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1436
Any help with this matter would be very much appreciated. Thank you for your time. Sincerely, Jeffrey Pham
Hi, I am trying to cycle through a table and trigger an event based on some critera. I am not sure how to do it. I am a classic VBA guy, so I might be way off: Dim myConnection As SqlConnection Dim myCommand As SqlCommand myConnection = New SqlConnection("MY SQL DATA SOURCE") myConnection.Open()
myCommand = New SqlCommand("SELECT * FROM history", myConnection) Dim dr = myCommand.ExecuteReader() Dim i As Integer = 1
While dr.read() i = i + 1 ' HOW DO I CYCLE THROUGH THE ROWS AND ASK IF A FIELD EQUALS A VALUE ' field name = "Tail"
hi I have 3 tables:Article,Source and File. Each article can have multiple filenames. The fields of table Article are:ArticleID,SourceID,ArticleDate,ArticleCategory The fields of table Source are:SourceID,SourceName the fields of Table File:ID,ArticleID,Filename Select*from Article inner join Source on Article.SourceID=source.SourceID order by ArticleDate I obtain a number of recordsets from the above query. Then for each recordset(Rs1),Let's say for the first recordset Rs1.MoveFirst I want to apply this query: Rs2.Open"Select SourceName,ArticleDate,File.Filename from [RS1] inner Join File on Article.ArticleID=File.ArticleID I want from the above query to have the Filenames corresponding to each Article because in my VB form I have 2 command buttons:one gives me the article's definition(Date,Source) and the other gives me the Filename of the current recordset(Article) The above SQL syntax is it correct?
SELECT TOP 2 MenuComments, MenuDate, MenuID, MenuIsActive, MenuName FROM Menu ORDER BY MenuDate DESC
This orders the data correctly, but the problem is, I need ONLY the SECOND row, not the top row. Also, because I am sorting for menus entered into the system, I cannot use a variable based on real dates (in other words, I can't use the server clock to help filter the results).
Any and all help would be GREATLY appreciated -- I've been banging my head against this one all day!
I am using classic ASP. Records are grouped together by a GroupUnique number. Some groups are small with about 10 records, othere are larger at about 160.
For each record, I have about 50 columns of data that I need to display on a webpage. Because the 50 columns don't easily fit on the one page, I create two tables, each displaying 26 columns, the first columnn being an ID column. Due to the size of groups, sometimes the tables are very large - and when they get too big it overloads the server.
I think the main problem is the two tables. I use two recordsets (one of them is shown below - although instead of a SELECT * I do in fact name the columns needed for each table). I have to use two because the Recordsets don't like me using the ID column again - once it is used it is gone.
Is there a better way to store all of this information so that I can just use the one recordset? Possibly in an array? Is there a more efficient way of getting the data?
<% Dim Recordset4__MMColParam1 Recordset4__MMColParam1 = "1" If (Scramble.Fields.Item("GU").Value <> "") Then Recordset4__MMColParam1 = Scramble.Fields.Item("GU").Value End If %>
I am having some problems. I'm pretty new to sql and really dont know how to achieve more than the basic selects etc.,
My problem is that I have a recordset on one page http://www.photoghetto.com/photo-images/animals.asp that returns the results of all the images in one category. In this case it's animals and wildlife.
The user can click on any image and go to a page that shows a larger detail version of the image. http://www.photoghetto.com/photo-images/animals-photo-detail.asp
What I do is post the ProductID number to this page so that the selected thumb is shown. So for exmaple for the image of the wild cat it is http://www.photoghetto.com/photo-images/animals-photo-detail.asp?ProductID=6
My problem is that on the animals-photo-detail.asp the user has to be able to "scroll" through all the images from the category.
I.e should be able to hit the previous image button and see the stallion image, or the next button to see the butterfly etc., and thus scroll through all the images on this age if he/ she wishes to.
I understand the principles of having a results page and then being able to click on one of the results and getting a detail page. Such as I have it here. With the http://www.photoghetto.com/photo-images/animals.asp as a results page listing all the results of the category, and then when the user clicks on one of the results, goes to a detail page, for example, http://www.photoghetto.com/photo-images/animals-photo-detail.asp?ProductID=6.
My problem is that what I need is the recordset from the listing page to function on the detail page so that the user can scroll through the results in the same order that they were on the results page.
I have searched now for a couple of days online and every tutorial I find shows the same structure. Results Page > Detail Page.
The sql i am using on the detail page is simply,
<% Dim photos_rs__MMColParam photos_rs__MMColParam = "1" If (Request.QueryString("ProductID") <> "") Then photos_rs__MMColParam = Request.QueryString("ProductID") End If %>
So I understand why it will only display the one result since thats the detail page.
Is it possible to be able to scroll through the results using the previous and next buttons as I have setup in the display on the http://www.photoghetto.com/photo-images/animals-photo-detail.asp page. For example, http://www.photoghetto.com/photo-images/animals-photo-detail.asp=ProductID=6.
Since the resutls are gathered from across the database its not possible to have a href tage that does a <<< http://www.photoghetto.com/photo-images/animals-photo-detail.asp=ProductID=(6 -1) or a >>> http://www.photoghetto.com/photo-images/animals-photo-detail.asp=ProductID=(6+1).
I guess it has to be something with a recordset index but does anyone know how to do it? And does anyone have the ability to help me do it?
My boss is kicking my butt now to get this thing online at some point today, and I'm turning to you guys for help if possible.
I'm sorry if this is a stupid question. I've really ran out of ideas.
This asp code displayes records in a combo box:<%openDB()call updateDB("usp_retrieveOptions",rs)if not rs.eof then%><tr><td width="66">Options</td><td width="137"><select name="select1" class="TextField1"><%i = 0do while not rs.eofif rs(0) <> Arr(i) thenresponse.write "<option value=" & rs(0) & ">" & rs(1)i = i + 1end ifrs.movenextloop%></select></td></tr><tr><td colspan="2" width="206"><center><table width="71" border="0" cellspacing="3" cellpadding="0"height="33"><tr><td width="9" height="30"><input type="submit" name="Assign" value="Assign"></td></tr></table></center></td></tr><%end ifcloseRS()closeDB()%>The call updateDB("usp_retrieveOptions",rs) invokessub updateDB(SQL,rs)set rs = objConn.Execute(SQL)end suband my usp_retrieveOptions stored procedure:create procedure usp_retrieveOptionsAS SET NOCOUNT ONSELECT OptionID, Description FROM OptionsReturnGOnow in my asp code when I try response.write rs.RecordCount I am getting-1 all the time. How do I solve the problem. Your help is kindlyappreciated.Eugene Anthony*** Sent via Developersdex http://www.developersdex.com ***
Im doing a select that should retrieve a name from one table and display thenumber of correct bets done in the betDB (using the gameDB that has info onhow a game ended)I want the "MyVAR" value to be used in the inner select statement withouttoo much hassle. As you can see im trying to get the "MyVAR" to insert inthe bottom line of the code.Whats the quick fix to this one..?Thanks in advance :-)---------- code begin ----------select memberDB.memberID as MyVAR, (select count(GamesDB.GameID)from GamesDBinner join GameBetDBon GameBetDB.betHome = GamesDB.homeGoal and GameBetDB.betAway =GamesDB.awaygoalinner join memberDBon memberDB.memberID = GameBetDB.memberIDwhere GamesDB.gameID=GameBetDB.gameIDand GameBetDB.memberID= MyVAR ) as wins from memberDB---------- code end ----------
I added a connection (ADO.NET) object by name testCon in the connection manager - I wanted to programmatically supply the connection string. So I used the "Expressions" property of the connection object and set the connectionstring to one DTS variable. The idea is to supply the connection string value to the variable - so that the connection object uses my connection string.
Then I added a "Backup Database Task" to my package with the name BkpTask. Now whenever I try to set the connection property of BkpTask to the testCon connection object, by typing testCon, it automatically gets cleared. I am not able to set the connection value.
Then after spending several hours I found that this is because I have customized the connection string in testCon. If I don't customize the connection string, I am able to enter the "testCon" value in the connection property of the BkpTask.
I am connecting to the database as following: set con = server.createobject("adodb.connection") con.open "connectionstring" set rs = con.execute("select * from tablename")
I am able to display the records but if I want to give adopenstatic to the above connection, how can I do so?
I have some problems to edit a recordset in an ActiveX DTS using Vbscript. Here is an example of the script :
dim varsql, varset, varconn set varconn = CreateObject("ADODB.Connection") set varset = CreateObject("ADODB.Recordset") varconn.Open = "Provider=SQLOLEDB.1;Data Source=(local);Initial Catalog=Enregistrement3;user id = 'sa';password=''"
if varset.recordcount >0 then do while varset.eof varset.edit ......................... varset.update varset.movenext loop end if varset.close varconn.close
Does someone see what is wrong ? I allways get -1 for the varset.recordcount ! (I checked there are some records into the table). I don't think it is a problem of user's right as it works with the same user configuration using a SQL action requery. If I modify the open statement like this : varset.Open varsql, varconn, 1 varset.recordcount contains the good number of records but the recordset is read noly and can't be modified...
<!--- Update the DISTANCE field on STORE table ---> <cfquery name="UpdateZips" datasource="#application.data#" username="#application.username#" password="#application.password#"> exec Stores_UpdateZipSeachInfo '#Dist#', '#zip2.zipcode#' </cfquery> </cfloop>
I'm an SQL novice, but I know this must be a common problem.
I'm trying to select a recordset (using ASP), but I know I only want part of the recordset, and am not sure how to limit it ahead of time.
For example, the query will return about 500 rows, but I know I only want to use a small section of these records. I want to give the user the ability to navigate through small sections of these 500 rows without having to get all rows all the time. I know ahead of time which rows to get, but have no idea how to limit the recordset before I get it (there is no fields in the database to help).
This is what I'm doing now. "select * from xyz where id=xxx order by date desc;" I know I only want the first 10, or 10-20, or 400-410. The way I'm doing it now, I'm getting the whole recordset each time, doing a "rs.move x" where x is where I want to start. This is really a waste of network traffic and memory since my SQL server is on a different machine as the web server running ASP.
How do I do this?
Please email me if you could at pmt@vantagenet.com
i have a select query that returns multiple rows (within a cursor). How do i loop through the rows to process it (in a stored proc)? I donot want to use nested cursors. a code sample is requested.
id | value ========== 1,abc 1,def 1,ghi 2,def 2,jkl
I want these values to go horizontally into another table matched on id, to look like this:
id | value ======== 1,abc def ghi 2, def jkl
I built a cursor to parse through it but was taking forever (there's 185,000 records in the reference table). Any idea's on the fastest way to perform this function?
I was wondering if there is a way to randomise a recordset once you have performed a query. eg I want my query return a set of records based upon criteria supplied and then either randomise the order of the recordset or only return 3 random records - which every is simplest to do.
Can this be done in SQL or do I need to handle it outside of my SQL query in my business logic?
'Use the ADO connection that Access uses Set cn = CurrentProject.AccessConnection
'Create an instance of the ADO Recordset class, and 'set its properties Set rs = New ADODB.Recordset With rs Set .ActiveConnection = cn .Source = RecSourceFloater .LockType = adLockOptimistic .CursorType = adOpenKeyset .CursorLocation = adUseServer .Open End With
'Set the form's Recordset property to the ADO recordset Set WeekDay.Form.Recordset = rs
Set rs = Nothing Set cn = Nothing
End Function
When I edit the form I noticed that I could not go back to a recently added record because it was not available, so I concluded that I had to force an update, which I do in the before update event of my form, as follows;
'Update the underlying recordset Me.Recordset.Update
It works great on my test server here, but when I instal it on the SQL Server 2000 - I get an error 'EOF' or 'BOF' is true......... so it fails
This is a C++ / ADO / SQL question. Maybe not the right forum but I am guessing there are some programmers out there ...
I am trying to use ADO disconnected recordset to insert data into a sql table. I am using AddNew(vField, vValue) with UpdateBatch(). The code below does not throw any exceptions ... but does not add data to the table.
Any comments are appreciated, Thanks, Chris
void CTestApp::TestDatabaseUpdateBatch1a(void) { int nDataCount = 0; long nIndex = 0; long nIndex2 = 0;
try { // When we open the application we will open the ADO connection pConnection.CreateInstance(__uuidof(Connection));
// Replace Data Source value with your server name. bstr_t bstrConnect("Provider='sqloledb';Data Source='SQLDEV';" "Initial Catalog='AlphaNumericData';" "User Id=cmacgowan;Password=cmacgowan");
// Open the ado connection pConnection->Open(bstrConnect,"","",adConnectUnspecified);
// Create an instance of the database pRecordset.CreateInstance(__uuidof(Recordset));
// Select the correct sql string. Note that we are creating an // empty string by doing a select on the primary key. We are only // doing inserts and we do not want to bring data back from the // server
csSQL = "SELECT * FROM dbo.AAMacgowanTest WHERE RecordId IS NULL"; // csSQL = "SELECT * FROM dbo.DICastRaw1Hr";
// Test to see if the recordset is connected if(pRecordset->GetState() != adStateClosed) { // The recordset is connected, we will see if we are // at the end
if((pRecordset->BOF) && (pRecordset->GetadoEOF())) { // The recordset is empty bIsEmpty = false; }
// COleDateTime is a wrapper for VARIANT's DATE type. COleVariant is // a wrapper for VARIANTs themselves. If you need to create a // variant, you can say: COleDateTime oledtCurrentDate2 = COleDateTime::GetCurrentTime();
// Convert the OleDateTime to the varient COleVariant vCurrentDateTime2(oledtCurrentDate2);
//Set the DATE variant data type. memset(&st, 0, sizeof(SYSTEMTIME)); st.wYear = 2000; st.wMonth = 1; st.wDay = 1; st.wHour = 12;
// vect is a vector of COleSafeArrays containing the records for(i = 0; i < 10; i++) {
// Setup the data nValueIndex = 0; vValueList[nValueIndex].vt = VT_BSTR; vValueList[nValueIndex].bstrVal = ::SysAllocString(L"BLUE"); nValueIndex++;
I cant seem to display the Customer_nickname from my Customer_recordset....... And the errors says that " Object is required" can anyone help me on this..?
