Hi All,
Here I have a view like this:
create view vw_Lcustkeycode as
select *, convert(bit,0) as IsArchived from dbo.Lcustkeycode
union all
select *, convert(bit,1) as IsArchived from
DataEntryArchive.dbo.aLcustkeycode
I created an INSTEAD OF UPDATE trigger like:
CREATE TRIGGER tr_update_2cols on vw_Lcustkeycode INSTEAD OF UPDATE
AS
BEGIN
if update(UserName) and update(DateModified) begin
update d
set d.UserName=i.UserName, d.DateModified=i.DateModified
from dbo.Lcustkeycode d
inner join inserted i on d.LCustKeycode_id=i.LCustKeycode_id
where i.IsArchived=0
update d
set d.UserName=i.UserName, d.DateModified=i.DateModified
from DataEntryArchive.dbo.aLcustkeycode d
inner join inserted i on d.LCustKeycode_id=i.LCustKeycode_id
where i.IsArchived=1
end
END
When I run following update in Query Analyzer (notice the lcustkeycode_id is
primary key so only 1 row should be affected):
update vw_LCustKeycode
set username='jamma', datemodified=getdate()
where lcustkeycode_id=111060167
It said:
(2 row(s) affected)
(1 row(s) affected)
(8 row(s) affected)
(0 row(s) affected)
(8 row(s) affected)
I check the data and they are correct and really only 1 row was updated, but
why it said so many rows were affected? I find there are no other trigers
sitting there except my instead of trigger.
Can anyone here explain this strange behaviour?
Thanks,
JamesJames Ma wrote:
> Hi All,
> Here I have a view like this:
> create view vw_Lcustkeycode as
> select *, convert(bit,0) as IsArchived from dbo.Lcustkeycode
> union all
> select *, convert(bit,1) as IsArchived from
> DataEntryArchive.dbo.aLcustkeycode
> I created an INSTEAD OF UPDATE trigger like:
> CREATE TRIGGER tr_update_2cols on vw_Lcustkeycode INSTEAD OF UPDATE
> AS
> BEGIN
> if update(UserName) and update(DateModified) begin
> update d
> set d.UserName=i.UserName, d.DateModified=i.DateModified
> from dbo.Lcustkeycode d
> inner join inserted i on d.LCustKeycode_id=i.LCustKeycode_id
> where i.IsArchived=0
> update d
> set d.UserName=i.UserName, d.DateModified=i.DateModified
> from DataEntryArchive.dbo.aLcustkeycode d
> inner join inserted i on d.LCustKeycode_id=i.LCustKeycode_id
> where i.IsArchived=1
> end
> END
> When I run following update in Query Analyzer (notice the
> lcustkeycode_id is primary key so only 1 row should be affected):
> update vw_LCustKeycode
> set username='jamma', datemodified=getdate()
> where lcustkeycode_id=111060167
> It said:
> (2 row(s) affected)
> (1 row(s) affected)
> (8 row(s) affected)
> (0 row(s) affected)
> (8 row(s) affected)
> I check the data and they are correct and really only 1 row was
> updated, but why it said so many rows were affected? I find there are
> no other trigers sitting there except my instead of trigger.
> Can anyone here explain this strange behaviour?
> Thanks,
> James
Look in Profiler and see what it's doing (look at SP:StmtCompleted
events in addition to SQL:StmtCompleted).
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Thanks for your quick reply. Just now I closed Query Analyzer and entered it
again, then the results become:
(1 row(s) affected)
(0 row(s) affected)
(1 row(s) affected)
Seems fine now. I can't explain what happened just now. Even when I set
nocount off, it retuned.
(2 row(s) affected)
(8 row(s) affected)
But now everything is fine.
"David Gugick" wrote:
> James Ma wrote:
> Look in Profiler and see what it's doing (look at SP:StmtCompleted
> events in addition to SQL:StmtCompleted).
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||Hello, James
The extra "n row(s) affected" are probably a side effect of the "Show
execution plan" in Query Analyzer. Retry your query with this option on
and off to see if that's the problem.
Razvan
Sunday, February 26, 2012
Fun (trouble) with outer join
Okay, I'm having an issue with an outer join that is just confounding
me! Can somebody help explain. I started out with a large query that
was not returning all of the data that I wanted, so i paired it down to
this simple outer join:
SELECT E.EMP_RNG_CDE
FROM DATA_TABLE B LEFT OUTER JOIN
CODE_TABLE E ON E.EMP_RNG_CDE =B.EMP_RNG_CDE
WHERE B.POST_CDE = '60503'
AND B.SEG_CDE = '5'
There are 6 rows in the in "CODE_TABLE" .. while only 2 rows in
"DATA_TABLE" will match.. so I want all 6 out of the "CODE_TABLE" every
time (hence my OUTER JOIN...)
my results look like this however:
EMP_RNG_CDE
1
2
-- I only received the rows that matched from B.. not the outer rows
found in E!
HOWEVER now this drives me really crazy.. if re-work the query to join
my tables in the WHERE Clause and use the "*=" syntax for my left outer
join.. it works just right!
SELECT E.EMP_RNG_CDE
FROM DATA_TABLE B, LEFT OUTER JOIN
CODE_TABLE E
WHERE E.EMP_RNG_CDE *= B.EMP_RNG_CDE AND
B.POST_CDE = '60503'
AND B.SEG_CDE = '5'
Returns:
EMP_RNG_CDE
1
2
3
4
5
6
? Aren't those 2 queries the same? Just with different syntax for the
LEFT OUTER JOIN '
Can somebody help me with why those two are different? And how I can
get the results from the 2nd query with a "OUTER JOIN" syntax in my
FROM clause?
thansks!
jeffprizJeff,
Try switching the order of the tables in the join clause or try a RIGHT
JOIN.
HTH
Jerry
<jeffpriz@.yahoo.com> wrote in message
news:1128372693.433884.78940@.g14g2000cwa.googlegroups.com...
> Okay, I'm having an issue with an outer join that is just confounding
> me! Can somebody help explain. I started out with a large query that
> was not returning all of the data that I wanted, so i paired it down to
> this simple outer join:
> SELECT E.EMP_RNG_CDE
> FROM DATA_TABLE B LEFT OUTER JOIN
> CODE_TABLE E ON E.EMP_RNG_CDE =B.EMP_RNG_CDE
> WHERE B.POST_CDE = '60503'
> AND B.SEG_CDE = '5'
> There are 6 rows in the in "CODE_TABLE" .. while only 2 rows in
> "DATA_TABLE" will match.. so I want all 6 out of the "CODE_TABLE" every
> time (hence my OUTER JOIN...)
> my results look like this however:
> EMP_RNG_CDE
> 1
> 2
> -- I only received the rows that matched from B.. not the outer rows
> found in E!
> HOWEVER now this drives me really crazy.. if re-work the query to join
> my tables in the WHERE Clause and use the "*=" syntax for my left outer
> join.. it works just right!
> SELECT E.EMP_RNG_CDE
> FROM DATA_TABLE B, LEFT OUTER JOIN
> CODE_TABLE E
> WHERE E.EMP_RNG_CDE *= B.EMP_RNG_CDE AND
> B.POST_CDE = '60503'
> AND B.SEG_CDE = '5'
> Returns:
> EMP_RNG_CDE
> 1
> 2
> 3
> 4
> 5
> 6
> ? Aren't those 2 queries the same? Just with different syntax for the
> LEFT OUTER JOIN '
> Can somebody help me with why those two are different? And how I can
> get the results from the 2nd query with a "OUTER JOIN" syntax in my
> FROM clause?
> thansks!
> jeffpriz
>|||The preserved table is on the LEFT or the RIGHT side of the infixed
operator.
Here is how OUTER JOINs work in SQL-92. Assume you are given:
Table1 Table2
a b a c
====== ======
1 w 1 r
2 x 2 s
3 y 3 t
4 z
and the outer join expression:
Table1
LEFT OUTER JOIN
Table2
ON Table1.a = Table2.a <== join condition
AND Table2.c = 't'; <== single table condition
We call Table1 the "preserved table" and Table2 the "unpreserved table"
in the query. What I am going to give you is a little different, but
equivalent to the ANSI/ISO standards.
1) We build the CROSS JOIN of the two tables. Scan each row in the
result set.
2) If the predicate tests TRUE for that row, then you keep it. You also
remove all rows derived from it from the CROSS JOIN
3) If the predicate tests FALSE or UNKNOWN for that row, then keep the
columns from the preserved table, convert all the columns from the
unpreserved table to NULLs and remove the duplicates.
So let us execute this by hand:
Let @. = passed the first predicate
Let * = passed the second predicate
Table1 CROSS JOIN Table2
a b a c
=========================
1 w 1 r @.
1 w 2 s
1 w 3 t *
2 x 1 r
2 x 2 s @.
2 x 3 t *
3 y 1 r
3 y 2 s
3 y 3 t @.* <== the TRUE set
4 z 1 r
4 z 2 s
4 z 3 t *
Table1 LEFT OUTER JOIN Table2
a b a c
=========================
3 y 3 t <= only TRUE row
--
1 w NULL NULL Sets of duplicates
1 w NULL NULL
1 w NULL NULL
--
2 x NULL NULL
2 x NULL NULL
2 x NULL NULL
3 y NULL NULL <== derived from the TRUE set - Remove
3 y NULL NULL
--
4 z NULL NULL
4 z NULL NULL
4 z NULL NULL
the final results:
Table1 LEFT OUTER JOIN Table2
a b a c
=========================
1 w NULL NULL
2 x NULL NULL
3 y 3 t
4 z NULL NULL
The basic rule is that every row in the preserved table is represented
in the results in at least one result row.
There are limitations and very serious problems with the extended
equality version of an outer join used in some diseased mutant
products. Consider the two Chris Date tables
Suppliers SupParts
supno supno partno qty
========= ==============
S1 S1 P1 100
S2 S1 P2 250
S3 S2 P1 100
S2 P2 250
and let's do an extended equality outer join like this:
SELECT *
FROM Supplier, SupParts
WHERE Supplier.supno *= SupParts.supno
AND qty < 200;
If I do the outer first, I get:
Suppliers LOJ SupParts
supno supno partno qty
=======================
S1 S1 P1 100
S1 S1 P2 250
S2 S2 P1 100
S2 S2 P2 250
S3 NULL NULL NULL
Then I apply the (qty < 200) predicate and get
Suppliers LOJ SupParts
supno supno partno qty
===================
S1 S1 P1 100
S2 S2 P1 100
Doing it in the opposite order
Suppliers LOJ SupParts
supno supno partno qty
===================
S1 S1 P1 100
S2 S2 P1 100
S3 NULL NULL NULL
Sybase does it one way, Oracle does it the other and Centura (nee
Gupta) lets you pick which one -- the worst of both non-standard
worlds! In SQL-92, you have a choice and can force the order of
execution. Either do the predicates after the join ...
SELECT *
FROM Supplier
LEFT OUTER JOIN
SupParts
ON Supplier.supno = SupParts.supno
WHERE qty < 200;
.. or do it in the joining:
SELECT *
FROM Supplier
LEFT OUTER JOIN
SupParts
ON Supplier.supno = SupParts.supno
AND qty < 200;
Another problem is that you cannot show the same table as preserved and
unpreserved in the extended equality version, but it is easy in SQL-92.
For example to find the students who have taken Math 101 and might
have taken Math 102:
SELECT C1.student, C1.math, C2.math
FROM (SELECT * FROM Courses WHERE math = 101) AS C1
LEFT OUTER JOIN
(SELECT * FROM Courses WHERE math = 102) AS C2
ON C1.student = C2.student;|||The WHERE clause causes your outer join to "become" an inner join. Not
explicitly, but in effect. (Put as simply as possible.)
Change your where clause like this or - as Jerry suggests - use RIGHT outer
join:
WHERE (B.POST_CDE = '60503' or B.POST_CDE is null)
AND (B.SEG_CDE = '5' or B.SEG_CDE is null)
ML|||Thanks! That actually helped a good bit! (it took me 5 reads, but I got
there!) LOL
I see where it's going. And i got my simple query to work, and I was
able to go back to my full-blown query to get it to work too
correctly..
thanks
me! Can somebody help explain. I started out with a large query that
was not returning all of the data that I wanted, so i paired it down to
this simple outer join:
SELECT E.EMP_RNG_CDE
FROM DATA_TABLE B LEFT OUTER JOIN
CODE_TABLE E ON E.EMP_RNG_CDE =B.EMP_RNG_CDE
WHERE B.POST_CDE = '60503'
AND B.SEG_CDE = '5'
There are 6 rows in the in "CODE_TABLE" .. while only 2 rows in
"DATA_TABLE" will match.. so I want all 6 out of the "CODE_TABLE" every
time (hence my OUTER JOIN...)
my results look like this however:
EMP_RNG_CDE
1
2
-- I only received the rows that matched from B.. not the outer rows
found in E!
HOWEVER now this drives me really crazy.. if re-work the query to join
my tables in the WHERE Clause and use the "*=" syntax for my left outer
join.. it works just right!
SELECT E.EMP_RNG_CDE
FROM DATA_TABLE B, LEFT OUTER JOIN
CODE_TABLE E
WHERE E.EMP_RNG_CDE *= B.EMP_RNG_CDE AND
B.POST_CDE = '60503'
AND B.SEG_CDE = '5'
Returns:
EMP_RNG_CDE
1
2
3
4
5
6
? Aren't those 2 queries the same? Just with different syntax for the
LEFT OUTER JOIN '
Can somebody help me with why those two are different? And how I can
get the results from the 2nd query with a "OUTER JOIN" syntax in my
FROM clause?
thansks!
jeffprizJeff,
Try switching the order of the tables in the join clause or try a RIGHT
JOIN.
HTH
Jerry
<jeffpriz@.yahoo.com> wrote in message
news:1128372693.433884.78940@.g14g2000cwa.googlegroups.com...
> Okay, I'm having an issue with an outer join that is just confounding
> me! Can somebody help explain. I started out with a large query that
> was not returning all of the data that I wanted, so i paired it down to
> this simple outer join:
> SELECT E.EMP_RNG_CDE
> FROM DATA_TABLE B LEFT OUTER JOIN
> CODE_TABLE E ON E.EMP_RNG_CDE =B.EMP_RNG_CDE
> WHERE B.POST_CDE = '60503'
> AND B.SEG_CDE = '5'
> There are 6 rows in the in "CODE_TABLE" .. while only 2 rows in
> "DATA_TABLE" will match.. so I want all 6 out of the "CODE_TABLE" every
> time (hence my OUTER JOIN...)
> my results look like this however:
> EMP_RNG_CDE
> 1
> 2
> -- I only received the rows that matched from B.. not the outer rows
> found in E!
> HOWEVER now this drives me really crazy.. if re-work the query to join
> my tables in the WHERE Clause and use the "*=" syntax for my left outer
> join.. it works just right!
> SELECT E.EMP_RNG_CDE
> FROM DATA_TABLE B, LEFT OUTER JOIN
> CODE_TABLE E
> WHERE E.EMP_RNG_CDE *= B.EMP_RNG_CDE AND
> B.POST_CDE = '60503'
> AND B.SEG_CDE = '5'
> Returns:
> EMP_RNG_CDE
> 1
> 2
> 3
> 4
> 5
> 6
> ? Aren't those 2 queries the same? Just with different syntax for the
> LEFT OUTER JOIN '
> Can somebody help me with why those two are different? And how I can
> get the results from the 2nd query with a "OUTER JOIN" syntax in my
> FROM clause?
> thansks!
> jeffpriz
>|||The preserved table is on the LEFT or the RIGHT side of the infixed
operator.
Here is how OUTER JOINs work in SQL-92. Assume you are given:
Table1 Table2
a b a c
====== ======
1 w 1 r
2 x 2 s
3 y 3 t
4 z
and the outer join expression:
Table1
LEFT OUTER JOIN
Table2
ON Table1.a = Table2.a <== join condition
AND Table2.c = 't'; <== single table condition
We call Table1 the "preserved table" and Table2 the "unpreserved table"
in the query. What I am going to give you is a little different, but
equivalent to the ANSI/ISO standards.
1) We build the CROSS JOIN of the two tables. Scan each row in the
result set.
2) If the predicate tests TRUE for that row, then you keep it. You also
remove all rows derived from it from the CROSS JOIN
3) If the predicate tests FALSE or UNKNOWN for that row, then keep the
columns from the preserved table, convert all the columns from the
unpreserved table to NULLs and remove the duplicates.
So let us execute this by hand:
Let @. = passed the first predicate
Let * = passed the second predicate
Table1 CROSS JOIN Table2
a b a c
=========================
1 w 1 r @.
1 w 2 s
1 w 3 t *
2 x 1 r
2 x 2 s @.
2 x 3 t *
3 y 1 r
3 y 2 s
3 y 3 t @.* <== the TRUE set
4 z 1 r
4 z 2 s
4 z 3 t *
Table1 LEFT OUTER JOIN Table2
a b a c
=========================
3 y 3 t <= only TRUE row
--
1 w NULL NULL Sets of duplicates
1 w NULL NULL
1 w NULL NULL
--
2 x NULL NULL
2 x NULL NULL
2 x NULL NULL
3 y NULL NULL <== derived from the TRUE set - Remove
3 y NULL NULL
--
4 z NULL NULL
4 z NULL NULL
4 z NULL NULL
the final results:
Table1 LEFT OUTER JOIN Table2
a b a c
=========================
1 w NULL NULL
2 x NULL NULL
3 y 3 t
4 z NULL NULL
The basic rule is that every row in the preserved table is represented
in the results in at least one result row.
There are limitations and very serious problems with the extended
equality version of an outer join used in some diseased mutant
products. Consider the two Chris Date tables
Suppliers SupParts
supno supno partno qty
========= ==============
S1 S1 P1 100
S2 S1 P2 250
S3 S2 P1 100
S2 P2 250
and let's do an extended equality outer join like this:
SELECT *
FROM Supplier, SupParts
WHERE Supplier.supno *= SupParts.supno
AND qty < 200;
If I do the outer first, I get:
Suppliers LOJ SupParts
supno supno partno qty
=======================
S1 S1 P1 100
S1 S1 P2 250
S2 S2 P1 100
S2 S2 P2 250
S3 NULL NULL NULL
Then I apply the (qty < 200) predicate and get
Suppliers LOJ SupParts
supno supno partno qty
===================
S1 S1 P1 100
S2 S2 P1 100
Doing it in the opposite order
Suppliers LOJ SupParts
supno supno partno qty
===================
S1 S1 P1 100
S2 S2 P1 100
S3 NULL NULL NULL
Sybase does it one way, Oracle does it the other and Centura (nee
Gupta) lets you pick which one -- the worst of both non-standard
worlds! In SQL-92, you have a choice and can force the order of
execution. Either do the predicates after the join ...
SELECT *
FROM Supplier
LEFT OUTER JOIN
SupParts
ON Supplier.supno = SupParts.supno
WHERE qty < 200;
.. or do it in the joining:
SELECT *
FROM Supplier
LEFT OUTER JOIN
SupParts
ON Supplier.supno = SupParts.supno
AND qty < 200;
Another problem is that you cannot show the same table as preserved and
unpreserved in the extended equality version, but it is easy in SQL-92.
For example to find the students who have taken Math 101 and might
have taken Math 102:
SELECT C1.student, C1.math, C2.math
FROM (SELECT * FROM Courses WHERE math = 101) AS C1
LEFT OUTER JOIN
(SELECT * FROM Courses WHERE math = 102) AS C2
ON C1.student = C2.student;|||The WHERE clause causes your outer join to "become" an inner join. Not
explicitly, but in effect. (Put as simply as possible.)
Change your where clause like this or - as Jerry suggests - use RIGHT outer
join:
WHERE (B.POST_CDE = '60503' or B.POST_CDE is null)
AND (B.SEG_CDE = '5' or B.SEG_CDE is null)
ML|||Thanks! That actually helped a good bit! (it took me 5 reads, but I got
there!) LOL
I see where it's going. And i got my simple query to work, and I was
able to go back to my full-blown query to get it to work too
correctly..
thanks
Fumo?
Fumo 10 euro 0,8 grammi
Maria 10 euro 0,8 grammi
Cocaine 100 euro 1 g> Fumo 10 euro 0,8 grammi
> Maria 10 euro 0,8 grammi
> Cocaine 100 euro 1 g
This must be for those 30-hour debugging sessions. :)
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave Mustane|||LOL!
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message news:%23OCbdED8FHA.1140@.tk2msftngp13.p
hx.gbl...
> This must be for those 30-hour debugging sessions. :)
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "When you kill a man, you're a murderer.
> Kill many, and you're a conqueror.
> Kill them all and you're a god." -- Dave Mustane
>|||Mike,
After your accident, didn't you get a prescription for medical "Maria"?
::-)
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:%23OCbdED8FHA.1140@.tk2msftngp13.phx.gbl...
> This must be for those 30-hour debugging sessions. :)
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "When you kill a man, you're a murderer.
> Kill many, and you're a conqueror.
> Kill them all and you're a god." -- Dave Mustane
>|||> After your accident, didn't you get a prescription for medical "Maria"?
No way.
In fact, the "smoking nazi's" were so hardcore, that when I was in the
hospital for a WHOLE WEEK they wouldn't let me go outside to smoke. So as I
was getting more and more fidgety and irritable because all I wanted was a
@.#$% smoke -- their solution was to send a PSYCHIATRIST to my room to try to
figure out why I was so upset.
He came into the room with all that pedantic emotional psycho babble and I
was like, "DUDE, this is really quite simple: I just want a friggen
cigarette!"
--
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave Mustane|||Sure, you told them that all you wanted was a smoke, but the psychiatrists
knew that, on a deeper sub-conscious level, you really just want to
'possess' your mother. I'm sure they were a big help in resolving your
frustration. ;-)
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:%2351s%23cD8FHA.1020@.TK2MSFTNGP15.phx.gbl...
> No way.
> In fact, the "smoking nazi's" were so hardcore, that when I was in the
> hospital for a WHOLE WEEK they wouldn't let me go outside to smoke. So as
> I was getting more and more fidgety and irritable because all I wanted was
> a @.#$% smoke -- their solution was to send a PSYCHIATRIST to my room to
> try to figure out why I was so upset.
> He came into the room with all that pedantic emotional psycho babble and I
> was like, "DUDE, this is really quite simple: I just want a friggen
> cigarette!"
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "When you kill a man, you're a murderer.
> Kill many, and you're a conqueror.
> Kill them all and you're a god." -- Dave Mustane
>
Maria 10 euro 0,8 grammi
Cocaine 100 euro 1 g> Fumo 10 euro 0,8 grammi
> Maria 10 euro 0,8 grammi
> Cocaine 100 euro 1 g
This must be for those 30-hour debugging sessions. :)
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave Mustane|||LOL!
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message news:%23OCbdED8FHA.1140@.tk2msftngp13.p
hx.gbl...
> This must be for those 30-hour debugging sessions. :)
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "When you kill a man, you're a murderer.
> Kill many, and you're a conqueror.
> Kill them all and you're a god." -- Dave Mustane
>|||Mike,
After your accident, didn't you get a prescription for medical "Maria"?
::-)
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:%23OCbdED8FHA.1140@.tk2msftngp13.phx.gbl...
> This must be for those 30-hour debugging sessions. :)
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "When you kill a man, you're a murderer.
> Kill many, and you're a conqueror.
> Kill them all and you're a god." -- Dave Mustane
>|||> After your accident, didn't you get a prescription for medical "Maria"?
No way.
In fact, the "smoking nazi's" were so hardcore, that when I was in the
hospital for a WHOLE WEEK they wouldn't let me go outside to smoke. So as I
was getting more and more fidgety and irritable because all I wanted was a
@.#$% smoke -- their solution was to send a PSYCHIATRIST to my room to try to
figure out why I was so upset.
He came into the room with all that pedantic emotional psycho babble and I
was like, "DUDE, this is really quite simple: I just want a friggen
cigarette!"
--
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave Mustane|||Sure, you told them that all you wanted was a smoke, but the psychiatrists
knew that, on a deeper sub-conscious level, you really just want to
'possess' your mother. I'm sure they were a big help in resolving your
frustration. ;-)
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:%2351s%23cD8FHA.1020@.TK2MSFTNGP15.phx.gbl...
> No way.
> In fact, the "smoking nazi's" were so hardcore, that when I was in the
> hospital for a WHOLE WEEK they wouldn't let me go outside to smoke. So as
> I was getting more and more fidgety and irritable because all I wanted was
> a @.#$% smoke -- their solution was to send a PSYCHIATRIST to my room to
> try to figure out why I was so upset.
> He came into the room with all that pedantic emotional psycho babble and I
> was like, "DUDE, this is really quite simple: I just want a friggen
> cigarette!"
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "When you kill a man, you're a murderer.
> Kill many, and you're a conqueror.
> Kill them all and you're a god." -- Dave Mustane
>
Fully Qualified Query Accross Databases
Are there any perfmonace or query optimization limitations or issues that arise when issueing a fully qualified query across multiple databases on the same Instance of SQL Server. In other words are all features of query optimization fully supported in queries that span databases on the same Instance.
The only limitations that I'm aware of are the same limitations if the queries only run against one database, i.e. the databases have to be properly optimized for performance. If one database is properly indexed and one is not the bottleneck will be the database that isn't properly indexed.|||
That is a fact, but I am really trying to determine if there are any optimizer limitations or nuances like there are if you are using partitioning.
|||The fact that there may be partitioning really isn't relevant since partitioning is only undertaken to optimize query performance anyway by distributing data across multiple files. As I mentioned, if the databases are optimized, whether through the use of partitioning or any other strategy, then cross database queries aren't an issue. There aren't any optimizer limitations or nuances that need to be considered.If you were performing distributed queries across multiple linked servers or across multiple resource engines, i.e. SQL Server/Oracle then there might be some things to consider but even then, the issue would probably still come down to whether the databases are properly optimized.
Fully qualified object names help reuse of execution plans.
Fully qualified object names help reuse of execution plans.
Is this true? Discuss.
Also, what constitutes fully qualified?
Is it
server.database.owner.object
or can you get away with
database.owner.object
and still resuse your execution plans?
ThanksYou should read this KB. Even though it is for 2005 most of the same
principles still apply.
http://www.microsoft.com/technet/pr...005/recomp.mspx
As for your question of what does fully mean well that depends. Mostly it
means you should always specify the object owner along with the object.
dbo.yoursp or dbo.yourtable etc.
If you are accessing an object from within the same db then just specify the
owner and the object. Do not specify the database as it is no necessary and
actually invokes a few more lines of code than necessary. If you need to
specify an object in another db on the same server than you must specify
that as well.
OtherDB.dbo.Object
Andrew J. Kelly SQL MVP
"Damien" <Damien@.discussions.microsoft.com> wrote in message
news:2BBC6EA6-2E85-4643-8E07-16E031D8E8A9@.microsoft.com...
> Fully qualified object names help reuse of execution plans.
> Is this true? Discuss.
> Also, what constitutes fully qualified?
> Is it
> server.database.owner.object
> or can you get away with
> database.owner.object
> and still resuse your execution plans?
> Thanks
>|||Hi
http://msdn.microsoft.com/library/d...br />
4azp.asp
If you want more information, get yourself "Inside SQL Server 2000" by Kalen
Delaney.
owner.object is good enough for re-use.
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Damien" <Damien@.discussions.microsoft.com> wrote in message
news:2BBC6EA6-2E85-4643-8E07-16E031D8E8A9@.microsoft.com...
> Fully qualified object names help reuse of execution plans.
> Is this true? Discuss.
> Also, what constitutes fully qualified?
> Is it
> server.database..object
> or can you get away with
> database.owner.object
> and still resuse your execution plans?
> Thanks
>
Is this true? Discuss.
Also, what constitutes fully qualified?
Is it
server.database.owner.object
or can you get away with
database.owner.object
and still resuse your execution plans?
ThanksYou should read this KB. Even though it is for 2005 most of the same
principles still apply.
http://www.microsoft.com/technet/pr...005/recomp.mspx
As for your question of what does fully mean well that depends. Mostly it
means you should always specify the object owner along with the object.
dbo.yoursp or dbo.yourtable etc.
If you are accessing an object from within the same db then just specify the
owner and the object. Do not specify the database as it is no necessary and
actually invokes a few more lines of code than necessary. If you need to
specify an object in another db on the same server than you must specify
that as well.
OtherDB.dbo.Object
Andrew J. Kelly SQL MVP
"Damien" <Damien@.discussions.microsoft.com> wrote in message
news:2BBC6EA6-2E85-4643-8E07-16E031D8E8A9@.microsoft.com...
> Fully qualified object names help reuse of execution plans.
> Is this true? Discuss.
> Also, what constitutes fully qualified?
> Is it
> server.database.owner.object
> or can you get away with
> database.owner.object
> and still resuse your execution plans?
> Thanks
>|||Hi
http://msdn.microsoft.com/library/d...br />
4azp.asp
If you want more information, get yourself "Inside SQL Server 2000" by Kalen
Delaney.
owner.object is good enough for re-use.
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Damien" <Damien@.discussions.microsoft.com> wrote in message
news:2BBC6EA6-2E85-4643-8E07-16E031D8E8A9@.microsoft.com...
> Fully qualified object names help reuse of execution plans.
> Is this true? Discuss.
> Also, what constitutes fully qualified?
> Is it
> server.database..object
> or can you get away with
> database.owner.object
> and still resuse your execution plans?
> Thanks
>
Fully Qualified names?
Hi all
A collegeue recently found an item on
http://www.sql-server-performance.c..._procedures.asp (do a search on
the page to "fully qualified name")
In theory this does make sense, and I can understand what the item is
getting at. I work with a team of 20 developers, and we had not implemented
that item.
I decided that before telling everyone to change the thousands of stored
procedures to use this convention, I wanted to be sure that it was worth it.
Anyway, I did a test using SSMS and it didn't seem to matter if I used just
the procedure name, a two part name or a three part name.
The article was written a while ago, so it must be directed at SQL2000. I'd
like to perform a better test to prove this item one way or another.
Can anyone suggest a fair, method of testing this claim and being able to
properly and fairly mesure the difference?
Regards
Colin Dawson
www.cjdawson.comHi, Colin
> Anyway, I did a test using SSMS and it didn't seem to matter if I used
> just the procedure name, a two part name or a three part name.
Well, I think if you specify fully qualified name it will help to SQL
Server to "find" the object more quickly , I mean not to run thro all
objects that have DBO. or all default schema , so it is just my point
of view
"Colin Dawson" <newsgroups@.cjdawson.com> wrote in message
news:Udh8g.67139$wl.47904@.text.news.blueyonder.co.uk...
> Hi all
> A collegeue recently found an item on
> http://www.sql-server-performance.c..._procedures.asp (do a search
> on the page to "fully qualified name")
> In theory this does make sense, and I can understand what the item is
> getting at. I work with a team of 20 developers, and we had not
> implemented that item.
> I decided that before telling everyone to change the thousands of stored
> procedures to use this convention, I wanted to be sure that it was worth
> it. Anyway, I did a test using SSMS and it didn't seem to matter if I used
> just the procedure name, a two part name or a three part name.
> The article was written a while ago, so it must be directed at SQL2000.
> I'd like to perform a better test to prove this item one way or another.
> Can anyone suggest a fair, method of testing this claim and being able to
> properly and fairly mesure the difference?
> Regards
> Colin Dawson
> www.cjdawson.com
>|||On Wed, 10 May 2006 08:10:28 GMT, Colin Dawson wrote:
>Hi all
>A collegeue recently found an item on
>http://www.sql-server-performance.c..._procedures.asp (do a search o
n
>the page to "fully qualified name")
>In theory this does make sense, and I can understand what the item is
>getting at. I work with a team of 20 developers, and we had not implemente
d
>that item.
>I decided that before telling everyone to change the thousands of stored
>procedures to use this convention, I wanted to be sure that it was worth it
.
>Anyway, I did a test using SSMS and it didn't seem to matter if I used just
>the procedure name, a two part name or a three part name.
>The article was written a while ago, so it must be directed at SQL2000. I'
d
>like to perform a better test to prove this item one way or another.
>Can anyone suggest a fair, method of testing this claim and being able to
>properly and fairly mesure the difference?
Hi Colin,
Contrary to what this article says, there's no need to database-qualify
any objects (unless they are in a different database of course).
Owner qualifying is important, though, for the following two reasons:
1. Reduce chance of unexpected bugs. (Example: EXEC MyProc suddenly
throws errors when executed by Alice, becuase she created a procedure
Allice.MyProc yesterday)
2. Performance gain. (EXEC MyProc for Alice takes two lookups: first for
Alice.MyProc, then [if the former doesn't exist] for dbo.MyProc).
The performance gain is real, but in most cases not big enough to
warrant the cost of changing thousands of existing stored procedures.
Just make it your policy to demand that all table, view, and procedure
names shoould be owner-qualified in new code, and that the owner should
be added when changing any existing code (exempting bugfixes and small
changes under heavy time pressurre).
Hugo Kornelis, SQL Server MVP|||Colin Dawson (newsgroups@.cjdawson.com) writes:
> A collegeue recently found an item on
> http://www.sql-server-performance.c..._procedures.asp (do a
> search on the page to "fully qualified name") In theory this does make
> sense, and I can understand what the item is getting at. I work with a
> team of 20 developers, and we had not implemented that item.
> I decided that before telling everyone to change the thousands of stored
> procedures to use this convention, I wanted to be sure that it was worth
> it. Anyway, I did a test using SSMS and it didn't seem to matter if I
> used just the procedure name, a two part name or a three part name.
Including the database name is meaningless, and only increases the
complexity of the programming. (Since you need to get to get the database
from somewhere, and hard-coding it seems like a bad idea.)
Including schema/owner on the other hand matters.
Let's first look at this on SQL 2000. On SQL 2000, each user has a default
schema with the same name as the user name. Thus, if fred says
EXEC some_sp
(or make this call through RPC), SQL Server will first lookup "some_sp" in
the cache, including the uid. If there is a fred.some_sp, there will be a
cache hit, else a cache miss. Later SQL Server will look up some_sp in
metadata, and when fred.some_sp is missing, it will try dbo.some_sp, which
it will find in the cache.
On the other hand, if fred says:
EXEC dbo.some_sp
he will get a cache hit directly.
On SQL 2005, this is somewhat different, as a user can have dbo as the
default schema. In this case, at least in theory, it should not matter
whether you specify dho or not.
Now, how to set the default schema of a user? Well, if you use the new
command, CREATE USER, the user's default schema will be dbo, and there will
be no schema with the user's name. But if you create users with sp_adduser
out of habit, you will get a schema with the user's name, and that will
be the default schema.
Since you cannot be sure that future users get not added in a proper way,
it appears to be a good recommendation to include schema on SQL 2005 as
well.
As for the question of what the impact is of not including dbo, well I don't
have any numbers, but on a busy system with few cycles to spare, I believe
all those cache misses can cause quite some aggrevation.
I should add that including owner/schema is even more important when you
work with dynamic SQL, be that sp_executesql or parameterised command from
the client (which is sp_executesql anyway). If there is a single unqualified
table in the query, the plan will be private to the user, and cannot be
used by other users. (Again, not if the user's default schema is dbo, but
do not rely on that.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks for the info. It's helping me to understand this particular nuance.
In this case, I think that I'm not going to insist on the change. The
reason of this is that the db is accessed from a middle tier. The middle
tier uses connection pooling so we need to standardise on a specific user.
This user we need to setup, to it is safe (in this instance) to rely on the
the schema being set to dbo.
I'll certainly remember this for when I'm working on and dynamic sql (of
which there is currently none in the entire app!)
If we find that the app comes even close to maxing out the processor (very
unlikley at this stage) then I'll take a look at forcing dbo. infront of all
objects.
Thanks for the enlightenment.
Regards
Colin Dawson
www.cjdawson.com
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97BF808BB6063Yazorman@.127.0.0.1...
> Colin Dawson (newsgroups@.cjdawson.com) writes:
> Including the database name is meaningless, and only increases the
> complexity of the programming. (Since you need to get to get the database
> from somewhere, and hard-coding it seems like a bad idea.)
> Including schema/owner on the other hand matters.
> Let's first look at this on SQL 2000. On SQL 2000, each user has a default
> schema with the same name as the user name. Thus, if fred says
> EXEC some_sp
> (or make this call through RPC), SQL Server will first lookup "some_sp" in
> the cache, including the uid. If there is a fred.some_sp, there will be a
> cache hit, else a cache miss. Later SQL Server will look up some_sp in
> metadata, and when fred.some_sp is missing, it will try dbo.some_sp, which
> it will find in the cache.
> On the other hand, if fred says:
> EXEC dbo.some_sp
> he will get a cache hit directly.
> On SQL 2005, this is somewhat different, as a user can have dbo as the
> default schema. In this case, at least in theory, it should not matter
> whether you specify dho or not.
> Now, how to set the default schema of a user? Well, if you use the new
> command, CREATE USER, the user's default schema will be dbo, and there
> will
> be no schema with the user's name. But if you create users with sp_adduser
> out of habit, you will get a schema with the user's name, and that will
> be the default schema.
> Since you cannot be sure that future users get not added in a proper way,
> it appears to be a good recommendation to include schema on SQL 2005 as
> well.
> As for the question of what the impact is of not including dbo, well I
> don't
> have any numbers, but on a busy system with few cycles to spare, I believe
> all those cache misses can cause quite some aggrevation.
> I should add that including owner/schema is even more important when you
> work with dynamic SQL, be that sp_executesql or parameterised command from
> the client (which is sp_executesql anyway). If there is a single
> unqualified
> table in the query, the plan will be private to the user, and cannot be
> used by other users. (Again, not if the user's default schema is dbo, but
> do not rely on that.)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Colin Dawson (newsgroups@.cjdawson.com) writes:
> In this case, I think that I'm not going to insist on the change. The
> reason of this is that the db is accessed from a middle tier. The middle
> tier uses connection pooling so we need to standardise on a specific
> user. This user we need to setup, to it is safe (in this instance) to
> rely on the the schema being set to dbo.
And you are on SQL 2005?
Else you need to make that standardised user dbo.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97C04703D6F1Yazorman@.127.0.0.1...
> Colin Dawson (newsgroups@.cjdawson.com) writes:
> And you are on SQL 2005?
> Else you need to make that standardised user dbo.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
Yes, I've been on SQL2005 for the last 12 months of so.
Regards
Colin Dawson
www.cjdawson.com
A collegeue recently found an item on
http://www.sql-server-performance.c..._procedures.asp (do a search on
the page to "fully qualified name")
In theory this does make sense, and I can understand what the item is
getting at. I work with a team of 20 developers, and we had not implemented
that item.
I decided that before telling everyone to change the thousands of stored
procedures to use this convention, I wanted to be sure that it was worth it.
Anyway, I did a test using SSMS and it didn't seem to matter if I used just
the procedure name, a two part name or a three part name.
The article was written a while ago, so it must be directed at SQL2000. I'd
like to perform a better test to prove this item one way or another.
Can anyone suggest a fair, method of testing this claim and being able to
properly and fairly mesure the difference?
Regards
Colin Dawson
www.cjdawson.comHi, Colin
> Anyway, I did a test using SSMS and it didn't seem to matter if I used
> just the procedure name, a two part name or a three part name.
Well, I think if you specify fully qualified name it will help to SQL
Server to "find" the object more quickly , I mean not to run thro all
objects that have DBO. or all default schema , so it is just my point
of view
"Colin Dawson" <newsgroups@.cjdawson.com> wrote in message
news:Udh8g.67139$wl.47904@.text.news.blueyonder.co.uk...
> Hi all
> A collegeue recently found an item on
> http://www.sql-server-performance.c..._procedures.asp (do a search
> on the page to "fully qualified name")
> In theory this does make sense, and I can understand what the item is
> getting at. I work with a team of 20 developers, and we had not
> implemented that item.
> I decided that before telling everyone to change the thousands of stored
> procedures to use this convention, I wanted to be sure that it was worth
> it. Anyway, I did a test using SSMS and it didn't seem to matter if I used
> just the procedure name, a two part name or a three part name.
> The article was written a while ago, so it must be directed at SQL2000.
> I'd like to perform a better test to prove this item one way or another.
> Can anyone suggest a fair, method of testing this claim and being able to
> properly and fairly mesure the difference?
> Regards
> Colin Dawson
> www.cjdawson.com
>|||On Wed, 10 May 2006 08:10:28 GMT, Colin Dawson wrote:
>Hi all
>A collegeue recently found an item on
>http://www.sql-server-performance.c..._procedures.asp (do a search o
n
>the page to "fully qualified name")
>In theory this does make sense, and I can understand what the item is
>getting at. I work with a team of 20 developers, and we had not implemente
d
>that item.
>I decided that before telling everyone to change the thousands of stored
>procedures to use this convention, I wanted to be sure that it was worth it
.
>Anyway, I did a test using SSMS and it didn't seem to matter if I used just
>the procedure name, a two part name or a three part name.
>The article was written a while ago, so it must be directed at SQL2000. I'
d
>like to perform a better test to prove this item one way or another.
>Can anyone suggest a fair, method of testing this claim and being able to
>properly and fairly mesure the difference?
Hi Colin,
Contrary to what this article says, there's no need to database-qualify
any objects (unless they are in a different database of course).
Owner qualifying is important, though, for the following two reasons:
1. Reduce chance of unexpected bugs. (Example: EXEC MyProc suddenly
throws errors when executed by Alice, becuase she created a procedure
Allice.MyProc yesterday)
2. Performance gain. (EXEC MyProc for Alice takes two lookups: first for
Alice.MyProc, then [if the former doesn't exist] for dbo.MyProc).
The performance gain is real, but in most cases not big enough to
warrant the cost of changing thousands of existing stored procedures.
Just make it your policy to demand that all table, view, and procedure
names shoould be owner-qualified in new code, and that the owner should
be added when changing any existing code (exempting bugfixes and small
changes under heavy time pressurre).
Hugo Kornelis, SQL Server MVP|||Colin Dawson (newsgroups@.cjdawson.com) writes:
> A collegeue recently found an item on
> http://www.sql-server-performance.c..._procedures.asp (do a
> search on the page to "fully qualified name") In theory this does make
> sense, and I can understand what the item is getting at. I work with a
> team of 20 developers, and we had not implemented that item.
> I decided that before telling everyone to change the thousands of stored
> procedures to use this convention, I wanted to be sure that it was worth
> it. Anyway, I did a test using SSMS and it didn't seem to matter if I
> used just the procedure name, a two part name or a three part name.
Including the database name is meaningless, and only increases the
complexity of the programming. (Since you need to get to get the database
from somewhere, and hard-coding it seems like a bad idea.)
Including schema/owner on the other hand matters.
Let's first look at this on SQL 2000. On SQL 2000, each user has a default
schema with the same name as the user name. Thus, if fred says
EXEC some_sp
(or make this call through RPC), SQL Server will first lookup "some_sp" in
the cache, including the uid. If there is a fred.some_sp, there will be a
cache hit, else a cache miss. Later SQL Server will look up some_sp in
metadata, and when fred.some_sp is missing, it will try dbo.some_sp, which
it will find in the cache.
On the other hand, if fred says:
EXEC dbo.some_sp
he will get a cache hit directly.
On SQL 2005, this is somewhat different, as a user can have dbo as the
default schema. In this case, at least in theory, it should not matter
whether you specify dho or not.
Now, how to set the default schema of a user? Well, if you use the new
command, CREATE USER, the user's default schema will be dbo, and there will
be no schema with the user's name. But if you create users with sp_adduser
out of habit, you will get a schema with the user's name, and that will
be the default schema.
Since you cannot be sure that future users get not added in a proper way,
it appears to be a good recommendation to include schema on SQL 2005 as
well.
As for the question of what the impact is of not including dbo, well I don't
have any numbers, but on a busy system with few cycles to spare, I believe
all those cache misses can cause quite some aggrevation.
I should add that including owner/schema is even more important when you
work with dynamic SQL, be that sp_executesql or parameterised command from
the client (which is sp_executesql anyway). If there is a single unqualified
table in the query, the plan will be private to the user, and cannot be
used by other users. (Again, not if the user's default schema is dbo, but
do not rely on that.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks for the info. It's helping me to understand this particular nuance.
In this case, I think that I'm not going to insist on the change. The
reason of this is that the db is accessed from a middle tier. The middle
tier uses connection pooling so we need to standardise on a specific user.
This user we need to setup, to it is safe (in this instance) to rely on the
the schema being set to dbo.
I'll certainly remember this for when I'm working on and dynamic sql (of
which there is currently none in the entire app!)
If we find that the app comes even close to maxing out the processor (very
unlikley at this stage) then I'll take a look at forcing dbo. infront of all
objects.
Thanks for the enlightenment.
Regards
Colin Dawson
www.cjdawson.com
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97BF808BB6063Yazorman@.127.0.0.1...
> Colin Dawson (newsgroups@.cjdawson.com) writes:
> Including the database name is meaningless, and only increases the
> complexity of the programming. (Since you need to get to get the database
> from somewhere, and hard-coding it seems like a bad idea.)
> Including schema/owner on the other hand matters.
> Let's first look at this on SQL 2000. On SQL 2000, each user has a default
> schema with the same name as the user name. Thus, if fred says
> EXEC some_sp
> (or make this call through RPC), SQL Server will first lookup "some_sp" in
> the cache, including the uid. If there is a fred.some_sp, there will be a
> cache hit, else a cache miss. Later SQL Server will look up some_sp in
> metadata, and when fred.some_sp is missing, it will try dbo.some_sp, which
> it will find in the cache.
> On the other hand, if fred says:
> EXEC dbo.some_sp
> he will get a cache hit directly.
> On SQL 2005, this is somewhat different, as a user can have dbo as the
> default schema. In this case, at least in theory, it should not matter
> whether you specify dho or not.
> Now, how to set the default schema of a user? Well, if you use the new
> command, CREATE USER, the user's default schema will be dbo, and there
> will
> be no schema with the user's name. But if you create users with sp_adduser
> out of habit, you will get a schema with the user's name, and that will
> be the default schema.
> Since you cannot be sure that future users get not added in a proper way,
> it appears to be a good recommendation to include schema on SQL 2005 as
> well.
> As for the question of what the impact is of not including dbo, well I
> don't
> have any numbers, but on a busy system with few cycles to spare, I believe
> all those cache misses can cause quite some aggrevation.
> I should add that including owner/schema is even more important when you
> work with dynamic SQL, be that sp_executesql or parameterised command from
> the client (which is sp_executesql anyway). If there is a single
> unqualified
> table in the query, the plan will be private to the user, and cannot be
> used by other users. (Again, not if the user's default schema is dbo, but
> do not rely on that.)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Colin Dawson (newsgroups@.cjdawson.com) writes:
> In this case, I think that I'm not going to insist on the change. The
> reason of this is that the db is accessed from a middle tier. The middle
> tier uses connection pooling so we need to standardise on a specific
> user. This user we need to setup, to it is safe (in this instance) to
> rely on the the schema being set to dbo.
And you are on SQL 2005?
Else you need to make that standardised user dbo.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97C04703D6F1Yazorman@.127.0.0.1...
> Colin Dawson (newsgroups@.cjdawson.com) writes:
> And you are on SQL 2005?
> Else you need to make that standardised user dbo.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
Yes, I've been on SQL2005 for the last 12 months of so.
Regards
Colin Dawson
www.cjdawson.com
fully qualified names with named instances
hallo it's unclear to me how to address a named instance using a fully
qualified name (server-name.database-name.owner-name.object-name)
In this syntax, where does the instance name fit?
thanks in advance
Raffaele,
server-name is actually linkedserver-name, which may not actually be the
name of a physical serve. Here is some code from SQL 2005 Books Online
EXEC sp_addlinkedserver
@.server='S1_instance1',
@.srvproduct='',
@.provider='SQLNCLI',
@.datasrc='S1\instance1'
You can see that the instance name is defined in the data source as the
server 'S1' and the instance '\instance1'. The server name of
'S1_instance1' reflects that name, but it could be named
'MyFavoriteLinkedServer' or anything else.
So, then: SELECT * FROM S1_instance1.mydatabase.myowner.MyTable
RLF
"Raffaele" <Raffaele@.discussions.microsoft.com> wrote in message
news:BFDA8EDF-1EC1-47E1-A71C-B13B89397DF5@.microsoft.com...
> hallo it's unclear to me how to address a named instance using a fully
> qualified name (server-name.database-name.owner-name.object-name)
> In this syntax, where does the instance name fit?
> thanks in advance
|||[Server\Instance].database.owner_or_schema.object
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Raffaele" <Raffaele@.discussions.microsoft.com> wrote in message
news:BFDA8EDF-1EC1-47E1-A71C-B13B89397DF5@.microsoft.com...
> hallo it's unclear to me how to address a named instance using a fully
> qualified name (server-name.database-name.owner-name.object-name)
> In this syntax, where does the instance name fit?
> thanks in advance
|||hallo aaron, this was i tried as first but i got this error
"unable to find 'servername\instancename' in sysservers
"Aaron Bertrand [SQL Server MVP]" wrote:
> [Server\Instance].database.owner_or_schema.object
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
> "Raffaele" <Raffaele@.discussions.microsoft.com> wrote in message
> news:BFDA8EDF-1EC1-47E1-A71C-B13B89397DF5@.microsoft.com...
>
>
|||i already tried linking the instance, getting error 15028 - the server
already exists.
In the test case, both the default and the named instance are on the same
server, which is running MSSQL 2000
"Russell Fields" wrote:
> Raffaele,
> server-name is actually linkedserver-name, which may not actually be the
> name of a physical serve. Here is some code from SQL 2005 Books Online
> EXEC sp_addlinkedserver
> @.server='S1_instance1',
> @.srvproduct='',
> @.provider='SQLNCLI',
> @.datasrc='S1\instance1'
> You can see that the instance name is defined in the data source as the
> server 'S1' and the instance '\instance1'. The server name of
> 'S1_instance1' reflects that name, but it could be named
> 'MyFavoriteLinkedServer' or anything else.
> So, then: SELECT * FROM S1_instance1.mydatabase.myowner.MyTable
> RLF
> "Raffaele" <Raffaele@.discussions.microsoft.com> wrote in message
> news:BFDA8EDF-1EC1-47E1-A71C-B13B89397DF5@.microsoft.com...
>
>
|||ok linked server solved! the named instance was already present as "remote
server" since was used for a test replica.
Thanks
"Raffaele" wrote:
> i already tried linking the instance, getting error 15028 - the server
> already exists.
|||OK, thanks for the update. - RLF
"Raffaele" <Raffaele@.discussions.microsoft.com> wrote in message
news:DA137E75-C8F2-41B3-8B2F-C94344FB3FB4@.microsoft.com...
> ok linked server solved! the named instance was already present as "remote
> server" since was used for a test replica.
> Thanks
> "Raffaele" wrote:
>
qualified name (server-name.database-name.owner-name.object-name)
In this syntax, where does the instance name fit?
thanks in advance
Raffaele,
server-name is actually linkedserver-name, which may not actually be the
name of a physical serve. Here is some code from SQL 2005 Books Online
EXEC sp_addlinkedserver
@.server='S1_instance1',
@.srvproduct='',
@.provider='SQLNCLI',
@.datasrc='S1\instance1'
You can see that the instance name is defined in the data source as the
server 'S1' and the instance '\instance1'. The server name of
'S1_instance1' reflects that name, but it could be named
'MyFavoriteLinkedServer' or anything else.
So, then: SELECT * FROM S1_instance1.mydatabase.myowner.MyTable
RLF
"Raffaele" <Raffaele@.discussions.microsoft.com> wrote in message
news:BFDA8EDF-1EC1-47E1-A71C-B13B89397DF5@.microsoft.com...
> hallo it's unclear to me how to address a named instance using a fully
> qualified name (server-name.database-name.owner-name.object-name)
> In this syntax, where does the instance name fit?
> thanks in advance
|||[Server\Instance].database.owner_or_schema.object
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Raffaele" <Raffaele@.discussions.microsoft.com> wrote in message
news:BFDA8EDF-1EC1-47E1-A71C-B13B89397DF5@.microsoft.com...
> hallo it's unclear to me how to address a named instance using a fully
> qualified name (server-name.database-name.owner-name.object-name)
> In this syntax, where does the instance name fit?
> thanks in advance
|||hallo aaron, this was i tried as first but i got this error
"unable to find 'servername\instancename' in sysservers
"Aaron Bertrand [SQL Server MVP]" wrote:
> [Server\Instance].database.owner_or_schema.object
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
> "Raffaele" <Raffaele@.discussions.microsoft.com> wrote in message
> news:BFDA8EDF-1EC1-47E1-A71C-B13B89397DF5@.microsoft.com...
>
>
|||i already tried linking the instance, getting error 15028 - the server
already exists.
In the test case, both the default and the named instance are on the same
server, which is running MSSQL 2000
"Russell Fields" wrote:
> Raffaele,
> server-name is actually linkedserver-name, which may not actually be the
> name of a physical serve. Here is some code from SQL 2005 Books Online
> EXEC sp_addlinkedserver
> @.server='S1_instance1',
> @.srvproduct='',
> @.provider='SQLNCLI',
> @.datasrc='S1\instance1'
> You can see that the instance name is defined in the data source as the
> server 'S1' and the instance '\instance1'. The server name of
> 'S1_instance1' reflects that name, but it could be named
> 'MyFavoriteLinkedServer' or anything else.
> So, then: SELECT * FROM S1_instance1.mydatabase.myowner.MyTable
> RLF
> "Raffaele" <Raffaele@.discussions.microsoft.com> wrote in message
> news:BFDA8EDF-1EC1-47E1-A71C-B13B89397DF5@.microsoft.com...
>
>
|||ok linked server solved! the named instance was already present as "remote
server" since was used for a test replica.
Thanks
"Raffaele" wrote:
> i already tried linking the instance, getting error 15028 - the server
> already exists.
|||OK, thanks for the update. - RLF
"Raffaele" <Raffaele@.discussions.microsoft.com> wrote in message
news:DA137E75-C8F2-41B3-8B2F-C94344FB3FB4@.microsoft.com...
> ok linked server solved! the named instance was already present as "remote
> server" since was used for a test replica.
> Thanks
> "Raffaele" wrote:
>
fully qualified names with named instances
hallo it's unclear to me how to address a named instance using a fully
qualified name (server-name.database-name.owner-name.object-name)
In this syntax, where does the instance name fit?
thanks in advanceRaffaele,
server-name is actually linkedserver-name, which may not actually be the
name of a physical serve. Here is some code from SQL 2005 Books Online
EXEC sp_addlinkedserver
@.server='S1_instance1',
@.srvproduct='',
@.provider='SQLNCLI',
@.datasrc='S1\instance1'
You can see that the instance name is defined in the data source as the
server 'S1' and the instance '\instance1'. The server name of
'S1_instance1' reflects that name, but it could be named
'MyFavoriteLinkedServer' or anything else.
So, then: SELECT * FROM S1_instance1.mydatabase.myowner.MyTable
RLF
"Raffaele" <Raffaele@.discussions.microsoft.com> wrote in message
news:BFDA8EDF-1EC1-47E1-A71C-B13B89397DF5@.microsoft.com...
> hallo it's unclear to me how to address a named instance using a fully
> qualified name (server-name.database-name.owner-name.object-name)
> In this syntax, where does the instance name fit?
> thanks in advance|||[Server\Instance].database.owner_or_schema.object
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Raffaele" <Raffaele@.discussions.microsoft.com> wrote in message
news:BFDA8EDF-1EC1-47E1-A71C-B13B89397DF5@.microsoft.com...
> hallo it's unclear to me how to address a named instance using a fully
> qualified name (server-name.database-name.owner-name.object-name)
> In this syntax, where does the instance name fit?
> thanks in advance|||hallo aaron, this was i tried as first but i got this error
"unable to find 'servername\instancename' in sysservers
"Aaron Bertrand [SQL Server MVP]" wrote:
> [Server\Instance].database.owner_or_schema.object
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
> "Raffaele" <Raffaele@.discussions.microsoft.com> wrote in message
> news:BFDA8EDF-1EC1-47E1-A71C-B13B89397DF5@.microsoft.com...
>
>|||i already tried linking the instance, getting error 15028 - the server
already exists.
In the test case, both the default and the named instance are on the same
server, which is running MSSQL 2000
"Russell Fields" wrote:
> Raffaele,
> server-name is actually linkedserver-name, which may not actually be the
> name of a physical serve. Here is some code from SQL 2005 Books Online
> EXEC sp_addlinkedserver
> @.server='S1_instance1',
> @.srvproduct='',
> @.provider='SQLNCLI',
> @.datasrc='S1\instance1'
> You can see that the instance name is defined in the data source as the
> server 'S1' and the instance '\instance1'. The server name of
> 'S1_instance1' reflects that name, but it could be named
> 'MyFavoriteLinkedServer' or anything else.
> So, then: SELECT * FROM S1_instance1.mydatabase.myowner.MyTable
> RLF
> "Raffaele" <Raffaele@.discussions.microsoft.com> wrote in message
> news:BFDA8EDF-1EC1-47E1-A71C-B13B89397DF5@.microsoft.com...
>
>|||ok linked server solved! the named instance was already present as "remote
server" since was used for a test replica.
Thanks
"Raffaele" wrote:
> i already tried linking the instance, getting error 15028 - the server
> already exists.|||OK, thanks for the update. - RLF
"Raffaele" <Raffaele@.discussions.microsoft.com> wrote in message
news:DA137E75-C8F2-41B3-8B2F-C94344FB3FB4@.microsoft.com...
> ok linked server solved! the named instance was already present as "remote
> server" since was used for a test replica.
> Thanks
> "Raffaele" wrote:
>
>
qualified name (server-name.database-name.owner-name.object-name)
In this syntax, where does the instance name fit?
thanks in advanceRaffaele,
server-name is actually linkedserver-name, which may not actually be the
name of a physical serve. Here is some code from SQL 2005 Books Online
EXEC sp_addlinkedserver
@.server='S1_instance1',
@.srvproduct='',
@.provider='SQLNCLI',
@.datasrc='S1\instance1'
You can see that the instance name is defined in the data source as the
server 'S1' and the instance '\instance1'. The server name of
'S1_instance1' reflects that name, but it could be named
'MyFavoriteLinkedServer' or anything else.
So, then: SELECT * FROM S1_instance1.mydatabase.myowner.MyTable
RLF
"Raffaele" <Raffaele@.discussions.microsoft.com> wrote in message
news:BFDA8EDF-1EC1-47E1-A71C-B13B89397DF5@.microsoft.com...
> hallo it's unclear to me how to address a named instance using a fully
> qualified name (server-name.database-name.owner-name.object-name)
> In this syntax, where does the instance name fit?
> thanks in advance|||[Server\Instance].database.owner_or_schema.object
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Raffaele" <Raffaele@.discussions.microsoft.com> wrote in message
news:BFDA8EDF-1EC1-47E1-A71C-B13B89397DF5@.microsoft.com...
> hallo it's unclear to me how to address a named instance using a fully
> qualified name (server-name.database-name.owner-name.object-name)
> In this syntax, where does the instance name fit?
> thanks in advance|||hallo aaron, this was i tried as first but i got this error
"unable to find 'servername\instancename' in sysservers
"Aaron Bertrand [SQL Server MVP]" wrote:
> [Server\Instance].database.owner_or_schema.object
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
> "Raffaele" <Raffaele@.discussions.microsoft.com> wrote in message
> news:BFDA8EDF-1EC1-47E1-A71C-B13B89397DF5@.microsoft.com...
>
>|||i already tried linking the instance, getting error 15028 - the server
already exists.
In the test case, both the default and the named instance are on the same
server, which is running MSSQL 2000
"Russell Fields" wrote:
> Raffaele,
> server-name is actually linkedserver-name, which may not actually be the
> name of a physical serve. Here is some code from SQL 2005 Books Online
> EXEC sp_addlinkedserver
> @.server='S1_instance1',
> @.srvproduct='',
> @.provider='SQLNCLI',
> @.datasrc='S1\instance1'
> You can see that the instance name is defined in the data source as the
> server 'S1' and the instance '\instance1'. The server name of
> 'S1_instance1' reflects that name, but it could be named
> 'MyFavoriteLinkedServer' or anything else.
> So, then: SELECT * FROM S1_instance1.mydatabase.myowner.MyTable
> RLF
> "Raffaele" <Raffaele@.discussions.microsoft.com> wrote in message
> news:BFDA8EDF-1EC1-47E1-A71C-B13B89397DF5@.microsoft.com...
>
>|||ok linked server solved! the named instance was already present as "remote
server" since was used for a test replica.
Thanks
"Raffaele" wrote:
> i already tried linking the instance, getting error 15028 - the server
> already exists.|||OK, thanks for the update. - RLF
"Raffaele" <Raffaele@.discussions.microsoft.com> wrote in message
news:DA137E75-C8F2-41B3-8B2F-C94344FB3FB4@.microsoft.com...
> ok linked server solved! the named instance was already present as "remote
> server" since was used for a test replica.
> Thanks
> "Raffaele" wrote:
>
>
fully qualified names with named instances
hallo it's unclear to me how to address a named instance using a fully
qualified name (server-name.database-name.owner-name.object-name)
In this syntax, where does the instance name fit?
thanks in advanceRaffaele,
server-name is actually linkedserver-name, which may not actually be the
name of a physical serve. Here is some code from SQL 2005 Books Online
EXEC sp_addlinkedserver
@.server='S1_instance1',
@.srvproduct='',
@.provider='SQLNCLI',
@.datasrc='S1\instance1'
You can see that the instance name is defined in the data source as the
server 'S1' and the instance '\instance1'. The server name of
'S1_instance1' reflects that name, but it could be named
'MyFavoriteLinkedServer' or anything else.
So, then: SELECT * FROM S1_instance1.mydatabase.myowner.MyTable
RLF
"Raffaele" <Raffaele@.discussions.microsoft.com> wrote in message
news:BFDA8EDF-1EC1-47E1-A71C-B13B89397DF5@.microsoft.com...
> hallo it's unclear to me how to address a named instance using a fully
> qualified name (server-name.database-name.owner-name.object-name)
> In this syntax, where does the instance name fit?
> thanks in advance|||[Server\Instance].database.owner_or_schema.object
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Raffaele" <Raffaele@.discussions.microsoft.com> wrote in message
news:BFDA8EDF-1EC1-47E1-A71C-B13B89397DF5@.microsoft.com...
> hallo it's unclear to me how to address a named instance using a fully
> qualified name (server-name.database-name.owner-name.object-name)
> In this syntax, where does the instance name fit?
> thanks in advance|||hallo aaron, this was i tried as first but i got this error
"unable to find 'servername\instancename' in sysservers
"Aaron Bertrand [SQL Server MVP]" wrote:
> [Server\Instance].database.owner_or_schema.object
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
> "Raffaele" <Raffaele@.discussions.microsoft.com> wrote in message
> news:BFDA8EDF-1EC1-47E1-A71C-B13B89397DF5@.microsoft.com...
> > hallo it's unclear to me how to address a named instance using a fully
> > qualified name (server-name.database-name.owner-name.object-name)
> > In this syntax, where does the instance name fit?
> >
> > thanks in advance
>
>|||i already tried linking the instance, getting error 15028 - the server
already exists.
In the test case, both the default and the named instance are on the same
server, which is running MSSQL 2000
"Russell Fields" wrote:
> Raffaele,
> server-name is actually linkedserver-name, which may not actually be the
> name of a physical serve. Here is some code from SQL 2005 Books Online
> EXEC sp_addlinkedserver
> @.server='S1_instance1',
> @.srvproduct='',
> @.provider='SQLNCLI',
> @.datasrc='S1\instance1'
> You can see that the instance name is defined in the data source as the
> server 'S1' and the instance '\instance1'. The server name of
> 'S1_instance1' reflects that name, but it could be named
> 'MyFavoriteLinkedServer' or anything else.
> So, then: SELECT * FROM S1_instance1.mydatabase.myowner.MyTable
> RLF
> "Raffaele" <Raffaele@.discussions.microsoft.com> wrote in message
> news:BFDA8EDF-1EC1-47E1-A71C-B13B89397DF5@.microsoft.com...
> > hallo it's unclear to me how to address a named instance using a fully
> > qualified name (server-name.database-name.owner-name.object-name)
> > In this syntax, where does the instance name fit?
> >
> > thanks in advance
>
>|||ok linked server solved! the named instance was already present as "remote
server" since was used for a test replica.
Thanks
"Raffaele" wrote:
> i already tried linking the instance, getting error 15028 - the server
> already exists.|||OK, thanks for the update. - RLF
"Raffaele" <Raffaele@.discussions.microsoft.com> wrote in message
news:DA137E75-C8F2-41B3-8B2F-C94344FB3FB4@.microsoft.com...
> ok linked server solved! the named instance was already present as "remote
> server" since was used for a test replica.
> Thanks
> "Raffaele" wrote:
>> i already tried linking the instance, getting error 15028 - the server
>> already exists.
>
qualified name (server-name.database-name.owner-name.object-name)
In this syntax, where does the instance name fit?
thanks in advanceRaffaele,
server-name is actually linkedserver-name, which may not actually be the
name of a physical serve. Here is some code from SQL 2005 Books Online
EXEC sp_addlinkedserver
@.server='S1_instance1',
@.srvproduct='',
@.provider='SQLNCLI',
@.datasrc='S1\instance1'
You can see that the instance name is defined in the data source as the
server 'S1' and the instance '\instance1'. The server name of
'S1_instance1' reflects that name, but it could be named
'MyFavoriteLinkedServer' or anything else.
So, then: SELECT * FROM S1_instance1.mydatabase.myowner.MyTable
RLF
"Raffaele" <Raffaele@.discussions.microsoft.com> wrote in message
news:BFDA8EDF-1EC1-47E1-A71C-B13B89397DF5@.microsoft.com...
> hallo it's unclear to me how to address a named instance using a fully
> qualified name (server-name.database-name.owner-name.object-name)
> In this syntax, where does the instance name fit?
> thanks in advance|||[Server\Instance].database.owner_or_schema.object
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Raffaele" <Raffaele@.discussions.microsoft.com> wrote in message
news:BFDA8EDF-1EC1-47E1-A71C-B13B89397DF5@.microsoft.com...
> hallo it's unclear to me how to address a named instance using a fully
> qualified name (server-name.database-name.owner-name.object-name)
> In this syntax, where does the instance name fit?
> thanks in advance|||hallo aaron, this was i tried as first but i got this error
"unable to find 'servername\instancename' in sysservers
"Aaron Bertrand [SQL Server MVP]" wrote:
> [Server\Instance].database.owner_or_schema.object
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
> "Raffaele" <Raffaele@.discussions.microsoft.com> wrote in message
> news:BFDA8EDF-1EC1-47E1-A71C-B13B89397DF5@.microsoft.com...
> > hallo it's unclear to me how to address a named instance using a fully
> > qualified name (server-name.database-name.owner-name.object-name)
> > In this syntax, where does the instance name fit?
> >
> > thanks in advance
>
>|||i already tried linking the instance, getting error 15028 - the server
already exists.
In the test case, both the default and the named instance are on the same
server, which is running MSSQL 2000
"Russell Fields" wrote:
> Raffaele,
> server-name is actually linkedserver-name, which may not actually be the
> name of a physical serve. Here is some code from SQL 2005 Books Online
> EXEC sp_addlinkedserver
> @.server='S1_instance1',
> @.srvproduct='',
> @.provider='SQLNCLI',
> @.datasrc='S1\instance1'
> You can see that the instance name is defined in the data source as the
> server 'S1' and the instance '\instance1'. The server name of
> 'S1_instance1' reflects that name, but it could be named
> 'MyFavoriteLinkedServer' or anything else.
> So, then: SELECT * FROM S1_instance1.mydatabase.myowner.MyTable
> RLF
> "Raffaele" <Raffaele@.discussions.microsoft.com> wrote in message
> news:BFDA8EDF-1EC1-47E1-A71C-B13B89397DF5@.microsoft.com...
> > hallo it's unclear to me how to address a named instance using a fully
> > qualified name (server-name.database-name.owner-name.object-name)
> > In this syntax, where does the instance name fit?
> >
> > thanks in advance
>
>|||ok linked server solved! the named instance was already present as "remote
server" since was used for a test replica.
Thanks
"Raffaele" wrote:
> i already tried linking the instance, getting error 15028 - the server
> already exists.|||OK, thanks for the update. - RLF
"Raffaele" <Raffaele@.discussions.microsoft.com> wrote in message
news:DA137E75-C8F2-41B3-8B2F-C94344FB3FB4@.microsoft.com...
> ok linked server solved! the named instance was already present as "remote
> server" since was used for a test replica.
> Thanks
> "Raffaele" wrote:
>> i already tried linking the instance, getting error 15028 - the server
>> already exists.
>
Fully qualified name of a table
Hi all,
How can I find the fully qualified name of a table, if all I have is the
table name
I realise that this might be a bit ambigious, but I was wondering.
For example, if I had a table name like employees. It could be in northwind,
or in corporate or both
is there a way to find this out
Thanks
RobertNo easy way, since the object names are not contained in one single table. T
he object names are
contained in a system table or a view inside each database. But you could cr
eate a stored procedure
in which you use a cursor to loop each database and construct dynamic SQL to
pass such a query
against that system table or view. Or you could cheat and use an undocumente
d stored procedure that
dopes just that:
EXEC sp_MSForeachdb 'SELECT * FROM ?.INFORMATION_SCHEMA.TABLES WHERE TABLE_N
AME = ''authors'''
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Robert Bravery" <me@.u.com> wrote in message news:OmHh35UmGHA.1404@.TK2MSFTNGP05.phx.gbl...[
color=darkred]
> Hi all,
> How can I find the fully qualified name of a table, if all I have is the
> table name
> I realise that this might be a bit ambigious, but I was wondering.
> For example, if I had a table name like employees. It could be in northwin
d,
> or in corporate or both
> is there a way to find this out
> Thanks
> Robert
>[/color]|||> How can I find the fully qualified name of a table, if all I have is the
> table name
If all you have is a table name, then you could get 1 or 20 answers (even
within the same database, because you could have dbo.Employees and
someOtherUser.Employees).
Now you are talking about going across databases, that's even more potential
answers. Here is how to find all the tables named 'Employees' in the
databases Northwind and Corporate:
SELECT '[' + TABLE_CATALOG + '].[ + TABLE_SCHEMA + '].[' + TABLE_NAME
FROM NorthWind.INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'Employees'
AND TABLE_TYPE = 'BASE_TABLE'
UNION
SELECT '[' + TABLE_CATALOG + '].[ + TABLE_SCHEMA + '].[' + TABLE_NAME
FROM Corporate.INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'Employees'
AND TABLE_TYPE = 'BASE_TABLE';|||You might try something like:
sp_msforeachdb 'select ''?.'' + rtrim(table_schema) + ''.'' +
rtrim(table_name)
from ?.information_schema.tables where table_name = ''employees'''
--
If you are looking for SQL Server examples check out my Website at
http://www.geocities.com/sqlserverexamples
"Robert Bravery" wrote:
> Hi all,
> How can I find the fully qualified name of a table, if all I have is the
> table name
> I realise that this might be a bit ambigious, but I was wondering.
> For example, if I had a table name like employees. It could be in northwin
d,
> or in corporate or both
> is there a way to find this out
> Thanks
> Robert
>
>|||Try,
create table #t (
tc sysname,
ts sysname,
tn sysname
)
declare @.sql nvarchar(4000)
declare @.tc sysname
declare my_cursor cursor
local
fast_forward
for
select [name]
from master.dbo.sysdatabases
where [name] not in ('master', 'tempdb', 'msdb', 'model')
open my_cursor
while 1 = 1
begin
fetch next from my_cursor into @.tc
if @.@.error != 0 or @.@.fetch_status != 0 break
set @.sql = N'use [' + @.tc + ']; select TABLE_CATALOG, TABLE_SCHEMA,
TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = ''BASE TABLE'''
insert into #t
exec sp_executesql @.sql
end
close my_cursor
deallocate my_cursor
select
*
from
#t as a
where
exists (
select
*
from
#t as b
where
b.ts = a.ts
and b.tn = a.tn
and b.tc != a.tc
)
order by
tn,
tc,
ts
drop table #t
go
AMB
"Robert Bravery" wrote:
> Hi all,
> How can I find the fully qualified name of a table, if all I have is the
> table name
> I realise that this might be a bit ambigious, but I was wondering.
> For example, if I had a table name like employees. It could be in northwin
d,
> or in corporate or both
> is there a way to find this out
> Thanks
> Robert
>
>
How can I find the fully qualified name of a table, if all I have is the
table name
I realise that this might be a bit ambigious, but I was wondering.
For example, if I had a table name like employees. It could be in northwind,
or in corporate or both
is there a way to find this out
Thanks
RobertNo easy way, since the object names are not contained in one single table. T
he object names are
contained in a system table or a view inside each database. But you could cr
eate a stored procedure
in which you use a cursor to loop each database and construct dynamic SQL to
pass such a query
against that system table or view. Or you could cheat and use an undocumente
d stored procedure that
dopes just that:
EXEC sp_MSForeachdb 'SELECT * FROM ?.INFORMATION_SCHEMA.TABLES WHERE TABLE_N
AME = ''authors'''
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Robert Bravery" <me@.u.com> wrote in message news:OmHh35UmGHA.1404@.TK2MSFTNGP05.phx.gbl...[
color=darkred]
> Hi all,
> How can I find the fully qualified name of a table, if all I have is the
> table name
> I realise that this might be a bit ambigious, but I was wondering.
> For example, if I had a table name like employees. It could be in northwin
d,
> or in corporate or both
> is there a way to find this out
> Thanks
> Robert
>[/color]|||> How can I find the fully qualified name of a table, if all I have is the
> table name
If all you have is a table name, then you could get 1 or 20 answers (even
within the same database, because you could have dbo.Employees and
someOtherUser.Employees).
Now you are talking about going across databases, that's even more potential
answers. Here is how to find all the tables named 'Employees' in the
databases Northwind and Corporate:
SELECT '[' + TABLE_CATALOG + '].[ + TABLE_SCHEMA + '].[' + TABLE_NAME
FROM NorthWind.INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'Employees'
AND TABLE_TYPE = 'BASE_TABLE'
UNION
SELECT '[' + TABLE_CATALOG + '].[ + TABLE_SCHEMA + '].[' + TABLE_NAME
FROM Corporate.INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'Employees'
AND TABLE_TYPE = 'BASE_TABLE';|||You might try something like:
sp_msforeachdb 'select ''?.'' + rtrim(table_schema) + ''.'' +
rtrim(table_name)
from ?.information_schema.tables where table_name = ''employees'''
--
If you are looking for SQL Server examples check out my Website at
http://www.geocities.com/sqlserverexamples
"Robert Bravery" wrote:
> Hi all,
> How can I find the fully qualified name of a table, if all I have is the
> table name
> I realise that this might be a bit ambigious, but I was wondering.
> For example, if I had a table name like employees. It could be in northwin
d,
> or in corporate or both
> is there a way to find this out
> Thanks
> Robert
>
>|||Try,
create table #t (
tc sysname,
ts sysname,
tn sysname
)
declare @.sql nvarchar(4000)
declare @.tc sysname
declare my_cursor cursor
local
fast_forward
for
select [name]
from master.dbo.sysdatabases
where [name] not in ('master', 'tempdb', 'msdb', 'model')
open my_cursor
while 1 = 1
begin
fetch next from my_cursor into @.tc
if @.@.error != 0 or @.@.fetch_status != 0 break
set @.sql = N'use [' + @.tc + ']; select TABLE_CATALOG, TABLE_SCHEMA,
TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = ''BASE TABLE'''
insert into #t
exec sp_executesql @.sql
end
close my_cursor
deallocate my_cursor
select
*
from
#t as a
where
exists (
select
*
from
#t as b
where
b.ts = a.ts
and b.tn = a.tn
and b.tc != a.tc
)
order by
tn,
tc,
ts
drop table #t
go
AMB
"Robert Bravery" wrote:
> Hi all,
> How can I find the fully qualified name of a table, if all I have is the
> table name
> I realise that this might be a bit ambigious, but I was wondering.
> For example, if I had a table name like employees. It could be in northwin
d,
> or in corporate or both
> is there a way to find this out
> Thanks
> Robert
>
>
Fully editable datagrid
I have been trying to formulate a fully editabe datagrid for a couple of days with no luck. I have used code from 4guys and some other sites and am at the point where I can render the datagrid correctly (as a bouncolumn and template column-textbox) but when I try to update the database it all falls apart. I am getting "input string was not in a correct format" and the error references ...
Dim id as Integer = Convert.ToInt32(sls.DataKeys(dgi.ItemIndex))
I suspect the problem lies in the area of primary indexes and such. The table I am using is a simple two-column table with usernames, passwords. Username is the primary field. Here is the actual code I am using...
<code>
<%@. Page Explicit="True" Language="VB" Debug="True" %>
<%@. Import Namespace="System.Data" %>
<%@. Import Namespace="System.Data.OleDb" %>
<html>
<script runat="server">
Dim Connect As OleDbConnection = New OleDbConnection
Dim Adapter As OleDbDataAdapter = New OleDbDataAdapter
Dim DS As DataSet = New DataSet
Dim ConnectString, SelectStatement As String
Sub Page_Load(Sender As Object, E As EventArgs)
If Not IsPostBack Then
SelectStatement = "Select * From Table"
ConnectString = "Provider=SQLOLEDB;UID=;PWD=;" & "Data Source=;Initial Catalog=;"
Connect.ConnectionString = ConnectString
Adapter.SelectCommand = New oleDbCommand(SelectStatement, Connect)
Adapter.SelectCommand.Connection.Open
Adapter.Fill(DS, "Items")
sls.Datasource = DS.Tables("Items")
Page.Databind
Connect.Close()
End If
End Sub
Sub Click(sender As Object, e As EventArgs)
Dim myConnection as New OleDbConnection(ConnectString)
Dim updateSQL as String = "UPDATE Table SET password = @.Password WHERE Username = @.ID"
Dim myCommand as New oleDbCommand(updateSQL, myConnection)
Dim dgi as DataGridItem
For Each dgi in sls.Items
'Read in the Primary Key Field
Dim id as Integer = Convert.ToInt32(sls.DataKeys(dgi.ItemIndex))
Dim password as String = CType(dgi.FindControl("txtPass"), TextBox).Text
'Issue an UPDATE statement...
myCommand.Parameters.Clear()
myCommand.Parameters.Add("@.ID", id)
myCommand.Parameters.Add("@.Password", password)
myCommand.ExecuteNonQuery()
Next
End Sub
</script>
<body>
<form runat="Server">
<asp:datagrid id="sls" runat="server" AutoGenerateColumns="False" datakeyfield="Username">
<Columns>
<asp:BoundColumn HeaderText="UserName" datafield="Username"/>
<asp:TemplateColumn HeaderText="Password">
<ItemTemplate>
<asp:TextBox runat="server" id="txtPass" Columns="10"
Text='<%# DataBinder.Eval(Container, "DataItem.Password") %>' />
</ItemTemplate>
</asp:TemplateColumn>
</Columns>
</asp:datagrid><asp:button id="Update" text="Update All" runat="Server" onclick="Click"/>
</form>
</body>
</html>
</code>
Anyone have any idea as to why the id variable (error message above) is causing problems?
You have the DataKeyField="UserName" in your datagrid setup rather than the Id...
<asp:datagrid id="sls" runat="server" AutoGenerateColumns="False" datakeyfield="Username">
So when you try to pass the key field to the procedure with this line...
Dim id as Integer = Convert.ToInt32(sls.DataKeys(dgi.ItemIndex))
it's going to error because it can't covnert an alphanumeric username to an int32. Change the DataKeyField to match your ID field and that should clear it up.|||
RJA,
Thanks for the response. I have datakeyfield set to "Username" because "Username" is the unique (primary key) column in that table. There are only two columns, username and password. I understand what you are saying about converting alphanumeric to integer but what would I use as the "ID field" you mentioned at the end of your post?
Thanks,
CM
Fulltextsearch Error in Userinstance
I've enabled the Fulltextsearch in some Tables of my DB.
In SQL Server Management Studio the querys are all have results and running free of errors.
If i will use the same querys in VWD, they're running in a SqlException. Translated from german:
The Fulltextsearch cant be used in the Userinstance.
But a SelectCommand from the WebSite to the IndexState:
SELECT crawl_end_date FROM sys.fulltext_indexes WHERE fulltext_catalog_id = 10
works fine.
What i'm wrong?
The ConnectionString i'm using:
="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Support.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"
The FulltextService (msftesql.exe) is running under a UserAccount.
Thanx
Klaus
Hi Klaus,
Full Text Search can not be used in a User Instance. If you need the functionality of FTS, you'll need to attach your database to the main instance and connect to that.
Regards,
Mike Wachal
SQL Express team
-
Check out my tips for getting your answer faster and how to ask a good question: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=307712&SiteID=1
Hi Mike,
User Instance is the magic word.
Change my connectionstring like connectionstring.com says and all works fine.
Thanks for the hint to the right way.
Klaus
Full-Text-Search
Hi friends
i work with sql server 2005 express(Management Studio)
can i use full text search service with that edition of sql server?
and how i can use that.
thank u.![Smile [:)]](http://pics.10026.com/?src=/emoticons/emotion-1.gif)
The Full-Text Search feature is not available in SQL Server 2005 Express.
HTH,
Ryan
thank u for ur answering.
so which edition of sql server support that?
|||
fahima wrote:
so which edition of sql server support that?
The Workgroup, Standard, and Enterprise editions all support Full-text Search.
HTH,
Ryan
ok
is it hard to work with service?
|||
is it hard to work with service?
Do you mean is it difficult to get Full-text Search to work? I've never played with Full-text Search, but here's an article that should give you a better understanding of it:Understanding SQL Server Full-Text Indexing.
HTH,
Ryan
the Express Edition does in fact have full text search feature available. to get this you need to download a different file, SQL Server with Advance services -http://msdn.microsoft.com/vstudio/express/sql/compare/default.aspx
Not sure if this was released since the last post here - just though i would answer seeing as i was floating around.
regards
Full-Text-Search
Hi,
I have a full-text search enabled table and an image data type column that stores the extracts from the documents stored in the files. I have created a full text catalog that indexes few columns in the table and image data type column as well and I assigned the extension column to this image data type column.
Everything works fine except that search on the image data type column called, in my case "body" does not return anything. The documents exist, there is an information in the "body" column.
My code goes like this:
select FT_TBL.resourceID, FT_TBL.lang, FT_TBL.keywords, FT_TBL.description, FT_TBL.extension, b.rank
from full_text_search as FT_TBL inner join containstable(full_text_search, *, 'Microsoft') as b
on b.[key] = FT_TBL.resourceID
order by b.rank desc
any help appreciated,
thanksI figured the solution to the previous post. I was trying to search on the .pdf files, but I do not have filter installed on the server to filter through these files, other files extension work fine.
Another problem:
I have two tables that are full-text search enabled. And I need to search one table first, and than the columns in the second one. This works fine if the search criteria is found in both tables, but if not the query returns nothing.
help much appreciated!
Thanks|||use union all.
I have a full-text search enabled table and an image data type column that stores the extracts from the documents stored in the files. I have created a full text catalog that indexes few columns in the table and image data type column as well and I assigned the extension column to this image data type column.
Everything works fine except that search on the image data type column called, in my case "body" does not return anything. The documents exist, there is an information in the "body" column.
My code goes like this:
select FT_TBL.resourceID, FT_TBL.lang, FT_TBL.keywords, FT_TBL.description, FT_TBL.extension, b.rank
from full_text_search as FT_TBL inner join containstable(full_text_search, *, 'Microsoft') as b
on b.[key] = FT_TBL.resourceID
order by b.rank desc
any help appreciated,
thanksI figured the solution to the previous post. I was trying to search on the .pdf files, but I do not have filter installed on the server to filter through these files, other files extension work fine.
Another problem:
I have two tables that are full-text search enabled. And I need to search one table first, and than the columns in the second one. This works fine if the search criteria is found in both tables, but if not the query returns nothing.
help much appreciated!
Thanks|||use union all.
FullTextSearch
Hi,
My FullTextSearch on clustered SQL Server 2000 in Windows 2003 is failing.
How do i fix it or do i need to reinstall?How?
This is the error msg i get.What file is it looking for?By replacing the
file, can i get it back online?
"An error occurred during the online operation for instance <SQL Server
Fulltext (CLUSTERABC)>: 80070002 - The system cannot find the file specified."
TIA
Did you rebuild the cluster? You may get this error after attempting to
rebuild the cluster when linger components of the initial failed cluster
installation failed. Consult
http://support.microsoft.com/kb/2909...22120121120120 for
more information.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:2B996818-7985-4B50-8F53-8E8336E18EDB@.microsoft.com...
> Hi,
> My FullTextSearch on clustered SQL Server 2000 in Windows 2003 is failing.
> How do i fix it or do i need to reinstall?How?
> This is the error msg i get.What file is it looking for?By replacing the
> file, can i get it back online?
> "An error occurred during the online operation for instance <SQL Server
> Fulltext (CLUSTERABC)>: 80070002 - The system cannot find the file
> specified."
> TIA
|||hi,
i didnt reinstall the cluster. It's just failing recently. Any idea? Any way
to fix it?
TIA
"Hilary Cotter" wrote:
> Did you rebuild the cluster? You may get this error after attempting to
> rebuild the cluster when linger components of the initial failed cluster
> installation failed. Consult
> http://support.microsoft.com/kb/2909...22120121120120 for
> more information.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "rupart" <rupart@.discussions.microsoft.com> wrote in message
> news:2B996818-7985-4B50-8F53-8E8336E18EDB@.microsoft.com...
>
>
My FullTextSearch on clustered SQL Server 2000 in Windows 2003 is failing.
How do i fix it or do i need to reinstall?How?
This is the error msg i get.What file is it looking for?By replacing the
file, can i get it back online?
"An error occurred during the online operation for instance <SQL Server
Fulltext (CLUSTERABC)>: 80070002 - The system cannot find the file specified."
TIA
Did you rebuild the cluster? You may get this error after attempting to
rebuild the cluster when linger components of the initial failed cluster
installation failed. Consult
http://support.microsoft.com/kb/2909...22120121120120 for
more information.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:2B996818-7985-4B50-8F53-8E8336E18EDB@.microsoft.com...
> Hi,
> My FullTextSearch on clustered SQL Server 2000 in Windows 2003 is failing.
> How do i fix it or do i need to reinstall?How?
> This is the error msg i get.What file is it looking for?By replacing the
> file, can i get it back online?
> "An error occurred during the online operation for instance <SQL Server
> Fulltext (CLUSTERABC)>: 80070002 - The system cannot find the file
> specified."
> TIA
|||hi,
i didnt reinstall the cluster. It's just failing recently. Any idea? Any way
to fix it?
TIA
"Hilary Cotter" wrote:
> Did you rebuild the cluster? You may get this error after attempting to
> rebuild the cluster when linger components of the initial failed cluster
> installation failed. Consult
> http://support.microsoft.com/kb/2909...22120121120120 for
> more information.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "rupart" <rupart@.discussions.microsoft.com> wrote in message
> news:2B996818-7985-4B50-8F53-8E8336E18EDB@.microsoft.com...
>
>
FullTextSearch
Hi,
My FullTextSearch on clustered SQL Server 2000 in Windows 2003 is failing.
How do i fix it or do i need to reinstall?How?
This is the error msg i get.What file is it looking for?By replacing the
file, can i get it back online?
"An error occurred during the online operation for instance <SQL Server
Fulltext (CLUSTERABC)>: 80070002 - The system cannot find the file specified."
TIADid you rebuild the cluster? You may get this error after attempting to
rebuild the cluster when linger components of the initial failed cluster
installation failed. Consult
http://support.microsoft.com/kb/290991/en-us#XSLTH3208121122120121120120 for
more information.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:2B996818-7985-4B50-8F53-8E8336E18EDB@.microsoft.com...
> Hi,
> My FullTextSearch on clustered SQL Server 2000 in Windows 2003 is failing.
> How do i fix it or do i need to reinstall?How?
> This is the error msg i get.What file is it looking for?By replacing the
> file, can i get it back online?
> "An error occurred during the online operation for instance <SQL Server
> Fulltext (CLUSTERABC)>: 80070002 - The system cannot find the file
> specified."
> TIA|||hi,
i didnt reinstall the cluster. It's just failing recently. Any idea? Any way
to fix it?
TIA
"Hilary Cotter" wrote:
> Did you rebuild the cluster? You may get this error after attempting to
> rebuild the cluster when linger components of the initial failed cluster
> installation failed. Consult
> http://support.microsoft.com/kb/290991/en-us#XSLTH3208121122120121120120 for
> more information.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "rupart" <rupart@.discussions.microsoft.com> wrote in message
> news:2B996818-7985-4B50-8F53-8E8336E18EDB@.microsoft.com...
> > Hi,
> > My FullTextSearch on clustered SQL Server 2000 in Windows 2003 is failing.
> > How do i fix it or do i need to reinstall?How?
> >
> > This is the error msg i get.What file is it looking for?By replacing the
> > file, can i get it back online?
> >
> > "An error occurred during the online operation for instance <SQL Server
> > Fulltext (CLUSTERABC)>: 80070002 - The system cannot find the file
> > specified."
> >
> > TIA
>
>
My FullTextSearch on clustered SQL Server 2000 in Windows 2003 is failing.
How do i fix it or do i need to reinstall?How?
This is the error msg i get.What file is it looking for?By replacing the
file, can i get it back online?
"An error occurred during the online operation for instance <SQL Server
Fulltext (CLUSTERABC)>: 80070002 - The system cannot find the file specified."
TIADid you rebuild the cluster? You may get this error after attempting to
rebuild the cluster when linger components of the initial failed cluster
installation failed. Consult
http://support.microsoft.com/kb/290991/en-us#XSLTH3208121122120121120120 for
more information.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:2B996818-7985-4B50-8F53-8E8336E18EDB@.microsoft.com...
> Hi,
> My FullTextSearch on clustered SQL Server 2000 in Windows 2003 is failing.
> How do i fix it or do i need to reinstall?How?
> This is the error msg i get.What file is it looking for?By replacing the
> file, can i get it back online?
> "An error occurred during the online operation for instance <SQL Server
> Fulltext (CLUSTERABC)>: 80070002 - The system cannot find the file
> specified."
> TIA|||hi,
i didnt reinstall the cluster. It's just failing recently. Any idea? Any way
to fix it?
TIA
"Hilary Cotter" wrote:
> Did you rebuild the cluster? You may get this error after attempting to
> rebuild the cluster when linger components of the initial failed cluster
> installation failed. Consult
> http://support.microsoft.com/kb/290991/en-us#XSLTH3208121122120121120120 for
> more information.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "rupart" <rupart@.discussions.microsoft.com> wrote in message
> news:2B996818-7985-4B50-8F53-8E8336E18EDB@.microsoft.com...
> > Hi,
> > My FullTextSearch on clustered SQL Server 2000 in Windows 2003 is failing.
> > How do i fix it or do i need to reinstall?How?
> >
> > This is the error msg i get.What file is it looking for?By replacing the
> > file, can i get it back online?
> >
> > "An error occurred during the online operation for instance <SQL Server
> > Fulltext (CLUSTERABC)>: 80070002 - The system cannot find the file
> > specified."
> >
> > TIA
>
>
FullTextSearch
Hi,
My FullTextSearch on clustered SQL Server 2000 in Windows 2003 is failing.
How do i fix it or do i need to reinstall?How?
This is the error msg i get.What file is it looking for?By replacing the
file, can i get it back online?
"An error occurred during the online operation for instance <SQL Server
Fulltext (CLUSTERABC)>: 80070002 - The system cannot find the file specified
."
TIADid you rebuild the cluster? You may get this error after attempting to
rebuild the cluster when linger components of the initial failed cluster
installation failed. Consult
http://support.microsoft.com/kb/290...122120121120120 for
more information.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:2B996818-7985-4B50-8F53-8E8336E18EDB@.microsoft.com...
> Hi,
> My FullTextSearch on clustered SQL Server 2000 in Windows 2003 is failing.
> How do i fix it or do i need to reinstall?How?
> This is the error msg i get.What file is it looking for?By replacing the
> file, can i get it back online?
> "An error occurred during the online operation for instance <SQL Server
> Fulltext (CLUSTERABC)>: 80070002 - The system cannot find the file
> specified."
> TIA|||hi,
i didnt reinstall the cluster. It's just failing recently. Any idea? Any way
to fix it?
TIA
"Hilary Cotter" wrote:
> Did you rebuild the cluster? You may get this error after attempting to
> rebuild the cluster when linger components of the initial failed cluster
> installation failed. Consult
> http://support.microsoft.com/kb/290...122120121120120 f
or
> more information.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "rupart" <rupart@.discussions.microsoft.com> wrote in message
> news:2B996818-7985-4B50-8F53-8E8336E18EDB@.microsoft.com...
>
>
My FullTextSearch on clustered SQL Server 2000 in Windows 2003 is failing.
How do i fix it or do i need to reinstall?How?
This is the error msg i get.What file is it looking for?By replacing the
file, can i get it back online?
"An error occurred during the online operation for instance <SQL Server
Fulltext (CLUSTERABC)>: 80070002 - The system cannot find the file specified
."
TIADid you rebuild the cluster? You may get this error after attempting to
rebuild the cluster when linger components of the initial failed cluster
installation failed. Consult
http://support.microsoft.com/kb/290...122120121120120 for
more information.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:2B996818-7985-4B50-8F53-8E8336E18EDB@.microsoft.com...
> Hi,
> My FullTextSearch on clustered SQL Server 2000 in Windows 2003 is failing.
> How do i fix it or do i need to reinstall?How?
> This is the error msg i get.What file is it looking for?By replacing the
> file, can i get it back online?
> "An error occurred during the online operation for instance <SQL Server
> Fulltext (CLUSTERABC)>: 80070002 - The system cannot find the file
> specified."
> TIA|||hi,
i didnt reinstall the cluster. It's just failing recently. Any idea? Any way
to fix it?
TIA
"Hilary Cotter" wrote:
> Did you rebuild the cluster? You may get this error after attempting to
> rebuild the cluster when linger components of the initial failed cluster
> installation failed. Consult
> http://support.microsoft.com/kb/290...122120121120120 f
or
> more information.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "rupart" <rupart@.discussions.microsoft.com> wrote in message
> news:2B996818-7985-4B50-8F53-8E8336E18EDB@.microsoft.com...
>
>
FullTexted table
Hi,
I have a database that has about 100 tables. How can I find out which
tables are full-text indexed?
Thanks
Ed
Ed,
You can use the following system stored procedures to determine what tables
are FT Indexed:
sp_help_fulltext_tables
You can also use the following metadata function to gather more FT info:
select
ObjectProperty(object_id(N'<table_name>'),N'TableH asActiveFulltextIndex')
select
objectproperty(object_id(N'<table_name>'),N'TableF ulltextChangeTrackingOn')
select
objectproperty(object_id(N'<table_name>'),N'TableF ullTextBackgroundUpdateInd
exOn')
select
objectproperty(object_id(N'<table_name>'),N'TableF ullTextPopulateStatus')
Regards,
John
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:8E4A8EDC-C5E3-4C75-A200-5412B0818CE3@.microsoft.com...
> Hi,
> I have a database that has about 100 tables. How can I find out which
> tables are full-text indexed?
> Thanks
> Ed
I have a database that has about 100 tables. How can I find out which
tables are full-text indexed?
Thanks
Ed
Ed,
You can use the following system stored procedures to determine what tables
are FT Indexed:
sp_help_fulltext_tables
You can also use the following metadata function to gather more FT info:
select
ObjectProperty(object_id(N'<table_name>'),N'TableH asActiveFulltextIndex')
select
objectproperty(object_id(N'<table_name>'),N'TableF ulltextChangeTrackingOn')
select
objectproperty(object_id(N'<table_name>'),N'TableF ullTextBackgroundUpdateInd
exOn')
select
objectproperty(object_id(N'<table_name>'),N'TableF ullTextPopulateStatus')
Regards,
John
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:8E4A8EDC-C5E3-4C75-A200-5412B0818CE3@.microsoft.com...
> Hi,
> I have a database that has about 100 tables. How can I find out which
> tables are full-text indexed?
> Thanks
> Ed
fulltext, distinct, order by
I have a problem. I use Constainstable to make a fulltextsearch over 4
tables.
Now I want to order the result set. The problem is that if I insert the
column all_ranks in the Select part I have some double results. That is
because some keywords are 2 times or 3 times related to an expert.
So If I use this statement I have double information:
Select distinct inQuery.all_rank, inQuery.employeeid,
inQuery.firstname, inQuery.familyname, inQuery.Qualifications,
inQuery.Title, inQuery.Profil, inQuery.deptname from experts,
(SELECT e.EMPLID as employeeid, e.FIRST_NAME AS firstname,
e.FAMILY_NAME AS familyname, e.QUALIFICATIONS AS Qualifications,
e.TITLE AS Title, e.LINK AS Profil,
d.NAME AS deptname, ISNULL(exp_rank.[RANK], 0) +
ISNULL(key_rank.[RANK], 0) + ISNULL(area_rank.[RANK],0)
+ ISNULL(theme_rank.[RANK],0) as all_rank FROM dbo.EXPERTS e
inner JOIN
dbo.EXP_KEY ek ON e.EMPLID = ek.EMPLID INNER JOIN
dbo.KEYWORDS k ON k.KEY_ID = ek.KEY_ID inner
JOIN
dbo.EXP_DEPT ed ON ed.EMPLID = e.EMPLID inner
JOIN
dbo.DEPARTMENT d ON d.DEPARTMENT_ID =
ed.DEPARTMENT_ID inner JOIN
dbo.AREA_KEY ak ON ak.KEY_ID = k.KEY_ID inner
JOIN
dbo.AREA a ON a.AREA_ID = ak.AREA_ID inner JOIN
dbo.THEME t ON t.THEME_ID = a.THEME_ID full
outer join
CONTAINSTABLE(EXPERTS, *, '"applied" or "computing"') as exp_rank on
exp_rank.[KEY] = e.emplid full outer join
CONTAINSTABLE(Keywords, *, '"applied" or "computing"') as key_rank on
key_rank.[KEY] = k.key_id full outer join
CONTAINSTABLE(Area, *, '"applied" or "computing"') as area_rank on
area_rank.[KEY] = a.area_id full outer join
CONTAINSTABLE(Theme, *, '"applied" or "computing"') as theme_rank on
theme_rank.[KEY] = t.theme_id
WHERE k.key_id = key_rank.[KEY] or
e.emplid = exp_rank.[KEY] or
a.area_id = area_rank.[KEY] or
t.theme_id = theme_rank.[KEY]) as inQuery
order by inQuery.all_rank desc
What I want to use is a Statement of this kind:
Select distinct inQuery.employeeid, inQuery.firstname,
inQuery.familyname, inQuery.Qualifications, inQuery.Title,
inQuery.Profil, inQuery.deptname from experts,
(SELECT e.EMPLID as employeeid, e.FIRST_NAME AS firstname,
e.FAMILY_NAME AS familyname, e.QUALIFICATIONS AS Qualifications,
e.TITLE AS Title, e.LINK AS Profil,
d.NAME AS deptname, ISNULL(exp_rank.[RANK], 0) +
ISNULL(key_rank.[RANK], 0) + ISNULL(area_rank.[RANK],0)
+ ISNULL(theme_rank.[RANK],0) as all_rank FROM dbo.EXPERTS e
inner JOIN
dbo.EXP_KEY ek ON e.EMPLID = ek.EMPLID INNER JOIN
dbo.KEYWORDS k ON k.KEY_ID = ek.KEY_ID inner
JOIN
dbo.EXP_DEPT ed ON ed.EMPLID = e.EMPLID inner
JOIN
dbo.DEPARTMENT d ON d.DEPARTMENT_ID =
ed.DEPARTMENT_ID inner JOIN
dbo.AREA_KEY ak ON ak.KEY_ID = k.KEY_ID inner
JOIN
dbo.AREA a ON a.AREA_ID = ak.AREA_ID inner JOIN
dbo.THEME t ON t.THEME_ID = a.THEME_ID full
outer join
CONTAINSTABLE(EXPERTS, *, '"applied" or "computing"') as exp_rank on
exp_rank.[KEY] = e.emplid full outer join
CONTAINSTABLE(Keywords, *, '"applied" or "computing"') as key_rank on
key_rank.[KEY] = k.key_id full outer join
CONTAINSTABLE(Area, *, '"applied" or "computing"') as area_rank on
area_rank.[KEY] = a.area_id full outer join
CONTAINSTABLE(Theme, *, '"applied" or "computing"') as theme_rank on
theme_rank.[KEY] = t.theme_id
WHERE k.key_id = key_rank.[KEY] or
e.emplid = exp_rank.[KEY] or
a.area_id = area_rank.[KEY] or
t.theme_id = theme_rank.[KEY]) as inQuery
order by inQuery.all_rank desc
Without the inQuery.all_rank in the Select part because this part
insert the double information.
This is the error Message:
Server: Msg 145, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if SELECT DISTINCT is
specified.
HELP!!!
I can't find a way !!
Thank you very much!
Cheers
Sebastian
Whenever you see an or you should ask yourself is it possible that the
values coming from the or condition could be duplicated.
So if it is a gender thing where there are three cases, M, F, and U
(unknown) there will never be any overlap in the OR condition.
And you can do union alls and get better performance.
If there is an overlap, ie where PK>10 or Gender='M' and you could have
Males with a PK >10 which would be duplicates you would have to do some sort
of funky group by like this:
--I really need to see your data to figure this out though
Select inQuery.all_rank, inQuery.employeeid,
inQuery.firstname, inQuery.familyname, inQuery.Qualifications,
inQuery.Title, inQuery.Profil, inQuery.deptname from experts,
(SELECT e.EMPLID as employeeid, e.FIRST_NAME AS firstname,
e.FAMILY_NAME AS familyname, e.QUALIFICATIONS AS Qualifications,
e.TITLE AS Title, e.LINK AS Profil,
d.NAME AS deptname, sum(ISNULL(exp_rank.[RANK], 0) +
ISNULL(key_rank.[RANK], 0) + ISNULL(area_rank.[RANK],0)
+ ISNULL(theme_rank.[RANK],0)) as all_rank FROM dbo.EXPERTS e
inner JOIN
dbo.EXP_KEY ek ON e.EMPLID = ek.EMPLID INNER JOIN
dbo.KEYWORDS k ON k.KEY_ID = ek.KEY_ID inner
JOIN
dbo.EXP_DEPT ed ON ed.EMPLID = e.EMPLID inner
JOIN
dbo.DEPARTMENT d ON d.DEPARTMENT_ID =
ed.DEPARTMENT_ID inner JOIN
dbo.AREA_KEY ak ON ak.KEY_ID = k.KEY_ID inner
JOIN
dbo.AREA a ON a.AREA_ID = ak.AREA_ID inner JOIN
dbo.THEME t ON t.THEME_ID = a.THEME_ID full
outer join
CONTAINSTABLE(EXPERTS, *, '"applied" or "computing"') as exp_rank on
exp_rank.[KEY] = e.emplid full outer join
CONTAINSTABLE(Keywords, *, '"applied" or "computing"') as key_rank on
key_rank.[KEY] = k.key_id full outer join
CONTAINSTABLE(Area, *, '"applied" or "computing"') as area_rank on
area_rank.[KEY] = a.area_id full outer join
CONTAINSTABLE(Theme, *, '"applied" or "computing"') as theme_rank on
theme_rank.[KEY] = t.theme_id
WHERE k.key_id = key_rank.[KEY] or
e.emplid = exp_rank.[KEY] or
a.area_id = area_rank.[KEY] or
t.theme_id = theme_rank.[KEY]
group by e.EMPLID, e.FIRST_NAME ,
e.FAMILY_NAME, e.QUALIFICATIONS,
e.TITLE, e.LINK,d.NAME) as inQuery
order by inQuery.all_rank desc
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Sebastian" <C_o_z_m_o@.gmx.de> wrote in message
news:1163990663.770428.118290@.m73g2000cwd.googlegr oups.com...
>I have a problem. I use Constainstable to make a fulltextsearch over 4
> tables.
> Now I want to order the result set. The problem is that if I insert the
> column all_ranks in the Select part I have some double results. That is
> because some keywords are 2 times or 3 times related to an expert.
> So If I use this statement I have double information:
> Select distinct inQuery.all_rank, inQuery.employeeid,
> inQuery.firstname, inQuery.familyname, inQuery.Qualifications,
> inQuery.Title, inQuery.Profil, inQuery.deptname from experts,
> (SELECT e.EMPLID as employeeid, e.FIRST_NAME AS firstname,
> e.FAMILY_NAME AS familyname, e.QUALIFICATIONS AS Qualifications,
> e.TITLE AS Title, e.LINK AS Profil,
> d.NAME AS deptname, ISNULL(exp_rank.[RANK], 0) +
> ISNULL(key_rank.[RANK], 0) + ISNULL(area_rank.[RANK],0)
> + ISNULL(theme_rank.[RANK],0) as all_rank FROM dbo.EXPERTS e
> inner JOIN
> dbo.EXP_KEY ek ON e.EMPLID = ek.EMPLID INNER JOIN
> dbo.KEYWORDS k ON k.KEY_ID = ek.KEY_ID inner
> JOIN
> dbo.EXP_DEPT ed ON ed.EMPLID = e.EMPLID inner
> JOIN
> dbo.DEPARTMENT d ON d.DEPARTMENT_ID =
> ed.DEPARTMENT_ID inner JOIN
> dbo.AREA_KEY ak ON ak.KEY_ID = k.KEY_ID inner
> JOIN
> dbo.AREA a ON a.AREA_ID = ak.AREA_ID inner JOIN
> dbo.THEME t ON t.THEME_ID = a.THEME_ID full
> outer join
> CONTAINSTABLE(EXPERTS, *, '"applied" or "computing"') as exp_rank on
> exp_rank.[KEY] = e.emplid full outer join
> CONTAINSTABLE(Keywords, *, '"applied" or "computing"') as key_rank on
> key_rank.[KEY] = k.key_id full outer join
> CONTAINSTABLE(Area, *, '"applied" or "computing"') as area_rank on
> area_rank.[KEY] = a.area_id full outer join
> CONTAINSTABLE(Theme, *, '"applied" or "computing"') as theme_rank on
> theme_rank.[KEY] = t.theme_id
> WHERE k.key_id = key_rank.[KEY] or
> e.emplid = exp_rank.[KEY] or
> a.area_id = area_rank.[KEY] or
> t.theme_id = theme_rank.[KEY]) as inQuery
> order by inQuery.all_rank desc
>
> What I want to use is a Statement of this kind:
>
> Select distinct inQuery.employeeid, inQuery.firstname,
> inQuery.familyname, inQuery.Qualifications, inQuery.Title,
> inQuery.Profil, inQuery.deptname from experts,
> (SELECT e.EMPLID as employeeid, e.FIRST_NAME AS firstname,
> e.FAMILY_NAME AS familyname, e.QUALIFICATIONS AS Qualifications,
> e.TITLE AS Title, e.LINK AS Profil,
> d.NAME AS deptname, ISNULL(exp_rank.[RANK], 0) +
> ISNULL(key_rank.[RANK], 0) + ISNULL(area_rank.[RANK],0)
> + ISNULL(theme_rank.[RANK],0) as all_rank FROM dbo.EXPERTS e
> inner JOIN
> dbo.EXP_KEY ek ON e.EMPLID = ek.EMPLID INNER JOIN
> dbo.KEYWORDS k ON k.KEY_ID = ek.KEY_ID inner
> JOIN
> dbo.EXP_DEPT ed ON ed.EMPLID = e.EMPLID inner
> JOIN
> dbo.DEPARTMENT d ON d.DEPARTMENT_ID =
> ed.DEPARTMENT_ID inner JOIN
> dbo.AREA_KEY ak ON ak.KEY_ID = k.KEY_ID inner
> JOIN
> dbo.AREA a ON a.AREA_ID = ak.AREA_ID inner JOIN
> dbo.THEME t ON t.THEME_ID = a.THEME_ID full
> outer join
> CONTAINSTABLE(EXPERTS, *, '"applied" or "computing"') as exp_rank on
> exp_rank.[KEY] = e.emplid full outer join
> CONTAINSTABLE(Keywords, *, '"applied" or "computing"') as key_rank on
> key_rank.[KEY] = k.key_id full outer join
> CONTAINSTABLE(Area, *, '"applied" or "computing"') as area_rank on
> area_rank.[KEY] = a.area_id full outer join
> CONTAINSTABLE(Theme, *, '"applied" or "computing"') as theme_rank on
> theme_rank.[KEY] = t.theme_id
> WHERE k.key_id = key_rank.[KEY] or
> e.emplid = exp_rank.[KEY] or
> a.area_id = area_rank.[KEY] or
> t.theme_id = theme_rank.[KEY]) as inQuery
> order by inQuery.all_rank desc
>
>
> Without the inQuery.all_rank in the Select part because this part
> insert the double information.
>
> This is the error Message:
> Server: Msg 145, Level 15, State 1, Line 1
> ORDER BY items must appear in the select list if SELECT DISTINCT is
> specified.
> HELP!!!
> I can't find a way !!
> Thank you very much!
> Cheers
> Sebastian
>
tables.
Now I want to order the result set. The problem is that if I insert the
column all_ranks in the Select part I have some double results. That is
because some keywords are 2 times or 3 times related to an expert.
So If I use this statement I have double information:
Select distinct inQuery.all_rank, inQuery.employeeid,
inQuery.firstname, inQuery.familyname, inQuery.Qualifications,
inQuery.Title, inQuery.Profil, inQuery.deptname from experts,
(SELECT e.EMPLID as employeeid, e.FIRST_NAME AS firstname,
e.FAMILY_NAME AS familyname, e.QUALIFICATIONS AS Qualifications,
e.TITLE AS Title, e.LINK AS Profil,
d.NAME AS deptname, ISNULL(exp_rank.[RANK], 0) +
ISNULL(key_rank.[RANK], 0) + ISNULL(area_rank.[RANK],0)
+ ISNULL(theme_rank.[RANK],0) as all_rank FROM dbo.EXPERTS e
inner JOIN
dbo.EXP_KEY ek ON e.EMPLID = ek.EMPLID INNER JOIN
dbo.KEYWORDS k ON k.KEY_ID = ek.KEY_ID inner
JOIN
dbo.EXP_DEPT ed ON ed.EMPLID = e.EMPLID inner
JOIN
dbo.DEPARTMENT d ON d.DEPARTMENT_ID =
ed.DEPARTMENT_ID inner JOIN
dbo.AREA_KEY ak ON ak.KEY_ID = k.KEY_ID inner
JOIN
dbo.AREA a ON a.AREA_ID = ak.AREA_ID inner JOIN
dbo.THEME t ON t.THEME_ID = a.THEME_ID full
outer join
CONTAINSTABLE(EXPERTS, *, '"applied" or "computing"') as exp_rank on
exp_rank.[KEY] = e.emplid full outer join
CONTAINSTABLE(Keywords, *, '"applied" or "computing"') as key_rank on
key_rank.[KEY] = k.key_id full outer join
CONTAINSTABLE(Area, *, '"applied" or "computing"') as area_rank on
area_rank.[KEY] = a.area_id full outer join
CONTAINSTABLE(Theme, *, '"applied" or "computing"') as theme_rank on
theme_rank.[KEY] = t.theme_id
WHERE k.key_id = key_rank.[KEY] or
e.emplid = exp_rank.[KEY] or
a.area_id = area_rank.[KEY] or
t.theme_id = theme_rank.[KEY]) as inQuery
order by inQuery.all_rank desc
What I want to use is a Statement of this kind:
Select distinct inQuery.employeeid, inQuery.firstname,
inQuery.familyname, inQuery.Qualifications, inQuery.Title,
inQuery.Profil, inQuery.deptname from experts,
(SELECT e.EMPLID as employeeid, e.FIRST_NAME AS firstname,
e.FAMILY_NAME AS familyname, e.QUALIFICATIONS AS Qualifications,
e.TITLE AS Title, e.LINK AS Profil,
d.NAME AS deptname, ISNULL(exp_rank.[RANK], 0) +
ISNULL(key_rank.[RANK], 0) + ISNULL(area_rank.[RANK],0)
+ ISNULL(theme_rank.[RANK],0) as all_rank FROM dbo.EXPERTS e
inner JOIN
dbo.EXP_KEY ek ON e.EMPLID = ek.EMPLID INNER JOIN
dbo.KEYWORDS k ON k.KEY_ID = ek.KEY_ID inner
JOIN
dbo.EXP_DEPT ed ON ed.EMPLID = e.EMPLID inner
JOIN
dbo.DEPARTMENT d ON d.DEPARTMENT_ID =
ed.DEPARTMENT_ID inner JOIN
dbo.AREA_KEY ak ON ak.KEY_ID = k.KEY_ID inner
JOIN
dbo.AREA a ON a.AREA_ID = ak.AREA_ID inner JOIN
dbo.THEME t ON t.THEME_ID = a.THEME_ID full
outer join
CONTAINSTABLE(EXPERTS, *, '"applied" or "computing"') as exp_rank on
exp_rank.[KEY] = e.emplid full outer join
CONTAINSTABLE(Keywords, *, '"applied" or "computing"') as key_rank on
key_rank.[KEY] = k.key_id full outer join
CONTAINSTABLE(Area, *, '"applied" or "computing"') as area_rank on
area_rank.[KEY] = a.area_id full outer join
CONTAINSTABLE(Theme, *, '"applied" or "computing"') as theme_rank on
theme_rank.[KEY] = t.theme_id
WHERE k.key_id = key_rank.[KEY] or
e.emplid = exp_rank.[KEY] or
a.area_id = area_rank.[KEY] or
t.theme_id = theme_rank.[KEY]) as inQuery
order by inQuery.all_rank desc
Without the inQuery.all_rank in the Select part because this part
insert the double information.
This is the error Message:
Server: Msg 145, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if SELECT DISTINCT is
specified.
HELP!!!
I can't find a way !!
Thank you very much!
Cheers
Sebastian
Whenever you see an or you should ask yourself is it possible that the
values coming from the or condition could be duplicated.
So if it is a gender thing where there are three cases, M, F, and U
(unknown) there will never be any overlap in the OR condition.
And you can do union alls and get better performance.
If there is an overlap, ie where PK>10 or Gender='M' and you could have
Males with a PK >10 which would be duplicates you would have to do some sort
of funky group by like this:
--I really need to see your data to figure this out though
Select inQuery.all_rank, inQuery.employeeid,
inQuery.firstname, inQuery.familyname, inQuery.Qualifications,
inQuery.Title, inQuery.Profil, inQuery.deptname from experts,
(SELECT e.EMPLID as employeeid, e.FIRST_NAME AS firstname,
e.FAMILY_NAME AS familyname, e.QUALIFICATIONS AS Qualifications,
e.TITLE AS Title, e.LINK AS Profil,
d.NAME AS deptname, sum(ISNULL(exp_rank.[RANK], 0) +
ISNULL(key_rank.[RANK], 0) + ISNULL(area_rank.[RANK],0)
+ ISNULL(theme_rank.[RANK],0)) as all_rank FROM dbo.EXPERTS e
inner JOIN
dbo.EXP_KEY ek ON e.EMPLID = ek.EMPLID INNER JOIN
dbo.KEYWORDS k ON k.KEY_ID = ek.KEY_ID inner
JOIN
dbo.EXP_DEPT ed ON ed.EMPLID = e.EMPLID inner
JOIN
dbo.DEPARTMENT d ON d.DEPARTMENT_ID =
ed.DEPARTMENT_ID inner JOIN
dbo.AREA_KEY ak ON ak.KEY_ID = k.KEY_ID inner
JOIN
dbo.AREA a ON a.AREA_ID = ak.AREA_ID inner JOIN
dbo.THEME t ON t.THEME_ID = a.THEME_ID full
outer join
CONTAINSTABLE(EXPERTS, *, '"applied" or "computing"') as exp_rank on
exp_rank.[KEY] = e.emplid full outer join
CONTAINSTABLE(Keywords, *, '"applied" or "computing"') as key_rank on
key_rank.[KEY] = k.key_id full outer join
CONTAINSTABLE(Area, *, '"applied" or "computing"') as area_rank on
area_rank.[KEY] = a.area_id full outer join
CONTAINSTABLE(Theme, *, '"applied" or "computing"') as theme_rank on
theme_rank.[KEY] = t.theme_id
WHERE k.key_id = key_rank.[KEY] or
e.emplid = exp_rank.[KEY] or
a.area_id = area_rank.[KEY] or
t.theme_id = theme_rank.[KEY]
group by e.EMPLID, e.FIRST_NAME ,
e.FAMILY_NAME, e.QUALIFICATIONS,
e.TITLE, e.LINK,d.NAME) as inQuery
order by inQuery.all_rank desc
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Sebastian" <C_o_z_m_o@.gmx.de> wrote in message
news:1163990663.770428.118290@.m73g2000cwd.googlegr oups.com...
>I have a problem. I use Constainstable to make a fulltextsearch over 4
> tables.
> Now I want to order the result set. The problem is that if I insert the
> column all_ranks in the Select part I have some double results. That is
> because some keywords are 2 times or 3 times related to an expert.
> So If I use this statement I have double information:
> Select distinct inQuery.all_rank, inQuery.employeeid,
> inQuery.firstname, inQuery.familyname, inQuery.Qualifications,
> inQuery.Title, inQuery.Profil, inQuery.deptname from experts,
> (SELECT e.EMPLID as employeeid, e.FIRST_NAME AS firstname,
> e.FAMILY_NAME AS familyname, e.QUALIFICATIONS AS Qualifications,
> e.TITLE AS Title, e.LINK AS Profil,
> d.NAME AS deptname, ISNULL(exp_rank.[RANK], 0) +
> ISNULL(key_rank.[RANK], 0) + ISNULL(area_rank.[RANK],0)
> + ISNULL(theme_rank.[RANK],0) as all_rank FROM dbo.EXPERTS e
> inner JOIN
> dbo.EXP_KEY ek ON e.EMPLID = ek.EMPLID INNER JOIN
> dbo.KEYWORDS k ON k.KEY_ID = ek.KEY_ID inner
> JOIN
> dbo.EXP_DEPT ed ON ed.EMPLID = e.EMPLID inner
> JOIN
> dbo.DEPARTMENT d ON d.DEPARTMENT_ID =
> ed.DEPARTMENT_ID inner JOIN
> dbo.AREA_KEY ak ON ak.KEY_ID = k.KEY_ID inner
> JOIN
> dbo.AREA a ON a.AREA_ID = ak.AREA_ID inner JOIN
> dbo.THEME t ON t.THEME_ID = a.THEME_ID full
> outer join
> CONTAINSTABLE(EXPERTS, *, '"applied" or "computing"') as exp_rank on
> exp_rank.[KEY] = e.emplid full outer join
> CONTAINSTABLE(Keywords, *, '"applied" or "computing"') as key_rank on
> key_rank.[KEY] = k.key_id full outer join
> CONTAINSTABLE(Area, *, '"applied" or "computing"') as area_rank on
> area_rank.[KEY] = a.area_id full outer join
> CONTAINSTABLE(Theme, *, '"applied" or "computing"') as theme_rank on
> theme_rank.[KEY] = t.theme_id
> WHERE k.key_id = key_rank.[KEY] or
> e.emplid = exp_rank.[KEY] or
> a.area_id = area_rank.[KEY] or
> t.theme_id = theme_rank.[KEY]) as inQuery
> order by inQuery.all_rank desc
>
> What I want to use is a Statement of this kind:
>
> Select distinct inQuery.employeeid, inQuery.firstname,
> inQuery.familyname, inQuery.Qualifications, inQuery.Title,
> inQuery.Profil, inQuery.deptname from experts,
> (SELECT e.EMPLID as employeeid, e.FIRST_NAME AS firstname,
> e.FAMILY_NAME AS familyname, e.QUALIFICATIONS AS Qualifications,
> e.TITLE AS Title, e.LINK AS Profil,
> d.NAME AS deptname, ISNULL(exp_rank.[RANK], 0) +
> ISNULL(key_rank.[RANK], 0) + ISNULL(area_rank.[RANK],0)
> + ISNULL(theme_rank.[RANK],0) as all_rank FROM dbo.EXPERTS e
> inner JOIN
> dbo.EXP_KEY ek ON e.EMPLID = ek.EMPLID INNER JOIN
> dbo.KEYWORDS k ON k.KEY_ID = ek.KEY_ID inner
> JOIN
> dbo.EXP_DEPT ed ON ed.EMPLID = e.EMPLID inner
> JOIN
> dbo.DEPARTMENT d ON d.DEPARTMENT_ID =
> ed.DEPARTMENT_ID inner JOIN
> dbo.AREA_KEY ak ON ak.KEY_ID = k.KEY_ID inner
> JOIN
> dbo.AREA a ON a.AREA_ID = ak.AREA_ID inner JOIN
> dbo.THEME t ON t.THEME_ID = a.THEME_ID full
> outer join
> CONTAINSTABLE(EXPERTS, *, '"applied" or "computing"') as exp_rank on
> exp_rank.[KEY] = e.emplid full outer join
> CONTAINSTABLE(Keywords, *, '"applied" or "computing"') as key_rank on
> key_rank.[KEY] = k.key_id full outer join
> CONTAINSTABLE(Area, *, '"applied" or "computing"') as area_rank on
> area_rank.[KEY] = a.area_id full outer join
> CONTAINSTABLE(Theme, *, '"applied" or "computing"') as theme_rank on
> theme_rank.[KEY] = t.theme_id
> WHERE k.key_id = key_rank.[KEY] or
> e.emplid = exp_rank.[KEY] or
> a.area_id = area_rank.[KEY] or
> t.theme_id = theme_rank.[KEY]) as inQuery
> order by inQuery.all_rank desc
>
>
> Without the inQuery.all_rank in the Select part because this part
> insert the double information.
>
> This is the error Message:
> Server: Msg 145, Level 15, State 1, Line 1
> ORDER BY items must appear in the select list if SELECT DISTINCT is
> specified.
> HELP!!!
> I can't find a way !!
> Thank you very much!
> Cheers
> Sebastian
>
Full-text working sporadically
I have full-text running on one database only. This index seems to have
issues about once every couple of weeks. Normally, a search can be done (via
webpage) on a keyword - and it works fine. But there are times that no
keyword (or other options) searches will work. The only option that is
searchable is "records updated in the last x number of days". So it's like
the full-text searching 1/2 works.
I have ran the stored procedure to ensure FTS was installed properly.
The MSSearch server is running under the local system account (as it should
be per MS).
I have deleted and rebuilt it complete. I have tried to rebuild the index
via the wizard and by right-clicking on the Full-Text Catalog -- neither
fixed the issue.
I've ran the stored procedure to ensure it was enabled.
Anyone have any idea why my FTS will work completely one day and work "a
little" the next?
Thanks!
Key
Can you check the gatherer logs to see if they reveal anything?
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"KEY" <key.hammonds@.gmail.com> wrote in message
news:F6E6798B-FA95-4C20-BBE7-104CE05B4B80@.microsoft.com...
>I have full-text running on one database only. This index seems to have
> issues about once every couple of weeks. Normally, a search can be done
> (via
> webpage) on a keyword - and it works fine. But there are times that no
> keyword (or other options) searches will work. The only option that is
> searchable is "records updated in the last x number of days". So it's
> like
> the full-text searching 1/2 works.
> I have ran the stored procedure to ensure FTS was installed properly.
> The MSSearch server is running under the local system account (as it
> should
> be per MS).
> I have deleted and rebuilt it complete. I have tried to rebuild the index
> via the wizard and by right-clicking on the Full-Text Catalog -- neither
> fixed the issue.
> I've ran the stored procedure to ensure it was enabled.
> Anyone have any idea why my FTS will work completely one day and work "a
> little" the next?
> Thanks!
> Key
issues about once every couple of weeks. Normally, a search can be done (via
webpage) on a keyword - and it works fine. But there are times that no
keyword (or other options) searches will work. The only option that is
searchable is "records updated in the last x number of days". So it's like
the full-text searching 1/2 works.
I have ran the stored procedure to ensure FTS was installed properly.
The MSSearch server is running under the local system account (as it should
be per MS).
I have deleted and rebuilt it complete. I have tried to rebuild the index
via the wizard and by right-clicking on the Full-Text Catalog -- neither
fixed the issue.
I've ran the stored procedure to ensure it was enabled.
Anyone have any idea why my FTS will work completely one day and work "a
little" the next?
Thanks!
Key
Can you check the gatherer logs to see if they reveal anything?
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"KEY" <key.hammonds@.gmail.com> wrote in message
news:F6E6798B-FA95-4C20-BBE7-104CE05B4B80@.microsoft.com...
>I have full-text running on one database only. This index seems to have
> issues about once every couple of weeks. Normally, a search can be done
> (via
> webpage) on a keyword - and it works fine. But there are times that no
> keyword (or other options) searches will work. The only option that is
> searchable is "records updated in the last x number of days". So it's
> like
> the full-text searching 1/2 works.
> I have ran the stored procedure to ensure FTS was installed properly.
> The MSSearch server is running under the local system account (as it
> should
> be per MS).
> I have deleted and rebuilt it complete. I have tried to rebuild the index
> via the wizard and by right-clicking on the Full-Text Catalog -- neither
> fixed the issue.
> I've ran the stored procedure to ensure it was enabled.
> Anyone have any idea why my FTS will work completely one day and work "a
> little" the next?
> Thanks!
> Key
Full-text usage?
Is it appropriate to use a full-text index and query to search CustomerName and CustomerDBA type fields? I've been told that this type of searching is meant for searching documents, but it seems that Full-text would be useful for fuzzy searching and noise word removal also. Does it make sense to index a varchar field? Does it add performace to searching over native 'Like' queries?
Thanks.
Full-text searching is great when you have multiple words in a column and you'd like to search on those individual words, or if you need to search on different tenses/forms of a word, eliminate noise words, or thesaurus capabilities. So yes, there are benefits to using it on varchar columns. As of SQL Server 2005 your full-text catalogs/indexes are included in your backups and restores so you don't have to rebuild your full-text indexes if you restore the database.
If you are searching on the full value or first few letters of the varchar, then a 'regular' index on the column would likely provide faster performance .. and in SQL Server 2005 we even improved the string searching for LIKEs that start with a wildcard.
Don
Fulltext Thesaurus
My understanding of the fulltext search using the thesaurus function is that
it will not work until the TSxxx.XML file is populated (in my case the
tsENU.xml file.)
Is this true?
We rae migrating from SQL 2000 and I could have sworn this was working there
.
Maybe I am mistaken.
I do see where the MS Office products use the MSTH3AM.LEX & MSTH3BR.LEX
dictionary files to support the thesaurus lookup there. Can these files
somehow be migrated over to SQL Server 2005 for use there or somehow be read
and converted to an XML thesaurus?
Thomas MannHere's a thought......I believe MS Word was installed on the machine that
had SQL 2000 on it.
I know that when I set up a new desktop, spellcheck & thesaurus are not
available to other applications (i.e. Outlook Express) until after MS Word i
s
installed.
Could this be the reason the Thesaurus funtion worked on the previous
machine and not the machine running SQL 2005?
--
Thomas Mann|||I have opened a trouble ticket on this issue and will continue to post
updates as this progresses (so you don't have to.)
What I have found thus far is that the standard advice of removing the
commented xml code in the tsENU.xml file does not cut it. Once you do this,
the xml tries to reference the schema (tsSchema.xml) which is missing from
the SQL Server 2005 install. I located a copy of the file from an earlier
version (SQL 2000 SP3) and that solved my missing schema problem but still
the Thesaurus function would not work (even after adding the substituion
params in tsENU.xml.)
I burned one of my support incidents from my MSDN subscription and contacted
the support center. They checked several of the machines there and could not
find the missing schema file amoungst the installed files either.
Bottom line, it sounds like this might be an install problem (or an
undocumented feature.)
I will post the fix here when I find out more.
--
Thomas Mann
it will not work until the TSxxx.XML file is populated (in my case the
tsENU.xml file.)
Is this true?
We rae migrating from SQL 2000 and I could have sworn this was working there
.
Maybe I am mistaken.
I do see where the MS Office products use the MSTH3AM.LEX & MSTH3BR.LEX
dictionary files to support the thesaurus lookup there. Can these files
somehow be migrated over to SQL Server 2005 for use there or somehow be read
and converted to an XML thesaurus?
Thomas MannHere's a thought......I believe MS Word was installed on the machine that
had SQL 2000 on it.
I know that when I set up a new desktop, spellcheck & thesaurus are not
available to other applications (i.e. Outlook Express) until after MS Word i
s
installed.
Could this be the reason the Thesaurus funtion worked on the previous
machine and not the machine running SQL 2005?
--
Thomas Mann|||I have opened a trouble ticket on this issue and will continue to post
updates as this progresses (so you don't have to.)
What I have found thus far is that the standard advice of removing the
commented xml code in the tsENU.xml file does not cut it. Once you do this,
the xml tries to reference the schema (tsSchema.xml) which is missing from
the SQL Server 2005 install. I located a copy of the file from an earlier
version (SQL 2000 SP3) and that solved my missing schema problem but still
the Thesaurus function would not work (even after adding the substituion
params in tsENU.xml.)
I burned one of my support incidents from my MSDN subscription and contacted
the support center. They checked several of the machines there and could not
find the missing schema file amoungst the installed files either.
Bottom line, it sounds like this might be an install problem (or an
undocumented feature.)
I will post the fix here when I find out more.
--
Thomas Mann
Fulltext Stops (Deadlocks Occurs)
How can I determine if I need to use the SQL Server
Fulltext services? This error messages occurs when the
CPU is greater than 80% for extended period of time.
Usually the error message occurs when the system has a
major deadlock and is using all the resources to resolve
the deadlock.
Please help me resolve this issue.
Thank You,
Mike
Error Messages from Cluster:
JobID = 881
KSName = 1838:General_EventLogV1-DBSpecial
MC MachineName = GHLDBB01A
Object Name = <NT_MachineFolder:GHLDBB01A>
EventMsg = 10 NT Events from System - batch 7
LongMsg = Typemm/dd/yyyy hh:mm:ssSource
CategoryEventUserMachineName
Description
Error4/17/2004 7:34:45 PMClusSvc
(4)1069N/AGHLDBB01A
Cluster resource 'SQL Server Fulltext (JSQL_OLTP)' failed.
Error4/17/2004 7:34:45 PMService Control Manager
None7031N/AGHLDBB01A
The Microsoft Search service terminated unexpectedly. It
has done this 5 time(s). The following corrective action
will be taken in 0 milliseconds: No action.
Error4/17/2004 7:34:46 PMClusSvc
(4)1069N/AGHLDBB01A
Cluster resource 'SQL Server Fulltext (JSQL_OLTP)' failed.
Error4/17/2004 7:36:39 PMService Control Manager
None7009N/AGHLDBB01A
Timeout (30000 milliseconds) waiting for the Microsoft
Search service to connect.
Error4/17/2004 7:36:49 PMService Control Manager
None7000N/AGHLDBB01A
The Microsoft Search service failed to start due to the
following error:
The service did not respond to the start or control
request in a timely fashion.
Error4/17/2004 7:36:50 PMDCOM
None10005ecmsvcGHLDBB01A
DCOM got error '%%%1' attempting to start the service %2
with arguments '%3'
in order to run the server:
%4
Error4/17/2004 7:38:23 PMService Control Manager
None7009N/AGHLDBB01A
Timeout (30000 milliseconds) waiting for the Microsoft
Search service to connect.
Error4/17/2004 7:38:32 PMService Control Manager
None7000N/AGHLDBB01A
The Microsoft Search service failed to start due to the
following error:
The service did not respond to the start or control
request in a timely fashion.
Error4/17/2004 7:38:38 PMDCOM
None10005ecmsvcGHLDBB01A
DCOM got error '%%%1' attempting to start the service %2
with arguments '%3'
in order to run the server:
%4
Error4/17/2004 7:38:46 PMClusSvc
(4)1069N/AGHLDBB01A
Cluster resource 'SQL Server Fulltext (JSQL_OLTP)' failed.
Mike,
First of all, can I assume that this is a SQL Server 2000 clustered
environment? Could you post the full output of SELECT @.@.version -- as this
is helpful in troubleshooting SQL FTS issues.
Secondly, could you post any "Microsoft Search" or MssCi source events
(warnings & errors) from your server JSQL_OLTP that occurred at or near the
time of the below error messages? As well as any Microsoft Search entries in
the cluster.log file.
Yes, the "Microsoft Search" (mssearch.exe) service and does use up to 90% of
CPU usage, for brief periods of time, either during shadow merge or master
merge that occurs during either Full or Incremental Populations or at
midnight. How long is the "extended period of time" that the CPU is greater
than 80%? Does the MSSearch service ever hit 100% CPU usage or does it just
peak at 80% and then go down to normal usage levels?
Also, do this server have multiple CPU's? If so, then the MSSearch service
can be "assigned" or bound to a specific or set of specific CPU's, while SQL
Server 2000 can be set to a specific set of CPU's (via sp_configure), such
that the MSSearch CPU usage will not affect your SQL Server 2000 processing.
Regards,
John
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:12c001c42642$0cd095f0$a001280a@.phx.gbl...
> How can I determine if I need to use the SQL Server
> Fulltext services? This error messages occurs when the
> CPU is greater than 80% for extended period of time.
> Usually the error message occurs when the system has a
> major deadlock and is using all the resources to resolve
> the deadlock.
> Please help me resolve this issue.
> Thank You,
> Mike
>
> Error Messages from Cluster:
> JobID = 881
> KSName = 1838:General_EventLogV1-DBSpecial
> MC MachineName = GHLDBB01A
> Object Name = <NT_MachineFolder:GHLDBB01A>
> EventMsg = 10 NT Events from System - batch 7
> LongMsg = Type mm/dd/yyyy hh:mm:ss Source
> Category Event User MachineName
> Description
> Error 4/17/2004 7:34:45 PM ClusSvc
> (4) 1069 N/A GHLDBB01A
> Cluster resource 'SQL Server Fulltext (JSQL_OLTP)' failed.
>
> Error 4/17/2004 7:34:45 PM Service Control Manager
> None 7031 N/A GHLDBB01A
> The Microsoft Search service terminated unexpectedly. It
> has done this 5 time(s). The following corrective action
> will be taken in 0 milliseconds: No action.
>
> Error 4/17/2004 7:34:46 PM ClusSvc
> (4) 1069 N/A GHLDBB01A
> Cluster resource 'SQL Server Fulltext (JSQL_OLTP)' failed.
>
> Error 4/17/2004 7:36:39 PM Service Control Manager
> None 7009 N/A GHLDBB01A
> Timeout (30000 milliseconds) waiting for the Microsoft
> Search service to connect.
>
> Error 4/17/2004 7:36:49 PM Service Control Manager
> None 7000 N/A GHLDBB01A
> The Microsoft Search service failed to start due to the
> following error:
> The service did not respond to the start or control
> request in a timely fashion.
>
> Error 4/17/2004 7:36:50 PM DCOM
> None 10005 ecmsvc GHLDBB01A
> DCOM got error '%%%1' attempting to start the service %2
> with arguments '%3'
> in order to run the server:
> %4
>
> Error 4/17/2004 7:38:23 PM Service Control Manager
> None 7009 N/A GHLDBB01A
> Timeout (30000 milliseconds) waiting for the Microsoft
> Search service to connect.
>
> Error 4/17/2004 7:38:32 PM Service Control Manager
> None 7000 N/A GHLDBB01A
> The Microsoft Search service failed to start due to the
> following error:
> The service did not respond to the start or control
> request in a timely fashion.
>
> Error 4/17/2004 7:38:38 PM DCOM
> None 10005 ecmsvc GHLDBB01A
> DCOM got error '%%%1' attempting to start the service %2
> with arguments '%3'
> in order to run the server:
> %4
>
> Error 4/17/2004 7:38:46 PM ClusSvc
> (4) 1069 N/A GHLDBB01A
> Cluster resource 'SQL Server Fulltext (JSQL_OLTP)' failed.
>
|||I tried to answer several questions listed below.
Thank You,
The server CPU can be greater than 90% for 5 - 7 minutes.
The server has 8 processors at 2 GHZs.
__________________________________________________ _________
Event Viewer Errors
Cluster resource 'SQL Server Fulltext (VSQL_OLTP)' failed.
The Microsoft Search service terminated unexpectedly. It
has done this 5 time(s). The following corrective action
will be taken in 0 milliseconds: No action.
DCOM got error "The service did not respond to the start
or control request in a timely fashion. " attempting to
start the service mssearch with arguments "" in order to
run the server:
{C731055A-AC80-11D1-8DF3-00C04FB6EF4F}
Timeout (30000 milliseconds) waiting for the Microsoft
Search service to connect.
__________________________________________________ _________
Select @.@.Version
Output:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.0 (Build 2195:
Service Pack 3)
(1 row(s) affected)
>--Original Message--
>Mike,
>First of all, can I assume that this is a SQL Server 2000
clustered
>environment? Could you post the full output of SELECT
@.@.version -- as this
>is helpful in troubleshooting SQL FTS issues.
>Secondly, could you post any "Microsoft Search" or MssCi
source events
>(warnings & errors) from your server JSQL_OLTP that
occurred at or near the
>time of the below error messages? As well as any
Microsoft Search entries in
>the cluster.log file.
>Yes, the "Microsoft Search" (mssearch.exe) service and
does use up to 90% of
>CPU usage, for brief periods of time, either during
shadow merge or master
>merge that occurs during either Full or Incremental
Populations or at
>midnight. How long is the "extended period of time" that
the CPU is greater
>than 80%? Does the MSSearch service ever hit 100% CPU
usage or does it just
>peak at 80% and then go down to normal usage levels?
>Also, do this server have multiple CPU's? If so, then the
MSSearch service
>can be "assigned" or bound to a specific or set of
specific CPU's, while SQL
>Server 2000 can be set to a specific set of CPU's (via
sp_configure), such
>that the MSSearch CPU usage will not affect your SQL
Server 2000 processing.
>Regards,
>John
>
>"Mike" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:12c001c42642$0cd095f0$a001280a@.phx.gbl...
failed.[vbcol=seagreen]
It[vbcol=seagreen]
action[vbcol=seagreen]
failed.[vbcol=seagreen]
failed.
>
>.
>
|||You're welcome, Mike,
Relative to DCOM on this server, have you made any changes to the default
DCOM configuration? Specifically, confirm that the 'Default Authentication
Level' in the DCOMCNFG's Default Properties tab is set to "None" and that
the 'Default Impersonation Level' (on the same tab) is set to "Anonymous".
Both are the default DCOM settings.
Also, I should of been more specific with the event log messages... The
"Microsoft Search" and MssCi are only recorded in the Application event log
and not the System log. Please, review the Application event log as well for
related messages.
Finally, the server's CPU usage of greater than 90% for 5 - 7 minutes, is
normal and expected during either the "shadow merge" or "Master Merge"
processes that the MSSearch service does to merge new "word lists" into it's
file system and then at the end of this process or at midnight (controllable
via a registry key). This process occurs during either a Full or Incremental
Population. Do you have frequently scheduled SQLServerAgent jobs that
execute either a Full or Incremental Population? If so, you may want to
either reduce the frequency or checkout the new SQL Server 2000 feature
"change tracking" and "update index in background" that will give you near
real-time updates of the FT Catalog when the FT-enable table(s) column(s)
are updated. Review the SQL Server 2000 BOL for more info on CT and UIiB.
Once the "Microsoft Search service terminated unexpectedly" issue is
identified and resolved, and if the MSSearch CPU usage is affecting your SQL
Server process, I can show you how to set the MSSearch process to one or
more of your 8 procs.
Regards,
John
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:138a01c4265b$cb6eeb60$a401280a@.phx.gbl...[vbcol=seagreen]
> I tried to answer several questions listed below.
> Thank You,
>
> The server CPU can be greater than 90% for 5 - 7 minutes.
> The server has 8 processors at 2 GHZs.
> __________________________________________________ _________
> Event Viewer Errors
> Cluster resource 'SQL Server Fulltext (VSQL_OLTP)' failed.
> The Microsoft Search service terminated unexpectedly. It
> has done this 5 time(s). The following corrective action
> will be taken in 0 milliseconds: No action.
> DCOM got error "The service did not respond to the start
> or control request in a timely fashion. " attempting to
> start the service mssearch with arguments "" in order to
> run the server:
> {C731055A-AC80-11D1-8DF3-00C04FB6EF4F}
> Timeout (30000 milliseconds) waiting for the Microsoft
> Search service to connect.
> __________________________________________________ _________
> Select @.@.Version
> Output:
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Enterprise Edition on Windows NT 5.0 (Build 2195:
> Service Pack 3)
>
> (1 row(s) affected)
>
>
>
> clustered
> @.@.version -- as this
> source events
> occurred at or near the
> Microsoft Search entries in
> does use up to 90% of
> shadow merge or master
> Populations or at
> the CPU is greater
> usage or does it just
> MSSearch service
> specific CPU's, while SQL
> sp_configure), such
> Server 2000 processing.
> message
> failed.
> It
> action
> failed.
> failed.
Fulltext services? This error messages occurs when the
CPU is greater than 80% for extended period of time.
Usually the error message occurs when the system has a
major deadlock and is using all the resources to resolve
the deadlock.
Please help me resolve this issue.
Thank You,
Mike
Error Messages from Cluster:
JobID = 881
KSName = 1838:General_EventLogV1-DBSpecial
MC MachineName = GHLDBB01A
Object Name = <NT_MachineFolder:GHLDBB01A>
EventMsg = 10 NT Events from System - batch 7
LongMsg = Typemm/dd/yyyy hh:mm:ssSource
CategoryEventUserMachineName
Description
Error4/17/2004 7:34:45 PMClusSvc
(4)1069N/AGHLDBB01A
Cluster resource 'SQL Server Fulltext (JSQL_OLTP)' failed.
Error4/17/2004 7:34:45 PMService Control Manager
None7031N/AGHLDBB01A
The Microsoft Search service terminated unexpectedly. It
has done this 5 time(s). The following corrective action
will be taken in 0 milliseconds: No action.
Error4/17/2004 7:34:46 PMClusSvc
(4)1069N/AGHLDBB01A
Cluster resource 'SQL Server Fulltext (JSQL_OLTP)' failed.
Error4/17/2004 7:36:39 PMService Control Manager
None7009N/AGHLDBB01A
Timeout (30000 milliseconds) waiting for the Microsoft
Search service to connect.
Error4/17/2004 7:36:49 PMService Control Manager
None7000N/AGHLDBB01A
The Microsoft Search service failed to start due to the
following error:
The service did not respond to the start or control
request in a timely fashion.
Error4/17/2004 7:36:50 PMDCOM
None10005ecmsvcGHLDBB01A
DCOM got error '%%%1' attempting to start the service %2
with arguments '%3'
in order to run the server:
%4
Error4/17/2004 7:38:23 PMService Control Manager
None7009N/AGHLDBB01A
Timeout (30000 milliseconds) waiting for the Microsoft
Search service to connect.
Error4/17/2004 7:38:32 PMService Control Manager
None7000N/AGHLDBB01A
The Microsoft Search service failed to start due to the
following error:
The service did not respond to the start or control
request in a timely fashion.
Error4/17/2004 7:38:38 PMDCOM
None10005ecmsvcGHLDBB01A
DCOM got error '%%%1' attempting to start the service %2
with arguments '%3'
in order to run the server:
%4
Error4/17/2004 7:38:46 PMClusSvc
(4)1069N/AGHLDBB01A
Cluster resource 'SQL Server Fulltext (JSQL_OLTP)' failed.
Mike,
First of all, can I assume that this is a SQL Server 2000 clustered
environment? Could you post the full output of SELECT @.@.version -- as this
is helpful in troubleshooting SQL FTS issues.
Secondly, could you post any "Microsoft Search" or MssCi source events
(warnings & errors) from your server JSQL_OLTP that occurred at or near the
time of the below error messages? As well as any Microsoft Search entries in
the cluster.log file.
Yes, the "Microsoft Search" (mssearch.exe) service and does use up to 90% of
CPU usage, for brief periods of time, either during shadow merge or master
merge that occurs during either Full or Incremental Populations or at
midnight. How long is the "extended period of time" that the CPU is greater
than 80%? Does the MSSearch service ever hit 100% CPU usage or does it just
peak at 80% and then go down to normal usage levels?
Also, do this server have multiple CPU's? If so, then the MSSearch service
can be "assigned" or bound to a specific or set of specific CPU's, while SQL
Server 2000 can be set to a specific set of CPU's (via sp_configure), such
that the MSSearch CPU usage will not affect your SQL Server 2000 processing.
Regards,
John
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:12c001c42642$0cd095f0$a001280a@.phx.gbl...
> How can I determine if I need to use the SQL Server
> Fulltext services? This error messages occurs when the
> CPU is greater than 80% for extended period of time.
> Usually the error message occurs when the system has a
> major deadlock and is using all the resources to resolve
> the deadlock.
> Please help me resolve this issue.
> Thank You,
> Mike
>
> Error Messages from Cluster:
> JobID = 881
> KSName = 1838:General_EventLogV1-DBSpecial
> MC MachineName = GHLDBB01A
> Object Name = <NT_MachineFolder:GHLDBB01A>
> EventMsg = 10 NT Events from System - batch 7
> LongMsg = Type mm/dd/yyyy hh:mm:ss Source
> Category Event User MachineName
> Description
> Error 4/17/2004 7:34:45 PM ClusSvc
> (4) 1069 N/A GHLDBB01A
> Cluster resource 'SQL Server Fulltext (JSQL_OLTP)' failed.
>
> Error 4/17/2004 7:34:45 PM Service Control Manager
> None 7031 N/A GHLDBB01A
> The Microsoft Search service terminated unexpectedly. It
> has done this 5 time(s). The following corrective action
> will be taken in 0 milliseconds: No action.
>
> Error 4/17/2004 7:34:46 PM ClusSvc
> (4) 1069 N/A GHLDBB01A
> Cluster resource 'SQL Server Fulltext (JSQL_OLTP)' failed.
>
> Error 4/17/2004 7:36:39 PM Service Control Manager
> None 7009 N/A GHLDBB01A
> Timeout (30000 milliseconds) waiting for the Microsoft
> Search service to connect.
>
> Error 4/17/2004 7:36:49 PM Service Control Manager
> None 7000 N/A GHLDBB01A
> The Microsoft Search service failed to start due to the
> following error:
> The service did not respond to the start or control
> request in a timely fashion.
>
> Error 4/17/2004 7:36:50 PM DCOM
> None 10005 ecmsvc GHLDBB01A
> DCOM got error '%%%1' attempting to start the service %2
> with arguments '%3'
> in order to run the server:
> %4
>
> Error 4/17/2004 7:38:23 PM Service Control Manager
> None 7009 N/A GHLDBB01A
> Timeout (30000 milliseconds) waiting for the Microsoft
> Search service to connect.
>
> Error 4/17/2004 7:38:32 PM Service Control Manager
> None 7000 N/A GHLDBB01A
> The Microsoft Search service failed to start due to the
> following error:
> The service did not respond to the start or control
> request in a timely fashion.
>
> Error 4/17/2004 7:38:38 PM DCOM
> None 10005 ecmsvc GHLDBB01A
> DCOM got error '%%%1' attempting to start the service %2
> with arguments '%3'
> in order to run the server:
> %4
>
> Error 4/17/2004 7:38:46 PM ClusSvc
> (4) 1069 N/A GHLDBB01A
> Cluster resource 'SQL Server Fulltext (JSQL_OLTP)' failed.
>
|||I tried to answer several questions listed below.
Thank You,
The server CPU can be greater than 90% for 5 - 7 minutes.
The server has 8 processors at 2 GHZs.
__________________________________________________ _________
Event Viewer Errors
Cluster resource 'SQL Server Fulltext (VSQL_OLTP)' failed.
The Microsoft Search service terminated unexpectedly. It
has done this 5 time(s). The following corrective action
will be taken in 0 milliseconds: No action.
DCOM got error "The service did not respond to the start
or control request in a timely fashion. " attempting to
start the service mssearch with arguments "" in order to
run the server:
{C731055A-AC80-11D1-8DF3-00C04FB6EF4F}
Timeout (30000 milliseconds) waiting for the Microsoft
Search service to connect.
__________________________________________________ _________
Select @.@.Version
Output:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.0 (Build 2195:
Service Pack 3)
(1 row(s) affected)
>--Original Message--
>Mike,
>First of all, can I assume that this is a SQL Server 2000
clustered
>environment? Could you post the full output of SELECT
@.@.version -- as this
>is helpful in troubleshooting SQL FTS issues.
>Secondly, could you post any "Microsoft Search" or MssCi
source events
>(warnings & errors) from your server JSQL_OLTP that
occurred at or near the
>time of the below error messages? As well as any
Microsoft Search entries in
>the cluster.log file.
>Yes, the "Microsoft Search" (mssearch.exe) service and
does use up to 90% of
>CPU usage, for brief periods of time, either during
shadow merge or master
>merge that occurs during either Full or Incremental
Populations or at
>midnight. How long is the "extended period of time" that
the CPU is greater
>than 80%? Does the MSSearch service ever hit 100% CPU
usage or does it just
>peak at 80% and then go down to normal usage levels?
>Also, do this server have multiple CPU's? If so, then the
MSSearch service
>can be "assigned" or bound to a specific or set of
specific CPU's, while SQL
>Server 2000 can be set to a specific set of CPU's (via
sp_configure), such
>that the MSSearch CPU usage will not affect your SQL
Server 2000 processing.
>Regards,
>John
>
>"Mike" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:12c001c42642$0cd095f0$a001280a@.phx.gbl...
failed.[vbcol=seagreen]
It[vbcol=seagreen]
action[vbcol=seagreen]
failed.[vbcol=seagreen]
failed.
>
>.
>
|||You're welcome, Mike,
Relative to DCOM on this server, have you made any changes to the default
DCOM configuration? Specifically, confirm that the 'Default Authentication
Level' in the DCOMCNFG's Default Properties tab is set to "None" and that
the 'Default Impersonation Level' (on the same tab) is set to "Anonymous".
Both are the default DCOM settings.
Also, I should of been more specific with the event log messages... The
"Microsoft Search" and MssCi are only recorded in the Application event log
and not the System log. Please, review the Application event log as well for
related messages.
Finally, the server's CPU usage of greater than 90% for 5 - 7 minutes, is
normal and expected during either the "shadow merge" or "Master Merge"
processes that the MSSearch service does to merge new "word lists" into it's
file system and then at the end of this process or at midnight (controllable
via a registry key). This process occurs during either a Full or Incremental
Population. Do you have frequently scheduled SQLServerAgent jobs that
execute either a Full or Incremental Population? If so, you may want to
either reduce the frequency or checkout the new SQL Server 2000 feature
"change tracking" and "update index in background" that will give you near
real-time updates of the FT Catalog when the FT-enable table(s) column(s)
are updated. Review the SQL Server 2000 BOL for more info on CT and UIiB.
Once the "Microsoft Search service terminated unexpectedly" issue is
identified and resolved, and if the MSSearch CPU usage is affecting your SQL
Server process, I can show you how to set the MSSearch process to one or
more of your 8 procs.
Regards,
John
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:138a01c4265b$cb6eeb60$a401280a@.phx.gbl...[vbcol=seagreen]
> I tried to answer several questions listed below.
> Thank You,
>
> The server CPU can be greater than 90% for 5 - 7 minutes.
> The server has 8 processors at 2 GHZs.
> __________________________________________________ _________
> Event Viewer Errors
> Cluster resource 'SQL Server Fulltext (VSQL_OLTP)' failed.
> The Microsoft Search service terminated unexpectedly. It
> has done this 5 time(s). The following corrective action
> will be taken in 0 milliseconds: No action.
> DCOM got error "The service did not respond to the start
> or control request in a timely fashion. " attempting to
> start the service mssearch with arguments "" in order to
> run the server:
> {C731055A-AC80-11D1-8DF3-00C04FB6EF4F}
> Timeout (30000 milliseconds) waiting for the Microsoft
> Search service to connect.
> __________________________________________________ _________
> Select @.@.Version
> Output:
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Enterprise Edition on Windows NT 5.0 (Build 2195:
> Service Pack 3)
>
> (1 row(s) affected)
>
>
>
> clustered
> @.@.version -- as this
> source events
> occurred at or near the
> Microsoft Search entries in
> does use up to 90% of
> shadow merge or master
> Populations or at
> the CPU is greater
> usage or does it just
> MSSearch service
> specific CPU's, while SQL
> sp_configure), such
> Server 2000 processing.
> message
> failed.
> It
> action
> failed.
> failed.
fulltext statistics
Hi all,
Is it possible to get statistics of any sort from the full text engine?
My understanding is SQL Server treats the the fulltext engine as a remote
server and performs distributed queries against it via oledb. AFAIK, oledb
has optional interfaces to obtain stats (cardinality, distribution) which the
fulltext engine presumably supports since it comes up with reasonable
estimates in estimated query plans involving containstable.
So using an estimated plan is one way to get cardinality estimates for a
particular query, but I'm wondering if there's a more direct approach -- e.g.
I'd really like to be able to just get a histogram of terms. Perhaps it's
possible to connect directly to the full text engine via oledb? If so, what
would the connection string look like? or...?
Thanks for any ideas.
-Geoff
This is supposed to ship in SQL 2008.
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Geoff Chappell" <GeoffChappell@.discussions.microsoft.com> wrote in message
news:95516FF5-7316-437D-9278-0463823A028B@.microsoft.com...
> Hi all,
> Is it possible to get statistics of any sort from the full text engine?
> My understanding is SQL Server treats the the fulltext engine as a remote
> server and performs distributed queries against it via oledb. AFAIK, oledb
> has optional interfaces to obtain stats (cardinality, distribution) which
> the
> fulltext engine presumably supports since it comes up with reasonable
> estimates in estimated query plans involving containstable.
> So using an estimated plan is one way to get cardinality estimates for a
> particular query, but I'm wondering if there's a more direct approach --
> e.g.
> I'd really like to be able to just get a histogram of terms. Perhaps it's
> possible to connect directly to the full text engine via oledb? If so,
> what
> would the connection string look like? or...?
> Thanks for any ideas.
> -Geoff
>
>
>
Is it possible to get statistics of any sort from the full text engine?
My understanding is SQL Server treats the the fulltext engine as a remote
server and performs distributed queries against it via oledb. AFAIK, oledb
has optional interfaces to obtain stats (cardinality, distribution) which the
fulltext engine presumably supports since it comes up with reasonable
estimates in estimated query plans involving containstable.
So using an estimated plan is one way to get cardinality estimates for a
particular query, but I'm wondering if there's a more direct approach -- e.g.
I'd really like to be able to just get a histogram of terms. Perhaps it's
possible to connect directly to the full text engine via oledb? If so, what
would the connection string look like? or...?
Thanks for any ideas.
-Geoff
This is supposed to ship in SQL 2008.
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Geoff Chappell" <GeoffChappell@.discussions.microsoft.com> wrote in message
news:95516FF5-7316-437D-9278-0463823A028B@.microsoft.com...
> Hi all,
> Is it possible to get statistics of any sort from the full text engine?
> My understanding is SQL Server treats the the fulltext engine as a remote
> server and performs distributed queries against it via oledb. AFAIK, oledb
> has optional interfaces to obtain stats (cardinality, distribution) which
> the
> fulltext engine presumably supports since it comes up with reasonable
> estimates in estimated query plans involving containstable.
> So using an estimated plan is one way to get cardinality estimates for a
> particular query, but I'm wondering if there's a more direct approach --
> e.g.
> I'd really like to be able to just get a histogram of terms. Perhaps it's
> possible to connect directly to the full text engine via oledb? If so,
> what
> would the connection string look like? or...?
> Thanks for any ideas.
> -Geoff
>
>
>
Subscribe to:
Posts (Atom)