SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Using BCP to export
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jpost
Starting Member

43 Posts

Posted - 09/11/2012 :  14:30:15  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 09/11/2012 :  14:36:04  Show Profile  Reply with Quote
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/

Go to Top of Page

jpost
Starting Member

43 Posts

Posted - 09/11/2012 :  14:55:44  Show Profile  Reply with Quote
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?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 09/11/2012 :  15:26:25  Show Profile  Reply with Quote
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/

Go to Top of Page

jpost
Starting Member

43 Posts

Posted - 09/12/2012 :  09:55:29  Show Profile  Reply with Quote
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?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 09/12/2012 :  11:16:26  Show Profile  Reply with Quote
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/

Go to Top of Page

jpost
Starting Member

43 Posts

Posted - 09/17/2012 :  13:17:38  Show Profile  Reply with Quote
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
Go to Top of Page

jpost
Starting Member

43 Posts

Posted - 09/20/2012 :  14:22:00  Show Profile  Reply with Quote
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
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/20/2012 :  14:31:35  Show Profile  Reply with Quote
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
Go to Top of Page

xhostx
Constraint Violating Yak Guru

USA
277 Posts

Posted - 09/21/2012 :  09:18:31  Show Profile  Reply with Quote
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.


--------------------------
Joins are what RDBMS's do for a living
Go to Top of Page

jpost
Starting Member

43 Posts

Posted - 09/25/2012 :  09:00:28  Show Profile  Reply with Quote
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?
Go to Top of Page

jpost
Starting Member

43 Posts

Posted - 09/25/2012 :  09:30:25  Show Profile  Reply with Quote
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
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/25/2012 :  10:17:50  Show Profile  Reply with Quote
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.
Go to Top of Page

jpost
Starting Member

43 Posts

Posted - 09/25/2012 :  14:23:18  Show Profile  Reply with Quote
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?
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/25/2012 :  14:40:52  Show Profile  Reply with Quote
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.
Go to Top of Page

jpost
Starting Member

43 Posts

Posted - 09/26/2012 :  12:53:10  Show Profile  Reply with Quote
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.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/26/2012 :  13:21:33  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000