Any SQL Server Programmers/DBA's?

[PJC]

In case there are, I'll post my problem.....


The college that I work at has a third party software package that among other things allows users to access information about their classes, transcripts, bills, etc. on the web. Obviously, the back-end is SQL Server. In the instruction manual, they explain how to bulk-insert users to create their accounts using DTS. I have tested this package on our development server and it works like a charm.

Basically, it takes a csv file and bulk loads the information into a "bulk insert" table. This table has an insert trigger that encrypts the password provided, among other things, and insert the information into another "accounts" table. This "accounts" table is where the application pulls from when the user logs in.

This works well on the test site, but in live, the trigger doesn't fire off. The DTS package executes properly and the data from the csv file is loaded into the "bulk insert" table, but the data is not being transfered from the "bulk insert" table to the "accounts" table via the trigger.

Any thoughts as to why a trigger wouldn't work. Our live server is a clustered server if that makes any difference. I've tried dropping the trigger on the live server and re-creating it with the one from the test server. I even tried running a trace (which I'm not very good at) with no luck.

ideas appreciated.

Thanks,
Pete

[JSUPreston]

Are you sure that your trigger fires on both insert and update? That's the only thing I can think of. I'm a SQL DBA, but I don't do any programming. I handle the server itself, backups, restores, cover your ..., etc. If I can think of anything else, I'll post.

Also, when are you looking at moving to SQL 2k5? If it is soon, you may not want to spend too much time on this, since DTS is a thing of the past in SQL 2k5. Now you use Integration Services (or something similar sounding). We just loaded it at work, and it is very different than SQL 2k. It looks like it runs INSIDE of Visual Studio 2k5. Nothing is where I remember it being. If you're going to 2k5 soon, you may want to load it on a test bed just to get familiar with the interface.

[PJC]

It is definitely an insert only trigger on the table. I, however, did not write it as it came as part of the software package that we bought. It's very weird. I have never heard of a trigger not firing. I can't even find anything on Google. I did run a command to disable and then enable the trigger, but that didn't solve the problem.

This needs to be done by the end of this week, so I can't hold off on it. Everything else works as expected, it's just this trigger. Like I said, it works fine in the test environment. I don't know if there are certain permissions that can be set for specific objects? Maybe I don't have permissions on this trigger? Shouldn't be a problem as my account should have full sa permissions.

We are going to go to SQL2k5, but probably not until next summer. We are going to order a copy in the fall to install on a test server. However, there are some bugs with the software package we bought and 2k5. I want to wait until the bugs are solved before upgrading.

Pete

[RayintheUK]

I ran this by a friend of mine, who replied as follows:

"This smacks of permissions. The trigger comes from a third party and works on the test server. Therefore there is nothing fundamentally wrong with the code.

You need to check what userid is being used by the process that fires the trigger and make sure that userid has insert/update permissions on the relevant table on the live machine(s)."

Hope that may help? Hope so!

Ray.

[Luckbox]

I agree with Ray, Check the permissions on the database and the table that the trigger inserts to. You will probably find that you are either not a valid user in the database or that you do not have permission to insert data into the table.

I would be very supprised if this was not a permissions issue of some sort.

[PJC]

I think it's a permissions issue too, but I have given the login id that does the insert full permissions to both tables and it still doesn't work. Maybe there are permissions I don't know about. The login ID has full Update, Insert, Delete and Execute permissions on both tables.

Pete

[JSUPreston]

User ID gave me another thought. Check to make sure that the owner of the table is dbo. At my shop, we script all tables with dbo as owner, since any table not owned by dbo may not be accessible to other users.

Then again, if the table owner isn't supposed to be dbo due to your software, I wouldn't muck with it too much.