473,320 Members | 1,746 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Fix dependencies during or after renaming

The previous post regarding dependencies reminded me of another bugger I come
up with from time to time. SQL Server allows us to rename objects via a
system stored procedure (I usually use an ADP front-end that presumably calls
the same procedure), but after the rename, I find singnificant strangeness.

For instance, triggers are still connected to the tables, but the trigger text
still refers to the old table name. Views and stored procedures dependent on
the table are broken, and are not fixed by even by restarting the server,
though I thought the cached plans were supposed to be dumped on shut-down,
then recreated on demand later.

Is there some tool or technique to rename a table and automatically detect and
properly drop/rebuild dependent triggers, views, stored procedures,
relationships, and anything else I'm not thinking of?

Oh yeah, and there are extended properties to deal with as well, but I guess
that's intractible unless we know what client build them (e.g. Access ADP),
and what it expects to be recorded there, but I suppose it would be nice to
just dump them rather than leave them there possibly corrupted from a client's
persective.
Jul 20 '05 #1
4 7482
Steve Jorgensen wrote:
The previous post regarding dependencies reminded me of another bugger I come
up with from time to time. SQL Server allows us to rename objects via a
system stored procedure (I usually use an ADP front-end that presumably calls
the same procedure), but after the rename, I find singnificant strangeness.

For instance, triggers are still connected to the tables, but the trigger text
still refers to the old table name. Views and stored procedures dependent on
the table are broken, and are not fixed by even by restarting the server,
though I thought the cached plans were supposed to be dumped on shut-down,
then recreated on demand later.

Is there some tool or technique to rename a table and automatically detect and
properly drop/rebuild dependent triggers, views, stored procedures,
relationships, and anything else I'm not thinking of?

Oh yeah, and there are extended properties to deal with as well, but I guess
that's intractible unless we know what client build them (e.g. Access ADP),
and what it expects to be recorded there, but I suppose it would be nice to
just dump them rather than leave them there possibly corrupted from a client's
persective.


One thing I know about views, if you rename a view, you best double
click it in EM and rename it in the create statement as well otherwise
it will get renamed back to the old name if ever you script it.
(checks his 4 views, nope, that's not why that happened).
Jul 20 '05 #2
On Fri, 23 Jan 2004 11:01:27 GMT, Steve Jorgensen <no****@nospam.nospam>
wrote:
The previous post regarding dependencies reminded me of another bugger I come
up with from time to time. SQL Server allows us to rename objects via a
system stored procedure (I usually use an ADP front-end that presumably calls
the same procedure), but after the rename, I find singnificant strangeness.

For instance, triggers are still connected to the tables, but the trigger text
still refers to the old table name. Views and stored procedures dependent on
the table are broken, and are not fixed by even by restarting the server,
though I thought the cached plans were supposed to be dumped on shut-down,
then recreated on demand later.


Oh yeah, I guess the views and procedures don't get the table names updated in
the code, so that one's obvious (though I'd still like an automated way to fix
it). What -was- confusing about that was why renaming a -column- causes
problems for stored procedures and views, even after a restart, even if the
procedure or view is uing an asterisk in the select and not directly
referencing the column in question.
Jul 20 '05 #3
Steve Jorgensen (no****@nospam.nospam) writes:
Oh yeah, I guess the views and procedures don't get the table names
updated in the code, so that one's obvious (though I'd still like an
automated way to fix it). What -was- confusing about that was why
renaming a -column- causes problems for stored procedures and views,
even after a restart, even if the procedure or view is uing an asterisk
in the select and not directly referencing the column in question.


Hm, I wrote this procedure:

create proc test_sp as select * from test

Which I subsequently executed. And I found that test had two columns
'mystring' and 'col'.

Then I ran:

sp_rename 'test.mystring', hisstring, 'column'

Without restarting the server, I successfully ran the procedure, and
the column was called 'hisstring'. I then created:

create view test_view as select * from test

And then:

sp_rename 'test.hisstring', herstring, 'column'

I was able to select from the view after this. However in the view
the column was still named 'hisstring'. This is because SQL Server
stores the name of the columns in the view in the system tables. This
can be remedied with:

exec sp_refreshview test_view

Finally, I would like to point out that I, and many with me, find it
to be bad coding practice to use SELECT * in production code.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #4
Steve Jorgensen (no****@nospam.nospam) writes:
The previous post regarding dependencies reminded me of another bugger I
come up with from time to time. SQL Server allows us to rename objects
via a system stored procedure (I usually use an ADP front-end that
presumably calls the same procedure), but after the rename, I find
singnificant strangeness.

For instance, triggers are still connected to the tables, but the
trigger text still refers to the old table name. Views and stored
procedures dependent on the table are broken, and are not fixed by even
by restarting the server, though I thought the cached plans were
supposed to be dumped on shut-down, then recreated on demand later.

Is there some tool or technique to rename a table and automatically
detect and properly drop/rebuild dependent triggers, views, stored
procedures, relationships, and anything else I'm not thinking of?


The major enterprise data-modelling tools offers such features.

If you have your code under version-control - and this is what you should -
you could run some search/replace tool, provided that the old column
name is significant enough.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

48
by: Joseph | last post by:
Hi I'm writing a commercial program which must be reliable. It has to do some basic reading and writing to and from files on the hard disk, and also to a floppy. I have foreseen a potential...
7
by: Brian Sabolik | last post by:
I'm not sure if I've broken any Object Oriented rules or not, but ... I have projects in 2 different solutions that need to use each other's methods. Therefore I may have an "update" method in...
3
by: Bruce Wilhelm | last post by:
I have a VS2003 solution with 4 projects in it. I am trying to set up the dependencies in the dialog box, but after I close the dialog the dependencies do not change. Is there something that I am...
0
by: karunakar | last post by:
Hi All I am not able to read the class name I want read the particular class name string path = System.Configuration.ConfigurationSettings.AppSettings; string className = path + ".User";...
2
by: cloudx | last post by:
Hi there, it is driving me crazy. Aftering renaming app.config the following code I always get null on myApp. It works if I keep the name app.config. Why? string myApp =...
0
by: Guest | last post by:
Hi! Could anyone help me with my problem? I have a VC++.Net project. Project specific defines and includepaths are saved in an external file (response file) as compileroptions. I use the file...
2
by: aallee83 | last post by:
i'm new in asp.net after develop my solution i copied it on the server where I want it to run but something cares... WHAT?!?! thank you in advance File or assembly name System, or one of its...
1
by: Olav Tollefsen | last post by:
I get the included error message when trying to run my ASP.NET application under Windows Server 2003 (with all updates installed). How can I troubleshoot this? Olav File or assembly name...
4
by: Tiraman | last post by:
Hi, i have some problem to run my dll's (assemblies) on the production server. I have 3 dll's which use each other. Every thing is ok on my local machine where i developed them but when i...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.