April 3, 2009 at 2:33 am
We are getting a problem when using BCP.
When we are exporting VARCHAR fields, and inside this fields we have no chars (NO NULLs, NO BLANKS), then we have an '00' HEX in the result file.
I'll try to give you information to test.
-- CREATE TABLE TEST
USE [tempdb]
GO
/****** Object: Table [dbo].[PRUEBA] Script Date: 04/03/2009 09:35:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PRUEBA](
[Identificador] [int] NOT NULL,
[Descripcion] [varchar](50) NOT NULL,
[FechaModificacion] [datetime] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
-- POPULATE THE TABLE
INSERT INTO PRUEBA
VALUES (1, '', GETDATE())
GO
INSERT INTO PRUEBA
VALUES (2, 'data', getdate)
-- EXPORT THE DATA TO A FILE
BCP temp.dbo.PRUEBA out salida.txt -f c:\pruebas\prueba.fmt -S ibquest01 -T
We use this fmt file to give format to the ouput file
C:\PRUEBAS\PRUEBA.FMT
9.0
3
1 SQLCHAR 0 4 "" 1 Identificador ""
2 SQLCHAR 0 30 "" 2 Descripcion Modern_Spanish_CI_AS
3 SQLCHAR 0 10 "\r" 3 FechaModificacion ""
And we obtain this output file: SALIDA.TXT
You can check, than in the row 1, col 5, there is an '00' hex when we expect an '32' hex (blank char)
Where is the problem?
What must we change?
Is this a known problem in SQL Server 2005?
Any help will be be very wellcome.
Thanks!
April 6, 2009 at 1:45 am
Excuse me, but i need your help.
We are having problems charging this ouptut file in a Teradata Environment, and in a DDM database.
Do you know if this is a known bug?
We have noticed that if we change the export with a view an one CHAR conversion then there is no problem.
Do we have to change all our export proccess?
Again, any help will be very wellcome.
April 23, 2009 at 7:54 am
Anyone can help us with this topic.
We have opened an incident with Microsoft and they tell us that this is the overall performance of the bcp program; and sended us to this note of the KB:
But we have tried this:
a) Change the varchar field to admit null
b) Insert a record whit the null value in the varchar field
c) Export the table using BCP and the format file specified in the beginning of this topic
And we have obtained the next result:
1) Record with null value is exported as all blank characters (HEX 20, ASCII 32)
2) Record with empty string (LEN(FIELD) = 0) is exported with the first char HEX 00, and the rest are blank characters (HEX 20, ASCII 32)
Is it a normal behavior?
Is it a bug, and there is a solution to this problem?
We have a lot of export processes (several hundreds) and we don't want to change all unless it is inevitable.
Is there a solution to our problem?
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply