Error using BCP to export data from SQL Server 2008

Question

I'm getting the error:

SQLState = S1000, NativeError = 0 Error = [Microsoft][SQL Server Native Client 10.0]Unable to open BCP host data-file

When running the following command:

set nocount on;
Declare @sql varchar(1000);
declare @noteid int;
declare xx1 cursor for select nic.NotebookItemId from NotebookItemContent nic
inner join NotebookLinks nl on nl.NotebookItemId = nic.NotebookItemId
inner join NotebookItems ni on ni.NotebookItemId = nic.NotebookItemId
where nl.clientid = 1235074
AND ni.NotebookTypeId = 56;
open xx1;
fetch xx1 into @noteid;
while (@@fetch_status = 0)
begin
set @sql = 'BCP "SELECT memo FROM Monarch_Pronet_ITOIL.dbo.notebookitemcontent where notebookitemid=' + cast(@noteid as varchar) + 
'" QUERYOUT C:\TEMP\' + cast(@noteid as varchar) + '.doc -T -f C:\temp\bcp.fmt -S ' + @@SERVERNAME
EXEC master.dbo.xp_CmdShell @sql
fetch xx1 into @noteid;
end;
close xx1;
deallocate xx1;

Any help would be appreciated!

Print @SQL returns:

BCP "SELECT memo FROM Monarch_Pronet_ITOIL.dbo.notebookitemcontent where notebookitemid=17695498" QUERYOUT C:\TEMP\17695498.doc -T -f C:\temp\bcp.fmt -S BHAMSQL-SRV03

Also realised my original query wasn't right (was selecting all memo rather than using the cursor...! so the correct query is now the one above - apologies for me being a muppet :D

@sql is only 1000 characters wide. What if the SQL String is larger than 1000 characters? — rvphx
The print @SQL also returns all the other rows as I'd expect and not just that one line. — franglais
Well, thats a good thing to begin with. If you are getting at least something, you should be able to figure out the path from where the process is trying to read the file from. Can you post the exact output of the PRINT statement? — rvphx
Rajiv, I've managed to work it out - I was assuming that the BCP would be using my local credentials as I'm running SSMS from my PC, so when I was putting c:\temp\ it was using the C:\temp from another location. Putting in my network path (after making a network share with read/write permissions) seems to have sorted it. Thanks for the help though! — franglais

rvphx rvphx · Accepted Answer · 2012-04-12T15:10:08

Can you do a

PRINT @sql

and see what the @sql string value is. I am guessing, its a misplaced quote or a backslash

Error using BCP to export data from SQL Server 2008

1 Answers