[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 452
  • Last Modified:

bcp destination file has some blank lines

Hi experts, can anybody tell me why I got an extra return carriage character at line num 3088, in my bcp text file?
I alunch the bcp as below:
bcp "exec sp_storedname 201106" queryout..............................
the sp_storedprocedure is like that:



SELECT    
a+  
b+
c+
d+
e
as RigaOut
FROM      
tablename


tablenames does not contais rows with null values
0
dionisio_79
Asked:
dionisio_79
  • 3
1 Solution
 
AndyAinscowCommented:
Just a guess - does field 'e' contain a return char ?
0
 
dionisio_79Author Commented:
no last filed is do not contains return chars. It is the result of the following function

create FUNCTION [dbo].[fn_SFormatImportoVirgola13] (@varVal sql_variant)
RETURNS VARCHAR(32)
AS
BEGIN

  DECLARE @segno CHAR(1)
  DECLARE @lung INT
  DECLARE @importo VARCHAR(32)

  if @varVal is null
    begin
      set @importo = null
      goto Exit_Function
    end


  SET @importo = CAST(@varVal AS VARCHAR(32))
  SET @importo = dbo.fn_SDecToVar((dbo.fn_SVarToDec(@importo)))
  SET @importo = LTRIM(@importo)
 

  IF charindex('+', @importo) = 0
      and charindex('-', @importo) = 0
      set @segno = '+'
  else
      begin
            SET @segno = LEFT(@importo, 1)
            set @importo = replace(@importo,'+','')
            set @importo = replace(@importo,'-','')
      end
      
     
      IF charindex('.', @importo) = 0
        SET @importo = @importo + '.00'
      ELSE
        BEGIN
          -- Se la parte decimale e' composta da una sola cifra devo aggiungere uno zero finale

          IF (len(right(@importo, len(@importo) - charindex('.', @importo))) = 1)
            SET @importo = @importo + '0'
        END
 

     
      IF @segno = '-' OR @segno = '+'
        BEGIN
          SET @lung = LEN(@importo)
          SET @importo = RIGHT(@importo, @lung)
        END
      ELSE
        BEGIN
          SET @segno = '+'
          SET @lung = LEN(@importo)+1
        END

      SET @importo = @segno + REPLICATE('0', 16-@lung) + @importo
        set @importo = replace(@importo,'.',',')


Exit_Function:
  RETURN @importo
END

GO


0
 
Andrei FomitchevCommented:
It is a bug I couldn't get rid of. It looks like an internal BCP buffer size.
I wasted a lot of time Googling and trying different param combinations. I resulted with writing export with C#. Don't waste your time - use C# or VB or C++.
0
 
dionisio_79Author Commented:
found out it was a null string in the input.
Thank you anyway
0
 
dionisio_79Author Commented:
I had a null value in the input string. NULL + string + null = null
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now