Active Directory has killed Replication...

Hi folks,
We've just upgraded our SQL servers to active directory. Have deleted all
of the existing jobs and replications before upgrading, as one is supposed
to do, but now when I create a new replication it just fails, saying it
can't find the distribution server specified.
The snapshot is failing at the Run agent step with "The process could not
create file '\\DIVCS-SQL1\F$\Replication\unc'. The step failed.", where
DIVCS-SQL1 is the old name of the server.
When I go to the configuration screen for the replication agent it shows the
old server name as the Distribution Server name (DIVCS-SQL1)
I assume that there is a registry item that points SQL server to the right
agent, but I don't want to start playing with it without some advice...
Thanks in advance...
Paul
Paul,
Try:
Use Master
go
Select @.@.Servername
This should return your current server name but if it
returns NULL or the oldname then try:
Use Master
go
Sp_DropServer 'Server1'
GO
Use Master
go
Sp_Addserver 'Server1', 'local'
GO
Stop and Start SQL Services
NB: You can use the @.@.SERVERNAME global variable or the SERVERPROPERTY('ServerName') function in SQL Server to find the network name of the computer running SQL Server. The ServerName property of the SERVERPROPERTY function automatically reports the chang
e in the network name of the computer when you restart the computer and the SQL Server service. The @.@.SERVERNAME global variable retains the original SQL Server computer name until the SQL Server name is manually reset.
HTH,
Paul Ibison
|||Hi,
Just tried sp_dropserver and got the following error message :
"Server: Msg 20582, Level 16, State 1, Procedure sp_MSrepl_check_server,
Line 39
Cannot drop server 'DIVCS-SQL1' because it is used as a Publisher in
replication."
I tried the 'droplogins' parameter as well, but to no avail.
Any ideas?
Cheers...P
"Paul Ibison" <Paul.Ibison@.Pygmalion.com> wrote in message
news:84A80174-EB3D-4414-9288-C84A01B6C72E@.microsoft.com...
> Paul,
> Try:
> Use Master
> go
> Select @.@.Servername
> This should return your current server name but if it
> returns NULL or the oldname then try:
> Use Master
> go
> Sp_DropServer 'Server1'
> GO
> Use Master
> go
> Sp_Addserver 'Server1', 'local'
> GO
> Stop and Start SQL Services
> NB: You can use the @.@.SERVERNAME global variable or the
SERVERPROPERTY('ServerName') function in SQL Server to find the network name
of the computer running SQL Server. The ServerName property of the
SERVERPROPERTY function automatically reports the change in the network name
of the computer when you restart the computer and the SQL Server service.
The @.@.SERVERNAME global variable retains the original SQL Server computer
name until the SQL Server name is manually reset.
> HTH,
> Paul Ibison
>
|||Paul,
am I right in saying that
Select @.@.Servername
Select SERVERPROPERTY('ServerName')
return different values?
This is the case I mentioned when you need to update the value in sysservers.
If this is so, then it means that you have set up replication on a renamed server, or you have set up replication then renamed the server. The simplest method to solve the issue is to drop the publications, disable replication, update sysservers using the
script in my previous post, then recreate the publications.
HTH,
Paul Ibison
|||Phew...Got it working.
Thanks for your help...I kind of got it...eventually.
P
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:016D7C4C-5BAF-416F-87E6-0184DE7FD002@.microsoft.com...
> Paul,
> am I right in saying that
> Select @.@.Servername
> Select SERVERPROPERTY('ServerName')
> return different values?
> This is the case I mentioned when you need to update the value in
sysservers.
> If this is so, then it means that you have set up replication on a renamed
server, or you have set up replication then renamed the server. The simplest
method to solve the issue is to drop the publications, disable replication,
update sysservers using the script in my previous post, then recreate the
publications.
> HTH,
> Paul Ibison

Accessing publication properties from Visual Basic (VB6)

Hi all,

I have an existing application that checks a few things before it
launches another application. One thing that the application needs to
check is the subscription properties of both the local database and the
remote (LAN) server.

I've stumbled upon some properties but I am trying to fit in the last
ones and I can't seem to find the right code to accomplish this... Can
anyone shine a little light on this please ?

The following code queries the SQL Server (remote) to get (some of) the
properties of the replicated database. What I would like to fit in
after the 'state' are the properties that you can retrieve with the
EnumAllSubscriptions function. However, I can't seem to find the right
object (sample code) to retrieve those (or I'm just overlooking the
obvious)...

Here is (part of) the code I'm using to query the publication on the
remote server :

Dim oMergePublication As SQLDMO.MergePublication2
Dim oSubscription As SQLDMO.MergeSubscription2

Me.lstPublications.Clear
For Each oReplicationDatabase In
objSQLSERVER.Replication.ReplicationDatabases
If oReplicationDatabase.Name = Me.txtSQLServerDatabase.Text
Then
For Each oMergePublication In
oReplicationDatabase.MergePublications
bFoundReplicated = True
Me.lstPublications.AddItem "Database replicated as
'" & oMergePublication.Name & "'"
For Each oSubscription In
oMergePublication.MergeSubscriptions
Me.lstPublications.AddItem " - subscriber
:" & oSubscription.Subscriber
Me.lstPublications.AddItem " - local
DB :" & oSubscription.SubscriptionDB
Me.lstPublications.AddItem " -
state :" & GetReplStatus(oSubscription.Status)
Next
Next
'For Each osubscriber In oReplicationDatabase.
'Next
End If
Next
Set oReplicationDatabase = Nothing
Set oMergePublication = Nothing

After this, I also want to check the local DB (=pulled subscription)
and show some of the properties (from EnumAllSubscriptions). For
example to warn the user about the need to synchronise (last_updated
value)...

Any help appreciated,

Many thanks

GB

--
Your eyes are weary from staring at the CRT. You feel sleepy. Notice
how restful it is to watch the cursor blink. Close your eyes. The
opinions stated above are yours. When I snap my fingers, you cannot
imagine why you ever felt otherwise. <snapI'm not sure what you're asking - do you mean that you know that
EnumAllScubscriptions will return the information you need, but you
don't know how to manipulate a SQL-DMO QueryResults object? If so, then
you need to iterate over it, and use the GetColumn% methods to return
the data in the format you want - in this case, GetColumnString is
probably the best one to use.

This is a simple VBScript example of using a QueryResults object:

Option Explicit

Dim oSrv, res, c, r

Set oSrv = WScript.CreateObject("SQLDMO.SqlServer2")
oSrv.LoginSecure = True
oSrv.Name = "MyServer"
oSrv.Connect

Set res = oSrv.ExecuteWithResults("select * from sysobjects")

For r = 1 To res.Rows
For c = 1 To res.Columns
If res.ColumnName(c) = "name" Then
WScript.Echo res.GetColumnString(r, c)
End If
Next
Next

If this isn't helpful, you might get a better answer in
microsoft.public.sqlserver.replication.

Simon|||Sort of Simon,

I'm having difficulty to get the returnset from EnumAllSubscriptions in
a workable object. It just returns nothing or won't compile.
In the code I've added, I managed to get the right call for the
EnumAllSubscriptions but I'm not sure on the syntax it requires. Like :

Dim oSubscription as SQLDMO.Subscriber2
Dim oSubscriptionData as SQLDMO.<??>
For each oSubscriptiondata in
oSubscription.EnumAllSubscribers(SQLDMOSubscriptio n_All)

OR do I need to make something like
Dim Result
Dim oSubscription as SQLDMO.Subscriber2
Result = oSubscription.EnumAllSubscribers(SQLDMOSubscriptio n_All)
and then walk that Result for the correct data

Thanks anyway. I'll give this a try in the .repication group.

GB

Simon Hayes bracht volgend ide uit :
> I'm not sure what you're asking - do you mean that you know that
> EnumAllScubscriptions will return the information you need, but you
> don't know how to manipulate a SQL-DMO QueryResults object? If so, then
> you need to iterate over it, and use the GetColumn% methods to return
> the data in the format you want - in this case, GetColumnString is
> probably the best one to use.
> This is a simple VBScript example of using a QueryResults object:
> Option Explicit
> Dim oSrv, res, c, r
> Set oSrv = WScript.CreateObject("SQLDMO.SqlServer2")
> oSrv.LoginSecure = True
> oSrv.Name = "MyServer"
> oSrv.Connect
> Set res = oSrv.ExecuteWithResults("select * from sysobjects")
> For r = 1 To res.Rows
> For c = 1 To res.Columns
> If res.ColumnName(c) = "name" Then
> WScript.Echo res.GetColumnString(r, c)
> End If
> Next
> Next
>
> If this isn't helpful, you might get a better answer in
> microsoft.public.sqlserver.replication.
> Simon

--
Your eyes are weary from staring at the CRT. You feel sleepy. Notice
how restful it is to watch the cursor blink. Close your eyes. The
opinions stated above are yours. When I snap my fingers, you cannot
imagine why you ever felt otherwise. <snap