Auto generate return type for stored procedure containing EXEC

Question

I have the following in a stored procedure:

DECLARE @Query varchar(8000)
   

    SELECT @Query=
    'SELECT
        ....'

    SET @Query = N'SELECT * FROM OPENQUERY(SERVER, ''' + REPLACE(@Query, '''', '''''') + ''')'

    EXEC(@Query)


For some reason Linq won't auto generate a return type. Even if I create a temp table and insert the results of the EXEC into the table and then select from it Linq still refuses to generate a return type.

How can I get this to work?

Answer 1

I have the same problem. Hopefully you found a solution.

regards,


steve

Answer 2

Unfortunately, nothing will get it to work. The mechanism in SQL Server that provides this information returns nothing when the stored procedure uses a temp table or a dynamic query (via exec).  You'll have to specify the signature of the method by hand in the source code.

---

Wayward LINQ Lacky

Answer 3

I had the same issue and while editing the dbml file by hand worked fine, the easier 'fix' was to simply comment out the 'exec' line in the stored procedure, then drag the stored procedure into the model, save, then uncomment the exec line in the stored procedure again... of course that is only going to work if the procedure uses a select statement to return the data.