Any limitation for sqlcmd?

• Chiang

  SSC Veteran

  Points: 261

  More actions

  October 20, 2011 at 9:14 am

  #259027

  Hi all,

  I used

  sqlcmd -U dbName -P password -S hostName -y 0 -u -d dbSchema -i C:\MyLargeScriptFile.sql

  Is there a limit for the file size for MyLargeScriptFile.sql?

  The reason is that when I try out a 22MB size for MyLargeScriptFile.sql (containing a list of insert statements), it seems to load and hang forever.

  Thanks

• Elliott Whitlow

  SSC Guru

  Points: 102402

  More actions

  October 20, 2011 at 10:52 am

  #1397540

  I seem to remember only that the limit is something like 64K times the packet size or like 256MB by default.

  See http://msdn.microsoft.com/en-us/library/ms143432.aspx

  I am unaware of anything different in sqlcmd. However, have you considered that a 22MB file will take rather a long time to open and push up the pipe to the SQL Server? As well as single insert statements are pretty expensive..

  CEWII

• Jeff Moden

  SSC Guru

  Points: 1004365

  More actions

  October 20, 2011 at 7:57 pm

  #1397720

  Chiang (10/20/2011)

  ---

  Hi all,

  I used

  sqlcmd -U dbName -P password -S hostName -y 0 -u -d dbSchema -i C:\MyLargeScriptFile.sql

  Is there a limit for the file size for MyLargeScriptFile.sql?

  The reason is that when I try out a 22MB size for MyLargeScriptFile.sql (containing a list of insert statements), it seems to load and hang forever.

  Thanks

  It's not SQLCMD that's the problem... you'd have the same problem if it was a script in SSIS. It's 22MB worth of INSERT statements that's the problem. As Elliot suggested, pushing stuff like this on SQL Server inherently takes a long time. Instead of using INSERT statements, why not just use a raw data-file and use BULK INSERT to load the data?

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