(Optimizer) - Number of Indexes Per Table

Hi all,
We were all told that SQL 7.0 and up could use more than one index per table
during query optimization. In practice, how often does this actually occur?
If and when it does occur, how does this appear in the output of the 'Show
Estimated Execution Plan?". It seems to us that this almost never occurs.
We're seeing the first index, and then after that all HASH joins and such.
Thanks!
James HokesHi James
It happens when you have two where clauses referencing two different columns
with nonclustered indexes, and for each, there are only a few rows that
satisfy the condition.
In the showplan output, it looks like a join, but both tables are the same
table, and it's usually a hash join. SQL Server has build an internal
worktable from the results of using each of the indexes, and then those two
worktables have to be joined. SInce there are no good indexes on the
worktables, a hash join is used to find the rows in common between the
worktables.
Here is an example
USE Northwind
select * into od from [order details]
create index qnt_index on od(quantity)
create index price_index on od(unitprice)
go
select * from od
where quantity = 4 and unitprice = 6
Look at the plan for the select, after building the table and the two
indexes.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"James Hokes" <no_spam@.thank_you.com> wrote in message
news:#bXed3KxDHA.1740@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> We were all told that SQL 7.0 and up could use more than one index per
table
> during query optimization. In practice, how often does this actually
occur?
> If and when it does occur, how does this appear in the output of the 'Show
> Estimated Execution Plan?". It seems to us that this almost never occurs.
> We're seeing the first index, and then after that all HASH joins and such.
> Thanks!
> James Hokes
>|||Sounds like you have a complicated multi-table join. After the first couple
joins all you have are work tables (intermediate data results) which have no
indexes. In this case it may be necessary to do hash joins due to the lack
of indexes. The key is to whittle down the result sets to a manageable set
in the first joins using the appropriate indexes so the joining of the
intermediate tables are relatively painless. Maybe if you post a query
example and the associated query plan we can cnfirm or deny that theory.
--
Andrew J. Kelly SQL MVP
"James Hokes" <no_spam@.thank_you.com> wrote in message
news:%23bXed3KxDHA.1740@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> We were all told that SQL 7.0 and up could use more than one index per
table
> during query optimization. In practice, how often does this actually
occur?
> If and when it does occur, how does this appear in the output of the 'Show
> Estimated Execution Plan?". It seems to us that this almost never occurs.
> We're seeing the first index, and then after that all HASH joins and such.
> Thanks!
> James Hokes
>|||Kalen,
Interesting that it would only happen for 'a few rows'.
That being the case, I suspect that this is due to 'total page reads', i.e.
query cost.
Is that a safe assumption?
Thanks,
James Hokes
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:e2zkDXLxDHA.1932@.TK2MSFTNGP09.phx.gbl...
> Hi James
> It happens when you have two where clauses referencing two different
columns
> with nonclustered indexes, and for each, there are only a few rows that
> satisfy the condition.
> In the showplan output, it looks like a join, but both tables are the same
> table, and it's usually a hash join. SQL Server has build an internal
> worktable from the results of using each of the indexes, and then those
two
> worktables have to be joined. SInce there are no good indexes on the
> worktables, a hash join is used to find the rows in common between the
> worktables.
> Here is an example
> USE Northwind
> select * into od from [order details]
> create index qnt_index on od(quantity)
> create index price_index on od(unitprice)
> go
> select * from od
> where quantity = 4 and unitprice = 6
> Look at the plan for the select, after building the table and the two
> indexes.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "James Hokes" <no_spam@.thank_you.com> wrote in message
> news:#bXed3KxDHA.1740@.TK2MSFTNGP12.phx.gbl...
> > Hi all,
> >
> > We were all told that SQL 7.0 and up could use more than one index per
> table
> > during query optimization. In practice, how often does this actually
> occur?
> >
> > If and when it does occur, how does this appear in the output of the
'Show
> > Estimated Execution Plan?". It seems to us that this almost never
occurs.
> > We're seeing the first index, and then after that all HASH joins and
such.
> >
> > Thanks!
> > James Hokes
> >
> >
>|||This is a general principle for any use of nonclustered indexes. They will
only be cost effective if there are only a few rows that meet the condition,
so only a few pages need to be read. If you need to use a nc index to access
100's of rows, which means 100s of pages, it is often considered by the
optimizer to be more cost effective to just scan the table.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"James Hokes" <no_spam@.thank_you.com> wrote in message
news:eoTyWLMxDHA.3208@.tk2msftngp13.phx.gbl...
> Kalen,
> Interesting that it would only happen for 'a few rows'.
> That being the case, I suspect that this is due to 'total page reads',
i.e.
> query cost.
> Is that a safe assumption?
> Thanks,
> James Hokes
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:e2zkDXLxDHA.1932@.TK2MSFTNGP09.phx.gbl...
> > Hi James
> >
> > It happens when you have two where clauses referencing two different
> columns
> > with nonclustered indexes, and for each, there are only a few rows that
> > satisfy the condition.
> >
> > In the showplan output, it looks like a join, but both tables are the
same
> > table, and it's usually a hash join. SQL Server has build an internal
> > worktable from the results of using each of the indexes, and then those
> two
> > worktables have to be joined. SInce there are no good indexes on the
> > worktables, a hash join is used to find the rows in common between the
> > worktables.
> >
> > Here is an example
> >
> > USE Northwind
> >
> > select * into od from [order details]
> > create index qnt_index on od(quantity)
> > create index price_index on od(unitprice)
> > go
> > select * from od
> > where quantity = 4 and unitprice = 6
> >
> > Look at the plan for the select, after building the table and the two
> > indexes.
> >
> > --
> > HTH
> > --
> > Kalen Delaney
> > SQL Server MVP
> > www.SolidQualityLearning.com
> >
> >
> > "James Hokes" <no_spam@.thank_you.com> wrote in message
> > news:#bXed3KxDHA.1740@.TK2MSFTNGP12.phx.gbl...
> > > Hi all,
> > >
> > > We were all told that SQL 7.0 and up could use more than one index per
> > table
> > > during query optimization. In practice, how often does this actually
> > occur?
> > >
> > > If and when it does occur, how does this appear in the output of the
> 'Show
> > > Estimated Execution Plan?". It seems to us that this almost never
> occurs.
> > > We're seeing the first index, and then after that all HASH joins and
> such.
> > >
> > > Thanks!
> > > James Hokes
> > >
> > >
> >
> >
>|||Andrew,
Right on. We're talking multi-table, multi-column JOIN, multi-column WHERE
clause, multi-column output, multi-million rows. My main curiosity revolved
around the 'multiple tables per query'. I had pretty much put that out of my
mind for the last few years, because it never happens in this environment.
One of our team asked about it this morning during a debugging session, and
I remarked that I never see it. As for whittling the result sets down,
there's just no way.
Oh, I should mention that there's also no possibility of building covering
indices here, because of disk space constraints, as well as the constant
influx of new data, and the need for high UPDATE performance.
Hence, we got curious as to when it actually _would_ happen, and by the
sounds of it, pretty much never. :-)
Thanks,
James Hokes.
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:eiAOiZLxDHA.2360@.TK2MSFTNGP10.phx.gbl...
> Sounds like you have a complicated multi-table join. After the first
couple
> joins all you have are work tables (intermediate data results) which have
no
> indexes. In this case it may be necessary to do hash joins due to the
lack
> of indexes. The key is to whittle down the result sets to a manageable
set
> in the first joins using the appropriate indexes so the joining of the
> intermediate tables are relatively painless. Maybe if you post a query
> example and the associated query plan we can cnfirm or deny that theory.
> --
> Andrew J. Kelly SQL MVP
>
> "James Hokes" <no_spam@.thank_you.com> wrote in message
> news:%23bXed3KxDHA.1740@.TK2MSFTNGP12.phx.gbl...
> > Hi all,
> >
> > We were all told that SQL 7.0 and up could use more than one index per
> table
> > during query optimization. In practice, how often does this actually
> occur?
> >
> > If and when it does occur, how does this appear in the output of the
'Show
> > Estimated Execution Plan?". It seems to us that this almost never
occurs.
> > We're seeing the first index, and then after that all HASH joins and
such.
> >
> > Thanks!
> > James Hokes
> >
> >
>|||However, it is not true that you won't have indexes to use even when you
have a multi-table join. If your join density (the average number of
duplicates on your join column) is not too high, and you have an index on
the join column, I have seen cases where an index is used for every table in
a 5 or 6 table join.
Have you tried index tuning wizard?
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"James Hokes" <no_spam@.thank_you.com> wrote in message
news:e6sE9NMxDHA.1740@.TK2MSFTNGP12.phx.gbl...
> Andrew,
> Right on. We're talking multi-table, multi-column JOIN, multi-column WHERE
> clause, multi-column output, multi-million rows. My main curiosity
revolved
> around the 'multiple tables per query'. I had pretty much put that out of
my
> mind for the last few years, because it never happens in this environment.
> One of our team asked about it this morning during a debugging session,
and
> I remarked that I never see it. As for whittling the result sets down,
> there's just no way.
> Oh, I should mention that there's also no possibility of building covering
> indices here, because of disk space constraints, as well as the constant
> influx of new data, and the need for high UPDATE performance.
> Hence, we got curious as to when it actually _would_ happen, and by the
> sounds of it, pretty much never. :-)
> Thanks,
> James Hokes.
>
> "Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> news:eiAOiZLxDHA.2360@.TK2MSFTNGP10.phx.gbl...
> > Sounds like you have a complicated multi-table join. After the first
> couple
> > joins all you have are work tables (intermediate data results) which
have
> no
> > indexes. In this case it may be necessary to do hash joins due to the
> lack
> > of indexes. The key is to whittle down the result sets to a manageable
> set
> > in the first joins using the appropriate indexes so the joining of the
> > intermediate tables are relatively painless. Maybe if you post a query
> > example and the associated query plan we can cnfirm or deny that theory.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "James Hokes" <no_spam@.thank_you.com> wrote in message
> > news:%23bXed3KxDHA.1740@.TK2MSFTNGP12.phx.gbl...
> > > Hi all,
> > >
> > > We were all told that SQL 7.0 and up could use more than one index per
> > table
> > > during query optimization. In practice, how often does this actually
> > occur?
> > >
> > > If and when it does occur, how does this appear in the output of the
> 'Show
> > > Estimated Execution Plan?". It seems to us that this almost never
> occurs.
> > > We're seeing the first index, and then after that all HASH joins and
> such.
> > >
> > > Thanks!
> > > James Hokes
> > >
> > >
> >
> >
>|||Right.
I never said it wasn't using indexes; all of our queries do. I've tuned them
all.
I was just wondering about the 'multiple indexes per table' in the optimizer
step,
since we never see it happen. My original post referenced the fact that we
_are_ seeing the one index used per table,
but never multiple.
Thanks,
James Hokes
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:uwIYkmMxDHA.1912@.TK2MSFTNGP09.phx.gbl...
> However, it is not true that you won't have indexes to use even when you
> have a multi-table join. If your join density (the average number of
> duplicates on your join column) is not too high, and you have an index on
> the join column, I have seen cases where an index is used for every table
in
> a 5 or 6 table join.
> Have you tried index tuning wizard?
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "James Hokes" <no_spam@.thank_you.com> wrote in message
> news:e6sE9NMxDHA.1740@.TK2MSFTNGP12.phx.gbl...
> > Andrew,
> >
> > Right on. We're talking multi-table, multi-column JOIN, multi-column
WHERE
> > clause, multi-column output, multi-million rows. My main curiosity
> revolved
> > around the 'multiple tables per query'. I had pretty much put that out
of
> my
> > mind for the last few years, because it never happens in this
environment.
> > One of our team asked about it this morning during a debugging session,
> and
> > I remarked that I never see it. As for whittling the result sets down,
> > there's just no way.
> >
> > Oh, I should mention that there's also no possibility of building
covering
> > indices here, because of disk space constraints, as well as the constant
> > influx of new data, and the need for high UPDATE performance.
> >
> > Hence, we got curious as to when it actually _would_ happen, and by the
> > sounds of it, pretty much never. :-)
> >
> > Thanks,
> > James Hokes.
> >
> >
> > "Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> > news:eiAOiZLxDHA.2360@.TK2MSFTNGP10.phx.gbl...
> > > Sounds like you have a complicated multi-table join. After the first
> > couple
> > > joins all you have are work tables (intermediate data results) which
> have
> > no
> > > indexes. In this case it may be necessary to do hash joins due to the
> > lack
> > > of indexes. The key is to whittle down the result sets to a
manageable
> > set
> > > in the first joins using the appropriate indexes so the joining of the
> > > intermediate tables are relatively painless. Maybe if you post a
query
> > > example and the associated query plan we can cnfirm or deny that
theory.
> > >
> > > --
> > > Andrew J. Kelly SQL MVP
> > >
> > >
> > > "James Hokes" <no_spam@.thank_you.com> wrote in message
> > > news:%23bXed3KxDHA.1740@.TK2MSFTNGP12.phx.gbl...
> > > > Hi all,
> > > >
> > > > We were all told that SQL 7.0 and up could use more than one index
per
> > > table
> > > > during query optimization. In practice, how often does this actually
> > > occur?
> > > >
> > > > If and when it does occur, how does this appear in the output of the
> > 'Show
> > > > Estimated Execution Plan?". It seems to us that this almost never
> > occurs.
> > > > We're seeing the first index, and then after that all HASH joins and
> > such.
> > > >
> > > > Thanks!
> > > > James Hokes
> > > >
> > > >
> > >
> > >
> >
> >
>|||Sorry, didn't mean to make it sound like it couldn't use indexes. Just
that it can and does happen<g>.
--
Andrew J. Kelly SQL MVP
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:uwIYkmMxDHA.1912@.TK2MSFTNGP09.phx.gbl...
> However, it is not true that you won't have indexes to use even when you
> have a multi-table join. If your join density (the average number of
> duplicates on your join column) is not too high, and you have an index on
> the join column, I have seen cases where an index is used for every table
in
> a 5 or 6 table join.
> Have you tried index tuning wizard?
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "James Hokes" <no_spam@.thank_you.com> wrote in message
> news:e6sE9NMxDHA.1740@.TK2MSFTNGP12.phx.gbl...
> > Andrew,
> >
> > Right on. We're talking multi-table, multi-column JOIN, multi-column
WHERE
> > clause, multi-column output, multi-million rows. My main curiosity
> revolved
> > around the 'multiple tables per query'. I had pretty much put that out
of
> my
> > mind for the last few years, because it never happens in this
environment.
> > One of our team asked about it this morning during a debugging session,
> and
> > I remarked that I never see it. As for whittling the result sets down,
> > there's just no way.
> >
> > Oh, I should mention that there's also no possibility of building
covering
> > indices here, because of disk space constraints, as well as the constant
> > influx of new data, and the need for high UPDATE performance.
> >
> > Hence, we got curious as to when it actually _would_ happen, and by the
> > sounds of it, pretty much never. :-)
> >
> > Thanks,
> > James Hokes.
> >
> >
> > "Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> > news:eiAOiZLxDHA.2360@.TK2MSFTNGP10.phx.gbl...
> > > Sounds like you have a complicated multi-table join. After the first
> > couple
> > > joins all you have are work tables (intermediate data results) which
> have
> > no
> > > indexes. In this case it may be necessary to do hash joins due to the
> > lack
> > > of indexes. The key is to whittle down the result sets to a
manageable
> > set
> > > in the first joins using the appropriate indexes so the joining of the
> > > intermediate tables are relatively painless. Maybe if you post a
query
> > > example and the associated query plan we can cnfirm or deny that
theory.
> > >
> > > --
> > > Andrew J. Kelly SQL MVP
> > >
> > >
> > > "James Hokes" <no_spam@.thank_you.com> wrote in message
> > > news:%23bXed3KxDHA.1740@.TK2MSFTNGP12.phx.gbl...
> > > > Hi all,
> > > >
> > > > We were all told that SQL 7.0 and up could use more than one index
per
> > > table
> > > > during query optimization. In practice, how often does this actually
> > > occur?
> > > >
> > > > If and when it does occur, how does this appear in the output of the
> > 'Show
> > > > Estimated Execution Plan?". It seems to us that this almost never
> > occurs.
> > > > We're seeing the first index, and then after that all HASH joins and
> > such.
> > > >
> > > > Thanks!
> > > > James Hokes
> > > >
> > > >
> > >
> > >
> >
> >
>|||No harm done!
I just didn't want people to spend any of their newsgroup time on that part
of the thread.
I know I only get to go on here a little bit, so I know your time and the
time of others is also valuable.
Thanks for yours!
James Hokes
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:uev3vyRxDHA.2520@.TK2MSFTNGP10.phx.gbl...
> Sorry, didn't mean to make it sound like it couldn't use indexes. Just
> that it can and does happen<g>.
> --
> Andrew J. Kelly SQL MVP
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:uwIYkmMxDHA.1912@.TK2MSFTNGP09.phx.gbl...
> > However, it is not true that you won't have indexes to use even when you
> > have a multi-table join. If your join density (the average number of
> > duplicates on your join column) is not too high, and you have an index
on
> > the join column, I have seen cases where an index is used for every
table
> in
> > a 5 or 6 table join.
> >
> > Have you tried index tuning wizard?
> >
> > --
> > HTH
> > --
> > Kalen Delaney
> > SQL Server MVP
> > www.SolidQualityLearning.com
> >
> >
> > "James Hokes" <no_spam@.thank_you.com> wrote in message
> > news:e6sE9NMxDHA.1740@.TK2MSFTNGP12.phx.gbl...
> > > Andrew,
> > >
> > > Right on. We're talking multi-table, multi-column JOIN, multi-column
> WHERE
> > > clause, multi-column output, multi-million rows. My main curiosity
> > revolved
> > > around the 'multiple tables per query'. I had pretty much put that out
> of
> > my
> > > mind for the last few years, because it never happens in this
> environment.
> > > One of our team asked about it this morning during a debugging
session,
> > and
> > > I remarked that I never see it. As for whittling the result sets down,
> > > there's just no way.
> > >
> > > Oh, I should mention that there's also no possibility of building
> covering
> > > indices here, because of disk space constraints, as well as the
constant
> > > influx of new data, and the need for high UPDATE performance.
> > >
> > > Hence, we got curious as to when it actually _would_ happen, and by
the
> > > sounds of it, pretty much never. :-)
> > >
> > > Thanks,
> > > James Hokes.
> > >
> > >
> > > "Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> > > news:eiAOiZLxDHA.2360@.TK2MSFTNGP10.phx.gbl...
> > > > Sounds like you have a complicated multi-table join. After the
first
> > > couple
> > > > joins all you have are work tables (intermediate data results) which
> > have
> > > no
> > > > indexes. In this case it may be necessary to do hash joins due to
the
> > > lack
> > > > of indexes. The key is to whittle down the result sets to a
> manageable
> > > set
> > > > in the first joins using the appropriate indexes so the joining of
the
> > > > intermediate tables are relatively painless. Maybe if you post a
> query
> >
> > > > example and the associated query plan we can cnfirm or deny that
> theory.
> > > >
> > > > --
> > > > Andrew J. Kelly SQL MVP
> > > >
> > > >
> > > > "James Hokes" <no_spam@.thank_you.com> wrote in message
> > > > news:%23bXed3KxDHA.1740@.TK2MSFTNGP12.phx.gbl...
> > > > > Hi all,
> > > > >
> > > > > We were all told that SQL 7.0 and up could use more than one index
> per
> > > > table
> > > > > during query optimization. In practice, how often does this
actually
> > > > occur?
> > > > >
> > > > > If and when it does occur, how does this appear in the output of
the
> > > 'Show
> > > > > Estimated Execution Plan?". It seems to us that this almost never
> > > occurs.
> > > > > We're seeing the first index, and then after that all HASH joins
and
> > > such.
> > > > >
> > > > > Thanks!
> > > > > James Hokes
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

"No data exists for the row/column" (InvalidOperationException) after a number of read

I've been looking around for some kind of known issue or something, but can't find anything. Here's what I'm experiencing:

I have a table with about 50,000 rows. I open several connections and use a command to ExecuteResultSet against each command, with CommandType.TableDirect, CommandText set to the name of the table, and IndexName set to various indexes. In the end, I have several SqlCeResultSet instances which are then maintained for the life of the AppDomain.

In a loop, I call SqlCeResultSet.Read() on one of the instances, and if it returns false, I call SqlCeResultSet.ReadFirst() - essentially creating a circular pass through the result set.

In a Visual Studio debug session, this approach goes swimmingly for a short time, and then after a successful Read(), I'm pegged with an InvalidOperationException (text: "No data exists for the row/column") for a column which was succesfully read on the previous Read(). If, in the immediate window, I call SqlCeResultSet.Read() again on the result set instance, the Get methods work as they had been in the previous reads.

It seems like the internal state of the ResultSet is getting corrupted somehow, but it is opaque to me. Any insights on why this suddenly throws this exception?

Well, the way around this, apparently, is to keep trying the operation until it succeeds. I have a governer on this repetition, and I've never had a problem with setting it to 5 - so it apparently succeeds after 5 or fewer retries, at least in my testing

|||

I got this error when I was using the SqlCeResultSet.Seek().
Use a breakpoint on this method to see what happens internally to the SqlCeResultSet
The values are restored once a read() is done, but after the seek and before the read is performed, the fields are unintelligible.
In other words a SqlCeResultSet.Seek() should always be followed up by a read of some kind.

"No data exists for the row/column" (InvalidOperationException) after a number of read

I've been looking around for some kind of known issue or something, but can't find anything. Here's what I'm experiencing:

I have a table with about 50,000 rows. I open several connections and use a command to ExecuteResultSet against each command, with CommandType.TableDirect, CommandText set to the name of the table, and IndexName set to various indexes. In the end, I have several SqlCeResultSet instances which are then maintained for the life of the AppDomain.

In a loop, I call SqlCeResultSet.Read() on one of the instances, and if it returns false, I call SqlCeResultSet.ReadFirst() - essentially creating a circular pass through the result set.

In a Visual Studio debug session, this approach goes swimmingly for a short time, and then after a successful Read(), I'm pegged with an InvalidOperationException (text: "No data exists for the row/column") for a column which was succesfully read on the previous Read(). If, in the immediate window, I call SqlCeResultSet.Read() again on the result set instance, the Get methods work as they had been in the previous reads.

It seems like the internal state of the ResultSet is getting corrupted somehow, but it is opaque to me. Any insights on why this suddenly throws this exception?

Well, the way around this, apparently, is to keep trying the operation until it succeeds. I have a governer on this repetition, and I've never had a problem with setting it to 5 - so it apparently succeeds after 5 or fewer retries, at least in my testing

|||

I got this error when I was using the SqlCeResultSet.Seek().
Use a breakpoint on this method to see what happens internally to the SqlCeResultSet
The values are restored once a read() is done, but after the seek and before the read is performed, the fields are unintelligible.
In other words a SqlCeResultSet.Seek() should always be followed up by a read of some kind.

"No data exists for the row/column" (InvalidOperationException) after a number of

I've been looking around for some kind of known issue or something, but can't find anything. Here's what I'm experiencing:

I have a table with about 50,000 rows. I open several connections and use a command to ExecuteResultSet against each command, with CommandType.TableDirect, CommandText set to the name of the table, and IndexName set to various indexes. In the end, I have several SqlCeResultSet instances which are then maintained for the life of the AppDomain.

In a loop, I call SqlCeResultSet.Read() on one of the instances, and if it returns false, I call SqlCeResultSet.ReadFirst() - essentially creating a circular pass through the result set.

In a Visual Studio debug session, this approach goes swimmingly for a short time, and then after a successful Read(), I'm pegged with an InvalidOperationException (text: "No data exists for the row/column") for a column which was succesfully read on the previous Read(). If, in the immediate window, I call SqlCeResultSet.Read() again on the result set instance, the Get methods work as they had been in the previous reads.

It seems like the internal state of the ResultSet is getting corrupted somehow, but it is opaque to me. Any insights on why this suddenly throws this exception?

Well, the way around this, apparently, is to keep trying the operation until it succeeds. I have a governer on this repetition, and I've never had a problem with setting it to 5 - so it apparently succeeds after 5 or fewer retries, at least in my testing

|||

I got this error when I was using the SqlCeResultSet.Seek().
Use a breakpoint on this method to see what happens internally to the SqlCeResultSet
The values are restored once a read() is done, but after the seek and before the read is performed, the fields are unintelligible.
In other words a SqlCeResultSet.Seek() should always be followed up by a read of some kind.

"Failed to acquire connection" when running a package from within another package.

I am receiving an error on my master package that executes a number of other packages. The individual packages work fine when executed by themselves. However, I am getting the following error when I attempt to execute it from another package:

Error: Failed to acquire connection "conneciton". Connection may not be configured correctly or you may not have the right permissions on this connection.

Thanks in advance for your help.

You could use this technique: http://blogs.conchango.com/jamiethomson/archive/2005/10/10/2253.aspx to help diagnose why your package is failing.

-Jamie

"batch size setting" ?

Is there a "bacth size setting" or some such t-sql option that determines the number of records sql will hold before commiting them as a group to the database?

TIA,

Barkingdog

Not that I know of, but you can use marshalling by setting up a loop and tellng it to commit.

% Wildcard Include Nulls

I have a query with 4 parameters:

Name

Location

Employee Number

Officer Code

There are no null values in Name, location and Employee Number. However, all employees do not have an officer code. So in my query I use a where clause that says WHERE OfficerCode LIKE'%'+@.Param4+'%'. I use a % for the default value in my ASP.net data control. The only problem is that the only records returned have non-null values in the OfficerCode field. How can I use a wildcard for a default value and return all records (null and non-null)?

OR OfficerCode IS NULL

% remainder in SQL

In t-sql you can do the following to check if a value is odd or even. If it returns a 0 then the number is even, if it returns a 1 it is an odd number.
select @.Value%2

I need to do something similar in a backgroundColor expression in RS 2005, any ideas? I dont see any built-in function for this.

You could do something like this and just use the boolean result(pseudocode only, not tested)

(@.Value/2 = CInt(@.Value/2))

|||

Thanks csi_hugh

This works great!

% disktime

In NT4 this counter returned a value between 0 and 100. In 2000, the number
exceeeds 100. What is it acuatlly counting and why would a percentage stat
return more than 100?I find that counter to be basically useless these days. I prefer the avg and
current disk queues for an indication of activity ove rthat.
--
Andrew J. Kelly SQL MVP
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:F7733806-7CB4-4D43-B20A-0F0E5AA6AAD2@.microsoft.com...
> In NT4 this counter returned a value between 0 and 100. In 2000, the
> number
> exceeeds 100. What is it acuatlly counting and why would a percentage
> stat
> return more than 100?|||http://support.microsoft.com/default.aspx?scid=kb;en-us;310067&sd=tech
"Jeffrey K. Ericson" wrote:
> In NT4 this counter returned a value between 0 and 100. In 2000, the number
> exceeeds 100. What is it acuatlly counting and why would a percentage stat
> return more than 100?|||click on the properties for that counter, your vertical scale is probably not
set to 100
"Jeffrey K. Ericson" wrote:
> In NT4 this counter returned a value between 0 and 100. In 2000, the number
> exceeeds 100. What is it acuatlly counting and why would a percentage stat
> return more than 100?

#of rows updated

Is there a command that will tell me the number of rows that are updated in a statement. I would like to put this in an Stored Procedure and pass the #rows updated back out.you can just look at @.@.ROWCOUNT after you do a statment and it will tell you how many rows were affected by the last SQL statement.

i.e., SELECT @.@.ROWCOUNT

or better yet, put it into a local variable such as:

DECLARE @.RowCountToReturn int

SELECT * FROM dbo.MyTable
SET @.RowCountToReturn = @.@.ROWCOUNTKeep in mind that pretty much EVERY SQL statement changes the value of @.@.ROWCOUNT, so you need to save it off if you have any code that may change it before you return it to your calling procedure.

#ERROR on SUM if Field value is a string

Hi,

I have some columns that can be either a Number or Text.

and I have to sum all the value if that column is a Number, therefore in my Table footer i have:

=IIF(IsNumeric(Fields!Col1.Value),Sum(Fields!Col1.Value),"")

However this will give me #ERROR if the columns is Text. It works for Number.

Any help is appreciated, thanks

Jon

Try =Sum(IIF(IsNumeric(Fields!Col1.Value),Fields!Col1.Value,""))

|||

Thanks for your reply.

unforunately i tried you suggestion but it didn't help, it still gives me #error

any other idea i can try?

thanks

|||

How about:

=Sum(IIF(IsNumeric(Fields!Col1.Value),Fields!Col1.Value,Nothing))

or

=Sum(IIF(IsNumeric(Fields!Col1.Value),Fields!Col1.Value,0))

|||

Thanks!

=Sum(IIF(IsNumeric(Fields!Col1.Value),Fields!Col1.Value,Nothing))

works well for most of them, but Not on columns which are TRUE/FALSE?

|||

Do you mean columns that are declared as a of Bit data type? If so, they are probably passing the IsNumeric test.

|||

Try:

=IIF(IsNumeric(Fields!Col1.Value),Sum(val(Fields!Col1.Value)),0)

Val() function returns the number part of the string.

Somiya.

#ERROR on SUM if Field value is a string

Hi,

I have some columns that can be either a Number or Text.

and I have to sum all the value if that column is a Number, therefore in my Table footer i have:

=IIF(IsNumeric(Fields!Col1.Value),Sum(Fields!Col1.Value),"")

However this will give me #ERROR if the columns is Text. It works for Number.

Any help is appreciated, thanks

Jon

Try =Sum(IIF(IsNumeric(Fields!Col1.Value),Fields!Col1.Value,""))

|||

Thanks for your reply.

unforunately i tried you suggestion but it didn't help, it still gives me #error

any other idea i can try?

thanks

|||

How about:

=Sum(IIF(IsNumeric(Fields!Col1.Value),Fields!Col1.Value,Nothing))

or

=Sum(IIF(IsNumeric(Fields!Col1.Value),Fields!Col1.Value,0))

|||

Thanks!

=Sum(IIF(IsNumeric(Fields!Col1.Value),Fields!Col1.Value,Nothing))

works well for most of them, but Not on columns which are TRUE/FALSE?

|||

Do you mean columns that are declared as a of Bit data type? If so, they are probably passing the IsNumeric test.

|||

Try:

=IIF(IsNumeric(Fields!Col1.Value),Sum(val(Fields!Col1.Value)),0)

Val() function returns the number part of the string.

Somiya.

#ERROR on SUM if Field value is a string

Hi,

I have some columns that can be either a Number or Text.

