May 4, 2012 at 11:51 am
Here is the lay of the land. I've got a .net application I'll call APP1 and two SQL server instances I'll call SQL1 and SQL2.
On SQL1 there is DB1 which contains a proc that inserts a row into Table1 of DB2 which is also on SQL1. There is an insert trigger on DB2 that does an insert to a database on SQL2.
From a query window in SQL enterprise manager I can execute the proc and the row gets inserted to Table1 of SQL1 which in turn fires the trigger and writes the row to the table on SQL2.
BUT....
When I execute the same proc, the exact same way, from my application, the insert trigger never fires.
So there must be some kind of right issue.
Where should I be looking
May 4, 2012 at 12:08 pm
bopritchard (5/4/2012)
Here is the lay of the land. I've got a .net application I'll call APP1 and two SQL server instances I'll call SQL1 and SQL2.On SQL1 there is DB1 which contains a proc that inserts a row into Table1 of DB2 which is also on SQL1. There is an insert trigger on DB2 that does an insert to a database on SQL2.
From a query window in SQL enterprise manager I can execute the proc and the row gets inserted to Table1 of SQL1 which in turn fires the trigger and writes the row to the table on SQL2.
BUT....
When I execute the same proc, the exact same way, from my application, the insert trigger never fires.
So there must be some kind of right issue.
Where should I be looking
When you execute the proc from your application, does the data you insert into the first table exist when the proc returns?
May 4, 2012 at 12:46 pm
It does not. If I disable the trigger it does.
May 4, 2012 at 1:04 pm
Means one of the triggers some where along the line is failing.
Since you disabled the first trigger and things worked, go to the next and work your way forward until you find the trigger that fails.
May 4, 2012 at 1:14 pm
There is only one trigger
May 4, 2012 at 1:18 pm
If I had to guess you're not seeing all the errors being kicked back all the way to .NET and you're dealing with a double-hop issue.
What's your linked server set to impersonate as? You most likely need to create a SQL User on SQL2 for SQL1 to talk to it as an impersonation for all users, or at least everyone who can call that proc.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
May 4, 2012 at 2:11 pm
but that's just it...there are no errors...
and i'm using sa impersonation for the linked server
May 4, 2012 at 2:28 pm
bopritchard (5/4/2012)
but that's just it...there are no errors...and i'm using sa impersonation for the linked server
i think Craig is right...errors are being supressed in the .net app, and stesting as sa / admin is not the same as testing as if you are a normal user.
try this yourself:
you should be able to know the login of the person who reports it's not working from the app right?
simply impersonate them in the query window you used before:
From a query window in SQL enterprise manager I can execute the proc and the row gets inserted to Table1 of SQL1 which in turn fires the trigger and writes the row to the table on SQL2.
--change my context from sa (where nothing ever fails1)
--to some guy getting errors:
EXECUTE AS USER='MyDomain\Lowell'
Execute myproc @SomeParameters
--turn back into myself!
REVERT;
Lowell
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply