CSV Scheduled Report

I need to run a report from my DB to run every night and save the results as
a CSV file. This file will need to be saved to a specified location to be
imported to another vendor DB. I created the query inside of a Stored
Procedure and scheduled the SP to execute using the Service Agent (Jobs) and
save the results to a .CSV file. The file is created but on the top of the
file there's always the time date and step of the job and also the CSV format
is not correct the information needs to be in their own column so that the
vendor DB can import the data to their DB. How can I schedule this job so
that the time, date and Job step does not show on the results file and also
how can i make sure that the format of the CSV file is correct. When I tun
the same query in analyzer it creates all my columns with the headings
correclty.
Thanks in advance for any help.
Carlos
Look at jobs in the BOL
create table ww
(
col1 int,
col2 varchar(50),
col3 varchar (50)
)
insert into ww values (47,'ReadyShip','(503)888-999')
insert into ww values (48,'MyShipper','(503)1212-454')
insert into ww values (49,'ReadyShip','(45)888-999')
insert into ww values (50,'MyShipper','(545)1212-454')
--Run
bcp northwind.dbo.ww out d:\test1.txt -c -t, -SServer -Usa -Ppass
exec master..xp_cmdshell 'BCP northwind..ww IN
d:\test1.txt -c -C850 -SServer -Usa -Ppass'
"Carlos Santos" <CarlosSantos@.discussions.microsoft.com> wrote in message
news:F78D3E35-2FE8-402D-949C-3AC1DD22090A@.microsoft.com...
> I need to run a report from my DB to run every night and save the results
as
> a CSV file. This file will need to be saved to a specified location to be
> imported to another vendor DB. I created the query inside of a Stored
> Procedure and scheduled the SP to execute using the Service Agent (Jobs)
and
> save the results to a .CSV file. The file is created but on the top of
the
> file there's always the time date and step of the job and also the CSV
format
> is not correct the information needs to be in their own column so that
the
> vendor DB can import the data to their DB. How can I schedule this job so
> that the time, date and Job step does not show on the results file and
also
> how can i make sure that the format of the CSV file is correct. When I
tun
> the same query in analyzer it creates all my columns with the headings
> correclty.
> Thanks in advance for any help.
|||I don't want to create another table. I already have the query that I need
to run against my DB and the results need to be saved to a .csv file. My
problem is that the .CSV data is all over the columns and also the top on the
file always shows the time date and the step that took to run the job.
The vendor appliaction is always monitoring a specif directory for the
formated .csv file to import to their app. So i need to find a way so that
when my stored procedure when scheduked to run needs to output the results to
a .csv file with the data in the right columns and without the time, date and
step number of the job.
Thanks
"Uri Dimant" wrote:

> Carlos
> Look at jobs in the BOL
> create table ww
> (
> col1 int,
> col2 varchar(50),
> col3 varchar (50)
> )
> insert into ww values (47,'ReadyShip','(503)888-999')
> insert into ww values (48,'MyShipper','(503)1212-454')
> insert into ww values (49,'ReadyShip','(45)888-999')
> insert into ww values (50,'MyShipper','(545)1212-454')
> --Run
> bcp northwind.dbo.ww out d:\test1.txt -c -t, -SServer -Usa -Ppass
>
> exec master..xp_cmdshell 'BCP northwind..ww IN
> d:\test1.txt -c -C850 -SServer -Usa -Ppass'
>
>
> "Carlos Santos" <CarlosSantos@.discussions.microsoft.com> wrote in message
> news:F78D3E35-2FE8-402D-949C-3AC1DD22090A@.microsoft.com...
> as
> and
> the
> format
> the
> also
> tun
>
>
|||Carlos
You might want to look at xp_execresultset SP. If I remember well OJ has a
great article about such kind of queries.
EXEC master..xp_execresultset N'SELECT
''EXEC master..xp_cmdshell ''''BCP "SELECT * FROM Northwind..Orders WHERE
Employeeid=''+CAST(Employeeid AS VARCHAR)+''" queryout
"C:\Temp\''+CAST(Employeeid AS
VARCHAR)+''.csv" -w -t"|" -T -S"''+@.@.servername+''"'''' ''
FROM (SELECT DISTINCT Employeeid FROM Northwind..Orders)x
',N'Northwind'
"Carlos Santos" <CarlosSantos@.discussions.microsoft.com> wrote in message
news:DBAEDDF1-37A5-4854-8A92-F798FB30F6B6@.microsoft.com...
> I don't want to create another table. I already have the query that I
need
> to run against my DB and the results need to be saved to a .csv file. My
> problem is that the .CSV data is all over the columns and also the top on
the
> file always shows the time date and the step that took to run the job.
> The vendor appliaction is always monitoring a specif directory for the
> formated .csv file to import to their app. So i need to find a way so
that
> when my stored procedure when scheduked to run needs to output the results
to
> a .csv file with the data in the right columns and without the time, date
and[vbcol=seagreen]
> step number of the job.
> Thanks
> "Uri Dimant" wrote:
message[vbcol=seagreen]
results[vbcol=seagreen]
to be[vbcol=seagreen]
(Jobs)[vbcol=seagreen]
of[vbcol=seagreen]
that[vbcol=seagreen]
job so[vbcol=seagreen]
I[vbcol=seagreen]
|||Who's OJ? and where can i look for such article on these kind of queries. I
could not get your sample to work.
Thanks again for your help.
"Uri Dimant" wrote:

> Carlos
> You might want to look at xp_execresultset SP. If I remember well OJ has a
> great article about such kind of queries.
>
> EXEC master..xp_execresultset N'SELECT
> ''EXEC master..xp_cmdshell ''''BCP "SELECT * FROM Northwind..Orders WHERE
> Employeeid=''+CAST(Employeeid AS VARCHAR)+''" queryout
> "C:\Temp\''+CAST(Employeeid AS
> VARCHAR)+''.csv" -w -t"|" -T -S"''+@.@.servername+''"'''' ''
> FROM (SELECT DISTINCT Employeeid FROM Northwind..Orders)x
> ',N'Northwind'
>
>
> "Carlos Santos" <CarlosSantos@.discussions.microsoft.com> wrote in message
> news:DBAEDDF1-37A5-4854-8A92-F798FB30F6B6@.microsoft.com...
> need
> the
> that
> to
> and
> message
> results
> to be
> (Jobs)
> of
> that
> job so
> I
>
>
|||Carlos
Sorry , try search on internet OJ+sql Server' ,i don't have a time right
now.
I posted yesterday one example about how it could be used.
EXEC master..xp_execresultset N'SELECT
''EXEC master..xp_cmdshell ''''BCP "SELECT * FROM Northwind..Orders WHERE
Employeeid=''+CAST(Employeeid AS VARCHAR)+''" queryout
"C:\Temp\''+CAST(Employeeid AS
VARCHAR)+''.csv" -w -t"|" -T -S"''+@.@.servername+''"'''' ''
FROM (SELECT DISTINCT Employeeid FROM Northwind..Orders)x
',N'Northwind'
"Carlos Santos" <CarlosSantos@.discussions.microsoft.com> wrote in message
news:5C6A6F57-DE0F-4213-92FF-88C0DC3A61CB@.microsoft.com...
> Who's OJ? and where can i look for such article on these kind of queries.
I[vbcol=seagreen]
> could not get your sample to work.
> Thanks again for your help.
> "Uri Dimant" wrote:
has a[vbcol=seagreen]
WHERE[vbcol=seagreen]
message[vbcol=seagreen]
My[vbcol=seagreen]
on[vbcol=seagreen]
results[vbcol=seagreen]
date[vbcol=seagreen]
location[vbcol=seagreen]
Stored[vbcol=seagreen]
top[vbcol=seagreen]
CSV[vbcol=seagreen]
this[vbcol=seagreen]
and[vbcol=seagreen]
When[vbcol=seagreen]
headings[vbcol=seagreen]