June 9, 2008 at 6:07 am
Hi All:
I'm trying to create an SP to prompt for a filename and perform a bulk insert and having some problems with it. I've tried several variations to this without any luck. Seems no matter what I do, I keep getting this error
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'FROM'.
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'FROM'.
(1 row(s) affected)
Here is the SP listing:
ALTER PROCEDURE [dbo].[VerifiedBulkInsert]
--
(
@DataFileName varchar(100) = null
)
--
ALTER PROCEDURE [dbo].[VerifiedBulkInsert]
--
(
@DataFileName varchar(100) = null
)
--
AS
begin
DECLARE @SQL VARCHAR(8000)
SET @SQL = '
Bulk Insert VERIFIED3
FROM ''' + @DataFileName + '''
WITH
(FORMATFILE = ''C:\temp\VERIFIED.fmt'')'
End
EXEC (@sql)
Thanks for any assistance on this
Bill Dillon
June 10, 2008 at 11:29 am
It looks like you have 1 too many single quotes in:
FROM ''' + @DataFileName + '''
I find using several single quotes together very hard to eyeball. I usually do something like this:
DECLARE
@SQL VARCHAR(MAX)
, @Tic VARCHAR(1)
SET @Tic = CHAR(39)
SET @SQL = ' INSERT INTO TheTable (NumCol, CharCol)'
+ ' SELECT 1, ' + @Tic + 'Some Text' + @Tic
It makes the code a bit longer, but at least you don't lose your place with how many single quotes you've got.
Todd Fifield
June 11, 2008 at 12:26 pm
Yup, sure did. I found where to quote and how many to quote to be a pain. What helped the was adding a print @sql to acutally see what the string being built looked like. If it helps anyone else out, here is a complete working version too.
Thanks for the assistance
Bill
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[VerifiedBulkInsert]
--
(
@DataFileName varchar(100) = null
)
--
AS
begin
DECLARE @SQL VARCHAR(8000)
SET @SQL = '
Bulk Insert [VERIFIED3] FROM ''C:\webcontent\bendmailing.com\ftproot\verified\' + @DataFileName + ''' WITH (FORMATFILE = ''C:\webcontent\bendmailing.com\VERIFIED.fmt'')'
print @sql
exec (@sql)
End
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply