TSQL - How to Execute Large SQL Script File In SQL Server
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]
sqlcmd -S MyServerName\InstanceName -d MyDataBaseName-i c:\MySQLScriptFile.sql
Fig 3: Running SQL Script File by SQLCmd