can't connect

hey all,
i'm trying, unsuccessfully, to connect to my msde(sp4) which is on another
pc (has xp sp2) wirelessly thru a virtual pc which has Enterprise Manager on
it.
My results have been a moving target. One day i can connect via my virtual
pc with enterprise manager and the next day i can connect via my host pc with
a plain UDL link but never both.
i don't understand the inconsistency in connecting to the pc that has msde
on it. i've successfully mapped a drive to it. the msde machine's windows
firewall is on accepting file and print sharing (port 445 enabled). i have a
4-port wireless router and as i mentioned before i'm trying to connect
wirelessly.
am i missing something here?
thanks,
rodchar
how do i verify my network is working properly?
"Andrea Montanari" wrote:

> hi Rodchar,
> rodchar wrote:
> you do not need to have file system shares to connect to.. you only have to
> worry about "general" network connectivity across the involved machines and
> that the target server accepts remote connections (as it already should, as
> you get some sort of connectivity)...
> verify your network is poroperly working..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz http://italy.mvps.org
> DbaMgr2k ver 0.20.0 - DbaMgr ver 0.64.0 and further SQL Tools
> -- remove DMO to reply
>
>

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
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

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

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

Can't Access Named Instanced With Network Protocol Disabled

Hi there,
I have a little question here.
I've installed a named instance of MSDE SP4 called mydb, with network
protocols disabled.
Then I noticed that I can't connect to it using OSQL
I tried
'osl -S (local)\mydb -E'
'osl -S localhost\mydb -E'
and both of them can't connect to the database, however.
'osql -S mypcname\mydb -E'
works perfectly... I don't want to manually fill in the machine name for
each connection string can someone tell me why (local) doesn't seem to
connect to the local machine? Does it have anything to do with network
protocols disabled?
Thanks.
Hi
(local) and localhost rely on the network stack to resolve. If you network
is disabled, there is nothing there to resolve the name. localhost resolves
to 127.0.0.1 by TCP/IP.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Marauderz" <marauderz@.nospam.com> wrote in message
news:u1dlfrAqFHA.3720@.TK2MSFTNGP14.phx.gbl...
> Hi there,
> I have a little question here.
> I've installed a named instance of MSDE SP4 called mydb, with network
> protocols disabled.
> Then I noticed that I can't connect to it using OSQL
> I tried
> 'osl -S (local)\mydb -E'
> 'osl -S localhost\mydb -E'
> and both of them can't connect to the database, however.
> 'osql -S mypcname\mydb -E'
> works perfectly... I don't want to manually fill in the machine name for
> each connection string can someone tell me why (local) doesn't seem to
> connect to the local machine? Does it have anything to do with network
> protocols disabled?
> Thanks.
>

Cannot use subquery in UPDATE statement?

Hi there,
I try to run the following update statement on MS SQL Server 2000 SP4
UPDATE Site
SET Active = 0
WHERE
RID IN (SELECT site.RID AS site_rid
FROM Site INNER JOIN
ManagerSite ms ON site.RID = ms.ArtistSiteID INNER JOIN
Manager m ON ms.ManagerID = m.RID
WHERE (am.UserName = 'iwk1004') AND
(ms.isPrimaryUser = 1))
Running this query I get the message "Subquery returned more than 1
value. This is not permitted when the subquery follows =, !=, <, <= ,
>, >= or when the subquery is used as an expression."
Which is weird, because according to the documentation and examples I
inferred could use UPDATE with the use of IN in the condition and the
subquery returining multiple rows. (sub-query works fine BTW)
Any idea what is going on?
Regards,
Iwaniwanvanderkleijn@.gmail.com wrote:
> Hi there,
> I try to run the following update statement on MS SQL Server 2000 SP4
> UPDATE Site
> SET Active = 0
> WHERE
> RID IN (SELECT site.RID AS site_rid
> FROM Site INNER JOIN
> ManagerSite ms ON site.RID = ms.ArtistSiteID INNER JOIN
> Manager m ON ms.ManagerID = m.RID
> WHERE (am.UserName = 'iwk1004') AND
> (ms.isPrimaryUser = 1))
> Running this query I get the message "Subquery returned more than 1
> value. This is not permitted when the subquery follows =, !=, <, <= ,
> >, >= or when the subquery is used as an expression."
> Which is weird, because according to the documentation and examples I
> inferred could use UPDATE with the use of IN in the condition and the
> subquery returining multiple rows. (sub-query works fine BTW)
> Any idea what is going on?
> Regards,
> Iwan
Your subquery returns multiple rows so change it to return some
aggregation of site.rid.
like
UPDATE Site
SET Active = 0
WHERE
RID IN (SELECT max(site.RID) AS site_rid
FROM Site INNER JOIN
ManagerSite ms ON site.RID = ms.ArtistSiteID INNER JOIN
Manager m ON ms.ManagerID = m.RID
WHERE (am.UserName = 'iwk1004') AND
(ms.isPrimaryUser = 1))
Regards
Amish Shah
http://shahamishm.tripod.com|||Not tested this at all - just an idea
UPDATE Site
SET Active = 0
FROM
(
SELECT site.RID AS site_rid
FROM Site INNER JOIN
ManagerSite ms ON site.RID = ms.ArtistSiteID INNER
JOIN
Manager m ON ms.ManagerID = m.RID
WHERE (am.UserName = 'iwk1004') AND
(ms.isPrimaryUser = 1
) as tRes
WHERE tRes.site_rid = site.RID
/* You could remove site table from the correlated subquery tRes all
together becuase it actually isn't needed and just join
tREs.ArtistSiteID = site.RID */
Or try your original subquery with EXISTS clause rather than IN|||I agree that the message makes no sense with the code shown, since
multiple values are expected after an IN.
This could be written with an EXISTS test using a correlated subquery,
rather than an IN.
UPDATE Site
SET Active = 0
WHERE EXISTS
(SELECT *
FROM ManagerSite ms
JOIN Manager m
ON ms.ManagerID = m.RID
WHERE site.RID = ms.ArtistSiteID
AND am.UserName = 'iwk1004'
AND ms.isPrimaryUser = 1)
Roy Harvey
Beacon Falls, CT
On 7 Aug 2006 03:07:06 -0700, "iwanvanderkleijn@.gmail.com"
<iwanvanderkleijn@.gmail.com> wrote:
>Hi there,
>I try to run the following update statement on MS SQL Server 2000 SP4
>UPDATE Site
>SET Active = 0
> WHERE
> RID IN (SELECT site.RID AS site_rid
> FROM Site INNER JOIN
> ManagerSite ms ON site.RID = ms.ArtistSiteID INNER JOIN
> Manager m ON ms.ManagerID = m.RID
> WHERE (am.UserName = 'iwk1004') AND
>(ms.isPrimaryUser = 1))
>Running this query I get the message "Subquery returned more than 1
>value. This is not permitted when the subquery follows =, !=, <, <= ,
>>, >= or when the subquery is used as an expression."
>Which is weird, because according to the documentation and examples I
>inferred could use UPDATE with the use of IN in the condition and the
>subquery returining multiple rows. (sub-query works fine BTW)
>Any idea what is going on?
>Regards,
>Iwan|||I'm confused about what the [am.] alias is referring to.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<iwanvanderkleijn@.gmail.com> wrote in message
news:1154945225.949531.95440@.i42g2000cwa.googlegroups.com...
> Hi there,
> I try to run the following update statement on MS SQL Server 2000 SP4
> UPDATE Site
> SET Active = 0
> WHERE
> RID IN (SELECT site.RID AS site_rid
> FROM Site INNER JOIN
> ManagerSite ms ON site.RID = ms.ArtistSiteID INNER JOIN
> Manager m ON ms.ManagerID = m.RID
> WHERE (am.UserName = 'iwk1004') AND
> (ms.isPrimaryUser = 1))
> Running this query I get the message "Subquery returned more than 1
> value. This is not permitted when the subquery follows =, !=, <, <= ,
>>, >= or when the subquery is used as an expression."
> Which is weird, because according to the documentation and examples I
> inferred could use UPDATE with the use of IN in the condition and the
> subquery returining multiple rows. (sub-query works fine BTW)
> Any idea what is going on?
> Regards,
> Iwan
>|||Arnie Rowland wrote:
> I'm confused about what the [am.] alias is referring to.
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> <iwanvanderkleijn@.gmail.com> wrote in message
> news:1154945225.949531.95440@.i42g2000cwa.googlegroups.com...
> > Hi there,
> >
> > I try to run the following update statement on MS SQL Server 2000 SP4
> >
> > UPDATE Site
> >
> > SET Active = 0
> >
> > WHERE
> > RID IN (SELECT site.RID AS site_rid
> > FROM Site INNER JOIN
> > ManagerSite ms ON site.RID = ms.ArtistSiteID INNER JOIN
> > Manager m ON ms.ManagerID = m.RID
> > WHERE (am.UserName = 'iwk1004') AND
> > (ms.isPrimaryUser = 1))
> >
> > Running this query I get the message "Subquery returned more than 1
> > value. This is not permitted when the subquery follows =, !=, <, <= ,
> >>, >= or when the subquery is used as an expression."
> >
> > Which is weird, because according to the documentation and examples I
> > inferred could use UPDATE with the use of IN in the condition and the
> > subquery returining multiple rows. (sub-query works fine BTW)
> >
> > Any idea what is going on?
> >
> > Regards,
> >
> > Iwan
> >
I misunderstand the prob.
Your query looks ok but am is not referencing any table
Regards
Amish Shah|||I think it is confused over what "Site" referance you are trying to
update because it is name twice. Try the following pattern (tables
from Northwind) where you alias the update table.
update o
set o.quantity = 1
from [Order Details] o
where o.OrderId in
(select [Order Details].OrderID
from [Order Details]
inner join dbo.Orders
on [Order Details].Orderid = Orders.OrderID )
JJ

Cannot Upgrade MSDE 2000 Sp3 to SP4

Hi!

Here's the scenario:

I have a MSDE 2000 installation running, already patched to SP3. Instancename is "STO". I've downloaded SQL2000.MSDE-KB884525-SP4-X86-ENU.exe and extracted the archive to C:\SQL2KSP4.

After starting setup from the command prompt with "setup /upgradesp sqlrun DISABLENETWORKPROTOCOLS=0 /L*V C:\MSDELog.log" i get a message box, telling me "The product is not installed". This happens even if I specify the exact instancename and supply the sa user and pwd!

Any ideas?`

Thanks in advance and regards

Clemens

In your command line, you need to specify the instance name and sa pass word. Would you try the following command line?

start /wait setup.exe /qb INSTANCENAME=sto SAPWD="<sa pass word>" SECURITYMODE=SQL

|||

Thx for your reply, but if you look at my post above, you'll see that i also tried that! ;-)

Quote:

"This happens even if I specify the exact instancename and supply the sa user and pwd!"

Rgds

Clemens

|||In your command line, you do not specify the instance name. It is required for an upgrading. Would you check and try?

cannot start MSSQLServerOLAPService....error 1058: the service cannot be started

sql 2000 w/ sp4 running on w2k3 using sql authenitcation......olap service
account is running under AD admin account and is a member of local OLAP
admin group.
not sure what else to check....have looked all over the net.
thanks.OS error 1058 means "The service cannot be started, either because it is
disabled or because it has no enabled devices associated with it".
So, from the services applet, have you made sure the service is not
disabled?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"madman_miceli" <madman@.amiceli.com> wrote in message
news:eVQK11JmFHA.3312@.tk2msftngp13.phx.gbl...
sql 2000 w/ sp4 running on w2k3 using sql authenitcation......olap service
account is running under AD admin account and is a member of local OLAP
admin group.
not sure what else to check....have looked all over the net.
thanks.

cannot start MSSQLServerOLAPService....error 1058: the service cannot be started

sql 2000 w/ sp4 running on w2k3 using sql authenitcation......olap service
account is running under AD admin account and is a member of local OLAP
admin group.
not sure what else to check....have looked all over the net.
thanks.
OS error 1058 means "The service cannot be started, either because it is
disabled or because it has no enabled devices associated with it".
So, from the services applet, have you made sure the service is not
disabled?
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"madman_miceli" <madman@.amiceli.com> wrote in message
news:eVQK11JmFHA.3312@.tk2msftngp13.phx.gbl...
sql 2000 w/ sp4 running on w2k3 using sql authenitcation......olap service
account is running under AD admin account and is a member of local OLAP
admin group.
not sure what else to check....have looked all over the net.
thanks.

cannot start MSSQLServerOLAPService....error 1058: the service cannot be started

sql 2000 w/ sp4 running on w2k3 using sql authenitcation......olap service
account is running under AD admin account and is a member of local OLAP
admin group.
not sure what else to check....have looked all over the net.
thanks.OS error 1058 means "The service cannot be started, either because it is
disabled or because it has no enabled devices associated with it".
So, from the services applet, have you made sure the service is not
disabled?
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"madman_miceli" <madman@.amiceli.com> wrote in message
news:eVQK11JmFHA.3312@.tk2msftngp13.phx.gbl...
sql 2000 w/ sp4 running on w2k3 using sql authenitcation......olap service
account is running under AD admin account and is a member of local OLAP
admin group.
not sure what else to check....have looked all over the net.
thanks.

Cannot shrink transaction log - windows 2003/sp1 and SQL 2000/SP4

I cannot shrink a transaction log to a database through the enterprise mmc.
This particular transaction log is about 135 GB and is filling up the hard
drive. Is there a way to force this?http://support.microsoft.com/kb/q272318/
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Sabo, Eric" wrote:
> I cannot shrink a transaction log to a database through the enterprise mmc.
> This particular transaction log is about 135 GB and is filling up the hard
> drive. Is there a way to force this?|||Mike,
Thanks for the reply but where do you run this command from. Also, what
if I have no space to backup this transaction log.
"Mike Epprecht (SQL MVP)" wrote:
> http://support.microsoft.com/kb/q272318/
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Sabo, Eric" wrote:
> > I cannot shrink a transaction log to a database through the enterprise mmc.
> > This particular transaction log is about 135 GB and is filling up the hard
> > drive. Is there a way to force this?|||It is saying the following when I tried to shrink the database:
Cannot shrink log file 2 (XXXXXXXX_log) because all logical log files are in
use.
"Sabo, Eric" wrote:
> I cannot shrink a transaction log to a database through the enterprise mmc.
> This particular transaction log is about 135 GB and is filling up the hard
> drive. Is there a way to force this?|||Hi
OK, so I assume that you have not backuped up your transaction log, nor
require it, so put the DB in simple Recovery Mode using Enterprise Manager
(Property of the DB)
This will clear all inactive entries in the log and then you can shrink the
log,
You need to run those commands in the KB from Query Analyser.
Look up "recovery plan" in Books Online to understand how the log is used
for recovery in DR scenarios.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Sabo, Eric" wrote:
> Mike,
> Thanks for the reply but where do you run this command from. Also, what
> if I have no space to backup this transaction log.
> "Mike Epprecht (SQL MVP)" wrote:
> > http://support.microsoft.com/kb/q272318/
> > --
> > --
> > Mike Epprecht, Microsoft SQL Server MVP
> > Zurich, Switzerland
> >
> > MVP Program: http://www.microsoft.com/mvp
> >
> > Blog: http://www.msmvps.com/epprecht/
> >
> >
> >
> > "Sabo, Eric" wrote:
> >
> > > I cannot shrink a transaction log to a database through the enterprise mmc.
> > > This particular transaction log is about 135 GB and is filling up the hard
> > > drive. Is there a way to force this?|||Mike,
Thanks alot.
That worked. I restricted the growth this time so it will not fill up my
hard drive again. Thanks again!
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> OK, so I assume that you have not backuped up your transaction log, nor
> require it, so put the DB in simple Recovery Mode using Enterprise Manager
> (Property of the DB)
> This will clear all inactive entries in the log and then you can shrink the
> log,
> You need to run those commands in the KB from Query Analyser.
> Look up "recovery plan" in Books Online to understand how the log is used
> for recovery in DR scenarios.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Sabo, Eric" wrote:
> > Mike,
> >
> > Thanks for the reply but where do you run this command from. Also, what
> > if I have no space to backup this transaction log.
> >
> > "Mike Epprecht (SQL MVP)" wrote:
> >
> > > http://support.microsoft.com/kb/q272318/
> > > --
> > > --
> > > Mike Epprecht, Microsoft SQL Server MVP
> > > Zurich, Switzerland
> > >
> > > MVP Program: http://www.microsoft.com/mvp
> > >
> > > Blog: http://www.msmvps.com/epprecht/
> > >
> > >
> > >
> > > "Sabo, Eric" wrote:
> > >
> > > > I cannot shrink a transaction log to a database through the enterprise mmc.
> > > > This particular transaction log is about 135 GB and is filling up the hard
> > > > drive. Is there a way to force this?

Cannot shrink transaction log - windows 2003/sp1 and SQL 2000/SP4

I cannot shrink a transaction log to a database through the enterprise mmc.
This particular transaction log is about 135 GB and is filling up the hard
drive. Is there a way to force this?
http://support.microsoft.com/kb/q272318/
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Sabo, Eric" wrote:

> I cannot shrink a transaction log to a database through the enterprise mmc.
> This particular transaction log is about 135 GB and is filling up the hard
> drive. Is there a way to force this?
|||It is saying the following when I tried to shrink the database:
Cannot shrink log file 2 (XXXXXXXX_log) because all logical log files are in
use.
"Sabo, Eric" wrote:

> I cannot shrink a transaction log to a database through the enterprise mmc.
> This particular transaction log is about 135 GB and is filling up the hard
> drive. Is there a way to force this?

Cannot shrink transaction log - windows 2003/sp1 and SQL 2000/SP4

I cannot shrink a transaction log to a database through the enterprise mmc.
This particular transaction log is about 135 GB and is filling up the hard
drive. Is there a way to force this?http://support.microsoft.com/kb/q272318/
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Sabo, Eric" wrote:

> I cannot shrink a transaction log to a database through the enterprise mmc
.
> This particular transaction log is about 135 GB and is filling up the hard
> drive. Is there a way to force this?|||It is saying the following when I tried to shrink the database:
Cannot shrink log file 2 (XXXXXXXX_log) because all logical log files are in
use.
"Sabo, Eric" wrote:

> I cannot shrink a transaction log to a database through the enterprise mmc
.
> This particular transaction log is about 135 GB and is filling up the hard
> drive. Is there a way to force this?

Cannot shrink database because of StatBlob data on sysindexes

SQL Server 2000 SP4
Data pages from the image column StatBlob on sysIndexes exist right at the
end of my data file. They are stopping me truncating the end of my datafile.
I had other tables with text columns. These were moved by either copying the
table or bcp/truncate.
It there a way to move the data on sysIndexes without doing a "shrinkfile
move datapages"?
Regards
Paul CahillYou can try dropping all the statistics and see if it frees up that extent.
Just make sure you know which stats you may want to put back. By the way
this is a good example of one reason why you should keep only the system
objects in the Primary filegroup and place the user objects in secondary
filegroups.
--
Andrew J. Kelly SQL MVP
"Paul Cahill" <anon@.anon.com> wrote in message
news:ubv2hej3GHA.3492@.TK2MSFTNGP06.phx.gbl...
> SQL Server 2000 SP4
> Data pages from the image column StatBlob on sysIndexes exist right at the
> end of my data file. They are stopping me truncating the end of my
> datafile.
> I had other tables with text columns. These were moved by either copying
> the table or bcp/truncate.
> It there a way to move the data on sysIndexes without doing a "shrinkfile
> move datapages"?
> Regards
> Paul Cahill
>|||Hi Andrew
How do I drop statistics for indexed columns? The StatBlob values are for
indexes not for stats manually created with CREATE STATISTICS.
I could rebuild all stats on the database but I do not know if this will
move the pages where StatBlob is stored.
I tried doing a full sp_updatestats on a test database and the pages did not
seem to move for sysIndexes
(I use dbcc extentinfo)
Regards
Paul
PS No he tells me
PPS Where does it say in the manual "DO NOT USE PRIMARY"
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eJDoC%23k3GHA.4560@.TK2MSFTNGP05.phx.gbl...
> You can try dropping all the statistics and see if it frees up that
> extent. Just make sure you know which stats you may want to put back. By
> the way this is a good example of one reason why you should keep only the
> system objects in the Primary filegroup and place the user objects in
> secondary filegroups.
> --
> Andrew J. Kelly SQL MVP
> "Paul Cahill" <anon@.anon.com> wrote in message
> news:ubv2hej3GHA.3492@.TK2MSFTNGP06.phx.gbl...
>> SQL Server 2000 SP4
>> Data pages from the image column StatBlob on sysIndexes exist right at
>> the end of my data file. They are stopping me truncating the end of my
>> datafile.
>> I had other tables with text columns. These were moved by either copying
>> the table or bcp/truncate.
>> It there a way to move the data on sysIndexes without doing a "shrinkfile
>> move datapages"?
>> Regards
>> Paul Cahill
>|||Ahh, well then you may have to drop the index itself. updating stats most
likely will not do anything since it just updates the row in sysindexes that
already exists. It has no need to move it. Where as is you had a lot of
stats or indexes that you drop there is a potential for the extent they are
on to be deallocated and thus allowing the shrink.
--
Andrew J. Kelly SQL MVP
"Paul Cahill" <anon@.anon.com> wrote in message
news:uA2U2kl3GHA.696@.TK2MSFTNGP03.phx.gbl...
> Hi Andrew
> How do I drop statistics for indexed columns? The StatBlob values are for
> indexes not for stats manually created with CREATE STATISTICS.
> I could rebuild all stats on the database but I do not know if this will
> move the pages where StatBlob is stored.
> I tried doing a full sp_updatestats on a test database and the pages did
> not seem to move for sysIndexes
> (I use dbcc extentinfo)
> Regards
> Paul
> PS No he tells me
> PPS Where does it say in the manual "DO NOT USE PRIMARY"
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eJDoC%23k3GHA.4560@.TK2MSFTNGP05.phx.gbl...
>> You can try dropping all the statistics and see if it frees up that
>> extent. Just make sure you know which stats you may want to put back. By
>> the way this is a good example of one reason why you should keep only the
>> system objects in the Primary filegroup and place the user objects in
>> secondary filegroups.
>> --
>> Andrew J. Kelly SQL MVP
>> "Paul Cahill" <anon@.anon.com> wrote in message
>> news:ubv2hej3GHA.3492@.TK2MSFTNGP06.phx.gbl...
>> SQL Server 2000 SP4
>> Data pages from the image column StatBlob on sysIndexes exist right at
>> the end of my data file. They are stopping me truncating the end of my
>> datafile.
>> I had other tables with text columns. These were moved by either copying
>> the table or bcp/truncate.
>> It there a way to move the data on sysIndexes without doing a
>> "shrinkfile move datapages"?
>> Regards
>> Paul Cahill
>>
>|||Hi Andrew.
Given a values from dbcc ententinfo can this be linked back to the row in
sysIndexes?
I.e. there may be a single index I could recreate that would clear my block.
I had never thought about not using primary for user data.
With hindsight it makes sense. I have not seen any Microsoft documents that
mention this.
It would make more sense if SQL Server simply kept it's system tables on a
private filegroup.
Paul
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e1Qn9Uo3GHA.1256@.TK2MSFTNGP04.phx.gbl...
> Ahh, well then you may have to drop the index itself. updating stats most
> likely will not do anything since it just updates the row in sysindexes
> that already exists. It has no need to move it. Where as is you had a lot
> of stats or indexes that you drop there is a potential for the extent they
> are on to be deallocated and thus allowing the shrink.
> --
> Andrew J. Kelly SQL MVP
> "Paul Cahill" <anon@.anon.com> wrote in message
> news:uA2U2kl3GHA.696@.TK2MSFTNGP03.phx.gbl...
>> Hi Andrew
>> How do I drop statistics for indexed columns? The StatBlob values are for
>> indexes not for stats manually created with CREATE STATISTICS.
>> I could rebuild all stats on the database but I do not know if this will
>> move the pages where StatBlob is stored.
>> I tried doing a full sp_updatestats on a test database and the pages did
>> not seem to move for sysIndexes
>> (I use dbcc extentinfo)
>> Regards
>> Paul
>> PS No he tells me
>> PPS Where does it say in the manual "DO NOT USE PRIMARY"
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:eJDoC%23k3GHA.4560@.TK2MSFTNGP05.phx.gbl...
>> You can try dropping all the statistics and see if it frees up that
>> extent. Just make sure you know which stats you may want to put back.
>> By the way this is a good example of one reason why you should keep only
>> the system objects in the Primary filegroup and place the user objects
>> in secondary filegroups.
>> --
>> Andrew J. Kelly SQL MVP
>> "Paul Cahill" <anon@.anon.com> wrote in message
>> news:ubv2hej3GHA.3492@.TK2MSFTNGP06.phx.gbl...
>> SQL Server 2000 SP4
>> Data pages from the image column StatBlob on sysIndexes exist right at
>> the end of my data file. They are stopping me truncating the end of my
>> datafile.
>> I had other tables with text columns. These were moved by either
>> copying the table or bcp/truncate.
>> It there a way to move the data on sysIndexes without doing a
>> "shrinkfile move datapages"?
>> Regards
>> Paul Cahill
>>
>>
>|||To be honest I don't know if it can be linked back or not but it would not
be a single index as that would only take up one row in sysindexes. You
would need to clear the entire extent so it can be deallocated. Going
forward in 2005 there is even more reason to separate the filegroups (user
from primary) due to how you deal with partial restores. I have to admit
this is the first time I have seen sysindexes be an issue for shrinking data
files.
--
Andrew J. Kelly SQL MVP
"Paul Cahill" <noname@.noname.com> wrote in message
news:Oa86zOw3GHA.4972@.TK2MSFTNGP03.phx.gbl...
> Hi Andrew.
> Given a values from dbcc ententinfo can this be linked back to the row in
> sysIndexes?
> I.e. there may be a single index I could recreate that would clear my
> block.
> I had never thought about not using primary for user data.
> With hindsight it makes sense. I have not seen any Microsoft documents
> that mention this.
> It would make more sense if SQL Server simply kept it's system tables on a
> private filegroup.
> Paul
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:e1Qn9Uo3GHA.1256@.TK2MSFTNGP04.phx.gbl...
>> Ahh, well then you may have to drop the index itself. updating stats
>> most likely will not do anything since it just updates the row in
>> sysindexes that already exists. It has no need to move it. Where as is
>> you had a lot of stats or indexes that you drop there is a potential for
>> the extent they are on to be deallocated and thus allowing the shrink.
>> --
>> Andrew J. Kelly SQL MVP
>> "Paul Cahill" <anon@.anon.com> wrote in message
>> news:uA2U2kl3GHA.696@.TK2MSFTNGP03.phx.gbl...
>> Hi Andrew
>> How do I drop statistics for indexed columns? The StatBlob values are
>> for indexes not for stats manually created with CREATE STATISTICS.
>> I could rebuild all stats on the database but I do not know if this will
>> move the pages where StatBlob is stored.
>> I tried doing a full sp_updatestats on a test database and the pages did
>> not seem to move for sysIndexes
>> (I use dbcc extentinfo)
>> Regards
>> Paul
>> PS No he tells me
>> PPS Where does it say in the manual "DO NOT USE PRIMARY"
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:eJDoC%23k3GHA.4560@.TK2MSFTNGP05.phx.gbl...
>> You can try dropping all the statistics and see if it frees up that
>> extent. Just make sure you know which stats you may want to put back.
>> By the way this is a good example of one reason why you should keep
>> only the system objects in the Primary filegroup and place the user
>> objects in secondary filegroups.
>> --
>> Andrew J. Kelly SQL MVP
>> "Paul Cahill" <anon@.anon.com> wrote in message
>> news:ubv2hej3GHA.3492@.TK2MSFTNGP06.phx.gbl...
>> SQL Server 2000 SP4
>> Data pages from the image column StatBlob on sysIndexes exist right at
>> the end of my data file. They are stopping me truncating the end of my
>> datafile.
>> I had other tables with text columns. These were moved by either
>> copying the table or bcp/truncate.
>> It there a way to move the data on sysIndexes without doing a
>> "shrinkfile move datapages"?
>> Regards
>> Paul Cahill
>>
>>
>>
>|||Too many text columns. Too many tables. Our system dates back to SQL 6.5.
I noticed SQL 2005 has a text cleanup.
I think the only way out of this for me is a long slow process of moving out
followed by shrinkfile with move data.
Disk space permitting.
It's a big database and it's being logshipped across a wan with litespeed.
Thanks
Paul
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23YJd2Vx3GHA.1588@.TK2MSFTNGP02.phx.gbl...
> To be honest I don't know if it can be linked back or not but it would not
> be a single index as that would only take up one row in sysindexes. You
> would need to clear the entire extent so it can be deallocated. Going
> forward in 2005 there is even more reason to separate the filegroups (user
> from primary) due to how you deal with partial restores. I have to admit
> this is the first time I have seen sysindexes be an issue for shrinking
> data files.
> --
> Andrew J. Kelly SQL MVP
> "Paul Cahill" <noname@.noname.com> wrote in message
> news:Oa86zOw3GHA.4972@.TK2MSFTNGP03.phx.gbl...
>> Hi Andrew.
>> Given a values from dbcc ententinfo can this be linked back to the row in
>> sysIndexes?
>> I.e. there may be a single index I could recreate that would clear my
>> block.
>> I had never thought about not using primary for user data.
>> With hindsight it makes sense. I have not seen any Microsoft documents
>> that mention this.
>> It would make more sense if SQL Server simply kept it's system tables on
>> a private filegroup.
>> Paul
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:e1Qn9Uo3GHA.1256@.TK2MSFTNGP04.phx.gbl...
>> Ahh, well then you may have to drop the index itself. updating stats
>> most likely will not do anything since it just updates the row in
>> sysindexes that already exists. It has no need to move it. Where as is
>> you had a lot of stats or indexes that you drop there is a potential for
>> the extent they are on to be deallocated and thus allowing the shrink.
>> --
>> Andrew J. Kelly SQL MVP
>> "Paul Cahill" <anon@.anon.com> wrote in message
>> news:uA2U2kl3GHA.696@.TK2MSFTNGP03.phx.gbl...
>> Hi Andrew
>> How do I drop statistics for indexed columns? The StatBlob values are
>> for indexes not for stats manually created with CREATE STATISTICS.
>> I could rebuild all stats on the database but I do not know if this
>> will move the pages where StatBlob is stored.
>> I tried doing a full sp_updatestats on a test database and the pages
>> did not seem to move for sysIndexes
>> (I use dbcc extentinfo)
>> Regards
>> Paul
>> PS No he tells me
>> PPS Where does it say in the manual "DO NOT USE PRIMARY"
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:eJDoC%23k3GHA.4560@.TK2MSFTNGP05.phx.gbl...
>> You can try dropping all the statistics and see if it frees up that
>> extent. Just make sure you know which stats you may want to put back.
>> By the way this is a good example of one reason why you should keep
>> only the system objects in the Primary filegroup and place the user
>> objects in secondary filegroups.
>> --
>> Andrew J. Kelly SQL MVP
>> "Paul Cahill" <anon@.anon.com> wrote in message
>> news:ubv2hej3GHA.3492@.TK2MSFTNGP06.phx.gbl...
>> SQL Server 2000 SP4
>> Data pages from the image column StatBlob on sysIndexes exist right
>> at the end of my data file. They are stopping me truncating the end
>> of my datafile.
>> I had other tables with text columns. These were moved by either
>> copying the table or bcp/truncate.
>> It there a way to move the data on sysIndexes without doing a
>> "shrinkfile move datapages"?
>> Regards
>> Paul Cahill
>>
>>
>>
>>
>