Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Incorrect syntax near '+'. in SQL Server 2005

Posted on 2007-04-03
6
Medium Priority
?
580 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

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.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
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 date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

972 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