1

I am attempting to build a stored procedure with variables that will change over time. on SP execution (Right clicking SP in programmability under the database and execute) results in

Error output:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'file20160303FROMx:'.

(1 row(s) affected)

I am really open to any feedback, be it a complete re-design if need be. Still a junior in this field.

Script:

USE [dbname]
GO
/****** Object:  StoredProcedure [dbo].[procname]    Script Date: 2016-03-03 09:45:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


 --=============================================
 --Author:      <Author,,Name>
 --Create date: <Create Date,,>
 --Description: <Description,,>
 --=============================================
 ALTER PROC [dbo].[procname]
@p1 nvarchar(8)
AS
BEGIN

SET NOCOUNT ON;
Declare @insertvariable NVARCHAR(MAX)
Declare @var1 NVARCHAR (32) = 'x:\xxxx\xxxx\file\err';
Declare @var2 NVARCHAR (15) = convert(varchar, getdate(),112) + '.' + replace(convert(varchar, getdate(),108),':','')
Declare @var3 NVARCHAR (MAX) = @var1 + @var2 + '.log';
Declare @var4  NVARCHAR(MAX)= 'x:\xxxx\xxxx\file';
Declare @var5 NVARCHAR(MAX) = @var2 + @p1 + '.tab.txt';
Declare @formatFile NVARCHAR (MAX) = 'x:\xxxx\xxxx\formatFile';

set @insertvariable =  N'
BULK INSERT dbname.dbo.tablename' + @p1 + 'FROM' + @var5 + 'WITH 
    (
   FIRSTROW = 1,
   ORDER (Id),
   CODEPAGE = Finnish_Swedish_CI_AS,
   FIELDTERMINATOR = \t,
   ROWTERMINATOR = 0x0a,
   TABLOCK,
   ERRORFILE = @var3,
   FORMATFILE = @formatFile, 
   KEEPNULLS
   );'
   exec sp_executesql @insertvariable, N'@p1 nvarchar, @var5 nvarchar, @var3 nvarchar, @formatFile nvarchar', @p1, @var5, @var3, @formatFile 
   end
3

You are missing spaces in your concatenated string and you also need to quote your filename. I also don't see the point of mixing concatenation and calling sp_executesql with parameters.

Try changing this part:

set @insertvariable =  N'
BULK INSERT dbname.dbo.tablename' + @p1 + 'FROM' + @var5 + 'WITH 
    (
   FIRSTROW = 1,
   ORDER (Id),
   CODEPAGE = Finnish_Swedish_CI_AS,
   FIELDTERMINATOR = \t,
   ROWTERMINATOR = 0x0a,
   TABLOCK,
   ERRORFILE = @var3,
   FORMATFILE = @formatFile, 
   KEEPNULLS
   );'
   exec sp_executesql @insertvariable, N'@p1 nvarchar, @var5 nvarchar, @var3 nvarchar, @formatFile nvarchar', @p1, @var5, @var3, @formatFile 
   end

Into this, mind the spaces where you concatenate @p1 and @var5 and single quotes:

set @insertvariable =  N'
BULK INSERT dbname.dbo.tablename ' + @p1 + ' FROM ''' + @var5 + ''' WITH 
    (
   FIRSTROW = 1,
   ORDER (Id),
   CODEPAGE = ''Finnish_Swedish_CI_AS'',
   FIELDTERMINATOR = \t,
   ROWTERMINATOR = 0x0a,
   TABLOCK,
   ERRORFILE = ''' + @var3 + ''',
   FORMATFILE = ''' + @formatFile + ''', 
   KEEPNULLS
   );'
   exec sp_executesql @insertvariable
   end

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.