Write query results in tab delimited file and push the file to ftp

Question

Post reply

Write query results in tab delimited file and push the file to ftp

ramadesai108

SSChampion

Points: 10609
More actions
May 27, 2011 at 12:03 pm

#241159

Hi,
How do I Write query results in tab delimited file and push the file to ftp on daily basis as a sql agent job.
Thanks.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246993 More actions · Answer 1

Sounds like a simple SSIS package just waiting to be written ...

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

prashavhad SSC Enthusiast Points: 165 More actions · Answer 2

Use Bcp Utility for this,

create your format file without mentioning -t in format file

Thanks & Regards,

Prashant Avhad

Phil Parkin SSC Guru Points: 246993 More actions · Answer 3

prashavhad (5/31/2011)
Use Bcp Utility for this,
create your format file without mentioning -t in format file
Thanks & Regards,
Prashant Avhad

How does this solution address the ftp requirement?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

ramadesai108 SSChampion Points: 10609 More actions · Answer 4

Hi Prashavhad,

Can you please give me more hint on creating bcp for this?

Thanks

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

ramadesai108 (5/31/2011)
Hi Prashavhad,
Can you please give me more hint on creating bcp for this?
Thanks

Here's the very best link for this : http://tinyurl.com/3tblqmu

ramadesai108 SSChampion Points: 10609 More actions · Answer 6

Why is this not working?

DECLARE @exe nvarchar(4000)

SET @exe =N'bcp '

SET @exe =@exe + N'

"SELECT * FROM Customers" '

SET @exe = @exe + N'queryout c:\test\CustomerInfo.xls -c, -T –S ' + @@SERVERNAME

EXEC master..xp_cmdshell @exe

Thanks.

ramadesai108 SSChampion Points: 10609 More actions · Answer 7

I missed to include "exec" in the previous statement

Why is this not working?

DECLARE @exe nvarchar(4000)

SET @exe =N'bcp exec '

SET @exe =@exe + N'

"SELECT * FROM Customers" '

SET @exe = @exe + N'queryout c:\test\CustomerInfo.xls -c, -T –S ' + @@SERVERNAME

EXEC master..xp_cmdshell @exe

Thanks.

ramadesai108 SSChampion Points: 10609 More actions · Answer 8

Yet another mistake correced from the previous statement

Why is this not working?

DECLARE @exe nvarchar(4000)

SET @exe =N'bcp '

SET @exe =@exe + N'

"EXEC SELECT * FROM Customers" '

SET @exe = @exe + N'queryout c:\test\CustomerInfo.xls -c, -T –S ' + @@SERVERNAME

EXEC master..xp_cmdshell @exe

Thanks.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

What error are you getting or what is wrong with the output?

ramadesai108 SSChampion Points: 10609 More actions · Answer 10

The problem was that I was having a line break within my select statement. I have it working now. Only thing that I need to do is to add column headers.

Thanks.

Phil Parkin SSC Guru Points: 246993 More actions · Answer 11

Did you manage to automate the FTP part? How did you do it?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

ramadesai108 SSChampion Points: 10609 More actions · Answer 12

I do not know how to ftp using cmdshell, so I wrote a webservice in .Net. Do you know a way to use cmdshell for ftp?

Thanks.