- Batch File Run Sql Script
- Sql Server Execute Batch File From Stored Procedure
- Tsql Execute Sql File
- Microsoft Sql Demo Database
- Sql Server 2012 Execute Batch File
- Sql Server Execute Batch File In Batch File
Introduction
The Bulk copy program aka bcp is the console application used to export and import data from text files to SQL Server or vice versa.
I'm having a little problem executing a batch file in SQL Server 2008. I've created a job and I want to zip a file using 7zip and then move that zip to an FTP server. I have a batch file to do this for me, but I want to run it in a job. So first I create a job and then I create a step. The type of the step goes to Operating System (CmdExec).
If you need to execute a script file with sqlcmd on a server using Windows Authentication (a Trusted Connection), you can do so with the following command: sqlcmd -S 127.0.0.1 -E -i AdventureWorksDW2012.sql The –S argument is the server name, and the –E argument is for a Trusted Connection. Hello, Am trying to execute a batch / cmd file with dynamic parameter to the batch file using t-sql / xpcmdshell. Basically am trying to execute a robocopy command from t-sql - read somewhere that this is not possible & i hav to wrap my command in batch file.
It is very popular because it is fast and easy to download.
This tool is installed by default with SQL Server. It is usually installed in the following path:
Drive: Program FilesMicrosoft SQL ServerClient SDKODBC130ToolsBinnbcp.exe
In this article we will learn how to:
- Get the bcp arguments
- Get the version
- Export data from a SQL Server table to a file
- Export data from a SQL Server query to a file
- Run bcp using PowerShell
- Run bcp on SSIS
- Invoke a batch file in SSIS
Requirements
- SQL Server installed
- bcp installed
- The Adventureworks database installed
- SSIS Installed
- SSDT installed
Get started
Batch File Run Sql Script
1. Get the bcp arguments
In the command line, write bcp. This will show you the possible arguments that can be used:
Sql Server Execute Batch File From Stored Procedure
bcp /? Is the same than bcp. It shows you the possible parameters:
2. Get the version
You can get the version of the bcp using the -v argument:
Here you have a list of versions of bcp:
bcp version | SQL Server |
14.0.X.X | SQL Server 2017 |
13.0.X.X | SQL Server 2016 |
12.0.X.X | SQL Server 2014 |
11.0.X.X | SQL Server 2012 |
10.0.X.X | SQL Server 2008 |
9.0.X.X | SQL Server 2005 |
8.0.X.X | SQL Server 2000 |
7.0.X.X | SQL Server 7 |
3. Export data from a SQL Server table to a file
To export data from a SQL Server table or view to a file, you can use the bcp command.
The following example exports the table person.person from the adventureworks database to a file named dimcustomer.bcp
bcp adventureworks.person.person out c:sqldimcustomer.bcp -c -T -S WIN-355CHQ0E524
The -c argument is used to perform operations using a character type. -T is used to connect using a Trusted connection (Windows Authentication). -S is used to specify the SQL Server name.
If everything is OK, the rows will be copied successfully:
You will receive the number of rows copied, the network packet size and the speed to copy the rows per second.
If everything is OK, the file will be created:
You can open the file and check the data:
4. Export data from a SQL Server query to a file
You can export data from a SQL Server Query to a file. To do this, you need to specify the query in quotes and use the out argument:
bcp adventureworks.person.person out c:sqldimcustomer.bcp -c -T -S WIN-355CHQ0E524
The result displayed is the following:
5. Run bcp using PowerShell
PowerShell is a powerful tool to automate tasks using scripts and the command line. You can also run bcp using PowerShell, which is included with Windows and it can be also installed on Linux and Mac.
In PowerShell create variables to store the database name, schema, table and output path:
2 4 6 | $db='adventureworks' $table='person' |
Store the bcp command line with the parameters in another parameter:
2 | $psCommand='bcp $($db).$($schema).$($table) out $path -T -c' |
Use the Invoke-Expression to call the variable with the bcp commands:
2 |
![File File](/uploads/1/2/6/2/126205447/839681854.jpg)
PowerShell will import the files in the powershell.txt file:
6. Run bcp on SSIS
It is not a common practice to run bcp on SSIS because SSIS contains tasks to import and export data. You can use the Data Flow task to create customized tasks or maybe use the Bulk Insert task to import data to SQL Server. https://madisonsoha.weebly.com/serial-shahrzad-16.html.
However, there are some scenarios where you can invoke bcp in SSIS. For example, if you already have some command lines in bcp and you just want to invoke them. Another case is when you are adept at the command line and you already have some batch files ready and you just want to invoke them.
Open the SSDT and create a New Project.
In projects select Integration Services Project:
Drag and drop the Execute Process Task to the design pane:
In executable, specify the path of the bcp file:
C:Program FilesMicrosoft SQL ServerClient SDKODBC130ToolsBinnbcp.exe
In arguments specify the bcp arguments to export data from a SQL Server table to a text file named: ssis.bcp
adventureworks.person.person out c:sqlssis.bcp -c -T -S WIN-355CHQ0E524
Your SSIS tasks will look like this:
Right click the tasks and select Execute task:
If everything is OK, the task will be like this and a file named ssis.bcp will be created with the data exported:
Tsql Execute Sql File
7. Invoke a batch file in SSIS
Microsoft Sql Demo Database
If you are good using the command line, it is common to use SSIS to invoke a batch file that includes several commands and the bcp file. In this new example, we will export data from a SQL Server table to a file including the current date.
The following file named bcpimport.bat
The file will have the following command lines:
2 4 | set var=export%date:~-4,4%%date:~-7,2%%date:~-10,2%.bcp bcp adventureworks.person.person outc:sqlbatfile%var%.bcp-c-T-SWIN-355CHQ0E524 |
The first line will set the file name in a variable named var, including the current date with the extension bcp. For example, if today is November 6, the file name of the exported data will be batfileexport20170611.bcp.
To invoke the file in SSIS using the Execute Process task, use the following parameters:
In executable, we will invoke the cmd which is usually in the
c:WindowsSystem32cmd.exe
In arguments, we will use /c bcpimport.bat to invoke the bat file.
If everything is fine, file will be created successfully:
Conclusions
![Batch file run sql script Batch file run sql script](/uploads/1/2/6/2/126205447/586602815.jpg)
Bcp is a powerful tool to import and export data from SQL Server tables to files or vice versa. It is fast, it is also a simple tool that can be easily downloaded. It is possible to export T-SQL queries to a file using bcp. https://madisonsoha.weebly.com/intel-serial-number-identification.html.
SSIS is another alternative that can be used if you need more customized and sophisticated solutions.
We also learned to invoke bcp in PowerShell. To do that we stored parameters in PowerShell and then we used the Invoke-Expression cmdlet.
Finally, we learned how to invoke bcp in SSIS. As we explained, it is not a common practice to invoked bcp in SSIS, but it is possible to do it using the Execute Process Task. However, it is common to invoke .bat files in SSIS and they can contain calls to the bcp. We learned how to use the SSIS Execute Process task to invoke a .bat file.
Daniel Calbimonte
Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 13 years of experience working with different databases.
He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He writes SQL Server training materials for certification exams.
He also helps with translating SQLShack articles to Spanish
View all posts by Daniel Calbimonte
He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He writes SQL Server training materials for certification exams.
He also helps with translating SQLShack articles to Spanish
View all posts by Daniel Calbimonte
Latest posts by Daniel Calbimonte (see all)
Sql Server 2012 Execute Batch File
- TSQL history - September 3, 2019
- SQL Server download guide - July 22, 2019
- SQL intersect use in SQL Server - July 10, 2019