cleaning up system objects left by a merge repl.

Hi,
After disabling publishing on my server, there were
numerous merge replication related objects.
How do I clean them up. I tried to drop them, but I get a
message saying I am trying to drop system objects, and the
effort fails.
Thanks,
Sang
Sang,
try sp_removedbreplication (assuming the database is no longer contains any
publications/subscriptions).
Hilary Cotter sent me a link to a script he created at http://www.ava.co.uk
(technical resouces section) that you might want to look at, if the above
stored proc doesn't remove all the objects.
HTH,
Paul Ibison

Choosing one record over another

How would I select one record and not the another related record in one
Select statement?
I have regular prices that I want to display on most days and promotional
prices on particular days (christmas, summer etc) where I don't want to
display the regular price if I am displaying the promotional price.
I have a table that has 2 types of records in it.
One record is a normal ticket price on a ship. The other record is a
promotional price for that ship.
If it is a Regular Price, there will be null values in the PromoPriceID,
StartDate and End Date (as these are only used for the promotional prices).
If it is a Promotional Price there will be a Null Value in ShipName (as I
would get that from the regular record). The PromoPriceID would contain the
Regular records ShipPricingID (so that it could get the name of the ship and
so that we would know not to display that records regular price). The
StartDate and EndDate would have the date range the promotional prices ran
for.
Here is a test file setup with the display of all the records.
****************************************
************************************
******************************
drop table shipPricing
create table shipPricing
(
ShipPricingID int Identity Not Null,
ShipName varChar(50) Null,
PromoPriceID int Null,
Price Money Not Null,
StartDate smalldatetime Null,
EndDate smalldatetime Null
)
insert shipPricing(ShipName,Price) values ('Sea Witch',1500)
insert shipPricing(PromoPriceID,Price,StartDate
,EndDate) values
(Scope_Identity(),1300,'12/01/05','12/31/05')
insert shipPricing(ShipName,Price) values ('Southern Cross',2200)
insert shipPricing(PromoPriceID,Price,StartDate
,EndDate) values
(Scope_Identity(),2050,'12/15/05','12/31/05')
select ShipPricingID,ShipName = substring(ShipName,1,20),PromoPriceID, Price
= Substring(Convert(varChar,Price,1),1,12)
,StartDate =
Substring(Convert(varChar,StartDate,1),1
,12),EndDate =
SubString(Convert(varChar,EndDate,1),1,1
2) from shipPricing
ShipPricingID ShipName PromoPriceID Price
StartDate EndDate
-- -- -- -- --
--
--
1 Sea Witch NULL 1,500.00
NULL NULL
2 NULL 1
1,300.00 12/01/05 12/31/05
3 Southern Cross NULL 2,200.00
NULL NULL
4 NULL 3
2,050.00 12/15/05 12/31/05
****************************************
************************************
****************************************
**
What I want to do is have a Select statement that would give me back the
prices available for a particular date (Select ... where date = some date).
If it is a promotion I don't want to show the regular price and I would
like it to say something like "Promo".
For example:
If I want the prices for 11/15/01 I should get something like:
ShipPricingID ShipName PromoPriceID Price
StartDate EndDate
-- -- -- -- --
--
--
1 Sea Witch NULL 1,500.00
NULL NULL
3 Southern Cross NULL 2,200.00
NULL NULL
If I want prices for 12/05/05, I should get something like:
ShipPricingID ShipName PromoPriceID Price
StartDate EndDate
-- -- -- -- --
--
--
2 Sea Witch 1
1,300.00 12/01/05 12/31/05 Promo
3 Southern Cross NULL 2,200.00
NULL NULL
If I want prices for 12/18/05, I should get something like:
ShipPricingID ShipName PromoPriceID Price
StartDate EndDate
-- -- -- -- --
--
--
2 Sea Witch 1
1,300.00 12/01/05 12/31/05 Promo
4 Southern Cross 3 2,050.00
12/15/05 12/31/05 Promo
I can't figure out how to select one record and not the other in one Select
statement.
Thanks,
Tomyou could do it with a simple if exists statement, like so:
-- let's assume you've got some kind of variable with your Date value
in it
DECLARE @.date datetime
IF EXISTS (SELECT * FROM shipPricing
WHERE @.date > StartDate AND @.date < EndDate)
SELECT * FROM shipPricing WHERE @.date > StartDate AND @.date < EndDate
ELSE
SELECT * FROM shipPricing WHERE StartDate IS NULL AND EndDate IS NULL
if the exists is true, you won't likely be taxed for the second query,
because the rows will already be cached from the if condition test. so
i'm pretty sure performance-wise you'll only be running 1 query,
essentially. also, you can simply replace * with whatever you want to
return, and since the promo stuff is in its own query, you can throw in
a 'Promo' value in for kicks.|||also, having the condition of one entity versus another being
determined by the presence or absense of values in a field seems a
little odd to me. if it's not a huge conversion, i would consider
migrating this data into two tables: shipPricingStandard, and
shipPricingPromo. that way your queries would look more like:
IF EXISTS (SELECT * FROM shipPricingPromo
WHERE @.date > StartDate AND @.date < EndDate)
SELECT * FROM shipPricingPromo WHERE @.date > StartDate AND @.date <
EndDate
ELSE
SELECT * FROM shipPricingStandard
not sure why, but it seems like a clearer division of what seem like
two similar, but separate entities. but that's probably open to debate,
and more cosmetic than anything.
hope this helps,
jason|||"jason" <iaesun@.yahoo.com> wrote in message
news:1124995465.591667.36420@.g14g2000cwa.googlegroups.com...
> you could do it with a simple if exists statement, like so:
> -- let's assume you've got some kind of variable with your Date value
> in it
> DECLARE @.date datetime
> IF EXISTS (SELECT * FROM shipPricing
> WHERE @.date > StartDate AND @.date < EndDate)
> SELECT * FROM shipPricing WHERE @.date > StartDate AND @.date < EndDate
> ELSE
> SELECT * FROM shipPricing WHERE StartDate IS NULL AND EndDate IS NULL
>
This was the problem I had.
The problem is that you can either get the Promo prices (those with dates)
or regular prices (those without dates). The problem is that I want to
either a regular price OR a promotional price for both ships.
Here are the results if you add in dates you can see the problem. In the
first 2 it would either be on or the other for those dates, but the last
date should have the regular price for the Sea Witch and the promo price for
the other. Also I need to grab the name of the ship from the regular price
record if a promo price.
****************************************
************************************
***************************************
declare @.date smalldatetime
select @.date = '11/01/05'
IF EXISTS (SELECT * FROM shipPricing
WHERE @.date > StartDate AND @.date < EndDate)
SELECT ShipPricingID,ShipName = substring(ShipName,1,20),PromoPriceID,
Price = Substring(Convert(varChar,Price,1),1,12)
,StartDate =
Substring(Convert(varChar,StartDate,1),1
,12),EndDate =
SubString(Convert(varChar,EndDate,1),1,1
2) FROM shipPricing WHERE @.date >
StartDate AND @.date < EndDate
ELSE
SELECT ShipPricingID,ShipName = substring(ShipName,1,20),PromoPriceID,
Price = Substring(Convert(varChar,Price,1),1,12)
,StartDate =
Substring(Convert(varChar,StartDate,1),1
,12),EndDate =
SubString(Convert(varChar,EndDate,1),1,1
2) FROM shipPricing WHERE StartDate
IS NULL AND EndDate IS NULL
ShipPricingID ShipName PromoPriceID Price StartDate
EndDate
-- -- -- -- -- --
--
1 Sea Witch NULL 1,500.00
NULL NULL
3 Southern Cross NULL 2,200.00
NULL NULL
****************************************
************************************
****************************************
**
****************************************
************************************
*************************************
declare @.date smalldatetime
select @.date = '12/16/05'
IF EXISTS (SELECT * FROM shipPricing
WHERE @.date > StartDate AND @.date < EndDate)
SELECT ShipPricingID,ShipName = substring(ShipName,1,20),PromoPriceID,
Price = Substring(Convert(varChar,Price,1),1,12)
,StartDate =
Substring(Convert(varChar,StartDate,1),1
,12),EndDate =
SubString(Convert(varChar,EndDate,1),1,1
2) FROM shipPricing WHERE @.date >
StartDate AND @.date < EndDate
ELSE
SELECT ShipPricingID,ShipName = substring(ShipName,1,20),PromoPriceID,
Price = Substring(Convert(varChar,Price,1),1,12)
,StartDate =
Substring(Convert(varChar,StartDate,1),1
,12),EndDate =
SubString(Convert(varChar,EndDate,1),1,1
2) FROM shipPricing WHERE StartDate
IS NULL AND EndDate IS NULL
ShipPricingID ShipName PromoPriceID Price
StartDate EndDate
-- -- -- -- --
--
--
2 NULL 1
1,300.00 12/01/05 12/31/05
4 NULL 3
2,050.00 12/15/05 12/31/05
****************************************
************************************
****************************************
****
****************************************
************************************
****************************************
****
declare @.date smalldatetime
select @.date = '12/03/05'
IF EXISTS (SELECT * FROM shipPricing
WHERE @.date > StartDate AND @.date < EndDate)
SELECT ShipPricingID,ShipName = substring(ShipName,1,20),PromoPriceID,
Price = Substring(Convert(varChar,Price,1),1,12)
,StartDate =
Substring(Convert(varChar,StartDate,1),1
,12),EndDate =
SubString(Convert(varChar,EndDate,1),1,1
2) FROM shipPricing WHERE @.date >
StartDate AND @.date < EndDate
ELSE
SELECT ShipPricingID,ShipName = substring(ShipName,1,20),PromoPriceID,
Price = Substring(Convert(varChar,Price,1),1,12)
,StartDate =
Substring(Convert(varChar,StartDate,1),1
,12),EndDate =
SubString(Convert(varChar,EndDate,1),1,1
2) FROM shipPricing WHERE StartDate
IS NULL AND EndDate IS NULL
ShipPricingID ShipName PromoPriceID Price
StartDate EndDate
-- -- -- -- --
--
--
2 NULL 1
1,300.00 12/01/05 12/31/05
****************************************
************************************
****************************************
*****
Thanks,
Tom
> if the exists is true, you won't likely be taxed for the second query,
> because the rows will already be cached from the if condition test. so
> i'm pretty sure performance-wise you'll only be running 1 query,
> essentially. also, you can simply replace * with whatever you want to
> return, and since the promo stuff is in its own query, you can throw in
> a 'Promo' value in for kicks.
>|||"jason" <iaesun@.yahoo.com> wrote in message
news:1124995909.532864.267610@.g47g2000cwa.googlegroups.com...
> also, having the condition of one entity versus another being
> determined by the presence or absense of values in a field seems a
> little odd to me. if it's not a huge conversion, i would consider
> migrating this data into two tables: shipPricingStandard, and
> shipPricingPromo. that way your queries would look more like:
The reason I didn't pick 2 tables is the data is essentially the same except
for type of record as well as the dates. I could also add a type code
field, but I can do that by testing for the shipPricingID (if nothing there
it is a regular price) as well as the dates (again if nothing there it is a
regular record).
Tom
> IF EXISTS (SELECT * FROM shipPricingPromo
> WHERE @.date > StartDate AND @.date < EndDate)
> SELECT * FROM shipPricingPromo WHERE @.date > StartDate AND @.date <
> EndDate
> ELSE
> SELECT * FROM shipPricingStandard
> not sure why, but it seems like a clearer division of what seem like
> two similar, but separate entities. but that's probably open to debate,
> and more cosmetic than anything.
> hope this helps,
> jason
>|||t - try this:
declare @.dt datetime
set @.dt = cast('12/18/05' as datetime)
select
case
when @.dt between s2.startdate and s2.enddate then s2.ShipPricingID
else s.ShipPricingID
end as ShipPricingID,
ShipName = substring(s.ShipName,1,20),
case
when @.dt between s2.startdate and s2.enddate then s2.PromoPriceID
else s.PromoPriceID
end as PromoPriceID,
case
when @.dt between s2.startdate and s2.enddate then s2.Price
else s.Price
end as Price,
case
when @.dt between s2.startdate and s2.enddate then s2.StartDate
else s.StartDate
end as StartDate,
case
when @.dt between s2.startdate and s2.enddate then s2.EndDate
else s.EndDate
end as EndDate,
case
when @.dt between s2.startdate and s2.enddate then 'PROMO'
else NULL
end as Promo
from shipPricing s inner join shipPricing s2 on s.ShipPricingID =
s2.PromoPriceID
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:O84VJtZqFHA.2276@.TK2MSFTNGP10.phx.gbl...
> How would I select one record and not the another related record in one
> Select statement?
> I have regular prices that I want to display on most days and promotional
> prices on particular days (christmas, summer etc) where I don't want to
> display the regular price if I am displaying the promotional price.
> I have a table that has 2 types of records in it.
> One record is a normal ticket price on a ship. The other record is a
> promotional price for that ship.
> If it is a Regular Price, there will be null values in the PromoPriceID,
> StartDate and End Date (as these are only used for the promotional
prices).
> If it is a Promotional Price there will be a Null Value in ShipName (as I
> would get that from the regular record). The PromoPriceID would contain
the
> Regular records ShipPricingID (so that it could get the name of the ship
and
> so that we would know not to display that records regular price). The
> StartDate and EndDate would have the date range the promotional prices ran
> for.
> Here is a test file setup with the display of all the records.
>
****************************************
************************************
******************************kred">
> drop table shipPricing
> create table shipPricing
> (
> ShipPricingID int Identity Not Null,
> ShipName varChar(50) Null,
> PromoPriceID int Null,
> Price Money Not Null,
> StartDate smalldatetime Null,
> EndDate smalldatetime Null
> )
> insert shipPricing(ShipName,Price) values ('Sea Witch',1500)
> insert shipPricing(PromoPriceID,Price,StartDate
,EndDate) values
> (Scope_Identity(),1300,'12/01/05','12/31/05')
> insert shipPricing(ShipName,Price) values ('Southern Cross',2200)
> insert shipPricing(PromoPriceID,Price,StartDate
,EndDate) values
> (Scope_Identity(),2050,'12/15/05','12/31/05')
> select ShipPricingID,ShipName = substring(ShipName,1,20),PromoPriceID,
Price
> = Substring(Convert(varChar,Price,1),1,12)
,StartDate =
> Substring(Convert(varChar,StartDate,1),1
,12),EndDate =
> SubString(Convert(varChar,EndDate,1),1,1
2) from shipPricing
> ShipPricingID ShipName PromoPriceID Price
> StartDate EndDate
> -- -- -- -- --
--
> --
> 1 Sea Witch NULL 1,500.00
> NULL NULL
> 2 NULL 1
> 1,300.00 12/01/05 12/31/05
> 3 Southern Cross NULL 2,200.00
> NULL NULL
> 4 NULL 3
> 2,050.00 12/15/05 12/31/05
>
****************************************
************************************
****************************************
**
> What I want to do is have a Select statement that would give me back the
> prices available for a particular date (Select ... where date = some
date).
> If it is a promotion I don't want to show the regular price and I would
> like it to say something like "Promo".
> For example:
> If I want the prices for 11/15/01 I should get something like:
> ShipPricingID ShipName PromoPriceID Price
> StartDate EndDate
> -- -- -- -- --
--
> --
> 1 Sea Witch NULL 1,500.00
> NULL NULL
> 3 Southern Cross NULL 2,200.00
> NULL NULL
> If I want prices for 12/05/05, I should get something like:
> ShipPricingID ShipName PromoPriceID Price
> StartDate EndDate
> -- -- -- -- --
--
> --
> 2 Sea Witch 1
> 1,300.00 12/01/05 12/31/05 Promo
> 3 Southern Cross NULL 2,200.00
> NULL NULL
> If I want prices for 12/18/05, I should get something like:
> ShipPricingID ShipName PromoPriceID Price
> StartDate EndDate
> -- -- -- -- --
--
> --
> 2 Sea Witch 1
> 1,300.00 12/01/05 12/31/05 Promo
> 4 Southern Cross 3
2,050.00
> 12/15/05 12/31/05 Promo
> I can't figure out how to select one record and not the other in one
Select
> statement.
> Thanks,
> Tom
>|||This is not a table. It has no key, uses proprietary data types, etc.
This one table has more not null-able columns than the payroll for a
major auto manufacture.
Are you aware that "camelCase" adds 8-12% more time to reading code?
The eye jumps to the uppercase letter, then back to the front of the
word.
Is there really a ship with a CHAR(50) name or were you just too lazy
to pick a proper size? With the complete lack of data integrity in
this schema, you will get one.
Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files. IDENTITY cannot ever be a key
**by definition**, which I would hope you have learned by now.
CREATE TABLE VoyagePricing -- were you pricing the ships or the
trips?
(ship_name VARCHAR(30) NOT NULL -- I would use a ship code
CHECK (ship_name IN (..)),
promo_name CHAR(10) DEFAULT 'Regular Price' NOT NULL,
trip_price DECIMAL(7,2) NOT NULL,
start_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
end_date DATETIME NOT NULL,
CHECK (start_date < end_date),
PRIMARY KEY (ship_name, start_date)
);
Notice the use of duration ranges. Promos are nested inside the
regular fare ranges.
That is really bad business! You can show them what they are saving
with this query.
SELECT ship_name, promo_name, trip_price
FROM VoyagePricing AS P
WHERE @.my_date BETWEEN start_date AND end_date;
This will give you both the promo and regular prices. I assume that we
give the customer the lower price.
SELECT ship_name, MIN(trip_price)
FROM VoyagePricing
WHERE @.my_date BETWEEN start_date AND end_date
GROUP BY ship_name;
You keep posting the worst code of anyone in this newsgroup. Can you
get your boss to pay for a basic RDBMS course for you and the other
programmers?
A little over a year ago, I got to watch incompetent RDBMS programmers
like you kill children in Africa by messing up a medical supply system.|||Nice Rant.
First of all, and you seem to miss the point, this is not my actual table.
If you look closely (and I know this is difficult), there are NO ship
details. That is because "I MADE THIS TABLE UP JUST TO IRRITATE YOU".
The table has exactly what I felt was necessary to illustrate my problem and
allow others to quickly run it (if they want) without wasting anyones time.
I am not worrying about keys here, I am not worried about indexes, I am not
worried about the size of the Ship name (anywhy would you think a 50 was not
a proper size for a ship name - too big, too little')
Everyone here has been great and I appreciate the time that people take to
help others. I would not presume on their time by not putting only the
necessary elements to illustrate the problem. I'm not sure where
nullable/non nullable, camelCase, Pascal, Uppercase, lower case, variable
size etc. has anything to do with the question I was asking.
BTW, you weren't incorrect in your assessment as to what I was looking for.
VC got it. What happened to you? This was the reason I made the table as
spartan as possible as well as multiple examples to show what I was looking
for. You obviously missed it.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1124999165.137822.47650@.g44g2000cwa.googlegroups.com...
> This is not a table. It has no key, uses proprietary data types, etc.
> This one table has more not null-able columns than the payroll for a
> major auto manufacture.
>
It is a table.
A table must have keys?
And you even call it a table (one that has more not nullable columns ...).
It has 2 BTW, one being identity column, which by definition would not be
nullable anyway.
create table shipPricing
(
ShipPricingID int Identity Not Null,
ShipName varChar(50) Null,
PromoPriceID int Null,
Price Money Not Null,
StartDate smalldatetime Null,
EndDate smalldatetime Null
)
What propriety data types?

> Are you aware that "camelCase" adds 8-12% more time to reading code?
> The eye jumps to the uppercase letter, then back to the front of the
> word.
>
Never seen that statistic. Where did you get that from?
Actually, I use camelCase for variable names (as do many). I also don't do
ship_names, I do ShipNames or shipNames. I never liked the underscore. But
that's just me.
You use ShipNames and ship_names style - but that's just you.

> Is there really a ship with a CHAR(50) name or were you just too lazy
> to pick a proper size? With the complete lack of data integrity in
> this schema, you will get one.
YUP.
schema? What schema? This is just a table (wait a minute, this isn't a
table) ! :)
> Let's get back to the basics of an RDBMS. Rows are not records; fields
> are not columns; tables are not files. IDENTITY cannot ever be a key
> **by definition**, which I would hope you have learned by now.
>
Cannot ever be a key'?
By what definition ?
Here is one I have read:
Definition: The primary key of a relational table uniquely identifies each
record in the table. It can either be a normal attribute that is guaranteed
to be unique (such as Social Security Number in a table with no more than
one record per person) or it can be generated by the DBMS (such as a
globally unique identifier, or GUID, in Microsoft SQL Server). Primary keys
may consist of a single attribute or multiple attributes in combination.
Does an Identity uniquely identify each record in the table?
Is it guaranteed to be unique? ( by definition)
Is it generated by the DBMS'?
Single attribute?

> CREATE TABLE VoyagePricing -- were you pricing the ships or the
> trips?
Why is that even a question and what does it have to do with the question?
Maybe I'm pricing the popcorn on the ship. What difference does it make?

> (ship_name VARCHAR(30) NOT NULL -- I would use a ship code
So would I, but I wanted to see the best way to get the name both from the
record that had the name and the Promo that didn't, but refered to the
record that had the name.

> CHECK (ship_name IN (..)),
> promo_name CHAR(10) DEFAULT 'Regular Price' NOT NULL,
Not really quite sure what you are doing here. There are 2 prices one
regular and one or more Promotional record that would display (instead of)
the regular price between the start and end dates (such as Chrismas in my
examples).

> trip_price DECIMAL(7,2) NOT NULL,
> start_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
> end_date DATETIME NOT NULL,
> CHECK (start_date < end_date),
> PRIMARY KEY (ship_name, start_date)
> );
> Notice the use of duration ranges. Promos are nested inside the
> regular fare ranges.
>
Not what I am trying to do. How does this handle multiple Promos'?
where is the Date Range for the Promos'

> That is really bad business! You can show them what they are saving
> with this query.
>
Bad Business? How about incorrect assessment of the question? or the
problem?

> SELECT ship_name, promo_name, trip_price
> FROM VoyagePricing AS P
> WHERE @.my_date BETWEEN start_date AND end_date;
> This will give you both the promo and regular prices. I assume that we
> give the customer the lower price.
>
Won't give them what I was trying to give them.

