programmher
asked on
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\my
print @cmdstr
EXEC master..xp_cmdshell @cmdstr
exec master..xp_cmdshell 'del \\servername\folderpath\my
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?
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\my
print @cmdstr
EXEC master..xp_cmdshell @cmdstr
exec master..xp_cmdshell 'del \\servername\folderpath\my
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?
ASKER
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.
I have checked and the .mdb file is not in use.
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?
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?
ASKER
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?
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?
ASKER
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
I changed the solution to use all cmdexec commands instead of executing a stored procedure or executing SQL statements
Please post your solution and accept your own answer if you found one so that it might help others.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Selected own answer per admin's request instead of deleting the question.
What are you trying to do?