• v.bartosh - Wednesday, December 20, 2017 3:15 AM

    Jeff Moden - Tuesday, December 19, 2017 9:32 PM

    I suspect that you're generating a script that uses a RBAR stored procedure to insert data into tables and that you're generating an EXEC for every row you're trying to insert.

    If that's what you're doing, try the following, instead.  Whatever "system" you're using to generate the SQLCMD stuff, change that to generate a TAB delimited file and using BULK INSERT to load the data file into a table.  Then, if you insist on continuing to use the RBAR stored procedure to input the data, just loop through the rows of the table to do it.

    A much better thing to do would be to still create the TAB delimited file and BULK INSERT it into a table and then rewrite the RBAR stored procedure to do things in bulk, as well.

    You are most surely right, but in this particular case we cannot do that, at least not the easy way. My question was not about the speed optimization, but about the possibility to run the script at all.
    But after looking into the script, we found out that there was an unescaped quote in some string values, causing SQLCMD to interpret the script incorrectly, hence the 'out of memory' error. Why not the syntax error - that I do not know.

    After we had fixed those quotes the script ran just fine, with SQLCMD consuming 15MB memory maximum.

    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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)