The syntax of the command is incorrect

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

The syntax of the command is incorrect

Author

Topic

Chris_Kelley
Posting Yak Master

114 Posts

Posted - 2014-10-14 : 12:30:12

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-10-14 : 13:17:12

You should break up the select that sets the @newfilename and @cmd into two separate SET or SELECT. Set based queries do not go sequentiall, so the @newfilename that you set won't be available for use when you set @cmd if both are in the same statement.

SELECT @NEWFILENAME = LEFT(@OldFileName,LEN(@OldFileName)-4) +'__'+ CONVERT(VARCHAR,CURRENT_TIMESTAMP,120)+'.TXT';
SELECT @cmd = 'RENAME \\Adsnt4\d$\LAYER_1\FINAL_LAYER\' + @OldFileName + ' ' + @NewFileName

Chris_Kelley
Posting Yak Master

114 Posts

Posted - 2014-10-14 : 13:33:14

Chris_Kelley
Posting Yak Master

114 Posts

Posted - 2014-10-14 : 13:41:36

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-14 : 13:50:07

quote:
Originally posted by Chris_Kelley

I think this works

SELECT @cmd = 'RENAME "\\Adsnt4\d$\LAYER_1\FINAL_LAYER\' + @OldFileName + '"'+' '+'"'+ @NewFileName+'"'

but now I'm getting --
The process cannot access the file because it is being used by another process.

You probably have the file open. Close it. If you don't have the file open, then you'll need to figure out what does. You can use Process Explorer from Sysinternals to figure it out. It's a separate download.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Chris_Kelley
Posting Yak Master

114 Posts

Posted - 2014-10-14 : 15:09:55

Thanks Tara,

I checked and I dont have a handle on it, but I was thinking maybe the loop in the query does....not sure

Thanks,
Chris
Jr Programmer

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources