osql input command file limitation?

• SeeCoolGuy

  SSCrazy

  Points: 2902

  More actions

  July 30, 2008 at 11:11 am

  #71769

  I have a command file that was generated from a legacy system, the total size of the command file is 2gb, I have timmed out with the following error: Error: cannot allocate memory for command buffer.

  My command looked like this:

  osql -E -S ServerName -d Databae -i C:\myfile.txt

  I suspect that it is due to the file as it is 2gb, any advise?

  ....

  more info:

  the file has the following sql commands:

  INSERT INTO "TableName" ('Value', 'Value2', 'subvalue3;subvalue4;subvalue5', 'Value6');

  INSERT INTO "TableName" ('Value', 'Value12', 'subvalue13;subvalue14;subvalue15', 'Value16');

  INSERT INTO "TableName" ('Value', 'Value22', 'subvalue23;subvalue24;subvalue25', 'Value26');

  ....up to 2gb

  -- Francisco

• Sergiy

  SSC Guru

  Points: 110206

  More actions

  July 31, 2008 at 5:02 pm

  #851590

  Francisco (7/30/2008)

  ---

  I have a command file that was generated from a legacy system, the total size of the command file is 2gb, I have timmed out with the following error: Error: cannot allocate memory for command buffer.

  You're obviously not lazy to build command files like this.

  And because you can make computer do it it does not mean it's smart.

  I would be lazy and create flat file with data (actually I'd prefer just to get this file from whoever needs the data to be inserted):

  Value, Value2, subvalue3;subvalue4;subvalue5, Value6

  Value, Value12, subvalue13;subvalue14;subvalue15, Value16

  Value, Value22, subvalue23;subvalue24;subvalue25, Value26

  and use simple bcp command (less than 1 kb command file ;)) to populate the table with the data in the file.

  _____________
  Code for TallyGenerator

• Jeff Moden

  SSC Guru

  Points: 1003851

  More actions

  July 31, 2008 at 6:28 pm

  #851608

  Yeaup! I agree with Sergiy... The file should only contain delimited data and then use either BCP or BULK INSERT to import the data. See Books Online for more information on both... they're pretty simple to use, too!

  --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)