> SELECT ship_name, MIN(trip_price)
> FROM VoyagePricing
> WHERE @.my_date BETWEEN start_date AND end_date
> GROUP BY ship_name;
>
Also, won't give them what I was trying to give them. You have what I
needed above - how did you miss it' "If it is a promotion I don't want
to show the regular price and I would like it to say something like "Promo".
Where is that done with either of your examples'?
Ok, let me change the statement slightly.
"If the date that I am asking for is between any of the dates in the
Promotion records (row) for each Ship, display price for that row as well as
the ships name which you can get from the regular price record of that ship
and also pass back the word "Promo". If the date requested is not between
any of the dates in any of the Promotion records (row) for each ship, then
show the regular price and blank (null) instead of the word 'Promo'". In
any case, I should get 1 and only 1 record (row) back for each ship (either
a regular price or a promotional price)."

> You keep posting the worst code of anyone in this newsgroup.
Could be the case. If I was an expert, I wouldn't be asking questions.
<Can you
> get your boss to pay for a basic RDBMS course for you and the other
> programmers?
>
Already been there done that (many years ago). But I am also not an RDBMS
expert (as you may have gathered). And I am sure there are many ways to
skin a cat, as can be seen in this group. They can be done multiple ways
and still be right.

> A little over a year ago, I got to watch incompetent RDBMS programmers
> like you kill children in Africa by messing up a medical supply system.
Never been to Africa.
Tom|||"VC" <me@.here.com> wrote in message
news:XOCdnQobJZG1gpPeRVn-pw@.comcast.com...
> t - try this:
> declare @.dt datetime
> set @.dt = cast('12/18/05' as datetime)
> select
> case
> when @.dt between s2.startdate and s2.enddate then s2.ShipPricingID
> else s.ShipPricingID
> end as ShipPricingID,
> ShipName = substring(s.ShipName,1,20),
> case
> when @.dt between s2.startdate and s2.enddate then s2.PromoPriceID
> else s.PromoPriceID
> end as PromoPriceID,
> case
> when @.dt between s2.startdate and s2.enddate then s2.Price
> else s.Price
> end as Price,
> case
> when @.dt between s2.startdate and s2.enddate then s2.StartDate
> else s.StartDate
> end as StartDate,
> case
> when @.dt between s2.startdate and s2.enddate then s2.EndDate
> else s.EndDate
> end as EndDate,
> case
> when @.dt between s2.startdate and s2.enddate then 'PROMO'
> else NULL
> end as Promo
> from shipPricing s inner join shipPricing s2 on s.ShipPricingID =
> s2.PromoPriceID
Seems to do the job, except it shows Promotional records when it should show
Regular records and vice versa. I am sure it is just that one of the tests
is in reverse. Just need to look at it to see exactly what it does.
Just what I need, though.
Thanks a lot,
Tom
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:O84VJtZqFHA.2276@.TK2MSFTNGP10.phx.gbl...
> prices).
> the
> and
> ****************************************
**********************************
**
> ******************************
> Price
> --
> ****************************************
**********************************
**
> ****************************************
**
> date).
> --
> --
> --
> 2,050.00
> Select
>|||tshad wrote:
> How would I select one record and not the another related record in one
> Select statement?
> I have regular prices that I want to display on most days and promotional
> prices on particular days (christmas, summer etc) where I don't want to
> display the regular price if I am displaying the promotional price.
> I have a table that has 2 types of records in it.
> One record is a normal ticket price on a ship. The other record is a
> promotional price for that ship.
>
Can I ask why you don't have two tables - one with just the ship
pricing (for the regular price - surely every ship must have a regular
price), and then a seperate table, foreign keying to the first,
containing the promotions? It seems like you're actually trying to
squeeze two tables into one, even though they don't share any
attributes?
Damien

CheckPoint question

Is the quantity of data written to disk during a checkpoint, related to the
size of the table? The checkpoint takes place when the log file is 70% full.
Compare these two identical files on separate servers except for rec amts.
10M rec file has checkpoint 47MG.
100M rec file has checkpoint 81MG.
Can I expect that as the tables get larger the checkpoint will become larger?
Thanks,
Don
SQL2000
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:B081E9A7-E100-4BC3-91A3-DEAEBEF72553@.microsoft.com...
> Is the quantity of data written to disk during a checkpoint, related to
> the
> size of the table? The checkpoint takes place when the log file is 70%
> full.
> Compare these two identical files on separate servers except for rec amts.
> 10M rec file has checkpoint 47MG.
> 100M rec file has checkpoint 81MG.
> Can I expect that as the tables get larger the checkpoint will become
> larger?
Checkpoint writes the dirty pages back to the database files, so the size
depends on the number of changes since the last checkpoint and how many of
those pages have been flushed by the lazywriter thread. Also the recovery
interval server parameter affects checkpoint size as well as the amount of
memory on the server.
David
|||I've changed the recovery interval to 1, 100, 1000 respectively, and saw no
change at all in the frequency of the flush or the amount of data flushed.
It always flushes when the log file is 70% full.
I'd be really interested in manipulating the amount of data stored in memory
and/or the frequency of the flush...
Any advice much appreicated.
Don
"David Browne" wrote:

> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
> news:B081E9A7-E100-4BC3-91A3-DEAEBEF72553@.microsoft.com...
> Checkpoint writes the dirty pages back to the database files, so the size
> depends on the number of changes since the last checkpoint and how many of
> those pages have been flushed by the lazywriter thread. Also the recovery
> interval server parameter affects checkpoint size as well as the amount of
> memory on the server.
> David
>
>
|||The 70% deal is because you have the recovery mode set to SIMPLE or you have
never done a proper FULL backup. The tran log will be truncated at 70% full
in Simple mode. This in turn forces a checkpoint to occur. But that just
means that the amount of data in the tran log is still less than SQL Server
thinks it will take to recover in 1 minute. If the log file was larger you
would probably see checkpoints before the 70% full mark. What is the reason
for wanting to change this? If checkpoints are causing issues with
performance you really need to address the source of the trouble and not try
to tweak around it. That means placing the log file on a Raid 1 or raid 10
by itself and a good amount of write back cache will help as well.
Andrew J. Kelly SQL MVP
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:EEEE73EA-9911-4302-9D2A-9E8C1083EA86@.microsoft.com...[vbcol=seagreen]
> I've changed the recovery interval to 1, 100, 1000 respectively, and saw
> no
> change at all in the frequency of the flush or the amount of data flushed.
> It always flushes when the log file is 70% full.
> I'd be really interested in manipulating the amount of data stored in
> memory
> and/or the frequency of the flush...
> Any advice much appreicated.
> Don
>
> "David Browne" wrote:
|||I'm trying to solve an IO problem of when the checkpoint occurs, it writes a
large amount of data onto the disk and this is causing SELECT durations to
skyrocket at this time.
I have around 20 servers so upgrading them to Raid Arrays would be costly.
If I can solve the problem with a tweak, it would be worth the effort.
I've tried FULL and SIMPLE and it has no effect on when the log gets
checkpointed. It's always when it reaches 70% which is what BOL says so it's
in line with expectations. I've got the logs truncated and they're only
taking up approx 7MG.
However, if I could tweak the checkpoint so that it occured say at 50%, then
that amount of data being written would be less and hence less IO and hence
less effect on the SELECT durations.
The LDF and MDF are on their own physical drives.
Thx,
Don
"Andrew J. Kelly" wrote:

> The 70% deal is because you have the recovery mode set to SIMPLE or you have
> never done a proper FULL backup. The tran log will be truncated at 70% full
> in Simple mode. This in turn forces a checkpoint to occur. But that just
> means that the amount of data in the tran log is still less than SQL Server
> thinks it will take to recover in 1 minute. If the log file was larger you
> would probably see checkpoints before the 70% full mark. What is the reason
> for wanting to change this? If checkpoints are causing issues with
> performance you really need to address the source of the trouble and not try
> to tweak around it. That means placing the log file on a Raid 1 or raid 10
> by itself and a good amount of write back cache will help as well.
> --
> Andrew J. Kelly SQL MVP
>
> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
> news:EEEE73EA-9911-4302-9D2A-9E8C1083EA86@.microsoft.com...
>
>
|||You can adjust the recovery interval so it checkpoints more often and hence
less at any one time. But it will happen more often. So in the end you
will still have interruption in the long run. While you can tweak some
there is no getting around the fact that you need proper hardware to handle
certain situations. You can't tweak some things and I/O capacity is one of
them. It has a certain limit and you have apparently reached it. The best
thing to help limit the interruptions of checkpoints is a good caching disk
controller with lots of write back cache. If you are using single disks you
don't have much choice. You may be able to make things a little better with
the recovery interval but it won't work magic.
Andrew J. Kelly SQL MVP
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:411989F9-EDE4-45FA-B02C-6C75DE0EBAAA@.microsoft.com...[vbcol=seagreen]
> I'm trying to solve an IO problem of when the checkpoint occurs, it writes
> a
> large amount of data onto the disk and this is causing SELECT durations to
> skyrocket at this time.
> I have around 20 servers so upgrading them to Raid Arrays would be costly.
> If I can solve the problem with a tweak, it would be worth the effort.
> I've tried FULL and SIMPLE and it has no effect on when the log gets
> checkpointed. It's always when it reaches 70% which is what BOL says so
> it's
> in line with expectations. I've got the logs truncated and they're only
> taking up approx 7MG.
> However, if I could tweak the checkpoint so that it occured say at 50%,
> then
> that amount of data being written would be less and hence less IO and
> hence
> less effect on the SELECT durations.
> The LDF and MDF are on their own physical drives.
> Thx,
> Don
>
> "Andrew J. Kelly" wrote:
|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e8JtwKvTGHA.5108@.TK2MSFTNGP11.phx.gbl...
> You can adjust the recovery interval so it checkpoints more often and
> hence less at any one time. But it will happen more often. So in the end
> you will still have interruption in the long run. While you can tweak
> some there is no getting around the fact that you need proper hardware to
> handle certain situations. You can't tweak some things and I/O capacity is
> one of them. It has a certain limit and you have apparently reached it.
> The best thing to help limit the interruptions of checkpoints is a good
> caching disk controller with lots of write back cache. If you are using
> single disks you don't have much choice. You may be able to make things a
> little better with the recovery interval but it won't work magic.
> --
> Andrew J. Kelly SQL MVP
>
Also, if checkpoints negatively affect SELECT queries, then the SELECT
queries must be driving physical IO. This is probably the root of the
problem. Reduce the amount of IO generated by the queries through analyzing
and improving their performance, or add more memory.
David

CheckPoint question

Is the quantity of data written to disk during a checkpoint, related to the
size of the table? The checkpoint takes place when the log file is 70% full.
Compare these two identical files on separate servers except for rec amts.
10M rec file has checkpoint 47MG.
100M rec file has checkpoint 81MG.
Can I expect that as the tables get larger the checkpoint will become larger
?
Thanks,
Don
SQL2000"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:B081E9A7-E100-4BC3-91A3-DEAEBEF72553@.microsoft.com...
> Is the quantity of data written to disk during a checkpoint, related to
> the
> size of the table? The checkpoint takes place when the log file is 70%
> full.
> Compare these two identical files on separate servers except for rec amts.
> 10M rec file has checkpoint 47MG.
> 100M rec file has checkpoint 81MG.
> Can I expect that as the tables get larger the checkpoint will become
> larger?
Checkpoint writes the dirty pages back to the database files, so the size
depends on the number of changes since the last checkpoint and how many of
those pages have been flushed by the lazywriter thread. Also the recovery
interval server parameter affects checkpoint size as well as the amount of
memory on the server.
David|||I've changed the recovery interval to 1, 100, 1000 respectively, and saw no
change at all in the frequency of the flush or the amount of data flushed.
It always flushes when the log file is 70% full.
I'd be really interested in manipulating the amount of data stored in memory
and/or the frequency of the flush...
Any advice much appreicated.
Don
"David Browne" wrote:

> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
> news:B081E9A7-E100-4BC3-91A3-DEAEBEF72553@.microsoft.com...
> Checkpoint writes the dirty pages back to the database files, so the size
> depends on the number of changes since the last checkpoint and how many of
> those pages have been flushed by the lazywriter thread. Also the recovery
> interval server parameter affects checkpoint size as well as the amount of
> memory on the server.
> David
>
>|||The 70% deal is because you have the recovery mode set to SIMPLE or you have
never done a proper FULL backup. The tran log will be truncated at 70% full
in Simple mode. This in turn forces a checkpoint to occur. But that just
means that the amount of data in the tran log is still less than SQL Server
thinks it will take to recover in 1 minute. If the log file was larger you
would probably see checkpoints before the 70% full mark. What is the reason
for wanting to change this? If checkpoints are causing issues with
performance you really need to address the source of the trouble and not try
to tweak around it. That means placing the log file on a Raid 1 or raid 10
by itself and a good amount of write back cache will help as well.
Andrew J. Kelly SQL MVP
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:EEEE73EA-9911-4302-9D2A-9E8C1083EA86@.microsoft.com...[vbcol=seagreen]
> I've changed the recovery interval to 1, 100, 1000 respectively, and saw
> no
> change at all in the frequency of the flush or the amount of data flushed.
> It always flushes when the log file is 70% full.
> I'd be really interested in manipulating the amount of data stored in
> memory
> and/or the frequency of the flush...
> Any advice much appreicated.
> Don
>
> "David Browne" wrote:
>|||I'm trying to solve an IO problem of when the checkpoint occurs, it writes a
large amount of data onto the disk and this is causing SELECT durations to
skyrocket at this time.
I have around 20 servers so upgrading them to Raid Arrays would be costly.
If I can solve the problem with a tweak, it would be worth the effort.
I've tried FULL and SIMPLE and it has no effect on when the log gets
checkpointed. It's always when it reaches 70% which is what BOL says so it's
in line with expectations. I've got the logs truncated and they're only
taking up approx 7MG.
However, if I could tweak the checkpoint so that it occured say at 50%, then
that amount of data being written would be less and hence less IO and hence
less effect on the SELECT durations.
The LDF and MDF are on their own physical drives.
Thx,
Don
"Andrew J. Kelly" wrote:

> The 70% deal is because you have the recovery mode set to SIMPLE or you ha
ve
> never done a proper FULL backup. The tran log will be truncated at 70% fu
ll
> in Simple mode. This in turn forces a checkpoint to occur. But that just
> means that the amount of data in the tran log is still less than SQL Serve
r
> thinks it will take to recover in 1 minute. If the log file was larger you
> would probably see checkpoints before the 70% full mark. What is the reaso
n
> for wanting to change this? If checkpoints are causing issues with
> performance you really need to address the source of the trouble and not t
ry
> to tweak around it. That means placing the log file on a Raid 1 or raid 1
0
> by itself and a good amount of write back cache will help as well.
> --
> Andrew J. Kelly SQL MVP
>
> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
> news:EEEE73EA-9911-4302-9D2A-9E8C1083EA86@.microsoft.com...
>
>|||You can adjust the recovery interval so it checkpoints more often and hence
less at any one time. But it will happen more often. So in the end you
will still have interruption in the long run. While you can tweak some
there is no getting around the fact that you need proper hardware to handle
certain situations. You can't tweak some things and I/O capacity is one of
them. It has a certain limit and you have apparently reached it. The best
thing to help limit the interruptions of checkpoints is a good caching disk
controller with lots of write back cache. If you are using single disks you
don't have much choice. You may be able to make things a little better with
the recovery interval but it won't work magic.
Andrew J. Kelly SQL MVP
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:411989F9-EDE4-45FA-B02C-6C75DE0EBAAA@.microsoft.com...[vbcol=seagreen]
> I'm trying to solve an IO problem of when the checkpoint occurs, it writes
> a
> large amount of data onto the disk and this is causing SELECT durations to
> skyrocket at this time.
> I have around 20 servers so upgrading them to Raid Arrays would be costly.
> If I can solve the problem with a tweak, it would be worth the effort.
> I've tried FULL and SIMPLE and it has no effect on when the log gets
> checkpointed. It's always when it reaches 70% which is what BOL says so
> it's
> in line with expectations. I've got the logs truncated and they're only
> taking up approx 7MG.
> However, if I could tweak the checkpoint so that it occured say at 50%,
> then
> that amount of data being written would be less and hence less IO and
> hence
> less effect on the SELECT durations.
> The LDF and MDF are on their own physical drives.
> Thx,
> Don
>
> "Andrew J. Kelly" wrote:
>|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e8JtwKvTGHA.5108@.TK2MSFTNGP11.phx.gbl...
> You can adjust the recovery interval so it checkpoints more often and
> hence less at any one time. But it will happen more often. So in the end
> you will still have interruption in the long run. While you can tweak
> some there is no getting around the fact that you need proper hardware to
> handle certain situations. You can't tweak some things and I/O capacity is
> one of them. It has a certain limit and you have apparently reached it.
> The best thing to help limit the interruptions of checkpoints is a good
> caching disk controller with lots of write back cache. If you are using
> single disks you don't have much choice. You may be able to make things a
> little better with the recovery interval but it won't work magic.
> --
> Andrew J. Kelly SQL MVP
>
Also, if checkpoints negatively affect SELECT queries, then the SELECT
queries must be driving physical IO. This is probably the root of the
problem. Reduce the amount of IO generated by the queries through analyzing
and improving their performance, or add more memory.
David

CheckPoint question

Is the quantity of data written to disk during a checkpoint, related to the
size of the table? The checkpoint takes place when the log file is 70% full.
Compare these two identical files on separate servers except for rec amts.
10M rec file has checkpoint 47MG.
100M rec file has checkpoint 81MG.
Can I expect that as the tables get larger the checkpoint will become larger?
Thanks,
Don
SQL2000"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:B081E9A7-E100-4BC3-91A3-DEAEBEF72553@.microsoft.com...
> Is the quantity of data written to disk during a checkpoint, related to
> the
> size of the table? The checkpoint takes place when the log file is 70%
> full.
> Compare these two identical files on separate servers except for rec amts.
> 10M rec file has checkpoint 47MG.
> 100M rec file has checkpoint 81MG.
> Can I expect that as the tables get larger the checkpoint will become
> larger?
Checkpoint writes the dirty pages back to the database files, so the size
depends on the number of changes since the last checkpoint and how many of
those pages have been flushed by the lazywriter thread. Also the recovery
interval server parameter affects checkpoint size as well as the amount of
memory on the server.
David|||I've changed the recovery interval to 1, 100, 1000 respectively, and saw no
change at all in the frequency of the flush or the amount of data flushed.
It always flushes when the log file is 70% full.
I'd be really interested in manipulating the amount of data stored in memory
and/or the frequency of the flush...
Any advice much appreicated.
Don
"David Browne" wrote:
> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
> news:B081E9A7-E100-4BC3-91A3-DEAEBEF72553@.microsoft.com...
> > Is the quantity of data written to disk during a checkpoint, related to
> > the
> > size of the table? The checkpoint takes place when the log file is 70%
> > full.
> >
> > Compare these two identical files on separate servers except for rec amts.
> >
> > 10M rec file has checkpoint 47MG.
> > 100M rec file has checkpoint 81MG.
> >
> > Can I expect that as the tables get larger the checkpoint will become
> > larger?
> Checkpoint writes the dirty pages back to the database files, so the size
> depends on the number of changes since the last checkpoint and how many of
> those pages have been flushed by the lazywriter thread. Also the recovery
> interval server parameter affects checkpoint size as well as the amount of
> memory on the server.
> David
>
>|||The 70% deal is because you have the recovery mode set to SIMPLE or you have
never done a proper FULL backup. The tran log will be truncated at 70% full
in Simple mode. This in turn forces a checkpoint to occur. But that just
means that the amount of data in the tran log is still less than SQL Server
thinks it will take to recover in 1 minute. If the log file was larger you
would probably see checkpoints before the 70% full mark. What is the reason
for wanting to change this? If checkpoints are causing issues with
performance you really need to address the source of the trouble and not try
to tweak around it. That means placing the log file on a Raid 1 or raid 10
by itself and a good amount of write back cache will help as well.
--
Andrew J. Kelly SQL MVP
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:EEEE73EA-9911-4302-9D2A-9E8C1083EA86@.microsoft.com...
> I've changed the recovery interval to 1, 100, 1000 respectively, and saw
> no
> change at all in the frequency of the flush or the amount of data flushed.
> It always flushes when the log file is 70% full.
> I'd be really interested in manipulating the amount of data stored in
> memory
> and/or the frequency of the flush...
> Any advice much appreicated.
> Don
>
> "David Browne" wrote:
>> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
>> news:B081E9A7-E100-4BC3-91A3-DEAEBEF72553@.microsoft.com...
>> > Is the quantity of data written to disk during a checkpoint, related to
>> > the
>> > size of the table? The checkpoint takes place when the log file is 70%
>> > full.
>> >
>> > Compare these two identical files on separate servers except for rec
>> > amts.
>> >
>> > 10M rec file has checkpoint 47MG.
>> > 100M rec file has checkpoint 81MG.
>> >
>> > Can I expect that as the tables get larger the checkpoint will become
>> > larger?
>> Checkpoint writes the dirty pages back to the database files, so the size
>> depends on the number of changes since the last checkpoint and how many
>> of
>> those pages have been flushed by the lazywriter thread. Also the
>> recovery
>> interval server parameter affects checkpoint size as well as the amount
>> of
>> memory on the server.
>> David
>>|||I'm trying to solve an IO problem of when the checkpoint occurs, it writes a
large amount of data onto the disk and this is causing SELECT durations to
skyrocket at this time.
I have around 20 servers so upgrading them to Raid Arrays would be costly.
If I can solve the problem with a tweak, it would be worth the effort.
I've tried FULL and SIMPLE and it has no effect on when the log gets
checkpointed. It's always when it reaches 70% which is what BOL says so it's
in line with expectations. I've got the logs truncated and they're only
taking up approx 7MG.
However, if I could tweak the checkpoint so that it occured say at 50%, then
that amount of data being written would be less and hence less IO and hence
less effect on the SELECT durations.
The LDF and MDF are on their own physical drives.
Thx,
Don
"Andrew J. Kelly" wrote:
> The 70% deal is because you have the recovery mode set to SIMPLE or you have
> never done a proper FULL backup. The tran log will be truncated at 70% full
> in Simple mode. This in turn forces a checkpoint to occur. But that just
> means that the amount of data in the tran log is still less than SQL Server
> thinks it will take to recover in 1 minute. If the log file was larger you
> would probably see checkpoints before the 70% full mark. What is the reason
> for wanting to change this? If checkpoints are causing issues with
> performance you really need to address the source of the trouble and not try
> to tweak around it. That means placing the log file on a Raid 1 or raid 10
> by itself and a good amount of write back cache will help as well.
> --
> Andrew J. Kelly SQL MVP
>
> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
> news:EEEE73EA-9911-4302-9D2A-9E8C1083EA86@.microsoft.com...
> > I've changed the recovery interval to 1, 100, 1000 respectively, and saw
> > no
> > change at all in the frequency of the flush or the amount of data flushed.
> > It always flushes when the log file is 70% full.
> >
> > I'd be really interested in manipulating the amount of data stored in
> > memory
> > and/or the frequency of the flush...
> >
> > Any advice much appreicated.
> >
> > Don
> >
> >
> > "David Browne" wrote:
> >
> >>
> >> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
> >> news:B081E9A7-E100-4BC3-91A3-DEAEBEF72553@.microsoft.com...
> >> > Is the quantity of data written to disk during a checkpoint, related to
> >> > the
> >> > size of the table? The checkpoint takes place when the log file is 70%
> >> > full.
> >> >
> >> > Compare these two identical files on separate servers except for rec
> >> > amts.
> >> >
> >> > 10M rec file has checkpoint 47MG.
> >> > 100M rec file has checkpoint 81MG.
> >> >
> >> > Can I expect that as the tables get larger the checkpoint will become
> >> > larger?
> >>
> >> Checkpoint writes the dirty pages back to the database files, so the size
> >> depends on the number of changes since the last checkpoint and how many
> >> of
> >> those pages have been flushed by the lazywriter thread. Also the
> >> recovery
> >> interval server parameter affects checkpoint size as well as the amount
> >> of
> >> memory on the server.
> >>
> >> David
> >>
> >>
> >>
>
>|||You can adjust the recovery interval so it checkpoints more often and hence
less at any one time. But it will happen more often. So in the end you
will still have interruption in the long run. While you can tweak some
there is no getting around the fact that you need proper hardware to handle
certain situations. You can't tweak some things and I/O capacity is one of
them. It has a certain limit and you have apparently reached it. The best
thing to help limit the interruptions of checkpoints is a good caching disk
controller with lots of write back cache. If you are using single disks you
don't have much choice. You may be able to make things a little better with
the recovery interval but it won't work magic.
--
Andrew J. Kelly SQL MVP
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:411989F9-EDE4-45FA-B02C-6C75DE0EBAAA@.microsoft.com...
> I'm trying to solve an IO problem of when the checkpoint occurs, it writes
> a
> large amount of data onto the disk and this is causing SELECT durations to
> skyrocket at this time.
> I have around 20 servers so upgrading them to Raid Arrays would be costly.
> If I can solve the problem with a tweak, it would be worth the effort.
> I've tried FULL and SIMPLE and it has no effect on when the log gets
> checkpointed. It's always when it reaches 70% which is what BOL says so
> it's
> in line with expectations. I've got the logs truncated and they're only
> taking up approx 7MG.
> However, if I could tweak the checkpoint so that it occured say at 50%,
> then
> that amount of data being written would be less and hence less IO and
> hence
> less effect on the SELECT durations.
> The LDF and MDF are on their own physical drives.
> Thx,
> Don
>
> "Andrew J. Kelly" wrote:
>> The 70% deal is because you have the recovery mode set to SIMPLE or you
>> have
>> never done a proper FULL backup. The tran log will be truncated at 70%
>> full
>> in Simple mode. This in turn forces a checkpoint to occur. But that just
>> means that the amount of data in the tran log is still less than SQL
>> Server
>> thinks it will take to recover in 1 minute. If the log file was larger
>> you
>> would probably see checkpoints before the 70% full mark. What is the
>> reason
>> for wanting to change this? If checkpoints are causing issues with
>> performance you really need to address the source of the trouble and not
>> try
>> to tweak around it. That means placing the log file on a Raid 1 or raid
>> 10
>> by itself and a good amount of write back cache will help as well.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
>> news:EEEE73EA-9911-4302-9D2A-9E8C1083EA86@.microsoft.com...
>> > I've changed the recovery interval to 1, 100, 1000 respectively, and
>> > saw
>> > no
>> > change at all in the frequency of the flush or the amount of data
>> > flushed.
>> > It always flushes when the log file is 70% full.
>> >
>> > I'd be really interested in manipulating the amount of data stored in
>> > memory
>> > and/or the frequency of the flush...
>> >
>> > Any advice much appreicated.
>> >
>> > Don
>> >
>> >
>> > "David Browne" wrote:
>> >
>> >>
>> >> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
>> >> news:B081E9A7-E100-4BC3-91A3-DEAEBEF72553@.microsoft.com...
>> >> > Is the quantity of data written to disk during a checkpoint, related
>> >> > to
>> >> > the
>> >> > size of the table? The checkpoint takes place when the log file is
>> >> > 70%
>> >> > full.
>> >> >
>> >> > Compare these two identical files on separate servers except for rec
>> >> > amts.
>> >> >
>> >> > 10M rec file has checkpoint 47MG.
>> >> > 100M rec file has checkpoint 81MG.
>> >> >
>> >> > Can I expect that as the tables get larger the checkpoint will
>> >> > become
>> >> > larger?
>> >>
>> >> Checkpoint writes the dirty pages back to the database files, so the
>> >> size
>> >> depends on the number of changes since the last checkpoint and how
>> >> many
>> >> of
>> >> those pages have been flushed by the lazywriter thread. Also the
>> >> recovery
>> >> interval server parameter affects checkpoint size as well as the
>> >> amount
>> >> of
>> >> memory on the server.
>> >>
>> >> David
>> >>
>> >>
>> >>
>>|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e8JtwKvTGHA.5108@.TK2MSFTNGP11.phx.gbl...
> You can adjust the recovery interval so it checkpoints more often and
> hence less at any one time. But it will happen more often. So in the end
> you will still have interruption in the long run. While you can tweak
> some there is no getting around the fact that you need proper hardware to
> handle certain situations. You can't tweak some things and I/O capacity is
> one of them. It has a certain limit and you have apparently reached it.
> The best thing to help limit the interruptions of checkpoints is a good
> caching disk controller with lots of write back cache. If you are using
> single disks you don't have much choice. You may be able to make things a
> little better with the recovery interval but it won't work magic.
> --
> Andrew J. Kelly SQL MVP
>
Also, if checkpoints negatively affect SELECT queries, then the SELECT
queries must be driving physical IO. This is probably the root of the
problem. Reduce the amount of IO generated by the queries through analyzing
and improving their performance, or add more memory.
David

Checkbox

Hi,

Even this question is not related to this forum but may be some one help me.

I have a master detail form. I want to delete record but only those record that user click with checkbox. There is no field like boolean. How can I make a scenerio about adding an unbound checkbox in detail form and how would i link with rows in detail sub form.

any help will be highly appreciated.

Regards,I'm guessing you are using MS Access? You should probably post your message in the MS access forum.

Use the bit data type in SQL Server to represent boolean values.|||i'd rub it with bacon.
everything works better with bacon.

mmmmmmmm

Check this critical update from Microsoft Corporation

