Pulling data from two tables

Here is my current query
Use Winpayment
GO
SELECT
convert(char(11), retrieval_reference_number) RR,
message_type,
authorization_identification,
convert(char(8), card_acceptor_identification) SN,
convert(char(25), transaction_name) TransactionName,
isnull(convert(char(2), id_code_1), ' ') ID,
convert (char (20), id_number_1)CardNumber,
convert(char(20), time_stamp) Time,
convert(char(2), response_code) RC,
isnull(convert(char(2), host_response_code), '') HRC,
convert(char(20), host_response_string)Message,
convert(char(7), stan) STAN,
convert(char(12), transaction_amount) Amount,
settlement_data
FROM
financial_message (NOLOCK)
WHERE
settlement_batch_number = '787'
and
LEN (card_acceptor_identification) < 6
and
(id_code_1 = 'PL' or id_code_1 = 'WF' or id_code_1 = '00' or
id_code_1 = 'YF')
and
message_type != 0100
ORDER BY time_stamp
All of this data is being pulled from the financial_message table. I want
to add another and statement that says
wp_category_code = '002' which comes from a table called financial_category
Do they need to have a common column in both tables?
Both financial _message and financial category have a column called
retrieval_reference_number
I am looking for a query that will search all those things from financial
message and then pull only data that also have a wp_category_code = '002'
from financial_category. Is there anyway to pull this data from two tables?
Any help would be greatly appreciated. Thanks......
FROM
financial_message FM (NOLOCK)
INNER JOIN financial_category FC
ON FM.retrieval_reference_number = FC.retrieval_reference_number
AND FC.wp_category_code = '002'
WHERE
....
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"tarheels4025" <tarheels4025@.discussions.microsoft.com> wrote in message
news:F2EA0A50-DED7-4D79-A068-AE734D090F6B@.microsoft.com...
> Here is my current query
> Use Winpayment
> GO
> SELECT
> convert(char(11), retrieval_reference_number) RR,
> message_type,
> authorization_identification,
> convert(char(8), card_acceptor_identification) SN,
> convert(char(25), transaction_name) TransactionName,
> isnull(convert(char(2), id_code_1), ' ') ID,
> convert (char (20), id_number_1)CardNumber,
> convert(char(20), time_stamp) Time,
> convert(char(2), response_code) RC,
> isnull(convert(char(2), host_response_code), '') HRC,
> convert(char(20), host_response_string)Message,
> convert(char(7), stan) STAN,
> convert(char(12), transaction_amount) Amount,
> settlement_data
>
> FROM
> financial_message (NOLOCK)
> WHERE
> settlement_batch_number = '787'
> and
> LEN (card_acceptor_identification) < 6
> and
> (id_code_1 = 'PL' or id_code_1 = 'WF' or id_code_1 = '00' or
> id_code_1 = 'YF')
> and
> message_type != 0100
>
> ORDER BY time_stamp
> All of this data is being pulled from the financial_message table. I want
> to add another and statement that says
> wp_category_code = '002' which comes from a table called
> financial_category
> Do they need to have a common column in both tables?
> Both financial _message and financial category have a column called
> retrieval_reference_number
> I am looking for a query that will search all those things from financial
> message and then pull only data that also have a wp_category_code = '002'
> from financial_category. Is there anyway to pull this data from two
> tables?
> Any help would be greatly appreciated. Thanks.|||I have an example from the one steetlement_batch_number and it isn't pulling
it with this query. Do you know why this might be? Thanks.
"Roji. P. Thomas" wrote:

> .....
> FROM
> financial_message FM (NOLOCK)
> INNER JOIN financial_category FC
> ON FM.retrieval_reference_number = FC.retrieval_reference_number
> AND FC.wp_category_code = '002'
> WHERE
> .....
>
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "tarheels4025" <tarheels4025@.discussions.microsoft.com> wrote in message
> news:F2EA0A50-DED7-4D79-A068-AE734D090F6B@.microsoft.com...
>
>

Publish Subscription replication - Side effects?

Hi Guys,
I've been given the task of creating a disaster recovery "replica" of a
production SQL server and keeping it current.
(SQL 2000)
At first we thought of using scripts to do a backup/restore method.
But a couple of the databases are upwards of a hundred gigabytes...
A bit hard on drive space.
I thought a better method would be to replicate the databases using the
Publish and "push subscriptions".
I'm not very knowledgeable about SQL management, However with the help of
Google and a little time,
I mamnaged to make it work experimentally with the Northwind database.
But that's tiny compared to the live DBs
My question is:
Am I likely to see any 'unforseen' side effects of replicating by this
method?
Thanks,
Jim
To keep maximum performance on your main SQL Server I would consider
"pulling" the transactionally replicated data.
This article from Microsoft has alot of great tips on how to maximize
replication performance.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/tranrepl.mspx
If you have money for a nice SAN unit you can check out Split Mirroring.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/spltmirr.mspx

