On Thu, 17 Nov 2005 17:04:46 +0100, Andrea Montanari wrote:
>Microsoft is quiet deprecating NG in favour of it's
>own forum
Hi Andrea,
Do you have any idea why they are doing that?
Until recently, the Microsoft community pages hosted a portal to the
various microsoft newsgroups. When I clicked the link in your message, I
was quite surprised to see a new (?) forum, totally unrelated to the
newsgroups.
For me personally, it would be a shame if the newsgroupw were abandoned
in favor of a web forum. With Agent (the tool of my choice), Outlook, or
may other NNTP enabled software, it's very easy to follow discussions in
usenet groups. I can easily skip threads about subjects that are of no
interest to me, keep messages that require some research or testing on
hold until I have the time to reply to them, re-read older messages, and
type and post answers when I have time. I am convinced that I'd never be
able to post anywhere near the amount of answers I currently do if I
can't use Agent or a similar tool to access the groups.
If anyone from Microsoft is reading this, can you please confirm or deny
what Andrea wrote? And if it's true, can you explain your reasons for
this move?
Inquiring mind wants to know....
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
hi Hugo,
Hugo Kornelis wrote:
> On Thu, 17 Nov 2005 17:04:46 +0100, Andrea Montanari wrote:
>
> Hi Andrea,
> Do you have any idea why they are doing that?
actually not... I think I've heard it at SQL Server MVPs summit in Dallas,
but I was not paying attention a lot due to alcoolic distractions (:D) ...
they told us that they are increasing Forums support and NNTP flavours will
not be dropped in the next future, and lot of MVPs were very glad to hear
about these new forum where only part of them were disappointed about this
NNTP deprecate status (me first of all :D)
> Until recently, the Microsoft community pages hosted a portal to the
> various microsoft newsgroups. When I clicked the link in your
> message, I was quite surprised to see a new (?) forum, totally
> unrelated to the newsgroups.
> For me personally, it would be a shame if the newsgroupw were
> abandoned in favor of a web forum. With Agent (the tool of my
> choice), Outlook, or may other NNTP enabled software, it's very easy
> to follow discussions in usenet groups. I can easily skip threads
> about subjects that are of no interest to me, keep messages that
> require some research or testing on hold until I have the time to
> reply to them, re-read older messages, and type and post answers when
> I have time. I am convinced that I'd never be able to post anywhere
> near the amount of answers I currently do if I can't use Agent or a
> similar tool to access the groups.
> If anyone from Microsoft is reading this, can you please confirm or
> deny what Andrea wrote? And if it's true, can you explain your
> reasons for this move?
hope they will answer "Andrea is totally wrong, we'll never do it and will
increase NNTP flavours.." :D
> Inquiring mind wants to know....
ROTFL
:D
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
sql
Sunday, March 25, 2012
(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
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
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
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
(ODBC driver) timeout expired when query "DELETE FROM mytable"
Hi,
I'm running SQL server 2000. I have at table with about 12 million records.
I want to empty the table. I use the Query option from Tables/mytable/opentable/query.
I type in the query and select Run. The process runs for some time, then raises
an error box as follows: (title)SQL Server Enterprise Manager. The body text says:
[Microsoft][ODBC SQL Server Driver] Timeout Expired. I've tried every timeout
setting I can find. I've tried setting all timeouts to 0 (infinite) to no avail.
Please help.........
Note: I can get the table empty if I select TOP n records, then DELETE FROM but
that takes forever!! It is also not a process that's very amenable to a clean programatic
solution.
Thanks, jack
That's very strange. Setting timeout to '0' should do the trick.
If all you need to do is empty the table you could just call "truncate table table_name". That should run pretty quickly.
Sorry you're having issues. Please reactivate this thread if the 'truncate' command doesn't fix your issue.
~Warren
|||Warren,
Thanks!
I have changed the query to "Truncate Table" and it's much faster. I haven't
tried it with the large table yet -- I'll have to reload the data before I know for
sure. I used the "Delete" command only because I hadn't stumbled on the "Truncate"
command. I still would like to know why I'm getting the timeout since I'm sure it will
jump up and bite me later because I'm dealing with such large tables, and Murphy is
looking over my shoulder -- ha!
jack
|||Consider that when you execute DELETE, the operation is logged so you are essentially "moving" the deleted rows to the transaction log with all of the associated disk IO expenses. Truncate table is also logged, but simply tells SQL Server to "drop" the data and leave the schema--it's dramatically simpler and faster.|||William,
"Dramatically" is an understatement! I'm amazed at how quickly the table is
emptied using "Truncate". Although knowing why, it makes perfect sense. Thanks
for the expanation of the 'why', that's even more valuable than the 'how'. Is there an
explicit means of preventing the transaction logging - since it's so time costly? Is
there a downside to such a thing if it exists?
I'm still bewildered about the "timeout expired" error inspired by the length of time
the DELETE takes. I guess I'll have to pull my copy of the "Guide to..." off the shelf
and review ADO/ODBC query timeouts etc.
thanks, jack
|||No, you can't (and should not) "turn off" the transaction log--it's your safety net. Yes, there are other operations that can be executed without the log getting in the way (like BulkCopy).
Consider that the Delete command must also delete the Index(es) for each row as well as reallocate space and execute other operations that take CPU time, RAM and disk IO. While the Truncate is fast, it also means that the server can clean up the freed space when it has idle time and it needs the space. For long operations you can set the CommandTimeout to a higher number, but whenever I find a neeed to do this I look for a more efficient way to handle the task...
hth
|||William,
Thanks! I'm in good shape now.
jack
(ObjKommun IN (xxxx%, oooo%))
My question, is it valid to use "IN" as in "LIKE" conserning strings? (Using the "%" operator)
Thx in advance!Hello,
you cant you % in this way (for Oracle). Use SUBSTR(var, start, length)
Hope this helps ?
Greetz
Manfred Peter
(Alligator Company)
http://www.alligatorsql.de|||This is just a short copy of a SP im using in SQL server... now the problem appears if there will be to many parameters involved.. (HUGE query)
My main problem is how to reduce the length of the query..
So, instead of doing it like this..:
AND (((xxxx like 'yyyy%') OR (xxxx like 'zzzz%')))
I was thinking of doing like this..:
xxxx IN ('yyyy%', 'zzzz%')
But when im using the "IN" im not able to use the % (Cause sometimes strings are weird) and no result is shown, but when removing the % i get the satisfying result, not depending on Case sensitivity which is perfect, but still...
And im really really lousy to explain things, hope its ok anyways ;)|||No, IN cannot be used as a substitute for 'like', it will not work. You could try looking at applying a string function on the column side, e.g. WHERE substring( mycolumn, 0, 4) IN ( 'stri', '1234', 'etc.' )|||Okay, thx for the response.
Think I will use "IN" but forget about trying to make it work like "LIKE" :)
Most important is that the length of the query is shortened.
Best wishes from Sweden.|||Thats it :)
Greetings
Manfred Peter
(Alligator Company)
http://www.alligatorsql.com
Thx in advance!Hello,
you cant you % in this way (for Oracle). Use SUBSTR(var, start, length)
Hope this helps ?
Greetz
Manfred Peter
(Alligator Company)
http://www.alligatorsql.de|||This is just a short copy of a SP im using in SQL server... now the problem appears if there will be to many parameters involved.. (HUGE query)
My main problem is how to reduce the length of the query..
So, instead of doing it like this..:
AND (((xxxx like 'yyyy%') OR (xxxx like 'zzzz%')))
I was thinking of doing like this..:
xxxx IN ('yyyy%', 'zzzz%')
But when im using the "IN" im not able to use the % (Cause sometimes strings are weird) and no result is shown, but when removing the % i get the satisfying result, not depending on Case sensitivity which is perfect, but still...
And im really really lousy to explain things, hope its ok anyways ;)|||No, IN cannot be used as a substitute for 'like', it will not work. You could try looking at applying a string function on the column side, e.g. WHERE substring( mycolumn, 0, 4) IN ( 'stri', '1234', 'etc.' )|||Okay, thx for the response.
Think I will use "IN" but forget about trying to make it work like "LIKE" :)
Most important is that the length of the query is shortened.
Best wishes from Sweden.|||Thats it :)
Greetings
Manfred Peter
(Alligator Company)
http://www.alligatorsql.com
(Object required) DTSPackageLog
I get "(Object Required)" when trying to
"DTSPackageLog.WriteStringToLog sErrorMessage" in transformation vbscripts being used by DTS.
All available documentation indicates that this should not be possible as it is 'intrinsic' like "DTSGLobalVariables".
What's the secret?I too have been trying to make this work. I found a script on SQLDTS.com that I've cut and pasted and whenever I try to run it, I am faced with the Object Required: 'DTSPackageLog' error.
http://www.sqldts.com/default.aspx?6,101,238,0,1
Any suggestions would be helpful!
Thanks,
Bob
Originally posted by Hollerith
I get "(Object Required)" when trying to
"DTSPackageLog.WriteStringToLog sErrorMessage" in transformation vbscripts being used by DTS.
All available documentation indicates that this should not be possible as it is 'intrinsic' like "DTSGLobalVariables".
What's the secret?
"DTSPackageLog.WriteStringToLog sErrorMessage" in transformation vbscripts being used by DTS.
All available documentation indicates that this should not be possible as it is 'intrinsic' like "DTSGLobalVariables".
What's the secret?I too have been trying to make this work. I found a script on SQLDTS.com that I've cut and pasted and whenever I try to run it, I am faced with the Object Required: 'DTSPackageLog' error.
http://www.sqldts.com/default.aspx?6,101,238,0,1
Any suggestions would be helpful!
Thanks,
Bob
Originally posted by Hollerith
I get "(Object Required)" when trying to
"DTSPackageLog.WriteStringToLog sErrorMessage" in transformation vbscripts being used by DTS.
All available documentation indicates that this should not be possible as it is 'intrinsic' like "DTSGLobalVariables".
What's the secret?
(nothing = 0) = true !
I have a bit type field that can have values 0, 1 and NULL in SQL database.
I want to hide an image when the value is different from 0.
The expression used in the visibility.Hidden property is
"=First(Fields!myfield.Value, "DataSet1") <>0"
And when the database value is NULL this expression returns false.
Please help !
(a French user...)I really canâ't understand why something like that does not work. I always
have to use something like the following:
=iif(Expression, 1=1, 1=0)
I hope it helps you!
> I have a bit type field that can have values 0, 1 and NULL in SQL database.
> I want to hide an image when the value is different from 0.
> The expression used in the visibility.Hidden property is
> "=First(Fields!myfield.Value, "DataSet1") <>0"
> And when the database value is NULL this expression returns false.
> Please help !
> (a French user...)
>|||thanks for you answer but it doen't help me...
I made this test : expression "= nothing = 0" in a new field, it's return
true !
"Soan" wrote:
> I really canâ't understand why something like that does not work. I always
> have to use something like the following:
> =iif(Expression, 1=1, 1=0)
> I hope it helps you!
>
> > I have a bit type field that can have values 0, 1 and NULL in SQL database.
> > I want to hide an image when the value is different from 0.
> > The expression used in the visibility.Hidden property is
> > "=First(Fields!myfield.Value, "DataSet1") <>0"
> > And when the database value is NULL this expression returns false.
> >
> > Please help !
> >
> > (a French user...)
> >|||I haven't tried it myself, but you may want to try the following:
"=(First(Fields!myfield.Value, "DataSet1") <> 0) OR
(First(Fields!myfield.Value, "DataSet1") IS DBNull.Value)"
Hope this helps.
Andy
tonio932 wrote:
> I have a bit type field that can have values 0, 1 and NULL in SQL database.
> I want to hide an image when the value is different from 0.
> The expression used in the visibility.Hidden property is
> "=First(Fields!myfield.Value, "DataSet1") <>0"
> And when the database value is NULL this expression returns false.
> Please help !
> (a French user...)
>|||When comparing with NULLs you have to use "is Nothing".
E.g. =iif(Fields!myField.Value is Nothing, True-Part, False-Part)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"tonio932" <tonio932@.discussions.microsoft.com> wrote in message
news:C4E4FDE8-81C8-41E4-AE37-09671864812A@.microsoft.com...
>I have a bit type field that can have values 0, 1 and NULL in SQL database.
> I want to hide an image when the value is different from 0.
> The expression used in the visibility.Hidden property is
> "=First(Fields!myfield.Value, "DataSet1") <>0"
> And when the database value is NULL this expression returns false.
> Please help !
> (a French user...)
>|||I have noticed that when a column value from the database is null, doing
myfield.Value is the same as doing null.Value, which makes RS blow.
Andy Hardin wrote:
> I haven't tried it myself, but you may want to try the following:
> "=(First(Fields!myfield.Value, "DataSet1") <> 0) OR
> (First(Fields!myfield.Value, "DataSet1") IS DBNull.Value)"
> Hope this helps.
> Andy
>
> tonio932 wrote:
>> I have a bit type field that can have values 0, 1 and NULL in SQL
>> database.
>> I want to hide an image when the value is different from 0.
>> The expression used in the visibility.Hidden property is
>> "=First(Fields!myfield.Value, "DataSet1") <>0"
>> And when the database value is NULL this expression returns false.
>> Please help !
>> (a French user...)sql
I want to hide an image when the value is different from 0.
The expression used in the visibility.Hidden property is
"=First(Fields!myfield.Value, "DataSet1") <>0"
And when the database value is NULL this expression returns false.
Please help !
(a French user...)I really canâ't understand why something like that does not work. I always
have to use something like the following:
=iif(Expression, 1=1, 1=0)
I hope it helps you!
> I have a bit type field that can have values 0, 1 and NULL in SQL database.
> I want to hide an image when the value is different from 0.
> The expression used in the visibility.Hidden property is
> "=First(Fields!myfield.Value, "DataSet1") <>0"
> And when the database value is NULL this expression returns false.
> Please help !
> (a French user...)
>|||thanks for you answer but it doen't help me...
I made this test : expression "= nothing = 0" in a new field, it's return
true !
"Soan" wrote:
> I really canâ't understand why something like that does not work. I always
> have to use something like the following:
> =iif(Expression, 1=1, 1=0)
> I hope it helps you!
>
> > I have a bit type field that can have values 0, 1 and NULL in SQL database.
> > I want to hide an image when the value is different from 0.
> > The expression used in the visibility.Hidden property is
> > "=First(Fields!myfield.Value, "DataSet1") <>0"
> > And when the database value is NULL this expression returns false.
> >
> > Please help !
> >
> > (a French user...)
> >|||I haven't tried it myself, but you may want to try the following:
"=(First(Fields!myfield.Value, "DataSet1") <> 0) OR
(First(Fields!myfield.Value, "DataSet1") IS DBNull.Value)"
Hope this helps.
Andy
tonio932 wrote:
> I have a bit type field that can have values 0, 1 and NULL in SQL database.
> I want to hide an image when the value is different from 0.
> The expression used in the visibility.Hidden property is
> "=First(Fields!myfield.Value, "DataSet1") <>0"
> And when the database value is NULL this expression returns false.
> Please help !
> (a French user...)
>|||When comparing with NULLs you have to use "is Nothing".
E.g. =iif(Fields!myField.Value is Nothing, True-Part, False-Part)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"tonio932" <tonio932@.discussions.microsoft.com> wrote in message
news:C4E4FDE8-81C8-41E4-AE37-09671864812A@.microsoft.com...
>I have a bit type field that can have values 0, 1 and NULL in SQL database.
> I want to hide an image when the value is different from 0.
> The expression used in the visibility.Hidden property is
> "=First(Fields!myfield.Value, "DataSet1") <>0"
> And when the database value is NULL this expression returns false.
> Please help !
> (a French user...)
>|||I have noticed that when a column value from the database is null, doing
myfield.Value is the same as doing null.Value, which makes RS blow.
Andy Hardin wrote:
> I haven't tried it myself, but you may want to try the following:
> "=(First(Fields!myfield.Value, "DataSet1") <> 0) OR
> (First(Fields!myfield.Value, "DataSet1") IS DBNull.Value)"
> Hope this helps.
> Andy
>
> tonio932 wrote:
>> I have a bit type field that can have values 0, 1 and NULL in SQL
>> database.
>> I want to hide an image when the value is different from 0.
>> The expression used in the visibility.Hidden property is
>> "=First(Fields!myfield.Value, "DataSet1") <>0"
>> And when the database value is NULL this expression returns false.
>> Please help !
>> (a French user...)sql
(NOLOCK) and Linked Servers
I know that you can't have an optimizer hint when running an ad-hoc query
from another server. My question is why? What prevents it from using the
hint?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200509/1PButler,
Use the OPENQUERY function instead of the 4 part name.
HTH
Jerry
"PButler via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:54C0F66FA391C@.SQLMonster.com...
>I know that you can't have an optimizer hint when running an ad-hoc query
> from another server. My question is why? What prevents it from using the
> hint?
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200509/1|||Thank you Jerry,
I guess I'm looking for a deeper explination of why? What prevents it from
using the hints in this way?
Also, Are there any performance issues with using OPENQUERY? Is it
faster/slower in general?
Jerry Spivey wrote:
>PButler,
>Use the OPENQUERY function instead of the 4 part name.
>HTH
>Jerry
>>I know that you can't have an optimizer hint when running an ad-hoc query
>> from another server. My question is why? What prevents it from using the
>> hint?
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200509/1|||If I had to guess I would guess that it is because of where the processing
occurs with respect to the 4 part name vs. OPENQUERY. You'll probably find
OPENQUERY slightly to much faster depending on the number of rows in the
destination table(s).
HTH
Jerry
"PButler via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:54C468F4CB638@.SQLMonster.com...
> Thank you Jerry,
> I guess I'm looking for a deeper explination of why? What prevents it
> from
> using the hints in this way?
> Also, Are there any performance issues with using OPENQUERY? Is it
> faster/slower in general?
> Jerry Spivey wrote:
>>PButler,
>>Use the OPENQUERY function instead of the 4 part name.
>>HTH
>>Jerry
>>I know that you can't have an optimizer hint when running an ad-hoc query
>> from another server. My question is why? What prevents it from using
>> the
>> hint?
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200509/1|||I am not a linked server expert but I suspect it has to do with the fact it
simply may not be supported on the other end. A linked server can be to
many things not just sql server. In addition to that there are distributed
transaction issues that may come into play. If you want all the features
supported you should create a stored proc on the lined server and call that.
Then all the processing is done on the other server just as if it was issued
locally and only the results are sent back.
--
Andrew J. Kelly SQL MVP
"PButler via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:54C468F4CB638@.SQLMonster.com...
> Thank you Jerry,
> I guess I'm looking for a deeper explination of why? What prevents it
> from
> using the hints in this way?
> Also, Are there any performance issues with using OPENQUERY? Is it
> faster/slower in general?
> Jerry Spivey wrote:
>>PButler,
>>Use the OPENQUERY function instead of the 4 part name.
>>HTH
>>Jerry
>>I know that you can't have an optimizer hint when running an ad-hoc query
>> from another server. My question is why? What prevents it from using
>> the
>> hint?
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200509/1
from another server. My question is why? What prevents it from using the
hint?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200509/1PButler,
Use the OPENQUERY function instead of the 4 part name.
HTH
Jerry
"PButler via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:54C0F66FA391C@.SQLMonster.com...
>I know that you can't have an optimizer hint when running an ad-hoc query
> from another server. My question is why? What prevents it from using the
> hint?
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200509/1|||Thank you Jerry,
I guess I'm looking for a deeper explination of why? What prevents it from
using the hints in this way?
Also, Are there any performance issues with using OPENQUERY? Is it
faster/slower in general?
Jerry Spivey wrote:
>PButler,
>Use the OPENQUERY function instead of the 4 part name.
>HTH
>Jerry
>>I know that you can't have an optimizer hint when running an ad-hoc query
>> from another server. My question is why? What prevents it from using the
>> hint?
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200509/1|||If I had to guess I would guess that it is because of where the processing
occurs with respect to the 4 part name vs. OPENQUERY. You'll probably find
OPENQUERY slightly to much faster depending on the number of rows in the
destination table(s).
HTH
Jerry
"PButler via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:54C468F4CB638@.SQLMonster.com...
> Thank you Jerry,
> I guess I'm looking for a deeper explination of why? What prevents it
> from
> using the hints in this way?
> Also, Are there any performance issues with using OPENQUERY? Is it
> faster/slower in general?
> Jerry Spivey wrote:
>>PButler,
>>Use the OPENQUERY function instead of the 4 part name.
>>HTH
>>Jerry
>>I know that you can't have an optimizer hint when running an ad-hoc query
>> from another server. My question is why? What prevents it from using
>> the
>> hint?
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200509/1|||I am not a linked server expert but I suspect it has to do with the fact it
simply may not be supported on the other end. A linked server can be to
many things not just sql server. In addition to that there are distributed
transaction issues that may come into play. If you want all the features
supported you should create a stored proc on the lined server and call that.
Then all the processing is done on the other server just as if it was issued
locally and only the results are sent back.
--
Andrew J. Kelly SQL MVP
"PButler via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:54C468F4CB638@.SQLMonster.com...
> Thank you Jerry,
> I guess I'm looking for a deeper explination of why? What prevents it
> from
> using the hints in this way?
> Also, Are there any performance issues with using OPENQUERY? Is it
> faster/slower in general?
> Jerry Spivey wrote:
>>PButler,
>>Use the OPENQUERY function instead of the 4 part name.
>>HTH
>>Jerry
>>I know that you can't have an optimizer hint when running an ad-hoc query
>> from another server. My question is why? What prevents it from using
>> the
>> hint?
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200509/1
(NOLOCK) and Linked Servers
I know that you can't have an optimizer hint when running an ad-hoc query
from another server. My question is why? What prevents it from using the
hint?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200509/1PButler,
Use the OPENQUERY function instead of the 4 part name.
HTH
Jerry
"PButler via droptable.com" <forum@.droptable.com> wrote in message
news:54C0F66FA391C@.droptable.com...
>I know that you can't have an optimizer hint when running an ad-hoc query
> from another server. My question is why? What prevents it from using the
> hint?
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200509/1|||Thank you Jerry,
I guess I'm looking for a deeper explination of why? What prevents it from
using the hints in this way?
Also, Are there any performance issues with using OPENQUERY? Is it
faster/slower in general?
Jerry Spivey wrote:[vbcol=seagreen]
>PButler,
>Use the OPENQUERY function instead of the 4 part name.
>HTH
>Jerry
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200509/1|||If I had to guess I would guess that it is because of where the processing
occurs with respect to the 4 part name vs. OPENQUERY. You'll probably find
OPENQUERY slightly to much faster depending on the number of rows in the
destination table(s).
HTH
Jerry
"PButler via droptable.com" <forum@.droptable.com> wrote in message
news:54C468F4CB638@.droptable.com...
> Thank you Jerry,
> I guess I'm looking for a deeper explination of why? What prevents it
> from
> using the hints in this way?
> Also, Are there any performance issues with using OPENQUERY? Is it
> faster/slower in general?
> Jerry Spivey wrote:
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200509/1|||I am not a linked server expert but I suspect it has to do with the fact it
simply may not be supported on the other end. A linked server can be to
many things not just sql server. In addition to that there are distributed
transaction issues that may come into play. If you want all the features
supported you should create a stored proc on the lined server and call that.
Then all the processing is done on the other server just as if it was issued
locally and only the results are sent back.
Andrew J. Kelly SQL MVP
"PButler via droptable.com" <forum@.droptable.com> wrote in message
news:54C468F4CB638@.droptable.com...
> Thank you Jerry,
> I guess I'm looking for a deeper explination of why? What prevents it
> from
> using the hints in this way?
> Also, Are there any performance issues with using OPENQUERY? Is it
> faster/slower in general?
> Jerry Spivey wrote:
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200509/1
from another server. My question is why? What prevents it from using the
hint?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200509/1PButler,
Use the OPENQUERY function instead of the 4 part name.
HTH
Jerry
"PButler via droptable.com" <forum@.droptable.com> wrote in message
news:54C0F66FA391C@.droptable.com...
>I know that you can't have an optimizer hint when running an ad-hoc query
> from another server. My question is why? What prevents it from using the
> hint?
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200509/1|||Thank you Jerry,
I guess I'm looking for a deeper explination of why? What prevents it from
using the hints in this way?
Also, Are there any performance issues with using OPENQUERY? Is it
faster/slower in general?
Jerry Spivey wrote:[vbcol=seagreen]
>PButler,
>Use the OPENQUERY function instead of the 4 part name.
>HTH
>Jerry
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200509/1|||If I had to guess I would guess that it is because of where the processing
occurs with respect to the 4 part name vs. OPENQUERY. You'll probably find
OPENQUERY slightly to much faster depending on the number of rows in the
destination table(s).
HTH
Jerry
"PButler via droptable.com" <forum@.droptable.com> wrote in message
news:54C468F4CB638@.droptable.com...
> Thank you Jerry,
> I guess I'm looking for a deeper explination of why? What prevents it
> from
> using the hints in this way?
> Also, Are there any performance issues with using OPENQUERY? Is it
> faster/slower in general?
> Jerry Spivey wrote:
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200509/1|||I am not a linked server expert but I suspect it has to do with the fact it
simply may not be supported on the other end. A linked server can be to
many things not just sql server. In addition to that there are distributed
transaction issues that may come into play. If you want all the features
supported you should create a stored proc on the lined server and call that.
Then all the processing is done on the other server just as if it was issued
locally and only the results are sent back.
Andrew J. Kelly SQL MVP
"PButler via droptable.com" <forum@.droptable.com> wrote in message
news:54C468F4CB638@.droptable.com...
> Thank you Jerry,
> I guess I'm looking for a deeper explination of why? What prevents it
> from
> using the hints in this way?
> Also, Are there any performance issues with using OPENQUERY? Is it
> faster/slower in general?
> Jerry Spivey wrote:
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200509/1
(NOLOCK) and Linked Servers
I know that you can't have an optimizer hint when running an ad-hoc query
from another server. My question is why? What prevents it from using the
hint?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200509/1
PButler,
Use the OPENQUERY function instead of the 4 part name.
HTH
Jerry
"PButler via droptable.com" <forum@.droptable.com> wrote in message
news:54C0F66FA391C@.droptable.com...
>I know that you can't have an optimizer hint when running an ad-hoc query
> from another server. My question is why? What prevents it from using the
> hint?
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200509/1
|||Thank you Jerry,
I guess I'm looking for a deeper explination of why? What prevents it from
using the hints in this way?
Also, Are there any performance issues with using OPENQUERY? Is it
faster/slower in general?
Jerry Spivey wrote:[vbcol=seagreen]
>PButler,
>Use the OPENQUERY function instead of the 4 part name.
>HTH
>Jerry
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200509/1
|||If I had to guess I would guess that it is because of where the processing
occurs with respect to the 4 part name vs. OPENQUERY. You'll probably find
OPENQUERY slightly to much faster depending on the number of rows in the
destination table(s).
HTH
Jerry
"PButler via droptable.com" <forum@.droptable.com> wrote in message
news:54C468F4CB638@.droptable.com...
> Thank you Jerry,
> I guess I'm looking for a deeper explination of why? What prevents it
> from
> using the hints in this way?
> Also, Are there any performance issues with using OPENQUERY? Is it
> faster/slower in general?
> Jerry Spivey wrote:
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200509/1
|||I am not a linked server expert but I suspect it has to do with the fact it
simply may not be supported on the other end. A linked server can be to
many things not just sql server. In addition to that there are distributed
transaction issues that may come into play. If you want all the features
supported you should create a stored proc on the lined server and call that.
Then all the processing is done on the other server just as if it was issued
locally and only the results are sent back.
Andrew J. Kelly SQL MVP
"PButler via droptable.com" <forum@.droptable.com> wrote in message
news:54C468F4CB638@.droptable.com...
> Thank you Jerry,
> I guess I'm looking for a deeper explination of why? What prevents it
> from
> using the hints in this way?
> Also, Are there any performance issues with using OPENQUERY? Is it
> faster/slower in general?
> Jerry Spivey wrote:
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200509/1
from another server. My question is why? What prevents it from using the
hint?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200509/1
PButler,
Use the OPENQUERY function instead of the 4 part name.
HTH
Jerry
"PButler via droptable.com" <forum@.droptable.com> wrote in message
news:54C0F66FA391C@.droptable.com...
>I know that you can't have an optimizer hint when running an ad-hoc query
> from another server. My question is why? What prevents it from using the
> hint?
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200509/1
|||Thank you Jerry,
I guess I'm looking for a deeper explination of why? What prevents it from
using the hints in this way?
Also, Are there any performance issues with using OPENQUERY? Is it
faster/slower in general?
Jerry Spivey wrote:[vbcol=seagreen]
>PButler,
>Use the OPENQUERY function instead of the 4 part name.
>HTH
>Jerry
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200509/1
|||If I had to guess I would guess that it is because of where the processing
occurs with respect to the 4 part name vs. OPENQUERY. You'll probably find
OPENQUERY slightly to much faster depending on the number of rows in the
destination table(s).
HTH
Jerry
"PButler via droptable.com" <forum@.droptable.com> wrote in message
news:54C468F4CB638@.droptable.com...
> Thank you Jerry,
> I guess I'm looking for a deeper explination of why? What prevents it
> from
> using the hints in this way?
> Also, Are there any performance issues with using OPENQUERY? Is it
> faster/slower in general?
> Jerry Spivey wrote:
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200509/1
|||I am not a linked server expert but I suspect it has to do with the fact it
simply may not be supported on the other end. A linked server can be to
many things not just sql server. In addition to that there are distributed
transaction issues that may come into play. If you want all the features
supported you should create a stored proc on the lined server and call that.
Then all the processing is done on the other server just as if it was issued
locally and only the results are sent back.
Andrew J. Kelly SQL MVP
"PButler via droptable.com" <forum@.droptable.com> wrote in message
news:54C468F4CB638@.droptable.com...
> Thank you Jerry,
> I guess I'm looking for a deeper explination of why? What prevents it
> from
> using the hints in this way?
> Also, Are there any performance issues with using OPENQUERY? Is it
> faster/slower in general?
> Jerry Spivey wrote:
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200509/1
(newbie) updating dimensions
Hi,
I apologise if I have missed something obvious here, but I am having a problem finding how to set up a cube to update dimensions automatically when data is changed in the source database. From what I can see in Books Online, dimension writeback does not do this because the update is initiated by the user. I have got storage settings for measures figured out, and we are using low-latency MOLAP. In BIDS, there does not seem to be a similar way to set up the dimensions to be updated. Can anyone advise me on how this is done?
Any help much appreciated.
Hello. The feature that you are looking for is maybe "proactive caching".
My advice is to be very careful about this feature. You will need to be an experienced SSAS2005(Analysis Services 2005) consultant to set this up properly.
I also think that it is only a feature in the enterprise edition.
The budget version is to have an SSIS-package(Integration Services) that detects changes in the database and automatically start an SSAS2005 processing task after that.
Looks like you are planning to build a cube on top of an OLTP-system(transaction system) and ignore the option to build a data warehouse/ data mart?
I always recommend a datamart separate from the source system as the minimum requirement to build a good analytic solution.
HTH
Thomas Ivarsson
Have a look at proactive caching here:
http://www.microsoft.com/technet/prodtechnol/sql/2005/rtbissas.mspx
|||Thanks for your reply, Thomas. We already have our options worked out re: proactive caching as far as measures are concerned, where the BIDS UI is perfectly clear. It is for dimensions specifically that we are having trouble. Can anyone shed any light on that specifically? Thanks very much for any help.|||Hello again Ed.
I have not used this feature myself and have no plans to do it in the near future.
You have all dimensions as ROLAP?
I you buy "MS SQL server 2005 Analysis Services"(SAMS) there is almost a whole chapter(24) with recommendations of how to configure this properly.
HTH
Thomas Ivarsson
(Newbie) Trying to get rid of NonEmptyCrossJoin
Hi everyone,
As I have said before, I am new to MDX. Any help much appreciated.
I am trying to substitute the Exists() function for the NonEmptyCrossJoin() function, but Exists is not producing the results that I would expect. The test query that I am running is:
SELECT {[Measures].[Value]} ON COLUMNS,
NonEmpty({[Fact].[Name].[All].CHILDREN}) ON ROWS
FROM [AS Test1]
WHERE
NonEmptyCrossJoin({[Start Date].[Month Hierarchy].[2006-01-01 00:00:00]:[Start Date].[Month Hierarchy].[2006-06-02 00:00:00]},
{[End Date].[Month Hierarchy].[2006-06-15 00:00:00]:[End Date].[Month Hierarchy].[2006-12-31 00:00:00]})
This is not ideal code, but performs correctly, only listing members with start and end dates with the appropriate values. I tried to substitute that query for this:
SELECT {[Measures].[Value]} ON COLUMNS,
NonEmpty({[Fact].[Name].[All].CHILDREN}) ON ROWS
FROM [AS Test1]
WHERE
Exists(NonEmpty({[Start Date].[Month Hierarchy].[2006-01-01 00:00:00]:[Start Date].[Month Hierarchy].[2006-06-02 00:00:00]}),
NonEmpty({[End Date].[Month Hierarchy].[2006-06-15 00:00:00]:[End Date].[Month Hierarchy].[2006-12-31 00:00:00]}))
This query effectively ignores the second set (i.e. the End Date set), listing all values with start dates in the given range. Can anyone tell me what I am doing wrong?
Any help much appreciated.
Edit: Adding a measure group at the end of the Exists function and/or removing the NonEmpty functions do not correct the problem.
Not sure whether you've read this blog entry, which discusses different scenarios in which NECJ was used. The closest equivalent is Exists() with measure group, but it depends on the scenario in which NECJ was being used:
http://sqljunkies.com/WebLog/mosha/archive/2006/10/09/nonempty_exists_necj.aspx
>>
MDX: NonEmpty, Exists and evil NonEmptyCrossJoin
...
NonEmptyCrossJoin(set1, set2, ..., setN, K) is equivalent to Exists(set1*...*setK, set(K+1)*...*setN, "measuregroupname")
where "measuregroupname" is the name of the measure group to which the current measure belongs.
...
>>
So, in your case, it should be like:
Exists({[Start Date].[Month Hierarchy].[2006-01-01 00:00:00]:[Start Date].[Month Hierarchy].[2006-06-02 00:00:00]}
* {[End Date].[Month Hierarchy].[2006-06-15 00:00:00]:[End Date].[Month Hierarchy].[2006-12-31 00:00:00]},, "measuregroupname")
|||Hi Ed,
First of all, why are you trying to replace NECJ with Exists rather than NonEmpty - are you setting the NullProcessing property anywhere? I think you're better off using the NonEmpty function here.
Secondly, in my experience you should always include a measure somewhere in either of the sets you pass into NonEmpty - especially if you have multiple measure groups in your cube. If you don't then I think what happens is that you remove all the items in your set which are empty for all measures in the cube, which can be confusing when you're only showing one measure and can also slow your query down.
So... does the following do what you want?
SELECT {[Measures].[Value]} ON COLUMNS,
NonEmpty({[Fact].[Name].[All].CHILDREN},[Measures].[Value]) ON ROWS
FROM [AS Test1]
WHERE
(NonEmpty({[Start Date].[Month Hierarchy].[2006-01-01 00:00:00]:[Start Date].[Month Hierarchy].[2006-06-02 00:00:00]} *
{[End Date].[Month Hierarchy].[2006-06-15 00:00:00]:[End Date].[Month Hierarchy].[2006-12-31 00:00:00]},[Measures].[Value]))
HTH,
Chris
|||Thank you very much Chris and Deepak for your replies. I'll give those approaches a try.(Newbie) Outer Join Simulation
I have two tables invoice(fact table) and agents(dimension). Not all invoices have an agent. How do I use the agents table as an outer join? When I include agents as a dimension and look at all agents, I only get invoices that have an agent. I know I could create a view in SQL but I want to do this in the cube.
Thanks,
Bill
You can create an unknown member in your agent dimension table and connect all fact records without an agent key to that member. You will have to use TSQL to this.
SSAS2005 can help you with doing this without writing code. Have a look at inferred members in BOL!
Regards
Thomas Ivarsson
(newbie) copying rows between tables via notepad
Hi
newbie question I'm sure
I have 2 databases on 2 different machines, there is no
connection between the 2. What I need is a way of
extracting particular rows from particular tables and adding them
to another database. The only viable way of doing this is via
email for unimportant reasons.
Via query analyser I can do a select and get the results in
text then manually adjust these back into an insert statement.
There must be a better way but I'm too new to this to know how
Any ideas?
newbie question I'm sure
I have 2 databases on 2 different machines, there is no
connection between the 2. What I need is a way of
extracting particular rows from particular tables and adding them
to another database. The only viable way of doing this is via
email for unimportant reasons.
Via query analyser I can do a select and get the results in
text then manually adjust these back into an insert statement.
There must be a better way but I'm too new to this to know how
Any ideas?
Thanks
Fhttp://vyaskn.tripod.com/code.htm#inserts
--
David Portas
SQL Server MVP
--|||You can use bcp.exe or DTS to export the data to file, then the same
mechanism to import them at the destination - see Books Online for the
details. Or if you want to use INSERTs, then check this out:
http://vyaskn.tripod.com/code.htm#inserts
Simon|||Simon Hayes wrote:
> You can use bcp.exe or DTS to export the data to file, then the same
> mechanism to import them at the destination - see Books Online for
the
> details. Or if you want to use INSERTs, then check this out:
> http://vyaskn.tripod.com/code.htm#inserts
> Simon
Thanks for the reply, this looked like it would have been ideal but
this is the output I get, it gets cut off (column names changed to
protect
the innocent :-))
INSERT INTO [XXXXXXXXXXXXXX]
([XXXXXXXXXXXXX],[XXXXXXXXXXXX],[XXXXXXXXXXXXXXX],[XXXXXXXXXXXXXXXXXXX],[XXXXXXXXXXXXXXXXX],[XXXXXXXXXXXXXX],[XXXXXXXXXXX],[XXXXXXXXXXXXXXXXX],[XXXXXXXXXXXXXXX],[XXXXXXXXXXXXXXXXXXX],[XXXXXXXXXXXXXXXXXX])VALUES('7CC60B79-1B27-4|||(foldface@.yahoo.co.uk) writes:
> Thanks for the reply, this looked like it would have been ideal but
> this is the output I get, it gets cut off (column names changed to
> protect
Sounds like you should go into to Tools->Options->Results and adjust
"Maximum characters per column".
--
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 for the reply, yes, that was it, it was in the faq for that SP
as well but I assumed it was the SP that wasn't working because I
briefly saw this comment, huge is a relative thing!
Works fine now, very handy
NOTE:This procedure may not work with tables with too many columns.
Results can be unpredictable with huge text columns
(newbie) Cant connect to msde Database,
Hi,
I am an ASP.net newbie learning thru databasing now.
When setting up and connectiong to Database, i am prompted at SQL Server
Authentication for a user name and password
When I installed, thru the CMD under Start: Run in Windows 2000, i believe i
specified "sa" and "password" for user name and password. but now i get an
error, trying to input that.
"unable to connect to database.
Login failed for user "sa" Reason : not associated with a trusted SQL Server
connection"
I am an ASP.net newbie learning thru databasing now.
When setting up and connectiong to Database, i am prompted at SQL Server
Authentication for a user name and password
When I installed, thru the CMD under Start: Run in Windows 2000, i believe i
specified "sa" and "password" for user name and password. but now i get an
error, trying to input that.
"unable to connect to database.
Login failed for user "sa" Reason : not associated with a trusted SQL Server
connection"
What am i doing wrong? if i mistyped the password info when setting up,
where can i check it? or reset it?
Please someone help me
RaphaelSo are you using a ASP.Net script to connect to the DB?
The SQL connection should look like:
server=localhost;Trusted_Connection=true;database=theDB;user id=sa;password=password
Does that make sense to you?
Adrian|||um,
No not at first site, I am a newbie, remember!
I am trying to run from the web matrix project, and i simply cannot create a new msde database.
Do you have any further advice?
Thanks
Raphael
(newbie) - Stored Procedure Problem
Can anyone tell me what is wrong with this stored procedure?
CREATE PROCEDURE [dbo].[AddGroupPermission]
@.Perm varchar(16)
AS
ALTER TABLE tblUserGroups ADD @.Perm VARCHAR(1) NULL
GO
When I click on Check Syntax, I get 'Error 170 - Line 4: Incorrect syntax near @.Perm'
I have checked the syntax for the ALTER command, and it looks correct to me...
This is my first day at using SQL Server in anger, so any help appreciated :)You must use dynamic sql for that, check out :
http://www.sqlteam.com/item.asp?ItemID=4599
The following will work
CREATE PROCEDURE [dbo].[AddGroupPermission]
@.Perm varchar(16)
AS
exec ('ALTER TABLE tblUserGroups ADD ' + @.Perm + 'VARCHAR(1) NULL')
GO|||Oh wow! Thanks for that - I didn't even know you could do that directly in SQL server - I'm used to doing it in ASP pages, of course, but this DB is new to me :)
I'm off to check your link now - thanks very much for taking the time to post.
Mark.sql
CREATE PROCEDURE [dbo].[AddGroupPermission]
@.Perm varchar(16)
AS
ALTER TABLE tblUserGroups ADD @.Perm VARCHAR(1) NULL
GO
When I click on Check Syntax, I get 'Error 170 - Line 4: Incorrect syntax near @.Perm'
I have checked the syntax for the ALTER command, and it looks correct to me...
This is my first day at using SQL Server in anger, so any help appreciated :)You must use dynamic sql for that, check out :
http://www.sqlteam.com/item.asp?ItemID=4599
The following will work
CREATE PROCEDURE [dbo].[AddGroupPermission]
@.Perm varchar(16)
AS
exec ('ALTER TABLE tblUserGroups ADD ' + @.Perm + 'VARCHAR(1) NULL')
GO|||Oh wow! Thanks for that - I didn't even know you could do that directly in SQL server - I'm used to doing it in ASP pages, of course, but this DB is new to me :)
I'm off to check your link now - thanks very much for taking the time to post.
Mark.sql
(newbie ques) Access 2000 to SQL Server migrate sync
I am a db newbie and am looking for advice on how to migrate a (fairly large - 900mb) Access b/e to SQL server 2000. The f/e is Access. I've read most of the upsizing info, but need help in understanding how to merge in new records/data which occur while the upsizing is done. (i estimate it will take several days to sort out the upsizing mess). This is a very active b/e, and not a lot of downtime is avail.
thanks for any expert advice
crunchy f.alter the access tables to add an extra column, let's call it Converted, with Required=yes, Allow zero length=No, Default='n'
run the extract to begin the upsizing process, then set Converted='y' for all records before putting the access database back online
all your existing apps can then continue as before
when the first stage of upsizing is done, do another extract, pulling only records where Converted='n' and set these to 'y' before putting the database back online
lather, rinse, repeat
rudy
http://rudy.ca/|||I am also interested in this. But just for my curiosity, can you let me know the significance of this new column etc.,
By the way, my knowledge of access and SQL and Vb is limited as I have learnt through forums like this
Thanks for your time and patience|||the significance of this new column? it marks which rows have been upsized
when you first add the column to the table, all rows have N
then you upsize all rows, and mark them Y (converted)
then the access application adds some more rows into the table, and they all get N by default
now you want to upsize only the recent ones, so you simply select them with converted='N' so that you don't pick up any rows that have already been upsized
rudy
thanks for any expert advice
crunchy f.alter the access tables to add an extra column, let's call it Converted, with Required=yes, Allow zero length=No, Default='n'
run the extract to begin the upsizing process, then set Converted='y' for all records before putting the access database back online
all your existing apps can then continue as before
when the first stage of upsizing is done, do another extract, pulling only records where Converted='n' and set these to 'y' before putting the database back online
lather, rinse, repeat
rudy
http://rudy.ca/|||I am also interested in this. But just for my curiosity, can you let me know the significance of this new column etc.,
By the way, my knowledge of access and SQL and Vb is limited as I have learnt through forums like this
Thanks for your time and patience|||the significance of this new column? it marks which rows have been upsized
when you first add the column to the table, all rows have N
then you upsize all rows, and mark them Y (converted)
then the access application adds some more rows into the table, and they all get N by default
now you want to upsize only the recent ones, so you simply select them with converted='N' so that you don't pick up any rows that have already been upsized
rudy
(MS Access) FIRST command equivalent in SQL Server
Is there an equivalent to the command FIRST from MS Access for SQL Server?
The command 'Top 1' doesn't seem to be the same...
:confused:What functionality do you need ?
The command 'Top 1' doesn't seem to be the same...
:confused:What functionality do you need ?
(MDX) EXISTING operator - inconsistent behaviour when using subcubes?
I am getting what I think is inconsistent behaviour when using the EXISTING operator to obtain a set defined by the current query context... See the two queries below (explanation follows).
WITH MEMBER [Measures].[MyDate] AS
Tail(EXISTING [Date].[Calendar].[Date], 1).item(0).membervalue
SELECT {[Measures].[MyDate]} ON 0
FROM [Adventure Works]
WHERE [Date].[Date].[October 26, 2002]
... returns [26-10-2002]
WITH MEMBER [Measures].[MyDate] AS
Tail(EXISTING [Date].[Calendar].[Date], 1).item(0).membervalue
SELECT {[Measures].[MyDate]} ON 0
FROM
(
SELECT [Date].[Date].[October 26, 2002] ON 0
FROM [Adventure Works]
)
... return [31-08-2004] (the last date in the Adventure Works time dimension).
From my point of view the two queries should return the same date (October 26, 2002), since the context is the same. The only difference is that in the first query the context is specified in the WHERE clause, whereas the second query specifies the context in a subcube. There really should not be any difference, should there? 
Chris, I was looking for an answer from you - not quite the answer I hoped for though. 
I see your point, but if what you are saying is true, the value of the EXISTING operator is not very high and it will be very questionable if Moshas approach to writing "multiselect friendly MDX calculations" (http://www.sqljunkies.com/WebLog/mosha/archive/2005/11/18/multiselect_friendly_mdx.aspx) is applicable (which I really hope it is!). Does it not also fail to explain why the following query only returns the sum of [Internet Sales Amount] from 2002 and 2003 (which - to me - is expected behavior)?
WITH MEMBER [Measures].[Test 2] AS
SUM(EXISTING [Date].[Date], [Measures].[Internet Sales Amount])
SELECT [Measures].[Test 2] ON 0
FROM
(SELECT {[Date].[Calendar Year].&[2002], [Date].[Calendar Year].&[2003]} ON 0 FROM [Adventure Works])
If what you are saying is true, wouldn't the calculated member return a sum for all years - since the calculated member would "look outside" the subsube, which is restricted to 2002 and 2003?
|||I think you've made a mistake in your query - the expression [Date].[Date] resolves to the All Member of that hierarchy. I think what you wanted to do was this:
WITH MEMBER [Measures].[Test 2] AS
SUM(EXISTING [Date].[Date].[Date].Members, [Measures].[Internet Sales Amount])
SELECT [Measures].[Test 2] ON 0
FROM
(SELECT {[Date].[Calendar Year].&[2002], [Date].[Calendar Year].&[2003]} ON 0 FROM [Adventure Works])
Which returns a different (and higher) value, the sum of all years.
You're right, though, it does cause problems if you're using subcubes instead of sets in the where clause and following Mosha's advice on multi-select friendly MDX.
Chris
|||You're right Chris - thanks... I will try go get Mosha to comment on this issue... I will follow up in this thread.|||I know this was an area where there was some discussion about what the most useful behaviour would be - on balance, I think they've done the right thing. As far as the multi-select issue goes, is there any reason why you're not using sets in the WHERE clause instead?
<Cheeky Plug>
This topic is discussed in the second edition of 'MDX Solutions' in one of George's chapters:
http://www.amazon.co.uk/exec/obidos/redirect?link_code=ur2&tag=chriswebbsbib-21&camp=1634&creative=6738&path=ASIN%2F0471748080%2Fqid%3D1141661836%2Fsr%3D8-3%2Fref%3Dsr_8_xs_ap_i3_xgl
</Cheeky Plug>
Chris
|||Just placed an order for this book yesterday, so I am looking forward to reading the good arguments for the current behavior. 
Why am I not using sets in the where clause for multi-select? There are more than one reason actually.
1) If a hierarchy is placed in the WHERE clause, the same hierarchy cannot - as you know - be placed on an axis (which, in many cases, in desireable).
2) The use of the WHERE clause vs. the use of subcubes for restricting the query result varies from client tool to client tool. The cube browser in SSMS, for example, uses both subcubes and sets in the WHERE clause depending on how you setup the query.
One could argue - perhaps - that the client tool should be smart enough to always place any hierarchy not already on an axis in the WHERE clause?! I wonder if this would always solve any problem? Hmm... 
EXISTING operator takes into account current coordinate. The whole difference between WHERE clause and subselect, is that WHERE clause sets current coordinate, while subselects merely do top level Exists with Axis (BTW WHERE clause also does it), and apply visual totals.
The confusion between WHERE and subselects is big and seem to grow every day :( It is unfortunate that not everybody realizes the semantic difference between the two and when each should be used. Perhaps we need another operator in MDX which will take into account the current restriction of subselect and/or CREATE subcube. Excel 12 is only going to add to the confusion, since it uses subselects extensively and it uses them for multiselect too, rendering my advice on how to write multiselect friendly calculations less useful...
Mosha
|||I blogged on this topic.
http://sqljunkies.com/WebLog/reckless/archive/2006/03/08/18601.aspx
Comments are welcome...
(long) querry doesn't fit in "query-string" window
hi,
DIM x AS New System.Text.StringBuilder()
x.Append("SELECT ") ' etc...
RETURN x.ToString()
End Function
i have worked three days on a query to display all my results in a beautiful report. The query is fine because when i execute it in Query Analyzer i have all results i want to see in my statistics-table in my report...
One thing: it's contains about 100 unioned statements, which results in a super-long query. Performance is OK because it are all 100 very easy statements that are union-ed together.
But, when I copy-paste it in my query-string window/textbox of the report designer, I see that there's a maximum on that textbox lenght, which results in the fact that my long query suddenly stops.
Any solutions?
put it in a stored procedure 
|||yes of course, but this was not asked by the client.
i tried already to edit the rdl file directly. (i paste my whole query in the xml structure), but then when i ask a report preview via BIDS, the BIDS crashes !!!!!!!!!!!!!!!!
i have no other choice then do the workaround via the stored procedure,
but it is really a bug in microsoft i say, isn't it?
|||
You have another alternative...
Create your query string in embedded code, like this:
Function GetSQLDIM x AS New System.Text.StringBuilder()
x.Append("SELECT ") ' etc...
RETURN x.ToString()
End Function
... Now your query string looks like this
= Code.GetSQL()
HTH, and yes it works <g>, and when you're trying to create a dynamic query out of a lot of parameters it's a heck of a lot more maintainable/legible, too,
>L<
(local) vs. the actual name of SQL Server when using a named instance
Hello,
My question is this: in a named instance installation of a SQL Server, when
you reference the server as (local) from a Stored Procedure that accesses a
table for example, will that point to the Named Instance or will it access
the table on the actual database on the main installation?
Thank you
leo
WHere yo you name the server as (local) in a stored procedure ? You
don=B4t have to. Just use the three or two part name that should be
enough for you.
HTH, Jens Suessmeyer,
sql
My question is this: in a named instance installation of a SQL Server, when
you reference the server as (local) from a Stored Procedure that accesses a
table for example, will that point to the Named Instance or will it access
the table on the actual database on the main installation?
Thank you
leo
WHere yo you name the server as (local) in a stored procedure ? You
don=B4t have to. Just use the three or two part name that should be
enough for you.
HTH, Jens Suessmeyer,
sql
(local) vs. the actual name of SQL Server when using a named instance
Hello,
My question is this: in a named instance installation of a SQL Server, when
you reference the server as (local) from a Stored Procedure that accesses a
table for example, will that point to the Named Instance or will it access
the table on the actual database on the main installation?
Thank you
leoWHere yo you name the server as (local) in a stored procedure ? You
don=B4t have to. Just use the three or two part name that should be
enough for you.
HTH, Jens Suessmeyer,|||Referencing (local) implies that you are creating a new connection. This
connection won't know which instance the command comes from so will connect
to the default instance.
Is this bcp or osql? Can't think of another reason for it.
If you want to connect to the same instance from an sp then use @.@.servername
and put it in [].
"Leo" wrote:
> Hello,
> My question is this: in a named instance installation of a SQL Server, when
> you reference the server as (local) from a Stored Procedure that accesses a
> table for example, will that point to the Named Instance or will it access
> the table on the actual database on the main installation?
>
> Thank you
> leo
>
>|||I am actually running an Instance of SQL server on the same machine as an
installation of SQL server. The reason for that is QA testing. When I make a
change to the database I copy it to the instance os SQL on the same machine.
I am using store procedures to run DTS packages. So I am doing a DTSRUN
command on the package. On my SQL server I was using (local) as my server.
The reason I asked was that I didn't want to go into the Instance of the SQL
and change the name of the server on the stored procedures manually. I
wanted the stored proc to pick up the name of the server it;s running on
without manual changes to the code. I was affraid that if I leave that code
unchanged as (local) that the stored proc will look for the wrong DTS
package, meaning the one on the SQL server, not the one in the instance of
SQL. I am not sure if I made this more confusing or more explained.
in short I am trying to provide the samew functionality within one physical
sever using an installation of a SQL instance, as if it was two separate
physical servers, where you can reference (local) and whenever you more a
stored proc from one server to the other you do not need to change that code
to point to the right SQL server
Thanks
Leo
"Nigel Rivett" <NigelRivett@.discussions.microsoft.com> wrote in message
news:15408704-EE37-4A96-97D2-1BF39A6CE5AE@.microsoft.com...
> Referencing (local) implies that you are creating a new connection. This
> connection won't know which instance the command comes from so will
connect
> to the default instance.
> Is this bcp or osql? Can't think of another reason for it.
> If you want to connect to the same instance from an sp then use
@.@.servername
> and put it in [].
> "Leo" wrote:
> > Hello,
> >
> > My question is this: in a named instance installation of a SQL Server,
when
> > you reference the server as (local) from a Stored Procedure that
accesses a
> > table for example, will that point to the Named Instance or will it
access
> > the table on the actual database on the main installation?
> >
> >
> >
> > Thank you
> >
> > leo
> >
> >
> >
My question is this: in a named instance installation of a SQL Server, when
you reference the server as (local) from a Stored Procedure that accesses a
table for example, will that point to the Named Instance or will it access
the table on the actual database on the main installation?
Thank you
leoWHere yo you name the server as (local) in a stored procedure ? You
don=B4t have to. Just use the three or two part name that should be
enough for you.
HTH, Jens Suessmeyer,|||Referencing (local) implies that you are creating a new connection. This
connection won't know which instance the command comes from so will connect
to the default instance.
Is this bcp or osql? Can't think of another reason for it.
If you want to connect to the same instance from an sp then use @.@.servername
and put it in [].
"Leo" wrote:
> Hello,
> My question is this: in a named instance installation of a SQL Server, when
> you reference the server as (local) from a Stored Procedure that accesses a
> table for example, will that point to the Named Instance or will it access
> the table on the actual database on the main installation?
>
> Thank you
> leo
>
>|||I am actually running an Instance of SQL server on the same machine as an
installation of SQL server. The reason for that is QA testing. When I make a
change to the database I copy it to the instance os SQL on the same machine.
I am using store procedures to run DTS packages. So I am doing a DTSRUN
command on the package. On my SQL server I was using (local) as my server.
The reason I asked was that I didn't want to go into the Instance of the SQL
and change the name of the server on the stored procedures manually. I
wanted the stored proc to pick up the name of the server it;s running on
without manual changes to the code. I was affraid that if I leave that code
unchanged as (local) that the stored proc will look for the wrong DTS
package, meaning the one on the SQL server, not the one in the instance of
SQL. I am not sure if I made this more confusing or more explained.
in short I am trying to provide the samew functionality within one physical
sever using an installation of a SQL instance, as if it was two separate
physical servers, where you can reference (local) and whenever you more a
stored proc from one server to the other you do not need to change that code
to point to the right SQL server
Thanks
Leo
"Nigel Rivett" <NigelRivett@.discussions.microsoft.com> wrote in message
news:15408704-EE37-4A96-97D2-1BF39A6CE5AE@.microsoft.com...
> Referencing (local) implies that you are creating a new connection. This
> connection won't know which instance the command comes from so will
connect
> to the default instance.
> Is this bcp or osql? Can't think of another reason for it.
> If you want to connect to the same instance from an sp then use
@.@.servername
> and put it in [].
> "Leo" wrote:
> > Hello,
> >
> > My question is this: in a named instance installation of a SQL Server,
when
> > you reference the server as (local) from a Stored Procedure that
accesses a
> > table for example, will that point to the Named Instance or will it
access
> > the table on the actual database on the main installation?
> >
> >
> >
> > Thank you
> >
> > leo
> >
> >
> >
(local) vs. the actual name of SQL Server when using a named instance
Hello,
My question is this: in a named instance installation of a SQL Server, when
you reference the server as (local) from a Stored Procedure that accesses a
table for example, will that point to the Named Instance or will it access
the table on the actual database on the main installation?
Thank you
leoWHere yo you name the server as (local) in a stored procedure ? You
don=B4t have to. Just use the three or two part name that should be
enough for you.
HTH, Jens Suessmeyer,
My question is this: in a named instance installation of a SQL Server, when
you reference the server as (local) from a Stored Procedure that accesses a
table for example, will that point to the Named Instance or will it access
the table on the actual database on the main installation?
Thank you
leoWHere yo you name the server as (local) in a stored procedure ? You
don=B4t have to. Just use the three or two part name that should be
enough for you.
HTH, Jens Suessmeyer,
(local) old db 4 sql server suddenly fails
Several DTS packages that use (local) as the server name
in the ms ole db providor for sql server suddenly broke.
They'd worked well for years. The existing connection
using (local) won't find the default instance on the
machine. A new connect using (local) works file. SQL
Server is 2k enterprise, sp 3a (818). OS is w2k
5.00.2195. Doesn't matter if the connection
authentication is W2k authenticated or standardHi,
It seems like the netbios name might have changed and connection still
resolving the (local) to old netbios.
Try using Select @.@.SERVERNAME and/or Select SERVERPROPERTY('servername')
thanks,
ashish
"bob b" <anonymous@.discussions.microsoft.com> wrote in message
news:6d6d01c3e69d$d7b3bac0$a401280a@.phx.gbl...
in the ms ole db providor for sql server suddenly broke.
They'd worked well for years. The existing connection
using (local) won't find the default instance on the
machine. A new connect using (local) works file. SQL
Server is 2k enterprise, sp 3a (818). OS is w2k
5.00.2195. Doesn't matter if the connection
authentication is W2k authenticated or standardHi,
It seems like the netbios name might have changed and connection still
resolving the (local) to old netbios.
Try using Select @.@.SERVERNAME and/or Select SERVERPROPERTY('servername')
thanks,
ashish
"bob b" <anonymous@.discussions.microsoft.com> wrote in message
news:6d6d01c3e69d$d7b3bac0$a401280a@.phx.gbl...
quote:
> Several DTS packages that use (local) as the server name
> in the ms ole db providor for sql server suddenly broke.
> They'd worked well for years. The existing connection
> using (local) won't find the default instance on the
> machine. A new connect using (local) works file. SQL
> Server is 2k enterprise, sp 3a (818). OS is w2k
> 5.00.2195. Doesn't matter if the connection
> authentication is W2k authenticated or standard
(local) name explanation please
In some SQL2K instances, I have (LOCAL) in others its (Local) and in
development code the connect to server parameter is always local.
Where is (local) associated to 127.0.0.1 or local server name?
If I have 2 or more instances of SQL Server on my maching which one would
(local) refer too?
TIA
Neil.
(local) or . refers to the local machine. The dot (.) or local tells the
client that the host is local and not remote, such that a local pipe
connection can be made. This avoids going on the network. This applied
more so to SQL 6.5, and SQL 7.0, since with SQL 2000 we use Shared Memory
for local connections.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||Thanks for your response.
So this is pipe and not tcp/ip then. Can you point me to any books
on-line/help files that actually explain the local usage please as they must
be somewhere?
Also do you know why LOCAL is uppercase on some machines and lowercase on
others in EM I mean?
thanks
"Kevin McDonnell [MSFT]" wrote:
> (local) or . refers to the local machine. The dot (.) or local tells the
> client that the host is local and not remote, such that a local pipe
> connection can be made. This avoids going on the network. This applied
> more so to SQL 6.5, and SQL 7.0, since with SQL 2000 we use Shared Memory
> for local connections.
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>
|||Yes. local pipe is a pipe connection not a socket based connection.
We mention local pipes briefly in Books Online under the topic "Client and
Server Net-Libraries".
I've also seen it discussed in Network Programming books.
"Windows NT Network Programming" Davis.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
development code the connect to server parameter is always local.
Where is (local) associated to 127.0.0.1 or local server name?
If I have 2 or more instances of SQL Server on my maching which one would
(local) refer too?
TIA
Neil.
(local) or . refers to the local machine. The dot (.) or local tells the
client that the host is local and not remote, such that a local pipe
connection can be made. This avoids going on the network. This applied
more so to SQL 6.5, and SQL 7.0, since with SQL 2000 we use Shared Memory
for local connections.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||Thanks for your response.
So this is pipe and not tcp/ip then. Can you point me to any books
on-line/help files that actually explain the local usage please as they must
be somewhere?
Also do you know why LOCAL is uppercase on some machines and lowercase on
others in EM I mean?
thanks
"Kevin McDonnell [MSFT]" wrote:
> (local) or . refers to the local machine. The dot (.) or local tells the
> client that the host is local and not remote, such that a local pipe
> connection can be made. This avoids going on the network. This applied
> more so to SQL 6.5, and SQL 7.0, since with SQL 2000 we use Shared Memory
> for local connections.
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>
|||Yes. local pipe is a pipe connection not a socket based connection.
We mention local pipes briefly in Books Online under the topic "Client and
Server Net-Libraries".
I've also seen it discussed in Network Programming books.
"Windows NT Network Programming" Davis.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
(local) name explanation please
In some SQL2K instances, I have (LOCAL) in others its (Local) and in
development code the connect to server parameter is always local.
Where is (local) associated to 127.0.0.1 or local server name?
If I have 2 or more instances of SQL Server on my maching which one would
(local) refer too?
TIA
Neil.(local) or . refers to the local machine. The dot (.) or local tells the
client that the host is local and not remote, such that a local pipe
connection can be made. This avoids going on the network. This applied
more so to SQL 6.5, and SQL 7.0, since with SQL 2000 we use Shared Memory
for local connections.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Thanks for your response.
So this is pipe and not tcp/ip then. Can you point me to any books
on-line/help files that actually explain the local usage please as they must
be somewhere?
Also do you know why LOCAL is uppercase on some machines and lowercase on
others in EM I mean?
thanks
"Kevin McDonnell [MSFT]" wrote:
> (local) or . refers to the local machine. The dot (.) or local tells th
e
> client that the host is local and not remote, such that a local pipe
> connection can be made. This avoids going on the network. This applied
> more so to SQL 6.5, and SQL 7.0, since with SQL 2000 we use Shared Memory
> for local connections.
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>|||Yes. local pipe is a pipe connection not a socket based connection.
We mention local pipes briefly in Books Online under the topic "Client and
Server Net-Libraries".
I've also seen it discussed in Network programming books.
"Windows NT Network Programming" Davis.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.sql
development code the connect to server parameter is always local.
Where is (local) associated to 127.0.0.1 or local server name?
If I have 2 or more instances of SQL Server on my maching which one would
(local) refer too?
TIA
Neil.(local) or . refers to the local machine. The dot (.) or local tells the
client that the host is local and not remote, such that a local pipe
connection can be made. This avoids going on the network. This applied
more so to SQL 6.5, and SQL 7.0, since with SQL 2000 we use Shared Memory
for local connections.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Thanks for your response.
So this is pipe and not tcp/ip then. Can you point me to any books
on-line/help files that actually explain the local usage please as they must
be somewhere?
Also do you know why LOCAL is uppercase on some machines and lowercase on
others in EM I mean?
thanks
"Kevin McDonnell [MSFT]" wrote:
> (local) or . refers to the local machine. The dot (.) or local tells th
e
> client that the host is local and not remote, such that a local pipe
> connection can be made. This avoids going on the network. This applied
> more so to SQL 6.5, and SQL 7.0, since with SQL 2000 we use Shared Memory
> for local connections.
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>|||Yes. local pipe is a pipe connection not a socket based connection.
We mention local pipes briefly in Books Online under the topic "Client and
Server Net-Libraries".
I've also seen it discussed in Network programming books.
"Windows NT Network Programming" Davis.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.sql
(local) database wont start?
I dont know how this happened but my (local) SQL Server wont start. I cant access any of the databases. I just get this error:
'A connection could not be established to (local).
Reason: SQL Server does not exist or access denied.
ConnectionOpen (Connect())
Please verify SQL Server is running and check your SQL Server registration properties (by right-clicking on the (local) node) and try again.'
If I go to check the properties I get the same error. Im completely locked out. I looked at the folder and the database files are there, but I cant get to them through Enterprise Manager.
I have no idea how this problem could have happened. It just seemed to have stopped working. All my other databases can be reached (they are external). Could this be a virus or something? Can someone help me out on this one? I dont have a clue what the problem is, much less how to fix it.
Thanks.Are you CERTAIN that the MSSQLServer service is running? A virus is unlikely the cause.
(local) Alias does not function in an virtual sql server?
When I try to connect to (local) server (in Query Analyzer for example) on an
virtual sql server, the application tries to connect to the active node
instead, resulting in an error message (SQL Server does not exist or access
denied).
Is it possible to configure this alias to connect to the virtual sql server?
Furthermore I recognized, that DTS-Packages with (local)-Data Sources still
work on the virtual server. On a second, similar installed server, this
results in the same error message.
Any thoughts?
Martin Saalmann
There is an Environment Variable you can use in order to run the SQLDIAG
utility on the active node. I'm not sure if it uses the (local) or the "."
designation, but it would be worth a shot.
Type set _CLUSTER_NETWORK_NAME_=SQL NETWORK NAME, and then press ENTER.
NOTE: The SQL NETWORK NAME is the SQL Server virtual name for a named
instance. This is only the first part of the name. For example, if the
instance name is VSQL2\INST2, the SQL NETWORK NAME is VSQL2.
INF: How To Run SQLDIAG On a Clustered/Virtual SQL Server
http://support.microsoft.com/kb/233332/EN-US/
Sincerely,
Anthony Thomas
"Martin Saalmann" <MartinSaalmann@.discussions.microsoft.com> wrote in
message news:8CD4BBE4-7548-4BD5-BDF1-7B437D2A102E@.microsoft.com...
> When I try to connect to (local) server (in Query Analyzer for example) on
an
> virtual sql server, the application tries to connect to the active node
> instead, resulting in an error message (SQL Server does not exist or
access
> denied).
> Is it possible to configure this alias to connect to the virtual sql
server?
> Furthermore I recognized, that DTS-Packages with (local)-Data Sources
still
> work on the virtual server. On a second, similar installed server, this
> results in the same error message.
> Any thoughts?
> Martin Saalmann
>
|||Martin,
you could create an Alias on each Node in the Client Network Utility with
name (local) and then the real name to point at the SQL Virtual Name
Andy.
"Martin Saalmann" <MartinSaalmann@.discussions.microsoft.com> wrote in
message news:8CD4BBE4-7548-4BD5-BDF1-7B437D2A102E@.microsoft.com...
> When I try to connect to (local) server (in Query Analyzer for example) on
> an
> virtual sql server, the application tries to connect to the active node
> instead, resulting in an error message (SQL Server does not exist or
> access
> denied).
> Is it possible to configure this alias to connect to the virtual sql
> server?
> Furthermore I recognized, that DTS-Packages with (local)-Data Sources
> still
> work on the virtual server. On a second, similar installed server, this
> results in the same error message.
> Any thoughts?
> Martin Saalmann
>
virtual sql server, the application tries to connect to the active node
instead, resulting in an error message (SQL Server does not exist or access
denied).
Is it possible to configure this alias to connect to the virtual sql server?
Furthermore I recognized, that DTS-Packages with (local)-Data Sources still
work on the virtual server. On a second, similar installed server, this
results in the same error message.
Any thoughts?
Martin Saalmann
There is an Environment Variable you can use in order to run the SQLDIAG
utility on the active node. I'm not sure if it uses the (local) or the "."
designation, but it would be worth a shot.
Type set _CLUSTER_NETWORK_NAME_=SQL NETWORK NAME, and then press ENTER.
NOTE: The SQL NETWORK NAME is the SQL Server virtual name for a named
instance. This is only the first part of the name. For example, if the
instance name is VSQL2\INST2, the SQL NETWORK NAME is VSQL2.
INF: How To Run SQLDIAG On a Clustered/Virtual SQL Server
http://support.microsoft.com/kb/233332/EN-US/
Sincerely,
Anthony Thomas
"Martin Saalmann" <MartinSaalmann@.discussions.microsoft.com> wrote in
message news:8CD4BBE4-7548-4BD5-BDF1-7B437D2A102E@.microsoft.com...
> When I try to connect to (local) server (in Query Analyzer for example) on
an
> virtual sql server, the application tries to connect to the active node
> instead, resulting in an error message (SQL Server does not exist or
access
> denied).
> Is it possible to configure this alias to connect to the virtual sql
server?
> Furthermore I recognized, that DTS-Packages with (local)-Data Sources
still
> work on the virtual server. On a second, similar installed server, this
> results in the same error message.
> Any thoughts?
> Martin Saalmann
>
|||Martin,
you could create an Alias on each Node in the Client Network Utility with
name (local) and then the real name to point at the SQL Virtual Name
Andy.
"Martin Saalmann" <MartinSaalmann@.discussions.microsoft.com> wrote in
message news:8CD4BBE4-7548-4BD5-BDF1-7B437D2A102E@.microsoft.com...
> When I try to connect to (local) server (in Query Analyzer for example) on
> an
> virtual sql server, the application tries to connect to the active node
> instead, resulting in an error message (SQL Server does not exist or
> access
> denied).
> Is it possible to configure this alias to connect to the virtual sql
> server?
> Furthermore I recognized, that DTS-Packages with (local)-Data Sources
> still
> work on the virtual server. On a second, similar installed server, this
> results in the same error message.
> Any thoughts?
> Martin Saalmann
>
(local)
Dear ALL,
my environment is SQL 2000 SP4 on WIN2003 Server Standard.
In enterprise manager, when I have to create a new sql registration, on a
server I can see (local) instance, on the other one NO !!
Server are the same, (HP Proliant DL380) , please could anyone of you give
me an help ?
Regards
Alberto
Brivio
tel. 011-5813734Type in (local) and hit the >> button.
"Alberto Brivio" <a.brivio@.adb.it> wrote in message
news:eaHeUVTxGHA.4972@.TK2MSFTNGP05.phx.gbl...
> Dear ALL,
>
> my environment is SQL 2000 SP4 on WIN2003 Server Standard.
> In enterprise manager, when I have to create a new sql registration, on a
> server I can see (local) instance, on the other one NO !!
> Server are the same, (HP Proliant DL380) , please could anyone of you
> give me an help ?
>
> Regards
> Alberto
> Brivio
>
> --
> tel. 011-5813734
>|||Hi Brivio,
Please try to reframe your question clearly. There are two suggestions i can
give with my understanding of your issue.
1. Follow Mike advice or register using host name
2. If you have one more instance on the same box, then it should be named
instance. Then you have to register your sql with hostname\instancename.
(Called named instance)
If you have any clarification, please come up with clear scnerio of your
issue.
Regards,
Rajesh Kumar
SQLDBA
"Alberto Brivio" wrote:
> Dear ALL,
>
> my environment is SQL 2000 SP4 on WIN2003 Server Standard.
> In enterprise manager, when I have to create a new sql registration, on a
> server I can see (local) instance, on the other one NO !!
> Server are the same, (HP Proliant DL380) , please could anyone of you giv
e
> me an help ?
>
> Regard
s
> Albert
o
> Brivio
>
> --
> tel. 011-5813734
>
>
my environment is SQL 2000 SP4 on WIN2003 Server Standard.
In enterprise manager, when I have to create a new sql registration, on a
server I can see (local) instance, on the other one NO !!
Server are the same, (HP Proliant DL380) , please could anyone of you give
me an help ?
Regards
Alberto
Brivio
tel. 011-5813734Type in (local) and hit the >> button.
"Alberto Brivio" <a.brivio@.adb.it> wrote in message
news:eaHeUVTxGHA.4972@.TK2MSFTNGP05.phx.gbl...
> Dear ALL,
>
> my environment is SQL 2000 SP4 on WIN2003 Server Standard.
> In enterprise manager, when I have to create a new sql registration, on a
> server I can see (local) instance, on the other one NO !!
> Server are the same, (HP Proliant DL380) , please could anyone of you
> give me an help ?
>
> Regards
> Alberto
> Brivio
>
> --
> tel. 011-5813734
>|||Hi Brivio,
Please try to reframe your question clearly. There are two suggestions i can
give with my understanding of your issue.
1. Follow Mike advice or register using host name
2. If you have one more instance on the same box, then it should be named
instance. Then you have to register your sql with hostname\instancename.
(Called named instance)
If you have any clarification, please come up with clear scnerio of your
issue.
Regards,
Rajesh Kumar
SQLDBA
"Alberto Brivio" wrote:
> Dear ALL,
>
> my environment is SQL 2000 SP4 on WIN2003 Server Standard.
> In enterprise manager, when I have to create a new sql registration, on a
> server I can see (local) instance, on the other one NO !!
> Server are the same, (HP Proliant DL380) , please could anyone of you giv
e
> me an help ?
>
> Regard
s
> Albert
o
> Brivio
>
> --
> tel. 011-5813734
>
>
(Less Than) vs. (Less Than Or Equal To)
Assume ID is an Integer field, which is faster (or is there any difference at all)?
Code Snippet
select * from <MyTable> where ID < 51
--or
select * from <MyTable> where ID <=50
You should get the same perf. It's just a constant filter.
|||Thanks much. That's been my experience, but wanted to make sure I wasn't leaving some performance on the table.(IR) is there a sql server for Linux !?
Is there a sql server for Linux !'Microsoft SQL Server only runs on Windows platforms.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"iraidmir" <iradimir@.inet.hr> wrote in message
news:bts7rm$r32$1@.sunce.iskon.hr...
> Is there a sql server for Linux !'
>|||Hi,
There is no server installation for SQL Server in Linux, but we can connect
a SQL Server from Linux Box.
But to connect from Linux we need to have a user with SQL Server
authentication.
Thanks
Hari
MCDBA
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:ezx2guH2DHA.1764@.TK2MSFTNGP10.phx.gbl...
> Microsoft SQL Server only runs on Windows platforms.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "iraidmir" <iradimir@.inet.hr> wrote in message
> news:bts7rm$r32$1@.sunce.iskon.hr...
> > Is there a sql server for Linux !'
> >
> >
>|||Not from MS. Do a search for MySQL.
> Is there a sql server for Linux !'
>
Neil Pike MVP/MCSE. Protech Computing Ltd
Reply here - no email
SQL FAQ (484 entries) see
http://forumsb.compuserve.com/gvforums/UK/default.asp?SRV=MSDevApps
(faqxxx.zip in lib 7)
or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
or www.sqlserverfaq.com
or www.mssqlserver.com/faq|||Hi,
SQL Server doesn't run on Linux as mentioned in the previous postings
but you can certainly connect a Linux machine to SQL Server running on
a remote Windows server. You might want to look at the OpenLink ODBC
drivers to achieve this:
http://www.openlinksw.com
I hope this helps,
Ashok Rautela
"iraidmir" <iradimir@.inet.hr> wrote in message news:<bts7rm$r32$1@.sunce.iskon.hr>...
> Is there a sql server for Linux !'
--
Hope this helps.
Dan Guzman
SQL Server MVP
"iraidmir" <iradimir@.inet.hr> wrote in message
news:bts7rm$r32$1@.sunce.iskon.hr...
> Is there a sql server for Linux !'
>|||Hi,
There is no server installation for SQL Server in Linux, but we can connect
a SQL Server from Linux Box.
But to connect from Linux we need to have a user with SQL Server
authentication.
Thanks
Hari
MCDBA
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:ezx2guH2DHA.1764@.TK2MSFTNGP10.phx.gbl...
> Microsoft SQL Server only runs on Windows platforms.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "iraidmir" <iradimir@.inet.hr> wrote in message
> news:bts7rm$r32$1@.sunce.iskon.hr...
> > Is there a sql server for Linux !'
> >
> >
>|||Not from MS. Do a search for MySQL.
> Is there a sql server for Linux !'
>
Neil Pike MVP/MCSE. Protech Computing Ltd
Reply here - no email
SQL FAQ (484 entries) see
http://forumsb.compuserve.com/gvforums/UK/default.asp?SRV=MSDevApps
(faqxxx.zip in lib 7)
or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
or www.sqlserverfaq.com
or www.mssqlserver.com/faq|||Hi,
SQL Server doesn't run on Linux as mentioned in the previous postings
but you can certainly connect a Linux machine to SQL Server running on
a remote Windows server. You might want to look at the OpenLink ODBC
drivers to achieve this:
http://www.openlinksw.com
I hope this helps,
Ashok Rautela
"iraidmir" <iradimir@.inet.hr> wrote in message news:<bts7rm$r32$1@.sunce.iskon.hr>...
> Is there a sql server for Linux !'
(IR) is there a sql server for Linux !?
Is there a sql server for Linux !'Microsoft SQL Server only runs on Windows platforms.
Hope this helps.
Dan Guzman
SQL Server MVP
"iraidmir" <iradimir@.inet.hr> wrote in message
news:bts7rm$r32$1@.sunce.iskon.hr...
There is no server installation for SQL Server in Linux, but we can connect
a SQL Server from Linux Box.
But to connect from Linux we need to have a user with SQL Server
authentication.
Thanks
Hari
MCDBA
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:ezx2guH2DHA.1764@.TK2MSFTNGP10.phx.gbl...
Neil Pike MVP/MCSE. Protech Computing Ltd
Reply here - no email
SQL FAQ (484 entries) see
http://forumsb.compuserve.com/gvfor...p?SRV=MSDevApps
(faqxxx.zip in lib 7)
or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
or www.sqlserverfaq.com
or www.mssqlserver.com/faq|||Hi,
SQL Server doesn't run on Linux as mentioned in the previous postings
but you can certainly connect a Linux machine to SQL Server running on
a remote Windows server. You might want to look at the OpenLink ODBC
drivers to achieve this:
http://www.openlinksw.com
I hope this helps,
Ashok Rautela
"iraidmir" <iradimir@.inet.hr> wrote in message news:<bts7rm$r32$1@.sunce.iskon.hr>...
Hope this helps.
Dan Guzman
SQL Server MVP
"iraidmir" <iradimir@.inet.hr> wrote in message
news:bts7rm$r32$1@.sunce.iskon.hr...
quote:|||Hi,
> Is there a sql server for Linux !'
>
There is no server installation for SQL Server in Linux, but we can connect
a SQL Server from Linux Box.
But to connect from Linux we need to have a user with SQL Server
authentication.
Thanks
Hari
MCDBA
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:ezx2guH2DHA.1764@.TK2MSFTNGP10.phx.gbl...
quote:|||Not from MS. Do a search for MySQL.
> Microsoft SQL Server only runs on Windows platforms.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "iraidmir" <iradimir@.inet.hr> wrote in message
> news:bts7rm$r32$1@.sunce.iskon.hr...
>
quote:
> Is there a sql server for Linux !'
>
Neil Pike MVP/MCSE. Protech Computing Ltd
Reply here - no email
SQL FAQ (484 entries) see
http://forumsb.compuserve.com/gvfor...p?SRV=MSDevApps
(faqxxx.zip in lib 7)
or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
or www.sqlserverfaq.com
or www.mssqlserver.com/faq|||Hi,
SQL Server doesn't run on Linux as mentioned in the previous postings
but you can certainly connect a Linux machine to SQL Server running on
a remote Windows server. You might want to look at the OpenLink ODBC
drivers to achieve this:
http://www.openlinksw.com
I hope this helps,
Ashok Rautela
"iraidmir" <iradimir@.inet.hr> wrote in message news:<bts7rm$r32$1@.sunce.iskon.hr>...
quote:
darkred">
> Is there a sql server for Linux !'
(how frequent) in SQL Server?!
I have used Base SAS for analysis for a while and it was really great.. everything is easy just with a simple command.. I am sure it's not the same in SQL Server but I need some help on how to start with the following:
I have a field called call_country and another field called call_minute. Each call will be saved with the destination country and the total number of minutes..
and I want to run a query to see what are the TOP frequent destinations in this format:
United States - Count: 420 - Total Minues: 12,345
It should be easy in SQL too.
SELECT call_country, COUNT(call_minutes) AS tCount, SUM(call_minutes) AS TotalMinutes
FROM calltable
GROUP BY call_country
|||If you want use the top, you can do this:
--1.based on total minutes
SELECT TOP (1) call_country, COUNT(call_minutes) AS tCount, SUM(call_minutes) AS TotalMinutes
FROM calltable
GROUP BY call_country
ORDER BY TotalMinutes DESC
--2. based on total count
SELECT TOP (1) call_country, COUNT(call_minutes) AS tCount, SUM(call_minutes) AS TotalMinutes
FROM calltable
GROUP BY call_country
ORDER BY tCount DESC
--3.based on total count and use total minutes as tie break.
SELECT TOP (1) call_country, COUNT(call_minutes) AS tCount, SUM(call_minutes) AS TotalMinutes
FROM calltable
GROUP BY call_country
ORDER BY tCount DESC, TotalMinutes DESC
--4.based on total minutes and use total count as tie break.
SELECT TOP (1) call_country, COUNT(call_minutes) AS tCount, SUM(call_minutes) AS TotalMinutes
FROM calltable
GROUP BY call_country
ORDER BY TotalMinutes DESC, tCount DESC
(Harder?) cube query & design question
Hi,
I have an MDX query (and worst case a cube design) problem that I haven't been able to solve, any ideas on how to go about this? Here's a simplified description, starting with the
Dimension & Attributes
* We make phone [Call]s.
* In each [Call], and for a number of [Product]s we ask a number of [Question]s.
* Each [Question] results in an [AnswerText]. These also reside in a user hierarchy [Answer Dimension].[Q and A].
* All of the above are attributes in the [Answer Dimension].
Facts
* Measures.[Answer Count], which at the granular level is always one, i.e. each fact records that we recieved a single [AnswerText] to a single [Question] about a single [Product] in a single [Call].
* Measures.[Call Count], which is the number of [Call]s made.
NB: We have thousands of different questions and answers, so surfacing each individual question and answer as a measure is not an option.
Queries & issues
* Counting the answers to a single particular question is easy:
SELECT
Measures.[Answer Count] ON 0
FROM cube
WHERE ([Answer Dimension].[Q and A].[Question].&[What color is it?].&[Blue])
* What I can't figure out is how to get Measures.[Answer Count] for multiple simultaneous questions, i.e.:
"For how many products and calls are &[What color is it?].&[Blue] AND &[What shape is it?].&[Round]"
I've tried (unsuccessfully) the following:
1) Various ways that equate to doing an intersection between the first and the second question. This fails since it returns the empty set - a single fact Measure.[Answer Count] only correspond to a single question.
2) Creating sets at the [Answer Dimension] leaf level where [Answer Count] = 1, and counting the number of tuples in the set. Although this looked promising it still failed me, the dimensionality either didn't allow combining the two questions or didn't slice the facts at all when say using a Filter() to combine the sets, even when using two different attribute to specify the answers.
3) Aggregating [Answer Count] to the set {[Call] * [Product]}, and Filter() where both questions have [Answer Count] >= 1. Again promising, but can't figure out the syntax to use.
Big Questions
* Is 2) or 3) above doable at all? What is the rough syntax needed?
* Is there a better (working!-) way to query this cube?
* Is there a better way to design the cube for answering these types of combined questions (remembering we have thousands of distinct questions and answers, and new ones get added over time, and a total of millions of facts)?
Any and all suggestions Much Appreciated!
Kristian
Very complex problem. My question is simply if you have tried data mining and a decision tree model on this problem? Is DM not an option?
Regards
Thomas Ivarsson
|||Assuming that the question: "For how many products and calls are &[What color is it?].&[Blue] AND &[What shape is it?].&[Round]" refers to counting product/call combinations, cascading NonEmpty() might compute what you're looking for, like:
Count(NonEmpty(NonEmpty({[Call].[Call].[Call] * [Product].[Product].[Product]},
{[Answer Dimension].[Q and A].[Question].&[What color is it?].&[Blue]}),
{[Answer Dimension].[Q and A].[Question].&[What shape is it?].&[Round]}))
|||Yes! This gives the right total on my mini test cube. One (hopefully simple) follow-up question:The calculation now happens at the right
[Call].[Call].[Call] * [Product].[Product].[Product]
level. How do I write the query so that I get the Count in a colum, and the list of Products on the rows? I.e.
Occurrences
Car 2
Bowl 1
For instance, this won't work:
WITH
SET MySet AS
[... Deepak's code from above ...]
MEMBER Measures.Occurrences AS
Count(MySet)
SELECT
[Product].[Product].[Product] ON 0
FROM cube
since it will give all Products the same total. Ideas?
Many thanks!
Kristian
|||
Hi Kristian,
To your query above, maybe you can add "Existing", to select only relevant tuples for each cell context:
MEMBER Measures.Occurrences AS
Count(Existing MySet)
When using large sets, the SSAS service crashes when running the above query. Any ideas on how to make the above query not crash the service, either through increasing limits on the SSAS instance or optimizing the query itself, any ideas?
Kristian|||
Kristian, unfortunately I haven't done much tweaking of memory settings for AS 2005 - maybe someone else can comment on this?
One alternative, which may save memory but be much slower, is to avoid creating the cross-joined named set. So, the Occurrences calculated measure could be directly defined as:
Count(NonEmpty(NonEmpty({(Existing [Call].[Call].[Call]) * (Existing [Product].[Product].[Product])},
{[Answer Dimension].[Q and A].[Question].&[What color is it?].&[Blue]}),
{[Answer Dimension].[Q and A].[Question].&[What shape is it?].&[Round]}))
sql
Subscribe to:
Posts (Atom)