How Do I Fix This Proc?

In a multi-user environment, this proc is expected to return distinct values
for concurrent users - but it doesn't, some duplicates are returned. I
thought that the 'BEGIN TRAN/COMMIT' would provide the required locking - I'
m
guessing that's where I went wrong. How do I fix this proc? The DDL for the
tables involved is included.
PROCEDURE procNextKey_Well
( @.NK int OUTPUT
) AS
BEGIN
DECLARE @.NK2 int
SELECT @.NK = COALESCE(MAX(wellId), 0) FROM dbo.well
SELECT @.NK2 = maxId FROM dbo.tbTableMaxId
WHERE (tableName = N'well')
BEGIN TRAN
IF @.NK2 IS NULL
BEGIN
SET @.NK = @.NK + 1
INSERT INTO dbo.tbTableMaxId
(tableName, maxId)
ELECT N'well', @.NK
END
ELSE
BEGIN
IF @.NK2 > @.NK
SET @.NK = @.NK2 + 1
ELSE
SET @.NK = @.NK + 1
UPDATE dbo.tbTableMaxId
SET maxId = @.NK
WHERE (tableName = N'well')
END
COMMIT
END
CREATE TABLE dbo.tbTableMaxId
( tableName nvarchar(50) NOT NULL,
maxId int NOT NULL,
PRIMARY KEY (tableName)
)
CREATE TABLE dbo.well
( wellId int NOT NULL,
wellName nvarchar(50) NULL,
PRIMARY KEY (wellId)
)
Thanks in advance for your help,
Hal Heinrich
VP Technology
Aralan Solutions Inc."Hal Heinrich" <HalHeinrich@.discussions.microsoft.com> wrote in message
news:3AF75018-7A96-403F-877E-8886D434975C@.microsoft.com...
> In a multi-user environment, this proc is expected to return distinct
> values
> for concurrent users - but it doesn't, some duplicates are returned. I
> thought that the 'BEGIN TRAN/COMMIT' would provide the required locking -
> I'm
> guessing that's where I went wrong. How do I fix this proc? The DDL for
> the
> tables involved is included.
>
Basically strict serialization of the whole procedure will be required to
make this correct, so I hope you don't have to support concurrent inserts.
Which is the main reason why IDENTITY columns exist and you shouldn't try to
emulate them with custom code.
David
Here is your fix:
PROCEDURE procNextKey_Well
( @.NK int OUTPUT
) AS
BEGIN
BEGIN TRAN
DECLARE @.NK2 int
SELECT @.NK = COALESCE(MAX(wellId), 0) FROM dbo.well (tablockx,holdlock)
SELECT @.NK2 = maxId FROM dbo.tbTableMaxId (tablockx,holdlock)
WHERE (tableName = N'well')
IF @.NK2 IS NULL
BEGIN
SET @.NK = @.NK + 1
INSERT INTO dbo.tbTableMaxId
(tableName, maxId)
ELECT N'well', @.NK
END
ELSE
BEGIN
IF @.NK2 > @.NK
SET @.NK = @.NK2 + 1
ELSE
SET @.NK = @.NK + 1
UPDATE dbo.tbTableMaxId
SET maxId = @.NK
WHERE (tableName = N'well')
END
COMMIT
END|||David,
Thank you for your response. I've modified your solution to avoid locking
the well table as follows:
PROCEDURE procNextKey_Well
( @.NK int OUTPUT
) AS
BEGIN
SET NOCOUNT ON
DECLARE @.NK2 int
SELECT @.NK = COALESCE(MAX(wellId), 0) FROM dbo.well
BEGIN TRAN
SELECT @.NK2 = maxId FROM dbo.tbTableMaxId (tablockx, holdlock)
WHERE (tableName = N'well')
IF @.NK2 IS NULL
BEGIN
SET @.NK = @.NK + 1
INSERT INTO dbo.tbTableMaxId
(tableName, maxId)
SELECT N'well', @.NK
END
ELSE
BEGIN
IF @.NK2 > @.NK
SET @.NK = @.NK2 + 1
ELSE
SET @.NK = @.NK + 1
UPDATE dbo.tbTableMaxId
SET maxId = @.NK
WHERE (tableName = N'well')
END
COMMIT
END
I'll be testing this next w and will post the results here.
Thanks again,
Hal Heinrich
VP Technology
Aralan Solutions Inc.
"David Browne" wrote:

> "Hal Heinrich" <HalHeinrich@.discussions.microsoft.com> wrote in message
> news:3AF75018-7A96-403F-877E-8886D434975C@.microsoft.com...
> Basically strict serialization of the whole procedure will be required to
> make this correct, so I hope you don't have to support concurrent inserts.
> Which is the main reason why IDENTITY columns exist and you shouldn't try
to
> emulate them with custom code.
> David
> Here is your fix:
> PROCEDURE procNextKey_Well
> ( @.NK int OUTPUT
> ) AS
> BEGIN
> BEGIN TRAN
> DECLARE @.NK2 int
> SELECT @.NK = COALESCE(MAX(wellId), 0) FROM dbo.well (tablockx,holdlock)
> SELECT @.NK2 = maxId FROM dbo.tbTableMaxId (tablockx,holdlock)
> WHERE (tableName = N'well')
>
> IF @.NK2 IS NULL
> BEGIN
> SET @.NK = @.NK + 1
> INSERT INTO dbo.tbTableMaxId
> (tableName, maxId)
> ELECT N'well', @.NK
> END
> ELSE
> BEGIN
> IF @.NK2 > @.NK
> SET @.NK = @.NK2 + 1
> ELSE
> SET @.NK = @.NK + 1
> UPDATE dbo.tbTableMaxId
> SET maxId = @.NK
> WHERE (tableName = N'well')
> END
> COMMIT
> END
>
>
>|||Hello Heinrich!
I think, it would a better idea if you try to lock only ,row for N'well'
table,
I thin you should use an rowlock hint
"Hal Heinrich" wrote:
> David,
> Thank you for your response. I've modified your solution to avoid locking
> the well table as follows:
> PROCEDURE procNextKey_Well
> ( @.NK int OUTPUT
> ) AS
> BEGIN
> SET NOCOUNT ON
> DECLARE @.NK2 int
> SELECT @.NK = COALESCE(MAX(wellId), 0) FROM dbo.well
> BEGIN TRAN
> SELECT @.NK2 = maxId FROM dbo.tbTableMaxId (tablockx, holdlock)
> WHERE (tableName = N'well')
> IF @.NK2 IS NULL
> BEGIN
> SET @.NK = @.NK + 1
> INSERT INTO dbo.tbTableMaxId
> (tableName, maxId)
> SELECT N'well', @.NK
> END
> ELSE
> BEGIN
> IF @.NK2 > @.NK
> SET @.NK = @.NK2 + 1
> ELSE
> SET @.NK = @.NK + 1
> UPDATE dbo.tbTableMaxId
> SET maxId = @.NK
> WHERE (tableName = N'well')
> END
> COMMIT
> END
> I'll be testing this next w and will post the results here.
> Thanks again,
> Hal Heinrich
> VP Technology
> Aralan Solutions Inc.
>
> "David Browne" wrote:
>|||"Fred" <Fred@.discussions.microsoft.com> wrote in message
news:8A55A9E5-C188-4DCF-BDD9-656AD810C31B@.microsoft.com...
> Hello Heinrich!
> I think, it would a better idea if you try to lock only ,row for N'well'
> table,
> I thin you should use an rowlock hint
>
Why? And what row do you propose locking?
David

How do I fix this cluster??

I have two w2k3 Servers on a SAN. I also have separate instances of SQL Server on the separate physical servers. I have have set up a cluster between the two. And I also have them in separate groups. I am trying to set up an active/active on SQL Server. H
owever, when I try to set up the 2nd virtual server for the active/active cluster, I get an error that says,
'The path, x:\Program Files\Microsoft SQL Server, that you have chosen is either not on a cluster disk resource, or if it is, the resource may be unavailable for the following reasons:
-The disk resource is owned by another node.
-You are installing a virtual server, but selected a disk which is in a group with a virtual server already in it.
HELP!
Ok, so a 2 node cluster. 1 instance of SQL that fails over perfectly? Now
you want to add another instance and do A/A? You have two groups, but does
each group have a separate disk resource? Say X for Group 1, Y for Group 2?
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering
"DBA" <DBA@.discussions.microsoft.com> wrote in message
news:1D4023C7-2430-43B3-83F9-32432B428362@.microsoft.com...
> I have two w2k3 Servers on a SAN. I also have separate instances of SQL
Server on the separate physical servers. I have have set up a cluster
between the two. And I also have them in separate groups. I am trying to set
up an active/active on SQL Server. However, when I try to set up the 2nd
virtual server for the active/active cluster, I get an error that says,
> 'The path, x:\Program Files\Microsoft SQL Server, that you have chosen is
either not on a cluster disk resource, or if it is, the resource may be
unavailable for the following reasons:
> -The disk resource is owned by another node.
> -You are installing a virtual server, but selected a disk which is in a
group with a virtual server already in it.
> HELP!
|||Yes they are on separate physical win2k3 servers
"Rodney R. Fournier [MVP]" wrote:

> Ok, so a 2 node cluster. 1 instance of SQL that fails over perfectly? Now
> you want to add another instance and do A/A? You have two groups, but does
> each group have a separate disk resource? Say X for Group 1, Y for Group 2?
> Cheers,
> Rod
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering
> "DBA" <DBA@.discussions.microsoft.com> wrote in message
> news:1D4023C7-2430-43B3-83F9-32432B428362@.microsoft.com...
> Server on the separate physical servers. I have have set up a cluster
> between the two. And I also have them in separate groups. I am trying to set
> up an active/active on SQL Server. However, when I try to set up the 2nd
> virtual server for the active/active cluster, I get an error that says,
> either not on a cluster disk resource, or if it is, the resource may be
> unavailable for the following reasons:
> group with a virtual server already in it.
>
>
|||Other question. Do I need to set up the sql server cluster prior to a w2k3 server cluster
"Rodney R. Fournier [MVP]" wrote:

> Ok, so a 2 node cluster. 1 instance of SQL that fails over perfectly? Now
> you want to add another instance and do A/A? You have two groups, but does
> each group have a separate disk resource? Say X for Group 1, Y for Group 2?
> Cheers,
> Rod
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering
> "DBA" <DBA@.discussions.microsoft.com> wrote in message
> news:1D4023C7-2430-43B3-83F9-32432B428362@.microsoft.com...
> Server on the separate physical servers. I have have set up a cluster
> between the two. And I also have them in separate groups. I am trying to set
> up an active/active on SQL Server. However, when I try to set up the 2nd
> virtual server for the active/active cluster, I get an error that says,
> either not on a cluster disk resource, or if it is, the resource may be
> unavailable for the following reasons:
> group with a virtual server already in it.
>
>
|||Agreed two servers (nodes in clustering). They also need shared storage, SAN
or SCSI based. Can you tell us more about your clustering groups. Does
failover currently work?
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering
"DBA" <DBA@.discussions.microsoft.com> wrote in message
news:031DC113-90E3-4448-8756-AE4FAF1A9E09@.microsoft.com...[vbcol=seagreen]
> Yes they are on separate physical win2k3 servers
> "Rodney R. Fournier [MVP]" wrote:
Now[vbcol=seagreen]
does[vbcol=seagreen]
2?[vbcol=seagreen]
SQL[vbcol=seagreen]
set[vbcol=seagreen]
is[vbcol=seagreen]
a[vbcol=seagreen]
|||I am currently just setting them up. When I tell them to initiate failure, it seems to work fine
"Rodney R. Fournier [MVP]" wrote:

