bulk insert error

• Sergio Campos

  SSC-Addicted

  Points: 459

  More actions

  August 29, 2005 at 4:03 am

  #165036

  hi, iam trying to impor a csv file but i get this err:

  requirement '@statement' type 'ntext/nchar/nvarchar'.

  any help plz?

  this is the code:

  CREATE PROCEDURE [wr_importar_detalles] AS

  declare @cad as varchar(8000)

  declare @fichero as varchar(200)

  set @Fichero='prueba.csv'

  set @cad='BULK INSERT webmisco.dbo.wr_primero FROM ''d:\transferencia$\web\reports\'+@Fichero+' with (DATAFILETYPE = ''char'', FIELDTERMINATOR = '','',CODEPAGE=''850'')'

  print @cad

  execute sp_executesql @cad

  Go

• Ray M

  SSC-Insane

  Points: 21093

  More actions

  August 29, 2005 at 4:57 pm

  #586014

  sp_executesql requires that the sql statement to be executed is of datatype 'ntext/nchar/nvarchar'

  Changes in Bold the Capitol 'N' tells sql to convert string to binary, but the conversion is implicit if you do not put the N there

  CREATE PROCEDURE [wr_importar_detalles] AS

  declare @cad as nvarchar(4000)

  declare @fichero as varchar(200)

  set @Fichero='prueba.csv'

  set @cad=N'BULK INSERT webmisco.dbo.wr_primero FROM ''d:\transferencia$\web\reports\'+@Fichero+' with (DATAFILETYPE = ''char'', FIELDTERMINATOR = '','',CODEPAGE=''850'')'

  print @cad

  execute sp_executesql @cad

   

• vadba

  SSChampion

  Points: 11133

  More actions

  August 30, 2005 at 6:10 am

  #586094

  The BULK INSERT statement will implicitly convert varchar to nvarchar if necessary.

  The problem is you're missing a single quote after the filename.

  Change this:

  set @cad='BULK INSERT webmisco.dbo.wr_primero FROM ''d:\transferencia$\web\reports\'+@Fichero+' with (DATAFILETYPE = ''char'', FIELDTERMINATOR = '','',CODEPAGE=''850'')'

  To this:

  set @cad='BULK INSERT webmisco.dbo.wr_primero FROM ''d:\transferencia$\web\reports\' + @Fichero + ''' with (DATAFILETYPE = ''char'', FIELDTERMINATOR = '','',CODEPAGE=''850'')'

• Rob Sanguin

  SSChasing Mays

  Points: 610

  More actions

  August 31, 2005 at 9:09 am

  #586509

  I think it's actually a double quote that's missing. Try this:-

  set @cad='BULK INSERT webmisco.dbo.wr_primero FROM ''d:\transferencia$\web\reports\'+@Fichero+' with (DATAFILETYPE = ''char'', FIELDTERMINATOR = '','',CODEPAGE=''850'')'

  should be

  set @cad='BULK INSERT webmisco.dbo.wr_primero FROM ''d:\transferencia$\web\reports\'+@Fichero+'" with (DATAFILETYPE = ''char'', FIELDTERMINATOR = '','',CODEPAGE=''850'')'