I changed a column on an existing table to be a Identity column but when I
try to insert a record I get an error that the id already exists. Is there a
way for me to change the value so identity returns a higher #?Are you passing the identity value?
AMB
"Joe" wrote:
> I changed a column on an existing table to be a Identity column but when I
> try to insert a record I get an error that the id already exists. Is there
a
> way for me to change the value so identity returns a higher #?
>
>|||You could use identity_insert to insert a row with the highest value
and then turn it off. Any inserts after that will increment sequentially.
ie.
create table test ( id int identity (1,1), col varchar(10) )
insert into test (col) values ('row1')
insert into test (col) values ('row2')
insert into test (col) values ('row3')
set identity_insert test on
insert into test (id, col) values (10,'row4')
set identity_insert test off
insert into test (col) values ('row5')
select * from test
"Joe" <J_no_spam@._no_spam_Fishinbrain.com> wrote in message
news:O5mJERHEFHA.2700@.TK2MSFTNGP14.phx.gbl...
> I changed a column on an existing table to be a Identity column but when I
> try to insert a record I get an error that the id already exists. Is there
a
> way for me to change the value so identity returns a higher #?
>|||Joe,
Look at the DBCC CHECKIDENT command in the Books Online. If your table
containing the identity column was called jobs,
DBCC CHECKIDENT (jobs, RESEED, 30)
would force the new value to 30.
On Fri, 11 Feb 2005 14:59:57 -0500, "Joe"
<J_no_spam@._no_spam_Fishinbrain.com> wrote:
>I changed a column on an existing table to be a Identity column but when I
>try to insert a record I get an error that the id already exists. Is there
a
>way for me to change the value so identity returns a higher #?
>
Showing posts with label error. Show all posts
Showing posts with label error. Show all posts
Tuesday, March 27, 2012
Change From when using xp_sendmail
I am writing a stored procedure that will send an email, using xp_sendmail, in the error handling if the procedure fails. However, I want the from and return address to list a shared mailbox, so that any questions from the user are directed back to the work queue. Can this be done?
DavidLook into CDO or xp_smtp_sendmail. (http://www.sqldev.net/download/xp/80/xpsmtp80-v1.1.0.8.zip) It's much easier and more flexible (bofem)
DavidLook into CDO or xp_smtp_sendmail. (http://www.sqldev.net/download/xp/80/xpsmtp80-v1.1.0.8.zip) It's much easier and more flexible (bofem)
Change from Enterprise Edition to Developer Edition
We want to install SQL Server 2005 Developer on a server that has SQL Server
2000 Enterprise edition and we are getting an error of you can't upgrade from
Enterprise to Developer. The server is not supporting production and has
never supported production; it is strictly for development activities. Is
there a way we can do that?
Thanks,
Joseph
You can uninstall Enterprise and install Developer. It will not delete any
user dbs but you should have proper backups of all dbs and scripts or a plan
to recreate logins etc if needed.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"SQLGuru_not" <SQLGurunot@.discussions.microsoft.com> wrote in message
news:0E3E5B6A-27ED-46B2-AA2E-F4E31363E859@.microsoft.com...
> We want to install SQL Server 2005 Developer on a server that has SQL
> Server
> 2000 Enterprise edition and we are getting an error of you can't upgrade
> from
> Enterprise to Developer. The server is not supporting production and has
> never supported production; it is strictly for development activities. Is
> there a way we can do that?
> Thanks,
> Joseph
|||Thanks Andrew. I wanted to avoid uninstalling 2000 because I've never been
able to uninstall it cleanly. Also wanted to see how the upgrade in-place
works for 2005. I want to know if there's a way I can downgrade the
Enterprise to Developer and then upgrade.
Joseph
"Andrew J. Kelly" wrote:
> You can uninstall Enterprise and install Developer. It will not delete any
> user dbs but you should have proper backups of all dbs and scripts or a plan
> to recreate logins etc if needed.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "SQLGuru_not" <SQLGurunot@.discussions.microsoft.com> wrote in message
> news:0E3E5B6A-27ED-46B2-AA2E-F4E31363E859@.microsoft.com...
>
|||Not that I know of. I am pretty sure it will require you to uninstall EE.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"SQLGuru_not" <SQLGurunot@.discussions.microsoft.com> wrote in message
news:D5495DB1-1592-4339-85EA-3536AA8A81BA@.microsoft.com...[vbcol=seagreen]
> Thanks Andrew. I wanted to avoid uninstalling 2000 because I've never
> been
> able to uninstall it cleanly. Also wanted to see how the upgrade in-place
> works for 2005. I want to know if there's a way I can downgrade the
> Enterprise to Developer and then upgrade.
> Joseph
> "Andrew J. Kelly" wrote:
|||There is no such a path is available because one of the edition is for
production\business purposes and the other one is for totally different
purpose which is development\test.
Here' s a list of available upgrade paths:
http://technet.microsoft.com/en-us/library/ms143393.aspx
Ekrem ?nsoy
"SQLGuru_not" <SQLGurunot@.discussions.microsoft.com> wrote in message
news:D5495DB1-1592-4339-85EA-3536AA8A81BA@.microsoft.com...[vbcol=seagreen]
> Thanks Andrew. I wanted to avoid uninstalling 2000 because I've never
> been
> able to uninstall it cleanly. Also wanted to see how the upgrade in-place
> works for 2005. I want to know if there's a way I can downgrade the
> Enterprise to Developer and then upgrade.
> Joseph
> "Andrew J. Kelly" wrote:
2000 Enterprise edition and we are getting an error of you can't upgrade from
Enterprise to Developer. The server is not supporting production and has
never supported production; it is strictly for development activities. Is
there a way we can do that?
Thanks,
Joseph
You can uninstall Enterprise and install Developer. It will not delete any
user dbs but you should have proper backups of all dbs and scripts or a plan
to recreate logins etc if needed.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"SQLGuru_not" <SQLGurunot@.discussions.microsoft.com> wrote in message
news:0E3E5B6A-27ED-46B2-AA2E-F4E31363E859@.microsoft.com...
> We want to install SQL Server 2005 Developer on a server that has SQL
> Server
> 2000 Enterprise edition and we are getting an error of you can't upgrade
> from
> Enterprise to Developer. The server is not supporting production and has
> never supported production; it is strictly for development activities. Is
> there a way we can do that?
> Thanks,
> Joseph
|||Thanks Andrew. I wanted to avoid uninstalling 2000 because I've never been
able to uninstall it cleanly. Also wanted to see how the upgrade in-place
works for 2005. I want to know if there's a way I can downgrade the
Enterprise to Developer and then upgrade.
Joseph
"Andrew J. Kelly" wrote:
> You can uninstall Enterprise and install Developer. It will not delete any
> user dbs but you should have proper backups of all dbs and scripts or a plan
> to recreate logins etc if needed.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "SQLGuru_not" <SQLGurunot@.discussions.microsoft.com> wrote in message
> news:0E3E5B6A-27ED-46B2-AA2E-F4E31363E859@.microsoft.com...
>
|||Not that I know of. I am pretty sure it will require you to uninstall EE.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"SQLGuru_not" <SQLGurunot@.discussions.microsoft.com> wrote in message
news:D5495DB1-1592-4339-85EA-3536AA8A81BA@.microsoft.com...[vbcol=seagreen]
> Thanks Andrew. I wanted to avoid uninstalling 2000 because I've never
> been
> able to uninstall it cleanly. Also wanted to see how the upgrade in-place
> works for 2005. I want to know if there's a way I can downgrade the
> Enterprise to Developer and then upgrade.
> Joseph
> "Andrew J. Kelly" wrote:
|||There is no such a path is available because one of the edition is for
production\business purposes and the other one is for totally different
purpose which is development\test.
Here' s a list of available upgrade paths:
http://technet.microsoft.com/en-us/library/ms143393.aspx
Ekrem ?nsoy
"SQLGuru_not" <SQLGurunot@.discussions.microsoft.com> wrote in message
news:D5495DB1-1592-4339-85EA-3536AA8A81BA@.microsoft.com...[vbcol=seagreen]
> Thanks Andrew. I wanted to avoid uninstalling 2000 because I've never
> been
> able to uninstall it cleanly. Also wanted to see how the upgrade in-place
> works for 2005. I want to know if there's a way I can downgrade the
> Enterprise to Developer and then upgrade.
> Joseph
> "Andrew J. Kelly" wrote:
Change from Enterprise Edition to Developer Edition
We want to install SQL Server 2005 Developer on a server that has SQL Server
2000 Enterprise edition and we are getting an error of you can't upgrade fro
m
Enterprise to Developer. The server is not supporting production and has
never supported production; it is strictly for development activities. Is
there a way we can do that?
Thanks,
JosephYou can uninstall Enterprise and install Developer. It will not delete any
user dbs but you should have proper backups of all dbs and scripts or a plan
to recreate logins etc if needed.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"SQLGuru_not" <SQLGurunot@.discussions.microsoft.com> wrote in message
news:0E3E5B6A-27ED-46B2-AA2E-F4E31363E859@.microsoft.com...
> We want to install SQL Server 2005 Developer on a server that has SQL
> Server
> 2000 Enterprise edition and we are getting an error of you can't upgrade
> from
> Enterprise to Developer. The server is not supporting production and has
> never supported production; it is strictly for development activities. Is
> there a way we can do that?
> Thanks,
> Joseph|||Thanks Andrew. I wanted to avoid uninstalling 2000 because I've never been
able to uninstall it cleanly. Also wanted to see how the upgrade in-place
works for 2005. I want to know if there's a way I can downgrade the
Enterprise to Developer and then upgrade.
Joseph
"Andrew J. Kelly" wrote:
> You can uninstall Enterprise and install Developer. It will not delete any
> user dbs but you should have proper backups of all dbs and scripts or a pl
an
> to recreate logins etc if needed.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "SQLGuru_not" <SQLGurunot@.discussions.microsoft.com> wrote in message
> news:0E3E5B6A-27ED-46B2-AA2E-F4E31363E859@.microsoft.com...
>|||Not that I know of. I am pretty sure it will require you to uninstall EE.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"SQLGuru_not" <SQLGurunot@.discussions.microsoft.com> wrote in message
news:D5495DB1-1592-4339-85EA-3536AA8A81BA@.microsoft.com...[vbcol=seagreen]
> Thanks Andrew. I wanted to avoid uninstalling 2000 because I've never
> been
> able to uninstall it cleanly. Also wanted to see how the upgrade in-place
> works for 2005. I want to know if there's a way I can downgrade the
> Enterprise to Developer and then upgrade.
> Joseph
> "Andrew J. Kelly" wrote:
>|||There is no such a path is available because one of the edition is for
production\business purposes and the other one is for totally different
purpose which is development\test.
Here' s a list of available upgrade paths:
http://technet.microsoft.com/en-us/...y/ms143393.aspx
Ekrem ?nsoy
"SQLGuru_not" <SQLGurunot@.discussions.microsoft.com> wrote in message
news:D5495DB1-1592-4339-85EA-3536AA8A81BA@.microsoft.com...[vbcol=seagreen]
> Thanks Andrew. I wanted to avoid uninstalling 2000 because I've never
> been
> able to uninstall it cleanly. Also wanted to see how the upgrade in-place
> works for 2005. I want to know if there's a way I can downgrade the
> Enterprise to Developer and then upgrade.
> Joseph
> "Andrew J. Kelly" wrote:
>sql
2000 Enterprise edition and we are getting an error of you can't upgrade fro
m
Enterprise to Developer. The server is not supporting production and has
never supported production; it is strictly for development activities. Is
there a way we can do that?
Thanks,
JosephYou can uninstall Enterprise and install Developer. It will not delete any
user dbs but you should have proper backups of all dbs and scripts or a plan
to recreate logins etc if needed.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"SQLGuru_not" <SQLGurunot@.discussions.microsoft.com> wrote in message
news:0E3E5B6A-27ED-46B2-AA2E-F4E31363E859@.microsoft.com...
> We want to install SQL Server 2005 Developer on a server that has SQL
> Server
> 2000 Enterprise edition and we are getting an error of you can't upgrade
> from
> Enterprise to Developer. The server is not supporting production and has
> never supported production; it is strictly for development activities. Is
> there a way we can do that?
> Thanks,
> Joseph|||Thanks Andrew. I wanted to avoid uninstalling 2000 because I've never been
able to uninstall it cleanly. Also wanted to see how the upgrade in-place
works for 2005. I want to know if there's a way I can downgrade the
Enterprise to Developer and then upgrade.
Joseph
"Andrew J. Kelly" wrote:
> You can uninstall Enterprise and install Developer. It will not delete any
> user dbs but you should have proper backups of all dbs and scripts or a pl
an
> to recreate logins etc if needed.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "SQLGuru_not" <SQLGurunot@.discussions.microsoft.com> wrote in message
> news:0E3E5B6A-27ED-46B2-AA2E-F4E31363E859@.microsoft.com...
>|||Not that I know of. I am pretty sure it will require you to uninstall EE.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"SQLGuru_not" <SQLGurunot@.discussions.microsoft.com> wrote in message
news:D5495DB1-1592-4339-85EA-3536AA8A81BA@.microsoft.com...[vbcol=seagreen]
> Thanks Andrew. I wanted to avoid uninstalling 2000 because I've never
> been
> able to uninstall it cleanly. Also wanted to see how the upgrade in-place
> works for 2005. I want to know if there's a way I can downgrade the
> Enterprise to Developer and then upgrade.
> Joseph
> "Andrew J. Kelly" wrote:
>|||There is no such a path is available because one of the edition is for
production\business purposes and the other one is for totally different
purpose which is development\test.
Here' s a list of available upgrade paths:
http://technet.microsoft.com/en-us/...y/ms143393.aspx
Ekrem ?nsoy
"SQLGuru_not" <SQLGurunot@.discussions.microsoft.com> wrote in message
news:D5495DB1-1592-4339-85EA-3536AA8A81BA@.microsoft.com...[vbcol=seagreen]
> Thanks Andrew. I wanted to avoid uninstalling 2000 because I've never
> been
> able to uninstall it cleanly. Also wanted to see how the upgrade in-place
> works for 2005. I want to know if there's a way I can downgrade the
> Enterprise to Developer and then upgrade.
> Joseph
> "Andrew J. Kelly" wrote:
>sql
Change from Enterprise Edition to Developer Edition
We want to install SQL Server 2005 Developer on a server that has SQL Server
2000 Enterprise edition and we are getting an error of you can't upgrade from
Enterprise to Developer. The server is not supporting production and has
never supported production; it is strictly for development activities. Is
there a way we can do that?
Thanks,
JosephYou can uninstall Enterprise and install Developer. It will not delete any
user dbs but you should have proper backups of all dbs and scripts or a plan
to recreate logins etc if needed.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"SQLGuru_not" <SQLGurunot@.discussions.microsoft.com> wrote in message
news:0E3E5B6A-27ED-46B2-AA2E-F4E31363E859@.microsoft.com...
> We want to install SQL Server 2005 Developer on a server that has SQL
> Server
> 2000 Enterprise edition and we are getting an error of you can't upgrade
> from
> Enterprise to Developer. The server is not supporting production and has
> never supported production; it is strictly for development activities. Is
> there a way we can do that?
> Thanks,
> Joseph|||Thanks Andrew. I wanted to avoid uninstalling 2000 because I've never been
able to uninstall it cleanly. Also wanted to see how the upgrade in-place
works for 2005. I want to know if there's a way I can downgrade the
Enterprise to Developer and then upgrade.
Joseph
"Andrew J. Kelly" wrote:
> You can uninstall Enterprise and install Developer. It will not delete any
> user dbs but you should have proper backups of all dbs and scripts or a plan
> to recreate logins etc if needed.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "SQLGuru_not" <SQLGurunot@.discussions.microsoft.com> wrote in message
> news:0E3E5B6A-27ED-46B2-AA2E-F4E31363E859@.microsoft.com...
> > We want to install SQL Server 2005 Developer on a server that has SQL
> > Server
> > 2000 Enterprise edition and we are getting an error of you can't upgrade
> > from
> > Enterprise to Developer. The server is not supporting production and has
> > never supported production; it is strictly for development activities. Is
> > there a way we can do that?
> >
> > Thanks,
> >
> > Joseph
>|||Not that I know of. I am pretty sure it will require you to uninstall EE.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"SQLGuru_not" <SQLGurunot@.discussions.microsoft.com> wrote in message
news:D5495DB1-1592-4339-85EA-3536AA8A81BA@.microsoft.com...
> Thanks Andrew. I wanted to avoid uninstalling 2000 because I've never
> been
> able to uninstall it cleanly. Also wanted to see how the upgrade in-place
> works for 2005. I want to know if there's a way I can downgrade the
> Enterprise to Developer and then upgrade.
> Joseph
> "Andrew J. Kelly" wrote:
>> You can uninstall Enterprise and install Developer. It will not delete
>> any
>> user dbs but you should have proper backups of all dbs and scripts or a
>> plan
>> to recreate logins etc if needed.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "SQLGuru_not" <SQLGurunot@.discussions.microsoft.com> wrote in message
>> news:0E3E5B6A-27ED-46B2-AA2E-F4E31363E859@.microsoft.com...
>> > We want to install SQL Server 2005 Developer on a server that has SQL
>> > Server
>> > 2000 Enterprise edition and we are getting an error of you can't
>> > upgrade
>> > from
>> > Enterprise to Developer. The server is not supporting production and
>> > has
>> > never supported production; it is strictly for development activities.
>> > Is
>> > there a way we can do that?
>> >
>> > Thanks,
>> >
>> > Joseph
>>|||There is no such a path is available because one of the edition is for
production\business purposes and the other one is for totally different
purpose which is development\test.
Here' s a list of available upgrade paths:
http://technet.microsoft.com/en-us/library/ms143393.aspx
--
Ekrem Ã?nsoy
"SQLGuru_not" <SQLGurunot@.discussions.microsoft.com> wrote in message
news:D5495DB1-1592-4339-85EA-3536AA8A81BA@.microsoft.com...
> Thanks Andrew. I wanted to avoid uninstalling 2000 because I've never
> been
> able to uninstall it cleanly. Also wanted to see how the upgrade in-place
> works for 2005. I want to know if there's a way I can downgrade the
> Enterprise to Developer and then upgrade.
> Joseph
> "Andrew J. Kelly" wrote:
>> You can uninstall Enterprise and install Developer. It will not delete
>> any
>> user dbs but you should have proper backups of all dbs and scripts or a
>> plan
>> to recreate logins etc if needed.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "SQLGuru_not" <SQLGurunot@.discussions.microsoft.com> wrote in message
>> news:0E3E5B6A-27ED-46B2-AA2E-F4E31363E859@.microsoft.com...
>> > We want to install SQL Server 2005 Developer on a server that has SQL
>> > Server
>> > 2000 Enterprise edition and we are getting an error of you can't
>> > upgrade
>> > from
>> > Enterprise to Developer. The server is not supporting production and
>> > has
>> > never supported production; it is strictly for development activities.
>> > Is
>> > there a way we can do that?
>> >
>> > Thanks,
>> >
>> > Joseph
>>
2000 Enterprise edition and we are getting an error of you can't upgrade from
Enterprise to Developer. The server is not supporting production and has
never supported production; it is strictly for development activities. Is
there a way we can do that?
Thanks,
JosephYou can uninstall Enterprise and install Developer. It will not delete any
user dbs but you should have proper backups of all dbs and scripts or a plan
to recreate logins etc if needed.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"SQLGuru_not" <SQLGurunot@.discussions.microsoft.com> wrote in message
news:0E3E5B6A-27ED-46B2-AA2E-F4E31363E859@.microsoft.com...
> We want to install SQL Server 2005 Developer on a server that has SQL
> Server
> 2000 Enterprise edition and we are getting an error of you can't upgrade
> from
> Enterprise to Developer. The server is not supporting production and has
> never supported production; it is strictly for development activities. Is
> there a way we can do that?
> Thanks,
> Joseph|||Thanks Andrew. I wanted to avoid uninstalling 2000 because I've never been
able to uninstall it cleanly. Also wanted to see how the upgrade in-place
works for 2005. I want to know if there's a way I can downgrade the
Enterprise to Developer and then upgrade.
Joseph
"Andrew J. Kelly" wrote:
> You can uninstall Enterprise and install Developer. It will not delete any
> user dbs but you should have proper backups of all dbs and scripts or a plan
> to recreate logins etc if needed.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "SQLGuru_not" <SQLGurunot@.discussions.microsoft.com> wrote in message
> news:0E3E5B6A-27ED-46B2-AA2E-F4E31363E859@.microsoft.com...
> > We want to install SQL Server 2005 Developer on a server that has SQL
> > Server
> > 2000 Enterprise edition and we are getting an error of you can't upgrade
> > from
> > Enterprise to Developer. The server is not supporting production and has
> > never supported production; it is strictly for development activities. Is
> > there a way we can do that?
> >
> > Thanks,
> >
> > Joseph
>|||Not that I know of. I am pretty sure it will require you to uninstall EE.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"SQLGuru_not" <SQLGurunot@.discussions.microsoft.com> wrote in message
news:D5495DB1-1592-4339-85EA-3536AA8A81BA@.microsoft.com...
> Thanks Andrew. I wanted to avoid uninstalling 2000 because I've never
> been
> able to uninstall it cleanly. Also wanted to see how the upgrade in-place
> works for 2005. I want to know if there's a way I can downgrade the
> Enterprise to Developer and then upgrade.
> Joseph
> "Andrew J. Kelly" wrote:
>> You can uninstall Enterprise and install Developer. It will not delete
>> any
>> user dbs but you should have proper backups of all dbs and scripts or a
>> plan
>> to recreate logins etc if needed.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "SQLGuru_not" <SQLGurunot@.discussions.microsoft.com> wrote in message
>> news:0E3E5B6A-27ED-46B2-AA2E-F4E31363E859@.microsoft.com...
>> > We want to install SQL Server 2005 Developer on a server that has SQL
>> > Server
>> > 2000 Enterprise edition and we are getting an error of you can't
>> > upgrade
>> > from
>> > Enterprise to Developer. The server is not supporting production and
>> > has
>> > never supported production; it is strictly for development activities.
>> > Is
>> > there a way we can do that?
>> >
>> > Thanks,
>> >
>> > Joseph
>>|||There is no such a path is available because one of the edition is for
production\business purposes and the other one is for totally different
purpose which is development\test.
Here' s a list of available upgrade paths:
http://technet.microsoft.com/en-us/library/ms143393.aspx
--
Ekrem Ã?nsoy
"SQLGuru_not" <SQLGurunot@.discussions.microsoft.com> wrote in message
news:D5495DB1-1592-4339-85EA-3536AA8A81BA@.microsoft.com...
> Thanks Andrew. I wanted to avoid uninstalling 2000 because I've never
> been
> able to uninstall it cleanly. Also wanted to see how the upgrade in-place
> works for 2005. I want to know if there's a way I can downgrade the
> Enterprise to Developer and then upgrade.
> Joseph
> "Andrew J. Kelly" wrote:
>> You can uninstall Enterprise and install Developer. It will not delete
>> any
>> user dbs but you should have proper backups of all dbs and scripts or a
>> plan
>> to recreate logins etc if needed.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "SQLGuru_not" <SQLGurunot@.discussions.microsoft.com> wrote in message
>> news:0E3E5B6A-27ED-46B2-AA2E-F4E31363E859@.microsoft.com...
>> > We want to install SQL Server 2005 Developer on a server that has SQL
>> > Server
>> > 2000 Enterprise edition and we are getting an error of you can't
>> > upgrade
>> > from
>> > Enterprise to Developer. The server is not supporting production and
>> > has
>> > never supported production; it is strictly for development activities.
>> > Is
>> > there a way we can do that?
>> >
>> > Thanks,
>> >
>> > Joseph
>>
Sunday, March 25, 2012
Change FK error: "Unable to delete relationship..."
Hello,
I have three tables in my existing database, (in a SQL Server 2000 server).
- a "Students" table,
- a "Classes" table, and
- a "StudentIsInClass" table.
The "StudentIsInClass" table is the relationship table linking a record in
the Students table to a record in the Classes table. This is effected with
two foreign keys:
FK_StudentIsInClass_Students
FK_StudentIsInClass_Classes
Recently, there was a problem in our application when changing the
"StudentNumber" for a Student. This is the primary key and so I get an erro
r
because of the relationship. The fix I want to apply is to change the
foreign key FK_StudentIsInClass_Students. I need to turn on the function of
"Cascade Update Related Fields". I use the GUI functions in SQL Server
Enterprise Manager to do this but, when I try to save the change, I get an
error:
'Students' table
- Unable to delete relationship 'FK_StudentIsInClass_Students'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL
Server]'FK_StudentIsInClass_Students' ''
[Microsoft][ODBC SQL Server Driver][SQL Server]'''
(The ' result because my SQL Server is running on a foreign language
server, so I can't read the exact error text.)
Does anyone have a clue why I would get this error? Any suggestions for
what to do about it? I appreciate any advice and help.
Many thanks in advance,
JustinJustin Little (JustinLittle@.discussions.microsoft.com) writes:
> Recently, there was a problem in our application when changing the
> "StudentNumber" for a Student. This is the primary key and so I get an
> error because of the relationship. The fix I want to apply is to change
> the foreign key FK_StudentIsInClass_Students. I need to turn on the
> function of "Cascade Update Related Fields". I use the GUI functions in
> SQL Server Enterprise Manager to do this but, when I try to save the
> change, I get an error:
> 'Students' table
> - Unable to delete relationship 'FK_StudentIsInClass_Students'.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL
> Server]'FK_StudentIsInClass_Students' ''
> [Microsoft][ODBC SQL Server Driver][SQL Server]'''
> (The ' result because my SQL Server is running on a foreign language
> server, so I can't read the exact error text.)
> Does anyone have a clue why I would get this error? Any suggestions for
> what to do about it? I appreciate any advice and help.
It would certainly have helped to see the text of the error message.
I assume that the foreign language does not use the Latin script?
Anyway, rather than using Enterprise Manager for chaning tables, which
is dangerous as the table designer is buggy, use Query Analyzer and
type the DLL.
DECLARE @.err int
BEGIN TRANSACTION
ALTER TABLE StudentsIsInClass DROP CONSTRAINT FK_StudentIsInClass_Students
SELECT @.err = @.@.error
IF @.err = 0
BEGIN
ALTER TABLE StudentsIsInClass ADD
CONSTRAINT FK_StudentIsInClass_Students FOREIGN KEY (StudentID)
REFERECENS Students(StudentID)
ON UPDATE CASCADE
ON DELETE CASCADE
SELECT @.err = @.@.error
END
IF @.err = 0
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION
It is not unlikely that that this will fail too, but at least in that
case you will see an error number. You can then look up that number in
SQL Server Books Online, provided that you have an English version of
Books Online installed on your computer.
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|||Hi Erland,
Many thanks for your advice on using Query Analyzer to get a readable error
message. (Thanks also for the tip about bugs in the Enterprise Manager GUI,
I'll bare that in mind in the future.)
I took your SQL procedure and used it in Query Analyzer. I find that there
is an error that occurs when I try to execute the line that reads "ALTER
TABLE StudentIsInClass DROP CONSTRAINT FK_StudentIsInClass_Students".
The error number I get is Msg 3728.
Server: Msg 3728, Level 16, State 1, Line 1
'dbo.FK_StudentIsInClass_Students' 並不是條件約束。
The server, as you guessed, does not use a Latin script. It's Chinese, so
the error message comes through in Chinese. On Query Analyzer I can see the
Chinese characters instead of a bunch of question marks. Books Online gives
the English message as:
'%.*ls' is not a constraint.
This seems strange to me. Also in Query Analyzer, in the Object Browser
I've expanded the tree beneath my "StudentIsInClass" table, and opened the
Constraints sub-section. There are three constraints listed there.
- PK_StudentIsInClass (StudentNumber, ClassID)
- FK_StudentIsInClass_Students (StudentNumber)
- FK_StudentIsInClass_Classes (ClassID)
If I try to right-click and delete any of these relationships I get the same
error message. (In fact, I can't delete any of constraints on any tables in
my entire database!)
Do you have any idea why I'm getting this confusing message?
Many thanks for your support.
Justin
PS: I'm connecting to the database using a user id called "bigbyte",
supplied to me by the ISP. The tables all belong to this user id (e.g.
"bigbyte.StudentIsInClass"), however I notice that the constraints are
members of the dbo group (e.g. "dbo.FK_StudentIsInClass_Students"). Do you
think that this could be the cause of my current problems?|||Justin Little (JustinLittle@.discussions.microsoft.com) writes:
> The error number I get is Msg 3728.
> Server: Msg 3728, Level 16, State 1, Line 1
> 'dbo.FK_StudentIsInClass_Students' ''
> The server, as you guessed, does not use a Latin script. It's Chinese,
> so the error message comes through in Chinese. On Query Analyzer I can
> see the Chinese characters instead of a bunch of question marks. Books
> Online gives the English message as: '%.*ls' is not a constraint.
Now, that was an interesting message...
> PS: I'm connecting to the database using a user id called "bigbyte",
> supplied to me by the ISP. The tables all belong to this user id (e.g.
> "bigbyte.StudentIsInClass"), however I notice that the constraints are
> members of the dbo group (e.g. "dbo.FK_StudentIsInClass_Students"). Do
> you think that this could be the cause of my current problems?
Most probably. I've tried to recreate this situation, but I was not
successful. When I create tables owned by a non-dbo user, and set up
a constraint, the constraint is owned by that user. The same happens
if I create the tables as owned by dbo, and then change ownership.
Maybe there was a bug in some previous version/service pack of SQL Server
that could cause this situation.
Since this is an abnormal situation, it is difficult to give sugestions
that I know work. I would try to change ownership of the table to
dbo, and then back to bigbyte.
You may have to talk to your ISP about this, as you may need someone
with dbo rights to perform this operation.
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|||Hi Erland,
I got dbo rights from the ISP for the day. I tried what you suggested about
changing the ownership of the table. Now I'm no SQL Server guru but I found
out that the stored procedure for doing this is called sp_changeobjectowner.
So I tried running the following command:
EXEC sp_changeobjectowner 'StudentIsInClass', 'dbo'
I get an error message saying:
Server: Msg 15505, Level 16, State 1, Procedure sp_changeobjectowner, Line 6
3
無法變更物件 'StudentIsInClass' 的擁有人或其所屬的_物件
_為新的擁有人 'dbo' 已有相同名稱的物件。
or in English:
Cannot change owner of object '%ls' or one of its child objects because the
new owner '%ls' already has an object with the same name.
HOWEVER, when I try to run the same sp on a table that has no constraints,
it runs through just fine and changes the ownership. Could this mean that
the database has some inconsistencies in the constraints that it is storing?
When I first created these tables (about a year or more ago) I used the
"bigbyte" user. Later, I noticed that all the ownerships had changed to
"dbo". I guessed that the ISP had been mucking around and changed it all fo
r
reasons best known to them. (Like I said, I'm no guru so I don't know what
all this ownership stuff is really about anyway.) However, later I started
having trouble, particularly in using DTS scripts to export this live data t
o
my "dev" database and I asked them to change the ownership back to "bigbyte"
.
Now it seems all my tables are owned by "bigbyte" but all the constraints
are owned by "dbo".
Is there a stored procedure or something I can use to try to track down
multiple instances of the same constraint name, possibly owned by different
owners? Perhaps my original constraints are still bouncing around somewhere
inside the db's system tables, unknown to all of us?
Many thanks,
Justin
"Erland Sommarskog" wrote:
> Justin Little (JustinLittle@.discussions.microsoft.com) writes:
> Now, that was an interesting message...
>
> Most probably. I've tried to recreate this situation, but I was not
> successful. When I create tables owned by a non-dbo user, and set up
> a constraint, the constraint is owned by that user. The same happens
> if I create the tables as owned by dbo, and then change ownership.
> Maybe there was a bug in some previous version/service pack of SQL Server
> that could cause this situation.
> Since this is an abnormal situation, it is difficult to give sugestions
> that I know work. I would try to change ownership of the table to
> dbo, and then back to bigbyte.
> You may have to talk to your ISP about this, as you may need someone
> with dbo rights to perform this operation.
> --
> 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
>|||Justin Little (JustinLittle@.discussions.microsoft.com) writes:
> I got dbo rights from the ISP for the day. I tried what you suggested
> about changing the ownership of the table. Now I'm no SQL Server guru
> but I found out that the stored procedure for doing this is called
> sp_changeobjectowner.
> So I tried running the following command:
> EXEC sp_changeobjectowner 'StudentIsInClass', 'dbo'
> I get an error message saying:
> Server: Msg 15505, Level 16, State 1, Procedure sp_changeobjectowner,
> Line 63 ' 'StudentIsInClass' '''' 'dbo' ''
> or in English: Cannot change owner of object '%ls' or one of its child
> objects because the new owner '%ls' already has an object with the same
> name.
> HOWEVER, when I try to run the same sp on a table that has no
> constraints, it runs through just fine and changes the ownership. Could
> this mean that the database has some inconsistencies in the constraints
> that it is storing?
You bet!
At this point, I would create a new set of tables copy all data over to
the new tables.
If you don't have a script saved for creating the tables, you can use
the Enterprise Manager to generate scripts. Make sure that you get triggers,
indexes and foreign keys.
Now, let's see what is the best way to have these two sets of tables in
parallel. I think the best is that youy edit the script so that all
table and trigger names are prefixed by "guest.". Constraints should not
be prefixed. Then use INSERT to copy data over. Take the occassion to add
ON UPDATE CASCADE.
Once this is done drop the existing tables - and let's hope *that* is
possible.
Finally, run sp_changeobjectowner to change ownership to bigbyte or dbo,
which ever you feel best.
> When I first created these tables (about a year or more ago) I used the
> "bigbyte" user. Later, I noticed that all the ownerships had changed to
> "dbo". I guessed that the ISP had been mucking around and changed it
> all for reasons best known to them. (Like I said, I'm no guru so I
> don't know what all this ownership stuff is really about anyway.)
Ownership of objects is like ownership of files in a file system.
But there is a second side of the coin. In SQL 2000, an owner is also a
schema. (This changes in SQL 2005). A schema is essentially a namespec.
A user has a default schema, which in SQL 2000 is always equal to his
user name. When you say "SELECT * FROM tbl" without specifying schema/owner,
SQL Server first looks in your default schema, then in the default schema of
the database, which in SQL 2000 always is dbo.
The guest user is a pre-defined user, and also has a schema, which I
suggested that you should use above, to permit you to have two sets of
tables at the same time.
I should add that in SQL 2000, having all objects owner by dbo - and thus
in the dbo schema - is what people do 99% of the time.
> Is there a stored procedure or something I can use to try to track down
> multiple instances of the same constraint name, possibly owned by
> different owners? Perhaps my original constraints are still bouncing
> around somewhere inside the db's system tables, unknown to all of us?
You would have to run queries on sysobjects to find out. But this database
seems a bit mashed, so you will need some brute force to get it working.
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
I have three tables in my existing database, (in a SQL Server 2000 server).
- a "Students" table,
- a "Classes" table, and
- a "StudentIsInClass" table.
The "StudentIsInClass" table is the relationship table linking a record in
the Students table to a record in the Classes table. This is effected with
two foreign keys:
FK_StudentIsInClass_Students
FK_StudentIsInClass_Classes
Recently, there was a problem in our application when changing the
"StudentNumber" for a Student. This is the primary key and so I get an erro
r
because of the relationship. The fix I want to apply is to change the
foreign key FK_StudentIsInClass_Students. I need to turn on the function of
"Cascade Update Related Fields". I use the GUI functions in SQL Server
Enterprise Manager to do this but, when I try to save the change, I get an
error:
'Students' table
- Unable to delete relationship 'FK_StudentIsInClass_Students'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL
Server]'FK_StudentIsInClass_Students' ''
[Microsoft][ODBC SQL Server Driver][SQL Server]'''
(The ' result because my SQL Server is running on a foreign language
server, so I can't read the exact error text.)
Does anyone have a clue why I would get this error? Any suggestions for
what to do about it? I appreciate any advice and help.
Many thanks in advance,
JustinJustin Little (JustinLittle@.discussions.microsoft.com) writes:
> Recently, there was a problem in our application when changing the
> "StudentNumber" for a Student. This is the primary key and so I get an
> error because of the relationship. The fix I want to apply is to change
> the foreign key FK_StudentIsInClass_Students. I need to turn on the
> function of "Cascade Update Related Fields". I use the GUI functions in
> SQL Server Enterprise Manager to do this but, when I try to save the
> change, I get an error:
> 'Students' table
> - Unable to delete relationship 'FK_StudentIsInClass_Students'.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL
> Server]'FK_StudentIsInClass_Students' ''
> [Microsoft][ODBC SQL Server Driver][SQL Server]'''
> (The ' result because my SQL Server is running on a foreign language
> server, so I can't read the exact error text.)
> Does anyone have a clue why I would get this error? Any suggestions for
> what to do about it? I appreciate any advice and help.
It would certainly have helped to see the text of the error message.
I assume that the foreign language does not use the Latin script?
Anyway, rather than using Enterprise Manager for chaning tables, which
is dangerous as the table designer is buggy, use Query Analyzer and
type the DLL.
DECLARE @.err int
BEGIN TRANSACTION
ALTER TABLE StudentsIsInClass DROP CONSTRAINT FK_StudentIsInClass_Students
SELECT @.err = @.@.error
IF @.err = 0
BEGIN
ALTER TABLE StudentsIsInClass ADD
CONSTRAINT FK_StudentIsInClass_Students FOREIGN KEY (StudentID)
REFERECENS Students(StudentID)
ON UPDATE CASCADE
ON DELETE CASCADE
SELECT @.err = @.@.error
END
IF @.err = 0
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION
It is not unlikely that that this will fail too, but at least in that
case you will see an error number. You can then look up that number in
SQL Server Books Online, provided that you have an English version of
Books Online installed on your computer.
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|||Hi Erland,
Many thanks for your advice on using Query Analyzer to get a readable error
message. (Thanks also for the tip about bugs in the Enterprise Manager GUI,
I'll bare that in mind in the future.)
I took your SQL procedure and used it in Query Analyzer. I find that there
is an error that occurs when I try to execute the line that reads "ALTER
TABLE StudentIsInClass DROP CONSTRAINT FK_StudentIsInClass_Students".
The error number I get is Msg 3728.
Server: Msg 3728, Level 16, State 1, Line 1
'dbo.FK_StudentIsInClass_Students' 並不是條件約束。
The server, as you guessed, does not use a Latin script. It's Chinese, so
the error message comes through in Chinese. On Query Analyzer I can see the
Chinese characters instead of a bunch of question marks. Books Online gives
the English message as:
'%.*ls' is not a constraint.
This seems strange to me. Also in Query Analyzer, in the Object Browser
I've expanded the tree beneath my "StudentIsInClass" table, and opened the
Constraints sub-section. There are three constraints listed there.
- PK_StudentIsInClass (StudentNumber, ClassID)
- FK_StudentIsInClass_Students (StudentNumber)
- FK_StudentIsInClass_Classes (ClassID)
If I try to right-click and delete any of these relationships I get the same
error message. (In fact, I can't delete any of constraints on any tables in
my entire database!)
Do you have any idea why I'm getting this confusing message?
Many thanks for your support.
Justin
PS: I'm connecting to the database using a user id called "bigbyte",
supplied to me by the ISP. The tables all belong to this user id (e.g.
"bigbyte.StudentIsInClass"), however I notice that the constraints are
members of the dbo group (e.g. "dbo.FK_StudentIsInClass_Students"). Do you
think that this could be the cause of my current problems?|||Justin Little (JustinLittle@.discussions.microsoft.com) writes:
> The error number I get is Msg 3728.
> Server: Msg 3728, Level 16, State 1, Line 1
> 'dbo.FK_StudentIsInClass_Students' ''
> The server, as you guessed, does not use a Latin script. It's Chinese,
> so the error message comes through in Chinese. On Query Analyzer I can
> see the Chinese characters instead of a bunch of question marks. Books
> Online gives the English message as: '%.*ls' is not a constraint.
Now, that was an interesting message...
> PS: I'm connecting to the database using a user id called "bigbyte",
> supplied to me by the ISP. The tables all belong to this user id (e.g.
> "bigbyte.StudentIsInClass"), however I notice that the constraints are
> members of the dbo group (e.g. "dbo.FK_StudentIsInClass_Students"). Do
> you think that this could be the cause of my current problems?
Most probably. I've tried to recreate this situation, but I was not
successful. When I create tables owned by a non-dbo user, and set up
a constraint, the constraint is owned by that user. The same happens
if I create the tables as owned by dbo, and then change ownership.
Maybe there was a bug in some previous version/service pack of SQL Server
that could cause this situation.
Since this is an abnormal situation, it is difficult to give sugestions
that I know work. I would try to change ownership of the table to
dbo, and then back to bigbyte.
You may have to talk to your ISP about this, as you may need someone
with dbo rights to perform this operation.
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|||Hi Erland,
I got dbo rights from the ISP for the day. I tried what you suggested about
changing the ownership of the table. Now I'm no SQL Server guru but I found
out that the stored procedure for doing this is called sp_changeobjectowner.
So I tried running the following command:
EXEC sp_changeobjectowner 'StudentIsInClass', 'dbo'
I get an error message saying:
Server: Msg 15505, Level 16, State 1, Procedure sp_changeobjectowner, Line 6
3
無法變更物件 'StudentIsInClass' 的擁有人或其所屬的_物件
_為新的擁有人 'dbo' 已有相同名稱的物件。
or in English:
Cannot change owner of object '%ls' or one of its child objects because the
new owner '%ls' already has an object with the same name.
HOWEVER, when I try to run the same sp on a table that has no constraints,
it runs through just fine and changes the ownership. Could this mean that
the database has some inconsistencies in the constraints that it is storing?
When I first created these tables (about a year or more ago) I used the
"bigbyte" user. Later, I noticed that all the ownerships had changed to
"dbo". I guessed that the ISP had been mucking around and changed it all fo
r
reasons best known to them. (Like I said, I'm no guru so I don't know what
all this ownership stuff is really about anyway.) However, later I started
having trouble, particularly in using DTS scripts to export this live data t
o
my "dev" database and I asked them to change the ownership back to "bigbyte"
.
Now it seems all my tables are owned by "bigbyte" but all the constraints
are owned by "dbo".
Is there a stored procedure or something I can use to try to track down
multiple instances of the same constraint name, possibly owned by different
owners? Perhaps my original constraints are still bouncing around somewhere
inside the db's system tables, unknown to all of us?
Many thanks,
Justin
"Erland Sommarskog" wrote:
> Justin Little (JustinLittle@.discussions.microsoft.com) writes:
> Now, that was an interesting message...
>
> Most probably. I've tried to recreate this situation, but I was not
> successful. When I create tables owned by a non-dbo user, and set up
> a constraint, the constraint is owned by that user. The same happens
> if I create the tables as owned by dbo, and then change ownership.
> Maybe there was a bug in some previous version/service pack of SQL Server
> that could cause this situation.
> Since this is an abnormal situation, it is difficult to give sugestions
> that I know work. I would try to change ownership of the table to
> dbo, and then back to bigbyte.
> You may have to talk to your ISP about this, as you may need someone
> with dbo rights to perform this operation.
> --
> 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
>|||Justin Little (JustinLittle@.discussions.microsoft.com) writes:
> I got dbo rights from the ISP for the day. I tried what you suggested
> about changing the ownership of the table. Now I'm no SQL Server guru
> but I found out that the stored procedure for doing this is called
> sp_changeobjectowner.
> So I tried running the following command:
> EXEC sp_changeobjectowner 'StudentIsInClass', 'dbo'
> I get an error message saying:
> Server: Msg 15505, Level 16, State 1, Procedure sp_changeobjectowner,
> Line 63 ' 'StudentIsInClass' '''' 'dbo' ''
> or in English: Cannot change owner of object '%ls' or one of its child
> objects because the new owner '%ls' already has an object with the same
> name.
> HOWEVER, when I try to run the same sp on a table that has no
> constraints, it runs through just fine and changes the ownership. Could
> this mean that the database has some inconsistencies in the constraints
> that it is storing?
You bet!
At this point, I would create a new set of tables copy all data over to
the new tables.
If you don't have a script saved for creating the tables, you can use
the Enterprise Manager to generate scripts. Make sure that you get triggers,
indexes and foreign keys.
Now, let's see what is the best way to have these two sets of tables in
parallel. I think the best is that youy edit the script so that all
table and trigger names are prefixed by "guest.". Constraints should not
be prefixed. Then use INSERT to copy data over. Take the occassion to add
ON UPDATE CASCADE.
Once this is done drop the existing tables - and let's hope *that* is
possible.
Finally, run sp_changeobjectowner to change ownership to bigbyte or dbo,
which ever you feel best.
> When I first created these tables (about a year or more ago) I used the
> "bigbyte" user. Later, I noticed that all the ownerships had changed to
> "dbo". I guessed that the ISP had been mucking around and changed it
> all for reasons best known to them. (Like I said, I'm no guru so I
> don't know what all this ownership stuff is really about anyway.)
Ownership of objects is like ownership of files in a file system.
But there is a second side of the coin. In SQL 2000, an owner is also a
schema. (This changes in SQL 2005). A schema is essentially a namespec.
A user has a default schema, which in SQL 2000 is always equal to his
user name. When you say "SELECT * FROM tbl" without specifying schema/owner,
SQL Server first looks in your default schema, then in the default schema of
the database, which in SQL 2000 always is dbo.
The guest user is a pre-defined user, and also has a schema, which I
suggested that you should use above, to permit you to have two sets of
tables at the same time.
I should add that in SQL 2000, having all objects owner by dbo - and thus
in the dbo schema - is what people do 99% of the time.
> Is there a stored procedure or something I can use to try to track down
> multiple instances of the same constraint name, possibly owned by
> different owners? Perhaps my original constraints are still bouncing
> around somewhere inside the db's system tables, unknown to all of us?
You would have to run queries on sysobjects to find out. But this database
seems a bit mashed, so you will need some brute force to get it working.
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
Change error logging for sys.sp_MSrepl_raiseerror
I have merge replication setup to a bunch of mobile subscribers. I want everything managed from the server and I want everything to run continuously. The reason we want this is that we want subscribers to automatically synchronize data within 60 seconds of plugging into the network from any location. We do not want to write anything to have to detect connectivity and initiate it. We do not want it initiated from a subscriber either. This creates a SEVERE logging issue and has halted a roll out at 2 different customers. We can't get beyond about 60 subscribers on a system that was running 3700 subscribers in SQL Server 2000. The server quite literally freezes and becomes non-responsive 1 - 2 times per day. We've tracked this back to the volume of logging into the Windows event log that is being caused by the replication engine.
When subscribers are disconnected, they throw constant errors, to the tune of 1 error every 2 minutes into the SQL Server error log as well as the Windows event log. This is because someone decided that these should be level 18 errors. There is no error. The publisher could not contact the subscriber, so I want it to simply log an error, shut up, go back to sleep, and then try again. I do NOT want a message in my SQL Server error log and I do NOT want a message in my Windows event log.
We are currently logging over 60,000 messages per day into both the Windows event log and the SQL Server error logs for something that we KNOW is a NORMAL operational state of the system. The merge agent doesn't have a parameter that I can feed it to ignore 14151 errors.
The culprit is in sys.sp_MSrepl_raiseerror. Since it is a system object, I can't override it and change the severity level of the error. So, right now we are stuck and the SQL Server error log as well as Windows event logs are being rendered quite useless on the system.
The offending code is:
raiserror (14151, 18, -1, @.agent, @.agent_name, @.message)
Does anyone have any idea how I can forcefully change sys.sp_MSrepl_raiseerror or in some other way suppress the logging of 14151 errors from the Windows and SQL Server logs?
More information.
We have continuous, push merge replication setup to a bunch of subscribers that routinely disconnect. It is merge replication, because we need to make changes at both publisher and subscriber in a disconnected model and this is exactly what merge replication was designed to do. It is setup as push, because we want all agents to be run from the distributor for centralized management and monitoring. It is continuous, because we want as low of latency as possible and for any subscriber that plugs in to begin synchronizing as soon as possible. In otherwords, we have a normal configuration, with very normal and reasonable requirements and we are using the merge engine in precisely the way it was designed and intended to be used.
However, it seems that running all of your agents at the distributor is absolutely incompatible with having disconnected subscribers with merge replication. It is quite literally impossible to run any architecture of any size with merge replication that has disconnected subscribers, unless you are running your agents at the subscriber and are then forced to manage them at the subscriber.
How did I reach this? Quite simply. replmerg.exe is the merge replication engine. It is coded to detect when a subscriber can not be contacted. (A normal state when a subscriber is disconnected.) When it detects one of these, it makes a call to sp_MSadd_merge_history90 and passes it a bunch of parameters, including a value of 6 for the run status. (This is coded directly into replmerg.exe and there is NO way to override this behavior.) When you pass a value of 6 for the run status to sp_MSadd_merge_history90, the last section of code makes a call to sp_MSrepl_raiserror and passes the value of 6 for the run status to it. When you pass a value of 6 to this procedure, it causes a level 18 error message of 14151 to be raised. Since anything level 16 and above is considered fatal, it writes to the SQL Server error log as well as the Windows event log.
Now, this is where the idiocy of this. The merge agent doesn't shut down when this error is thrown. It in fact goes to sleep, wakes up 1 minutes later, and goes through the same routine again. If the error was so fatal, why would the merge engine continue to run? Because, the NORMAL operational state of merge replication is to HAVE a subscriber disconnected.
So, we now have a situation where the merge engine is designed to run with disconnected subscribers as well as when those subscribers are disconnected, the merge engine throws level 18 error messages into the error log and Windows event log, and then goes to sleep so that it can continue to throw level 18 error messages for a situation that it was designed for. What does this mean? It means that as you add in several dozen subscribers, you get quite literally thousands of fatal error messages thrown into the event and error logs. Somewhere around the 60 subscriber mark, you throw fatal error messages at such as pace that the server itself quite literally freezes because of all of the error logging that it is forced to do based on a normal operational state.
So, from my testing thus far, using merge replication with push subscriptions is only suitable it you have 40 of fewer subscribers that are connected a majority of the time. If they are disconnected more than 50% of the time, I can only get about 30 subscribers running in a stable mode without causing the server to lock up. If I turn this around and run the agents at the subscriber where I lose any centralized management of replication, I can scale to well over 400 subscribers. Same publication, same subscription, same hosts. In both of these tests, I'm using multiple instances of SQL Server Express edition on a pool of 5 physical machines. I have ZERO data moving, the only activity happening is the replication engine simply trying to connect and poll for changes. In all of the push cases, the publisher locks up and becomes unresponsive requiring a reboot. The only discernable difference is that when agents are offloaded to subscribers, I don't throw level 18 errors into the event log and error log. Before someone says that it's also because I'm offloading the merge agent, I lock up the publisher in a push mode when I go beyond 30 subscribers who are mostly disconnected. However, I can also load up 100+ subscribers on a single machine in a pull mode (more than 3 times the number of agents as I could get in a push mode) without having a machine lock up. We've traced the cause of the server freeze back to the amount of error logging that is being forced on it and nearly 100% of the error logging is occuring from the single state of having a subscriber disconnected.
So, I've currently halted a couple of major customer upgrades of in excess of 5,000 processors, because this single issue would cause their environments to melt to the ground long before we ever go them upgraded. I have a deployment halted at another customer and one up in the air at a 4th customer. In all cases, I'm starting to evaluate 3rd party products to replace the replication engine, because it very clearly does NOT scale in a very normal configuration.
Thursday, March 22, 2012
change default errorlog retention
How can I save more errorlogs for SQL Server. Right now i believe the
default is 1 current and 6 archive logs.
How can I change it to 10 or 20 error logs ?
Hi
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ MSSQLServer\NumErrorLogs
registry entry of DWORD data type
(path may change if you have a named instance)
By default the key is not here.
BEWARE: Incorrectly changing the registry may result in your system becoming
unusable. Test your actions on a non-critical installation before you do it
on a critical system.
--
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/
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:edltj$5aFHA.720@.TK2MSFTNGP15.phx.gbl...
> How can I save more errorlogs for SQL Server. Right now i believe the
> default is 1 current and 6 archive logs.
> How can I change it to 10 or 20 error logs ?
>
|||Right click on SQL Server Logs in Enterprise Manager (under the Management
folder) and select Configure. You can select the number of logs there.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:edltj$5aFHA.720@.TK2MSFTNGP15.phx.gbl...
> How can I save more errorlogs for SQL Server. Right now i believe the
> default is 1 current and 6 archive logs.
> How can I change it to 10 or 20 error logs ?
>
sql
default is 1 current and 6 archive logs.
How can I change it to 10 or 20 error logs ?
Hi
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ MSSQLServer\NumErrorLogs
registry entry of DWORD data type
(path may change if you have a named instance)
By default the key is not here.
BEWARE: Incorrectly changing the registry may result in your system becoming
unusable. Test your actions on a non-critical installation before you do it
on a critical system.
--
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/
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:edltj$5aFHA.720@.TK2MSFTNGP15.phx.gbl...
> How can I save more errorlogs for SQL Server. Right now i believe the
> default is 1 current and 6 archive logs.
> How can I change it to 10 or 20 error logs ?
>
|||Right click on SQL Server Logs in Enterprise Manager (under the Management
folder) and select Configure. You can select the number of logs there.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:edltj$5aFHA.720@.TK2MSFTNGP15.phx.gbl...
> How can I save more errorlogs for SQL Server. Right now i believe the
> default is 1 current and 6 archive logs.
> How can I change it to 10 or 20 error logs ?
>
sql
change default errorlog retention
How can I save more errorlogs for SQL Server. Right now i believe the
default is 1 current and 6 archive logs.
How can I change it to 10 or 20 error logs ?Hi
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\NumErrorLogs
registry entry of DWORD data type
(path may change if you have a named instance)
By default the key is not here.
BEWARE: Incorrectly changing the registry may result in your system becoming
unusable. Test your actions on a non-critical installation before you do it
on a critical system.
--
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/
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:edltj$5aFHA.720@.TK2MSFTNGP15.phx.gbl...
> How can I save more errorlogs for SQL Server. Right now i believe the
> default is 1 current and 6 archive logs.
> How can I change it to 10 or 20 error logs ?
>|||Right click on SQL Server Logs in Enterprise Manager (under the Management
folder) and select Configure. You can select the number of logs there.
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:edltj$5aFHA.720@.TK2MSFTNGP15.phx.gbl...
> How can I save more errorlogs for SQL Server. Right now i believe the
> default is 1 current and 6 archive logs.
> How can I change it to 10 or 20 error logs ?
>
default is 1 current and 6 archive logs.
How can I change it to 10 or 20 error logs ?Hi
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\NumErrorLogs
registry entry of DWORD data type
(path may change if you have a named instance)
By default the key is not here.
BEWARE: Incorrectly changing the registry may result in your system becoming
unusable. Test your actions on a non-critical installation before you do it
on a critical system.
--
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/
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:edltj$5aFHA.720@.TK2MSFTNGP15.phx.gbl...
> How can I save more errorlogs for SQL Server. Right now i believe the
> default is 1 current and 6 archive logs.
> How can I change it to 10 or 20 error logs ?
>|||Right click on SQL Server Logs in Enterprise Manager (under the Management
folder) and select Configure. You can select the number of logs there.
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:edltj$5aFHA.720@.TK2MSFTNGP15.phx.gbl...
> How can I save more errorlogs for SQL Server. Right now i believe the
> default is 1 current and 6 archive logs.
> How can I change it to 10 or 20 error logs ?
>
change default errorlog retention
How can I save more errorlogs for SQL Server. Right now i believe the
default is 1 current and 6 archive logs.
How can I change it to 10 or 20 error logs ?Hi
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\MSSQLServer\NumErrorLogs
registry entry of DWORD data type
(path may change if you have a named instance)
By default the key is not here.
BEWARE: Incorrectly changing the registry may result in your system becoming
unusable. Test your actions on a non-critical installation before you do it
on a critical system.
--
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/
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:edltj$5aFHA.720@.TK2MSFTNGP15.phx.gbl...
> How can I save more errorlogs for SQL Server. Right now i believe the
> default is 1 current and 6 archive logs.
> How can I change it to 10 or 20 error logs ?
>|||Right click on SQL Server Logs in Enterprise Manager (under the Management
folder) and select Configure. You can select the number of logs there.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:edltj$5aFHA.720@.TK2MSFTNGP15.phx.gbl...
> How can I save more errorlogs for SQL Server. Right now i believe the
> default is 1 current and 6 archive logs.
> How can I change it to 10 or 20 error logs ?
>
default is 1 current and 6 archive logs.
How can I change it to 10 or 20 error logs ?Hi
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\MSSQLServer\NumErrorLogs
registry entry of DWORD data type
(path may change if you have a named instance)
By default the key is not here.
BEWARE: Incorrectly changing the registry may result in your system becoming
unusable. Test your actions on a non-critical installation before you do it
on a critical system.
--
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/
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:edltj$5aFHA.720@.TK2MSFTNGP15.phx.gbl...
> How can I save more errorlogs for SQL Server. Right now i believe the
> default is 1 current and 6 archive logs.
> How can I change it to 10 or 20 error logs ?
>|||Right click on SQL Server Logs in Enterprise Manager (under the Management
folder) and select Configure. You can select the number of logs there.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:edltj$5aFHA.720@.TK2MSFTNGP15.phx.gbl...
> How can I save more errorlogs for SQL Server. Right now i believe the
> default is 1 current and 6 archive logs.
> How can I change it to 10 or 20 error logs ?
>
Tuesday, March 20, 2012
Change database Access - (newbie question)
You should provide the password for the new user.
This article may help with the error message:
http://support.microsoft.com/default.aspx?kbid=826161
Hope this helps.
Tim
>--Original Message--
>I have added a new User Login in Enterprise Manager. I
>then want to give that User access to a database. When I
>tick the appropriate table in the the Database Access
>property of the User, I am prompted to confirm the
>password.
>Which password is the system expecting here? The user's
>password or the SA passsord? But no matter what I type,
I
>get a message: "The confirmation password is not correct"
>
>
>
>.
>Newbie? I don't think so. This has always bugged the heck out of me. Thanks
for asking!
And, even if it is NOT a new user, when you want to add a database from the
Server Security folder, just be sure to reenter the users password before yo
u hit the "permissions" tab. Then, when you add the database access you desi
re, the prompt for the new
password will accept the password.
David
This article may help with the error message:
http://support.microsoft.com/default.aspx?kbid=826161
Hope this helps.
Tim
>--Original Message--
>I have added a new User Login in Enterprise Manager. I
>then want to give that User access to a database. When I
>tick the appropriate table in the the Database Access
>property of the User, I am prompted to confirm the
>password.
>Which password is the system expecting here? The user's
>password or the SA passsord? But no matter what I type,
I
>get a message: "The confirmation password is not correct"
>
>
>
>.
>Newbie? I don't think so. This has always bugged the heck out of me. Thanks
for asking!
And, even if it is NOT a new user, when you want to add a database from the
Server Security folder, just be sure to reenter the users password before yo
u hit the "permissions" tab. Then, when you add the database access you desi
re, the prompt for the new
password will accept the password.
David
Monday, March 19, 2012
change connection pool
how can i change pool buffer of all connections?
I receive this error for each connection:
System.InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.May I ask what you're doing to consume all the connections?
Wednesday, March 7, 2012
Chain Linkage Problem
Hi All,
I am facing this error continously:
Error: 8908, Severity: 22, State: 6
Table error: Database ID 7, object ID 477400920, index ID 0. Chain
linkage
mismatch. (3:499157)->next = (1:736800), but (1:736800)->prev =
(1:736799)..
And when I run DBCC CHECKTABLE it shows consistency errors.
It get fixed using DBCC DBReIndex but re-occur after sometimes.
I have tried to find in newsgroup, some of them says it can happen due to
NOLOCK hint.
Is there any solution for this problem or please help me to identify the
root cause of the problem.
Thanks
Ritesh
Hi
KB article 308886 describes the issue, butthose errors occur in that query
only and do not affect data. In you case, you are showing data issues.
Have you run DBCC CheckDB and what version of SQL are you on?
Regards
Mike
"Ritesh" wrote:
> Hi All,
> I am facing this error continously:
> Error: 8908, Severity: 22, State: 6
> Table error: Database ID 7, object ID 477400920, index ID 0. Chain
> linkage
> mismatch. (3:499157)->next = (1:736800), but (1:736800)->prev =
> (1:736799)..
> And when I run DBCC CHECKTABLE it shows consistency errors.
> It get fixed using DBCC DBReIndex but re-occur after sometimes.
> I have tried to find in newsgroup, some of them says it can happen due to
> NOLOCK hint.
> Is there any solution for this problem or please help me to identify the
> root cause of the problem.
> Thanks
> Ritesh
>
>
|||Hi Mike,
Thanks for your response.
We are using Enterprise version of SQL 2000. Consistency errors get resolved
using DBCC DBReIndex but it is a re-occuring problem. Presently our server
is handling heavy traffic and according business logic needs we need to
access just inserted data, say Top 50 records desc by CreatedDateTime for
avoiding delay due to locks we use NOLock hints.
Will also like to know from you, though out of context, is there any way to
compute Fillfactor for indexes or just hit and trial is the only way. I have
a table which is more than 750 MB in size and in every 1 second atleast 10
records are getting inserted ( with updates also). Putting 90 or 85 can have
its effects but want to know exact FACTORS to be used, if any?
Will appreciate your help in this regard and think it may also help in
resolving the original issue (which may have this as its root cause)
Thanks & Regards,
Ritesh Khanna
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> KB article 308886 describes the issue, butthose errors occur in that query
> only and do not affect data. In you case, you are showing data issues.
> Have you run DBCC CheckDB and what version of SQL are you on?
> Regards
> Mike
> "Ritesh" wrote:
I am facing this error continously:
Error: 8908, Severity: 22, State: 6
Table error: Database ID 7, object ID 477400920, index ID 0. Chain
linkage
mismatch. (3:499157)->next = (1:736800), but (1:736800)->prev =
(1:736799)..
And when I run DBCC CHECKTABLE it shows consistency errors.
It get fixed using DBCC DBReIndex but re-occur after sometimes.
I have tried to find in newsgroup, some of them says it can happen due to
NOLOCK hint.
Is there any solution for this problem or please help me to identify the
root cause of the problem.
Thanks
Ritesh
Hi
KB article 308886 describes the issue, butthose errors occur in that query
only and do not affect data. In you case, you are showing data issues.
Have you run DBCC CheckDB and what version of SQL are you on?
Regards
Mike
"Ritesh" wrote:
> Hi All,
> I am facing this error continously:
> Error: 8908, Severity: 22, State: 6
> Table error: Database ID 7, object ID 477400920, index ID 0. Chain
> linkage
> mismatch. (3:499157)->next = (1:736800), but (1:736800)->prev =
> (1:736799)..
> And when I run DBCC CHECKTABLE it shows consistency errors.
> It get fixed using DBCC DBReIndex but re-occur after sometimes.
> I have tried to find in newsgroup, some of them says it can happen due to
> NOLOCK hint.
> Is there any solution for this problem or please help me to identify the
> root cause of the problem.
> Thanks
> Ritesh
>
>
|||Hi Mike,
Thanks for your response.
We are using Enterprise version of SQL 2000. Consistency errors get resolved
using DBCC DBReIndex but it is a re-occuring problem. Presently our server
is handling heavy traffic and according business logic needs we need to
access just inserted data, say Top 50 records desc by CreatedDateTime for
avoiding delay due to locks we use NOLock hints.
Will also like to know from you, though out of context, is there any way to
compute Fillfactor for indexes or just hit and trial is the only way. I have
a table which is more than 750 MB in size and in every 1 second atleast 10
records are getting inserted ( with updates also). Putting 90 or 85 can have
its effects but want to know exact FACTORS to be used, if any?
Will appreciate your help in this regard and think it may also help in
resolving the original issue (which may have this as its root cause)
Thanks & Regards,
Ritesh Khanna
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> KB article 308886 describes the issue, butthose errors occur in that query
> only and do not affect data. In you case, you are showing data issues.
> Have you run DBCC CheckDB and what version of SQL are you on?
> Regards
> Mike
> "Ritesh" wrote:
Chain Linkage Problem
Hi All,
I am facing this error continously:
Error: 8908, Severity: 22, State: 6
Table error: Database ID 7, object ID 477400920, index ID 0. Chain
linkage
mismatch. (3:499157)->next = (1:736800), but (1:736800)->prev = (1:736799)..
And when I run DBCC CHECKTABLE it shows consistency errors.
It get fixed using DBCC DBReIndex but re-occur after sometimes.
I have tried to find in newsgroup, some of them says it can happen due to
NOLOCK hint.
Is there any solution for this problem or please help me to identify the
root cause of the problem.
Thanks
RiteshHi
KB article 308886 describes the issue, butthose errors occur in that query
only and do not affect data. In you case, you are showing data issues.
Have you run DBCC CheckDB and what version of SQL are you on?
Regards
Mike
"Ritesh" wrote:
> Hi All,
> I am facing this error continously:
> Error: 8908, Severity: 22, State: 6
> Table error: Database ID 7, object ID 477400920, index ID 0. Chain
> linkage
> mismatch. (3:499157)->next = (1:736800), but (1:736800)->prev => (1:736799)..
> And when I run DBCC CHECKTABLE it shows consistency errors.
> It get fixed using DBCC DBReIndex but re-occur after sometimes.
> I have tried to find in newsgroup, some of them says it can happen due to
> NOLOCK hint.
> Is there any solution for this problem or please help me to identify the
> root cause of the problem.
> Thanks
> Ritesh
>
>|||Hi Mike,
Thanks for your response.
We are using Enterprise version of SQL 2000. Consistency errors get resolved
using DBCC DBReIndex but it is a re-occuring problem. Presently our server
is handling heavy traffic and according business logic needs we need to
access just inserted data, say Top 50 records desc by CreatedDateTime for
avoiding delay due to locks we use NOLock hints.
Will also like to know from you, though out of context, is there any way to
compute Fillfactor for indexes or just hit and trial is the only way. I have
a table which is more than 750 MB in size and in every 1 second atleast 10
records are getting inserted ( with updates also). Putting 90 or 85 can have
its effects but want to know exact FACTORS to be used, if any?
Will appreciate your help in this regard and think it may also help in
resolving the original issue (which may have this as its root cause)
Thanks & Regards,
Ritesh Khanna
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> KB article 308886 describes the issue, butthose errors occur in that query
> only and do not affect data. In you case, you are showing data issues.
> Have you run DBCC CheckDB and what version of SQL are you on?
> Regards
> Mike
> "Ritesh" wrote:
> > Hi All,
> >
> > I am facing this error continously:
> >
> > Error: 8908, Severity: 22, State: 6
> >
> > Table error: Database ID 7, object ID 477400920, index ID 0. Chain
> > linkage
> > mismatch. (3:499157)->next = (1:736800), but (1:736800)->prev => > (1:736799)..
> >
> > And when I run DBCC CHECKTABLE it shows consistency errors.
> > It get fixed using DBCC DBReIndex but re-occur after sometimes.
> >
> > I have tried to find in newsgroup, some of them says it can happen due to
> > NOLOCK hint.
> >
> > Is there any solution for this problem or please help me to identify the
> > root cause of the problem.
> >
> > Thanks
> > Ritesh
> >
> >
> >
I am facing this error continously:
Error: 8908, Severity: 22, State: 6
Table error: Database ID 7, object ID 477400920, index ID 0. Chain
linkage
mismatch. (3:499157)->next = (1:736800), but (1:736800)->prev = (1:736799)..
And when I run DBCC CHECKTABLE it shows consistency errors.
It get fixed using DBCC DBReIndex but re-occur after sometimes.
I have tried to find in newsgroup, some of them says it can happen due to
NOLOCK hint.
Is there any solution for this problem or please help me to identify the
root cause of the problem.
Thanks
RiteshHi
KB article 308886 describes the issue, butthose errors occur in that query
only and do not affect data. In you case, you are showing data issues.
Have you run DBCC CheckDB and what version of SQL are you on?
Regards
Mike
"Ritesh" wrote:
> Hi All,
> I am facing this error continously:
> Error: 8908, Severity: 22, State: 6
> Table error: Database ID 7, object ID 477400920, index ID 0. Chain
> linkage
> mismatch. (3:499157)->next = (1:736800), but (1:736800)->prev => (1:736799)..
> And when I run DBCC CHECKTABLE it shows consistency errors.
> It get fixed using DBCC DBReIndex but re-occur after sometimes.
> I have tried to find in newsgroup, some of them says it can happen due to
> NOLOCK hint.
> Is there any solution for this problem or please help me to identify the
> root cause of the problem.
> Thanks
> Ritesh
>
>|||Hi Mike,
Thanks for your response.
We are using Enterprise version of SQL 2000. Consistency errors get resolved
using DBCC DBReIndex but it is a re-occuring problem. Presently our server
is handling heavy traffic and according business logic needs we need to
access just inserted data, say Top 50 records desc by CreatedDateTime for
avoiding delay due to locks we use NOLock hints.
Will also like to know from you, though out of context, is there any way to
compute Fillfactor for indexes or just hit and trial is the only way. I have
a table which is more than 750 MB in size and in every 1 second atleast 10
records are getting inserted ( with updates also). Putting 90 or 85 can have
its effects but want to know exact FACTORS to be used, if any?
Will appreciate your help in this regard and think it may also help in
resolving the original issue (which may have this as its root cause)
Thanks & Regards,
Ritesh Khanna
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> KB article 308886 describes the issue, butthose errors occur in that query
> only and do not affect data. In you case, you are showing data issues.
> Have you run DBCC CheckDB and what version of SQL are you on?
> Regards
> Mike
> "Ritesh" wrote:
> > Hi All,
> >
> > I am facing this error continously:
> >
> > Error: 8908, Severity: 22, State: 6
> >
> > Table error: Database ID 7, object ID 477400920, index ID 0. Chain
> > linkage
> > mismatch. (3:499157)->next = (1:736800), but (1:736800)->prev => > (1:736799)..
> >
> > And when I run DBCC CHECKTABLE it shows consistency errors.
> > It get fixed using DBCC DBReIndex but re-occur after sometimes.
> >
> > I have tried to find in newsgroup, some of them says it can happen due to
> > NOLOCK hint.
> >
> > Is there any solution for this problem or please help me to identify the
> > root cause of the problem.
> >
> > Thanks
> > Ritesh
> >
> >
> >
Chain Linkage Problem
Hi All,
I am facing this error continously:
Error: 8908, Severity: 22, State: 6
Table error: Database ID 7, object ID 477400920, index ID 0. Chain
linkage
mismatch. (3:499157)->next = (1:736800), but (1:736800)->prev =
(1:736799)..
And when I run DBCC CHECKTABLE it shows consistency errors.
It get fixed using DBCC DBReIndex but re-occur after sometimes.
I have tried to find in newsgroup, some of them says it can happen due to
NOLOCK hint.
Is there any solution for this problem or please help me to identify the
root cause of the problem.
Thanks
RiteshHi
KB article 308886 describes the issue, butthose errors occur in that query
only and do not affect data. In you case, you are showing data issues.
Have you run DBCC CheckDB and what version of SQL are you on?
Regards
Mike
"Ritesh" wrote:
> Hi All,
> I am facing this error continously:
> Error: 8908, Severity: 22, State: 6
> Table error: Database ID 7, object ID 477400920, index ID 0. Chain
> linkage
> mismatch. (3:499157)->next = (1:736800), but (1:736800)->prev =
> (1:736799)..
> And when I run DBCC CHECKTABLE it shows consistency errors.
> It get fixed using DBCC DBReIndex but re-occur after sometimes.
> I have tried to find in newsgroup, some of them says it can happen due to
> NOLOCK hint.
> Is there any solution for this problem or please help me to identify the
> root cause of the problem.
> Thanks
> Ritesh
>
>|||Hi Mike,
Thanks for your response.
We are using Enterprise version of SQL 2000. Consistency errors get resolved
using DBCC DBReIndex but it is a re-occuring problem. Presently our server
is handling heavy traffic and according business logic needs we need to
access just inserted data, say Top 50 records desc by CreatedDateTime for
avoiding delay due to locks we use NOLock hints.
Will also like to know from you, though out of context, is there any way to
compute Fillfactor for indexes or just hit and trial is the only way. I have
a table which is more than 750 MB in size and in every 1 second atleast 10
records are getting inserted ( with updates also). Putting 90 or 85 can have
its effects but want to know exact FACTORS to be used, if any?
Will appreciate your help in this regard and think it may also help in
resolving the original issue (which may have this as its root cause)
Thanks & Regards,
Ritesh Khanna
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> KB article 308886 describes the issue, butthose errors occur in that query
> only and do not affect data. In you case, you are showing data issues.
> Have you run DBCC CheckDB and what version of SQL are you on?
> Regards
> Mike
> "Ritesh" wrote:
>
I am facing this error continously:
Error: 8908, Severity: 22, State: 6
Table error: Database ID 7, object ID 477400920, index ID 0. Chain
linkage
mismatch. (3:499157)->next = (1:736800), but (1:736800)->prev =
(1:736799)..
And when I run DBCC CHECKTABLE it shows consistency errors.
It get fixed using DBCC DBReIndex but re-occur after sometimes.
I have tried to find in newsgroup, some of them says it can happen due to
NOLOCK hint.
Is there any solution for this problem or please help me to identify the
root cause of the problem.
Thanks
RiteshHi
KB article 308886 describes the issue, butthose errors occur in that query
only and do not affect data. In you case, you are showing data issues.
Have you run DBCC CheckDB and what version of SQL are you on?
Regards
Mike
"Ritesh" wrote:
> Hi All,
> I am facing this error continously:
> Error: 8908, Severity: 22, State: 6
> Table error: Database ID 7, object ID 477400920, index ID 0. Chain
> linkage
> mismatch. (3:499157)->next = (1:736800), but (1:736800)->prev =
> (1:736799)..
> And when I run DBCC CHECKTABLE it shows consistency errors.
> It get fixed using DBCC DBReIndex but re-occur after sometimes.
> I have tried to find in newsgroup, some of them says it can happen due to
> NOLOCK hint.
> Is there any solution for this problem or please help me to identify the
> root cause of the problem.
> Thanks
> Ritesh
>
>|||Hi Mike,
Thanks for your response.
We are using Enterprise version of SQL 2000. Consistency errors get resolved
using DBCC DBReIndex but it is a re-occuring problem. Presently our server
is handling heavy traffic and according business logic needs we need to
access just inserted data, say Top 50 records desc by CreatedDateTime for
avoiding delay due to locks we use NOLock hints.
Will also like to know from you, though out of context, is there any way to
compute Fillfactor for indexes or just hit and trial is the only way. I have
a table which is more than 750 MB in size and in every 1 second atleast 10
records are getting inserted ( with updates also). Putting 90 or 85 can have
its effects but want to know exact FACTORS to be used, if any?
Will appreciate your help in this regard and think it may also help in
resolving the original issue (which may have this as its root cause)
Thanks & Regards,
Ritesh Khanna
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> KB article 308886 describes the issue, butthose errors occur in that query
> only and do not affect data. In you case, you are showing data issues.
> Have you run DBCC CheckDB and what version of SQL are you on?
> Regards
> Mike
> "Ritesh" wrote:
>
Chain Linkage Mismatch Errors
Recently I have been getting a barrage of errors such as:
DESCRIPTION: Error: 8908, Severity: 22, State: 6
Table error: Database ID 16, object ID 1893581784, index ID 2. Chain
linkage mismatch. (1:17214)->next = (1:17060), but (1:17060)->prev =
(3:178).
and
DESCRIPTION: Error: 605, Severity: 21, State: 1
Attempt to fetch logical page (1:164756) in database 'Clients' belongs
to object 'activities', not to object 'entity_address_check'.
The problem began to originally manifest itself with a couple errors
similar to:
DESCRIPTION: Error: 605, Severity: 21, State: 1
Attempt to fetch logical page (1:4930) in database 'tempdb' belongs to
object '1732152167', not to object
'#allrowstable____________________________________ __________________________________________________ _________________000100003472'.
So far all of the errors have been isolated to indexes and I have been
able to repair the problems with CHECKDB fast_rebuild or by dropping
the index that is causing the error and recreating it. At times the
errors will appear for a couple hours overnight and then resolve
themselves before the morning. Originally the problem began to appear
on a single SQL Servers and now appears daily on all three SQL
Servers.
We've investigated whether the NOLOCK optimizer was the culprit but
out of all of our views and procedures there was nothing compiled with
that optimizer in any of the databases, so this seems an unlikely
cause (SEE http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B308886
)
We've reviewed IO on the servers, and nothing appears out of the
ordinary. We have even checked into the possibility of unreport IO
errors (SEE http://support.microsoft.com/default.aspx?scid=kb;en-us;826433&Product=sql2k
) but this still is inconclusive. HP did admit that their disc array
is not fully compatible with W2K Adv Server SP3, but the problem only
recently appeared and the upgrade to SP3 was completed nearly 2 months
ago.
We are running SQL Server 2000 EE, SP3 build 2195 on Windows 2000
Advanced Server. The server is setup as an A/P cluster on 2 HP
Proliant servers with a HP HTA200 disc array.
If anyone has any insight or suggestions, it would be great to hear
them.Did you do the whole database?
Fixing one doesn't guarentee all...
Did you do DBCC CHECKDB after the work was done to see if there are any other errors?
How big is the db?
So as to nor prevent an outage..take a dump, restore it to another box/instance, and do a full repair...see how long it takes...
Then schedule an outage...
MOO
and Good Luck...|||The largest of the affected databases is about 3 GB and the smallest 800 MB. I have done DBCC CHECKDB (with Fast_Rebuild) to fix some of the errors and have been running CHECKDB multiple times daily to watch for and catch the errors as they are showing up.
Also DBCC DBREINDEX has been running weekly to rebuild the indexes.
One thing I have been considering is rebuilding statistics and usage for all of the databases. On some previous projects I have seen statistics that are out of wack severly affect performance.
Jason Strate
DESCRIPTION: Error: 8908, Severity: 22, State: 6
Table error: Database ID 16, object ID 1893581784, index ID 2. Chain
linkage mismatch. (1:17214)->next = (1:17060), but (1:17060)->prev =
(3:178).
and
DESCRIPTION: Error: 605, Severity: 21, State: 1
Attempt to fetch logical page (1:164756) in database 'Clients' belongs
to object 'activities', not to object 'entity_address_check'.
The problem began to originally manifest itself with a couple errors
similar to:
DESCRIPTION: Error: 605, Severity: 21, State: 1
Attempt to fetch logical page (1:4930) in database 'tempdb' belongs to
object '1732152167', not to object
'#allrowstable____________________________________ __________________________________________________ _________________000100003472'.
So far all of the errors have been isolated to indexes and I have been
able to repair the problems with CHECKDB fast_rebuild or by dropping
the index that is causing the error and recreating it. At times the
errors will appear for a couple hours overnight and then resolve
themselves before the morning. Originally the problem began to appear
on a single SQL Servers and now appears daily on all three SQL
Servers.
We've investigated whether the NOLOCK optimizer was the culprit but
out of all of our views and procedures there was nothing compiled with
that optimizer in any of the databases, so this seems an unlikely
cause (SEE http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B308886
)
We've reviewed IO on the servers, and nothing appears out of the
ordinary. We have even checked into the possibility of unreport IO
errors (SEE http://support.microsoft.com/default.aspx?scid=kb;en-us;826433&Product=sql2k
) but this still is inconclusive. HP did admit that their disc array
is not fully compatible with W2K Adv Server SP3, but the problem only
recently appeared and the upgrade to SP3 was completed nearly 2 months
ago.
We are running SQL Server 2000 EE, SP3 build 2195 on Windows 2000
Advanced Server. The server is setup as an A/P cluster on 2 HP
Proliant servers with a HP HTA200 disc array.
If anyone has any insight or suggestions, it would be great to hear
them.Did you do the whole database?
Fixing one doesn't guarentee all...
Did you do DBCC CHECKDB after the work was done to see if there are any other errors?
How big is the db?
So as to nor prevent an outage..take a dump, restore it to another box/instance, and do a full repair...see how long it takes...
Then schedule an outage...
MOO
and Good Luck...|||The largest of the affected databases is about 3 GB and the smallest 800 MB. I have done DBCC CHECKDB (with Fast_Rebuild) to fix some of the errors and have been running CHECKDB multiple times daily to watch for and catch the errors as they are showing up.
Also DBCC DBREINDEX has been running weekly to rebuild the indexes.
One thing I have been considering is rebuilding statistics and usage for all of the databases. On some previous projects I have seen statistics that are out of wack severly affect performance.
Jason Strate
Chain Linkage Mismatch Errors
Recently I have been getting a barrage of errors such as:
DESCRIPTION: Error: 8908, Severity: 22, State: 6
Table error: Database ID 16, object ID 1893581784, index ID 2. Chain
linkage mismatch. (1:17214)->next = (1:17060), but (1:17060)->prev =
(3:178).
and
DESCRIPTION: Error: 605, Severity: 21, State: 1
Attempt to fetch logical page (1:164756) in database 'Clients' belongs
to object 'activities', not to object 'entity_address_check'.
The problem began to originally manifest itself with a couple errors
similar to:
DESCRIPTION: Error: 605, Severity: 21, State: 1
Attempt to fetch logical page (1:4930) in database 'tempdb' belongs to
object '1732152167', not to object
'#allrowstable__________________________
____________________________________
________________________________________
_000100003472'.
So far all of the errors have been isolated to indexes and I have been
able to repair the problems with CHECKDB fast_rebuild or by dropping
the index that is causing the error and recreating it. At times the
errors will appear for a couple hours overnight and then resolve
themselves before the morning. Originally the problem began to appear
on a single SQL Servers and now appears daily on all three SQL
Servers.
We've investigated whether the NOLOCK optimizer was the culprit but
out of all of our views and procedures there was nothing compiled with
that optimizer in any of the databases, so this seems an unlikely
cause (SEE http://support.microsoft.com/defaul...t
=sql2k
) but this still is inconclusive. HP did admit that their disc array
is not fully compatible with W2K Adv Server SP3, but the problem only
recently appeared and the upgrade to SP3 was completed nearly 2 months
ago.
We are running SQL Server 2000 EE, SP3 build 2195 on Windows 2000
Advanced Server. The server is setup as an A/P cluster on 2 HP
Proliant servers with a HP HTA200 disc array.
If anyone has any insight or suggestions, it would be great to hear
them.>
> Recently I have been getting a barrage of errors such as:
> DESCRIPTION: Error: 8908, Severity: 22, State: 6
> Table error: Database ID 16, object ID 1893581784, index ID 2. Chain
> linkage mismatch. (1:17214)->next = (1:17060), but (1:17060)->prev =
> (3:178).
> and
> DESCRIPTION: Error: 605, Severity: 21, State: 1
> Attempt to fetch logical page (1:164756) in database 'Clients' belongs
> to object 'activities', not to object 'entity_address_check'.
> The problem began to originally manifest itself with a couple errors
> similar to:
> DESCRIPTION: Error: 605, Severity: 21, State: 1
> Attempt to fetch logical page (1:4930) in database 'tempdb' belongs to
> object '1732152167', not to object
>
'#allrowstable__________________________
____________________________________
________________________________________
_000100003472'.
> So far all of the errors have been isolated to indexes and I have been
> able to repair the problems with CHECKDB fast_rebuild or by dropping
> the index that is causing the error and recreating it. At times the
> errors will appear for a couple hours overnight and then resolve
> themselves before the morning. Originally the problem began to appear
> on a single SQL Servers and now appears daily on all three SQL
> Servers.
> We've investigated whether the NOLOCK optimizer was the culprit but
> out of all of our views and procedures there was nothing compiled with
> that optimizer in any of the databases, so this seems an unlikely
> cause (SEE
http://support.microsoft.com/defaul...Ben-us%3B308886
> )
> We've reviewed IO on the servers, and nothing appears out of the
> ordinary. We have even checked into the possibility of unreport IO
> errors (SEE
http://support.microsoft.com/defaul...3&Product=sql2k
> ) but this still is inconclusive. HP did admit that their disc array
> is not fully compatible with W2K Adv Server SP3, but the problem only
> recently appeared and the upgrade to SP3 was completed nearly 2 months
> ago.
> We are running SQL Server 2000 EE, SP3 build 2195 on Windows 2000
> Advanced Server. The server is setup as an A/P cluster on 2 HP
> Proliant servers with a HP HTA200 disc array.
> If anyone has any insight or suggestions, it would be great to hear
> them.
>
--
Hi Jason,
Here is my recommended action plan:
1. Turn off write caching in you disk controllers. They have been known to
cause these 605 errors. Monitor your database for a few weeks and see if
turning off the write caching has made a difference.
2. Run SQLIOStress.exe on your system to check how it can handle a typical
SQL Server load. Information about SQLIOStress follows:
HOW TO: Use the SQLIOStress Utility to Stress a Disk Subsystem Such As SQL
Server
http://support.microsoft.com/?id=231619
Hope this helps,
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.
DESCRIPTION: Error: 8908, Severity: 22, State: 6
Table error: Database ID 16, object ID 1893581784, index ID 2. Chain
linkage mismatch. (1:17214)->next = (1:17060), but (1:17060)->prev =
(3:178).
and
DESCRIPTION: Error: 605, Severity: 21, State: 1
Attempt to fetch logical page (1:164756) in database 'Clients' belongs
to object 'activities', not to object 'entity_address_check'.
The problem began to originally manifest itself with a couple errors
similar to:
DESCRIPTION: Error: 605, Severity: 21, State: 1
Attempt to fetch logical page (1:4930) in database 'tempdb' belongs to
object '1732152167', not to object
'#allrowstable__________________________
____________________________________
________________________________________
_000100003472'.
So far all of the errors have been isolated to indexes and I have been
able to repair the problems with CHECKDB fast_rebuild or by dropping
the index that is causing the error and recreating it. At times the
errors will appear for a couple hours overnight and then resolve
themselves before the morning. Originally the problem began to appear
on a single SQL Servers and now appears daily on all three SQL
Servers.
We've investigated whether the NOLOCK optimizer was the culprit but
out of all of our views and procedures there was nothing compiled with
that optimizer in any of the databases, so this seems an unlikely
cause (SEE http://support.microsoft.com/defaul...t
=sql2k
) but this still is inconclusive. HP did admit that their disc array
is not fully compatible with W2K Adv Server SP3, but the problem only
recently appeared and the upgrade to SP3 was completed nearly 2 months
ago.
We are running SQL Server 2000 EE, SP3 build 2195 on Windows 2000
Advanced Server. The server is setup as an A/P cluster on 2 HP
Proliant servers with a HP HTA200 disc array.
If anyone has any insight or suggestions, it would be great to hear
them.>
> Recently I have been getting a barrage of errors such as:
> DESCRIPTION: Error: 8908, Severity: 22, State: 6
> Table error: Database ID 16, object ID 1893581784, index ID 2. Chain
> linkage mismatch. (1:17214)->next = (1:17060), but (1:17060)->prev =
> (3:178).
> and
> DESCRIPTION: Error: 605, Severity: 21, State: 1
> Attempt to fetch logical page (1:164756) in database 'Clients' belongs
> to object 'activities', not to object 'entity_address_check'.
> The problem began to originally manifest itself with a couple errors
> similar to:
> DESCRIPTION: Error: 605, Severity: 21, State: 1
> Attempt to fetch logical page (1:4930) in database 'tempdb' belongs to
> object '1732152167', not to object
>
'#allrowstable__________________________
____________________________________
________________________________________
_000100003472'.
> So far all of the errors have been isolated to indexes and I have been
> able to repair the problems with CHECKDB fast_rebuild or by dropping
> the index that is causing the error and recreating it. At times the
> errors will appear for a couple hours overnight and then resolve
> themselves before the morning. Originally the problem began to appear
> on a single SQL Servers and now appears daily on all three SQL
> Servers.
> We've investigated whether the NOLOCK optimizer was the culprit but
> out of all of our views and procedures there was nothing compiled with
> that optimizer in any of the databases, so this seems an unlikely
> cause (SEE
http://support.microsoft.com/defaul...Ben-us%3B308886
> )
> We've reviewed IO on the servers, and nothing appears out of the
> ordinary. We have even checked into the possibility of unreport IO
> errors (SEE
http://support.microsoft.com/defaul...3&Product=sql2k
> ) but this still is inconclusive. HP did admit that their disc array
> is not fully compatible with W2K Adv Server SP3, but the problem only
> recently appeared and the upgrade to SP3 was completed nearly 2 months
> ago.
> We are running SQL Server 2000 EE, SP3 build 2195 on Windows 2000
> Advanced Server. The server is setup as an A/P cluster on 2 HP
> Proliant servers with a HP HTA200 disc array.
> If anyone has any insight or suggestions, it would be great to hear
> them.
>
--
Hi Jason,
Here is my recommended action plan:
1. Turn off write caching in you disk controllers. They have been known to
cause these 605 errors. Monitor your database for a few weeks and see if
turning off the write caching has made a difference.
2. Run SQLIOStress.exe on your system to check how it can handle a typical
SQL Server load. Information about SQLIOStress follows:
HOW TO: Use the SQLIOStress Utility to Stress a Disk Subsystem Such As SQL
Server
http://support.microsoft.com/?id=231619
Hope this helps,
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.
Chain Linkage Mismatch Errors
Recently I have been getting a barrage of errors such as:
DESCRIPTION:Error: 8908, Severity: 22, State: 6
Table error: Database ID 16, object ID 1893581784, index ID 2. Chain
linkage mismatch. (1:17214)->next = (1:17060), but (1:17060)->prev =
(3:178).
and
DESCRIPTION:Error: 605, Severity: 21, State: 1
Attempt to fetch logical page (1:164756) in database 'Clients' belongs
to object 'activities', not to object 'entity_address_check'.
The problem began to originally manifest itself with a couple errors
similar to:
DESCRIPTION:Error: 605, Severity: 21, State: 1
Attempt to fetch logical page (1:4930) in database 'tempdb' belongs to
object '1732152167', not to object
'#allrowstable____________________________________ __________________________________________________ _________________000100003472'.
So far all of the errors have been isolated to indexes and I have been
able to repair the problems with CHECKDB fast_rebuild or by dropping
the index that is causing the error and recreating it. At times the
errors will appear for a couple hours overnight and then resolve
themselves before the morning. Originally the problem began to appear
on a single SQL Servers and now appears daily on all three SQL
Servers.
We've investigated whether the NOLOCK optimizer was the culprit but
out of all of our views and procedures there was nothing compiled with
that optimizer in any of the databases, so this seems an unlikely
cause (SEE http://support.microsoft.com/default...en-us%3B308886
)
We've reviewed IO on the servers, and nothing appears out of the
ordinary. We have even checked into the possibility of unreport IO
errors (SEE http://support.microsoft.com/default...&Product=sql2k
) but this still is inconclusive. HP did admit that their disc array
is not fully compatible with W2K Adv Server SP3, but the problem only
recently appeared and the upgrade to SP3 was completed nearly 2 months
ago.
We are running SQL Server 2000 EE, SP3 build 2195 on Windows 2000
Advanced Server. The server is setup as an A/P cluster on 2 HP
Proliant servers with a HP HTA200 disc array.
If anyone has any insight or suggestions, it would be great to hear
them.
>
> Recently I have been getting a barrage of errors such as:
> DESCRIPTION:Error: 8908, Severity: 22, State: 6
> Table error: Database ID 16, object ID 1893581784, index ID 2. Chain
> linkage mismatch. (1:17214)->next = (1:17060), but (1:17060)->prev =
> (3:178).
> and
> DESCRIPTION:Error: 605, Severity: 21, State: 1
> Attempt to fetch logical page (1:164756) in database 'Clients' belongs
> to object 'activities', not to object 'entity_address_check'.
> The problem began to originally manifest itself with a couple errors
> similar to:
> DESCRIPTION:Error: 605, Severity: 21, State: 1
> Attempt to fetch logical page (1:4930) in database 'tempdb' belongs to
> object '1732152167', not to object
>
'#allrowstable____________________________________ __________________________
_________________________________________000100003 472'.
> So far all of the errors have been isolated to indexes and I have been
> able to repair the problems with CHECKDB fast_rebuild or by dropping
> the index that is causing the error and recreating it. At times the
> errors will appear for a couple hours overnight and then resolve
> themselves before the morning. Originally the problem began to appear
> on a single SQL Servers and now appears daily on all three SQL
> Servers.
> We've investigated whether the NOLOCK optimizer was the culprit but
> out of all of our views and procedures there was nothing compiled with
> that optimizer in any of the databases, so this seems an unlikely
> cause (SEE
http://support.microsoft.com/default...en-us%3B308886
> )
> We've reviewed IO on the servers, and nothing appears out of the
> ordinary. We have even checked into the possibility of unreport IO
> errors (SEE
http://support.microsoft.com/default...&Product=sql2k
> ) but this still is inconclusive. HP did admit that their disc array
> is not fully compatible with W2K Adv Server SP3, but the problem only
> recently appeared and the upgrade to SP3 was completed nearly 2 months
> ago.
> We are running SQL Server 2000 EE, SP3 build 2195 on Windows 2000
> Advanced Server. The server is setup as an A/P cluster on 2 HP
> Proliant servers with a HP HTA200 disc array.
> If anyone has any insight or suggestions, it would be great to hear
> them.
>
Hi Jason,
Here is my recommended action plan:
1. Turn off write caching in you disk controllers. They have been known to
cause these 605 errors. Monitor your database for a few weeks and see if
turning off the write caching has made a difference.
2. Run SQLIOStress.exe on your system to check how it can handle a typical
SQL Server load. Information about SQLIOStress follows:
HOW TO: Use the SQLIOStress Utility to Stress a Disk Subsystem Such As SQL
Server
http://support.microsoft.com/?id=231619
Hope this helps,
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.
DESCRIPTION:Error: 8908, Severity: 22, State: 6
Table error: Database ID 16, object ID 1893581784, index ID 2. Chain
linkage mismatch. (1:17214)->next = (1:17060), but (1:17060)->prev =
(3:178).
and
DESCRIPTION:Error: 605, Severity: 21, State: 1
Attempt to fetch logical page (1:164756) in database 'Clients' belongs
to object 'activities', not to object 'entity_address_check'.
The problem began to originally manifest itself with a couple errors
similar to:
DESCRIPTION:Error: 605, Severity: 21, State: 1
Attempt to fetch logical page (1:4930) in database 'tempdb' belongs to
object '1732152167', not to object
'#allrowstable____________________________________ __________________________________________________ _________________000100003472'.
So far all of the errors have been isolated to indexes and I have been
able to repair the problems with CHECKDB fast_rebuild or by dropping
the index that is causing the error and recreating it. At times the
errors will appear for a couple hours overnight and then resolve
themselves before the morning. Originally the problem began to appear
on a single SQL Servers and now appears daily on all three SQL
Servers.
We've investigated whether the NOLOCK optimizer was the culprit but
out of all of our views and procedures there was nothing compiled with
that optimizer in any of the databases, so this seems an unlikely
cause (SEE http://support.microsoft.com/default...en-us%3B308886
)
We've reviewed IO on the servers, and nothing appears out of the
ordinary. We have even checked into the possibility of unreport IO
errors (SEE http://support.microsoft.com/default...&Product=sql2k
) but this still is inconclusive. HP did admit that their disc array
is not fully compatible with W2K Adv Server SP3, but the problem only
recently appeared and the upgrade to SP3 was completed nearly 2 months
ago.
We are running SQL Server 2000 EE, SP3 build 2195 on Windows 2000
Advanced Server. The server is setup as an A/P cluster on 2 HP
Proliant servers with a HP HTA200 disc array.
If anyone has any insight or suggestions, it would be great to hear
them.
>
> Recently I have been getting a barrage of errors such as:
> DESCRIPTION:Error: 8908, Severity: 22, State: 6
> Table error: Database ID 16, object ID 1893581784, index ID 2. Chain
> linkage mismatch. (1:17214)->next = (1:17060), but (1:17060)->prev =
> (3:178).
> and
> DESCRIPTION:Error: 605, Severity: 21, State: 1
> Attempt to fetch logical page (1:164756) in database 'Clients' belongs
> to object 'activities', not to object 'entity_address_check'.
> The problem began to originally manifest itself with a couple errors
> similar to:
> DESCRIPTION:Error: 605, Severity: 21, State: 1
> Attempt to fetch logical page (1:4930) in database 'tempdb' belongs to
> object '1732152167', not to object
>
'#allrowstable____________________________________ __________________________
_________________________________________000100003 472'.
> So far all of the errors have been isolated to indexes and I have been
> able to repair the problems with CHECKDB fast_rebuild or by dropping
> the index that is causing the error and recreating it. At times the
> errors will appear for a couple hours overnight and then resolve
> themselves before the morning. Originally the problem began to appear
> on a single SQL Servers and now appears daily on all three SQL
> Servers.
> We've investigated whether the NOLOCK optimizer was the culprit but
> out of all of our views and procedures there was nothing compiled with
> that optimizer in any of the databases, so this seems an unlikely
> cause (SEE
http://support.microsoft.com/default...en-us%3B308886
> )
> We've reviewed IO on the servers, and nothing appears out of the
> ordinary. We have even checked into the possibility of unreport IO
> errors (SEE
http://support.microsoft.com/default...&Product=sql2k
> ) but this still is inconclusive. HP did admit that their disc array
> is not fully compatible with W2K Adv Server SP3, but the problem only
> recently appeared and the upgrade to SP3 was completed nearly 2 months
> ago.
> We are running SQL Server 2000 EE, SP3 build 2195 on Windows 2000
> Advanced Server. The server is setup as an A/P cluster on 2 HP
> Proliant servers with a HP HTA200 disc array.
> If anyone has any insight or suggestions, it would be great to hear
> them.
>
Hi Jason,
Here is my recommended action plan:
1. Turn off write caching in you disk controllers. They have been known to
cause these 605 errors. Monitor your database for a few weeks and see if
turning off the write caching has made a difference.
2. Run SQLIOStress.exe on your system to check how it can handle a typical
SQL Server load. Information about SQLIOStress follows:
HOW TO: Use the SQLIOStress Utility to Stress a Disk Subsystem Such As SQL
Server
http://support.microsoft.com/?id=231619
Hope this helps,
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.
Chain Linkage Mismatch Errors
Recently I have been getting a barrage of errors such as:
DESCRIPTION: Error: 8908, Severity: 22, State: 6
Table error: Database ID 16, object ID 1893581784, index ID 2. Chain
linkage mismatch. (1:17214)->next = (1:17060), but (1:17060)->prev = (3:178).
and
DESCRIPTION: Error: 605, Severity: 21, State: 1
Attempt to fetch logical page (1:164756) in database 'Clients' belongs
to object 'activities', not to object 'entity_address_check'.
The problem began to originally manifest itself with a couple errors
similar to:
DESCRIPTION: Error: 605, Severity: 21, State: 1
Attempt to fetch logical page (1:4930) in database 'tempdb' belongs to
object '1732152167', not to object
'#allrowstable_______________________________________________________________________________________________________000100003472'.
So far all of the errors have been isolated to indexes and I have been
able to repair the problems with CHECKDB fast_rebuild or by dropping
the index that is causing the error and recreating it. At times the
errors will appear for a couple hours overnight and then resolve
themselves before the morning. Originally the problem began to appear
on a single SQL Servers and now appears daily on all three SQL
Servers.
We've investigated whether the NOLOCK optimizer was the culprit but
out of all of our views and procedures there was nothing compiled with
that optimizer in any of the databases, so this seems an unlikely
cause (SEE http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B308886
)
We've reviewed IO on the servers, and nothing appears out of the
ordinary. We have even checked into the possibility of unreport IO
errors (SEE http://support.microsoft.com/default.aspx?scid=kb;en-us;826433&Product=sql2k
) but this still is inconclusive. HP did admit that their disc array
is not fully compatible with W2K Adv Server SP3, but the problem only
recently appeared and the upgrade to SP3 was completed nearly 2 months
ago.
We are running SQL Server 2000 EE, SP3 build 2195 on Windows 2000
Advanced Server. The server is setup as an A/P cluster on 2 HP
Proliant servers with a HP HTA200 disc array.
If anyone has any insight or suggestions, it would be great to hear
them.>
> Recently I have been getting a barrage of errors such as:
> DESCRIPTION: Error: 8908, Severity: 22, State: 6
> Table error: Database ID 16, object ID 1893581784, index ID 2. Chain
> linkage mismatch. (1:17214)->next = (1:17060), but (1:17060)->prev => (3:178).
> and
> DESCRIPTION: Error: 605, Severity: 21, State: 1
> Attempt to fetch logical page (1:164756) in database 'Clients' belongs
> to object 'activities', not to object 'entity_address_check'.
> The problem began to originally manifest itself with a couple errors
> similar to:
> DESCRIPTION: Error: 605, Severity: 21, State: 1
> Attempt to fetch logical page (1:4930) in database 'tempdb' belongs to
> object '1732152167', not to object
>
'#allrowstable______________________________________________________________
_________________________________________000100003472'.
> So far all of the errors have been isolated to indexes and I have been
> able to repair the problems with CHECKDB fast_rebuild or by dropping
> the index that is causing the error and recreating it. At times the
> errors will appear for a couple hours overnight and then resolve
> themselves before the morning. Originally the problem began to appear
> on a single SQL Servers and now appears daily on all three SQL
> Servers.
> We've investigated whether the NOLOCK optimizer was the culprit but
> out of all of our views and procedures there was nothing compiled with
> that optimizer in any of the databases, so this seems an unlikely
> cause (SEE
http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B308886
> )
> We've reviewed IO on the servers, and nothing appears out of the
> ordinary. We have even checked into the possibility of unreport IO
> errors (SEE
http://support.microsoft.com/default.aspx?scid=kb;en-us;826433&Product=sql2k
> ) but this still is inconclusive. HP did admit that their disc array
> is not fully compatible with W2K Adv Server SP3, but the problem only
> recently appeared and the upgrade to SP3 was completed nearly 2 months
> ago.
> We are running SQL Server 2000 EE, SP3 build 2195 on Windows 2000
> Advanced Server. The server is setup as an A/P cluster on 2 HP
> Proliant servers with a HP HTA200 disc array.
> If anyone has any insight or suggestions, it would be great to hear
> them.
>
--
Hi Jason,
Here is my recommended action plan:
1. Turn off write caching in you disk controllers. They have been known to
cause these 605 errors. Monitor your database for a few weeks and see if
turning off the write caching has made a difference.
2. Run SQLIOStress.exe on your system to check how it can handle a typical
SQL Server load. Information about SQLIOStress follows:
HOW TO: Use the SQLIOStress Utility to Stress a Disk Subsystem Such As SQL
Server
http://support.microsoft.com/?id=231619
Hope this helps,
--
Eric Cárdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.
DESCRIPTION: Error: 8908, Severity: 22, State: 6
Table error: Database ID 16, object ID 1893581784, index ID 2. Chain
linkage mismatch. (1:17214)->next = (1:17060), but (1:17060)->prev = (3:178).
and
DESCRIPTION: Error: 605, Severity: 21, State: 1
Attempt to fetch logical page (1:164756) in database 'Clients' belongs
to object 'activities', not to object 'entity_address_check'.
The problem began to originally manifest itself with a couple errors
similar to:
DESCRIPTION: Error: 605, Severity: 21, State: 1
Attempt to fetch logical page (1:4930) in database 'tempdb' belongs to
object '1732152167', not to object
'#allrowstable_______________________________________________________________________________________________________000100003472'.
So far all of the errors have been isolated to indexes and I have been
able to repair the problems with CHECKDB fast_rebuild or by dropping
the index that is causing the error and recreating it. At times the
errors will appear for a couple hours overnight and then resolve
themselves before the morning. Originally the problem began to appear
on a single SQL Servers and now appears daily on all three SQL
Servers.
We've investigated whether the NOLOCK optimizer was the culprit but
out of all of our views and procedures there was nothing compiled with
that optimizer in any of the databases, so this seems an unlikely
cause (SEE http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B308886
)
We've reviewed IO on the servers, and nothing appears out of the
ordinary. We have even checked into the possibility of unreport IO
errors (SEE http://support.microsoft.com/default.aspx?scid=kb;en-us;826433&Product=sql2k
) but this still is inconclusive. HP did admit that their disc array
is not fully compatible with W2K Adv Server SP3, but the problem only
recently appeared and the upgrade to SP3 was completed nearly 2 months
ago.
We are running SQL Server 2000 EE, SP3 build 2195 on Windows 2000
Advanced Server. The server is setup as an A/P cluster on 2 HP
Proliant servers with a HP HTA200 disc array.
If anyone has any insight or suggestions, it would be great to hear
them.>
> Recently I have been getting a barrage of errors such as:
> DESCRIPTION: Error: 8908, Severity: 22, State: 6
> Table error: Database ID 16, object ID 1893581784, index ID 2. Chain
> linkage mismatch. (1:17214)->next = (1:17060), but (1:17060)->prev => (3:178).
> and
> DESCRIPTION: Error: 605, Severity: 21, State: 1
> Attempt to fetch logical page (1:164756) in database 'Clients' belongs
> to object 'activities', not to object 'entity_address_check'.
> The problem began to originally manifest itself with a couple errors
> similar to:
> DESCRIPTION: Error: 605, Severity: 21, State: 1
> Attempt to fetch logical page (1:4930) in database 'tempdb' belongs to
> object '1732152167', not to object
>
'#allrowstable______________________________________________________________
_________________________________________000100003472'.
> So far all of the errors have been isolated to indexes and I have been
> able to repair the problems with CHECKDB fast_rebuild or by dropping
> the index that is causing the error and recreating it. At times the
> errors will appear for a couple hours overnight and then resolve
> themselves before the morning. Originally the problem began to appear
> on a single SQL Servers and now appears daily on all three SQL
> Servers.
> We've investigated whether the NOLOCK optimizer was the culprit but
> out of all of our views and procedures there was nothing compiled with
> that optimizer in any of the databases, so this seems an unlikely
> cause (SEE
http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B308886
> )
> We've reviewed IO on the servers, and nothing appears out of the
> ordinary. We have even checked into the possibility of unreport IO
> errors (SEE
http://support.microsoft.com/default.aspx?scid=kb;en-us;826433&Product=sql2k
> ) but this still is inconclusive. HP did admit that their disc array
> is not fully compatible with W2K Adv Server SP3, but the problem only
> recently appeared and the upgrade to SP3 was completed nearly 2 months
> ago.
> We are running SQL Server 2000 EE, SP3 build 2195 on Windows 2000
> Advanced Server. The server is setup as an A/P cluster on 2 HP
> Proliant servers with a HP HTA200 disc array.
> If anyone has any insight or suggestions, it would be great to hear
> them.
>
--
Hi Jason,
Here is my recommended action plan:
1. Turn off write caching in you disk controllers. They have been known to
cause these 605 errors. Monitor your database for a few weeks and see if
turning off the write caching has made a difference.
2. Run SQLIOStress.exe on your system to check how it can handle a typical
SQL Server load. Information about SQLIOStress follows:
HOW TO: Use the SQLIOStress Utility to Stress a Disk Subsystem Such As SQL
Server
http://support.microsoft.com/?id=231619
Hope this helps,
--
Eric Cárdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.
Saturday, February 25, 2012
CFE2SQL Server does not exist or access denied.
Does anyone have any idea what is causing the following error.
"CFE2SQL Server does not exist or access denied."
--
Thanks
JDSJDS wrote:
> Does anyone have any idea what is causing the following error.
> "CFE2SQL Server does not exist or access denied."
>
I'd say the server does not exist, can't be accessed, or you don't have
permission to use it. Without further detail, that's about all I can offer.|||The Server exist and this problem is happen sporadically. I can't find any
information on what is CFE2SQL.
--
Thanks
JDS
"Tracy McKibben" wrote:
> JDS wrote:
> > Does anyone have any idea what is causing the following error.
> >
> > "CFE2SQL Server does not exist or access denied."
> >
> >
> I'd say the server does not exist, can't be accessed, or you don't have
> permission to use it. Without further detail, that's about all I can offer.
>|||JDS wrote:
> The Server exist and this problem is happen sporadically. I can't find any
> information on what is CFE2SQL.
Where are you seeing this message displayed? The actual error message
is "SQL Server does not exist or access denied", the "CFE2" part is
coming from elsewhere.
"CFE2SQL Server does not exist or access denied."
--
Thanks
JDSJDS wrote:
> Does anyone have any idea what is causing the following error.
> "CFE2SQL Server does not exist or access denied."
>
I'd say the server does not exist, can't be accessed, or you don't have
permission to use it. Without further detail, that's about all I can offer.|||The Server exist and this problem is happen sporadically. I can't find any
information on what is CFE2SQL.
--
Thanks
JDS
"Tracy McKibben" wrote:
> JDS wrote:
> > Does anyone have any idea what is causing the following error.
> >
> > "CFE2SQL Server does not exist or access denied."
> >
> >
> I'd say the server does not exist, can't be accessed, or you don't have
> permission to use it. Without further detail, that's about all I can offer.
>|||JDS wrote:
> The Server exist and this problem is happen sporadically. I can't find any
> information on what is CFE2SQL.
Where are you seeing this message displayed? The actual error message
is "SQL Server does not exist or access denied", the "CFE2" part is
coming from elsewhere.
Subscribe to:
Posts (Atom)