January 30, 2014 at 1:37 pm
Hi all,
I have a wierd one. I can't provide specific code due to proprietary concerns, but let me see if I can at least outline the basic nature of the problem. I'm working with a "rules engine" that applies SQL statement logic automatically to incoming ETL data in staging tables for validation and transformation, as needed. The processing starts with a master stored proc, that then calls various other stored procs to handle specific pieces. The piece that executes the various logic elements can handle either function-like statements or full SQL code, based on configuration within the logic table.
Anyway, one of the basic elements of ALL the rules is the textual replacement of certain key text values in the SQL, such as database names, table names, etc., and certain criteria values. To make a long story short, I have an update query that's over 11,000 characters long, so I put it into a stored procedure. I can run that proc from SSMS and it updates the database successfully. I can NOT get that procedure to update the db at all when it runs through the rules engine, and there just doesn't appear to be any logical reason for it. The user context isn't an issue, as replacing the rule logic SQL text with an execute of a dummy update stored proc that updates an unused field runs just fine and updates that data without a problem. Because the normal process of the rules engine involves replacing query elements like db names and criteria values, I have to do the same thing with my procedure. The rules logic has the necessary replaceable elements as parameters to the proc, which then replaces the values within the stored procedure's SQL variable, which is varchar(max), using REPLACE() function. The only thing I can think of is that somehow the level of nesting of dyamic SQL is causing a really wierd problem, but I checked Google and found that @@NESTING can go up to 32 before there's an error, and I don't get an error. The rules engine operates within a BEGIN TRY / END TRY, BEGIN CATCH / END CATCH, and my stored proc also does this. Any chance that nesting of TRY/CATCH is the issue ?
Any/all advice about things I might look for would be most appreciated.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
January 30, 2014 at 1:52 pm
It may be an unhandled error so it's failing silently.
I'd start with a trace of the process so you can see exactly what the rules engine is providing to the database. It's really the only place to start. You're too disconnected from the error interface to be sure where the problem is. Make sure the statement gets to the server, and make sure it's complete with the replacements so that it looks the way you expect.
After that, troubleshoot upstream/downstream as appropriate.
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
January 30, 2014 at 2:01 pm
That was why I tried just replacing the one rules' logic SQL to execute a different proc that just does a lot less. Clearly, the rule is getting executed, but something about the more complex procedure may be failing in a way that the CATCH of the calling stored proc isn't seeing. I'm not hopeful about getting a trace anytime soon, but I don't see any other way, given that manual execution of the stored procedure OUTSIDE of the rules engine works just fine. As I have TRY / CATCH within my stored proc, I'd have thought errors would be caught, as there are no remote sessions to worry about.
Evil Kraig F (1/30/2014)
It may be an unhandled error so it's failing silently.I'd start with a trace of the process so you can see exactly what the rules engine is providing to the database. It's really the only place to start. You're too disconnected from the error interface to be sure where the problem is. Make sure the statement gets to the server, and make sure it's complete with the replacements so that it looks the way you expect.
After that, troubleshoot upstream/downstream as appropriate.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
January 30, 2014 at 2:08 pm
I'm thinking your proc is reporting an error to the engine, and the engine is losing it, not necessarily that your proc is losing it.
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
January 30, 2014 at 5:16 pm
I think I've ran into a similar issue.
I had to trace through the code.
If you're using dynamic sql, I assume you are using sp_executeSQL @withsomeparameter ?
If so I would check the sql parameter at each level and make sure the code hasn't been truncated or not what you were expecting.
In my cases I was either missing some spacing, parameters weren't log enough, or I wasn't accounting for null parameters.
That was my experience. Hope it helps.
January 30, 2014 at 9:08 pm
I know the substitution works correctly because we have that working on ALL the rules, so I know it can't be failing here unless something to do with nested EXECUTE statements combined with being in the middle of a TRY / CATCH construct has something to do with it...
Here's a pseudo-code version of what happens:
EXEC master proc @parms....
... within the master proc:
EXEC ProcessLogic @parms
... within the ProcessLogic proc:
BEGIN TRY
EXEC (@SQL) -- the @sql contains the EXECUTE myUpdateProc @parms
END TRY
... within myUpdateProc:
EXEC (@SQL) -- the dynamic SQL assembled by my update proc based on @parms
... within the dynamic SQL:
BEGIN TRY
BEGIN TRAN T1
BEGIN TRAN C1
COMMIT TRAN C1
BEGIN TRAN A1
COMMIT TRAN A1
COMMIT TRAN T1
END TRY
BEGIN CATCH
SET error vars
EXEC sp_LogError @parms
ROLLBACK TRAN T1
END CATCH
... then repeat that a second time... with different tran names
UPDATE
UPDATE
UPDATE
UPDATE
Not sure if that helps, but it's the basics... Anyone have any ideas ?
Again, standalone within SSMS, this works just fine, and I've verified that the parameters are correctly being set. I don't suppose that REPLACE has undocumented limitations? The final @sql is over 11,000 characters, and I seem to recall someone somewhere talking about that defaulting to 8,000 ... but then, it would fail the same way in SSMS if that were the case.
stmu009 (1/30/2014)
I think I've ran into a similar issue.I had to trace through the code.
If you're using dynamic sql, I assume you are using sp_executeSQL @withsomeparameter ?
If so I would check the sql parameter at each level and make sure the code hasn't been truncated or not what you were expecting.
In my cases I was either missing some spacing, parameters weren't log enough, or I wasn't accounting for null parameters.
That was my experience. Hope it helps.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
January 30, 2014 at 9:23 pm
Try replacing exec (@sql) with select @sql to see what's actually running in the app.
For some reason, I remember reading something about not not using exec (@sql). Can't remember any specifics though. Try using sp_executeSQL instead and see if that changes anything.
Not sure if I would be of any help other than that...
January 31, 2014 at 7:22 am
Unfortunately, that fails to produce a change in behavior. Has anyone heard of Sophos anti-virus causing depth of process call to fail without any errors getting triggered in SQL Server 2008 R2 ? A coworker suggested the possibility because apparently it does happen in the C# .NET environment if they get too fancy with nested calls from one thing to another...
stmu009 (1/30/2014)
Try replacing exec (@sql) with select @sql to see what's actually running in the app.For some reason, I remember reading something about not not using exec (@sql). Can't remember any specifics though. Try using sp_executeSQL instead and see if that changes anything.
Not sure if I would be of any help other than that...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
January 31, 2014 at 7:46 am
Are you rolling back your transaction in the CATCH AFTER you write to the error log? i.e. rolling back your record of a problem occuring.
sequence should be I guess
...catch
--- rollback
......log error.
and not
...catch
..... log error
.......rollback.
February 2, 2014 at 10:24 pm
I don't get to use trans often, so I missed that part, but ultimately, that wasn't where some of the problem turned out to be. I had decided to try hard coding everything to eliminate the dynamic SQL, and of course, forgot that I had this designed around the transaction use, and spent hours assuming my query was fine when the join for the 2nd part of one particular update was now pointing to a field that just got updated, and thus had to be joined differently. Net result is that I went back to using the transactions, but a tad bit differently. I still have an issue with the rules engine, as I need to run it twice to get it to work, but at least it works on the 2nd run AND does most of what it is supposed to do. I will have to research the rules engine processing further and see what comes of it.
David McKinney (1/31/2014)
Are you rolling back your transaction in the CATCH AFTER you write to the error log? i.e. rolling back your record of a problem occuring.sequence should be I guess
...catch
--- rollback
......log error.
and not
...catch
..... log error
.......rollback.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 6, 2014 at 7:15 am
The issue has been resolved. We found that one of the core assumptions the rules were based on was not actually true. Table data was being copied into place in pieces, rather than all at once in advance, so some rules could see all the data they needed, and others could not. A very frustrating situation that is thankfully, dealt with.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply