Rob M.
asked on
BCP: Execute a Stored Procedure and Extract Data to a .txt File with Pipe Delimited Format with a Pipe Delimited Header
I am creating a data extract. I am using BCP to execute a SQL Server Stored Procedure that produces a pipe delimited text file.
I am required to have pipe delimited headers. My question is how do I create a pipe delimited header in my pipe delimited text file? I've been reading that SSIS is a good option but unfortunately I do not have access to it so I am looking for another method.
Output:
TEST SQL:
(Not exact SQL but should correctly convey the concept)
declare @sql varchar(8000)
select @sql = 'bcp "execute yourdatabase.dbo.usp_userd etails " queryout c:\logs\bcpheadertest.txt -c -t"|" -T -S' + @@servername exec master..xp_cmdshell @sql
create table userdetails
(
insert into userdetails(login,fullname ,address)
values ('atest','a test','123 anywhere, aw 00000'),
('btest','b test','456 anywhere, aw 00000'),
('ctest','c test','789 anywhere, aw 00000')
create procedure dbo.usp_userdetails
as
begin
select userid,login,fullname,addr ess
from userdetails
end
Thanks in advance.
Robert
I am required to have pipe delimited headers. My question is how do I create a pipe delimited header in my pipe delimited text file? I've been reading that SSIS is a good option but unfortunately I do not have access to it so I am looking for another method.
Output:
TEST SQL:
(Not exact SQL but should correctly convey the concept)
declare @sql varchar(8000)
select @sql = 'bcp "execute yourdatabase.dbo.usp_userd
create table userdetails
(
userid int identity(1,1),
login varchar(10),
fullname varchar(100),
address varchar(1000)
)login varchar(10),
fullname varchar(100),
address varchar(1000)
insert into userdetails(login,fullname
values ('atest','a test','123 anywhere, aw 00000'),
('btest','b test','456 anywhere, aw 00000'),
('ctest','c test','789 anywhere, aw 00000')
create procedure dbo.usp_userdetails
as
begin
select userid,login,fullname,addr
from userdetails
end
Thanks in advance.
Robert
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks you very much. I am going to use the second option.