Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

BCP Command Expand / Collapse
Author
Message
Posted Tuesday, October 30, 2012 11:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 1, 2013 9:30 AM
Points: 8, Visits: 7
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
Post #1378933
Posted Tuesday, October 30, 2012 12:00 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 10:22 AM
Points: 394, Visits: 1,604
Try putting your sql statement in a view and then select from the view in the BCP statement.

MWise



Post #1378935
Posted Tuesday, October 30, 2012 12:13 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 1, 2013 9:30 AM
Points: 8, Visits: 7
I understand what you are saying, but not quite sure how to do so. Suggestions?
Post #1378943
Posted Tuesday, October 30, 2012 12:22 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:44 AM
Points: 12,905, Visits: 32,158
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1378950
Posted Tuesday, October 30, 2012 12:24 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 1, 2013 9:30 AM
Points: 8, Visits: 7
Thanks, was just going to reply that i meant from the command line, but you covered both topics so thanks
Post #1378951
Posted Tuesday, October 30, 2012 12:25 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 1, 2013 9:30 AM
Points: 8, Visits: 7
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?
Post #1378952
Posted Tuesday, October 30, 2012 12:29 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 9, 2014 8:40 AM
Points: 25, Visits: 149
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
Post #1378955
Posted Tuesday, October 30, 2012 12:31 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:44 AM
Points: 12,905, Visits: 32,158
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1378956
Posted Tuesday, October 30, 2012 1:08 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 1, 2013 9:30 AM
Points: 8, Visits: 7
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?
Post #1378981
Posted Tuesday, October 30, 2012 1:17 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:44 AM
Points: 12,905, Visits: 32,158
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1378989
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse