Hello
I have a problem with one of our DB servers, MS SQL 2000 SP4.
We use SP for weekly maintenance, this SP is stored in the master DB.
Therefore I had to add a new SP into all DB servers but when I run the
script on this server I get the error:
Server: Msg 2714, Level 16, State 5, Procedure
sp_u_IndexDefragAndRebuild, Line 172
There is already an object named 'sp_u_IndexDefragAndRebuild' in the
database.
The script does have an exist and drop procedure command at the
beginning. I also tried several naming for the SP but I get always the
same error.
While I was googling for a solution a found http://support.microsoft.com/kb/827448/en-us
a workaround. But when I run this script I get the same error:
Server: Msg 2714, Level 16, State 6, Line 1
There is already an object named 'test_table0' in the database.
and if I try to drop the table I get:
Server: Msg 3701, Level 11, State 5, Line 1
Cannot drop the table 'test_table0', because it does not exist in the
system catalog.
Is there a solution without restoring the whole master DB? And if I
have to restore the master DB what do I need to look for?
I appreciate any help
Martin
On Aug 3, 12:03 pm, DBA <martin-za.fr...@.ubs.com> wrote:
> Hello
> I have a problem with one of our DB servers, MS SQL 2000 SP4.
> We use SP for weekly maintenance, this SP is stored in the master DB.
> Therefore I had to add a new SP into all DB servers but when I run the
> script on this server I get the error:
> Server: Msg 2714, Level 16, State 5, Procedure
> sp_u_IndexDefragAndRebuild, Line 172
> There is already an object named 'sp_u_IndexDefragAndRebuild' in the
> database.
> The script does have an exist and drop procedure command at the
> beginning. I also tried several naming for the SP but I get always the
> same error.
> While I was googling for a solution a foundhttp://support.microsoft.com/kb/827448/en-us
> a workaround. But when I run this script I get the same error:
> Server: Msg 2714, Level 16, State 6, Line 1
> There is already an object named 'test_table0' in the database.
> and if I try to drop the table I get:
> Server: Msg 3701, Level 11, State 5, Line 1
> Cannot drop the table 'test_table0', because it does not exist in the
> system catalog.
> Is there a solution without restoring the whole master DB? And if I
> have to restore the master DB what do I need to look for?
> I appreciate any help
> Martin
Check whether you have create 'test_table0' in the SP more than
once.
Also make sure that table is in master and check the owner of the
table ( may not be dbo)
|||On Aug 3, 9:20 am, M A Srinivas <masri...@.gmail.com> wrote:
> On Aug 3, 12:03 pm, DBA <martin-za.fr...@.ubs.com> wrote:
>
>
>
>
>
>
>
>
> Check whether you have create 'test_table0' in the SP more than
> once.
> Also make sure that table is in master and check the owner of the
> table ( may not be dbo)- Hide quoted text -
> - Show quoted text -
no, the 'test_table0' was not created, not even once but the error
says that there is already a table. If I go throught sysobject I cant
find the SP and if I try to add a table manually by right mousbutton
and new table... I get the same error:
'test_tab' table
- Unable to create table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]There is
already an object named 'test_tab' in the database.
same happens if I try to add a new SP manually:
Error 2714: There is already an object named 'test_sp' in the database
|||On Aug 3, 12:54 pm, DBA <martin-za.fr...@.ubs.com> wrote:
> On Aug 3, 9:20 am, M A Srinivas <masri...@.gmail.com> wrote:
>
>
>
>
>
>
>
>
>
>
> no, the 'test_table0' was not created, not even once but the error
> says that there is already a table. If I go throught sysobject I cant
> find the SP and if I try to add a table manually by right mousbutton
> and new table... I get the same error:
> 'test_tab' table
> - Unable to create table.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]There is
> already an object named 'test_tab' in the database.
> same happens if I try to add a new SP manually:
> Error 2714: There is already an object named 'test_sp' in the database- Hide quoted text -
> - Show quoted text -
Following may point to some of the issues
1. Check database connection in ODBC pointing to intended server/
instance and database
2. ODBC user may be connected with a sql server user and there may be
table name with owner being the connected user . If sql user connected
through ODBC is abcuser check any table with abcuser is present in the
database
3. No spelling mistakes in object names (table, SP etc)
4. Multiple users are connecting to your database and trying to run
same code (create table, create procedure etc) . I suggest keep
creating tables , SPs as a separate task . Do not mingle this with
your application
|||On Aug 3, 10:49 am, M A Srinivas <masri...@.gmail.com> wrote:
> On Aug 3, 12:54 pm, DBA <martin-za.fr...@.ubs.com> wrote:
>
>
>
>
>
>
>
>
>
>
>
>
>
> Following may point to some of the issues
> 1. Check database connection in ODBC pointing to intended server/
> instance and database
> 2. ODBC user may be connected with a sql server user and there may be
> table name with owner being the connected user . If sql user connected
> through ODBC is abcuser check any table with abcuser is present in the
> database
> 3. No spelling mistakes in object names (table, SP etc)
> 4. Multiple users are connecting to your database and trying to run
> same code (create table, create procedure etc) . I suggest keep
> creating tables , SPs as a separate task . Do not mingle this with
> your application- Hide quoted text -
> - Show quoted text -
Thanks for your input but all of them look good.
I am the DBA and nobody else is allowed to use master DB or add any
tables/SP to the master DB. There are some users connected to other
DBs but without permission to add objects to any DB.
I don't think this is an user issue, I rather believe there is
something wrong with my master DB.
http://support.microsoft.com/kb/827448/en-us
The object ID is already in use and the existing object has rows in
the syscolumns system table. When you try to create a stored
procedure, a failure may occur with error number 134. This problem
occurs because the syscolumns table already contains a row with the
same values for the objid, the colid, and the paramid columns.
How can I find out what object ID does that SP uses if I can't find it
in sysobject of the master DB?
thanks
Martin
Showing posts with label master. Show all posts
Showing posts with label master. Show all posts
Thursday, March 22, 2012
Can't add any SP to master DB
Hello
I have a problem with one of our DB servers, MS SQL 2000 SP4.
We use SP for weekly maintenance, this SP is stored in the master DB.
Therefore I had to add a new SP into all DB servers but when I run the
script on this server I get the error:
Server: Msg 2714, Level 16, State 5, Procedure
sp_u_IndexDefragAndRebuild, Line 172
There is already an object named 'sp_u_IndexDefragAndRebuild' in the
database.
The script does have an exist and drop procedure command at the
beginning. I also tried several naming for the SP but I get always the
same error.
While I was googling for a solution a found http://support.microsoft.com/kb/827448
/en-us
a workaround. But when I run this script I get the same error:
Server: Msg 2714, Level 16, State 6, Line 1
There is already an object named 'test_table0' in the database.
and if I try to drop the table I get:
Server: Msg 3701, Level 11, State 5, Line 1
Cannot drop the table 'test_table0', because it does not exist in the
system catalog.
Is there a solution without restoring the whole master DB? And if I
have to restore the master DB what do I need to look for?
I appreciate any help
MartinOn Aug 3, 12:03 pm, DBA <martin-za.fr...@.ubs.com> wrote:
> Hello
> I have a problem with one of our DB servers, MS SQL 2000 SP4.
> We use SP for weekly maintenance, this SP is stored in the master DB.
> Therefore I had to add a new SP into all DB servers but when I run the
> script on this server I get the error:
> Server: Msg 2714, Level 16, State 5, Procedure
> sp_u_IndexDefragAndRebuild, Line 172
> There is already an object named 'sp_u_IndexDefragAndRebuild' in the
> database.
> The script does have an exist and drop procedure command at the
> beginning. I also tried several naming for the SP but I get always the
> same error.
> While I was googling for a solution a foundhttp://support.microsoft.com/kb
/827448/en-us
> a workaround. But when I run this script I get the same error:
> Server: Msg 2714, Level 16, State 6, Line 1
> There is already an object named 'test_table0' in the database.
> and if I try to drop the table I get:
> Server: Msg 3701, Level 11, State 5, Line 1
> Cannot drop the table 'test_table0', because it does not exist in the
> system catalog.
> Is there a solution without restoring the whole master DB? And if I
> have to restore the master DB what do I need to look for?
> I appreciate any help
> Martin
Check whether you have create 'test_table0' in the SP more than
once.
Also make sure that table is in master and check the owner of the
table ( may not be dbo)|||On Aug 3, 9:20 am, M A Srinivas <masri...@.gmail.com> wrote:
> On Aug 3, 12:03 pm, DBA <martin-za.fr...@.ubs.com> wrote:
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> Check whether you have create 'test_table0' in the SP more than
> once.
> Also make sure that table is in master and check the owner of the
> table ( may not be dbo)- Hide quoted text -
> - Show quoted text -
no, the 'test_table0' was not created, not even once but the error
says that there is already a table. If I go throught sysobject I cant
find the SP and if I try to add a table manually by right mousbutton
and new table... I get the same error:
'test_tab' table
- Unable to create table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]There
is
already an object named 'test_tab' in the database.
same happens if I try to add a new SP manually:
Error 2714: There is already an object named 'test_sp' in the database|||On Aug 3, 12:54 pm, DBA <martin-za.fr...@.ubs.com> wrote:
> On Aug 3, 9:20 am, M A Srinivas <masri...@.gmail.com> wrote:
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> no, the 'test_table0' was not created, not even once but the error
> says that there is already a table. If I go throught sysobject I cant
> find the SP and if I try to add a table manually by right mousbutton
> and new table... I get the same error:
> 'test_tab' table
> - Unable to create table.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The
re is
> already an object named 'test_tab' in the database.
> same happens if I try to add a new SP manually:
> Error 2714: There is already an object named 'test_sp' in the database- Hi
de quoted text -
> - Show quoted text -
Following may point to some of the issues
1. Check database connection in ODBC pointing to intended server/
instance and database
2. ODBC user may be connected with a sql server user and there may be
table name with owner being the connected user . If sql user connected
through ODBC is abcuser check any table with abcuser is present in the
database
3. No spelling mistakes in object names (table, SP etc)
4. Multiple users are connecting to your database and trying to run
same code (create table, create procedure etc) . I suggest keep
creating tables , SPs as a separate task . Do not mingle this with
your application|||On Aug 3, 10:49 am, M A Srinivas <masri...@.gmail.com> wrote:
> On Aug 3, 12:54 pm, DBA <martin-za.fr...@.ubs.com> wrote:
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> Following may point to some of the issues
> 1. Check database connection in ODBC pointing to intended server/
> instance and database
> 2. ODBC user may be connected with a sql server user and there may be
> table name with owner being the connected user . If sql user connected
> through ODBC is abcuser check any table with abcuser is present in the
> database
> 3. No spelling mistakes in object names (table, SP etc)
> 4. Multiple users are connecting to your database and trying to run
> same code (create table, create procedure etc) . I suggest keep
> creating tables , SPs as a separate task . Do not mingle this with
> your application- Hide quoted text -
> - Show quoted text -
Thanks for your input but all of them look good.
I am the DBA and nobody else is allowed to use master DB or add any
tables/SP to the master DB. There are some users connected to other
DBs but without permission to add objects to any DB.
I don't think this is an user issue, I rather believe there is
something wrong with my master DB.
http://support.microsoft.com/kb/827448/en-us
The object ID is already in use and the existing object has rows in
the syscolumns system table. When you try to create a stored
procedure, a failure may occur with error number 134. This problem
occurs because the syscolumns table already contains a row with the
same values for the objid, the colid, and the paramid columns.
How can I find out what object ID does that SP uses if I can't find it
in sysobject of the master DB?
thanks
Martin
I have a problem with one of our DB servers, MS SQL 2000 SP4.
We use SP for weekly maintenance, this SP is stored in the master DB.
Therefore I had to add a new SP into all DB servers but when I run the
script on this server I get the error:
Server: Msg 2714, Level 16, State 5, Procedure
sp_u_IndexDefragAndRebuild, Line 172
There is already an object named 'sp_u_IndexDefragAndRebuild' in the
database.
The script does have an exist and drop procedure command at the
beginning. I also tried several naming for the SP but I get always the
same error.
While I was googling for a solution a found http://support.microsoft.com/kb/827448
/en-us
a workaround. But when I run this script I get the same error:
Server: Msg 2714, Level 16, State 6, Line 1
There is already an object named 'test_table0' in the database.
and if I try to drop the table I get:
Server: Msg 3701, Level 11, State 5, Line 1
Cannot drop the table 'test_table0', because it does not exist in the
system catalog.
Is there a solution without restoring the whole master DB? And if I
have to restore the master DB what do I need to look for?
I appreciate any help
MartinOn Aug 3, 12:03 pm, DBA <martin-za.fr...@.ubs.com> wrote:
> Hello
> I have a problem with one of our DB servers, MS SQL 2000 SP4.
> We use SP for weekly maintenance, this SP is stored in the master DB.
> Therefore I had to add a new SP into all DB servers but when I run the
> script on this server I get the error:
> Server: Msg 2714, Level 16, State 5, Procedure
> sp_u_IndexDefragAndRebuild, Line 172
> There is already an object named 'sp_u_IndexDefragAndRebuild' in the
> database.
> The script does have an exist and drop procedure command at the
> beginning. I also tried several naming for the SP but I get always the
> same error.
> While I was googling for a solution a foundhttp://support.microsoft.com/kb
/827448/en-us
> a workaround. But when I run this script I get the same error:
> Server: Msg 2714, Level 16, State 6, Line 1
> There is already an object named 'test_table0' in the database.
> and if I try to drop the table I get:
> Server: Msg 3701, Level 11, State 5, Line 1
> Cannot drop the table 'test_table0', because it does not exist in the
> system catalog.
> Is there a solution without restoring the whole master DB? And if I
> have to restore the master DB what do I need to look for?
> I appreciate any help
> Martin
Check whether you have create 'test_table0' in the SP more than
once.
Also make sure that table is in master and check the owner of the
table ( may not be dbo)|||On Aug 3, 9:20 am, M A Srinivas <masri...@.gmail.com> wrote:
> On Aug 3, 12:03 pm, DBA <martin-za.fr...@.ubs.com> wrote:
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> Check whether you have create 'test_table0' in the SP more than
> once.
> Also make sure that table is in master and check the owner of the
> table ( may not be dbo)- Hide quoted text -
> - Show quoted text -
no, the 'test_table0' was not created, not even once but the error
says that there is already a table. If I go throught sysobject I cant
find the SP and if I try to add a table manually by right mousbutton
and new table... I get the same error:
'test_tab' table
- Unable to create table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]There
is
already an object named 'test_tab' in the database.
same happens if I try to add a new SP manually:
Error 2714: There is already an object named 'test_sp' in the database|||On Aug 3, 12:54 pm, DBA <martin-za.fr...@.ubs.com> wrote:
> On Aug 3, 9:20 am, M A Srinivas <masri...@.gmail.com> wrote:
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> no, the 'test_table0' was not created, not even once but the error
> says that there is already a table. If I go throught sysobject I cant
> find the SP and if I try to add a table manually by right mousbutton
> and new table... I get the same error:
> 'test_tab' table
> - Unable to create table.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The
re is
> already an object named 'test_tab' in the database.
> same happens if I try to add a new SP manually:
> Error 2714: There is already an object named 'test_sp' in the database- Hi
de quoted text -
> - Show quoted text -
Following may point to some of the issues
1. Check database connection in ODBC pointing to intended server/
instance and database
2. ODBC user may be connected with a sql server user and there may be
table name with owner being the connected user . If sql user connected
through ODBC is abcuser check any table with abcuser is present in the
database
3. No spelling mistakes in object names (table, SP etc)
4. Multiple users are connecting to your database and trying to run
same code (create table, create procedure etc) . I suggest keep
creating tables , SPs as a separate task . Do not mingle this with
your application|||On Aug 3, 10:49 am, M A Srinivas <masri...@.gmail.com> wrote:
> On Aug 3, 12:54 pm, DBA <martin-za.fr...@.ubs.com> wrote:
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> Following may point to some of the issues
> 1. Check database connection in ODBC pointing to intended server/
> instance and database
> 2. ODBC user may be connected with a sql server user and there may be
> table name with owner being the connected user . If sql user connected
> through ODBC is abcuser check any table with abcuser is present in the
> database
> 3. No spelling mistakes in object names (table, SP etc)
> 4. Multiple users are connecting to your database and trying to run
> same code (create table, create procedure etc) . I suggest keep
> creating tables , SPs as a separate task . Do not mingle this with
> your application- Hide quoted text -
> - Show quoted text -
Thanks for your input but all of them look good.
I am the DBA and nobody else is allowed to use master DB or add any
tables/SP to the master DB. There are some users connected to other
DBs but without permission to add objects to any DB.
I don't think this is an user issue, I rather believe there is
something wrong with my master DB.
http://support.microsoft.com/kb/827448/en-us
The object ID is already in use and the existing object has rows in
the syscolumns system table. When you try to create a stored
procedure, a failure may occur with error number 134. This problem
occurs because the syscolumns table already contains a row with the
same values for the objid, the colid, and the paramid columns.
How can I find out what object ID does that SP uses if I can't find it
in sysobject of the master DB?
thanks
Martin
Can't add any SP to master DB
Hello
I have a problem with one of our DB servers, MS SQL 2000 SP4.
We use SP for weekly maintenance, this SP is stored in the master DB.
Therefore I had to add a new SP into all DB servers but when I run the
script on this server I get the error:
Server: Msg 2714, Level 16, State 5, Procedure
sp_u_IndexDefragAndRebuild, Line 172
There is already an object named 'sp_u_IndexDefragAndRebuild' in the
database.
The script does have an exist and drop procedure command at the
beginning. I also tried several naming for the SP but I get always the
same error.
While I was googling for a solution a found http://support.microsoft.com/kb/827448/en-us
a workaround. But when I run this script I get the same error:
Server: Msg 2714, Level 16, State 6, Line 1
There is already an object named 'test_table0' in the database.
and if I try to drop the table I get:
Server: Msg 3701, Level 11, State 5, Line 1
Cannot drop the table 'test_table0', because it does not exist in the
system catalog.
Is there a solution without restoring the whole master DB? And if I
have to restore the master DB what do I need to look for?
I appreciate any help
MartinOn Aug 3, 12:03 pm, DBA <martin-za.fr...@.ubs.com> wrote:
> Hello
> I have a problem with one of our DB servers, MS SQL 2000 SP4.
> We use SP for weekly maintenance, this SP is stored in the master DB.
> Therefore I had to add a new SP into all DB servers but when I run the
> script on this server I get the error:
> Server: Msg 2714, Level 16, State 5, Procedure
> sp_u_IndexDefragAndRebuild, Line 172
> There is already an object named 'sp_u_IndexDefragAndRebuild' in the
> database.
> The script does have an exist and drop procedure command at the
> beginning. I also tried several naming for the SP but I get always the
> same error.
> While I was googling for a solution a foundhttp://support.microsoft.com/kb/827448/en-us
> a workaround. But when I run this script I get the same error:
> Server: Msg 2714, Level 16, State 6, Line 1
> There is already an object named 'test_table0' in the database.
> and if I try to drop the table I get:
> Server: Msg 3701, Level 11, State 5, Line 1
> Cannot drop the table 'test_table0', because it does not exist in the
> system catalog.
> Is there a solution without restoring the whole master DB? And if I
> have to restore the master DB what do I need to look for?
> I appreciate any help
> Martin
Check whether you have create 'test_table0' in the SP more than
once.
Also make sure that table is in master and check the owner of the
table ( may not be dbo)|||On Aug 3, 9:20 am, M A Srinivas <masri...@.gmail.com> wrote:
> On Aug 3, 12:03 pm, DBA <martin-za.fr...@.ubs.com> wrote:
>
>
> > Hello
> > I have a problem with one of our DB servers, MS SQL 2000 SP4.
> > We use SP for weekly maintenance, this SP is stored in the master DB.
> > Therefore I had to add a new SP into all DB servers but when I run the
> > script on this server I get the error:
> > Server: Msg 2714, Level 16, State 5, Procedure
> > sp_u_IndexDefragAndRebuild, Line 172
> > There is already an object named 'sp_u_IndexDefragAndRebuild' in the
> > database.
> > The script does have an exist and drop procedure command at the
> > beginning. I also tried several naming for the SP but I get always the
> > same error.
> > While I was googling for a solution a foundhttp://support.microsoft.com/kb/827448/en-us
> > a workaround. But when I run this script I get the same error:
> > Server: Msg 2714, Level 16, State 6, Line 1
> > There is already an object named 'test_table0' in the database.
> > and if I try to drop the table I get:
> > Server: Msg 3701, Level 11, State 5, Line 1
> > Cannot drop the table 'test_table0', because it does not exist in the
> > system catalog.
> > Is there a solution without restoring the whole master DB? And if I
> > have to restore the master DB what do I need to look for?
> > I appreciate any help
> > Martin
> Check whether you have create 'test_table0' in the SP more than
> once.
> Also make sure that table is in master and check the owner of the
> table ( may not be dbo)- Hide quoted text -
> - Show quoted text -
no, the 'test_table0' was not created, not even once but the error
says that there is already a table. If I go throught sysobject I cant
find the SP and if I try to add a table manually by right mousbutton
and new table... I get the same error:
'test_tab' table
- Unable to create table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]There is
already an object named 'test_tab' in the database.
same happens if I try to add a new SP manually:
Error 2714: There is already an object named 'test_sp' in the database|||On Aug 3, 12:54 pm, DBA <martin-za.fr...@.ubs.com> wrote:
> On Aug 3, 9:20 am, M A Srinivas <masri...@.gmail.com> wrote:
>
>
> > On Aug 3, 12:03 pm, DBA <martin-za.fr...@.ubs.com> wrote:
> > > Hello
> > > I have a problem with one of our DB servers, MS SQL 2000 SP4.
> > > We use SP for weekly maintenance, this SP is stored in the master DB.
> > > Therefore I had to add a new SP into all DB servers but when I run the
> > > script on this server I get the error:
> > > Server: Msg 2714, Level 16, State 5, Procedure
> > > sp_u_IndexDefragAndRebuild, Line 172
> > > There is already an object named 'sp_u_IndexDefragAndRebuild' in the
> > > database.
> > > The script does have an exist and drop procedure command at the
> > > beginning. I also tried several naming for the SP but I get always the
> > > same error.
> > > While I was googling for a solution a foundhttp://support.microsoft.com/kb/827448/en-us
> > > a workaround. But when I run this script I get the same error:
> > > Server: Msg 2714, Level 16, State 6, Line 1
> > > There is already an object named 'test_table0' in the database.
> > > and if I try to drop the table I get:
> > > Server: Msg 3701, Level 11, State 5, Line 1
> > > Cannot drop the table 'test_table0', because it does not exist in the
> > > system catalog.
> > > Is there a solution without restoring the whole master DB? And if I
> > > have to restore the master DB what do I need to look for?
> > > I appreciate any help
> > > Martin
> > Check whether you have create 'test_table0' in the SP more than
> > once.
> > Also make sure that table is in master and check the owner of the
> > table ( may not be dbo)- Hide quoted text -
> > - Show quoted text -
> no, the 'test_table0' was not created, not even once but the error
> says that there is already a table. If I go throught sysobject I cant
> find the SP and if I try to add a table manually by right mousbutton
> and new table... I get the same error:
> 'test_tab' table
> - Unable to create table.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]There is
> already an object named 'test_tab' in the database.
> same happens if I try to add a new SP manually:
> Error 2714: There is already an object named 'test_sp' in the database- Hide quoted text -
> - Show quoted text -
Following may point to some of the issues
1. Check database connection in ODBC pointing to intended server/
instance and database
2. ODBC user may be connected with a sql server user and there may be
table name with owner being the connected user . If sql user connected
through ODBC is abcuser check any table with abcuser is present in the
database
3. No spelling mistakes in object names (table, SP etc)
4. Multiple users are connecting to your database and trying to run
same code (create table, create procedure etc) . I suggest keep
creating tables , SPs as a separate task . Do not mingle this with
your application|||On Aug 3, 10:49 am, M A Srinivas <masri...@.gmail.com> wrote:
> On Aug 3, 12:54 pm, DBA <martin-za.fr...@.ubs.com> wrote:
>
>
> > On Aug 3, 9:20 am, M A Srinivas <masri...@.gmail.com> wrote:
> > > On Aug 3, 12:03 pm, DBA <martin-za.fr...@.ubs.com> wrote:
> > > > Hello
> > > > I have a problem with one of our DB servers, MS SQL 2000 SP4.
> > > > We use SP for weekly maintenance, this SP is stored in the master DB.
> > > > Therefore I had to add a new SP into all DB servers but when I run the
> > > > script on this server I get the error:
> > > > Server: Msg 2714, Level 16, State 5, Procedure
> > > > sp_u_IndexDefragAndRebuild, Line 172
> > > > There is already an object named 'sp_u_IndexDefragAndRebuild' in the
> > > > database.
> > > > The script does have an exist and drop procedure command at the
> > > > beginning. I also tried several naming for the SP but I get always the
> > > > same error.
> > > > While I was googling for a solution a foundhttp://support.microsoft.com/kb/827448/en-us
> > > > a workaround. But when I run this script I get the same error:
> > > > Server: Msg 2714, Level 16, State 6, Line 1
> > > > There is already an object named 'test_table0' in the database.
> > > > and if I try to drop the table I get:
> > > > Server: Msg 3701, Level 11, State 5, Line 1
> > > > Cannot drop the table 'test_table0', because it does not exist in the
> > > > system catalog.
> > > > Is there a solution without restoring the whole master DB? And if I
> > > > have to restore the master DB what do I need to look for?
> > > > I appreciate any help
> > > > Martin
> > > Check whether you have create 'test_table0' in the SP more than
> > > once.
> > > Also make sure that table is in master and check the owner of the
> > > table ( may not be dbo)- Hide quoted text -
> > > - Show quoted text -
> > no, the 'test_table0' was not created, not even once but the error
> > says that there is already a table. If I go throught sysobject I cant
> > find the SP and if I try to add a table manually by right mousbutton
> > and new table... I get the same error:
> > 'test_tab' table
> > - Unable to create table.
> > ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]There is
> > already an object named 'test_tab' in the database.
> > same happens if I try to add a new SP manually:
> > Error 2714: There is already an object named 'test_sp' in the database- Hide quoted text -
> > - Show quoted text -
> Following may point to some of the issues
> 1. Check database connection in ODBC pointing to intended server/
> instance and database
> 2. ODBC user may be connected with a sql server user and there may be
> table name with owner being the connected user . If sql user connected
> through ODBC is abcuser check any table with abcuser is present in the
> database
> 3. No spelling mistakes in object names (table, SP etc)
> 4. Multiple users are connecting to your database and trying to run
> same code (create table, create procedure etc) . I suggest keep
> creating tables , SPs as a separate task . Do not mingle this with
> your application- Hide quoted text -
> - Show quoted text -
Thanks for your input but all of them look good.
I am the DBA and nobody else is allowed to use master DB or add any
tables/SP to the master DB. There are some users connected to other
DBs but without permission to add objects to any DB.
I don't think this is an user issue, I rather believe there is
something wrong with my master DB.
http://support.microsoft.com/kb/827448/en-us
The object ID is already in use and the existing object has rows in
the syscolumns system table. When you try to create a stored
procedure, a failure may occur with error number 134. This problem
occurs because the syscolumns table already contains a row with the
same values for the objid, the colid, and the paramid columns.
How can I find out what object ID does that SP uses if I can't find it
in sysobject of the master DB?
thanks
Martin
I have a problem with one of our DB servers, MS SQL 2000 SP4.
We use SP for weekly maintenance, this SP is stored in the master DB.
Therefore I had to add a new SP into all DB servers but when I run the
script on this server I get the error:
Server: Msg 2714, Level 16, State 5, Procedure
sp_u_IndexDefragAndRebuild, Line 172
There is already an object named 'sp_u_IndexDefragAndRebuild' in the
database.
The script does have an exist and drop procedure command at the
beginning. I also tried several naming for the SP but I get always the
same error.
While I was googling for a solution a found http://support.microsoft.com/kb/827448/en-us
a workaround. But when I run this script I get the same error:
Server: Msg 2714, Level 16, State 6, Line 1
There is already an object named 'test_table0' in the database.
and if I try to drop the table I get:
Server: Msg 3701, Level 11, State 5, Line 1
Cannot drop the table 'test_table0', because it does not exist in the
system catalog.
Is there a solution without restoring the whole master DB? And if I
have to restore the master DB what do I need to look for?
I appreciate any help
MartinOn Aug 3, 12:03 pm, DBA <martin-za.fr...@.ubs.com> wrote:
> Hello
> I have a problem with one of our DB servers, MS SQL 2000 SP4.
> We use SP for weekly maintenance, this SP is stored in the master DB.
> Therefore I had to add a new SP into all DB servers but when I run the
> script on this server I get the error:
> Server: Msg 2714, Level 16, State 5, Procedure
> sp_u_IndexDefragAndRebuild, Line 172
> There is already an object named 'sp_u_IndexDefragAndRebuild' in the
> database.
> The script does have an exist and drop procedure command at the
> beginning. I also tried several naming for the SP but I get always the
> same error.
> While I was googling for a solution a foundhttp://support.microsoft.com/kb/827448/en-us
> a workaround. But when I run this script I get the same error:
> Server: Msg 2714, Level 16, State 6, Line 1
> There is already an object named 'test_table0' in the database.
> and if I try to drop the table I get:
> Server: Msg 3701, Level 11, State 5, Line 1
> Cannot drop the table 'test_table0', because it does not exist in the
> system catalog.
> Is there a solution without restoring the whole master DB? And if I
> have to restore the master DB what do I need to look for?
> I appreciate any help
> Martin
Check whether you have create 'test_table0' in the SP more than
once.
Also make sure that table is in master and check the owner of the
table ( may not be dbo)|||On Aug 3, 9:20 am, M A Srinivas <masri...@.gmail.com> wrote:
> On Aug 3, 12:03 pm, DBA <martin-za.fr...@.ubs.com> wrote:
>
>
> > Hello
> > I have a problem with one of our DB servers, MS SQL 2000 SP4.
> > We use SP for weekly maintenance, this SP is stored in the master DB.
> > Therefore I had to add a new SP into all DB servers but when I run the
> > script on this server I get the error:
> > Server: Msg 2714, Level 16, State 5, Procedure
> > sp_u_IndexDefragAndRebuild, Line 172
> > There is already an object named 'sp_u_IndexDefragAndRebuild' in the
> > database.
> > The script does have an exist and drop procedure command at the
> > beginning. I also tried several naming for the SP but I get always the
> > same error.
> > While I was googling for a solution a foundhttp://support.microsoft.com/kb/827448/en-us
> > a workaround. But when I run this script I get the same error:
> > Server: Msg 2714, Level 16, State 6, Line 1
> > There is already an object named 'test_table0' in the database.
> > and if I try to drop the table I get:
> > Server: Msg 3701, Level 11, State 5, Line 1
> > Cannot drop the table 'test_table0', because it does not exist in the
> > system catalog.
> > Is there a solution without restoring the whole master DB? And if I
> > have to restore the master DB what do I need to look for?
> > I appreciate any help
> > Martin
> Check whether you have create 'test_table0' in the SP more than
> once.
> Also make sure that table is in master and check the owner of the
> table ( may not be dbo)- Hide quoted text -
> - Show quoted text -
no, the 'test_table0' was not created, not even once but the error
says that there is already a table. If I go throught sysobject I cant
find the SP and if I try to add a table manually by right mousbutton
and new table... I get the same error:
'test_tab' table
- Unable to create table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]There is
already an object named 'test_tab' in the database.
same happens if I try to add a new SP manually:
Error 2714: There is already an object named 'test_sp' in the database|||On Aug 3, 12:54 pm, DBA <martin-za.fr...@.ubs.com> wrote:
> On Aug 3, 9:20 am, M A Srinivas <masri...@.gmail.com> wrote:
>
>
> > On Aug 3, 12:03 pm, DBA <martin-za.fr...@.ubs.com> wrote:
> > > Hello
> > > I have a problem with one of our DB servers, MS SQL 2000 SP4.
> > > We use SP for weekly maintenance, this SP is stored in the master DB.
> > > Therefore I had to add a new SP into all DB servers but when I run the
> > > script on this server I get the error:
> > > Server: Msg 2714, Level 16, State 5, Procedure
> > > sp_u_IndexDefragAndRebuild, Line 172
> > > There is already an object named 'sp_u_IndexDefragAndRebuild' in the
> > > database.
> > > The script does have an exist and drop procedure command at the
> > > beginning. I also tried several naming for the SP but I get always the
> > > same error.
> > > While I was googling for a solution a foundhttp://support.microsoft.com/kb/827448/en-us
> > > a workaround. But when I run this script I get the same error:
> > > Server: Msg 2714, Level 16, State 6, Line 1
> > > There is already an object named 'test_table0' in the database.
> > > and if I try to drop the table I get:
> > > Server: Msg 3701, Level 11, State 5, Line 1
> > > Cannot drop the table 'test_table0', because it does not exist in the
> > > system catalog.
> > > Is there a solution without restoring the whole master DB? And if I
> > > have to restore the master DB what do I need to look for?
> > > I appreciate any help
> > > Martin
> > Check whether you have create 'test_table0' in the SP more than
> > once.
> > Also make sure that table is in master and check the owner of the
> > table ( may not be dbo)- Hide quoted text -
> > - Show quoted text -
> no, the 'test_table0' was not created, not even once but the error
> says that there is already a table. If I go throught sysobject I cant
> find the SP and if I try to add a table manually by right mousbutton
> and new table... I get the same error:
> 'test_tab' table
> - Unable to create table.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]There is
> already an object named 'test_tab' in the database.
> same happens if I try to add a new SP manually:
> Error 2714: There is already an object named 'test_sp' in the database- Hide quoted text -
> - Show quoted text -
Following may point to some of the issues
1. Check database connection in ODBC pointing to intended server/
instance and database
2. ODBC user may be connected with a sql server user and there may be
table name with owner being the connected user . If sql user connected
through ODBC is abcuser check any table with abcuser is present in the
database
3. No spelling mistakes in object names (table, SP etc)
4. Multiple users are connecting to your database and trying to run
same code (create table, create procedure etc) . I suggest keep
creating tables , SPs as a separate task . Do not mingle this with
your application|||On Aug 3, 10:49 am, M A Srinivas <masri...@.gmail.com> wrote:
> On Aug 3, 12:54 pm, DBA <martin-za.fr...@.ubs.com> wrote:
>
>
> > On Aug 3, 9:20 am, M A Srinivas <masri...@.gmail.com> wrote:
> > > On Aug 3, 12:03 pm, DBA <martin-za.fr...@.ubs.com> wrote:
> > > > Hello
> > > > I have a problem with one of our DB servers, MS SQL 2000 SP4.
> > > > We use SP for weekly maintenance, this SP is stored in the master DB.
> > > > Therefore I had to add a new SP into all DB servers but when I run the
> > > > script on this server I get the error:
> > > > Server: Msg 2714, Level 16, State 5, Procedure
> > > > sp_u_IndexDefragAndRebuild, Line 172
> > > > There is already an object named 'sp_u_IndexDefragAndRebuild' in the
> > > > database.
> > > > The script does have an exist and drop procedure command at the
> > > > beginning. I also tried several naming for the SP but I get always the
> > > > same error.
> > > > While I was googling for a solution a foundhttp://support.microsoft.com/kb/827448/en-us
> > > > a workaround. But when I run this script I get the same error:
> > > > Server: Msg 2714, Level 16, State 6, Line 1
> > > > There is already an object named 'test_table0' in the database.
> > > > and if I try to drop the table I get:
> > > > Server: Msg 3701, Level 11, State 5, Line 1
> > > > Cannot drop the table 'test_table0', because it does not exist in the
> > > > system catalog.
> > > > Is there a solution without restoring the whole master DB? And if I
> > > > have to restore the master DB what do I need to look for?
> > > > I appreciate any help
> > > > Martin
> > > Check whether you have create 'test_table0' in the SP more than
> > > once.
> > > Also make sure that table is in master and check the owner of the
> > > table ( may not be dbo)- Hide quoted text -
> > > - Show quoted text -
> > no, the 'test_table0' was not created, not even once but the error
> > says that there is already a table. If I go throught sysobject I cant
> > find the SP and if I try to add a table manually by right mousbutton
> > and new table... I get the same error:
> > 'test_tab' table
> > - Unable to create table.
> > ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]There is
> > already an object named 'test_tab' in the database.
> > same happens if I try to add a new SP manually:
> > Error 2714: There is already an object named 'test_sp' in the database- Hide quoted text -
> > - Show quoted text -
> Following may point to some of the issues
> 1. Check database connection in ODBC pointing to intended server/
> instance and database
> 2. ODBC user may be connected with a sql server user and there may be
> table name with owner being the connected user . If sql user connected
> through ODBC is abcuser check any table with abcuser is present in the
> database
> 3. No spelling mistakes in object names (table, SP etc)
> 4. Multiple users are connecting to your database and trying to run
> same code (create table, create procedure etc) . I suggest keep
> creating tables , SPs as a separate task . Do not mingle this with
> your application- Hide quoted text -
> - Show quoted text -
Thanks for your input but all of them look good.
I am the DBA and nobody else is allowed to use master DB or add any
tables/SP to the master DB. There are some users connected to other
DBs but without permission to add objects to any DB.
I don't think this is an user issue, I rather believe there is
something wrong with my master DB.
http://support.microsoft.com/kb/827448/en-us
The object ID is already in use and the existing object has rows in
the syscolumns system table. When you try to create a stored
procedure, a failure may occur with error number 134. This problem
occurs because the syscolumns table already contains a row with the
same values for the objid, the colid, and the paramid columns.
How can I find out what object ID does that SP uses if I can't find it
in sysobject of the master DB?
thanks
Martin
Sunday, March 11, 2012
Cannot View Master.sysdatabase Names in Webpage
I have a webpage that should display the database names from the master.sysdatabases in a dropdownlist.
The code for the stored procedure works fine in Query Analyzer and returns the list of all database names under that instance of SQL-Server 2000.
The code:
select name from master.dbo.sysdatabases
order by 1
doesn't return the sysdatabase names to the ASP.NET webpage when executing the stored procedure.
It returns:
System.Data.DataRowView
Why does it do this and how can I fix it?
Tx
I don't know what you are trying to do but the Master database and it's content are Microsoft Property, I don't think you can use them in your web application. If you look inside the Master or look at the System tables Poster you know using those tables in your application is not something you do. Hope this helps.|||Probably a permissions issue.|||Thanks.
The lookup is for our administrative/development purposes.
Since we have multiple databases to search through, the app shows the databases, you select a database, there's a lookup and display of tables, views and sprocs, and the contents are displayed in a large textbox for cutting and pasting purposes.
So the sysdatabases lookup is needed to select though the production databases.
The sproc doing the work has remote privs.
Other than that, how would you assign a priv to access the tables?
I didn't need it in QA.
Your help is appreciated.
|||I don't think it's a security/permissions issue. The built-in stored procedure sp_databases, which does exactly what your sql query does, has its execute permissions default to thepublic role. I think the problem is in your code, so you should post your code snippet here.|||A quick test is to run this in both the webpage and your QA and see the difference:
SELECT SUSER_NAME(),USER_NAME()
That will tell you who are logged into SQL Server as, and what login are you being mapped to.
Although, rereading your original message, herman is most likely correct. It appears that your .NET code is wrong, considering that system.whatever.dataview is a .NET class, I would say that you've assigned a dataview to a variable using the .ToString method, or have done so implicitly.
Wednesday, March 7, 2012
CANNOT START SQL SERVER Service - 18052 NEED HELP here !
Hi ,
This is refering to my previous mail " moving master db to another location"
and really need advise as i messed up the SQL Server !!
below is the series of steps & checks i have made from my earlier postings
1) +++++++++++++++++
Hi ,
I was trying to move both msdb & model using the following steps :
1. addede the parameter -T3608
2a. use master
go
sp_detach_db 'msdb'
go
2b. use master
go
sp_detach_db 'model'
go
3. stop & restart SQL SERVER
4. i could not restart the SQL SERVER Service from the SQL Server Service
Manager as it keeps restarting & stopping by itself
any way i can get it to work now ?
tks & rdgs
2) ++++++++++++++++=
Hi ,
I got the 18502 error (looked into past posting seems no solution)
Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17055
Date: 5/29/2006
Time: 6:11:53 PM
User: N/A
Computer: ALTIRISSERVER
Description:
18052 :
Error: 823, Severity: 24, State: 2.
and
from the SQL Error Log i got the followings :
2006-05-29 18:11:51.41 server Microsoft SQL Server 2000 - 8.00.760 (Intel
X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
2006-05-29 18:11:51.42 server Copyright (C) 1988-2002 Microsoft
Corporation.
2006-05-29 18:11:51.42 server All rights reserved.
2006-05-29 18:11:51.42 server Server Process ID is 2268.
2006-05-29 18:11:51.42 server Logging SQL Server messages in file 'd:\ms
sqlserver\MSSQL\log\ERRORLOG'.
2006-05-29 18:11:51.42 server SQL Server is starting at priority class
'high'(2 CPUs detected).
2006-05-29 18:11:51.44 server Performance monitor shared memory setup
failed: -1
2006-05-29 18:11:51.53 server SQL Server configured for thread mode
processing.
2006-05-29 18:11:51.53 server Using dynamic lock allocation. [2500] Lock
Blocks, [5000] Lock Owner Blocks.
2006-05-29 18:11:51.57 server Attempting to initialize Distributed
Transaction Coordinator.
2006-05-29 18:11:52.60 spid4 Starting up database 'master'.
2006-05-29 18:11:53.85 spid4 Error: 823, Severity: 24, State: 2.
2006-05-29 18:11:53.85 spid4 Cannot recover the master database. Exiting.
Any solutions besidess re-install ?
tks & rdgs
3 +++++++++++++++
Hi ,
further info :
I have checked the registry for the Master db the path is as per the
startup parameters before i moved the 'model' and 'msdb' database
really appreciate all advise
tks & rdgs
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1> 3 +++++++++++++++
> Hi ,
> further info :
> I have checked the registry for the Master db the path is as per the
> startup parameters before i moved the 'model' and 'msdb' database
The startup parameters must be changed to reflect the new master file
locations in order for SQL Server to start.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"maxzsim via SQLMonster.com" <u14644@.uwe> wrote in message
news:60f93fb49965d@.uwe...
> Hi ,
> This is refering to my previous mail " moving master db to another
> location"
> and really need advise as i messed up the SQL Server !!
> below is the series of steps & checks i have made from my earlier postings
> 1) +++++++++++++++++
> Hi ,
> I was trying to move both msdb & model using the following steps :
> 1. addede the parameter -T3608
> 2a. use master
> go
> sp_detach_db 'msdb'
> go
> 2b. use master
> go
> sp_detach_db 'model'
> go
> 3. stop & restart SQL SERVER
> 4. i could not restart the SQL SERVER Service from the SQL Server Service
> Manager as it keeps restarting & stopping by itself
> any way i can get it to work now ?
> tks & rdgs
>
>
> 2) ++++++++++++++++=> Hi ,
> I got the 18502 error (looked into past posting seems no solution)
> Event Type: Error
> Event Source: MSSQLSERVER
> Event Category: (2)
> Event ID: 17055
> Date: 5/29/2006
> Time: 6:11:53 PM
> User: N/A
> Computer: ALTIRISSERVER
> Description:
> 18052 :
> Error: 823, Severity: 24, State: 2.
> and
> from the SQL Error Log i got the followings :
> 2006-05-29 18:11:51.41 server Microsoft SQL Server 2000 - 8.00.760
> (Intel
> X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> 2006-05-29 18:11:51.42 server Copyright (C) 1988-2002 Microsoft
> Corporation.
> 2006-05-29 18:11:51.42 server All rights reserved.
> 2006-05-29 18:11:51.42 server Server Process ID is 2268.
> 2006-05-29 18:11:51.42 server Logging SQL Server messages in file
> 'd:\ms
> sqlserver\MSSQL\log\ERRORLOG'.
> 2006-05-29 18:11:51.42 server SQL Server is starting at priority class
> 'high'(2 CPUs detected).
> 2006-05-29 18:11:51.44 server Performance monitor shared memory setup
> failed: -1
> 2006-05-29 18:11:51.53 server SQL Server configured for thread mode
> processing.
> 2006-05-29 18:11:51.53 server Using dynamic lock allocation. [2500]
> Lock
> Blocks, [5000] Lock Owner Blocks.
> 2006-05-29 18:11:51.57 server Attempting to initialize Distributed
> Transaction Coordinator.
> 2006-05-29 18:11:52.60 spid4 Starting up database 'master'.
> 2006-05-29 18:11:53.85 spid4 Error: 823, Severity: 24, State: 2.
> 2006-05-29 18:11:53.85 spid4 Cannot recover the master database.
> Exiting.
> Any solutions besidess re-install ?
> tks & rdgs
>
>
> 3 +++++++++++++++
> Hi ,
> further info :
> I have checked the registry for the Master db the path is as per the
> startup parameters before i moved the 'model' and 'msdb' database
> really appreciate all advise
> tks & rdgs
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1|||Hi ,
But i did not move the Master file i moved only 'model' and 'msdb' . will
this affect the master db's parameter as well ?
tks & rdgs
Dan Guzman wrote:
>> 3 +++++++++++++++
>[quoted text clipped - 4 lines]
>> I have checked the registry for the Master db the path is as per the
>> startup parameters before i moved the 'model' and 'msdb' database
>The startup parameters must be changed to reflect the new master file
>locations in order for SQL Server to start.
>> Hi ,
>[quoted text clipped - 95 lines]
>> tks & rdgs
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1|||Moving model and msdb files will not affect the master database file
locations but you mentioned moving master in your other thread and it looks
to me like this problem may be related.
Check to ensure the master database files are at the locations specified by
the registry startup parameters.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"maxzsim via SQLMonster.com" <u14644@.uwe> wrote in message
news:60f9d688bc36d@.uwe...
> Hi ,
> But i did not move the Master file i moved only 'model' and 'msdb' . will
> this affect the master db's parameter as well ?
>
> tks & rdgs
> Dan Guzman wrote:
>> 3 +++++++++++++++
>>[quoted text clipped - 4 lines]
>> I have checked the registry for the Master db the path is as per the
>> startup parameters before i moved the 'model' and 'msdb' database
>>The startup parameters must be changed to reflect the new master file
>>locations in order for SQL Server to start.
>> Hi ,
>>[quoted text clipped - 95 lines]
>> tks & rdgs
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1|||Hi,
That was my intention to move master to another location but before i could
do that i wasn't able to start SQL server service after moving both the model
& msdb db to a new location at the same time
tks & rdgs
Dan Guzman wrote:
>Moving model and msdb files will not affect the master database file
>locations but you mentioned moving master in your other thread and it looks
>to me like this problem may be related.
>Check to ensure the master database files are at the locations specified by
>the registry startup parameters.
>> Hi ,
>[quoted text clipped - 17 lines]
>> tks & rdgs
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1|||Hi ,
This is the latest details from the Error Log :
2006-05-29 22:18:11.46 server Microsoft SQL Server 2000 - 8.00.760 (Intel
X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
2006-05-29 22:18:11.46 server Copyright (C) 1988-2002 Microsoft
Corporation.
2006-05-29 22:18:11.46 server All rights reserved.
2006-05-29 22:18:11.46 server Server Process ID is 1672.
2006-05-29 22:18:11.46 server Logging SQL Server messages in file 'd:\ms
sqlserver\MSSQL\log\ERRORLOG'.
2006-05-29 22:18:11.47 server SQL Server is starting at priority class
'high'(2 CPUs detected).
2006-05-29 22:18:11.61 server SQL Server configured for thread mode
processing.
2006-05-29 22:18:11.61 server Using dynamic lock allocation. [2500] Lock
Blocks, [5000] Lock Owner Blocks.
2006-05-29 22:18:11.64 server Attempting to initialize Distributed
Transaction Coordinator.
2006-05-29 22:18:11.67 server Failed to obtain
TransactionDispenserInterface: Result Code = 0x8004d01b
2006-05-29 22:18:11.67 spid3 Starting up database 'master'.
2006-05-29 22:18:11.83 server Using 'SSNETLIB.DLL' version '8.0.766'.
2006-05-29 22:18:11.85 server SQL server listening on 10.224.1.9: 1433.
2006-05-29 22:18:11.85 server SQL server listening on 127.0.0.1: 1433.
2006-05-29 22:18:11.85 spid3 Server name is 'ALTIRISSERVER'.
2006-05-29 22:18:11.85 spid8 Starting up database 'Altiris'.
2006-05-29 22:18:11.86 spid10 Starting up database 'Altiris_Incidents'.
2006-05-29 22:18:11.86 spid9 Starting up database 'SidataSQL_DC'.
2006-05-29 22:18:11.86 spid11 Starting up database 'ScalaDB_SGO_Restore'.
2006-05-29 22:18:11.86 spid12 Starting up database 'ScalaDB_SGO_TEST'.
2006-05-29 22:18:11.86 spid5 Could not find database ID 3. Database may
not be activated yet or may be in transition.
which database does ID 3 refers to ?
tks & rdgs
maxzsim wrote:
>Hi,
> That was my intention to move master to another location but before i could
>do that i wasn't able to start SQL server service after moving both the model
>& msdb db to a new location at the same time
>tks & rdgs
>>Moving model and msdb files will not affect the master database file
>>locations but you mentioned moving master in your other thread and it looks
>[quoted text clipped - 8 lines]
>> tks & rdgs
--
Message posted via http://www.sqlmonster.com|||Error 823 is usually preceded by a detailed message about the cause of the
I/O error. Do you have any other related messages in the event log? Are
you moving these database files because a disk is going bad?
--
Hope this helps.
Dan Guzman
SQL Server MVP
"maxzsim via SQLMonster.com" <u14644@.uwe> wrote in message
news:60fa3a34e6e1e@.uwe...
> Hi,
> That was my intention to move master to another location but before i
> could
> do that i wasn't able to start SQL server service after moving both the
> model
> & msdb db to a new location at the same time
> tks & rdgs
> Dan Guzman wrote:
>>Moving model and msdb files will not affect the master database file
>>locations but you mentioned moving master in your other thread and it
>>looks
>>to me like this problem may be related.
>>Check to ensure the master database files are at the locations specified
>>by
>>the registry startup parameters.
>> Hi ,
>>[quoted text clipped - 17 lines]
>> tks & rdgs
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1|||The model database must be assigned dbid 3. Perhaps dbid 3 was not assigned
correctly either because of the other problems or because you forgot to
remove the 3608 trace flag.
Start SQL Server in minimal configuration mode (-f parameter) and run the
following query
SELECT dbid
FROM master..sysdatabases
WHERE name = 'model'
If model reports a dbid other than 3, repeat the model database move steps
in minimal configuration mode.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"maxzsim via SQLMonster.com" <u14644@.uwe> wrote in message
news:60fa4c757d28d@.uwe...
> Hi ,
> This is the latest details from the Error Log :
> 2006-05-29 22:18:11.46 server Microsoft SQL Server 2000 - 8.00.760
> (Intel
> X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> 2006-05-29 22:18:11.46 server Copyright (C) 1988-2002 Microsoft
> Corporation.
> 2006-05-29 22:18:11.46 server All rights reserved.
> 2006-05-29 22:18:11.46 server Server Process ID is 1672.
> 2006-05-29 22:18:11.46 server Logging SQL Server messages in file
> 'd:\ms
> sqlserver\MSSQL\log\ERRORLOG'.
> 2006-05-29 22:18:11.47 server SQL Server is starting at priority class
> 'high'(2 CPUs detected).
> 2006-05-29 22:18:11.61 server SQL Server configured for thread mode
> processing.
> 2006-05-29 22:18:11.61 server Using dynamic lock allocation. [2500]
> Lock
> Blocks, [5000] Lock Owner Blocks.
> 2006-05-29 22:18:11.64 server Attempting to initialize Distributed
> Transaction Coordinator.
> 2006-05-29 22:18:11.67 server Failed to obtain
> TransactionDispenserInterface: Result Code = 0x8004d01b
> 2006-05-29 22:18:11.67 spid3 Starting up database 'master'.
> 2006-05-29 22:18:11.83 server Using 'SSNETLIB.DLL' version '8.0.766'.
> 2006-05-29 22:18:11.85 server SQL server listening on 10.224.1.9: 1433.
> 2006-05-29 22:18:11.85 server SQL server listening on 127.0.0.1: 1433.
> 2006-05-29 22:18:11.85 spid3 Server name is 'ALTIRISSERVER'.
> 2006-05-29 22:18:11.85 spid8 Starting up database 'Altiris'.
> 2006-05-29 22:18:11.86 spid10 Starting up database 'Altiris_Incidents'.
> 2006-05-29 22:18:11.86 spid9 Starting up database 'SidataSQL_DC'.
> 2006-05-29 22:18:11.86 spid11 Starting up database
> 'ScalaDB_SGO_Restore'.
> 2006-05-29 22:18:11.86 spid12 Starting up database 'ScalaDB_SGO_TEST'.
> 2006-05-29 22:18:11.86 spid5 Could not find database ID 3. Database
> may
> not be activated yet or may be in transition.
>
> which database does ID 3 refers to ?
> tks & rdgs
> maxzsim wrote:
>>Hi,
>> That was my intention to move master to another location but before i
>> could
>>do that i wasn't able to start SQL server service after moving both the
>>model
>>& msdb db to a new location at the same time
>>tks & rdgs
>>Moving model and msdb files will not affect the master database file
>>locations but you mentioned moving master in your other thread and it
>>looks
>>[quoted text clipped - 8 lines]
>> tks & rdgs
> --
> Message posted via http://www.sqlmonster.com|||Hi ,
Yes i am moving the databases because the harddisk is failing and i did the
following steps below
1. >sqlservr.exe /T3608
2. Then from SQL Query Analyzer I tried to attach both MODEL and MSDB
databases, and they were attached.
step 1 & 2 is from a posting by VM
3. but i cannot "see" model and when i tried to attached from EM it says
successfully but i still cannot see "model" db or when i tried to force
restore or WITH REPLACE it says RESTORE failed abnormally
what shall i do now ?
tks & rdgs
Dan Guzman wrote:
>Error 823 is usually preceded by a detailed message about the cause of the
>I/O error. Do you have any other related messages in the event log? Are
>you moving these database files because a disk is going bad?
>> Hi,
>[quoted text clipped - 20 lines]
>> tks & rdgs
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1|||Hello ,
Have you taken a copy of Master/Model/MSDB Database files before Performing
the above Steps?
Vishal|||I suggest you repeat the model database attach steps as described in
http://support.microsoft.com/kb/224071/en-us. Don't attach the model
database from EM - run the SQL scripts from Query Analyzer or OSQL.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"maxzsim via SQLMonster.com" <u14644@.uwe> wrote in message
news:60fae5663e141@.uwe...
> Hi ,
> Yes i am moving the databases because the harddisk is failing and i did
> the
> following steps below
> 1. >sqlservr.exe /T3608
> 2. Then from SQL Query Analyzer I tried to attach both MODEL and MSDB
> databases, and they were attached.
> step 1 & 2 is from a posting by VM
> 3. but i cannot "see" model and when i tried to attached from EM it says
> successfully but i still cannot see "model" db or when i tried to force
> restore or WITH REPLACE it says RESTORE failed abnormally
>
> what shall i do now ?
> tks & rdgs
>
> Dan Guzman wrote:
>>Error 823 is usually preceded by a detailed message about the cause of the
>>I/O error. Do you have any other related messages in the event log? Are
>>you moving these database files because a disk is going bad?
>> Hi,
>>[quoted text clipped - 20 lines]
>>>
>>> tks & rdgs
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1|||Hi ,
I have made a backup copies of all the system db but i am not able to
restore from the model.bak
tks & rdgs
Vishal Gandhi wrote:
>Hello ,
>Have you taken a copy of Master/Model/MSDB Database files before Performing
>the above Steps?
>Vishal
--
Message posted via http://www.sqlmonster.com|||Hi ,
using QA to attach the msdb & model db was done after i ran sqlserv.exe
/T3608
it simply says attached successfully but i could not see the "model" db and
btw ID 3 refers to the "msdb" db
in the worst case scenario if i need to re-install SQL Server can i restore
the master , model & msdb over the existing ones ?
tks & rdgs
Dan Guzman wrote:
>I suggest you repeat the model database attach steps as described in
>http://support.microsoft.com/kb/224071/en-us. Don't attach the model
>database from EM - run the SQL scripts from Query Analyzer or OSQL.
>> Hi ,
>[quoted text clipped - 26 lines]
>>>
>>> tks & rdgs
--
Message posted via http://www.sqlmonster.com|||> it simply says attached successfully but i could not see the "model" db
> and
> btw ID 3 refers to the "msdb" db
It looks like msdb got assigned dbid 3 instead of model.
Start sql server in minimal configuration mode and detaching both model and
msdb. Attach model first and verify all is expected:
USE model
EXEC sp_helpfile
EXEC sp_helpdb 'model' --make sure dbid is 3
GO
Then attach msdb and verify all is good:
USE msdb
EXEC sp_helpfile
EXEC sp_helpdb 'msdb' --make sure dbid is > 3
GO
Then restart SQL Server normally.
> in the worst case scenario if i need to re-install SQL Server can i
> restore
> the master , model & msdb over the existing ones ?
Yes, you could restore following a reinstall (or REBUILDM) but I'd try the
reattach first. You are almost there.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"maxzsim via SQLMonster.com" <u14644@.uwe> wrote in message
news:60fb4a3f43fa3@.uwe...
> Hi ,
> using QA to attach the msdb & model db was done after i ran sqlserv.exe
> /T3608
> it simply says attached successfully but i could not see the "model" db
> and
> btw ID 3 refers to the "msdb" db
> in the worst case scenario if i need to re-install SQL Server can i
> restore
> the master , model & msdb over the existing ones ?
> tks & rdgs
> Dan Guzman wrote:
>>I suggest you repeat the model database attach steps as described in
>>http://support.microsoft.com/kb/224071/en-us. Don't attach the model
>>database from EM - run the SQL scripts from Query Analyzer or OSQL.
>> Hi ,
>>[quoted text clipped - 26 lines]
>>>
>>> tks & rdgs
> --
> Message posted via http://www.sqlmonster.com|||tks for all the help provided but unfortunately the hdd failed on me before i
could do anything further
now i'll need to rebuild or re-install but luckily i got all the system &
user DBs backed up
tks & rdgs
Dan Guzman wrote:
>> it simply says attached successfully but i could not see the "model" db
>> and
>> btw ID 3 refers to the "msdb" db
>It looks like msdb got assigned dbid 3 instead of model.
>Start sql server in minimal configuration mode and detaching both model and
>msdb. Attach model first and verify all is expected:
>USE model
>EXEC sp_helpfile
>EXEC sp_helpdb 'model' --make sure dbid is 3
>GO
>Then attach msdb and verify all is good:
>USE msdb
>EXEC sp_helpfile
>EXEC sp_helpdb 'msdb' --make sure dbid is > 3
>GO
>Then restart SQL Server normally.
>> in the worst case scenario if i need to re-install SQL Server can i
>> restore
>> the master , model & msdb over the existing ones ?
>Yes, you could restore following a reinstall (or REBUILDM) but I'd try the
>reattach first. You are almost there.
>> Hi ,
>[quoted text clipped - 19 lines]
>>>>
>>>> tks & rdgs
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1
This is refering to my previous mail " moving master db to another location"
and really need advise as i messed up the SQL Server !!
below is the series of steps & checks i have made from my earlier postings
1) +++++++++++++++++
Hi ,
I was trying to move both msdb & model using the following steps :
1. addede the parameter -T3608
2a. use master
go
sp_detach_db 'msdb'
go
2b. use master
go
sp_detach_db 'model'
go
3. stop & restart SQL SERVER
4. i could not restart the SQL SERVER Service from the SQL Server Service
Manager as it keeps restarting & stopping by itself
any way i can get it to work now ?
tks & rdgs
2) ++++++++++++++++=
Hi ,
I got the 18502 error (looked into past posting seems no solution)
Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17055
Date: 5/29/2006
Time: 6:11:53 PM
User: N/A
Computer: ALTIRISSERVER
Description:
18052 :
Error: 823, Severity: 24, State: 2.
and
from the SQL Error Log i got the followings :
2006-05-29 18:11:51.41 server Microsoft SQL Server 2000 - 8.00.760 (Intel
X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
2006-05-29 18:11:51.42 server Copyright (C) 1988-2002 Microsoft
Corporation.
2006-05-29 18:11:51.42 server All rights reserved.
2006-05-29 18:11:51.42 server Server Process ID is 2268.
2006-05-29 18:11:51.42 server Logging SQL Server messages in file 'd:\ms
sqlserver\MSSQL\log\ERRORLOG'.
2006-05-29 18:11:51.42 server SQL Server is starting at priority class
'high'(2 CPUs detected).
2006-05-29 18:11:51.44 server Performance monitor shared memory setup
failed: -1
2006-05-29 18:11:51.53 server SQL Server configured for thread mode
processing.
2006-05-29 18:11:51.53 server Using dynamic lock allocation. [2500] Lock
Blocks, [5000] Lock Owner Blocks.
2006-05-29 18:11:51.57 server Attempting to initialize Distributed
Transaction Coordinator.
2006-05-29 18:11:52.60 spid4 Starting up database 'master'.
2006-05-29 18:11:53.85 spid4 Error: 823, Severity: 24, State: 2.
2006-05-29 18:11:53.85 spid4 Cannot recover the master database. Exiting.
Any solutions besidess re-install ?
tks & rdgs
3 +++++++++++++++
Hi ,
further info :
I have checked the registry for the Master db the path is as per the
startup parameters before i moved the 'model' and 'msdb' database
really appreciate all advise
tks & rdgs
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1> 3 +++++++++++++++
> Hi ,
> further info :
> I have checked the registry for the Master db the path is as per the
> startup parameters before i moved the 'model' and 'msdb' database
The startup parameters must be changed to reflect the new master file
locations in order for SQL Server to start.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"maxzsim via SQLMonster.com" <u14644@.uwe> wrote in message
news:60f93fb49965d@.uwe...
> Hi ,
> This is refering to my previous mail " moving master db to another
> location"
> and really need advise as i messed up the SQL Server !!
> below is the series of steps & checks i have made from my earlier postings
> 1) +++++++++++++++++
> Hi ,
> I was trying to move both msdb & model using the following steps :
> 1. addede the parameter -T3608
> 2a. use master
> go
> sp_detach_db 'msdb'
> go
> 2b. use master
> go
> sp_detach_db 'model'
> go
> 3. stop & restart SQL SERVER
> 4. i could not restart the SQL SERVER Service from the SQL Server Service
> Manager as it keeps restarting & stopping by itself
> any way i can get it to work now ?
> tks & rdgs
>
>
> 2) ++++++++++++++++=> Hi ,
> I got the 18502 error (looked into past posting seems no solution)
> Event Type: Error
> Event Source: MSSQLSERVER
> Event Category: (2)
> Event ID: 17055
> Date: 5/29/2006
> Time: 6:11:53 PM
> User: N/A
> Computer: ALTIRISSERVER
> Description:
> 18052 :
> Error: 823, Severity: 24, State: 2.
> and
> from the SQL Error Log i got the followings :
> 2006-05-29 18:11:51.41 server Microsoft SQL Server 2000 - 8.00.760
> (Intel
> X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> 2006-05-29 18:11:51.42 server Copyright (C) 1988-2002 Microsoft
> Corporation.
> 2006-05-29 18:11:51.42 server All rights reserved.
> 2006-05-29 18:11:51.42 server Server Process ID is 2268.
> 2006-05-29 18:11:51.42 server Logging SQL Server messages in file
> 'd:\ms
> sqlserver\MSSQL\log\ERRORLOG'.
> 2006-05-29 18:11:51.42 server SQL Server is starting at priority class
> 'high'(2 CPUs detected).
> 2006-05-29 18:11:51.44 server Performance monitor shared memory setup
> failed: -1
> 2006-05-29 18:11:51.53 server SQL Server configured for thread mode
> processing.
> 2006-05-29 18:11:51.53 server Using dynamic lock allocation. [2500]
> Lock
> Blocks, [5000] Lock Owner Blocks.
> 2006-05-29 18:11:51.57 server Attempting to initialize Distributed
> Transaction Coordinator.
> 2006-05-29 18:11:52.60 spid4 Starting up database 'master'.
> 2006-05-29 18:11:53.85 spid4 Error: 823, Severity: 24, State: 2.
> 2006-05-29 18:11:53.85 spid4 Cannot recover the master database.
> Exiting.
> Any solutions besidess re-install ?
> tks & rdgs
>
>
> 3 +++++++++++++++
> Hi ,
> further info :
> I have checked the registry for the Master db the path is as per the
> startup parameters before i moved the 'model' and 'msdb' database
> really appreciate all advise
> tks & rdgs
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1|||Hi ,
But i did not move the Master file i moved only 'model' and 'msdb' . will
this affect the master db's parameter as well ?
tks & rdgs
Dan Guzman wrote:
>> 3 +++++++++++++++
>[quoted text clipped - 4 lines]
>> I have checked the registry for the Master db the path is as per the
>> startup parameters before i moved the 'model' and 'msdb' database
>The startup parameters must be changed to reflect the new master file
>locations in order for SQL Server to start.
>> Hi ,
>[quoted text clipped - 95 lines]
>> tks & rdgs
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1|||Moving model and msdb files will not affect the master database file
locations but you mentioned moving master in your other thread and it looks
to me like this problem may be related.
Check to ensure the master database files are at the locations specified by
the registry startup parameters.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"maxzsim via SQLMonster.com" <u14644@.uwe> wrote in message
news:60f9d688bc36d@.uwe...
> Hi ,
> But i did not move the Master file i moved only 'model' and 'msdb' . will
> this affect the master db's parameter as well ?
>
> tks & rdgs
> Dan Guzman wrote:
>> 3 +++++++++++++++
>>[quoted text clipped - 4 lines]
>> I have checked the registry for the Master db the path is as per the
>> startup parameters before i moved the 'model' and 'msdb' database
>>The startup parameters must be changed to reflect the new master file
>>locations in order for SQL Server to start.
>> Hi ,
>>[quoted text clipped - 95 lines]
>> tks & rdgs
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1|||Hi,
That was my intention to move master to another location but before i could
do that i wasn't able to start SQL server service after moving both the model
& msdb db to a new location at the same time
tks & rdgs
Dan Guzman wrote:
>Moving model and msdb files will not affect the master database file
>locations but you mentioned moving master in your other thread and it looks
>to me like this problem may be related.
>Check to ensure the master database files are at the locations specified by
>the registry startup parameters.
>> Hi ,
>[quoted text clipped - 17 lines]
>> tks & rdgs
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1|||Hi ,
This is the latest details from the Error Log :
2006-05-29 22:18:11.46 server Microsoft SQL Server 2000 - 8.00.760 (Intel
X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
2006-05-29 22:18:11.46 server Copyright (C) 1988-2002 Microsoft
Corporation.
2006-05-29 22:18:11.46 server All rights reserved.
2006-05-29 22:18:11.46 server Server Process ID is 1672.
2006-05-29 22:18:11.46 server Logging SQL Server messages in file 'd:\ms
sqlserver\MSSQL\log\ERRORLOG'.
2006-05-29 22:18:11.47 server SQL Server is starting at priority class
'high'(2 CPUs detected).
2006-05-29 22:18:11.61 server SQL Server configured for thread mode
processing.
2006-05-29 22:18:11.61 server Using dynamic lock allocation. [2500] Lock
Blocks, [5000] Lock Owner Blocks.
2006-05-29 22:18:11.64 server Attempting to initialize Distributed
Transaction Coordinator.
2006-05-29 22:18:11.67 server Failed to obtain
TransactionDispenserInterface: Result Code = 0x8004d01b
2006-05-29 22:18:11.67 spid3 Starting up database 'master'.
2006-05-29 22:18:11.83 server Using 'SSNETLIB.DLL' version '8.0.766'.
2006-05-29 22:18:11.85 server SQL server listening on 10.224.1.9: 1433.
2006-05-29 22:18:11.85 server SQL server listening on 127.0.0.1: 1433.
2006-05-29 22:18:11.85 spid3 Server name is 'ALTIRISSERVER'.
2006-05-29 22:18:11.85 spid8 Starting up database 'Altiris'.
2006-05-29 22:18:11.86 spid10 Starting up database 'Altiris_Incidents'.
2006-05-29 22:18:11.86 spid9 Starting up database 'SidataSQL_DC'.
2006-05-29 22:18:11.86 spid11 Starting up database 'ScalaDB_SGO_Restore'.
2006-05-29 22:18:11.86 spid12 Starting up database 'ScalaDB_SGO_TEST'.
2006-05-29 22:18:11.86 spid5 Could not find database ID 3. Database may
not be activated yet or may be in transition.
which database does ID 3 refers to ?
tks & rdgs
maxzsim wrote:
>Hi,
> That was my intention to move master to another location but before i could
>do that i wasn't able to start SQL server service after moving both the model
>& msdb db to a new location at the same time
>tks & rdgs
>>Moving model and msdb files will not affect the master database file
>>locations but you mentioned moving master in your other thread and it looks
>[quoted text clipped - 8 lines]
>> tks & rdgs
--
Message posted via http://www.sqlmonster.com|||Error 823 is usually preceded by a detailed message about the cause of the
I/O error. Do you have any other related messages in the event log? Are
you moving these database files because a disk is going bad?
--
Hope this helps.
Dan Guzman
SQL Server MVP
"maxzsim via SQLMonster.com" <u14644@.uwe> wrote in message
news:60fa3a34e6e1e@.uwe...
> Hi,
> That was my intention to move master to another location but before i
> could
> do that i wasn't able to start SQL server service after moving both the
> model
> & msdb db to a new location at the same time
> tks & rdgs
> Dan Guzman wrote:
>>Moving model and msdb files will not affect the master database file
>>locations but you mentioned moving master in your other thread and it
>>looks
>>to me like this problem may be related.
>>Check to ensure the master database files are at the locations specified
>>by
>>the registry startup parameters.
>> Hi ,
>>[quoted text clipped - 17 lines]
>> tks & rdgs
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1|||The model database must be assigned dbid 3. Perhaps dbid 3 was not assigned
correctly either because of the other problems or because you forgot to
remove the 3608 trace flag.
Start SQL Server in minimal configuration mode (-f parameter) and run the
following query
SELECT dbid
FROM master..sysdatabases
WHERE name = 'model'
If model reports a dbid other than 3, repeat the model database move steps
in minimal configuration mode.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"maxzsim via SQLMonster.com" <u14644@.uwe> wrote in message
news:60fa4c757d28d@.uwe...
> Hi ,
> This is the latest details from the Error Log :
> 2006-05-29 22:18:11.46 server Microsoft SQL Server 2000 - 8.00.760
> (Intel
> X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> 2006-05-29 22:18:11.46 server Copyright (C) 1988-2002 Microsoft
> Corporation.
> 2006-05-29 22:18:11.46 server All rights reserved.
> 2006-05-29 22:18:11.46 server Server Process ID is 1672.
> 2006-05-29 22:18:11.46 server Logging SQL Server messages in file
> 'd:\ms
> sqlserver\MSSQL\log\ERRORLOG'.
> 2006-05-29 22:18:11.47 server SQL Server is starting at priority class
> 'high'(2 CPUs detected).
> 2006-05-29 22:18:11.61 server SQL Server configured for thread mode
> processing.
> 2006-05-29 22:18:11.61 server Using dynamic lock allocation. [2500]
> Lock
> Blocks, [5000] Lock Owner Blocks.
> 2006-05-29 22:18:11.64 server Attempting to initialize Distributed
> Transaction Coordinator.
> 2006-05-29 22:18:11.67 server Failed to obtain
> TransactionDispenserInterface: Result Code = 0x8004d01b
> 2006-05-29 22:18:11.67 spid3 Starting up database 'master'.
> 2006-05-29 22:18:11.83 server Using 'SSNETLIB.DLL' version '8.0.766'.
> 2006-05-29 22:18:11.85 server SQL server listening on 10.224.1.9: 1433.
> 2006-05-29 22:18:11.85 server SQL server listening on 127.0.0.1: 1433.
> 2006-05-29 22:18:11.85 spid3 Server name is 'ALTIRISSERVER'.
> 2006-05-29 22:18:11.85 spid8 Starting up database 'Altiris'.
> 2006-05-29 22:18:11.86 spid10 Starting up database 'Altiris_Incidents'.
> 2006-05-29 22:18:11.86 spid9 Starting up database 'SidataSQL_DC'.
> 2006-05-29 22:18:11.86 spid11 Starting up database
> 'ScalaDB_SGO_Restore'.
> 2006-05-29 22:18:11.86 spid12 Starting up database 'ScalaDB_SGO_TEST'.
> 2006-05-29 22:18:11.86 spid5 Could not find database ID 3. Database
> may
> not be activated yet or may be in transition.
>
> which database does ID 3 refers to ?
> tks & rdgs
> maxzsim wrote:
>>Hi,
>> That was my intention to move master to another location but before i
>> could
>>do that i wasn't able to start SQL server service after moving both the
>>model
>>& msdb db to a new location at the same time
>>tks & rdgs
>>Moving model and msdb files will not affect the master database file
>>locations but you mentioned moving master in your other thread and it
>>looks
>>[quoted text clipped - 8 lines]
>> tks & rdgs
> --
> Message posted via http://www.sqlmonster.com|||Hi ,
Yes i am moving the databases because the harddisk is failing and i did the
following steps below
1. >sqlservr.exe /T3608
2. Then from SQL Query Analyzer I tried to attach both MODEL and MSDB
databases, and they were attached.
step 1 & 2 is from a posting by VM
3. but i cannot "see" model and when i tried to attached from EM it says
successfully but i still cannot see "model" db or when i tried to force
restore or WITH REPLACE it says RESTORE failed abnormally
what shall i do now ?
tks & rdgs
Dan Guzman wrote:
>Error 823 is usually preceded by a detailed message about the cause of the
>I/O error. Do you have any other related messages in the event log? Are
>you moving these database files because a disk is going bad?
>> Hi,
>[quoted text clipped - 20 lines]
>> tks & rdgs
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1|||Hello ,
Have you taken a copy of Master/Model/MSDB Database files before Performing
the above Steps?
Vishal|||I suggest you repeat the model database attach steps as described in
http://support.microsoft.com/kb/224071/en-us. Don't attach the model
database from EM - run the SQL scripts from Query Analyzer or OSQL.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"maxzsim via SQLMonster.com" <u14644@.uwe> wrote in message
news:60fae5663e141@.uwe...
> Hi ,
> Yes i am moving the databases because the harddisk is failing and i did
> the
> following steps below
> 1. >sqlservr.exe /T3608
> 2. Then from SQL Query Analyzer I tried to attach both MODEL and MSDB
> databases, and they were attached.
> step 1 & 2 is from a posting by VM
> 3. but i cannot "see" model and when i tried to attached from EM it says
> successfully but i still cannot see "model" db or when i tried to force
> restore or WITH REPLACE it says RESTORE failed abnormally
>
> what shall i do now ?
> tks & rdgs
>
> Dan Guzman wrote:
>>Error 823 is usually preceded by a detailed message about the cause of the
>>I/O error. Do you have any other related messages in the event log? Are
>>you moving these database files because a disk is going bad?
>> Hi,
>>[quoted text clipped - 20 lines]
>>>
>>> tks & rdgs
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1|||Hi ,
I have made a backup copies of all the system db but i am not able to
restore from the model.bak
tks & rdgs
Vishal Gandhi wrote:
>Hello ,
>Have you taken a copy of Master/Model/MSDB Database files before Performing
>the above Steps?
>Vishal
--
Message posted via http://www.sqlmonster.com|||Hi ,
using QA to attach the msdb & model db was done after i ran sqlserv.exe
/T3608
it simply says attached successfully but i could not see the "model" db and
btw ID 3 refers to the "msdb" db
in the worst case scenario if i need to re-install SQL Server can i restore
the master , model & msdb over the existing ones ?
tks & rdgs
Dan Guzman wrote:
>I suggest you repeat the model database attach steps as described in
>http://support.microsoft.com/kb/224071/en-us. Don't attach the model
>database from EM - run the SQL scripts from Query Analyzer or OSQL.
>> Hi ,
>[quoted text clipped - 26 lines]
>>>
>>> tks & rdgs
--
Message posted via http://www.sqlmonster.com|||> it simply says attached successfully but i could not see the "model" db
> and
> btw ID 3 refers to the "msdb" db
It looks like msdb got assigned dbid 3 instead of model.
Start sql server in minimal configuration mode and detaching both model and
msdb. Attach model first and verify all is expected:
USE model
EXEC sp_helpfile
EXEC sp_helpdb 'model' --make sure dbid is 3
GO
Then attach msdb and verify all is good:
USE msdb
EXEC sp_helpfile
EXEC sp_helpdb 'msdb' --make sure dbid is > 3
GO
Then restart SQL Server normally.
> in the worst case scenario if i need to re-install SQL Server can i
> restore
> the master , model & msdb over the existing ones ?
Yes, you could restore following a reinstall (or REBUILDM) but I'd try the
reattach first. You are almost there.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"maxzsim via SQLMonster.com" <u14644@.uwe> wrote in message
news:60fb4a3f43fa3@.uwe...
> Hi ,
> using QA to attach the msdb & model db was done after i ran sqlserv.exe
> /T3608
> it simply says attached successfully but i could not see the "model" db
> and
> btw ID 3 refers to the "msdb" db
> in the worst case scenario if i need to re-install SQL Server can i
> restore
> the master , model & msdb over the existing ones ?
> tks & rdgs
> Dan Guzman wrote:
>>I suggest you repeat the model database attach steps as described in
>>http://support.microsoft.com/kb/224071/en-us. Don't attach the model
>>database from EM - run the SQL scripts from Query Analyzer or OSQL.
>> Hi ,
>>[quoted text clipped - 26 lines]
>>>
>>> tks & rdgs
> --
> Message posted via http://www.sqlmonster.com|||tks for all the help provided but unfortunately the hdd failed on me before i
could do anything further
now i'll need to rebuild or re-install but luckily i got all the system &
user DBs backed up
tks & rdgs
Dan Guzman wrote:
>> it simply says attached successfully but i could not see the "model" db
>> and
>> btw ID 3 refers to the "msdb" db
>It looks like msdb got assigned dbid 3 instead of model.
>Start sql server in minimal configuration mode and detaching both model and
>msdb. Attach model first and verify all is expected:
>USE model
>EXEC sp_helpfile
>EXEC sp_helpdb 'model' --make sure dbid is 3
>GO
>Then attach msdb and verify all is good:
>USE msdb
>EXEC sp_helpfile
>EXEC sp_helpdb 'msdb' --make sure dbid is > 3
>GO
>Then restart SQL Server normally.
>> in the worst case scenario if i need to re-install SQL Server can i
>> restore
>> the master , model & msdb over the existing ones ?
>Yes, you could restore following a reinstall (or REBUILDM) but I'd try the
>reattach first. You are almost there.
>> Hi ,
>[quoted text clipped - 19 lines]
>>>>
>>>> tks & rdgs
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1
CANNOT START SQL SERVER Service - 18052 NEED HELP here !
Hi ,
This is refering to my previous mail " moving master db to another location"
and really need advise as i messed up the SQL Server !!
below is the series of steps & checks i have made from my earlier postings
1) +++++++++++++++++
Hi ,
I was trying to move both msdb & model using the following steps :
1. addede the parameter -T3608
2a. use master
go
sp_detach_db 'msdb'
go
2b. use master
go
sp_detach_db 'model'
go
3. stop & restart SQL SERVER
4. i could not restart the SQL SERVER Service from the SQL Server Service
Manager as it keeps restarting & stopping by itself
any way i can get it to work now ?
tks & rdgs
2) ++++++++++++++++=
Hi ,
I got the 18502 error (looked into past posting seems no solution)
Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17055
Date: 5/29/2006
Time: 6:11:53 PM
User: N/A
Computer: ALTIRISSERVER
Description:
18052 :
Error: 823, Severity: 24, State: 2.
and
from the SQL Error Log i got the followings :
2006-05-29 18:11:51.41 server Microsoft SQL Server 2000 - 8.00.760 (Inte
l
X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
2006-05-29 18:11:51.42 server Copyright (C) 1988-2002 Microsoft
Corporation.
2006-05-29 18:11:51.42 server All rights reserved.
2006-05-29 18:11:51.42 server Server Process ID is 2268.
2006-05-29 18:11:51.42 server Logging SQL Server messages in file 'd:\ms
sqlserver\MSSQL\log\ERRORLOG'.
2006-05-29 18:11:51.42 server SQL Server is starting at priority class
'high'(2 CPUs detected).
2006-05-29 18:11:51.44 server Performance monitor shared memory setup
failed: -1
2006-05-29 18:11:51.53 server SQL Server configured for thread mode
processing.
2006-05-29 18:11:51.53 server Using dynamic lock allocation. [2500] L
ock
Blocks, [5000] Lock Owner Blocks.
2006-05-29 18:11:51.57 server Attempting to initialize Distributed
Transaction Coordinator.
2006-05-29 18:11:52.60 spid4 Starting up database 'master'.
2006-05-29 18:11:53.85 spid4 Error: 823, Severity: 24, State: 2.
2006-05-29 18:11:53.85 spid4 Cannot recover the master database. Exiting
.
Any solutions besidess re-install ?
tks & rdgs
3 +++++++++++++++
Hi ,
further info :
I have checked the registry for the Master db the path is as per the
startup parameters before i moved the 'model' and 'msdb' database
really appreciate all advise
tks & rdgs
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200605/1> 3 +++++++++++++++
> Hi ,
> further info :
> I have checked the registry for the Master db the path is as per the
> startup parameters before i moved the 'model' and 'msdb' database
The startup parameters must be changed to reflect the new master file
locations in order for SQL Server to start.
Hope this helps.
Dan Guzman
SQL Server MVP
"maxzsim via droptable.com" <u14644@.uwe> wrote in message
news:60f93fb49965d@.uwe...
> Hi ,
> This is refering to my previous mail " moving master db to another
> location"
> and really need advise as i messed up the SQL Server !!
> below is the series of steps & checks i have made from my earlier postings
> 1) +++++++++++++++++
> Hi ,
> I was trying to move both msdb & model using the following steps :
> 1. addede the parameter -T3608
> 2a. use master
> go
> sp_detach_db 'msdb'
> go
> 2b. use master
> go
> sp_detach_db 'model'
> go
> 3. stop & restart SQL SERVER
> 4. i could not restart the SQL SERVER Service from the SQL Server Service
> Manager as it keeps restarting & stopping by itself
> any way i can get it to work now ?
> tks & rdgs
>
>
> 2) ++++++++++++++++=
> Hi ,
> I got the 18502 error (looked into past posting seems no solution)
> Event Type: Error
> Event Source: MSSQLSERVER
> Event Category: (2)
> Event ID: 17055
> Date: 5/29/2006
> Time: 6:11:53 PM
> User: N/A
> Computer: ALTIRISSERVER
> Description:
> 18052 :
> Error: 823, Severity: 24, State: 2.
> and
> from the SQL Error Log i got the followings :
> 2006-05-29 18:11:51.41 server Microsoft SQL Server 2000 - 8.00.760
> (Intel
> X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> 2006-05-29 18:11:51.42 server Copyright (C) 1988-2002 Microsoft
> Corporation.
> 2006-05-29 18:11:51.42 server All rights reserved.
> 2006-05-29 18:11:51.42 server Server Process ID is 2268.
> 2006-05-29 18:11:51.42 server Logging SQL Server messages in file
> 'd:\ms
> sqlserver\MSSQL\log\ERRORLOG'.
> 2006-05-29 18:11:51.42 server SQL Server is starting at priority class
> 'high'(2 CPUs detected).
> 2006-05-29 18:11:51.44 server Performance monitor shared memory setup
> failed: -1
> 2006-05-29 18:11:51.53 server SQL Server configured for thread mode
> processing.
> 2006-05-29 18:11:51.53 server Using dynamic lock allocation. [2500]
> Lock
> Blocks, [5000] Lock Owner Blocks.
> 2006-05-29 18:11:51.57 server Attempting to initialize Distributed
> Transaction Coordinator.
> 2006-05-29 18:11:52.60 spid4 Starting up database 'master'.
> 2006-05-29 18:11:53.85 spid4 Error: 823, Severity: 24, State: 2.
> 2006-05-29 18:11:53.85 spid4 Cannot recover the master database.
> Exiting.
> Any solutions besidess re-install ?
> tks & rdgs
>
>
> 3 +++++++++++++++
> Hi ,
> further info :
> I have checked the registry for the Master db the path is as per the
> startup parameters before i moved the 'model' and 'msdb' database
> really appreciate all advise
> tks & rdgs
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200605/1|||Hi ,
But i did not move the Master file i moved only 'model' and 'msdb' . will
this affect the master db's parameter as well ?
tks & rdgs
Dan Guzman wrote:[vbcol=seagreen]
>[quoted text clipped - 4 lines]
>The startup parameters must be changed to reflect the new master file
>locations in order for SQL Server to start.
>
>[quoted text clipped - 95 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200605/1|||Moving model and msdb files will not affect the master database file
locations but you mentioned moving master in your other thread and it looks
to me like this problem may be related.
Check to ensure the master database files are at the locations specified by
the registry startup parameters.
Hope this helps.
Dan Guzman
SQL Server MVP
"maxzsim via droptable.com" <u14644@.uwe> wrote in message
news:60f9d688bc36d@.uwe...
> Hi ,
> But i did not move the Master file i moved only 'model' and 'msdb' . will
> this affect the master db's parameter as well ?
>
> tks & rdgs
> Dan Guzman wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200605/1|||Hi,
That was my intention to move master to another location but before i could
do that i wasn't able to start SQL server service after moving both the mode
l
& msdb db to a new location at the same time
tks & rdgs
Dan Guzman wrote:[vbcol=seagreen]
>Moving model and msdb files will not affect the master database file
>locations but you mentioned moving master in your other thread and it looks
>to me like this problem may be related.
>Check to ensure the master database files are at the locations specified by
>the registry startup parameters.
>
>[quoted text clipped - 17 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200605/1|||Hi ,
This is the latest details from the Error Log :
2006-05-29 22:18:11.46 server Microsoft SQL Server 2000 - 8.00.760 (Inte
l
X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
2006-05-29 22:18:11.46 server Copyright (C) 1988-2002 Microsoft
Corporation.
2006-05-29 22:18:11.46 server All rights reserved.
2006-05-29 22:18:11.46 server Server Process ID is 1672.
2006-05-29 22:18:11.46 server Logging SQL Server messages in file 'd:\ms
sqlserver\MSSQL\log\ERRORLOG'.
2006-05-29 22:18:11.47 server SQL Server is starting at priority class
'high'(2 CPUs detected).
2006-05-29 22:18:11.61 server SQL Server configured for thread mode
processing.
2006-05-29 22:18:11.61 server Using dynamic lock allocation. [2500] L
ock
Blocks, [5000] Lock Owner Blocks.
2006-05-29 22:18:11.64 server Attempting to initialize Distributed
Transaction Coordinator.
2006-05-29 22:18:11.67 server Failed to obtain
TransactionDispenserInterface: Result Code = 0x8004d01b
2006-05-29 22:18:11.67 spid3 Starting up database 'master'.
2006-05-29 22:18:11.83 server Using 'SSNETLIB.DLL' version '8.0.766'.
2006-05-29 22:18:11.85 server SQL server listening on 10.224.1.9: 1433.
2006-05-29 22:18:11.85 server SQL server listening on 127.0.0.1: 1433.
2006-05-29 22:18:11.85 spid3 Server name is 'ALTIRISSERVER'.
2006-05-29 22:18:11.85 spid8 Starting up database 'Altiris'.
2006-05-29 22:18:11.86 spid10 Starting up database 'Altiris_Incidents'.
2006-05-29 22:18:11.86 spid9 Starting up database 'SidataSQL_DC'.
2006-05-29 22:18:11.86 spid11 Starting up database 'ScalaDB_SGO_Restore'.
2006-05-29 22:18:11.86 spid12 Starting up database 'ScalaDB_SGO_TEST'.
2006-05-29 22:18:11.86 spid5 Could not find database ID 3. Database may
not be activated yet or may be in transition.
which database does ID 3 refers to ?
tks & rdgs
maxzsim wrote:[vbcol=seagreen]
>Hi,
> That was my intention to move master to another location but before i coul
d
>do that i wasn't able to start SQL server service after moving both the mod
el
>& msdb db to a new location at the same time
>tks & rdgs
>
>[quoted text clipped - 8 lines]
Message posted via http://www.droptable.com|||Error 823 is usually preceded by a detailed message about the cause of the
I/O error. Do you have any other related messages in the event log? Are
you moving these database files because a disk is going bad?
Hope this helps.
Dan Guzman
SQL Server MVP
"maxzsim via droptable.com" <u14644@.uwe> wrote in message
news:60fa3a34e6e1e@.uwe...
> Hi,
> That was my intention to move master to another location but before i
> could
> do that i wasn't able to start SQL server service after moving both the
> model
> & msdb db to a new location at the same time
> tks & rdgs
> Dan Guzman wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200605/1|||The model database must be assigned dbid 3. Perhaps dbid 3 was not assigned
correctly either because of the other problems or because you forgot to
remove the 3608 trace flag.
Start SQL Server in minimal configuration mode (-f parameter) and run the
following query
SELECT dbid
FROM master..sysdatabases
WHERE name = 'model'
If model reports a dbid other than 3, repeat the model database move steps
in minimal configuration mode.
Hope this helps.
Dan Guzman
SQL Server MVP
"maxzsim via droptable.com" <u14644@.uwe> wrote in message
news:60fa4c757d28d@.uwe...
> Hi ,
> This is the latest details from the Error Log :
> 2006-05-29 22:18:11.46 server Microsoft SQL Server 2000 - 8.00.760
> (Intel
> X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> 2006-05-29 22:18:11.46 server Copyright (C) 1988-2002 Microsoft
> Corporation.
> 2006-05-29 22:18:11.46 server All rights reserved.
> 2006-05-29 22:18:11.46 server Server Process ID is 1672.
> 2006-05-29 22:18:11.46 server Logging SQL Server messages in file
> 'd:\ms
> sqlserver\MSSQL\log\ERRORLOG'.
> 2006-05-29 22:18:11.47 server SQL Server is starting at priority class
> 'high'(2 CPUs detected).
> 2006-05-29 22:18:11.61 server SQL Server configured for thread mode
> processing.
> 2006-05-29 22:18:11.61 server Using dynamic lock allocation. [2500]
> Lock
> Blocks, [5000] Lock Owner Blocks.
> 2006-05-29 22:18:11.64 server Attempting to initialize Distributed
> Transaction Coordinator.
> 2006-05-29 22:18:11.67 server Failed to obtain
> TransactionDispenserInterface: Result Code = 0x8004d01b
> 2006-05-29 22:18:11.67 spid3 Starting up database 'master'.
> 2006-05-29 22:18:11.83 server Using 'SSNETLIB.DLL' version '8.0.766'.
> 2006-05-29 22:18:11.85 server SQL server listening on 10.224.1.9: 1433.
> 2006-05-29 22:18:11.85 server SQL server listening on 127.0.0.1: 1433.
> 2006-05-29 22:18:11.85 spid3 Server name is 'ALTIRISSERVER'.
> 2006-05-29 22:18:11.85 spid8 Starting up database 'Altiris'.
> 2006-05-29 22:18:11.86 spid10 Starting up database 'Altiris_Incidents'.
> 2006-05-29 22:18:11.86 spid9 Starting up database 'SidataSQL_DC'.
> 2006-05-29 22:18:11.86 spid11 Starting up database
> 'ScalaDB_SGO_Restore'.
> 2006-05-29 22:18:11.86 spid12 Starting up database 'ScalaDB_SGO_TEST'.
> 2006-05-29 22:18:11.86 spid5 Could not find database ID 3. Database
> may
> not be activated yet or may be in transition.
>
> which database does ID 3 refers to ?
> tks & rdgs
> maxzsim wrote:
> --
> Message posted via http://www.droptable.com|||Hi ,
Yes i am moving the databases because the harddisk is failing and i did the
following steps below
1. >sqlservr.exe /T3608
2. Then from SQL Query Analyzer I tried to attach both MODEL and MSDB
databases, and they were attached.
step 1 & 2 is from a posting by VM
3. but i cannot "see" model and when i tried to attached from EM it says
successfully but i still cannot see "model" db or when i tried to force
restore or WITH REPLACE it says RESTORE failed abnormally
what shall i do now ?
tks & rdgs
Dan Guzman wrote:[vbcol=seagreen]
>Error 823 is usually preceded by a detailed message about the cause of the
>I/O error. Do you have any other related messages in the event log? Are
>you moving these database files because a disk is going bad?
>
>[quoted text clipped - 20 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200605/1|||> it simply says attached successfully but i could not see the "model" db
> and
> btw ID 3 refers to the "msdb" db
It looks like msdb got assigned dbid 3 instead of model.
Start sql server in minimal configuration mode and detaching both model and
msdb. Attach model first and verify all is expected:
USE model
EXEC sp_helpfile
EXEC sp_helpdb 'model' --make sure dbid is 3
GO
Then attach msdb and verify all is good:
USE msdb
EXEC sp_helpfile
EXEC sp_helpdb 'msdb' --make sure dbid is > 3
GO
Then restart SQL Server normally.
> in the worst case scenario if i need to re-install SQL Server can i
> restore
> the master , model & msdb over the existing ones ?
Yes, you could restore following a reinstall (or REBUILDM) but I'd try the
reattach first. You are almost there.
Hope this helps.
Dan Guzman
SQL Server MVP
"maxzsim via droptable.com" <u14644@.uwe> wrote in message
news:60fb4a3f43fa3@.uwe...
> Hi ,
> using QA to attach the msdb & model db was done after i ran sqlserv.exe
> /T3608
> it simply says attached successfully but i could not see the "model" db
> and
> btw ID 3 refers to the "msdb" db
> in the worst case scenario if i need to re-install SQL Server can i
> restore
> the master , model & msdb over the existing ones ?
> tks & rdgs
> Dan Guzman wrote:
> --
> Message posted via http://www.droptable.com
This is refering to my previous mail " moving master db to another location"
and really need advise as i messed up the SQL Server !!
below is the series of steps & checks i have made from my earlier postings
1) +++++++++++++++++
Hi ,
I was trying to move both msdb & model using the following steps :
1. addede the parameter -T3608
2a. use master
go
sp_detach_db 'msdb'
go
2b. use master
go
sp_detach_db 'model'
go
3. stop & restart SQL SERVER
4. i could not restart the SQL SERVER Service from the SQL Server Service
Manager as it keeps restarting & stopping by itself
any way i can get it to work now ?
tks & rdgs
2) ++++++++++++++++=
Hi ,
I got the 18502 error (looked into past posting seems no solution)
Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17055
Date: 5/29/2006
Time: 6:11:53 PM
User: N/A
Computer: ALTIRISSERVER
Description:
18052 :
Error: 823, Severity: 24, State: 2.
and
from the SQL Error Log i got the followings :
2006-05-29 18:11:51.41 server Microsoft SQL Server 2000 - 8.00.760 (Inte
l
X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
2006-05-29 18:11:51.42 server Copyright (C) 1988-2002 Microsoft
Corporation.
2006-05-29 18:11:51.42 server All rights reserved.
2006-05-29 18:11:51.42 server Server Process ID is 2268.
2006-05-29 18:11:51.42 server Logging SQL Server messages in file 'd:\ms
sqlserver\MSSQL\log\ERRORLOG'.
2006-05-29 18:11:51.42 server SQL Server is starting at priority class
'high'(2 CPUs detected).
2006-05-29 18:11:51.44 server Performance monitor shared memory setup
failed: -1
2006-05-29 18:11:51.53 server SQL Server configured for thread mode
processing.
2006-05-29 18:11:51.53 server Using dynamic lock allocation. [2500] L
ock
Blocks, [5000] Lock Owner Blocks.
2006-05-29 18:11:51.57 server Attempting to initialize Distributed
Transaction Coordinator.
2006-05-29 18:11:52.60 spid4 Starting up database 'master'.
2006-05-29 18:11:53.85 spid4 Error: 823, Severity: 24, State: 2.
2006-05-29 18:11:53.85 spid4 Cannot recover the master database. Exiting
.
Any solutions besidess re-install ?
tks & rdgs
3 +++++++++++++++
Hi ,
further info :
I have checked the registry for the Master db the path is as per the
startup parameters before i moved the 'model' and 'msdb' database
really appreciate all advise
tks & rdgs
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200605/1> 3 +++++++++++++++
> Hi ,
> further info :
> I have checked the registry for the Master db the path is as per the
> startup parameters before i moved the 'model' and 'msdb' database
The startup parameters must be changed to reflect the new master file
locations in order for SQL Server to start.
Hope this helps.
Dan Guzman
SQL Server MVP
"maxzsim via droptable.com" <u14644@.uwe> wrote in message
news:60f93fb49965d@.uwe...
> Hi ,
> This is refering to my previous mail " moving master db to another
> location"
> and really need advise as i messed up the SQL Server !!
> below is the series of steps & checks i have made from my earlier postings
> 1) +++++++++++++++++
> Hi ,
> I was trying to move both msdb & model using the following steps :
> 1. addede the parameter -T3608
> 2a. use master
> go
> sp_detach_db 'msdb'
> go
> 2b. use master
> go
> sp_detach_db 'model'
> go
> 3. stop & restart SQL SERVER
> 4. i could not restart the SQL SERVER Service from the SQL Server Service
> Manager as it keeps restarting & stopping by itself
> any way i can get it to work now ?
> tks & rdgs
>
>
> 2) ++++++++++++++++=
> Hi ,
> I got the 18502 error (looked into past posting seems no solution)
> Event Type: Error
> Event Source: MSSQLSERVER
> Event Category: (2)
> Event ID: 17055
> Date: 5/29/2006
> Time: 6:11:53 PM
> User: N/A
> Computer: ALTIRISSERVER
> Description:
> 18052 :
> Error: 823, Severity: 24, State: 2.
> and
> from the SQL Error Log i got the followings :
> 2006-05-29 18:11:51.41 server Microsoft SQL Server 2000 - 8.00.760
> (Intel
> X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> 2006-05-29 18:11:51.42 server Copyright (C) 1988-2002 Microsoft
> Corporation.
> 2006-05-29 18:11:51.42 server All rights reserved.
> 2006-05-29 18:11:51.42 server Server Process ID is 2268.
> 2006-05-29 18:11:51.42 server Logging SQL Server messages in file
> 'd:\ms
> sqlserver\MSSQL\log\ERRORLOG'.
> 2006-05-29 18:11:51.42 server SQL Server is starting at priority class
> 'high'(2 CPUs detected).
> 2006-05-29 18:11:51.44 server Performance monitor shared memory setup
> failed: -1
> 2006-05-29 18:11:51.53 server SQL Server configured for thread mode
> processing.
> 2006-05-29 18:11:51.53 server Using dynamic lock allocation. [2500]
> Lock
> Blocks, [5000] Lock Owner Blocks.
> 2006-05-29 18:11:51.57 server Attempting to initialize Distributed
> Transaction Coordinator.
> 2006-05-29 18:11:52.60 spid4 Starting up database 'master'.
> 2006-05-29 18:11:53.85 spid4 Error: 823, Severity: 24, State: 2.
> 2006-05-29 18:11:53.85 spid4 Cannot recover the master database.
> Exiting.
> Any solutions besidess re-install ?
> tks & rdgs
>
>
> 3 +++++++++++++++
> Hi ,
> further info :
> I have checked the registry for the Master db the path is as per the
> startup parameters before i moved the 'model' and 'msdb' database
> really appreciate all advise
> tks & rdgs
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200605/1|||Hi ,
But i did not move the Master file i moved only 'model' and 'msdb' . will
this affect the master db's parameter as well ?
tks & rdgs
Dan Guzman wrote:[vbcol=seagreen]
>[quoted text clipped - 4 lines]
>The startup parameters must be changed to reflect the new master file
>locations in order for SQL Server to start.
>
>[quoted text clipped - 95 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200605/1|||Moving model and msdb files will not affect the master database file
locations but you mentioned moving master in your other thread and it looks
to me like this problem may be related.
Check to ensure the master database files are at the locations specified by
the registry startup parameters.
Hope this helps.
Dan Guzman
SQL Server MVP
"maxzsim via droptable.com" <u14644@.uwe> wrote in message
news:60f9d688bc36d@.uwe...
> Hi ,
> But i did not move the Master file i moved only 'model' and 'msdb' . will
> this affect the master db's parameter as well ?
>
> tks & rdgs
> Dan Guzman wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200605/1|||Hi,
That was my intention to move master to another location but before i could
do that i wasn't able to start SQL server service after moving both the mode
l
& msdb db to a new location at the same time
tks & rdgs
Dan Guzman wrote:[vbcol=seagreen]
>Moving model and msdb files will not affect the master database file
>locations but you mentioned moving master in your other thread and it looks
>to me like this problem may be related.
>Check to ensure the master database files are at the locations specified by
>the registry startup parameters.
>
>[quoted text clipped - 17 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200605/1|||Hi ,
This is the latest details from the Error Log :
2006-05-29 22:18:11.46 server Microsoft SQL Server 2000 - 8.00.760 (Inte
l
X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
2006-05-29 22:18:11.46 server Copyright (C) 1988-2002 Microsoft
Corporation.
2006-05-29 22:18:11.46 server All rights reserved.
2006-05-29 22:18:11.46 server Server Process ID is 1672.
2006-05-29 22:18:11.46 server Logging SQL Server messages in file 'd:\ms
sqlserver\MSSQL\log\ERRORLOG'.
2006-05-29 22:18:11.47 server SQL Server is starting at priority class
'high'(2 CPUs detected).
2006-05-29 22:18:11.61 server SQL Server configured for thread mode
processing.
2006-05-29 22:18:11.61 server Using dynamic lock allocation. [2500] L
ock
Blocks, [5000] Lock Owner Blocks.
2006-05-29 22:18:11.64 server Attempting to initialize Distributed
Transaction Coordinator.
2006-05-29 22:18:11.67 server Failed to obtain
TransactionDispenserInterface: Result Code = 0x8004d01b
2006-05-29 22:18:11.67 spid3 Starting up database 'master'.
2006-05-29 22:18:11.83 server Using 'SSNETLIB.DLL' version '8.0.766'.
2006-05-29 22:18:11.85 server SQL server listening on 10.224.1.9: 1433.
2006-05-29 22:18:11.85 server SQL server listening on 127.0.0.1: 1433.
2006-05-29 22:18:11.85 spid3 Server name is 'ALTIRISSERVER'.
2006-05-29 22:18:11.85 spid8 Starting up database 'Altiris'.
2006-05-29 22:18:11.86 spid10 Starting up database 'Altiris_Incidents'.
2006-05-29 22:18:11.86 spid9 Starting up database 'SidataSQL_DC'.
2006-05-29 22:18:11.86 spid11 Starting up database 'ScalaDB_SGO_Restore'.
2006-05-29 22:18:11.86 spid12 Starting up database 'ScalaDB_SGO_TEST'.
2006-05-29 22:18:11.86 spid5 Could not find database ID 3. Database may
not be activated yet or may be in transition.
which database does ID 3 refers to ?
tks & rdgs
maxzsim wrote:[vbcol=seagreen]
>Hi,
> That was my intention to move master to another location but before i coul
d
>do that i wasn't able to start SQL server service after moving both the mod
el
>& msdb db to a new location at the same time
>tks & rdgs
>
>[quoted text clipped - 8 lines]
Message posted via http://www.droptable.com|||Error 823 is usually preceded by a detailed message about the cause of the
I/O error. Do you have any other related messages in the event log? Are
you moving these database files because a disk is going bad?
Hope this helps.
Dan Guzman
SQL Server MVP
"maxzsim via droptable.com" <u14644@.uwe> wrote in message
news:60fa3a34e6e1e@.uwe...
> Hi,
> That was my intention to move master to another location but before i
> could
> do that i wasn't able to start SQL server service after moving both the
> model
> & msdb db to a new location at the same time
> tks & rdgs
> Dan Guzman wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200605/1|||The model database must be assigned dbid 3. Perhaps dbid 3 was not assigned
correctly either because of the other problems or because you forgot to
remove the 3608 trace flag.
Start SQL Server in minimal configuration mode (-f parameter) and run the
following query
SELECT dbid
FROM master..sysdatabases
WHERE name = 'model'
If model reports a dbid other than 3, repeat the model database move steps
in minimal configuration mode.
Hope this helps.
Dan Guzman
SQL Server MVP
"maxzsim via droptable.com" <u14644@.uwe> wrote in message
news:60fa4c757d28d@.uwe...
> Hi ,
> This is the latest details from the Error Log :
> 2006-05-29 22:18:11.46 server Microsoft SQL Server 2000 - 8.00.760
> (Intel
> X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> 2006-05-29 22:18:11.46 server Copyright (C) 1988-2002 Microsoft
> Corporation.
> 2006-05-29 22:18:11.46 server All rights reserved.
> 2006-05-29 22:18:11.46 server Server Process ID is 1672.
> 2006-05-29 22:18:11.46 server Logging SQL Server messages in file
> 'd:\ms
> sqlserver\MSSQL\log\ERRORLOG'.
> 2006-05-29 22:18:11.47 server SQL Server is starting at priority class
> 'high'(2 CPUs detected).
> 2006-05-29 22:18:11.61 server SQL Server configured for thread mode
> processing.
> 2006-05-29 22:18:11.61 server Using dynamic lock allocation. [2500]
> Lock
> Blocks, [5000] Lock Owner Blocks.
> 2006-05-29 22:18:11.64 server Attempting to initialize Distributed
> Transaction Coordinator.
> 2006-05-29 22:18:11.67 server Failed to obtain
> TransactionDispenserInterface: Result Code = 0x8004d01b
> 2006-05-29 22:18:11.67 spid3 Starting up database 'master'.
> 2006-05-29 22:18:11.83 server Using 'SSNETLIB.DLL' version '8.0.766'.
> 2006-05-29 22:18:11.85 server SQL server listening on 10.224.1.9: 1433.
> 2006-05-29 22:18:11.85 server SQL server listening on 127.0.0.1: 1433.
> 2006-05-29 22:18:11.85 spid3 Server name is 'ALTIRISSERVER'.
> 2006-05-29 22:18:11.85 spid8 Starting up database 'Altiris'.
> 2006-05-29 22:18:11.86 spid10 Starting up database 'Altiris_Incidents'.
> 2006-05-29 22:18:11.86 spid9 Starting up database 'SidataSQL_DC'.
> 2006-05-29 22:18:11.86 spid11 Starting up database
> 'ScalaDB_SGO_Restore'.
> 2006-05-29 22:18:11.86 spid12 Starting up database 'ScalaDB_SGO_TEST'.
> 2006-05-29 22:18:11.86 spid5 Could not find database ID 3. Database
> may
> not be activated yet or may be in transition.
>
> which database does ID 3 refers to ?
> tks & rdgs
> maxzsim wrote:
> --
> Message posted via http://www.droptable.com|||Hi ,
Yes i am moving the databases because the harddisk is failing and i did the
following steps below
1. >sqlservr.exe /T3608
2. Then from SQL Query Analyzer I tried to attach both MODEL and MSDB
databases, and they were attached.
step 1 & 2 is from a posting by VM
3. but i cannot "see" model and when i tried to attached from EM it says
successfully but i still cannot see "model" db or when i tried to force
restore or WITH REPLACE it says RESTORE failed abnormally
what shall i do now ?
tks & rdgs
Dan Guzman wrote:[vbcol=seagreen]
>Error 823 is usually preceded by a detailed message about the cause of the
>I/O error. Do you have any other related messages in the event log? Are
>you moving these database files because a disk is going bad?
>
>[quoted text clipped - 20 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200605/1|||> it simply says attached successfully but i could not see the "model" db
> and
> btw ID 3 refers to the "msdb" db
It looks like msdb got assigned dbid 3 instead of model.
Start sql server in minimal configuration mode and detaching both model and
msdb. Attach model first and verify all is expected:
USE model
EXEC sp_helpfile
EXEC sp_helpdb 'model' --make sure dbid is 3
GO
Then attach msdb and verify all is good:
USE msdb
EXEC sp_helpfile
EXEC sp_helpdb 'msdb' --make sure dbid is > 3
GO
Then restart SQL Server normally.
> in the worst case scenario if i need to re-install SQL Server can i
> restore
> the master , model & msdb over the existing ones ?
Yes, you could restore following a reinstall (or REBUILDM) but I'd try the
reattach first. You are almost there.
Hope this helps.
Dan Guzman
SQL Server MVP
"maxzsim via droptable.com" <u14644@.uwe> wrote in message
news:60fb4a3f43fa3@.uwe...
> Hi ,
> using QA to attach the msdb & model db was done after i ran sqlserv.exe
> /T3608
> it simply says attached successfully but i could not see the "model" db
> and
> btw ID 3 refers to the "msdb" db
> in the worst case scenario if i need to re-install SQL Server can i
> restore
> the master , model & msdb over the existing ones ?
> tks & rdgs
> Dan Guzman wrote:
> --
> Message posted via http://www.droptable.com
Tuesday, February 14, 2012
cannot see database
i have installed MS SQL Server 2000, and in the manager i see all the defaults dbs like : master, model, msdb, northwind, pubs and tempdb
i copyed a data base of my own (a .mdf and a .ldf) in Data folder and i cannot see it
what is the problem? how can i see it?/?You must attach it first (make it known to the SQLServer).
Use EM, klick the server node, right-klick the "Databases" (Folder-like) node, point to "All tasks", point+click "attach database" + put the other stuff in. ...
Or RTFM the section about "sp_attach_db" if you would like to do it via SQL ...|||ill try that when i get home
thx mate :)|||You need to attach the database and when the file open window pop-up, choose the file you have copied.
i copyed a data base of my own (a .mdf and a .ldf) in Data folder and i cannot see it
what is the problem? how can i see it?/?You must attach it first (make it known to the SQLServer).
Use EM, klick the server node, right-klick the "Databases" (Folder-like) node, point to "All tasks", point+click "attach database" + put the other stuff in. ...
Or RTFM the section about "sp_attach_db" if you would like to do it via SQL ...|||ill try that when i get home
thx mate :)|||You need to attach the database and when the file open window pop-up, choose the file you have copied.
Sunday, February 12, 2012
cannot retrieve data from linked server with read-only database
I have linked a SQL Server 6.5 to a SQL Server 2000. The database I'm retrie
ving data from is read-only (a user database, not master).
When I do a select column1, column2 from [linked server].[database].dbo.[table]
it returns some error like
cannot begin transaction because the database is read-only.
What setting I can use to specify that I'm interested in just read-only acce
ss, and no transactions whatsoever?
P.S. I was watching the traffic with Network Monitor, and after a lot of log
in frames and metadata retrieval, the offending statements came (approximate
d from memory):
set implicit_transactions on
begin transaction
set @.@.ROWCOUNT = 1
select * from [the table][QUOTE]
if (@.@.TRANCOUNT > 0) rollback tran
It seems that 2k was trying to check if either the database accepts transact
ions, or whether it is read-only.
Thanks,
MikeHi ,
Can you remove the transaction handling and try executing the script. What
is the reason you are using a Begin tran and Rollback tran with in a select
statement?
There is no need to rollback a select statement.
Incase if you need to do some DML (Insert , delete / update) , Then do the
transaction later in your script.
Thanks
Hari
MCDBA
"Mike U." <anonymous@.discussions.microsoft.com> wrote in message
news:8CB39B05-68FC-4783-824B-17DC987E4583@.microsoft.com...
retrieving data from is read-only (a user database, not master).
server].[database].dbo.[table]
access, and no transactions whatsoever?
login frames and metadata retrieval, the offending statements came
(approximated from memory):
transactions, or whether it is read-only.
The only statement issued by me was
select column1, column2 from [linked server].[database].dbo.[table]
The sequence of statements where you saw transactions were generated by the
SQL Server while attempting to establish communication with the linked serve
r, and captured with Network Monitor. There is a protocol implemented intern
ally, including retrieving
metadata from the linked server. Part of this protocol is what disturbed my
attempt to read from a read-only database, because it tries something with t
ransactions, that has nothing to do with my simple select statement.
I am looking for a setting in the linked server properties to stop it trying
to do the transactions test, and stick with read-only operations.
Mike|||Hi,
Try execute the below statment before running the select statement
SET XACT_ABORT OFF
Thanks
Hari
MCDBA
"Mike U." <anonymous@.discussions.microsoft.com> wrote in message
news:0286FFDD-66C4-475B-8CB4-4191BE872A35@.microsoft.com...
the SQL Server while attempting to establish communication with the linked
server, and captured with Network Monitor. There is a protocol implemented
internally, including retrieving metadata from the linked server. Part of
this protocol is what disturbed my attempt to read from a read-only
database, because it tries something with transactions, that has nothing to
do with my simple select statement.
trying to do the transactions test, and stick with read-only operations.
se as the default catalog in the linked server properties. If I leave it bla
nk, its gonna use master as default catalog, wich is read-write.
The set xact_abort off didn't work, so I still don't know what I would have
done if the whole server was read-only, not just a database.
Mike
-- Hari wrote: --
Hi,
Try execute the below statment before running the select statement
SET XACT_ABORT OFF
Thanks
Hari
MCDBA
"Mike U." <anonymous@.discussions.microsoft.com> wrote in message
news:0286FFDD-66C4-475B-8CB4-4191BE872A35@.microsoft.com...
ving data from is read-only (a user database, not master).
When I do a select column1, column2 from [linked server].[database].dbo.[table]
it returns some error like
cannot begin transaction because the database is read-only.
What setting I can use to specify that I'm interested in just read-only acce
ss, and no transactions whatsoever?
P.S. I was watching the traffic with Network Monitor, and after a lot of log
in frames and metadata retrieval, the offending statements came (approximate
d from memory):
set implicit_transactions on
begin transaction
set @.@.ROWCOUNT = 1
select * from [the table][QUOTE]
if (@.@.TRANCOUNT > 0) rollback tran
It seems that 2k was trying to check if either the database accepts transact
ions, or whether it is read-only.
Thanks,
MikeHi ,
Can you remove the transaction handling and try executing the script. What
is the reason you are using a Begin tran and Rollback tran with in a select
statement?
There is no need to rollback a select statement.
Incase if you need to do some DML (Insert , delete / update) , Then do the
transaction later in your script.
Thanks
Hari
MCDBA
"Mike U." <anonymous@.discussions.microsoft.com> wrote in message
news:8CB39B05-68FC-4783-824B-17DC987E4583@.microsoft.com...
quote:
> I have linked a SQL Server 6.5 to a SQL Server 2000. The database I'm
retrieving data from is read-only (a user database, not master).
quote:
> When I do a select column1, column2 from [linked
server].[database].dbo.[table]
quote:
> it returns some error like
> cannot begin transaction because the database is read-only.
> What setting I can use to specify that I'm interested in just read-only
access, and no transactions whatsoever?
quote:
> P.S. I was watching the traffic with Network Monitor, and after a lot of
login frames and metadata retrieval, the offending statements came
(approximated from memory):
quote:
> set implicit_transactions on
> begin transaction
> set @.@.ROWCOUNT = 1
> select * from [the table]
> if (@.@.TRANCOUNT > 0) rollback tran
> It seems that 2k was trying to check if either the database accepts
transactions, or whether it is read-only.
quote:|||Hari,
> Thanks,
> Mike
>
The only statement issued by me was
select column1, column2 from [linked server].[database].dbo.[table]
The sequence of statements where you saw transactions were generated by the
SQL Server while attempting to establish communication with the linked serve
r, and captured with Network Monitor. There is a protocol implemented intern
ally, including retrieving
metadata from the linked server. Part of this protocol is what disturbed my
attempt to read from a read-only database, because it tries something with t
ransactions, that has nothing to do with my simple select statement.
I am looking for a setting in the linked server properties to stop it trying
to do the transactions test, and stick with read-only operations.
Mike|||Hi,
Try execute the below statment before running the select statement
SET XACT_ABORT OFF
Thanks
Hari
MCDBA
"Mike U." <anonymous@.discussions.microsoft.com> wrote in message
news:0286FFDD-66C4-475B-8CB4-4191BE872A35@.microsoft.com...
quote:
> Hari,
> The only statement issued by me was
> select column1, column2 from [linked server].[database].dbo.[table]
> The sequence of statements where you saw transactions were generated by
the SQL Server while attempting to establish communication with the linked
server, and captured with Network Monitor. There is a protocol implemented
internally, including retrieving metadata from the linked server. Part of
this protocol is what disturbed my attempt to read from a read-only
database, because it tries something with transactions, that has nothing to
do with my simple select statement.
quote:
> I am looking for a setting in the linked server properties to stop it
trying to do the transactions test, and stick with read-only operations.
quote:|||Hi I found a workaround. The problem was that I have set my read-only databa
> Mike
>
se as the default catalog in the linked server properties. If I leave it bla
nk, its gonna use master as default catalog, wich is read-write.
The set xact_abort off didn't work, so I still don't know what I would have
done if the whole server was read-only, not just a database.
Mike
-- Hari wrote: --
Hi,
Try execute the below statment before running the select statement
SET XACT_ABORT OFF
Thanks
Hari
MCDBA
"Mike U." <anonymous@.discussions.microsoft.com> wrote in message
news:0286FFDD-66C4-475B-8CB4-4191BE872A35@.microsoft.com...
quote:
> Hari,
> The only statement issued by me was
> select column1, column2 from [linked server].[database].dbo.[table]
the SQL Server while attempting to establish communication with the linked
server, and captured with Network Monitor. There is a protocol implemented
internally, including retrieving metadata from the linked server. Part of
this protocol is what disturbed my attempt to read from a read-only
database, because it tries something with transactions, that has nothing to
do with my simple select statement.[QUOTE]
trying to do the transactions test, and stick with read-only operations.[QUOTE]
>
cannot retrieve data from linked server with read-only database
I have linked a SQL Server 6.5 to a SQL Server 2000. The database I'm retrieving data from is read-only (a user database, not master)
When I do a select column1, column2 from [linked server].[database].dbo.[table
it returns some error like
cannot begin transaction because the database is read-only
What setting I can use to specify that I'm interested in just read-only access, and no transactions whatsoever
P.S. I was watching the traffic with Network Monitor, and after a lot of login frames and metadata retrieval, the offending statements came (approximated from memory)
set implicit_transactions o
begin transactio
set @.@.ROWCOUNT = select * from [the table
>> (here came the frame with the error response from 6.5
if (@.@.TRANCOUNT > 0) rollback tra
It seems that 2k was trying to check if either the database accepts transactions, or whether it is read-only
Thanks
MikHi ,
Can you remove the transaction handling and try executing the script. What
is the reason you are using a Begin tran and Rollback tran with in a select
statement?
There is no need to rollback a select statement.
Incase if you need to do some DML (Insert , delete / update) , Then do the
transaction later in your script.
Thanks
Hari
MCDBA
"Mike U." <anonymous@.discussions.microsoft.com> wrote in message
news:8CB39B05-68FC-4783-824B-17DC987E4583@.microsoft.com...
> I have linked a SQL Server 6.5 to a SQL Server 2000. The database I'm
retrieving data from is read-only (a user database, not master).
> When I do a select column1, column2 from [linked
server].[database].dbo.[table]
> it returns some error like
> cannot begin transaction because the database is read-only.
> What setting I can use to specify that I'm interested in just read-only
access, and no transactions whatsoever?
> P.S. I was watching the traffic with Network Monitor, and after a lot of
login frames and metadata retrieval, the offending statements came
(approximated from memory):
> set implicit_transactions on
> begin transaction
> set @.@.ROWCOUNT = 1
> select * from [the table]
> >> (here came the frame with the error response from 6.5)
> if (@.@.TRANCOUNT > 0) rollback tran
> It seems that 2k was trying to check if either the database accepts
transactions, or whether it is read-only.
> Thanks,
> Mike
>|||Hari
The only statement issued by me wa
select column1, column2 from [linked server].[database].dbo.[table
The sequence of statements where you saw transactions were generated by the SQL Server while attempting to establish communication with the linked server, and captured with Network Monitor. There is a protocol implemented internally, including retrieving metadata from the linked server. Part of this protocol is what disturbed my attempt to read from a read-only database, because it tries something with transactions, that has nothing to do with my simple select statement
I am looking for a setting in the linked server properties to stop it trying to do the transactions test, and stick with read-only operations
Mik|||Hi,
Try execute the below statment before running the select statement
SET XACT_ABORT OFF
Thanks
Hari
MCDBA
"Mike U." <anonymous@.discussions.microsoft.com> wrote in message
news:0286FFDD-66C4-475B-8CB4-4191BE872A35@.microsoft.com...
> Hari,
> The only statement issued by me was
> select column1, column2 from [linked server].[database].dbo.[table]
> The sequence of statements where you saw transactions were generated by
the SQL Server while attempting to establish communication with the linked
server, and captured with Network Monitor. There is a protocol implemented
internally, including retrieving metadata from the linked server. Part of
this protocol is what disturbed my attempt to read from a read-only
database, because it tries something with transactions, that has nothing to
do with my simple select statement.
> I am looking for a setting in the linked server properties to stop it
trying to do the transactions test, and stick with read-only operations.
> Mike
>|||Hi I found a workaround. The problem was that I have set my read-only database as the default catalog in the linked server properties. If I leave it blank, its gonna use master as default catalog, wich is read-write.
The set xact_abort off didn't work, so I still don't know what I would have done if the whole server was read-only, not just a database.
Mike
-- Hari wrote: --
Hi,
Try execute the below statment before running the select statement
SET XACT_ABORT OFF
Thanks
Hari
MCDBA
"Mike U." <anonymous@.discussions.microsoft.com> wrote in message
news:0286FFDD-66C4-475B-8CB4-4191BE872A35@.microsoft.com...
> Hari,
> The only statement issued by me was
> select column1, column2 from [linked server].[database].dbo.[table]
>> The sequence of statements where you saw transactions were generated by
the SQL Server while attempting to establish communication with the linked
server, and captured with Network Monitor. There is a protocol implemented
internally, including retrieving metadata from the linked server. Part of
this protocol is what disturbed my attempt to read from a read-only
database, because it tries something with transactions, that has nothing to
do with my simple select statement.
>> I am looking for a setting in the linked server properties to stop it
trying to do the transactions test, and stick with read-only operations.
>> Mike
>
When I do a select column1, column2 from [linked server].[database].dbo.[table
it returns some error like
cannot begin transaction because the database is read-only
What setting I can use to specify that I'm interested in just read-only access, and no transactions whatsoever
P.S. I was watching the traffic with Network Monitor, and after a lot of login frames and metadata retrieval, the offending statements came (approximated from memory)
set implicit_transactions o
begin transactio
set @.@.ROWCOUNT = select * from [the table
>> (here came the frame with the error response from 6.5
if (@.@.TRANCOUNT > 0) rollback tra
It seems that 2k was trying to check if either the database accepts transactions, or whether it is read-only
Thanks
MikHi ,
Can you remove the transaction handling and try executing the script. What
is the reason you are using a Begin tran and Rollback tran with in a select
statement?
There is no need to rollback a select statement.
Incase if you need to do some DML (Insert , delete / update) , Then do the
transaction later in your script.
Thanks
Hari
MCDBA
"Mike U." <anonymous@.discussions.microsoft.com> wrote in message
news:8CB39B05-68FC-4783-824B-17DC987E4583@.microsoft.com...
> I have linked a SQL Server 6.5 to a SQL Server 2000. The database I'm
retrieving data from is read-only (a user database, not master).
> When I do a select column1, column2 from [linked
server].[database].dbo.[table]
> it returns some error like
> cannot begin transaction because the database is read-only.
> What setting I can use to specify that I'm interested in just read-only
access, and no transactions whatsoever?
> P.S. I was watching the traffic with Network Monitor, and after a lot of
login frames and metadata retrieval, the offending statements came
(approximated from memory):
> set implicit_transactions on
> begin transaction
> set @.@.ROWCOUNT = 1
> select * from [the table]
> >> (here came the frame with the error response from 6.5)
> if (@.@.TRANCOUNT > 0) rollback tran
> It seems that 2k was trying to check if either the database accepts
transactions, or whether it is read-only.
> Thanks,
> Mike
>|||Hari
The only statement issued by me wa
select column1, column2 from [linked server].[database].dbo.[table
The sequence of statements where you saw transactions were generated by the SQL Server while attempting to establish communication with the linked server, and captured with Network Monitor. There is a protocol implemented internally, including retrieving metadata from the linked server. Part of this protocol is what disturbed my attempt to read from a read-only database, because it tries something with transactions, that has nothing to do with my simple select statement
I am looking for a setting in the linked server properties to stop it trying to do the transactions test, and stick with read-only operations
Mik|||Hi,
Try execute the below statment before running the select statement
SET XACT_ABORT OFF
Thanks
Hari
MCDBA
"Mike U." <anonymous@.discussions.microsoft.com> wrote in message
news:0286FFDD-66C4-475B-8CB4-4191BE872A35@.microsoft.com...
> Hari,
> The only statement issued by me was
> select column1, column2 from [linked server].[database].dbo.[table]
> The sequence of statements where you saw transactions were generated by
the SQL Server while attempting to establish communication with the linked
server, and captured with Network Monitor. There is a protocol implemented
internally, including retrieving metadata from the linked server. Part of
this protocol is what disturbed my attempt to read from a read-only
database, because it tries something with transactions, that has nothing to
do with my simple select statement.
> I am looking for a setting in the linked server properties to stop it
trying to do the transactions test, and stick with read-only operations.
> Mike
>|||Hi I found a workaround. The problem was that I have set my read-only database as the default catalog in the linked server properties. If I leave it blank, its gonna use master as default catalog, wich is read-write.
The set xact_abort off didn't work, so I still don't know what I would have done if the whole server was read-only, not just a database.
Mike
-- Hari wrote: --
Hi,
Try execute the below statment before running the select statement
SET XACT_ABORT OFF
Thanks
Hari
MCDBA
"Mike U." <anonymous@.discussions.microsoft.com> wrote in message
news:0286FFDD-66C4-475B-8CB4-4191BE872A35@.microsoft.com...
> Hari,
> The only statement issued by me was
> select column1, column2 from [linked server].[database].dbo.[table]
>> The sequence of statements where you saw transactions were generated by
the SQL Server while attempting to establish communication with the linked
server, and captured with Network Monitor. There is a protocol implemented
internally, including retrieving metadata from the linked server. Part of
this protocol is what disturbed my attempt to read from a read-only
database, because it tries something with transactions, that has nothing to
do with my simple select statement.
>> I am looking for a setting in the linked server properties to stop it
trying to do the transactions test, and stick with read-only operations.
>> Mike
>
Cannot restore to database
Drop database and recreated, however cannot restore data. Says Data dump came from Master. If I choose Master, says I must be in single user mode.Hi,
Are you trying to restore the Master database or some user database ? If it
is master u have to
1.. Start Microsoft® SQL ServerT in single-user mode. (use -m in service
manager while starting sql server)
2.. Execute the RESTORE DATABASE statement to restore the master database
backup
3.. Stop and start the SQL Server in Muliuser Mode. (Just Restart MSSQL
Server service)
Thanks
Hari
MCDBA
"bscottparis" <anonymous@.discussions.microsoft.com> wrote in message
news:9D1F49FF-E704-4DE1-BB81-722033F6169E@.microsoft.com...
> Drop database and recreated, however cannot restore data. Says Data dump
came from Master. If I choose Master, says I must be in single user mode.
Are you trying to restore the Master database or some user database ? If it
is master u have to
1.. Start Microsoft® SQL ServerT in single-user mode. (use -m in service
manager while starting sql server)
2.. Execute the RESTORE DATABASE statement to restore the master database
backup
3.. Stop and start the SQL Server in Muliuser Mode. (Just Restart MSSQL
Server service)
Thanks
Hari
MCDBA
"bscottparis" <anonymous@.discussions.microsoft.com> wrote in message
news:9D1F49FF-E704-4DE1-BB81-722033F6169E@.microsoft.com...
> Drop database and recreated, however cannot restore data. Says Data dump
came from Master. If I choose Master, says I must be in single user mode.
Subscribe to:
Posts (Atom)