locked
Sql Script RRS feed

  • Question

  • User-1499457942 posted

    Hi

      I am executing sql script it is giving me error 'An unhandled exception of type System.Outofmemoryexecption' occurred in mscorlib.dll. File size is more than 10 G.B

    Thanks

    Thursday, January 25, 2018 7:32 AM

All replies

  • Thursday, January 25, 2018 9:01 AM
  • User1400794712 posted

    Hi JagjitSingh,

    According to your description, it seems that the file is too large to load it in memory.

    The file is too big, we'd better to not read it at one time. You can use StreamReader.ReadLine() to read the file line by line.

    string sqlstatements=null;
    using(StreamReader sr=new StreamReader(@"D:\Daisy\sqltest.txt"))
    {
        while (!sr.EndOfStream)
        {
            sqlstatements+=sr.ReadLine();//sr.ReadLine() will read data line by line.
        }
    }
    

    Best Regards,

    Daisy

    Friday, January 26, 2018 8:36 AM
  • User753101303 posted

    Hi,

    More likely you try to load the full script in memory ? It should run in chunks (do you have "go" delilmiters in this script).

    If this is a one time operation, you could also try a command line tool (that may run the script in chunks already). If not I would try to see if there is no better option than a huge SQL script for this data transfert.

    Friday, January 26, 2018 8:43 AM
  • User842257015 posted

    Would you please try <g class="gr_ gr_47 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling" id="47" data-gr-id="47"><g class="gr_ gr_113 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar only-ins replaceWithoutSep" id="113" data-gr-id="113">sqlcmd</g></g> command, open Command prompt window with administrator access, type below command.

    <g class="gr_ gr_91 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling" id="91" data-gr-id="91">sqlcmd</g> -s <<ServerName>> -i <<file path with extension >>

    If you wanna save execution summary, use windows <g class="gr_ gr_187 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="187" data-gr-id="187">powershell</g> and execute the same <g class="gr_ gr_244 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling" id="244" data-gr-id="244">sqlcmd</g>.

    Note: please make sure your file properly designed.

    Friday, January 26, 2018 3:49 PM