July 14, 2014 at 7:18 pm
I have a problem and I don't even know where to start Googling.
I have two tables, a header and a detail table. Inserting a record into the header triggers a stored proc that gathers data from other tables and inserts it into the detail table. There are around 20 select statements that gather the data and insert it. None of the selects are at all complicated, there is no error handling in the stored proc it just goes through each of the statements and they either add some rows to the detail table or not.
Everything works fine if it's run in Management Studio but when it's triggered through an insert via ODBC it returns with no error message but nothing happens. No insert into the header and no inserts into the details table.
To add to the confusion it works fine via ODBC in one of our environments but fails in another that is in a different instance of SQLServer on the same box.
It's obviously an ODBC<->Sqlserver issue but I'm at a loss as to where to go next. It returns within a few seconds if it actually works or not so it's not a time out.
Can anyone give me some pointers as to where to go from here??
Thanks
July 15, 2014 at 11:14 am
There are several options:
1. Server-side trace to see what is really happening in SQL Server
2. Add error handling in the trigger/proc to log any errors.
3. Disable the trigger, run the app to get a header inserted, and then work through the proc to create the details to find an error.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 15, 2014 at 6:37 pm
Jack,thanks for the reply.
I'll give the trace a shot first as the stored proc works fine until triggered via ODBC and only in some environments. After my post yesterday we detached the DB from the instance it was failing in and re-attached it to the default instance and it worked fine.
Scott
July 31, 2014 at 10:05 pm
Just an update if anyone was wondering.
This got handed off to one of our DBA's and he eventually tracked down what seems to be the problem.
At each step in the stored proc there was a "print step X" statement used for debugging during dev.
It seems they were overflowing the buffer and causing the issue.
Removing the statements has fixed it.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply