Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

BCP host-files must contain at least one column Expand / Collapse
Author
Message
Posted Wednesday, August 10, 2011 10:45 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 1, 2012 9:56 AM
Points: 22, 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

Post #1157822
Posted Thursday, August 11, 2011 6:28 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, July 20, 2014 5:23 PM
Points: 4,576, Visits: 8,341
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.
Post #1158406
Posted Thursday, August 11, 2011 6:51 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 1, 2012 9:56 AM
Points: 22, 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?
Post #1158429
Posted Tuesday, August 16, 2011 2:46 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 1, 2012 9:56 AM
Points: 22, 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

Post #1160418
Posted Tuesday, August 16, 2011 5:52 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, July 20, 2014 5:23 PM
Points: 4,576, Visits: 8,341
I cannot find a SELECT statement which would return data for bcp
Post #1160465
Posted Tuesday, August 16, 2011 9:03 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 1, 2012 9:56 AM
Points: 22, Visits: 51
so the problem is where the cursors are being used then? These need to be selecting data?
Post #1160600
Posted Tuesday, August 16, 2011 4:43 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, July 20, 2014 5:23 PM
Points: 4,576, Visits: 8,341
You're using cursor to populate a table.
But you never return the data from that table.
Post #1160868
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse