v.bartosh - Wednesday, December 20, 2017 3:15 AM
I realize that your question had nothing to do with performance optimization. My additional rhetoric on the subject of performance is something to consider because this will become a performance problem when the company can least afford for it become a problem. 😉
I also agree that my suggestion about using something like a TAB delimited file wouldn't be easy because it would require you good folks to change the source code that produces the current output and build some new code on the SQL Server side to receive the data of the new output.
My concern about the process and the company that you work for is genuine, though. There hasn't yet been a company I've worked for that hasn't done something similar to the current process. The nature of business puts everyone in the "if it works, don't mess with it" mode and that's fine... right up to the point where it no longer works in the time allotted or causes resource issues that slow other processes down. Of course, that's the worst time to find out about it because, chances are, you're under the gun to have it work in the time allotted and all hell breaks loose trying to come up with a quick fix and there isn't anything quick about such a fix.
The most difficult and longest to code part of my suggestion is to change the output to a delimited file, write the T-SQL to import it into a table, and then write the code to loop through the table to run the stored procedure one row at a time. While that may seem fruitless unless you change the final proc to work in an other than RBAR fashion, it won't be fruitless. It'll make it a whole lot easier to come up with a quick fix when the proverbial poo hits the fan because you won't have to find someone that can make the change at the source and make the table load work. Instead, you might just be able to tweak some things in the final stored procedure to temporarily get you past the crisis moment and then fix the proc to run in a non-RBAR mode in a much less urgent fashion.
So my recommendation continues... convince management to be proactive on this one so that the entire group doesn't have to go reactive when the eventuality of problems with the current method rear their ugly heads when you can least afford for them to do so (like they have at every company I've seen use such methods). And, yeah... if they refuse, get the refusal in writing because heads tend to roll during and after a crisis. Protect your head. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.