/*
Warren Brunk - MCITP,MCTS,MCDBA
www.techintsolutions.com
*/
"Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
news:epEJFv0QIHA.2376@.TK2MSFTNGP02.phx.gbl...
> Hi Guys,
> I've been given the task of creating a disaster recovery "replica" of a
> production SQL server and keeping it current.
> (SQL 2000)
> At first we thought of using scripts to do a backup/restore method.
> But a couple of the databases are upwards of a hundred gigabytes...
> A bit hard on drive space.
> I thought a better method would be to replicate the databases using the
> Publish and "push subscriptions".
> I'm not very knowledgeable about SQL management, However with the help of
> Google and a little time,
> I mamnaged to make it work experimentally with the Northwind database.
> But that's tiny compared to the live DBs
> My question is:
> Am I likely to see any 'unforseen' side effects of replicating by this
> method?
> Thanks,
> Jim
>
|||Thanks Warren!
I'll give it a go.
Unfortunately the client is 'cheap' and wants to spend as little as
possible.
Getting to be par for the course these days...
"Warren Brunk" <wbrunk@.techintsolutions.com> wrote in message
news:elyRBZ5QIHA.4180@.TK2MSFTNGP06.phx.gbl...
> To keep maximum performance on your main SQL Server I would consider
> "pulling" the transactionally replicated data.
> This article from Microsoft has alot of great tips on how to maximize
> replication performance.
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/tranrepl.mspx
> If you have money for a nice SAN unit you can check out Split Mirroring.
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/spltmirr.mspx
>
> --
> /*
> Warren Brunk - MCITP,MCTS,MCDBA
> www.techintsolutions.com
> */
>
> "Jim Millar" <Jim.Millar_NOSPAM_@.hotmail.com> wrote in message
> news:epEJFv0QIHA.2376@.TK2MSFTNGP02.phx.gbl...
>

Publish scenario - how can this work?

Our current setup has a hand held device, a laptop and head office. The
field reps have the hand held and laptop and need to get and send data back
to head office. Merge Replication seems to be the answer and I was thinking
that the Head Office would publish to the Laptop and the Laptop would then
publish to the Hand Held. But SQL Server 2005 Express can only be a
Subscriber and not a Publisher.
The reason to do this is that only certain rows will be published to the
laptop (say 1 weeks work) and then only certain rows (say 1 or 2 days work)
will be published to the hand held.
Is there a way to accomplish this?
Here's how it breaks down:
Head Office = SQL Server 2005 Standard Edition
Laptop = SQL Server 2005 Express Edition
Hand Held = SQL Server 2005 Compact Edition
Thanks,
Richard.
You might want to look at RDA as the transit mechanism between Express and
the HandHelds.
Otherwise I would replicate from the Standard Edition publisher to the
handhelds.
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Richard Wodabek" <rwodabek@.cogeco.ca> wrote in message
news:uocMysrNIHA.2268@.TK2MSFTNGP02.phx.gbl...
> Our current setup has a hand held device, a laptop and head office. The
> field reps have the hand held and laptop and need to get and send data
> back
> to head office. Merge Replication seems to be the answer and I was
> thinking
> that the Head Office would publish to the Laptop and the Laptop would then
> publish to the Hand Held. But SQL Server 2005 Express can only be a
> Subscriber and not a Publisher.
> The reason to do this is that only certain rows will be published to the
> laptop (say 1 weeks work) and then only certain rows (say 1 or 2 days
> work)
> will be published to the hand held.
> Is there a way to accomplish this?
> Here's how it breaks down:
> Head Office = SQL Server 2005 Standard Edition
> Laptop = SQL Server 2005 Express Edition
> Hand Held = SQL Server 2005 Compact Edition
> Thanks,
> Richard.
>
|||Thanks Hilary, RDA might work for us.
Any chance 2008 Express Edition will allow Publishing?
Richard.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uNmcUmzNIHA.292@.TK2MSFTNGP02.phx.gbl...[vbcol=seagreen]
> You might want to look at RDA as the transit mechanism between Express and
> the HandHelds.
> Otherwise I would replicate from the Standard Edition publisher to the
> handhelds.
> --
> http://www.zetainteractive.com - Shift Happens!
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Richard Wodabek" <rwodabek@.cogeco.ca> wrote in message
> news:uocMysrNIHA.2268@.TK2MSFTNGP02.phx.gbl...
then
>