Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 moving csv file from one folder to another folder

Author  Topic 

new_tosql
Starting Member

17 Posts
Posted - 2008-03-28 : 13:02:11
I am having problem with moving a file from one folder to another folder. Here is the detailed scenario:

I want to move a input.csv file from shared input folder to shared archive folder. i am using the below code to do this.
declare @inpath varchar(100)
SET @inpath = 'move "\\abcdef\INPUT\input*.csv" "\\abcdef\ARCHIVE\archive.csv"'
EXEC @filestatus = master..xp_cmdshell @inpath
but the problem was it was cutting the input.csv file from INPUT folder but not pasting it in the ARCHIVE folder.
I really appreciate if anyone can help me to solve this or anyone can tell some workarounds.

Thanks,

rmiao
Master Smack Fu Yak Hacker

7266 Posts
Posted - 2008-03-29 : 00:42:13
Tried with SET @inpath = 'move \\abcdef\INPUT\input.csv \\abcdef\ARCHIVE\archive.csv'? Does sql service account have permission to write file in \\abcdef\ARCHIVE?
Go to Top of Page

new_tosql
Starting Member

17 Posts
Posted - 2008-03-31 : 09:21:03
Hi,

I tried with out double quotes., still not working., giving this error “The syntax is incorrect.” How do I know that my sql server account have write permissions or not?

Thanks
Go to Top of Page

nr
SQLTeam MVY

12543 Posts
Posted - 2008-03-31 : 10:10:43
If you don't have permission it should tell you.
Having a wildcard and moving to a single file is probably an issue.
Try a copy instead.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

new_tosql
Starting Member

17 Posts
Posted - 2008-03-31 : 10:41:55
Hi,

I used copy instead of move command. It was saying "1 File copied". but i didn't able to see the file in the archive folder. I think its just copying the file from input folder but not pasting in the archive folder. what might be the reason?
Here is the syntax i used:
'copy "\\abcdef\INPUT\input*.csv" "\\abcdef\ARCHIVE\archive.csv"'

Thanks,
Go to Top of Page

nr
SQLTeam MVY

12543 Posts
Posted - 2008-03-31 : 11:13:02
Can you put in the full filename for the input file rather than a wildcard.
That command won't actually copy the file (I don't think).

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

new_tosql
Starting Member

17 Posts
Posted - 2008-03-31 : 11:57:28
Hi,

I removed the wild card .,still its cutting from input folder but not pasting the file in the archive folder. And the output says "1 file copied".
Here is the syntax i used:
'copy "\\abcdef\INPUT\input.csv" "\\abcdef\ARCHIVE\archive.csv"'

Thanks
Go to Top of Page

jhocutt
Constraint Violating Yak Guru

385 Posts
Posted - 2008-03-31 : 12:17:28
Then you have a permission problem.
The destination folder must be writable by the user running the command.
If you are using SQL authentication then the destination must be writable by everyone/Guest (A bad idea)


"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking
Go to Top of Page

new_tosql
Starting Member

17 Posts
Posted - 2008-03-31 : 12:32:43
Everyone have full control on the destination folder. Eventhough it was not writing in to it.

Thanks
Go to Top of Page

jhocutt
Constraint Violating Yak Guru

385 Posts
Posted - 2008-03-31 : 12:37:31
Is the SQL command being run by a network user.
IE are you logging into Query Analyzer with a network id (DOMAIN\myuserid) or a SQL id (sa)?


"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking
Go to Top of Page

new_tosql
Starting Member

17 Posts
Posted - 2008-03-31 : 12:54:22
I am logging in with sqlserver authentication(not sa) in to the query analyser.userid is the same as the one logging in to the system but the password is different.

Thanks
Go to Top of Page

jhocutt
Constraint Violating Yak Guru

385 Posts
Posted - 2008-03-31 : 12:56:19
Then add Guest to the destination share
Everyone only works for authenticated users.

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking
Go to Top of Page

new_tosql
Starting Member

17 Posts
Posted - 2008-03-31 : 14:15:57
Hi,

I added Guest to the destination folder and gave full control.,but still its not working fine.

Thanks
Go to Top of Page

jhocutt
Constraint Violating Yak Guru

385 Posts
Posted - 2008-03-31 : 15:29:20
Print @inpath and run it from a dos window manually.
You might (I doubt it) get some additional info from there.


"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking
Go to Top of Page

nr
SQLTeam MVY

12543 Posts
Posted - 2008-04-01 : 05:54:19
Try
master..xp_cmdshell 'dir \\abcdef\ARCHIVE\'

Note that the sql server service will probably need permission on the share - but you should get a permission error if that's the problem.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

new_tosql
Starting Member

17 Posts
Posted - 2008-04-01 : 10:35:52
Hi jhocutt,

I am able to manually move the file from input folder to archive from dos window. but its not working when i'm doing from sql.

Thanks

quote:
Originally posted by jhocutt

Print @inpath and run it from a dos window manually.
You might (I doubt it) get some additional info from there.


"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking
Go to Top of Page

nr
SQLTeam MVY

