another table corruption question

sql2k sp3
Is there a way to just narrow down the rows of data that are corrupted and
delete them as opposed to the other alternatives? (Restore, CheckTable with
Repair%, etc.)
TIA, ChrisR.There are many types of corruption. The page could be bad. So it is not
always possible to delete the bad row.
--
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"ChrisR" <noemail@.bla.com> wrote in message
news:eDCAX%23nSFHA.576@.TK2MSFTNGP15.phx.gbl...
> sql2k sp3
> Is there a way to just narrow down the rows of data that are corrupted and
> delete them as opposed to the other alternatives? (Restore, CheckTable
> with Repair%, etc.)
>
> TIA, ChrisR.
>
>

another table corruption question

sql2k sp3
Is there a way to just narrow down the rows of data that are corrupted and
delete them as opposed to the other alternatives? (Restore, CheckTable with
Repair%, etc.)
TIA, ChrisR.There are many types of corruption. The page could be bad. So it is not
always possible to delete the bad row.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"ChrisR" <noemail@.bla.com> wrote in message
news:eDCAX%23nSFHA.576@.TK2MSFTNGP15.phx.gbl...
> sql2k sp3
> Is there a way to just narrow down the rows of data that are corrupted and
> delete them as opposed to the other alternatives? (Restore, CheckTable
> with Repair%, etc.)
>
> TIA, ChrisR.
>
>

another table corruption question

sql2k sp3
Is there a way to just narrow down the rows of data that are corrupted and
delete them as opposed to the other alternatives? (Restore, CheckTable with
Repair%, etc.)
TIA, ChrisR.
There are many types of corruption. The page could be bad. So it is not
always possible to delete the bad row.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"ChrisR" <noemail@.bla.com> wrote in message
news:eDCAX%23nSFHA.576@.TK2MSFTNGP15.phx.gbl...
> sql2k sp3
> Is there a way to just narrow down the rows of data that are corrupted and
> delete them as opposed to the other alternatives? (Restore, CheckTable
> with Repair%, etc.)
>
> TIA, ChrisR.
>
>

Another Row After SubTotal in Matrix

I have a Matrix in Report, with SubTotals of rows and columns.

I want to add another row below the subtotal (as well as another column to the left) in order to display percentage. Could anyone please tell me how?

There is a work around. you need to add one more column and display percentage in that column, this would add a column after total column and would display the sum of percentage column.

Then you reduce the width of the column (the column that you have manually added) to 0.

|||

First, Thanks for your reply.

I Tried it, but it doesn't work.

When I set the width of the added column to 0, the total row becomes 0 too.

?

Another execution plan question

SELECT t2.col1 FROM Table1 t1 JOIN table2 t2 on t1.col2=t2.col2 and
t1.col3=t2.col3
The total number of rows in Table1 are just 100. But the estimated row count
shows 75,000.
Its a production environment and probably cannot drop any procedure caches .
How can I make the estimated row count to show me the actual count or
atleast drop it to within the 100s range. Table1 has only 1 clustered index
on (col2,col3) . IF i have to use hints, what hints can i use and if not,
what else can i do to reflect the actual count"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:ef9LfUWiDHA.616@.TK2MSFTNGP11.phx.gbl...
> SELECT t2.col1 FROM Table1 t1 JOIN table2 t2 on t1.col2=t2.col2 and
> t1.col3=t2.col3
> The total number of rows in Table1 are just 100. But the estimated row
count
> shows 75,000.
> Its a production environment and probably cannot drop any procedure caches
.
> How can I make the estimated row count to show me the actual count or
> atleast drop it to within the 100s range. Table1 has only 1 clustered
index
> on (col2,col3) . IF i have to use hints, what hints can i use and if not,
> what else can i do to reflect the actual count
How many rows are there in table 2? If there is a one to many relationship
then a single row in table 1 will be counted for every row in table 2 it is
paired with.
If you only want a row to appear once from table 1 then change the query to
SELECT DISTINCT t2.col1 FROM Table1 t1 JOIN table2 t2 on t1.col2=t2.col2 and
t1.col3=t2.col3
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.522 / Virus Database: 320 - Release Date: 29/09/2003|||You didnt follow my question Bob..
When i talk about estimated row count, I mean when i click on the operators
in the execution plan under the clustered index scan of table1 which only
has 100 rows but the estimated row count shown there is around 75000.
Probably at one point in time it was that much and hence the adhoc query
plan is reflecting that. I would like to change it to show me atleast in a
100s range...
"Bob Simms" <bob_simms@.hotmail.com> wrote in message
news:AB7fb.820$Wm6.21@.news-binary.blueyonder.co.uk...
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:ef9LfUWiDHA.616@.TK2MSFTNGP11.phx.gbl...
> > SELECT t2.col1 FROM Table1 t1 JOIN table2 t2 on t1.col2=t2.col2 and
> > t1.col3=t2.col3
> >
> > The total number of rows in Table1 are just 100. But the estimated row
> count
> > shows 75,000.
> > Its a production environment and probably cannot drop any procedure
caches
> .
> > How can I make the estimated row count to show me the actual count or
> > atleast drop it to within the 100s range. Table1 has only 1 clustered
> index
> > on (col2,col3) . IF i have to use hints, what hints can i use and if
not,
> > what else can i do to reflect the actual count
> How many rows are there in table 2? If there is a one to many
relationship
> then a single row in table 1 will be counted for every row in table 2 it
is
> paired with.
> If you only want a row to appear once from table 1 then change the query
to
> SELECT DISTINCT t2.col1 FROM Table1 t1 JOIN table2 t2 on t1.col2=t2.col2
and
> t1.col3=t2.col3
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.522 / Virus Database: 320 - Release Date: 29/09/2003
>|||Did you try updating statistics with fullscan. I'm not sure it'll help, but worth a try...
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Hassan" <fatima_ja@.hotmail.com> wrote in message news:ef9LfUWiDHA.616@.TK2MSFTNGP11.phx.gbl...
> SELECT t2.col1 FROM Table1 t1 JOIN table2 t2 on t1.col2=t2.col2 and
> t1.col3=t2.col3
> The total number of rows in Table1 are just 100. But the estimated row count
> shows 75,000.
> Its a production environment and probably cannot drop any procedure caches .
> How can I make the estimated row count to show me the actual count or
> atleast drop it to within the 100s range. Table1 has only 1 clustered index
> on (col2,col3) . IF i have to use hints, what hints can i use and if not,
> what else can i do to reflect the actual count
>|||I did all that... As I was hoping the plan might change...but no luck...
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:OzlahhXiDHA.2248@.TK2MSFTNGP12.phx.gbl...
> Did you try updating statistics with fullscan. I'm not sure it'll help,
but worth a try...
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
news:ef9LfUWiDHA.616@.TK2MSFTNGP11.phx.gbl...
> > SELECT t2.col1 FROM Table1 t1 JOIN table2 t2 on t1.col2=t2.col2 and
> > t1.col3=t2.col3
> >
> > The total number of rows in Table1 are just 100. But the estimated row
count
> > shows 75,000.
> > Its a production environment and probably cannot drop any procedure
caches .
> > How can I make the estimated row count to show me the actual count or
> > atleast drop it to within the 100s range. Table1 has only 1 clustered
index
> > on (col2,col3) . IF i have to use hints, what hints can i use and if
not,
> > what else can i do to reflect the actual count
> >
> >
>|||On Thu, 2 Oct 2003 21:16:28 -0700, "Hassan" <fatima_ja@.hotmail.com>
wrote:
>SELECT t2.col1 FROM Table1 t1 JOIN table2 t2 on t1.col2=t2.col2 and
>t1.col3=t2.col3
>The total number of rows in Table1 are just 100. But the estimated row count
>shows 75,000.
>Its a production environment and probably cannot drop any procedure caches .
>How can I make the estimated row count to show me the actual count or
>atleast drop it to within the 100s range. Table1 has only 1 clustered index
>on (col2,col3) . IF i have to use hints, what hints can i use and if not,
>what else can i do to reflect the actual count
I seldom even look at the estimated numbers, and even more seldom am
able to make any sense out of them. I suspect many, many bugs, as
well as unexplained complexities, in the numbers displayed.
When you actually run the query, what kind of statistics do you get?
And, how many rows get returned?
Joshua Stern|||you have indexes on the columns in both t1 and t2 AND you have updated
statistics with FULLSCAN on both tables? And you still are getting widely
off estimated row counts showing in the graphical showplan? I just want to
make sure I understand...
that would be bit wierd to be off SOO much on such simple queries if the
columns are indexed and you did FULLSCAN...
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:e9cqgmXiDHA.1796@.TK2MSFTNGP10.phx.gbl...
> I did all that... As I was hoping the plan might change...but no luck...
> "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:OzlahhXiDHA.2248@.TK2MSFTNGP12.phx.gbl...
> > Did you try updating statistics with fullscan. I'm not sure it'll help,
> but worth a try...
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at: http://groups.google.com/groups?oi=djq&as
> ugroup=microsoft.public.sqlserver
> >
> >
> > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:ef9LfUWiDHA.616@.TK2MSFTNGP11.phx.gbl...
> > > SELECT t2.col1 FROM Table1 t1 JOIN table2 t2 on t1.col2=t2.col2 and
> > > t1.col3=t2.col3
> > >
> > > The total number of rows in Table1 are just 100. But the estimated row
> count
> > > shows 75,000.
> > > Its a production environment and probably cannot drop any procedure
> caches .
> > > How can I make the estimated row count to show me the actual count or
> > > atleast drop it to within the 100s range. Table1 has only 1 clustered
> index
> > > on (col2,col3) . IF i have to use hints, what hints can i use and if
> not,
> > > what else can i do to reflect the actual count
> > >
> > >
> >
> >
>|||On Thu, 2 Oct 2003 21:16:28 -0700, "Hassan" <fatima_ja@.hotmail.com>
wrote:
>SELECT t2.col1 FROM Table1 t1 JOIN table2 t2 on t1.col2=t2.col2 and
>t1.col3=t2.col3
>The total number of rows in Table1 are just 100. But the estimated row count
>shows 75,000.
>Its a production environment and probably cannot drop any procedure caches .
>How can I make the estimated row count to show me the actual count or
>atleast drop it to within the 100s range. Table1 has only 1 clustered index
>on (col2,col3) . IF i have to use hints, what hints can i use and if not,
>what else can i do to reflect the actual count
You've got no where clause.
Actually, if it's a clustered index, and the 100 rows remaining are
spread over a space that used to have 75,000 rows, maybe it's telling
you that it has to physically scan the space that would normally take
75k rows? Maybe someone here with more experience with these plans
and clustered indexes can confirm this.
J.|||Dropped all indexes and recreated them to resolve this.. It was weird. The
adhoc query plan was still being cached I guess.
"Brian Moran" <brian@.solidqualitylearning.com> wrote in message
news:OBan2MeiDHA.1200@.TK2MSFTNGP09.phx.gbl...
> you have indexes on the columns in both t1 and t2 AND you have updated
> statistics with FULLSCAN on both tables? And you still are getting widely
> off estimated row counts showing in the graphical showplan? I just want to
> make sure I understand...
> that would be bit wierd to be off SOO much on such simple queries if the
> columns are indexed and you did FULLSCAN...
> --
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www.solidqualitylearning.com
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:e9cqgmXiDHA.1796@.TK2MSFTNGP10.phx.gbl...
> > I did all that... As I was hoping the plan might change...but no luck...
> >
> > "Tibor Karaszi"
> <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> > wrote in message news:OzlahhXiDHA.2248@.TK2MSFTNGP12.phx.gbl...
> > > Did you try updating statistics with fullscan. I'm not sure it'll
help,
> > but worth a try...
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > Archive at: http://groups.google.com/groups?oi=djq&as
> > ugroup=microsoft.public.sqlserver
> > >
> > >
> > > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > news:ef9LfUWiDHA.616@.TK2MSFTNGP11.phx.gbl...
> > > > SELECT t2.col1 FROM Table1 t1 JOIN table2 t2 on t1.col2=t2.col2 and
> > > > t1.col3=t2.col3
> > > >
> > > > The total number of rows in Table1 are just 100. But the estimated
row
> > count
> > > > shows 75,000.
> > > > Its a production environment and probably cannot drop any procedure
> > caches .
> > > > How can I make the estimated row count to show me the actual count
or
> > > > atleast drop it to within the 100s range. Table1 has only 1
clustered
> > index
> > > > on (col2,col3) . IF i have to use hints, what hints can i use and if
> > not,
> > > > what else can i do to reflect the actual count
> > > >
> > > >
> > >
> > >
> >
> >
>|||Hi Hassan.
I think that Bob is probably on the money - I've carefully re-read your
original post, what Bob's responded with & the other posts too, but I think
Bob most likely right.
Does table2 have 75000 rows? If so, I think that you won't get the estimated
rowcount down below this because, critically, your query is selecting
t2.col1 which is from the right hand side (table2) of the join statement.
This means that the estimated rows will be however many rows are in table2 &
as you have no where clause (either on table1 or table2) there is no way for
the optimizer to short cut a full scan of table2.col1. If table2 has 75000
rows, then the only valid estimate is 75000 rows.
Regards,
Greg Linwood
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:e#IhICXiDHA.2984@.TK2MSFTNGP11.phx.gbl...
> You didnt follow my question Bob..
> When i talk about estimated row count, I mean when i click on the
operators
> in the execution plan under the clustered index scan of table1 which only
> has 100 rows but the estimated row count shown there is around 75000.
> Probably at one point in time it was that much and hence the adhoc query
> plan is reflecting that. I would like to change it to show me atleast in a
> 100s range...
>
> "Bob Simms" <bob_simms@.hotmail.com> wrote in message
> news:AB7fb.820$Wm6.21@.news-binary.blueyonder.co.uk...
> > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > news:ef9LfUWiDHA.616@.TK2MSFTNGP11.phx.gbl...
> > > SELECT t2.col1 FROM Table1 t1 JOIN table2 t2 on t1.col2=t2.col2 and
> > > t1.col3=t2.col3
> > >
> > > The total number of rows in Table1 are just 100. But the estimated row
> > count
> > > shows 75,000.
> > > Its a production environment and probably cannot drop any procedure
> caches
> > .
> > > How can I make the estimated row count to show me the actual count or
> > > atleast drop it to within the 100s range. Table1 has only 1 clustered
> > index
> > > on (col2,col3) . IF i have to use hints, what hints can i use and if
> not,
> > > what else can i do to reflect the actual count
> >
> > How many rows are there in table 2? If there is a one to many
> relationship
> > then a single row in table 1 will be counted for every row in table 2 it
> is
> > paired with.
> >
> > If you only want a row to appear once from table 1 then change the query
> to
> > SELECT DISTINCT t2.col1 FROM Table1 t1 JOIN table2 t2 on t1.col2=t2.col2
> and
> > t1.col3=t2.col3
> >
> >
> >
> > --
> > Outgoing mail is certified Virus Free.
> > Checked by AVG anti-virus system (http://www.grisoft.com).
> > Version: 6.0.522 / Virus Database: 320 - Release Date: 29/09/2003
> >
> >
>|||Table T2 has 20 millions rows and the estimated row count for that shows
around the same. But T1 has only 100 rows and the estimated row count for T1
showed 75000. I dropped and recreated all indexes and it worked fine now.
Just some plan being cached I guess.
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:%234MVFakiDHA.2536@.TK2MSFTNGP10.phx.gbl...
> Hi Hassan.
> I think that Bob is probably on the money - I've carefully re-read your
> original post, what Bob's responded with & the other posts too, but I
think
> Bob most likely right.
> Does table2 have 75000 rows? If so, I think that you won't get the
estimated
> rowcount down below this because, critically, your query is selecting
> t2.col1 which is from the right hand side (table2) of the join statement.
> This means that the estimated rows will be however many rows are in table2
&
> as you have no where clause (either on table1 or table2) there is no way
for
> the optimizer to short cut a full scan of table2.col1. If table2 has 75000
> rows, then the only valid estimate is 75000 rows.
> Regards,
> Greg Linwood
> SQL Server MVP
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:e#IhICXiDHA.2984@.TK2MSFTNGP11.phx.gbl...
> > You didnt follow my question Bob..
> >
> > When i talk about estimated row count, I mean when i click on the
> operators
> > in the execution plan under the clustered index scan of table1 which
only
> > has 100 rows but the estimated row count shown there is around 75000.
> > Probably at one point in time it was that much and hence the adhoc query
> > plan is reflecting that. I would like to change it to show me atleast in
a
> > 100s range...
> >
> >
> > "Bob Simms" <bob_simms@.hotmail.com> wrote in message
> > news:AB7fb.820$Wm6.21@.news-binary.blueyonder.co.uk...
> > > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > > news:ef9LfUWiDHA.616@.TK2MSFTNGP11.phx.gbl...
> > > > SELECT t2.col1 FROM Table1 t1 JOIN table2 t2 on t1.col2=t2.col2 and
> > > > t1.col3=t2.col3
> > > >
> > > > The total number of rows in Table1 are just 100. But the estimated
row
> > > count
> > > > shows 75,000.
> > > > Its a production environment and probably cannot drop any procedure
> > caches
> > > .
> > > > How can I make the estimated row count to show me the actual count
or
> > > > atleast drop it to within the 100s range. Table1 has only 1
clustered
> > > index
> > > > on (col2,col3) . IF i have to use hints, what hints can i use and if
> > not,
> > > > what else can i do to reflect the actual count
> > >
> > > How many rows are there in table 2? If there is a one to many
> > relationship
> > > then a single row in table 1 will be counted for every row in table 2
it
> > is
> > > paired with.
> > >
> > > If you only want a row to appear once from table 1 then change the
query
> > to
> > > SELECT DISTINCT t2.col1 FROM Table1 t1 JOIN table2 t2 on
t1.col2=t2.col2
> > and
> > > t1.col3=t2.col3
> > >
> > >
> > >
> > > --
> > > Outgoing mail is certified Virus Free.
> > > Checked by AVG anti-virus system (http://www.grisoft.com).
> > > Version: 6.0.522 / Virus Database: 320 - Release Date: 29/09/2003
> > >
> > >
> >
> >
>|||On Fri, 3 Oct 2003 17:14:09 -0700, "Hassan" <fatima_ja@.hotmail.com>
wrote:
>Dropped all indexes and recreated them to resolve this.. It was weird. The
>adhoc query plan was still being cached I guess.
Did you drop a clustered index on table1?
J.

Another 101 question

With a plain select if no rows are returned then the row count is 0, but
with a select that assigns to a variable you will get a rowcount of one even
though no actual row was found. I am guessing that is because of assigning
to a variable SQL will return an empty result set, which is a result set of
1, is this correct?
i.e. SET @.mycol =
(SELECT col FROM myTable WHERE id = 1)
Under these circumstances is the best technique to just test the variable
@.mycol for a null value
OR write the select like:
IF EXISTS
(SELECT mycol FROM myTable
WHERE myID = 3)
BEGIN
SET @.mycol =
(SELECT mycol FROM myTable
WHERE myID = 3)
PRINT '@.mycol : ' + CAST(mycol as varchar(15))
END
ELSE
PRINT 'ROW DOES NOT EXIST'
Or is there an even better and/or more professional way to do it?Thank you! I was making my self nuts with the what if's, it seemed that
going beyond validating the current entry could turn into a never ending
task. :)
Except for the news groups I am learning in a vacuum, it is not like
learning in maintenance of a production environment where you get to see
what others have done.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eb6rNDKvFHA.3896@.TK2MSFTNGP15.phx.gbl...
> Thanks Erland, I really should have mentioned that.
> Dazed,
> If you have a Unique constraint you should not have to test for
> duplicates when selecting the values out. The constraint will make sure
> there are no duplicates in the first place.
> --
> Andrew J. Kelly SQL MVP
>
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns96D61468ABF3Yazorman@.127.0.0.1...
>|||What is the desired behavior? DO you simply want to know if one or more
rows exist or not? If so then always use EXISTS.
Andrew J. Kelly SQL MVP
"DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
news:eXq$4VHvFHA.2072@.TK2MSFTNGP14.phx.gbl...
> With a plain select if no rows are returned then the row count is 0, but
> with a select that assigns to a variable you will get a rowcount of one
> even though no actual row was found. I am guessing that is because of
> assigning to a variable SQL will return an empty result set, which is a
> result set of 1, is this correct?
> i.e. SET @.mycol =
> (SELECT col FROM myTable WHERE id = 1)
> Under these circumstances is the best technique to just test the variable
> @.mycol for a null value
> OR write the select like:
> IF EXISTS
> (SELECT mycol FROM myTable
> WHERE myID = 3)
> BEGIN
> SET @.mycol =
> (SELECT mycol FROM myTable
> WHERE myID = 3)
> PRINT '@.mycol : ' + CAST(mycol as varchar(15))
> END
> ELSE
> PRINT 'ROW DOES NOT EXIST'
> Or is there an even better and/or more professional way to do it?
>|||When you use a scalar subquery, you can get a one-row, one-column table
that is converted to a scalar; you can get an empty table that is
converted to a NULL; you can get a multi-row, one-column table that
gives a cardinality error when you try to put it into a scalar.|||In this paticular situation I want the field value. I was wondering if it
was better to just do the select and test the variable for a null or do a
EXISTS SELECT and then if it does exist select into the variable.
I'm just learning and trying to find the best way to build a mouse trap,
I've found this news group very helpful. i.e. I had a 130 line procedure
yesterday that was cut down to about 30 from information obtained from the
group. There are a lot of ways to get things to work, some are a lot better
than others. I'm looking for the right ways.
Thank you!!
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ObA0rlHvFHA.2504@.TK2MSFTNGP15.phx.gbl...
> What is the desired behavior? DO you simply want to know if one or more
> rows exist or not? If so then always use EXISTS.
> --
> Andrew J. Kelly SQL MVP
>
> "DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
> news:eXq$4VHvFHA.2072@.TK2MSFTNGP14.phx.gbl...
>|||OK then there is no need for an EXISTS first. You can do this several ways
like:
SET @.mycol = (SELECT col FROM myTable WHERE id = 1)
or
SELECT @.mycol = Col FROM myTable WHERE id = 1
IF @.myCol IS NOT NULL
BEGIN
-- Do your thing here
END
ELSE
...
Also make sure that there will only be at most 1 row returned. As long as
ID is a unique value you should be ok.
Andrew J. Kelly SQL MVP
"DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
news:uQq5RzHvFHA.3548@.tk2msftngp13.phx.gbl...
> In this paticular situation I want the field value. I was wondering if it
> was better to just do the select and test the variable for a null or do a
> EXISTS SELECT and then if it does exist select into the variable.
> I'm just learning and trying to find the best way to build a mouse trap,
> I've found this news group very helpful. i.e. I had a 130 line procedure
> yesterday that was cut down to about 30 from information obtained from the
> group. There are a lot of ways to get things to work, some are a lot
> better than others. I'm looking for the right ways.
> Thank you!!
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:ObA0rlHvFHA.2504@.TK2MSFTNGP15.phx.gbl...
>|||Thank you again! id is unique, but I am checking if rowcount is > 1 anyways
and plan on throwing an error if it is. I plan on temporarily removing the
unique constraint and putting in a dupe record to test it.
Is that going too far?
Or is it a good idea to try to handle a situation that in theory should
never happen?
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OMkfpNIvFHA.3256@.TK2MSFTNGP09.phx.gbl...
> OK then there is no need for an EXISTS first. You can do this several
> ways like:
> SET @.mycol = (SELECT col FROM myTable WHERE id = 1)
> or
> SELECT @.mycol = Col FROM myTable WHERE id = 1
>
> IF @.myCol IS NOT NULL
> BEGIN
> -- Do your thing here
> END
> ELSE
> ...
> Also make sure that there will only be at most 1 row returned. As long as
> ID is a unique value you should be ok.
> --
> Andrew J. Kelly SQL MVP
>
> "DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
> news:uQq5RzHvFHA.3548@.tk2msftngp13.phx.gbl...
>|||DazedAndConfused (AceMagoo61@.yahoo.com) writes:
> Thank you again! id is unique, but I am checking if rowcount is > 1
> anyways and plan on throwing an error if it is. I plan on temporarily
> removing the unique constraint and putting in a dupe record to test it.
> Is that going too far?
> Or is it a good idea to try to handle a situation that in theory should
> never happen?
OK, now we are in for a real treat! I will show you how to do it, and if
that does not convince you that are going too far, nothing will. :-)
Andy showed you two ways, but they are a little different, which he failed
to tell. Let's look at them again:
0 rows -> @.mycol is assigned NULL, @.@.rowcount = 1
1 rows -> @.mycol is assigned the value, @.@.rowcount = 1
many rows -> You will get an error, "subquery returned more than one value".
0 rows -> @.mycol unchanged(!), @.@.rowcount = 0
1 row -> @.mycol assigned the value, @.@.rowcount = 1
many rows -> @.mycol gets the last value in the result set, which that
is undefined unless you have an ORDER BY. @.@.rowcount is
set to the number of matching rows.
Look at 0 rows again:
SELECT @.mycol = 4711
SELECT @.mycol = Col FROM myTable WHERE id = 1
If there is no row with id = 1, @.mycol will remain 4711, it will not be
set to NULL.
If you really want to check for duplicates, and handle the situation
yourself, it is the SELECT assignment you want to use.
If you keep the constraints, which you should unless you have very good
reasons, the SET method is a little safer. Then again, if you know how
SELECT behaves you can be careful make sure variable is NULL before
you use it. (Yet then again, that is a trap that even season T-SQL
programmers fall into, every now and then!)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland, I really should have mentioned that.
Dazed,
If you have a Unique constraint you should not have to test for
duplicates when selecting the values out. The constraint will make sure
there are no duplicates in the first place.
Andrew J. Kelly SQL MVP
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96D61468ABF3Yazorman@.127.0.0.1...
> DazedAndConfused (AceMagoo61@.yahoo.com) writes:
> OK, now we are in for a real treat! I will show you how to do it, and if
> that does not convince you that are going too far, nothing will. :-)
> Andy showed you two ways, but they are a little different, which he failed
> to tell. Let's look at them again:
>
> 0 rows -> @.mycol is assigned NULL, @.@.rowcount = 1
> 1 rows -> @.mycol is assigned the value, @.@.rowcount = 1
> many rows -> You will get an error, "subquery returned more than one
> value".
>
> 0 rows -> @.mycol unchanged(!), @.@.rowcount = 0
> 1 row -> @.mycol assigned the value, @.@.rowcount = 1
> many rows -> @.mycol gets the last value in the result set, which that
> is undefined unless you have an ORDER BY. @.@.rowcount is
> set to the number of matching rows.
> Look at 0 rows again:
> SELECT @.mycol = 4711
> SELECT @.mycol = Col FROM myTable WHERE id = 1
> If there is no row with id = 1, @.mycol will remain 4711, it will not be
> set to NULL.
> If you really want to check for duplicates, and handle the situation
> yourself, it is the SELECT assignment you want to use.
> If you keep the constraints, which you should unless you have very good
> reasons, the SET method is a little safer. Then again, if you know how
> SELECT behaves you can be careful make sure variable is NULL before
> you use it. (Yet then again, that is a trap that even season T-SQL
> programmers fall into, every now and then!)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
>|||Thank you for the SET/SELECT behavior. Your reply seemed to imply (reading
in between the lines) that since the the id is UNIQUE don't bother to check
for multiple rows, SQL will error anyways in the unlikely event.
If there are duplicates in a unique column, then there is data corruption
anyways, pretty messages aren't really going to help the application faling,
it is time to contact the DBA to see why the data is corrupt.
Is that right?
I'm going nuts creating a procedure that checks for both bad and/or
duplicate data being passed into it and handling for corrupt database
information that should not happen. Seems like opening pandora's box when I
try to code pretty returns to notify the application that the database is
corrupt.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96D61468ABF3Yazorman@.127.0.0.1...
> DazedAndConfused (AceMagoo61@.yahoo.com) writes:
> OK, now we are in for a real treat! I will show you how to do it, and if
> that does not convince you that are going too far, nothing will. :-)
> Andy showed you two ways, but they are a little different, which he failed
> to tell. Let's look at them again:
>
> 0 rows -> @.mycol is assigned NULL, @.@.rowcount = 1
> 1 rows -> @.mycol is assigned the value, @.@.rowcount = 1
> many rows -> You will get an error, "subquery returned more than one
> value".
>
> 0 rows -> @.mycol unchanged(!), @.@.rowcount = 0
> 1 row -> @.mycol assigned the value, @.@.rowcount = 1
> many rows -> @.mycol gets the last value in the result set, which that
> is undefined unless you have an ORDER BY. @.@.rowcount is
> set to the number of matching rows.
> Look at 0 rows again:
> SELECT @.mycol = 4711
> SELECT @.mycol = Col FROM myTable WHERE id = 1
> If there is no row with id = 1, @.mycol will remain 4711, it will not be
> set to NULL.
> If you really want to check for duplicates, and handle the situation
> yourself, it is the SELECT assignment you want to use.
> If you keep the constraints, which you should unless you have very good
> reasons, the SET method is a little safer. Then again, if you know how
> SELECT behaves you can be careful make sure variable is NULL before
> you use it. (Yet then again, that is a trap that even season T-SQL
> programmers fall into, every now and then!)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
>

Andrea ... MSDE Error ! ... Contd ...

Hi,
I checked the links, you suggested me to research on this error “Unknown
token received from SQL Server”.
I am trying to insert 8-10 million rows in the table. I am using a while
loop to insert the row into a single table and not using cursor (Website
suggested to use LOCAL FAST FORWARD). The process was running smooth for 4.3
million rows inserted.
I am using a field VARBINARY(20) and the other fields are VARCHAR, TINYINT &
DATETIME if that helps.
Please advice,
David
hi David,
"David" <David@.discussions.microsoft.com> ha scritto nel messaggio
news:DBFF3C8B-81F2-4655-B645-3CD21B679B84@.microsoft.com
> Hi,
> I checked the links, you suggested me to research on this error
> “Unknown token received from SQL Server”.
> I am trying to insert 8-10 million rows in the table. I am using a
> while loop to insert the row into a single table and not using cursor
> (Website suggested to use LOCAL FAST FORWARD). The process was
> running smooth for 4.3 million rows inserted.
> I am using a field VARBINARY(20) and the other fields are VARCHAR,
> TINYINT & DATETIME if that helps.
> Please advice,
> David
perhaps break your transaction in smaller transactions every n rows you have
to insert...
say every 50,000 rows...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply