BCP Command

  • 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

  • Try putting your sql statement in a view and then select from the view in the BCP statement.

    MWise

  • I understand what you are saying, but not quite sure how to do so. Suggestions?

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Thanks, was just going to reply that i meant from the command line, but you covered both topics so thanks

  • 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?

  • 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

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • 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?

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply