Incorrect syntax near '+'. in SQL Server 2005

I have a stored procedure that works in SQL Server 2000.  I created a script to update the stored procedure, and it worked fine.  Now I have converted to SQL Server 2005 and am getting an error when I run the script.  The error I get is

Incorrect syntax near '+'.

Here is a snippet from the script.  The error points to the last line of this snippet.

DECLARE @OrganizationShortName varchar(26)
      SELECT @OrganizationShortName = (SELECT [Organization Short Name] From [dbo].[Organization Info] WHERE RecID = 1)
DECLARE @FileContents VARCHAR(8000)
CREATE TABLE #tempHTML (HTMLTEXT VARCHAR(8000))
BULK INSERT #tempHTML FROM 'D:\Inetpub\wwwroot\APER Survey Module\'+@OrganizationShortName+'\Email Docs\'+@OrganizationShortName+' Reminder After Deadline Email.html'

Why did this work in 2000 but will not work in 2005, and how do I modify the code to get it to work?
LVL 1
wsturdevAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
>The code ran.
which is part 1.

>The table has a Null value in HTMLTEXT.
bulk insert will insert and row- and column based data.
if you want to insert a html file, you have to add options to the BULK INSERT to make it believe the entire file is 1 row / 1 column.

ie, set the ROWTERMINATOR and COLUMNTERMINATOR to values that are not in the file
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
any better with this:

DECLARE @OrganizationShortName varchar(26)
SELECT @OrganizationShortName = [Organization Short Name] From [dbo].[Organization Info] WHERE RecID = 1
DECLARE @path VARCHAR(8000)
SET @path = 'D:\Inetpub\wwwroot\APER Survey Module\'+@OrganizationShortName+'\Email Docs\'+@OrganizationShortName+' Reminder After Deadline Email.html'
DECLARE @FileContents VARCHAR(8000)
CREATE TABLE #tempHTML (HTMLTEXT VARCHAR(8000))
BULK INSERT #tempHTML
 FROM @path


or this:

DECLARE @OrganizationShortName varchar(26)
SELECT @OrganizationShortName = [Organization Short Name] From [dbo].[Organization Info] WHERE RecID = 1
DECLARE @path VARCHAR(8000)
SET @path = 'D:\\Inetpub\\wwwroot\\APER Survey Module\\'+@OrganizationShortName+'\\Email Docs\\'+@OrganizationShortName+' Reminder After Deadline Email.html'
DECLARE @FileContents VARCHAR(8000)
CREATE TABLE #tempHTML (HTMLTEXT VARCHAR(8000))
BULK INSERT #tempHTML
 FROM @path


0
 
wsturdevAuthor Commented:
Sorry for the long delay in responding -- I finally got back to this problem.

Both of your suggestions produced an error:
Incorrect syntax near '@Path'.

So I tried this:
DECLARE @SQL VARCHAR(800)
SET @SQL = N'BULK INSERT #tempHTML FROM ' + @Path
EXEC (@SQL)

I no longer get a syntax error and the procedure runs, but the BULK INSERT never happens.

What should I be using in SQL Server 2005 to import an HTML file so I can use it in a SPROC?

0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you cannot bulk insert into a temp table, you have to make it a true table.
0
 
wsturdevAuthor Commented:
I create a true table called Bulk_Insert_Table_KT and included 1 column called HTMLTEXT with a size of nvarchar(MAX).

Then I ran this code:
DECLARE @SQL VARCHAR(800)
SET @SQL = N'BULK INSERT Bulk_Insert_Table_KT FROM ' + @Path
EXEC (@SQL)



The code ran.  The table has a Null value in HTMLTEXT.
0
 
wsturdevAuthor Commented:
Thanks for your latest suggestions, but I finally got it to work as follows:

DECLARE @OrganizationShortName varchar(26)
SELECT @OrganizationShortName = [Organization Short Name] From [dbo].[Organization Info] WHERE RecID = 1

CREATE TABLE #tempHTML (HTMLTEXT VARCHAR(8000))
DECLARE @SQL VARCHAR(800)
SET @SQL = N'BULK INSERT #tempHTML FROM ''D:\Inetpub\KTReceiverModule\$$OrganizationShortName$$\Email Docs\$$OrganizationShortName$$ Initial KT Email.html'''
SELECT @SQL =  REPLACE(@SQL,'$$OrganizationShortName$$',@OrganizationShortName)
EXEC (@SQL)

I will accept your solution and award the points
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.