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 therefore. Show all posts
Showing posts with label therefore. 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
Saturday, February 25, 2012
Cannot start Integration Services
Hi,
I would like to use the features of the Integration Service on SQL Server
2005.
Therefore I have to connect to Server type Integration Services.
When I connect, it is trying to connect for 30 seconds and then it displays
the message that the service did not respond to the start or control request
in a timely fashion.
When I try to start the integrated services service, it won't start.
The error I am getting is:
Could not start the SQL Server Integration Services service on Local
Computer.
Error 1053: The service did not respond to the start or control request in a
timely fashion.
In the Event Log the following 2 errors are created:
ID 7000:
The SQL Server Integration Services service failed to start due to the
following error:
The service did not respond to the start or control request in a timely
fashion.
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
ID 7009:
Timeout (30000 milliseconds) waiting for the SQL Server Integration Services
service to connect.
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
When I follow this hyperlink, a page is displayed with information how to
display the WIN32_EXIT_CODE error.
It tells me to type sc query <service name> from the command prompt.
I have not been able to see this error code, because I don't know the exact
name of the service.
I tried all kinds of variations of the name, in- and excluding spaces and
qoutes, but everytime I got this error:
The specified service does not exist as an installed service.
I am working with SQL Server 2005 on a Windows 2003 server.
Any ideas?Please see if this applies: http://support.microsoft.com/kb/918644
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||It is working fine now, although there were lots of errors in the
installation logs of each update.
The errors reported failures of verifying the correct version of installed
files and some registry keys.
Thanks,
John.
Paul Ibison <Paul.Ibison@.Pygmalion.Com> wrote in message
news:#qFHBrnLHHA.4928@.TK2MSFTNGP06.phx.gbl...
> Please see if this applies: http://support.microsoft.com/kb/918644
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
I would like to use the features of the Integration Service on SQL Server
2005.
Therefore I have to connect to Server type Integration Services.
When I connect, it is trying to connect for 30 seconds and then it displays
the message that the service did not respond to the start or control request
in a timely fashion.
When I try to start the integrated services service, it won't start.
The error I am getting is:
Could not start the SQL Server Integration Services service on Local
Computer.
Error 1053: The service did not respond to the start or control request in a
timely fashion.
In the Event Log the following 2 errors are created:
ID 7000:
The SQL Server Integration Services service failed to start due to the
following error:
The service did not respond to the start or control request in a timely
fashion.
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
ID 7009:
Timeout (30000 milliseconds) waiting for the SQL Server Integration Services
service to connect.
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
When I follow this hyperlink, a page is displayed with information how to
display the WIN32_EXIT_CODE error.
It tells me to type sc query <service name> from the command prompt.
I have not been able to see this error code, because I don't know the exact
name of the service.
I tried all kinds of variations of the name, in- and excluding spaces and
qoutes, but everytime I got this error:
The specified service does not exist as an installed service.
I am working with SQL Server 2005 on a Windows 2003 server.
Any ideas?Please see if this applies: http://support.microsoft.com/kb/918644
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||It is working fine now, although there were lots of errors in the
installation logs of each update.
The errors reported failures of verifying the correct version of installed
files and some registry keys.
Thanks,
John.
Paul Ibison <Paul.Ibison@.Pygmalion.Com> wrote in message
news:#qFHBrnLHHA.4928@.TK2MSFTNGP06.phx.gbl...
> Please see if this applies: http://support.microsoft.com/kb/918644
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
Cannot start Integration Services
Hi,
I would like to use the features of the Integration Service on SQL Server
2005.
Therefore I have to connect to Server type Integration Services.
When I connect, it is trying to connect for 30 seconds and then it displays
the message that the service did not respond to the start or control request
in a timely fashion.
When I try to start the integrated services service, it won't start.
The error I am getting is:
Could not start the SQL Server Integration Services service on Local
Computer.
Error 1053: The service did not respond to the start or control request in a
timely fashion.
In the Event Log the following 2 errors are created:
ID 7000:
The SQL Server Integration Services service failed to start due to the
following error:
The service did not respond to the start or control request in a timely
fashion.
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
ID 7009:
Timeout (30000 milliseconds) waiting for the SQL Server Integration Services
service to connect.
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
When I follow this hyperlink, a page is displayed with information how to
display the WIN32_EXIT_CODE error.
It tells me to type sc query <service name> from the command prompt.
I have not been able to see this error code, because I don't know the exact
name of the service.
I tried all kinds of variations of the name, in- and excluding spaces and
qoutes, but everytime I got this error:
The specified service does not exist as an installed service.
I am working with SQL Server 2005 on a Windows 2003 server.
Any ideas?Please see if this applies: http://support.microsoft.com/kb/918644
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||It is working fine now, although there were lots of errors in the
installation logs of each update.
The errors reported failures of verifying the correct version of installed
files and some registry keys.
Thanks,
John.
Paul Ibison <Paul.Ibison@.Pygmalion.Com> wrote in message
news:#qFHBrnLHHA.4928@.TK2MSFTNGP06.phx.gbl...
> Please see if this applies: http://support.microsoft.com/kb/918644
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
I would like to use the features of the Integration Service on SQL Server
2005.
Therefore I have to connect to Server type Integration Services.
When I connect, it is trying to connect for 30 seconds and then it displays
the message that the service did not respond to the start or control request
in a timely fashion.
When I try to start the integrated services service, it won't start.
The error I am getting is:
Could not start the SQL Server Integration Services service on Local
Computer.
Error 1053: The service did not respond to the start or control request in a
timely fashion.
In the Event Log the following 2 errors are created:
ID 7000:
The SQL Server Integration Services service failed to start due to the
following error:
The service did not respond to the start or control request in a timely
fashion.
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
ID 7009:
Timeout (30000 milliseconds) waiting for the SQL Server Integration Services
service to connect.
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
When I follow this hyperlink, a page is displayed with information how to
display the WIN32_EXIT_CODE error.
It tells me to type sc query <service name> from the command prompt.
I have not been able to see this error code, because I don't know the exact
name of the service.
I tried all kinds of variations of the name, in- and excluding spaces and
qoutes, but everytime I got this error:
The specified service does not exist as an installed service.
I am working with SQL Server 2005 on a Windows 2003 server.
Any ideas?Please see if this applies: http://support.microsoft.com/kb/918644
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||It is working fine now, although there were lots of errors in the
installation logs of each update.
The errors reported failures of verifying the correct version of installed
files and some registry keys.
Thanks,
John.
Paul Ibison <Paul.Ibison@.Pygmalion.Com> wrote in message
news:#qFHBrnLHHA.4928@.TK2MSFTNGP06.phx.gbl...
> Please see if this applies: http://support.microsoft.com/kb/918644
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
Subscribe to:
Posts (Atom)