BCP "Access Denied" Error

Hello All,
  I am having a problem with a BCP statement which I am running from a stored procedure. Basically all it does is loop through all the tables and exports the data into a text file with todays date on it. However the BCP statement give me an error of "Access Denied". Any suggestions? The stored procedure is below.....

CREATE PROCEDURE sp_Daily_Backup AS

DECLARE
      @TableName VARCHAR (255),       
      @FileName VARCHAR (255),
      @bcpCommand VARCHAR(2000)

-- Declare cursor
DECLARE tables CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

-- Open the cursor
OPEN tables

-- Loop through all the tables in the database
FETCH NEXT
FROM tables
INTO @tablename

WHILE @@FETCH_STATUS = 0
BEGIN

-- Backup each table
SET @FileName = REPLACE('c:\Daily_Backups\'+@TableName+'\'+@TableName+CONVERT(char(8),GETDATE(),1)+'.txt','/','')

SET @bcpCommand = 'bcp "SELECT * FROM Database..' + @TableName + '" queryout "'
SET @bcpCommand = @bcpCommand + @FileName + '" -U garth -P pw -c'

SELECT @bcpCommand

EXEC master..xp_cmdshell @bcpCommand

FETCH NEXT
FROM tables
INTO @tablename
END

-- Close and deallocate the cursor
CLOSE tables
DEALLOCATE tables
GO

PLEASE HELP. IT'S DRIVING ME INSANE!!!!!!!!!

Thanks

mcdermon
mcdermonAsked:
Who is Participating?
 
Melih SARICAOwnerCommented:
Open EM.. -> Ur Ssql Server->Management->Sql Server Agent--Right Click ->Properties->General Tab

U ll see the User.. which one is Selected ? System USer or a Specific User?

if its system User Change is To Administrator..

And also .. Check ur Users write access to the Location that ur backups file r created..


Melih SARICA
0
 
Melih SARICAOwnerCommented:
r u using this SP with a Job ..
if so .. Check the SQL serverAgent  Startup Account  ..

I guess ur SQL server Agent is starting up with System User


Melih SARICA
0
 
mcdermonAuthor Commented:
Sorry,
I should have said that I am a newbie at all this, so I am embarrassed to say that I do not know exactly what the SQL server agent does. As far as I know I use windows authentication to connect to the database.

Thanks for replying!

mcdermon
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Melih SARICAOwnerCommented:

ok ..

Question .. Is the stored Procedure runs from Query Analyzer or from a Job  or from an Application ?
0
 
mcdermonAuthor Commented:
I am hoping to run it from a batch file eventually - but at the minute I am trying to get it to run in the query analyzer. The "Access Denied" message comes up for every table in my loop.

Thanks,

mcdermon
0
 
mcdermonAuthor Commented:
Non Zero, I tried your suggestions but it made no difference - is there anything else I can try?

mcdermon
0
 
Melih SARICAOwnerCommented:
did u restart SQL Server Agent.. ?

And While ur Running it from Query Analyzer.. U must login with a valid windows USer to access these paths..

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.

All Courses

From novice to tech pro — start learning today.