SQL 2005 Rolls Back Inserts/updates
Apr 2, 2007
Hi:
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.
View 2 Replies
ADVERTISEMENT
Apr 18, 2008
Hi,
I have a ms access application with a button. Clicking this button runs a sql server 2000 stored procedure. The stored procedure updates product information. When I click the button it updates the products fine. When the users (with lesser permissions than me) click the button, no error message is sent but the transactions are rolled back. Does anyone know why or how this could happen? Please let me know before I pull out the last strands of hair from my head.
Thank you,
Anand
View 4 Replies
View Related
Oct 30, 2007
Hi...
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
View 5 Replies
View Related
Jan 22, 2001
I 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 Related
Nov 28, 2007
Hi
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
View 5 Replies
View Related
Sep 15, 2006
Hi, 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
View 1 Replies
View Related
Nov 22, 1999
Is there a way to switch off transaction logging for insert and update statements and not only for select into bulk copy?
View 1 Replies
View Related
May 26, 1999
Can 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 Related
Oct 30, 2007
Hi 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
View 8 Replies
View Related
Nov 5, 2007
I 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.
View 4 Replies
View Related
Aug 15, 2006
Hi,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 Related
Sep 6, 2005
Hello 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 Related
Mar 11, 2002
I 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.
View 1 Replies
View Related
Jul 20, 2005
Hopefully 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 Related
Jul 20, 2005
Hello,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 Related
Mar 1, 2006
I 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
View 3 Replies
View Related
Oct 23, 2007
Hi 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.
View 1 Replies
View Related
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
View 4 Replies
View Related
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
View 1 Replies
View Related
Feb 16, 2006
I'm performing a stored proc that has 4 inserts. I only want the inserts to complete as a batch. If one fails, I want to rollback the whole transaction. Does anyone know the syntax?? :)
View 4 Replies
View Related
Mar 3, 2006
I want to insure that each of my insert statements in a stored proc are rolled back if any of the inserts fail. I already have the below statement with error handling but is this correct? It seems to me that all the steps should be made part of an entire transaction so if one part fails then it all fails. Can someone help me w/ the syntax of this??
CREATE PROCEDURE Addrecords AS
--USERS
INSERT INTO [Production].[dbo].[USERS]([LastName], [UserName], [EmailAddress], [Address1], [WorkPhone], [Company], [CompanyWebsite], [pword], [IsAdmin], [IsRestricted],[AdvertiserAccountID])
SELECT dbo.fn_ReplaceTags (convert (varchar (8000),Advertisername)), [AdvertiserEmail], [AdvertiserEmail],[AdvertiserAddress], [AdvertiserPhone], [AdvertiserCompany], [AdvertiserURL], [AccountNumber],'3',0, [AccountNumber]
FROM production
WHERE not exists (select * from users Where users.Username = temp.AdvertiserEmail)
AND validAD=1
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RETURN
END
--PROPERTY
INSERT INTO [Production].[dbo].[Property]([ListDate],[CommunityName],[TowerName],[PhaseName],[Unit], [Address1], [City], [State], [Zip],[IsActive],[AdPrintId])
SELECT [FirstInsertDate],[PropertyBuilding],[PropertyStreetAddress],PropertyCity + ' ' + PropertyState + ' ' + PropertyZipCode as PhaseName,[PropertyUnitNumber],[PropertyStreetAddress],[PropertyCity], [PropertyState], [PropertyZipCode],'0',[AdPrintId]
FROM [Production].[dbo].[Temp]
WHERE AdvertiserEmail IS NOT NULL
AND validAD=1
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RETURN
END
View 2 Replies
View Related
Jun 8, 2007
Ok, 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 Related
Jan 3, 2008
I 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.
View 6 Replies
View Related
May 27, 2014
I 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 Related
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
View 5 Replies
View Related
Jul 10, 2015
I've just imported some SSIS packages onto my instance but my users aren't able to run the jobs for these packages (if thats the write terminology) it keeps failing with this error: Non-SysAdmins have been denied permission to run DTS Execution job steps without a proxy account. The step failed.
I've been looking around and I've been told I can assign the logins to the database roles in msdb but when I look for the roles in msdb they don't exist. SSIS is definitely installed on the server. How do I go about getting these added?
View 2 Replies
View Related
Sep 22, 2006
Does anybody know of a way to rollback SQL Server 2005
databases back to SQL Server 2000? Is there a way of doing it without
resorting to Copy Database Wizard? I love to find a way of attaching a SS 2005 database
to a SS 2000 instance without any issues.
I recently upgraded to SS 2005 and I am very unhappy with the SS 2005 and I
want to rollback to SS 2000, which was a lot more stable. I am having
several major issues that are affecting my whole company's day-to-day
operations and the managers are not happy. Some of the issues include
night time batch running very sluggish for no apparent reason. This is a
biggest problem because it only occurs once or so a week and causes a disturbance
with the daily activities when the night time processing isn€™t completed on
time. The rest of the time, the batch processing runs great, even a little better then on SS 2000. I
don't believe it is a matter of my application needing to be retuned because if
that was the case, then why isn't it running sluggish every night? Also,
it's never the same day that the sluggish behavior occurs. If it was occurring
on the same night, then I would have something to investigate within our
application, but it doesn't. Another issue that I am having involves a
night time job that restores a copy of the production database to the Data
Warehouse server to be used for updating the data warehouse. Again, most
of the time it runs great (~2 1/2 hours), but once or twice a week, it goes
stupid and takes 6 1/2 hours for no apparent reason. Again, it is not happening
the same day either, which could give me something to invesigate. On SS 2000, this same job ran flawlessly. Never I did I run into situation that the
database restoration took that long to run. Even another issue involves a SQL Server Agent Job that was put into suspended
state. What's a suspended state and how can I get it out of suspended
state? I can find no information about suspended state in BOL. I
did a Google and nothing came up. If this suspended state was put
in for security reasons, great, but then tell me how I can remove the suspended
state. I am also not happy with the
fact that I can't get accurate information about the queries that are actively
running at that particular moment. In SS 2000, when I noticed high CPU
usage on the server, I would run the sp_who2 active stored proc and it would
show me all the active thread and how much CPU it was consuming. I would
then find the running threads with the highest CPU numbers and investigate the
query and see if we could improve it. Now in SS 2005, I get in the same
situation and run the sp_who2 stored proc, and there is no smoking gun.
All of the active threads are showing very little CPU usage, which I am very
suspect of. What the heck happen to sp_who2? I looked at some of
the other ways of looking at running processes (i.e... sys.sysprocesses) and
they don't appear to be giving the information that I need.
I am very unhappy and I just want to roll back to SS 2000 and wait a couple of
years before I upgrade to SS 2005.
Dave Brown
View 1 Replies
View Related
Jul 6, 2006
Please help.I have a table with single row. I need to allow only UPDATEs of thetable, forbid INSERTs and DELETEs. How to achieve it?Thank you for information/RAM/
View 3 Replies
View Related
Sep 18, 2007
I have a SQL Server 2005 database where covering indexes had to be used to improve performance for the heavy amounts of retrievals; however, the inserts into the tables are now very slow of course. Is there any way to improve the performance of the inserts without taking away the indexes.
Would changing locking or partitioning the index help the inserts?
Other databases use a concept of "freespace" to set up in the beginning - making pre-existing space for inserts - is there anything like this in SQL Server 2005?
Thanks for any help, Mary
View 4 Replies
View Related
May 21, 2007
I'm using MS SQLEXPRESS 2005. Everyting worked fine: I could connect to it from Access and VS 2005. After updates (SQLEXPRESS SP2) I can't connect to it. Error:
Connection failed:
SQLState: 01000
SQL Server error: 2
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen(Connect))
Connection failed:
SQLState: `08001
SQL Server error: 17
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exists or access denied
It applies to SQL SERVER too.
Pls help.
View 1 Replies
View Related
Aug 22, 2006
hi. changes were made to a dababase and we need to read the log file to see how much exactly was changed and what. how do i read the log file in sql server 2005 with out buying 3rd party software?
View 5 Replies
View Related
Mar 28, 2006
When one process has one record locked in Update-Mode then an other process can't update any other records on that table with some queries, other queries that access the same records (but with a different WHERE statement) will execute.
This problem occurs with SQL Server 2005, but it didn't with SQL Server 2000 (or any other database).
The problem:
-Process 1 locks a record in the table, and keeps it in Update-Mode, because the user is editing it. (Using OLEDB Pessimistic Cursor-locking)
-Process 2 wants to update an other record, buts gets a "Lock timeout" when using one query but not with another.
For example this query will work :
UPDATE gwseqnumber SET nextseqnr = 3 WHERE row_id = 110;
But this qeury will give me a "Lock timeout" :
UPDATE gwseqnumber SET nextseqnr = 3 WHERE name = 'REC_2';
But it is the same record!!
The record with name = 'REC_2' has the row_id = 110, both values are unique in the table.
The data:
The table [gwseqnumber] has the following CREATE statement:
CREATE TABLE GWSEQNUMBER
(
NEXTSEQNR INTEGER,
NAME CHAR (20),
ADMINISTRATIONCODE INTEGER,
FINDHIGHESTNUMBER CHAR (1),
CLOSEDYN CHAR (1),
ROW_ID INT IDENTITY(1,1) NOT NULL
);
CREATE INDEX KEY_1 ON GWSEQNUMBER (NEXTSEQNR);
CREATE UNIQUE KEY_2 ON GWSEQNUMBER (ADMINISTRATIONCODE, NAME, IDENTIFIER);
CREATE UNIQUE INDEX KEY_3 ON GWSEQNUMBER (ROW_ID);
Both KEY_2 and KEY_3 are unique, KEY_1 is not.
If I remove the index on the NEXTSEQNR column (the index named KEY_1) then both these queries will work, so it is obviously related to the index.
Altough the index is obsolete and can be removed from this table, it should not result in bogus locking errors.
Even when i removed the index not all the queries will work. (With work i mean not run into a locking error, while the record is not even locked.)
Solutions tried:
- Set the compaitiblity level of the databsae back to 80 (for SQL Server 2000 compatibility).
- I have already tried to disable Page-Locking on all the indexes of this table.
So why does the index lock my updates in SQL Server 2005?
And how do i fix it so my database does not run into these locks?
View 14 Replies
View Related
Mar 31, 2007
I'm currently running Windows Vista x64 and SQL server 2005 on an HP laptop with a AMD Turion 64 processor. I've install all of the Windows Vista x64 update so far with no issues. However, I'm unable to install SQL server 2005 SP2 via Windows updates.
I was able to successfully download the service pack with no issues and it is currenly on my machine ready to install. The system does take a check prior to the install process beginning to perform the install.
I continuely get and "Error Code A91" when the install process is running. As a result the install does not complete successfully. When I used the "Get Help" option I get a list of possible error conditions but none of them seem be useful. I checked each one and none of them provide any help with a solution.
I've successful installed SP2 on Vista x32 system with no problem. Is this a "Windows Update" issue?
Thanks
View 11 Replies
View Related
