|
Hi, I'm choosing between transaction on stored proc level and transaction on application level. You see:
<br />
DECLARE @ValidationCode INTEGER<br />
BEGIN TRAN<br />
SET @ValidationCode = 0<br />
EXEC sp_CreateSupplier 1, 'M', 1, 'Golden Lucky and Associates', 'Golden Lucky and Associates', NULL, NULL, 'A', 3, 8, @ValidationCode OUTPUT<br />
PRINT 'Validation code: ' + CAST (@ValidationCode AS VARCHAR(10))<br />
<br />
... do other stuff...<br />
<br />
COMMIT TRAN<br />
<br />
CREATE PROCEDURE sp_CreateSupplier(<br />
@supplier_no INTEGER,<br />
@type VARCHAR(8),<br />
@group_no INTEGER = NULL,<br />
@short_name VARCHAR(50) = NULL,<br />
@full_name VARCHAR(100) = NULL,<br />
@address VARCHAR(100) = NULL,<br />
@country_code VARCHAR(10) = NULL,<br />
@status VARCHAR(8) = NULL,<br />
@createBy INTEGER,<br />
@ownerGroup INTEGER,<br />
@ValidationCode INTEGER OUTPUT<br />
)<br />
AS<br />
BEGIN<br />
<br />
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE<br />
BEGIN TRANSACTION<br />
<br />
-- STEP 1: Validation<br />
SET @ValidationCode = 0<br />
IF EXISTS(SELECT * FROM fe_supplier WHERE supplier_no=@supplier_no)<br />
BEGIN<br />
SET @ValidationCode = -10<br />
ROLLBACK TRANSACTION<br />
RETURN<br />
END<br />
...<br />
COMMIT TRAN<br />
END<br />
The above snippet will result in:
Server: Msg 266, Level 16, State 2, Procedure sp_CreateSupplier, Line 26<br />
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.
Now, what I'm really doing in my code is I called the stored proc from a method in application level - And this method is executed in context of a transaction (Which wraps around the stored proc with its own transaction):
Public Function Create(ByVal sup As vendormanagement.to.Supplier, ByVal tran As IDbTransaction) As vendormanagement.to.Supplier<br />
<br />
Dim sql As String = "sp_CreateSupplier"<br />
Dim cmd As IDbCommand<br />
Dim rdr As IDataReader<br />
Dim param As IDataParameter<br />
Dim factory As IDbProviderFactory<br />
Dim validationCode As Int32 = 0<br />
<br />
Try<br />
If _conn.State <> ConnectionState.Open Then<br />
_conn.Open()<br />
End If<br />
<br />
factory = apputil.GlobalVariables.ProviderFactory<br />
cmd = factory.CreateCommand(sql, Conn)<br />
cmd.CommandType = CommandType.StoredProcedure<br />
cmd.Transaction = tran<br />
<br />
...<br />
rdr = cmd.ExecuteReader()<br />
...<br />
Catch<br />
...<br />
Finally<br />
...<br />
End Try<br />
End Function
What I wish to do is to MAINTAIN transaction in stored proc level - which suggest that I must remove application level transaction. BUT I can't do that because:
<br />
vconn = vFactory.CreateConnection(...)<br />
tran = vconn.BeginTransaction(...serializable...)<br />
SupplierBean.Create(..., tran)<br />
AnotherBean.Update(..., tran)<br />
tran.Commit()<br />
So, my question is, how can I reconcile the two: Application Vs Stored proc transaction. The ONLY way to just recitify the problem seems to be removing stored proc level transaction.... Am I right?
Thanks in advance!
Norman Fung
|
|
|
|
|
declare @intnewmoviecode int
declare @vcmovieplot varchar(400)
declare @intreturn int
set @vcmovieplot=
'although the fellowship is shattered,war continues'+
'to fill the lands of middle earth.frodo heads toward'+
'mordor iwth sam,while in the west,' +
'saruman continues his plot against rohan.'
exec @intreturn=
spinsertdata'the two towers',2002,
@vcmovieplot,200,@intnewmoviecode output
select * from movies where moviecode=@intnewmoviecode
select @intreturn
declare @intreturn int
declare @intnewmoviecode int
exec @intreturn=
spinsertdata 'An old movie',1940,
'this movie won''t be added',200,
@intnewmoviecode output
select @intreturn
Error converting data type varchar to int.
aaffi
|
|
|
|
|
You are probably trying to insert varchar (string) data where int is required.
Check your spinsertdata and the input variables declared.
BTW, do remember not to name your stored procedure as sp_*** as this will incur a performance penalty.
Hope it helps,
Edbert
|
|
|
|
|
declare @intnewmoviecode int
declare @vcmovieplot varchar(400)
declare @intreturn int
set @vcmovieplot='although the fellowship is shattered,war continues'+
'to fill the lands of middle earth.frodo heads toward'+
'mordor iwth sam,while in the west,' +
'saruman continues his plot against rohan.'
exec @intreturn=
spinsertdata'the two towers',2002,
@vcmovieplot,200,@intnewmoviecode output
select * from movies where moviecode=@intnewmoviecode
select @intreturn
aaffi
|
|
|
|
|
|
Like the previous post suggests, it would be very helpful if you would say what kind of trouble you're having with the code provided.
The most exciting phrase to hear in science, the one that heralds the most discoveries, is not 'Eureka!' ('I found it!') but 'That's funny...’
|
|
|
|
|
Hello,
I make a Database in SQL SERVER which has 9 tables. I want to copy that database to another computer, how can i do this.
kindly tell me the procedures, so that i can transfer database to another computer, along with its inserted data
EsHbAn BaHaDuR
EsHbAn BaHaDuR
|
|
|
|
|
|
Hi I'm using some DevExpress components in C#. Using sqlConnection, sqlDataAdapter, dataSet, gridControl (a Datagrid) and controlNavigator (to choose insert record, edit record etc.).
When I make my program I can see all the data in the table. Using the controlNavigator I can add records, delete them etc.
I use Microsoft SQL server 2000.
I understand that my program loads a local copy of the database and at the end I have to let the program know that the real table now needs to be updated.
I made a button under which I can test some commands.
I tried sqlDataAdapter1.Update(dataset11) and sqlDataAdapter1.Update(DataSet11,"films") (where films is the name of the table) also I tried dataSet11.acceptchanges().
No matter what I try each time when I quit the program and restart the program my table is empty.
What do I need to do to effectivly change/update the real table??
I would like a solution where I can input/change data in the datagrid using the controlNavigator and then by adding somewhere some extra code make the changes permanent.
I hope someone can help
Kind regards,
Clem
|
|
|
|
|
I am new to ADO, and Can I have a sample link to understand the need and purpose of ADO events with an example? Thanks.
|
|
|
|
|
http://www.support.microsoft.com/ph/6349
|
|
|
|
|
I am also want to know thx!
|
|
|
|
|
How can I Insepct / analyze the contents of a given *.ldf transaction log file?
The demand is for an analysis / inspection which is independent from any
database connection. Therefore - DBCC LOG and alike are not applicable.
In short - what is the internal structure of the transaction log?
How can one access thier contents?
Thanx
Hillel
|
|
|
|
|
The internal structure of the transaction log is officially undocumented. You won't be able to open it while the database is running. SQL Server uses the transaction log to record operations that will be performed before making the appropriate changes to the data pages; this allows transactions to be either recovered in full or rolled back in full if recovery is required.
You can find some information in Inside SQL Server 2000.
If you want to audit access to your database, I suggest reading SQL Server 2000 Auditing[^].
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
using(OleDbConnection _Connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=dBASE IV;User ID=;Password=;Data Source=c:\\CT"))
{
_Connection.Open();
_Connection.Close();
}
The 3 lines code above is causing me to lose Handles (on the Task Manager). So that my resource (the Handles) is not being released.
Code executes properly if I have no BDE (Borland Database Engine) installed on my machine. But I cannot live without BDE since it is needed by other applications.
|
|
|
|
|
Sounds like you need to talk to Borland...
Anger is the most impotent of passions. It effects nothing it goes about, and hurts the one who is possessed by it more than the one against whom it is directed.
Carl Sandburg
|
|
|
|
|
why is it important to use views in sql using transact tsql language
aaffi
|
|
|
|
|
Depends what you're using it for. I find it interesting that your last post was missing a "r" and your handle or ID would be freeha (meaning we're not free) if you added a "r". Are you a communist?
Edit *** Ooops you were missing an "m". Maybe your programming an online card game?
"People who never make mistakes, never do anything."
|
|
|
|
|
It isn't important to use a view unless your situation calls for it.
One use of VIEWs is to restrict access to data. For example, I normally lock out all the tables from all the users so that no one gets direct access to the tables. If they want to see the data they need to go to a view or use a stored procedure.
The way security works in SQL Server means that you can "revoke" permission on a table (or any object) to a specific user or role. However, if that object is accessed by something they do have access to (like a VIEW or PROCEDURE) then the user can use the data as much as they object they do have access to permits.
For instance, you could set up a view to hide columns from a table so that a specific user (or group of users) cannot see that data. Or you might perform a common join in the view so that commonly run queries are easier.
Does this help?
Cada uno es artifice de su ventura
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums
|
|
|
|
|
Angus, is it true using view is faster?
<italic>Work hard, Work effectively and a bit of luck is the key to success.
|
|
|
|
|
|
IF EXISTS (SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME ='vwsociety_groups_balances')
DROP VIEW vwsociety_groups_balances
GO
CREATE VIEW vwsociety_groups_balances with schemabinding
AS
select society_group_id,bank_balance,
(select count(*) fro dbo.players
where society_group=society_group_id) as no_of_players
from dbo.society_groups
go
aaffi
|
|
|
|
|
You have fro instead of from in your subquery.
"People who never make mistakes, never do anything."
|
|
|
|
|
|
I'm new to SQL and trying to understand what is most likely a simple question.
I'm trying to use UNION to pull together a result set of Name and Employee Number. Unfortunately there are duplicates in the two tables. The UNION syntax is supposed to weed out duplicates, but I have this situation.
First Name Last Name ID
TOM JONES 1111
TOM L JONES 1111
The code below shows what I have. The UNION evidentally looks at all the fields you bring back with the select. I need it to UNION only on the EmpID, but also have the name in the result list. I don't care which table it pulls the name, because either way, the name should be basically alike.
SELECT LastName,FirstName,EmpNo AS EmpID
FROM table1
WHERE EmpTermDate IS NULL
UNION
SELECT LastName, FirstName, SocSecNo AS EmpID
FROM table2
ORDER BY LastName ASC,FirstName ASC, EmpID ASC
Any suggestions how to UNION the information while eliminating duplicates based on EmpID only? Any help is appreciated!!
Lost in the vast sea of .NET
|
|
|
|
|