Link to home
Create AccountLog in
Avatar of jackadmin
jackadminFlag for United States of America

asked on

SQL OUTPUT TO with space in file path

I have a script that calls a SQL text file with an OUTPUT TO command to create a CSV file.  I am trying to get this to go to a UNC path with spaces in the folder names.  I am trying several combinations of quotes and cannot get it to work.  I'm sure this is easy but I haven't been able to google for the correct information.

I am trying

OUTPUT TO "\\servername\share\folder name\another folder\filename.csv" qoute' ';

If I go to a path without spaces and use below, it works.  

OUTPUT TO \\servername\share\filename.csv qoute' ';
Avatar of jackadmin
jackadmin
Flag of United States of America image

ASKER

Bonus question...

How can I date stamp the file name?  I'd like

Example Date 12/30/2010

Filename= filename_12-30.csv
Avatar of Guy Hengel [angelIII / a3]
this is MySQL, right?
what os?
usually, you might need to escape the (double) quotes also.
It looks more like SQL Server from the example given but running

MySQL on a Windows machine the following works.
SELECT
 *
 FROM
 table
 INTO OUTFILE
 	'C:\\Folder With Space\\result.txt'

Open in new window

The key is the double slashes in the output location so try.


OUTPUT TO "\\servername\\share\\folder name\\another folder\\filename.csv"

Open in new window

I believe it is MS SQL Server 2005 (Version 9).  Forgive my lack of SQL/DB knowledge.  It's running on Windows XP and on Win Server 2003.  I have multiple databases that I need to do this on.
marklogan

Very close...didn't work the first time.  I added a third slash before the servername since you added a slashed to all the sub folders.  That worked.  It needed three slashes, then 2 slashes for all the sub folders with double quotes around the path.

OUTPUT TO "\\\servername\\share\\folder name\\another folder\\filename.csv" quote' ';

Now, how about adding a date stamp to the file name?
How you are running this query? Automated from a script?

If so you could assign a variable at the top of the script to be the date.

Then pass the variable into the OUTPUT statement.

OUTPUT TO "\\\servername\\share\\folder name\\another folder\\filename_" + @Date + '.csv" quote' ';

The syntax there will be wrong, I don't have MS SQL to test this on.
Can you give specific syntax?  I'm trying to declare at the top:

DECLARE @FileDate DATETIME
SET @FileDate GETDATE()

OUTPUT TO "\\\servername\\share\\folder name\\another folder\\filename_" + @FileDate + '.csv" quote' ';

That isn't working.  Am I way off?
you will need dynamic sql for that...

apart from that, you will \\\\ instead of \\\ in the beginning.
I'm unable to test the syntax, I don't have access to a MSSQL machine.
It's actually Sybase SQL Anywhere.
ASKER CERTIFIED SOLUTION
Avatar of lbushby
lbushby

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of lbushby
lbushby

I don't know if there is a better place to post this but it would be helpful if a new category could be added for the product 'Sybase SQL Anywhere', which this question refers to, as it is distinct from both Microsoft and Sybase SQL Server.