Replication from SQL 7 to SQL 6.5

[David Lu]

For certain reason, I need to replicate from SQL 7 to SQL 6.5.

On Microsoft Support KB, I found an article about <<How to create a push subsription from a SQL 6.5 to SQL 7.0 subscriber (Enterprise Manager)>>.
(I tried and get &#34;Error 5: Code execution exception: Exception_Access_Violation&#34;. Though it did not work, I still think there should have a way to replicate from SQL 7 to SQL 6.5.

I tried Microsoft support/kb with no clear answer.
I tried a coulpe of times in SQL 7 Enterprise Manager Console but with error about &#34;connection could not establish (to SQL 6.5 server), please upgrade to SQL 7.0&#34;. I also had tried to install the SQL 6.5 sp5a on the target subscriber SQL 6.5 server, but still could not establish the connection in Enterprise Manager level.

However, from isql/w, I could connect and query from SQL 7 to SQL 6.5 and also to query from SQL 6.5(with sql sp3 only) to query SQL 7. It is really confusion.

Thanks for your help.
David

[Jeremy Vance]

Have you enabled the SQL 7.0 database as a remote server in the SQL 6.5 system? There is a script which loads a stored procedure (located in your SQL 7.0 directory <drive>:mssqlininstall
epl70.sql or something like that).

Run this script on your 6.5 machine. Then you will need to execute the stored procedure it has created to register the 7.0 server.

This is covered in the SQL 7.0 Books Online on replicating data to heterogenous subscribers -- pushing data to a 6.5 system.

Hope it helps ...


------------
David Lu at 11/18/99 3:42:58 PM

For certain reason, I need to replicate from SQL 7 to SQL 6.5.

On Microsoft Support KB, I found an article about <<How to create a push subsription from a SQL 6.5 to SQL 7.0 subscriber (Enterprise Manager)>>.
(I tried and get &#34;Error 5: Code execution exception: Exception_Access_Violation&#34;. Though it did not work, I still think there should have a way to replicate from SQL 7 to SQL 6.5.

I tried Microsoft support/kb with no clear answer.
I tried a coulpe of times in SQL 7 Enterprise Manager Console but with error about &#34;connection could not establish (to SQL 6.5 server), please upgrade to SQL 7.0&#34;. I also had tried to install the SQL 6.5 sp5a on the target subscriber SQL 6.5 server, but still could not establish the connection in Enterprise Manager level.

However, from isql/w, I could connect and query from SQL 7 to SQL 6.5 and also to query from SQL 6.5(with sql sp3 only) to query SQL 7. It is really confusion.

Thanks for your help.
David

[David Lu]

Hi Jeremy:

Thank you very much for your response. Here are what I did based on your tips:
1. found replp70.sql and run it in the SQL6.5 master database
results: Configuration option changed. Run the RECONFIGURE command to install.
Creating stored procedure sp_addpublisher70.
Creating special 6.x version of stored procedure sp_table_validation.
2. exec sp_addpublisher70 &#39;pubserverName&#39;, &#39;domainName/Administrator&#39;
(use SQLAgentService&#39;s same domainAccount)
3. exec sp_dboption dbName, &#39;subscribed&#39;, true

At the SQL 7 side, (whether I pick &#39;All SQL Server&#39; or &#39;heterogenous&#39; for the subscriber (SQL 6.5)), after successful creation of the &#39;push to new subcription&#39;, the Distribution Agent job failed with red sign and error:
Publication: <Multiple Publications>
Last Action: The process could not connect to Subscriber &#39;SFSIMONDB2&#39;.
Error Msg: Login failed- User: sa Reason: Not associated with a trusted SQL Server connection.

Both SQL 7 and SQL 6.5 are within the same domain. And I also upgrade the SQL 6.5 from standard security mode to mixed mode.
It looks like that I have a new problem on &#39;trusted SQL server connection&#39;.

Thanks for take your time
David


------------
Jeremy Vance at 11/18/99 4:08:55 PM

Have you enabled the SQL 7.0 database as a remote server in the SQL 6.5 system? There is a script which loads a stored procedure (located in your SQL 7.0 directory <drive>:mssqlininstall
epl70.sql or something like that).

Run this script on your 6.5 machine. Then you will need to execute the stored procedure it has created to register the 7.0 server.

This is covered in the SQL 7.0 Books Online on replicating data to heterogenous subscribers -- pushing data to a 6.5 system.

Hope it helps ...


------------
David Lu at 11/18/99 3:42:58 PM

For certain reason, I need to replicate from SQL 7 to SQL 6.5.

On Microsoft Support KB, I found an article about <<How to create a push subsription from a SQL 6.5 to SQL 7.0 subscriber (Enterprise Manager)>>.
(I tried and get &#34;Error 5: Code execution exception: Exception_Access_Violation&#34;. Though it did not work, I still think there should have a way to replicate from SQL 7 to SQL 6.5.

I tried Microsoft support/kb with no clear answer.
I tried a coulpe of times in SQL 7 Enterprise Manager Console but with error about &#34;connection could not establish (to SQL 6.5 server), please upgrade to SQL 7.0&#34;. I also had tried to install the SQL 6.5 sp5a on the target subscriber SQL 6.5 server, but still could not establish the connection in Enterprise Manager level.

However, from isql/w, I could connect and query from SQL 7 to SQL 6.5 and also to query from SQL 6.5(with sql sp3 only) to query SQL 7. It is really confusion.

Thanks for your help.
David

[Jeremy Vance]

David:

Check the version numbers for the connection protocols dll&#39;s located in your c:mssqlin directory of the 6.5 machine. These same dll&#39;s are located in your winnt/system32 directory; however, applications sometimes use the onces in your mssql directories. Make sure the dlls are marked for a date later than nov 98. There is an article about this on http://search.microsoft.com on replication and trusted connections.

Make sure your default protocols you are communicating with are either named pipes or multi-protocol. These are the only two to support trusted connections.

Jeremy



------------
David Lu at 11/18/99 7:47:33 PM

Hi Jeremy:

Thank you very much for your response. Here are what I did based on your tips:
1. found replp70.sql and run it in the SQL6.5 master database
results: Configuration option changed. Run the RECONFIGURE command to install.
Creating stored procedure sp_addpublisher70.
Creating special 6.x version of stored procedure sp_table_validation.
2. exec sp_addpublisher70 &#39;pubserverName&#39;, &#39;domainName/Administrator&#39;
(use SQLAgentService&#39;s same domainAccount)
3. exec sp_dboption dbName, &#39;subscribed&#39;, true

At the SQL 7 side, (whether I pick &#39;All SQL Server&#39; or &#39;heterogenous&#39; for the subscriber (SQL 6.5)), after successful creation of the &#39;push to new subcription&#39;, the Distribution Agent job failed with red sign and error:
Publication: <Multiple Publications>
Last Action: The process could not connect to Subscriber &#39;SFSIMONDB2&#39;.
Error Msg: Login failed- User: sa Reason: Not associated with a trusted SQL Server connection.

Both SQL 7 and SQL 6.5 are within the same domain. And I also upgrade the SQL 6.5 from standard security mode to mixed mode.
It looks like that I have a new problem on &#39;trusted SQL server connection&#39;.

Thanks for take your time
David


------------
Jeremy Vance at 11/18/99 4:08:55 PM

Have you enabled the SQL 7.0 database as a remote server in the SQL 6.5 system? There is a script which loads a stored procedure (located in your SQL 7.0 directory <drive>:mssqlininstall
epl70.sql or something like that).

Run this script on your 6.5 machine. Then you will need to execute the stored procedure it has created to register the 7.0 server.

This is covered in the SQL 7.0 Books Online on replicating data to heterogenous subscribers -- pushing data to a 6.5 system.

Hope it helps ...


------------
David Lu at 11/18/99 3:42:58 PM

For certain reason, I need to replicate from SQL 7 to SQL 6.5.

On Microsoft Support KB, I found an article about <<How to create a push subsription from a SQL 6.5 to SQL 7.0 subscriber (Enterprise Manager)>>.
(I tried and get &#34;Error 5: Code execution exception: Exception_Access_Violation&#34;. Though it did not work, I still think there should have a way to replicate from SQL 7 to SQL 6.5.

I tried Microsoft support/kb with no clear answer.
I tried a coulpe of times in SQL 7 Enterprise Manager Console but with error about &#34;connection could not establish (to SQL 6.5 server), please upgrade to SQL 7.0&#34;. I also had tried to install the SQL 6.5 sp5a on the target subscriber SQL 6.5 server, but still could not establish the connection in Enterprise Manager level.

However, from isql/w, I could connect and query from SQL 7 to SQL 6.5 and also to query from SQL 6.5(with sql sp3 only) to query SQL 7. It is really confusion.

Thanks for your help.
David