|
I have a problem when rollback a transaction. I create a stored procedure as follow:
CREATE PROCEDURE [dbo].[sp_test] AS
begin transaction
declare @Res int
select * into abc from sysusers
delete from abc123 --an error is raised cause there is no abc123 table
Set @Res = @@Error
if @Res <> 0
Rollback transaction
else
Commit transaction
When I execute this stored procedure from Querry Analyzer:
Exec sp_test
It shows an error message:
Invalid object name 'abc123'.
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.
The problem here is that: when a statement makes error (select,delete, insert), SQL Server 2000 stop executing other statement. So, the rollback transaction statement has no meaning.
Please help me to solve this problem. All I want is to rollback a stored procedure when never there is an error.
Thanks.
adfafafa
|
|
|
|
|
If there is no table abc123 then the proc will not compile so you are not getting a data error you are getting a syntax error in your code.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I create this proc in SQL Server 2000 and I think this proc will be compiled because there is no syntax error (I have checked syntax error after creating). Instead, it will cause run-time error because there is no abc123 table in the data base.
The problem is SQL Server 2000 stop executing other statement when it gets an error in select,delete,insert or update statement.
I've tried to use:
SET XACT_ABORT ON
to rollback other statements in the transaction but it still making error cause: ROLLBACK or COMMIT are not executed after BEGIN TRAN.
Please run my proc then help me to solve this problem.
Thank you very much.
CREATE PROCEDURE [dbo].[sp_test] AS
SET XACT_ABORT ON
DECLARE @Res int
begin transaction
select * into abc from sysusers
delete from abc123
Set @Res = @@ERROR
if @Res <> 0
ROLLBACK TRANSACTION
else
COMMIT TRANSACTION
GO
adfafafa
|
|
|
|
|
I don't think you are going to get transaction processing to do what you want, you are using the wrong tool, you should check for the existence of the table before you attempt to process against that table.
[rant]
IMHO your design stinks, if there is a possibility that a table does not exist in the database I am betting your design is wrong.
Another thing - relying on transactions stink, this is a classic "programming by error", if there is no error then it worked and we can continue when you should validate the data before attempting to process it. Error traps/transaction are to trap faults they are not a tool to make your programming life easier, the only time you should get into an error trap is during development or there is an environment issue.
[/rant]
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I am using 'ADOX.Catalog' namespace and the following code to create a new MS Access database.
Private Function CreateAccessDatabase() As Boolean
'-------------------------------------------------------
'This method is called to create a new Access database
'Required: NewDBConnectionString
'-------------------------------------------------------
Dim ADOXCatalog As New ADOX.Catalog
Try
If Me.NewDBConnectionString.Trim.ToString.Length = 0 Then Throw New Exception("Connection string is not specified")
'Call create method to create a new database
ADOXCatalog.Create(Me.NewDBConnectionString.Trim.T oString)
Return True
Catch ex As Exception
Throw ex
Return False
Finally
If Not ADOXCatalog Is Nothing Then
ADOXCatalog = Nothing
End If
End Try
End Function
The moment it created database, it is also leaving a .ldb file in the same directory. I don't want that .ldb file. If I tried to delete the file forcedly, it is passing an exception "The file is being used....". All the connection strings are properly closed.
Can anyone help me to remove this .ldb file.
|
|
|
|
|
|
SELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'ONE_ROW_TABLE';
Will get me the column names, ok, when this table is meant to be a one-row table, how can I get the column names of that table that has a value of say '0'
Example:
ONE_ROW_TABLE
COL1 COL2 COL3 COL4 COL5
---- ---- ---- ---- ----
1 0 1 0 0
I want to see an output like:
COL1
COL3
Thank you guys!
All generalizations are wrong, including this one!
(\ /)
(O.o)
(> <)
<div class="ForumMod">modified on Wednesday, October 22, 2008 4:45 AM</div>
|
|
|
|
|
You can create a query using UNION to make it row based or a dynamic query using the case statement for a column based answer.
Need software developed? Offering C# development all over the United States, ERL GLOBAL, Inc is the only call you will have to make.
Happiness in intelligent people is the rarest thing I know. -- Ernest Hemingway
Most of this sig is for Google, not ego.
|
|
|
|
|
How??
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
|
|
|
|
|
What you could try:
- select everything from the table
- use PIVOT to 'turn' the results (make columns rows and vice versa)
- use the result as inline view in FROM section
- create a select around inline view and restrict rows based on values
so the keyword in this is PIVOT.
Hope this helps,
Mika
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Dear Friend's
 i have an float value 2.796 and 2.792 i want it in two digit value when i use round keyword it give me output as 2.80 and 2.79 but i want it as 2.79 and 2.79 can it possible if yes please tell me how to do it in sql server 2005
i have use Round and STR keyword from sql for this but in vain
CODE
--USING STR
select STR(2.792,25,2) as ID
select STR(2.796,25,2) as ID
OUTPUT
2.79
2.80
--END
--USING ROUND
select round(2.792,2) as ID
select round(2.796,2) as ID
OUTPUT
2.790
2.800
--END
Sasmi
|
|
|
|
|
Actually, you're asking for this output!, if you dont want a decimal, then simply skip it! here
SELECT ROUND(2.792) FROM ....;
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
|
|
|
|
|
Actually i want last two digit i mean if i have value 2, 2.7312467 and 2.796 then the output should be 2.00, 2.73 and 2.79
thanks for the code but it's giving me error "The round function requires 2 to 3 arguments."
CODE
select round(2.792) as ID
select round(2.796) as ID
OUTPUT
Msg 189, Level 15, State 1, Line 7
The round function requires 2 to 3 arguments.
Msg 189, Level 15, State 1, Line 8
The round function requires 2 to 3 arguments.
Sasmi
|
|
|
|
|
Sasmi_Office wrote: 2, 2.7312467 and 2.796 then the output should be 2.00, 2.73 and 2.79
Q: Do you know what the hell is "Rounding"?? 
A: It is bringing the number to the closest whole number, "and in case you specify a decimal place(s), to the closest number of those decimals"
then 2.01 >> 2
and 2.55 >> 3
Sasmi_Office wrote: thanks for the code but it's giving me error "The round function requires 2 to 3 arguments."
Then give it a 0 as the second parameter
SELECT ROUND(2.88,0)...
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
|
|
|
|
|
An old trick
declare @a float,@b float
select @a = 2.796, @b = 2.792
select round(@a - 0.005,2),round(@b - 0.005,2)
Both return 2.79. Its because round follows the maths rule of going up at .5 and over.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
thanks dear.
it's working now, But i have one question you add 0.005 to the value, should every time we have to add 0.005 or you add it with some specific reason i mean i found my value (2.792 or 2.796) is with 3 digit and you add 0.005 which also have 3 digit if my value is more then 3 digit (2.79546) then should i add the same 0.005 value or i have to add 0.00005
can you clarify me...
Sasmi
|
|
|
|
|
It doesn't matter how long your number is, you need the same number of zeros as you want to round to. For example, if you want to round to 4 decimal places you use .00005, to round to 3 decimal places you use .0005
Hope this clarifies.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Thanks for the clarification.
Sasmi
|
|
|
|
|
Hi all,
I want to write a store proc that queries across db, and whenever I am at one db location, i want to query for a list of records which I want to store because I am going to perform an update on each of these based on the results that i gather earlier. once im done, ill loop and move on the next db.
Looks something like this;
1. Retrieve all db links
2. Loop to go to the first db link provided
3. Query table @current db link and use cursor to store the results
4. Loop results to perform update
5. Once, done go back to (2) and move to the next db until there is no more left
Now the question is, how to I access query across db dynamically? Currently i am using EXECUTE IMMEDIATE method. Next, can i use crusor when using execute immediate?
Any advice? Greatly appreciate it
Regards, Jensen
|
|
|
|
|
You can either use DBMS_SQL package to define and use the cursor or simply use REF CURSOR.
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Hi,
How can i get a column sum by checking similar rows in database.I know its simple,but pls help me.
thanks
|
|
|
|
|
select sum(mark1) from table1 group by roll_no having roll_no=10
I think this will help u.
|
|
|
|
|
Thank u but the problem is i need to sm up the values depending on a flag
ie if flag is set then add or else substract.
|
|
|
|
|
Try something like this
create table #b1(usr sysname, flag char(1), val float)
insert into #b1(usr,flag,val) select 'a','n',22
insert into #b1(usr,flag,val) select 'b','Y',22
insert into #b1(usr,flag,val) select 'a','n',11
insert into #b1(usr,flag,val) select 'b','n',2
insert into #b1(usr,flag,val) select 'a','Y',100
select usr,sum(case when flag = 'y' then val else val * -1 end) from #b1 group by usr
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi friends
I have two table , name of the table is Table1, table2
table1 values
**************
rollno name tot
1 a 100
2 b 75
3 c 76
table2 values
*************
rollno reference_name
1 SSSS
1 RRRR
2 MMMM
i written the query like this
select table1.rallno,table1.mark,table2.refrence_name from table1 left join table2 on table1.rallno=table2.rallno
I want all the vale from Table1 and Table2 Roll No 1 has more reference name but I want only 1 Reference name
How can i write the query plz tell
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
