hello all..,
i want to make procedure can decreasetotalcost from order table(database:games.dbo) withbalance in bill table(database:bank.dbo). my 2 database in same server is name "boy"
i have 2 database like: bank.dbo and games.dbo
in games.dbo, have a table name is order(user_id,no_order,date,totalcost)
in bank.dbo, have a table name like is bill(no_bill,balance)
this is a list of bill table
no_bill balance
111222 200$
222444 10$
this is a list of order table
user_id no_order date totalcost
a 1 1/1/07 50$
when customer insert no_bill(111222) in page and click a button, then bill table became
no_bill balance
111222 150$
222444 10$
when customer insert no_bill(222444) in page and click a button, then message "sorry, your balance is not enough"
is procedure can take data from 2 database?
mystore procedure like:
ALTER PROCEDURE [dbo].[pay]
(
@.no_bill AS INT,
@.no_order AS int,
@.totalcost AS money
)
AS
BEGIN
BEGIN TRANSACTION
DECLARE @.balanc AS money
SET @.balanc= (SELECT [balance] FROM [boy\sqlexpress.Bank.dbo.bill] WHERE [no_bill] = @.no_bill)
UPDATE [bill]
SET
[balance] = @.balanc - @.totalcost
WHERE
[no_bill] = @.no_bill
COMMIT TRANSACTION
END
it's output message "Invalid object name '<boy\sqlexpress>.Bank.dbo.bill'.
Transactioncount after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTIONstatement is missing. Previous count = 0, current count = 1.
No rows affected.
(0 row(s) returned)
@.RETURN_VALUE =
Finished running [dbo].[pay]."
plss.. help...
Hi,
Just remove <boy\sqlexpress>.section, it would run. If two databases are on same server, they don't need to reference instance name with them.
thx for information...
i have remove it, but it can not work too...
SET @.balanc= (SELECT [balance] FROM [Bank.dbo.bill] WHERE [no_bill] = @.no_bill)
the error is same... another ways?
plzz...,help..
|||What database is the pay stored procedure in?
SET @.balanc= (SELECT [balance] FROM [boy\sqlexpress.Bank.dbo.bill] WHERE [no_bill] = @.no_bill)
When setting the balanc variable you reference the table with [boy\sqlexpress.Bank.dbo.bill], but in the update statement you use UPDATE [bill]
did you try [Bank].[dbo].[bill] instead of [Bank.dbo.bill] ?
|||thx for dleonard and david information...
i have try it all... but cannot too...
[boy\sqlexpress.bank.dbo.bill]
[boy\sqlexpress].[bank].[dbo].[bill]
[bank.dbo.bill]
[bank].[dbo].[bill]
[bank.bill]
[bank].[bill]
[bank.dbo].[bill]
[boy\sqlexpress.bank.dbo].[bill]
[bill.bank.dbo]
i am create store procedure inboy\sqlexpress.games.dbo.
i haveboy\sqlexpress.bank.dbo andboy\sqlexpress.games.dbo
i have post this in this forums 3 thread, but it's cannot resolved...
pls... another idea?
or maybe store procedure can not get data from 2 database?
hardy:
or maybe store procedure can not get data from 2 database?
I know it can be done with SqlServer. Don't use SqlExpress...
|||
Oh! I just re-read your example and noticed the BEGIN TRANSACTION / COMMIT TRANSACTION commands in it.
That's probably why it's barfing.
I would be surprised if SqlExpress supported transactions across databases. If I'm right, removing those two commands would enable you to proceed.
(Of course, it would also open you up to the dangers of the transaction failing half-way thru and leaving the first half committed in the other database.)
|||it can not work too...
the errors is focus that bill table can not be found...
the other command is already right... now big problem is [bank.dbo.bill].
maybe connection string?
Let say you have two tables (Table1 in Database1 owned by User1) and (Table2 in Database2 owned by User2)
Make sure that each time you call any object .. call it with its full name: [ServerName].[DatabaseName].[OwnerName].[ObjectName]
Example:
SELECT *FROM Server1.Database1.User1.Table1 aINNERJOIN Server2.Datbase2.User2.Table2 bON a.col1 = b.col1
Good luck.
thx for ur code...
can u make it to my code?
my code in procedure:
ALTER PROCEDURE [dbo].[pay]
(
@.no_bill AS INT,
@.no_order AS int,
@.totalcost AS money
)
AS
BEGIN
BEGIN TRANSACTION
DECLARE @.balanc AS money
SET @.balanc= (SELECT [balance] FROM [boy\sqlexpress.Bank.dbo.bill] WHERE [no_bill] = @.no_bill)
UPDATE [bill]
SET
[balance] = @.balanc - @.totalcost
WHERE
[no_bill] = @.no_bill
COMMIT TRANSACTION
END
i only want to call bank.dbo. because i create a store procedure in games.dbo, so i dont need call a games.dbo..
now, the problem is bank.dbo can not be detected... any ideas?
Note: I assumed thatpay stored procedure exists in the Bank database, try this:
ALTER PROCEDURE [bank].[dbo].[pay] @.no_billINT, @.no_orderint, @.totalcostmoneyASBEGIN BEGIN TRANSACTION DECLARE @.balancmoney SELECT@.balanc= [balance]FROM [bank].[dbo].[bill]WHERE [no_bill] = @.no_billUPDATE [bank].[dbo].[bill]SET [balance] = (@.balanc - @.totalcost)WHERE [no_bill] = @.no_billCOMMIT TRANSACTIONEND
Good luck.
|||yess, many thx cs4ever. u are great...
my wrong yesterday is i make in select[bank].[dbo].[bill], i make in update
[bank.dbo.bill]. it cannot be work. i think [bank.dbo.bill] is similar with[bank].[dbo].[bill].i just know that it no similar...
so it must [bank].[dbo].[bill], can work...
and thx to david too, u ways is true too.but u not give example..., so i try ur ways in select not in update...
ok, thx to all.. this day is so great.....
No comments:
Post a Comment