Just wanted to share a stupid .Net trick. :)

When working with LinQ and the ISingleResult interface, returning the result from the complex stored procedure (building certain data in pre-defined format) I've hit the problem that turned out to be a deficiency in current LinQ/SqlMetal incarnation.

The problem errors out like this:

Warning : SQM1014: Unable to extract stored procedure 'dbo.myStoredProcedure' from SqlServer. Invalid object name '#projectAssignments

It cannot work with the results, obtained using the dynamic SQL in the stored procedure. (You could see some examples here: http://code.google.com/p/lbob/wiki/SqlMetal )

I did not want to rewrite the SP in a different way, and the solution I found was to make a wrapper stored procedure like this:

CREATE PROCEDURE [dbo].[MyStoredProcedureWrapper]
@employeeId int,  @startDate datetime, @endDate datetime
AS
BEGIN
.
		SET NOCOUNT ON

DECLARE @assignments TABLE 
		(
		projectid int,
		projectname varchar (50),
                                 ...
		)

INSERT INTO @assignments EXEC MyStoredProcedure @employeeId ,  @startDate , @endDate 

SELECT * FROM @assignments

END

That did the trick - SqlMetal does not complain anymore and creates the proxy classes code with no error.