I am trying to export a csv file using bcp but get: Unable to open BCP host data-file
My sproc does not return any error and does not create the file so i tried the bcp part in DOS and got the above error. I have checked the permissions.
To execute the below sproc I use
shrExportData 'C:\Temp\',0
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[shrExportData]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[shrExportData]
GO
CREATE procedure dbo.shrExportData_iNet
@ExportCmdPath varchar(300),
@Debug bit = 0
as
begin -- Proc
set nocount on
---------------------------
-- Variable declarations --
---------------------------
DECLARE @stmt shrsLnkMsg
DECLARE @stmtftp shrsLnkMsg
DECLARE @FileName varchar(30)
DECLARE @DataSource varchar(30)
Declare @ErrorMsg varchar(255)
Declare @BCPCommand varchar(255)
--------------------------
-- Initialize variables --
--------------------------
IF @ExportCmdPath='' BEGIN SET @ExportCmdPath = 'C:\Temp ' END --
SELECT @FileName = 'Employee.csv'
SELECT @DataSource = 'EmployeeDatabase..TempLoadExportData'
------------------------------
-- Create ExportData Table --
------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[TempLoadExportData]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [TempLoadExportData]
CREATE TABLE TempLoadExportData
(
FullName varchar (100),
SSN varchar (20)
)
----------------------------------
-- Insert Into ExportData Table --
----------------------------------
INSERT INTO TempLoadExportData(FullName,SSN)
(SELECT QUOTENAME(p.FullName, '"') AS FullName,QUOTENAME(p.SSN_SIN, '"') AS SSN FROM EmpTable)
------------------------
--Export File
------------------------
/* Create specified directory if it doesn't already exist */
SELECT @stmt = 'md ' + @ExportCmdPath
EXEC master..xp_cmdshell @stmt, no_output
/* Ensure we can write to output file by deleting it first */
SELECT @stmt = 'DEL ' + @ExportCmdPath + @FileName
EXEC master..xp_cmdshell @stmt, no_output
/* Then check if it still exists */
CREATE TABLE #t (x varchar(5))
SELECT @stmt = 'if exist ' + @ExportCmdPath + @FileName + ' echo 1'
INSERT #t EXEC master..xp_cmdshell @stmt, no_output
SELECT @stmt = x FROM #t where x = '1' -- (there might be more rows in #t; we only need to know if the '1' exists.
DROP TABLE #t
IF @stmt = '1' BEGIN
PRINT 'Can''t open ' + @ExportCmdPath + @FileName + ' for output. File in use or permission denied.'
END
/* Write data out to the master export file on the local server. MUST use the -C RAW command to retain foreign language character set.*/
SELECT @stmt = 'bcp ' + @DataSource + ' out ' + @ExportCmdPath + @FileName + ' -T -c -t , -r\n'
EXEC master..xp_cmdshell @stmt, no_output
------------------------------------------------------------
END -- Proc
GO
My sproc does not return any error and does not create the file so i tried the bcp part in DOS and got the above error. I have checked the permissions.
To execute the below sproc I use
shrExportData 'C:\Temp\',0
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[shrExportData]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[shrExportData]
GO
CREATE procedure dbo.shrExportData_iNet
@ExportCmdPath varchar(300),
@Debug bit = 0
as
begin -- Proc
set nocount on
---------------------------
-- Variable declarations --
---------------------------
DECLARE @stmt shrsLnkMsg
DECLARE @stmtftp shrsLnkMsg
DECLARE @FileName varchar(30)
DECLARE @DataSource varchar(30)
Declare @ErrorMsg varchar(255)
Declare @BCPCommand varchar(255)
--------------------------
-- Initialize variables --
--------------------------
IF @ExportCmdPath='' BEGIN SET @ExportCmdPath = 'C:\Temp ' END --
SELECT @FileName = 'Employee.csv'
SELECT @DataSource = 'EmployeeDatabase..TempLoadExportData'
------------------------------
-- Create ExportData Table --
------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[TempLoadExportData]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [TempLoadExportData]
CREATE TABLE TempLoadExportData
(
FullName varchar (100),
SSN varchar (20)
)
----------------------------------
-- Insert Into ExportData Table --
----------------------------------
INSERT INTO TempLoadExportData(FullName,SSN)
(SELECT QUOTENAME(p.FullName, '"') AS FullName,QUOTENAME(p.SSN_SIN, '"') AS SSN FROM EmpTable)
------------------------
--Export File
------------------------
/* Create specified directory if it doesn't already exist */
SELECT @stmt = 'md ' + @ExportCmdPath
EXEC master..xp_cmdshell @stmt, no_output
/* Ensure we can write to output file by deleting it first */
SELECT @stmt = 'DEL ' + @ExportCmdPath + @FileName
EXEC master..xp_cmdshell @stmt, no_output
/* Then check if it still exists */
CREATE TABLE #t (x varchar(5))
SELECT @stmt = 'if exist ' + @ExportCmdPath + @FileName + ' echo 1'
INSERT #t EXEC master..xp_cmdshell @stmt, no_output
SELECT @stmt = x FROM #t where x = '1' -- (there might be more rows in #t; we only need to know if the '1' exists.
DROP TABLE #t
IF @stmt = '1' BEGIN
PRINT 'Can''t open ' + @ExportCmdPath + @FileName + ' for output. File in use or permission denied.'
END
/* Write data out to the master export file on the local server. MUST use the -C RAW command to retain foreign language character set.*/
SELECT @stmt = 'bcp ' + @DataSource + ' out ' + @ExportCmdPath + @FileName + ' -T -c -t , -r\n'
EXEC master..xp_cmdshell @stmt, no_output
------------------------------------------------------------
END -- Proc
GO
