SQL Stored Procedure Bulk Load Syntax Error

I am creating a stored procedure to bulk load a fixed length text file into a SQL table.  I want to pass the name and location of the file to be imported as one parameter and th ename and location of the FORMAT FILE as another parameter.

I created the SP but I'm getting a syntax error on the openrowset statement

Msg 102, Level 15, State 1, Procedure aImportspMuni_DS_BulkInSPORC, Line 27
Incorrect syntax near '@ImportFileNameAndLoc'.

I don't see the error.  Can anyone sport it?

Here's the entire SPROC.
USE [JTSConversion]
GO
/****** Object:  StoredProcedure [dbo].[aImportspMuni_DS_BulkInSPORC]    Script Date: 3/10/2018 11:08:00 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- 
-- =============================================
Alter PROCEDURE [dbo].[aImportspMuni_DS_BulkInSPORC]
				@MuniCode				 Int,
				@ImportFileNameAndLoc    nvarchar(250),
				@FormatFileNameAndLoc    nvarchar(250)   
AS
BEGIN
    SET NOCOUNT ON;
	--
	-- Clear the table to receive the import
	--
	Execute dbo.spDeleteAllRecsFromSQLTable 'SQDS_PropertyDesc_BulkImport'   -- clear bulk load

	--
	--  Now load Bulk Table
	--
	Insert into SQDS_PropertyDesc_BulkImport
	select * FROM OPENROWSET( BULK @ImportFileNameAndLoc,FORMATFILE = @FormatFileNameAndLoc) as a

End
--
--  Parse Bulk Loaded Fields Into Import Table
--
Begin

	Execute dbo.spDeleteAllRecsFromSQLTable 'SQDS_PropertyDesc_Import'   -- clear Import

	INSERT INTO [dbo].SQDS_PropertyDesc_Import (
		ControlNumber,
		SequenceNumber,
		Description
		)
	SELECT
		SUBSTRING(BulkLoad,  1, 7) AS ControlNumber,
		SUBSTRING(BulkLoad,  8, 3) AS SequenceNumber,
		SUBSTRING(BulkLoad, 11,50) AS Description
	FROM SQNA_NameAddress_BulkLoad 



End

Open in new window

LVL 1
mlcktmguyAsked:
Who is Participating?
 
Mark WillsTopic AdvisorCommented:
Couple of comments...

>> Execute dbo.spDeleteAllRecsFromSQLTable 'SQDS_PropertyDesc_BulkImport'

hope that is just doing a truncate table :)

You will need dynamic SQL for your openrowset. By way of example
declare @source varchar(200) = 'c:\mrwtest\ee\TQD2FILE.txt'
declare @format varchar(200) = 'c:\mrwtest\ee\tqdtfile_format.txt'
declare @sql varchar(max)

set @sql = 'insert yourtable
select * from openrowset( bulk '''+@source+''' ,FORMATFILE = '''+@format+''') as a'

print @sql

exec (@sql)

Open in new window

0
 
Nitin SontakkeDeveloperCommented:
I haven't used this feature but the documentation (https://docs.microsoft.com/en-us/sql/t-sql/functions/openrowset-transact-sql) suggests that filename should be literal. If that is the case you probably will not be able to use expression there.

Just to give you an example, you cannot have

declare @tablename varchar(1000) = 'tblCustomer';
select * from @tablename;

If this is what you want to do, you may have to resort to dynamic SQL instead.

Also failed to understand your begin...end begin...end construct. Confusing.

Also can you just not truncate the table instead of calling a stored procedure to just do that job? Looks like there is a dynamic Sql there.
0
 
mlcktmguyAuthor Commented:
Yes the called procedure executes a truncate.  Not much need to use that SP in SQL but I use it when I want to clear a SQL table from within my Access program.

Thanks the dynamic SQL you posted works as I wanted.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.