Sql-server – Error in “BCP IN” operation

bcpsql serversql-server-2012

I have a table in 2 different database.

In Database_A, there is a table created as:

CREATE TABLE tbl_Employee(
    E_Id int null
    ,E_Name varchar(24) not null
    ,Joining_Date datetime not null
    )

In Database_B, the same table is created as:

CREATE TABLE tbl_Employee(
    E_Id int null
    ,E_Name varchar(24) not null
    ,Joining_Date datetime null
    )

The difference is, the column Joining_Date is NOT NULL in Database_A but it allows NULL in the Database_B.

Now when I export data from the table Database_A.dbo.tbl_Employee
using command

bcp "select * from Database_A.dbo.tbl_Employee" queryout "D:\test.txt" -S Server_Name -n -T

and import into Database_B.dbo.tbl_Employee using command

bcp "Database_B.dbo.tbl_Employee2" in "D:\test.txt"-S Server_Name -n -T

It raises an Error:

SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Server Native Client 11.0]Unexpected EOF encountered in
BCP data-file

I can not alter the column to NOT NULL on Database_B

Best Answer

Try using with -c which is character instead of -n native

bcp "select * from Database_A.dbo.tbl_Employee" queryout "D:\test.txt" -S Server_Name -c -T

bcp "Database_B.dbo.tbl_Employee" in "D:\test.txt"-S Server_Name -c -T

Or an alternative is to create a format file.

Create format file syntax is:

bcp Database_A.dbo.tbl_Employee format -null -f D:\BCPFormat.fmt -n -T

Export as normal with native:

bcp "select * from Database_A.dbo.tbl_Employee" queryout "D:\test.txt" -S Server_Name -n -T

Import with -k keeps nulls and format file, no -n flag needed.

bcp Database_B.dbo.Employee in "D:\Test.txt" -S Server_Name -f D:\BCPFormat.fmt -k -T

Related Solutions

Exporting Access DB to SQL Server: Unexpected EOF Error

First, use tab delimited files unless you want to use -t to specify a different delimiter (such as a comma). By default it will accept the tab.

Next, unless you're exporting data with the -n flag, use -c to import the data.

Finally, based on your comments, it looks like there is a mis-match between the size of your table columns and the actual width of the data. That's why you're getting notified the data is being truncated. Match those two up and you'll be all set.

You can check out the MSDN page on bcp for more information on flags, etc.

Sql-server – Use Distinct With Cast As XML

You have 2 issues that I can see:

You cannot pass a VARCHAR(MAX) to xp_cmdshell, largest you can pass is VARCHAR(8000) or NVARCHAR(4000), as per the xp_cmdshell documentation here.
Your temp table (and your Table variable for that matter) are out of scope for other connections, so bcp won't be able to see it.

I worked around these issues with the following:

DECLARE @TestData TABLE (
    pinkpurpleblue VARCHAR(MAX)
)

INSERT INTO @TestData (pinkpurpleblue) VALUES ('xx11'), ('rr22'), ('xx11'), ('rr22')

CREATE TABLE ##distinctValues (
    pinkpb VARCHAR(MAX)
)

INSERT INTO ##distinctValues (pinkpb) SELECT DISTINCT pinkpurpleblue FROM @TestData

DECLARE @file VARCHAR(200), @sql VARCHAR(8000)
SET @file = 'C:\Prod\Results\testing.csv'
SET @sql = 'bcp "SELECT ''pinkpurpleblue'' UNION ALL SELECT CAST(pinkpb AS XML) FROM ##distinctValues" queryout "' 
SET @sql = @sql + @file + '" -c -t, -T -S '+@@SERVERNAME
EXEC MASTER..xp_cmdshell @sql 

DROP TABLE ##distinctValues

Here, I make sure that the type of @sql is VARCHAR(8000) rather than VARCHAR(MAX) and I use a global temp table instead of a local one, so that bcp will be able to see it.

Best Answer

Related Solutions

Exporting Access DB to SQL Server: Unexpected EOF Error

Sql-server – Use Distinct With Cast As XML

Related Question