Hello, we have scheduled a nightly refresh into an Employees table in a
database, that receives data from Active Directory. AD is the "authority" o
f
who is an active employee and frees us from manual maintenance of the
Employees table in our DB. However, we want to store information on
employees who resign, retire etc. and don't want orphaned records (this is a
training database storing information on what employees take what classes,
with dates and costs). I'd like to set up some kind of append query but
unlike Access, SQL would not let me append duplicate records. The database
has a termination date field also. Any suggestions would be appreciated.
Thanks, Pancho.Do you want only one row per employee, or one for every change in the
employee data?
Are you only updating the termination date from AD, or are you loading all
of the employee data from AD?
Please include DDL and more detail.
(explained here)
http://www.aspfaq.com/etiquette.asp?id=5006
"Pancho" <Pancho@.discussions.microsoft.com> wrote in message
news:4FBC26F2-F086-4734-8A2B-06CC11525F36@.microsoft.com...
> Hello, we have scheduled a nightly refresh into an Employees table in a
> database, that receives data from Active Directory. AD is the "authority"
of
> who is an active employee and frees us from manual maintenance of the
> Employees table in our DB. However, we want to store information on
> employees who resign, retire etc. and don't want orphaned records (this is
a
> training database storing information on what employees take what classes,
> with dates and costs). I'd like to set up some kind of append query but
> unlike Access, SQL would not let me append duplicate records. The
database
> has a termination date field also. Any suggestions would be appreciated.
> Thanks, Pancho.|||Jim,
We only want one row per employee. To the table structure depicted in the
script below I would ideally like to add a date/time column named TermDate.
If empty the employee would be still active.
Thank you for the etiquette link. The programmer kept default length 255's
on varchars. In reality our EmpNumbers are only 3-4 characters, not 50. In
our employee population, we have some employees who leave to go to college
and then return, and we typically preserve their old employee number, and
reactivate it when they return.
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Employees]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Employees]
GO
CREATE TABLE [dbo].[Employees] (
[FullName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[LName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Extension] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EmpNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Email] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Company] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Department] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
"Jim Underwood" wrote:
> Do you want only one row per employee, or one for every change in the
> employee data?
> Are you only updating the termination date from AD, or are you loading all
> of the employee data from AD?
> Please include DDL and more detail.
> (explained here)
> http://www.aspfaq.com/etiquette.asp?id=5006
>
> "Pancho" <Pancho@.discussions.microsoft.com> wrote in message
> news:4FBC26F2-F086-4734-8A2B-06CC11525F36@.microsoft.com...
> of
> a
> database
>
>|||OK, so you need to add a column to your table:
alter table [dbo].[Employees]
add TerminationDate datetime;
Then you need to update this table on a periodic basis? I have not worked
much with AD in SQL Server, but lets assume you are connected and querying
AD as you would a table. We'll call the "table" ADEmpData, and assume it
has EmpNumber and TerminationDate as columns. Also assume EmpNumber can be
duplicated in AD, but it is the PK (or at least unique) in the Employees
table. If you can explain how you are connecting to AD, someone may be able
to provide more complete code.
Update Employees as Emp
set Emp.TerminationDate = (
select top 1 AD.TerminationDate
from ADEmpData as AD
Where AD.EmpNumber = Emp.EmpNumber
order by AD.TerminationDate
-- the above added just to give consistent results with TOP
)
and exists (select 1
from ADEmpData as AD
Where AD.EmpNumber = Emp.EmpNumber)
-- exists used to only attempt to update values if the emp
-- exists in AD
Note that AD may store term date as a string, not a date type, so you may
have to do some conversion here.
"Pancho" <Pancho@.discussions.microsoft.com> wrote in message
news:79A765A9-80AE-4B80-BD4F-106AB1CB636C@.microsoft.com...
> Jim,
> We only want one row per employee. To the table structure depicted in the
> script below I would ideally like to add a date/time column named
TermDate.
> If empty the employee would be still active.
> Thank you for the etiquette link. The programmer kept default length
255's
> on varchars. In reality our EmpNumbers are only 3-4 characters, not 50.
In
> our employee population, we have some employees who leave to go to college
> and then return, and we typically preserve their old employee number, and
> reactivate it when they return.
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Employees]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
> drop table [dbo].[Employees]
> GO
> CREATE TABLE [dbo].[Employees] (
> [FullName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [LName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [FName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Extension] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [EmpNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Email] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Company] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Department] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
> "Jim Underwood" wrote:
>
all
a
"authority"
(this is
classes,
but
appreciated.|||Jim,
This is good information and I appreciate the detail. This will probably
work here. Have a nice w
end! Pancho.
"Jim Underwood" wrote:
> OK, so you need to add a column to your table:
> alter table [dbo].[Employees]
> add TerminationDate datetime;
> Then you need to update this table on a periodic basis? I have not worked
> much with AD in SQL Server, but lets assume you are connected and querying
> AD as you would a table. We'll call the "table" ADEmpData, and assume it
> has EmpNumber and TerminationDate as columns. Also assume EmpNumber can b
e
> duplicated in AD, but it is the PK (or at least unique) in the Employees
> table. If you can explain how you are connecting to AD, someone may be ab
le
> to provide more complete code.
> Update Employees as Emp
> set Emp.TerminationDate = (
> select top 1 AD.TerminationDate
> from ADEmpData as AD
> Where AD.EmpNumber = Emp.EmpNumber
> order by AD.TerminationDate
> -- the above added just to give consistent results with TOP
> )
> and exists (select 1
> from ADEmpData as AD
> Where AD.EmpNumber = Emp.EmpNumber)
> -- exists used to only attempt to update values if the emp
> -- exists in AD
> Note that AD may store term date as a string, not a date type, so you may
> have to do some conversion here.
> "Pancho" <Pancho@.discussions.microsoft.com> wrote in message
> news:79A765A9-80AE-4B80-BD4F-106AB1CB636C@.microsoft.com...
> TermDate.
> 255's
> In
> 1)
> all
> a
> "authority"
> (this is
> classes,
> but
> appreciated.
>
>|||Glad if I could help.
Please remember to post your final solution so others who have a similar
issue will be able to learn from it.
Also, I am interested in seeing how you handle the AD connection. Thus far
I have only done it via VB.Net and have not connected via SQL Server.
"Pancho" <Pancho@.discussions.microsoft.com> wrote in message
news:84DA9C84-4201-4766-9ADC-FD74F3EB5DED@.microsoft.com...
> Jim,
> This is good information and I appreciate the detail. This will probably
> work here. Have a nice w
end! Pancho.
> "Jim Underwood" wrote:
>
worked
querying
it
be
able
may
the
50.
college
and
=
NULL ,
,
NULL ,
the
loading
in
the
on
query|||We use a scheduled DTS package to import a .csv file. The .csv file is
exported nightly using a scheduled .bat file that calls a .vbs script that
performs the export from AD. We're not going to implement the TermDate just
yet but I will post the outcome when we do. Thanks again, P.
"Jim Underwood" wrote:
> Glad if I could help.
> Please remember to post your final solution so others who have a similar
> issue will be able to learn from it.
> Also, I am interested in seeing how you handle the AD connection. Thus fa
r
> I have only done it via VB.Net and have not connected via SQL Server.
>
> "Pancho" <Pancho@.discussions.microsoft.com> wrote in message
> news:84DA9C84-4201-4766-9ADC-FD74F3EB5DED@.microsoft.com...
> worked
> querying
> it
> be
> able
> may
> the
> 50.
> college
> and
> =
> NULL ,
> ,
> NULL ,
> the
> loading
> in
> the
> on
> query
>
>|||You might check out microsoft.public.active.directory.interfaces to see if
you can use a more direct approach. I believe SQL Server allows you to
query AD directly, which could eliminate the need for the VBS, BAT, CSV,
etc. I do not know how much is implemented in 2000 VS 2005, but I think I
have seen some examples in this forum.
"Pancho" <Pancho@.discussions.microsoft.com> wrote in message
news:931B3577-C055-4184-AEF5-AC0D22D9652E@.microsoft.com...
> We use a scheduled DTS package to import a .csv file. The .csv file is
> exported nightly using a scheduled .bat file that calls a .vbs script that
> performs the export from AD. We're not going to implement the TermDate
just
> yet but I will post the outcome when we do. Thanks again, P.
> "Jim Underwood" wrote:
>
far
probably
assume
can
Employees
be
you
depicted in
length
not
number,
N'IsUserTable')
NOT
,
,
NULL
NULL ,
,
NULL ,
NULL
in
table
of
information
records
what
The
