12 Sep 2013

Modelling a Stored Procedure in EF: The selected stored procedure returns no columns

Trying to map a SQL Stored Procedure in Entity Framework in Visual Studio, I clicked "Update my Model from Database" on the EDMX view, and added the Stored Procedure. However, unlike other ones I had imported, when I looked in Model Browser, no Complex Type was created in the model to represent the result rows of the Stored Proc.

When I clicked on the Function Import for the sproc, The Return Type was set to none, so I tried to edit it. On the Edit Function Import screen, clicking Get Column Information resulted in: "The selected stored procedure returns no columns". This was not true!

Well, to cut a long story short, the fix was to include this line at the top of the Stored Proc SQL:

SET FMTONLY OFF;

After that, clicking Get Column Information worked, and I could create the new Complex Type with no problems.

No comments:

Post a Comment

Comments are very welcome unless you're a spammer, in which case you should probably kill yourself.

If I helped you out today, you can buy me a beer below. Cheers!