May 5, 2023 at 2:13 am
NOTE: Forgive me if I am using the wrong terminology with regards to this. \
I am trying to get a list of all The SP's (Stored Procedures) that are called by an SP. For example let's say I have the following SP's:
LF5_Job1, LF5_JOb2, LF5_CalcResFees, F5_CalcCommFees & LF5_PopulatFees
The SP LF5_Job contains one 'EXECUTE' in it and it's to LF5_job2
The SP LF5_Job2 has 2 'Executes' in it. One to LF5_CalcResFees and another to LF5_CalcCommFees.
The SP's LF5_CalcResFees and LF5_CalcComFees each have 1 'Execute' in them and it's to to LF5_PopulateFees
What I need to see is something that would show me the hierarchal structure of these SP's, smoothing along these lines:
I didn't paste the code in her because the actual SP's are very lengthy and their inner workings aren't relevant to the question. I've tried several solutions but none will show the complete hierarchy. At best I can get one solution to show what SP the specified SP calls but if that SP calls more than 1 SP the solutions shows just the first SP it calls.
Any positive/useful input is appreciated.
Thanks
Kindest Regards,
Just say No to Facebook!May 5, 2023 at 3:49 am
The old article you found in a previous response to your question used sysdepends, which would sometimes miss things depending on the order of creation. Take a look at the following, which also has the example code that you're looking for.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 5, 2023 at 4:39 am
Thanks soooo much Jeff!
Kindest Regards,
Just say No to Facebook!May 5, 2023 at 4:52 am
Jeff - I just took a look at the article and gave it a try and none of the examples work for an SP. I see that they list SP's in the article but unless there is a different query from the examples for SP's this doesn't appear to . As I am typing this one one system and reading the article on another I believe I may know the issue. You must have VIEW DEFINITION permission and I bet I don't. I'm testing this on our cloud based DB from our app provider using my surface laptop and we defiantly do not have dbo rights to our own DB. I'll have to test it more tomorrow as it's midnight and time to call it a night!
Thanks Again JJeff
Kindest Regards,
Just say No to Facebook!May 5, 2023 at 1:13 pm
You must have VIEW DEFINITION permission and I bet I don't. I'm testing this on our cloud based DB from our app provider using my surface laptop and we defiantly do not have dbo rights to our own DB.
That would definitely be a limitation and another reason why I don't like "Cloud Implementations".
One of the companies I do a little work for is going through that same horrible limitation. The phone system they use has a local SQL Server Database and it was super easy to create special reports not provided by the maker. They took to making their own reports because the maker would charge an arm and a leg to create each new report and they'd usually not get it right.
The maker has now declared that all on-premise systems will no longer be supported and, since the maker "owns" the software and the database, has stated the they MUST migrate to the "new and improved" cloud version. Not only does the company that uses that new "Smoke'n'Mirrors" version NOT have DBO privs, they no longer even have access to the underlying data once again proving my take on things in general...
"Change is inevitable... change for the better is not". 🙁
Thank you for the feedback on what you're running into. I really appreciate it.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 5, 2023 at 4:18 pm
Yep just another case of cooperate money grubbers trying to line their pockets with even more than they already have -- problem is they keep stepping over the hundred dollar bills to pick up that extra penny. They are to much transaction thinking (limited scope short term gain) versus portfolio thinking (broader scope long term gain). As yeah in the short run they may see additional revenue but in the long term what this does is make their customers become disgruntled and go looking for another solution that either costs about the same but with more flexibility or that is cheaper. As both of these solutions are out there and in plenty, so it is not like they do not have competition. Thus inevitably they are just shooting themselves in their feet (not just one foot but both feet). I have seen this same issue within management in numerous companies and usually it eventually kills the company or gets caught by a smart manager and gets axed from the company in favor of better more productive and long term sustainability thinking. However, sadly the former seems to happen much more often than the latter.
And as Jeff Moden points out: "Change is inevitable... change for the better (or the best) is not."
May 5, 2023 at 5:36 pm
My absolute pleasure and like you I have been thru the painful move to the vendor's cloud and since we've stopped having the app on-prem things have been a lot harder. We managed (due to our influence and size as a client) to convince the vendor to gave us a VPN Tunnel so I can connect SSMS on my desktop over the tunnel to the SQL DB that is our DB but I'm limited to effectively DBO level which is sufficient for most of what I need to do. We also managed to get them to approve the permission necessary to profile/capture/trace which has also been a tremendous help when trying to fix the vendors own mistakes. Still it's nothing compared to being on-prem and me having SA level access.
We held off on moving to the vendors cloud up until they made a version of their accounting software that was available only in the cloud. At that point we had little choice. Our Direct access to teh DB though has helped offset a lot of the downsides to moving to the vendors cloud based software.
I used to work for the software vendor but I left and a year later one of the clients of the software I provided support to offered me a job so I have an inside view and knowledge of the vendor and I believe the move to the cloud was more about hiding as much from the clients as possible as to how the system works. It also meant that the vendors support people always had direct access to every clients data to make updates/fixes when necessary. In this case I believe the cloud was about more than just money, it was about money and control. It's now much easier for the vendor to roll out updates since it doesn't have to be packaged such that it can be done by the clients as we had to do in the past.
I believe the cloud should be treated as a tool, something that's good for somethings but not everything,. and not the be-all and -all software solution it's marketed to be.
Kindest Regards,
Just say No to Facebook!May 5, 2023 at 5:37 pm
Out of curiosity what type of reports were they making? Was it using one of teh 3rd party popular custom reporting apps like Crystal Reports or SSRS or was it something proprietary to the app?
Kindest Regards,
Just say No to Facebook!May 5, 2023 at 7:50 pm
It's a telephone system. They just wanted different ways to evaluate things like doing call duration analysis depending on which client someone was calling about (we handle the calls for most of our clients) and what category the call was based on, etc, etc. Some of it is based on ad hoc/what if stuff and some of it is scheduled to be delivered by day, week, day of week, month, and quarter. Some of the reports are actually a "bottom line summary" of multiple different reports. Some are a bit more complicated based on "call path" but usually not.
It's amazing to me that such reports cost so much for a vendor to build and how wrong they can get it. The reports also have a major suck factor when it comes to the load it places on the telephone system itself.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 5, 2023 at 9:06 pm
I can't say the same applies to your situation but on my end when the vendor does reports they charge a lot (I used to make those reports mostly in Crystal Reports when I worked at the vendor) and often it's done by some who's background with SQL boils down to reading "Learn SQL in 24 hours" and that's it and no I'm not joking. The mentality at the company at the time (ie this was 16 years ago so bare that in mind ) was that SQL was something any dev could learn in a few hours. They even created an in-house query builder that would generate the code for them and of course it was usually very ugly and never tweaked.
In my business we have to close our accounting books for the entities we manage at the end of every month. In this accounting software there is a function you run as part of this process that creates 10's of thousands of charges and a few thousand receipts. When you run this function from within the application you have to run it for a small number of entities (versus your entire portfolio) at a time else it can lock up or crash. Doing it this way takes about 1 minute per entity depending on how many leases you have at each entity. We have 100+ of these entities so it can take a while to complete. When I came to work where I am now I decided to dig into the process using SQL Profiler and found that the SP's it was using and I re-wrote the main SP to by-pass some poor design decisions . Long story short, when we call my SP directly for the entire portfolio it completes in around 18 minutes on average. This was 14-15 years ago, they've yet to update the code so we've been back door running this function for 15 years and not once have we had any issues or discrepancies in our accounting from doing this.
Kindest Regards,
Just say No to Facebook!May 6, 2023 at 2:12 am
and often it's done by some who's background with SQL boils down to reading "Learn SQL in 24 hours" and that's it and no I'm not joking.
That's pretty much the same as what I've been seeing from the vendor and the "support vendors" for this particular phone system. A lot of them have also appeared to listen to folks that say things like "Ah... for that, you need a cursor". <headdesk>
--Jeff Moden
Change is inevitable... Change for the better is not.
May 6, 2023 at 2:55 am
OMG. This vendor has soooo many cursors. Pretty much all updates to transactions (charges, receipts, check, invoices) are done using a cursor. In more recent years I heard they finally hired a few SQL pro's because international clients were too big for these poorly written queries to work. I can see in newer stuff where these newer guys are on teh job as I'm starting to see more modern techniques like using MERGE and Windows functions but I doubt they'll ever fully replace all the cursors because that's what the traditional programmers are comfortable with, iteration. Working in sets of data just is not something they can grasp.
Kindest Regards,
Just say No to Facebook!Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply