About a Free MSDE manager

Hello!!
Can you advice me some free MSDE tools (like database manager, query
interface...) that can be found over web?
Thanks in advance for your help!!
Ambros Moreno
From Almeria (Spain)
http://www.aspfaq.com/2442
http://www.aspfaq.com/
(Reverse address to reply.)
"Ambros" <ambros@.sasao.com> wrote in message
news:Obexjnf1EHA.2804@.TK2MSFTNGP15.phx.gbl...
> Hello!!
> Can you advice me some free MSDE tools (like database manager, query
> interface...) that can be found over web?
> Thanks in advance for your help!!
> Ambros Moreno
> From Almeria (Spain)
>
|||Thanks for the link Aaron!! is really usefull.!!
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> escribi en el mensaje
news:eOwVYai1EHA.2156@.TK2MSFTNGP10.phx.gbl...
> http://www.aspfaq.com/2442
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Ambros" <ambros@.sasao.com> wrote in message
> news:Obexjnf1EHA.2804@.TK2MSFTNGP15.phx.gbl...
>

A question on database design

Hi everyone,
I'm hoping someone could give me some advice on how to achieve the following.
I'm making a system that stores information on holiday homes. In particular
I need to store information on their bookings. When is a home occupied and
for how long.
I've considered making a Bookings table which would be somthing like:
ID
HomeID
StartDate
EndDate
What I can't see yet is how I'm going to query this table to find all properties
that are available for a given period.
What I want to be able to say is show me all homes that are available between
the 12th of april and the 27th of April.
Can anyone suggest how that query would work? Or is there perhaps a better
way to store this booking information?
Any advice and suggestions would be gratefully received.
Kindest Regards
Simon
> I've considered making a Bookings table which would be somthing like:
> ID
How about BookingID? ID is meaningless and can easily be taken out of
context.

> What I can't see yet is how I'm going to query this table to find all
> properties that are available for a given period.

> What I want to be able to say is show me all homes that are available
> between the 12th of april and the 27th of April.
-- untested, due to lack of DDL, sample data, desired results;
DECLARE @.startDate SMALLDATETIME, @.endDate SMALLDATETIME;
SELECT @.startDate = '20060412', @.endDate = '20060427';
SELECT HomeID, Description
FROM dbo.Homes h
WHERE NOT EXISTS
(
SELECT 1
FROM dbo.Bookings b
WHERE b.HomeID = h.HomeID
AND
(
b.StartDate BETWEEN @.startDate AND @.endDate
OR
b.EndDate BETWEEN @.startDate AND @.endDate
)
);
|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
Don't you need a few more things in Bookings?
CREATE TABLE Bookings
(customer_id INTEGER NOT NULL
REFERENCES Customers(customer_id),
property_id INTEGER NOT NULL
REFERENCES Properties (property_id),
start_date DATETIME NOT NULL,
end_date DATETIME,
CHECK (start_date < end_date),
rental_rate DECIMAL (8,2) NOT NULL
CHECK (rental_rate > 0.00),
PRIMARY KEY (customer_id, property_id, start_date));
[vbcol=seagreen]
the 12th of April and the 27th of April. <<
I would build a Calendar table which is discussed in a lot of old
postings.
SELECT @.my_arrival, @.my_depart, P.property_id
FROM Properties AS P
WHERE NOT EXISTS
(SELECT *
FROM Bookings AS B,
Calendar AS C
WHERE C.cal_date
BETWEEN B.start_date AND B.end_date
AND B.property_id = P.property_id
AND cal_date BETWEEN @.my_arrival AND @.my_depart);
Untested.
(SELECT cal_date
FROM Calendar
WHERE cal_date BETWEEN @.my_arrival AND @.my_depart) AS X(cal_date)
|||You should consider recording dates for which a given unit is actually
available. One way is to create "fake" bookings for non-availability
dates - but that can get ugly.
|||On Tue, 17 Jan 2006 07:28:49 -0800, Simon Harvey wrote:
(snip)
>Can anyone suggest how that query would work? Or is there perhaps a better
>way to store this booking information?
Hi Simon,
Depending on your indexes, the query below might run faster than the
query Aaron suggests:
DECLARE @.startDate SMALLDATETIME, @.endDate SMALLDATETIME;
SELECT @.startDate = '20060412', @.endDate = '20060427';
SELECT HomeID, Description
FROM dbo.Homes h
WHERE NOT EXISTS
(
SELECT 1
FROM dbo.Bookings b
WHERE b.HomeID = h.HomeID
AND b.StartDate <= @.endDate
AND b.EndDate >= @.startDate
);
NOTE: I've set the follow-up to just the SQL Server groups. I can't help
with Access syntax anyway.
Hugo Kornelis, SQL Server MVP
|||Hi everyone
[vbcol=seagreen]
context.
Actually, the primary key in my tables is always ID, that way you never have
to think about it. I used to do it the other way, but then I got to thinking,
why call it BookingID? It's obviously a BookingID by virtue of the fact that
its the ID of the Booking table. I always fully qualify column names anyway
so I use Bookings.ID. I've heard some people say that doing this makes things
faster but I don't really care. I just do it for clarity and to avoid conflicts.
Of course if I was dealing with a foregin key in a table then I'd call it
BookingID because that would be implicitly helpful and quite necessary.
[vbcol=seagreen]
Declarative Referential Integrity, data types, etc. in your schema are. Sample
data is also a good idea, along with clear specifications. It is very hard
to debug code when you do not let us see it."
Well that would be difficult considering I don't have any DDL to offer. I
was asking a general queston on how to achieve an end goal. I suggested a
bookings table which was remarkably simple and existed only in my head. I
thought it was simple enough to be clear to those who saw the possible columns.
Thank you to everyone who has given some suggestions. I think I was pretty
much getting to the same conclusion as Aarons suggestion, but thats solidified
it in my mind.
Thanks again
Simon

A question on database design

Hi everyone,
I'm hoping someone could give me some advice on how to achieve the following
.
I'm making a system that stores information on holiday homes. In particular
I need to store information on their bookings. When is a home occupied and
for how long.
I've considered making a Bookings table which would be somthing like:
ID
HomeID
StartDate
EndDate
What I can't see yet is how I'm going to query this table to find all proper
ties
that are available for a given period.
What I want to be able to say is show me all homes that are available betwee
n
the 12th of april and the 27th of April.
Can anyone suggest how that query would work? Or is there perhaps a better
way to store this booking information?
Any advice and suggestions would be gratefully received.
Kindest Regards
Simon> I've considered making a Bookings table which would be somthing like:
> ID
How about BookingID? ID is meaningless and can easily be taken out of
context.

> What I can't see yet is how I'm going to query this table to find all
> properties that are available for a given period.

> What I want to be able to say is show me all homes that are available
> between the 12th of april and the 27th of April.
-- untested, due to lack of DDL, sample data, desired results;
DECLARE @.startDate SMALLDATETIME, @.endDate SMALLDATETIME;
SELECT @.startDate = '20060412', @.endDate = '20060427';
SELECT HomeID, Description
FROM dbo.Homes h
WHERE NOT EXISTS
(
SELECT 1
FROM dbo.Bookings b
WHERE b.HomeID = h.HomeID
AND
(
b.StartDate BETWEEN @.startDate AND @.endDate
OR
b.EndDate BETWEEN @.startDate AND @.endDate
)
);|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
Don't you need a few more things in Bookings?
CREATE TABLE Bookings
(customer_id INTEGER NOT NULL
REFERENCES Customers(customer_id),
property_id INTEGER NOT NULL
REFERENCES Properties (property_id),
start_date DATETIME NOT NULL,
end_date DATETIME,
CHECK (start_date < end_date),
rental_rate DECIMAL (8,2) NOT NULL
CHECK (rental_rate > 0.00),
PRIMARY KEY (customer_id, property_id, start_date));
the 12th of April and the 27th of April. <<
I would build a Calendar table which is discussed in a lot of old
postings.
SELECT @.my_arrival, @.my_depart, P.property_id
FROM Properties AS P
WHERE NOT EXISTS
(SELECT *
FROM Bookings AS B,
Calendar AS C
WHERE C.cal_date
BETWEEN B.start_date AND B.end_date
AND B.property_id = P.property_id
AND cal_date BETWEEN @.my_arrival AND @.my_depart);
Untested.
(SELECT cal_date
FROM Calendar
WHERE cal_date BETWEEN @.my_arrival AND @.my_depart) AS X(cal_date)|||You should consider recording dates for which a given unit is actually
available. One way is to create "fake" bookings for non-availability
dates - but that can get ugly.|||On Tue, 17 Jan 2006 07:28:49 -0800, Simon Harvey wrote:
(snip)
>Can anyone suggest how that query would work? Or is there perhaps a better
>way to store this booking information?
Hi Simon,
Depending on your indexes, the query below might run faster than the
query Aaron suggests:
DECLARE @.startDate SMALLDATETIME, @.endDate SMALLDATETIME;
SELECT @.startDate = '20060412', @.endDate = '20060427';
SELECT HomeID, Description
FROM dbo.Homes h
WHERE NOT EXISTS
(
SELECT 1
FROM dbo.Bookings b
WHERE b.HomeID = h.HomeID
AND b.StartDate <= @.endDate
AND b.EndDate >= @.startDate
);
NOTE: I've set the follow-up to just the SQL Server groups. I can't help
with Access syntax anyway.
Hugo Kornelis, SQL Server MVP|||Hi everyone
context.
Actually, the primary key in my tables is always ID, that way you never have
to think about it. I used to do it the other way, but then I got to thinking
,
why call it BookingID? It's obviously a BookingID by virtue of the fact that
its the ID of the Booking table. I always fully qualify column names anyway
so I use Bookings.ID. I've heard some people say that doing this makes thing
s
faster but I don't really care. I just do it for clarity and to avoid confli
cts.
Of course if I was dealing with a foregin key in a table then I'd call it
BookingID because that would be implicitly helpful and quite necessary.
Declarative Referential Integrity, data types, etc. in your schema are. Samp
le
data is also a good idea, along with clear specifications. It is very hard
to debug code when you do not let us see it."
Well that would be difficult considering I don't have any DDL to offer. I
was asking a general queston on how to achieve an end goal. I suggested a
bookings table which was remarkably simple and existed only in my head. I
thought it was simple enough to be clear to those who saw the possible colum
ns.
Thank you to everyone who has given some suggestions. I think I was pretty
much getting to the same conclusion as Aarons suggestion, but thats solidifi
ed
it in my mind.
Thanks again
Simon

A question on database design

Hi everyone,
I'm hoping someone could give me some advice on how to achieve the following
.
I'm making a system that stores information on holiday homes. In particular
I need to store information on their bookings. When is a home occupied and
for how long.
I've considered making a Bookings table which would be somthing like:
ID
HomeID
StartDate
EndDate
What I can't see yet is how I'm going to query this table to find all proper
ties
that are available for a given period.
What I want to be able to say is show me all homes that are available betwee
n
the 12th of april and the 27th of April.
Can anyone suggest how that query would work? Or is there perhaps a better
way to store this booking information?
Any advice and suggestions would be gratefully received.
Kindest Regards
Simon> I've considered making a Bookings table which would be somthing like:
> ID
How about BookingID? ID is meaningless and can easily be taken out of
context.

> What I can't see yet is how I'm going to query this table to find all
> properties that are available for a given period.

> What I want to be able to say is show me all homes that are available
> between the 12th of april and the 27th of April.
-- untested, due to lack of DDL, sample data, desired results;
DECLARE @.startDate SMALLDATETIME, @.endDate SMALLDATETIME;
SELECT @.startDate = '20060412', @.endDate = '20060427';
SELECT HomeID, Description
FROM dbo.Homes h
WHERE NOT EXISTS
(
SELECT 1
FROM dbo.Bookings b
WHERE b.HomeID = h.HomeID
AND
(
b.StartDate BETWEEN @.startDate AND @.endDate
OR
b.EndDate BETWEEN @.startDate AND @.endDate
)
);|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
Don't you need a few more things in Bookings?
CREATE TABLE Bookings
(customer_id INTEGER NOT NULL
REFERENCES Customers(customer_id),
property_id INTEGER NOT NULL
REFERENCES Properties (property_id),
start_date DATETIME NOT NULL,
end_date DATETIME,
CHECK (start_date < end_date),
rental_rate DECIMAL (8,2) NOT NULL
CHECK (rental_rate > 0.00),
PRIMARY KEY (customer_id, property_id, start_date));
[vbcol=seagreen]
the 12th of April and the 27th of April. <<
I would build a Calendar table which is discussed in a lot of old
postings.
SELECT @.my_arrival, @.my_depart, P.property_id
FROM Properties AS P
WHERE NOT EXISTS
(SELECT *
FROM Bookings AS B,
Calendar AS C
WHERE C.cal_date
BETWEEN B.start_date AND B.end_date
AND B.property_id = P.property_id
AND cal_date BETWEEN @.my_arrival AND @.my_depart);
Untested.
(SELECT cal_date
FROM Calendar
WHERE cal_date BETWEEN @.my_arrival AND @.my_depart) AS X(cal_date)|||You should consider recording dates for which a given unit is actually
available. One way is to create "fake" bookings for non-availability
dates - but that can get ugly.|||On Tue, 17 Jan 2006 07:28:49 -0800, Simon Harvey wrote:
(snip)
>Can anyone suggest how that query would work? Or is there perhaps a better
>way to store this booking information?
Hi Simon,
Depending on your indexes, the query below might run faster than the
query Aaron suggests:
DECLARE @.startDate SMALLDATETIME, @.endDate SMALLDATETIME;
SELECT @.startDate = '20060412', @.endDate = '20060427';
SELECT HomeID, Description
FROM dbo.Homes h
WHERE NOT EXISTS
(
SELECT 1
FROM dbo.Bookings b
WHERE b.HomeID = h.HomeID
AND b.StartDate <= @.endDate
AND b.EndDate >= @.startDate
);
NOTE: I've set the follow-up to just the SQL Server groups. I can't help
with Access syntax anyway.
Hugo Kornelis, SQL Server MVP|||Hi everyone
[vbcol=seagreen]
context.
Actually, the primary key in my tables is always ID, that way you never have
to think about it. I used to do it the other way, but then I got to thinking
,
why call it BookingID? It's obviously a BookingID by virtue of the fact that
its the ID of the Booking table. I always fully qualify column names anyway
so I use Bookings.ID. I've heard some people say that doing this makes thing
s
faster but I don't really care. I just do it for clarity and to avoid confli
cts.
Of course if I was dealing with a foregin key in a table then I'd call it
BookingID because that would be implicitly helpful and quite necessary.
[vbcol=seagreen]
Declarative Referential Integrity, data types, etc. in your schema are. Samp
le
data is also a good idea, along with clear specifications. It is very hard
to debug code when you do not let us see it."
Well that would be difficult considering I don't have any DDL to offer. I
was asking a general queston on how to achieve an end goal. I suggested a
bookings table which was remarkably simple and existed only in my head. I
thought it was simple enough to be clear to those who saw the possible colum
ns.
Thank you to everyone who has given some suggestions. I think I was pretty
much getting to the same conclusion as Aarons suggestion, but thats solidifi
ed
it in my mind.
Thanks again
Simon

