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

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
0
mcdermon
Asked:
mcdermon
  • 4
  • 3
1 Solution
 
Melih SARICACommented:
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
 
Melih SARICACommented:

ok ..

Question .. Is the stored Procedure runs from Query Analyzer or from a Job  or from an Application ?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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
 
Melih SARICACommented:
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
 
mcdermonAuthor Commented:
Non Zero, I tried your suggestions but it made no difference - is there anything else I can try?

mcdermon
0
 
Melih SARICACommented:
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

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

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