none
sqlMETAL bug RRS feed

  • Question

  • I am trying to execute SQLmetal to generate a dbml file from my database, and it keeps ignoring two of my stored procs.

     

    I run it from the command line with the /sprocs option and it picks up all of my stored proces except two.

     

    These are quite complex and use a temporary table internally, however they don't return any data. I tried specifying @result int OUTPUT in the definition to make this clear (and return zero) but it would not generate the output.

     

    Next I tried commenting out the contents and leaving nothing inside.. and they then are picked up by SQLmetal. So obviously SQLmetal dislikes something in the content.

     

    However, if I open the .dbml in designer mode, I can drag the same SProcs into the designer from the server explorer!?

     

    Any suggestions MS ?

    Wednesday, August 27, 2008 2:48 PM
    Answerer

Answers

  • Stored procedures that use temporary tables aren't supported by SqlMetal or the designer.

     

    Neither tool runs the stored procedures because doing so could be potentially disastrous so they attempt to work out what will be returned. Dynamic queries, cursors and temporary tables are not supported using this technique.

     

    You can, however, temporarily add SET FMTONLY OFF in the stored procedure to indicate to the tools they can run the SP in order to determine the return type but once generated you should definitely remove this again.

     

    [)amien
    Wednesday, August 27, 2008 6:10 PM
    Moderator

All replies

  • Stored procedures that use temporary tables aren't supported by SqlMetal or the designer.

     

    Neither tool runs the stored procedures because doing so could be potentially disastrous so they attempt to work out what will be returned. Dynamic queries, cursors and temporary tables are not supported using this technique.

     

    You can, however, temporarily add SET FMTONLY OFF in the stored procedure to indicate to the tools they can run the SP in order to determine the return type but once generated you should definitely remove this again.

     

    [)amien
    Wednesday, August 27, 2008 6:10 PM
    Moderator
  • Thanks for the response.

     

    Your statement "Neither tool runs the stored procedures ..." is not correct.

     

    I just re-tested this by dragging the offending SP with its temp tables onto the Object Relational Designer for LINQ to SQL - no problems. It creates the SP and sets the return type to 'none' which is fine.

     

    It's just SQLmetal that barfs over this routine. I won't bother with SET FMTONLY OFF as I need to refresh my model using SqlMetal regularly and having to modify and restore the SP all the time is unworkable. I suspect I will need to write my own SP-scanner that pick up the holes in SQLmetal.

    Thursday, August 28, 2008 8:32 AM
    Answerer