| Author |
 Topic  |
|
|
jpost
Starting Member
43 Posts |
 Posted - 09/11/2012 : 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
India
47099 Posts |
|
|
jpost
Starting Member
43 Posts |
Posted - 09/11/2012 : 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
India
47099 Posts |
Posted - 09/11/2012 : 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 - 09/12/2012 : 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
India
47099 Posts |
Posted - 09/12/2012 : 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 - 09/17/2012 : 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 - 09/20/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 09/20/2012 : 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
USA
261 Posts |
Posted - 09/21/2012 : 09:18:31
|
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 |
|
|
|
jpost
Starting Member
43 Posts |
Posted - 09/25/2012 : 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 - 09/25/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 09/25/2012 : 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 - 09/25/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 09/25/2012 : 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 - 09/26/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 09/26/2012 : 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. |
|
|
| |
Topic |
|
Using BCP to export
