Incorrect syntax near the keyword ELSE.

Hi,

I have written a stored procedure to add the records to the table in DB from the report I generate, but the sored procedure gives me this error:

Incorrect syntax near the keyword 'ELSE'.

I am using Sql Server 2005.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spCRMPublisherSummaryUpdate](
@.ReportDate smalldatetime,
@.SiteID int,
@.DataFeedID int,
@.FromCode varchar,
@.Sent int,
@.Delivered int,
@.TotalOpens REAL,
@.UniqueUserOpens REAL,
@.UniqueUserMessageClicks REAL,
@.Unsubscribes REAL,
@.Bounces REAL,
@.UniqueUserLinkClicks REAL,
@.TotalLinkClicks REAL,
@.SpamComplaints int,
@.Cost int
)
AS
DECLARE @.PKID INT
DECLARE @.TagID INT

SELECT @.TagID=ID FROM Tag WHERE SiteID=@.SiteID AND FromCode=@.FromCode

SELECT @.PKID=PKID FROM DimTag
WHERE TagID=@.TagID AND StartDate<=@.ReportDate AND @.ReportDate< ISNULL(EndDate,'12/31/2050')
IF @.PKID IS NULL BEGIN
SELECT TOP 1 @.PKID=PKID FROM DimTag WHERE TagID=@.TagID AND SiteID=@.SiteID
END

DECLARE @.LastReportDate smalldatetime, @.LastSent INT, @.LastDelivered INT, @.LastTotalOpens Real,
@.LastUniqueUserOpens Real, @.LastUniqueUserMessageClicks Real, @.LastUniqueUserLinkClicks Real,
@.LastTotalLinkClicks Real, @.LastUnsubscribes Real, @.LastBounces Real, @.LastSpamComplaints INT, @.LastCost INT

SELECT @.Sent=@.Sent-Sent,@.Delivered=@.Delivered-Delivered,@.TotalOpens=@.TotalOpens-TotalOpens,
@.UniqueUserOpens=@.UniqueUserOpens-UniqueUserOpens,@.UniqueUserMessageClicks=@.UniqueUserMessageClicks-UniqueUserMessageClicks,
@.UniqueUserLinkClicks=@.UniqueUserLinkClicks-UniqueUserLinkClicks,@.TotalLinkClicks=@.TotalLinkClicks-TotalLinkClicks,
@.Unsubscribes=@.Unsubscribes-Unsubscribes,@.Bounces=@.Bounces-Bounces,@.SpamComplaints=@.SpamComplaints-SpamComplaints,
@.Cost=@.Cost-Cost
FROM CrmPublisherSummary
WHERE @.LastReportDate < @.ReportDate
AND SiteID=@.SiteID
AND TagPKID=@.PKID

UPDATE CrmPublisherSummary SET
Sent=@.Sent,
Delivered=@.Delivered,
TotalOpens=@.TotalOpens,
UniqueUserOpens=@.UniqueUserOpens,
UniqueUserMessageClicks=@.UniqueUserMessageClicks,
UniqueUserLinkClicks=@.UniqueUserLinkClicks,
TotalLinkClicks=@.TotalLinkClicks,
Unsubscribes=@.Unsubscribes,
Bounces=@.Bounces,
SpamComplaints=@.SpamComplaints,
Cost=@.Cost
WHERE ReportDate=@.ReportDate
AND SiteID=@.SiteID
AND TagPKID=@.PKID

ELSE
SET NOCOUNT ON

INSERT INTO CrmPublisherSummary(
ReportDate, SiteID, TagPKID, Sent, Delivered, TotalOpens, UniqueUserOpens, UniqueUserMessageClicks, UniqueUserLinkClicks, TotalLinkClicks, Unsubscribes,
Bounces, SpamComplaints, Cost, DataFeedID, TagID)

SELECT
@.ReportDate,
@.SiteID,
@.PKID,
@.Sent,
@.Delivered,
@.TotalOpens,
@.UniqueUserOpens,
@.UniqueUserMessageClicks,
@.UniqueUserLinkClicks,
@.TotalLinkClicks,
@.Unsubscribes,
@.Bounces,
@.SpamComplaints,
@.Cost,
@.DataFeedID,
@.TagID

SET NOCOUNT OFF

Hi,

I think you find that the End Statement must be immediately before the Else (IF... BEGIN...END ELSE). Certainly if you move the END to just before the ELSE the SP compiles OK.

Hope this helps,

Paul

|||

How could I miss that.

Thanks a lot!!!

|||

No problems, sometimes these things just need a fresh pair of eyes.

Cheers

|||Try
ALTER PROCEDURE [dbo].[spCRMPublisherSummaryUpdate](
@.ReportDate smalldatetime,
@.SiteID int,
@.DataFeedID int,
@.FromCode varchar,
@.Sent int,
@.Delivered int,
@.TotalOpens REAL,
@.UniqueUserOpens REAL,
@.UniqueUserMessageClicks REAL,
@.Unsubscribes REAL,
@.Bounces REAL,
@.UniqueUserLinkClicks REAL,
@.TotalLinkClicks REAL,
@.SpamComplaints int,
@.Cost int
)
AS
SET NOCOUNT ON -- moved this
DECLARE @.PKID INT
DECLARE @.TagID INT
SELECT @.TagID=ID FROM Tag WHERE SiteID=@.SiteID AND FromCode=@.FromCode
SELECT @.PKID=PKID FROM DimTag
WHERE TagID=@.TagID AND StartDate<=@.ReportDate AND @.ReportDate< ISNULL(EndDate,'12/31/2050')
IF @.PKID IS NULL BEGIN
SELECT TOP 1 @.PKID=PKID FROM DimTag WHERE TagID=@.TagID AND SiteID=@.SiteID
DECLARE @.LastReportDate smalldatetime, @.LastSent INT, @.LastDelivered INT, @.LastTotalOpens Real,
@.LastUniqueUserOpens Real, @.LastUniqueUserMessageClicks Real, @.LastUniqueUserLinkClicks Real,
@.LastTotalLinkClicks Real, @.LastUnsubscribes Real, @.LastBounces Real, @.LastSpamComplaints INT, @.LastCost INT
SELECT @.Sent=@.Sent-Sent,@.Delivered=@.Delivered-Delivered,@.TotalOpens=@.TotalOpens-TotalOpens,
@.UniqueUserOpens = @.UniqueUserOpens-UniqueUserOpens,
@.UniqueUserMessageClicks = @.UniqueUserMessageClicks-UniqueUserMessageClicks,
@.UniqueUserLinkClicks = @.UniqueUserLinkClicks-UniqueUserLinkClicks,
@.TotalLinkClicks = @.TotalLinkClicks-TotalLinkClicks,
@.Unsubscribes = @.Unsubscribes-Unsubscribes,
@.Bounces = @.Bounces-Bounces,
@.SpamComplaints = @.SpamComplaints-SpamComplaints,
@.Cost = @.Cost-Cost
FROM CrmPublisherSummary
WHERE @.LastReportDate < @.ReportDate AND SiteID=@.SiteID AND TagPKID=@.PKID
UPDATE CrmPublisherSummary SET
Sent=@.Sent,
Delivered=@.Delivered,
TotalOpens=@.TotalOpens,
UniqueUserOpens=@.UniqueUserOpens,
UniqueUserMessageClicks=@.UniqueUserMessageClicks,
UniqueUserLinkClicks=@.UniqueUserLinkClicks,
TotalLinkClicks=@.TotalLinkClicks,
Unsubscribes=@.Unsubscribes,
Bounces=@.Bounces,
SpamComplaints=@.SpamComplaints,
Cost=@.Cost
WHERE ReportDate=@.ReportDate AND SiteID=@.SiteID AND TagPKID=@.PKID
END
ELSE
INSERT INTO CrmPublisherSummary(
ReportDate, SiteID, TagPKID, Sent, Delivered, TotalOpens, UniqueUserOpens,
UniqueUserMessageClicks, UniqueUserLinkClicks, TotalLinkClicks, Unsubscribes,
Bounces, SpamComplaints, Cost, DataFeedID, TagID)
VALUES ( -- Should be values
@.ReportDate, @.SiteID, @.PKID, @.Sent, @.Delivered, @.TotalOpens, @.UniqueUserOpens,
@.UniqueUserMessageClicks, @.UniqueUserLinkClicks, @.TotalLinkClicks, @.Unsubscribes,
@.Bounces, @.SpamComplaints, @.Cost, @.DataFeedID, @.TagID)
SET NOCOUNT OFF|||You have an ELSE with no matching IF.

Incorrect Scripts using database generate scripts wizard

I am getting incorrect scripts, not an error, on the indexes when I use the database ‘generate scripts’ wizard.This only happens when I have 8 or more tables and on indexes with include columns.It’s getting the index columns mix up the include columns.Here are the scripts I got:

-- From database ‘generate scripts’ wizard with 8 or more tables(Incorrect)--

CREATE UNIQUE NONCLUSTERED INDEX [IX_DimArmType_Lookup] ON [dbo].[DimArmType]

(

[EW_Arm_Type_Skey] ASC,

[Arm_Type_ID] ASC

)

INCLUDE ( [Arm_Type_LD],

[EW_Source_DB_ID]) WITH (PAD_INDEX= OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]

The ‘script index as’ wizard seem to work fine.

--From ‘script index as’ wizard (Correct)--

CREATE UNIQUE NONCLUSTERED INDEX [IX_DimArmType_Lookup] ON [dbo].[DimArmType]

(

[EW_Source_DB_ID] ASC,

[Arm_Type_ID] ASC

)

INCLUDE ( [EW_Arm_Type_Skey],

[Arm_Type_LD]) WITH (PAD_INDEX= OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

This is happening on all SQL 2005 machines I tested, with or without SP1 and hotfixes.

This doesn't sound like an SSIS issue. You might want to try a more appropriate forum.|||This app uses SMO to script out all objects in any 2000 or 2005

database. The source is freely available so you can easily modify

it if it's not to your liking. It generates a separate file for each

object, which is useful for getting all your code into source control

if it isn't already.

http://www.elsasoft.org/tools.htm

Include Comments in SQL Script

Hi,

When I generate SQL Script for my database, I wish to include the comments added against each column in the script. But there is no option available where I can specify to include "COMMENTS".

Please suggest.

Regards,

Sudhir Chawla

you can add custom comments by using this

-- two dashes

sql server scripts genarator has no comments option

you have to comment the scripts by yourself

|||

Or you can wrap your scripts in block comments

/*
If you have multiple
lines to comment
then you can use block comments
*/

|||

Actually,

When you create table and add columns, at that time there is a "DESCRIPTION" field, where we can specify the comments for that column. So how do I include these column level comments to be present in my script. I can add my own comments using -- or /* adjkasjd */, but I wish to include column level comments in my script.

Thanks for your advice.

Regards,

Sudhir

|||

I believe the DESCRIPTION field is actually an extended property. If you are using the Script Wizard (right click on the database, select check Tasks > Generate Scripts) to create your scripts, you can check the "Script Extended Properties" checkbox on the Script Options page to get the description scripted.

If you are using the Script context menu in Object Explorer to create your script, you will have to wait until SP2 to get this option, which will be exposed in a new scripting options dialog (e.g. Tools > Options > Scripting in the main menu bar).

Hope this helps,
Steve

Inadequate Results When Using Group By On Subqueries With Newid()

/*
RUN THIS QUERY AT LEAST 10 TIMES - YOU WILL BE IN A SURPRISE
DON'T BE AFFRAID - 1 SEC FOR EACH RUN
*/
/*
This query should generate 1 milion of mixed random ones and zeroes,
and group their counts
*/
select X,"Count"=count(*) from
(
select X=case when checksum(newid())>0 then 0 else 1 end
from ( select 1 as X union all select 1 union all select 1 union all select 1 ) XXX
cross join ( select 1 as X union all select 1 union all select 1 union all select 1 ) XXX1
cross join ( select 1 as X union all select 1 union all select 1 union all select 1 ) XXX2
cross join ( select 1 as X union all select 1 union all select 1 union all select 1 ) XXX3
cross join ( select 1 as X union all select 1 union all select 1 union all select 1 ) XXX4
cross join ( select 1 as X union all select 1 union all select 1 union all select 1 ) XXX5
cross join ( select 1 as X union all select 1 union all select 1 union all select 1 ) XXX6
cross join ( select 1 as X union all select 1 union all select 1 union all select 1 ) XXX7
cross join ( select 1 as X union all select 1 union all select 1 union all select 1 ) XXX8
cross join ( select 1 as X union all select 1 union all select 1 union all select 1 ) XXX9
) XXX group by X

/*
RESULTS:

Correct - count can vary
| Count | X |
-----
| 524288 | 1 |
-----
| 524288 | 0 |
-----

But you can also get this result
| Count | X |
-----
| 786432 | 1 | <-- 1 !!!
-----
| 262144 | 1 | <-- 1 !!!
-----

!!!!!!!!!!! BIFURCATE ONE !!!!!!!!!!!!!!!
*/

--And this query is seems to be correct (not so fast, about 2 min to get output)
declare @.t table (X int)
insert @.t(X)
select X=case when checksum(newid())>0 then 0 else 1 end
from ( select 1 as X union all select 1 union all select 1 union all select 1 ) XXX
cross join ( select 1 as X union all select 1 union all select 1 union all select 1 ) XXX1
cross join ( select 1 as X union all select 1 union all select 1 union all select 1 ) XXX2
cross join ( select 1 as X union all select 1 union all select 1 union all select 1 ) XXX3
cross join ( select 1 as X union all select 1 union all select 1 union all select 1 ) XXX4
cross join ( select 1 as X union all select 1 union all select 1 union all select 1 ) XXX5
cross join ( select 1 as X union all select 1 union all select 1 union all select 1 ) XXX6
cross join ( select 1 as X union all select 1 union all select 1 union all select 1 ) XXX7
cross join ( select 1 as X union all select 1 union all select 1 union all select 1 ) XXX8
cross join ( select 1 as X union all select 1 union all select 1 union all select 1 ) XXX9
select X,"Count"=count(*) from @.t
group by X

/*
This query is simillar, but it's principle is different, because if error during query occurs,
@.table would disappear and #table not. However MSSQLSERVER2K implementation prefers physical #tables ;], that's why this query lasts about 20 sec. Result is also correct.
*/
create table #t (X int)
insert #t(X)
select X=case when checksum(newid())>0 then 0 else 1 end
from ( select 1 as X union all select 1 union all select 1 union all select 1 ) XXX
cross join ( select 1 as X union all select 1 union all select 1 union all select 1 ) XXX1
cross join ( select 1 as X union all select 1 union all select 1 union all select 1 ) XXX2
cross join ( select 1 as X union all select 1 union all select 1 union all select 1 ) XXX3
cross join ( select 1 as X union all select 1 union all select 1 union all select 1 ) XXX4
cross join ( select 1 as X union all select 1 union all select 1 union all select 1 ) XXX5
cross join ( select 1 as X union all select 1 union all select 1 union all select 1 ) XXX6
cross join ( select 1 as X union all select 1 union all select 1 union all select 1 ) XXX7
cross join ( select 1 as X union all select 1 union all select 1 union all select 1 ) XXX8
cross join ( select 1 as X union all select 1 union all select 1 union all select 1 ) XXX9
select X,"Count"=count(*) from #t
group by X
drop table #t

/*
Syntax "CHECKSUM(NEWID())" gives fast pseudo-random int for each usage ( not like RAND() - once per query and seed )
Problem is not bind to CHECKSUM(), but "NEWID()" AND "GROUP BY" combination.
MS SQL Server 2000 has built-in strange "select-driven" ordering routine, group by uses ordering ...?
*/

/*
Tested on Microsoft SQL Server 2000 SP2 - many different hardware
*/Check out the following article:

article (http://support.microsoft.com/default.aspx?scid=KB;en-us;293219&)|||Originally posted by rnealejr
Check out the following article:

article (http://support.microsoft.com/default.aspx?scid=KB;en-us;293219&)

This patch is unofficial and not available to public. I would cripple my SQL server grudgingly. My query is also not corellated as this Microsoft article says. Did you try my query on server with this patch applied with correct results?|||It is available to the general public - you just have to contact them. I agree though that the remarks by ms in the article are not reassuring but that is your decision. I have had to apply these type of intermediate fixes before and have had no problems (from my experiences with these fixes, you can back them out if necessary - but you can confirm that with ms).

To apply any release to your production system from ms without testing it first would be asking for disaster. If you wanted to pursue this, I am sure that you have sql server running on your desktop that has the same issue - just apply the fix to that and see.

In the server explorer "generate create script" is greyed out, whats wrong?

In the server explorer "generate create script" is greyed out, what am I doing wrong?
I want to generate a script that will re-create my DB schema on another machine.
Any help would be appreciated.
ThanksFirst of all you have to use the proper names otherwise we dont know what you want. "enterprise manager no server explorer"

ok that was my anal retentive dba!!!
now

Books online {Documenting and Scripting Databases}

at the bottom of this doc are instructions on how to do it.|||First of all you have to use the proper names otherwise we dont know what you want. "enterprise manager no server explorer"

ok that was my anal retentive dba!!!
now

Books online {Documenting and Scripting Databases}

at the bottom of this doc are instructions on how to do it.

Ill be more clear, Im Using visual studio 2003, In my server explorer window, when I right click on my DB the "generate create script" option is greyed out. I would like to know why its greyed out?
Im guessing by your answer that this faeture is only available on "enterprise manager" edition? Can someone please confirm this for me?
Thankyou|||first of all sorry i thought that you were in the SQL tool directly , it didnt occur to me that you were in VS7

nope, object scripting is a feature that is avilable in every version of SQL Server. this might be a specific trait of VS7 and i dont currently have it installed anywhere to check.
sorry

RDjabarov can help here, he uses VS7