View won't show in Object Explorer

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Express Edition and Compact Edition (2005)

View won't show in Object Explorer

Author

Topic

Arithmomaniac
Yak Posting Veteran

66 Posts

Posted - 2007-07-18 : 14:46:38

A view I created won't show up in "views' in Object Explorer, but will only show up when I script for it (Select * from blah). It also won't go into the ODBC. why?

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-18 : 14:49:52

Did you refresh the views in the object explorer? You can also try disconnecting from the server and reconnecting.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Arithmomaniac
Yak Posting Veteran

66 Posts

Posted - 2007-07-18 : 15:12:46

I did both.

Arithmomaniac
Yak Posting Veteran

66 Posts

Posted - 2007-07-18 : 17:29:26

Actually, the problem appears to be with the code - I ran a different query separately. Now, my view is locked - I can't delete or create it, unless I give it a new name, and another, etc.



CREATE VIEW vwDaysPerSet AS
SELECT     
Account, 
ContactManager, 
datediff(dd,CreatedAt, LastShipmentAtDT) AS DaysofTerm
FROM         [x].[dbo].[wvDataFlavorBase] as d1
where (DATEDIFF(dd, CreatedAt, GETDATE()) < 60) -- Last 60 Days
AND d1.Account in

-- the following ensures four uploads from the account in the last 60 days

(select Account from [x].[dbo].[wvDataFlavorBase] AS D2
where DATEDIFF(mm, CreatedAt, GETDATE()) <= 6
 GROUP BY Account 
HAVING Count(Account) > 3)

what does Object Explorer not like about this block?

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-18 : 17:55:46

What do you mean blocked? You couldnt DROP the view? If you have the right query you can ALTER it.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Arithmomaniac
Yak Posting Veteran

66 Posts

Posted - 2007-07-18 : 19:28:41

[code]DROP VIEW vwDaysPerSet
Msg 3701, Level 11, State 5, Line 1
Cannot drop the view 'vwDaysPerSet', because it does not exist or you do not have permission.

CREATE VIEW vwDaysPerSet AS...
Msg 2714, Level 16, State 3, Procedure vwDaysPerSet, Line 2
There is already an object named 'vwDaysPerSet' in the database.

ALTER VIEW vwDaysPerSet AS...
Command(s) completed successfully.

SELECT * FROM vwDaysPerSet
(3495 row(s) affected)[/code]

Looked in Object Explorer...Still not there.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-18 : 23:29:05

create with dbo. as the prefix and see if it show up. This is weird.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Arithmomaniac
Yak Posting Veteran

66 Posts

Posted - 2007-07-19 : 13:17:22

Yeah, I don't know what the problem was, but I forced it to create in the right database and with dbo. Thanks.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-19 : 14:01:02

When you do not use dbo as the owner, the object is created with the username with which you login. And if the application has a userId that has very limited permissions it might not even be able to use such objects with non dbo objects.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources