Hi all,

Still not figured out this problem with BCP.

I'm using a table variable here not a temp table. The SP code I'm using is below. Can anyone see anything in there that would cause problems with BCP to work?

CREATE procEDURE [dbo].[spStockDespatches] (

@Company varchar(2),

@Location varchar(6),

@Warehouse varchar(6)

) AS

BEGIN TRANSACTION

declare @DETAILS table(

Line varchar(400),

AdviceNoteNumber int,

LineID char(1)

)

DECLARE @Line VARCHAR(400)

DECLARE @Seperator CHAR(1)

DECLARE @HEADINGCHARACTER CHAR(1)

DECLARE @RECORDCHARACTER CHAR(1)

DECLARE @FOOTCHARACTERCHAR(1)

DECLARE @DISPLAYWAREHOUSE VARCHAR(6)

DECLARE @ADVICENOTENUMBER INT

SET @Seperator = '|'

SET @HEADINGCHARACTER = 'F'

SET @RECORDCHARACTER = 'R'

SET @FOOTCHARACTER= 'T'

SELECT @DISPLAYWAREHOUSE = CASE @WAREHOUSE

WHEN '17' THEN 'SHILL'

ELSE @WAREHOUSE

END

DECLARE csrLine CURSOR FOR

SELECT LINE FROM @DETAILS

DECLARE csrADVICENOTENUMBER CURSOR FOR

SELECT DISTINCT ADVICENOTENUMBER FROM @DETAILS WHERE LineID = @RECORDCHARACTER

SET NOCOUNT ON

INSERT INTO @DETAILS

SELECT(@HEADINGCHARACTER + @Seperator + @DISPLAYWAREHOUSE + @Seperator + CONVERT(VARCHAR(20),getdate(),105) + @Seperator + CONVERT(varchar(20),getdate(),108)),0,@HEADINGCHARACTER

IF @@ERROR <> 0

BEGIN

GOTO LOCALERROR

END

INSERT INTO @Details

SELECT LTRIM(@RECORDCHARACTER + @Seperator +

RTRIM(ANH.Company) + @Seperator +

RTRIM(ANh.Location) + @Seperator +

RTRIM(ANH.Warehouse) + @Seperator +

CONVERT(VarCHar(20),ANH.AdviceNoteCreationDate,103) + ' ' + CONVERT(VarCHar(20),ANH.AdviceNoteCreationDate,108) + @Seperator +

RTRIM(ANH.SalesOrderNumber) + @Seperator +

RTRIM(SOH.PromixRefNumber) + @Seperator +

RTRIM(ANH.AdviceNoteNUmber) + @Seperator +

RTRIM(ANL.productNumber) + @Seperator +

RTRIM(SUM(ANL.QuantitySHipped)) + @Seperator +

RTRIM(ANL.QuantityUOM) + @Seperator +

RTRIM(SUM(ANL.Weight)) + @Seperator +

RTRIM(ANL.WeightUom) + @Seperator +

RTRIM(MIN(ANL.BatchNumber)) + @Seperator +

CONVERT(VarCHar(20),MIN(ANL.BBEDate),103) + @Seperator +

RTRIM(SOH.CustomerCode) + @Seperator +

RTRIM(SOH.CustomerOrderNumber) ),ANL.AdviceNoteNumber,@RECORDCHARACTER

FROMAdviceNoteHeaders ANH

INNER JOIN SalesOrderHeaders SOH ON

ANH.Company = SOH.Company AND

ANH.Location = SOH.Location AND

ANH.Warehouse = SOH.Warehouse AND

ANH.SalesOrderNumber= SOH.SalesOrderNumber

INNER JOIN AdviceNoteLines ANL ON

ANH.Company = ANL.Company AND

ANH.Location = ANL.Location AND

ANH.Warehouse = ANL.Warehouse AND

ANH.AdviceNoteNumber = ANL.AdviceNoteNumber

WHEREANH.Company = @COMPANY AND

ANH.Location = @LOCATION AND

ANH.Warehouse = @WAREHOUSE AND

ANH.RequiresUpload = 1 AND

LEN(ANH.SalesOrderNumber) = 6 AND

LEN(ANH.AdviceNoteNumber) <=6 AND

SOH.LitePick = 0

GROUP BY

ANH.Company,

ANh.Location,

ANH.Warehouse,

ANH.AdviceNoteCreationDate,

ANH.SalesOrderNumber,

SOH.PromixRefNumber,

ANH.AdviceNoteNUmber,

ANL.productNumber,

ANL.QuantityUOM,

ANL.WeightUom,

SOH.CustomerCode,

SOH.CustomerOrderNumber,

ANL.AdviceNoteNumber

IF @@ERROR <> 0

BEGIN

GOTO LOCALERROR

END

INSERT INTO @DETAILS

SELECT(@FOOTCHARACTER + @Seperator + @DISPLAYWAREHOUSE + @Seperator + CAST((SELECT COUNT(*) + 1 FROM @DETAILS) AS VARCHAR(6))),0,@FOOTCHARACTER

IF @@ERROR <> 0

BEGIN

GOTO LOCALERROR

END

--Cursor for Line Details

OPEN csrLINE

IF @@ERROR <> 0

BEGIN

GOTO LOCALERROR

END

FETCH NEXT FROM csrLINE INTO @LINE

IF @@ERROR <> 0

BEGIN

GOTO LOCALERROR

END

WHILE @@FETCH_STATUS =0

BEGIN

PRINT @LINE

FETCH NEXT FROM csrLINE INTO @LINE

IF @@ERROR <> 0

BEGIN

GOTO LOCALERROR

END

END

--Cursor For Advice Notes

OPEN csrADVICENOTENUMBER

IF @@ERROR <> 0

BEGIN

GOTO LOCALERROR

END

FETCH NEXT FROM csrADVICENOTENUMBER INTO @ADVICENOTENUMBER

IF @@ERROR <> 0

BEGIN

GOTO LOCALERROR

END

WHILE @@FETCH_STATUS = 0

BEGIN

UPDATE AdviceNoteHeaders

SET RequiresUpload = 0

WHERE Company= @Company AND Location= @Location AND Warehouse= @Warehouse and AdviceNoteNumber= @AdviceNoteNumber

IF @@ERROR <> 0

BEGIN

GOTO LOCALERROR

END

FETCH NEXT FROM csrADVICENOTENUMBER INTO @ADVICENOTENUMBER

IF @@ERROR <> 0

BEGIN

GOTO LOCALERROR

END

END

SET NOCOUNT OFF

COMMIT TRANSACTION

LOCALEXIT:

CLOSE csrLINE

DEALLOCATE csrLINE

CLOSE csrADVICENOTENUMBER

DEALLOCATE csrADVICENOTENUMBER

RETURN @@ERROR

LOCALERROR:

ROLLBACK TRANSACTION

GOTO LOCALEXIT