Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
qman
Constraint Violating Yak Guru
442 Posts |
 Posted - 2009-03-24 : 13:29:12
|
| The following code comes from a job running on a windows server. This job simply queries a SQL 2005 DB and writes to a directory on the SAME server using BCP.I just found out that I need to write this data to a mainframe environment? I was just supplied a location on the mainframe to write to and a mainframe user id and password.Is it as simple as changing the queryout to point to the mainframe location along with changing the username/password?Current Windows Code -set @cmd = 'bcp "exec usp_create_company_list_for_export" queryout "\\windowsservername\c$\companydata\data_' + @today + '.csv" -U myname -P mypass -c -t"0x7C"' |
|
|
qman
Constraint Violating Yak Guru
442 Posts |
Posted - 2009-03-25 : 17:00:26
|
| Let me rephrase, is it possible to write a result set out to a file that will reside on a mainframe environment using BCP queryout and SQL Server 2005?I have a job which currently writes to location on the same windows server. I need to change the BCP logic to write out to a mainframe location rather than the windows server SQL is installed on?Has anyone ever done something similar?Any ideas? Anyone....? |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-03-25 : 17:08:50
|
| A few options for you:1. If the mainframe can be accessed via UNC paths, you should be able to bcp directly to it as long as you have write permissions2. If you can't bcp directly, you can bcp locally and use a file copy utility to copy to the mainframe3. If THAT doesn't work, and the mainframe supports FTP, you can bcp locally and then FTP the file to itI use #3 at work when dealing with an AS/400 system and some Windows domains that I can't access directly, it's fairly easy to do. Windows has a command-line ftp utility that's easy to script. |
|
|
|
qman
Constraint Violating Yak Guru
442 Posts |
Posted - 2009-03-26 : 07:49:27
|
| Hi Robvolk:Thanks for the info, I think I am going to explore option #3.Mainframe environments are not strong points.Much appreciated!Qman |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-03-26 : 12:38:10
|
| I should also add you can bcp to a folder on your local FTP server, and have the mainframe do an FTP get. |
|
|
|
qman
Constraint Violating Yak Guru
442 Posts |
Posted - 2009-03-26 : 12:48:01
|
| Hi Robvolk:I was able to write a simple FTP script which grabs the file written from the BCP/SQL Job and sends to the mainframe.Works for me, thanks for your help! |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-03-26 : 12:57:14
|
| Cool, glad I could help. |
|
|
|
|
|
|
Write to a mainframe environment using bcp?