Hello
I've tried to modify some code found on the web to import multiple files
into a table, where I need to include a format file in the bulk insert
syntax...I've tried everything but I can't get the syntax right for the file
path to the format file.  Greatly appreciated if someone could set me in the
right direction..thx  The line in question is 6th from the bottom.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[usp_ImportMultipleFiles] @filepath varchar(500),
@pattern varchar(100), @TableName varchar(128)
as
set quoted_identifier off
declare @query varchar(1000)
declare @max1 int
declare @count1 int
Declare @filename varchar(100)
set @count1 =0
create table #x (name varchar(200))
set @query ='master.dbo.xp_cmdshell "dir '+@filepath+@pattern +' /b"'
insert #x exec (@query)
delete from #x where name is NULL
select identity(int,1,1) as ID, name into #y from #x
drop table #x
set @max1 = (select max(ID) from #y)
--print @max1
--print @count1
While @count1 <= @max1
begin
set @count1=@count1+1
set @filename = (select name from #y where [id] = @count1)
set @Query ='BULK INSERT '+ @Tablename + ' FROM "'+ @Filepath+@Filename+'"
WITH (FORMATFILE = "'C:\Lean_schema_id.txt'", firstrow = 2)'
--print @query
exec (@query)
insert into logtable (query) select @query
end
drop table #y

Dale (dale@nospam.com) writes:
When working with dynamic SQL, it's always a good idea to add a
parameter like:

   @debug bit = 0

and then in the code:

   IF @debug = 1
      PRINT @query

so that you can see the code that gives you problems.

There may be more than one error in the code, but this one I spotted:

When QUOTED_IDENTIFIER is ON (and this is the preferred setting), there
is only string delimiter in T-SQL, and that is the single quote('). Here
you try to use double quotes(") as well, but that does not fly. When
you nest strings like here, you need to double the nested single quotes
to include them in the outer strings.

If you are uncertain of the syntax of BULK INSERT as such, Books Online
is a great place to look in.

Stored Procedure Help
Conversion failed when converting datetime from character string.
Variable table name in a cursor declaration
Comparing two tables
Is there an eval() function in sql server 2000?