MS SQL Server - Problem with Views

Author

Problem with Views

Tracy

2005-08-16, 1:23 pm

A while back I ran into a problem where changes to views (such as renaming
them) was not being reflected. Somehow I was able to get past this problem.

Now I want to generate a script that will create my views. When I look at
the script that was generated has old information; the old names, and the old
queries.

Is there something I'm overlooking when I make changes to views?

Wayne Snyder

2005-08-16, 1:24 pm

Ensure that you are not creating views with different owners other than
DBO.... It's possible that you are getting messed up with different version
of the same view owned by different owners..

--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)

I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Tracy" <Tracy@discussions.microsoft.com> wrote in message
news:711B82B3-D80C-4EA4-9014- 7EA62C8DF370@microso
ft.com...
>A while back I ran into a problem where changes to views (such as renaming
> them) was not being reflected. Somehow I was able to get past this
> problem.
>
> Now I want to generate a script that will create my views. When I look at
> the script that was generated has old information; the old names, and the
> old
> queries.
>
> Is there something I'm overlooking when I make changes to views?

Tracy

2005-08-16, 1:24 pm

DBO is the only user for this DB. Each create statement in the generated
script looks like this: CREATE VIEW dbo.someview. The problem is that these
view names and associated querys are old, the way the were before I renamed
them and made changes to the queries.

Thanks for your quick reply,
-Tracy

"Wayne Snyder" wrote:

> Ensure that you are not creating views with different owners other than
> DBO.... It's possible that you are getting messed up with different version
> of the same view owned by different owners..
>
>
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
>
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "Tracy" <Tracy@discussions.microsoft.com> wrote in message
> news:711B82B3-D80C-4EA4-9014- 7EA62C8DF370@microso
ft.com...
>
>
>

Hugo Kornelis

2005-08-16, 8:23 pm

On Tue, 16 Aug 2005 09:55:04 -0700, Tracy wrote:

>DBO is the only user for this DB. Each create statement in the generated
>script looks like this: CREATE VIEW dbo.someview. The problem is that these
>view names and associated querys are old, the way the were before I renamed
>them and made changes to the queries.
>
>Thanks for your quick reply,
>-Tracy

Hi Tracy,

How did you rename the queries? Did you execute a sp_rename? Or did you
open the view definition, change the name and execute it? In the latter
case, you didn't rename it - you made a new view (with a new name),
based on the old view. You'll still have to drop the old view.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Tracy

2005-08-17, 9:23 am

Using SQL Server Enterprise Manager (v8.0) I renamed the views the Windows
Explorer way: Highlight the item, single click it, type in the new name.

I generated another script just now, but this time I left the "Generate the
DROP <object>" checked. The script had DROP commands for the current views,
but the CREATE VIEWs were all the old view names and queries.

"Hugo Kornelis" wrote:

> On Tue, 16 Aug 2005 09:55:04 -0700, Tracy wrote:
>
>
> Hi Tracy,
>
> How did you rename the queries? Did you execute a sp_rename? Or did you
> open the view definition, change the name and execute it? In the latter
> case, you didn't rename it - you made a new view (with a new name),
> based on the old view. You'll still have to drop the old view.
>
> Best, Hugo
> --
>
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

Hugo Kornelis

2005-08-17, 8:23 pm

X-Newsreader: Forte Agent 1.91/32.564
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Complaints-To: abuse@supernews.com
Lines: 29
Path: TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!t-online.de!newsfeed.freenet.de!news.osn.de!diablo1-ffm.news.osn.de!news.tele.dk!news.tele.dk!small.news.tele.dk!sn-xit-03!sn-xit-10!sn-xit-01!sn-post-01!supernews.com!corp.supernews.com!not-for-mail
Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.server:403053

On Wed, 17 Aug 2005 06:45:03 -0700, Tracy wrote:

>Using SQL Server Enterprise Manager (v8.0) I renamed the views the Windows
>Explorer way: Highlight the item, single click it, type in the new name.

Hi Tracy,

Thanks - I never even knew that you could rename objects that way as
well!

However, the best way to rename views, stored procedures, triggers, and
user-defined functions is to explicitly drop and recreate them. Here's a
recent post by Tibor Karaszi about what goes wrong:
(Warning - long URL, check for line wrapping)

http://groups.google.com/groups?hl=...raszi&safe=off&
qt_s=Search

>I generated another script just now, but this time I left the "Generate the
>DROP <object>" checked. The script had DROP commands for the current views,
>but the CREATE VIEWs were all the old view names and queries.

That's how you should rename your objects next time, but it won't help
now. I guess you'll have to manually change the name in the scripts.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Tracy

2005-08-18, 9:37 am

Thank you!

I was able to do in-depth research with the starting point you gave me.

I executed "select * from information_schema.views" and found that the
"view_definition" column had the old names. I was a little hasty in my
conclusion that the queries were old as well. I checked them thoroughly and
they look to be correct.

So to solve my current problem, I just need to change the names in the
generated script and then run the script, (including the DROPs).

In the future, I will try to get by without renaming. But if I must rename
the views, I will copy the query, drop the view, create a new view, paste the
query, and save it with the new name.

That's a lot more steps than simply doing the Explorer-style rename, but it
will save a lot of grief in the long run.

Thanks again, Hugo and Wayne, for your help

-Tracy

"Hugo Kornelis" wrote:

> Thanks - I never even knew that you could rename objects that way as
> well!
>
> However, the best way to rename views, stored procedures, triggers, and
> user-defined functions is to explicitly drop and recreate them. Here's a
> recent post by Tibor Karaszi about what goes wrong:
> (Warning - long URL, check for line wrapping)
>
> http://groups.google.com/groups?hl=...Karaszi&safe=of
f& qt_s=Search
>

Hugo Kornelis

2005-08-18, 8:23 pm

On Thu, 18 Aug 2005 06:50:08 -0700, Tracy wrote:

(snip)
>In the future, I will try to get by without renaming. But if I must rename
>the views, I will copy the query, drop the view, create a new view, paste the
>query, and save it with the new name.
>
>That's a lot more steps than simply doing the Explorer-style rename, but it
>will save a lot of grief in the long run.

Hi Tracy,

Slightly shorter: generate a script including the DROP command; edit the
script (but only chage the name on the CREATE command) and execute it.

Better yet: store all DDL for your tables, views, procs, etc outside of
SQL Server - preferably in source control.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Sponsored Links