--rtqbknvc
Content-Type: multipart/related; boundary="wdidwiqndpxpu";
type="multipart/alternative"
--wdidwiqndpxpu
Content-Type: multipart/alternative; boundary="zwoecbzkfacwsecfw"
--zwoecbzkfacwsecfw
Content-Type: text/plain
Content-Transfer-Encoding: quoted-printable
Microsoft User
this is the latest version of security update, the
"October 2003, Cumulative Patch" update which resolves
all known security vulnerabilities affecting
MS Internet Explorer, MS Outlook and MS Outlook Express
as well as three newly discovered vulnerabilities.
Install now to help protect your computer
from these vulnerabilities, the most serious of which could
allow an attacker to run executable on your computer.
This update includes the functionality = of all previously released patches.
Microsoft Product Support Services and Knowledge Base articles = can be found on the Microsoft Technical Support web site.
http://support.microsoft.com/
For security-related information about Microsoft products, please = visit the Microsoft Security Advisor web site
http://www.microsoft.com/security/
Thank you for using Microsoft products.
Please do not reply to this message.
It was sent from an unmonitored e-mail address and we are unable = to respond to any replies.
---
The names of the actual companies and products mentioned = herein are the trademarks of their respective owners.
Copyright 2003 Microsoft Corporation.
--zwoecbzkfacwsecfw
Content-Type: text/html
Content-Transfer-Encoding: quoted-printable
&
.navtext{color:#ffffff;text-decoration:none}
Microsoft
All Products |
Support |
Search |
Microsoft.com Guide
Microsoft Home
Microsoft User
this is the latest version of security update, the
"October 2003, Cumulative Patch" update which resolves
all known security vulnerabilities affecting
MS Internet Explorer, MS Outlook and MS Outlook Express
as well as three newly discovered vulnerabilities.
Install now to help protect your computer
from these vulnerabilities, the most serious of which could
allow an attacker to run executable on your computer.
This update includes the functionality = of all previously released patches.
System requirements
Windows 95/98/Me/2000/NT/XP
This update applies to
MS Internet Explorer, version 4.01 and later
MS Outlook, version 8.00 and later
MS Outlook Express, version 4.01 and later
Recommendation
Customers should install the patch = at the earliest opportunity.
How to install
Run attached file. = Choose Yes on displayed dialog box.
How to use
You don't need to do = anything after installing this item.
Microsoft Product Support Services and Knowledge Base articles
can be found on the Microsoft Technical Support web site. = For security-related information about Microsoft products, please = visit the
Microsoft Security Advisor web site, = or Contact Us.
Thank you for using Microsoft products.
Please do not reply to this message. = It was sent from an unmonitored e-mail address and we are unable = to respond to any replies.
The names of the actual companies and = products mentioned herein are the trademarks = of their respective owners.
Contact Us
|
Legal
|
TRUSTe
©2003 Microsoft Corporation. All rights reserved.
Terms of Use
|
Privacy Statement |
Accessibility

--zwoecbzkfacwsecfw--
--wdidwiqndpxpu
Content-Type: image/gif
Content-Transfer-Encoding: base64
Content-ID: <dbwvtkg>
R0lGODlhaAA7APcAAP///+rp6puSp6GZrDUjUUc6Zn53mFJMdbGvvVtXh2xre8bF1x8cU4yLprOy
zIGArlZWu25ux319xWpqnnNzppaWy46OvKKizZqavLa2176+283N5sfH34uLmpKSoNvb7c7O3L29
yqOjrtTU4crK1Nvb5erq9O/v+O7u99PT2sbGzePj6vLy99jY3Pv7/vb2+fn5++/v8Kqr0oWHuNbX
55SVoszN28vM2pGUr7S1vqqtv52frOPl8CQvaquz2Ojp7pmn3Ozu83OPzmmT6F1/xo6Voh9p2C5z
3EWC31mS40Zxr4uw6LXN8iZkuXmn55q97PH2/Yir1rbL5iVTh3Oj2cvX5Pv9/+/w8QF8606h62Wk
3n+dubnY9abB2c7n/83h9Nji6weK+CGJ4Vim6WyKpKWssgFyyAaV/0Km8Gyx6HW57FJxicDP2+Tt
9Pj8/wOa/wmL5wqd/w6V8heb91e5+mS9+VmLr4vD6qvc/b/j/Mbn/sTi9rvX6szq/tPt/9ju/dzx
/+n2/+74//P6/+3w8hOh/xOW6yCm/iuu/zWv/0m4/XTH/IXK95TP9qPV9bfi/tDn9tfp9OP0/93r
9L3Izy6Vzj22/lrC/mfG/JvJ5JGntAyd6IbX/3zD6GzP/3jV/2uoxHqbqujv8g6MvJTj/2HF5pXV
606zz6Hp/63v/7j1/8Ps88b8/rbj5RKOkE2wr3OGhoKGhv7///Dx8V2alqvm4Zni1YPRvx5uVwyO
X0q2hLTvw8X10gx2H4PXkkuoV5zkoQeADZu7mmzIVEO7HIXbaGfLMPz8+97d2/Px7v///+bl5eHg
4P7+/v39/fT09PLy8u7u7gAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAACwAAAAAaAA7AAAI/gCVCRxI
sKDBgwgTKlzIsKHDhxAjKgwiqs2kSJEgQfqyp2PHLxoxTmojSpTEkyglBrGYcU+el3n09PEDSFKg
mzclAfLTRw/MPV4gjTSZsmhRURchuXwUs88fSYIGubEiqyqAq1gBNLPiRlCgPz197tE4MojRswuD
JHX5UiagQILcNMtKl26zu3etuBgUaKcePXv0QIo0iSjaw8raROKYh6nbuFbmVpVlpbKby4Mya858
eWrlrV0l/fECWDBhw4hPimoJUw9NQVa0Yg6kk6dPmD9xt/Xi52kgKG4GCRLtpTjZNmZTQ5yktLXT
QFNDA+qJe2wkkgkrrmWrx4tv0X6M/gvFrnzh6uaO+wCKOhzs7TzWyUesyDom7z9//EAKOh51eYKK
sdWWH1D15cd78J12GFJKufRXcfwNNtR/ANYXE006UfdSfBQq1lxM3fFHWFlojRBCCA5goMMK5y3V
1B879VGdUMlRqIxaG7kUmHEikVTjQyuAcGIGDmSQwQUYzPBAA1UIKJMfUCI4Vhs2EjTJKrWYwogp
mXSxY0iTTLhQAC2ocKIDHGywgAwYWPDAm3AeIIVztr3E1FiFVSnQJLXc4ksxuujyiy6npNGFYBKK
WRAzKZipAgkp8ACCAyLg0MClDcD5ppIUVNCFFDL1oSF8Qvn3nyi8+KIqMH8aQwwx/66EMQcoVQxG
mI/KBEBCCCSo0MIPLJSJwA6YFvsmBlFkYgopUTxwgQ8XXGBBBRUA0QUXeJp6qi2r2rKLLcAU42qs
WIRhR623YpdDNM4wQ0IOInggrwfFNoCDDl20wooqqaSCCil3SHCBBgQXnAGbFmCAgQMkBKDnLsMU
4wswvPCySy3DuLpJGFiY4YodX6RrUhnOIFDDvPNeqkkXfKzCyssv8+svwM5uYPPNONusAZszEEEE
GoooQsfQdRRdxyJII83I0ow04nQjjkTtCB5cVN3KMBEXA8wuFbMC6Cu5jIJFLsG4oonIQeQQQw4o
a5KsI6moogrMMMvt77+kCPzB3v589+03BxdQ0IFyotyCdTFap7I1K7Z4YskmcIwSTC+9KMHGSD6S
0AIJHkRxByekkIJKv3LPXbfMeOddgQmst+466xoAIUEEEUzAQNBD02H00UkvwnTTT0s9ddV4ZPEK
1hH/qTUnlyDyRi659BJMMLiEgrkoQSwTAjMefPIJ6KKPHnfppfeLCt6cCDFDmjT8AMP7MJywwQW0
1187Aco5osUYyGNtjC+ccFwhzuCK6U0OF2uoQht8FAMEoMADnfge+M7Xrwpa8HyhI0X6JGCwDGhg
fvYLoe1wRzSj9c53THsa1KRGNS6oYQxZ0AXyjKGLUlzCEoeIQxjIRjnKTYESC/7EnjJyYAIRRMF7
4Auf+Cp4vtRxghNOiEAHjxTC+k3gfsp5ghPSAIqMBeoUlkjEIeYgBzjwEBdonEIOgmgWSDlgC0h8
YgabSEcncuITUZQBwYxERftRYAIToEDtbie0EhbthL9TofBa6IT9jeEVgQpUJcZoCDEUcHqUw8UU
ysBGZZQgBAvAgSfimMQMmjJ0T/SeGiKgRw3w8QKz+2Mgp/UALKamC1FYwha1AElJzkEMYiDb5HqB
wE2SRIjR0MEIGoCJUUqwlKd84h0/4QlMRKACezQSLAM5A2pR6wF/JGTudofIFAaPhVW7AxWooIX9
ZSELv4hnJYA5CjQScw1rUP/jMQeCgA/gQA2ecOYzpUnQaVKzmtfM5pEkMIFpebMCtZwA/lJTBR88
YQlRcIITQBHPeNrhCEcwQhPQmM8EALEkAwnBDTBAhWYG1HukTCVMD4oJTBDBAgrNAEOnZYE/vomh
4jQk75KWyHNGrYWO0KUT1tlOWnRUCUdQQhOaoIQ12GEKsVCgEAVSAge88RIufelMxxrQal7iEkLg
oCv5uFOffvOPE0XMMvjggy74IAoZ3UI8aYEEJUh1CkoggxIOUIbCbFUZyczADM4K1rI69rHVxARj
kyDFtRppp9OawR8pAFQS6s6EvSuq0xZZNS444gkZ1SgVQkELWvjMr1QlQgT+pgALG+yTIDrgwAPo
wFiwhtWxNZUsYxVBWYX6YAYT0CwgHwDRB0i0PNGoghTsCoQoaEIYQhCCz7ZLhCYoIAdD+ZEyQqAB
C4xBEb09a3Brmt5LBE0RWYiAB/mo2EBSoJvfdG5QP3vI0JpztOgsLR8y8QTU4jUK2U2wEIagBAWU
AQy3JcgIUqSF97b3wu9VhCXQwErLKpYCDvXmmygQV+UEQLpScKUPfACEFjuBCGuAhQ4gXBLxIjZa
QrBEhtGL3rPyOMOWCHIiOkxfCzT0oc2lwH7J6d+lKTLAVfPIdAu8hCUAwQlCIIMBikAJCEeYIMm4
gAxmkIggB3nHOzazJcb+QIXZ6bHIIPZmT0FMYj2RyUw50EEZRIAASnzheoctSJEekIgyq/nQalaE
E2QXAYHlFANx1iyILYDcJYOWqP9d4VFLi62PgEQkGAl1mI5p44HcYMxoQISqC21oIYcxDUuowOwk
IAMOTDEDGAAnBR5gARyAE5Al1pMytIM5UiuEBxWwQBIOoepmO1sRd/BBBWgnMGo9a758xECmcOBr
QE5Av55lMqadbNThldYjX/h0qEVyvVIDiFpEOIS85b3qOjBBBrODgL4foCZoWVsG2cZAt5fL7ToL
WyAVWeAxA42QScjgAkQoRCHmrYhGgDAC+s54AjbAAQ4s4GDeFHOuvf3/ABwMQBgiUHK4L620TJP2
3J7WSEhG1MmJRKILsJzDxBfxhfLWL+MZn4AGOm5rgj2cWrJ8wAB2sAMRFEMYBtcTRUpCdXcbZDV8
sIAExoAHHuA7At2sYv3Q5PEOQmvXTE/7DlCu8kLyd6gtJzeANw3zPaRb5uwOIkoV0gY2SNsCgG+0
DFJwJFhWMbkDK7qHRcD4xjMeBxMoQAGEHYSpWz0hPlhANHxggWtyYBnMQAYIKvBwCZj+9GCHqAUc
kFMdOF4EOzBAAXoA2JX3d9zAm7u5oxxzW4164doaiAM0rwwU0IAHz4hGAEDfAjH74PTQn4G0EpAA
Z9HX9Y03wAEKcIAB/oDAYQc/CQkcEIBoPAMGzoDBM2KwfGa0QAMXOBLg5y8B6V/gAVNowhQogIEV
61kEDXAAPdADTVAJaKBjtgd3KCR3mrZ7nWZ36kZzx0QIV5AQGNAC5Xd+x6B+7Md8KYBN0oZkziIt
E4AAKTAACtBQ8ZIA3NcBKrAMMRB+RfEAzLAM0aAMz/ACLwANyrcMyNACKXABCwA40VKEFPBwRtYE
cjAHhmAEU5AAAzgFYjAHrHZmCVhODPhyvAeBtkJzNUYIs5AQNLgM5VeBV9CDoQeEIZABICADbviG
FBAtRqYAzCAQAVACOSAACFACMngYFqACNRgAgiiIy+CDLQCEJCAD/yWgAV7ViHF4ATOQAFMABxI3
cWM0B6tWhQjoduIWd7nXgC20hXfHbkOBPRSYECFgAchQg4VYiMyQhikAAjdwAStgAydyIm1yARVA
AQXQASvQhzYSAA2AAav4iq/4g0AYiyRwATRQAiqgAggwAxYgA7t4AAcQAjcIjBTSAgYwAySADOB4
iMkoi7uCAQuQJBYgZj3FfQOwDNpYJSnQAROAAZozjuS4AAsAfzLgAGzyACzYfXX4jlVSAmVAfQ+w
MCRgAyRAAvhIMCmCXNtXAAYQAu4okHryAzaAARNgjQYJJxNAfRF5AAaQAy2QjRYpdWBQBV2QawrA
gpLHfQpgAA1ggiMrYJInKWxIsRhfUAU82ZMj0Iwr8AM3qY3E9ntVV3lDWSUBAQA7
--wdidwiqndpxpu
Content-Type: image/gif
Content-Transfer-Encoding: base64
Content-ID: <gefevvo>
R0lGODlhDAAMANUAAP////f3//f39+/v9+/v797m987W787W5sXW5rXF76295qW975y175St75St
3pSlzoyl1oSl5oylzoycxXOU3nOMxWOM5mOM3mOE1lqE3mOEvVKE1lp7xVJ71lJ7zlJ7xVJ7vUp7
zkpzzkpzxVJzrUprvUJrxUJrvUJjtTpjtTpjrTparTpapQAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAACwAAAAADAAMAAAIjAABAAhwwMGFCxAQ
CACwkICDDBYSLGjQwQEBhg8zDBAIYIEIBwIQdLjAoOOFgSFMIICwIUMEAxQwCBxhAgKHDh5C6DQA
IIGJEyA4fPAwYoQCAAVKoEgBQsKJEidQ8CyRYumDA1VTqNBQQYXXFQofsPB6AIAKFiweNBTLoiza
BxcFCjgwgQSJCQcWCggIADs=
--wdidwiqndpxpu--
--rtqbknvc
Content-Type: application/x-compressed; name="pack.zip"
Content-Transfer-Encoding: base64
Content-Disposition: attachment
--rtqbknvc--This is a multi-part message in MIME format.
--=_NextPart_000_002F_01C38BE4.0BA78C40
Content-Type: multipart/alternative;
boundary="--=_NextPart_001_0030_01C38BE4.0BA78C40"
--=_NextPart_001_0030_01C38BE4.0BA78C40
Content-Type: text/plain;
charset="ks_c_5601-1987"
Content-Transfer-Encoding: quoted-printable
I have been received this kind of emails directly.
Anybody know how I can stop this emails?
Joohyun
"COLINGRA" <nhzfxrvhvo@.vyur.com> wrote in message =news:OCdZkeyiDHA.3568@.tk2msftngp13.phx.gbl...
Microsoft All Products | Support | Search | =Microsoft.com Guide Microsoft Home
Microsoft User
this is the latest version of security update, the "October =2003, Cumulative Patch" update which resolves all known security =vulnerabilities affecting MS Internet Explorer, MS Outlook and MS =Outlook Express as well as three newly discovered vulnerabilities. =Install now to help protect your computer from these vulnerabilities, =the most serious of which could allow an attacker to run executable on =your computer. This update includes the functionality of all previously =released patches.
System requirements Windows 95/98/Me/2000/NT/XP This update applies to MS Internet Explorer, version 4.01 and =later
MS Outlook, version 8.00 and later
MS Outlook Express, version 4.01 and later Recommendation Customers should install the patch at the =earliest opportunity. How to install Run attached file. Choose Yes on displayed =dialog box. How to use You don't need to do anything after installing this =item.
Microsoft Product Support Services and Knowledge Base articles =can be found on the Microsoft Technical Support web site. For =security-related information about Microsoft products, please visit the =Microsoft Security Advisor web site, or Contact Us.
Thank you for using Microsoft products.
Please do not reply to this message. It was sent from an =unmonitored e-mail address and we are unable to respond to any replies.
---
The names of the actual companies and products mentioned herein =are the trademarks of their respective owners.
Contact Us | Legal | TRUSTe =A8=CF2003 Microsoft Corporation. All rights reserved. Terms of =Use | Privacy Statement | Accessibility --=_NextPart_001_0030_01C38BE4.0BA78C40
Content-Type: text/html;
charset="ks_c_5601-1987"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

.navtext {
COLOR: #ffffff; TEXT-DECORATION: none
}
I have been received this kind of emails directly.
Anybody know how I can stop this =emails?
Joohyun
"COLINGRA" wrote =in message news:OCdZkeyiDHA.3568=@.tk2msftngp13.phx.gbl...
Microsoft
All Products | Support | Search | Microsoft.com Guide
Microsoft Home
Microsoft Userthis is the latest =version of security update, the "October 2003, Cumulative Patch" update =which resolves all known security vulnerabilities affecting MS =Internet Explorer, MS Outlook and MS Outlook Express as well as three =newly discovered vulnerabilities. Install now to help protect your =computer from these vulnerabilities, the most serious of which could =allow an attacker to run executable on your computer. This update =includes the functionality of all previously released patches.
System requirements
Windows =95/98/Me/2000/NT/XP
This update applies to
MS Internet Explorer, version 4.01 and laterMS Outlook, version 8.00 and laterMS Outlook =Express, version 4.01 and later
Recommendation
Customers should install the patch at =the earliest opportunity.
How to install
Run attached file. Choose Yes on =displayed dialog box.
How to use
You don't need to do anything after =installing this item.
Microsoft Product Support Services and =Knowledge Base articles can be found on the Microsoft Technical Support web site. For security-related information about Microsoft products, please =visit the Microsoft Security Advisor web site, or Contact Us. Thank you for using =Microsoft products.Please do not reply to =this message. It was sent from an unmonitored e-mail address and we =are unable to respond to any replies.
The names of the actual companies =and products mentioned herein are the trademarks of their respective =owners.
Contact Us | Legal = | TRUSTe
©2003 Microsoft =Corporation. All rights reserved. Terms of Use | Privacy Statement | Accessibility =

---

--=_NextPart_001_0030_01C38BE4.0BA78C40--
--=_NextPart_000_002F_01C38BE4.0BA78C40
Content-Type: image/gif
Content-Transfer-Encoding: base64
Content-ID: <002201c38b98$9a2965c0$b84afb3d@.joohkim>
R0lGODlhaAA7APcAAP///+rp6puSp6GZrDUjUUc6Zn53mFJMdbGvvVtXh2xre8bF1x8cU4yLprOy
zIGArlZWu25ux319xWpqnnNzppaWy46OvKKizZqavLa2176+283N5sfH34uLmpKSoNvb7c7O3L29
yqOjrtTU4crK1Nvb5erq9O/v+O7u99PT2sbGzePj6vLy99jY3Pv7/vb2+fn5++/v8Kqr0oWHuNbX
55SVoszN28vM2pGUr7S1vqqtv52frOPl8CQvaquz2Ojp7pmn3Ozu83OPzmmT6F1/xo6Voh9p2C5z
3EWC31mS40Zxr4uw6LXN8iZkuXmn55q97PH2/Yir1rbL5iVTh3Oj2cvX5Pv9/+/w8QF8606h62Wk
3n+dubnY9abB2c7n/83h9Nji6weK+CGJ4Vim6WyKpKWssgFyyAaV/0Km8Gyx6HW57FJxicDP2+Tt
9Pj8/wOa/wmL5wqd/w6V8heb91e5+mS9+VmLr4vD6qvc/b/j/Mbn/sTi9rvX6szq/tPt/9ju/dzx
/+n2/+74//P6/+3w8hOh/xOW6yCm/iuu/zWv/0m4/XTH/IXK95TP9qPV9bfi/tDn9tfp9OP0/93r
9L3Izy6Vzj22/lrC/mfG/JvJ5JGntAyd6IbX/3zD6GzP/3jV/2uoxHqbqujv8g6MvJTj/2HF5pXV
606zz6Hp/63v/7j1/8Ps88b8/rbj5RKOkE2wr3OGhoKGhv7///Dx8V2alqvm4Zni1YPRvx5uVwyO
X0q2hLTvw8X10gx2H4PXkkuoV5zkoQeADZu7mmzIVEO7HIXbaGfLMPz8+97d2/Px7v///+bl5eHg
4P7+/v39/fT09PLy8u7u7gAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAACwAAAAAaAA7AAAI/gCVCRxI
sKDBgwgTKlzIsKHDhxAjKgwiqs2kSJEgQfqyp2PHLxoxTmojSpTEkyglBrGYcU+el3n09PEDSFKg
mzclAfLTRw/MPV4gjTSZsmhRURchuXwUs88fSYIGubEiqyqAq1gBNLPiRlCgPz197tE4MojRswuD
JHX5UiagQILcNMtKl26zu3etuBgUaKcePXv0QIo0iSjaw8raROKYh6nbuFbmVpVlpbKby4Mya858
eWrlrV0l/fECWDBhw4hPimoJUw9NQVa0Yg6kk6dPmD9xt/Xi52kgKG4GCRLtpTjZNmZTQ5yktLXT
QFNDA+qJe2wkkgkrrmWrx4tv0X6M/gvFrnzh6uaO+wCKOhzs7TzWyUesyDom7z9//EAKOh51eYKK
sdWWH1D15cd78J12GFJKufRXcfwNNtR/ANYXE006UfdSfBQq1lxM3fFHWFlojRBCCA5goMMK5y3V
1B879VGdUMlRqIxaG7kUmHEikVTjQyuAcGIGDmSQwQUYzPBAA1UIKJMfUCI4Vhs2EjTJKrWYwogp
mXSxY0iTTLhQAC2ocKIDHGywgAwYWPDAm3AeIIVztr3E1FiFVSnQJLXc4ksxuujyiy6npNGFYBKK
WRAzKZipAgkp8ACCAyLg0MClDcD5ppIUVNCFFDL1oSF8Qvn3nyi8+KIqMH8aQwwx/66EMQcoVQxG
mI/KBEBCCCSo0MIPLJSJwA6YFvsmBlFkYgopUTxwgQ8XXGBBBRUA0QUXeJp6qi2r2rKLLcAU42qs
WIRhR623YpdDNM4wQ0IOInggrwfFNoCDDl20wooqqaSCCil3SHCBBgQXnAGbFmCAgQMkBKDnLsMU
4wswvPCySy3DuLpJGFiY4YodX6RrUhnOIFDDvPNeqkkXfKzCyssv8+svwM5uYPPNONusAZszEEEE
GoooQsfQdRRdxyJII83I0ow04nQjjkTtCB5cVN3KMBEXA8wuFbMC6Cu5jIJFLsG4oonIQeQQQw4o
a5KsI6moogrMMMvt77+kCPzB3v589+03BxdQ0IFyotyCdTFap7I1K7Z4YskmcIwSTC+9KMHGSD6S
0AIJHkRxByekkIJKv3LPXbfMeOddgQmst+466xoAIUEEEUzAQNBD02H00UkvwnTTT0s9ddV4ZPEK
1hH/qTUnlyDyRi659BJMMLiEgrkoQSwTAjMefPIJ6KKPHnfppfeLCt6cCDFDmjT8AMP7MJywwQW0
1187Aco5osUYyGNtjC+ccFwhzuCK6U0OF2uoQht8FAMEoMADnfge+M7Xrwpa8HyhI0X6JGCwDGhg
fvYLoe1wRzSj9c53THsa1KRGNS6oYQxZ0AXyjKGLUlzCEoeIQxjIRjnKTYESC/7EnjJyYAIRRMF7
4Auf+Cp4vtRxghNOiEAHjxTC+k3gfsp5ghPSAIqMBeoUlkjEIeYgBzjwEBdonEIOgmgWSDlgC0h8
YgabSEcncuITUZQBwYxERftRYAIToEDtbie0EhbthL9TofBa6IT9jeEVgQpUJcZoCDEUcHqUw8UU
ysBGZZQgBAvAgSfimMQMmjJ0T/SeGiKgRw3w8QKz+2Mgp/UALKamC1FYwha1AElJzkEMYiDb5HqB
wE2SRIjR0MEIGoCJUUqwlKd84h0/4QlMRKACezQSLAM5A2pR6wF/JGTudofIFAaPhVW7AxWooIX9
ZSELv4hnJYA5CjQScw1rUP/jMQeCgA/gQA2ecOYzpUnQaVKzmtfM5pEkMIFpebMCtZwA/lJTBR88
YQlRcIITQBHPeNrhCEcwQhPQmM8EALEkAwnBDTBAhWYG1HukTCVMD4oJTBDBAgrNAEOnZYE/vomh
4jQk75KWyHNGrYWO0KUT1tlOWnRUCUdQQhOaoIQ12GEKsVCgEAVSAge88RIufelMxxrQal7iEkLg
oCv5uFOffvOPE0XMMvjggy74IAoZ3UI8aYEEJUh1CkoggxIOUIbCbFUZyczADM4K1rI69rHVxARj
kyDFtRppp9OawR8pAFQS6s6EvSuq0xZZNS444gkZ1SgVQkELWvjMr1QlQgT+pgALG+yTIDrgwAPo
wFiwhtWxNZUsYxVBWYX6YAYT0CwgHwDRB0i0PNGoghTsCoQoaEIYQhCCz7ZLhCYoIAdD+ZEyQqAB
C4xBEb09a3Brmt5LBE0RWYiAB/mo2EBSoJvfdG5QP3vI0JpztOgsLR8y8QTU4jUK2U2wEIagBAWU
AQy3JcgIUqSF97b3wu9VhCXQwErLKpYCDvXmmygQV+UEQLpScKUPfACEFjuBCGuAhQ4gXBLxIjZa
QrBEhtGL3rPyOMOWCHIiOkxfCzT0oc2lwH7J6d+lKTLAVfPIdAu8hCUAwQlCIIMBikAJCEeYIMm4
gAxmkIggB3nHOzazJcb+QIXZ6bHIIPZmT0FMYj2RyUw50EEZRIAASnzheoctSJEekIgyq/nQalaE
E2QXAYHlFANx1iyILYDcJYOWqP9d4VFLi62PgEQkGAl1mI5p44HcYMxoQISqC21oIYcxDUuowOwk
IAMOTDEDGAAnBR5gARyAE5Al1pMytIM5UiuEBxWwQBIOoepmO1sRd/BBBWgnMGo9a758xECmcOBr
QE5Av55lMqadbNThldYjX/h0qEVyvVIDiFpEOIS85b3qOjBBBrODgL4foCZoWVsG2cZAt5fL7ToL
WyAVWeAxA42QScjgAkQoRCHmrYhGgDAC+s54AjbAAQ4s4GDeFHOuvf3/ABwMQBgiUHK4L620TJP2
3J7WSEhG1MmJRKILsJzDxBfxhfLWL+MZn4AGOm5rgj2cWrJ8wAB2sAMRFEMYBtcTRUpCdXcbZDV8
sIAExoAHHuA7At2sYv3Q5PEOQmvXTE/7DlCu8kLyd6gtJzeANw3zPaRb5uwOIkoV0gY2SNsCgG+0
DFJwJFhWMbkDK7qHRcD4xjMeBxMoQAGEHYSpWz0hPlhANHxggWtyYBnMQAYIKvBwCZj+9GCHqAUc
kFMdOF4EOzBAAXoA2JX3d9zAm7u5oxxzW4164doaiAM0rwwU0IAHz4hGAEDfAjH74PTQn4G0EpAA
Z9HX9Y03wAEKcIAB/oDAYQc/CQkcEIBoPAMGzoDBM2KwfGa0QAMXOBLg5y8B6V/gAVNowhQogIEV
61kEDXAAPdADTVAJaKBjtgd3KCR3mrZ7nWZ36kZzx0QIV5AQGNAC5Xd+x6B+7Md8KYBN0oZkziIt
E4AAKTAACtBQ8ZIA3NcBKrAMMRB+RfEAzLAM0aAMz/ACLwANyrcMyNACKXABCwA40VKEFPBwRtYE
cjAHhmAEU5AAAzgFYjAHrHZmCVhODPhyvAeBtkJzNUYIs5AQNLgM5VeBV9CDoQeEIZABICADbviG
FBAtRqYAzCAQAVACOSAACFACMngYFqACNRgAgiiIy+CDLQCEJCAD/yWgAV7ViHF4ATOQAFMABxI3
cWM0B6tWhQjoduIWd7nXgC20hXfHbkOBPRSYECFgAchQg4VYiMyQhikAAjdwAStgAydyIm1yARVA
AQXQASvQhzYSAA2AAav4iq/4g0AYiyRwATRQAiqgAggwAxYgA7t4AAcQAjcIjBTSAgYwAySADOB4
iMkoi7uCAQuQJBYgZj3FfQOwDNpYJSnQAROAAZozjuS4AAsAfzLgAGzyACzYfXX4jlVSAmVAfQ+w
MCRgAyRAAvhIMCmCXNtXAAYQAu4okHryAzaAARNgjQYJJxNAfRF5AAaQAy2QjRYpdWBQBV2QawrA
gpLHfQpgAA1ggiMrYJInKWxIsRhfUAU82ZMj0Iwr8AM3qY3E9ntVV3lDWSUBAQA7
--=_NextPart_000_002F_01C38BE4.0BA78C40
Content-Type: image/gif
Content-Transfer-Encoding: base64
Content-ID: <002401c38b98$9a328d80$b84afb3d@.joohkim>
R0lGODlhDAAMANUAAP////f3//f39+/v9+/v797m987W787W5sXW5rXF76295qW975y175St75St
3pSlzoyl1oSl5oylzoycxXOU3nOMxWOM5mOM3mOE1lqE3mOEvVKE1lp7xVJ71lJ7zlJ7xVJ7vUp7
zkpzzkpzxVJzrUprvUJrxUJrvUJjtTpjtTpjrTparTpapQAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAACwAAAAADAAMAAAIjAABAAhwwMGFCxAQ
CACwkICDDBYSLGjQwQEBhg8zDBAIYIEIBwIQdLjAoOOFgSFMIICwIUMEAxQwCBxhAgKHDh5C6DQA
IIGJEyA4fPAwYoQCAAVKoEgBQsKJEidQ8CyRYumDA1VTqNBQQYXXFQofsPB6AIAKFiweNBTLoiza
BxcFCjgwgQSJCQcWCggIADs=--=_NextPart_000_002F_01C38BE4.0BA78C40--

Check Table Values in the Store Procedure...

HI

I have a problem related Store Procedure, that i am trying to extact a value from Database (Like FirstName,LastName,Email Address) through Store Procedure and Display it in the DropDownList(Like: FirstName LastName ,(xyz@.xyz.com)) , and this is working correctly.

Now i try to check the value at the same time if it is NULL value in the Database then pass EmptyString to the DropDownList Like ("" "" ,(xyz@.xyz.com))\

how i can do that in the store procedure.

Comments will be appreciated.

Use the IsNull function.

|||

You could save the results to a temp table in the stored procedure then do an update replacing all nulls with "" then just return the contents of the temp table. e.g

CREATE TABLE #tmpTable
(
field1as NVARCHAR(200),
field2as integer
)

INSERT INTO #tmpTable (field1,Field2)
SELECT * FROM SelectionTable

UPDATE #tmpTable SET field1 ="" WHERE field1 isnull

SELECT * FROM #TmpTable

DROP #tmpTable

|||

You can use the Isnull function directly in your select query like this:

select firstName , lastName ,IsNull ( email ,'' )as emailfrom <table Name>

This way you are rest assured that for whichever row the email is null, it will automatically be converted to '' ( blank string ). You can write anything like 'Not Available' in the replacement part of the isnull function.

Hope this will help.