Help with sqlcmd or bcp and data only

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with sqlcmd or bcp and data only

Author

Topic

ejaggers
Starting Member

16 Posts

Posted - 2012-02-16 : 11:10:40

I'm using the following command to create a data file, and it works fine. But it puts the number of rows affected at the bottom, and a extra blank line.
How do i get data only?

sqlcmd -E -d database -S svc1 -i C:\cmd.sql -o c:\outfile.txt -s "|" -h -1 -W

c:\outfile.txt:

888888|JOHN|DOE|THOMAS|02|124|YOUNG, SMITH|
999999|RABBIT|MARIE|WHITE|02|124|YOUNG, SMITH|
777777|DEER||VARGAS|KG|8888|RIOS, SMITH|

(36312 rows affected)

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-16 : 11:35:55

Add

SET NOCOUNT OFF

to your input Script file

Why don't you post the script?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

ejaggers
Starting Member

16 Posts

Posted - 2012-02-16 : 12:59:57

Brett,

I did post the script except I dummied out the db and server name. It is this:

sqlcmd -E -d database -S svc1 -i C:\cmd.sql -o c:\outfile.txt -s "|" -h -1 -W

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-16 : 13:07:35

No, what's in HERE is the script

C:\cmd.sql

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

ejaggers
Starting Member

16 Posts

Posted - 2012-02-16 : 13:38:01

Ooooh!!! Okay:

select distinct
rtrim(reg.student_id) AS student_id, UPPER(reg.first_name) AS first_name,

CASE
WHEN reg.middle_name IS NULL THEN ''
ELSE UPPER(reg.middle_name)
END AS middle_name,

UPPER(reg.last_name) AS last_name, reg.grade, reg.building,

UPPER(reg_staff_bldgs.staff_name) AS staff_name,

CASE
WHEN reg.middle_name IS NULL THEN ''
ELSE ''
END AS Row_Delimeter

from reg
join reg_district on reg_district.district = reg.district

join schd_stu_course on schd_stu_course.student_id = reg.student_id
and schd_stu_course.course_status in ('A', 'L')

join schd_ms_mp on schd_ms_mp.section_key = schd_stu_course.section_key

join schd_ms_session on schd_ms_session.section_key = schd_stu_course.section_key
and ( schd_ms_session.start_period = '4' and reg.building = 5
OR schd_ms_session.start_period = '2' and reg.building != 5 )

join schd_ms on schd_ms.section_key = schd_stu_course.section_key
and schd_ms.School_year = reg_district.School_year

join reg_staff_bldgs on reg_staff_bldgs.staff_id = schd_ms_session.primary_staff_id
and reg_staff_bldgs.building between 2 and 150

join reg_staff on reg_staff.staff_id = reg_staff_bldgs.staff_id
order by reg.building,staff_name,reg.grade,reg.last_name,reg.first_name

----------------------------

They want a '|' at the end of each row, so that's what this is for:

CASE
WHEN reg.middle_name IS NULL THEN ''
ELSE ''
END AS Row_Delimeter

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-16 : 13:51:46

Why do they want an Ending Pipe? That will make it look like an additional Column when there isn't one...oil well



SET NOCOUNT ON
GO

   SELECT DISTINCT
		  RTRIM(reg.student_id)												AS student_id
		, UPPER(reg.first_name)												AS first_name
		, CASE WHEN reg.middle_name IS NULL THEN '' 
						    ELSE UPPER(reg.middle_name)
		   END																AS middle_name
		, UPPER(reg.last_name) AS last_name
                , reg.grade
		, reg.building														AS Building
		, UPPER(reg_staff_bldgs.staff_name)									AS staff_name
		, CASE WHEN reg.middle_name IS NULL THEN '' 
											ELSE ''
		   END																AS Row_Delimeter 
	  FROM reg
INNER JOIN reg_district 
		ON reg_district.district			= reg.district
INNER JOIN schd_stu_course 
		ON schd_stu_course.student_id		= reg.student_id
	   AND schd_stu_course.course_status	in ('A', 'L')
INNER JOIN schd_ms_mp 
		ON schd_ms_mp.section_key			= schd_stu_course.section_key 
INNER JOIN schd_ms_session 
		ON schd_ms_session.section_key		= schd_stu_course.section_key
	   AND ( schd_ms_session.start_period	= '4' AND reg.building = 5 
		OR schd_ms_session.start_period		= '2' AND reg.building != 5 ) 
INNER JOIN schd_ms 
		ON schd_ms.section_key				= schd_stu_course.section_key
	   AND schd_ms.School_year				= reg_district.School_year 
INNER JOIN reg_staff_bldgs 
		ON reg_staff_bldgs.staff_id			= schd_ms_session.primary_staff_id
	   AND reg_staff_bldgs.building between 2 AND 150
INNER JOIN reg_staff 
		ON reg_staff.staff_id = reg_staff_bldgs.staff_id 
  ORDER BY reg.building,staff_name,reg.grade,reg.last_name,reg.first_name
GO

SET NOCOUNT OFF
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

ejaggers
Starting Member

16 Posts

Posted - 2012-02-16 : 16:21:22

Brett,

Quote: Why do they want an Ending Pipe?

Because originally the file came from UNIX/Informix and had the '|' at the end, and that's what the app is expecting.

I put those stmts in cmd.sql but still got the rows affected and blank line. If I can't unload this table with sqlcmd or bcp,
I'll have to use SSIS, and I got 3 words for that; Lordy, Lordy, Lordy!!!

SSIS is not for a rookie, in fact it seems to be the art of making simple things difficult.

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-16 : 19:25:40

get out...SSIS must be easy..I don't like for my own personal reasons

But come on...maybe take out the GO

And if you still have a problem, we can create a view and bcp that out..have no fear...

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

ejaggers
Starting Member

16 Posts

Posted - 2012-02-17 : 12:39:42

Brett,

Go / No Go, doesn't work. Have you got anymore suggestions???

quote: create a view and bcp that out..have no fear...

Won't that still put rows affected at the end (using bcp)??

I'm using sqlcmd, but how do I use bcp?

sqlcmd -E -d database -S svc1 -i C:\cmd.sql -o c:\outfile.txt -s "|" -h -1 -W

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-17 : 12:56:49

no

bcp will just put out data

And I'm having a hard time believing that NOCOUNT didn't work

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

ejaggers
Starting Member

16 Posts

Posted - 2012-02-17 : 14:18:58

can you give me an example using bcp, cmd.sql as input, a '|' = delimiter? Maybe it works better than sqlcmd.

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-17 : 14:38:44

Create a view


CREATE VIEW myView99
AS
   SELECT DISTINCT
		  RTRIM(reg.student_id)												AS student_id
		, UPPER(reg.first_name)												AS first_name
		, CASE WHEN reg.middle_name IS NULL THEN '' 
						    ELSE UPPER(reg.middle_name)
		   END																AS middle_name
		, UPPER(reg.last_name) AS last_name
                , reg.grade
		, reg.building														AS Building
		, UPPER(reg_staff_bldgs.staff_name)									AS staff_name
		, CASE WHEN reg.middle_name IS NULL THEN '' 
											ELSE ''
		   END																AS Row_Delimeter 
	  FROM reg
INNER JOIN reg_district 
		ON reg_district.district			= reg.district
INNER JOIN schd_stu_course 
		ON schd_stu_course.student_id		= reg.student_id
	   AND schd_stu_course.course_status	in ('A', 'L')
INNER JOIN schd_ms_mp 
		ON schd_ms_mp.section_key			= schd_stu_course.section_key 
INNER JOIN schd_ms_session 
		ON schd_ms_session.section_key		= schd_stu_course.section_key
	   AND ( schd_ms_session.start_period	= '4' AND reg.building = 5 
		OR schd_ms_session.start_period		= '2' AND reg.building != 5 ) 
INNER JOIN schd_ms 
		ON schd_ms.section_key				= schd_stu_course.section_key
	   AND schd_ms.School_year				= reg_district.School_year 
INNER JOIN reg_staff_bldgs 
		ON reg_staff_bldgs.staff_id			= schd_ms_session.primary_staff_id
	   AND reg_staff_bldgs.building between 2 AND 150
INNER JOIN reg_staff 
		ON reg_staff.staff_id = reg_staff_bldgs.staff_id

ejaggers
Starting Member

16 Posts

Posted - 2012-02-21 : 13:26:05

Brett,
Humbly apologize and yield to your wisdom. The “SET NOCOUNT ON” did work
after all. I don’t know what I did different the other day. Thanks A Lot!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources