Friday, February 10, 2012

BulkCopy problem

Hello,

I am using bcp in a stored procedure. I am calling the stored procedure with VB.NET 2.0. The stored procedure does work, but not every time. I can run the stored procedure from Server Manager Express, and it does run, but not every time.

Here is my bcp command. Is this correct?

set @.cmd = 'bcp '+@.dbname+'.dbo.'+@.tablename+' '+@.direction+' '+@.filepath+' -T -c -S .\SQLEXPRESS'

exec master..xp_cmdshell @.cmd

Thanks for any ideas on how to debug this.

Tom

It looks to be a problem of needing double quotes. (Dummy values supplied.)


Code Snippet


DECLARE
@.cmd nvarchar(250),
@.dbName varchar(25),
@.TableName varchar(25),
@.Direction varchar(3),
@.FilePath varchar(150)


SELECT
@.dbName = 'MyDataBase',
@.TableName = 'MyTable',
@.Direction = 'IN',
@.FilePath = 'c:\temp\input.csv'


SET @.cmd = 'bcp "' + @.dbname + '.dbo.' + @.tablename + '" ' +
@.direction + ' "' + @.filepath + '" -T -c -S .\SQLEXPRESS'


PRINT @.cmd


bcp "MyDataBase.dbo.MyTable" IN "c:\temp\input.csv" -T -c -S .\SQLEXPRESS

|||

Hi Arnie,

Thanks for taking time to reply to this issue.

Adding the quotes eliminated the problem where it was reporting an error on the filepath that contained spaces.

Now it is reporting this error:

Error = [Microsoft][SQL Native Client]Unable to open BCP host data-file

I have done some searching for the answer to this error, and I cannot find a solution. :-(

Thanks

MisterT

|||

I changed the permissions on the directory to allow Users "read" permission and it is now working.

Thank You,

Tom

No comments:

Post a Comment