Showing posts with label transactions. Show all posts
Showing posts with label transactions. Show all posts

Friday, March 30, 2012

help on table design

Hi,
I need a table for the transactions, the transcation could
be credit card, check. they will have different data like
credit card has credit card number, and check have account
number, should I have separate table for each type? or put
them in one table? ThanksThis is a multi-part message in MIME format.
--=_NextPart_000_0183_01C3811B.1B861CE0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
As always, it depends. However, checking accounts and credit card =accounts are very different animals. A checking account could pay =interest but a credit card would not. A credit card has an expiry date. = A checking account does not. I'd be inclined to model these =separately. You can always use a UNION ALL to display the account =numbers in a single resultset.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Jen" <follower1999@.yahoo.com> wrote in message =news:060401c3813b$59a8e3c0$a301280a@.phx.gbl...
Hi,
I need a table for the transactions, the transcation could be credit card, check. they will have different data like credit card has credit card number, and check have account number, should I have separate table for each type? or put them in one table? Thanks
--=_NextPart_000_0183_01C3811B.1B861CE0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

As always, it depends. However, =checking accounts and credit card accounts are very different animals. A =checking account could pay interest but a credit card would not. A credit =card has an expiry date. A checking account does not. I'd be inclined =to model these separately. You can always use a UNION ALL to display =the account numbers in a single resultset.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Jen" =wrote in message news:060401c3813b$59=a8e3c0$a301280a@.phx.gbl...Hi,I need a table for the transactions, the transcation could be credit =card, check. they will have different data like credit card has credit =card number, and check have account number, should I have separate table =for each type? or put them in one table? Thanks

--=_NextPart_000_0183_01C3811B.1B861CE0--
Posted by prerequisiteqwep at 12:15 AM 0 comments
Labels: , , , , , , , , , , ,

Monday, March 12, 2012

Help needed on sum query

i have two table, one containing banking files header (a) information and a
second table containing the banking transactions (b) they are linked by a
LedgerKey field
The banking transaction my have a trans code of either 99 0r 17. Im trying
to get the sum of the header files where transaction trans code = 99, as i
need to knw what the client credit is. There can be several Header records,
but none of the transaction codes are mixed ie one file header will only
have transcode 99 or transcode 17
Select SUM((Cast(a.TransValue as MONEY(11,2))/100))
from Table1 a
JOIN Table2 b
on a.LedgerKey = b.LedgerKey
where a.Licence = 123456
AND b.TransCode = '99'
a.TransValue varchar(11)
a.LedgerKey Varchar(12)
a.Licence Varchar(6)
b.LedgerKey varchar(12)
b.TransCode varchar (2)
in this instance there are 7 records in table b and im getting a SUM result
of 7 x a.TransValue instread if 1 X a.TransValue
Im a bit reluctant to add the ddl as it wil take a few hours to create
sample date, as for ovious reasons i can not post up live account details.Peter Newman wrote:
> i have two table, one containing banking files header (a)
> information and a second table containing the banking transactions
> (b) they are linked by a LedgerKey field
> The banking transaction my have a trans code of either 99 0r 17. Im
> trying to get the sum of the header files where transaction trans
> code = 99, as i need to knw what the client credit is. There can be
> several Header records, but none of the transaction codes are mixed
> ie one file header will only have transcode 99 or transcode 17
> Select SUM((Cast(a.TransValue as MONEY(11,2))/100))
> from Table1 a
> JOIN Table2 b
> on a.LedgerKey = b.LedgerKey
> where a.Licence = 123456
> AND b.TransCode = '99'
> a.TransValue varchar(11)
> a.LedgerKey Varchar(12)
> a.Licence Varchar(6)
> b.LedgerKey varchar(12)
> b.TransCode varchar (2)
> in this instance there are 7 records in table b and im getting a SUM
> result of 7 x a.TransValue instread if 1 X a.TransValue
> Im a bit reluctant to add the ddl as it wil take a few hours to create
> sample date, as for ovious reasons i can not post up live account
> details.
A few hours? It took me five minutes to simulate your situation:
LedgerKey Varchar(12),
TransValue varchar(11),
Licence Varchar(6))
create table #trans (
LedgerKey varchar(12),
TransCode varchar (2))
insert into #header
select 'abc','1000.00','123456'
insert into #trans
select 'abc','99'
union all
select 'abc','99'
union all
select 'abc','99'
union all
select 'abc','99'
union all
select 'abc','99'
union all
select 'abc','99'
union all
select 'abc','99'
go
Select SUM((Cast(a.TransValue as MONEY(11,2))/100))
from #header a
WHERE EXISTS (Select * FROM #trans b
where a.LedgerKey = b.LedgerKey and
a.Licence = 123456
AND b.TransCode = '99')
drop table #header
drop table #trans
HTH,
Bob Barrows
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Posted by prerequisiteqwep at 2:35 AM 0 comments
Labels: , , , , , , , , , , , , , , ,

