Change how command runs

This should be an easy one. I want to change the command to use the
DBID number instead of the database name to run the process. The
current loop does not keep the order if you add or remove databases
beecuse i am using the min() function.
DECLARE @.sql varchar(4000)
DECLARE @.db varchar(64)
SET @.db=''
SELECT @.db=min(name)
FROM master.dbo.sysdatabases
WHERE dbid > 4 and name > @.db
WHILE @.db is not null
BEGIN
SET @.sql='use ' +@.db + ' exec dbo.sp_UD_RebuildIndexes 30, 100'
EXEC (@.sql)
SELECT @.db=min(name)
FROM master.dbo.sysdatabases
WHERE dbid > 4 and name > @.db
END
Thanks
-Matt-not sure what are you exactly looking for.
but if you want to loop through the databases on the basis of DBID rather
than database name , then you can try following procedural code.
DECLARE @.sql varchar(4000)
DECLARE @.db int
SELECT @.db=min(dbid)
FROM master.dbo.sysdatabases
WHERE dbid > 4
WHILE @.db is not null
BEGIN
select @.sql='use ' + name + ' exec dbo.sp_UD_RebuildIndexes 30, 100'
from master..sysdatabases where dbid =@.db
EXEC (@.sql)
SELECT @.db=min(dbid)
FROM master.dbo.sysdatabases
WHERE dbid > @.db
END
"MKruer@.gmail.com" wrote:

> This should be an easy one. I want to change the command to use the
> DBID number instead of the database name to run the process. The
> current loop does not keep the order if you add or remove databases
> beecuse i am using the min() function.
> DECLARE @.sql varchar(4000)
> DECLARE @.db varchar(64)
> SET @.db=''
> SELECT @.db=min(name)
> FROM master.dbo.sysdatabases
> WHERE dbid > 4 and name > @.db
> WHILE @.db is not null
> BEGIN
> SET @.sql='use ' +@.db + ' exec dbo.sp_UD_RebuildIndexes 30, 100'
> EXEC (@.sql)
> SELECT @.db=min(name)
> FROM master.dbo.sysdatabases
> WHERE dbid > 4 and name > @.db
> END
> Thanks
> -Matt-
>|||That was it. The Query is to run a stored procedure that auto reindex
the entire Database. The only problem with the stored procedure is that
it only works on the database that it was executed on. This is more or
a temporary work around. Ideally I want to be able to specify
individual indexes directly using a table.
Creating a Stored Procedure to collect DB information and then run
another Stored Procedure using that information
http://groups.google.com/group/micr...bdb43d49fed239e
Query for Database, Table, Index name all on one list.
http://groups.google.com/group/micr...17098a35404820c|||That was it. The Query is to run a stored procedure that auto reindex
the entire Database. The only problem with the stored procedure is that
it only works on the database that it was executed on. This is more or
a temporary work around. Ideally I want to be able to specify
individual indexes directly using a table.
Creating a Stored Procedure to collect DB information and then run
another Stored Procedure using that information
http://groups.google.com/group/micr...bdb43d49fed239e
Query for Database, Table, Index name all on one list.
http://groups.google.com/group/micr...17098a35404820csql

Caught in a Replication Catch-22, SQLServer2005

I am currently moving from SQL2K to 2005 and am having trouble incorporating
replication in 2005.
First off, I've found myself in a loop where I have two publications that
"don't exist" even though they show up in my Local Publishers list. I can't
delete tables that are articles in those publications because the tables are
being replicated. However, if I try to delete the publication, I get the
message that the publication doesn't exist. So the tables I need to drop are
bound to a publication that doesn't exist.
I've tried looking in the distribution and master databases to find the
missing links but I can't find anything. Without spending a lot of time
going through how I got in this mess, I'd just like to know if there are some
sp's that I can use to purge these publications once and for all.
Thanks in advance.
Roger.
You don't say which type of replication you are using. My experience
is only with merge replication, in this case I would suggest using
sp_removedbreplication in addition to normal sp_dropmergepublication
(or delete from the Gui)
Please see KB324401 for recommendations from MS. http://
support.microsoft.com/kb/324401
Tim Hill
On Feb 9, 3:33 pm, Roger Denison
<RogerDeni...@.discussions.microsoft.com> wrote:
> I am currently moving from SQL2K to 2005 and am having trouble incorporating
> replication in 2005.
> First off, I've found myself in a loop where I have two publications that
> "don't exist" even though they show up in my Local Publishers list. I can't
> delete tables that are articles in those publications because the tables are
> being replicated. However, if I try to delete the publication, I get the
> message that the publication doesn't exist. So the tables I need to drop are
> bound to a publication that doesn't exist.
> I've tried looking in the distribution and master databases to find the
> missing links but I can't find anything. Without spending a lot of time
> going through how I got in this mess, I'd just like to know if there are some
> sp's that I can use to purge these publications once and for all.
> Thanks in advance.
> --
> Roger.
|||I had a similar problem to this. The only way round it was to script
the publications & subscriptions, remove replication and then
re-enable.
On Fri, 9 Feb 2007 07:33:00 -0800, Roger Denison
<RogerDenison@.discussions.microsoft.com> wrote:

>I am currently moving from SQL2K to 2005 and am having trouble incorporating
>replication in 2005.
>First off, I've found myself in a loop where I have two publications that
>"don't exist" even though they show up in my Local Publishers list. I can't
>delete tables that are articles in those publications because the tables are
>being replicated. However, if I try to delete the publication, I get the
>message that the publication doesn't exist. So the tables I need to drop are
>bound to a publication that doesn't exist.
>I've tried looking in the distribution and master databases to find the
>missing links but I can't find anything. Without spending a lot of time
>going through how I got in this mess, I'd just like to know if there are some
>sp's that I can use to purge these publications once and for all.
>Thanks in advance.