Max [date] from DIFFERENT columns?

Hi,
I have a table in which there are several date columns recording different
event points occuring to a record (e.g., date opened, date action-1, etc).
I need to find the most recent date (MAX(date-n)) from across all these
columns to compare with a final closure date.
Is there a (simple/sensible) mechanism that makes this possible?
I have considered using IF/ELSE to try and determine if one is later than
the other, but this seems like a no-go (too complex to implement sensibly).
CASE statement instead maybe?
Any pointers gratefully received...Thx
Al
Alec,
If you do not have a lot of columns, you can use the following approach:
Please let me kow if it helps...
-- BEGIN SCRIPT
declare @.table table
(RecordID int
, Created datetime
, Opened datetime
, Updated datetime
)
insert into @.table
values (1, getdate(), getdate()+ .10, getdate()+.15)
insert into @.table
values (2, getdate()+.3, getdate()+ .40, getdate()+.45)
-- Preview of the table
select * from @.table
-- Actual query
select RecordID
, MAX(ActionDate) LatestActionDate
from(
select RecordId
, Created ActionDate
, 'Created' Action
from @.table
union
select RecordId
, Opened ActionDate
, 'Opened' Action
from @.table
union
select RecordId
, Updated ActionDate
, 'Updated' Action
from @.table
) t1
group by RecordID
-- END SCRIPT
"Alec MacLean" wrote:

> Hi,
> I have a table in which there are several date columns recording different
> event points occuring to a record (e.g., date opened, date action-1, etc).
> I need to find the most recent date (MAX(date-n)) from across all these
> columns to compare with a final closure date.
> Is there a (simple/sensible) mechanism that makes this possible?
> I have considered using IF/ELSE to try and determine if one is later than
> the other, but this seems like a no-go (too complex to implement sensibly).
> CASE statement instead maybe?
> Any pointers gratefully received...Thx
> Al
>
>

Max [date] from DIFFERENT columns?

Hi,
I have a table in which there are several date columns recording different
event points occuring to a record (e.g., date opened, date action-1, etc).
I need to find the most recent date (MAX(date-n)) from across all these
columns to compare with a final closure date.
Is there a (simple/sensible) mechanism that makes this possible?
I have considered using IF/ELSE to try and determine if one is later than
the other, but this seems like a no-go (too complex to implement sensibly).
CASE statement instead maybe?
Any pointers gratefully received...Thx
AlAlec,
If you do not have a lot of columns, you can use the following approach:
Please let me kow if it helps...
-- BEGIN SCRIPT
declare @.table table
(RecordID int
, Created datetime
, Opened datetime
, Updated datetime
)
insert into @.table
values (1, getdate(), getdate()+ .10, getdate()+.15)
insert into @.table
values (2, getdate()+.3, getdate()+ .40, getdate()+.45)
-- Preview of the table
select * from @.table
-- Actual query
select RecordID
, MAX(ActionDate) LatestActionDate
from (
select RecordId
, Created ActionDate
, 'Created' Action
from @.table
union
select RecordId
, Opened ActionDate
, 'Opened' Action
from @.table
union
select RecordId
, Updated ActionDate
, 'Updated' Action
from @.table
) t1
group by RecordID
-- END SCRIPT
"Alec MacLean" wrote:

> Hi,
> I have a table in which there are several date columns recording different
> event points occuring to a record (e.g., date opened, date action-1, etc).
> I need to find the most recent date (MAX(date-n)) from across all these
> columns to compare with a final closure date.
> Is there a (simple/sensible) mechanism that makes this possible?
> I have considered using IF/ELSE to try and determine if one is later than
> the other, but this seems like a no-go (too complex to implement sensibly)
.
> CASE statement instead maybe?
> Any pointers gratefully received...Thx
> Al
>
>

Max [date] from DIFFERENT columns?

Hi,
I have a table in which there are several date columns recording different
event points occuring to a record (e.g., date opened, date action-1, etc).
I need to find the most recent date (MAX(date-n)) from across all these
columns to compare with a final closure date.
Is there a (simple/sensible) mechanism that makes this possible?
I have considered using IF/ELSE to try and determine if one is later than
the other, but this seems like a no-go (too complex to implement sensibly).
CASE statement instead maybe?
Any pointers gratefully received...Thx
AlAlec,
If you do not have a lot of columns, you can use the following approach:
Please let me kow if it helps...
-- BEGIN SCRIPT
declare @.table table
(RecordID int
, Created datetime
, Opened datetime
, Updated datetime
)
insert into @.table
values (1, getdate(), getdate()+ .10, getdate()+.15)
insert into @.table
values (2, getdate()+.3, getdate()+ .40, getdate()+.45)
-- Preview of the table
select * from @.table
-- Actual query
select RecordID
, MAX(ActionDate) LatestActionDate
from (
select RecordId
, Created ActionDate
, 'Created' Action
from @.table
union
select RecordId
, Opened ActionDate
, 'Opened' Action
from @.table
union
select RecordId
, Updated ActionDate
, 'Updated' Action
from @.table
) t1
group by RecordID
-- END SCRIPT
"Alec MacLean" wrote:
> Hi,
> I have a table in which there are several date columns recording different
> event points occuring to a record (e.g., date opened, date action-1, etc).
> I need to find the most recent date (MAX(date-n)) from across all these
> columns to compare with a final closure date.
> Is there a (simple/sensible) mechanism that makes this possible?
> I have considered using IF/ELSE to try and determine if one is later than
> the other, but this seems like a no-go (too complex to implement sensibly).
> CASE statement instead maybe?
> Any pointers gratefully received...Thx
> Al
>
>sql

matching saved searches to newly inserted record

Hello SQL wizards,
I'm trying to match saved searches to a newly inserted "job", and send
an email for matching searches. get about 20 job postings a day, and
have about 150k saved searches. want to do this as quickly as possible.
Please advise...
here's what I need to do:
1. job sers create saved searches, with criteria such as location
and some keywords
2. job is posted by a employer and inserted in job table
3. find all saved searches that match the newly inserted job
4. send emails to job sers with matching searches
right now, i'm doing the following:
1. using insert trigger on job table
2. put matching searches into a cursor (except by keyword search as I
can't figure out how to match by keyword using full text index all in
one statement)
select savedSearchId,...from savedSearches where (location='' OR
location=@.JobLocation) AND (duration='' or duration=@.jobDuration)...
3. loop throught cursor, doing
if(savedSearch has keywords)
select count(*) from jobtable where jobid=@.newlyInsertedJobId and
CONTAINS(*, keywords)
4. send email if matches keywords
this takes a while. there are about 150k saved searches. filtering on
non keywords returns about 3000 records to the cursor. the CONTAINS
search takes a long time.
Questions:
1. possible to do an asynchronous insert using ADO.net 1.1?
2. should i find the matching saved searches, put them in a table, and
do the keyword search/email later? if so, how?
3. how would you do it differently?
4. how to send email? xpsendmail or external component?
Thanks in advance!
Neilfound some problems myself:
1. full text index doesn't contain the new posting as it was just
inserted. should i do an incremental catalog population on insert?
2. contains() returns all rows that match the keywords, and THEN it's
filtered by jobid, so that's why it's slow...
any advice would be greatly appreciated.|||(neilmcguigan@.gmail.com) writes:
> 1. possible to do an asynchronous insert using ADO.net 1.1?
No and yes. There is no such thing as an asynchrounous insert, but
in your INSERT trigger just write a row to an alert table, and have
a job to run from SQL Agent (or scheduled by your own app) once a minute
or how often you see fit, to check for new entries.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||For instant propagation of changes to the FTI, you should use change trackin
g
and background propagation.
Look up sp_fulltext_table in Books Online.
ML|||Yes, both are true for SQL Server 2000...
For #1 you should enabled "Change Tracking" with "Update Index in
Background". The first initial setting of the CT with UIiB will
automatically run either a Full or Incremental population depending upon a
timestamp column in the table and if the FT Catalog is already populated.
For #2 you may want to use more sophisticated filtering with pre- and post-
processing as I once worked with a client in Europe who had a similar
requirement, except they were using FTS with a custom new clipping service
where the newspaper publishers were the employers and the newspaper reader
was the job ser. If you're interested, I may be able to put you in touch
with them. Feel free to email me directly if you want.
Thanks,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
<neilmcguigan@.gmail.com> wrote in message
news:1131222002.331389.73460@.g49g2000cwa.googlegroups.com...
> found some problems myself:
> 1. full text index doesn't contain the new posting as it was just
> inserted. should i do an incremental catalog population on insert?
> 2. contains() returns all rows that match the keywords, and THEN it's
> filtered by jobid, so that's why it's slow...
> any advice would be greatly appreciated.
>

master..xp_cmdshell Error 997 from GetProxyAccount

Hello all,
I have an aspx page that inserts a record in a table via a stored
procedure. That table has a trigger which gets fired on an insert. The
trigger has the following code
EXEC master..xp_cmdshell 'C:\MyApp\try.exe'
This all works when the stored procedure is fired inserting the record
in turn firing off the trigger; but when the ASPX page runs the stored
procedure I get the following error.
A severe error occurred on the current command. The results, if any,
should be discarded. xpsql.cpp: Error 997 from GetProxyAccount on line
472
The Guest login has permissions to the extended stored procedure
xp_cmdshell on the master db, but this has not helped.
Any help would be greatly appriciated.
PhilHi,
You need to set up a proxy account.
Create an user and then configure that in EM (Management, right-clickSQL
Agent, configure that proxy account).
Have a look into below article:-
http://support.microsoft.com/defaul...microsoft.com:
80/support/kb/articles/Q264/1/55.ASP&NoWebContent=1
Thanks
Hari
MCDBA
"Phil" <toomuchphill@.hotmail.com> wrote in message
news:8358f7c7.0406132044.6b353856@.posting.google.com...
> Hello all,
> I have an aspx page that inserts a record in a table via a stored
> procedure. That table has a trigger which gets fired on an insert. The
> trigger has the following code
> EXEC master..xp_cmdshell 'C:\MyApp\try.exe'
> This all works when the stored procedure is fired inserting the record
> in turn firing off the trigger; but when the ASPX page runs the stored
> procedure I get the following error.
> A severe error occurred on the current command. The results, if any,
> should be discarded. xpsql.cpp: Error 997 from GetProxyAccount on line
> 472
> The Guest login has permissions to the extended stored procedure
> xp_cmdshell on the master db, but this has not helped.
> Any help would be greatly appriciated.
> Phil|||Thanks for that Hari,
I am going forward... I think
The error I am getting now is General network error. Check your
network documentation.
I have now configured the proxy account within EM. I unticked the
'Only users with SysAdmin privileges can execute cmdExec and
ActiveScripting job steps.'
I have since changed the trigger from executing my .exe to execute a
.bat which simply copies a file. The Internet Guest account has
privileges to the directory.
The batch file is being run but the copy is not working. I guess this
is to do with the privileges still.
Any suggestions would be greatly appreciated.
Phil.
"Hari" <hari_prasad_k@.hotmail.com> wrote in message news:<eRBIv2cUEHA.3944@.tk2msftngp13.phx.
gbl>...[vbcol=seagreen]
> Hi,
> You need to set up a proxy account.
> Create an user and then configure that in EM (Management, right-clickSQL
> Agent, configure that proxy account).
> Have a look into below article:-
> [url]http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:[/ur
l]
> 80/support/kb/articles/Q264/1/55.ASP&NoWebContent=1
>
> --
> Thanks
> Hari
> MCDBA
> "Phil" <toomuchphill@.hotmail.com> wrote in message
> news:8358f7c7.0406132044.6b353856@.posting.google.com...|||- Start SQL Enterprise manager
- Open your server
- open the Management folder
- right mouse on SQL Server Agent and select properties
- select Job System tab
- UNCHECK the box in the section 'Non SysAdmin job step proxy account'
which says 'Only users with SysAdmin priveleges can ...'
- when the dialog comes up enter the username, password, [domain] of
some defined Windows user with sysAdmin priveleges (create one if needed fir
st).
- click Apply and OK
"Phil" wrote:

> Hello all,
> I have an aspx page that inserts a record in a table via a stored
> procedure. That table has a trigger which gets fired on an insert. The
> trigger has the following code
> EXEC master..xp_cmdshell 'C:\MyApp\try.exe'
> This all works when the stored procedure is fired inserting the record
> in turn firing off the trigger; but when the ASPX page runs the stored
> procedure I get the following error.
> A severe error occurred on the current command. The results, if any,
> should be discarded. xpsql.cpp: Error 997 from GetProxyAccount on line
> 472
> The Guest login has permissions to the extended stored procedure
> xp_cmdshell on the master db, but this has not helped.
> Any help would be greatly appriciated.
> Phil
>