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?

asked 18 Aug '11, 15:17

kmj's gravatar image

kmj
16112
accept rate: 0%


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
permanent link

answered 18 Aug '11, 15:58

Vincent%20Buck's gravatar image

Vincent Buck
70681520
accept rate: 15%

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

permanent link

answered 18 Aug '11, 16:44

kmj's gravatar image

kmj
16112
accept rate: 0%

Comment Text Removed
Comment Text Removed

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

(18 Aug '11, 16:48) Vincent Buck

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.

(19 Aug '11, 04:14) Martin
Replies hidden

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

(20 Aug '11, 07:53) Volker Barth

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

permanent link

answered 18 Aug '11, 16:46

kmj's gravatar image

kmj
16112
accept rate: 0%

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

permanent link

answered 18 Aug '11, 16:46

kmj's gravatar image

kmj
16112
accept rate: 0%

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

(19 Aug '11, 03:10) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×36
×18

question asked: 18 Aug '11, 15:17

question was seen: 1,062 times

last updated: 20 Aug '11, 07:53