Transaction limitations

TheMSsForum.com: The Microsoft Software Forum

The MSS Forum ‹ Ado
- Archive
  - Biz
  - MCSE
  - CRM
  - Drivers
  - Framework
  - ADO
  - ASP
  - Compact
  - Forms
  - Dotnet
  - C#
  - VB
  - FontpageGen
  - Excel
  - WorkSheet
  - Exchange
  - Setup
  - Fox
  - Fontpage
  - ASP
  - IIS
  - Entourage
  - Money
  - Messanger
  - PocketPC
  - Powerpoint
  - Project
  - Publisher
  - Excel
  - VB
  - Security
  - Portal
  - Services
  - SQLServerDev
  - SVCS
  - SQLServer
  - VB
  - VC
  - MFC
  - ExcelGen

- Previous
  - 1
    - Where to Lock when updating a DataTable Hi, I have an C# app which uses a Dataset which contains multiple DataTables as an in memory database. (Currently the dataset is not connected to a database.) The app is multi threaded and adds/update rows in the various tables. Currently I lock the Dataset on each modification - but am I locking at the right (most efficent) level? Could I just lock at the DataTable or even just the row? or can I do without locks? TIA swin Tag: Transaction limitations Tag: 129648
  - 2
    - How to filter by Month using Dataview.RowFilter Hello, My Code: dvM1.RowFilter = "DATEPART(Month, Datum) = " + Monat.Month + " AND DATEPART(Year, Datum) = " + Monat.Year; But DATEPART is not a ADO Functions? Can someone help me? Tag: Transaction limitations Tag: 129647
  - 3
    - Run SQL against DataView Object Hi, I have a requirement in a current project to show the effect of a particular SQL Statement on a given database table. The statement will only effect one row but could be an UPDATE, DELETE or an INSERT command. The user should see the table row before and after for an UPDATE, a new row for an INSERT and the row to be removed for a DELETE command. I intend to represent a copy of the table before and after the query has been run (without affecting the database) as a DataView object. Does anyone know whether there is any built in functionality for executing raw SQL statements against ADO.NET objects? My current plan (not find anything more suitable) involves creating a function which would parse the SQL statement as a string and determine the target table and the type of query (it would always start with either INSERT, DELETE or UPDATE). It would then locate the WHERE clause which could easily be applied to a DataView of the table. Has anyone else ever come across something like this before? I am relatively new to .NET and am not sure whether they would be a more elequent/faster approach. Any views would be greatly appreciated! I am writing the application in C# although that is probably irrelevant here. Thanks! Andrew Tag: Transaction limitations Tag: 129646
  - 4
    - Simple Multiple Row Insert Question Hi everyone, I have a pretty simple situation and I'm hoping that someone could give me some advice on the best way to handle it. I need to insert a whole load of rows into SQL Server 2005 using ado.net. I use custom business objects to represent the rows. So for example, in this specific case, I have a big collection of "Booking" objects. I also have a stored procedure called CreateBooking. My question is - assuming that I need to import say, 7000 rows in one go, is simply looping through the Bookings collection and calling the Stored Procedure for each booking the best way to do things? Is there some other way to get that much data into the database without constantly calling the stored procedure 7000 times. I use the same connection each time, but still, it seems a bit clumsy. I was wondering if there is a better way. Many thanks to anyone who can advise... Simon PS: I really need to use ADO.net to do this - importing by some other mechanism isn't going to be possible in this instance Tag: Transaction limitations Tag: 129640
  - 5
    - SqlBulkCopy with SQL2005 Column encryption Hi, Can the SqlBulkCopy class in .NET 2.0 be used in conjunction with SQL Server 2005's native column encryption? Many Thanks, Andy Tag: Transaction limitations Tag: 129636
  - 6
    - Programatically unique Hi, I have have dataset on the client side and I would like to set it up so that it cant take data entered that isnt unique on two columns. There is obviously the unique property of each column , but they work indepedant, im looking for something that will be unique on more than one column. Thanks Tag: Transaction limitations Tag: 129635
  - 7
    - Issue with Command Timed out with data adapter When i try to fill a dataset with a adapter am setting the command timed out to 15 seconds. but the querry is not timing out after 15 seconds. what may be the issue? Code i am using ------------------ SqlConnection objConn = new SqlConnection("Connection string"); objConn.Open(); SqlCommand objComand = new SqlCommand("select * from table1", objConn); //Query will execute for 20 miniutes objComand.CommandTimeout = 15; //This is not working SqlDataAdapter sqlAdapter = new SqlDataAdapter(objComand); //create a new dataset to hold our data DataSet ds = new DataSet(); sqlAdapter.Fill(ds); Tag: Transaction limitations Tag: 129631
  - 8
    - ADO.Net- Anyone know of a way to find all databases on my computer? I'd like to find all the databases on my computer. In other words, would like to find information similar to the info shown in Server Explorer in Visual Studio .Net. Anyone know of a way of doing this? Tag: Transaction limitations Tag: 129625
  - 9
    - Created Dataset with two tables - How do I populate at runtime? In VS 2005, I created a dataset with the dataset designer that has two tables that have a parent-child relationship (which I also added in the designer). Each table gets data from a stored procedure. I want to populate the dataset at runtime, passing in a parameter from another control on the form, then pass the dataset to a crystal report. And that is what I haven't figured out - how to populate the dataset at run time. Can someone point me in the right direction? thanks -- dchman Tag: Transaction limitations Tag: 129610
  - 10
    - Programatically call extended stored procedure? Hi, C# / .NET Framework 1.1 I would like to query extended stored procedure like stored procedure... However, I found that CommandType property does not have Extended Stored Procedure value..... I tried something like.. SqlConnection con = new SqlConnection(....); SqlCommand command = new SqlCommand('xp_blahblahblah", con); command.CommandType = CommandType.StoredProcedure; ..... ... command.ExecuteScalar(); However, it always throw exception saying "Could not find store procedure xp_blahblahblah".... Is there any way I can use the extended stored procedure programatically? Thanks. ..................................................................TJ Tag: Transaction limitations Tag: 129609
  - 11
    - How do I compare 2 UniqueIdentifier Guids? Hello. I have two databases, both of which store an objectGUID (from a contact object) in a UniqueIdentifier Column. How can I search a value picked up in one (using sqlreader) to a value in the other table? For example, using the following: "SELECT * FROM table1 Where objectGUID = '" & sqlReader3("guid") & "'" This just tells me that I cannot compare objects. How do I cast them, and what is the syntax for the sql command? Thanks in advance! Tag: Transaction limitations Tag: 129605
  - 12
    - newbie question I have a collection of objects that can be bound directly as a data source into a DataViewGrid... works great, but now I can't figure out how to get the columns sorted in the order that I want, or how to hide certain columns. Is this possible? Paul Tag: Transaction limitations Tag: 129604
  - 13
    - number of recs from select Hi all, I am trying to figure out how to retrieve the number of records resulting from a select. I tried executereader but the object does not contain the number iof records retrieved. Any suggestions? Thanks, Carlos Tag: Transaction limitations Tag: 129603
  - 14
    - question about client callback and gridview Hi, I read about client callback and i have a question. Assume the option "sorting" is set in a gridview. Each time the user clicks on a fieldname, the data are sorted. My question is: are the data on each click fetched from the server, or are they 'cached' on the client-side and so avoiding a lot of transferts between server and browser? If not 'cached', would it not bebetter to use the client callback technology? Thanks for explanation Chris Tag: Transaction limitations Tag: 129599
  - 15
    - SP w/ parameter using a DAL Hi, I am using the enterprise DAL that I downloaded from Microsoft. I am trying to create a data table based SP with a passed parameter but I keep getting a null exception error. I thought it was set-up correctly but it doesn't appear data table is being populated. The code below is what I have written but confused on the issue. Any help or suggestion is greatly appreciated. I am new to C# and .Net so I am going through growing pains Error: An unhandled exception of type 'System.NullReferenceException' occurred in classlibrary1.dll Mark private void cbPyramid_SelectedIndexChanged(object sender, System.EventArgs e) { DataTable dt = new DataTable(); int i; Department dsDept = new Department(cbPyramid.Text); dt = dsDept.Get_Dept; cbDept.Items.Clear(); for (i = 0; i < dt.Rows.Count; i++) { cbDept.Items.Add(dt.Rows["Dept"]); } } //Business object code #region Private Members private DataSet _dsDept; #endregion #region Public Properties public DataTable Get_Dept { get { return _dsDept.Tables[0]; } } #endregion #region Public Methods public Department(string Pyramid) { //This will establish the database connection Database db = DatabaseFactory.CreateDatabase("Test"); //This will allow you to call a SP and use parameters DBCommandWrapper dbc = db.GetStoredProcCommandWrapper("usp_Inv_GetDepartment"); dbc.AddInParameter("@pyramid", DbType.String, Pyramid); db.ExecuteDataSet(dbc); } #endregion } Tag: Transaction limitations Tag: 129598
  - 16
    - Informix Drivers I am using Informix ODBC to connect to my POS database. It works fine and shows all the tables in Excel but when I connect to the ODBC in Visual Web Developer it says Test Successed but doesn't show any tables or other information. Any help on getting Informix working with Visual Web Developer. Thanks. Tag: Transaction limitations Tag: 129594
  - 17
    - SQLTransaction IsolationLevel questions Hello, Sorry for posting this in more than one place, I meant to put it in this newsgroup, not m.p.d.f.aspnet... I have a VB.NET subroutine similar to the following: Private Sub RunUpdateTransaction(ByVal UpdateSQL As String, ByVal UpdateConn As SqlConnection) Dim trnDedupe As SqlTransaction = UpdateConn.BeginTransaction(IsolationLevel.ReadCommitted, "Dedupe") Dim cmdDedupe As New SqlCommand(UpdateSQL, UpdateConn, trnDedupe) Try Dim iUpdated As Integer iUpdated = cmdDedupe.ExecuteNonQuery trnDedupe.Rollback() Catch ex As Exception Throw ex Finally If Not cmdDedupe Is Nothing Then cmdDedupe.Dispose() End Try End Sub UpdateSQL looks like: INSERT INTO MyTable1(Field1, Field2)(SELECT Field1, Field2 FROM MyTable1 WHERE MyId1 = 123); UPDATE MyTable2 SET MyID1 = (SELECT MAX(MyID1) FROM MyTable1) WHERE MyID1 = 123 AND MyID2 = 456; I have a breakpoint on trnDedupe.Rollback(). I then switched to Query Analyzer and tried to run a query SELECT * FROM MyTable1 WHERE MyID1 = (SELECT MAX(MyID1) FROM MyTable1) I started this query before I stepped away from my desk and it's been running for 2-1/2 hours. Should I try another type of IsolationLevel other than ReadCommitted. It looks like ReadCommitted might be good to prevent someone else from inserting a record into MyTable1 in between my INSERT and UPDATE. From the documentation: -------------------------------------------------------------------------------------- ReadUncommitted: Shared locks are held while the data is being read to avoid dirty reads, but the data can be changed before the end of the transaction, resulting in non-repeatable reads or phantom data. ReadUncommitted: A dirty read is possible, meaning that no shared locks are issued and no exclusive locks are honored. RepeatableRead: Locks are placed on all data that is used in a query, preventing other users from updating the data. Prevents non-repeatable reads but phantom rows are still possible. -------------------------------------------------------------------------------------- If I'm reading the documentation correctly, it looks like I might want to use ReadUncommitted for my testing and ReadUncommitted when I'm using the application in production to prevent inserts between my INSERT and UPDATE. Would any of the other IsolationLevels be useful? Thanks, Eric Tag: Transaction limitations Tag: 129592
  - 18
    - Need help with Case-sensitive queries in SQLServer 2000 I need to retrieve records just as they are and enforce case-sensitivy. For example, my query return 'E1234' in place of 'e1234'. I want to return exactly what is in the table. How do I do that? Thnaks mo Tag: Transaction limitations Tag: 129590
  - 19
    - Bindingsource c# Hi there, are there any good reference examples for using a bindingsource object ? I new to ado.net and trying to create a simple maintenance app on an sql mobile database. I created a datasource and then dragged a table onto my form (Detail mode). Where to go from there ? thanx in advance. Tag: Transaction limitations Tag: 129587
  - 20
    - New to ASP.Net, Cascading DropDown List in Detail View...Please help A) Destination Table with 4 Fields. 1) Last Name 2) First Name 3) State 4) Zip Code. B) Look up table State/Zip Code with 2 Fields 1) State 2) Zip Code. I created a DetailView Form in Asp.net to Insert/Update Destination Table. Destination Table's State and Zip Code Fields are converted from Bound Column to Item/Edit/Insert Template, Created 2 Drop Down Lists, DDLState and DDLZIP. I am using '<%# Bind(...)%>' for both of them. Also, I do have 2 SQLDatasource for DDL's (i.e. SqlState for DDLState and SqlZip for DDLZIP). My Intention is very simple: When user select State Name from DDLState DropDownList List (example: NJ), in DDLZIP Dropdown list it must show only Zip Codes from that State. In order to Change the Value of DDLZIP, users must Change the Value of DDLState. Basically DDLZIP is Child/Dependant of DDLState. I did correctly setup proper reference to DDLState in SelectedParameters, Control Parameters, PropertyName in SqlZip (SqlDataSource for DDLZIP). This Cascading dropdown logic does work Fine in a Form without a DetailView. I tested it. But I am having hard time to implement this within Detail View. Any Complete Example (i.e. Form with Subs) will be highly appreciated. I will be glad if this example is based on NorthWind Database. Thanks a lot for your time and efforts. - Sam Tag: Transaction limitations Tag: 129584
  - 21
    - Validating Data using Typed Dataset Hi! All, How can i validate data filling into a typed dataset? e.g. Say I am importing data from a flat file into a typed dataset. I want to validate that each data entering into the dataset are in match with the dataset Schema and if any error i want to log the same row and move to execute the next row. How do I do this?? Regards, Baren Tag: Transaction limitations Tag: 129583
  - 22
    - Datasets fundamental Hi there! I'm very new to dot net envirnoment and have little bit understanding. Due to some reason, I need to work straight forward on datasets and webservices and xml. I don't have much hold on the same but would appreciate if someone put lights on the given questions: a) How datasets which has passed by value can be retreived from cache? b) How would one know whether datasets is containing any records or not? Is there any way to test the same? Please let me know via coding. c) How long datasets will store in server memory? d) How to create a dataset and publish it in cache? Beside this, I do have other questions which will not fall into this group like : 1) How to map datasets records to update a MYSQL Server? Please help me out. With Thanks! Shyam Tag: Transaction limitations Tag: 129581
  - 23
    - PLEASE tell me how to handle RowUpdated with/against a typed dataset I have searched and searched and searched and sear.. anyway, you get the idea. I am trying desperately to figure out how, in "C++/CLI" (Managed C++/2005) to handle the RowUpdated event in my code but can not find an answer that works. I either get the always fun "error C3767: 'FrameWorks::eclipseDataSetTableAdapters::RepairDataTableAdapter::Adapter::get': candidate function(s) not accessible" error, or other various errors. Apparently, my strongly typed RepairDataTableAdapter does NOT expose anything that even looks like RowUpdated. So, given that, I thought ok, I'll do it the hard way, and try to deal with this with untyped .. objects. So I've tried to create an adapter that would expose RowUpdated, but then I wanted to get the existing insert, update, ... commands from my typed adapter so that I wouldn't completely redo all my database code. Well, that didn't work either because those objects are also not exposed. Not even read-only! This is what led to the error above. Is there SOMEONE, ANYONE out there who can tell me how the frell to handle RowUpdated with a typed adapter? I'm begging here. Thanks, Scott "Cmdr. Beavis" F. Tag: Transaction limitations Tag: 129574
  - 24
    - Open DataReader associated with this Command which must be closed first. Hello, I'm getting the a "There is already an open DataReader associated with this Command which must be closed first" error message in the following scenario. The message seems misleading, since the command isn't associated with the datareader, but the connection is. Is there any way to get this work work without closing my DataReader? Thanks, Eric ------- Dim conn As SqlConnection Dim cmd As SqlCommand Dim drTests As SqlDataReader strTestQuery = BuildTestSQL() cmd = New SqlCommand(strTestQuery, conn) drTests = cmd.ExecuteReader Dim iDataRowCounter As Integer = 0 Do While drTests.Read Dim strTest2SQL As String strTest2SQL = BuildTest2SQL() RunUpdateTransaction(strTest2SQL, conn) Loop Private Sub RunUpdateTransaction(ByVal UpdateSQL As String, ByVal conn As SqlConnection) Dim cmdTest As New SqlCommand(UpdateSQL, conn) Try Dim trnTestTest As SqlTransaction trnTestTest = conn.BeginTransaction(IsolationLevel.ReadCommitted, "TestTest") Dim iUpdated As Integer iUpdated = cmdTest.ExecuteNonQuery 'TODO:Change rollback tran to Commit trnTestTest.Rollback() 'trnTestTest.Commit() Catch ex As Exception Throw ex Finally If Not cmdTest Is Nothing Then cmdTest.Dispose() End Try End Sub Tag: Transaction limitations Tag: 129564
  - 25
    - Generating a collection from a data set table Hi, I have a strongly typed dataset with 2 tables: Orders and OrdersDetails, that have a parent-child relationship by OrderId. I want to be able to work sequentually on each row in Orders and in OrderDetails in the following manner: foreach (TableRow HeadRow in Orders.Rows) { ... foreach(TableRow DetRow in OrdersDetails.Rows) { ..... } } My question is how can I make sure the inner foreach works only on the Rows that have the same OrderId (the OrderId from the outter foreach) Thanks Tag: Transaction limitations Tag: 129563
- Next
  - 1
    - Good way to do lookups I want to have a a set of "records" (i.e. would have used a Recordset in the VB6 world) that contains a list of say 500 short string values to lookup and then 500 short strings to replace them with when found. What I'm going to use this record set for is to lookup the parts of a street address and replace them with standard abbreviations. What I could use guidance with is the best object to use for this record set which will be used for lookups. Tag: Transaction limitations Tag: 129562
  - 2
    - merge not working I'm trying to handle concurrency updating errors in my app - vb.net 2003, ado.net 1.1. I have a datatable with the data the user pulled and then changed ("dtMain") and another that I filled after the updates failed, with the most current data in the database ("dtConflict"). I am allowing the user to decide on a row-by-row basis how to handle the update. When I try to merge a particular row's current values (from "dtConflict") into the main table ("dtMain") nothing is happening. Here is the code (I added dtMain to a dataset "dsBig" in order to access the Merge method, which is still unavailable to me on a datatable as I'm in ado.net 1.1): Dim row As DataRow = CType(objCurrencyManager.Current, DataRowView).Row Dim rowConflict As DataRow = dtConflicts.Rows.Find(row(0)) if blnPreserve = True then dsBig.Merge(New DataRow() {rowConflict}, True, MissingSchemaAction.Ignore) else dsBig.Merge(New DataRow() {rowConflict}) end if row.ClearErrors() The merge does not throw an exception but neither does it get the new values. This code is basically taken from David Sceppa's Microsoft ADO.NET (Core Reference). Tag: Transaction limitations Tag: 129561
  - 3
    - AutoIncrement and MultiStep OLE DB Hello this error starts to really go o nmy nerves. I Searched the i net but found no solution for it. Maybe you can help. My problem is, that i want to convert an Access-DB into a SQL-Server DB (i cant use the tools, because this should work also on systems which only have the MSDE and no access). The error ocurres when the "AutoIncrement" Property is set to true and i try to add the Table to the SQL-Server-DB (e.g. Catalog). The only solution i found for a similar problem, is that the catalog has to be set prior to the properties are manipulated. I do this, but still it doesnt work. I use MDAC 2.8, SQL-Server 2000 What am i missing??? Thanks Matthias This is the code (C#): protected void CloneColumn(ADOX.Table refTable, ref ADOX.Table destTable, string refColumnName, string destColumnName, ref ADOX.Catalog catDest) { ADOX.Column newColumn = new ADOX.ColumnClass(); newColumn.ParentCatalog = catDest; newColumn.Name = destColumnName; newColumn.Type = refTable.Columns[refColumnName].Type; if (newColumn.Type==ADOX.DataTypeEnum.adDate) newColumn.Type= ADOX.DataTypeEnum.adDBTimeStamp; newColumn.DefinedSize = refTable.Columns[refColumnName].DefinedSize; UpdateColumnProperties(refTable.Columns[refColumnName], ref catDest, ref newColumn, Common.Constant.listOfSupportedProperties, "created"); destTable.Columns.Append( newColumn, newColumn.Type,newColumn.DefinedSize); } protected void UpdateColumnProperties( ADOX.Column referenceColumn, ref ADOX.Catalog catDest, ref ADOX.Column targetColumn, string[] listOfSupportedProperties, string actionMsgString) { Array listOfSupportedPropertiesArray = (Array)listOfSupportedProperties; System.Collections.ArrayList listOfSupportedPropertiesArrayList = new System.Collections.ArrayList (listOfSupportedPropertiesArray); System.Collections.IEnumerator propertyEnumerator = null; propertyEnumerator = referenceColumn.Properties.GetEnumerator(); propertyEnumerator.Reset(); string propertyName = ""; while (propertyEnumerator.MoveNext()==true) { try { for(int i=0;adoConnDest.Errors.Count>0;i++) { string errorStr=adoConnDest.Errors[i].Description; } propertyName = ((ADOX.Property)propertyEnumerator.Current).Name; if(listOfSupportedPropertiesArrayList.Contains(propertyName)) // check the property name with the allowed properties { try { Object oldObj = targetColumn.Properties[propertyName].Value; Object newObj = referenceColumn.Properties[propertyName].Value; if(newObj!=null) { if( newObj.Equals(oldObj)==false ) // check if an update is neccessary { if (newObj.ToString().StartsWith("\"")) { newObj=newObj.ToString().Replace("\"","'"); } targetColumn.Properties[propertyName].Value = newObj; Object changedObj = targetColumn.Properties[propertyName].Value; // for debugging if(oldObj!=null) Common.MessageLogger.getInstance().WriteLogMessageToFile("\tColumn property '" + propertyName + "' ("+oldObj.ToString()+"<>"+newObj.ToString()+") "+actionMsgString+"."); else Common.MessageLogger.getInstance().WriteLogMessageToFile("\tColumn property '" + propertyName + "' (null<>"+newObj.ToString()+") "+actionMsgString+"."); } } } catch(Exception error) { Common.MessageLogger.getInstance().WriteLogMessageToFile("Exception catched during updating property '" +propertyName+"':"+ error.Message); } } for(int i=0;adoConnDest.Errors.Count>0;i++) { string errorStr=adoConnDest.Errors[i].Description; } } catch(Exception error) { Common.MessageLogger.getInstance().WriteLogMessageToFile("Exception catched during updating property '" +propertyName+"':"+ error.Message); } Control.GenericEpxUpdateTool.getInstance().GetMainForm().IncreaseProgressBar(); } } Tag: Transaction limitations Tag: 129559
  - 4
    - Migration from ADO to ADO.Net with SqlClient I am trying to get to terms with the use of updating through a data adaptor in VS 2005. The application allows the user to create forms and add fields and tables to the database and so the code has to be generic. In ADO, one can read the record,change the value and simply update: RecordSet.Fields(FieldName)=SomeValue RecordSet.Update I can update a DataSet in a similar way, but how do I get the chages back to the database? The data adaptor needs an UpdateCommand, but I cant find any information about what this should be like. Any help would be welcome. -- John Austin Tag: Transaction limitations Tag: 129558
  - 5
    - Oracle- and ODBC Provider missing in Visual Studio Express 2005 Hello, I have Visual C# Express 2005 and Oracle 10g installed. After some problems with different versions of Oracle including a restoring of a system restore-point I now have the following problem: When I try to add a data source to my project I can only select Access-File via OLEDB or Local MSSQL Server file via .NET provider. As far as I know there should also be an Oracle and an ODBC Provider available, but they are missing. I tried to install everything new including the NET-2.0-framework, but still the same problem. Somewhere I read, that in the machine.config file the available providers are listed, but there are all 4 present. Does anyone know what to do? Thanks in advance Sascha Tag: Transaction limitations Tag: 129548
  - 6
    - SQL Migration - Varchar 8000 in SQL 2005 Hello, We have migrated a sql server 2000 database to sql server 2005, this was done through scripts (table, stored procedures and data). To access this database we are using an ASP.Net 2.0 application which uses the sqlhelper Aplication Block to connect to the database. Everything works fine except one Stored Procedure which has an OUT varchar(8000) parameter. We use the following .Net Code to execute the stored procedure this stored procedure: aParams[2] = sSerDatos.GetParameter("@DominiosMenu", DbType.String, 8000); aParams[2].Direction = ParameterDirection.Output; sSerDatos.ExecuteNonQuery("VM_SDominiosMenu", aParams) When we invoque the sqlcommand we get this sqlexception: The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 3 ("@DominiosMenu"): Data type 0xE7 has an invalid data length or metadata length. If we change the DbType.String Size to 4000 in the .Net code everything works, this same procedure works correctly in SQL Server 2000 with the same .Net code. Any help would be appreciated. Thanks, Sam Tag: Transaction limitations Tag: 129542
  - 7
    - How to change encoding for XML Column I am updating an xml column in SQLServer 2005, but it always gives me an error "XML parsing: line 1, character 38, unable to switch the encoding" my csharp is like the following cmd.Parameters.Add("@xml", SqlDbType.Xml); cmd.Parameters["@xml"].Value =xmldata; My xml have the following line "<?xml version="1.0" encoding="UTF-8"?>..." If I remove this line, everything is OK, looks like the SQLParameter is set to UTF-16 and my xml data is set to UTF-8, how do I make SQLParameter take UTF-8? I did not see any properties under SQLParameter to change the encode. Please advice. Thanks in advance. John Tag: Transaction limitations Tag: 129540
  - 8
    - Connection Pool Time Out Hi all, I have the following scenario: App1: Asp.Net Application that shows reports from some SQL DB App2: Asp.Net WebService with two services exposed for clients, and a single â??Checkerâ?? in the Application object that checks every some time (normally 2 or 3 minutes) for conditions in the same DB, and insert some values in it using a transaction. When the time spans completes, the checker retrieves via a SqlDataReader the ready items and make the insertions as shown: SqlDataReader reader = GetReadies(); try { // do something // initialize the transaction â??transâ?? and the connection â??conâ?? try { // insert using the transaction trans.Commit(); } catch(Exception ex) { trans.Rollback(); throw ex; } finally { trans.Dispose(); con.Close(); } } finally { reader.Close(); } In the web application I have few pages that get information from the DB, and after a few retrievals, I got this 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 reached.â?? So I review all code searching for unclosed datareaders or connections, for nothing. I use SQLHelper in the web application and I always retrieve datasets, anyway, I checked that all connections were closed. Then I review the code in the webservice app, I think it is correct, isnâ??t it? Even though I didnâ??t found unclosed connections, I started to watch the .Net CLR Data performance object with the object Current # pooled connections. The graph never goes over 7 connections; it oscillates between 5 and 8. I also started a new trace in the SQLProfile to see if it audits logout events occurs. And still, after a while browsing in the asp.net application, I receive the same error. Note: the max pool size is set to 100 explicitly. Any idea of the cause? Thank you very much. Tag: Transaction limitations Tag: 129539
  - 9
    - txn isolation level Hi, Should the transaction isolation level persist across the reuse of a connection from the connection pool? From my experiment it does persist and I was not expecting it. I was under the impression that "sp_reset_connection" will tear down all the connection level settings and go back to the defaults. I am using the sql .net provder with the 1.1 framework and connecting to a sql 200 server. Just wondering if this is a bug or by design ... -- Vikram Vamshi Database Engineer Eclipsys Corporation Tag: Transaction limitations Tag: 129538
  - 10
    - VB.NET and SQL Server 2005 - Data locked when they should be. LOOK Hi, I have 2 ways of selecting data from my table "Membre". 1- By permit number (memNoPermis) 2- By lastname, firstname (memNom AND memPrenom) SAMPLE DATA: memNoPermis: 1 memNom: Walter memPrenom: Gregory memNoPermis: 2 memNom: Wick memPrenom: Timothee memNoPermis: 3 memNom: Williams memPrenom: Simon He is my weird scenario. FIRST- I run this query. SELECT M.memNoPermis, M.memNom, M.memPrenom FROM Membre M WITH (UPDLOCK, NOWAIT) WHERE M.memNoPermis = 2 It reads & locks the record ! FINE. ----- After, if I run this query : SELECT M.memNoPermis, M.memNom, M.memPrenom FROM Membre M WITH (UPDLOCK, NOWAIT) WHERE M.memNom = 'Williams' AND M.memPrenom = 'Simon' It tells me its lock ... but that is false. Only record id 2 should be locked. The query above should return unlocked record id 3. BUT If I run the following query instead.. which ask for the same record. SELECT M.memNoPermis, M.memNom, M.memPrenom FROM Membre M WITH (UPDLOCK, NOWAIT) WHERE M.memNoPermis = 3 In this case the record can be selected as if it wasn't locked (which is the case). Can anyone help me on this? memNoPermis is my table PrimaryKey field. Could my problem be related to that? I made many tests. If I only read records with "memNoPermis" in my WHERE clause, everything works perfectly. BUT. More explanation: If I read record id 3 with memNom+memPrenom and no row it locked ! GREAT. READS & LOCKS. While record id 3 is locked: If I read record id 2 (which is before 3 in the table) with memNom+memPrenom ... GREAT. READS & LOCKS. BUT Instead if I read record id 4 (which is after 3 in the table) .. it acts as if I was locked already...which is wrong. Pretty strange. When reading with (memNom+memPrenom) in my WHERE clause, If the record I need is after a locked record in my table, it acts as if it was locked already. When reading with (memNoPermis <-- PK field) in my WHERE clause, In all cases it always works great !! Thanks for helping me resolving this problem. I tried putting memNoPermis+memNom+memPrenom in a UniqueKey. But it didn't help. Tag: Transaction limitations Tag: 129537
  - 11
    - DBTransaction question Hi, I have some code like this : DBTransaction = DBConnection.BeginTransaction() Assuming I don't book keep the return from this call , How do I 1) determine if there is a DBTransaction pending on this DBConnection and 2 ) if there is , what is the DBTransaction . thanks -chaz Tag: Transaction limitations Tag: 129536
  - 12
    - How To Use Keyword DataDirectory For Database In Same Folder As Executable Hello, With a fairly complete and detailed example, how do I use the DataDirectory keyword? What I ultimately want to do is place my database in the same folder as the executable and have my application find it there. Thank you, Christopher Lusardi Tag: Transaction limitations Tag: 129535
  - 13
    - ExecuteNonQuery with error checking (or continue on error equiv) First off I'll explain why I need something similar to ContinueUpdateOnError feature that a data adapter has, and why I can't just use a data adapter. Basically I'm updating a Large amount of data using BCP to copy the data to a temp table in order to quickly move the mass amount of data from a flat file to my database. I Then run updates on the temp table I just loaded that insert AND update the data from the temp table to the production table. The reason i do this is because my data (unfortunately) contains both inserts and updates. The most efficient way I've found to do this involves using BCP to move the data, then running SQL Statements I have generated to process the updates and inserts from the temp table to the production table in the database. I'm using something 'similar' to the BCP code from the PDC 05 Demo's ( http://blogs.msdn.com/dataaccess/archive/2006/01/09/510083.aspx ) Well it appears that when using try/catch if I have errors that yes it will catch the error, however I have no control over it. The entire Update command will then hault not updating any records. Is there some way to have the ability to continue on with my SQL statement if it happens to hit a record that doesn't have a corresponding foreign key or something similar? Thanks -- Matt www.Fiddelke.org Tag: Transaction limitations Tag: 129528
  - 14
    - Tabeladapter with row_number() over... Hi Friends, I have created a tableadapter (using wizard of VS2005) with some SQL query statement. But I want to write SQL query such a way that it returns table with one more fields, i.e. Row number. I have tried this SQL query in SQL server 2005: select ordernumber, row_number() over( order by ordernumber) sno from orderhead It's working in SQL server 2005 but not in table adapter (It gives SQL syntax error in 'Add Query' wizard). Please help me to add such a column in SQL. And why should tableadapter give any error? This query is working with dataadapter!! I mean, this code is working Dim ds As New DataSet Dim da As New SqlClient.SqlDataAdapter("select ordernumber, row_number() over( order by ordernumber) sno from orderhead" _ , conStr) da.Fill(ds, "TAB1") Thanks in Advanced, -Yogee Tag: Transaction limitations Tag: 129520
  - 15
    - Binding to object datasource I have created a project that contains a typed dataset. I have extended this dataset through partial classes an daab 2.0. I have created a datasource out of this project through the datasources wizard in a new winforms project , I have a dropped a binding source component on the form . When I try to bind the bindingsource to the object datasouce , I get an invalid property error. If i try and expand the node containing this object datasource in the data sources window , the i.d.e shuts down with out warning. Using vs 2005 proefessional vb.net Any help whatsoever would be greatly appreciated Tag: Transaction limitations Tag: 129519
  - 16
    - OleDbCommand.Parameters.Add and 'IN' Comparator and MSAccess I am using Visual Studio with a MS Access Database I just can not work out how to create my parameter for the following Select Fld1, Fld2 from myTable where Fld1 in (?) The value I wish to add to the parameter collection for Fld1 is '2, 3, 17, 9, 235' to add a single Parameter I would do the following cmd.parameters.add("",oledbtype.integer).value = 1 There does not seem to be an OleDbType for an integer array But I don't have a single value and I do not know how many values, there will be in the 'IN' query I also have other parameters in the Selection (not shown) and as OleDbParameters, seem to be positional, with no way of changing that, I can't just enter many '?' into the 'IN' part and only enter as many as I need. In any case what would happen if I had multiple 'IN' comparators. Any ideas or suggestions, welcomed Tag: Transaction limitations Tag: 129514
  - 17
    - About Load-on-Demand ADO.NET skill. I want to improve my C# ADO.NET skills. I want to study how to handle Load-On-Demand and Paging Skills in C#. Is there any information to study? Tag: Transaction limitations Tag: 129511
  - 18
    - Two DataViews of the Same database Table but different columns Hi, I'm new to ADO.Net and I want to DataViews containing different columns of the same database table. Is it possible without creating 2 dataadpaters? Thank you, Scott Tag: Transaction limitations Tag: 129509
  - 19
    - sql server 2005 crashes by sqlbulkcopy when the column size does not match dear all, i am having this strange problem. What i have done is i load a datatable to the sql server 2005 using the sqlbulkcopy. when the column size of the destination table is less than the value of the mapped column of the datatable instead of throwing exception the sql server 2005 instead crashes. the only exception that gets caught is the transport level error( TCP Provider, error: 0 .......) what is happening ???? i am so confused. here is the part of the code: ......... SqlConnection oSQLConn = new SqlConnection(); oSQLConn.ConnectionString = Config.GetConnectionString(); oSQLConn.Open(); try{ System.Data.SqlClient.SqlBulkCopy bc= new System.Data.SqlClient.SqlBulkCopy(oSQLConn, SqlBulkCopyOptions.TableLock, null); .......................... ............................// other part of the code the initializes the // DataTable with name dataTable bc.BatchSize = 500; bc.DestinationTableName = "desttable"; bc.WriteToServer(dataTable); bc.close(); } catch(Exception exe){} ..... if anyone knows what is wrong.. or what is going wrong then it would be a great help to me. this is causing a lot of headache.... regards, kamal Tag: Transaction limitations Tag: 129504
  - 20
    - How to udpate and alter procedure at once. I want to use SqlCommand.ExecuteNonQuery to execute a Sql which is read from a text file. I want to sql to update a record in myTable, and alter a procedure. The text file is like: -------------------------------------------------------------------------------- UPDATE myTable set Col1='text' ALTER procedure [dbo].[EmptyData] as Delete from Acc; Delete from BodyMind; Delete from Checks; Delete from CheckTemp; -------------------------------------------------------------------------------- when I execute the ExecuteNonQuery, it result into an error: The Alter command must in the first line. But if I put the Update command at last line, it become a part of the ALTER procedure. How can I udpate and alter procedure at once. Tag: Transaction limitations Tag: 129491
  - 21
    - Loading data into an Access Table from CSV or XML I have lots of old data files, which I am adding to an Access Database. I read somewhere, that you can select directly on a CSV file or XML by changing the Connection String - Extended Property - or I could have been dreaming. So to save a lot of coding and before I break everything, is it possible to do something like the following COPY CSV into an Access Table INSERT INTO TABLE1 (Fld1, fld2, fld3, fld4) SELECT fld1, fld2, fld3, fld4 from mycsv.csv COPY XML into an Access Table INSERT INTO TABLE1 (Fld1, fld2, fld3, fld4) SELECT fld1, fld2, fld3, fld4 from myxml.xml Using OleDbCommand.executeNonQuery If it is possible, some sample code would be very helpful Thanks for any help - Richard Tag: Transaction limitations Tag: 129490
  - 22
    - create *.cub file from DataSet Hi, The environment is .NET 1.1, VS2003. (can also be .NET 2.0 +VS2005) Suppose I have downloaded a DataSet from the server to the client machine. Now I want to create a *.cub file from this DataSet and save it to disk. I tried using ADOMD.NET but dodn't know how to connect to a DataSet. Is it at all doable? Thanks GM P.S. I have already asked this question on sqlserver.olap group, but still didn't get any response. Tag: Transaction limitations Tag: 129489
  - 23
    - HELP!!! Error installing SQL Express 2005.... I have VS 2005 Pro installed, but I tried to install SQL Express (as I did not know if the "developer" edition that installs with 2005 Pro was the same as my customers would be using). I have got to the "SQL Server Database Services" line in the setup progress screen, and I get "SQL Server Setup has encountered the following problem: [Microsoft][SQL Native Client]Communication link failure. To continue, correct the problem, and then run SQL Server Setup again." I'd be happy to "Correct the problem..." if the error msg actually told me what it was or how to fix it. When I copied the text I also got this link (which basically says "we don;t know")... "For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=setup.rll&EvtID=60009&EvtType=lib%5codbc_statement.h%40sqlStatement%40OdbcStatement%3a%3aexecute_batch%40HotfixDBEINST%4013%40xea69 " I am leaving everything right where it is (and, yes, I backed up my system before embarking upon this little adventure). Anybody seen this one? Tag: Transaction limitations Tag: 129488
  - 24
    - Send Recieve data to insert into tables using Socket Hi, I have to develop a Client Server application. What I want is that the server will listen on a specific port and client will connect to the server and then send a request to retrieve/insert some data from/into the database. Database is placed on server side. What is the best way to send and retrive a large amount of data from a database over the sockets? Thanks in anticipation. Regards, Ahmad Jalil Qarshi Tag: Transaction limitations Tag: 129487
  - 25
    - how to set the value of the parameter for the SelectCommand? Hi, The gridview linked to this datasource shows nothing. The problem is the value of the variable 'a' which is not passed, i'm afraid ... The code-behind contains the value to be passed: public a As String Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load a = "phil" End Sub the aspx contains: <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="Provider=....." ProviderName="System.Data.OleDb" SelectCommand="SELECT [name],[city] FROM [mytable] where [name]= @name"> <SelectParameters> <asp:Parameter Name="name" DefaultValue=a /> </SelectParameters> </asp:SqlDataSource> Thanks Phil Tag: Transaction limitations Tag: 129486

I am updating a table of a few million records with a update file i.e
based on the data in the update file, I am updating my records.

My code throws an error everytime it reaches a particular record. The
record is fine, logic is fine. If I use the same code with a file that
has data for only that record to be updated, it works fine.

I wonder if there is a limit to the number of SQL statements that can
be contained in one transaction or number of operations that a
transaction can hold.

Thanks,

Re: Transaction limitations by sdbillsfan

sdbillsfan
Thu Jun 08 13:34:31 CDT 2006

1.) Which database are you using?
2.) What's the structure of the affected table(s)?
3.) What's the error message you receive?

nas...@hotmail.com wrote:
> I am updating a table of a few million records with a update file i.e
> based on the data in the update file, I am updating my records.
>
> My code throws an error everytime it reaches a particular record. The
> record is fine, logic is fine. If I use the same code with a file that
> has data for only that record to be updated, it works fine.
>
> I wonder if there is a limit to the number of SQL statements that can
> be contained in one transaction or number of operations that a
> transaction can hold.
>
> Thanks,

Re: Transaction limitations by nashak

nashak
Mon Jun 12 14:46:40 CDT 2006

Hello,

DB - SQL Server 2000

An update, the error thrown is not at a particular record. It can
happen anytime. Error messages are not consistent. I think it has to do
something with number of steps in a transaction etc.

sdbillsfan@gmail.com wrote:
> 1.) Which database are you using?
> 2.) What's the structure of the affected table(s)?
> 3.) What's the error message you receive?
>
>
> nas...@hotmail.com wrote:
> > I am updating a table of a few million records with a update file i.e
> > based on the data in the update file, I am updating my records.
> >
> > My code throws an error everytime it reaches a particular record. The
> > record is fine, logic is fine. If I use the same code with a file that
> > has data for only that record to be updated, it works fine.
> >
> > I wonder if there is a limit to the number of SQL statements that can
> > be contained in one transaction or number of operations that a
> > transaction can hold.
> >
> > Thanks,