|
Hi To All....!
I have two stored procedure 1)sp_User_Master 2)sp_User_Detail. When I save data it first execute sp_User_Master stored procedure and then sp_User_Detail. Now these are two diffrent stored procedure and are executed one after another.
My Problem is when an error occurs in sp_User_Detail stored procedure i am unable to rollback the data saved using sp_User_Master. So is it possible to rollback the data saved by first executed stored procedure.
Please help.
Thanks to all.
.
|
|
|
|
|
your code look like this:
try
{
begin tran
exec sp_User_Master
exec sp_User_Detail
commit tran
}
catch (Execption objException)
{
rollback transaction
}
|
|
|
|
|
1. What database are you using?
2. What programming language are you using?
|
|
|
|
|
I am Using SQL SERVER 2005 and C SHARP.NET
.
|
|
|
|
|
Use a controller procedure and structured error handling technique.
The controller procedure will call the 2 procedures for data insertion and with structured error handling you can eassily RollBack the transaction if any of the procedure fails...
|
|
|
|
|
use this,
cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = "Your_SP";
cmd.CommandType = CommandType.StoredProcedure;
if (con.State != ConnectionState.Open)
con.Open();
cmd.Transaction = con.BeginTransaction();
try
{
parentmainid=Subroutine_For_Parent_Insert(userid);
for (items = 0; items <= _alldata.Count - 1; items++)
{
Subroutine_For_Child_Insert(parentmainid,childdata);
}
cmd.Transaction.Commit();
_status = true;
}
catch (Exception e)
{
cmd.Transaction.Rollback();
_status = false;
}
Some important points :
1>Remember to declare cmd as global or pass it as byreference.
2> Do not reinitialze the connection in any of the functions.
3> Use cmd.Parameters.clear() on every function call or else you would get a "Too many parameters error".
4> Do not reinitialize the cmd object during the transaction or else the transaction will lose the data and won`t be rolled back.
When you fail to plan, you are planning to fail.
|
|
|
|
|
If one has to select about 30-100 different rows from a table based on their ids...
which one is best approach:
1.
select * from table where id1=3 or id1=9 or id1=200 or id1=205 ...
or
2. using a loop... eg
foreach(string id in ids)
{
stmt="select * from table where id3="+id
}
thankyou for guidance...
|
|
|
|
|
Generally speaking, the fewest trips between the database client and the server, the better the application will perform. This is especially true for queries that return small amounts of data. For this reason, option #1 is more likely to perform better.
Adam Maras | Software Developer
Microsoft Certified Professional Developer
|
|
|
|
|
|
Does MySql not have an IN operation, I hate using OR in a where clause.
Select * from Table where ID in(1,23,4,56,700)
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
Based on the 2 options that you gave, the first option is better.
|
|
|
|
|
So I am working on this SP for my DB at work:
CREATE PROCEDURE dbo.sp_CreateChecklistForPackage
(
@PACKAGE varchar(255)
)
AS
INSERT INTO TBL_SWBOM_CHECKLISTS
(
PACKAGE,
TEMPLATEOWNER,
STEPNUMBER,
STEPNAME,
DESCRIPTION,
LASTCHANGE,
LASTCHANGEBY
)
select
@PACKAGE,
TEMPLATEOWNER,
STEPNUMBER,
STEPNAME,
DESCRIPTION,
LASTCHANGE,
LASTCHANGEBY
FROM TBL_SWBOM_CHECKLIST_TEMPLATES
GO
What I need it to do is find the newest entry for each of the three users (specific names that I can test against, like "TEMPLATEOWNER='Joe Smith') and put only that template into the SWBOM_CHECKLISTS table only. So the TBL_SWBOM_CHECKLISTS table should have one entry per package made up of three.
So basically I need it to find the newest entry from each of the three user's separate entries and then put that into the other table. I think it's fairly simple and doable, just not 100% sure.
|
|
|
|
|
Well, it is easy enough to find the newest entry for a given owner in your setup:
SELECT TOP 1 *
FROM TBL_SWBOM_CHECKLIST_TEMPLATES
WHERE LASTCHANGEBY = 'Joe Smith'
ORDER BY LASTCHANGE DESC
Wrap something like that in your INSERT and you should be good to go, at least for the first of your three records.
|
|
|
|
|
I have created an SSIS package that works if Windows authentication is
selected, but fails upon using SQL Server Authentication.
Basically, Its not saving the password for SQLServer Authentication.
Can anyone please help me on this ?
|
|
|
|
|
Are you able to loging into SQL Server using the same User ID or password ? Does that user having permission to access the Data?
You can check it from Security Permission. Check the User, which you are using having access permission on that particular database or not.
Steps :
1. Goto SQL Server Object Explorer > Security > Logins
2. Double Click on the particular user, that you need
3. Goto User Mapping Tab
4. Check Which Datbases are selected and what role is given to user.
5. Update
That's All 
Thanks.
|
|
|
|
|
1. No cross-posting
2. Don't use urgent. This is run by volunteers so your cries for time constraints make us want to avoid you.
|
|
|
|
|
For database connections or any other configurable objects, in your SSIS Package use Configuration File as the value might be changing with environments.
In the target envieonment, you just need to update the configuration file and it would work fine.
|
|
|
|
|
Hi all,
here is my SP
create FUNCTION fn_Ceiling
(
Source IN DECIMAL
)
RETURN DECIMAL
as
DecDiff DECIMAL(16,3);
Ceiling DECIMAL(16,3);
Source1 DECIMAL;
BEGIN
SELECT NVL(Source, 0) INTO Source1 FROM DUAL;
SELECT Source1 - FLOOR(Source1) INTO DecDiff FROM DUAL;
SELECT FLOOR(Source) +
case
WHEN DecDiff between 0.000 and 0.250 THEN 0.000
WHEN DecDiff between 0.250 and 0.750 THEN 0.500
WHEN DecDiff between 0.750 and 1.000 THEN 1.000
else DecDiff
end INTO Ceiling FROM DUAL;
return Ceiling;
END;
/
It is created...
but if i execute the function am not getting the correct value
ex:-
select fn_ceiling(1.75) from dual;
am getting the result as 1
(actaul result should be 1.500)
Dnt know what i did wrong?
|
|
|
|
|
Not sure with Oracle but I would look at Source1, no presision is declared.
Also your case logic is going to cause a problem with 25 and 75. It meets 2 conditions is should be
WHEN DecDiff between 0.000 and 0.250 THEN 0.000
WHEN DecDiff between 0.251 and 0.750 THEN 0.500
WHEN DecDiff between 0.751 and 1.000 THEN 1.000
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
If i add the presision also the same result..
|
|
|
|
|
hi,
Below is my table.
Parent ID.......ID.......Name
NULL..........10001.....Region1
NULL..........10002.....Region2
NULL..........10003.....Region3
10001.........501.......Test1
10001.........502.......Test2
10002.........503.......Test3
10002.........504.......Test4
10003.........505.......Test5
10003.........506.......Test6
501...........507.......Test7
501...........508.......Test8
502...........509.......Test9
502...........510.......Test10
I want the output to be the following.
ParentID.......ID.......Name
10001..........501......Test1
10001..........502......Test2
501............512......Armenia
501............536......Brunei
NULL...........10001....Region1
Based on where clause for ID = '10001'
Pls help. I can't figure out on how to add the last entry of "NULL......10001....Region1"
Thx.
|
|
|
|
|
The query logic is not coming out clear from your posting, please elaborate of the selection criteria...
|
|
|
|
|
well actually i need it to be that output for my asp.net treeview control.
if you look the 10001 is the main ParentID and the ChildID is based on ParentID. in the ID = 501 is also fall inside 10001 ID group, and there is a SubChild for the ChildID = 501
I hope u understand.....i donno how to say...hehe
its a TREEVIEW control.
Thx.
|
|
|
|
|
Not sure if I understood your question correctly. Try this:
select * from TableName where
(ParentID = '10001') or (ID='10001') or
(ParentID in (select ID from TableName where ParentID = '10001'))
May be this can help you.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
