Batch Copy Process - on Sql Server & Oracle

Posted on 2013-11-11
Last Modified: 2016-02-11
I have two questions today. I hope someone can help.

I'm looking for some help on understanding what some of these arguments mean below.
i am using BCP to batch copy data from a sql server db into txt files and then from those txt files to another sql server db.

There are some commands being used on the script and i'm trying to understand them.
Can anyone please tell me what they might stand for?
Here is a snipit, the commands are at the end
-U sa

I believe the "-u sa" is username sa
I looked up the -w and i'm not sure if its logging the activity?
I believe the T argument is used in place of -p as its ran on a trusted site.
perhaps - e is exit? or is this specifying the format of the data coming out of the sql server db into this txt file?

the variable @cmd is declared as a varchar already...
The variables @db, @tbl and @path are declared already

SELECT @cmd = 'BCP "SELECT * FROM ' + rtrim( @db) + '..' + rtrim( @tbl) + ' WITH(NOLOCK)" QUERYOUT "' + rtrim( @path) + rtrim( @tbl) + '.txt" -w -S' + @@servername + ' -U sa -T -E'

The second question I have is when running this i call the master..xp_cmdshell to help export the data to the correct files.
Since i am running this on Sql Server. My question is, if i were to run this on another database type, such as Oracle, what would i use instead of the xp_cmdshell >?

is there a comparison to it on Oracle to enable BCP?

Thank you,
Question by:Putoch
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +2
LVL 29

Assisted Solution

MikeOM_DBA earned 125 total points
ID: 39639379
In Oracle the equivalent to xp_cmdshell would likely be: SQL*Plus.
You cannot run either BCP or "as is" the query you posted, but you could code and equivalent query that would execute under sqlplus.

As per the BCP parameters, refer to this link: bcp Utility
LVL 16

Assisted Solution

DcpKing earned 125 total points
ID: 39639474
In addition to what MikeOM_DBA says, have you thought of trying out SSIS? You should be able to pull data from the SQL Server system and put it straight into the Oracle system without going through text files. If you do have to go through text files, I personally find SSIS much easier to work with than BCP!


LVL 27

Assisted Solution

Zberteoc earned 125 total points
ID: 39639609
One thing though,

If you use -T switch, which means Trusted connection, then it is not compatible with the -U(user) sa switch. You should use:

SELECT @cmd = 'BCP "SELECT * FROM ' + rtrim( @db) + '..' + rtrim( @tbl) + ' WITH(NOLOCK)" QUERYOUT "' + rtrim( @path) + rtrim( @tbl) + '.txt" -w -S' + @@servername + ' -T -E'

Open in new window

Another thing is if you want to export the whole table then you can use just the name of teh table and OUT instead of QUERYOUT:

SELECT @cmd = 'BCP ' + rtrim( @db) + '..' + rtrim( @tbl) + ' OUT "' + rtrim( @path) + rtrim( @tbl) + '.txt" -w -S' + @@servername + ' -T -E'

Open in new window

BCP is a windows product and usually comes with SQL server. If you don't have SQL server on a box most likely you don't have BCP either.
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

LVL 29

Expert Comment

ID: 39639848
DcpKing's proposal to use SSIS is an excellent suggestion, you avoid many head-aches.
LVL 27

Expert Comment

ID: 39639890
"DcpKing's proposal to use SSIS is an excellent suggestion, you avoid many head-aches."

I beg to differ. When you try a simple export/import wizard in SQL >= 2005, which actually is an SSIS package that you execute instantly, you will run into varchar and nvarchar data types errors, which I expect will happen in an SSIS package as well. BCP is the simplest solution for this kind of operations and it will work with no issues.
LVL 16

Expert Comment

ID: 39640025
This looks like becoming an idealogical war, but hopefully a polite one.

When creating an SSIS package you can can indeed encounter the errors that Zberteoc refers to. These are most often associated with using Excel as an input source, as it is always treated as unicode. However, in this case you are dealing simply with a flat file, so you should encounter none of these problems.

If you decide to go directly into Oracle then you'll have to use Microsoft's OLD DB interface into Oracle, which may have problems with unicode strings, or it may not - I haven't used it enough to tell.

I'd suggest at least trying it out. Regarding the simplest data export with the wizard, you shouldn't have problems - I just tried it out with a table with an nvarchar field with no problems at all!


LVL 27

Expert Comment

ID: 39640084
There is no ideological war here just simple experience.

It happened with CSV files as well and not only that, when importing from one database table to another database table on the same server! I am talking about wizards, which is very frustrating.

It is possible that SSIS will work.

What's wrong with BCP anyway, when it comes to such a trivial thing like exporting the content of a table? Why would you use SSIS, when maybe is not even installed, or maybe using SQL EXSPRESS version? We are talking about simple stuff here and not some complex ETLs.

Author Comment

ID: 39640206
Thanks all for the advice.
in my present situation I cannot use SSIS - which was why I was going to go the BCP route.
I would prefer to work with SSIS but purely because that is what I am more comfortable using. Since you can imagine when i'm here looking for the meaning of command arguments, I have more control with SSIS and feel I can do more .

regardless, thank you all for the info above.
I will look into other ETL solutions, but open source it will have to be. Has anyone any experience with Talend?
LVL 16

Expert Comment

ID: 39640377
I didn't know you weren't allowed to use SSIS, or I wouldn't have suggested it.

BCP parameters are

-w      Uses unicode throughout
-s       There isn't an "s" switch that I know of. There is an "S" one though, so .....
-S       The Server name. Can be "server" or "machine\server"
-U sa  U is the login ID, as you might have guessed. Here it's specifying "sa".
-T        Trusted connection - i.e. your Windows login, so you don't need -U and -P
-E        SQL Server allows you to define a column as being an identity column
           (i.e. autonumber). Using this tells bcp to write the values from the incoming file
            into the identity column. Otherwise SQL Server auto-generates.

Your code looked ok, excepting that you will need a -P to specify the password for the sa user that you specified.

bcp is a program that's really part of SQL Server. So far as I am aware you can't use it on other databases (except possibly Sybase). For Oracle you might look here for an explanation:


LVL 27

Expert Comment

ID: 39641319
Again, if you use -T, trusted connection, you don't need any user and/or password.
LVL 77

Accepted Solution

slightwv (䄆 Netminder) earned 125 total points
ID: 39641528
>>My question is, if i were to run this on another database type, such as Oracle

If you have a txt file and want to get it into an Oracle database: SQL Loader or External tables.

The online docs talk all about these:

Author Comment

ID: 39652684
Thank you all for the advice.
I will continue to read these articles.
It looks like I need to change the BCP SP that I had been using to export the data out from the SQL server - as the formatting is unreadable when inserting into our Oracle db.

thank you, p

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This video shows how to recover a database from a user managed backup
Viewers will learn how the fundamental information of how to create a table.

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question