BCP vs Bulk Insert

[MMund]

We have several instances in our project where we have to read into our SQL Server 2005 database from outside files using TSQL.

To make a long story short, this works:

create table ##Thrgl02(s varchar(8000))
set @as_bcp = 'bcp ##Thrgl02 in (path and file name) -T -c -S ' + convert(varchar,@ThisServerName)
exec master.dbo.xp_cmdshell @as_bcp

but when we try to do this:

create table ##Thrgl02(s varchar(8000))
bulk insert ##Thrgl02 from (path and file name) with (codepage =  'ACP')

gives this error:
Cannot bulk load because the file "(path and file name)" could not be opened. Operating system error code 5(Access is denied.).

Ideas anyone?

TIA,

MMund

 

[mrdenny]

If this is being run as a member of the sysadmin fixed server role, and you are logged in as a Windows login (not a SQL login) then the xp_cmdshell is being run under your domain account so it uses your rights. The bulk insert goes to the file (I'm guessing a network share) via the domain account (or local account) that the SQL Server is running under. Because of this, I'm guessing that the account which the SQL Server is running under doesn't have rights to the folder or network share which you are trying to get the file from.

Simply give the account which the SQL Server runs under rights to the share or folder and the BULK INSERT command should work.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog