Lock/block Retained After Commit
Jul 20, 2005
I am using SQL server 2000 with Uniface (4GL).
I am running a process which commits every 30 seconds or so on one
machine and another user is trying to perform a small update at the
same time on the one of the tables whose data is modified by the first
process.
I accept that the two second process may get blocked at times by the
first but expect that to only happen rarely due to the likelihood of
both processes trying to update the very same row in a large table.
Other users are also on line at the same time performing updates
across the database.
What I have witnessed though was the second process being blocked by
the first (confirmed by looking in enterprise manager) and REMAINING
BLOCKED EVEN AFTER A COMMIT in the first process!!
My big problem is that I have only seen this happen once on a customer
site and have not been able to reproduce it myself to provide any
further information.
The application is set to use row level locking and is also in read
uncommited mode.
Any help or suggestions would be greatly appreciated.
Bob.
View 3 Replies
ADVERTISEMENT
Dec 2, 2015
We have a reporting job that will query the mainDB and do so come calculation and insert into reportingDB, there are a lot of blocking and performance issue when it query the mainDB so we have plan to read the secondary replica and do the processing there later to insert back into primaryDB (reportingDB).. for this we have implemented linked server but it gets timeout, I do not want to change the remote query timeout on server level so is there anyway to do this, Any one making use of secondary replica to do get the data by job?
View 4 Replies
View Related
Dec 3, 2014
I have the following stored procedure to test scope of variables
alter proc updatePrereq
@pcntr int,
@pmax int
as
begin
[Code] ....
In the above script @i is declare in the if block only when the @pcntr value is 1. Assume the above stored procedure is called 5 times from this script
declare @z int
set @z = 1
declare @max int
set @max = 5
while @z <= @max
begin
exec dbo.updatePrereq @z, @max
set @z = @z + 1
end
go
As i said earlier `@i` variable exists only when `@pcntr` is `1`. Therefore when i call the stored procedure for the second time and so forth the control cannot enter the if block therefore @i variable wouldn't even exist. But the script prints the value in `@i` in each iteration, How comes this is possible should it throw an error saying `@i` variable does not exist when `@pcntr` values is greater than `1`?
View 1 Replies
View Related
Sep 27, 2007
Hi There
We do not use message retention in our application.
However today i dicovered messages still sitting in a queue, on further investigation i found that the messages had been processed, the problem was that i had mistakenly altered a queue with rentention ON earlier.
So the messages on the queue were simply retatined messages.
But i have 2 questions:
1.Is there a way to "delete" these retained messages without ending the conversation, we use permanant conversations so ending the conversation is a last resort.
2.Why is the message status of the retaitned messages 0 ?
BOL : From the topic "Message Retention"
"For queues that specify retention, the RECEIVE command sets the status of received messages to 1 rather than deleting the message."
However my retained messages have a status of 0 ? Also i have confirmed that new not yet received messages have a status of 1.
BOL : From topic "RECEIVE (Transact-SQL) "
" Message Status
0=Ready1=Received message2=Not yet complete3=Retained sent message
"
But from my testing retained = 0 and Ready = 1, is this a mistake in BOL or am i missing something ?
Thanx
View 3 Replies
View Related
Jun 16, 2007
Hi was wondering whether it is possible to log somewhere outside SB that there are messages in the transmission_queue because the Target queue was disabled.
I was testing this scenario:
try to send messages on a disabled queue and log the problem.
But the transmission_status from the trasmission_queue is always empty.
This is the code that I tried to execute between the send and the commit and after the commit:
WHILE (1=1)
BEGIN
BEGIN DIALOG CONVERSATION .....
SEND ON CONVERSATION ......
if select count(*) from sys.transmission_queue <> 0
BEGIN
set @transmission_status = (select transmission_status from sys.transmission_queue where conversation_handle=@dialog_handle);
if @transmission_status = ''
--Successful send - Exit the LOOP
BEGIN
UPDATE Mytable set isReceivedSuccessfully = 1 where ID = @IDMessageXML;
BREAK;
END
ELSE
raiserror(@transmission_status,1,1) with log;
END
ELSE
BEGIN
UPDATE [dbo].[tblDumpMsg] set isReceivedSuccessfully = 1 where ID = @IDMessageXML;
BREAK;
END
END
COMMIT TRANSACTION;
As I wrote before the @transmission_status variable is always empty and I have the same result even if I put the code after the commit transaction!
Maybe what I'm trying to reach has no sense?
With the event notification I can notify when the queue is disable because the receive rollsback 5 times but what if by mistake the target queue is disabled outside the SB environment? I can I catch it and handle it properly?
Thank you!Marina B
View 3 Replies
View Related
Feb 2, 2007
I simply made my script task (or any other task) fail
In my package error handler i have a Exec SQL task - for Stored Proc
SP statement is set in following expression (works fine in design time):
"EXEC [dbo].[us_sp_Insert_STG_FEED_EVENT_LOG] @FEED_ID= " + (DT_WSTR,10) @[User::FEED_ID] + ", @FEED_EVENT_LOG_TYPE_ID = 3, @STARTED_ON = '"+(DT_WSTR,30)@[System::StartTime] +"', @ENDED_ON = NULL, @message = 'Package failed. ErrorCode: "+(DT_WSTR,10)@[System::ErrorCode]+" ErrorMsg: "+@[System::ErrorDescription]+"', @FILES_PROCESSED = '" + @[User::t_ProcessedFiles] + "', @PKG_EXECUTION_ID = '" + @[System::ExecutionInstanceGUID] + "'"
From progress:
Error: The Script returned a failure result.
Task SCR REIL Data failed
OnError - Task SQL Insert Error Msg
Error: A deadlock was detected while trying to lock variable "System::ErrorCode, System::ErrorDescription, System::ExecutionInstanceGUID, System::StartTime, User::FEED_ID, User::t_ProcessedFiles" for read access. A lock could not be acquired after 16 attempts and timed out.
Error: The expression ""EXEC [dbo].[us_sp_Insert_STG_FEED_EVENT_LOG] @FEED_ID= " + (DT_WSTR,10) @[User::FEED_ID] + ", @FEED_EVENT_LOG_TYPE_ID = 3, @STARTED_ON = '"+(DT_WSTR,30)@[System::StartTime] +"', @ENDED_ON = NULL, @message = 'Package failed. ErrorCode: "+(DT_WSTR,10)@[System::ErrorCode]+" ErrorMsg: "+@[System::ErrorDescription]+"', @FILES_PROCESSED = '" + @[User::t_ProcessedFiles] + "', @PKG_EXECUTION_ID = '" + @[System::ExecutionInstanceGUID] + "'"" on property "SqlStatementSource" cannot be evaluated. Modify the expression to be valid.
Warning: The Execution method succeeded, but the number of errors raised (4) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
And how did I get 4 errors? - I only set my script task result to failure
View 11 Replies
View Related
Mar 28, 2008
Microsoft SQL Server Management Studio Express
select @@trancount
begin tran
select @@trancount
use ProdNetPerfMon
select @@trancount
update Nodes set Caption = 'xxxx' where Vendor = 'yyyy'
select @@trancount
commit tran
select @@trancount
Executes as expected including trancount without errors.
Nodes.Caption is updated but reverts after a few minutes.
sa privileges
What am I missing?
View 1 Replies
View Related
Apr 7, 2004
Hi
We are facing an acute situation in our web-application. Technology is ASP.NEt/VB.NET, SQL Server 2000.
Consider a scenario in which User 1 is clicking on a button which calls a SQL stored procedure. This procedure selects Group A of records of Database Page1.
At the same time if User 2 also clicks the same button which calls same SQL stored procedure. This procedure selects Group B of records of Database Page1.
So, its the same Page1 but different sets of records. At this moment, both the calls have shared locked on the Page1 inside the procedure.
Now, in call 1, inside the procedure after selecting Group A of records, the next statement is and update to those records. As soon as update statement executes, SQL Server throws a deadlock exception as follows :
Transaction (Process ID 78) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction
We are able to understand why its happening. Its because, Group A and Group B of records are on the same Page1. But both the users have shared lock on the Page1. So, no one gets the exclusive lock in records for update, even though, the records are different.
How can I resolve this issue? How can I get lock on wanted rows instead of entire page?
Please advice. Thanks a bunch.
Pankaj
View 1 Replies
View Related
May 22, 2006
Hi,
I have set of 2 DTS packages, one of which calls the other by forming a command-line (dtexec) using a Execute Process task.
From the parent package-> Execute Process Task->
dtsexec /F etc... /<pkg variable> = "servername"
Each of the parent and the called package have a variable: "User::DWServerSQLInstance" which is mapped to the SQL server connection manager server name property using an expression. The outer package has the above variable and so does the inner called package (which gets assigned through the command line from the outerpackage call to inner)
I "sometimes" get the following error:
OnError,I4,TESTDOMAdministrator,ACDWAggregation,{A1F8E43F-15F1-4685-8C18-6866AB31E62B},{77B2F3C7-6756-46EB-8C01-D880598FB4B3},5/22/2006 5:10:28 PM,5/22/2006 5:10:28 PM,-1073659822,0x,The variable "User::DWServerSQLInstance" is already on the read list. A variable may only be added once to either the read lock list or the write lock list.
Help would be appreciated!
I have seen other posts on this but, not able to relate the solution to my scenario.
View 9 Replies
View Related
May 10, 2006
Hi All,
I have seen a few other people have this error.
Package works fine when run from BIDS, DTExec, dtexecui. When I schedule it, It get these random errors. (See below)
The main culprit is a variable called "RecordsetFileDIR" which is set using an expression. (@[User::_ROOT] + "RecordSets\")
A number of other variables use this as part of their expression and as they all fail, pretty much everything dies.
I have installed SP1 (Not Beta) on server. Package uses config files to set the value of _ROOT.
The error does not always seem to be with this particular variable though. Always a variable that uses an expression but errors are random. Also, It will run 3 out of 10 times without a problem. I am the only person on the server at the time.
Any ideas?
Cheers,
Crispin
Error log:
OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073659822,0x,The variable "User::RecordsetFileDIR" is already on the read list. A variable may only be added once to either the read lock list or the write lock list.
OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073639420,0x,The expression for variable "rsHeaderFile" failed evaluation. There was an error in the expression.
OnError,,,DF_Header_Header,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636247,0x,Accessing variable "User::rsHeaderFile" failed with error code 0xC00470EA.
OnError,,,Move All Data,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636247,0x,Accessing variable "User::rsHeaderFile" failed with error code 0xC00470EA.
OnError,,,Load Open Batches and Process Files,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636247,0x,Accessing variable "User::rsHeaderFile" failed with error code 0xC00470EA.
OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636247,0x,Accessing variable "User::rsHeaderFile" failed with error code 0xC00470EA.
OnError,,,DF_Header_Header,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636390,0x,The file name is not properly specified. Supply the path and name to the raw file either directly in the FileName property or by specifying a variable in the FileNameVariable property.
OnError,,,Move All Data,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636390,0x,The file name is not properly specified. Supply the path and name to the raw file either directly in the FileName property or by specifying a variable in the FileNameVariable property.
OnError,,,Load Open Batches and Process Files,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636390,0x,The file name is not properly specified. Supply the path and name to the raw file either directly in the FileName property or by specifying a variable in the FileNameVariable property.
OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636390,0x,The file name is not properly specified. Supply the path and name to the raw file either directly in the FileName property or by specifying a variable in the FileNameVariable property.
OnError,,,DF_Header_Header,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073450901,0x,"component "rsHeader" (365)" failed validation and returned validation status "VS_ISBROKEN".
OnError,,,Move All Data,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073450901,0x,"component "rsHeader" (365)" failed validation and returned validation status "VS_ISBROKEN".
OnError,,,Load Open Batches and Process Files,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073450901,0x,"component "rsHeader" (365)" failed validation and returned validation status "VS_ISBROKEN".
OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073450901,0x,"component "rsHeader" (365)" failed validation and returned validation status "VS_ISBROKEN".
View 1 Replies
View Related
May 23, 2001
Hi,
I want to lock a table so others cannot lock it but able to read it inside transactions.
The coding I need is something like this: set implicit_transactions on begin transaction select * from table1 with (tablock, holdlock) update table2 set field1 = 'test' commit transaction commit transaction
I have tried the coding above, it won't prevent others from locking table1.
So, I changed the tablock to tablockx to prevent others from locking table1. But this will also prevent others from reading table1. So, how can I lock table1 so others cannot lock it but still able to read it?
Thank you for any help
View 1 Replies
View Related
Apr 8, 2008
I've written a couple blocks but I have no idea when it comes to this one.
Create a PL/SQL block to retrieve the last name and department ID if each employee from the EMPLOYEES table
for those employees whose EMPLOYEE_ID is less than 114. From the values retreived from the employees table, populate two PL/SQL tables,
one to store the records of the employee last names and the other to store the records of their department IDs.
Using a loop, retreive the employee name information and salary infromation from the PL/SQL tables and doisplay it in the window,
using DBMS_OUTPUT.PUT_LINE. Display these details for the first 15 employees in the PL/SQL tables.
Any help helps
View 1 Replies
View Related
May 18, 2006
If I've got a 64-bit OS such as Windows XP Professional x64 Edition running and I have the SQL Server 2005 Express Edition, is AES permittable or does TRIPLEDES still need to be used?
View 1 Replies
View Related
Sep 14, 2004
Do delete/update statements in MS SQL Server need a "commit" (or equivalent) run after them as they do in Oracle?
View 1 Replies
View Related
Apr 10, 2008
Hello,
I have been experiencing a lot of blocking activity in my database. My trace results show that the following extended stored procedures sp_prepexec and sp_cursorfetch are executed by both the user causing the block and the user tha is blocked. Any ideas as to how to rectify this situation will be appreciated.
Regards,
Albert
View 3 Replies
View Related
Dec 11, 2006
HI,
i'm trying to execute some sql using the Try.. Catch blocks.
Following code does not execute in Catch Block
Begin
begin try
insert into dbo.Test values (1,'aaa')
-- here we are inserting int value in identity field...
END TRY
Begin catch
PRINT 'TEST'
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() as ErrorState,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_LINE() as ErrorLine,
ERROR_MESSAGE() as ErrorMessage;
END Catch
End
GO
Whereas the following block works fine and the Catch block executes.
Begin
begin try
Select 1/0
--This causes an error.
END TRY
Begin catch
PRINT 'TEST'
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() as ErrorState,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_LINE() as ErrorLine,
ERROR_MESSAGE() as ErrorMessage;
END Catch
End
GO
Any idea why is it so?
View 5 Replies
View Related
Mar 5, 2008
Can anyone guide me to write check block in SQL Server?
I Mean if blocking occurs, just inform people.
View 3 Replies
View Related
Mar 18, 2008
Hi there! I need such thing: I have a data in Table A. When I get this data, I also increment this data by one, what I want is block Table A in order to other people will not get access to data.. I think about transactions, but are transactions reaaly blocks a table's content?
P.S. For example in MySQL there are Lock table command for blocking table for reading/writing. I need such thing/
Thanks
View 8 Replies
View Related
Jul 3, 2007
Hi to everybody,
i have this problem.
sometimes a process in my SQL Server 2005 (upd 2) is blocked from itself. If I find in monitor it wait for a lock_M_Sch_M on a temporary table as you can see from
select * from sys.dm_tran_locks where request_session_id=51 and request_status <> 'GRANT'
resorce type : OBJECT
resource id : 218899226
request mode : Sch-M
request status WAIT
request_session id : 51
request life time : 33554432
or from
SELECT * FROM sys.dm_os_waiting_tasks WHERE SESSION_ID=51
waiting task address : 0x0000000000C2F198
session id : 51
wait type : LCK_M_SCH_M
resource address : 0x0000000201C71300
blocking task address : 0x0000000000C2F198
blocking session id = 51
resource description objectlock lockPartition=0 objid=218899226 subresource=FULL dbid=2 id=lock80d04900 mode=Sch-S associatedObjectId=218899226
I resolve this problem only with restart of sql server . that clear tempdb and eliminate this process
I think is not a problem about latch present from sql server 2000 sp4 (I read some document abount this problem)
So I haven't idea how to resolve this problem and how to kill this kind of process without to restart sql server
thanks in advance
Luca
View 4 Replies
View Related
Oct 25, 2007
hi there,
to retrieve more then one record with a cursor we can use block cursor, so we have to set the cursor attribute SQL_ATTR_ROWSET_SIZE to some number...i am not getting any code example on this.
do anyone know this???
View 6 Replies
View Related
Apr 12, 2006
Hi:
one of our study group members noticed a strange behavior and has the following question. Any thoughts are appreciated.
I am unable to understand as to why the CATCH block is not executed when an INSERT is made
On table T3 which is dropped after the first transaction.
The severity of Insert into t3 values (3) is Msg 208, Level 16, State 1, Line 2
Invalid object name 't3'.
BOL says TRY€¦CATCH constructs do not trap the following conditions:
Warnings or informational messages with a severity of 10 or lower.
Errors with severity of 20 or higher that terminate the SQL Server Database Engine task processing for the session. If an error occurs with severity of 20 or higher and the database connection is not disrupted, TRY€¦CATCH will handle the error.
Here is the script.
use tempdb
go
create table t1 (a int)
create table t2 (b int)
create table t3 (c int)
Begin tran
Insert into t1 values (1)
Insert into t2 values (2)
Insert into t3 values (3)
IF @@error <> 0
Rollback tran
else
commit tran
-------------------------------------------------------------
Select * from t1
Select * from t2
Select * from t3
-------------------------------------------------------------
Drop table t3
-------------------------------------------------------------
Set xact_abort on
Begin try
Begin tran insertNow
Insert into t1 values (1)
Insert into t2 values (2)
save tran insertNow
Insert into t3 values (3)
commit tran insertNow
End try
Begin Catch
IF (XACT_STATE()) = -1
BEGIN
PRINT 'The transaction is in an uncommittable state.' +
' Rolling back transaction.'
ROLLBACK TRANSACTION insertNow
END;
-- Test if the transaction is active and valid.
IF (XACT_STATE()) = 1
BEGIN
PRINT 'The transaction is committable.' +
' Committing transaction.'
COMMIT TRANSACTION insertNow
END
End Catch
View 9 Replies
View Related
Jun 8, 1999
Hi ,
I've tried to switch MS-Sql/Server 6.5 on explicit_transactions
without success.
(set implicit_transaction on/off)
What is the exact syntax for doing that ?
Herve
(PS: Thanks Gregory for your quick answer )
View 1 Replies
View Related
Jun 3, 1999
Hi All,
I don't know MS-SQLserver internal system at all. I 've just used Oracle
a couple years ago and so in some cases (e.g using TP-monitor MTS or Tuxedo)
you can switch off the implicit transaction by using
the option AUTOCOMMIT ON/OFF.
How can switch off the implicit transaction system on MS-SQLServer ?
In advance thanks,
Herve
View 1 Replies
View Related
Aug 21, 2002
l also use the
begin transaction
select ........etc
commit
structure when l wrtite queries.My problem is that if l close the query analyser it asks me to commit transaction before l exit. Why?
How do you check for uncommitted trans and commit them?
View 1 Replies
View Related
Aug 17, 2005
I built this in SQL query analyzer to update all records with the 1/1/02 date:
update tbl.EMPPOS set EFFECT_DATE = '2005-01-01'
where EFFECT_DATE = '2002-01-01';
when I run the query it updates and shows records in the lower window, but the tbl isn't altered. What is wrong with my syntax ??
thanks,
View 1 Replies
View Related
Apr 14, 2008
hi friends,
Iam Executing the sp logic.suppose incase if any problem occurs inbetween execution(NO SPACE,communication failure,log full)
data is getting commited partially insteady of rollbacking entire transaction.
CREATE procedure RBI_Control_sp
as
begin
set nocount on
--Checking the count before truncating
exec fin_ods..count_sp
--Truncating the Table
exec fin_ods..trun_sp
--Data Transfer
exec fin_ods..RBI_Data_Transfer_sp
--Checking the count after Data transfer
exec fin_ods..count_sp
--temp table Table population,Fetching data from the fin_ods[erp Table]
exec FIN_wh..RBI_SPExecution_sp
set nocount off
end
View 1 Replies
View Related
Dec 15, 2007
I have a cursor loop through a set of records that looks something like this.
OPEN database_cursor
FETCH NEXT FROM database_cursor
INTO @iID
WHILE @@FETCH_STATUS = 0
BEGIN
update table 1
update table
......
FETCH NEXT FROM database_cursor
INTO @iID
END
CLOSE database_cursor
DEALLOCATE database_cursor
Is there a way i could put all the UPDATE statements within a transaction. either everything goes or nothing.THnaks
Thanks,
View 3 Replies
View Related
Jul 20, 2005
I've a complex stored procedure, that makes a lot of insert, update,delete and so on.I would like to make some commits durint this sp, but of course theyare not "real" commit because who call the sp could decide for arollback.But I know that this commit has to be real. In fact, the transactionlog grows really too much during the execution.Is there a way to force a commit durint a sp ?thank you very much!
View 3 Replies
View Related
Oct 9, 2007
I have an SSIS package that iterates through a thousand or so download text files, parses them and inserts the results into a database via a Stored procedure and an OLE DB Command.
For the most part this process works without any issues, yet I keep obtaining random errors on a DT_STR (500) column. I have reviewed the data extensively and this column - which is the same across all of the rows - does not appear to be any different.
The rest of the rows before and after the error rows all insert properly but these rows consistently fail in the OLE DB task with the following error:
[OLE DB Command [35549]] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. The RPC name is invalid.".
When I inserted the rows into an errors table, I found that the error code, -1071607698, is not defined and the only thing I could find online was a reference to:
DTS_E_COMMANDDESTINATIONADAPTERSTATIC_UNAVAILABLE
which appears to be a DTS error and not an SSIS error.
I have added tasks to explicitly verify the length of the field and that field actually inserts without any issues into the error table - which has the exact same column definition as the target table.
I am at a complete loss as to how to proceed - does anybody have an idea?
View 4 Replies
View Related
Dec 21, 2007
when doing an 'update table set field1 = 'N',using sql query analyzer, is the update committed immediately? If it isn't commited can a 'rollback' be executed, and what is the format of the rollback command?
View 4 Replies
View Related
Jun 15, 2006
How to implement two pase commit in SQL Server 2000, Is there Database Link link Oracle available here ?
View 5 Replies
View Related
Apr 4, 2007
Hi,
I'm using an SQL Express database over a network, using a C# Express program. So I had to use pure SQL connections and commands instead of using Data Sources (couldn't find a way for it to work). In the program / DB I've got a couple of Master - Detail situations. Something like:
Product:
-----------
productID
(...)
Acessories:
----------------
acessID
(...)
ProductAcess:
--------------------
productID
acessID
So when inserting a new Product, I'll have to first insert the product (with product name, price, and so on) and once I get the product ID from the insert command, I'll insert the ProductAcess rows. I've found a problem in this though. If for some reason the insert of the product is successful, but the insert of ProductAcess fails, I've got a big mess in hands because I'll have a row in Product with no rows in ProductAcess (which shouldn't happen in my program scenario). I could solve this by deleting all rows from the DB which connected in someway to the product that failed to insert, but would be far better and correct if I used a commit command at the end of the insert commands to make sure only the right data would be inserted (saving time and resources). I use this all the time in Oracle databases, but don't know if it is possible in SQL Express... Is it? How? Thanks
View 1 Replies
View Related
Sep 3, 2007
Hi,
My data flow has several transformations:
1. Search an employee, if the employee already exists, update it, otherwise insert it.
2. Once the new employee is created, i have to get its id (with another search transformation )to update another table with it. This id is an autonumeric , thats the reason i have to get it once the record is inserted.
At this momment this second search transformation to get the assigned id for the new reacord doesnt find any employee... i suppose its because these new data is not commited in the database....
the question is, Its possible to force a commit?
Thanks!
View 5 Replies
View Related
