Comms link failure / semaphore timeout - is the server starved of resources at that point?  What jobs are running at the point of failure?  Anything really heavy duty?

Here's one option using windowing functions and aggregates.

WITH
CSIDChange -- mark the first line of each batch / island
  AS
  (
   SELECT...

Have you looked in the SQL Server logs for the error state code for that error 18456?  If so, what was it?  If not, then go find it!

An alternative is to use the "dynamic crosstab" query.  For example, using the temporary table provided above, something like:

SELECT @query = 'SELECT ItemID, Description ';
SELECT...

Ah, right.  +1 for Thom A's suggestion of Unicode Left-to-Right / Right-To-Left characters embedded in the string.

interesting that this also reverses the direction of the brackets.  🙂

I'm finding it difficult to spot the difference here...  can you highlight the problem areas?

Can you provide an example of you making the direct query, and an invocation to the stored procedure?

And, if possible, the definition of the stored procedure?

Also be sure to check the full SQL Server error log - probably at something like C:\program files\microsoft sql server\mssql10_50\mssql\log\errorlog (assuming you installed using the default settings)...

I know this was a followup to an old SQL2008 question, but, if you're running SQL 2017, check out the new DMV `sys.dm_os_enumerate_filesystem`

eg:

SELECT *...

Are you doing replication using this database / has this database ever been involved in any replication?

What does `DBCC OPENTRAN` say when you run it in that database?

just one thing... please tell me you're not storing money / financial values as `float`s?