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-12 : 15:39:31
|
| The college I work for has a third party package for users to access information such online such as their course schedule, transcripts, etc. The documentation for this package comes with instructions on bulk loading users using DTS. Basically the package takes a csv file and loads it into one of the vendor's tables. This table has an insert trigger that fires off and actually creates the accounts in a different table. Everything works fine on our test server, however when I try to put it into production,the trigger doesn't fire off. The DTS package completes successfully and the table that the csv file loads into is populated with the info from the file, but the "accounts" table that get's populated from the trigger is not being populated, hence the accounts are not actually being created. The package on the test server that works and the package on production are the same other than the names of the servers. I've tried changing the connection from "windows authentication" to specifically the 'sa' user/password. I even dropped the trigger on the live server and re-created it using the trigger from the test server, still no luck. Maybe it's a permissions issue, but I am not sure where to look for something like that. I tried to run a trace, but I'm not good at doing that, so I don't know that I set it up right. Any thoughts as to why a trigger might not be executing?Also, the production site is a clustered server if that makes any difference.Thanks,Pete |
|
|
pjc
Starting Member
7 Posts |
Posted - 2006-08-13 : 11:32:10
|
| Also, as a follow up...the trigger executes properly when I do a manual insert statement inserting one record. |
 |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-08-13 : 21:20:44
|
| how are you inserting records in the trigger?insert into tablename(field1,field2...)select field1,field2... from inserted--------------------keeping it simple... |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-08-13 : 22:03:21
|
| I am assuming you have the fastload option checked in your DTS package. fastload in DTS actually uses bulk insert or bcp to load the data. These bulk operations bypass triggers by default unless the FIRE_TRIGGERS hint is used. There is no way that I know of to enable FIRE_TRIGGERS within DTS, so your only solution is to disable fastload (if it is enabled). -ec |
|
|
|
pjc
Starting Member
7 Posts |
Posted - 2006-08-14 : 08:34:53
|
| Jen- I did not create the trigger, the trigger is part of the program that we bought. I just created the DTS package as per our vendor's specs. The specs use a .csv file (I am using a comma separated .txt file). The trigger that they provided is using "from inserted".Eyechart - I can't find the fastload option. Where would I be able to check if it's enabled or not.I also should have been more specific on bulk load. I am not using the bulk load command from DTS, I am just loading a bunch of accounts (about 375) from a .txt comma separated file. Thanks. |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-08-14 : 10:08:25
|
quote:
Originally posted by pjcEyechart - I can't find the fastload option. Where would I be able to check if it's enabled or not.
fastload is an option in the transform data task. If selected, then DTS will use bulk load automatically. this will bypass trigger ezecution.-ec |
|
|
|
pjc
Starting Member
7 Posts |
Posted - 2006-08-15 : 08:28:09
|
| I fixed the problem. I re-designed the vendors package. Instead of loading the information from a csv file, I insert the bulk records into another table. I then run "insert into 'table with trigger' select * from 'mytable'"Works fine....trigger executes.Thanks for the input! |
|
|
|
|
|
|
|
|
Trigger not executing