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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.