TSQL - How to Execute Large SQL Script File In SQL Server

I got this big (4GB) SQL Script file(Schem +Data)  from one of our vendor that I needed to execute on one or our database.

Execute File by using SSMS:
First I tried to open that file with SSMS but received error as below

Error HRESULT E_FAIL has been returned from a call to a COM component. (mscorlib)

Fig 1: Error Message from SQL Server Management Studio

Seems like we can not open this big file with SQL Server Management studio and execute.

Execute Large SQL File By SSIS:
The next thing came in my mind, Why not to run this big file by using SSIS. Created quick SSIS Package with Execute SQL Task and provided the File path for Execute. When executed, received below error

Error: 0xC002F304 at Execute SQL Task, Execute SQL Task: An error occurred with the following error message: "Exception of type 'System.OutOfMemoryException' was thrown.".

Fig 2: Error Message from SSIS Package while execute Large SQL File.

Look like, SSIS also can not run this large SQL script file by using Execute SQL Task.

Execute Large SQL Script File by using SQLCmd:
Executed the SQL Script file by using SQLCmd and it worked :)
Go to Run, type CMD and then paste and hit Enter

sqlcmd -S [servername] -d [databasename] -i [scriptfilename]

e.g.
sqlcmd -S MyServerName\InstanceName -d MyDataBaseName-i c:\MySQLScriptFile.sql


Fig 3: Running SQL Script File by SQLCmd

2 comments:

  1. Excellent post. This article is very helpful for the people and also for me. I get the some useful knowledge in this post. Thanks for sharing the informative topic, by the way
    Best BBA Tuition in Rohini | Best BBA Coaching Classes in Rohini | Best BBA Coaching in Rohini | Best BBA Classes in Rohini | Best BBA Tuition Center in Rohini

    ReplyDelete
  2. So can you share the more details about it, then will be helpful for everyone. Also check out spacebar counter for 20

    ReplyDelete