Sql Metal Error sqm1014 Unable extract Stored Procedure .Can you help?

Question

 

Hi,

New to Linq to Sql and I have been using SqlMetal to explore it's capabilities on a real world DB.

I have noticed that It works without a hitch on Northwind ,however I tried on real world DB and got errors after errors in trying to extract SPS.

 

The error I got is

Warning SQM1014 Unable extract Stored Procedure "GetAllCustomers".Invalid object #Countries.

 

It seems that it cannot handle SPS with temporary table or nested SPS.

Is this the case? Is there a workaround?

Is there  a list of limitation that SqlMetal for obvious reasons cannot handle with the respect to SPS?

 

Thanks a lot in advance

 

 

Answer 1

SQLMetal uses the execution of the stored procedure via ADO w/ SchemaOnly option to learn the structure of the result of the stored procedure.  The server itself is failing when asked to return the schema.  You can still map this stored procedure but you are either going to have to edit the DBML file by hand or add the mapped method signature yourself to the DataContext (via your own partial class).

 

Answer 2

Thank you very much for your reply.

Actually is very important that I find a workaround as I am evaluating both linq to sql vs Nhibernate.I really want to go for linq to sql as I find so much easier to use."Gui designers ,more info etc..."

 

Is my understanding correct that for those Stored Procedures that use Temporary Table or Dynamic sql I can still map my stored procedure but the only difference is that I have to create it manually and put it in my own partial class rather than use SqlMetal?

So it's more of a sql metal codeGeneration limitation rather than linq to Sql on it's own.Is this correct?

 

So if i create by hand an exact match or what SqlMetal would create it will work.Is this correct?

 

I really appreciate your help and further clarification of the above,as i have googled and goggled but could not find an answer.

Thanks a lot

 

Answer 3

If you change the stored proc, that is creating the temporary table, replace the following code:

CREATE TABLE #Countries(

with:

DECLARE @Countries TABLE(

This worked for me.