and I have to sum all the value if that column is a Number, therefore in my Table footer i have:

=IIF(IsNumeric(Fields!Col1.Value),Sum(Fields!Col1.Value),"")

However this will give me #ERROR if the columns is Text. It works for Number.

Any help is appreciated, thanks

Jon

Try =Sum(IIF(IsNumeric(Fields!Col1.Value),Fields!Col1.Value,""))

|||

Thanks for your reply.

unforunately i tried you suggestion but it didn't help, it still gives me #error

any other idea i can try?

thanks

|||

How about:

=Sum(IIF(IsNumeric(Fields!Col1.Value),Fields!Col1.Value,Nothing))

or

=Sum(IIF(IsNumeric(Fields!Col1.Value),Fields!Col1.Value,0))

|||

Thanks!

=Sum(IIF(IsNumeric(Fields!Col1.Value),Fields!Col1.Value,Nothing))

works well for most of them, but Not on columns which are TRUE/FALSE?

|||

Do you mean columns that are declared as a of Bit data type? If so, they are probably passing the IsNumeric test.

|||

Try:

=IIF(IsNumeric(Fields!Col1.Value),Sum(val(Fields!Col1.Value)),0)

Val() function returns the number part of the string.

Somiya.

# of subscribers

Is there a limit on the number of subscribers in any of the replication models?
AFAIK there isn't an upper limit. The technet merge replication guide uses
2000 subscribers to illustrate scalability.
HTH,
Paul Ibison

# of SQL 2000 Databases

Is there a limit to the number of SQL databases you can
have on one server? If I remember correctly it depends
only on space and memory. We currently have 49 databases
running on one server and we are continueing to grow. The
server is running fine as far as memory goes and we have
plenty of space but I wanted to make sure we don't have a
limit as far as the amount of databases we are aloud on
one server.Books Online, Maximum Capacity Specifications, say that the max number is
32,767 per instance. So you are quite far from limit...
--
Dejan Sarka, SQL Server MVP
Please reply only to the newsgroups.
"Shannon" <anonymous@.discussions.microsoft.com> wrote in message
news:06e701c3aeaf$a6ec9c20$a001280a@.phx.gbl...
> Is there a limit to the number of SQL databases you can
> have on one server? If I remember correctly it depends
> only on space and memory. We currently have 49 databases
> running on one server and we are continueing to grow. The
> server is running fine as far as memory goes and we have
> plenty of space but I wanted to make sure we don't have a
> limit as far as the amount of databases we are aloud on
> one server.|||See the topic "Maximum Capacity Specifications" in SQL
Server Books ONline. The documented limit noted there is
32767.
In theory you're only limited to memory, I/O, processor,
etc., but also think about manageability: do you actually
WANT to manage 1000 databases under one instance?
If you're interested in things like this, which is more of
a consolidation scenario, I suggest you look at the
Planning for SQL 2K Consolidation whitepaper since it
talks about the combined gotchas.
>--Original Message--
>Is there a limit to the number of SQL databases you can
>have on one server? If I remember correctly it depends
>only on space and memory. We currently have 49 databases
>running on one server and we are continueing to grow. The
>server is running fine as far as memory goes and we have
>plenty of space but I wanted to make sure we don't have a
>limit as far as the amount of databases we are aloud on
>one server.
>.
>|||Note that the GUI tools might not be that well suited when you go above somewhere between 100 and
200 databases. Also, having an steadily increasing number of database might be an indication of
questionable design.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Shannon" <anonymous@.discussions.microsoft.com> wrote in message
news:06e701c3aeaf$a6ec9c20$a001280a@.phx.gbl...
> Is there a limit to the number of SQL databases you can
> have on one server? If I remember correctly it depends
> only on space and memory. We currently have 49 databases
> running on one server and we are continueing to grow. The
> server is running fine as far as memory goes and we have
> plenty of space but I wanted to make sure we don't have a
> limit as far as the amount of databases we are aloud on
> one server.|||Hi,
Dont worry, You can have 32767 databases per instance.
Thanks
Hari
MCDBA
"Shannon" <anonymous@.discussions.microsoft.com> wrote in message
news:06e701c3aeaf$a6ec9c20$a001280a@.phx.gbl...
> Is there a limit to the number of SQL databases you can
> have on one server? If I remember correctly it depends
> only on space and memory. We currently have 49 databases
> running on one server and we are continueing to grow. The
> server is running fine as far as memory goes and we have
> plenty of space but I wanted to make sure we don't have a
> limit as far as the amount of databases we are aloud on
> one server.