Email results from trigger tables

I'm trying to e-mail the results of a trigger that fires on an employee
table. I'm trying to included the results of either the DELETED or INSERTED
tables, but nothing is returned. Can xp_sendmail include DELETED or INSERTED
table queries in the @.query = parameter?
I'm trying to send an e-mail to someone whenever a new employee is added or
deleted.
Thanks.
Regardless that doesn′t work because the new session with XP_sendmail won′t
know about your deleted tables, the action you want to perform is not
preferable, because mail sending will be done synchronisly. So if your
mailserver is stuck in a problem and need 5 minutes for sending a mail (for
some reason) your transaction will hold on for that time, if the process
throws an error, your transaction might rollback. You don′t want that, erh ?
I would suggest (as this is not time critical) to write the data in a table
which is regulary checked for content to be sent.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Shawn Barrow" wrote:

> I'm trying to e-mail the results of a trigger that fires on an employee
> table. I'm trying to included the results of either the DELETED or INSERTED
> tables, but nothing is returned. Can xp_sendmail include DELETED or INSERTED
> table queries in the @.query = parameter?
> I'm trying to send an e-mail to someone whenever a new employee is added or
> deleted.
> Thanks.

Email results from trigger tables

I'm trying to e-mail the results of a trigger that fires on an employee
table. I'm trying to included the results of either the DELETED or INSERTED
tables, but nothing is returned. Can xp_sendmail include DELETED or INSERTED
table queries in the @.query = parameter?
I'm trying to send an e-mail to someone whenever a new employee is added or
deleted.
Thanks.Regardless that doesn´t work because the new session with XP_sendmail won´t
know about your deleted tables, the action you want to perform is not
preferable, because mail sending will be done synchronisly. So if your
mailserver is stuck in a problem and need 5 minutes for sending a mail (for
some reason) your transaction will hold on for that time, if the process
throws an error, your transaction might rollback. You don´t want that, erh ?
I would suggest (as this is not time critical) to write the data in a table
which is regulary checked for content to be sent.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Shawn Barrow" wrote:
> I'm trying to e-mail the results of a trigger that fires on an employee
> table. I'm trying to included the results of either the DELETED or INSERTED
> tables, but nothing is returned. Can xp_sendmail include DELETED or INSERTED
> table queries in the @.query = parameter?
> I'm trying to send an e-mail to someone whenever a new employee is added or
> deleted.
> Thanks.

Email results from trigger tables

I'm trying to e-mail the results of a trigger that fires on an employee
table. I'm trying to included the results of either the DELETED or INSERTED
tables, but nothing is returned. Can xp_sendmail include DELETED or INSERTE
D
table queries in the @.query = parameter?
I'm trying to send an e-mail to someone whenever a new employee is added or
deleted.
Thanks.Regardless that doesn′t work because the new session with XP_sendmail won′
t
know about your deleted tables, the action you want to perform is not
preferable, because mail sending will be done synchronisly. So if your
mailserver is stuck in a problem and need 5 minutes for sending a mail (for
some reason) your transaction will hold on for that time, if the process
throws an error, your transaction might rollback. You don′t want that, erh
?
I would suggest (as this is not time critical) to write the data in a table
which is regulary checked for content to be sent.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Shawn Barrow" wrote:

> I'm trying to e-mail the results of a trigger that fires on an employee
> table. I'm trying to included the results of either the DELETED or INSERT
ED
> tables, but nothing is returned. Can xp_sendmail include DELETED or INSER
TED
> table queries in the @.query = parameter?
> I'm trying to send an e-mail to someone whenever a new employee is added o
r
> deleted.
> Thanks.

Email if errors Exists in the error tables

I am setting my error tables.What i want to do is.If i find any data within my error tables i want an email to be sent to me.

How can i achieve this?

Please let me know

How are you populating those tables? do you want to receive an email on the first/each error or do you want to receive a single email at the end?

|||

Create a SQL Task that returns a value if you want to send an email or not.

Put the returned value in a variable.

Conditionally branch to email if the variable has the found value.

The SQL could be something like SELECT COUNT(1) cnt FROM ErrorTable. Have the SQL task return a single row. In mappings put variable 0 into a variable such as FoundError.

Add an email task, connect the two. Double click the line (constraint) and set it to an expression such as

@.FoundError > 0.

Hope that helps.

|||one single email only

|||

sureshv wrote:

one single email only

Then, Larry's suggestion should work for you. Wait until all data flow are done and then use an Execute SQl task in control flow to check is there were any errors; then you can use an expression in a precedence constraint to decide whether the email should be sent or not. There are another threads in this forums that tackle the how-to-send-an-email question

|||

I have Sql task and in General Tab in the

sql statement : i type SELECT COUNT(1)
FROM dbo.hb_test_error

when i execute and see now it workes fine

result set: from none to singlerow

Then Result set Tab

Variable Name : User::FoundError

Result Name :any name

Then i connect to email task

In precedence constraint editor

Evaluation operator : expression

Expression FoundError >0

i have this error

[Execute SQL Task] Error: Executing the query "SELECT COUNT(1) FROM dbo.hb_test_error" failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

How do i solve this?

|||Set Result Name to: 0 (that's a zero)

email for db changes

Other than Log Explorer, is there a tool that can email me whenever db
changes are made? (Tables/ Procs/ etc.)
TIA, ChrisRwww.LogPi.com, but no e-mails.
Wait for SQL 2005 where this functionality is provided for system tables.
For SQL server 2000 and 2005, user table auditing is best done though
triggers.
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/
"ChrisR" <noemail@.bla.com> wrote in message
news:%2390HsdwNFHA.3988@.tk2msftngp13.phx.gbl...
> Other than Log Explorer, is there a tool that can email me whenever db
> changes are made? (Tables/ Procs/ etc.)
> TIA, ChrisR
>

email for db changes

Other than Log Explorer, is there a tool that can email me whenever db
changes are made? (Tables/ Procs/ etc.)
TIA, ChrisR
www.LogPi.com, but no e-mails.
Wait for SQL 2005 where this functionality is provided for system tables.
For SQL server 2000 and 2005, user table auditing is best done though
triggers.
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/
"ChrisR" <noemail@.bla.com> wrote in message
news:%2390HsdwNFHA.3988@.tk2msftngp13.phx.gbl...
> Other than Log Explorer, is there a tool that can email me whenever db
> changes are made? (Tables/ Procs/ etc.)
> TIA, ChrisR
>

email for db changes

Other than Log Explorer, is there a tool that can email me whenever db
changes are made? (Tables/ Procs/ etc.)
TIA, ChrisRwww.LogPi.com, but no e-mails.
Wait for SQL 2005 where this functionality is provided for system tables.
For SQL server 2000 and 2005, user table auditing is best done though
triggers.
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/
"ChrisR" <noemail@.bla.com> wrote in message
news:%2390HsdwNFHA.3988@.tk2msftngp13.phx.gbl...
> Other than Log Explorer, is there a tool that can email me whenever db
> changes are made? (Tables/ Procs/ etc.)
> TIA, ChrisR
>

Email adddress format validation question

Trying to find the best way to vaildate email formats when importing records to result tables etc... the first seems to run faster, but it seems too easy, like something is getting missed.

Does anyone know if using:

WHERE email LIKE '%@.%.%'

instead of using:

WHERE
( CHARINDEX(' ',LTRIM(RTRIM([email]))) = 0
AND LEFT(LTRIM([email]),1) <> '@.'
AND RIGHT(RTRIM([email]),1) <> '.'
AND CHARINDEX('.',[email],CHARINDEX('@.',[email])) - CHARINDEX('@.',[email]) > 1
AND LEN(LTRIM(RTRIM([email]))) - LEN(REPLACE(LTRIM(RTRIM([email])),'@.','')) = 1
AND CHARINDEX('.',REVERSE(LTRIM(RTRIM([email])))) >= 3
AND (CHARINDEX('.@.',[email]) = 0 AND CHARINDEX('..',[email]) = 0) )

is any better, or are they both pretty much bring back the same result sets?

Thanks for sharing your insight.Looks like T-SQL from where I am standing. Moving to SQL Server. Any mod spot something I missed please relocate.|||The latter is more thorough but restrictive. You may also get false positives (for example my email address ends .uk). You don't need to use LEFT and RIGHT - you can use LIKE (especially for the LEFT since it is then sargable) so it could be more efficient. Perhaps create more of a mask (off the top of my head)

LIKE '_%@._%.__%'
Min 1 char then @. then min 1 char then . then min 2 chars.

HTH|||most people use regex for this kind of thing, although you don't have that option in t-sql.

The regex for the RFC 822 definition of what constitutes a valid address is amusing, see link below.

http://www.regular-expressions.info/email.html|||Great insights! I didnt think about the .uk etc.... Thanks! And I've never used regex before. Thanks so much for the link! I will read up on it! Thank you both!!!!!

Email a DB - supplementary question

Thanks guys
Thats really helpful.
What if I wanted to include data from some of the tables (such as lookup
tables).
Kind regards
Ross Petersen
"Chandra" wrote:
> hi
> you can generate the script and then e-mail the script to the destination
> please let me know if u have any questions
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "Ross" wrote:
>You can include INSERT Scripts for that table.
See http://vyaskn.tripod.com/code.htm#inserts
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Ross" <Ross@.discussions.microsoft.com> wrote in message
news:D6FD9EFA-59A4-4AA9-B46A-D62420A4AEBF@.microsoft.com...
> Thanks guys
> Thats really helpful.
> What if I wanted to include data from some of the tables (such as lookup
> tables).
> Kind regards
> Ross Petersen
>
> "Chandra" wrote:
>|||Hi Ross
You can add INSERT .. statement in the script file. once the script is
executed, you will have all the objects with data in lookup tables
please let me know if you have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Ross" wrote:
> Thanks guys
> Thats really helpful.
> What if I wanted to include data from some of the tables (such as lookup
> tables).
> Kind regards
> Ross Petersen
>
> "Chandra" wrote:
>|||Hi fellers
Many thanks for your help.
Kind regards
Ross Petersen
"Chandra" wrote:
> Hi Ross
> You can add INSERT .. statement in the script file. once the script is
> executed, you will have all the objects with data in lookup tables
> please let me know if you have any questions
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "Ross" wrote:
>

EM SQL Scripts

The EM can produce SQL Scripts of tables/indexes/constraints/default values etc from a database. When doing so, the create statement for tables and the alter statements for constraints (including the default values) are separated. In effect, the create table-statement will only create the table; keys, default values, indexes are changes on the table. My question is: when saving the results in separated files as a basis to continue working on, would you take the effort of changing the create-tables statements so that will also fill in the default values or keep it separate?I've never bothered to mangle the generated scripts... I've never seen any need for it.

-PatP|||Pat: Thing is, there's a db here without any of the tables/views/sp's stored on disk making it, imho, an unhealthy basis to continue building on. So I've scripted the db layout, views and all that allowing me to create a dev. I'm just considering whether I should take the effort or not; when changes are made to the db/tables I'll process it into the create-scripts as well.
I wonder though, do you have any create scripts that create all the tables in a database, do you keep it separate from the indexes/constraints? I don't want to start a flame-thing on "who's responsible", just wondering if you'd take the trouble?|||I'm not really clear what you are asking, so this response may seem way off base.

If you are talking about the option that causes Enterprise Mangler to script the PK/FK/Default/Check Constraints as part of the table versus scripting the table "raw" then creating ALTER TABLE statements to add the constraints, I usually don't care how it gets done. If there was some compelling reason to set it one way or the other, then I would. Since the net effect after running the whole script will be the same, I've never bothered to monkey with it... Either way works fine for me, and produces the same results in the end.

-PatP

EM in SS2000

In EM when I right-click on a database and choose "view", "taskpad" I get a
list of tables. Under some of the tables, I see the names of indexes that I
recognize but then I also see something like hind_909246294_10A_1A or
_WA_Sys_dtCCreationDate_3631FF56. What are these?
Thanks,
--
Dan D.Those are not indexes. They are auto-creates statistics. See the auto create statistics database
option. You can drop these using DROP STATISTICS, but they were created for a reason in the first
place, so best is most often to leave them.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:A2619689-507C-4EC2-96BF-78A1461F542E@.microsoft.com...
> In EM when I right-click on a database and choose "view", "taskpad" I get a
> list of tables. Under some of the tables, I see the names of indexes that I
> recognize but then I also see something like hind_909246294_10A_1A or
> _WA_Sys_dtCCreationDate_3631FF56. What are these?
> Thanks,
> --
> Dan D.|||Thanks.
--
Dan D.
"Tibor Karaszi" wrote:
> Those are not indexes. They are auto-creates statistics. See the auto create statistics database
> option. You can drop these using DROP STATISTICS, but they were created for a reason in the first
> place, so best is most often to leave them.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:A2619689-507C-4EC2-96BF-78A1461F542E@.microsoft.com...
> > In EM when I right-click on a database and choose "view", "taskpad" I get a
> > list of tables. Under some of the tables, I see the names of indexes that I
> > recognize but then I also see something like hind_909246294_10A_1A or
> > _WA_Sys_dtCCreationDate_3631FF56. What are these?
> >
> > Thanks,
> > --
> > Dan D.
>
>

EM in SS2000

In EM when I right-click on a database and choose "view", "taskpad" I get a
list of tables. Under some of the tables, I see the names of indexes that I
recognize but then I also see something like hind_909246294_10A_1A or
_WA_Sys_dtCCreationDate_3631FF56. What are these?
Thanks,
--
Dan D.Those are not indexes. They are auto-creates statistics. See the auto create
statistics database
option. You can drop these using DROP STATISTICS, but they were created for
a reason in the first
place, so best is most often to leave them.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:A2619689-507C-4EC2-96BF-78A1461F542E@.microsoft.com...
> In EM when I right-click on a database and choose "view", "taskpad" I get
a
> list of tables. Under some of the tables, I see the names of indexes that
I
> recognize but then I also see something like hind_909246294_10A_1A or
> _WA_Sys_dtCCreationDate_3631FF56. What are these?
> Thanks,
> --
> Dan D.|||Thanks.
--
Dan D.
"Tibor Karaszi" wrote:

> Those are not indexes. They are auto-creates statistics. See the auto crea
te statistics database
> option. You can drop these using DROP STATISTICS, but they were created fo
r a reason in the first
> place, so best is most often to leave them.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:A2619689-507C-4EC2-96BF-78A1461F542E@.microsoft.com...
>
>

EM frozen when viewing tables

Hi,
When I tried to view tables of one my database (sql2000) from Enterprise
manger by clicking on Databases --> <database name> --> Tables, it got
frozen. But if I do it from Query Analyzer, I can query any table as usual.
I
close EM and tried several times, it's still the same. How can I
troubleshoot? ThanksIs this on the actual server or on another workstation using the client tool
s?
In any case, you may need to install SP4 on the client. If it happens on the
server, you need to do an impcat analisys before installing SP4. I would not
be surprised that some applications are not up to date with the current SP
from MS.
"Jen" wrote:

> Hi,
> When I tried to view tables of one my database (sql2000) from Enterprise
> manger by clicking on Databases --> <database name> --> Tables, it got
> frozen. But if I do it from Query Analyzer, I can query any table as usual
. I
> close EM and tried several times, it's still the same. How can I
> troubleshoot? Thanks|||it's on another workstation. Restart service clears it. Thanks
"Edgardo Valdez, MCSD, MCDBA" wrote:
[vbcol=seagreen]
> Is this on the actual server or on another workstation using the client to
ols?
> In any case, you may need to install SP4 on the client. If it happens on t
he
> server, you need to do an impcat analisys before installing SP4. I would n
ot
> be surprised that some applications are not up to date with the current SP
> from MS.
> "Jen" wrote:
>

EM frozen when viewing tables

Hi,
When I tried to view tables of one my database (sql2000) from Enterprise
manger by clicking on Databases --> <database name> --> Tables, it got
frozen. But if I do it from Query Analyzer, I can query any table as usual. I
close EM and tried several times, it's still the same. How can I
troubleshoot? Thanks
Is this on the actual server or on another workstation using the client tools?
In any case, you may need to install SP4 on the client. If it happens on the
server, you need to do an impcat analisys before installing SP4. I would not
be surprised that some applications are not up to date with the current SP
from MS.
"Jen" wrote:

> Hi,
> When I tried to view tables of one my database (sql2000) from Enterprise
> manger by clicking on Databases --> <database name> --> Tables, it got
> frozen. But if I do it from Query Analyzer, I can query any table as usual. I
> close EM and tried several times, it's still the same. How can I
> troubleshoot? Thanks
|||it's on another workstation. Restart service clears it. Thanks
"Edgardo Valdez, MCSD, MCDBA" wrote:
[vbcol=seagreen]
> Is this on the actual server or on another workstation using the client tools?
> In any case, you may need to install SP4 on the client. If it happens on the
> server, you need to do an impcat analisys before installing SP4. I would not
> be surprised that some applications are not up to date with the current SP
> from MS.
> "Jen" wrote:

EM frozen when viewing tables

Hi,
When I tried to view tables of one my database (sql2000) from Enterprise
manger by clicking on Databases --> <database name> --> Tables, it got
frozen. But if I do it from Query Analyzer, I can query any table as usual. I
close EM and tried several times, it's still the same. How can I
troubleshoot? ThanksIs this on the actual server or on another workstation using the client tools?
In any case, you may need to install SP4 on the client. If it happens on the
server, you need to do an impcat analisys before installing SP4. I would not
be surprised that some applications are not up to date with the current SP
from MS.
"Jen" wrote:
> Hi,
> When I tried to view tables of one my database (sql2000) from Enterprise
> manger by clicking on Databases --> <database name> --> Tables, it got
> frozen. But if I do it from Query Analyzer, I can query any table as usual. I
> close EM and tried several times, it's still the same. How can I
> troubleshoot? Thanks|||it's on another workstation. Restart service clears it. Thanks
"Edgardo Valdez, MCSD, MCDBA" wrote:
> Is this on the actual server or on another workstation using the client tools?
> In any case, you may need to install SP4 on the client. If it happens on the
> server, you need to do an impcat analisys before installing SP4. I would not
> be surprised that some applications are not up to date with the current SP
> from MS.
> "Jen" wrote:
> > Hi,
> >
> > When I tried to view tables of one my database (sql2000) from Enterprise
> > manger by clicking on Databases --> <database name> --> Tables, it got
> > frozen. But if I do it from Query Analyzer, I can query any table as usual. I
> > close EM and tried several times, it's still the same. How can I
> > troubleshoot? Thanks

EM and QA access different

Users (other than sysadmin) can query tables in QA just fine, but when they
try to view the same table in EM, they get an error that says they "might no
t
have permission to perform this operation, or the object XXX might no longer
exist in the database."
Any ideas?RP
How do you query the table? by stored procedure or by querying underlying
table?
"RP" <RP@.discussions.microsoft.com> wrote in message
news:19B8A84F-3E22-455D-9792-BD4AB4F5E7AF@.microsoft.com...
> Users (other than sysadmin) can query tables in QA just fine, but when
they
> try to view the same table in EM, they get an error that says they "might
not
> have permission to perform this operation, or the object XXX might no
longer
> exist in the database."
> Any ideas?|||just a regular query of the table
"Uri Dimant" wrote:

> RP
> How do you query the table? by stored procedure or by querying underlying
> table?
>
> "RP" <RP@.discussions.microsoft.com> wrote in message
> news:19B8A84F-3E22-455D-9792-BD4AB4F5E7AF@.microsoft.com...
> they
> not
> longer
>
>

Eliminating Duplicates

Have a pretty simple wuestion but the answer seems to be evading me:

Here's the DDL for the tables in question:

CREATE TABLE [dbo].[Office] (
[OfficeID] [int] IDENTITY (1, 1) NOT NULL ,
[ParentOfficeID] [int] NOT NULL ,
[WebSiteID] [int] NOT NULL ,
[IsDisplayOnWeb] [bit] NOT NULL ,
[IsDisplayOnAdmin] [bit] NOT NULL ,
[OfficeStatus] [char] (1) NOT NULL ,
[DisplayORD] [smallint] NOT NULL ,
[OfficeTYPE] [varchar] (10) NOT NULL ,
[OfficeNM] [varchar] (50) NOT NULL ,
[OfficeDisplayNM] [varchar] (50) NOT NULL ,
[OfficeADDR1] [varchar] (50) NOT NULL ,
[OfficeADDR2] [varchar] (50) NOT NULL ,
[OfficeCityNM] [varchar] (50) NOT NULL ,
[OfficeStateCD] [char] (2) NOT NULL ,
[OfficePostalCD] [varchar] (15) NOT NULL ,
[OfficeIMG] [varchar] (100) NOT NULL ,
[OfficeIMGPath] [varchar] (100) NOT NULL ,
[RegionID] [int] NOT NULL ,
[OfficeTourURL] [varchar] (255) NULL ,
[GeoAreaID] [int] NOT NULL ,
[CreateDT] [datetime] NOT NULL ,
[UpdateDT] [datetime] NOT NULL ,
[CreateByID] [varchar] (50) NOT NULL ,
[UpdateByID] [varchar] (50) NOT NULL ,
[OfficeBrandedURL] [varchar] (255) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[OfficeManagement] (
[OfficeID] [int] NOT NULL ,
[PersonnelID] [int] NOT NULL ,
[JobTitleID] [int] NOT NULL ,
[CreateDT] [datetime] NOT NULL ,
[CreateByID] [varchar] (50) NOT NULL ,
[SeqNBR] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[OfficeMls] (
[OfficeID] [int] NOT NULL ,
[SourceID] [int] NOT NULL ,
[OfficeMlsNBR] [varchar] (20) NOT NULL ,
[CreateDT] [datetime] NOT NULL ,
[UpdateDT] [datetime] NOT NULL ,
[CreateByID] [varchar] (50) NOT NULL ,
[UpdateByID] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Personnel] (
[PersonnelID] [int] IDENTITY (1, 1) NOT NULL ,
[PersonnelDisplayName] [varchar] (100) NOT NULL ,
[FirstNM] [varchar] (50) NOT NULL ,
[PreferredFirstNM] [varchar] (50) NOT NULL ,
[MiddleNM] [varchar] (50) NOT NULL ,
[LastNM] [varchar] (50) NOT NULL ,
[PersonalTaxID] [varchar] (9) NOT NULL ,
[HireDT] [datetime] NOT NULL ,
[TermDT] [datetime] NOT NULL ,
[HomePhoneNBR] [varchar] (15) NULL ,
[HomeADDR1] [varchar] (50) NOT NULL ,
[HomeADDR2] [varchar] (50) NOT NULL ,
[HomeCityNM] [varchar] (50) NOT NULL ,
[HomeStateCD] [char] (2) NOT NULL ,
[HomePostalCD] [varchar] (15) NOT NULL ,
[PersonnelLangCSV] [varchar] (500) NOT NULL ,
[PersonnelSlogan] [varchar] (500) NOT NULL ,
[BGColor] [varchar] (50) NOT NULL ,
[IsEAgent] [bit] NOT NULL ,
[IsArchAgent] [bit] NOT NULL ,
[IsOptOut] [bit] NOT NULL ,
[IsDispOnlyPrefFirstNM] [bit] NOT NULL ,
[IsHideMyListingLink] [bit] NOT NULL ,
[IsPreviewsSpecialist] [bit] NOT NULL ,
[AudioFileNM] [varchar] (100) NULL ,
[iProviderID] [int] NOT NULL ,
[DRENumber] [varchar] (10) NOT NULL ,
[AgentBrandedURL] [varchar] (255) NOT NULL ,
[CreateDT] [datetime] NOT NULL ,
[UpdateDT] [datetime] NOT NULL ,
[CreateByID] [varchar] (50) NOT NULL ,
[UpdateByID] [varchar] (50) NOT NULL ,
[IsDisplayAwards] [bit] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[PersonnelMLS] (
[PersonnelID] [int] NOT NULL ,
[SourceID] [int] NOT NULL ,
[AgentMlsNBR] [varchar] (20) NOT NULL ,
[CreateDT] [datetime] NOT NULL ,
[UpdateDT] [datetime] NOT NULL ,
[CreateByID] [varchar] (50) NOT NULL ,
[UpdateByID] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Office] ADD
CONSTRAINT [FK_Office_OfficeProfile] FOREIGN KEY
(
[OfficeID]
) REFERENCES [dbo].[OfficeProfile] (
[OfficeID]
) NOT FOR REPLICATION
GO

alter table [dbo].[Office] nocheck constraint [FK_Office_OfficeProfile]
GO

ALTER TABLE [dbo].[OfficeManagement] ADD
CONSTRAINT [FK_OfficeManagement_LookupJobTitle] FOREIGN KEY
(
[JobTitleID]
) REFERENCES [dbo].[LookupJobTitle] (
[JobTitleID]
),
CONSTRAINT [FK_OfficeManagement_Office] FOREIGN KEY
(
[OfficeID]
) REFERENCES [dbo].[Office] (
[OfficeID]
) NOT FOR REPLICATION ,
CONSTRAINT [FK_OfficeManagement_Personnel] FOREIGN KEY
(
[PersonnelID]
) REFERENCES [dbo].[Personnel] (
[PersonnelID]
) ON DELETE CASCADE
GO

alter table [dbo].[OfficeManagement] nocheck constraint [FK_OfficeManagement_Office]
GO

ALTER TABLE [dbo].[OfficeMls] ADD
CONSTRAINT [FK_OfficeMls_Office] FOREIGN KEY
(
[OfficeID]
) REFERENCES [dbo].[Office] (
[OfficeID]
) NOT FOR REPLICATION
GO

alter table [dbo].[OfficeMls] nocheck constraint [FK_OfficeMls_Office]
GO

ALTER TABLE [dbo].[PersonnelMLS] ADD
CONSTRAINT [FK_PersonnelMLS_Personnel] FOREIGN KEY
(
[PersonnelID]
) REFERENCES [dbo].[Personnel] (
[PersonnelID]
) NOT FOR REPLICATION
GO

alter table [dbo].[PersonnelMLS] nocheck constraint [FK_PersonnelMLS_Personnel]
GO

Here's the query I'm having trouble with:

SELECT distinct Personnel.PersonnelID,
Personnel.FirstNM,
Personnel.LastNM,
Office.OfficeNM,
Office.OfficeID,
OfficeMls.SourceID AS OfficeBoard,
PersonnelMLS.SourceID AS AgentBoard
FROM Personnel INNER JOIN
OfficeManagement ON
Personnel.PersonnelID = OfficeManagement.PersonnelID
INNER JOIN
Office ON OfficeManagement.OfficeID = Office.OfficeID
INNER JOIN
OfficeMls ON Office.OfficeID = OfficeMls.OfficeID
INNER JOIN
PersonnelMLS ON Personnel.PersonnelID = PersonnelMLS.PersonnelID
where officemls.sourceid <> personnelmls.sourceid
and office.officenm not like ('%admin%')
group by PersonnelMLS.SourceID,
Personnel.PersonnelID,
Personnel.FirstNM,
Personnel.LastNM,
Office.OfficeNM,
Office.OfficeID,
OfficeMls.SourceID
order by office.officenm

What I'm trying to retrieve are those agents who have source id's that are not in the Office's domain of valid source id's. Here's a small portion of the results:

PersonnelID FirstNM LastNM OfficeNM OfficeID OfficeBoard AgentBoard
---- ---------------- ---------------- ---------------- ---- ---- ----
18205 Margaret Peggy Quattro Aventura North 650 906 908
18205 Margaret Peggy Quattro Aventura North 650 918 908
15503 Susan Jordan Blackburn Point 889 920 909
15503 Susan Jordan Blackburn Point 889 921 909
15503 Susan Jordan Blackburn Point 889 921 920
15279 Sandra Humphrey Boca Beach North 890 917 906
15279 Sandra Humphrey Boca Beach North 890 906 917
15279 Sandra Humphrey Boca Beaches 626 917 906
15279 Sandra Humphrey Boca Beaches 626 906 917
13532 Michael Demcho Boca Downtown 735 906 917
14133 Maria Ford Boca Downtown 735 906 917
19126 Michael Silverman Boca Glades Road 736 917 906
18920 Beth Schwartz Boca Glades Road 736 906 917

If you take a look at Sandra Humphries, you'll see she's out of office 626. Office 626 is associated with source id's 907 and 916. Sandra Humphries is also associated with those two source id's , but she shows up in the results.

I know this was AWFULLY long winded, but just wanted to make sure made myself as clear as possible.

Any help would be greatly appreciated.

Thanks in advance!Could you edit the post and wrap [ code] [ /code ] tags around it?

Just reomve the spaces in the tags|||You're right...that is a LOT better!! Thanks for the tip...|||Do the same with the data...

Why are you doing a GROUP BY? To get DISTINCT data?

EDIT: And can you post the DDL for the tables|||...and I guess I really don't need the GROUP BY...|||As far as i can see .. the rows are not exactly duplicates ... OfficeBoard id seems to be differing ... a joi n problem .. maybe ??|||didnt read the post completely .. my bad !!!

Didnt get what exactly you were trying to accomplish ...

How do you decide that Sandra Humphries is outta office 626 ?|||Office 626 is the office she's associated with - it's set up in the main table. Been working on the join and can't seem to work out the bugs on this one...|||you could try replacing
"where officemls.sourceid <> personnelmls.sourceid"
with
"where personnelmls.sourceid not in
(select sourceid from officemls omls join office o on omls.officeid = o.officeid
where officemanagement.officeid = omls.officeid)"
This will give you duplicates because of the "distinct" but only the wrong personnelmls.sourceid should be selected.

Eliminating Duplicated

I have 3 tables Employee (empno,name,city) , Emp-Pl (empno,noofleaves) , and
Emp-Cl (empno,noofleaves). Now i require the report in the following format
Sample Data
employee
1 ravi kumar rajahmundry
2 ravindra kakinada
emp-pl emp-cl
empno noofleave empno noofleave
1 2 1 4
1 3 1 2
2 4 1 3
2 1 2 1
2 1
EMPNO NAME CL PL
1 ravikumar 4 2
2 3
3
2 ravindra 1 4
1
1
"venkataramarao" <venkataramarao@.discussions.microsoft.com> wrote in message
news:AC5F930E-5EE5-41B2-AB7B-C809082EF5C7@.microsoft.com...
>I have 3 tables Employee (empno,name,city) , Emp-Pl (empno,noofleaves) ,
>and
> Emp-Cl (empno,noofleaves). Now i require the report in the following
> format
> Sample Data
> employee
> 1 ravi kumar rajahmundry
> 2 ravindra kakinada
> emp-pl emp-cl
> empno noofleave empno noofleave
> 1 2 1 4
> 1 3 1 2
> 2 4 1 3
> 2 1 2 1
> 2 1
> EMPNO NAME CL PL
> 1 ravikumar 4 2
> 2 3
> 3
> 2 ravindra 1 4
> 1
> 1
I don't understand your requirements. Why does data from the CL table appear
in both the CL and PL columns? What determines this output?
Please post DDL and post sample data as INSERT statements rather than
sketches of tables.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

Eliminating Duplicate Records

Hi

How can i eliminate duplicate records from a single table, when i use a query that links multipla tables together?

There is a surrogate key TABLE1 and i just can't seem to wrap my head around the layout.

TABLE1

ID | memQuestionaireID | QuestscoreID | DateOfAssessment | etc.

TABLE2
memQuestionaireID | MemberID | Answer1 | Answer2 | etc.

TABLE3

MemberID | Name | Surname | IDNo | etc.

t1.memQuestionaireID = t2.memQuestionaireID

t2.MemberID = t3.MemberID

That's how the tables link, obvious enough. How can i delete duplicate records from TABLE1 where MemberID occurs more than once and the t1.DateOfAssessment = MAX(DateOfAssessment)

The ID is unique, but there are duplicate memberID's with different date of assessments, i want to take the last used date of assessment and keep only that one, and remove the rest from the table?

I'm sure the query is simple, i'm just too stupid to see how to solve it!!

Any help would be greatly appreciated.

I'm not very clued up on how to remove records or update records, Please help.

Kind Regards
Carel Greaves

See if this does what you need...

Code Snippet

DELETE t1

FROM Table1 t1

INNER JOIN Table2

on t1.memQuestionaireID = t2.memQuestionaireID

INNER JOIN

(

SELECT t2b.MemberID, max(t1b.DateOfAssessment) as DateOfAssessment

FROM table1 t1b

INNER JOIN table2 t2b

ON t1b.memQuestionaireID = t2b.memQuestionaireID

AND t2b.MemberID in

(

SELECT MemberID

FROM Table3

GROUP BY MemberID

HAVING count(*)>1

)

GROUP BY t2b.MemberID

) as list

ON t1.DateOfAssessment < list.DateOfAssessment

and t2.MemberID = list.MemberID

To view what will be delete w/o actually deleting, just change the first line from DELETE t1 to SELECT t1.*

|||

I'm a bit confused Carel,

Call me dense today, but it seems like the MemberID 'should' be in Table1 since it is the Member that has a memQuestionaireID, has a QuestScoreID and a DateOfAssessment. Then for Table2, it is the Questionaire that has a set of answers, etc. Please help me out here...

It seems like your Table1 has a one-to-many relationship with Table2. I'm assuming that a Member may have multiple instances of the same questionnaire (re-takes, etc.)

Is one questionnaire (MemQuestionnaireID) with a QuestScoreID and a DateOfAssessment directly linked to one or more set(s) of answers in Table2?

Is the QuestScoreID changed upon each re-take? (Shouldn't the score be directly related to a set of answers?)

I ask this because it seems from the design you presented that perhaps you want to also delete rows from Table2...

|||

I know the database structure is a bit stupid. Not my design!

I am only trying to remove duplicate entries in the QuestScore table.

Problem:

A member fills in a Questionaire - MemQuestionaires

The Querstionaire's Score is placed in the QuestScore Table.

There are duplicate entries in the QuestScore table with regards to a questionaireID from the memQuestionaires Table

If a duplicate exists, then the members can't log into there profiles anymore, so i need to remove the duplicates so that the members can access their profiles again.

I'm quite new to all of this, so any help would be greatly appreciated

Kind Regards

Carel Greaves.

|||

You gave us Table1, Table2, Table3...I'm guessing Table1 is QuestScore?

Did my code above work? If not, what was not quite right?

Are there multiple entries just in Table1, or is it Table3?

Please clarify and maybe post some sample data...

|||

Okay, so i'm a bito f a moron :-) he he

Here is the code i used to find the duplicates

SELECT me.MemberID, /*me.QuestionaireID,*/ q.memQuestionaireID, q.*, me.*

FROM questscore q, memQuestionaires m, Members me

WHERE q.memquestionaireID = m.memquestionaireID

AND m.MemberiD = me.memberid

AND me.ClientID = 124

ANd m.questionaireid = 85

AND q.sectionid >= 1000000

ORDER BY me.memberID

Here is my results:

MemberID memQuestionaireID QuestScoreID AnswerID AnswerValue DateCompleted 10005772 7615 273308 0 -500 50:59.7 10005789 11124 267771 0 -500 25:16.0 10005798 11129 259150 0 -500 44:41.1 10005798 11129 261032 0 -500 01:21.3 10005807 8214 237464 0 -500 59:18.1 10005819 11142 293317 0 -500 43:09.4

i have marked the duplicate values in red that i want to eliminate.

I need to eliminate only the record with the oldest date, and keep the newer one.

Hopefully when i've gone for my SQL course then i will only be half a moron :-) he he

Thanks for helping me out guys.

Kind Regards

Carel Greaves

|||

Wow, talk about a 'bolixed' up database...

I'm still confused, in your query above, you have a WHERE clause that indicates the results should be filtered down to QuestionaireID = 85, but your duplicate records are for memQuestionaireID 11129. What is the difference between QuestionaireID and memQuestionaireID? How did that happen?

Also, the DateCompleted is obviously not a date, but a number of hours. Is that the number of hours since some event? How are the hours determined?

Which row is the 'newer one'?

It would be very helpful, and keep us from wasting our time, if you would post the DDL for each of the tables, as well as some sample data for each table (in the form of INSERT statements.)

As you seen, you have folks sincerely attempting to help you, and to this point wasting our time because we just don't have the 'full' picture. You've inherited a very 'odd' database, and you need to help us so we can better help you.

|||

Here is sample data in the Tables.

Member Table

med_aid MemberID ClientID Name Surname Username Password NULL 10000000 1 NiftyName NiftySurname his test NULL 10000001 5 NiftyTest2 Surname2 nifty@.ybo.oa JBCYWO NULL 10000002 5 KeithTest WilsonTest willi willi

memQuestionaires Table

memQuestionaireID MemberID QuestionaireID LastPage Paused Complete MaxPages Sent LastSaved InitMail ReceiveMail UpdateDate 871 10000000 85 1 0 0 1 1 0 1 1 2007/02/2506:23 872 10000001 85 1 0 0 1 1 0 1 1 2007/02/2506:23 873 10000002 85 1 0 0 1 1 0 1 1 2007/02/2506:23

QuestScore Table

QuestScoreID memQuestionaireID AnswerID AnswerValue SectionID Page QuestType AnswerText AnswerShort Updatedate DefaultValue DateCompleted 4641 871 0 -9 361 1 9 NULL NULL

2007/02/0722:31

4642 872 0 -5 362 1 6 NULL NULL

2007/02/0722:31

4643 873 0 0 345 1 2 NULL NULL

2007/02/0722:31

When i executed this statement, i only used these three tables.

SELECT me.MemberID, /*me.QuestionaireID,*/ q.memQuestionaireID, q.*, me.*
FROM questscore q, memQuestionaires m, Members me
WHERE q.memquestionaireID = m.memquestionaireID
AND m.MemberiD = me.memberid
AND me.ClientID = 124
ANd m.questionaireid = 85
AND q.sectionid >= 1000000
ORDER BY me.memberID

I am only trying to remove the duplicate records for where ClientID = 124

AND QuestionaireID = 85

as for the 1000000, there it is just to narrow down the search for myself, there are a lot of duplicates.

There are a lot of members -> Members Table

Each member can fill in a number of questionaires -> QuestionaireID on memQuestionaires Table in this case 85

And all the questionaire's data is stored in the QuestScore Table which is linked to the memQuestionaires table using the memQuestionairesID.

There seems to be duplicate values in the QuestScore Table referring to the members.

The date completed is an actual DATETIME field in the database, i used excel to transfer the data to here. The newer date will be a actual date with the time in the database i.e. 2007/05/23 02:05:11

All the fields get inserted into the database via a application which i haven't seen.

I'm new to the company and am trying to figure a lot of it out for myself too.

If there is any more information you need, please don't hesitate to ask.

|||try this one,

DECLARE @.QuestScore TABLE (
QuestScoreID int
, memQuestionaireID int
, DateCompleted smalldatetime
)

DECLARE @.MemQuestionaire TABLE (
MemQuestionaireID int
, MemberID int
, QuestionaireID int
, UpdateDate smalldatetime
)

INSERT
INTO @.QuestScore
SELECT 1, 1, dateadd(day,-1, getdate()) UNION ALL
SELECT 2, 2, dateadd(day,-1, getdate()) UNION ALL
SELECT 3, 3, dateadd(day,-3, getdate())

INSERT
INTO @.MemQuestionaire
SELECT 1, 1, 4, dateadd(day,-1, getdate()) UNION ALL
SELECT 2, 1, 4, dateadd(day,-1, getdate()) UNION ALL
SELECT 3, 2, 5, dateadd(day,-3, getdate())

select *
from @.memquestionaire

select *
from @.questscore

declare @.tobedeleted table( -- create a table var for the data to be deleted
memquestionaireid int
, datecompleted smalldatetime
)

insert
into @.tobedeleted -- the memquestionaireid and datecompleted to be deleted
select MIN(qq.memquestionaireid) as memquestionaireid -- just in case if they have the same date, get the lowest id
, mq.datecompleted
from @.questscore qq inner join
(
select m.memberid
, m.questionaireid
, MIN(q.datecompleted) as datecompleted -- delete the lowest date
from @.memquestionaire m inner join
@.questscore q on m.memquestionaireid = q.memquestionaireid
group by
m.memberid
, m.questionaireid
having count(m.memberid) > 1 -- member having more than 1 quest score
) mq on qq.datecompleted = mq.datecompleted
group by
mq.datecompleted

delete @.questscore -- delete in questscore
from @.questscore q inner join
@.tobedeleted dq on q.memquestionaireid = dq.memquestionaireid
and q.datecompleted = dq.datecompleted

delete @.memquestionaire -- delete in memquestionaire
from @.memquestionaire m inner join
@.tobedeleted dq on m.memquestionaireid = dq.memquestionaireid

select *
from @.questscore

select *
from @.memquestionaire|||

Thanks, that's awesome, that's why i come to you guys for help.

Another quick question, that i think it might be easier to do, i just can't do it. (I just thought of it now)

If i added memQuestionaireID and SectionID together to create a unique field, and then filter out the duplicates by taking out the old dates within the duplicates it might be easier, but how would i go about accomplishing this?

I don't know how to compare the dates against itself to get keep the new date and get rid of the old date?

sorry for all of this i'm just trying to learn from all the things myself?

|||

Thanks, that was a start.

Here is an example of the 'best' form for DDL and sample data. In this fashion, each person that wants to try and help you can just run this code and have 'your' problem to work with. Without this, folks are turned away because it takes so much time and effort to duplicate your situation -and helps to keep us from running off on tangents that don't really help you.

Now if you would only add to the sample data so that examples of the duplicate records you want to find and delete are represented, we 'should' be able to help you.

Code Snippet

DECLARE @.Members table
( med_aid int,
MemberID int,
ClientID int,
Name varchar(20),
Surname varchar(20),
Username varchar(100),
Password varchar(20)
)

INSERT INTO @.Members VALUES ( NULL, 10000000, 1, 'NiftyName', 'NiftySurname', 'his', 'test' )
INSERT INTO @.Members VALUES ( NULL, 10000001, 5, 'NiftyTest2', 'Surname2', 'nifty@.ybo.oa', 'JBCYWO' )
INSERT INTO @.Members VALUES ( NULL, 10000002, 5, 'KeithTest', 'WilsonTest', 'willi', 'willi' )

DECLARE @.memQuestionaires table
( memQuestionaireID int,
MemberID int,
QuestionaireID int,
LastPage int,
Paused int,
Complete int,
MaxPages int,
Sent int,
LastSaved int,
InitMail int,
ReceiveMail int,
UpdateDate datetime
)

INSERT INTO @.memQuestionaires VALUES ( 871, 10000000, 85, 1, 0, 0, 1, 1, 0, 1, 1, '2007/02/25 06:23' )
INSERT INTO @.memQuestionaires VALUES ( 872, 10000001, 85, 1, 0, 0, 1, 1, 0, 1, 1, '2007/02/25 06:23' )
INSERT INTO @.memQuestionaires VALUES ( 873, 10000002, 85, 1, 0, 0, 1, 1, 0, 1, 1, '2007/02/25 06:23' )

DECLARE @.QuestScore table
( QuestScoreID int,
memQuestionaireID int,
AnswerID int,
AnswerValue int,
SectionID int,
Page int,
QuestType int,
AnswerText int,
AnswerShort int,
Updatedate int,
DefaultValue int,
DateCompleted datetime
)

INSERT INTO @.QuestScore VALUES ( 4641, 871, 0, -9, 361, 1, 9, '', '', NULL, NULL, '2007/02/07 22:31' )
INSERT INTO @.QuestScore VALUES ( 4642, 872, 0, -5, 362, 1, 6, '', '', NULL, NULL, '2007/02/07 22:31' )
INSERT INTO @.QuestScore VALUES ( 4643, 873, 0, 0, 345, 1, 2, '', '', NULL, NULL, '2007/02/07 22:31' )

|||very clean example arnie.. i wonder how your desk looks like |||

Sorry i'm sending you guys on a wild goose chase like this, and thanks a lot Arnie, i'd buy you a case of beer if i knew where you were.

I'm really learning a lot from this!

I just thought that it might be easier, if i had to add the memQuestionaireID and SectionID together in the QuestScore table to get a single unique value and remove the duplicates based on the newer unique value according to the Date, i.e. Only removing the old dates.

Sorry about this, i only saw it now.

That way i'm only using a single table.

|||

Thanks,

And my desk is under the stuff somewhere, or so it was a few months ago...

And UPS serves Portland, OR! But it would be much more enjoyable to share, so if you can wrangle it, start lobbying management to let you come to the PASS (Professional Association for SQL Server) Conference in September. (http://sqlpass.org/)

|||

Cummon guys, i'm a newbie at SQL Server in South Africa and it's late at night and my CEO's say i can't go home until i get this problem sorted out.

Please could you guys help me out quick?

Eliminate duplicate data

Hi,
I want to eliminate duplicate entries from different tables into a single
new table and keep the reference from the original data.
Example:
Table1: toto, titi
Table2: toto, titi, tutu
Table3: tutu, tata
the new table will contain:
NewTable: toto, titi, tutu, tata
and i want to keep a reference that allows to retrieve that toto comes from
Table1 and Table2
tutu comes from Table2 and Table3...
if someone can help me it would be great.
Thanks.
Beter than a single new table, You can make a View
UNION will eliminate duplicate data.
SELECT Field FROM Table1
UNION
SELECT Field FROM Table2
UNION
SELECT Field FROM Table3
To know where 'toto' come from:
SELECT TableName FROM
(
SELECT 'Table1' AS TableName, Field FROM Table1
UNION
SELECT 'Table2' AS TableName, Field FROM Table2
UNION
SELECT 'Table3' AS TableName, Field FROM Table3
) Tmp
WHERE Field='toto'
Laurent.
<Christophe> wrote in message news:OVqTbQGLEHA.1192@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I want to eliminate duplicate entries from different tables into a single
> new table and keep the reference from the original data.
> Example:
> Table1: toto, titi
> Table2: toto, titi, tutu
> Table3: tutu, tata
> the new table will contain:
> NewTable: toto, titi, tutu, tata
> and i want to keep a reference that allows to retrieve that toto comes
from
> Table1 and Table2
> tutu comes from Table2 and Table3...
> if someone can help me it would be great.
> Thanks.
>