Similar to the cursor suggestion, I used a temp table to build the results then read them in. The final solution I had was to change the database design to use a join table to resolve the relationship into discreet records. (They can still come out as a comma del string for display purposes.) Once that was done, there were individual records for each value and are directly queriable without the mess in a sproc or other code.

Fortunately...

This turned out to be a symptom of a mis-configured server with a linked server. The RPC settings were not turned on, so were not cleared in a timely fashion if the connection was severed/halted.

I am populating an object from MS SQL2000 server. The SQL server has a linked server which provides some of the information. Everything works OK, but if there is an error and an exception gets thrown when attempting to read the datareader AND the connection object remains active. To get it to work again, I have to close VS2005 and reopen the project. the linked server may be holding open the connection. If I re-run the debugger, it's as though the...

//using C# 2005, MS SQL 2000(prod) and SQL Express 2005 (dev)

My sprocs all have a clearing mechanism at the end like this for each temporary table I use:
--drop temporary tables
IF EXISTS(SELECT name FROM [DBNAME]..sysobjects WHERE name = N'#Temp' AND xtype='U')
DROP TABLE #Temp

Every once in a while, something will happen to cause a hiccup and the table doesn't clear. The result is no results,...