Inter Table Multiplication and Result (Newbie)

I want to multiply a column (Units) for all transactions in the current
table by a price from another table and reflect the result a column in the
'Units' table. How would I do this and update this daily using SQL server.
The transactions and pricing are matched by a 'Date' field.
Thanks for any help or direction.http://www.aspfaq.com/etiquette.asp?id=5006
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"dl" <d@.l.com> wrote in message news:eRqusn$pFHA.364@.TK2MSFTNGP11.phx.gbl...
> I want to multiply a column (Units) for all transactions in the current
> table by a price from another table and reflect the result a column in the
> 'Units' table. How would I do this and update this daily using SQL server.
> The transactions and pricing are matched by a 'Date' field.
> Thanks for any help or direction.
>|||Adam
The SQL statement I am using is below but I get the error that is shown
thereafter. What I am really trying to achieve is as documented int eh
second statement.
UPDATE [JOHCM-Test].[dbo].[numbers]
SET [number_result]=[numbers]*10
WHERE [Dates]=[dbo].[Prices].[Dates]
Server: Msg 107, Level 16, State 2, Line 1
The column prefix 'dbo.Prices' does not match with a table name or alias
name used in the query.
UPDATE [JOHCM-Test].[dbo].[numbers]
SET [number_result]=[numbers]*[dbo].[Prices].[Prices]
WHERE [Dates]=[dbo].[Prices].[Dates]
Thanks for an assistance.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23hfzzu$pFHA.3160@.TK2MSFTNGP14.phx.gbl...
> http://www.aspfaq.com/etiquette.asp?id=5006
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "dl" <d@.l.com> wrote in message
> news:eRqusn$pFHA.364@.TK2MSFTNGP11.phx.gbl...
>|||Total guess, since you still haven't provided DDL (I don't know what the
keys are):
UPDATE [JOHCM-Test].[dbo].[numbers]
SET [number_result]=
(
SELECT [dbo].[Prices].[numbers]*10
FROM [dbo].[Prices]
WHERE [Dates]=[dbo].[Prices].[Dates]
)
WHERE EXISTS
(
(SELECT *
FROM [dbo].[Prices]
WHERE [Dates]=[dbo].[Prices].[Dates]
)
If this still doesn't do it, please post DDL and sample data.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"dl" <d@.l.com> wrote in message
news:uNPC%236IqFHA.644@.TK2MSFTNGP10.phx.gbl...
> Adam
> The SQL statement I am using is below but I get the error that is shown
> thereafter. What I am really trying to achieve is as documented int eh
> second statement.
> UPDATE [JOHCM-Test].[dbo].[numbers]
> SET [number_result]=[numbers]*10
> WHERE [Dates]=[dbo].[Prices].[Dates]
> Server: Msg 107, Level 16, State 2, Line 1
> The column prefix 'dbo.Prices' does not match with a table name or alias
> name used in the query.
>
> UPDATE [JOHCM-Test].[dbo].[numbers]
> SET [number_result]=[numbers]*[dbo].[Prices].[Prices]
> WHERE [Dates]=[dbo].[Prices].[Dates]
> Thanks for an assistance.
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:%23hfzzu$pFHA.3160@.TK2MSFTNGP14.phx.gbl...
>|||Adam
I hope I have the relevant info correct as requested. Thanks again.
CREATE TABLE [Prices] (
[Prices] [float] NULL ,
[Dates] [float] NULL
) ON [PRIMARY]
GO
CREATE TABLE [numbers] (
[numbers] [float] NULL ,
[number_result] [float] NULL ,
[Dates] [float] NULL
) ON [PRIMARY]
GO
INSERT INTO [JOHCM-Test].[dbo].[numbers]([numbers], [number_result],
[Dates])
VALUES(100, 0, 38564)
INSERT INTO [JOHCM-Test].[dbo].[numbers]([numbers], [number_result],
[Dates])
VALUES(200, 0, 38564)
INSERT INTO [JOHCM-Test].[dbo].[numbers]([numbers], [number_result],
[Dates])
VALUES(300, 0, 38565)
INSERT INTO [JOHCM-Test].[dbo].[numbers]([numbers], [number_result],
[Dates])
VALUES(400, 0, 38565)
INSERT INTO [JOHCM-Test].[dbo].[Prices]([Prices], [Dates])
VALUES(2.5, 38564)
INSERT INTO [JOHCM-Test].[dbo].[Prices]([Prices], [Dates])
VALUES(3.2, 38565)
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:e9JZGMLqFHA.3180@.TK2MSFTNGP15.phx.gbl...
> Total guess, since you still haven't provided DDL (I don't know what the
> keys are):
> UPDATE [JOHCM-Test].[dbo].[numbers]
> SET [number_result]=
> (
> SELECT [dbo].[Prices].[numbers]*10
> FROM [dbo].[Prices]
> WHERE [Dates]=[dbo].[Prices].[Dates]
> )
> WHERE EXISTS
> (
> (SELECT *
> FROM [dbo].[Prices]
> WHERE [Dates]=[dbo].[Prices].[Dates]
> )
>
> If this still doesn't do it, please post DDL and sample data.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "dl" <d@.l.com> wrote in message
> news:uNPC%236IqFHA.644@.TK2MSFTNGP10.phx.gbl...
>|||Try:
UPDATE [JOHCM-Test].[dbo].[numbers]
SET [number_result]=
(
SELECT [dbo].[numbers].[numbers]*[dbo].[Prices].[Prices]
FROM [dbo].[Prices]
WHERE [dbo].[numbers].[Dates]=[dbo].[Prices].[Dates]
)
WHERE EXISTS
(
(SELECT *
FROM [dbo].[Prices]
WHERE [dbo].[numbers].[Dates]=[dbo].[Prices].[Dates]
)
By the way, what are these floating-point "dates" ?
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"dl" <d@.l.com> wrote in message
news:eSa%236NMqFHA.1272@.TK2MSFTNGP11.phx.gbl...
> Adam
> I hope I have the relevant info correct as requested. Thanks again.
>
> CREATE TABLE [Prices] (
> [Prices] [float] NULL ,
> [Dates] [float] NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [numbers] (
> [numbers] [float] NULL ,
> [number_result] [float] NULL ,
> [Dates] [float] NULL
> ) ON [PRIMARY]
> GO
> INSERT INTO [JOHCM-Test].[dbo].[numbers]([numbers], [number_result],
> [Dates])
> VALUES(100, 0, 38564)
> INSERT INTO [JOHCM-Test].[dbo].[numbers]([numbers], [number_result],
> [Dates])
> VALUES(200, 0, 38564)
> INSERT INTO [JOHCM-Test].[dbo].[numbers]([numbers], [number_result],
> [Dates])
> VALUES(300, 0, 38565)
> INSERT INTO [JOHCM-Test].[dbo].[numbers]([numbers], [number_result],
> [Dates])
> VALUES(400, 0, 38565)
> INSERT INTO [JOHCM-Test].[dbo].[Prices]([Prices], [Dates])
> VALUES(2.5, 38564)
> INSERT INTO [JOHCM-Test].[dbo].[Prices]([Prices], [Dates])
> VALUES(3.2, 38565)
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:e9JZGMLqFHA.3180@.TK2MSFTNGP15.phx.gbl...
alias
message
in
>|||Adam
I get the error 'Server: Msg 170, Level 15, State 1, Line 13
Line 13: Incorrect syntax near ')'.
I have tried adding another ')' after line and then get the error message
Invalid object name 'dbo.Prices'
Thanks
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%235WjsUMqFHA.3544@.TK2MSFTNGP15.phx.gbl...
> Try:
> UPDATE [JOHCM-Test].[dbo].[numbers]
> SET [number_result]=
> (
> SELECT [dbo].[numbers].[numbers]*[dbo].[Prices].[Prices]
> FROM [dbo].[Prices]
> WHERE [dbo].[numbers].[Dates]=[dbo].[Prices].[Dates]
> )
> WHERE EXISTS
> (
> (SELECT *
> FROM [dbo].[Prices]
> WHERE [dbo].[numbers].[Dates]=[dbo].[Prices].[Dates]
> )
>
> By the way, what are these floating-point "dates" ?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "dl" <d@.l.com> wrote in message
> news:eSa%236NMqFHA.1272@.TK2MSFTNGP11.phx.gbl...
> alias
> message
> in
>|||On Wed, 24 Aug 2005 17:53:48 +0100, dl wrote:

>Adam
>I get the error 'Server: Msg 170, Level 15, State 1, Line 13
>Line 13: Incorrect syntax near ')'.
Hi dl,
There's a small typo in Adam's message:
should be
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I changed this and got the error message 'Invalid object name 'dbo.Prices'.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:8uapg19u9imvq32ca4a1nc2ujhdq84186o@.
4ax.com...
> On Wed, 24 Aug 2005 17:53:48 +0100, dl wrote:
>
> Hi dl,
> There's a small typo in Adam's message:
>
> should be
>
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

inter DB connections

Ok been a while.
I have one db that uses DBname..Procname to execute a proc in another dB.
works fine.
On the same server I have created some new DB's and tried the same thing.
I get "Can not find the DB on the server"
I created the DB using the ADP upsize
is there a registration that I need to-do?forgot
this is being called from AmericanBridalInterface db
here is the sp call
execute AmercianBridal..SP_LkUpSupplier @.SupplierID
here is what the profiler shows
[AmericanBridalInterface]..sp_procedure_params_rowset N'SP_LkUpSupplier', 1,
N'dbo', NULL
if exists (select * from syscomments where
id=object_id(N'[dbo].[sp_procedure_params_rowset]')) select c.text,
c.encrypted, c.number, xtype=convert(nchar(2), o.xtype), datalength(c.text),
convert(varbinary(8000), c.text) from syscomments c, sysobjects o where
o.id = c.id and c.id = object_id(N'[dbo].[sp_procedure_params_rowset]')
order by c.number, c.colid
select o.name from sysobjects o where o.xtype = N'P' and
(OBJECTPROPERTY(o.id, N'ExecIsStartup') = 1) and (o.name =N'sp_procedure_params_rowset')
"BJ Freeman" <bj_newsgroups@.free-man.net> wrote in message
news:eQ7zEvGWDHA.548@.tk2msftngp13.phx.gbl...
> Ok been a while.
> I have one db that uses DBname..Procname to execute a proc in another dB.
> works fine.
> On the same server I have created some new DB's and tried the same thing.
> I get "Can not find the DB on the server"
> I created the DB using the ADP upsize
> is there a registration that I need to-do?
>|||never mind. went to sysdatabases and got the name from there.
works now.
"BJ Freeman" <bj_newsgroups@.free-man.net> wrote in message
news:eQ7zEvGWDHA.548@.tk2msftngp13.phx.gbl...
> Ok been a while.
> I have one db that uses DBname..Procname to execute a proc in another dB.
> works fine.
> On the same server I have created some new DB's and tried the same thing.
> I get "Can not find the DB on the server"
> I created the DB using the ADP upsize
> is there a registration that I need to-do?
>

Inter Database Security

I have a stored procedure db1.dbo.sp1
this Stored Procedure grabs data from another database. Say db2.dbo.tbl1

If i call the qry1.
how does the security from db2 come in to play? if at all?

For example, If I am SQL Server Authenticated, for DB1 but not for DB2 will the SP fail. etc.If you have security to db1 but not db2, and you run it as yourself, it will fail.

HTH|||Tnx for the info. I picked up some additional tips and was able to confirm your suggestion on our sql servers over here.

Is there anyway to use NT Authentication in the first db. Then from within a stored procedure (in the 1st database), access the 2nd database using the sa account?

I.E. pass a sa login request to the 2nd database.