SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


BCP Command


BCP Command

Author
Message
justin_post
justin_post
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
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
MWise
MWise
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1044 Visits: 1804
Try putting your sql statement in a view and then select from the view in the BCP statement.

MWise



justin_post
justin_post
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 7
I understand what you are saying, but not quite sure how to do so. Suggestions?
Lowell
Lowell
SSC Guru
SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)

Group: General Forum Members
Points: 70952 Visits: 40924
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!
justin_post
justin_post
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 7
Thanks, was just going to reply that i meant from the command line, but you covered both topics so thanks
justin_post
justin_post
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
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?
menon.satyen
menon.satyen
SSC Journeyman
SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)

Group: General Forum Members
Points: 83 Visits: 230
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
Lowell
Lowell
SSC Guru
SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)

Group: General Forum Members
Points: 70952 Visits: 40924
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!
justin_post
justin_post
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
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?
Lowell
Lowell
SSC Guru
SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)

Group: General Forum Members
Points: 70952 Visits: 40924
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!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search