Fishing for Ideas

[darude]

Hello All,
My company recently moved to SQL Server 2005 from Access so we are attempting to move our queries to SQL. With that said. We upload mass student data into our system. We then check for school transfers. If a school transfer occurs I want to assign the student to the new school's case manager, but also notify the former case manager that their case has been transferred hopefully through email. If anyone has suggestions or somewhere to point me it would be greatly appreciated.

 

[RiverGuy]

Here's how I would do it, without knowing more:

1.Configure Database Mail on your SQL Server.
2.Create another, audit-type table to hold student id, current school id, new school id, current case manager id, new case manager id, and Processed flag.
3.Add a trigger to your table which tracks which school the student is in. When the school id is updated, write to that new audit type table with the old and new values.
4.Create a SQL Agent job to query that audit table every n minutes. The job would also need to do a join to other tables to get the valid email addresses for the addressees. Send an email for each record where Processed = 0. Once the email is sent, set Processed = 1.

 

[kjv1611]

Well, I could be going down the wrong path compared to what you want, but I suggest taking that whole thing apart, and working on one piece at a time.

First, you've got to get the underlying tables and queries setup.

THEN you can worry about the email portion.

So, you might say:
[OL][LI]My table definitions are:[/LI]
[LI]My table relationships are (based on fields, of course):[/LI]
[LI]I need these queries:
[OL][LI]INSERT query to move the student's data from one table to another, with associated DELETE query for old record(s)... or[/LI][LI]UPDATE query to change the case manager, etc...[/LI][LI]And at least one SELECT query for pulling appropriate data after the change to let the necessary folks know of the change.[/li][/OL][/LI]
[LI]I need a script for notifying required persons via email of any said changes above. That script would not necessarily be something for this forum - but I could be wrong.[/LI][/OL]

Anyway, hopefully that might give you some ideas.

If you and/or your company cannot complete all of this, it sounds like it would be something worth hiring a contractor for. That might end up being where you'd best look... again, IF it's really important and you don't know enough or just can't get your mind wrapped around it well enough... Just thinking from a business perspective. Basically, you want to make sure the job gets done, I'd imagine. [WINK]

Oh, if you ended up going the route of a contractor, I'd not be the guy. I have a LOT of learning to do, myself. [SMILE]

--

"If to err is human, then I must be some kind of human!" -Me

 

[darude]

Thank you for you suggestions. I just needed the how to from the SQL Server side. Again thank you. You guys are fantastic. I love this website.

 

[NuJoizey]

Here are a bunch of links that I found useful when I was learning to setup and use sql server database mail. Maybe you'll find some of them useful too.

http://msdn.microsoft.com/en-us/library/ms175887.aspx

http://www.databasejournal.com/feat.../3626056/Database-Mail-in-SQL-Server-2005.htm

http://www.sqlserverclub.com/articles/article05.aspx

http://www.tek-tips.com/viewthread.cfm?qid=1430755&page=1

http://msdn.microsoft.com/en-us/library/ms190307.aspx

http://www.tek-tips.com/viewthread.cfm?qid=1442090&page=1