32-bit SQL 2000 publisher and SQL 2005 64-bit SQL distributor and Subscriber

For transactional replication, are there any issues and is it even possible to have 32-bit SQL 2000 publisher and SQL 2005 64-bit distributor and subscriber? Thanks

This is a supported case and should work fine. Remember you have to set everything up in SQL Server Management Studio 2005 as you can not connect to SQL 2005 from SQL 2000 Enterprise Manager. Alternatively, you can use TSQL script to setup replication.

Hope that helps,

Zhiqiang Feng

This posting is provided "AS IS" with no warranties, and confers no rights.

2005 Replication with SCHEMABINDING Subscriber Views

Hi Folks

Is there an easy way around this ?

One Way Transactional Rep Subscriber needs SCHEMABINDING on the majority of their Views (require View Indexes) which read from Replicated Tables.

Main table has 4 Million Rows

ReInitialize Subscription Errors with Cannot Drop Table because it is being referenced By Object .... [schemaBound View]

GWmmmm - not much interest in this thread :eek:

well for info I'm trying to backup any schemabound objects with the following code (Showing the OBJECT_DEFINITION Usage).


ALTER PROCEDURE [dbo].[usp_SchemaBoundObjects_Backup]
AS
SET NOCOUNT ON
IF(SELECT OBJECT_ID('SchemaBoundObjects')) IS NULL
BEGIN
CREATE TABLE SchemaBoundObjects
(ObjName SysName,ObjType VarChar(50),ObjText nVarChar(4000))
END
ELSE
BEGIN
TRUNCATE TABLE SchemaBoundObjects
END
INSERT INTO SchemaBoundObjects
SELECT DISTINCT OBJECT_NAME(o.object_id) ObjName
,o.type_desc ObjType
,OBJECT_DEFINITION (o.object_id) ObjText
FROM sys.sql_dependenciesAS d
JOIN sys.objectsAS o
ON d.object_id= o.object_id
AND o.type IN('FN','IF','TF','V','P')
WHERE d.class = 1
INSERT INTO SchemaBoundObjects

-- Does'nt work
SELECT si.name,'INDEX',OBJECT_DEFINITION (OBJECT_ID(si.name)) ObjText
FROM sys.indexes si
JOIN sys.views sv
ON sv.object_id= si.object_id
WHERE sv.name IN (SELECT ObjName FROM SchemaBoundObjects)


Programatically ReInitialize Subscription, spike the Distributor - Then Recreate the Objects (which I have the Code for).

Now if I can just sort out the sodding View Indexes which don't work Grrr :(

C'mon folks Join in anytime :p

GW

2005 replication subscriber can't see 2000 publisher - why?

I enabled publishing on the SQL 2000 box so that I can do replication from
2000 to 2005. During the wizard setup, the 2005 subscriber gets this error:
---
Cannot connect to Server1. Additional information: Failed to connection to
server Server1 (Microsoft.SqlServer.ConnectionInfo) An error has occurred
while establishing a connection tot he server. When connecting to SQL
Server 2005, this failure may be caused by the fact that under the default
settings SQL Server does not allow remote connections. (provider: Named
Pipes Provider, error 40 - Could not open a connection to SQL Server)
(Microsoft SQL Server, Error: 52).
---
Both boxes are on the same LAN subnet with no firewall between them. I
added an entry to my HOSTS file on the 2005 server so it can see the 2000
server by name (the wizard won't let you type in an IP address). Pinging
that machine works fine.
I don't know why this happens. I've tried windows auth and SQL auth.
What's the trick? Is 2005 using some new communications method that 2000
doesn't understand? I don't even know of a log file that would help but
maybe someone knows the answer or where there is some log info.Open the Surface Area Configuration on the 2005 machine and enable remote
connections. By default a newly installed SQL Server 2005 instance will
only respond to requests sent from the physical machine it was installed on.
--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"HK" <replywithingroup@.notreal.com> wrote in message
news:ZSbKf.13005$Ou1.8109@.tornado.socal.rr.com...
>I enabled publishing on the SQL 2000 box so that I can do replication from
> 2000 to 2005. During the wizard setup, the 2005 subscriber gets this
> error:
> ---
> Cannot connect to Server1. Additional information: Failed to connection
> to
> server Server1 (Microsoft.SqlServer.ConnectionInfo) An error has occurred
> while establishing a connection tot he server. When connecting to SQL
> Server 2005, this failure may be caused by the fact that under the default
> settings SQL Server does not allow remote connections. (provider: Named
> Pipes Provider, error 40 - Could not open a connection to SQL Server)
> (Microsoft SQL Server, Error: 52).
> ---
> Both boxes are on the same LAN subnet with no firewall between them. I
> added an entry to my HOSTS file on the 2005 server so it can see the 2000
> server by name (the wizard won't let you type in an IP address). Pinging
> that machine works fine.
> I don't know why this happens. I've tried windows auth and SQL auth.
> What's the trick? Is 2005 using some new communications method that 2000
> doesn't understand? I don't even know of a log file that would help but
> maybe someone knows the answer or where there is some log info.
>|||On the subscriber machine? That sounds like something I would do on the
publisher but the publisher is SQL 2000.
"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:uHHA4ZpNGHA.3460@.TK2MSFTNGP15.phx.gbl...
> Open the Surface Area Configuration on the 2005 machine and enable remote
> connections. By default a newly installed SQL Server 2005 instance will
> only respond to requests sent from the physical machine it was installed
on.
> --
> Mike
> http://www.solidqualitylearning.com
> Disclaimer: This communication is an original work and represents my sole
> views on the subject. It does not represent the views of any other person
> or entity either by inference or direct reference.
>
> "HK" <replywithingroup@.notreal.com> wrote in message
> news:ZSbKf.13005$Ou1.8109@.tornado.socal.rr.com...
> >I enabled publishing on the SQL 2000 box so that I can do replication
from
> > 2000 to 2005. During the wizard setup, the 2005 subscriber gets this
> > error:
> >
> > ---
> > Cannot connect to Server1. Additional information: Failed to
connection
> > to
> > server Server1 (Microsoft.SqlServer.ConnectionInfo) An error has
occurred
> > while establishing a connection tot he server. When connecting to SQL
> > Server 2005, this failure may be caused by the fact that under the
default
> > settings SQL Server does not allow remote connections. (provider:
Named
> > Pipes Provider, error 40 - Could not open a connection to SQL Server)
> > (Microsoft SQL Server, Error: 52).
> > ---
> >
> > Both boxes are on the same LAN subnet with no firewall between them. I
> > added an entry to my HOSTS file on the 2005 server so it can see the
2000
> > server by name (the wizard won't let you type in an IP address).
Pinging
> > that machine works fine.
> >
> > I don't know why this happens. I've tried windows auth and SQL auth.
> > What's the trick? Is 2005 using some new communications method that
2000
> > doesn't understand? I don't even know of a log file that would help but
> > maybe someone knows the answer or where there is some log info.
> >
> >
>|||The setting Mike told you about is to allow the remote machine (publisher)
to connect to the local (Subscriber - 2005) machine. It is from the
viewpoint of the 2005 machine.
--
Andrew J. Kelly SQL MVP
"HK" <replywithingroup@.notreal.com> wrote in message
news:J9HKf.8983$Jg.197@.tornado.socal.rr.com...
> On the subscriber machine? That sounds like something I would do on the
> publisher but the publisher is SQL 2000.
> "Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
> news:uHHA4ZpNGHA.3460@.TK2MSFTNGP15.phx.gbl...
>> Open the Surface Area Configuration on the 2005 machine and enable remote
>> connections. By default a newly installed SQL Server 2005 instance will
>> only respond to requests sent from the physical machine it was installed
> on.
>> --
>> Mike
>> http://www.solidqualitylearning.com
>> Disclaimer: This communication is an original work and represents my sole
>> views on the subject. It does not represent the views of any other
>> person
>> or entity either by inference or direct reference.
>>
>> "HK" <replywithingroup@.notreal.com> wrote in message
>> news:ZSbKf.13005$Ou1.8109@.tornado.socal.rr.com...
>> >I enabled publishing on the SQL 2000 box so that I can do replication
> from
>> > 2000 to 2005. During the wizard setup, the 2005 subscriber gets this
>> > error:
>> >
>> > ---
>> > Cannot connect to Server1. Additional information: Failed to
> connection
>> > to
>> > server Server1 (Microsoft.SqlServer.ConnectionInfo) An error has
> occurred
>> > while establishing a connection tot he server. When connecting to SQL
>> > Server 2005, this failure may be caused by the fact that under the
> default
>> > settings SQL Server does not allow remote connections. (provider:
> Named
>> > Pipes Provider, error 40 - Could not open a connection to SQL Server)
>> > (Microsoft SQL Server, Error: 52).
>> > ---
>> >
>> > Both boxes are on the same LAN subnet with no firewall between them. I
>> > added an entry to my HOSTS file on the 2005 server so it can see the
> 2000
>> > server by name (the wizard won't let you type in an IP address).
> Pinging
>> > that machine works fine.
>> >
>> > I don't know why this happens. I've tried windows auth and SQL auth.
>> > What's the trick? Is 2005 using some new communications method that
> 2000
>> > doesn't understand? I don't even know of a log file that would help
>> > but
>> > maybe someone knows the answer or where there is some log info.
>> >
>> >
>>
>