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. 4 7495
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).
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.
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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
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 problem. The program may crash
unexpectedly while writing to the file. If so, my program should
detect this during startup, and then (during...
|
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 project 1-solution A
accessing a "save" method in project 2-solution B as well as "getinfo"
method in project 2 accessing a "read" method in project...
|
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 missing that keeps resetting the
dependencies wrongly.
Thanks,
Bruce
|
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";
return (SCS.SR.IDAL.IUser)
Assembly.Load(path).CreateInstance(className); ( This line iam getting
Error)
|
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 = ConfigurationSettings.AppSettings;
Thanks!
| |
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 during compilation giving it by means ofthe @ compiler option that is provided through projectproperties: C/C++ -> Command Line -> Additional Options....
|
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 dependencies, was not
found.
Description: An unhandled exception occurred during the execution of
the current web request. Please review the stack...
|
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 System.Xml, or one of its dependencies, was not found.
Description: An unhandled exception occurred during the execution of the
current web request....
|
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 upload them to the server and after signing them in to the GAC
i m getting some errors.
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
| |
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |