Advertisements

Archive

Archive for June 9, 2018

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: ,