pwdencrypt and Case sensitive

Hi there
I know how to use pwdencrypt for my password and know how to use collation,
but how can I merge it together to make my password case sensitive and
pwdencrypt working, because collation only not support varbinary and my
pwdencrypt is saved on varbinary data type.
Any one has idea how to to this?
Thanks
Tony
I believe pwdencrypt behavior depends on the instance default collation. In
any case, you shouldn't use the undocumented pwdencrypt function in your
code. This may change or be removed in future SQL Server service packs or
versions.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tony Dong" <tony.dong@.envoytrading.com> wrote in message
news:Oy07MZsRFHA.996@.TK2MSFTNGP09.phx.gbl...
> Hi there
> I know how to use pwdencrypt for my password and know how to use
> collation, but how can I merge it together to make my password case
> sensitive and pwdencrypt working, because collation only not support
> varbinary and my pwdencrypt is saved on varbinary data type.
> Any one has idea how to to this?
> Thanks
> Tony
>
|||> I know how to use pwdencrypt for my password
If you knew that then you wouldn't be using it. ;-) Don't.
..NET provides classees for generating password hashes. They are more secure
than pwdencrypt and they are also properly documented and supported whereas
pwdencrypt is likely to break or disappear in future releases.
David Portas
SQL Server MVP
|||I am Tony's coworker and using .NET to encrypt the password is not an
attractive option because it means that we cannot operate the database
without .NET. It makes the business tier and database tier tightly
coupled or to use some of my old C++ jargon "It breaks encapsulation!"
If we were using Yukon, then .NET would be the way to go.
Cheers
Ted
*** Sent via Developersdex http://www.codecomments.com ***

pwdencrypt and Case sensitive

Hi there
I know how to use pwdencrypt for my password and know how to use collation,
but how can I merge it together to make my password case sensitive and
pwdencrypt working, because collation only not support varbinary and my
pwdencrypt is saved on varbinary data type.
Any one has idea how to to this?
Thanks
TonyI believe pwdencrypt behavior depends on the instance default collation. In
any case, you shouldn't use the undocumented pwdencrypt function in your
code. This may change or be removed in future SQL Server service packs or
versions.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Tony Dong" <tony.dong@.envoytrading.com> wrote in message
news:Oy07MZsRFHA.996@.TK2MSFTNGP09.phx.gbl...
> Hi there
> I know how to use pwdencrypt for my password and know how to use
> collation, but how can I merge it together to make my password case
> sensitive and pwdencrypt working, because collation only not support
> varbinary and my pwdencrypt is saved on varbinary data type.
> Any one has idea how to to this?
> Thanks
> Tony
>|||> I know how to use pwdencrypt for my password
If you knew that then you wouldn't be using it. ;-) Don't.
.NET provides classees for generating password hashes. They are more secure
than pwdencrypt and they are also properly documented and supported whereas
pwdencrypt is likely to break or disappear in future releases.
--
David Portas
SQL Server MVP
--

pwdencrypt and Case sensitive

Hi there
I know how to use pwdencrypt for my password and know how to use collation,
but how can I merge it together to make my password case sensitive and
pwdencrypt working, because collation only not support varbinary and my
pwdencrypt is saved on varbinary data type.
Any one has idea how to to this?
Thanks
TonyI believe pwdencrypt behavior depends on the instance default collation. In
any case, you shouldn't use the undocumented pwdencrypt function in your
code. This may change or be removed in future SQL Server service packs or
versions.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tony Dong" <tony.dong@.envoytrading.com> wrote in message
news:Oy07MZsRFHA.996@.TK2MSFTNGP09.phx.gbl...
> Hi there
> I know how to use pwdencrypt for my password and know how to use
> collation, but how can I merge it together to make my password case
> sensitive and pwdencrypt working, because collation only not support
> varbinary and my pwdencrypt is saved on varbinary data type.
> Any one has idea how to to this?
> Thanks
> Tony
>|||> I know how to use pwdencrypt for my password
If you knew that then you wouldn't be using it. ;-) Don't.
.NET provides classees for generating password hashes. They are more secure
than pwdencrypt and they are also properly documented and supported whereas
pwdencrypt is likely to break or disappear in future releases.
David Portas
SQL Server MVP
--|||I am Tony's coworker and using .NET to encrypt the password is not an
attractive option because it means that we cannot operate the database
without .NET. It makes the business tier and database tier tightly
coupled or to use some of my old C++ jargon "It breaks encapsulation!"
If we were using Yukon, then .NET would be the way to go.
Cheers
Ted
*** Sent via Developersdex http://www.codecomments.com ***

Pushing 2000 merge publication to 2005 server

I am attempting to push a SQL 2000 publication to a SQL 2005 server. I
realize that Microsoft does not recommend this, but I haven't found anything
that says that it can not be done.
When I try to register the SQL 2005 server in my Enterprise Manager I get
"To connect to this server you must use SQL Server Management Studio or SQL
Server Management Objects (SMO)".
Any ideas on how I can get around this?
I have over 100 locations that I run merge replication with.
Thanks.
Gary
Connect to all servers through SSMS (SQL Server Management Studio).
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
"gmac2" <gmac2@.discussions.microsoft.com> wrote in message
news:8F874E11-E1D1-4A6C-86EE-07C2FCE1DB35@.microsoft.com...
> I am attempting to push a SQL 2000 publication to a SQL 2005 server. I
> realize that Microsoft does not recommend this, but I haven't found
> anything
> that says that it can not be done.
> When I try to register the SQL 2005 server in my Enterprise Manager I get
> "To connect to this server you must use SQL Server Management Studio or
> SQL
> Server Management Objects (SMO)".
> Any ideas on how I can get around this?
> I have over 100 locations that I run merge replication with.
> Thanks.
> Gary
|||I am trying to do the push from the SQL 2000 end, I do not have access to the
2005 server as it belongs to a vendor. Any other ways around this?
I did get around registering the server throught publishing the database (I
had the other party create an empty database on their end) but then when I
try to push the subscriotion it can not deliver the snapshot as it gets the
sp_Msupdatesysmergeartciles has too many arguments specified (8144 error).
Thanks,
Gary
"gmac2" wrote:

> I am attempting to push a SQL 2000 publication to a SQL 2005 server. I
> realize that Microsoft does not recommend this, but I haven't found anything
> that says that it can not be done.
> When I try to register the SQL 2005 server in my Enterprise Manager I get
> "To connect to this server you must use SQL Server Management Studio or SQL
> Server Management Objects (SMO)".
> Any ideas on how I can get around this?
> I have over 100 locations that I run merge replication with.
> Thanks.
> Gary

push subscriber in merge replication

I am using merge replication with a push subscription type. I am wondering if the updates of the tables on the subscriber side are push to the publisher from the subscriber or pulled from the subscriber by the publisher when the syncronisation takes place. this makes a big diferrence for me and i can't find the answer to this question anywhere...

if anyone could answer it would be really appreciated

ThanksData Synchronization is performed by the Merge agent, wherever it runs from. In your case it's the Publisher.|||The Merge agent runs on the publisher but my question is : does the merge agent pull information from the subcriber or the subscriber push the information to the merge agent ??|||Well, it's a PULL if you look at it this way. The Agent runs on the Publisher, connects to Subscriber (see the agent's job definition in Scheduled Tasks), etc.sql

Push over the Internet?

Is it possible to have a push subscription for merge replication over the Internet?
If so, how?
If not, does anybody know why not?
Hi,
Have a look into the below article.
http://support.microsoft.com/?id=321822
FYI, I have never tried replication over internet.
Thanks
Hari
MCDBA
"Kyle" <Kyle@.discussions.microsoft.com> wrote in message
news:9B38B0BC-8700-46A9-A456-9432F24CFBAF@.microsoft.com...
> Is it possible to have a push subscription for merge replication over the
Internet?
> If so, how?
> If not, does anybody know why not?
|||Hari,
Thanks for the reply. Since writing my post I have become VERY familiar with that article. From what I understand, you can push over the Internet if the subscriber already has the schema. To get the initial schema, however, the subscriber must have a snap
shot agent that is configured to pull via FTP. While this is not my ideal solution, I may be able to make it work. Some of my subscribers will be using MSDE though so I don't know how well that will work.
Thanks,
Kyle
"Hari" wrote:

> Hi,
> Have a look into the below article.
> http://support.microsoft.com/?id=321822
> FYI, I have never tried replication over internet.
> --
> Thanks
> Hari
> MCDBA
> "Kyle" <Kyle@.discussions.microsoft.com> wrote in message
> news:9B38B0BC-8700-46A9-A456-9432F24CFBAF@.microsoft.com...
> Internet?
>
>
|||you can't push over the internet.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Kyle" <Kyle@.discussions.microsoft.com> wrote in message
news:7682CF86-87D4-4D5B-A9D1-6765278FEA62@.microsoft.com...
> Hari,
> Thanks for the reply. Since writing my post I have become VERY familiar
with that article. From what I understand, you can push over the Internet if
the subscriber already has the schema. To get the initial schema, however,
the subscriber must have a snapshot agent that is configured to pull via
FTP. While this is not my ideal solution, I may be able to make it work.
Some of my subscribers will be using MSDE though so I don't know how well
that will work.[vbcol=seagreen]
> Thanks,
> Kyle
>
> "Hari" wrote:
the[vbcol=seagreen]
|||Hilary,
I clicked on your book link and was disappointed to see that you wrote a book on transactional and snapshot replication but not merge. I then checked out the nwsu site and was delighted to see that you are devoting an ENTIRE book on merge replication. THA
TS GREAT! because so far, I have not been able to find a book that deals with the subject in depth. Usually I just see a chapter on replication that just repeates the Microsoft BOL. Please let me know when it is released.
That said, I HAVE been able to push over the Internet. It's not easy, it's not obvious and it's not well documented but it IS possible. I'm still working on my application but here's what I have learned so far which has enabled me to push over the Interne
t with dynamic filtering:
1. The publication must be saved to an FTP site that is available to the subscribers.
2. You must make a server alias (with Client Network Utility) of the subscriber on the publisher server that MATCHES the actual NetBIOS computer name of the subscriber. It seems dumb that you have to do this but it works.
3. For a dynamic filter to work with a push subscription, you must filter on HOST_NAME() and put a -hostname [somename] parameter in the Run Agent step of the subscription.
If I've got something wrong in my head or you have any other tips, I'd love to hear about it. Also, if I can be of any help with your upcoming book, please let me know.
Thanks,
Kyle
"Hilary Cotter" wrote:

> you can't push over the internet.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Kyle" <Kyle@.discussions.microsoft.com> wrote in message
> news:7682CF86-87D4-4D5B-A9D1-6765278FEA62@.microsoft.com...
> with that article. From what I understand, you can push over the Internet if
> the subscriber already has the schema. To get the initial schema, however,
> the subscriber must have a snapshot agent that is configured to pull via
> FTP. While this is not my ideal solution, I may be able to make it work.
> Some of my subscribers will be using MSDE though so I don't know how well
> that will work.
> the
>
>
|||Hilary,
I clicked on your book link and was disappointed to see that you wrote a book on transactional and snapshot replication but not merge. I then checked out the nwsu site and was delighted to see that you are currently working on a book devoted to merge repl
ication. Selfishly, I'm hoping you don't have good surf conditions for the next few months. Please let me know when it is released.
That said, I HAVE been able to push subscriptions over the Internet. It's not obvious or easy but it IS possible. I'm still working on my application but here's what I have learned so far which has enabled me to push a merge subscription over the internet
with dynamic filtering:
1. The publication must be put on an FTP site that is accessable to the subscribers.
2. The publishing server must have an alias to the subscriber that matches the actual NetBIOS computer name of the subscriber machine. This seems really dumb to me but it's the only way I could get it to work.
3. To enable dynamic filtering with a push subscription, you must filter on HOST_NAME() and you must put a -hostname [somename] parameter in the Run Agent step of the merge agent. This allows you to specify a particular subset of data to each subscriber i
ndependant of the subscriber's actual name or user (although, as I mentioned in point 2, you do have to know the subscriber's computer name).
If I've got something wrong or you have any other tips for me, please let me know. Also, if I can be of any help with your upcoming book, I'd be glad to contribute.
Thanks,
Kyle
"Hilary Cotter" wrote:

> you can't push over the internet.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Kyle" <Kyle@.discussions.microsoft.com> wrote in message
> news:7682CF86-87D4-4D5B-A9D1-6765278FEA62@.microsoft.com...
> with that article. From what I understand, you can push over the Internet if
> the subscriber already has the schema. To get the initial schema, however,
> the subscriber must have a snapshot agent that is configured to pull via
> FTP. While this is not my ideal solution, I may be able to make it work.
> Some of my subscribers will be using MSDE though so I don't know how well
> that will work.
> the
>
>
|||But if FTP is in the mix you are using a pull.
You don't have to register you Netbios name of the Subscriber on the
publisher. It can be any name as long as that name matches the ip address of
your subscriber and this name is entered in your hosts file.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Kyle" <Kyle@.discussions.microsoft.com> wrote in message
news:39C08D3F-D3FC-47E2-BB32-1AB5C04C8919@.microsoft.com...
> Hilary,
> I clicked on your book link and was disappointed to see that you wrote a
book on transactional and snapshot replication but not merge. I then checked
out the nwsu site and was delighted to see that you are devoting an ENTIRE
book on merge replication. THATS GREAT! because so far, I have not been able
to find a book that deals with the subject in depth. Usually I just see a
chapter on replication that just repeates the Microsoft BOL. Please let me
know when it is released.
> That said, I HAVE been able to push over the Internet. It's not easy, it's
not obvious and it's not well documented but it IS possible. I'm still
working on my application but here's what I have learned so far which has
enabled me to push over the Internet with dynamic filtering:
> 1. The publication must be saved to an FTP site that is available to the
subscribers.
> 2. You must make a server alias (with Client Network Utility) of the
subscriber on the publisher server that MATCHES the actual NetBIOS computer
name of the subscriber. It seems dumb that you have to do this but it works.
> 3. For a dynamic filter to work with a push subscription, you must filter
on HOST_NAME() and put a -hostname [somename] parameter in the Run Agent
step of the subscription.
> If I've got something wrong in my head or you have any other tips, I'd
love to hear about it. Also, if I can be of any help with your upcoming
book, please let me know.[vbcol=seagreen]
> Thanks,
> Kyle
> "Hilary Cotter" wrote:
familiar[vbcol=seagreen]
Internet if[vbcol=seagreen]
however,[vbcol=seagreen]
well[vbcol=seagreen]
over[vbcol=seagreen]
|||I see your point about the FTP but I don't think I'm creating a pull subscription. For the initial data... on the publisher I must tell the subscribers how to get to the FTP site (IP, login, password, folder). This is done via the Snapshot Location tab on
the publisher properties form, but this information does not show up anywhere (that I can find) on the subscriber. The subscriber shows that it has one subscription and that it is a push type. From the subscriber, I cannot modify or delete the subscripti
on - meaning that the publisher has complete control (as long as the connection remains in tact). Maybe internally there is an invisible pull subscription but from both sides all I see is push and the publisher maintains complete control (which is what I
want). Also, on the Subscription Options tab of the publication properties, I have un-checked the Allow pull subscriptions checkbox further convincing me that there are no pull subscriptions (even while moving the initial snapshot).
I tried using the lmhosts file instead of registering an alias with the Client Network Utility to map the subscriber IP to a name other than it's actual NetBIOS name. That allowed me to connect and it allowed me to push the initial snapshot. The merge age
nt failed, however, giving me the following error:
The subscription to publication 'TestPush1' is invalid.
(Source: Merge Replication Provider (Agent); Error number: -2147201019)
-----
The remote server is not defined as a subscription server.
(Source: SACRAMENTO (Data source); Error number: 14010)
-----
SACRAMENTO is the name in the lmhost file that is mapped to the subscriber's IP address. It IS defined as a subscription server. The same publication 'TestPush1' works fine with the other subscription (where the alias = the actual NetBIOS name).
This is all pretty much consistent with the KB article 321822 except that the article implies that you can't move the initial schema and data without a pull subscription. It does say, however, that you need to use the actual NetBIOS name although I would
sure like to find a way around this requirement.
Kyle
"Hilary Cotter" wrote:

> But if FTP is in the mix you are using a pull.
> You don't have to register you Netbios name of the Subscriber on the
> publisher. It can be any name as long as that name matches the ip address of
> your subscriber and this name is entered in your hosts file.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Kyle" <Kyle@.discussions.microsoft.com> wrote in message
> news:39C08D3F-D3FC-47E2-BB32-1AB5C04C8919@.microsoft.com...
> book on transactional and snapshot replication but not merge. I then checked
> out the nwsu site and was delighted to see that you are devoting an ENTIRE
> book on merge replication. THATS GREAT! because so far, I have not been able
> to find a book that deals with the subject in depth. Usually I just see a
> chapter on replication that just repeates the Microsoft BOL. Please let me
> know when it is released.
> not obvious and it's not well documented but it IS possible. I'm still
> working on my application but here's what I have learned so far which has
> enabled me to push over the Internet with dynamic filtering:
> subscribers.
> subscriber on the publisher server that MATCHES the actual NetBIOS computer
> name of the subscriber. It seems dumb that you have to do this but it works.
> on HOST_NAME() and put a -hostname [somename] parameter in the Run Agent
> step of the subscription.
> love to hear about it. Also, if I can be of any help with your upcoming
> book, please let me know.
> familiar
> Internet if
> however,
> well
> over
>
>
|||Can you script out your publication so we can see exactly what you have
done. It is possible you did a no sync push which can be done over the
internet.
FTP information is stored on the publisher not the subscriber in SQL 2000,
on SQL 7 IIRC it was also stored on the subcriber. The subscriber connects
to the publisher (using pull) to get ftp information.
Don't modify the lmhosts file, but the hosts file. LMhosts is primarily used
during netbios name resolution and if you are using TCPIP in the client
network utiltity it will use cache-hosts-DNS resolution to get the IP
address. It may then do a broadcast and consult the LMHosts files (IIRC).
You have to enable Sacremento as an enabled subscriber which won't work over
the internet. So you should be using anonymous.
Please contact me offline so we can resolve this quickly and I can get a
handle on what you are doing.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Kyle" <Kyle@.discussions.microsoft.com> wrote in message
news:985B1BF9-2345-4DB0-A803-FF5F4EBA7D80@.microsoft.com...
> I see your point about the FTP but I don't think I'm creating a pull
subscription. For the initial data... on the publisher I must tell the
subscribers how to get to the FTP site (IP, login, password, folder). This
is done via the Snapshot Location tab on the publisher properties form, but
this information does not show up anywhere (that I can find) on the
subscriber. The subscriber shows that it has one subscription and that it is
a push type. From the subscriber, I cannot modify or delete the
subscription - meaning that the publisher has complete control (as long as
the connection remains in tact). Maybe internally there is an invisible pull
subscription but from both sides all I see is push and the publisher
maintains complete control (which is what I want). Also, on the Subscription
Options tab of the publication properties, I have un-checked the Allow pull
subscriptions checkbox further convincing me that there are no pull
subscriptions (even while moving the initial snapshot).
> I tried using the lmhosts file instead of registering an alias with the
Client Network Utility to map the subscriber IP to a name other than it's
actual NetBIOS name. That allowed me to connect and it allowed me to push
the initial snapshot. The merge agent failed, however, giving me the
following error:
> The subscription to publication 'TestPush1' is invalid.
> (Source: Merge Replication Provider (Agent); Error number: -2147201019)
> ----
--
> The remote server is not defined as a subscription server.
> (Source: SACRAMENTO (Data source); Error number: 14010)
> ----
--
> SACRAMENTO is the name in the lmhost file that is mapped to the
subscriber's IP address. It IS defined as a subscription server. The same
publication 'TestPush1' works fine with the other subscription (where the
alias = the actual NetBIOS name).
> This is all pretty much consistent with the KB article 321822 except that
the article implies that you can't move the initial schema and data without
a pull subscription. It does say, however, that you need to use the actual
NetBIOS name although I would sure like to find a way around this
requirement.[vbcol=seagreen]
> Kyle
> "Hilary Cotter" wrote:
address of[vbcol=seagreen]
a[vbcol=seagreen]
checked[vbcol=seagreen]
ENTIRE[vbcol=seagreen]
able[vbcol=seagreen]
a[vbcol=seagreen]
me[vbcol=seagreen]
it's[vbcol=seagreen]
has[vbcol=seagreen]
the[vbcol=seagreen]
computer[vbcol=seagreen]
works.[vbcol=seagreen]
filter[vbcol=seagreen]
via[vbcol=seagreen]
work.[vbcol=seagreen]
replication[vbcol=seagreen]
|||Kyle, thanks for sending me your scripts off line.
Yes! You are successfully doing a push over the internet, and yes, it is
possible to do a push over the internet.
The reason it is working for you is that you are connecting over DSL (I did
a tracert to the IP address you provided me with and found out you are using
pacbell DSL). The reason it is working is that the you have the netbios
ports open. On most corporate internets this is locked down as it is a
security risk.
I assumed you were on a corporate network, or behind a firewall, and that
these ports will be blocked.
So my advise to you is to use a personal firewall, and some form of internet
security software, and migrate to a pull subscription.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:%237wZnlSYEHA.4092@.TK2MSFTNGP11.phx.gbl...
> Can you script out your publication so we can see exactly what you have
> done. It is possible you did a no sync push which can be done over the
> internet.
> FTP information is stored on the publisher not the subscriber in SQL 2000,
> on SQL 7 IIRC it was also stored on the subcriber. The subscriber connects
> to the publisher (using pull) to get ftp information.
> Don't modify the lmhosts file, but the hosts file. LMhosts is primarily
used
> during netbios name resolution and if you are using TCPIP in the client
> network utiltity it will use cache-hosts-DNS resolution to get the IP
> address. It may then do a broadcast and consult the LMHosts files (IIRC).
> You have to enable Sacremento as an enabled subscriber which won't work
over
> the internet. So you should be using anonymous.
> Please contact me offline so we can resolve this quickly and I can get a
> handle on what you are doing.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Kyle" <Kyle@.discussions.microsoft.com> wrote in message
> news:985B1BF9-2345-4DB0-A803-FF5F4EBA7D80@.microsoft.com...
> subscription. For the initial data... on the publisher I must tell the
> subscribers how to get to the FTP site (IP, login, password, folder). This
> is done via the Snapshot Location tab on the publisher properties form,
but
> this information does not show up anywhere (that I can find) on the
> subscriber. The subscriber shows that it has one subscription and that it
is
> a push type. From the subscriber, I cannot modify or delete the
> subscription - meaning that the publisher has complete control (as long as
> the connection remains in tact). Maybe internally there is an invisible
pull
> subscription but from both sides all I see is push and the publisher
> maintains complete control (which is what I want). Also, on the
Subscription
> Options tab of the publication properties, I have un-checked the Allow
pull[vbcol=seagreen]
> subscriptions checkbox further convincing me that there are no pull
> subscriptions (even while moving the initial snapshot).
> Client Network Utility to map the subscriber IP to a name other than it's
> actual NetBIOS name. That allowed me to connect and it allowed me to push
> the initial snapshot. The merge agent failed, however, giving me the
> following error:
> ----
> --
> ----
> --
> subscriber's IP address. It IS defined as a subscription server. The same
> publication 'TestPush1' works fine with the other subscription (where the
> alias = the actual NetBIOS name).
that
> the article implies that you can't move the initial schema and data
without[vbcol=seagreen]
> a pull subscription. It does say, however, that you need to use the actual
> NetBIOS name although I would sure like to find a way around this
> requirement.
> address of
wrote[vbcol=seagreen]
> a
> checked
> ENTIRE
been[vbcol=seagreen]
> able
see[vbcol=seagreen]
> a
let[vbcol=seagreen]
> me
easy,[vbcol=seagreen]
> it's
> has
> the
> computer
> works.
> filter
Agent[vbcol=seagreen]
I'd[vbcol=seagreen]
upcoming[vbcol=seagreen]
pull[vbcol=seagreen]
> via
> work.
how
> replication
>

Push Merge snapshot fails with Data is Null message.

Hi,

I'm converting a replication script from SQL 2000 to SQL 2005.

I am getting an error with push merge with no way to figure out what is wrong.

I've configured replication on a single XP server in SQL 2005 RTM version.

I have a push merge set up between A and B and between B and C. All 3 databases are 9.0 compatibility.

The snapshot and merge jobs for the A to B run fine with no errors, and merge replicates ok.

The snapshot for B to C fails with this message:
Message
2006-03-09 17:30:35.94
2006-03-09 17:30:35.94 -BcpBatchSize 100000
2006-03-09 17:30:35.94 -HistoryVerboseLevel 2
2006-03-09 17:30:35.94 -LoginTimeout 15
2006-03-09 17:30:35.94 -QueryTimeout 1800
2006-03-09 17:30:35.94
2006-03-09 17:30:35.95 Connecting to Publisher 'MyInstance'
2006-03-09 17:30:35.97 Publisher database compatibility level is set to 90.
2006-03-09 17:30:35.97 Retrieving publication and article information from the publisher database 'MyInstance.MyDB'
2006-03-09 17:30:36.22 [0%] The replication agent had encountered an exception.
2006-03-09 17:30:36.22 Source: Replication
2006-03-09 17:30:36.22 Exception Type: Microsoft.SqlServer.Replication.ReplicationAgentSqlException
2006-03-09 17:30:36.22 Exception Message: Data is Null. This method or property cannot be called on Null values.
2006-03-09 17:30:36.22 Message Code: 52006
2006-03-09 17:30:36.22

Love that exception message: "Data is Null" - very helpful to someone who is clairvoyant perhaps.
I checked the snapshot bcp files. The tables being merged all have data.

A sample add article command is:

exec sp_addmergearticle @.publication = N'MyMerge', @.article = N'Phone', @.processing_order = 4, @.source_owner = N'dbo', @.source_object = N'Phone', @.type = N'table', @.description = null, @.column_tracking = N'true', @.pre_creation_cmd = N'drop', @.creation_script = null, @.schema_option = 0x000000004C42CDDF, @.article_resolver = null, @.subset_filterclause = null, @.vertical_partition = N'false', @.destination_owner = N'dbo', @.verify_resolver_signature = 0, @.allow_interactive_resolver = N'false', @.fast_multicol_updateproc = N'true', @.check_permissions = 0, @.identityrangemanagementoption = N'none' ,@.force_invalidate_snapshot = 1,@.force_reinit_subscription = 1

If you have any ideas on how to fix this, I'd be most grateful. As it is after 6pm I probably won't read this again until morning. Thanks for any suggestions.

Can you get the full stack trace from MSrepl_errors in the distribution database and post it here please? Thanks.

-Raymond

|||

Here you go:

Message: Data is Null. This method or property cannot be called on Null values.
Command Text: sp_MSestimatemergesnapshotworkload
Parameters: @.publication = StandardMerge Stack:
at Microsoft.SqlServer.Replication.AgentCore.ExecuteWithOptionalResults(CommandSetupDelegate commandSetupDelegate, ProcessResultsDelegate processResultsDelegate, Int32 queryTimeout, CommandBehavior commandBehavior)
at Microsoft.SqlServer.Replication.AgentCore.ExecuteWithOptionalResults(CommandSetupDelegate commandSetupDelegate, ProcessResultsDelegate processResultsDelegate)
at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.InitializeProgressTracker()
at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.GenerateSnapshot()
at Microsoft.SqlServer.Replication.SnapshotGenerationAgent.InternalRun()
at Microsoft.SqlServer.Replication.AgentCore.Run()

Source: System.Data Target Site: Int32 get_Int32()
Message: Data is Null. This method or property cannot be called on Null values. Stack:
at System.Data.SqlClient.SqlBuffer.get_Int32()
at System.Data.SqlClient.SqlDataReader.GetInt32(Int32 i)
at Microsoft.SqlServer.Replication.Snapshot.ProgressTracker..ctor(SqlDataReader dataReader, SnapshotGenerationAgent snapshotGenerationAgent)
at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.CreateProgressTracker(SqlDataReader dataReader)
at Microsoft.SqlServer.Replication.AgentCore.ExecuteWithOptionalResults(CommandSetupDelegate commandSetupDelegate, ProcessResultsDelegate processResultsDelegate, Int32 queryTimeout, CommandBehavior commandBehavior)

|||

This looks like a bug, can you execute sp_MSestimatemergesnapshotworkload @.publication = 'StandardMerge' at the publisher database and post any rows with a null 'taskid' or 'taskload' value here. Thanks.

-Raymond

|||

Here they are. I've renamed the tables from their real names.

name taskid taskload Table 1 4 NULL Table 2 4 NULL Table 3 4 NULL Table 4 4 NULL Table 5 4 NULL Table 6 4 NULL Table 7 4 NULL Table 8 4 NULL Table 9 4 NULL Table 10 4 NULL Table 11 4 NULL Table 12 4 NULL Table 13 4 NULL

|||

By the way, for the same task id (4), for the A to B merge, these are the values (in case this is useful for comparison).

name taskid taskload Table 1 4 28 Table 2 4 41 Table 3 4 9 Table 4 4 2 Table 5 4 10 Table 6 4 20 Table 7 4 2 Table 8 4 5 Table 9 4 20 Table 10 4 37 Table 11 4 10 Table 12 4 27 Table 13 4 64

|||

Can you try the following query at the publisher database and see if it returns any rows?

select rowcnt from sysindexes where id = object_id(N'Table1') and indid in (0,1)

If the above query doesn't return any rows then this is basically the cause of the problem and it would be great if you can retry the query without the "and indid in (0,1)" part and report back whether there are any rows returned in that case. And if the publisher connection of the snapshot agent is not db_owner\sysadmin, you may want to temporarily change that and see if the problem goes away.

-Raymond

|||

The result from the original query is rowcnt = 195.

|||Also, this query and the prior queries were run using the login of the original script.|||

More info - in case it is useful

The parms passed for the merge publications:

For the AtoB merge (which works)
exec sp_addmergepublication @.publication = N'AtoB', @.description = N'Corporate Merge Replication', @.retention = 14, @.sync_mode = N'native', @.allow_push = N'true', @.allow_pull = N'true', @.allow_anonymous = N'false', @.publication_compatibility_level = '90RTM', @.enabled_for_internet = N'false', @.conflict_logging = N'publisher', @.snapshot_in_defaultfolder = N'true', @.pre_snapshot_script = @.PreScript, @.post_snapshot_script = @.PostScript, @.compress_snapshot = N'false', @.ftp_port = 21, @.ftp_login = N'anonymous', @.conflict_retention = 14, @.keep_partition_changes = N'false', @.allow_subscription_copy = N'false', @.allow_synctoalternate = N'false', @.add_to_active_directory = N'false', @.max_concurrent_merge = 0, @.max_concurrent_dynamic_snapshots = 0

exec sp_addpublication_snapshot @.publication = N'AtoB',@.frequency_type = 4, @.frequency_interval = 1, @.frequency_relative_interval = 1, @.frequency_recurrence_factor = 0, @.frequency_subday = 1, @.frequency_subday_interval = 5, @.active_start_date = 0, @.active_end_date = 0, @.active_start_time_of_day = 500, @.active_end_time_of_day = 235959, @.snapshot_job_name = @.MyName

exec sp_addmergesubscription @.publication = N'AtoB', @.subscriber = @.MyInstance, @.subscriber_db = N'SiteMaster', @.subscription_type = N'push', @.subscriber_type = N'global', @.subscription_priority = 75.0, @.sync_type = N'automatic', @.frequency_type = 4, @.frequency_interval = 1, @.frequency_relative_interval = 1, @.frequency_recurrence_factor = 0, @.frequency_subday = 8, @.frequency_subday_interval = 1, @.active_start_date = 0, @.active_end_date = 0, @.active_start_time_of_day = 0, @.active_end_time_of_day = 235959, @.enabled_for_syncmgr = N'false', @.offloadagent = 0, @.use_interactive_resolver = N'false', @.merge_job_name = @.Myname

For the BtoC merge (which doesn't)
exec sp_addmergepublication @.publication = N'BtoC', @.description = N'Facility Master Merge Replication', @.retention = 14, @.sync_mode = N'native', @.allow_push = N'true', @.allow_pull = N'true', @.allow_anonymous = N'false',@.publication_compatibility_level = '90RTM', @.enabled_for_internet = N'false', @.conflict_logging = N'publisher', @.snapshot_in_defaultfolder = N'true', @.pre_snapshot_script = @.PreScript, @.post_snapshot_script = @.PostScript, @.compress_snapshot = N'false', @.ftp_port = 21, @.ftp_login = N'anonymous', @.conflict_retention = 14, @.keep_partition_changes = N'false', @.allow_subscription_copy = N'false', @.allow_synctoalternate = N'false', @.add_to_active_directory = N'false', @.max_concurrent_merge = 0, @.max_concurrent_dynamic_snapshots = 0

exec sp_addpublication_snapshot @.publication = N'BtoC',@.frequency_type = 4, @.frequency_interval = 1, @.frequency_relative_interval = 1, @.frequency_recurrence_factor = 0, @.frequency_subday = 1, @.frequency_subday_interval = 5, @.active_start_date = 0, @.active_end_date = 0, @.active_start_time_of_day = 500, @.active_end_time_of_day = 235959, @.snapshot_job_name = @.Myname

exec sp_addmergesubscription @.publication = N'BtoC', @.subscriber = @.MyInstance, @.subscriber_db = @.MyFacility, @.subscription_type = N'push', @.subscriber_type = N'local', @.subscription_priority = 0.0, @.sync_type = N'automatic', @.frequency_type = 4, @.frequency_interval = 1, @.frequency_relative_interval = 1, @.frequency_recurrence_factor = 0, @.frequency_subday = 8, @.frequency_subday_interval = 1, @.active_start_date = 0, @.active_end_date = 0, @.active_start_time_of_day = 0, @.active_end_time_of_day = 235959, @.enabled_for_syncmgr = N'false', @.offloadagent = 0, @.use_interactive_resolver = N'false', @.merge_job_name = @.MyName

|||

This looks really weird, can you run sp_helpmergearticle @.publication =N'BtoC' at B and report back whether the source_object and source_owner columns are NULLs? Thanks much.

-Raymond

|||

Raymond,

I get nothing back from that at all. When I run it on the AtoB pub,run at A, the result looks normal. But nothing at all comes back for BtoC publication, run at B.

|||

This is interesting:

When I run EXEC dbo.sp_helpmergepublication at A, I get priority = 100.

When I run the same thing at B, I also get priority = 100.

You can see in my sp_addmergesubscription for A, I had set priority to 75.0 and that for B I'd set it to 0.0 so SQL is ignoring those parms I guess.

|||

Ok, looks like your publication\article metadata is very messed up at B. To confirm my suspicion, can you try the following query at B:

select name, pubid, objid, object_name(objid) from dbo.sysmergeextendedarticlesview

I am interested to know if any values in the last column are null or not. If there are any null values, then your article meta-data is seriously corrupted. You can, of course, recover from this particular problem by recreating the publication at B but given the kind of problems you have experienced in the past, I strongly suspect some other background operations at B is what is causing the problems in the first place. Here are a couple of possibilities that I can think of:

1) Someone tried to renamed the published table at B

2) Someone tried to drop and recreate the published table at B and use sp_MSunmarkreplino to allow the operation to occur.

3) The merge agent has a problem updating the object ids of published tables at B when a snapshot is delivered from A to B.

4) You have included sysmergearticles and\or sysmergepublications as part of the publication from A to B.

HTH

-Raymond

|||

I ran it (ordered by 2,1) and all the articles for B publication have NULL in the last column.

As for the possibilities, nobody but me has done anything to this data.

1) haven't done

2) I ran that once a week ago when I was first putting this stuff together and trying to clean stuff up with another problem I have with transactional replication getting a 3724 error ("Cannot drop the table 'dbo.Mytablename' because it is being used for replication.) Would that still be messing me up after many builds since then?

3) At the moment, I believe everything is running under the SQL Agent Svc login.

4) Those are not in my 13 explicitly named sp_addmergearticle articles.

Also, per priority difference mentioned earlier. I ran sp_helpmergesubscription and the priorities were as I expected. So why are the sp_helpmergepublication priorities 100?

Push merge replication fails - problem connecting to subscriber

I am trying to replicate a SQL 2000 database from one branch office to
another. The replication monitor seems to stall at Connecting to
'Subscriber', opening SQL profiler shows repeated sp_MSgetonerow calls. I
have left it running for a day and a half to see if it would sort itself out
and it hasn't.
Replication seemed to work fine while I was making changes at the publisher
and replicating them down to subscriber. As soon as subscriber made changes,
this happened. How do I start to troubleshoot?
Thanks
Brandon
Pasting my output debugging file
Microsoft SQL Server Merge Agent 8.00.760
Copyright (c) 2000 Microsoft Corporation
Microsoft SQL Server Replication Agent: SERVER-ildm-ildm-CALGSERV-34
Percent Complete: 0
Connecting to Distributor 'SERVER'
Connecting to Distributor 'SERVER.'
Server: SERVER
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[2/7/2005 11:08:00 AM]SERVER.: {call sp_MSgetversion }
[2/7/2005 11:08:00 AM]SERVER.: {call sp_helpdistpublisher (N'SERVER') }
[2/7/2005 11:08:00 AM]SERVER.distribution: select datasource, srvid from
master..sysservers where upper(srvname) = upper(N'SERVER')
[2/7/2005 11:08:00 AM]SERVER.distribution: select datasource, srvid from
master..sysservers where upper(srvname) = upper(N'CALGSERV')
[2/7/2005 11:08:00 AM]SERVER.distribution: {call sp_MShelp_merge_agentid (0,
N'ildm', N'ildm', 2, N'ILDM')}
[2/7/2005 11:08:00 AM]SERVER.distribution: {call sp_MShelp_profile (34, 4,
N'')}
Percent Complete: 0
Connecting to Publisher 'SERVER.ildm'
Initializing
Server: SERVER
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[2/7/2005 11:08:00 AM]SERVER.ildm: set nocount on declare @.dbname sysname
select @.dbname = db_name() declare @.collation nvarchar(255) select @.collation
= convert(nvarchar(255), databasepropertyex(@.dbname, N'COLLATION')) select
collationproperty(@.collation, N'CODEPAGE') as 'CodePage',
collationproperty(@.collation, N'LCID') as 'LCID',
collationproperty(@.collation, N'COMPARISONSTYLE') as 'ComparisonStyle'
Percent Complete: 1
Connecting to Publisher 'SERVER.ildm'
Connecting to Publisher 'SERVER'
Server: SERVER
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[2/7/2005 11:08:01 AM]SERVER.ildm: {call sp_MSgetversion }
[2/7/2005 11:08:01 AM]SERVER.distribution: {call sp_MShelp_subscriber_info
(N'SERVER', N'CALGSERV')}
Connecting to Subscriber 'CALGSERV.ILDM'
Server: CALGSERV
DBMS: Microsoft SQL Server
Version: 08.00.0194
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[2/7/2005 11:08:01 AM]CALGSERV.ILDM: {call sp_MSgetversion }
Percent Complete: 2
Connecting to Subscriber 'CALGSERV'
Percent Complete: 3
Retrieving publication information
Percent Complete: 4
Retrieving subscription information
Percent Complete: 4
The merge process is cleaning up meta data in database 'ildm'.
Percent Complete: 4
The merge process cleaned up 0 row(s) in MSmerge_genhistory, 0 row(s) in
MSmerge_contents, and 0 row(s) in MSmerge_tombstone.
Percent Complete: 4
Uploading data changes to the Publisher
Connecting to Subscriber 'CALGSERV.ILDM'
Server: CALGSERV
DBMS: Microsoft SQL Server
Version: 08.00.0194
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[2/7/2005 11:08:03 AM]CALGSERV.ILDM: {call sp_MSgetversion }
Connecting to Publisher 'SERVER.ildm'
Server: SERVER
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[2/7/2005 11:08:03 AM]SERVER.ildm: {call sp_MSgetversion }
Connecting to Subscriber 'CALGSERV.ILDM'
Server: CALGSERV
DBMS: Microsoft SQL Server
Version: 08.00.0194
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[2/7/2005 11:08:03 AM]CALGSERV.ILDM: {call sp_MSgetversion }
Connecting to Publisher 'SERVER.ildm'
Server: SERVER
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[2/7/2005 11:08:04 AM]SERVER.ildm: {call sp_MSgetversion }
Connecting to Subscriber 'CALGSERV.ILDM'
Server: CALGSERV
DBMS: Microsoft SQL Server
Version: 08.00.0194
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[2/7/2005 11:08:04 AM]CALGSERV.ILDM: {call sp_MSgetversion }
Connecting to Publisher 'SERVER.ildm'
Server: SERVER
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[2/7/2005 11:08:04 AM]SERVER.ildm: {call sp_MSgetversion }
Connecting to Subscriber 'CALGSERV.ILDM'
Server: CALGSERV
DBMS: Microsoft SQL Server
Version: 08.00.0194
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[2/7/2005 11:08:05 AM]CALGSERV.ILDM: {call sp_MSgetversion }
Connecting to Publisher 'SERVER.ildm'
Server: SERVER
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[2/7/2005 11:08:05 AM]SERVER.ildm: {call sp_MSgetversion }
|||What profile are you running?
Can you right click on your merge agent, select agent properties, Steps, and
then Run Agent. Click on the Edit button. Click in the Commands box, and
press CTRL A. Then Press CTRL C
Then paste the commands here and post everything here.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Now available on Amazon.com
http://www.amazon.com/gp/product/off...?condition=all
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"pUmkInhEd" <pUmkInhEd@.discussions.microsoft.com> wrote in message
news:388D911E-2BB3-4A02-AC22-F1129C02C2BC@.microsoft.com...
> I am trying to replicate a SQL 2000 database from one branch office to
> another. The replication monitor seems to stall at Connecting to
> 'Subscriber', opening SQL profiler shows repeated sp_MSgetonerow calls. I
> have left it running for a day and a half to see if it would sort itself
out
> and it hasn't.
> Replication seemed to work fine while I was making changes at the
publisher
> and replicating them down to subscriber. As soon as subscriber made
changes,
> this happened. How do I start to troubleshoot?
> Thanks
> Brandon

pull vs push

I just inherited an application using merge replication
to pull information from the database server when the
users activates the synchronization process. When the
user is connected to the network, can I push the
subscription to the them from the server instead of
having them initiate the pull? Will pushing the
subscription to the user change the way the tables are
synchronized/updated?
Thanks
Emma
Emma,
pull vs push won't affect the data replicated. If you already have pull
agents set up, you could initiate them from the publisher if you want to
mimic a push.
Regards,
Paul Ibison

pull subscription, subscriber configuration

Hi,
I was trying a pull subscription and I got all kinds of error messages
all suggesting that the merge agent isn't being run or it isn't
accessible. But, the moment I did a push subscription, it ran and
replication happened without an error message. I don't understand why.
My subscriber is an ordinary windows 2000 OS and is not of a server
configuration. Could that be a reason, since, in a pull subscription,
the agent is supposed to run on the subscriber(which isn't a server)?
Any suggestions or thoughts are welcome!
Also, is there a way a subscriber can tell the publisher to push a
subscription?
Thanks,
PS.
By definition a pull subscriber "pulls" the subscription on its own schedule - it doesn't tell the publisher push now.
Its hard to figure out exactly what the error is from your description. Please provide the complete error messages you are getting.
In general when you have pull subscription problems.
1) check that the SQL Server agent account has rights to read the snapshot share and the underlying path on the publisher, likely to be \\publisherName\C$\Program Files\Microsoft SQL Server\MSSQL\ReplData\unc
2) check on your publisher that you have enabled your subscriber to impersonate the Server agent account on the Publisher. Go to Tools, point to replication, click configure publishers, subscribers, and distributors, click on the browse button to the righ
t of your subscriber and select the impersonate option.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"ps" wrote:

> Hi,
> I was trying a pull subscription and I got all kinds of error messages
> all suggesting that the merge agent isn't being run or it isn't
> accessible. But, the moment I did a push subscription, it ran and
> replication happened without an error message. I don't understand why.
> My subscriber is an ordinary windows 2000 OS and is not of a server
> configuration. Could that be a reason, since, in a pull subscription,
> the agent is supposed to run on the subscriber(which isn't a server)?
> Any suggestions or thoughts are welcome!
> Also, is there a way a subscriber can tell the publisher to push a
> subscription?
> Thanks,
> PS.
>

Pull Subscription, Merge Replication

Hi,
I was trying to pull a subscription to an MSDE machine(subscriber)
from a sql server 2000 machine acting as the publisher and
distributor. when I configure for the pull, it generates a snapshot
immediately which is successfully completed. But then, the pull
actually never happens and I get the message saying the pull
subscription has 'Never Started'. I found that, if the name of the job
exceeds 100 characters, then the problem arises, but my job name is
very small and hence, I dont think that is my problem. I tried to
manually start and reinitialize all the agents, but that does not
solve my problem.
Can someone suggest what else could be done?
Thanks
PS.
you will get long job names when you are using a pull subscription, and
short names when you are doing a push subscription.
can you enable logging to see what it reports. Follow the steps in this kb
article to enable logging.
http://support.microsoft.com/default...b;EN-US;312292
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"ps" <ps_sr2001@.yahoo.com> wrote in message
news:1a7be36d.0406131046.2aa8d35e@.posting.google.c om...
> Hi,
> I was trying to pull a subscription to an MSDE machine(subscriber)
> from a sql server 2000 machine acting as the publisher and
> distributor. when I configure for the pull, it generates a snapshot
> immediately which is successfully completed. But then, the pull
> actually never happens and I get the message saying the pull
> subscription has 'Never Started'. I found that, if the name of the job
> exceeds 100 characters, then the problem arises, but my job name is
> very small and hence, I dont think that is my problem. I tried to
> manually start and reinitialize all the agents, but that does not
> solve my problem.
> Can someone suggest what else could be done?
> Thanks
> PS.
|||Hi Hilary,
Thanks for the response. Well, I went to the agent, right clicked on
it and went to 'Agent Properties'. under 'Steps' tab, I found 'Run
Agent' for which I clicked on Edit and appended '-Output
C:\Repl_out.txt -Outputverboselevel 2'.
Upon starting the agent, I am not finding any such file or if I create
the file myself, the file is empty.
Also, I am getting an error in the subscriber under subscriptions
saying "The process could not connect to Distributor 'Name'. Login
failed for user". The subscriber is listed under the 'Remote Servers'
of the publisher. Is there anything else that could be done?
Thanks,
PS
"Hilary Cotter" <hilaryk@.att.net> wrote in message news:<#0A$ylbUEHA.4064@.TK2MSFTNGP11.phx.gbl>...[vbcol=seagreen]
> you will get long job names when you are using a pull subscription, and
> short names when you are doing a push subscription.
> can you enable logging to see what it reports. Follow the steps in this kb
> article to enable logging.
> http://support.microsoft.com/default...b;EN-US;312292
>
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "ps" <ps_sr2001@.yahoo.com> wrote in message
> news:1a7be36d.0406131046.2aa8d35e@.posting.google.c om...
|||Hi,
From the verbose mode 2, I got this output. This is the last part of
the log:
A snapshot of 1 article(s) was generated.
Disconnecting from Publisher '********'
Microsoft SQL Server Snapshot Agent 8.00.760
Copyright (c) 2000 Microsoft Corporation
Microsoft SQL Server Replication Agent: *************-Repl-5
Connecting to Distributor '*********'
Connecting to Publisher '*************'
But in the subscriber database, under subscriptions, I got a message
stating:
"SQL Server Agent could not access the replication agent. Use the
DCOMCNFG utility".
I have SP3a installed on both publisher and subscriber, hence it
should not be the bug because of it.
Upon refreshing the same, I got a message stating - "The step did not
generate any output. The step failed".
Any ideas or suggestions will be greatly appreciated!
Thanks,
PS.
"Hilary Cotter" <hilaryk@.att.net> wrote in message news:<#0A$ylbUEHA.4064@.TK2MSFTNGP11.phx.gbl>...[vbcol=seagreen]
> you will get long job names when you are using a pull subscription, and
> short names when you are doing a push subscription.
> can you enable logging to see what it reports. Follow the steps in this kb
> article to enable logging.
> http://support.microsoft.com/default...b;EN-US;312292
>
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "ps" <ps_sr2001@.yahoo.com> wrote in message
> news:1a7be36d.0406131046.2aa8d35e@.posting.google.c om...

Pull subscription initialisation

Hi there all

I have a problem creating a pull merge subscription on a server that's outside of our firewall. All standard ports are blocked so MS UNC connections are not possible which SQL 2000 uses by default when creating a subscription. Equally FTP is out = insecure.

I found a procedure to that said create the snapshot of the publication, create a backup of the published database, restore that backup to the remote server as a subscription db and then sync using merge. Didn't work, failed (several times).

Can anyone enlighten me as to what I'm doing wrong or indeed if I'm doing anything correctly!

I can create a remote desktop (Term Server) connection to the remote and another back to the publisher (from the subscriber's desktop) and both connections are using SSH tunnels. SQL Server uses a non standard port to communicate over the firewall. The remote server sits behind another firewall/router with port redirection to it's private address. Each server has the other registered and there are no comms problems and indeed there are other replicated dbs between them.

Both servers are win 2003 and the remote (subscriber) is R2 version, both with SQL 2000 Server Std patched up to SP3. (if this has any bearing on the solution which I presume there is)

Many thanks for listening and I hope a few of you can answer as well

CraigI have a problem creating a pull merge subscription on a server that's outside of our firewall. All standard ports are blocked
Default SQL Server port is 1433. You need to ask your System Administrator to set appropriate access for MS SQL Server at both end.

Check your server is enabled for subscription on publisher / distributor server.

These are basic check system, could be other reasons but you need to start from bottom.|||Hi there

Yep, I'm the SysAdmin, that's why I'm fielding the question. We purposefully changed the default port as a security measure. Duplication and general SQL functions work perfectly on the chosen port. Ports are blocked and changed because communication is over the internet. The problem is that when you launch an initialisation of a subscriber SQL uses the windows default port 445 for mapped drives, etc to copy down snapshot.pre and the other scripts. This port is is blocked, again this is a security measure.

What I need to do is find a way to apply the snapshot (after copying it from the publisher) to the Subscriber without using a network or internetwork connection and then the subscriber can sync with the Publisher

I hope this clarifies the problem|||The problem is that when you launch an initialization of a subscriber SQL uses the windows default port 445 for mapped drives, etc to copy down snapshot.pre and the other scripts. This port is is blocked, again this is a security measure.

As far as I knew, regarding firewall you can set particular port access for particular application in your firewall. Even you can configure particular IP access for port, but this all depend on your firewall configuration options.

Without providing adequate access privilege, you could not expect any transaction on network.

What I need to do is find a way to apply the snapshot (after copying it from the publisher) to the Subscriber without using a network or internetwork connection and then the subscriber can sync with the Publisher

Get the copy of snapshot, dump it & set snapshot file location from subscription properties.

But I feel it's not the correct way, manual setups could be disturb frequently.|||Hi Rajesh

I've managed to do it and syncronisation is working. The process as I managed to do it was

Create a subscription in the publisher
create a backup of the subscription
copy this backup to the subscriber
restore the subscription over a previously created database making the DB larger enough that SQL doesn't have to grow the DB during initialisation
copy the snapshot to a folder on the subscriber ensuring that the complete path below \\%publisher%\%replfolder%\ was as it exists on the publisher (because this appears to be encoded into the snapshot)
Create the subscription on the subscriber
Change the snapshot location on the subscriber to point to the local snapshot
Launch sincronisation
Change the subscriber snapshot location back to the publisher default (v. important)

and it worked

to test we added some data to the publication and sincronised from the subscriber because it's a pull sub.

and then cleaned up ready to populate the database with it's full content

We've create the subscription as a data-empty database because there's some 11 Gb of data to prepopulate the db for use and we'll sincronise later using a modified agent to cope with the longer sincronisation times (and not get timeout errors)

The normally impossible was acheived

pull subscription chaning identity seed/increment

I have an anynonomous pull merge set and working. But, I create a new
table on the client machine with increment and seed as -1000 and -20.
The table is created fine. But when I execture the first synchrnoize
data from the client, the table is changed to be 1, 2, which is what the
server has. I am using SQL-DMO on the client with the following code:
With myMergeObj
.Distributor = inxRS.Globals.gServer
.DistributorLogin = "inxsql"
.DistributorPassword = "inxsql4u"
.DistributorSecurityMode = SECURITY_TYPE.DB_AUTHENTICATION
.DistributorNetwork = NETWORK_TYPE.TCPIP_SOCKETS
.DistributorAddress = inxRS.Globals.gServerIP
.Publisher = inxRS.Globals.gServer
.PublisherDatabase = in_comp
.Publication = Trim(in_comp) & "RS"
.PublisherLogin = "inxsql"
.PublisherPassword = "inxsql4u"
.PublisherSecurityMode = SECURITY_TYPE.DB_AUTHENTICATION
.PublisherNetwork = NETWORK_TYPE.TCPIP_SOCKETS
.PublisherAddress = inxRS.Globals.gServerIP
.Subscriber = Trim(Environment.MachineName)
.SubscriberDatabase = in_comp
.SubscriberSecurityMode = SECURITY_TYPE.NT_AUTHENTICATION
.SubscriptionType = SUBSCRIPTION_TYPE.ANONYMOUS
.SubscriptionName = Trim(in_comp) & "RS"
.HostName = Trim(inxRS.Globals.gLogin)
.ExchangeType = EXCHANGE_TYPE.BIDIRECTIONAL
.ReinitializeSubscription(false)
End With
myMergeObj.Initialize()
myMergeObj.Run()
myMergeObj.Terminate()
I can't tell if it is a problem in the pull or in how the publication
was/is setup.
Thanks.
Darin
*** Sent via Developersdex http://www.codecomments.com ***
I removed the reinit statement and I still have the same problem.
Darin
*** Sent via Developersdex http://www.codecomments.com ***
|||FIXED IT.
Darin
*** Sent via Developersdex http://www.codecomments.com ***

Pull replication

I cannot get a pull merge replication to work.
The distributor is Win2000 running SQL 2000.
Subscribers are XP Pro running MSDE.
Agent command:
-Publisher [DistSVR] -PublisherDB [MyDB] -Publication [MyDB] -Subscriber
[SubSVR] -SubscriberDB [MyDB] -SubscriptionType 1 -SubscriberSecurityMode 0
-SubscriberLogin [sa] -SubscriberEncryptedPassword
[??????] -Distributor [DistSVR]
The errors:
Category:AGENT
Source: SubSVR
Number: 20084
Message: The process could not connect to Subscriber 'SubSVR'.
Category:SQLSERVER
Source: SubSVR
Number: 17
Message: SQL Server does not exist or access denied.
Thank you ver much.
This means your subscriber can't connect.
Log on to your subscriber machine and try to ping your publisher.
Also verify you can ping your subscriber using the host name SubSVR from
your subscriber. Also verify that your subscriber password is correct.
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
"Thang Long" <ThangLong@.discussions.microsoft.com> wrote in message
news:C60CE48B-18A5-4C03-A034-5FF59E876951@.microsoft.com...
> I cannot get a pull merge replication to work.
> The distributor is Win2000 running SQL 2000.
> Subscribers are XP Pro running MSDE.
> Agent command:
> -Publisher [DistSVR] -PublisherDB [MyDB] -Publication [MyDB] -Subscriber
> [SubSVR] -SubscriberDB [MyDB] -SubscriptionType 1 -SubscriberSecurityMode
0
> -SubscriberLogin [sa] -SubscriberEncryptedPassword
> [??????] -Distributor [DistSVR]
> The errors:
> Category:AGENT
> Source: SubSVR
> Number: 20084
> Message: The process could not connect to Subscriber 'SubSVR'.
> Category:SQLSERVER
> Source: SubSVR
> Number: 17
> Message: SQL Server does not exist or access denied.
> Thank you ver much.
>
|||Thank you for your reply.
Subscriber PC name and SQL server name are different. It's the reason ?
Excuse me for the question: SubscriberLogin is the login to Subscriber or
Distributer ?
Thank you.
"Thang Long" wrote:

> I cannot get a pull merge replication to work.
> The distributor is Win2000 running SQL 2000.
> Subscribers are XP Pro running MSDE.
> Agent command:
> -Publisher [DistSVR] -PublisherDB [MyDB] -Publication [MyDB] -Subscriber
> [SubSVR] -SubscriberDB [MyDB] -SubscriptionType 1 -SubscriberSecurityMode 0
> -SubscriberLogin [sa] -SubscriberEncryptedPassword
> [??????] -Distributor [DistSVR]
> The errors:
> Category:AGENT
> Source: SubSVR
> Number: 20084
> Message: The process could not connect to Subscriber 'SubSVR'.
> Category:SQLSERVER
> Source: SubSVR
> Number: 17
> Message: SQL Server does not exist or access denied.
> Thank you ver much.
>

Pull or Push subscription in Merge replication

I have 2 identical systems in my LAN. I want to set up merge replication between these. I am bit confused among PULL or PUSH subscriptions.
Gurus can you suggest me best option
Regards
Javed
"Javed Iqbal" schrieb:
> I have 2 identical systems in my LAN. I want to set up merge replication between these. I am bit confused among PULL or PUSH subscriptions.
> Gurus can you suggest me best option
> Regards
> Javed
In push subscriptions the distributor agent runs on the distributor, in pull
subscriptions it runs on (each) subscriber.
Use push if
1. the central publisher must control the timing for the data synchronization.
2. the distributor server has enough resources to run the extra agent.
Use pull if
1. the subscribers must decide the timing for the data synchronization (e.g.
Laptops that are not always connected to the network or avoiding data
synchronization during extensive batch processes)
2. the distributor is performing badly or low on resources.
|||Thanks Christian
Regards
Javed
"Christian Donner" <ChristianDonner@.discussions.microsoft.com> wrote in
message news:87D4A071-F04F-4910-A27F-3D915C786DAD@.microsoft.com...[vbcol=seagreen]
> "Javed Iqbal" schrieb:
between these. I am bit confused among PULL or PUSH subscriptions.
> In push subscriptions the distributor agent runs on the distributor, in
pull
> subscriptions it runs on (each) subscriber.
> Use push if
> 1. the central publisher must control the timing for the data
synchronization.
> 2. the distributor server has enough resources to run the extra agent.
> Use pull if
> 1. the subscribers must decide the timing for the data synchronization
(e.g.
> Laptops that are not always connected to the network or avoiding data
> synchronization during extensive batch processes)
> 2. the distributor is performing badly or low on resources.
|||also use pull if you have a large number of subscribers.
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
"Christian Donner" <ChristianDonner@.discussions.microsoft.com> wrote in
message news:87D4A071-F04F-4910-A27F-3D915C786DAD@.microsoft.com...[vbcol=seagreen]
> "Javed Iqbal" schrieb:
between these. I am bit confused among PULL or PUSH subscriptions.
> In push subscriptions the distributor agent runs on the distributor, in
pull
> subscriptions it runs on (each) subscriber.
> Use push if
> 1. the central publisher must control the timing for the data
synchronization.
> 2. the distributor server has enough resources to run the extra agent.
> Use pull if
> 1. the subscribers must decide the timing for the data synchronization
(e.g.
> Laptops that are not always connected to the network or avoiding data
> synchronization during extensive batch processes)
> 2. the distributor is performing badly or low on resources.

Pull Merge replication between two SQL Server machines in different, but trusted domains

Hi,
We are attempting to setup Pull Merge replication between two SQL Server
(SQL 2K sp3a, Windows Authentication) machines in different domains. We have
been able to get Pull Merge replication to work within the same domain.
While we are running Win2k on both machines, two one way trusts were setup
between them. The server are linked to each other, but cannot initiate the
replication agents. The message we get is:
SQL Server Agent could not access the replication agent. Use the DCOMCNFG
utility to confirm that the SQL Server Agent Windows account has permissions
to launch the replication agent. The step failed.
I did not spot any in DCOMCNFG, but I am not familiar with it. Any
suggections?
Tia
Paul
Paul,
here is an MSDN article which shows how to enable the merge agent to be run
remotely:
http://msdn.microsoft.com/library/de...us/howtosql/ht
_replctem_6c1l.asp
HTH,
Paul Ibison
|||I have a feeling that the error message may be misleading me away from the
true problem. It appears that DCOMCNFG is used for running agents remotely.
I am looking at having the push replication run at the distributor and pull
replications to run at the subscriber. Besides, I couldn't get it to work by
using DCOMCNFG.
I want to have one merge publication which is pushed to subscribers which
will not be intentionally disconnected from the network. And, the same
publication pulled to subscribers which will be removed from the network for
possibly weeks. This currently works as long as long as all the servers are
in the same domain. The problem is that the pull subscribers will be in
different domains with trust relationship setup between them and the
distributor.
When I take the existing scripts that work when all of the server are in the
same domain and run them with the pull subscriber in different domains I get
the error:
SQL Server Agent could not access the replication agent. Use the DCOMCNFG
utility to confirm that the SQL Server Agent Windows account has permissions
to launch the replication agent. The step failed.
I don't think this has any thing to do with running the agent remotely, but
I'm not sure. Any suggestions?
Thanks
Paul
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uQmkhv7FEHA.744@.TK2MSFTNGP09.phx.gbl...
> Paul,
> here is an MSDN article which shows how to enable the merge agent to be
run
> remotely:
>
http://msdn.microsoft.com/library/de...us/howtosql/ht
> _replctem_6c1l.asp
> HTH,
> Paul Ibison
>
|||PaulW,
The easiest way to set up replication between different domains is to create a local user account on both the servers with the same username and password. Make the account an administrator of the local machine. Then set this account as the startup account
for the SQL Server Agent. Restart the SQL Server agent and start the merge agent. This should fix your problem.
Regards
Chris R
|||Thanks for the response. I saw that option for non-trusted domains and when
I presented it, the option was quickly shot down because the production
system will be administered by different companies and they didn't want to
be forced to share passwords..
Thanks,
Paul
"Chris R" <crichardson@.iir-central.com> wrote in message
news:7CE505D6-BE9C-40E4-80D4-75F7287AAB3C@.microsoft.com...
> PaulW,
> The easiest way to set up replication between different domains is to
create a local user account on both the servers with the same username and
password. Make the account an administrator of the local machine. Then set
this account as the startup account for the SQL Server Agent. Restart the
SQL Server agent and start the merge agent. This should fix your problem.
> Regards
> Chris R
|||Non-trusted domains can also be set up using the Guest account, but I would assume that this is even less likely to be allowed :-)
Regards,
Paul Ibison
|||I already know that the guest account has been disabled. There will be a two
way trust between the domains. I don't know how that got approved. There
must be some way to perform pull subscriptions in a trusted relationship
without matching accounts and passwords.
Paul
|||Paul,
I have used pass-through security, or the guest account, because of working
in a non-trusted environment but in your case you should be able to use
impersonation. Have a look at this article:
http://support.microsoft.com/?id=321822.
Regards,
Paul Ibison
|||I think I got it. We started having net work troubles once it appeared to be
working, so I'm not sure. The problem was that the SQL Server agent accounts
had the same name on both machines, but different passwords. Once I changed
the name of the account on one machine and gave it dbo access to the other
machine's database it looked like it was working. Most of the initial
snapshot was applied, but it failed due to the retry count. Before this
change, it never get started.
Thanks for the help.
Paul

Pull Merge Replication - Expand Column Size

I need to increase the size of a column in a table which is part of merge
replication.
Here's the approach I believe I need to take:
1) Use sp_repladdcolumn to create a new temporary column and use the
schema_script to populate my new column
with the values from my old column.
2) Use sp_repldropcolumn to drop the old column
3) Use sp_repladdcolumn to create a new column using the "old column" name
and use the
schema_script to populate the new column with the values.
4) Use sp_repldropcolumn to drop the temporary column created in step 1.
Questions:
1) Do I need do these steps on both the publisher and subscriber?
2) Million Dollar Question - Will this work without having to reinitialize
all my subscribers?
Thanks for all your help!
Tina
1) just publisher
2) yes
Rgds,
Paul Ibison
[vbcol=seagreen]
|||The sp_repladdcolumn system stored procedure adds the dummy column but it
doesn't run the script I set for the schema_change_script parameter.
Is there something I'm missing?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:e2SwX4IsEHA.4008@.TK2MSFTNGP14.phx.gbl...
> 1) just publisher
> 2) yes
> Rgds,
> Paul Ibison
>
>
|||I have found in testing that this approach will NOT work when using a Push
ExchangeType ( 1 ). Replication makes the schema change on the subscriber
but the values in the column are lost.
"Tina Smith" <tb.smith@.earthlink.net> wrote in message
news:%23d9WM8HsEHA.1752@.TK2MSFTNGP14.phx.gbl...
> I need to increase the size of a column in a table which is part of merge
> replication.
> Here's the approach I believe I need to take:
> 1) Use sp_repladdcolumn to create a new temporary column and use the
> schema_script to populate my new column
> with the values from my old column.
> 2) Use sp_repldropcolumn to drop the old column
> 3) Use sp_repladdcolumn to create a new column using the "old column" name
> and use the
> schema_script to populate the new column with the values.
> 4) Use sp_repldropcolumn to drop the temporary column created in step 1.
> --
> Questions:
> 1) Do I need do these steps on both the publisher and subscriber?
> 2) Million Dollar Question - Will this work without having to reinitialize
> all my subscribers?
> Thanks for all your help!
> Tina
>
|||use sp_repladdcolumn to add a dummy column.
Then update the the value of the dummy column with the value of the column
whose data type you want to change. Do this on the publisher.
Then use sp_repldropcolumn to drop the column which you wish to modify.
Then use sp_repladdcolumn to add the column back in with the correct
datatype.
Then update the value of the new column with the value of the dummy column.
Do this on the publisher.
Then drop the dummy column using sp_repldropcolumn.
Do all the work on the publisher, you shouldn't have to do anything on the
subscriber. You do not have to reinitialize all of your subscribers.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Tina Smith" <tb.smith@.earthlink.net> wrote in message
news:%23d9WM8HsEHA.1752@.TK2MSFTNGP14.phx.gbl...
> I need to increase the size of a column in a table which is part of merge
> replication.
> Here's the approach I believe I need to take:
> 1) Use sp_repladdcolumn to create a new temporary column and use the
> schema_script to populate my new column
> with the values from my old column.
> 2) Use sp_repldropcolumn to drop the old column
> 3) Use sp_repladdcolumn to create a new column using the "old column" name
> and use the
> schema_script to populate the new column with the values.
> 4) Use sp_repldropcolumn to drop the temporary column created in step 1.
> --
> Questions:
> 1) Do I need do these steps on both the publisher and subscriber?
> 2) Million Dollar Question - Will this work without having to reinitialize
> all my subscribers?
> Thanks for all your help!
> Tina
>
|||Tina,
what is in the @.schema_change_script that you are
sending? This parameter shouldn't be nesessary for merge.
If it is a script to populate the column, try a simple
update statement instead (on the publisher).[vbcol=seagreen]
|||It's a script to populate the column with the values from the old column.
update CustomerContact
Set dummy = State
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:037e01c4b106$83c0b060$a401280a@.phx.gbl...
> Tina,
> what is in the @.schema_change_script that you are
> sending? This parameter shouldn't be nesessary for merge.
> If it is a script to populate the column, try a simple
> update statement instead (on the publisher).
>
|||I went with the following steps since I couldn't get the schema_script
parameter to work.
All went well on the Publisher, I now have a varchar(3) state column with
the data. I then ran the agent on the subscriber and ended up with a
varchar(3) state column BUT with no data. My subscriber is a PUSH (
exchangetype (1 ) ) for this publication so I don't see how it will work.
sp_repladdcolumn @.source_object = 'CustomerContact'
, @.column = 'Dummy'
, @.typetext = 'varchar(3)'
, @.publication_to_add = 'SAM_HomeOffice'
GO
update CustomerContact
set dummy = state
GO
sp_repldropcolumn @.source_object = 'CustomerContact'
, @.column = 'state'
GO
sp_repladdcolumn @.source_object = 'CustomerContact'
, @.column = 'State'
, @.typetext = 'varchar(3)'
, @.publication_to_add = 'SAM_HomeOffice'
GO
update CustomerContact
set state = dummy
GO
sp_repldropcolumn @.source_object = 'CustomerContact' , @.column = 'dummy'
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eko7LbMsEHA.1032@.TK2MSFTNGP10.phx.gbl...
> use sp_repladdcolumn to add a dummy column.
> Then update the the value of the dummy column with the value of the column
> whose data type you want to change. Do this on the publisher.
> Then use sp_repldropcolumn to drop the column which you wish to modify.
> Then use sp_repladdcolumn to add the column back in with the correct
> datatype.
> Then update the value of the new column with the value of the dummy
column.[vbcol=seagreen]
> Do this on the publisher.
> Then drop the dummy column using sp_repldropcolumn.
> Do all the work on the publisher, you shouldn't have to do anything on the
> subscriber. You do not have to reinitialize all of your subscribers.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Tina Smith" <tb.smith@.earthlink.net> wrote in message
> news:%23d9WM8HsEHA.1752@.TK2MSFTNGP14.phx.gbl...
merge[vbcol=seagreen]
name[vbcol=seagreen]
reinitialize
>
|||Pls try a simple update statement (on the publisher)
after the sp_repladdcolumn statement and miss out the
@.schema_change_script parameter.
TIA,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Sorry Tina,
ignore my last message as in your more recent post you
mentioned the -EXCHANGETYPE forcing uploading. Please try
putting the update statement in sp_addscriptexec as a
step after adding the column.
Rgds,
Paul Ibison