12543 Posts
Posted - 2008-04-01 : 10:50:29
When you do it from a dos window it will use your profile - from xp_cmdshell it will probably use the sql server service.
Try the dir command I posted.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

new_tosql
Starting Member

17 Posts
Posted - 2008-04-01 : 13:33:18
Hi NR,

declare @filestatus int
declare @inpath varchar(100)
SET @inpath = 'dir "\\abcdef\INPUT\input*.csv"'
EXEC @filestatus = master..xp_cmdshell @inpath

i tried the dir command that you posted not giving any error about the permissions. It was giving the filestatus as zero(which means file exists in input folder).

Also did master..xp_cmdshell 'dir “\\abcdef\ARCHIVE\"' just telling how many directories it have. Not giving any error about the permissions.

Thanks,

quote:
Originally posted by nr

When you do it from a dos window it will use your profile - from xp_cmdshell it will probably use the sql server service.
Try the dir command I posted.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

nr
SQLTeam MVY

12543 Posts
Posted - 2008-04-01 : 22:14:54
Sounds like you have read permission on the folder.
I assume you mean it's also listing the folders rather than just returning how many there are?
Also assume from your response that there are no fioles in that folder.
Now try

declare @filestatus int
declare @inpath varchar(100)
SET @inpath = 'hello > "\\abcdef\ARCHIVE\hello.txt"'
EXEC @filestatus = master..xp_cmdshell @inpath
SET @inpath = 'dir "\\abcdef\ARCHIVE\"'
EXEC @filestatus = master..xp_cmdshell @inpath
SET @inpath = 'type "\\abcdef\ARCHIVE\hello.txt"'
EXEC @filestatus = master..xp_cmdshell @inpath

See if that creates the file correctly and can detect it.
If it can then the only thing left is that the input file can't be read.
It's being deleted by the move so I doubt whether that's the issue but we can test that.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

new_tosql
Starting Member

17 Posts
Posted - 2008-04-02 : 09:22:40
Hi NR,

Here is the output i got: Hello.txt got created in the archive with out any data in it.

OUTPUT:
'hello' is not recognized as an internal or external command,
operable program or batch file.

OUTPUT:
Volume in drive \\rpdbusch08\doc$ is New Volume
Volume Serial Number is 1AD1-C6C4
NULL
Directory of "\\abcdef\ARCHIVE\”
NULL
04/02/2008 09:18a <DIR> .
04/02/2008 09:18a <DIR> ..
04/02/2008 09:18a 0 hello.txt
1 File(s) 0 bytes
1 Dir(s) 11,370,168,320 bytes free

Thanks for your help!!

quote:
Originally posted by nr

Sounds like you have read permission on the folder.
I assume you mean it's also listing the folders rather than just returning how many there are?
Also assume from your response that there are no fioles in that folder.
Now try

declare @filestatus int
declare @inpath varchar(100)
SET @inpath = 'hello > "\\abcdef\ARCHIVE\hello.txt"'
EXEC @filestatus = master..xp_cmdshell @inpath
SET @inpath = 'dir "\\abcdef\ARCHIVE\"'
EXEC @filestatus = master..xp_cmdshell @inpath
SET @inpath = 'type "\\abcdef\ARCHIVE\hello.txt"'
EXEC @filestatus = master..xp_cmdshell @inpath

See if that creates the file correctly and can detect it.
If it can then the only thing left is that the input file can't be read.
It's being deleted by the move so I doubt whether that's the issue but we can test that.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

nr
SQLTeam MVY

12543 Posts
Posted - 2008-04-02 : 09:40:59
Oops - missed out the echo. Should have been
SET @inpath = 'echo hello > "\\abcdef\ARCHIVE\hello.txt"'

Now try creating a file in the input folder then moving it.

declare @filestatus int
declare @inpath varchar(100)
SET @inpath = 'echo hello > "\\abcdef\INPUT\hello.txt"'
EXEC @filestatus = master..xp_cmdshell @inpath
SET @inpath = 'dir "\\abcdef\INPUT\"'
EXEC @filestatus = master..xp_cmdshell @inpath
SET @inpath = 'type "\\abcdef\INPUT\hello.txt"'
EXEC @filestatus = master..xp_cmdshell @inpath

SET @inpath = 'move "\\abcdef\INPUT\hello.txt" "\\abcdef\ARCHIVE\hello.txt"'
EXEC @filestatus = master..xp_cmdshell @inpath
SET @inpath = 'dir "\\abcdef\INPUT\"'
EXEC @filestatus = master..xp_cmdshell @inpath
SET @inpath = 'dir "\\abcdef\ARCHIVE\"'
EXEC @filestatus = master..xp_cmdshell @inpath
SET @inpath = 'type "\\abcdef\ARCHIVE\hello.txt"'
EXEC @filestatus = master..xp_cmdshell @inpath

also try it without the filename on the destination
SET @inpath = 'move "\\abcdef\INPUT\hello.txt" "\\abcdef\ARCHIVE\"'
EXEC @filestatus = master..xp_cmdshell @inpath


==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site