Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
pjc
Starting Member
7 Posts |
 Posted - 2006-08-07 : 15:37:53
|
| I don't know if this is the best way to do this, but let me explain....I work at a college and we have a third party package that we use for our students, faculty to view items online (course schedules, transcripts, etc.). This software also came with instructions on setting up a DTS package to bulk load user_id's and passwords from a comma delimited file. This package can be run as many times and will add new users or update any info you change on the file for existing users. So, if I need to change a users password, I can update that users record on the file, run the DTS package and the users password will be changed.Here's where the problem:We have a link that a student can use to reset their password. We want it to be a single-sign on, so I need to incorporate this new third part software. I can only change passwords on the new software by using their DTS package. Right now, I have a table that the information gets stored in and then the DTS package creates a comma delimited text file from that table and then imports that file into a table from the vendor. Let me try and diagram...myTable--->comma separated text------>Vendor table---->their table has triggers and stored procs that encrypt the password and put it in another table of theirshave I lost you yet? I have it working so when the user resets their password, it changes on my table, but in order to change it on the vendor's tables, I need to execute the DTS package. I tried creating an update trigger on my table to kick off the DTS package, but it seems to process forever. I don't think SQL server likes to run DTS in a trigger. Below is my trigger....please note p_Exec_DTS is a stored procedure that accepts a string as the variable @package and then runs "Exec master..xp_cmdshell @package"Create Trigger tu_myTrigger on MyTable For update AS BEGINexec p_Exec_DTS 'dtsrun /S servername/U sa /P sapassword/N DTS Name'ENDGOThe exec part of the trigger works fine if I run it by itself.I'm sure this is confusing. If I can clear anything up, please let me know.Pete |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-08-07 : 18:08:36
|
| Don't run DTS in the trigger.Load the info to be changed into a table from your trigger, and let a scheduled job run the DTS package, using that table as a data source.CODO ERGO SUM |
 |
|
|
pjc
Starting Member
7 Posts |
Posted - 2006-08-08 : 08:32:51
|
| Thanks Michael. I may have to do it that way, I was just hoping to make it more instantaneous....though the job runs pretty quick, so I can set it to go off every 1/2 hour - hour.Pete |
|
|
|
AugustinPrasanna
Starting Member
3 Posts |
Posted - 2006-08-09 : 04:16:37
|
| You can even create a job (need not schedule it) and trigger the job using sp_startjob procedure. |
|
|
|
|
|
|
|
|
Executing DTS in a trigger