A question on database design

Hi everyone,
I'm hoping someone could give me some advice on how to achieve the following.
I'm making a system that stores information on holiday homes. In particular
I need to store information on their bookings. When is a home occupied and
for how long.
I've considered making a Bookings table which would be somthing like:
ID
HomeID
StartDate
EndDate
What I can't see yet is how I'm going to query this table to find all properties
that are available for a given period.
What I want to be able to say is show me all homes that are available between
the 12th of april and the 27th of April.
Can anyone suggest how that query would work? Or is there perhaps a better
way to store this booking information?
Any advice and suggestions would be gratefully received.
Kindest Regards
Simon> I've considered making a Bookings table which would be somthing like:
> ID
How about BookingID? ID is meaningless and can easily be taken out of
context.
> What I can't see yet is how I'm going to query this table to find all
> properties that are available for a given period.
> What I want to be able to say is show me all homes that are available
> between the 12th of april and the 27th of April.
-- untested, due to lack of DDL, sample data, desired results;
DECLARE @.startDate SMALLDATETIME, @.endDate SMALLDATETIME;
SELECT @.startDate = '20060412', @.endDate = '20060427';
SELECT HomeID, Description
FROM dbo.Homes h
WHERE NOT EXISTS
(
SELECT 1
FROM dbo.Bookings b
WHERE b.HomeID = h.HomeID
AND
(
b.StartDate BETWEEN @.startDate AND @.endDate
OR
b.EndDate BETWEEN @.startDate AND @.endDate
)
);|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
Don't you need a few more things in Bookings?
CREATE TABLE Bookings
(customer_id INTEGER NOT NULL
REFERENCES Customers(customer_id),
property_id INTEGER NOT NULL
REFERENCES Properties (property_id),
start_date DATETIME NOT NULL,
end_date DATETIME,
CHECK (start_date < end_date),
rental_rate DECIMAL (8,2) NOT NULL
CHECK (rental_rate > 0.00),
PRIMARY KEY (customer_id, property_id, start_date));
>> What I want to be able to say is show me all homes that are available between
the 12th of April and the 27th of April. <<
I would build a Calendar table which is discussed in a lot of old
postings.
SELECT @.my_arrival, @.my_depart, P.property_id
FROM Properties AS P
WHERE NOT EXISTS
(SELECT *
FROM Bookings AS B,
Calendar AS C
WHERE C.cal_date
BETWEEN B.start_date AND B.end_date
AND B.property_id = P.property_id
AND cal_date BETWEEN @.my_arrival AND @.my_depart);
Untested.
(SELECT cal_date
FROM Calendar
WHERE cal_date BETWEEN @.my_arrival AND @.my_depart) AS X(cal_date)|||You should consider recording dates for which a given unit is actually
available. One way is to create "fake" bookings for non-availability
dates - but that can get ugly.|||On Tue, 17 Jan 2006 07:28:49 -0800, Simon Harvey wrote:
(snip)
>Can anyone suggest how that query would work? Or is there perhaps a better
>way to store this booking information?
Hi Simon,
Depending on your indexes, the query below might run faster than the
query Aaron suggests:
DECLARE @.startDate SMALLDATETIME, @.endDate SMALLDATETIME;
SELECT @.startDate = '20060412', @.endDate = '20060427';
SELECT HomeID, Description
FROM dbo.Homes h
WHERE NOT EXISTS
(
SELECT 1
FROM dbo.Bookings b
WHERE b.HomeID = h.HomeID
AND b.StartDate <= @.endDate
AND b.EndDate >= @.startDate
);
NOTE: I've set the follow-up to just the SQL Server groups. I can't help
with Access syntax anyway.
--
Hugo Kornelis, SQL Server MVP

A newbie to Reporting Services seeking advice

I have been a long time Crystal Reports user but now due to a new
application, I want to switch over to SQL Server Reporting Services.
My question is whether I can do what I want with Reporting services.
My app is a 2 tier winforms app. The client will query the database through
a VPN. The database is a SQL Server 2005 database. Can my app call reports
located in a directory on the server and display them in a viewer in my app
and then the client either prints to their local machine or saves to file
ex. pdf file? The client does have an IIS server running on the same
machine as the database.
BillIf I understand your question correctly, yes, you can host a server-
based report inside of a client app and also output them to PDF by
using the ReportViewer control.
Start here: http://www.gotreportviewer.com/
On Apr 14, 12:44 am, "BillG" <billgo...@.charter.net> wrote:
> I have been a long time Crystal Reports user but now due to a new
> application, I want to switch over to SQL Server Reporting Services.
> My question is whether I can do what I want with Reporting services.
> My app is a 2 tier winforms app. The client will query the database through
> a VPN. The database is a SQL Server 2005 database. Can my app call reports
> located in a directory on the server and display them in a viewer in my app
> and then the client either prints to their local machine or saves to file
> ex. pdf file? The client does have an IIS server running on the same
> machine as the database.
> Bill

A little advice about locks with SQL 2000

Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:576062
Hi there,
I am hoping someone can give me a little advice about locks.
I have just finished reading a chapter about them in a reference book
and i am now more than when I started.
What I want to do is simple, i hope.
I have multiple users selecting from the same table, I am worried about
2 users getting the same result, which would be very bad, I just need
to find away to stop this from happening.
My set up is real simple.
The SP is below:
Basically A user will call this SP the first part brings back a row,
then updates that table and sets a field to 1, which is used in
selecting the initial row. Basically, I am worried that someone will
have selected the row and in between that persons SP updating the
table, another user come along and then get the same row, and then try
to update the same table. My program will then perform the same
functionon the identical data, which is bad.
I know that a lot of books, say this is unlikely, but my app is such
that this SP will be hit by hundreds of users, every second.
in an ideal world, a user would not be able to execute the SP, until it
has finished executing the last, but I still have to be able to process
hundreds of requests per second, without having massive bottle necks.
Any help would be much appreciated.
CREATE PROCEDURE [dbo].[Engine_Fetch_List]
AS
DECLARE @.client_id int
DECLARE @.url_id int
/*LOCK THIS TABLE UNTIL IT HAS BEEN UPDATED TO STOP ANYONE ELSE FROM
SELECTING IT*/
SELECT TOP 1 @.client_id = l.client_id, @.url_id = l.url_id
FROM dbo.tbl_client_fetch_list l
WHERE (l.updating = 0) AND (l.updated = 0)
GROUP BY l.client_id, l.url_id
ORDER BY l.client_id
/*SET THE UPDATING FLAG, TO STOP ANYONE ELSE FROM SELECTING THIS URL*/
UPDATE dbo.tbl_client_fetch_list SET updating = 1 WHERE client_id =
@.client_id AND url_id = @.url_id
/* REMOVE LOCK*/
GOChris
DECLARE @.pr INT
BEGIN TRANSACTION
SELECT @.pr =MAX(col) FROM Table WITH (UPDLOCK,HOLDLOCK)
UPDATE Table1 SET col =@.pr WHERE blabala
COMMIT TRANSACTION
I'd recommend you to search on interent an explanation posted by Brian
Selzer about those lock hints or please refer for the details in BOL.
"ChrisDangerfield" <chris@.dangerfieldbrothers.co.uk> wrote in message
news:1136639527.280129.256420@.g14g2000cwa.googlegroups.com...
> Hi there,
> I am hoping someone can give me a little advice about locks.
> I have just finished reading a chapter about them in a reference book
> and i am now more than when I started.
> What I want to do is simple, i hope.
> I have multiple users selecting from the same table, I am worried about
> 2 users getting the same result, which would be very bad, I just need
> to find away to stop this from happening.
> My set up is real simple.
> The SP is below:
> Basically A user will call this SP the first part brings back a row,
> then updates that table and sets a field to 1, which is used in
> selecting the initial row. Basically, I am worried that someone will
> have selected the row and in between that persons SP updating the
> table, another user come along and then get the same row, and then try
> to update the same table. My program will then perform the same
> functionon the identical data, which is bad.
> I know that a lot of books, say this is unlikely, but my app is such
> that this SP will be hit by hundreds of users, every second.
> in an ideal world, a user would not be able to execute the SP, until it
> has finished executing the last, but I still have to be able to process
> hundreds of requests per second, without having massive bottle necks.
> Any help would be much appreciated.
> CREATE PROCEDURE [dbo].[Engine_Fetch_List]
> AS
> DECLARE @.client_id int
> DECLARE @.url_id int
> /*LOCK THIS TABLE UNTIL IT HAS BEEN UPDATED TO STOP ANYONE ELSE FROM
> SELECTING IT*/
> SELECT TOP 1 @.client_id = l.client_id, @.url_id = l.url_id
> FROM dbo.tbl_client_fetch_list l
> WHERE (l.updating = 0) AND (l.updated = 0)
> GROUP BY l.client_id, l.url_id
> ORDER BY l.client_id
> /*SET THE UPDATING FLAG, TO STOP ANYONE ELSE FROM SELECTING THIS URL*/
> UPDATE dbo.tbl_client_fetch_list SET updating = 1 WHERE client_id =
> @.client_id AND url_id = @.url_id
> /* REMOVE LOCK*/
> GO
>|||Hello Chris,
Usually the best way is just to start with the UPDATE, if you used SQL 2005
you could also use the OUTPUT clause of the UPDATE.
This way in this example you don't need to worry about locks etc. ex:
CREATE PROCEDURE [dbo].[Engine_Fetch_List]
AS
DECLARE @.client_id int
DECLARE @.url_id int
/*LOCK THIS TABLE UNTIL IT HAS BEEN UPDATED TO STOP ANYONE ELSE FROM
SELECTING IT*/
/*SET THE UPDATING FLAG, TO STOP ANYONE ELSE FROM SELECTING THIS URL*/
SET ROWCOUNT 1 -- Only update one row.
UPDATE dbo.tbl_client_fetch_list SET
@.client_id = client_id -- Here you want to fecth the table PK to search
on that in any SELECTs below.
,@.url_id = url_id
,updating = 1
WHERE updating = 0 AND updated = 0
SET ROWCOUNT 0
-- Do any selects that you need here, you may (not always) want to open a
tran. before the update to hold the X-lock it took.
HTH
Tobias