Using BCP to export - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Administration

Using BCP to export

Author

Topic

jpost
Starting Member

43 Posts

Posted - 2012-09-11 : 14:30:15

I am new to using bcp and am having a few issues. I want to export the results of a query that I wrote, so I can use task scheduler to ftp the results to an offsite provider. How would I do that? I know that you use command line, but everything I have found just exports tables and not results. The result I want to export is called Info and I would like to save it as tab delimited. Any suggestions would be great, I am very new to sql server administration.
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-11 : 14:36:04

you can use bcp with queryout option to export results of query using bcp

http://sqlfool.com/2008/12/bcp-basics/

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jpost
Starting Member

43 Posts

Posted - 2012-09-11 : 14:55:44

Can you provide an example of what the code would look like. Every time I use something I find and manipulate it to what I need it doesn't work. Is there also a way to save the command so I can automate it using task scheduler?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-11 : 15:26:25

the posted link has example of queryout. did you have a look at that?

for saving command you can save it as a batch file and call it from task scheduler

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jpost
Starting Member

43 Posts

Posted - 2012-09-12 : 09:55:29

The link is very useful thanks, but I do have one last question. Can you call a query from a saved project file or do you have to write the entire script on the actual command line. When I try to copy and apst it runs it with errors and doesn't let me finish?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-12 : 11:16:26

task scheduler can call and execute a batch file (.bat) inside which you can include your command line commands to execute bcp

another way is to use T-SQL script to execute bcp using xp_cmdshell proc and save it as .sql file

then you can use osql utility to execute the .sql file

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jpost
Starting Member

43 Posts

Posted - 2012-09-17 : 13:17:38

This is what my bat file looks like, but doesn't work properly:
echo off

bcp pickeringtoncmb "select [Student Number], IRN, Name, Gender, Address, city, state, zip, phone, birthdate, Grade, Homeroom, Teacher From
(select distinct
left(isnull(s.studentNumber,'') + space(9),9) as 'Student Number',
case left (isnull (sc.number,'') + SPACE (6),6)
when 5101 then '029876'
when 5102 then '145086'
when 5111 then '112581'
when 5112 then '145078'
when 5121 then '138966'
when 5122 then '138958'
when 5123 then '011369'
when 5131 then '086579'
when 5135 then '142638'
when 5132 then '029868'
when 5133 then '065433'
when 5137 then '011368'
when 5136 then '011370'
when 5134 then '123307'
end as 'IRN',
left(isnull(s.lastName + ', ' + s.firstName,'') + space(36),36) as 'Name',
left(isnull(s.gender,'') + space(1),1) as 'Gender',
left(isnull(a.number + ' ' + a.street + ' ' + a.tag ,'') + space(51),51)as 'Address',
left(isnull(a.city,'') + space(18),18) as 'City',
left(isnull(a.state,'') + space(4),4)as 'State',
left(isnull(a.zip,'') + space(9),9)as 'Zip',
left(isnull(hh.phone,'') + space(14),14) as 'Phone',
convert(varchar,s.birthdate,111) as 'birthDate',
left(isnull(s.grade,'') + space(6),6) as 'Grade',
left(isnull(se.teacherdisplay,'') + space(5),5) as 'Homeroom',
left(isnull(se.teacherdisplay,'') + space(26),26) as 'Teacher',
ROW_NUMBER() OVER (PARTITION BY s.studentnumber ORDER BY s.studentnumber) AS RN
From student s
INNER JOIN school sc on sc.schoolID = s.schoolID
INNER JOIN HouseholdMember hm ON hm.personID = s.personID
INNER JOIN Household hh ON hh.householdID = hm.householdID
INNER JOIN HouseholdLocation hl ON hl.householdID = hh.householdID
INNER JOIN Address a ON a.addressID = hl.addressID
JOIN Roster r ON r.personID = s.personID
JOIN Section se ON se.sectionID = r.sectionID
JOIN SectionPlacement sp ON sp.sectionID = se.sectionID
JOIN Period p ON p.periodID = sp.periodID
JOIN Course c ON c.courseID = se.courseID
JOIN Calendar ca ON ca.calendarID = c.calendarID AND ca.calendarID = s.calendarID and sc.schoolID = ca.schoolID
JOIN SchoolYear sy ON sy.endYear = ca.endYear
where s.calendarID in (8,12,16,20,24,28,32,36,40,44,48,52,56,60) and s.startYear = '2012'
and ((( p.seq ='2') or (c.homeroom = '1'))))s
where RN = 1" query out C:\users\jpost\desktop\MEC\Student_Info.txt -c -T

Any suggestions? This is all on a local machine.
Thanks

jpost
Starting Member

43 Posts

Posted - 2012-09-20 : 14:22:00

So I am trying to narrow down my issues and still get errors when I try the following:

bcp pickeringtoncmb "select * from pickeringtoncmb.pickerington.student" queryout C:\User\jpost\desktop\Student_Info.txt -c-T

PickeringtonCMB is the name of the machine that holds the databases
Pickerington is the database I want to use
Student is the table I want to pull the query from.
Suggestions?
Thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-20 : 14:31:35

The naming convention you are using is not consistent with what you described. The naming convention is servername.databasename.schemaname.tablename. So your select query should be this (assuming the schema is the default schema dbo)

bcp "select * from Pickerington.dbo.student" queryout C:\User\jpost\desktop\Student_Info.txt -S pickeringtoncmb  -c -T

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-09-21 : 09:18:31

[code]Just want to add something!
I'm not sure "Queryout" Keyword, try just "out" instead.
Also, make sure your database allow you to loggin with a switch -T, instead. I would use -Udbname and -Pdbpassword. [/code]

--------------------------
Joins are what RDBMS's do for a living

jpost
Starting Member

43 Posts

Posted - 2012-09-25 : 09:00:28

I just tried the last suggested command and still got an error. Here is what I wrote:
bcp"select * from pickerington.dbo.student" out C:\User\jpost\desktop\Student_Info.txt -S pickeringtoncmb -c -jpost -mypassword.

It fails with an error. Not sure why I can't get this to work?

jpost
Starting Member

43 Posts

Posted - 2012-09-25 : 09:30:25

I think I got part of the issue resolved, it prompts me for a password, but when I use the same on that I use to log into the machine I get an error saying login failed for user. If I use the -T I get the error that says unable to open BCP host datafile

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-25 : 10:17:50

That sounds like you are able to connect using Windows security, but not able to connect using the username and password. From a command window, see if you can run any of these commands successfully. This is just running a simple query against the database using windows security and sql security.


sqlcmd -Q "SELECT GETDATE();" -S pickeringtoncmb -E
sqlcmd -Q "SELECT GETDATE();" -S pickeringtoncmb -U yourusername -P yourpassword

This is not anything new, I am just trying to eliminate the possibility that it is inability to access the server.

jpost
Starting Member

43 Posts

Posted - 2012-09-25 : 14:23:18

The first line worked, but the second one failed. Login failed for user jpost. Again I am using the same password that I use to login to my account from the login screen?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-25 : 14:40:52

quote:
Originally posted by jpost

The first line worked, but the second one failed. Login failed for user jpost. Again I am using the same password that I use to login to my account from the login screen?

By that did you mean the same user name and password that you use to login to Windows? SQL login would not be the same. Regardless, since the first command worked correctly you are able to access SQL Server using windows authentication.

Can you try the following:

1. Launch SQL Server Management Studio, connect to the database server using Windows authentication. Once you are able to connect, run this query and see if you get any data.

select * from Pickerington.dbo.student

2. If the previous step was successful, run this from a command window and if that generates an error, post the exact error message.

bcp "select * from Pickerington.dbo.student" queryout C:\User\jpost\desktop\Student_Info.txt -S pickeringtoncmb  -c -T

Before you run this command, verify that the directory C:\user\jpost\desktop exists.

jpost
Starting Member

43 Posts

Posted - 2012-09-26 : 12:53:10

I could run the first command inside of Management Studio. When I open the command line and ran the command I received the following error:SQLState = S1000, NativeError=0
Error =[Microsoft][SQL Server Native Client 10.0] unable to open BCP host data-file

I even changed where I saved it to the documents folder instead and still got the same error.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-26 : 13:21:33

I am not familiar with that specific error message, but can you try to run it without specifying any path names at all.

bcp "select * from Pickerington.dbo.student" queryout Student_Info.txt -S pickeringtoncmb  -c -T

That should create the file in the same folder where you are running the command from.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources