BCP IN error: #@ Row 1, Column 1: String data, right truncation @#

Question

Post reply

BCP IN error: #@ Row 1, Column 1: String data, right truncation @#

Kari Suresh

Hall of Fame

Points: 3712
More actions
July 21, 2010 at 12:36 am

#139919

[p]Hi,
I am able to do BCP OUT successfully, but failing when BCP IN from flat files to tables. My BCP OUT query creating flat file, which is having data of server name. In this case data is 0GCC0501921\SQLSERVER2008. I am using the following query to do BCP IN..DECLARE @cmd varchar(1000), @result int
SET @cmd = 'BCP BIW.dbo.BIW_Stage_Dim_DatabaseServer IN E:\temp\BIW_Stage_Dim_DatabaseServer.txt -N -SGCC0501921\SQLSERVER2008 -Ulogin -Ppassword -eE:\temp\errorfile.txt'
EXEC @result = master.dbo.xp_cmdshell @cmd, no_output I am not getting any error while executing this query but data is not loading into tables. I noticed error in error log file: #@ Row 1, Column 1: String data, right truncation @#
GCC0501921\SQLSERVER2008.[/p][p]Then I suspected the character "\", removed manually and executed again. Now data is loading into table.[/p][p]How can I load same data into tables without removing any special characters like "\" ? [/p][p]Can anybody suggest me the best practice, Please?[/p]
KSB
-----------------------------
Thousands of candles can be lit from a single candle, and the life of the candle will not be shortened. Knowledge and happiness never decreases by being shared.” - Buddha

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

lakshmi2np Old Hand Points: 373 More actions · Answer 1

Hi,

You can use a square brackets [] like -S[GCC0501921\SQLSERVER2008] to specify the server name.

This is a good practice.

DECLARE @cmd varchar(1000), @result int

SET @cmd = 'BCP BIW.dbo.BIW_Stage_Dim_DatabaseServer IN E:\temp\BIW_Stage_Dim_DatabaseServer.txt -N -S[GCC0501921\SQLSERVER2008] -Ulogin -Ppassword -eE:\temp\errorfile.txt'

EXEC @result = master.dbo.xp_cmdshell @cmd, no_output

Thanks.:-)

Kari Suresh Hall of Fame Points: 3712 More actions · Answer 2

[p]Actually, they are using SELECT @@SERVERNAME to get the server name and storing into a table. Then I am doing BCP OUT to flat file. Next doing BCP IN to store into another table. Here only I am getting error. [/p]

KSB
-----------------------------
Thousands of candles can be lit from a single candle, and the life of the candle will not be shortened. Knowledge and happiness never decreases by being shared.” - Buddha

Lowell SSC Guru Points: 323504 More actions · Answer 3

lak's suggestion still stands; if you bracket the server name you'd fix the issue:

select quotename(@@SERVERNAME)

--or

select quotename(ColumnName) from SomeTable

you are probably building the bcp string, right? very simple to get the quotename function involved, or even ... + '[' + servername + ']' + .... in there to fix it.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Kari Suresh Hall of Fame Points: 3712 More actions · Answer 4

[p]Thanks for your response. It is working.[/p]

KSB
-----------------------------
Thousands of candles can be lit from a single candle, and the life of the candle will not be shortened. Knowledge and happiness never decreases by being shared.” - Buddha