[dba-SQLServer] Is there any chance?

John,
  I know that EXEC @SQL command won't evaluate syntax errors until the
statement is called.  If you run the pure SQL

BULK INSERT [_DataEmailDD].[dbo].[ConsEmai
l_AK] FROM '\\Azul\PSM\Data\_DataEmailDD\ConsEmail_AK.CSV' WITH
(FORMATFILE= '\\Azul\PSM\data\_DataEmailDD\EmailImpFormat.xml')


what is the error message?

also, is there any reason for using EXEC over sp_executesql?  from what I
understand sp_executesql is more efficient, in that if you are making
similar calls to the query engine but varying  minor values (ala variables)
the engine will try to use the previous query plan as much as possible.

-Francisco
http://bit.ly/sqlthis   | Tsql and More...
<http://db.tt/JeXURAx>




On Mon, May 21, 2012 at 3:17 PM, jwcolby <jwcolby at colbyconsulting.com>wrote:

> I am trying to get Bulk Insert (import) working with CSV files (","
> delimited) using a format file.  I have about 60 files with around 250
> million total records that I need to import.  Just using the built-in
> wizard works but is a royal PITA because I have to modify each and every
> time to widen the fields to 250 characters, build each file by hand etc.
>
> So I have a SP that creates the tables.  I then use the following to try
> to do the import:
>
> USE [_DataEmailDD]
> GO
> /****** Object:  StoredProcedure [dbo].[sp_AZIn_BCPInOneFile]    Script
> Date: 05/21/2012 17:19:43 ******/
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> -- ==============================**===============
> -- Author:              <Author,,Name>
> -- Create date: <Create Date,,>
> -- Description: <Description,,>
> -- ==============================**===============
> ALTER PROCEDURE [dbo].[sp_AZIn_BCPInOneFile]
>        -- Add the parameters for the stored procedure here
>        @DBName varchar(50), @TblName varchar(50),
>        @FilePath varchar(1000), @FileName varchar(255)
>
> AS
> BEGIN
>        -- SET NOCOUNT ON added to prevent extra result sets from
>        -- interfering with SELECT statements.
>        SET NOCOUNT ON;
>
>    -- Insert statements for procedure here
> declare @sql varchar(8000)
> DECLARE @FileSpec varchar(1000)
>
> Declare @ErrorDesc varchar(4000)
> Declare @ErrorNo int
> Declare @RecsAffected int
>
>        set @filespec = @FilePath
>        set @filespec = @filespec + @FileName
>        begin try
>                set @SQL = 'BULK INSERT [' + @DBName + '].[dbo].[' +
> @TblName + '] FROM '  + '''' + @filespec + ''''  + ' WITH (FORMATFILE= ' +
> '''' + '\\Azul\PSM\data\_DataEmailDD\**EmailImpFormat.xml' + '''' + ')'
>
>                print @SQL
>                exec (@SQL)
>                select @RecsAffected = @@RowCount
>                select @ErrorDesc = 'Success'
>                select @ErrorNo = 0
>                return 0
>        end try
>        begin catch
>                select @ErrorNo = @@Error
>                select @ErrorDesc = ERROR_MESSAGE()
>                print 'There was a BCP error IMPORTING data into ' + @DBName
>                print ERROR_MESSAGE()
>                return -1
>        end catch
>        --
>
> Standard stuff except for the SQL Statement which when printed gives me
> the following:
>
> BULK INSERT [_DataEmailDD].[dbo].[**ConsEmail_AK] FROM
> '\\Azul\PSM\Data\_DataEmailDD\**ConsEmail_AK.CSV' WITH (FORMATFILE=
> '\\Azul\PSM\data\_DataEmailDD\**EmailImpFormat.xml')
>
> and execution thereof gives me the infamous (and totally useless)
>
> There was a BCP error IMPORTING data into _DataEmailDD
> Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
>
> It just seems like my chance of making this work is pretty minimal.
>
> I have spent about 4 hours Googling to even get as far as I am and the
>
> Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
>
> returns about a million entirely different pages for a million entirely
> different problems.  It is as if any problem whatsoever returns this
> message.
>
> So, what do you say?  Is this a "you gotta be an expert so forget it"
> kinda deal?  All of the various web pages explaining how to do this make it
> sound so easy but of course no one mentions how to troubleshoot the mess.
>
> --
> John W. Colby
> Colby Consulting
>
> Reality is what refuses to go away
> when you do not believe in it
>
> ______________________________**_________________
> dba-SQLServer mailing list
> dba-SQLServer@**databaseadvisors.com <dba-SQLServer at databaseadvisors.com>
> http://databaseadvisors.com/**mailman/listinfo/dba-sqlserver<http://databaseadvisors.com/mailman/listinfo/dba-sqlserver>
> http://www.databaseadvisors.**com <http://www.databaseadvisors.com>
>
>