I am no sql guru, but I have been using sql server for a few years and
have never witnessed anything like the following:
I noticed that one of my queries was very slow on a particular server
(Server B), but fast on another server (Server A). The query joined
two views (View A and View B). Something like:
SELECT *
FROM View_A INNER JOIN
View_B ON View_B.RaceId = View_A.RaceId AND View_B.MemberId =
View_A.MemberId
WHERE (View_A.RaceId = 21876)
ORDER BY View_B.[Position]
I looked at the estimated plan in QA, and found that the plans were
identical, but that on server A it was prediected to cost .0577 and on
server B 350.00, many many orders of magnitude slower! An index seek
was the problem, on Server B it was estimated to execute more than
30,000X and on server A just 2X. I could not figure out the problem,
as I believed the DB schema to be identical.
I sometimes use a tool called Embarcadero Change Manager, which can
compare database schema. I ran the tool and it said that View_A on
Server A and View_A on Server B differed. View_A on Server B had an
extra line: "ORDER BY RaceId". BUt when I right clicked on the view
and looked at the "properties" in Enterprise Manager I saw no such
"Order By RaceId".
Dropping the view and readding it did turn out to fix the problem.
Here are my questions:
1. why did EM not show the "Order by RaceId"
2. I thought an "Order by" clause is illegal in views. How the hell
did it get there (I sure don't think I put it in, but regardless EM
forbids it).
Any ideas about what was going on would be very much welcome.
Thanks!
Sincerely,
Felix
I often have seen cases when SQL Server keep using old version of a view
until you recompile it (just open in EM and click OK). Why this happen I
don't know but it is not rare.
Actually you can have order by in a view but only if you have TOP N clause.
Bojidar Alexandrov
|||Perhaps that is what happened ... although the computer had been
rebooted (not sure if this makes a difference).
Btw, I was not using a TOP N clause.
Thanks for your input.
Felix
"Bojidar Alexandrov" <bojo_do_not_spam@.kodar.net> wrote in message news:<uY2OBQ3KEHA.3684@.TK2MSFTNGP12.phx.gbl>...
> I often have seen cases when SQL Server keep using old version of a view
> until you recompile it (just open in EM and click OK). Why this happen I
> don't know but it is not rare.
> Actually you can have order by in a view but only if you have TOP N clause.
> Bojidar Alexandrov
Showing posts with label guru. Show all posts
Showing posts with label guru. Show all posts
Thursday, March 22, 2012
a very very strange sql bug (?)
I am no sql guru, but I have been using sql server for a few years and
have never witnessed anything like the following:
I noticed that one of my queries was very slow on a particular server
(Server B), but fast on another server (Server A). The query joined
two views (View A and View B). Something like:
SELECT *
FROM View_A INNER JOIN
View_B ON View_B.RaceId = View_A.RaceId AND View_B.MemberId = View_A.MemberId
WHERE (View_A.RaceId = 21876)
ORDER BY View_B.[Position]
I looked at the estimated plan in QA, and found that the plans were
identical, but that on server A it was prediected to cost .0577 and on
server B 350.00, many many orders of magnitude slower! An index seek
was the problem, on Server B it was estimated to execute more than
30,000X and on server A just 2X. I could not figure out the problem,
as I believed the DB schema to be identical.
I sometimes use a tool called Embarcadero Change Manager, which can
compare database schema. I ran the tool and it said that View_A on
Server A and View_A on Server B differed. View_A on Server B had an
extra line: "ORDER BY RaceId". BUt when I right clicked on the view
and looked at the "properties" in Enterprise Manager I saw no such
"Order By RaceId".
Dropping the view and readding it did turn out to fix the problem.
Here are my questions:
1. why did EM not show the "Order by RaceId"
2. I thought an "Order by" clause is illegal in views. How the hell
did it get there (I sure don't think I put it in, but regardless EM
forbids it).
Any ideas about what was going on would be very much welcome.
Thanks!
Sincerely,
FelixI often have seen cases when SQL Server keep using old version of a view
until you recompile it (just open in EM and click OK). Why this happen I
don't know but it is not rare.
Actually you can have order by in a view but only if you have TOP N clause.
Bojidar Alexandrov|||Perhaps that is what happened ... although the computer had been
rebooted (not sure if this makes a difference).
Btw, I was not using a TOP N clause.
Thanks for your input.
Felix
"Bojidar Alexandrov" <bojo_do_not_spam@.kodar.net> wrote in message news:<uY2OBQ3KEHA.3684@.TK2MSFTNGP12.phx.gbl>...
> I often have seen cases when SQL Server keep using old version of a view
> until you recompile it (just open in EM and click OK). Why this happen I
> don't know but it is not rare.
> Actually you can have order by in a view but only if you have TOP N clause.
> Bojidar Alexandrov
have never witnessed anything like the following:
I noticed that one of my queries was very slow on a particular server
(Server B), but fast on another server (Server A). The query joined
two views (View A and View B). Something like:
SELECT *
FROM View_A INNER JOIN
View_B ON View_B.RaceId = View_A.RaceId AND View_B.MemberId = View_A.MemberId
WHERE (View_A.RaceId = 21876)
ORDER BY View_B.[Position]
I looked at the estimated plan in QA, and found that the plans were
identical, but that on server A it was prediected to cost .0577 and on
server B 350.00, many many orders of magnitude slower! An index seek
was the problem, on Server B it was estimated to execute more than
30,000X and on server A just 2X. I could not figure out the problem,
as I believed the DB schema to be identical.
I sometimes use a tool called Embarcadero Change Manager, which can
compare database schema. I ran the tool and it said that View_A on
Server A and View_A on Server B differed. View_A on Server B had an
extra line: "ORDER BY RaceId". BUt when I right clicked on the view
and looked at the "properties" in Enterprise Manager I saw no such
"Order By RaceId".
Dropping the view and readding it did turn out to fix the problem.
Here are my questions:
1. why did EM not show the "Order by RaceId"
2. I thought an "Order by" clause is illegal in views. How the hell
did it get there (I sure don't think I put it in, but regardless EM
forbids it).
Any ideas about what was going on would be very much welcome.
Thanks!
Sincerely,
FelixI often have seen cases when SQL Server keep using old version of a view
until you recompile it (just open in EM and click OK). Why this happen I
don't know but it is not rare.
Actually you can have order by in a view but only if you have TOP N clause.
Bojidar Alexandrov|||Perhaps that is what happened ... although the computer had been
rebooted (not sure if this makes a difference).
Btw, I was not using a TOP N clause.
Thanks for your input.
Felix
"Bojidar Alexandrov" <bojo_do_not_spam@.kodar.net> wrote in message news:<uY2OBQ3KEHA.3684@.TK2MSFTNGP12.phx.gbl>...
> I often have seen cases when SQL Server keep using old version of a view
> until you recompile it (just open in EM and click OK). Why this happen I
> don't know but it is not rare.
> Actually you can have order by in a view but only if you have TOP N clause.
> Bojidar Alexandrov
a very very strange sql bug (?)
I am no sql guru, but I have been using sql server for a few years and
have never witnessed anything like the following:
I noticed that one of my queries was very slow on a particular server
(Server B), but fast on another server (Server A). The query joined
two views (View A and View B). Something like:
SELECT *
FROM View_A INNER JOIN
View_B ON View_B.RaceId = View_A.RaceId AND View_B.MemberId =
View_A.MemberId
WHERE (View_A.RaceId = 21876)
ORDER BY View_B.[Position]
I looked at the estimated plan in QA, and found that the plans were
identical, but that on server A it was prediected to cost .0577 and on
server B 350.00, many many orders of magnitude slower! An index seek
was the problem, on Server B it was estimated to execute more than
30,000X and on server A just 2X. I could not figure out the problem,
as I believed the DB schema to be identical.
I sometimes use a tool called Embarcadero Change Manager, which can
compare database schema. I ran the tool and it said that View_A on
Server A and View_A on Server B differed. View_A on Server B had an
extra line: "ORDER BY RaceId". BUt when I right clicked on the view
and looked at the "properties" in Enterprise Manager I saw no such
"Order By RaceId".
Dropping the view and readding it did turn out to fix the problem.
Here are my questions:
1. why did EM not show the "Order by RaceId"
2. I thought an "Order by" clause is illegal in views. How the hell
did it get there (I sure don't think I put it in, but regardless EM
forbids it).
Any ideas about what was going on would be very much welcome.
Thanks!
Sincerely,
FelixI often have seen cases when SQL Server keep using old version of a view
until you recompile it (just open in EM and click OK). Why this happen I
don't know but it is not rare.
Actually you can have order by in a view but only if you have TOP N clause.
Bojidar Alexandrov|||Perhaps that is what happened ... although the computer had been
rebooted (not sure if this makes a difference).
Btw, I was not using a TOP N clause.
Thanks for your input.
Felix
"Bojidar Alexandrov" <bojo_do_not_spam@.kodar.net> wrote in message news:<uY2OBQ3KEHA.3684@.TK
2MSFTNGP12.phx.gbl>...
> I often have seen cases when SQL Server keep using old version of a view
> until you recompile it (just open in EM and click OK). Why this happen I
> don't know but it is not rare.
> Actually you can have order by in a view but only if you have TOP N clause
.
> Bojidar Alexandrov
have never witnessed anything like the following:
I noticed that one of my queries was very slow on a particular server
(Server B), but fast on another server (Server A). The query joined
two views (View A and View B). Something like:
SELECT *
FROM View_A INNER JOIN
View_B ON View_B.RaceId = View_A.RaceId AND View_B.MemberId =
View_A.MemberId
WHERE (View_A.RaceId = 21876)
ORDER BY View_B.[Position]
I looked at the estimated plan in QA, and found that the plans were
identical, but that on server A it was prediected to cost .0577 and on
server B 350.00, many many orders of magnitude slower! An index seek
was the problem, on Server B it was estimated to execute more than
30,000X and on server A just 2X. I could not figure out the problem,
as I believed the DB schema to be identical.
I sometimes use a tool called Embarcadero Change Manager, which can
compare database schema. I ran the tool and it said that View_A on
Server A and View_A on Server B differed. View_A on Server B had an
extra line: "ORDER BY RaceId". BUt when I right clicked on the view
and looked at the "properties" in Enterprise Manager I saw no such
"Order By RaceId".
Dropping the view and readding it did turn out to fix the problem.
Here are my questions:
1. why did EM not show the "Order by RaceId"
2. I thought an "Order by" clause is illegal in views. How the hell
did it get there (I sure don't think I put it in, but regardless EM
forbids it).
Any ideas about what was going on would be very much welcome.
Thanks!
Sincerely,
FelixI often have seen cases when SQL Server keep using old version of a view
until you recompile it (just open in EM and click OK). Why this happen I
don't know but it is not rare.
Actually you can have order by in a view but only if you have TOP N clause.
Bojidar Alexandrov|||Perhaps that is what happened ... although the computer had been
rebooted (not sure if this makes a difference).
Btw, I was not using a TOP N clause.
Thanks for your input.
Felix
"Bojidar Alexandrov" <bojo_do_not_spam@.kodar.net> wrote in message news:<uY2OBQ3KEHA.3684@.TK
2MSFTNGP12.phx.gbl>...
> I often have seen cases when SQL Server keep using old version of a view
> until you recompile it (just open in EM and click OK). Why this happen I
> don't know but it is not rare.
> Actually you can have order by in a view but only if you have TOP N clause
.
> Bojidar Alexandrov
Sunday, March 11, 2012
A SQL query for the SQL Guru
I have a been presented with a question on writing a sql query that involves four tables. I can kind of get there but I'm missing a piece and can't figure it out. Here goes:
Table A (id is pk)
Table B (corresponding id field, not pk)
Table B has freq min and freq max fields (search based on these)
Table C (keyid is pk)
Table D (corresponding keyid field, not pk)
Table D has freq min and freq max fields (search based on these)
The ultimate goal is to get the number of records in Table A
The user selects 'name' from table C, the corresponding keyid is then used to select all the records in Table D that match.
Select freqmin, freqmax from Table D
where table d.keyid = table c.keyid
Let say the return was 2 records
Record 1
freqmin = 12
freqmax = 15
Record 2
freqmin = 18
freqmax = 21
I use the values to find the number of records in Table B that meet the following criteria: (this is where I run into a problem)
Select id
from Table B
where record 1. freqmin between table B.freqmin and table B.freqmax
and record1.freqmax between table B.freqmin and table B.freqmax
When both records are compared the id in Table B needs to be the same or else it's an invalid result.
I don't think this can be done in One Query ... if it can I'm all ears. I couldn't find a way to do it because I have no connection between Table B & Table C.
Any and all inputs are appreciated.Hi Schimelcat
A couple of questions :-
What sort of sql environment are you using (in oracle you can add sub queries in the from clause - which I find really useful when linking so many tables together) ?
What are you trying to achive? (sorry, its not that clear from the information) - it might be useful if you describe more of the columns in each table.
As a quick pointer - in your first sql you haven't specified table c in your from clause, yet you've linked to it in your where clause.
Kind regards
Keith|||select D.freqmin, D.freqmax, count(a.id) as Acount
from TableD C
inner
join TableC D
on C.keyid = D.keyid
inner
join TableB B
on D.freqmin between B.freqmin and B.freqmax
and D.freqmax between B.freqmin and B.freqmax
inner
join tableA A
on B.id = A.id
where C.name = 'userpick'
group
by D.freqmin, D.freqmax|||Hi Keith,
First let me answer the easy question. It's MS SQL talking to an Access database.
The ultimate goal is to get all the product ids from Table A
that meet the selection criteria found in Table D.
The following are the fields I'm dealing with:
Table A
Product ID(PK)
Table B
Product ID, Freqmin, Freqmax
Table C
KeyID, KeyName
Table D
KeyID(PK), Freqmin, FreqMax
If the user selects a keyname(Table C) that results in several keyid's in Table D all the values of freqmin/freqmax need to then be compared to Table B.
Let's say keyname generated a keyid of 6, I take the keyid and count how many times I find it in Table D. Lets say there are 3 records, and the values for freq min for the 3 different records are 15.5, 18, 20.1 and the values for freqmax are 17, 20, 22.5
I now have 6 values: 15.5 - 17, 18-20, 20.1-22.5
I need to look and see if I can find those six values in the "range" of the freqmin and freqmax of Table B(PK field is FeatureID).
Example:
ProductID = 4
Freqmin = 15
Freqmax = 17
ProductID = 4
Freqmin = 30
Freqmax = 31
ProductID = 4
Freqmin = 45
Freqmax = 46
I should get a return of zero records because ProductID 4 didn't meet the 18 - 20 or the 20.1 - 22.5.
If I only had one record set from Table D and that was the 15.5 - 17 then ProductID 4 would be a valid recordset.
I hope this makes sense .... I appreciate the help on trying to get this in "one" query.
Regards ! Tammy|||Gracias ! It worked great !
Originally posted by r937
select D.freqmin, D.freqmax, count(a.id) as Acount
from TableD C
inner
join TableC D
on C.keyid = D.keyid
inner
join TableB B
on D.freqmin between B.freqmin and B.freqmax
and D.freqmax between B.freqmin and B.freqmax
inner
join tableA A
on B.id = A.id
where C.name = 'userpick'
group
by D.freqmin, D.freqmax
Table A (id is pk)
Table B (corresponding id field, not pk)
Table B has freq min and freq max fields (search based on these)
Table C (keyid is pk)
Table D (corresponding keyid field, not pk)
Table D has freq min and freq max fields (search based on these)
The ultimate goal is to get the number of records in Table A
The user selects 'name' from table C, the corresponding keyid is then used to select all the records in Table D that match.
Select freqmin, freqmax from Table D
where table d.keyid = table c.keyid
Let say the return was 2 records
Record 1
freqmin = 12
freqmax = 15
Record 2
freqmin = 18
freqmax = 21
I use the values to find the number of records in Table B that meet the following criteria: (this is where I run into a problem)
Select id
from Table B
where record 1. freqmin between table B.freqmin and table B.freqmax
and record1.freqmax between table B.freqmin and table B.freqmax
When both records are compared the id in Table B needs to be the same or else it's an invalid result.
I don't think this can be done in One Query ... if it can I'm all ears. I couldn't find a way to do it because I have no connection between Table B & Table C.
Any and all inputs are appreciated.Hi Schimelcat
A couple of questions :-
What sort of sql environment are you using (in oracle you can add sub queries in the from clause - which I find really useful when linking so many tables together) ?
What are you trying to achive? (sorry, its not that clear from the information) - it might be useful if you describe more of the columns in each table.
As a quick pointer - in your first sql you haven't specified table c in your from clause, yet you've linked to it in your where clause.
Kind regards
Keith|||select D.freqmin, D.freqmax, count(a.id) as Acount
from TableD C
inner
join TableC D
on C.keyid = D.keyid
inner
join TableB B
on D.freqmin between B.freqmin and B.freqmax
and D.freqmax between B.freqmin and B.freqmax
inner
join tableA A
on B.id = A.id
where C.name = 'userpick'
group
by D.freqmin, D.freqmax|||Hi Keith,
First let me answer the easy question. It's MS SQL talking to an Access database.
The ultimate goal is to get all the product ids from Table A
that meet the selection criteria found in Table D.
The following are the fields I'm dealing with:
Table A
Product ID(PK)
Table B
Product ID, Freqmin, Freqmax
Table C
KeyID, KeyName
Table D
KeyID(PK), Freqmin, FreqMax
If the user selects a keyname(Table C) that results in several keyid's in Table D all the values of freqmin/freqmax need to then be compared to Table B.
Let's say keyname generated a keyid of 6, I take the keyid and count how many times I find it in Table D. Lets say there are 3 records, and the values for freq min for the 3 different records are 15.5, 18, 20.1 and the values for freqmax are 17, 20, 22.5
I now have 6 values: 15.5 - 17, 18-20, 20.1-22.5
I need to look and see if I can find those six values in the "range" of the freqmin and freqmax of Table B(PK field is FeatureID).
Example:
ProductID = 4
Freqmin = 15
Freqmax = 17
ProductID = 4
Freqmin = 30
Freqmax = 31
ProductID = 4
Freqmin = 45
Freqmax = 46
I should get a return of zero records because ProductID 4 didn't meet the 18 - 20 or the 20.1 - 22.5.
If I only had one record set from Table D and that was the 15.5 - 17 then ProductID 4 would be a valid recordset.
I hope this makes sense .... I appreciate the help on trying to get this in "one" query.
Regards ! Tammy|||Gracias ! It worked great !
Originally posted by r937
select D.freqmin, D.freqmax, count(a.id) as Acount
from TableD C
inner
join TableC D
on C.keyid = D.keyid
inner
join TableB B
on D.freqmin between B.freqmin and B.freqmax
and D.freqmax between B.freqmin and B.freqmax
inner
join tableA A
on B.id = A.id
where C.name = 'userpick'
group
by D.freqmin, D.freqmax
A Special Thank you
I want to thank Brett Kaiser and Pat Phelan for advising me to get the "The Guru's Guide to Transcact-SQL" HOLY MOLY what an awesome book and the software that comes with it is so awesome "Sequin". This book is better then any of the Microsoft books, it breaks it all down for you and doesnt make it too difficult to understand I stayed up late last night reading this book. (which is why I'm exhausted today). This is by far the best Transact-SQL book I have ever come across.
THANKS GUYS :):):)I'm Blushing...
:p
You should also pick up ADMIN911 for SQL Server 2000 by Brian Knight
It's more of an administrators guide, but it's got lots of good stuff about security, performance, ect...mine is very dog eared|||Hey Brett...can I have your copy? ;) Apparently it's outta print, and the dude that has his listed for $80 on Amazon wants his copy more than I do... :rolleyes:
I wonder if they'll come out with another version...have you (or anyone else) heard such a thing?|||I'd really rather have Brian's SQL Server 2000 for Experienceed DBAs (http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?userid=6921cRQAhW&isbn=0072227885&itm=3) instead. I think that it is more current, and includes additional material.
-PatP|||THANKS GUYS :):):)Great zot! A lady that gets giddy reading tech. Why the heck couldn't I find any girls like that ?!?!
Then again, I shouldn't really complain... My girlfriend is nearly as geeky as I am, just in different areas (she does infrastructure and Citrix).
-PatP|||Just added both to my bookshelf on safari.|||I'd really rather have Brian's SQL Server 2000 for Experienceed DBAs (http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?userid=6921cRQAhW&isbn=0072227885&itm=3) instead. I think that it is more current, and includes additional material.
-PatP
Looks good...why do you say more current though...|||My first reason for thinking SQL Server 2000 for Experienceed DBAs is more current than Admin911: SQL Server 2000 is some of Brian's own comments about it... I didn't have any reason to question his comment.
-PatP|||Such as? At the library right now...damn if they don't have any tech books...
And I'd just as soon own my own copy...
Does Brian Speak?
THANKS GUYS :):):)I'm Blushing...
:p
You should also pick up ADMIN911 for SQL Server 2000 by Brian Knight
It's more of an administrators guide, but it's got lots of good stuff about security, performance, ect...mine is very dog eared|||Hey Brett...can I have your copy? ;) Apparently it's outta print, and the dude that has his listed for $80 on Amazon wants his copy more than I do... :rolleyes:
I wonder if they'll come out with another version...have you (or anyone else) heard such a thing?|||I'd really rather have Brian's SQL Server 2000 for Experienceed DBAs (http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?userid=6921cRQAhW&isbn=0072227885&itm=3) instead. I think that it is more current, and includes additional material.
-PatP|||THANKS GUYS :):):)Great zot! A lady that gets giddy reading tech. Why the heck couldn't I find any girls like that ?!?!
Then again, I shouldn't really complain... My girlfriend is nearly as geeky as I am, just in different areas (she does infrastructure and Citrix).
-PatP|||Just added both to my bookshelf on safari.|||I'd really rather have Brian's SQL Server 2000 for Experienceed DBAs (http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?userid=6921cRQAhW&isbn=0072227885&itm=3) instead. I think that it is more current, and includes additional material.
-PatP
Looks good...why do you say more current though...|||My first reason for thinking SQL Server 2000 for Experienceed DBAs is more current than Admin911: SQL Server 2000 is some of Brian's own comments about it... I didn't have any reason to question his comment.
-PatP|||Such as? At the library right now...damn if they don't have any tech books...
And I'd just as soon own my own copy...
Does Brian Speak?
Thursday, March 8, 2012
a server with this name already exists?
Hi SQL Guru's
I have installed sql server 2000 on a new server. Our old server was called
SQL01 and I installed the new server as SQL01 as well (but just not on the
network to avoid the name clash)
I have now removed the old SQL01 server, put the new one in and changed the
IP Address so everything is the same as the old one. When I open EM on the
server itself and try and register it as SQL01 (it is currently local) I get
the error meesage "A Server with this name already exists" and I cannot
register the server.
I can connect to it remotly fine and setup my remote EM with the name SQL01
without any problems, but as soon as I try on the server I get that error.
Firstly does anyone know how to resolve this. I think the new server has
somehow learned about the old one and has remembered it so it won't let me
add this one with the same name. I have tried rebooting it and still does
this, so it's like it's cached the setting somewher and I need to remove
that so I can regoster it correctly.
Thanks for any help.Teddy
Look at sp_addserver and sp_dropserver stored procedures in the BOL
"Teddy Smith" <teddy@.smith.com> wrote in message
news:ce2b2s$c3h$1@.pinah.connect.com.au...
> Hi SQL Guru's
> I have installed sql server 2000 on a new server. Our old server was
called
> SQL01 and I installed the new server as SQL01 as well (but just not on the
> network to avoid the name clash)
> I have now removed the old SQL01 server, put the new one in and changed
the
> IP Address so everything is the same as the old one. When I open EM on the
> server itself and try and register it as SQL01 (it is currently local) I
get
> the error meesage "A Server with this name already exists" and I cannot
> register the server.
> I can connect to it remotly fine and setup my remote EM with the name
SQL01
> without any problems, but as soon as I try on the server I get that error.
> Firstly does anyone know how to resolve this. I think the new server has
> somehow learned about the old one and has remembered it so it won't let me
> add this one with the same name. I have tried rebooting it and still does
> this, so it's like it's cached the setting somewher and I need to remove
> that so I can regoster it correctly.
> Thanks for any help.
>|||A server name can only be registered once in SEM... Delete the other server
registration, THEN add the new registration...( I mean delete the LOCAL
registration first.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Teddy Smith" <teddy@.smith.com> wrote in message
news:ce2b2s$c3h$1@.pinah.connect.com.au...
> Hi SQL Guru's
> I have installed sql server 2000 on a new server. Our old server was
called
> SQL01 and I installed the new server as SQL01 as well (but just not on the
> network to avoid the name clash)
> I have now removed the old SQL01 server, put the new one in and changed
the
> IP Address so everything is the same as the old one. When I open EM on the
> server itself and try and register it as SQL01 (it is currently local) I
get
> the error meesage "A Server with this name already exists" and I cannot
> register the server.
> I can connect to it remotly fine and setup my remote EM with the name
SQL01
> without any problems, but as soon as I try on the server I get that error.
> Firstly does anyone know how to resolve this. I think the new server has
> somehow learned about the old one and has remembered it so it won't let me
> add this one with the same name. I have tried rebooting it and still does
> this, so it's like it's cached the setting somewher and I need to remove
> that so I can regoster it correctly.
> Thanks for any help.
>
I have installed sql server 2000 on a new server. Our old server was called
SQL01 and I installed the new server as SQL01 as well (but just not on the
network to avoid the name clash)
I have now removed the old SQL01 server, put the new one in and changed the
IP Address so everything is the same as the old one. When I open EM on the
server itself and try and register it as SQL01 (it is currently local) I get
the error meesage "A Server with this name already exists" and I cannot
register the server.
I can connect to it remotly fine and setup my remote EM with the name SQL01
without any problems, but as soon as I try on the server I get that error.
Firstly does anyone know how to resolve this. I think the new server has
somehow learned about the old one and has remembered it so it won't let me
add this one with the same name. I have tried rebooting it and still does
this, so it's like it's cached the setting somewher and I need to remove
that so I can regoster it correctly.
Thanks for any help.Teddy
Look at sp_addserver and sp_dropserver stored procedures in the BOL
"Teddy Smith" <teddy@.smith.com> wrote in message
news:ce2b2s$c3h$1@.pinah.connect.com.au...
> Hi SQL Guru's
> I have installed sql server 2000 on a new server. Our old server was
called
> SQL01 and I installed the new server as SQL01 as well (but just not on the
> network to avoid the name clash)
> I have now removed the old SQL01 server, put the new one in and changed
the
> IP Address so everything is the same as the old one. When I open EM on the
> server itself and try and register it as SQL01 (it is currently local) I
get
> the error meesage "A Server with this name already exists" and I cannot
> register the server.
> I can connect to it remotly fine and setup my remote EM with the name
SQL01
> without any problems, but as soon as I try on the server I get that error.
> Firstly does anyone know how to resolve this. I think the new server has
> somehow learned about the old one and has remembered it so it won't let me
> add this one with the same name. I have tried rebooting it and still does
> this, so it's like it's cached the setting somewher and I need to remove
> that so I can regoster it correctly.
> Thanks for any help.
>|||A server name can only be registered once in SEM... Delete the other server
registration, THEN add the new registration...( I mean delete the LOCAL
registration first.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Teddy Smith" <teddy@.smith.com> wrote in message
news:ce2b2s$c3h$1@.pinah.connect.com.au...
> Hi SQL Guru's
> I have installed sql server 2000 on a new server. Our old server was
called
> SQL01 and I installed the new server as SQL01 as well (but just not on the
> network to avoid the name clash)
> I have now removed the old SQL01 server, put the new one in and changed
the
> IP Address so everything is the same as the old one. When I open EM on the
> server itself and try and register it as SQL01 (it is currently local) I
get
> the error meesage "A Server with this name already exists" and I cannot
> register the server.
> I can connect to it remotly fine and setup my remote EM with the name
SQL01
> without any problems, but as soon as I try on the server I get that error.
> Firstly does anyone know how to resolve this. I think the new server has
> somehow learned about the old one and has remembered it so it won't let me
> add this one with the same name. I have tried rebooting it and still does
> this, so it's like it's cached the setting somewher and I need to remove
> that so I can regoster it correctly.
> Thanks for any help.
>
a server with this name already exists?
Hi SQL Guru's
I have installed sql server 2000 on a new server. Our old server was called
SQL01 and I installed the new server as SQL01 as well (but just not on the
network to avoid the name clash)
I have now removed the old SQL01 server, put the new one in and changed the
IP Address so everything is the same as the old one. When I open EM on the
server itself and try and register it as SQL01 (it is currently local) I get
the error meesage "A Server with this name already exists" and I cannot
register the server.
I can connect to it remotly fine and setup my remote EM with the name SQL01
without any problems, but as soon as I try on the server I get that error.
Firstly does anyone know how to resolve this. I think the new server has
somehow learned about the old one and has remembered it so it won't let me
add this one with the same name. I have tried rebooting it and still does
this, so it's like it's cached the setting somewher and I need to remove
that so I can regoster it correctly.
Thanks for any help.Teddy
Look at sp_addserver and sp_dropserver stored procedures in the BOL
"Teddy Smith" <teddy@.smith.com> wrote in message
news:ce2b2s$c3h$1@.pinah.connect.com.au...
> Hi SQL Guru's
> I have installed sql server 2000 on a new server. Our old server was
called
> SQL01 and I installed the new server as SQL01 as well (but just not on the
> network to avoid the name clash)
> I have now removed the old SQL01 server, put the new one in and changed
the
> IP Address so everything is the same as the old one. When I open EM on the
> server itself and try and register it as SQL01 (it is currently local) I
get
> the error meesage "A Server with this name already exists" and I cannot
> register the server.
> I can connect to it remotly fine and setup my remote EM with the name
SQL01
> without any problems, but as soon as I try on the server I get that error.
> Firstly does anyone know how to resolve this. I think the new server has
> somehow learned about the old one and has remembered it so it won't let me
> add this one with the same name. I have tried rebooting it and still does
> this, so it's like it's cached the setting somewher and I need to remove
> that so I can regoster it correctly.
> Thanks for any help.
>|||A server name can only be registered once in SEM... Delete the other server
registration, THEN add the new registration...( I mean delete the LOCAL
registration first.)
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Teddy Smith" <teddy@.smith.com> wrote in message
news:ce2b2s$c3h$1@.pinah.connect.com.au...
> Hi SQL Guru's
> I have installed sql server 2000 on a new server. Our old server was
called
> SQL01 and I installed the new server as SQL01 as well (but just not on the
> network to avoid the name clash)
> I have now removed the old SQL01 server, put the new one in and changed
the
> IP Address so everything is the same as the old one. When I open EM on the
> server itself and try and register it as SQL01 (it is currently local) I
get
> the error meesage "A Server with this name already exists" and I cannot
> register the server.
> I can connect to it remotly fine and setup my remote EM with the name
SQL01
> without any problems, but as soon as I try on the server I get that error.
> Firstly does anyone know how to resolve this. I think the new server has
> somehow learned about the old one and has remembered it so it won't let me
> add this one with the same name. I have tried rebooting it and still does
> this, so it's like it's cached the setting somewher and I need to remove
> that so I can regoster it correctly.
> Thanks for any help.
>
I have installed sql server 2000 on a new server. Our old server was called
SQL01 and I installed the new server as SQL01 as well (but just not on the
network to avoid the name clash)
I have now removed the old SQL01 server, put the new one in and changed the
IP Address so everything is the same as the old one. When I open EM on the
server itself and try and register it as SQL01 (it is currently local) I get
the error meesage "A Server with this name already exists" and I cannot
register the server.
I can connect to it remotly fine and setup my remote EM with the name SQL01
without any problems, but as soon as I try on the server I get that error.
Firstly does anyone know how to resolve this. I think the new server has
somehow learned about the old one and has remembered it so it won't let me
add this one with the same name. I have tried rebooting it and still does
this, so it's like it's cached the setting somewher and I need to remove
that so I can regoster it correctly.
Thanks for any help.Teddy
Look at sp_addserver and sp_dropserver stored procedures in the BOL
"Teddy Smith" <teddy@.smith.com> wrote in message
news:ce2b2s$c3h$1@.pinah.connect.com.au...
> Hi SQL Guru's
> I have installed sql server 2000 on a new server. Our old server was
called
> SQL01 and I installed the new server as SQL01 as well (but just not on the
> network to avoid the name clash)
> I have now removed the old SQL01 server, put the new one in and changed
the
> IP Address so everything is the same as the old one. When I open EM on the
> server itself and try and register it as SQL01 (it is currently local) I
get
> the error meesage "A Server with this name already exists" and I cannot
> register the server.
> I can connect to it remotly fine and setup my remote EM with the name
SQL01
> without any problems, but as soon as I try on the server I get that error.
> Firstly does anyone know how to resolve this. I think the new server has
> somehow learned about the old one and has remembered it so it won't let me
> add this one with the same name. I have tried rebooting it and still does
> this, so it's like it's cached the setting somewher and I need to remove
> that so I can regoster it correctly.
> Thanks for any help.
>|||A server name can only be registered once in SEM... Delete the other server
registration, THEN add the new registration...( I mean delete the LOCAL
registration first.)
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Teddy Smith" <teddy@.smith.com> wrote in message
news:ce2b2s$c3h$1@.pinah.connect.com.au...
> Hi SQL Guru's
> I have installed sql server 2000 on a new server. Our old server was
called
> SQL01 and I installed the new server as SQL01 as well (but just not on the
> network to avoid the name clash)
> I have now removed the old SQL01 server, put the new one in and changed
the
> IP Address so everything is the same as the old one. When I open EM on the
> server itself and try and register it as SQL01 (it is currently local) I
get
> the error meesage "A Server with this name already exists" and I cannot
> register the server.
> I can connect to it remotly fine and setup my remote EM with the name
SQL01
> without any problems, but as soon as I try on the server I get that error.
> Firstly does anyone know how to resolve this. I think the new server has
> somehow learned about the old one and has remembered it so it won't let me
> add this one with the same name. I have tried rebooting it and still does
> this, so it's like it's cached the setting somewher and I need to remove
> that so I can regoster it correctly.
> Thanks for any help.
>
Subscribe to:
Posts (Atom)