cannot find the query 'x' in the embedded xml file.

I have a Customer object (which is a table) and a NoteView object (which is a view) in my database.

I have two custom stored procedures  _ Customer_DoSomething  and _NoteView_DoSomething.

 I CAN execute the _Customer_DoSomething proc fine via the CustomerService

I CAN NOT execute the _NoteView_DoSomething proc via the NoteViewService 

 I get this error:

cannot find the query 'dbo._NoteView_DoSomething' in the embedded xml file.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.ApplicationException: cannot find the query 'dbo._NoteView_DoSomething' in the embedded xml file.

Source Error:

Line 193:        nSvc = New Services.NoteViewService
Line 194:
Line 195:        nSvc.DoSomething()
Line 196:
Line 197:       

Seems like any Custom Stored Procedure that is assigned to a View will get this error.   I am using nettiers-2.3.0.b1.

have you checked the file procedures.xml in Data.SqlClient ?? is your procedure there?

Are you following the  Custom Stored Procedures naming convension?

 I know the conventions were fine as these procedures all worked 100% before we upgraded to the latest beta templates.

 Which template were you guys using?? 2.2??

 Yes, we were using 2.2 before we tried the Beta.  We've since had to go back to 2.2 so that we can keep developing.  The Beta left us dead in the water for too long.

This is the schema that reproduces the problem. 

CREATE TABLE [dbo].[Customer](

[CustomerId] [int] NOT NULL,

[FirstName] [varchar](50) NULL,

[LastName] [varchar](50) NULL,

CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED

[CustomerId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

CREATE VIEW [dbo].[CustomerView]

SELECT CustomerId, FirstName + ' ' + LastName AS Name

FROM dbo.Customer

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for procedure here

Where CustomerId = @CustomerId

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for procedure here

Where CustomerId = @CustomerId

I am using Visual stuido 2008 and Sql Server 2008

If anyone else has the same problem I have with the above schema it would be cool to know that I am not the only one.

I have attached a patch for this problem in issue 65 on the Google code site. The error with stored procedures on views was introduced in version 719.

If you want to fix this yourself, open the Source\DataAccessLayer.SqlClient\Views\SqlEntityViewProviderBase.generated.cst file, about line 389 you will see:

  DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "<%= GetOwnerName(command, true) %><%=GetSafeName(command.Name)%>", _useStoredProcedure);

Change the "_useStoredProcedure" to "true" so it looks like this:

DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "<%= GetOwnerName(command, true) %><%=GetSafeName(command.Name)%>", true);

The custom stored procedures will now always be called, which is what should happen, and you will not get the "cannot find the query 'x' in the embedded file" error.

DoniG

Thanks that worked!