Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Import/Export (DTS) and Replication (2000)
 BCP Export error, help!

Author  Topic 

PALLEN
Starting Member

3 Posts

Posted - 2006-07-29 : 10:04:52
Hi guys,

I hope you can help me, i am trying to export data using bcp to a directory on my website. I have verified that the directory exists etc, it creates the file i want, but doesnt populate it. Instead it says unable to open bcp host data-file.

My proc is:

ALTER PROCEDURE usp_ExportData
@ClientID INT
AS


/* GET CLIENT NAME */

DECLARE @Client_Name NVARCHAR(50)
SET @Client_Name = (SELECT ClientName FROM Clients WHERE ClientID = @ClientID)

/* DECLARE DIRECTORY NAME */

DECLARE @Dir_Name NVARCHAR(55)
SET @Dir_Name = @Client_Name+'data'

/* BEGIN EXPORT OF DATA */

DECLARE @C_String NVARCHAR(400)

SET @C_String = 'bcp "SELECT * FROM PSLFootball.dbo.Clubs WHERE ClientID = '+CAST(@ClientID AS NVARCHAR)+'" queryout "C:\inetpub\wwwroot\'+@Dir_Name+'\ClubsOut.txt" -Usa -PMYPASS -f,'
--PRINT @C_String
EXEC master..xp_cmdshell @C_String

I checked out a couple of other problems like this, but the solution is normally that the dir doesnt exist or its going across a server... can anyone help?

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-29 : 13:36:44
Sounds like a write error on the file.
try
exec master..xp_cmdshell 'echo hello > C:\inetpub\wwwroot\clientnamedata\a.txt'


==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

PALLEN
Starting Member

3 Posts

Posted - 2006-08-08 : 13:00:41
Hi, that works no problem. Have you any other suggestions?
Go to Top of Page
   

- Advertisement -