can sqlsvr2005 replicate tables to sqlsvr2000?

Hello,
I have replication in place between 2 sqlsvr2000 DBs. Everything is working
OK. I am looking to upgrade the publishing sqlsvr from sqlsvr2000 to
sqlsvr2005. The subscription sever will remain as a sqlsvr2000 server
because I don't own that one. If I were to go ahead an perform the upgrade
on my end, will I still be able to replicate my table to the sqlsvr2000
subscriber?
Thanks,
Rich
Absolutely. The only caveat is that if you are using merge replication the
snapshot will have to be sent again.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:DEE16A4E-CCF9-4D6A-9A4C-4F4A69FDBD24@.microsoft.com...
> Hello,
> I have replication in place between 2 sqlsvr2000 DBs. Everything is
> working
> OK. I am looking to upgrade the publishing sqlsvr from sqlsvr2000 to
> sqlsvr2005. The subscription sever will remain as a sqlsvr2000 server
> because I don't own that one. If I were to go ahead an perform the
> upgrade
> on my end, will I still be able to replicate my table to the sqlsvr2000
> subscriber?
> Thanks,
> Rich
|||Thanks. The folks over my way are a little reluctant to move forward with
the upgrade - but hey! We have an MSDN subscription and I already downloaded
all of sqlsrv2005 enterprise. They were trying to use the Replication angle.
Glad that won't be an issue.
Rich
"Hilary Cotter" wrote:

> Absolutely. The only caveat is that if you are using merge replication the
> snapshot will have to be sent again.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:DEE16A4E-CCF9-4D6A-9A4C-4F4A69FDBD24@.microsoft.com...
>
>

can not remove rowguid currently replicated

After losing my publishing server, I re-built on a new server.
The databases had replication remnants so I ran cleanup scripts
(Replication system Objects, indexes and rowguids)
I then re-built replication
I find that I have a column (rowguid) left over that I can not remove.
The error message is "Can not Alter table, ... rowguid currently replicated"
how do I remove this column?
Haven't seen this error personally, but please have a look at the solutions
proposed in this thread for some ideas to take advantage of:
http://www.webservertalk.com/showthread.php?t=902849
HTH,
Paul Ibison
|||Hi Paul;
This is what I have done so far
Problem:
Open EM
Open Agency Table
Delete Column
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]
ALTER TABLE DROP COLUMN failed because 'rowguid' is currently replicated.
Approaches:
Drop replicated column - sp_repldropcolumn
Drop subscription - sp_dropsubscription
drop publication - sp_droppublication
disable publishing and distributor - sp_removedbreplication
exec sp_repldropcolumn @.source_object = 'Agency'
, @.column = 'rowguid'
, @.force_reinit_subscription = 1
Server: Msg 21246, Level 16, State 1, Procedure sp_repldropcolumn, Line 212
This step failed because table 'Agency' is not part of any publication.
sp_dropsubscription @.publication = 'IsoprepArchive'
, @.subscriber = 'SQLDEV'
, @.destination_db = 'Archivedisopreps'
sp_droppublication @.publication = 'IsoprepArchive'
exec sp_removedbreplication 'Isoprep'
-- Unmark table for replication
SELECT 'exec sp_MSUnmarkReplInfo ' + '''' + Name + '''' + Char(13)
+ ' GO ' + Char(13)
+ 'ALTER TABLE ' + Name + CHAR(13)
+ 'DROP CONSTRAINT DF_' + Name + '_rowguid' + Char(13)
+ ' GO ' + CHAR(13)
+ 'ALTER TABLE ' + Name + CHAR(13)
+ 'DROP COLUMN ROWGUID' + Char(13)
+ ' GO ' + Char(13) + Char(13)
FROM sysobjects
WHERE xtype = 'U'
-- return replinfo flag
SELECT 'Print ' + '''' + Name + '''' + Char(13) + ' GO '
+ Char(13)
+ 'SELECT replinfo' + Char(13)
+ 'FROM sysobjects' + Char(13)
+ ' WHERE name = ' + '''' + name + '''' + Char(13)
FROM sysobjects
WHERE xtype = 'U'

exec sp_MSUnmarkReplInfo 'ErrorLog'
GO
ALTER TABLE ErrorLog
DROP CONSTRAINT DF_ErrorLog_rowguid
GO
ALTER TABLE ErrorLog
DROP COLUMN ROWGUID
GO
Warning: The table 'ErrorLog' has been created but its maximum row size
(9416)
exceeds the maximum number of bytes per row (8060).
INSERT or UPDATE of a row in this table will fail if the resulting row
length exceeds 8060 bytes.
Server: Msg 4932, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN failed because 'ROWGUID' is currently replicated.
Warning: The table 'ErrorLog' has been created but its maximum row size
(9416)
exceeds the maximum number of bytes per row (8060).
INSERT or UPDATE of a row in this table will fail if the resulting row
length exceeds 8060 bytes.
Thanks
MJS
"Paul Ibison" wrote:

> Haven't seen this error personally, but please have a look at the solutions
> proposed in this thread for some ideas to take advantage of:
> http://www.webservertalk.com/showthread.php?t=902849
> HTH,
> Paul Ibison
>
|||Quite By Accident, I found the following:
open EM
Open Design view of table
open constraint tab
Look for replication constraints
I found a constraint that looked like:
"repl_identity_range_sub_B0BB3703_3C1D_4648_9DCA_B F47DE69E485"
execute the following to build an alter table statement that will
remove the constraints.
SELECT 'ALTER TABLE ' + U.name + CHAR(13)
+ 'DROP CONSTRAINT ' + C.name + CHAR(13)
+ CHAR(13) + 'GO' + CHAR(13)
FROM sysobjects C
, sysobjects U
WHERE C.parent_Obj = U.Id
AND C.xtype = 'C'
AND C.name like 'repl_identity_range_sub_%'
Thanks
MJ
"mj" wrote:
[vbcol=seagreen]
> Hi Paul;
> This is what I have done so far
> Problem:
> Open EM
> Open Agency Table
> Delete Column
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]
> ALTER TABLE DROP COLUMN failed because 'rowguid' is currently replicated.
> Approaches:
> Drop replicated column - sp_repldropcolumn
> Drop subscription - sp_dropsubscription
> drop publication - sp_droppublication
> disable publishing and distributor - sp_removedbreplication
>
> exec sp_repldropcolumn @.source_object = 'Agency'
> , @.column = 'rowguid'
> , @.force_reinit_subscription = 1
> Server: Msg 21246, Level 16, State 1, Procedure sp_repldropcolumn, Line 212
> This step failed because table 'Agency' is not part of any publication.
> sp_dropsubscription @.publication = 'IsoprepArchive'
> , @.subscriber = 'SQLDEV'
> , @.destination_db = 'Archivedisopreps'
> sp_droppublication @.publication = 'IsoprepArchive'
> exec sp_removedbreplication 'Isoprep'
> -- Unmark table for replication
> SELECT 'exec sp_MSUnmarkReplInfo ' + '''' + Name + '''' + Char(13)
> + ' GO ' + Char(13)
> + 'ALTER TABLE ' + Name + CHAR(13)
> + 'DROP CONSTRAINT DF_' + Name + '_rowguid' + Char(13)
> + ' GO ' + CHAR(13)
> + 'ALTER TABLE ' + Name + CHAR(13)
> + 'DROP COLUMN ROWGUID' + Char(13)
> + ' GO ' + Char(13) + Char(13)
> FROM sysobjects
> WHERE xtype = 'U'
> -- return replinfo flag
> SELECT 'Print ' + '''' + Name + '''' + Char(13) + ' GO '
> + Char(13)
> + 'SELECT replinfo' + Char(13)
> + 'FROM sysobjects' + Char(13)
> + ' WHERE name = ' + '''' + name + '''' + Char(13)
> FROM sysobjects
> WHERE xtype = 'U'
>
> ----
> exec sp_MSUnmarkReplInfo 'ErrorLog'
> GO
> ALTER TABLE ErrorLog
> DROP CONSTRAINT DF_ErrorLog_rowguid
> GO
> ALTER TABLE ErrorLog
> DROP COLUMN ROWGUID
> GO
> Warning: The table 'ErrorLog' has been created but its maximum row size
> (9416)
> exceeds the maximum number of bytes per row (8060).
> INSERT or UPDATE of a row in this table will fail if the resulting row
> length exceeds 8060 bytes.
> Server: Msg 4932, Level 16, State 1, Line 1
> ALTER TABLE DROP COLUMN failed because 'ROWGUID' is currently replicated.
> Warning: The table 'ErrorLog' has been created but its maximum row size
> (9416)
> exceeds the maximum number of bytes per row (8060).
> INSERT or UPDATE of a row in this table will fail if the resulting row
> length exceeds 8060 bytes.
>
> Thanks
> MJS
> ----
> "Paul Ibison" wrote: