bcp bulk insert - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

bcp bulk insert

Author

Topic

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2009-06-10 : 10:06:14

I would like to be able to copy using T-SQL data out of a table to a file so it can be imported at a later date. The tables use many datatypes. So far I have tried the following. The export seems to do something however I get the error listed below when I try and import. It was my understanding that -N (widenative) would keep datatypes intact. Does it also keep the fields intact or do I need filed and row escape characters, if so what are they? I am exporting from mytable and importing into a temp table. From there I would insert/update back to mytable. I had to remove xp cmd shell from below to get it to post? I have also tried the export/import to excel from the script lib however this does not seem to deal with bit or datetime datatypes for example.

USE [VC]

GO

/****** Object: StoredProcedure [dbo].[Export] Script Date: 06/10/2009 16:27:57 ******/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================

ALTER PROCEDURE [dbo].[Export]

As

Declare @sql nvarchar(max), @data_file nvarchar(255)

--Set Machine Name
Declare @MachineName nvarchar (255)
Set @MachineName = (SELECT Convert(nvarchar (100), MachineName) + '\XTRALIS_CYCLOPS'
From (SELECT SERVERPROPERTY ('MachineName') As MachineName) as t)

Set @data_file = 'C:\Program Files\Xtralis Pty Ltd\Video Central Platinum Server\VCPDataRoot\VCPRS\Exports\test_wideData.dat'

Set @sql='exec master..shell ''bcp VC..UserDetails out "'+@data_file+'" -N -T -S "'+@MachineName+'"'''

Print @sql

Exec(@sql)

USE [VC]
GO
/****** Object: StoredProcedure [dbo].[Import] Script Date: 06/10/2009 10:34:47 ******/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================

ALTER PROCEDURE [dbo].[Import]

As

Create Table #t ([UserID] int, [FirstName] nvarchar(50), [MiddleName] nvarchar(50), [LastName] nvarchar(50), [Initials] nvarchar(50), [LoginName] nvarchar(50), [LoginPassword] nvarchar(50), [IsDeleted] bit, [ProfileXMLFile] varbinary(MAX), [UserImageFileName] nvarchar(MAX), [UserNotes] nvarchar(MAX), [LastLoginTime] DateTime, [BlogEntryID] int)

BULK INSERT #t

FROM 'C:\Program Files\Xtralis Pty Ltd\Video Central Platinum Server\VCPDataRoot\VCPRS\Exports\test_wideData.dat'
WITH
(DATAFILETYPE='widenative');

Select * From #t

Drop Table #t

RETURN

Errors:

Msg 4866, Level 16, State 7, Procedure Import, Line 14

The bulk load failed. The column is too long in the data file for row 1, column 8. Verify that the field terminator and row terminator are specified correctly.

Msg 7399, Level 16, State 1, Procedure Import, Line 14

The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.

Msg 7330, Level 16, State 2, Procedure Import, Line 14

Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

asgast
Posting Yak Master

149 Posts

Posted - 2009-06-10 : 11:19:18

show us the 1st row in our exported file, there is a problem with it

you can try by specifying field terminators for our export file and then use the same terminator in importation

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2009-06-10 : 19:49:27

If I open the file in wordpad I get the following:

T e s t e r ÿÿ B u g ÿÿ
a d m i n p a s s w o r d ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ t e s t ÿÿ t e s t ÿÿ t e s t t e s t ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ
S t e v e M i d d l e N a m e L a s t N a m e a a P a s s w o r d ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ U s e r N o t e s ÿÿ
S t e v e M i d d l e N a m e L a s t N a m e b b P a s s w o r d ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ U s e r N o t e s ÿÿ
S t e v e M i d d l e N a m e L a s t N a m e c c P a s s w o r d ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ U s e r N o t e s ÿÿ
S t e v e M i d d l e N a m e L a s t N a m e d d P a s s w o r d ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ U s e r N o t e s ÿÿ
S t e v e M i d d l e N a m e L a s t N a m e e e P a s s w o r d ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ U s e r N o t e s ÿÿ
S t e v e M i d d l e N a m e L a s t N a m e f f P a s s w o r d ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ U s e r N o t e s ÿÿ q q q q q q ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ q ÿÿ
s s s s s s s s ÿÿÿÿÿÿÿÿ f i l e n a m e U s e n o t e s „›
r ÿÿ r ÿÿ r r ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ

asgast
Posting Yak Master

149 Posts

Posted - 2009-06-11 : 04:22:48

i see that 1st row has a carriage return symbol sql sees it as an end of row => row is broken in 2 columns don't have enough values => error

try manually fixing the file, if it imports after that ou have found your problem

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources