Export using Query Analyzer

Question

Post reply

Export using Query Analyzer

norbertackerman

SSC Eights!

Points: 945
More actions
November 28, 2006 at 11:47 am

#90764

Is there a way to export a comma delimited file using Query Analyzer?
Thanks in advance.

Viewing 6 posts - 1 through 5 (of 5 total)

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

JDixon-385826 SSCrazy Points: 2723 More actions · Answer 1

Open Query Analyzer
Ctrl+shift+O or Tools > Options
go to "Results" tab
Set "Default results target" to "Results to File"
Set "results output format" to "Comma Delimited (csv)"
Run script (you will get a prompt for file save location)

You will want to have the following the first thing in your script to eliminate the count - if not already set >>>>

SET NOCOUNT ON

GO

NOTE: Setp 4 above if set to "results to Text" will show you what will be created in the ouput file.

EXAMPLE:

SET NOCOUNT ON

GO

SP_who2 'active'

norbertackerman SSC Eights! Points: 945 More actions · Answer 2

Is there a way to hardcode the file location into the script that can be used in a stored procedure?

Thanks.

JDixon-385826 SSCrazy Points: 2723 More actions · Answer 3

You could use xp_cmdshell to call osql: see below>>

USE PUBS

GO

CREATE PROCEDURE ExportTest1

AS

SET NOCOUNT ON

DECLARE @cmd VARCHAR (255)

SET @cmd = 'OSQL -E -S 424XG61\DBA -d Pubs -Q "EXIT(SET NOCOUNT ON SELECT * FROM authors)" > C:\ExportFile.txt'

EXEC master.dbo.XP_cmdshell @cmd, NO_OUTPUT

GO

EXEC ExportTest1

or

use a DTS package using a Transform Data Task. Which would allow you to call a stored procedure or just run a script with a set output file

Not certain anything else is possible with SQL syntax

NOTE: for more on xp_cmdshell or osql see books online which is typically installed by default with MSSQL server (Query Analyzer uses isql)

SDM SSCommitted Points: 1581 More actions · Answer 4

If your DBAs have any self respect they'll have locked down xp_cmdshell so it can't be used.

Since the poster's starting point was QA, it might be simpler to just get osql to run the stored proc from the same (desktop?) environment that QA was run from.

norbertackerman SSC Eights! Points: 945 More actions · Answer 5

JDixon.

Thanks for your help! Both of your answers were very helpful.

Sincerely,

Norbert

meLearnASP.net