bogus Database is Published message prevents design changes

bogus Database is Published message prevents design changes

Post by Steve Brow » Wed, 15 Sep 1999 04:00:00



Hi,
I was wondering if anyone had seen the following: We're running SQL Server
7. A database which used to be published now can't be modified (field type
change or new fields added). We get the message: "Unable to modify table.
ODBC error: [MS][ODBC SQL Server Driver][SQL Server] Cannot drop the table
'dbo.tablename' because it is published for replication."

We've spent about 24 hours trying to turn off anything that remotely smells
of replication or publication to no avail. We've also upgraded to SQL Server
7 SP1.

We also had installed MS Natural Language Query and Crystal Info, but belive
we've turned those off as well.

Any ideas?
Thanks,
Steve Brown
Cognitive Systems Technologies, Inc.

 
 
 

bogus Database is Published message prevents design changes

Post by Jerry Spive » Wed, 15 Sep 1999 04:00:00


Steve - Have you queried the system tables in the database to look for the
published database entry(s) (might try syspublications and others)

Jerry


>Hi,
>I was wondering if anyone had seen the following: We're running SQL Server
>7. A database which used to be published now can't be modified (field type
>change or new fields added). We get the message: "Unable to modify table.
>ODBC error: [MS][ODBC SQL Server Driver][SQL Server] Cannot drop the table
>'dbo.tablename' because it is published for replication."

>We've spent about 24 hours trying to turn off anything that remotely smells
>of replication or publication to no avail. We've also upgraded to SQL
Server
>7 SP1.

>We also had installed MS Natural Language Query and Crystal Info, but
belive
>we've turned those off as well.

>Any ideas?
>Thanks,
>Steve Brown
>Cognitive Systems Technologies, Inc.


 
 
 

bogus Database is Published message prevents design changes

Post by Steve Brow » Wed, 15 Sep 1999 04:00:00


Jerry,
I'm a bit new to digging through the system tables for useful clues, but
there is no syspublications, and a looking through quite a few other tables
didn't show the unchangeable table. Other tables in the same database are
changeable, so this problem seems solvable.
Steve


> Steve - Have you queried the system tables in the database to look for the
> published database entry(s) (might try syspublications and others)

> Jerry

> >Hi,
> >I was wondering if anyone had seen the following: We're running SQL
Server
> >7. A database which used to be published now can't be modified (field
type
> >change or new fields added). We get the message: "Unable to modify table.
> >ODBC error: [MS][ODBC SQL Server Driver][SQL Server] Cannot drop the
table
> >'dbo.tablename' because it is published for replication."

> >We've spent about 24 hours trying to turn off anything that remotely
smells
> >of replication or publication to no avail. We've also upgraded to SQL
> Server
> >7 SP1.

> >We also had installed MS Natural Language Query and Crystal Info, but
> belive
> >we've turned those off as well.

> >Any ideas?
> >Thanks,
> >Steve Brown
> >Cognitive Systems Technologies, Inc.

 
 
 

bogus Database is Published message prevents design changes

Post by Kevin Potte » Wed, 15 Sep 1999 04:00:00


I have same problem but with SQL Server 6.5.  I will tell the steps I talk
to remove rep. from the table.  Maybe you can use some of this for SQL
Server 7.0.

1. To remove rep from the whole DB (SQL6.5):

You run sp (from MS) sp_MSunpublish_db "<Database Name>"
The sp can be found:
http://support.microsoft.com/support/kb/articles/q178/4/44.asp download file
sqlunpub.exe

2. To remove rep from one table I run the following (I got this from MS tech
support a while ago when I had rep. problems)(SQL6.5).

sp_dropsubscription 'publication', 'article', 'subscriber'
go
sp_droparticle publication, article
go
sp_configure "allow", 1
RECONFIGURE with override
go
update sysobjects
 set category = category & ~64
where name = "<Table>"

update sysobjects
 set category = category & ~32
