August 10, 2011 at 10:45 am
Hi - I'm trying my hand at some BCP and am getting this error.
My bcp statement is
bcp "EXEC WMS.dbo.spStockDespatches 'K','88','88'" queryout \\dfs\sites\\Despatches_201108101739.dat -c -t , -T -Slocalhost
and the stored proc produces data in the following format - do you need column headings in your output in order to use bcp.
F|71|29-07-2011|12:43:32
R|K|71|71|29/07/2011 12:44:00|302645|302645|302645|17710|1|CASE|4.8|KGS|17710110617|07/10/2011|SVCD0000|301-97898
R|K|71|71|29/07/2011 12:44:00|302645|302645|302645|17739|1|CASE|5.6|KGS|17739110719|04/01/2012|SVCD0000|301-97898
R|K|71|71|29/07/2011 12:44:00|302645|302645|302645|20109|3|CASE|7.2|KGS|20109110518|20/12/2011|SVCD0000|301-97898
R|K|71|71|29/07/2011 12:44:00|302645|302645|302645|21093|2|CASE|12.8|KGS|21093110719|08/11/2011|SVCD0000|301-97898
August 11, 2011 at 6:28 am
The last SELECT from your SP must be from a # table.
BCP evaluates output by recompiling procedure, before it's executed.
At the time of compilation temp table does not exist yet, therefore there is no output.
_____________
Code for TallyGenerator
August 11, 2011 at 6:51 am
Thanks.
I'm using a table variable rather than a # table. I have bcp working with a stored proc that uses a table variable, but for that file the first row was the column names. I think this is to do with the file format above not having column headers.
I am using a cursor in the sp though - do you know if that would cause a problem?
August 16, 2011 at 2:46 am
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
August 16, 2011 at 5:52 am
I cannot find a SELECT statement which would return data for bcp
_____________
Code for TallyGenerator
August 16, 2011 at 9:03 am
so the problem is where the cursors are being used then? These need to be selecting data?
August 16, 2011 at 4:43 pm
You're using cursor to populate a table.
But you never return the data from that table.
_____________
Code for TallyGenerator
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply