Help on linked server

Hi All,
I need your expertise comments for using linked server in WAN
environment. I need to suggest it to my client.
Here is the scenario:
My client is having 20 retail shops across US. He got a Central office
located in OHIO state.
All the stores are connected through WAN.
Now we are developing a transaction system for store's. All stores are
going to use MSDE.
One Enterprise SQL server is going to be installed in central office.
In this scenario will linked server work for distributing transaction
across stores?.
For example,
The gift card transaction happened in one store should be immediately
updated (or in frequent interval)
to central server. This enables other store to pickup the transaction
on need basis.
I used linked server in LAN environment and it works. But I could not
think of WAN environment.
What ports need to be opened for these SQL transaction?
What environement is required to enable linked server (including
security)?
How do I create an environment in my Office itself, so that I will be
confirtable on suggesting it or any other way to acheive the immediate
update and retrieval (other than replication).
Please help.
With thanks,
VishHi
Your best bet would be SQL Server Replication. The central SQL Server would
pull data from the MSDE instances.
Have a look at the links off
http://www.microsoft.com/technet/prodtechnol/sql/2000/technologies/replictn.mspx
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Vish" <vichumca@.gmail.com> wrote in message
news:1127131645.102228.245660@.f14g2000cwb.googlegroups.com...
> Hi All,
> I need your expertise comments for using linked server in WAN
> environment. I need to suggest it to my client.
> Here is the scenario:
> My client is having 20 retail shops across US. He got a Central office
> located in OHIO state.
> All the stores are connected through WAN.
> Now we are developing a transaction system for store's. All stores are
> going to use MSDE.
> One Enterprise SQL server is going to be installed in central office.
> In this scenario will linked server work for distributing transaction
> across stores?.
> For example,
> The gift card transaction happened in one store should be immediately
> updated (or in frequent interval)
> to central server. This enables other store to pickup the transaction
> on need basis.
> I used linked server in LAN environment and it works. But I could not
> think of WAN environment.
> What ports need to be opened for these SQL transaction?
> What environement is required to enable linked server (including
> security)?
> How do I create an environment in my Office itself, so that I will be
> confirtable on suggesting it or any other way to acheive the immediate
> update and retrieval (other than replication).
>
> Please help.
> With thanks,
> Vish
>

Help on Database Design

I would like to design some tables for offices. In the scenario, there are so many offices which are categorized by different levels. Eg. National level, State level, District level etc... The design must satisfy the future need of adding another intermediate level (say, Zone level in between National and State), without any redesign of database. So how could I materialze this?In one table you can create a column with CHAR(5) that specifies the category of level that can be used as a master table to specify these categories with a future addition of sections.

Help on Agent Login Change

Hello,
I need to change the SQLAgent start up login in roder to begin a
replication scenario.
I have been using "system account" byt now wish to change it to
another one I set up - Publish. Publish is an administartor account,
is in the Logins section, etc.
When I changed the SQLAgent and tried to restart it I received an
error as follow -
Could not start the SQLServerAgent service on Local Computer.
The service did not return an error. This could be an internal Windows
error or and internal service error.
If the problem persists, contact your system administrator.
When I tried to go back to the system account I got the same error and
now cannot start the SQLAgent.
Anyone have any suggestions on how to resolve this ?
THANK YOU in advance !
Jon SpartanWhat do you have in SQLAgent.out file?
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Jon Spartan" <jonu@.ixtech.net> wrote in message
news:23ltqvsp52v2gm1n22nflf100g41o6ddrv@.4ax.com...
> Hello,
> I need to change the SQLAgent start up login in roder to begin a
> replication scenario.
> I have been using "system account" byt now wish to change it to
> another one I set up - Publish. Publish is an administartor account,
> is in the Logins section, etc.
> When I changed the SQLAgent and tried to restart it I received an
> error as follow -
> Could not start the SQLServerAgent service on Local Computer.
> The service did not return an error. This could be an internal Windows
> error or and internal service error.
> If the problem persists, contact your system administrator.
> When I tried to go back to the system account I got the same error and
> now cannot start the SQLAgent.
> Anyone have any suggestions on how to resolve this ?
> THANK YOU in advance !
> Jon Spartan|||Contents of SQLAgent.Out are as follows -
11/10/2003 8:56:32 AM - ? [129] SQLServerAgent starting under Windows
NT service control
11/10/2003 8:56:37 AM - ! [298] SQLServer Error: 18456, Login failed
for user 'NT AUTHORITY\SYSTEM'. [SQLSTATE 28000]
11/10/2003 8:56:37 AM - ! [000] Unable to connect to server;
SQLServerAgent cannot start
11/10/2003 8:57:01 AM - ? [098] SQLServerAgent terminated (normally)
On Mon, 10 Nov 2003 00:00:06 GMT, Jon Spartan <jonu@.ixtech.net> wrote:
>Hello,
>I need to change the SQLAgent start up login in roder to begin a
>replication scenario.
>I have been using "system account" byt now wish to change it to
>another one I set up - Publish. Publish is an administartor account,
>is in the Logins section, etc.
>When I changed the SQLAgent and tried to restart it I received an
>error as follow -
>Could not start the SQLServerAgent service on Local Computer.
>The service did not return an error. This could be an internal Windows
>error or and internal service error.
>If the problem persists, contact your system administrator.
>When I tried to go back to the system account I got the same error and
>now cannot start the SQLAgent.
>Anyone have any suggestions on how to resolve this ?
>THANK YOU in advance !
>Jon Spartan|||That is strange. The error message suggests that Agent tries to login as
LocalSystem, and it cannot do that because that isn't added as a Windows
Login. LocaSystem should be there, and also you mention that you aren't
using LocalSystem. Sorry, beats me...
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Jon Spartan" <jonu@.ixtech.net> wrote in message
news:jhbvqv08sbaehnl7lsrvk1ooa59090mpjf@.4ax.com...
> Contents of SQLAgent.Out are as follows -
> 11/10/2003 8:56:32 AM - ? [129] SQLServerAgent starting under Windows
> NT service control
> 11/10/2003 8:56:37 AM - ! [298] SQLServer Error: 18456, Login failed
> for user 'NT AUTHORITY\SYSTEM'. [SQLSTATE 28000]
> 11/10/2003 8:56:37 AM - ! [000] Unable to connect to server;
> SQLServerAgent cannot start
> 11/10/2003 8:57:01 AM - ? [098] SQLServerAgent terminated (normally)
>
> On Mon, 10 Nov 2003 00:00:06 GMT, Jon Spartan <jonu@.ixtech.net> wrote:
> >Hello,
> >
> >I need to change the SQLAgent start up login in roder to begin a
> >replication scenario.
> >
> >I have been using "system account" byt now wish to change it to
> >another one I set up - Publish. Publish is an administartor account,
> >is in the Logins section, etc.
> >
> >When I changed the SQLAgent and tried to restart it I received an
> >error as follow -
> >
> >Could not start the SQLServerAgent service on Local Computer.
> >The service did not return an error. This could be an internal Windows
> >error or and internal service error.
> >If the problem persists, contact your system administrator.
> >
> >When I tried to go back to the system account I got the same error and
> >now cannot start the SQLAgent.
> >
> >Anyone have any suggestions on how to resolve this ?
> >
> >THANK YOU in advance !
> >
> >Jon Spartan
>

Help Normalizing an Address

While working with a database that contains subcontractors I came
across an interesting scenario with the respect to their addresses.
Currently all fields for the required data are in the one table and is
causing some problems with regards to updating the redundant info
across multiple entries.
Each sub-contractor can have one or multiple addresses. Main Address,
Remit To Address, Correspondance Address, etc...
Simple enough, but...
Some sub-contractors sell their receivables to a factoring company, so
in addition to typical address info, we must also have phone, fax,
contact info, etc., on hand in these situations.
Also, of the sub contractors that use a factoring company, (about 10%)
use the same factoring company. Redundant info.
Also, some sub contractors are a satilte location of a parent company
and althought their phyical address is different, their 'remit to' is
actually their parent co's remit to address.
Main (physical) addresses are important because we link their zip code
to lat/long database to find sub contractors closest to the particular
area of the country where a job needs to be done.
Right now we just have remit to fields in every sub contractor record.
When a Factoring company changes it's address, though, it can be a pain
to update.
Any suggestions?Without knowing how many records you are talking about, I think it would be
easiest to have one address table with a flag column indicating the type of
address and a fk column pointing back to the sub contractor. From an
administration standpoint, this would be easiest.
If you really feel the need to normalize out, then create a table with all
the unique addresses and use their UID as a Fk back to the sub contractor
table. This way when a new address comes in or an existing one is modified,
you can drop the old one and add the new one, while updating subs address
pointers.
"Clyde Venhause" wrote:

> While working with a database that contains subcontractors I came
> across an interesting scenario with the respect to their addresses.
> Currently all fields for the required data are in the one table and is
> causing some problems with regards to updating the redundant info
> across multiple entries.
> Each sub-contractor can have one or multiple addresses. Main Address,
> Remit To Address, Correspondance Address, etc...
> Simple enough, but...
> Some sub-contractors sell their receivables to a factoring company, so
> in addition to typical address info, we must also have phone, fax,
> contact info, etc., on hand in these situations.
> Also, of the sub contractors that use a factoring company, (about 10%)
> use the same factoring company. Redundant info.
> Also, some sub contractors are a satilte location of a parent company
> and althought their phyical address is different, their 'remit to' is
> actually their parent co's remit to address.
> Main (physical) addresses are important because we link their zip code
> to lat/long database to find sub contractors closest to the particular
> area of the country where a job needs to be done.
> Right now we just have remit to fields in every sub contractor record.
> When a Factoring company changes it's address, though, it can be a pain
> to update.
> Any suggestions?
>|||--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
It seems to be a basic Many-to-Many consideration. At first thought I'd
do it like this:
CREATE TABLE ContractorAddresses (
contractor_id integer not null references Contractors ,
address_id integer not null references Addresses ,
address_type_code integer not null references AddressTypes ,
CONSTRAINT PK_ContractorAddresses
PRIMARY KEY (contractor_id, address_id, address_type_code)
)
The table Address will not have a ContractorID, 'cuz the above table
provides that info. The table AddressTypes will hold data like this:
address_type_code description
-- --
1 Remittance
2 Correspondence
3 Main Office
.. etc. ...
The communication info should be in a different table, much like the
ContractorAddresses table.
CREATE TABLE ContactorContacts (
contractor_id integer not null references Contractors ,
contact_id integer not null references Contacts ,
contact_type_code integer not null references ContactTypes ,
CONSTRAINT PK_ContractorContacts
PRIMARY KEY (contractor_id, contact_id, contact_type_code)
)
The ContactTypes table would have data like this:
contact_type_code description
-- --
1 Purchaser
2 Inspector
3 Lawyer
.. etc. ...
The Contacts table will hold info on who to contact:
CREATE TABLE Contacts (
contact_id integer not null UNIQUE ,
person varchar(30) not null PRIMARY KEY,
.. other columns ? ... -- may change PK requirement
)
A phones table for the contacts:
CREATE TABLE ContactPhones (
contact_id integer not null references Contacts ,
phone_type_code integer not null references PhoneTypes ,
phone_nr char(10) not null,
CONSTRAINT PK_ContactPhones
PRIMARY KEY (contact_id, phone_type_code, phone_nr)
)
The PhoneTypes table would have data like this:
phone_type_code description
-- --
1 Fax
2 Office
3 Purchasing Office
.. etc. ...
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQmKiW4echKqOuFEgEQKgUACdF8x/K9jakhVTRe6okJI7qtFevNoAn0ky
DEbKy122rQJN1FlCOcaXp9jq
=gg/j
--END PGP SIGNATURE--
Clyde Venhause wrote:
> While working with a database that contains subcontractors I came
> across an interesting scenario with the respect to their addresses.
> Currently all fields for the required data are in the one table and is
> causing some problems with regards to updating the redundant info
> across multiple entries.
> Each sub-contractor can have one or multiple addresses. Main Address,
> Remit To Address, Correspondance Address, etc...
> Simple enough, but...
> Some sub-contractors sell their receivables to a factoring company, so
> in addition to typical address info, we must also have phone, fax,
> contact info, etc., on hand in these situations.
> Also, of the sub contractors that use a factoring company, (about 10%)
> use the same factoring company. Redundant info.
> Also, some sub contractors are a satilte location of a parent company
> and althought their phyical address is different, their 'remit to' is
> actually their parent co's remit to address.
> Main (physical) addresses are important because we link their zip code
> to lat/long database to find sub contractors closest to the particular
> area of the country where a job needs to be done.
> Right now we just have remit to fields in every sub contractor record.
> When a Factoring company changes it's address, though, it can be a pain
> to update.
> Any suggestions?
>

Help needed for the following scenario

Hi all,
Heres the scenario i want to make a query of
I have four Tables namely: Bug, Test, REQ and REQ_COVER
Bug Schema:
BUG_ID
TEST_REFERENCE
BUG_DESC
BUG_RESPONSIBLE

TEST Schema:
TEST_ID(Relation with BUG.TEST_REFERENCE
TEST_NAME

REQ Schema:
REQ_ID
REQ_NAME

REQ_COVER Schema:
RC_REQ_ID (Relation with REQ.REQ_ID)
RC_TEST_ID (Relation with TEST.TEST_ID)

Now I need to have following data:
Number of BUG_ID by BG_RESPONSIBLE with Number of Distinct TEST_ID and Number of Distinct REQ_ID.
i.e. how many BUG_ID (number of bugs )are assigned to BG_RESPONSIBLE (list of users) and that BUG_ID is assoiciated with how many TEST_ID(TestCases) and those TEST_IDs are associated to how many REQ_ID(Requirements).

Any assistance will be highly appriciatedplease give sample output from the query|||BG_RESPONSIBLE || Count(BUG_ID against BG_RESPONSIBLE) || Count(TEST_ID against BUG_ID for BG_RESPONSIBLE) || Count(REQ_ID against TEST_ID against BUG_ID for BG_RESPONSIBLE)

Ineed the records in above table Columns are separated by || signs.
Actually this is custom Bug tracker. And i need to know the total number of Bugs , total number of test cases they belongs to and the total number of requirements associated with those test cases.. all against each developer (BG_RESPONSIBLE)