October 30, 2012 at 11:53 am
I am fairly new to SQL have been trying to automate a task that I currently run manually. I am trying to create a bcp command so I can export my data. I get an error of : SQl Server native client 10.0 unable to open bcp host file. The script that I have is fairly long and if I write something simple like bcp "select * from pickerington.dbo.students" queryout "C:\User\jpost\Info\Student_Info.txt" -S pickeringtoncmb -c -T it works fine. Its when I replace the script part and try to run it that I get the error. Below is the command that I am trying to use. Any insight would be great:
bcp "select [Student Number], IRN, Name, Gender, Address, city, state, zip, phone, birthdate, Grade, Lastname4 + first as '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 (4),4) AS Lastname4,LEFT(LTRIM(STUFF(se.teacherdisplay,1,CHARINDEX(',',se.teacherdisplay),'')),1)as 'First',left(isnull(se.teacherdisplay,'') + space(26),26) as 'Teacher',ROW_NUMBER() OVER(PARTITION BY s.studentnumber ORDER BY s.studentnumber) AS RN From pickerington.dbo.student s INNER JOIN pickerington.dbo.school sc on sc.schoolID = s.schoolID Left JOIN pickerington.dbo.HouseholdMember hm ON hm.personID = s.personID Left JOIN pickerington.dbo.Household hh ON hh.householdID = hm.householdID Left JOIN pickerington.dbo.HouseholdLocation hl ON hl.householdID = hh.householdID Left JOIN pickerington.dbo.Address a ON a.addressID = hl.addressID JOIN pickerington.dbo.Roster r ON r.personID = s.personID JOIN pickerington.dbo.Section se ON se.sectionID = r.sectionID JOIN pickerington.dbo.SectionPlacement sp ON sp.sectionID = se.sectionID JOIN pickerington.dbo.Period p ON p.periodID = sp.periodID JOIN pickerington.dbo.Course c ON c.courseID = se.courseID JOIN pickerington.dbo.Calendar ca ON ca.calendarID = c.calendarID AND ca.calendarID = s.calendarID and sc.schoolID = ca.schoolID JOIN pickerington.dbo.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 s.endDate is null and (((sc.number in (5101,5102,5111,5112) and p.seq = 2 ) or (sc.number in (5121,5122,5123,5131,5132,5133,5134,5135,5136,5137) and c.homeroom = '1' or p.seq = 1))))s where rn = 1" queryout "C:\User\jpost\Info\Student_Info.txt" -S pickeringtoncmb -c -T
October 30, 2012 at 12:00 pm
Try putting your sql statement in a view and then select from the view in the BCP statement.
MWise
October 30, 2012 at 12:13 pm
I understand what you are saying, but not quite sure how to do so. Suggestions?
October 30, 2012 at 12:22 pm
He means do something like this:
create the view via TSQL, for example:
CREATE VIEW MyView
AS
SELECT
[Student Number],
IRN,
Name,
Gender,
Address,
city,
state,
zip,
phone,
birthdate,
Grade,
Lastname4 + first AS '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 (4), 4) AS Lastname4,
LEFT(LTRIM(STUFF(se.teacherdisplay, 1, CHARINDEX(',', se.teacherdisplay), '')), 1)AS 'First',
LEFT(ISNULL(se.teacherdisplay, '') + SPACE(26), 26) AS 'Teacher',
ROW_NUMBER() OVER(PARTITION BY s.studentnumber ORDER BY s.studentnumber) AS RN
FROM pickerington.dbo.student s
INNER JOIN pickerington.dbo.school sc
ON sc.schoolID = s.schoolID
LEFT JOIN pickerington.dbo.HouseholdMember hm
ON hm.personID = s.personID
LEFT JOIN pickerington.dbo.Household hh
ON hh.householdID = hm.householdID
LEFT JOIN pickerington.dbo.HouseholdLocation hl
ON hl.householdID = hh.householdID
LEFT JOIN pickerington.dbo.Address a
ON a.addressID = hl.addressID
JOIN pickerington.dbo.Roster r
ON r.personID = s.personID
JOIN pickerington.dbo.Section se
ON se.sectionID = r.sectionID
JOIN pickerington.dbo.SectionPlacement sp
ON sp.sectionID = se.sectionID
JOIN pickerington.dbo.Period p
ON p.periodID = sp.periodID
JOIN pickerington.dbo.Course c
ON c.courseID = se.courseID
JOIN pickerington.dbo.Calendar ca
ON ca.calendarID = c.calendarID
AND ca.calendarID = s.calendarID
AND sc.schoolID = ca.schoolID
JOIN pickerington.dbo.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 s.endDate IS NULL
AND (( ( sc.number IN ( 5101, 5102, 5111, 5112 )
AND p.seq = 2 )
OR ( sc.number IN ( 5121, 5122, 5123, 5131,
5132, 5133, 5134, 5135,
5136, 5137 )
AND c.homeroom = '1'
OR p.seq = 1 ) )))s
WHERE rn = 1
then your query will not hit the size limit of a command in the cmd window, because your bcp command changes to this:
bcp "SELECT * FROM DatabaseName.dbo.MyView" queryout "C:\User\jpost\Info\Student_Info.txt" -S pickeringtoncmb -c -T
Lowell
October 30, 2012 at 12:24 pm
Thanks, was just going to reply that i meant from the command line, but you covered both topics so thanks
October 30, 2012 at 12:25 pm
One more thing, with the data constantly changing, will I need to create a view each day or how would I make sure that the data stays up to date?
October 30, 2012 at 12:29 pm
Hi,
I guess Old Hand suggested the following.
1. create view viewtest as
select [Student Number], IRN, Name, Gender, Address, city, state, zip, phone, birthdate, Grade, Lastname4 + first as '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 (4),4) AS Lastname4,LEFT(LTRIM(STUFF(se.teacherdisplay,1,CHARINDEX(',',se.teacherdisplay),'')),1)as 'First',left(isnull(se.teacherdisplay,'') + space(26),26) as 'Teacher',ROW_NUMBER() OVER(PARTITION BY s.studentnumber ORDER BY s.studentnumber) AS RN From pickerington.dbo.student s INNER JOIN pickerington.dbo.school sc on sc.schoolID = s.schoolID Left JOIN pickerington.dbo.HouseholdMember hm ON hm.personID = s.personID Left JOIN pickerington.dbo.Household hh ON hh.householdID = hm.householdID Left JOIN pickerington.dbo.HouseholdLocation hl ON hl.householdID = hh.householdID Left JOIN pickerington.dbo.Address a ON a.addressID = hl.addressID JOIN pickerington.dbo.Roster r ON r.personID = s.personID JOIN pickerington.dbo.Section se ON se.sectionID = r.sectionID JOIN pickerington.dbo.SectionPlacement sp ON sp.sectionID = se.sectionID JOIN pickerington.dbo.Period p ON p.periodID = sp.periodID JOIN pickerington.dbo.Course c ON c.courseID = se.courseID JOIN pickerington.dbo.Calendar ca ON ca.calendarID = c.calendarID AND ca.calendarID = s.calendarID and sc.schoolID = ca.schoolID JOIN pickerington.dbo.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 s.endDate is null and (((sc.number in (5101,5102,5111,5112) and p.seq = 2 ) or (sc.number in (5121,5122,5123,5131,5132,5133,5134,5135,5136,5137) and c.homeroom = '1' or p.seq = 1))))s where rn = 1"
2. bcp "select * from pickerington.dbo.viewtest" queryout "C:\User\jpost\Info\Student_Info.txt" -S pickeringtoncmb -c -T
October 30, 2012 at 12:31 pm
justin_post (10/30/2012)
One more thing, with the data constantly changing, will I need to create a view each day or how would I make sure that the data stays up to date?
not sure; i don't see a "date" changing perse;, maybe you mean that this portion (the WHERE statement?) is dynamic?
WHERE s.calendarID IN ( 8, 12, 16, 20,
24, 28, 32, 36,
40, 44, 48, 52,
56, 60 )
if that's the part that's changing, it would come OUT of the view, and INTO the bcp statement.
so the bcp changes to something like :
bcp "SELECT * FROM DatabaseName.dbo.MyView WHERE s.calendarID IN ( 8, 12, 16, 20,24, 28, 32, 36,40, 44, 48, 52,56, 60 )" queryout "C:\User\jpost\Info\Student_Info.txt" -S pickeringtoncmb -c -T
Lowell
October 30, 2012 at 1:08 pm
On the front end, new students get added regularly. If I used the view I created today, on Friday, it wouldn't contain any new students that had been added between now and then. I guess the easiest thing to do is maybe drop and then re-create the view daily?
October 30, 2012 at 1:17 pm
justin_post (10/30/2012)
On the front end, new students get added regularly. If I used the view I created today, on Friday, it wouldn't contain any new students that had been added between now and then. I guess the easiest thing to do is maybe drop and then re-create the view daily?
either way is going to work, whether you rebuild the view to match your WHERE, or build a WHERE tomatch the selection criteria you need.
i assume the AND sc.number in (5101,5102,5111,5112) in the WHERE predicates is what you mean by "student numbers always changing".
if that's what changes, it doesn't belong in the view, it belongs in a query of the view.
goes back to the view definition again.
It might be better to simply create a view that selects from the core tables, that has the data (without WHERE filtering) so it is always correct.
then change you bcp process to build a WHERE statement to append a WHERE statement, but it might get so long you hit the max size of a cmd again.
dropping and recreating might work best for you.
Lowell
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply