SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


BCP host-files must contain at least one column


BCP host-files must contain at least one column

Author
Message
ricko
ricko
SSC-Enthusiastic
SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)

Group: General Forum Members
Points: 110 Visits: 51
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
Sergiy
Sergiy
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26598 Visits: 12489
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.
ricko
ricko
SSC-Enthusiastic
SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)

Group: General Forum Members
Points: 110 Visits: 51
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?
ricko
ricko
SSC-Enthusiastic
SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)

Group: General Forum Members
Points: 110 Visits: 51
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 @FOOTCHARACTER CHAR(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
FROM AdviceNoteHeaders 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
WHERE ANH.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


Sergiy
Sergiy
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26598 Visits: 12489
I cannot find a SELECT statement which would return data for bcp
ricko
ricko
SSC-Enthusiastic
SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)

Group: General Forum Members
Points: 110 Visits: 51
so the problem is where the cursors are being used then? These need to be selecting data?
Sergiy
Sergiy
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26598 Visits: 12489
You're using cursor to populate a table.
But you never return the data from that table.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search