> Agreed two servers (nodes in clustering). They also need shared storage, SAN
> or SCSI based. Can you tell us more about your clustering groups. Does
> failover currently work?
> Cheers,
> Rod
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering
> "DBA" <DBA@.discussions.microsoft.com> wrote in message
> news:031DC113-90E3-4448-8756-AE4FAF1A9E09@.microsoft.com...
> Now
> does
> 2?
> SQL
> set
> is
> a
>
>
|||No. You need to first setup up Windows cluster server and then setup SQL Server 2000 failover clustering. SQL cluster runs on top of Windows cluster.
For additional information please review
Microsoft Whitepaper - SQL Server 2000 Failover Clustering
http://www.microsoft.com/SQL/techinf...vercluster.asp
Need more references, here you go :
Microsoft SQL Server 2000 High Availability Series
http://www.microsoft.com/technet/pro...y/sqlhalp.mspx
Microsoft Webcasts
Introduction to Microsoft SQL Server 2000 Clustering
http://support.microsoft.com/default...lurb051001.asp
Microsoft SQL Server 2000 Virtual Server: Things You Should Know
http://support.microsoft.com/default...lurb032602.asp
Microsoft SQL Server 2000 Virtual Server Basic Setup, Maintenance, and Service Pack http://support.microsoft.com/default.aspx?
scid=/servicedesks/webcasts/wcd061002/wcdblurb061002.asp
Microsoft SQL Server 2000 Failover Clustering Disaster Recovery Procedures
http://support.microsoft.com/default...lurb101802.asp
Troubleshooting SQL 2000 Virtual Server and Service Pack Setups for Failover Clustering
http://support.microsoft.com/default...lurb020703.asp
Microsoft Knowledge Base Articles
Q243218 INF: Installation Order for SQL 2000 Enterprise Edition
http://support.microsoft.com/?kbid=243218
Q260758 - INF: Frequently Asked Questions - SQL Server 2000 - Failover Clustering
http://support.microsoft.com/?kbid=260758
Best Regards,
Uttam Parui
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest
way to do this is to visit the following websites: http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx
|||However,
When I do this I get an error saying that the system files
could not be found to complete setup. What does that mean?
Also when I look at the Cluster admin, the only thing that
does not start is the virtual SQL Server itself. The IP
add and net name are fine
>--Original Message--
>No. You need to first setup up Windows cluster server and
then setup SQL Server 2000 failover clustering. SQL
cluster runs on top of Windows cluster.
>For additional information please review
>Microsoft Whitepaper - SQL Server 2000 Failover
Clustering
>http://www.microsoft.com/SQL/techinf...stration/2000/
failovercluster.asp
>Need more references, here you go :
>Microsoft SQL Server 2000 High Availability Series
>http://www.microsoft.com/technet/pro.../sql/2000/depl
oy/sqlhalp.mspx
>Microsoft Webcasts
>Introduction to Microsoft SQL Server 2000 Clustering
>http://support.microsoft.com/default.aspx?
scid=/servicedesks/webcasts/wc051001/wcblurb051001.asp
>Microsoft SQL Server 2000 Virtual Server: Things You
Should Know
>http://support.microsoft.com/default.aspx?
scid=/servicedesks/webcasts/wc032602/wcblurb032602.asp
>
>Microsoft SQL Server 2000 Virtual Server Basic Setup,
Maintenance, and Service Pack
http://support.microsoft.com/default.aspx?
>scid=/servicedesks/webcasts/wcd061002/wcdblurb061002.asp
>Microsoft SQL Server 2000 Failover Clustering Disaster
Recovery Procedures
>http://support.microsoft.com/default.aspx?
scid=/servicedesks/webcasts/wcd101802/wcdblurb101802.asp
>Troubleshooting SQL 2000 Virtual Server and Service Pack
Setups for Failover Clustering
>http://support.microsoft.com/default.aspx?
scid=/servicedesks/webcasts/wcd020703/wcdblurb020703.asp
>
>Microsoft Knowledge Base Articles
>Q243218 INF: Installation Order for SQL 2000 Enterprise
Edition
>http://support.microsoft.com/?kbid=243218
>Q260758 - INF: Frequently Asked Questions - SQL Server
2000 - Failover Clustering
>http://support.microsoft.com/?kbid=260758
>Best Regards,
>Uttam Parui
>Microsoft Corporation
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>Are you secure? For information about the Strategic
Technology Protection Program and to order your FREE
Security Tool Kit, please visit
>http://www.microsoft.com/security.
>Microsoft highly recommends that users with Internet
access update their Microsoft software to better protect
against viruses and security vulnerabilities. The easiest
>way to do this is to visit the following websites:
http://www.microsoft.com/protect
>http://www.microsoft.com/security/guidance/default.mspx
>
>.
>
|||Please give more details for us to help you. Here are some things I would like to know:
1) From your postings, it appears that you have installed Windows 2003 Cluster Server? Is that correct?
2) If yes, what are the groups that you have? Also, write the resources in each group?
3) Have you successfully installed SQL Server 2000 virtual instance? What method did you use to install SQL Server 2000 virtual instance?
4) You wrote "Also when I look at the Cluster admin, the only thing that
does not start is the virtual SQL Server itself. The IP
add and net name are fine"
Has the virtual SQL Server resource ever come online?
5) What error do you get when you try to get the resource online? See NT app log, NT sys log, Cluster.log and SQL Errorlog
6) Can you start SQL Server 2000 virtual instance from the command window?
7) If yes, can you connect to it ? Let us know what you do to conect to it and what is the servername that you use to connect to it?
8) You wrote " When I do this I get an error saying that the system files
could not be found to complete setup. What does that mean? "
I do not understand what you mean by saying "when I do this" --> "when you do what?".
Provide as much details as you can. As you might have already realized that troubleshooting SQL Clustering issues are not that straight forward? So the more info you give i.e.details setps that you took and the
errors that you get, attaching/copying/pasting the errors will help us help you with your issues.
Best Regards,
Uttam Parui
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way to do this is to visit the following websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx

How do I fix an intenal NS SP error?

I am in the process of moving some Notification Services 2.0 applications to another server running SQL Server 2005 with Notification Services 2005. I have modified my old ADF files to get rid of the Notify() function as it is no longer supported in version 2005.

When I try to create a new instance using the old IDF file I get some errors, that I believe is internal Notification Services errors: The creation-script tries to create an SP named NSGet<MyAlertName>SMTPNotificationsDeveloperDefined but failed because of an undeclared scalar variable @.workItemId. This is not a variable I have created.

Looking at my old application, currently running fine on SQL Server 2000, I can not find the NSGet<MyAlertName>SMTPNotificationsDeveloperDefined SP, but one that is named NSGet<MyAlertName>SMTPNotifications instead. This SP takes the @.workItemId variable as a parameter, but the new SP (NSGet<MyAlertName>SMTPNotificationsDeveloperDefined) does not.

These stored procedures and the variable is as far as I know created by Notification Services so I don't see how I can correct the error? But as things are now, I'm not able to create the new instance because of this error. I have tried on both Management Studio and the NS command promt - but the same error occurs.

Anyone know how to fix this issue?

Please ignore or delete my post.

It seems that I have done some querying on the old SP which had @.workItemId via an <SQLExpression>.

Sorry for the inconvinience.