MSSQL stored procedures does not correctly works with schemas
Nobody has claimed this yet.
Assessment
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Newbie friendliness
- 35/100
Research direction
Start by running the supplied MSSQL schema and stored-procedure scripts, then reproduce the issue through SqlDataProvider.GetDataContext and ctx.Procedures. Trace how procedures with the same name in clm and eeInf are discovered and exposed. Done means both schema-specific procedures remain distinct and Invoke shows the correct parameters for each.
Written by the indexing model from the issue text.
Description
SqlDataProvider does not have the functionality to specify schema of stored procedure (in contrast to accessing the tables). Rather, all stored procedures appear under ctx.Procedures where ctx is a database context obtained via call to GetDataContext, e.g.:
type private WorkerNodeDb = SqlDataProvider<
Common.DatabaseProviderTypes.MSSQLSERVER,
ConnectionString = WorkerNodeConnectionStringValue,
UseOptionTypes = Common.NullableColumnType.OPTION>
type private WorkerNodeDbContext = WorkerNodeDb.dataContext
let private getDbContext (c : unit -> ConnectionString) = c().value |> WorkerNodeDb.GetDataContext
This how clm.tryUpdateProgressRunQueue (from schema clm) appears in F#:

Hovering over (as shown on the picture) correctly shows that the stored procedure belongs to clm schema. This is inconvenient but that would've been OK and that could be dealt with.
However, the error appears if there is a stored procedure with the same name but in a different schema (eeInf in the example). The second procedure "acquires" an extra ' in the name:

Unfortunately, finally nothing works. Hovering over Invoke shows SP parameters mixed up from both procedures:

Here are the blank SPs along with schema creation scripts for convenience:
if not exists(select schema_name from information_schema.schemata where schema_name = 'clm') begin
print 'Creating schema clm...'
exec sp_executesql N'create schema clm'
end else begin
print 'Schema clm already exists...'
end
go
if not exists(select schema_name from information_schema.schemata where schema_name = 'eeInf') begin
print 'Creating schema eeInf...'
exec sp_executesql N'create schema eeInf'
end else begin
print 'Schema eeInf already exists...'
end
go
drop procedure if exists clm.tryUpdateProgressRunQueue
go
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure clm.tryUpdateProgressRunQueue (
@runQueueId uniqueidentifier,
@progress decimal(18, 14),
@callCount bigint,
@relativeInvariant float,
@maxEe float,
@maxAverageEe float,
@maxWeightedAverageAbsEe float,
@maxLastEe float)
as
begin
declare @rowCount int
set nocount on;
-- Do something useful here.
set @rowCount = @@rowcount
select @rowCount as [RowCount]
end
go
drop procedure if exists eeInf.tryUpdateProgressRunQueue
go
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure eeInf.tryUpdateProgressRunQueue (
@runQueueId uniqueidentifier,
@progress decimal(18, 14),
@callCount bigint,
@relativeInvariant float,
@dummy float)
as
begin
declare @rowCount int
set nocount on;
-- Do something useful here.
set @rowCount = @@rowcount
select @rowCount as [RowCount]
end
go
I am using SQLProvider version 1.3.7 and MSSQL.
- Dominant language
- F#
- Stars
- 627
- Forks
- 147
- Avg merge
- 2h 2m
- Merged PRs (30d)
- 1
Contributor guide
No contributing guide indexed for this repository
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
More from fsprojects/SQLProvider
-
enhancement
Difficulty 3/5 1-2 days Newbie friendliness 55/100
fsprojects/SQLProvider#872 · 2 comments ·
-
Repo Assist? Openenhancement
Difficulty 5/5 Over a week Newbie friendliness 10/100
fsprojects/SQLProvider#870 · 1 comment ·
-
postgresql
Difficulty 4/5 3-5 days Newbie friendliness 58/100
fsprojects/SQLProvider#869 · 2 comments ·
-
documentation
Difficulty 2/5 1-3 hours Newbie friendliness 42/100
fsprojects/SQLProvider#868 · 2 comments ·
-
sql server
Difficulty 4/5 3-5 days Newbie friendliness 45/100
fsprojects/SQLProvider#851 · 1 comment ·
All issues in fsprojects/SQLProvider
Similar issues
-
[6.x]: "Cannot use object of type stdClass as array" loading Users index (regression of #19182) Open
Difficulty 1/5 Under an hour Newbie friendliness 90/100
-
Difficulty 2/5 1-3 hours Newbie friendliness 68/100
community-scripts/ProxmoxVE#17396 · 1 comment ·
-
bug possible-duplicate
Difficulty 2/5 1-3 hours Newbie friendliness 88/100
AOSSIE-Org/PictoPy#1553 · 4 comments ·
-
Difficulty 2/5 1-3 hours Newbie friendliness 78/100
BasedHardware/omi#15274 · 3 comments ·
-
Difficulty 2/5 1-3 hours Newbie friendliness 88/100