Puzzle: Fixed, and now it almost makes sense.
The puzzle I posted about yesterday is fixed.
The immediate cause, that has resolved the issue, turned out to be some bad data in the tables that generate the dynamic SQL. A few rows were lurking that had NULL values, and that meant when they got appended to the other values necessary to make the SQL code. When SQL server appends NULLs to other strings the result is always NULL.
Now, I knew this, and I also designed my code with the expectation that there might be some queries with nothing but dates in the WHERE clause. So I threw in a COALESCE() call that says, "if the WHERE clause is blank, replace it with '1=1'". It's just a placeholder, because there's also some logic that appends "AND period_year=2008...". To keep the "AND" syntactically correct, I just wanted to throw in some condition that would always evaluate to true.
So, the intended behavior was that, if there was no WHERE clause, it would generate the following: "WHERE 1=1 AND period_year = 2008...". There was only one problem: I appended the "AND period_year" bit before I coalesced to 1=1.
Follow with me:
@whereClause is NULL.
I append "AND period_year" to @whereClause, and the result is NULL.
I then COALESCE to convert the NULL to "1=1".
I then send a query over to the remote system with the following WHERE clause: "WHERE 1=1".
And the query never completes. That was the immediate problem, and it's fixed now, but it raises a few other questions:
1) Why did the query complete when run on its own?
2) When this query was #11 in the sequence, why did the output hang at query #6?
My best guess answer to question #2 is that SQL Server's output was lying. It just didn't flush the output buffer and so it told me I was on query #6, but really it was busy hanging on query #11. Which is interesting behavior, and only reinforces how damn difficult it is to debug TSQL.
The moral of the story, of course, is that dynamic SQL is bad. I hate it, and I hate having to do it. This whole application is one of my least favorite projects, and sadly, there's really no way to avoid getting stuck with dynamic SQL given the objectives, unless I wanted to write it in .NET. Oh, that's right, I did want to write it in .NET or SSIS, but the user demanded that each query be implemented via a stored procedure.
Since SQL Server doesn't let you specify the linked server name via a variable, there is absolutely no way to do this without resorting to dynamic SQL. It's awful. Awful. I know people find Oracle frustrating to administer, but when it comes to programming in PL/SQL, I know that I'm not going to bump into arbitrary seeming restrictions, like "functions can't modify temp tables, even if the temp table is a locally created table variable, because we just don't like letting functions modify data, even if the only way to get data back from dynamic sql in Sql Server is to have it insert its results into a temporary table".
Ugh. Lesson learned: next time a customer specifies a technical preference for implementation details, I politely tell them to go fuck themselves and do it my way.
The immediate cause, that has resolved the issue, turned out to be some bad data in the tables that generate the dynamic SQL. A few rows were lurking that had NULL values, and that meant when they got appended to the other values necessary to make the SQL code. When SQL server appends NULLs to other strings the result is always NULL.
Now, I knew this, and I also designed my code with the expectation that there might be some queries with nothing but dates in the WHERE clause. So I threw in a COALESCE() call that says, "if the WHERE clause is blank, replace it with '1=1'". It's just a placeholder, because there's also some logic that appends "AND period_year=2008...". To keep the "AND" syntactically correct, I just wanted to throw in some condition that would always evaluate to true.
So, the intended behavior was that, if there was no WHERE clause, it would generate the following: "WHERE 1=1 AND period_year = 2008...". There was only one problem: I appended the "AND period_year" bit before I coalesced to 1=1.
Follow with me:
@whereClause is NULL.
I append "AND period_year" to @whereClause, and the result is NULL.
I then COALESCE to convert the NULL to "1=1".
I then send a query over to the remote system with the following WHERE clause: "WHERE 1=1".
And the query never completes. That was the immediate problem, and it's fixed now, but it raises a few other questions:
1) Why did the query complete when run on its own?
2) When this query was #11 in the sequence, why did the output hang at query #6?
My best guess answer to question #2 is that SQL Server's output was lying. It just didn't flush the output buffer and so it told me I was on query #6, but really it was busy hanging on query #11. Which is interesting behavior, and only reinforces how damn difficult it is to debug TSQL.
The moral of the story, of course, is that dynamic SQL is bad. I hate it, and I hate having to do it. This whole application is one of my least favorite projects, and sadly, there's really no way to avoid getting stuck with dynamic SQL given the objectives, unless I wanted to write it in .NET. Oh, that's right, I did want to write it in .NET or SSIS, but the user demanded that each query be implemented via a stored procedure.
Since SQL Server doesn't let you specify the linked server name via a variable, there is absolutely no way to do this without resorting to dynamic SQL. It's awful. Awful. I know people find Oracle frustrating to administer, but when it comes to programming in PL/SQL, I know that I'm not going to bump into arbitrary seeming restrictions, like "functions can't modify temp tables, even if the temp table is a locally created table variable, because we just don't like letting functions modify data, even if the only way to get data back from dynamic sql in Sql Server is to have it insert its results into a temporary table".
Ugh. Lesson learned: next time a customer specifies a technical preference for implementation details, I politely tell them to go fuck themselves and do it my way.