May 3, 2012 at 3:13 pm
Hi everyone,
I have a simple trigger that updates another table when a new value is inserted.
Basically, when a printing slip is printed, it inserts a date into the ITEMSENT table, then the trigger is fired and inserts the date into the Contact2.Userdef24 field.
This works for only one user but not the other. I did use the "EXECUTE AS OWNER" in the script but it did not work after that.
The software connects to our CRM database via ODBC and SQL Authentication.
Could this be an ODBC scenario? The user for which the trigger is working is running 32bit Vista and the user for which the trigger doesn't work is using 7 64bit. Both have the same identical settings on both 32 and 64 bit ODBC settings on the Windows 7 machine. Both connect to the database successfully.
Both users also have identical SSMS settings.
What could be going on here? I am absolutely stumped and I'm betting it's something completely trivial I am overlooking.
Here's my trigger:
USE [Goldmine]
GO
Trigger [dbo].[INSERT_FEDEX_SHIP_DATE]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[INSERT_FEDEX_SHIP_DATE]
ON [dbo].[ITEMSENT]
FOR INSERT, UPDATE
AS BEGIN
UPDATE dbo.CONTACT2 SET dbo.CONTACT2.USERDEF24
= CASE
WHEN ISDATE(SHIPDATE) = 1
THEN CAST(SHIPDATE AS DATETIME)
ELSE GETDATE() --?assume today?
END
FROM INSERTED
WHERE INSERTED.ACCOUNTNO = CONTACT2.ACCOUNTNO
END
May 3, 2012 at 4:26 pm
When you say it doesn't work for one user, exactly what do you mean?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 4, 2012 at 7:02 am
When one user runs our FedEx software on their pc, it initiates the trigger. When another user runs the FedEx software on their seperate pc, the trigger doesn't initiate.
May 4, 2012 at 7:07 am
Does everything else work for this user when running the FedEx software?
Does the software properly update everything it is supposed to update?
May 4, 2012 at 7:10 am
Everything else runs exactly as normal for the user.
May 4, 2012 at 7:58 am
Are you talking about locally installed databases, or about two users connecting to the same database on a server?
If local, check the setup at each. It's probably SQL Express, might be different versions/patch-levels. Might be one is missing a linked server or has different settings on a linked server. And so on.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 4, 2012 at 8:55 am
Are you using roles here for security to be sure the two users have the same access?
Is there perhaps some other trigger on the table that is causing issues?
Can you debug this by adding a line that writes to a local logging table all the relevant values, including user_name()? Or suser_sname()?
May 4, 2012 at 12:58 pm
The only roles the two users have are public, read, and write and they're both setup identical in the database.
I'm wondering if it's a case of 32bit vs. 64bit
May 4, 2012 at 1:01 pm
joshd 1807 (5/4/2012)
The only roles the two users have are public, read, and write and they're both setup identical in the database.I'm wondering if it's a case of 32bit vs. 64bit
Should not be an issue. There is something else going on, just need to dig deeper.
May 4, 2012 at 4:39 pm
Definitely not x86 v x64. The way the database works and disk structures are the same.
There has to be something with security, or perhaps some other issue. If you post a little more detail or code about how access to db1 v db 2 works, perhaps we can help.
Can both users do a direct insert or update against CONTACT2 ?
May 7, 2012 at 10:48 am
Both users have direct update and insert, that's why I cannot understand why the trigger will not fire for this one user.
The CRM users have a generic SQL User that is used to connect to the database. The FedEx software connects to the same CRM table (CONTACT2) with ODBC that uses SQL Authentication. The FedEx data is stored in a table called ITEMSENT which is located in the CRM database.
Hopefully this helps someone out
May 7, 2012 at 10:52 am
The only suggestion I have for you at this time is to setup a server-side trace to see what is happening when this user that isn't firing the trigger does their work.
May 7, 2012 at 12:11 pm
Thanks for the insight everyone.
As someone who isn't a seasoned SQL Server vet, this is great information.
May 7, 2012 at 12:32 pm
Let us know what you find, and if you have specific questions, post back.
One thing I'd caution is not to assume anything here, especially with security. Use SETUSER and actually test all your inserts, updates, etc., both remotely, and locally in each database. Make sure that it is functioning as you assume it is.
May 8, 2012 at 3:15 pm
Here's what I figured out...
Using SQL Server Profiler, I enabled SP:StmtStarting SP:StmtCompleted in the profiler options to see when the trigger is being run.
When the FedEx software inserts data into the ITEMSENT table, the trigger is, in fact, being ran but the data is not getting updated in the CONTACT2 table as stated earlier. I am verifying this through a phone number that is inserted into the ITEMSENT table.
Now the only thing is, what else could I enable to see if the table is actually being updated?
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply