Can stored procedures execute BCP..

Question

Hello,
I have made an stored procedure as follows, which exec a BCP .But its giving an error when executing
exec cmdmaster 1,1

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

The Stored procedure looks like below..


ALTER PROCEDURE cmdmaster @board INT = NULL,
@branch INT = NULL
AS
SET NOCOUNT ON;
IF @board IS NULL
BEGIN
 PRINT 'ERROR:( You must specify the BoardID'
 IF @branch IS NULL
 PRINT 'ERROR:( You must specify the BrancdID'
 RETURN
END
DECLARE @bcp AS VARCHAR(500)
SELECT @bcp='BCP "SELECT TestID,TestName,CommandLine,BailOutTime FROM NVQM.dbo.MasterTestPlan" queryout C:\TEMP.TXT -c -k -T -S"VPATEKAR-DT3\SQLEXPRESS" -t" " -r"##"'
EXEC NVQM.sys.xp_cmdshell @bcp

RETURN
GO

Issue is that; im not getting error or so when i run the following from command line
'BCP "SELECT TestID,TestName,CommandLine,BailOutTime FROM NVQM.dbo.MasterTestPlan" queryout C:\TEMP.TXT -c -k -T -S"VPATEKAR-DT3\SQLEXPRESS" -t" " -r"##"
in command prompt it works fine...

Does Stored procedure executes BCP is a question in my mind.

Answer 1

That's different between calling bcp from command line and calling it from stored procedure.
When using BCP from connad line, the output file is created on your local machine; Calling BCP from stored procedure will generate the file on your remote server machine.
Please make sure the path/permission are correct in later case.

---

This posting is provided "AS IS" with no warranties, and confers no rights

Answer 2

You also have to consider where SQL Server has permissions to write under its user account and while running as a service.  The two paths that are writable in all installations of SQL Server are %TEMPDIR% and the SQL Server log/data directories.  The C:\ root is locked down more tightly.

As Ziran mentioned, remember that all the paths are relative to the server, not any client.


Hope that helps,

John

---

This post is provided 'as is' and confers no express or implied warranties or rights.

Answer 3

Thanks for your valuable reply,
now i think, i can resolve the problem..

thanks
anuj