Create T-Sql script to export to csv

Question

Create T-Sql script to export to csv

Kris-155042

Hall of Fame

Points: 3460
More actions
December 6, 2006 at 2:15 pm

#116514

Can someone please show me the syntax to export data to a csv file. I currently have a script that creates a temp table, I want those results to export to a file on another server as csv. I had this working in SQL 2000 as it is an option when you create a schedule, but that doesn't seem to be an option in 2005.
Thanks,
Kris

Viewing 15 posts - 1 through 15 (of 18 total)

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

MohammedU SSCoach Points: 19644 More actions · Answer 1

You do that by using SSIS package OR bcp etc..

I didn't get you what you were using in 2000 but If you were using job outfile as .csv file it is available in 2005 to..

MohammedU
Microsoft SQL Server MVP

Kris-155042 Hall of Fame Points: 3460 More actions · Answer 2

I'm not very familiar with SSIS at this stage. Where is it available in 2005. In 2000 it's just part of the job scheduling under SQL Agent, I can't seem to find this in 2005

Thanks,

Kris

MG-148046 SSCrazy Eights Points: 9946 More actions · Answer 3

SSMS - Tools - Options - Query Results - Results to Text --> there is a drop down box for output format that has the options that are in 2000.

MG

"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.

Kris-155042 Hall of Fame Points: 3460 More actions · Answer 4

Thanks for that. But how do I tell it to do it to a particular script. I don't mean to be stupid, but I can't see where it says export it for this script only.

Thanks,

Kris

MG-148046 SSCrazy Eights Points: 9946 More actions · Answer 5

Honestly, I didn't know it could be done progammatically in the script. I have always changed the option for the script before running it.

MG

"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.

Kris-155042 Hall of Fame Points: 3460 More actions · Answer 6

I've figured out how to do it for individual jobs by going to the properties of the auto job and choosing Steps - advanced, but it exports too much infomation. I just want the flat file in CSV and ansi format. I had a similar problem in 2000, but resolved it in a DTS package. Obviously 2005 doesn't have DTS packages. It saves it with a header and in unicode. As this is going into Unix I need it with no headers and in ansi not unicode.

Any ideas?

Thanks,

Kris

MG-148046 SSCrazy Eights Points: 9946 More actions · Answer 7

You could use the DTS replacement- Integration Service (don't ask me how but mybe someone else can help with this)

I would use OSQL with an output file. Use the -h-1 -n in the command line to strip out the column headers and the rowcount. Create the query so that it outputs in the format you want. It will be a concatenated string but it works for me with no issues.

MG

"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.

Kris-155042 Hall of Fame Points: 3460 More actions · Answer 8

Like you, I'm not very good with Integration Service yet so I'm trying to avoid it at the moment as this is urgent.

Can you give me an example of the OSQL script you use to output a file in csv ansi and do I need to just put it on the end of my current script or the beginning?

Thanks,

Kris

MG-148046 SSCrazy Eights Points: 9946 More actions · Answer 9

OSQL is run the command line. You can put the OSQL command in a batch file (.bat) and use Scheduled Task to schedule and execute it if it's something that needs to be an regular basis. The typical command for this type of output might be.

osql -E -S <servername here> -d <database name here> -h-1 -n -w1000 -i "<sql script name here>" -o "<output path\filename here>"

ex: osql -E -S D00036890 -d junk -h-1 -n -w1000 -i "comma_delim.sql" -o "e:\unixfiles\comma_delim.txt"

MG

"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.

Kris-155042 Hall of Fame Points: 3460 More actions · Answer 10

I ran this script and got a syntax error.

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near 'E'.

Thanks,

Kris

Kris-155042 Hall of Fame Points: 3460 More actions · Answer 11

I've got the script working by doing: sqlcmd -S Server\instance -i "D:\SQL_Scripts\Script.sql" -o "\\server\folder\file.CSV" -s"," - h-1 -W

However I can't get SQL to automate the job. It keeps coming up with there is no output. Yet when I run it in a cmd prompt it works. Any ideas?

Thanks,

Kris

MG-148046 SSCrazy Eights Points: 9946 More actions · Answer 12

I put the osql (or your case sqlcmd) in a batch file (.bat) and create a step in a job using the operating system command (CmdExec). Use xp_cmdshell '<path\batchfile.bat>'

MG

"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.

Kris-155042 Hall of Fame Points: 3460 More actions · Answer 13

Cool thanks it's all working with sqlcmd -S Server\Instance -i d:\sql_scripts\script.sql -o\\server\folder\File.CSV -s "," -h -1 -W

Thanks,

Kris

MG-148046 SSCrazy Eights Points: 9946 More actions · Answer 14

glad I could help

MG

"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.