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

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