sql server codes: jobs

Showing posts with label jobs. Show all posts

Monday, March 26, 2012

Help on migrating from SQL7 to SQL2K

Hi,
We just installed a new server with SQL2k (OS win2k) on it. We have an old
SQL7.0 server (OS NT4). It has a few databases, jobs, packages and etc. I
like to transfer and convert all the data from SQL 7.0 to the new SQL 2k
server. Can someone tell me what is the best way to do so?
Thanks,
SarahSG,
Might want to read:
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/sqlugrd.mspx
and
http://support.microsoft.com/default.aspx?scid=kb;en-us;261334
HTH
Jerry
"SG" <sguo@.coopervision.ca> wrote in message
news:eh7GZE3yFHA.3812@.TK2MSFTNGP09.phx.gbl...
> Hi,
> We just installed a new server with SQL2k (OS win2k) on it. We have an old
> SQL7.0 server (OS NT4). It has a few databases, jobs, packages and etc. I
> like to transfer and convert all the data from SQL 7.0 to the new SQL 2k
> server. Can someone tell me what is the best way to do so?
> Thanks,
> Sarah
>
>|||Hi Jerry,
I found that there is a "Copy database wizard" on sql2k, would that be the
best solution in my case?
Thanks,
Sarah
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:OFsm4K3yFHA.2064@.TK2MSFTNGP09.phx.gbl...
> SG,
> Might want to read:
> http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/sqlugrd.mspx
> and
> http://support.microsoft.com/default.aspx?scid=kb;en-us;261334
> HTH
> Jerry
> "SG" <sguo@.coopervision.ca> wrote in message
> news:eh7GZE3yFHA.3812@.TK2MSFTNGP09.phx.gbl...
>> Hi,
>> We just installed a new server with SQL2k (OS win2k) on it. We have an
>> old
>> SQL7.0 server (OS NT4). It has a few databases, jobs, packages and etc. I
>> like to transfer and convert all the data from SQL 7.0 to the new SQL 2k
>> server. Can someone tell me what is the best way to do so?
>> Thanks,
>> Sarah
>>
>|||Do you want to replace the existing SQL Server 7 with SQL Server 2000? If
so then the copy database wizard is probably not the best solution. This
wizard is useful at bringing a database over from an existing 7 install to a
new install of 2000 - easier to test database by database this way while
maintaining the originating install.
HTH
Jerry
"SG" <sguo@.coopervision.ca> wrote in message
news:e4GlMs3yFHA.1168@.TK2MSFTNGP15.phx.gbl...
> Hi Jerry,
> I found that there is a "Copy database wizard" on sql2k, would that be the
> best solution in my case?
> Thanks,
> Sarah
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:OFsm4K3yFHA.2064@.TK2MSFTNGP09.phx.gbl...
>> SG,
>> Might want to read:
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/sqlugrd.mspx
>> and
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;261334
>> HTH
>> Jerry
>> "SG" <sguo@.coopervision.ca> wrote in message
>> news:eh7GZE3yFHA.3812@.TK2MSFTNGP09.phx.gbl...
>> Hi,
>> We just installed a new server with SQL2k (OS win2k) on it. We have an
>> old
>> SQL7.0 server (OS NT4). It has a few databases, jobs, packages and etc.
>> I
>> like to transfer and convert all the data from SQL 7.0 to the new SQL 2k
>> server. Can someone tell me what is the best way to do so?
>> Thanks,
>> Sarah
>>
>>
>sql

Help on migrating from SQL7 to SQL2K

Friday, March 23, 2012

Help on Emails & scheduled Jobs

Hi All,
The name of the Server was changed which in turn gave me the following
error when I tried to delete the jobs
Error 14274: Cannot add, update, or delete a job (or its steps or
schedules) that originated from an MSX server.
The job was not saved.
Knowing that it has to do with the Originator server in the sysjobs
table; I hacked the table ( now I think not a good idea ) and deleted
all the jobs that I wanted to delete and performed the same task on
sysjobservers.
Now although I have accomplished the deletion of the jobs.
But I still get emails ( job failure notifications) for the deleted
jobs exactly at the time they were scheduled.
This is driving me crazy as I do not how to stop these emails .
Any help is appreciated.
Thank youcheck the sysjobsteps and the sysjobschedules tables. Since you have
deleted the entries from the sysjobs table, you will not be able to
tell by job_id.
But if you look into the command and step name (if you have given a
meaningful name when you create the step, that will be easier to spot
out the email notification steps) from the SYSJOBSTEPS table and the
name (if you have given a meaningful name when you schedule the job)
from the SYSJOBSCHEDULES table.
Obviousbly it is not advisable to modify the system tables directly but
since we have a bad start already, so ... find the records (email
notification step and schedule) and delete it from the tables (backup
the the table first).
Mel|||Hi
Thank you for the info.
I did see job steps and job schedules of the deleted jobs in the table.
So I wenty ahead and deleted them .
Now I have the data for only the job ( that I want ).
Hopefully this will stop emails being sent out (notifcation) for
deleted jobs.
Thank you again|||Hi All,
Removing the records from sysjobschedules and sysjobsteps did not help.
I am still receiving emails.
Please advice|||You mentioned the name of a server was changed, was it the target
server or the master server where the job was stored?
I believe that you have deleted the jobs on the MSX server (mentioned
in your post). It may be the target server not yet received the new
set of instruction (do nothing - jobs have deleted). Run the stored
procedure as below to enforce it to happen (on the master job server):
USE msdb
EXEC sp_resync_targetserver 'target server name'
sp_resync_targetserver deletes the current set of instructions for the
target server and posts a new set for the target server to download.
The new set consists of an instruction to delete all multiserver jobs,
followed by an insert for each job currently targeted at the server.
Mel|||If the above doesn't help.
Run this to see what is available for the target servers to download
from the master.
sp_help_downloadlist
To force a target server to poll the master server.
If you make changes to multiserver job definitions outside of SQL
Server Enterprise Manager (which it is in this case), you must post the
changes to the download list so that target servers can download the
updated job again. To ensure that target servers have the most current
job definitions, post an INSERT instruction after you update the
multiserver job:
EXECUTE sp_post_msx_operation 'INSERT', 'JOB', '<job id>'
Check BOL for more details for the above stored procedures.
Mel

Help on Emails & scheduled Jobs

Help on design question

Hi,
I have a fundamental design issue that I would appreciate any assistance on.
I have a DB that is aimed at tracking jobs that come into a department and
the charges associated with these jobs.
Most jobs are handled by that dept, but some need to be outsourced to
external suppliers. All the products and services that are provided by that
dept are stored in a table called tblInternalItems with the primary key bein
g
a field called ItemID. If a job requires the use of an external supplier, I
store that info in a table called tblExternalItems with the primary key bein
g
a field called ItemID. Because this info needs to be accounted for and
accounts notified at the end of each month on what we owe the suppliers, thi
s
seems to make sense and the final figures easy to calculate.
To keep a track of what customers have had, I have a table called tblJobItem
s.
Now, the big problem comes in with the fact that a customer can have an item
that is provided by my dept, or an item provided by an external supplier.
So, I have a field here called ItemID, that being the foreign key between th
e
2 tables. Now that's the big question - I don't think I can enforce good
referential rules in this type of situation where the required value can com
e
from 2 different tables.
There are 2 solutions I can think of. Introduce another field in the
tblJobItems table to track external supplier items, or merge the two tables
into one.
I am leaning towards a single table with just an additional field called
SupplierID to track any jobs that have been outsourced.
That would then make referential integrity easily enforcable.
That's what i suspect but would appreciate input to confirm my thoughts
before I go ahead and change things.
Many thanks in advance for any assistance
A confused MarekI would put all the items in a single table (because what you're really
talking about, from a data design perspective, is a single entity). Then
you'd just have to add another column to that table that indicates the
source of the work (initially internal or external but it could be expanded
to be a supplierID where the internal department is just one of those
suppliers - might come in handy later on down the track and your accounts
dept might decide they'd like cost breakdowns by supplier). It makes the
allocation of unique itemIDs much easier when you don't have to co-ordinate
between 2 different tables, solves your DRI problem and querying the items
also becomes easier IMHO.
That's my 2c worth. HTH.
Cheers,
Mike
"Marek" <Marek@.discussions.microsoft.com> wrote in message
news:4991B633-8289-4971-A1F8-82A8C0ED4743@.microsoft.com...
> Hi,
> I have a fundamental design issue that I would appreciate any assistance
> on.
> I have a DB that is aimed at tracking jobs that come into a department and
> the charges associated with these jobs.
> Most jobs are handled by that dept, but some need to be outsourced to
> external suppliers. All the products and services that are provided by
> that
> dept are stored in a table called tblInternalItems with the primary key
> being
> a field called ItemID. If a job requires the use of an external supplier,
> I
> store that info in a table called tblExternalItems with the primary key
> being
> a field called ItemID. Because this info needs to be accounted for and
> accounts notified at the end of each month on what we owe the suppliers,
> this
> seems to make sense and the final figures easy to calculate.
> To keep a track of what customers have had, I have a table called
> tblJobItems.
> Now, the big problem comes in with the fact that a customer can have an
> item
> that is provided by my dept, or an item provided by an external supplier.
> So, I have a field here called ItemID, that being the foreign key between
> the
> 2 tables. Now that's the big question - I don't think I can enforce good
> referential rules in this type of situation where the required value can
> come
> from 2 different tables.
> There are 2 solutions I can think of. Introduce another field in the
> tblJobItems table to track external supplier items, or merge the two
> tables
> into one.
>
> I am leaning towards a single table with just an additional field called
> SupplierID to track any jobs that have been outsourced.
> That would then make referential integrity easily enforcable.
> That's what i suspect but would appreciate input to confirm my thoughts
> before I go ahead and change things.
>
> --
> Many thanks in advance for any assistance
> A confused Marek|||Thanks for the swift response Mike. Confirms my thoughts too so will swiftl
y
change my design.
Marek
"Mike Hodgson" wrote:

> I would put all the items in a single table (because what you're really
> talking about, from a data design perspective, is a single entity). Then
> you'd just have to add another column to that table that indicates the
> source of the work (initially internal or external but it could be expande
d
> to be a supplierID where the internal department is just one of those
> suppliers - might come in handy later on down the track and your accounts
> dept might decide they'd like cost breakdowns by supplier). It makes the
> allocation of unique itemIDs much easier when you don't have to co-ordinat
e
> between 2 different tables, solves your DRI problem and querying the items
> also becomes easier IMHO.
> That's my 2c worth. HTH.
> --
> Cheers,
> Mike
> "Marek" <Marek@.discussions.microsoft.com> wrote in message
> news:4991B633-8289-4971-A1F8-82A8C0ED4743@.microsoft.com...
>
>|||Mike suggests a much more scalable design... In your first design if there
were another kind of thing, you'd have to create yet another table for
it...Now you can simply add a new row with new, different type field value..
Good job!
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Marek" <Marek@.discussions.microsoft.com> wrote in message
news:4991B633-8289-4971-A1F8-82A8C0ED4743@.microsoft.com...
> Hi,
> I have a fundamental design issue that I would appreciate any assistance
on.
> I have a DB that is aimed at tracking jobs that come into a department and
> the charges associated with these jobs.
> Most jobs are handled by that dept, but some need to be outsourced to
> external suppliers. All the products and services that are provided by
that
> dept are stored in a table called tblInternalItems with the primary key
being
> a field called ItemID. If a job requires the use of an external supplier,
I
> store that info in a table called tblExternalItems with the primary key
being
> a field called ItemID. Because this info needs to be accounted for and
> accounts notified at the end of each month on what we owe the suppliers,
this
> seems to make sense and the final figures easy to calculate.
> To keep a track of what customers have had, I have a table called
tblJobItems.
> Now, the big problem comes in with the fact that a customer can have an
item
> that is provided by my dept, or an item provided by an external supplier.
> So, I have a field here called ItemID, that being the foreign key between
the
> 2 tables. Now that's the big question - I don't think I can enforce good
> referential rules in this type of situation where the required value can
come
> from 2 different tables.
> There are 2 solutions I can think of. Introduce another field in the
> tblJobItems table to track external supplier items, or merge the two
tables
> into one.
>
> I am leaning towards a single table with just an additional field called
> SupplierID to track any jobs that have been outsourced.
> That would then make referential integrity easily enforcable.
> That's what i suspect but would appreciate input to confirm my thoughts
> before I go ahead and change things.
>
> --
> Many thanks in advance for any assistance
> A confused Mareksql

Help on design question

Hi,
I have a fundamental design issue that I would appreciate any assistance on.
I have a DB that is aimed at tracking jobs that come into a department and
the charges associated with these jobs.
Most jobs are handled by that dept, but some need to be outsourced to
external suppliers. All the products and services that are provided by that
dept are stored in a table called tblInternalItems with the primary key being
a field called ItemID. If a job requires the use of an external supplier, I
store that info in a table called tblExternalItems with the primary key being
a field called ItemID. Because this info needs to be accounted for and
accounts notified at the end of each month on what we owe the suppliers, this
seems to make sense and the final figures easy to calculate.
To keep a track of what customers have had, I have a table called tblJobItems.
Now, the big problem comes in with the fact that a customer can have an item
that is provided by my dept, or an item provided by an external supplier.
So, I have a field here called ItemID, that being the foreign key between the
2 tables. Now that's the big question - I don't think I can enforce good
referential rules in this type of situation where the required value can come
from 2 different tables.
There are 2 solutions I can think of. Introduce another field in the
tblJobItems table to track external supplier items, or merge the two tables
into one.
I am leaning towards a single table with just an additional field called
SupplierID to track any jobs that have been outsourced.
That would then make referential integrity easily enforcable.
That's what i suspect but would appreciate input to confirm my thoughts
before I go ahead and change things.
--
Many thanks in advance for any assistance
A confused MarekI would put all the items in a single table (because what you're really
talking about, from a data design perspective, is a single entity). Then
you'd just have to add another column to that table that indicates the
source of the work (initially internal or external but it could be expanded
to be a supplierID where the internal department is just one of those
suppliers - might come in handy later on down the track and your accounts
dept might decide they'd like cost breakdowns by supplier). It makes the
allocation of unique itemIDs much easier when you don't have to co-ordinate
between 2 different tables, solves your DRI problem and querying the items
also becomes easier IMHO.
That's my 2c worth. HTH.
--
Cheers,
Mike
"Marek" <Marek@.discussions.microsoft.com> wrote in message
news:4991B633-8289-4971-A1F8-82A8C0ED4743@.microsoft.com...
> Hi,
> I have a fundamental design issue that I would appreciate any assistance
> on.
> I have a DB that is aimed at tracking jobs that come into a department and
> the charges associated with these jobs.
> Most jobs are handled by that dept, but some need to be outsourced to
> external suppliers. All the products and services that are provided by
> that
> dept are stored in a table called tblInternalItems with the primary key
> being
> a field called ItemID. If a job requires the use of an external supplier,
> I
> store that info in a table called tblExternalItems with the primary key
> being
> a field called ItemID. Because this info needs to be accounted for and
> accounts notified at the end of each month on what we owe the suppliers,
> this
> seems to make sense and the final figures easy to calculate.
> To keep a track of what customers have had, I have a table called
> tblJobItems.
> Now, the big problem comes in with the fact that a customer can have an
> item
> that is provided by my dept, or an item provided by an external supplier.
> So, I have a field here called ItemID, that being the foreign key between
> the
> 2 tables. Now that's the big question - I don't think I can enforce good
> referential rules in this type of situation where the required value can
> come
> from 2 different tables.
> There are 2 solutions I can think of. Introduce another field in the
> tblJobItems table to track external supplier items, or merge the two
> tables
> into one.
>
> I am leaning towards a single table with just an additional field called
> SupplierID to track any jobs that have been outsourced.
> That would then make referential integrity easily enforcable.
> That's what i suspect but would appreciate input to confirm my thoughts
> before I go ahead and change things.
>
> --
> Many thanks in advance for any assistance
> A confused Marek|||Thanks for the swift response Mike. Confirms my thoughts too so will swiftly
change my design.
Marek
"Mike Hodgson" wrote:
> I would put all the items in a single table (because what you're really
> talking about, from a data design perspective, is a single entity). Then
> you'd just have to add another column to that table that indicates the
> source of the work (initially internal or external but it could be expanded
> to be a supplierID where the internal department is just one of those
> suppliers - might come in handy later on down the track and your accounts
> dept might decide they'd like cost breakdowns by supplier). It makes the
> allocation of unique itemIDs much easier when you don't have to co-ordinate
> between 2 different tables, solves your DRI problem and querying the items
> also becomes easier IMHO.
> That's my 2c worth. HTH.
> --
> Cheers,
> Mike
> "Marek" <Marek@.discussions.microsoft.com> wrote in message
> news:4991B633-8289-4971-A1F8-82A8C0ED4743@.microsoft.com...
> > Hi,
> >
> > I have a fundamental design issue that I would appreciate any assistance
> > on.
> > I have a DB that is aimed at tracking jobs that come into a department and
> > the charges associated with these jobs.
> >
> > Most jobs are handled by that dept, but some need to be outsourced to
> > external suppliers. All the products and services that are provided by
> > that
> > dept are stored in a table called tblInternalItems with the primary key
> > being
> > a field called ItemID. If a job requires the use of an external supplier,
> > I
> > store that info in a table called tblExternalItems with the primary key
> > being
> > a field called ItemID. Because this info needs to be accounted for and
> > accounts notified at the end of each month on what we owe the suppliers,
> > this
> > seems to make sense and the final figures easy to calculate.
> >
> > To keep a track of what customers have had, I have a table called
> > tblJobItems.
> >
> > Now, the big problem comes in with the fact that a customer can have an
> > item
> > that is provided by my dept, or an item provided by an external supplier.
> > So, I have a field here called ItemID, that being the foreign key between
> > the
> > 2 tables. Now that's the big question - I don't think I can enforce good
> > referential rules in this type of situation where the required value can
> > come
> > from 2 different tables.
> >
> > There are 2 solutions I can think of. Introduce another field in the
> > tblJobItems table to track external supplier items, or merge the two
> > tables
> > into one.
> >
> >
> > I am leaning towards a single table with just an additional field called
> > SupplierID to track any jobs that have been outsourced.
> >
> > That would then make referential integrity easily enforcable.
> >
> > That's what i suspect but would appreciate input to confirm my thoughts
> > before I go ahead and change things.
> >
> >
> >
> > --
> > Many thanks in advance for any assistance
> > A confused Marek
>
>|||Mike suggests a much more scalable design... In your first design if there
were another kind of thing, you'd have to create yet another table for
it...Now you can simply add a new row with new, different type field value..
Good job!
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Marek" <Marek@.discussions.microsoft.com> wrote in message
news:4991B633-8289-4971-A1F8-82A8C0ED4743@.microsoft.com...
> Hi,
> I have a fundamental design issue that I would appreciate any assistance
on.
> I have a DB that is aimed at tracking jobs that come into a department and
> the charges associated with these jobs.
> Most jobs are handled by that dept, but some need to be outsourced to
> external suppliers. All the products and services that are provided by
that
> dept are stored in a table called tblInternalItems with the primary key
being
> a field called ItemID. If a job requires the use of an external supplier,
I
> store that info in a table called tblExternalItems with the primary key
being
> a field called ItemID. Because this info needs to be accounted for and
> accounts notified at the end of each month on what we owe the suppliers,
this
> seems to make sense and the final figures easy to calculate.
> To keep a track of what customers have had, I have a table called
tblJobItems.
> Now, the big problem comes in with the fact that a customer can have an
item
> that is provided by my dept, or an item provided by an external supplier.
> So, I have a field here called ItemID, that being the foreign key between
the
> 2 tables. Now that's the big question - I don't think I can enforce good
> referential rules in this type of situation where the required value can
come
> from 2 different tables.
> There are 2 solutions I can think of. Introduce another field in the
> tblJobItems table to track external supplier items, or merge the two
tables
> into one.
>
> I am leaning towards a single table with just an additional field called
> SupplierID to track any jobs that have been outsourced.
> That would then make referential integrity easily enforcable.
> That's what i suspect but would appreciate input to confirm my thoughts
> before I go ahead and change things.
>
> --
> Many thanks in advance for any assistance
> A confused Marek

Help on design question

Hi,
I have a fundamental design issue that I would appreciate any assistance on.
I have a DB that is aimed at tracking jobs that come into a department and
the charges associated with these jobs.
Most jobs are handled by that dept, but some need to be outsourced to
external suppliers. All the products and services that are provided by that
dept are stored in a table called tblInternalItems with the primary key being
a field called ItemID. If a job requires the use of an external supplier, I
store that info in a table called tblExternalItems with the primary key being
a field called ItemID. Because this info needs to be accounted for and
accounts notified at the end of each month on what we owe the suppliers, this
seems to make sense and the final figures easy to calculate.
To keep a track of what customers have had, I have a table called tblJobItems.
Now, the big problem comes in with the fact that a customer can have an item
that is provided by my dept, or an item provided by an external supplier.
So, I have a field here called ItemID, that being the foreign key between the
2 tables. Now that's the big question - I don't think I can enforce good
referential rules in this type of situation where the required value can come
from 2 different tables.
There are 2 solutions I can think of. Introduce another field in the
tblJobItems table to track external supplier items, or merge the two tables
into one.
I am leaning towards a single table with just an additional field called
SupplierID to track any jobs that have been outsourced.
That would then make referential integrity easily enforcable.
That's what i suspect but would appreciate input to confirm my thoughts
before I go ahead and change things.
Many thanks in advance for any assistance
A confused Marek
I would put all the items in a single table (because what you're really
talking about, from a data design perspective, is a single entity). Then
you'd just have to add another column to that table that indicates the
source of the work (initially internal or external but it could be expanded
to be a supplierID where the internal department is just one of those
suppliers - might come in handy later on down the track and your accounts
dept might decide they'd like cost breakdowns by supplier). It makes the
allocation of unique itemIDs much easier when you don't have to co-ordinate
between 2 different tables, solves your DRI problem and querying the items
also becomes easier IMHO.
That's my 2c worth. HTH.
Cheers,
Mike
"Marek" <Marek@.discussions.microsoft.com> wrote in message
news:4991B633-8289-4971-A1F8-82A8C0ED4743@.microsoft.com...
> Hi,
> I have a fundamental design issue that I would appreciate any assistance
> on.
> I have a DB that is aimed at tracking jobs that come into a department and
> the charges associated with these jobs.
> Most jobs are handled by that dept, but some need to be outsourced to
> external suppliers. All the products and services that are provided by
> that
> dept are stored in a table called tblInternalItems with the primary key
> being
> a field called ItemID. If a job requires the use of an external supplier,
> I
> store that info in a table called tblExternalItems with the primary key
> being
> a field called ItemID. Because this info needs to be accounted for and
> accounts notified at the end of each month on what we owe the suppliers,
> this
> seems to make sense and the final figures easy to calculate.
> To keep a track of what customers have had, I have a table called
> tblJobItems.
> Now, the big problem comes in with the fact that a customer can have an
> item
> that is provided by my dept, or an item provided by an external supplier.
> So, I have a field here called ItemID, that being the foreign key between
> the
> 2 tables. Now that's the big question - I don't think I can enforce good
> referential rules in this type of situation where the required value can
> come
> from 2 different tables.
> There are 2 solutions I can think of. Introduce another field in the
> tblJobItems table to track external supplier items, or merge the two
> tables
> into one.
>
> I am leaning towards a single table with just an additional field called
> SupplierID to track any jobs that have been outsourced.
> That would then make referential integrity easily enforcable.
> That's what i suspect but would appreciate input to confirm my thoughts
> before I go ahead and change things.
>
> --
> Many thanks in advance for any assistance
> A confused Marek
|||Thanks for the swift response Mike. Confirms my thoughts too so will swiftly
change my design.
Marek
"Mike Hodgson" wrote:

> I would put all the items in a single table (because what you're really
> talking about, from a data design perspective, is a single entity). Then
> you'd just have to add another column to that table that indicates the
> source of the work (initially internal or external but it could be expanded
> to be a supplierID where the internal department is just one of those
> suppliers - might come in handy later on down the track and your accounts
> dept might decide they'd like cost breakdowns by supplier). It makes the
> allocation of unique itemIDs much easier when you don't have to co-ordinate
> between 2 different tables, solves your DRI problem and querying the items
> also becomes easier IMHO.
> That's my 2c worth. HTH.
> --
> Cheers,
> Mike
> "Marek" <Marek@.discussions.microsoft.com> wrote in message
> news:4991B633-8289-4971-A1F8-82A8C0ED4743@.microsoft.com...
>
>
|||Mike suggests a much more scalable design... In your first design if there
were another kind of thing, you'd have to create yet another table for
it...Now you can simply add a new row with new, different type field value..
Good job!
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Marek" <Marek@.discussions.microsoft.com> wrote in message
news:4991B633-8289-4971-A1F8-82A8C0ED4743@.microsoft.com...
> Hi,
> I have a fundamental design issue that I would appreciate any assistance
on.
> I have a DB that is aimed at tracking jobs that come into a department and
> the charges associated with these jobs.
> Most jobs are handled by that dept, but some need to be outsourced to
> external suppliers. All the products and services that are provided by
that
> dept are stored in a table called tblInternalItems with the primary key
being
> a field called ItemID. If a job requires the use of an external supplier,
I
> store that info in a table called tblExternalItems with the primary key
being
> a field called ItemID. Because this info needs to be accounted for and
> accounts notified at the end of each month on what we owe the suppliers,
this
> seems to make sense and the final figures easy to calculate.
> To keep a track of what customers have had, I have a table called
tblJobItems.
> Now, the big problem comes in with the fact that a customer can have an
item
> that is provided by my dept, or an item provided by an external supplier.
> So, I have a field here called ItemID, that being the foreign key between
the
> 2 tables. Now that's the big question - I don't think I can enforce good
> referential rules in this type of situation where the required value can
come
> from 2 different tables.
> There are 2 solutions I can think of. Introduce another field in the
> tblJobItems table to track external supplier items, or merge the two
tables
> into one.
>
> I am leaning towards a single table with just an additional field called
> SupplierID to track any jobs that have been outsourced.
> That would then make referential integrity easily enforcable.
> That's what i suspect but would appreciate input to confirm my thoughts
> before I go ahead and change things.
>
> --
> Many thanks in advance for any assistance
> A confused Marek

Monday, March 26, 2012

Help on migrating from SQL7 to SQL2K

Help on migrating from SQL7 to SQL2K

Help on migrating from SQL7 to SQL2K

Friday, March 23, 2012

Help on Emails & scheduled Jobs

Help on Emails & scheduled Jobs

Help on design question

Help on design question

Help on design question

sql server codes

Blog Archive

About Me