How To Switch Off Transaction Logging For Inserts/updates?
Nov 22, 1999Is there a way to switch off transaction logging for insert and update statements and not only for select into bulk copy?
View 1 Replies
ADVERTISEMENT
How Can I Do A Multiple Insert Or Multiple Updates Or Inserts And Updates To The Same Table..
Oct 30, 2007Hi...
I have data that i am getting through a dbf file. and i am dumping that data to a sql server... and then taking the data from the sql server after scrubing it i put it into the production database.. right my stored procedure handles a single plan only... but now there may be two or more plans together in the same sql server database which i need to scrub and then update that particular plan already exists or inserts if they dont...
this is my sproc...
ALTER PROCEDURE [dbo].[usp_Import_Plan]
@ClientId int,
@UserId int = NULL,
@HistoryId int,
@ShowStatus bit = 0-- Indicates whether status messages should be returned during the import.
AS
SET NOCOUNT ON
DECLARE
@Count int,
@Sproc varchar(50),
@Status varchar(200),
@TotalCount int
SET @Sproc = OBJECT_NAME(@@ProcId)
SET @Status = 'Updating plan information in Plan table.'
UPDATE
Statements..Plan
SET
PlanName = PlanName1,
Description = PlanName2
FROM
Statements..Plan cp
JOIN (
SELECT DISTINCT
PlanId,
PlanName1,
PlanName2
FROM
Census
) c
ON cp.CPlanId = c.PlanId
WHERE
cp.ClientId = @ClientId
AND
(
IsNull(cp.PlanName,'') <> IsNull(c.PlanName1,'')
OR
IsNull(cp.Description,'') <> IsNull(c.PlanName2,'')
)
SET @Count = @@ROWCOUNT
IF @Count > 0
BEGIN
SET @Status = 'Updated ' + Cast(@Count AS varchar(10)) + ' record(s) in ClientPlan.'
END
ELSE
BEGIN
SET @Status = 'No records were updated in Plan.'
END
SET @Status = 'Adding plan information to Plan table.'
INSERT INTO Statements..Plan (
ClientId,
ClientPlanId,
UserId,
PlanName,
Description
)
SELECT DISTINCT
@ClientId,
CPlanId,
@UserId,
PlanName1,
PlanName2
FROM
Census
WHERE
PlanId NOT IN (
SELECT DISTINCT
CPlanId
FROM
Statements..Plan
WHERE
ClientId = @ClientId
AND
ClientPlanId IS NOT NULL
)
SET @Count = @@ROWCOUNT
IF @Count > 0
BEGIN
SET @Status = 'Added ' + Cast(@Count AS varchar(10)) + ' record(s) to Plan.'
END
ELSE
BEGIN
SET @Status = 'No information was added Plan.'
END
SET NOCOUNT OFF
So how do i do multiple inserts and updates using this stored procedure...
Regards
Karen
Updates, Inserts
Jan 22, 2001I have a number of columns with predefined character length but user can input more from gui. i want to trucncate automatically to the desired length and insert or update the database right now it does not allow me to update , or insert the values can i do it and how this is urgent
View 2 Replies View RelatedCascaded Inserts And Updates?
Nov 28, 2007Hi
I have 3 table (tbl1, tbl2, tbl3).
tbl1 colums ---Pid(autonNumber),Pcode(unique values),PGuid
tbl2 columns---Pid, CatId,Fid
tbl3 Colums----Pid,BundleId
Using Trigger(After Insert) in tbl1 to insert the data of tbl1.Pid to tbl2.Pid and tbl3.Pid . tbl1 will be uploaded with new rows and existing rows will be updated from the csv file.
Trigger in tbl1 works fine by inserting data to tbl2.Pid and tbl3.Pid.
My questions are :
1. Is it possible to use trigger to update tbl2 and tbl3 if records exist if not insert new record to colum Pid in tbl2 and tbl3 from Pid of tbl1
2.Or is it possible to use sql procedure to do cascaded updates or inserts by merging datasets from tables and csv file using asp.net
3.Or How can I use single Trigger to insert if not exist and update if data exist.
4.Or Do I have to use two trigger one for insert and one for update. If this is the case how do check for data existance.
After insert Triger I am using at the moment is below (replied as an answer in this forum for my previous thread)
create trigger t1 on tbl1
after insert
as
begin
declare @id int
select @id=pid from inserted
insert into tbl2(pid) values(@id)
insert into tbl3(pid) values(@id)
end
Inserts And Updates Issue Using Sp
Sep 15, 2006Hi, i have the following sp. im using vs.net2005, sqlserver2005.
now how do i implement functions like - > okay using datasets, exception is thrown that the row has been modified and i get to inform the user, reload the modified row, .
how do i get same functionality using sp? is that possible ? how/
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER Procedure [dbo].[sp_Insert_Or_Update_Bill]
@BillID_All uniqueidentifier,
@BillID uniqueidentifier,
@Pono nvarchar(25),
@Date_ smalldatetime,
@SupplierCode nvarchar(25),
@Reference nvarchar(25),
@AmountType nchar(10),
@BillType nvarchar(25),
@TypeCode nchar(10),
@AmountFC decimal(18,2),
@ROE decimal(9,2),
@Currency nchar(5),
@LinkBill uniqueidentifier,
@multiplicityID uniqueidentifier,
@payment_or_bill smallint
as
If Exists(Select * from bill where billid_all= @BillID_All and amounttype = @amounttype)
begin
update bill
SET [BillID_All] = @BillID_All
,[BillID] = @BillID
,[Pono] = @Pono
,[Date_] = @Date_
,[SupplierCode] = @SupplierCode
,[Reference] = @Reference
,[AmountType] = @AmountType
,[BillType] = @BillType
,[TypeCode] = @TypeCode
,[AmountFC] = @AmountFC
,[ROE] = @ROE
,[Currency] = @Currency
,[LinkBill] = @LinkBill
,[multiplicityID] = @multiplicityID
,[payment_or_bill] = @payment_or_bill
where billid_all= @BillID_All and amounttype = @amounttype
end
else
if not @AmountFC = 0
begin
begin
INSERT INTO [Costing].[dbo].[Bill]
([BillID_All]
,[BillID]
,[Pono]
,[Date_]
,[SupplierCode]
,[Reference]
,[AmountType]
,[BillType]
,[TypeCode]
,[AmountFC]
,[ROE]
,[Currency]
,[LinkBill]
,[multiplicityID]
,[payment_or_bill])
VALUES
(@BillID_All
,@BillID
,@Pono
,@Date_
,@SupplierCode
,@Reference
,@AmountType
,@BillType
,@TypeCode
,@AmountFC
,@ROE
,@Currency
,@LinkBill
,@multiplicityID
,@payment_or_bill)
end
end
Encoding Strings For INSERTS And UPDATES - Newbie
May 26, 1999Can someone point me to some code that properly prepares a string for an INSERT or UPDATE into a char or varchar column? I need to be able to handle single & double quotes and any other possible issues. thanks
View 1 Replies View RelatedSQL Server - Checking Inserts / Updates After DTS Package
Oct 30, 2007Hi All,
I'm a relative novice on SQL Server and am a complete beginner at SQL, so am looking for a little help.
I currently use a DTS package to perform inserts / updates to a "production" table.
The DTS package transforms a comma separated file into a "temporary" table that is truncated / cleared before the load starts.
The temporary table has a column denoting Insert or Update. The production table is almost identical, however, doesn't contain the Insert / Update column. The DTS package then, depending upon the Insert / Update flag, either inserts data into the production table or updates data in the production table.
When the DTS package has completed, I'd like to be able to run an SQL Query that validates everything in the "temporary" table is identical to that in the "production" table, which it should be.
I have managed to do some queries to verify that everything has loaded / updated i.e. select primary_key from temporary table where primary_key not in (select * from production table), however, what I haven't been able to do is verify that all the columns on the temporary table match the values in the production table (excluding the Insert / Update flag).
I tried concatenating the columns in each table and comparing the concatenated values, however, this failed due to the different data-types, i.e. decimal, text etc.
Any help will be greatly appreciated.
Many thanks.
Cheers,
David
Inserts And Updates To A Table That Contains A Unique Key Constraint
Nov 5, 2007I am looking for pros and cons for the following scenarios:
When a table contains a unique key constraint is it viable to always do an insert and immediately check the @@ERROR value and if @@ERROR states a duplicate key exception then perform an update statement?
Another possible solution would be to always check if the key exists and then do the insert / update based upon that result. This method will always require two steps.
SQL 2005 Rolls Back Inserts/updates
Apr 2, 2007Hi:
We have a weird problem in our test SQL 2005 SP1 - Windows 2003 Enterprise SP1 - Server (after upgrading from SQL 2000).
While changing data via JBOSS - JDBC connection, we get all our data modifications rolled back.
Selects are fine. Same SQL 2005 Inserts/Updates through JBOSS are fine on other machines with Windows XP (JBOSS is on the same machine there, while in troubled configuration it's on its own server).
But in production imitation environment we can't modify data.
Please advise where to look for the solution.
Thanks.
Logging Inserts From Flat File
Apr 25, 2007Can someone tell me if SQL server logs individual inserts when copying from Flat File to OLE DB Destination? If so, can this be turned off?
ULTIMATELY, I would like to use a Bulk Insert task, but it looks like it is pretty specific to a single DB and a single table in the DB. I would like the Bulk Insert task to be generic enough to where I can specify (via Parameters) the DB and table for the Bulk Insert because this task will be performed in an iteration of many different flat files.
Sorry I never seem to have a "simple" question. My DBA is breathing down my neck here..
Odd: Inserts And Updates Happen Before Running Code In Debugger
Aug 15, 2006Hi,Before stepping into ado.net code to perform an insert or update, the insert / update has already taken place, just on starting the debugger. I use VS 2005 on SQL Server 2000. This did not happen with VS 2003 and SQL Server 2000.Anyone else encountered this?
View 2 Replies View RelatedInserts And Updates Question (newbie)...should Be Simple To Explain
Sep 6, 2005Hello people!Here is the situation:We have an old propriatary database that is used for daily tasks. Not much we can do with it, but we have to use it.I can create basically a read only connection to it through ODBC.I would like to, on a timed interval copy certain data for reporting from this slow thing to my SQL server so that I can learn to program, and create some cool reports etc without having to wait on this server all day.So here is what I don't quite understand.I had originally planned on just deleting the contents of the tbl on my SQL server just before I populated it each time, but found out that my AutoNumber field will continue to increase, and I'm assuming that eventually I'm going to run into a problem as a result.Should I be doing some kind of update instead? if so do I need to first CHECK if the record exisit, if not then do an insert, if so do an update type thing?Or is there a way to basically do it in one command?I hope this makes sense. I would show you some code but there really isn't much to show you other than my insert statement :->Thanks for any advice!Josh
View 5 Replies View RelatedCounting Inserts & Updates From A Specific Or Group Of Tables?
Mar 11, 2002I am kinda new with SQL and am trying to get a count of on the number of updates and or inserts to any given or group of tables and cannot get the syntax correct...can anyone help with this?
Thank you in advance.
Colin P.
Help: How To Detect Inserts, Updates, Deleted On A Table From Within C++ Application?
Jul 20, 2005Hopefully someone can at least point me in the right direction for moreresearch (e.g.: correct terminology). My only previous experience was justdumping data into a database using ODBC, and that was some years ago so nowmostly forgotten.I need to write an NT Service/Application (in C/C++) that will be gettingdata sent to it via SQL Server 2000. The data will arrive in my SQL Server(read-only access), via replication of tables from another remote SQLServer.My application needs know when new row are inserted, or updated so it can toread this data (needs to be quick/timely so hopefully no polling) to theninterface with other remote proprietary systems.T.I.A.PS: If you can recommend appropriate books on SQL Server 2000 that wouldalso be useful.
View 2 Replies View RelatedCounting The Inserts And Updates On A Table In A Sql Server Database
Jul 20, 2005Hello,Can someone point me to getting the total number of inserts and updates on a tableover a period of time?I just want to measure the insert and update activity on the tables.Thanks.- Vish
View 3 Replies View RelatedReplication Performing UPDATES As INSERTS And DELETES On The Subscriber
Mar 1, 2006I have transactional replication set up between two SQL Server 2000 databases. In some cases when I perform an UPDATE on a published table on the the publisher, SQL Server attempts to perform a DELETE followed by an INSERT on the subscriber using the stored procedures created during the initial snapshot.
Why does it do this?
How can I stop it doing this and force an UPDATE on the publisher to call the UPDATE procedure on the subscriber?
Thanks
Slow Inserts/updates As The Database Size Grows
Oct 23, 2007Hi all,
This managed application was written to run on a Symbol 3090 Win CE 5.0 scanning device. We are using the symbol provided classes to access the scanning interface, and SQL Compact database on the device to collect the scanned data, and then using merge replication to synchronize scanned data when the device is docked. The problem we have experienced seems to be releated to the performance when inserting and updating records in the database.
We have tested some randomly generated 1000 records and inserting/updatating into a database. At first the time to commit a record increases when the database is flushing into the memory (The flush interval in the connection string property is 10 seconds by default). and then as the database size grows increasing the time to commit every single record which is causing the application to perform slowly as they scan items into the database. However, the device program memory remains consistant as they are scan items. From our tests, I found the time to execute either a update/insert command on 2MB sqlMobile database (upto 10000 records, depending on the size of the columns) is taking nearly 2 to 2 and half seconds to complete. Below is the only code I am executing,
If Not sqlObj.UpdateItem(1061022, itemNo, 1) Then
sqlObj.InsertResultSet(1061022, itemNo, itemObj.Style, itemObj.Color, itemObj.Size, itemObj.Description, 0, 1)
End If
For the notes, I am using prepared updated command and resultset.insert methods to perform update and insert commands into the database.
Any help on this issue is highly appreciated.
Thanks
Ravi.
Measuring Daily Inserts/updates On A Production Databse
Apr 16, 2007
I want to measure updates/Insertion rate of my databse in order to measure that how percent the databses is booked for insert and update.
can some one can suggest me the mechanism or other resource for doing this work...
thanxs in advance
Temporarily Disable Logging During Large Inserts?
Feb 19, 2007I have an application that dumps massive amounts of data into a database during the installation. My log file always ends up being 30-40GB+ at the end of the install. Can I turn off logging while I do the install and enable it after? What are my options.
View 6 Replies View RelatedAccess To SQL Server 2000 Pauses With Lots Of Inserts, Updates, ...
Nov 21, 2007
Hi,
I have the following problem: Within a VBScript, I use a component (written in C++ I think with use of ADO) for sending "Insert", "Update" Statements to an SQL Server 2000 for inserting, updating data. If I insert 100 - 120 Records in a Loop, all works fine. If I insert 1000 records, approximately 150 records will be inserted very quick, then the program pause fo approx. 8 - 15 minutes and then it proceed for the next 150 recs, pause for 8 - 15 minutes and so on.
If I use a SQL Server 2005 for the database, all works fine. The same happens with another customer and another program written in Visual Basic 6.0 with ADO. The access to SQL 2000 pause and with SQL 2005 all works fine. It seems to me that this is a problem with some buffers, timeout, or so. Has anyone an idea on what screw I can turn?
Thanks
Hans
Does The Configure Data Source Wizard Work For Updates,Inserts, And Deletes ?
Jun 8, 2007Ok, I think this may have a simple answer. Basically I have no problems in setting up QueryString/Control/etc parameters when I use SELECT in the Configure Data Source Wizard as it prompts me for the necessary parameters. But when I try to use the Configure Data Source Wizard with an UPDATE, INSERT or DELETE it does NOT prompt me for the required parameters.Is this a bug or am I just missing something? Do I have to put them in manually or something?Thanks!
View 5 Replies View RelatedWould A TLOG File Size Change When Inserts/updates/deletes Are Not Involved?
Jan 3, 2008I have a 14GB database whose data content is legacy and is described as static. The log file is significantly large and continues to change size mostly increasing by 2-5GB a day (~60GB now) I have observed over the past two days; it shrank once unexpectly by a few GB. The instance is hosting other databases such as: EnterpriseVaultDirectory, EnterpriseVaultMonitoring, EnterpriseVaultStore, and NetPerfMon - might these seemingly unrelated data sources be involved?
I am trying to a trace to find traffic against the tables, no such luck.
Web applications are playing against it for queries but there should be no UPDATEs beign applied. I can only suspect that other unknown applications are performing operations but have yet to find unexplained connections.
Are there any other reasons why this type of log file activity would happen merely due to queries or stored procedure calls?
Lets also state, "mirroring, indexing, replication" are not at play. I know logging "Full" is not necessary as "Simple" should suffice but I am still hunting down why UPDATEs might be getting through. I realize I might adjust the migrated SQL 2000 security model to deny updates to find what breaks but would rather not take that iniative yet.
The installation is a fresh SQL 2005 Standard setup with SP2 applied; the databases were upgraded.
Logging Batch Updates Without Using A Trigger Or A Cursor
May 5, 2008Hi,
Does anyone know if there's a way to log batch updates done using SQL queries without using a trigger or a cursor?
Thanks in advance,
Vinod
SQL Server 2014 :: Stored Procedure That Inserts And Updates A Table With Excel Data?
May 27, 2014I need a script that inserts the data of an excel sheet into a table. If something already exists it should leave it, unless it's edited in the excel sheet and so on and so on. This proces has to go through a stored procedure... ...But how?
View 6 Replies View RelatedMerge Replication Performance Issues, Enumerating Inserts And Updates For Articles...generations And Batches
Oct 8, 2007
We have a SQLServer 2005 Enterprise merge replication publication with SQL Mobile 3.0 subscribers (Windows Mobile 5.0 and 6.0). We do not use pre-computed partitions due to trigger performance issues with an SSIS/ETL application that supplies data to the merge database. We do use the "Optimize" (=true) option, though we have tried this both ways with no significant differences. We use filters and joins for each worker ID (as HOST_ID) from the subscriptions.
The sync times become increasingly worse after we run the snapshot and bring the publication online. I have tried rerunning the snapshots, this helps little, as it often behaves like the subscription was set to reinitialize and forces a big sync (reload of all data) to the subscriber. We have tried much of the obvious (e.g., flattening filters and joins, adding indexes, etc.).
When users are synchronizing, we watch replication monitor and notice that a lot of time is spent processing "enumerating inserts and updates for article [any article]", especially processing the many generations and batches. This is true for any follow-up syncs after the 1st big sync (initializing the subscription).
I read several posts regarding the batches and generations of changes, and decided to try increasing the €œDownloadGenerationsPerBatch€?. I tried adding this parameter to the snapshot agent job, and the job fails each time with a vague message, even with the default value of 100. How do you change this parameter for SQLServer 2005 Enterprise?
Any suggestions?
Thanks in advance,
Matt
T-SQL With Inserts In Various Table In One Transaction
Mar 29, 2008Dear All,
I developed an application years ago where I have to insert records in multiple table from asp.net page during registration process with one button click. I have seperate Insert statements and then i have to select Identity column value and insert into another table as a foriegn key and so on.
I want to run all these SQL insert in one statement or Stored procedure. becuase currently, if one of insert statemetn fails, I have no way to roll back previous inserts and start over again.
Any advice or sample T-SQL Where I can insert in Table A, and then read the identity from Table A and insert in Table B, in one single Transaction, so if it fails at end of Transaction, i want to roll back the all the inserts and updates in table(s).
Thanks,
Can Logging Be Turned Off On Inserts To A Specific Temp Table From A Specific Sp?
Oct 10, 2007I want to ship 500,000 aged transactions each night to an archive table and delete them from their source table in one or more logical units of work (LUW). Each row is approx 60 bytes and there is only one non clustered index on the source table presently.
I'm trying to weigh the pros and cons of 3 alternatives. One of them would basically insert the non-aged rows into tempdb, ship the aged records, truncate the table and then insert the tempdb records back into their source all in the same LUW.
For this alternative, I'd at least like to turn off logging when the records get inserted into tempdb as I dont see any value in logging that part of the activity. Is this possible?
ROLLBACK TRANSACTION - MULTIPLE INSERTS
May 28, 2008Hello,
I have a stored procedure that updates my table with values entered in a datatable in my windows app.
An error occurs 1/2 way through the update process. I assumed that by implementing the rollback transaction command that the inserted lines would not be saved to my db. This is not the case.
I will elaborate a little more on what I need. From my windows app I have a datatable with 100 new rows that need to be written to my db. This I can do. However, a problem crops up should there be an error during the transfer. Let's say I have 100 rows in my datatable in my windows app, and row 57 causes an error, what is happening is that rows 1-56 are committed and 57-100 are not. What I need is for 1-100 to NOT be committed to my DB should there be a snag along the way. I need a code sample as I'm having way too much difficulty with this as is.
The basic code that copies to my db(sans rollback):
BEGIN
SET NOCOUNT ON
INSERT INTO userprofile (uid, uname, ustatus)
VALUES @userid, @username, @userstatus;
END
Thank You.
Transaction Logging
Oct 20, 2005Lets say I have version 1 of a database - DB1. I am creating the second database, DB2.
What I need is a log of all the SQL statements that where used to
change DB1 into DB2. This means recording both what happened in the GUI
and in the SQL Query Analyser.
Is there a way I can do this? I know SQL Server has a transaction log
somewhere. Is there a way to set this to output all the changes made
from a set date on a database into a SQL log file?
Thanks in advance for any help.
Jagdip
DTS And Transaction Logging
Jan 29, 2001If you use the import feature of DTS to import data into various tables and your database is setup with truncate log on checkpoint set to false, will SQL Server log those transactions?
Thanks,
Veronica
Mulitple Updates On Table In Same Transaction
Oct 31, 2007 I need to update information for a user and if the user is classified
as a primary (@blnPrimary) then I need to update information for all
users within his agency (AgencyUniqueId). The issue is that the second
UPDATE to "cdds_User_Profile" always returns a rowcount of 0 (should be
1) even though the values for "@Original_AgencyUniqueId" and
"@Original_UserId" are correct. This is just a snippet of the whole
procedure. I'm trying to implement similar logic in other parts of the
procedure and I'm observing the same behavior there as well. Any help
anyone can provide is greatly appreciated. </p><pre>/*** Update User Profile ***/UPDATE [cdds_User_Profile] SET [FirstName] = @FirstName, [LastName] = @LastName, [Title] = @Title, [Phone] = @Phone, [AcctType] = @AcctType, [AcctStatus] = @AcctStatus, [LastUpdatedDate] = GETDATE() WHERE ([FirstName] = @Original_FirstName AND [LastName] = @Original_LastName AND [Title]=@Original_Title AND [Phone]=@Original_Phone AND [AcctType]=@Original_AcctType AND [AcctStatus]= @Original_AcctStatus AND [AgencyUniqueId] = @Original_AgencyUniqueIdAND [UserId] = @Original_UserId);IF @@ROWCOUNT = 0BEGINSET @err_message = 'Data has been edited by another user since you began viewing this information.'RAISERROR (@err_message,11, 1)ROLLBACK TRANSACTIONRETURNEND IF @@ERROR <> 0 BEGINROLLBACK TRANSACTIONRETURN ENDIF @blnPrimary = 1 BEGIN IF LOWER(@AcctStatus) <> LOWER(@AgencyAcctStatus)/*** Update Users Acct. Status ***//* update all users in same agency profile */UPDATE [cdds_User_Profile] SET [AcctStatus] = @AcctStatus,[LastUpdatedDate] = GETDATE() WHERE ([AgencyUniqueId] = @Original_AgencyUniqueIdAND [UserId] = @Original_UserId); IF @@ROWCOUNT = 0BEGINSET @err_message = 'Data for this agency has been edited by another user since you began viewing this information.'RAISERROR (@err_message,11, 1)ROLLBACK TRANSACTIONRETURNENDIF @@ERROR <> 0 BEGINROLLBACK TRANSACTIONRETURN ENDEND</pre><pre>
Retrieving Updates Made Within A Transaction?
Jul 20, 2005Hi All,This seems like a tricky question to me. I have a Stored Procedurethat encapsulates a number of updates to various tables within atransaction.However, at a later part of the transaction I need to be able toselect records changed by an update statement made earlier within thesame stored proc (and within the same transaction) and need for thatselect to reflect the changed values.My understanding, however, is that the records aren't actually changedby the update statement until the transaction is committed, andtherefore my later select statement won't return the expected recordsbecause the update is being held until the transaction is committed.Is this accurate? And, if so, is there a reasonable workaround thatstill leaves me able to rollback the entire transaction if I strike aproblem somewhere along the way?So, a pseudo code example would be:BEGIN TRANSACTIONUPDATE mytable SET myid = @yourid WHERE myid = @idSELECT * FROM mytable where myid = @idCOMMIT TRANSACTIONIn this above example, would the select statement return the recordsthat have a myid value of @id as before the update as after theupdate?Many, many thanks in advance!Much warmth,Murray
View 2 Replies View RelatedMaximum Number Of Updates Within A Transaction
Aug 21, 2006Good day to all
SQL Server 2000.
I have a problem with a piece of code, which updates some tables using transaction. This process brings the program to a halt when updating large files.
With smaller files, the process finishes without problems.
I have noticed that, if a comment out the "begin transaction" and respective "commit", the same code executes without problems, even when updating large files.
I suspect that there is a limit on the number of records a transaction can hold before a commit is issued. I am surprised however, that SQL Server halts, without messages or warnings.
Is this a configuration issue? If there is a limit on the number of records a transaction can hold, what is this limit:? Anything I can do to have a warning form SQL Server when a situation like this is reached (or indeed to avoid this situation) ?
Thank you in advance for any help
Cecil Ricardo