Solved

Incorrect syntax near '+'. in SQL Server 2005

Posted on 2007-04-03
6
563 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 142

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 142

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

 
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 142

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

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