?
Solved

Batch Copy Process - on Sql Server & Oracle

Posted on 2013-11-11
12
Medium Priority
?
604 Views
Last Modified: 2016-02-11
Hello,
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
-w
-s
-U sa
-T
-E

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,
P
0
Comment
Question by:Putoch
[X]
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
12 Comments
 
LVL 29

Assisted Solution

by:MikeOM_DBA
MikeOM_DBA earned 375 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
0
 
LVL 16

Assisted Solution

by:DcpKing
DcpKing earned 375 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!

hth

Mike
0
 
LVL 27

Assisted Solution

by:Zberteoc
Zberteoc earned 375 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.
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 29

Expert Comment

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

Expert Comment

by:Zberteoc
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.
0
 
LVL 16

Expert Comment

by:DcpKing
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!

hth

Mike
0
 
LVL 27

Expert Comment

by:Zberteoc
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.
0
 

Author Comment

by:Putoch
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?
0
 
LVL 16

Expert Comment

by:DcpKing
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: http://www.oracle-developer.net/display.php?id=201

hth

Mike
0
 
LVL 27

Expert Comment

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

Accepted Solution

by:
slightwv (䄆 Netminder) earned 375 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:
http://www.oracle.com/pls/db112/homepage
0
 

Author Comment

by:Putoch
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
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

719 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