"Yellow Triangles" on several stored procedures in Schema View

Question

I am preparing to migrate our database projects from 2008 GDR R2 to VS2010.  To do this, I copied our solution to another drive and ran through the conversion process.  After conversion, I deployed to my local SQL Server.

During deployment, I saw several messages in the output window like this one:

The module 'readm_subseq_populate' depends on the missing object 'dbo.setparse'. The module will still be created; however, it cannot run successfully until the object exists.

As a DBA I am familiar with this message, which occurs when a procedure depends on another procedure, but the other procedure does not exist yet.  I was surprised by these messages, because I never received them while deploying in 2008 and I had not made any changes to the database projects.  As I started to investigate, I happened to open the Schema View, where I found that all of the procedures that threw the warning messages during deployment had the yellow triangle with the exclamation point.  When I discovered this, I went back and opened the 2008 solution and found the same thing.

My question is how do I find out what is generating these warning icons for the procedures?  I do not have any warnings in the "Error List" window of Visual Studio, and there is no Tool Tip or similar function that appears if I hover over the procedure in question.  Even thought I am able to deploy successfully, I want to know what Visual Studio is finding just so I can see if I can make the database project better.

Answer 1

Hi,

I am a bit confused, because you already said how to find out why the procedures are barking at you. Open the warnings page in the output and you can investigate the missing references. You did not mention if the procedures in fact exist or not...

-Jens

---

Jens K. Suessmeyer http://blogs.msdn.com/Jenss

Answer 2

What appears to be happening is that, during Deployment, the procedures are created in alphabetical order.  So, if the referenced procedure has a name that is further down the alphabet, it will not have been created when the procedure depending on it is created.  I get that.

What I don't get is why Visual Studio gives me the same kind of warning it gives when I have an invalid DLL reference or invalid Database Reference.  The code for both procedures is in the Database Project.  Visual Studio ought to be able to tell that both procedures exist in the project, so why show me a warning here?  I don't mind a message in the Output window during deployment, but showing me the yellow triangle in the Database Project seems like a false positive to me.

And again, if Visual Studio shows me this warning icon in the project, why doesn't it give me a warning message in the Error List window?  If the warning is simply that the procedure won't work if the other procedure isn't created, why not give me that message in the Error List window?  Then I could choose to suppress the Warning, since it is not meaningful to me.

 

Answer 3

Hi Ken,

now I think I got your point. In most cases you probably have overseen something. The most project I get for taking a look into that are just missing a little piece to produce no errors. Hard to tell though while not having the project at hands. Can you send me the project, I will have a look into that. My mail adress is JENSS <- at - > M1cr0s0ft.c0m (made spam save, replace the characters accordingly)

-Jens

---

Jens K. Suessmeyer http://blogs.msdn.com/Jenss

Answer 4

Hi Jens & Ken,

I have the same issue. In my case the Stored procedures in schema view (with the yellow triangle alert icon) all reference a User Defined Table Type in another schema. This table type is used as a READ ONLY input parameter to the proc, see example below. The project/solution builds without errors or warnings. It seems schema view is throwing a false issue.

CREATE TYPE [DataServices].[udttOrderingColumns] AS  TABLE (
    [SortID]          INT            NOT NULL,
    [SortColumnName]  NVARCHAR (128) NOT NULL,
    [IsAscendingFlag] BIT            NOT NULL,
    PRIMARY KEY CLUSTERED ([SortID] ASC));

CREATE PROCEDURE [EpisodeOfCare].[upAssociationGetPagedList]
(
  @pEpisodeOfCareAssociationID UNIQUEIDENTIFIER = NULL,
  @pEpisodeOfCareID UNIQUEIDENTIFIER = NULL,
  @pAssociatedEpisodeOfCareID UNIQUEIDENTIFIER = NULL,
  @pEpisodeOfCareAssociationTypeID UNIQUEIDENTIFIER = NULL,
  @pLastUpdateDateTime DATETIME = NULL,
  @pLastUpdateUserID SYSNAME = NULL,
  @pStartRow INT = NULL,
  @pPagesize INT = NULL,
  @ptblOrderingColumns [DataServices].[udttOrderingColumns] READONLY,
  @pRowcountLimit INT = NULL
)
WITH EXECUTE AS CALLER
AS
BEGIN

.

.

.

 

Answer 5

Paul which version are you using? Just added your snipper to VS2010 and it was working OK, no warnings or errors.

---

GertD @ www.DBProj.com

Answer 6

Hi Gert,

I'm using VS 2010. Although I don't get any warnings or errors, I do get the yellow alert icon showing against the proc in 'schema' view. Hovering over the icon reveals no error tooltip/popup. I'd just like to know what the IDE is concerned about. The build & deploy processes work fine.

 

Paul

---

Paul D. Bell SQL Database Developer/DBA

Answer 7

Weird I am not getting this. You can send me your project and I can look at it. You can send it to gertd at dbproj dot com

---

GertD @ www.DBProj.com

Answer 8

Issue resolved - missing dependencies.

The TVP was pointing me in the right direction. Later in the proc, the TVP was passed to a scalar function which in turned called another scalar function.

Both these scalar functions were missing from the project.

Once I added the functions, the warning icon disappeared from the procs schema view after a build.

Strangely these original missing dependencies were not explicitly flagged during build or deploy, yet the schema view was 'aware' there was an issue.

Thanks for looking into it for me.

---

Paul D. Bell SQL Database Developer/DBA

Answer 9

Hovering over the icon reveals no error tooltip/popup. I'd just like to know what the IDE is concerned about. The build & deploy processes work fine.

Exactly.  I don't like to have warnings in my projects, but the IDE doesn't give me the information I need in order to troubleshoot the warning.  And I still get the Warnings even after I upgraded to VS 2010.  I'm going to have to dig through all of these procedures to see if I can find anything they might have in common.

Answer 10

I have a question about how to resolve the "yellow triangle" when calling a system stored procedure. In one of my procedures, I build a dynamic sql statement for a search page. At the end of the procedure, I call the sp_executesql procedure as follows:

EXEC sp_executesql @SqlStr, @ParmDefinition, @vLawFirmID = @LawFirmID

The schema view indicates that there is an error with the procedure because of this one line. How do I get rid of this?

Thanks,

JE