?
Solved

Incorrect syntax near '+'. in SQL Server 2005

Posted on 2007-04-03
6
Medium Priority
?
576 Views
Last Modified: 2008-01-09
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?
0
Comment
Question by:wsturdev
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18843011
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
 
LVL 1

Author Comment

by:wsturdev
ID: 18989179
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18989233
you cannot bulk insert into a temp table, you have to make it a true table.
0
Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

 
LVL 1

Author Comment

by:wsturdev
ID: 18989345
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
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1500 total points
ID: 18989517
>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
 
LVL 1

Author Comment

by:wsturdev
ID: 18989576
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

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question