Run Query save data to tab delimitied file

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

Run Query save data to tab delimitied file

Author

Topic

Kyle Doouss
Yak Posting Veteran

64 Posts

Posted - 2011-11-25 : 07:21:15

I am trying to work out how to run a query, save the data as tab delimited to a specific folder called a specific name.

If I could send it straight to an ftp site then that would be great!

I will then set up a job to run this.

I welcome any help you can give me

Many Thanks

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-25 : 07:40:03

bcp out to a file then ftp it.
Or use ssis to do both.
I would keep the ftp separate.

There are many ways to create the file but I think bcp is the simplest.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kyle Doouss
Yak Posting Veteran

64 Posts

Posted - 2011-11-25 : 08:30:33

Sorry you have to bear with me. I am very new to sql

Have you got any examples of what I am trying to do?

I will have to pass the authorisation info through too I think.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-25 : 08:42:37

Simplest way if your system allows it.

for the file creation
exec master..xp_cmdshell 'bcp "select .. from ... where ..." queryout "c:\myfile.txt" -c T -Smyserver'
for the ftp create a .bat file myFTP.bat which executes the ftp
exec master..xp_cmdshell 'myFTP.bat'

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kyle Doouss
Yak Posting Veteran

64 Posts

Posted - 2011-11-25 : 10:06:44

Thanks alot for the last post.

I have put the following in but getting the following error

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'queryout'.

exec master..xp_cmdshell 'bcp "select STKCODE from SL_ACCOUNT where STKCODE=A4MYCOP" queryout "c:\myfile.txt" -c T -SAppserver'

Do I need to put the IP for the server or just the name?
I have not put ' either side of A4MYCOP which you would do if in a normal query?

Sorry I am so thick!

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-25 : 10:38:45

exec master..xp_cmdshell 'bcp "select STKCODE from SL_ACCOUNT where STKCODE=''A4MYCOP''" queryout "c:\myfile.txt" -c -T -SAppserver'

Server name is ok - it will execute on the server anyway - I take it the filepath is on the server?
Odd error message though - I don't think it's getting as far as trying to execute the bcp.
I missed out the - before the T which shuold have been the error returned

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kyle Doouss
Yak Posting Veteran

64 Posts

Posted - 2011-11-25 : 13:23:26

Thanks so much for your help.

I have just tested and it seems to work a dream.

Good job some people know what they are doing

Nice One!!!!

Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources