BCP problem

[Boris-France-Paris]

Hi ,I want to make a flat file when someone make a insert, or update, or delete commande on my database.
I begin to test this code (SQLscripting) but it don't work, I don't understand :

if exists (select * from sysobjects where id = object_id('dbo.FImpBCPProc&#39 and sysstat & 0xf = 4)
drop procedure dbo.FImpBCPProc
GO

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 = 'MYTable'
SELECT @FILENAME = 'c:Exportcommande emp.txt'
SELECT @FORMAT = '-f c:Exportcommande emp.fmt'


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

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

END
GO

Megathanks for your help

[Ray Miao]

1. where do you specify db name?
2. how do you fire the sp? From trigger?


------------
Boris-France-Paris at 11/12/99 10:18:02 AM

Hi ,I want to make a flat file when someone make a insert, or update, or delete commande on my database.
I begin to test this code (SQLscripting) but it don't work, I don't understand :

if exists (select * from sysobjects where id = object_id('dbo.FImpBCPProc&#39 and sysstat & 0xf = 4)
drop procedure dbo.FImpBCPProc
GO

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 = 'MYTable'
SELECT @FILENAME = 'c:Exportcommande emp.txt'
SELECT @FORMAT = '-f c:Exportcommande emp.fmt'


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

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

END
GO

Megathanks for your help