Thanks! I should have looked at the quotes closer...the @debug var is a
good idea too I've had a lot of trouble trying to debug in the past.

Carter



"Simon Hayes" <sql@hayes.ch> wrote in message
news:40d09fda$1_2@news.bluewin.ch...
>
> "me" <me@work.com> wrote in message
> news:10d1737o5hjfd50@corp.supernews.com...
> > Here is probably where the problem is per your suggestion the actual sp
> code
> > follows:
> > exec ('BULK INSERT SHIPPING.DBO.ItemTest FROM '+ @ImportFile +' WITH
> > (FORMATFILE ='+ @FormatFile +')')
> >
> > here is the sp code:
>
> <snip>
>
> If you check the examples in Books Online, you'll see that the filename
has
> single quotes around it:
>
> ... FROM 'f:\orders\lineitem.tbl' ...
>
> But your variable substitution will not have any quotes:
>
> ... FROM \\ACCT_NT\SHARED\CBL\ITEMTEST.txt ...
>
> So you need to add them:
>
> exec ('BULK INSERT SHIPPING.DBO.ItemTest FROM '''+ @ImportFile +''' WITH
> (FORMATFILE ='''+ @FormatFile +''')')
>
> (That's 3 single quotes in each case, in case your font isn't quite
clear).
> An easier way to troubleshoot this is:
>
> declare @sql nvarchar(1000)
> set @sql = 'BULK INSERT SHIPPING.DBO.ItemTest FROM '''+ @ImportFile +'''
> WITH (FORMATFILE ='''+ @FormatFile +''')'
> if @debug = 1 print @sql
> exec(@sql)
>
> Now you can add a parameter to your procedure called @debug:
>
> @debug bit default 0
>
> If you get strange syntax errors, you can execute the procedure like this,
> and you will see the SQL string, which will hopefully make the problem
more
> obvious:
>
> EXEC shipping.dbo.pts_ImportItems @ImportFile = 'c:\temp\in.txt', @debug =
1
>
> Simon
>
>