Unable to unload to a path with a strings in it

[Former Member]

The first statement below works correctly:

unload select 1 to 'c:\\temp\\test.txt' delimited by ',' quotes off escapes off

The next statement does not work correctly. The difference is I am passing in the now() function to put a timestamp in the file name:

unload select 1 to 'c:\\temp\\test_'||now()||'.txt' delimited by ',' quotes off escapes off

I can't figure out how to string together the directory and path on the unload to command. The problem is not the now() function because even this does not work:

unload select 1 to 'c:\\temp\\test_'||'test'||'.txt' delimited by ',' quotes off escapes off

Any ideas?

[former_SQLA_member1694886]

Make the path/filename a variable like so:

begin
declare @path varchar(50);
set @path = 'c:/temp/test_' + string(dateformat(now(), 'yyyy-mm-dd-hh-mm')) + '.txt';
unload select 1 to @path delimited by ',' quotes off escapes off
end

[Former Member]

Seems to be stripping out the the extra backslashes in the path when I post.

[VolkerBarth]

Yes, that's default behaviour on this site:)

[Former Member]

actually this is what I had (the extra back slashes)...

begin declare @path varchar(50); set @path = '\\\\myserver\\temp\\test_' + string(dateformat(now(), 'yyyy-mm-dd-hh-mm')) + '.txt'; unload select 1 to @path delimited by ',' quotes off escapes off end

[Former Member]

Thanks Vincent. I do want to take this one step further though, and send the file to a network drive. The following is the syntax I am using. I am not getting an error, but it is not writing the file to the place I need it to go. Does this syntax appear correct? Not sure if it is syntax or permissions.

begin declare @path varchar(50); set @path = '\\\\myserver\\temp\\test_' + string(dateformat(now(), 'yyyy-mm-dd-hh-mm')) + '.txt'; unload select 1 to @path delimited by ',' quotes off escapes off end

[former_SQLA_member1694886]

set @filename = '\\\\\\\\AIICDEVSERVER\\\\Temp\\\\test_' + string(dateformat(now(), 'yyyy-mm-dd-hh-mm')) + '.txt';

[MCMartin]

Which user account is your database server running under? If it is localsystem the reason is that localsystem is per se not able to access network resources. In this case you will have to change the user account which is used for the SQL Aynwhere service.

[VolkerBarth]

In case permissions are the problem, much more information and several approaches can be found in this FAQ.