BCP through stored procedure

[MANISH MEHTA]

Below is my stored procedure
================================================== ====================
================================================== ====================

CREATE PROC FImpBCPProc
AS

DECLARE @TABLENAME VARCHAR(75)
DECLARE @FORMAT VARCHAR(75)
DECLARE @FILENAME VARCHAR(75)
DECLARE @TEMPSTR VARCHAR(255)
DECLARE @RESULT int
BEGIN

SELECT @TABLENAME = ' MANISH..TRY'
SELECT @FILENAME = ' c: emp.txt'
SELECT @FORMAT = ' -f c: emp.fmt'


SELECT @TEMPSTR = 'BCP '+ @TABLENAME + ' in '+ @FILENAME + @FORMAT + ' -Usa -P '

EXEC @RESULT = MASTER..xp_cmdshell @TEMPSTR
IF (@RESULT = 0 )
PRINT 'SUCCESS'
ELSE
PRINT 'FALILURE'

SELECT * FROM TRY

END
GO

================================================== ====================
================================================== ====================
THIS IS MY RESULT

output
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
NULL
Starting copy...
NULL
5 rows copied.
Network packet size (bytes): 8192
Clock Time (ms.): total 70 Avg 14 (71.43 rows per sec.)

(6 row(s) affected)

SUCCESS
Field1 Field2 Field3 Field4 Field5
-------------- ------------- ---------- ------------- --------------

(0 row(s) affected)
================================================== ====================
================================================== ====================
IT prints "SUCCESS"
and says 5 rows copied
but none of them
gets inserted in my TRY table as shown above

Can anyone figure out where I am wrong.

[Ray Miao]

@result is integer, how can you put command string into integer?


------------
MANISH MEHTA at 8/19/99 1:42:13 PM

Below is my stored procedure
================================================== ====================
================================================== ====================

CREATE PROC FImpBCPProc
AS

DECLARE @TABLENAME VARCHAR(75)
DECLARE @FORMAT VARCHAR(75)
DECLARE @FILENAME VARCHAR(75)
DECLARE @TEMPSTR VARCHAR(255)
DECLARE @RESULT int
BEGIN

SELECT @TABLENAME = ' MANISH..TRY'
SELECT @FILENAME = ' c: emp.txt'
SELECT @FORMAT = ' -f c: emp.fmt'


SELECT @TEMPSTR = 'BCP '+ @TABLENAME + ' in '+ @FILENAME + @FORMAT + ' -Usa -P '

EXEC @RESULT = MASTER..xp_cmdshell @TEMPSTR
IF (@RESULT = 0 )
PRINT 'SUCCESS'
ELSE
PRINT 'FALILURE'

SELECT * FROM TRY

END
GO

================================================== ====================
================================================== ====================
THIS IS MY RESULT

output
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
NULL
Starting copy...
NULL
5 rows copied.
Network packet size (bytes): 8192
Clock Time (ms.): total 70 Avg 14 (71.43 rows per sec.)

(6 row(s) affected)

SUCCESS
Field1 Field2 Field3 Field4 Field5
-------------- ------------- ---------- ------------- --------------

(0 row(s) affected)
================================================== ====================
================================================== ====================
IT prints "SUCCESS"
and says 5 rows copied
but none of them
gets inserted in my TRY table as shown above

Can anyone figure out where I am wrong.