Help Needed on DTC

Hi,
I'm having problems while running distributed transactions on two different servers. I
have two servers 'EPOOL5' and 'EPOOL9' respectively running SQl servers.
I have linked both the servers using sp_addlinkedserver 'EPOOL5' and sp_addlinkedserver 'EPOOL9' on EPOOL9 and EPOOL5 respectively. I have created a user PCRCCTEST on both the server databases. The databases being VDPMASTER on EPOOL5 and VDPMASTERTEST on EPOOL9. I have given the user PCRCCTEST proper privileges to access the tables in the respective databases.
I have started DTC on both the servers EPOOL9 and EPOOL5.

Now when I execute a query say select * from epool9.vdpmastertest.dbo.project_master from EPOOL5, the query is successfully executed and the rows are retrieved. When I insert rows similary, the rows are getting inserted. I am facing a problem when I try to run transactions. i.e I have created a stored procedure 'test' on VDPMASTER database on EPOOL5. 'Project_Master' being the table in EPOOL9 VDPMASTERTEST database. The user PCRCCTEST has privileges to insert data into Project_Master.

create procedure test
as
begin distributed transaction

insert into epool9.VDPMASTERTEST.dbo.Project_Master
(project_id,quality_id,project_name,project_client ,start_date,end_date,
project_active,project_master_update_flag,project_ master_updated_by, project_master_updated_on)
values (447,'3433','manufacturing','firstbank','02/22/2002','02/23/2003','Y','I',1,getdate())

if @.@.error=0
begin
commit transaction
print 'commitTest'
end
else
begin
print 'rollbackTest'
rollback transaction
end

When I execute this stored procedure from EPOOL5 server, I get the following error.

Server: Msg 7392, Level 16, State 2, Procedure test, Line 5
Could not start a transaction for OLE DB provider 'SQLOLEDB'.
[OLE/DB provider returned message: Only one transaction can be active on this session.]

I would be grateful if you could help me on this.
Thanks in advance
P.C. VaidyanathanI believe that this error is caused by nested transactions

begin tran
begin tran

commit tran
commit tran

To prevent your stored procedure from creating nested transactions you can check @.@.TRANCOUNT to see if a BEGIN TRAN has already been issued.

CREATE PROCEDURE test
as
DECLARE @.tfTran tinyint

--
-- Start Transaction
--
IF (@.@.TRANCOUNT = 0) BEGIN
SET @.tfTran = 1
BEGIN DISTRIBUTED TRANSACTION
END
ELSE
SET @.tfTran = 0

INSERT INTO epool9.VDPMASTERTEST.dbo.Project_Master
(project_id,quality_id,project_name,
project_client,start_date,end_date,
project_active,project_master_update_flag,
project_master_updated_by, project_master_updated_on)
values
(447,'3433','manufacturing','firstbank',
'02/22/2002','02/23/2003','Y','I',1,getdate())

IF @.@.error=0 BEGIN
IF (@.tfTran = 1) BEGIN
COMMIT TRAN
PRINT 'Commit Test'
END
END
ELSE BEGIN
IF (@.tfTran = 1) BEGIN
ROLLBACK TRAN
PRINT 'Rollback Test'
END
END
Posted by prerequisiteqwep at 2:33 AM 0 comments
Labels: , , , , , , , , , , ,
Subscribe to: Posts (Atom)