Custom Procedure unable to select views

Stefan Sturm asked on February 17, 2016 15:14

Hello,

i've developed a custom stored procedure.

create PROCEDURE [dbo].[procView]    
AS
BEGIN
    select * from View_xxx_Joined
end

Im trying to call this stored procedute form the bit-wizard sql execution model. Unfortunately this causes an error "Cant find View....."

If i call the

select *
from View_xxx_Joined

i get data.

Any help would be appreciated!

view stored procedure

Correct Answer

Brenden Kehren answered on March 18, 2016 22:55

You might look into finding out what permissions or ownership the user running the site or executing the queries has. If you're trying to call a dbo.View_CMS.... the schema owned by that user might not be dbo so you'd have to define the schema it was created under.

1 votesVote for this answer Unmark Correct answer

Recent Answers

Roman Hutnyk answered on February 17, 2016 15:23

What about calling it from SQL Management Studio?

0 votesVote for this answer Mark as a Correct answer

Stefan Sturm answered on February 17, 2016 15:27

We do not have direct access to the database via SQL MS. We can only access it through the BitWizard SQL Execution module.

When querying the view via "select * from VIEW_xxx" there is no problem. When executing a custom stored procedure (which queries select * from View_xx") we do run into the problem

Are there any special permissions when querying views in procedures?

0 votesVote for this answer Mark as a Correct answer

Roman Hutnyk answered on February 17, 2016 15:31

There are no any extra permissions, so you should be fine. Can you call that SP from anywhere else? Try to call it in custom report?

0 votesVote for this answer Mark as a Correct answer

Stefan Sturm answered on February 17, 2016 15:38

Same problem here ... Still getting: Invalid object name 'View_xxxxx'. No data found

0 votesVote for this answer Mark as a Correct answer

Roman Hutnyk answered on February 17, 2016 15:44

If you can select from view directly, but can't through SP - looks like there are issues with SP. Do you have DEV environment with more permissions?

0 votesVote for this answer Mark as a Correct answer

Stefan Sturm answered on February 17, 2016 15:51

On our local kentico installation I was able to create the procedure via sql execution modul, and execute it without any problems...

Same situation in live environment causes error..

0 votesVote for this answer Mark as a Correct answer

Brenden Kehren answered on February 19, 2016 06:00

What version of Kentico are you using? In v9 those views are no longer part of the CMS. They were removed for optimization reasons.

0 votesVote for this answer Mark as a Correct answer