Linqpad not listing all the stored procedures

svive

Just confirmed that even though the sprocs are all in the same schema, linqpad is not listing some of them. All of them are being properly listed in SSMS though.

I also tried listing the sprocs using this query and it shows that the missing ones and the shown ones both have the same schema.

SELECT specific_schema, routine_schema, specific_name FROM information_schema.routines
WHERE routine_type = 'PROCEDURE'
ORDER BY specific_name

JoeAlbahari

Can you provide the DDL for a simple repro?

kingkeith

possibly related: http://forum.linqpad.net/discussion/1521/potential-name-conflict-problems-when-multiple-items-in-different-db-namespaces-have-the-same-name#latest

jhurdlow

We're seeing this issue with Stored Procs that use custom Table Types. The interesting thing is that LINQPad 7 works properly, but LP5 and LP8 don't. (All licensed)
LP7 shows a Table Types section with our custom types, and all the stored procs show properly.
Both LP5 and LP8 do not show a Table Types section, and don't show the stored procs that use them (but others show fine). So there is a clear regression from LP7 to LP8.
Everything is in one schema (dbo), and there is no duplication of names anywhere.
Should be very easy to repro, but if I can assist, let me know.

JoeAlbahari

Can you provide a MRE? A simple script that I can run on SQL Server to reproduce the problem.

jhurdlow

Sure:
Here's a contrived (and poorly normalized) db that shows it:

CREATE DATABASE [LP_SP_Quiz]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'LP_SP_Quiz', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\LP_SP_Quiz.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'LP_SP_Quiz_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\LP_SP_Quiz_log.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
GO

USE [LP_SP_Quiz]

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Students](
    [Id] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
    [Name] varchar(200) NOT NULL
)

CREATE TABLE [dbo].[Quiz](
    [Id] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
    [Name] varchar(100) NOT NULL
)

CREATE TABLE [dbo].[QuizQuestionAnswers](
    [Id] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
    [QuizId] [int] NOT NULL,
    [QuestionNum] [int] NOT NULL,
    [QuestionText] varchar(200) NOT NULL,
    [AnswerNum] [int] NOT NULL,
    [AnswerText] varchar(200) NOT NULL
)

ALTER TABLE [dbo].[QuizQuestionAnswers]  WITH CHECK ADD  CONSTRAINT [FK_QuizQuestionAnswers_Quiz] FOREIGN KEY([QuizId])
REFERENCES [dbo].[Quiz] ([Id])
GO

CREATE TABLE [dbo].[StudentQuizAnswers](
    [Id] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
    [StudentId] [int] NOT NULL,
    [QuizId] [int] NOT NULL,
    [QuestionNum] [smallint] NOT NULL,
    [AnswerNum] [smallint] NOT NULL,
)
GO

ALTER TABLE [dbo].[StudentQuizAnswers]  WITH CHECK ADD  CONSTRAINT [FK_StudentQuizAnswers_Student] FOREIGN KEY([StudentId])
REFERENCES [dbo].[Students] ([Id])
GO

ALTER TABLE [dbo].[StudentQuizAnswers]  WITH CHECK ADD  CONSTRAINT [FK_StudentQuizAnswers_Quiz] FOREIGN KEY([QuizId])
REFERENCES [dbo].[Quiz] ([Id])
GO

GO
CREATE TYPE [dbo].[QuestionAnswerList] AS TABLE ( -- Will show in LP 7, but not LP 5 or 8
    [QuestionId] SMALLINT NULL,
    [AnswerId]   SMALLINT NULL);

GO

CREATE PROCEDURE [dbo].[SetStudentName] -- Will show in 5, 7, and 8.
    @studentId INT,
    @studentName varchar(200)
AS
SET NOCOUNT ON;

    UPDATE [Students]
    SET [Name] = @studentName
    WHERE [Id] = @studentId
GO

CREATE PROCEDURE [dbo].[SetStudentQuizAnswers]  -- Will show in LP 7, but not LP 5 or 8
    @studentId INT,
    @quizId INT,
    @questionAnswers QuestionAnswerList READONLY  -- The use of a custom type causes LP 5 and 8 issues apparently.
AS
SET NOCOUNT ON;

BEGIN TRANSACTION

    -- Delete answers that have been removed (in DB but not passed in)
    DELETE currentAnswers
    FROM dbo.[StudentQuizAnswers] AS currentAnswers
    LEFT JOIN @questionAnswers AS newAnswers
        ON newAnswers.QuestionId = currentAnswers.QuestionNum
        AND newAnswers.AnswerId = currentAnswers.AnswerNum
    WHERE currentAnswers.StudentId = @studentId
        AND currentAnswers.QuizId = @quizId
        AND newAnswers.AnswerId IS NULL;

    -- Insert answers that have been added (passed in but but not in DB)
    INSERT dbo.[StudentQuizAnswers] (StudentId, QuizId, QuestionNum, AnswerNum)
    SELECT @studentId,
        @quizId,
        newAnswers.QuestionId,
        newAnswers.AnswerId
    FROM @questionAnswers AS newAnswers
    LEFT JOIN dbo.[StudentQuizAnswers] AS currentAnswers
        ON currentAnswers.StudentId = @studentId
        AND currentAnswers.QuizId = @quizId
        AND currentAnswers.QuestionNum = newAnswers.QuestionId
        AND currentAnswers.AnswerNum = newAnswers.AnswerId                          
    WHERE currentAnswers.AnswerNum IS NULL

COMMIT TRANSACTION

RETURN 0
GO

JoeAlbahari

Thanks for the repro. This is caused by a strange bug in SQL Server, whereby user-generated table types report as system-generated. There will be a workaround in the next beta; in the meantime, you can avoid this problem by checking the 'Include System Tables/Views' option in the connection dialog.