Question: For what reason would a SPROC not have an available constructor?

[willnationsdev]

I am trying to use the SqlProgrammabilityProvider to execute stored procedures. I've set up some basic crud operations for a DataType on my project:

• Select (int -> Option<DataType>)
• SelectAll (unit -> Seq<DataType>)
• Insert (DataType -> int)
• Update (DataType -> bool)
• Delete (int -> bool)

Curiously though, only the Select/SelectAll can be called. The Insert, Update, and Delete commands say there is no available constructor.

I can execute the stored procedures directly in the database and have confirmed that my user that I am connecting with has permission to execute the stored procedures. Can anyone offer another explanation for why I cannot use them?

Thanks

[smoothdeveloper]

@willnationsdev would be relevant places to investigate:

https://github.com/fsprojects/FSharp.Data.SqlClient/blob/2ef6c06a2844c2a1b61305bc6b78cbeede090915/src/SqlClient.DesignTime/SqlClientExtensions.fs#L168-L218

https://github.com/fsprojects/FSharp.Data.SqlClient/blob/2ef6c06a2844c2a1b61305bc6b78cbeede090915/src/SqlClient.DesignTime/SqlClientExtensions.fs#L276-L290

https://github.com/fsprojects/FSharp.Data.SqlClient/blob/4450560810f0970894d6a46226dae1ca55d7640d/src/SqlClient.DesignTime/DesignTime.fs#L621-L665

Could you check the kind of results the queries in those areas are returning against your procedure definition?

[willnationsdev]

Abbreviated results of the first query, reworded for security, that is relevant to these stored procedures:

Schema, Name, RoutineSubType, Definition, BaseObjectSchema, BaseObjectName, Description
dbo	uspInsertDataType	StoredProcedure	  -- =============================================  -- Author:  MY_USER  -- Create date: 2019-11-18  -- Description: Inserts DataType  -- =============================================  CREATE PROCEDURE [dbo].[uspInsertDataType]   @Name1 VARCHAR(50),      @Name2 VARCHAR(100),   @Name3 VARCHAR(100),   @P VARCHAR(100),   @E VARCHAR(10),      @IA BIT,      @CreatedBy INT  AS  BEGIN        INSERT INTO dbo.DataTypes      (       [Name1],          [Name2],          [Name3],    [P],    [E],    [IA],          [CreatedOn],          [CreatedBy],          [ModifiedOn],          [ModifiedBy]      )      VALUES      (          @Name1, -- Name1 - varchar(50)    @Name2,        -- Name2 - varchar(100)    @Name3,     -- Name3 - varchar(100)    @P,    -- P - varchar(100)    @E, -- E - varchar(10)          @IA,    -- IA - bit          GETDATE(),    -- CreatedOn - datetime          @CreatedBy,   -- CreatedBy - varchar(100)          GETDATE(),    -- ModifiedOn - datetime          @CreatedBy    -- ModifiedBy - varchar(100)      );     SELECT SCOPE_IDENTITY();    END;  	dbo	uspInsertDataType	NULL
dbo	uspUpdateDataType	StoredProcedure	  -- =============================================  -- Author:  MY_USER  -- Create date: 2019-11-18  -- Description: Updates a DataType  -- =============================================  CREATE PROCEDURE [dbo].[uspUpdateDataType]      @Id INT,   @Name1 VARCHAR(50),      @Name2 VARCHAR(100),   @Name3 VARCHAR(100),   @P VARCHAR(100),   @E VARCHAR(10),      @IA BIT,      @ModifiedBy INT  AS  BEGIN        UPDATE [dbo].[DataTypes]      SET [Name1] = @Name1,       [Name2] = @Name2,    [Name3] = @Name3,    [P] = @P,    [E] = @E,          [IA] = @IA,          [ModifiedOn] = GETDATE(),          [ModifiedBy] = @ModifiedBy      WHERE [Id] = @Id;    END;  	dbo	uspUpdateDataType	NULL
dbo	uspDeleteDataType	StoredProcedure	  -- =============================================  -- Author:  MY_USER  -- Create date: 2019-11-18  -- Description: Deletes DataType  -- =============================================  CREATE PROCEDURE [dbo].[uspDeleteDataType] @Id INT  AS  BEGIN        DELETE FROM [dbo].[DataTypes]      WHERE [Id] = @Id;    END;  	dbo	uspDeleteDataType	NULL
dbo	uspSelectDataType	StoredProcedure	  -- =============================================  -- Author:  MY_USER  -- Create date: 2019-11-18  -- Description: Gets a DataType  -- =============================================  CREATE PROCEDURE [dbo].[uspSelectDataType] @Id INT  AS  BEGIN      SELECT *      FROM [dbo].[DataTypes]      WHERE [Id] = @Id;    END;  	dbo	uspSelectDataType	NULL
dbo	uspSelectDataTypes	StoredProcedure	    -- =============================================  -- Author:  MY_USER  -- Create date: 2019-11-18  -- Description: Gets a list of DataTypes  -- =============================================  CREATE PROCEDURE [dbo].[uspSelectDataTypes] @IIA BIT  AS  BEGIN        SELECT *      FROM [dbo].[DataTypes]      WHERE (                IA = 1                OR @IIA = 1            );    END;  	dbo	uspSelectDataTypes	NULL

