Solved

Incorrect syntax near '+'. in SQL Server 2005

Posted on 2007-04-03
6
567 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
  • 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
LTrim & Double Space Correction 5 42
Download ms sql express. 2 28
changing page verifacation 1 32
Use column to search string column 2 12
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

821 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