FSharp.Data.SqlCommandProvider throws error if statement/proc uses temp table

[unuunc]

I have a stored procedure which uses temp tables when i try to execute that I get following error
"The type provider 'FSharp.Data.SqlCommandProvider' reported an error: The metadata could not be determined because statement ... uses a temp table."

[unuunc]

Got it , it is limitation of Sql Server side it seems

[unuunc]

any alternate way to execute these kind of procs?

[dmitry-a-morozov]

You have 2 options:

1. Specify ResultType = ResultType.DataReader which provides types for input params and result is untyped SqlDataReader.
   https://github.com/fsprojects/FSharp.Data.SqlClient/blob/master/src/SqlClient.Tests/SqlCommand.fsx#L114
   The data reader can be converted to maps. See example at bottom of this page
   http://fsprojects.github.io/FSharp.Data.SqlClient/output.html

2. A second, better option is to use second type provider SqlProgrammability which gives you direct access to SP. See example here
   https://github.com/fsprojects/FSharp.Data.SqlClient/blob/master/src/SqlClient.Tests/SqlProgrammability.fsx

[unuunc]

Thanks Dimitry,
About second option , I tried using it but it throws similar error due to same reason
"The type provider 'FSharp.Data.SqlProgrammabilityProvider' reported an error: The metadata could not be determined because statement 'SELECT SR
INTO .. FROM .. JOIN ...' in procedure 'Proc' uses a temp table."

but it is working fine with normal Stored procedures.

Effectively I am not getting AsyncExecute() or Execute() with my stored proc.

[dsevastianov]

You are right, the problem is that this type provider uses sys.sp_describe_first_result_set stored procedure in MS SQL 2012 to figure output columns and falls back to SET FMTONLY if it fails. Both of them fail in certain scenarios, and apparently you hit one of them.

As Dmitry said, your only option in this case is to use ResultType.DataReader. Note that you will still have design-time validation of the sql command and input parameters in AsyncExecute()/Execute().

[ScottHutchinson]

Is there no alternative to the sys.sp_describe_first_result_set stored procedure that would work with temporary tables or table variables? Maybe as an option, in case it has any negative side effects, like slowness?

[smoothdeveloper]

@ScottHutchinson please check #277 #278 this is feature proposal with a pending PR implementing it to deal with temp tables.