Fixing SysDepends
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/sjones/fixingsysdepends.asp
-
Cheers 4 that - looks very useful!
-
I receive an error message just before the image appears that will (hopefully) display the dependencies.
Here's what's logged to the event viewer:
These are the technical details about an error that occurred.
If you are need assistance with this you can phone or email us:
phone: 1 866 733 4283 (toll free USA and Canada)
phone: +44 (0)870 160 0037 (rest of the world)
email: support@red-gate.com
exception name: System.Net.WebException
description: The remote server returned an error: (407) Proxy Authentication Required.
handler: user interface
------------------------------ STACK TRACE BEGINS ------------------------------
at System.Net.HttpWebRequest.CheckFinalStatus()
at System.Net.HttpWebRequest.EndGetResponse(IAsyncResult asyncResult)
at System.Net.HttpWebRequest.GetResponse()
at System.Xml.XmlDownloadManager.GetNonFileStream(Uri uri, ICredentials credentials)
at System.Xml.XmlDownloadManager.GetStream(Uri uri, ICredentials credentials)
at System.Xml.XmlUrlResolver.GetEntity(Uri absoluteUri, String role, Type ofObjectToReturn)
at System.Xml.Schema.DtdParser.ParseDocTypeDecl()
at System.Xml.Schema.DtdParser.Parse()
at System.Xml.XmlTextReader.ParseDtd(XmlScanner scanner)
at System.Xml.XmlTextReader.ParseTag()
at System.Xml.XmlTextReader.ParseRoot()
at System.Xml.XmlTextReader.Read()
at System.Xml.XmlValidatingReader.ReadWithCollectTextToken()
at System.Xml.XmlValidatingReader.Read()
at System.Xml.XmlLoader.LoadCurrentNode()
at System.Xml.XmlLoader.LoadDocSequence(XmlDocument parentDoc)
at System.Xml.XmlLoader.Load(XmlDocument doc, XmlReader reader, Boolean preserveWhitespace)
at System.Xml.XmlDocument.Load(XmlReader reader)
at System.Xml.XmlDocument.Load(String filename)
at _16._1(String )
at _28._1(String )
at _26._3(Object , EventArgs )
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
------------------------------ STACK TRACE ENDS ------------------------------
-
A nice utility which would be even nicer if it could filter on object type/object name prefix or suffix. This would make it easier to handle databases with a large number of objects.
BrgdsJonas
-
Looks like a useful utility. It did miss one area of dependicies that matter to my job that get over looked by the developers when they make changes to table structures, DTS packages.
-
This shows good potential, however I think the article doesn't relate to the application as it doesn't seem to fix the sysdepends problems in any way. I think the title is slightly misleading... However again, this is something that I could really use in real life.
-
Still evaluating, but certainly looks promising.
Wish List:
Ability to rearrange objects.
Sent to file or printer.
-
Title of the article is not appropriate. How does this fixes sysdepends again?
[Edit] : Oops I see Remi already mentioned
* Noel -
That would be the real turn on to buy a product.
I guess it could be done. If you were to re-run each script with alter proc... but I never got the courage to run that on production
. -
I'd like to be able to tell it what object to start with.
Need to be able to print it.
need to be able to zoom-all and have it centered. (need to be able to move objects so the zoom works better -- kind of like the diagram tool in Enterprise Manager).
-
It's cool, but it makes it really difficult for large models. Perhaps more of an effort to fit items on screen instead of one large row for items and then going down to dependent items would work better.
Neat and it looks like it will be useful. No DTS dependencies as noted, but that was kind of expected. I imagine it would also have trouble with encrypted procs, but I'll need to test that.
Overall, it's a decent start for this tool. Worth keeping an eye on.
-
It was suggested that spinning through all scripts doing ALTER PROCEDURE would fix the dependencies, but that may not work. It looks like ALTER PROCEDURE does not preserve the dependencies as BOL seems to say it does. In the example below, the fact that Test3 depends on Test2 is lost when Test2 is altered.
CREATE PROCEDURE Test1
AS
SELECT 'Test1'
RETURN
GO
CREATE PROCEDURE Test2
AS
SELECT 'Test2'
EXECUTE Test1
RETURN
GO
CREATE PROCEDURE Test3
AS
SELECT 'Test3'
EXECUTE Test2
RETURN
GO
EXECUTE Test1
EXECUTE Test2
EXECUTE Test3
GO
SELECT O.name, T.name
FROM sysobjects AS O
JOIN sysdepends AS D
ON O.id = D.id
JOIN sysobjects AS T
ON D.depid = T.id
WHERE O.name LIKE 'Test[123]'
GO
ALTER PROCEDURE Test2
AS
SELECT 'Test2'
EXECUTE Test1
RETURN
GO
SELECT O.name, T.name
FROM sysobjects AS O
JOIN sysdepends AS D
ON O.id = D.id
JOIN sysobjects AS T
ON D.depid = T.id
WHERE O.name LIKE 'Test[123]'
GO
DROP PROCEDURE Test1
DROP PROCEDURE Test2
DROP PROCEDURE Test3
GO
-
Nice catch. Do you have any idea on how we could go around that problem?
-
Hi,
I'd be interested in knowing *why* you want to fix sysdepends. Is there an underlying reason? For example, do you need to script things out in the correct order, or just list dependencies, or know the impact of modifications you're about to make?
- Neil
-
Basically, when I change a table, I like to know all the objets (client and app side) that may be impacted by the change and retest those. I had to create a tool (however imperfect) to do that that makes sure I don't forget a single place to check.
It would be nice to be able to do that only using the sysdepends table instead of having to do string manipulations that can't be 100% correct all the time.
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply