Solved

Access Denied error when running xp_cmdshell  on SQL 2005

Posted on 2013-11-20
9
1,085 Views
Last Modified: 2013-12-10
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
Comment
Question by:programmher
  • 5
  • 3
9 Comments
 
LVL 25

Expert Comment

by:Lee Savidge
Comment Utility
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
 

Author Comment

by:programmher
Comment Utility
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
 
LVL 25

Expert Comment

by:Lee Savidge
Comment Utility
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
 

Author Comment

by:programmher
Comment Utility
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 

Author Comment

by:programmher
Comment Utility
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
 
LVL 25

Expert Comment

by:Lee Savidge
Comment Utility
Please post your solution and accept your own answer if you found one so that it might help others.
0
 

Accepted Solution

by:
programmher earned 0 total points
Comment Utility
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
 

Author Closing Comment

by:programmher
Comment Utility
Selected own answer per admin's request instead of deleting the question.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now