Advertisements

Archive

Posts Tagged ‘Bulk Copy Program’

SQL Tips – Issues with BCP when you have two SQL Server instances

June 9, 2018 1 comment

 
I observed one thing here with BCP (Bulk Copy Program), when you have 2 versions of SQL Server installed on you PC or Server. I had SQL Server 2014 & 2016 installed on one of my DEV server.
 

So if you are executing Query from SQL 2016 instance, it was inserting records in SQL 2014 instance:

exec master..xp_cmdshell ‘BCP AdventureWorks2014.Person.Address2 IN d:\PersonAddressByQuery.txt -T -c’

 

But even if you use BCP 2016 version, it was still inserting in SQL 2014 instance:

exec master..xp_cmdshell ‘C:\”Program Files”\”Microsoft SQL Server”\”Client SDK”\ODBC\130\Tools\Binn\BCP.exe AdventureWorks2014.Person.Address2 IN d:\PersonAddressByQuery.txt -T -c’

 

On inquiring a bit I came to know that by default the BCP command points to the Default instance of SQL Server, no matter from which instance you are executing the Query. MSDN BoL

So, you have to provide parameter to connect to server. If not specified, it would go to default instance
 

So your BCP command should go with the “-S” option, like this:

exec master..xp_cmdshell ‘C:\”Program Files”\”Microsoft SQL Server”\”Client SDK”\ODBC\130\Tools\Binn\BCP.exe AdventureWorks2014.Person.Address2 IN d:\PersonAddressByQuery.txt -T -c -S DBServerName/InstanceName


Advertisements
Categories: SQL Tips Tags: ,

BCP { IN | OUT | QUERYOUT } – the BCP util in SQL Server

September 9, 2011 6 comments

As per MS BOL the BCP utility bulk copies data between an instance of SQL Server and a data file in a user-specified format. The BCP utility can be used to import large numbers of new rows into SQL Server tables or to export data out of tables into data files.

BCP or Bulk Copy Program is a command line utility that runs on DOS console. It is not a SQL statement, to run it from SQL SSMS you must use the xp_cmdshell Extended Stored Procedure as shown below.
 

Now let’s check with few examples how we can use the BCP Utility:
 

–> BCP OUT


-- Bulk Copy All Table records to a File
SELECT *
FROM Person.Address

exec master..xp_cmdshell 'BCP AdventureWorks.Person.Address OUT d:\PersonAddressAll.txt -T -c'
-- Processing status on Result Pane
NULL
Starting copy...
1000 rows successfully bulk-copied to host-file. Total received: 1000
1000 rows successfully bulk-copied to host-file. Total received: 2000
...
1000 rows successfully bulk-copied to host-file. Total received: 18000
1000 rows successfully bulk-copied to host-file. Total received: 19000
NULL
19614 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 250 Average : (78456.00 rows per sec.)
NULL

 

–> BCP QUERYOUT


-- Bulk Copy Selected (Top 20) records by using Query in BCP command from Table to a file
SELECT TOP 20 AddressID, AddressLine1, City, StateProvinceID, PostalCode, ModifiedDate
FROM Person.Address

exec master..xp_cmdshell 'BCP "SELECT TOP 20 AddressID, AddressLine1, City, StateProvinceID, PostalCode, ModifiedDate FROM AdventureWorks.Person.Address" QUERYOUT d:\PersonAddressByQuery.txt -T -c'
-- Processing status on Result Pane
NULL
Starting copy...
NULL
20 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1 Average : (20000.00 rows per sec.)
NULL

 

–> BCP IN


-- Bulk Copy All File records to an existing Table
-- Create a similar table first
IF OBJECT_ID('AdventureWorks.Person.Address2') IS NOT NULL
DROP TABLE AdventureWorks.Person.Address2

SELECT *
INTO AdventureWorks.Person.Address2
FROM AdventureWorks.Person.Address
WHERE 1=2

exec master..xp_cmdshell 'BCP AdventureWorks.Person.Address2 IN d:\PersonAddressAll.txt -T -c'

-- Check newly inserted records
SELECT *
FROM AdventureWorks.Person.Address2 -- (19614 row(s) affected)

 

Options used in above queries:
-T : Trusted connection, here username & password are not required.
-c : Performs the operation using a character data type.
 

Note: In case of no Trusted connection, you must have to use the -U & -P options, like: “-U username -P password” instead of “-T”. If “-P password” is not applied the password is asked when the command is submitted, thus it is also a secure way of entering a masked password.
If you are not on default instance than there will be need to provide the Server instance name also by adding -S option, like: “-S ServerName”.
 

>> Check & Subscribe my [YouTube videos] on SQL Server.