Incorrect syntax near ';'

Question

Post reply

Incorrect syntax near ';'

donato1026

SSCrazy

Points: 2765
More actions
March 30, 2011 at 1:59 pm

#145063

I am not sure why I am getting this error, but I am... when I doucle-click the error, it is bringing me to the very end of the SP to the RETURN;
Here is my code:
USE [DataWarehouse]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[usp_send_delivery_emails]
AS
DECLARE
@ship_to_f_name nvarchar(MAX)
,@ship_to_l_name nvarchar(MAX)
,@ship_to_addr1 nvarchar(MAX)
,@ShippingAddress nvarchar(MAX)
,@ship_to_addr2 nvarchar(MAX)
,@ship_to_city nvarchar(MAX)
,@ship_to_st_cd nvarchar(MAX)
,@ship_to_zip_cd nvarchar(MAX)
,@ship_to_h_phone nvarchar(MAX)
,@bill_to_f_name nvarchar(MAX)
,@bill_to_l_name nvarchar(MAX)
,@bill_to_addr1 nvarchar(MAX)
,@BillingAddress nvarchar(MAX)
,@bill_to_addr2 nvarchar(MAX)
,@bill_to_city nvarchar(MAX)
,@bill_to_st_cd nvarchar(MAX)
,@bill_to_zip_cd nvarchar(MAX)
,@bill_to_h_phone nvarchar(MAX)
,@cust_cd nvarchar(MAX)
,@email nvarchar(MAX)
,@body nvarchar(MAX);
DECLARE Contacts CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
SELECT
distinct(cust_cd),
ship_to_f_name,
ship_to_l_name,
coalesce(
ship_to_addr1 + char(13) + char(10) + ship_to_addr2,
ship_to_addr1,
ship_to_addr2,
'') as ShippingAddress,
ship_to_addr1,
isnull(ship_to_addr2, ''),
ship_to_city,
ship_to_st_cd,
ship_to_zip_cd,
ship_to_h_phone,
bill_to_f_name,
bill_to_l_name,
coalesce(
bill_to_addr1 + char(13) + char(10) + bill_to_addr2,
bill_to_addr1,
bill_to_addr2,
'') as BillingAddress,
bill_to_addr1,
isnull(bill_to_addr2, ''),
bill_to_city,
bill_to_st_cd,
bill_to_zip_cd,
bill_to_h_phone,
email
FROM dbo.email_list where convert(varchar(10), date_email, 101) = CONVERT(VARCHAR(10), GETDATE(), 101);
OPEN Contacts;
WHILE 1 = 1
BEGIN
FETCH NEXT FROM Contacts INTO
@cust_cd,
@ship_to_f_name,
@ship_to_l_name,
@ShippingAddress,
@ship_to_addr1,
@ship_to_addr2,
@ship_to_city,
@ship_to_st_cd,
@ship_to_zip_cd,
@ship_to_h_phone,
@bill_to_f_name,
@bill_to_l_name,
@BillingAddress,
@bill_to_addr1,
@bill_to_addr2,
@bill_to_city,
@bill_to_st_cd,
@bill_to_zip_cd,
@bill_to_h_phone,
@email;
IF @@FETCH_STATUS = -1 BREAK;
SET @body = '<style type="text/css">
.style1
{
color: #FF0000;
}
.body
{
font-family: Arial, Helvetica, sans-serif;
font-size: small;
}
.style3
{
font-family: Arial, Helvetica, sans-serif;
font-size: x-small;
}
.style4
{
font-size: xx-small;
}
.style6
{
color: #FF0000;
font-family: Arial, Helvetica, sans-serif;
}
.style7
{
font-family: Arial, Helvetica, sans-serif;
font-size: xx-small;
}
.style8
{
font-family: Arial, Helvetica, sans-serif;
font-weight: bold;
font-size: x-small;
}
.style10
{
font-size: x-small;
}
</style><body>
<div>
<table style="width: 100%;">
<tr>
<td width="50%">
<a href="http://www.*****.com">
<img src="http://www.*****.com/skins/Skin_1/images/logo.jpg"
alt="" style="border-width: 0px" /></a>
</td>
<td width="50%">
Do NOT reply to this email. This is a system generated email from
an unmonitored mailbox.<br
class="style6" />
All replies should be sent to <a href="mailto:customerservice@*****.com">
customerservice@*****.com</a><span
class="style7">.

</td>
</tr>
<tr>
<td width="50%" colspan="2" style="width: 100%" class="style3">



Dear ' + @ship_to_f_name + ' ' + @ship_to_l_name + ',<br
class="style3" />
 
Thank you for your purchase from.<br
class="style3" />
 
Please review your delivery address and account information.<br
class="style3" />
 
</td>
</tr>
<tr>
<td width="50%" class="style8">
Ship To Address:</td>
<td width="50%" class="style8">
Bill To Address:</td>
</tr>
<tr>
<td width="50%" class="style3">
' + @ship_to_f_name + ' ' + @ship_to_l_name + '<br
class="style3"/>
' + @ShippingAddress + ' 
' + @ship_to_city + ', ' + @ship_to_st_cd + ' ' + @ship_to_zip_cd + '<br
class="style3"/>
' + @ship_to_h_phone + '</td>
<td width="50%">
' + @bill_to_f_name + ' ' + @bill_to_l_name + '<br
class="style3"/>
' + @BillingAddress + ' 
' + @bill_to_city + ', ' + @bill_to_st_cd + ' ' + @bill_to_zip_cd + '<br
class="style3"/>
' + @bill_to_h_phone + '</td>
</tr>
<tr>
<td width="50%" class="style3">
</td>
<td width="50%" class="style3">
</td>
</tr>
<tr>
<td width="50%" colspan="2" style="width: 100%" class="style3">
<ol>
<li class="style3">To change your account information, please
<a href="http://www.*****.com/change-address-request.aspx">click here</a>. 
 
</li>
<li class="style3">If your information is correct, <a href="http://www.*****.com/schedule-delivery.aspx">CLICK HERE TO
SCHEDULE YOUR DELIVERY</a>.</li>
</ol>

Please note, if you do not have an account, you will need your account number to set one up
and to schedule your delivery: '
+ @cust_cd + '.
</td>
</tr>
<tr>
<td width="50%">
</td>
<td width="50%">
</td>
</tr>
<tr>
<td width="50%" class="style3">
</td>
<td width="50%" class="style3">
</td>
</tr>
<tr>
<td width="50%" class="style3">
Please add 
<a href="mailto:noreply@*****.com">
noreply@*****.com</a> to your safe senders
list or address book to ensure delivery to your inbox.</td>
<td width="50%">
</td>
</tr>
</table>
 
</div>
</body>'
EXECUTE msdb.dbo.sp_send_dbmail
@subject = 'Your order is ready for delivery!',
@recipients = @email,
@body_format = 'HTML',
@body = @body,
@profile_name ='SQL Server Agent';
-- ==============================================================
--
-- This will update comments
--
-- ==============================================================
DECLARE
@tempSeq TABLE (seq_num int);
DECLARE ContactsComments CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
SELECT
so_wr_dt,
so_seq_num,
del_doc_num
FROM dbo.ASAP_email_listTrain where convert(varchar(10), date_email, 101) = CONVERT(VARCHAR(10), GETDATE(), 101);
OPEN ContactsComments;
WHILE 1 = 1
BEGIN
declare
@so_wr_dt varchar(12)
,@so_seq_num nvarchar(50)
,@deldoc nvarchar(14)
,@sql nvarchar(MAX)
,@so_store_cd nvarchar(12)
,@seq_num varchar(5)
,@today as varchar(12)
FETCH NEXT FROM ContactsComments INTO
@so_wr_dt,
@so_seq_num,
@deldoc;
IF @@FETCH_STATUS = -1 BREAK;
set @sql = ' SELECT * FROM OPENQUERY ( TRAIN, '' SELECT '
set @sql = @sql + ' max(seq#) + 1 '
set @sql = @sql + ' FROM SALES.SO_CMNT '
set @sql = @sql + ' WHERE SO_WR_DT = ''''' + @so_wr_dt + ''''''
set @sql = @sql + ' AND SO_STORE_CD = ''''' + @so_store_cd + ''''''
set @sql = @sql + ' AND SO_SEQ_NUM = ''''' + @so_seq_num + ''''''
set @sql = @sql + ' '' ) '
insert @tempSeq exec(@sql);
set @seq_num = (select max(seq_num) from @tempSeq);
set @today = convert (varchar , getDate(), 106);
set @sql = ' INSERT OPENQUERY ( TRAIN, '' SELECT so_wr_dt, so_store_cd, so_seq_num, seq#, dt, text, del_doc_num, cmnt_type FROM SALES.SO_CMNT '
set @sql = @sql + ' WHERE so_wr_dt = ''''' + @so_wr_dt + ''''''
set @sql = @sql + ' AND so_store_cd = ''''' + @so_store_cd + ''''''
set @sql = @sql + ' AND so_seq_num = ''''' + @so_seq_num + ''''''
set @sql = @sql + ' '' ) '
set @sql = @sql + ' VALUES(''' + @so_wr_dt + ''',''' + @so_store_cd + ''',''' + @so_seq_num + ''', ' + @seq_num + ', ''' + @today + ''', ''''DELIVERY EMAIL SENT'''',''' + @deldoc + ''', ''D'')'
exec (@sql);
END
CLOSE Contacts;
DEALLOCATE Contacts;
CLOSE ContactsComments;
DEALLOCATE ContactsComments;
RETURN;
Strange... any help would be greatly appreciated.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

donato1026 SSCrazy Points: 2765 More actions · Answer 1

donato1026

SSCrazy

Points: 2765

March 30, 2011 at 2:14 pm

#1305685

Nevermind... I needed END at the end....

LutzM SSC Guru Points: 107049 More actions · Answer 2

There's an unclosed code block (BEGIN without END).

Strong hint: Proper indention helps a lot... 😉

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

donato1026 SSCrazy Points: 2765 More actions · Answer 3

LutzM (3/30/2011)
There's an unclosed code block (BEGIN without END).
Strong hint: Proper indention helps a lot... 😉

Thank you. I plan on cleaning it up in a bit...