specifying a text qualifier when outputing to file

Question

specifying a text qualifier when outputing to file

Nazir Jooman

Valued Member

Points: 63
More actions
September 11, 2009 at 2:59 am

#204519

Hi,
How is it possible to specify a text qualifier when outputing the contents of a query to file that is comma delimitted
I gave an example of what my query is but any solution would be welcomed.
SSIS is not a very viable option on my side.
Thanks
Example
DECLARE @cmd VARCHAR(255)
SET @cmd = 'osql -E -h -s"," /Q "SET NOCOUNT ON SELECT TOP 10 * From [dbo].test" -o c:\test2.txt'
EXEC xp_cmdshell @cmd

Viewing 4 posts - 1 through 3 (of 3 total)

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

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 1

Unfortunately, lot's of things don't even consider the "real" CSV format anymore. Take a look at the QUOTENAME function in Books Online. You'll need to build the correct output in the query.

Oddly enough, the following article on Forum Ettiquette has a demonstration of such code...

http://www.sqlservercentral.com/articles/Best+Practices/61537/

... look for the section with the following quote in it...

(Sidebar: If you take out the UNION ALL from the code generator, and change the single quote to a double quote in QUOTENAME, guess what you have... a nice neat little CSV data generator!)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

lcarrethers SSCommitted Points: 1917 More actions · Answer 2

How would I do this if i wanted to pull from two different tables.

When i add inner join it doesn't work.

Here is what i have:

osql -Usa -Psanicle -Sdbc-gp -Q"SELECT sum(qtyonhnd-atyalloc) AS QTYAVAILABLE, ITEMNMBR, LOCNCODE FROM QA2..IV00102 INNER JOIN IV00101 ON IV00102.ITEMNMBR = IV00101.ITEMNMBR GROUP BY IV00102.ITEMNMBR, LOCNCODE HAVING sum(qtyonhnd-atyalloc) < 0 ORDER BY ITEMNMBR,LOCNCODE " -o"\\dbc-gp\d$\BusinessAlertResults\INTQA2QtyShortage.txt" -w500

If i take the inner join out it works fine but i need another column from iv00101 table.

I keep getting this error:

Msg 208, Level 16, State 1, Server DBC-GP, Line 1

Invalid object name 'IV00101'.

winash SSChampion Points: 11446 More actions · Answer 3

Specify QA2..IV00101 instead of just IV00101 in the query and it should work fine (assuming that table IV00101 exists in the QA2 DB - otherwise specify the appropriate DB name)

osql -Usa -Psanicle -Sdbc-gp -Q"SELECT sum(qtyonhnd-atyalloc) AS QTYAVAILABLE, ITEMNMBR, LOCNCODE FROM QA2..IV00102 INNER JOIN QA2..IV00101 ON IV00102.ITEMNMBR = IV00101.ITEMNMBR GROUP BY IV00102.ITEMNMBR, LOCNCODE HAVING sum(qtyonhnd-atyalloc) < 0 ORDER BY ITEMNMBR,LOCNCODE " -o"\\dbc-gp\d$\BusinessAlertResults\INTQA2QtyShortage.txt" -w500