• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 630
  • Last Modified:

Batch Copy Process - on Sql Server & Oracle

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
Putoch
Asked:
Putoch
  • 4
  • 3
  • 2
  • +2
4 Solutions
 
MikeOM_DBACommented:
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
 
DcpKingCommented:
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
 
ZberteocCommented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
MikeOM_DBACommented:
DcpKing's proposal to use SSIS is an excellent suggestion, you avoid many head-aches.
:p
0
 
ZberteocCommented:
"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
 
DcpKingCommented:
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
 
ZberteocCommented:
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
 
PutochAuthor Commented:
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
 
DcpKingCommented:
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
 
ZberteocCommented:
Again, if you use -T, trusted connection, you don't need any user and/or password.
0
 
slightwv (䄆 Netminder) Commented:
>>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
 
PutochAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 4
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now