• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1163
  • Last Modified:

Access Denied error when running xp_cmdshell on SQL 2005

I need to update a process on a SQL 2005 package.

I need to copy, rename, and delete a file.

Here is my code:  

DECLARE
@TodayDate as varchar(40),
@TodayHour as varchar(40),
@TodayMinu  as varchar(40),
@NewFileName as varchar(100),
@cmdstr as varchar(328)
SELECT @TodayDate = CONVERT(varchar(10), GETDATE(), 112)
SELECT @TodayHour = DATEPART(hh,GETDATE())
SELECT @TodayMinu = DATEPART(mi,GETDATE())
SELECT @NewFileName = 'myfileone' + '_' + @TodayDate + '_' + @TodayHour + '_' + @TodayMinu + '.mdb'
print @NewFileName
set @cmdstr='copy \\servername\folderpath\myfileone.mdb  \\servername\folderpath\'  + @NewFileName  
print @cmdstr
EXEC master..xp_cmdshell @cmdstr
exec master..xp_cmdshell 'del \\servername\folderpath\myfileone.mdb'

The stored procedure does run without errors but I get the below message:

Access is denied.

Why am I getting this message?  The login I am running this under has full permissions,  How do I resolve this error?
0
programmher
Asked:
programmher
  • 5
  • 3
1 Solution
 
Lee SavidgeCommented:
The mdb file is probably in use as it is a database file so you won't be able to delete it.

What are you trying to do?
0
 
programmherAuthor Commented:
I need to copy the existing .mdb, rename it, then delete the first .mdb.

I have checked and the .mdb file is not in use.
0
 
Lee SavidgeCommented:
If this is a SQL database and not an Access database, then unless the database has been detached, it will be in use.

I'm still at a loss as to what you're trying to achieve. I know that you're attempting to copy, rename and then delete, but what is the aim here?
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
programmherAuthor Commented:
The size of the access database is exceeding the 2 GB limit.  Deleting the large database and replacing it with a smaller database is the manual work around.  I am trying to automate this process.

Do you know of an easier way to do this?   Is there a way to delete the records in the Access database from SQL then proceed with updating the tables?
0
 
programmherAuthor Commented:
I've requested that this question be deleted for the following reason:

I changed the solution to use all cmdexec commands instead of executing a stored procedure or executing SQL statements
0
 
Lee SavidgeCommented:
Please post your solution and accept your own answer if you found one so that it might help others.
0
 
programmherAuthor Commented:
First, I deleted the existing .mdb via  a SQL job's CMDEXEC command:  del: path the file existed.

Second, I copied a smaller version (with all the data elements deleted) of the mdb to the path in the same folder the final .mdb will reside:  copy:  filepath oldfilename.mdb newfilepath.

Last, I renamed the .mdb: rename "filepath\oldname.mdb" "newname.mdb"

The newname.mdb database was ready to receive new data from the schedued job.
0
 
programmherAuthor Commented:
Selected own answer per admin's request instead of deleting the question.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now