Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Access Denied error when running xp_cmdshell  on SQL 2005

Posted on 2013-11-20
9
Medium Priority
?
1,131 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
9 Comments
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 39662949
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
ID: 39662994
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
ID: 39663021
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:programmher
ID: 39663260
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
 

Author Comment

by:programmher
ID: 39665113
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
ID: 39665114
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
ID: 39698938
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
ID: 39708081
Selected own answer per admin's request instead of deleting the question.
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

650 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