where name = "<Table>"
go
sp_configure "allow", 0
RECONFIGURE with override
go

Hope this help somewhat.

Later,
Kevin Potter (JKP Consultants)


Quote:> Hi,
> I was wondering if anyone had seen the following: We're running SQL Server
> 7. A database which used to be published now can't be modified (field type
> change or new fields added). We get the message: "Unable to modify table.
> ODBC error: [MS][ODBC SQL Server Driver][SQL Server] Cannot drop the table
> 'dbo.tablename' because it is published for replication."

> We've spent about 24 hours trying to turn off anything that remotely
smells
> of replication or publication to no avail. We've also upgraded to SQL
Server
> 7 SP1.

> We also had installed MS Natural Language Query and Crystal Info, but
belive
> we've turned those off as well.

> Any ideas?
> Thanks,
> Steve Brown
> Cognitive Systems Technologies, Inc.

 
 
 

bogus Database is Published message prevents design changes

Post by Steve Brow » Wed, 15 Sep 1999 04:00:00


Kevin,
Thanks kindly. That did the trick! Now that I look back, the table with the
no-edit problem had been exported from a different SQL Server, and had been
published over there. I guess it brought along some hidden baggage.

Regards,
Steve Brown


> I have same problem but with SQL Server 6.5.  I will tell the steps I talk
> to remove rep. from the table.  Maybe you can use some of this for SQL
> Server 7.0.

> 1. To remove rep from the whole DB (SQL6.5):

> You run sp (from MS) sp_MSunpublish_db "<Database Name>"
> The sp can be found:
> http://support.microsoft.com/support/kb/articles/q178/4/44.asp download
file
> sqlunpub.exe

> 2. To remove rep from one table I run the following (I got this from MS
tech
> support a while ago when I had rep. problems)(SQL6.5).

> sp_dropsubscription 'publication', 'article', 'subscriber'
> go
> sp_droparticle publication, article
> go
> sp_configure "allow", 1
> RECONFIGURE with override
> go
> update sysobjects
>  set category = category & ~64
> where name = "<Table>"

> update sysobjects
>  set category = category & ~32
> where name = "<Table>"
> go
> sp_configure "allow", 0
> RECONFIGURE with override
> go

> Hope this help somewhat.

> Later,
> Kevin Potter (JKP Consultants)



> > Hi,
> > I was wondering if anyone had seen the following: We're running SQL
Server
> > 7. A database which used to be published now can't be modified (field
type
> > change or new fields added). We get the message: "Unable to modify
table.
> > ODBC error: [MS][ODBC SQL Server Driver][SQL Server] Cannot drop the
table
> > 'dbo.tablename' because it is published for replication."

> > We've spent about 24 hours trying to turn off anything that remotely
> smells
> > of replication or publication to no avail. We've also upgraded to SQL
> Server
> > 7 SP1.

> > We also had installed MS Natural Language Query and Crystal Info, but
> belive
> > we've turned those off as well.

> > Any ideas?
> > Thanks,
> > Steve Brown
> > Cognitive Systems Technologies, Inc.

 
 
 

1. how should change table design to prevent this bottleneck

hi all,

i have a table, let's say with four fields, namely, a, b, c, d, and my query
uses (c,d) for where clause and (a,b) for sorting matching records.
currently, either where clause is so slow, or sorting is slow, is there a
way to avoid that?

select c, d
    from aTable
where
    a between 10 and 1000
    and
    b between 10 and 1000
order by
    a, b

thanx,
ozz

2. Database inaccesible

3. Mouse Events/Messages - preventing change of focus

4. how to speed up a "select count(*) from ..."

5. I am getting this message when i am tring to export or import anything using

6. SQL 7 wildcard queries 2X slower than 6.5

7. Restoring a published database to a non- published database

8. sp_sendmsg

9. Prevent dbo alias from changing database properties

10. prevent changes to database properties

11. Preventing User Changes to Database Structure

12. Changing the database structure while published

13. Schema changes to published database