---

~~Similar for query 2 (let me know if I'm missing stuff here). I couldn't quite figure out what to set for the OBJECT_ID(...), but these appear to be the relevant rows from what I can tell.~~ I figured out how to use the query. Here are the tables of information:

dbo.uspInsertDataType
name	suggested_system_type_id	suggested_user_type_id	suggested_is_output	suggested_is_input	max_length	precision	scale	description
@Name1 167	167	0	1	50	0	0	
@Name2	167	167	0	1	100	0	0	
@Name3	167	167	0	1	100	0	0	
@P 167	167	0	1	100	0	0	
@E 167	167	0	1	10	0	0	
@IA 104	104	0	1	1	1	0	
@CreatedBy	56	56	0	1	4	10	0	

dbo.uspUpdateDataType
name	suggested_system_type_id	suggested_user_type_id	suggested_is_output	suggested_is_input	max_length	precision	scale	description
@Id	56	56	0	1	4	10	0	
@Name1 167	167	0	1	50	0	0	
@Name2	167	167	0	1	100	0	0	
@Name3	167	167	0	1	100	0	0	
@P 167	167	0	1	100	0	0	
@E 167	167	0	1	10	0	0	
@IA 104	104	0	1	1	1	0	
@ModifiedBy	56	56	0	1	4	10	0	

dbo.uspDeleteDataType
name	suggested_system_type_id	suggested_user_type_id	suggested_is_output	suggested_is_input	max_length	precision	scale	description
@Id	56	56	0	1	4	10	0	

dbo.uspSelectDataType
name	suggested_system_type_id	suggested_user_type_id	suggested_is_output	suggested_is_input	max_length	precision	scale	description
@Id 56	56	0	1	4	10	0	

dbo.uspSelectDataTypes
name	suggested_system_type_id	suggested_user_type_id	suggested_is_output	suggested_is_input	max_length	precision	scale	description
@IIA	104	104	0	1	1	1	0	

---

I'm not really sure how to execute the last code link you sent me.

[willnationsdev]

I have double-checked that the db user has both execute and view definition permissions on each stored procedure in the database.

I've also noticed that this particular data type's select methods are the only ones that I can get records for. Every other stored procedure doesn't even let me reference the .Record value to define a type. Such as this:

type private DataTypeRecord =
| SelectDataType of DB.Records.dbo.uspSelectDataType.Record
| SelectDataTypes of DB.Records.dbo.uspSelectDataTypes.Record

I can't seem to do the same with any other data type in the database (of which there are 5), even though it works with the first one.

One of the datatypes' stored procedures doesn't even appear in the autocompletion dropdown, even though I can see it in the list returned by the first query you shared with me.

Perhaps some of these symptoms can give a better idea of what the problem might be? I don't know if they are related or not.

[willnationsdev]

From that third code sample, I couldn't construct a function that runs it with the typeof<Connection>, even after I included all of the same libraries. I switched it to SqlConnection (perhaps that is right?) and ended up getting it to compile, but the result of the query, when printed, is <null>.

open System
open System.Reflection
open System.Data
open System.Data.SqlClient
open Microsoft.SqlServer.Server
open System.Collections.Generic
open System.Diagnostics
open Microsoft.FSharp.Quotations
open ProviderImplementation.ProvidedTypes
open FSharp.Data
open System.Text.RegularExpressions

[<EntryPoint>]
let main argv =

    let ctorImpl = typeof<``ISqlCommand Implementation``>.GetConstructor [| typeof<DesignTimeConfig>; typeof<SqlConnection>; typeof<int> |]

    printfn "%A" ctorImpl
    Console.ReadLine() |> ignore
    0 // return an integer exit code

[willnationsdev]

@smoothdeveloper ~~We figured it out! Apparently, even though the user had permissions to execute and view definitions on the individual stored procedures, they still needed to be given view definition access to the database itself! You guys might want to add something to the documentation that warns users about this.~~ Seems like it was some kind of fluke. The error re-appeared.