BCP utility - SQL Server Forums

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

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

BCP utility

Author

Topic

ramecepa
Starting Member

43 Posts

Posted - 2010-11-08 : 03:57:55

How can we export data from stored procedure to text file using BCP command

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-08 : 03:59:16

This will give you an idea:

http://www.sqlteam.com/search.aspx?cx=011171816663894899992%3Aaow51lf_dim&cof=FORID%3A9&q=export+data+from+stored+procedure+to+text+file+using+BCP+command&sa=%C2%A0Go%C2%A0#1420

No, you're never too old to Yak'n'Roll if you're too young to die.

ramecepa
Starting Member

43 Posts

Posted - 2010-11-08 : 04:07:02

i want to known..how to export from data from stored procedure to txt file

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-08 : 04:18:48

Yes. What is wrong with the above given link? There are tons of solutions for that.
Or would you prefer to have an answer exactly for your needs to copy and paste?
In this case you might give more info and show what you have done so far...

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-08 : 05:40:07

http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/bcp-export-data-to-text-file.aspx

Madhivanan

Failing to plan is Planning to fail

ramecepa
Starting Member

43 Posts

Posted - 2010-11-08 : 22:36:57

I have created one stored proc as below...
create procedure cif_view
as
declare c1 cursor for
select maintbrn,hp_flag from cifdata
declare @cmthdate as varchar(20)
declare @hp_flag as varchar(20)
open c1;
fetch next from c1
into @cmthdate,@hp_flag;
while @@fetch_status =0
begin
print 'Branch:'+@cmthdate + ' '+'Hp_flag:'+@hp_flag
fetch next from c1 into @cmthdate,@hp_flag;
end;
close c1;
deallocate c1;

i want to load above stored procedure data into txt file

Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-09 : 04:38:42

You dont need a cursor for this. Do it in single query as shown above link

Madhivanan

Failing to plan is Planning to fail

ramecepa
Starting Member

43 Posts

Posted - 2010-11-09 : 22:25:23

This is my orignal SP
CREATE procedure cif_view
as
declare c1 cursor for
select cif_no,userid,title,salutatn,status,idno,oldidno,idcode,hphone,bphone,creadate,lmaint,origbrn,maintbrn,race,custtype,bumi,sex,marital,religion,citizen,country,occuptn,vip,bnkcd,employer,remarks from cifdata where error='Y'
declare @cif_no as varchar(20)
declare @userid as varchar(20)
declare @title as varchar(20)
declare @salutatn as varchar(20)
declare @status as varchar(20)
declare @idno as varchar(20)
declare @oldidno as varchar(20)
declare @idcode as varchar(20)
declare @hphone as varchar(20)
declare @bphone as varchar(20)
declare @creadate as varchar(20)
declare @lmaint as varchar(20)
declare @origbrn as varchar(20)
declare @maintbrn as varchar(20)
declare @race as varchar(20)
declare @custtype as varchar(20)
declare @bumi as varchar(20)
declare @sex as varchar(20)
declare @marital as varchar(20)
declare @religion as varchar(20)
declare @citizen as varchar(20)
declare @country as varchar(20)
declare @occuptn as varchar(20)
declare @vip as varchar(20)
declare @employer as varchar(20)
declare @remarks as varchar(20)
declare @bnkcd as varchar(20)

open c1;
fetch next from c1
into @cif_no,@userid,@title,@salutatn,@status,@idno,@oldidno,@idcode,@hphone,@bphone,@creadate,@lmaint,@origbrn,@maintbrn,@race,@custtype,@bumi,@sex,@marital,@religion,@citizen,@country,@occuptn,@vip,@employer,@remarks,@bnkcd ;
while @@fetch_status =0
begin
print 'CIF No:' +@cif_no + 'User ID:'+ @userid
print 'Title:'+ @title + 'Salutation:' + @salutatn + 'Status:'+ @status
print 'ID No:'+ @idno +'Old ID No:' +@oldidno + 'ID Code:' +@idcode
print 'Hphone:' +@hphone +'Busn_phone:' + @bphone
print 'Create Date:' + @creadate + 'Maint Date:' + @lmaint
print 'Codes:' + 'Home Branch:' + @origbrn + 'Maint Branch:' +@maintbrn
print 'Race:' +@race + 'Cust Type:' +@custtype + 'Bumi:' +@bumi
print 'Sex:' +@sex + 'MaritalStatus:' +@marital + 'Religion:' +@religion + 'Citizen:' +@citizen
print 'Country:' +@country + 'Occupation:' +@occuptn + 'VIP Code:' +@vip + 'Bank Code:' +@bnkcd
print 'Employer:' +@employer
print 'Remark:' +@remarks
print ' ';
fetch next from c1 into @cif_no,@userid,@title,@salutatn,@status,@idno,@oldidno,@idcode,@hphone,@bphone,@creadate,@lmaint,@origbrn,@maintbrn,@race,@custtype,@bumi,@sex,@marital,@religion,@citizen,@country,@occuptn,@vip,@employer,@remarks,@bnkcd ;
end;
close c1;
deallocate c1;

exec cif_view

GO

is it possiable to write as one query??

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-11-10 : 02:28:45

quote:
is it possiable to write as one query??

Yes. Just delete all the lines except the "select cif_no,userid...."


CREATE procedure cif_view
as
select cif_no,userid,title,salutatn,. . . .

KH
[spoiler]Time is always against us[/spoiler]

ramecepa
Starting Member

43 Posts

Posted - 2010-11-10 : 19:55:51

i want to genrate report like above one

ramecepa
Starting Member

43 Posts

Posted - 2010-11-10 : 20:03:10

Using BCp...how can we load stored procedure result into txt file

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-11-10 : 20:28:12

use QUERYOUT

BCP QUERYOUT "exec cif_view" . . . .

refer to BOL on BCP : [url]http://msdn.microsoft.com/en-us/library/aa174646%28SQL.80%29.aspx[/url]

You can refer to BCP example here. It is on SQL 2008 but the BCP util hasn't change much from SQL 2000. The basic IN / OUT / QUERYOUT syntax still valid
http://msdn.microsoft.com/en-us/library/ms162802.aspx

KH
[spoiler]Time is always against us[/spoiler]

X002548
Not Just a Number

15586 Posts

Posted - 2010-11-11 : 13:30:15

[code]
CREATE VIEW myView99
AS
SELECT
'CIF No:' +cif_no + 'User ID:'+ userid + CHAR(13) + CHAR(10)
+ 'Title:'+ title + 'Salutation:' + salutatn + 'Status:'+ status + CHAR(13) + CHAR(10)
+ 'ID No:'+ idno +'Old ID No:' +oldidno + 'ID Code:' +idcode + CHAR(13) + CHAR(10)
+ 'Hphone:' +hphone +'Busn_phone:' + bphone + CHAR(13) + CHAR(10)
+ 'Create Date:' + creadate + 'Maint Date:' + lmaint + CHAR(13) + CHAR(10)
+ 'Codes:' + 'Home Branch:' + origbrn + 'Maint Branch:' +maintbrn + CHAR(13) + CHAR(10)
+ 'Race:' +race + 'Cust Type:' +custtype + 'Bumi:' +bumi + CHAR(13) + CHAR(10)
+ 'Sex:' +sex + 'MaritalStatus:' +marital + 'Religion:' +religion + 'Citizen:' +citizen + CHAR(13) + CHAR(10)
+ 'Country:' +country + 'Occupation:' +occuptn + 'VIP Code:' +vip + 'Bank Code:' +bnkcd + CHAR(13) + CHAR(10)
+ 'Employer:' +employer
+ 'Remark:' +remarks
from cifdata where error='Y'

Now just bcp out the view
[/code]

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/

ramecepa
Starting Member

43 Posts

Posted - 2010-11-11 : 21:45:33

Thanks for all,
i got the solution..
i have used below one
osql -E -d databasename -S servername -Q "query/SP" -0 output file

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources