Problem
Today I was struck with an annoying problem while generating LINQ to SQL dbml file for a stored procedure using Visual Studio 2008.
When the stored procedure is dragged and dropped on the DBML designer, Visual Studio is generating wrong signature for the stored procedure output: instead of returning a result set as output, an int value is set as output.
Possible Cause
Only one stored procedure out of 100s available in the dbml file is affected with the bug in LINQ to SQL class builder. When the stored procedure is closely examined, the sp is returning a single record as output and the values of output columns are calculated in the sp(they are not retrieved from tables directly). Looks like it is the cause for misbehavior of Visual Studio 2008.
Solution or Workaround
After spending couple of unfruitful hours in fixing the issue directly, I Google for solution to the issue. Luckily a blog post gave a tip on work around to sort out this annoying issue. The work around is
- Create a dummy stored procedure with same signature(input parameters & output columns) by returning dummy values
- Generate the dbml file using the dummy stored procedure
- Drop the dummy stored procedure and create the actual stored procedure on the database
Here is the dummy stored procedure used for generating the dbml file
CREATE PROCEDURE pGetProfileDetails
(
@P_IdentifyingUID BIGINT,
@P_IdentifyingType VARCHAR(50)
)
AS
BEGIN
SELECT 'test' AS Name,
'test' AS Industry,
'test' AS FunctionName,
'test' as JobRoles,
'test' as Skills,
'test' as Specializations
END
Pingback: Anon
Thanks a lot with solution SET FMTONLY OFF
How can i use LINQ with stored procedure with dynamic columns? Any idea?
I had a similar problem trying to get result from parameterized stored proc. Here is the fix.
Parametrized stored proc compiles and executes on the runtime so dbml generation tool is not sure of the resulting IsingleResult object .To make this work just copy again your select statement at the end of the stored proc and execute it.Now try dragging storedproc from server explorer and it should work as usual. Don’t forget to takeoff the copied select statement at the end of the storedproc after dbml generation is done.
Thank you Max. Brilliant one liner. I’m now a happy chappy
I face the same issue, and resolve it by setting FMTonly to off at the last of Stored Procedure before commit.
and it works fine for me.
I don’t agree above is the best solution, when your stored procedure need to change that add one more column, how to do?
Because you need to create the strongly type again by another stored procude.
I have Dynamic Sql Stored Procedure and Temp Table also creates using Dynamic Sql Columns. Anybody have any clues to fix this?
Thank you.
SET FMTONLY OFF works perfect i put it in all my stored proc now ;) thx a lot !!!!!!!!!!
Thanks alot, I can confirm that setting FMTONLY OFF works. Could someone explain why?
Thanks.
I have dynamic sql in my SP… any ideas how use LINQ?
thanks
SET FMTONLY OFF ? ok i ll try this by the end of this week ! thx a lot :)
Yes, putting SET FMTONLY OFF at the top of your sproc will fix the issue with #temp tables in the sproc
nk54,
We noticed this issue when we have temporary tables in the SP.
i got the same kind of problem : my sp return a int value instead of a sp_myFunctionResult type.
Is it because of using #table in my sp ? alias of my my column which may be wrong ?
Thx 4 your help :)
Try sticking SET FMTONLY OFF at the top of the stored proc’s code. I ran into this with SQL Reporting Services as well. I’m guessing that FMTONLY gets set to ON right before calling the stored proc with the intention to only get the columns that it should return, but this doesn’t seem to work in alot of cases. Overriding it to force it off will cause it to return data (which is likely just ignored), but it will be able to determine the proper format of the records returned. You may have to set default values on the stored proc parameters that will force it to return some data.
As we progress with the development, more SPs are falling in to this category(it’s bad!). So creation of dummy sp’s is no longer a feasible solution for us.
We stopped re-generating the full dbml file now. We are manually adding/updating the individual stored procedures as when they are created or modified.
By the way, sp’s for which dbml file generation failing contains one or more of the following
1) Temp tables
2) Using of OUTPUT clause(SQL Server 2005+ feature)
3) SELECT statements that output a single row of values calculated using formula
This generally happens when there is a problem parsing the stored procedure. Usually, the stored procedure has a typo (double-check your table/column names) or a conversion error (double-check that any Convert/Casts are being used correctly).
Your solution is a good first step, but I think nine times out of ten, it will generate a runtime exception when the sproc executes
Even though I got a work around to this problem, it is very frustrating to create dummy sp and then replace it with actual sp. In the development environment, we regenerate the dbml for stored procedure very often and every time fiddling with this issue is very annoying.
@LINQ Master,
My SP returns does not return data from any table/temp table. It does lot of calculations and at the end returns a single record output with the help of SELECT statement.