- C# Scripts
- DWH INTERVIEW QUESTIONS
- MS Dynamics AX 2012 R2 Video Tutorial
- Project / Work Support
- SQL SERVER DBA INTERVIEW QUESTIONS
- SQL SERVER DBA Video Tutorial
- SQL Server / TSQL Tutorial
- SQL Server 2016
- SQL Server Scripts
- SSIS INTERVIEW QUESTIONS
- SSIS Video Tutorial
- SSRS INTERVIEW QUESTIONS
- SSRS Video Tutorial
- TSQL INTERVIEW QUESTIONS
- Team Foundation Server 2013 Video Tutorial
- Team Foundation Server 2015 Video Tutorial
- Windows 10
- Windows Server 2012 R2 Installation Videos
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]
sqlcmd -S MyServerName\InstanceName -d MyDataBaseName-i c:\MySQLScriptFile.sql
Fig 3: Running SQL Script File by SQLCmd