Can't start transactional replication SQL 2005. System.OutOfMemory Exception during start

We currently use log shipping to 2 other servers for reporting. We
want to use transactional replication instead, so that our reporting
databases are always available (when the restore agent runs the
database is unavailable for a few seconds) and to reduce the latency
between changes in the main database appearing in our reporting
database.
I've successfully got replication to work on a test scenario (with
only a few thousand rows) but when I try and get transactional
replication going on our production site I get a System.OutOfMemory
exception when performing the snapshot. It fails at about 40 percent
of one of our larger tables (157 million rows.) The database backup
is about 27GB.
The hardware has 4GB memory, 2 dual core x86 running 32bit Windows
Server 2003.
We are running SQL2005 Standard edtion SP2, but I had the same problem
on SP1.
I've tried the /3GB switch.
I've tried putting the distrubutor on the main server, and on a
seperate server, and on the reporting box.
I've tried pull and push subscriptions.
I'm going to try a row filter to pull only the most recent data (to
drop the number of rows) and see at what point it fails, but this is a
fairly long interative process. And we do need all the data in the
end.
I'm looking for advice, has anybody else had similar problems?
Is there a way to start replication from a restored database?
I'm considering installing more Memory - but am unsure how to estimate
how much more memory we need. Also our machines run fine with 4GB of
memory normally; I can't really afford to buy more memory just to
"start" the replication process going (but of course I guess this
depends on how much more I would need!)
Daniel Bryars
This is something that I would really like to have a closer look so let me
ask you a few questions:
1) Is snapshot.exe consuming a large amount of memory according to taskmgr?
2) Do you have a pagefile configured on the distributor machine? I have
heard of one case where disabling the pagefile seemed to be the cause of OOM
error in the snapshot agent. I suspect that this may have something to do
with the use of memory mapped file in the underlying BCP api but I was never
able to prove one way or another.
3) Do you have a large amount of blob column data?
If possible, you may also want to consider upgrading your software stack at
your distributor (OS + SQL Server) to 64bit as it is quite possible that you
are running out of address space range. Most dual-core processors should
already be capable of running in 64bit mode.
-Raymond
<bryars@.hotmail.com> wrote in message
news:1174821642.773195.15500@.e65g2000hsc.googlegro ups.com...
> We currently use log shipping to 2 other servers for reporting. We
> want to use transactional replication instead, so that our reporting
> databases are always available (when the restore agent runs the
> database is unavailable for a few seconds) and to reduce the latency
> between changes in the main database appearing in our reporting
> database.
> I've successfully got replication to work on a test scenario (with
> only a few thousand rows) but when I try and get transactional
> replication going on our production site I get a System.OutOfMemory
> exception when performing the snapshot. It fails at about 40 percent
> of one of our larger tables (157 million rows.) The database backup
> is about 27GB.
> The hardware has 4GB memory, 2 dual core x86 running 32bit Windows
> Server 2003.
> We are running SQL2005 Standard edtion SP2, but I had the same problem
> on SP1.
> I've tried the /3GB switch.
> I've tried putting the distrubutor on the main server, and on a
> seperate server, and on the reporting box.
> I've tried pull and push subscriptions.
> I'm going to try a row filter to pull only the most recent data (to
> drop the number of rows) and see at what point it fails, but this is a
> fairly long interative process. And we do need all the data in the
> end.
> I'm looking for advice, has anybody else had similar problems?
> Is there a way to start replication from a restored database?
> I'm considering installing more Memory - but am unsure how to estimate
> how much more memory we need. Also our machines run fine with 4GB of
> memory normally; I can't really afford to buy more memory just to
> "start" the replication process going (but of course I guess this
> depends on how much more I would need!)
> Daniel Bryars
>

cant start the sql server services

Hi all

After i configure the Merge Replication, my sql server services wont start again.

The error show that "The request failed for the service did not respone in time fashion. Consult the event log or other application error logs for detail"

>>The error show that "The request failed for the service did not respone in time fashion. Consult the event log or other application error logs for detail"

Did you?

|||what is that mean actually/|||Did you consult with sql server log?|||

Go to C:\MS SQL Server\MSSQL4 (or 3 something like that)

There find a LOG folder and several log files which should tell you what exactly happend and why the service is not running.

Also check if you have set the services to start automatically or manually.

Can't start replication agent

Hi,
I am using snapshot replication and want to create pull /
push replication but I always encountered same problem :
Login failed for user . . .
I think I had already set my user as an Administrator but
it didn't work.
Thanks in advance !
Michael Rawi
Michael,
in the subscriber database, right-click the distribution agent and select
properties.
On the security tab, choose to impersonate the SQL Server Agent.
You can change the service accounts to use the same domain user or have the
windows login of the subscriber's agent as a separate login on the
publisher.
HTH,
Paul Ibison
sql

Can't setup replication: Encryption error using CryptProtectData?

I am trying to setup replication but am having the following issues
Publisher:
SQL 2005 SP1 Enterprise Cluster (Active, Passive)
Subscriber:
SQL 2005 SP1 Enterprise


When trying to create a publication or subsubscription running under a Windows Domain account the following error message appears:

Replication-Replication Distribution Subsystem: agent ECHO\ECHO-InsDB-INS_CMS_PUB-HARPO-58 failed. Unable to start execution of step 2 (reason: Error authenticating proxy DETINI\srvRep_user, system error: Logon failure: unknown user name or bad password.). The step failed.

Further in the SQL logs we find that the above message is due to:

[298] SQLServer Error: 22046, Encryption error using CryptProtectData. [SQLSTATE 42000]

It appears that when Credentials for the agent proxy are being created that this message occurs. So when the SP that tries to run the agent (snapshot or distrobution) is called it always returns incorrect password details.

The Credentials do appear to be created, they are visible via SQL Studio, but you can't see the password. Changing the password via SQL Studio does not work as the Credentials are recreated every time.

I have checked the following:

MSDTC is running correctly
The Domain Account has the correct privileges to run these services
Regenerated the Service Master Key
Created Master Keys in each database effected by replication
Ensure SQL Service accounts have access to decrypt the Service Master Key
I can create a Publication and Subscription using the local system account on the cluster

Does anyone have any idea ?

Chris

Chirs,

My testing environment is the same as yours - the publisher/distributer is clustered SQL2005 enterprise with SP1 on windows 2003, and the subscriber is SQL2005 enterprise on 2003 server, and I have got the same error when I was trying to run the creating snapshot job. The only workaround I found was to put the distributor on the same server as the publisher, and use a local directory for shapshot folder, which was not ideal, but at least I could continue with my test. Then I had the same error again when I ran the push distribution job using a domain ammount. I had to change the security to use SQL agent account, which is a domain admin account.

I would like to know how to avoid the error too, since I want the distributor to be on a different machine than the publisher, and limit the rights of the credentials on the production environments.

Ying

sql

Can't setup replication: Encryption error using CryptProtectData?

I am trying to setup replication but am having the following issues
Publisher:
SQL 2005 SP1 Enterprise Cluster (Active, Passive)
Subscriber:
SQL 2005 SP1 Enterprise


When trying to create a publication or subsubscription running under a Windows Domain account the following error message appears:

Replication-Replication Distribution Subsystem: agent ECHO\ECHO-InsDB-INS_CMS_PUB-HARPO-58 failed. Unable to start execution of step 2 (reason: Error authenticating proxy DETINI\srvRep_user, system error: Logon failure: unknown user name or bad password.). The step failed.

Further in the SQL logs we find that the above message is due to:

[298] SQLServer Error: 22046, Encryption error using CryptProtectData. [SQLSTATE 42000]

It appears that when Credentials for the agent proxy are being created that this message occurs. So when the SP that tries to run the agent (snapshot or distrobution) is called it always returns incorrect password details.

The Credentials do appear to be created, they are visible via SQL Studio, but you can't see the password. Changing the password via SQL Studio does not work as the Credentials are recreated every time.

I have checked the following:

MSDTC is running correctly
The Domain Account has the correct privileges to run these services
Regenerated the Service Master Key
Created Master Keys in each database effected by replication
Ensure SQL Service accounts have access to decrypt the Service Master Key
I can create a Publication and Subscription using the local system account on the cluster

Does anyone have any idea ?

Chris

Chirs,

My testing environment is the same as yours - the publisher/distributer is clustered SQL2005 enterprise with SP1 on windows 2003, and the subscriber is SQL2005 enterprise on 2003 server, and I have got the same error when I was trying to run the creating snapshot job. The only workaround I found was to put the distributor on the same server as the publisher, and use a local directory for shapshot folder, which was not ideal, but at least I could continue with my test. Then I had the same error again when I ran the push distribution job using a domain ammount. I had to change the security to use SQL agent account, which is a domain admin account.

I would like to know how to avoid the error too, since I want the distributor to be on a different machine than the publisher, and limit the rights of the credentials on the production environments.

Ying

Can't setup replication

Initially, when I started to setup replication I received the error message ~
"one or more articles are published ...", so I issued the following commands
to remove replication settings from the DB:
sp_configure 'allow',1
GO
reconfigure with override
GO
USE ImageCraft
GO
sp_removedbreplication 'IMAGECRAFT'
GO
UPDATE sysobjects set replinfo=0 WHERE replinfo<>0
GO
Drop table MSdynamicsnapshotjobs
Drop table MSdynamicsnapshotviews
Drop table MSmerge_altsyncpartners
Drop table MSmerge_contents
Drop table MSmerge_delete_conflicts
Drop table MSmerge_errorlineage
Drop table MSmerge_genhistory
Drop table MSmerge_replinfo
Drop table MSmerge_tombstone
Drop table MSrepl_identity_range
Drop table sysmergearticles
Drop table sysmergepublications
Drop table sysmergeschemaarticles
Drop table sysmergeschemachange
Drop table sysmergesubscriptions
Drop table sysmergesubsetfilters
GO
sp_configure 'allow',0
GO
reconfigure with override
GO
Appearently, I overkilled it because I get an error message I've never seen
before when adding a table to the replication setup:
SQL Server Enterprise Manager could not initialize an article based on table
'BusinessUnit'.
Error 208: Invalid object name 'sysmergearticles'.
Invalid object name 'sysmergeschemaarticles'.
Invalid object name 'sysmergearticles'.
Invalid object name 'sysmergeschmaarticles'.
Could not use view or function 'sysmergeextendedarticlesview' because of
binding errors.
Could not use view or function 'sysmergeextendedarticlesview' because of
binding errors.
I did a restore on the I am trying to create the replication for, but still
get the same errors.
Any suggestions?
can you disable this database for merge publishing and then try to re-enable
it. Also what version of SQL are you running?
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
"Steven" <Steven@.discussions.microsoft.com> wrote in message
news:1838D345-A23E-4CB6-A122-3736554334BE@.microsoft.com...
> Initially, when I started to setup replication I received the error
> message ~
> "one or more articles are published ...", so I issued the following
> commands
> to remove replication settings from the DB:
> sp_configure 'allow',1
> GO
> reconfigure with override
> GO
> USE ImageCraft
> GO
> sp_removedbreplication 'IMAGECRAFT'
> GO
> UPDATE sysobjects set replinfo=0 WHERE replinfo<>0
> GO
> Drop table MSdynamicsnapshotjobs
> Drop table MSdynamicsnapshotviews
> Drop table MSmerge_altsyncpartners
> Drop table MSmerge_contents
> Drop table MSmerge_delete_conflicts
> Drop table MSmerge_errorlineage
> Drop table MSmerge_genhistory
> Drop table MSmerge_replinfo
> Drop table MSmerge_tombstone
> Drop table MSrepl_identity_range
> Drop table sysmergearticles
> Drop table sysmergepublications
> Drop table sysmergeschemaarticles
> Drop table sysmergeschemachange
> Drop table sysmergesubscriptions
> Drop table sysmergesubsetfilters
> GO
> sp_configure 'allow',0
> GO
> reconfigure with override
> GO
> Appearently, I overkilled it because I get an error message I've never
> seen
> before when adding a table to the replication setup:
> SQL Server Enterprise Manager could not initialize an article based on
> table
> 'BusinessUnit'.
> Error 208: Invalid object name 'sysmergearticles'.
> Invalid object name 'sysmergeschemaarticles'.
> Invalid object name 'sysmergearticles'.
> Invalid object name 'sysmergeschmaarticles'.
> Could not use view or function 'sysmergeextendedarticlesview' because of
> binding errors.
> Could not use view or function 'sysmergeextendedarticlesview' because of
> binding errors.
> I did a restore on the I am trying to create the replication for, but
> still
> get the same errors.
> Any suggestions?
>
|||I tried this, but no success. The system tables required for replication
have been deleted and I'm not sure how to bring them back. This seems to be
crux anyways. Any further thoughts?
"Narayana Vyas Kondreddi" wrote:

> Try this. Using sp_dboption set 'published' and 'merge publish' options to
> false.
> Then from the publisher properties dialog box in enterprise manager, enable
> this database again for replication.
> Then try to setup a publication and see if it works.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Steven" <Steven@.discussions.microsoft.com> wrote in message
> news:1838D345-A23E-4CB6-A122-3736554334BE@.microsoft.com...
>
>
|||I tried this too, but no success. I seem to have deleted the system tables
required for replication and need to bring them back.
"Hilary Cotter" wrote:

> can you disable this database for merge publishing and then try to re-enable
> it. Also what version of SQL are you running?
> --
> 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
> "Steven" <Steven@.discussions.microsoft.com> wrote in message
> news:1838D345-A23E-4CB6-A122-3736554334BE@.microsoft.com...
>
>

Can't re-submit a deleted conflicted row

Hello,
I have a merge replication which use identity range management (for some
tables).
I have a table that inserted a row on the publisher and tried to transfer it
to subscriber. The subscriber got an error when trying to insert it (by the
way, I use MSDE on subscriber and MSmerge_contents and MSmerge_tombstone are
huge and the DB limit of 2 GB arrive fast - it was nice that the system
tables are not included in the 2 GB limit... I decided to move that 2 tables
in another DB and it works nice). As I said, the subscriber gave an error
(different reasons) and the conflict resolver decided to delete the row from
publisher, because it can't insert in on subscriber !?! Anyway seems to be ok
because the row i have in conflict viewer, that seems that I can re-insert
it. But when I try to re-insert it, it gave me an error because the identity
ranges changed and the old value (of deleted row) is not proper for the new
constraints of identity... I supposed that SQL will deactivate the
constraint, as it does during replication...
Any ideea how to re-insert it? Or I have to "manually" (programatically)
de-activate it, insert it from conflict table and re-activate it?
Thanks for any suugestion.
Catalin
Hello Catalin,
I have something for you. I've tried reaching you through phone or email,
without success. Email me at chris lafrance at h_tm__l dot com.
Cheers
"Catalin NASTAC" wrote:

> Hello,
> I have a merge replication which use identity range management (for some
> tables).
> I have a table that inserted a row on the publisher and tried to transfer it
> to subscriber. The subscriber got an error when trying to insert it (by the
> way, I use MSDE on subscriber and MSmerge_contents and MSmerge_tombstone are
> huge and the DB limit of 2 GB arrive fast - it was nice that the system
> tables are not included in the 2 GB limit... I decided to move that 2 tables
> in another DB and it works nice). As I said, the subscriber gave an error
> (different reasons) and the conflict resolver decided to delete the row from
> publisher, because it can't insert in on subscriber !?! Anyway seems to be ok
> because the row i have in conflict viewer, that seems that I can re-insert
> it. But when I try to re-insert it, it gave me an error because the identity
> ranges changed and the old value (of deleted row) is not proper for the new
> constraints of identity... I supposed that SQL will deactivate the
> constraint, as it does during replication...
> Any ideea how to re-insert it? Or I have to "manually" (programatically)
> de-activate it, insert it from conflict table and re-activate it?
> Thanks for any suugestion.
> Catalin

Can't replicate with DB on LAN

Hi all,
I'm doing merge replication sql2000 to sql2000, handled programatically
in my c# application.
Everything is working perfectly when the publisher and subscriber are
on the same SQL Server registration, but not when I try to replicate
with SQL Server on our server.
The Server DB registration is listed in my local Enterprise Manager.
In my application, the working version of my setup includes these
variables:
// Publisher Info
private string strPublisher = "BCN"; //the local machine name
private string strPublisherAddress ="localhost"; // the local machine
private string strPublisherDatabase ="Pub1";
But if I simply change the publisher name:
// Publisher Info
private string strPublisher = "MON"; //the server machine name
I get an unspecified error.
- The DB Instance names are identical to the server names.
- Using TCP-IP Network protocol, tried Multi-protocol to no avail.
Can anyone help? I am really stuck at this point.
Thanks,
JJ
Update - If I switch away from TCP-IP to Default Network, it works.
I wonder how well it will work when I go back to TCP-IP when i test the
app's connection over a WAN.
JJ

Can't replicate with cellular modem.

Using Merge replication.
Publisher is Sql Server 2000 SP4, subscribers are laptops using MSDE SP4.
Using anonymous subscribers that use the SQLMerge object to invoke
replication. Our laptops are everywhere. Some use highspeed internet, some
dialup and some cellular modems.
When using the Aircard (Wireless PC card cellular modem), after 120 seconds
the connection is being dropped. This is happening during the SQL server
initial synchronize process (and perhaps during subsequent merge processes
though they wouldn't likely be more than 120 seconds).
We of course thought it was the Aircard dropping the connection thinking
there was no activity but even when we opened a browser and surfed the
Internet during the synchronization process, it still dropped the connection.
When we simply surfed the Internet (without the synchronize process running)
it never dropped the connection.
Is there a fix? Do I need to have a function going in another thread to fake
a heartbeat to the publisher Sql server from the laptop?
Thanks!
Hello Buzz,
This is typically something you set on the modem itself. Check the configuration
of it to see if the is an auto disconnection after x minutes of inactivity.
Hilary

> Using Merge replication.
> Publisher is Sql Server 2000 SP4, subscribers are laptops using MSDE
> SP4.
> Using anonymous subscribers that use the SQLMerge object to invoke
> replication. Our laptops are everywhere. Some use highspeed internet,
> some dialup and some cellular modems.
> When using the Aircard (Wireless PC card cellular modem), after 120
> seconds the connection is being dropped. This is happening during the
> SQL server initial synchronize process (and perhaps during subsequent
> merge processes though they wouldn't likely be more than 120 seconds).
> We of course thought it was the Aircard dropping the connection
> thinking
> there was no activity but even when we opened a browser and surfed the
> Internet during the synchronization process, it still dropped the
> connection.
> When we simply surfed the Internet (without the synchronize process
> running)
> it never dropped the connection.
> Is there a fix? Do I need to have a function going in another thread
> to fake a heartbeat to the publisher Sql server from the laptop?
> Thanks!
>
|||My client has been working with the phone company on this issue. I am not
working on it directly. However, my understanding is from them that they've
rulled out the settings of the modem being an issue and supposedly the phone
company has had issues with Sql Server before. I was suppose to check on
something called Sql Server latency but that only seems to point to postings
about the time between merge agents which is totally unrelated.
Thanks,
Buzz
"Hilary Cotter" wrote:

> Hello Buzz,
> This is typically something you set on the modem itself. Check the configuration
> of it to see if the is an auto disconnection after x minutes of inactivity.
> Hilary
>
>
|||Hello Buzz,
I agree with Hilary. Based on the symptom, it seems to be network issue
related to wireless modem under high load to replication. You may want to
download or copy big files via this network or use special tool from phone
company to check the error code rate.
It is not likely SQL replicaiton will drop the connection because it is
based on higher protocols such as TCP which is not related to modem
directly.
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
| Thread-Topic: Can't replicate with cellular modem.
| thread-index: AcXNH3N3SFSz00jSR8q0eK8whAmWuw==
| X-WBNR-Posting-Host: 68.145.59.227
| From: "=?Utf-8?B?QnV6eg==?=" <buzz@.online.nospam>
| References: <B88E11B9-6828-4098-B476-A8BEBB1CB736@.microsoft.com>
<56bbe4560a878c79b0e0191d820@.msnews.microsoft.com>
| Subject: Re: Can't replicate with cellular modem.
| Date: Sun, 9 Oct 2005 15:19:01 -0700
| Lines: 49
| Message-ID: <E36785DD-DA79-4A66-B81C-5053E82CB290@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.replication
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.replication:65767
| X-Tomcat-NG: microsoft.public.sqlserver.replication
|
| My client has been working with the phone company on this issue. I am not
| working on it directly. However, my understanding is from them that
they've
| rulled out the settings of the modem being an issue and supposedly the
phone
| company has had issues with Sql Server before. I was suppose to check on
| something called Sql Server latency but that only seems to point to
postings
| about the time between merge agents which is totally unrelated.
|
| Thanks,
| Buzz
|
| "Hilary Cotter" wrote:
|
| > Hello Buzz,
| >
| > This is typically something you set on the modem itself. Check the
configuration
| > of it to see if the is an auto disconnection after x minutes of
inactivity.
| >
| > Hilary
| >
| > > Using Merge replication.
| > >
| > > Publisher is Sql Server 2000 SP4, subscribers are laptops using MSDE
| > > SP4.
| > >
| > > Using anonymous subscribers that use the SQLMerge object to invoke
| > > replication. Our laptops are everywhere. Some use highspeed internet,
| > > some dialup and some cellular modems.
| > >
| > > When using the Aircard (Wireless PC card cellular modem), after 120
| > > seconds the connection is being dropped. This is happening during the
| > > SQL server initial synchronize process (and perhaps during subsequent
| > > merge processes though they wouldn't likely be more than 120 seconds).
| > >
| > > We of course thought it was the Aircard dropping the connection
| > > thinking
| > > there was no activity but even when we opened a browser and surfed the
| > > Internet during the synchronization process, it still dropped the
| > > connection.
| > > When we simply surfed the Internet (without the synchronize process
| > > running)
| > > it never dropped the connection.
| > > Is there a fix? Do I need to have a function going in another thread
| > > to fake a heartbeat to the publisher Sql server from the laptop?
| > >
| > > Thanks!
| > >
| >
| >
| >
|
|||Okay, with a bit more experimentation and googling, we think it is the FTP
part of the merge replication that is dieing on us. We tried FTPing directly
and got "the connection with the server was reset". Googling showed that
people said it was because the client was running in passive, not active mode.
Another poster from 2001 said that he discovered that merge repliction FTP's
some files, processes some files and then FTP's some more. Problem with this
is that the server can shut the FTP down.
Does Merge ActiveX use passive FTP? If so, how to work around it? We are
using IIS's FTP server, can the server be set to wait longer?
"Peter Yang [MSFT]" wrote:

> Hello Buzz,
> I agree with Hilary. Based on the symptom, it seems to be network issue
> related to wireless modem under high load to replication. You may want to
> download or copy big files via this network or use special tool from phone
> company to check the error code rate.
> It is not likely SQL replicaiton will drop the connection because it is
> based on higher protocols such as TCP which is not related to modem
> directly.
> Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> --
> | Thread-Topic: Can't replicate with cellular modem.
> | thread-index: AcXNH3N3SFSz00jSR8q0eK8whAmWuw==
> | X-WBNR-Posting-Host: 68.145.59.227
> | From: "=?Utf-8?B?QnV6eg==?=" <buzz@.online.nospam>
> | References: <B88E11B9-6828-4098-B476-A8BEBB1CB736@.microsoft.com>
> <56bbe4560a878c79b0e0191d820@.msnews.microsoft.com>
> | Subject: Re: Can't replicate with cellular modem.
> | Date: Sun, 9 Oct 2005 15:19:01 -0700
> | Lines: 49
> | Message-ID: <E36785DD-DA79-4A66-B81C-5053E82CB290@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.replication
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.replication:65767
> | X-Tomcat-NG: microsoft.public.sqlserver.replication
> |
> | My client has been working with the phone company on this issue. I am not
> | working on it directly. However, my understanding is from them that
> they've
> | rulled out the settings of the modem being an issue and supposedly the
> phone
> | company has had issues with Sql Server before. I was suppose to check on
> | something called Sql Server latency but that only seems to point to
> postings
> | about the time between merge agents which is totally unrelated.
> |
> | Thanks,
> | Buzz
> |
> | "Hilary Cotter" wrote:
> |
> | > Hello Buzz,
> | >
> | > This is typically something you set on the modem itself. Check the
> configuration
> | > of it to see if the is an auto disconnection after x minutes of
> inactivity.
> | >
> | > Hilary
> | >
> | > > Using Merge replication.
> | > >
> | > > Publisher is Sql Server 2000 SP4, subscribers are laptops using MSDE
> | > > SP4.
> | > >
> | > > Using anonymous subscribers that use the SQLMerge object to invoke
> | > > replication. Our laptops are everywhere. Some use highspeed internet,
> | > > some dialup and some cellular modems.
> | > >
> | > > When using the Aircard (Wireless PC card cellular modem), after 120
> | > > seconds the connection is being dropped. This is happening during the
> | > > SQL server initial synchronize process (and perhaps during subsequent
> | > > merge processes though they wouldn't likely be more than 120 seconds).
> | > >
> | > > We of course thought it was the Aircard dropping the connection
> | > > thinking
> | > > there was no activity but even when we opened a browser and surfed the
> | > > Internet during the synchronization process, it still dropped the
> | > > connection.
> | > > When we simply surfed the Internet (without the synchronize process
> | > > running)
> | > > it never dropped the connection.
> | > > Is there a fix? Do I need to have a function going in another thread
> | > > to fake a heartbeat to the publisher Sql server from the laptop?
> | > >
> | > > Thanks!
> | > >
> | >
> | >
> | >
> |
>
|||Hello Buzz,
FTP is used for the snapshot download, both during the initial application
and when the subscription is re-initialized. All other synchronizations use
SQL packets over TCP/IP to transfer data between Publisher and Subscriber.
Did you publish data over Internet? Or did you manually configure
publication to store snapshot via FTP?
You may consider create VPN connection between server/client instead of
using Interent publication.
MergePublication2 control could be used to configruation FTP feature for
snapshot on publisher.
SQL replication can only use active mode FTP. It is not possible to do
passive MODE FTP using SQL replication. The INTERNET_FLAG_PASSIVE has to
be set in FTP connection to do passive mode Ftp. However, this option is
not implemented in SQL replication.
Anyway, it is weird that FTP connection reset could drop WAN connection
because it is higher level ftp protocol based on TCPIP protocols. WAN
connection is based on lower level such as data link layer. You may want to
use different ISP/wireless modem to test this.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
| Thread-Topic: Can't replicate with cellular modem.
| thread-index: AcXPoPJCdBSSfQOHQ1iA5xqQ6wGeXg==
| X-WBNR-Posting-Host: 68.145.59.227
| From: "=?Utf-8?B?QnV6eg==?=" <buzz@.online.nospam>
| References: <B88E11B9-6828-4098-B476-A8BEBB1CB736@.microsoft.com>
<56bbe4560a878c79b0e0191d820@.msnews.microsoft.com>
<E36785DD-DA79-4A66-B81C-5053E82CB290@.microsoft.com>
<4kdWUTWzFHA.424@.TK2MSFTNGXA02.phx.gbl>
| Subject: Re: Can't replicate with cellular modem.
| Date: Wed, 12 Oct 2005 19:51:01 -0700
| Lines: 124
| Message-ID: <688D9A3C-5FC2-4E96-9D3A-88231CCC5DC2@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.replication
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.replication:17070
| X-Tomcat-NG: microsoft.public.sqlserver.replication
|
| Okay, with a bit more experimentation and googling, we think it is the
FTP
| part of the merge replication that is dieing on us. We tried FTPing
directly
| and got "the connection with the server was reset". Googling showed that
| people said it was because the client was running in passive, not active
mode.
|
| Another poster from 2001 said that he discovered that merge repliction
FTP's
| some files, processes some files and then FTP's some more. Problem with
this
| is that the server can shut the FTP down.
|
| Does Merge ActiveX use passive FTP? If so, how to work around it? We are
| using IIS's FTP server, can the server be set to wait longer?
|
| "Peter Yang [MSFT]" wrote:
|
| > Hello Buzz,
| >
| > I agree with Hilary. Based on the symptom, it seems to be network issue
| > related to wireless modem under high load to replication. You may want
to
| > download or copy big files via this network or use special tool from
phone
| > company to check the error code rate.
| >
| > It is not likely SQL replicaiton will drop the connection because it is
| > based on higher protocols such as TCP which is not related to modem
| > directly.
| >
| > Regards,
| >
| > Peter Yang
| > MCSE2000/2003, MCSA, MCDBA
| > Microsoft Online Partner Support
| >
| > When responding to posts, please "Reply to Group" via your newsreader
so
| > that others may learn and benefit from your issue.
| >
| > ================================================== ===
| >
| >
| >
| > This posting is provided "AS IS" with no warranties, and confers no
rights.
| >
| >
| > --
| > | Thread-Topic: Can't replicate with cellular modem.
| > | thread-index: AcXNH3N3SFSz00jSR8q0eK8whAmWuw==
| > | X-WBNR-Posting-Host: 68.145.59.227
| > | From: "=?Utf-8?B?QnV6eg==?=" <buzz@.online.nospam>
| > | References: <B88E11B9-6828-4098-B476-A8BEBB1CB736@.microsoft.com>
| > <56bbe4560a878c79b0e0191d820@.msnews.microsoft.com>
| > | Subject: Re: Can't replicate with cellular modem.
| > | Date: Sun, 9 Oct 2005 15:19:01 -0700
| > | Lines: 49
| > | Message-ID: <E36785DD-DA79-4A66-B81C-5053E82CB290@.microsoft.com>
| > | MIME-Version: 1.0
| > | Content-Type: text/plain;
| > | charset="Utf-8"
| > | Content-Transfer-Encoding: 7bit
| > | X-Newsreader: Microsoft CDO for Windows 2000
| > | Content-Class: urn:content-classes:message
| > | Importance: normal
| > | Priority: normal
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| > | Newsgroups: microsoft.public.sqlserver.replication
| > | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| > | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA03.phx.gbl
| > | Xref: TK2MSFTNGXA02.phx.gbl
microsoft.public.sqlserver.replication:65767
| > | X-Tomcat-NG: microsoft.public.sqlserver.replication
| > |
| > | My client has been working with the phone company on this issue. I am
not
| > | working on it directly. However, my understanding is from them that
| > they've
| > | rulled out the settings of the modem being an issue and supposedly
the
| > phone
| > | company has had issues with Sql Server before. I was suppose to check
on
| > | something called Sql Server latency but that only seems to point to
| > postings
| > | about the time between merge agents which is totally unrelated.
| > |
| > | Thanks,
| > | Buzz
| > |
| > | "Hilary Cotter" wrote:
| > |
| > | > Hello Buzz,
| > | >
| > | > This is typically something you set on the modem itself. Check the
| > configuration
| > | > of it to see if the is an auto disconnection after x minutes of
| > inactivity.
| > | >
| > | > Hilary
| > | >
| > | > > Using Merge replication.
| > | > >
| > | > > Publisher is Sql Server 2000 SP4, subscribers are laptops using
MSDE
| > | > > SP4.
| > | > >
| > | > > Using anonymous subscribers that use the SQLMerge object to invoke
| > | > > replication. Our laptops are everywhere. Some use highspeed
internet,
| > | > > some dialup and some cellular modems.
| > | > >
| > | > > When using the Aircard (Wireless PC card cellular modem), after
120
| > | > > seconds the connection is being dropped. This is happening
during the
| > | > > SQL server initial synchronize process (and perhaps during
subsequent
| > | > > merge processes though they wouldn't likely be more than 120
seconds).
| > | > >
| > | > > We of course thought it was the Aircard dropping the connection
| > | > > thinking
| > | > > there was no activity but even when we opened a browser and
surfed the
| > | > > Internet during the synchronization process, it still dropped the
| > | > > connection.
| > | > > When we simply surfed the Internet (without the synchronize
process
| > | > > running)
| > | > > it never dropped the connection.
| > | > > Is there a fix? Do I need to have a function going in another
thread
| > | > > to fake a heartbeat to the publisher Sql server from the laptop?
| > | > >
| > | > > Thanks!
| > | > >
| > | >
| > | >
| > | >
| > |
| >
| >
|

Cant remove subscription info

Dear All,

I deleted a replication in the PROD server. However, the subscription info still exists in the subscription folder in the DR server.

Can anyone tells me how to remove the subscription info? (When I delete the publication, there's a message saying we need to manually delete the subscription info in the subscriber)

I'd tried restart the SQLAgent service, Disabling Publishing in PROD server but it's useless.

thanks first.

rgds.The subscriber may be not online when u dropped the publication.
Run sp_dropsubscriber at the publisher, see syntax in BOL. If it won't work; manually delete the subscription from the subscriber using Enterprise-Manager.|||Thanks TALAT.

However, the sp_dropsubscriber won't work, i keep hiting error 14048.

Anyway, my real interest is how to 'manually delete the subscription in EM' as mentioned by you...

Details:
At PROD server, publication is drop, there's NO item in Publications & Subscriptions Folder.

At DR server, there's no item in Publications Folder, BUT there's 1 item (subscription) in Subscriptions Folder.

Can you pls provide step-by-step details in how to delete the subscription info using EM?

thanks...|||Simply right click the subscription in the subscriber and choose delete. If u get error; run sp_mergesubscription_cleanup (if u r using merge replication) in the subscription db. It would clear all the subscription info.|||Thanks again TALAT,

Extra info: Previously I'm using Transactional Replication. At the Subscriber, in the Subscriptions Folder, it shows 1 item of subscription with the Type: Push

When I right-click on either the Subscriptions Folder or the Subscription itself, there's no Delete option to choose from, this is what bugging me.

Any idea?

Thanks first.|||http://support.microsoft.com/default.aspx?scid=kb;en-us;324401 to remove replication manually.|||Thanks Satya, the link is useful.

What's really bugging me is why there's no simple way to do this in EM?

rgds|||Sometimes its better to follow system supplied SPs rather than depending upon Lazy GUI tools, as far as my exp. conveys.

can't modify stored proc which is in a publication

Hi

SQL 2005 sp1 - merge replication - HTTPS.

We have 2 publications for the database - one which has subscription.SyncType = SubscriptionSyncType.Automatic
and another with SyncType = SubscriptionSyncType.None.

The first publication is there so we can add new stored procs etc, the second contains the initial schema and the data.

When we try to modify a proc which is in the publication with SyncType = Automatic, the query never returns.

This is most urgent - thanks for your help.

Bruce

Do we have one or two subscriptions to publication one that contains the stored proc as a merge article in the publication?

|||

There are 2 subscriptions - one for each publication - but the proc is only in one publication

regards
Bruce

|||

1. Back to your original question - could the query (modifying the SP) be locked by another process so that it won't return?

2. Let us try to isolate this problem. If you can - colon the publication database, this publication (no second one), and subscribing database; does the same problem still occur?

Thanks.

|||

1. I doubt it..

2. 'colon the publication database' - what do you mean ?

thanks

|||

Create another publication database and create the same set of user tables/views/SPs. Just create one publication which includes the SP and other merge articles. See if you still can repro this issue with a single publication.

Thanks.

|||

We will try this tomorrow - but it's worth pointing out the following

a) the publication with SyncType = none -- has a few hundred stored procs - I can change any of these no worries

b) the publication with SyncType = automatic - initially just has 1 stored proc - so we can create the publication! - it's the procs we've added to this which we can't subsequently change.

Regards
Bruce

|||

Ok

I stripped it right back.

a. created a new database

b. added one proc

c. created the merge publication , snapshot etc

d. initalized ok

e. The query never returns....

Thanks
Bruce

|||

Can you share the proc prototype/definition (also I assume you use "ALTER PROCEDURE" to modify it)? I want to repro this case in house.

Thanks.

|||

Absolutely - it's a mindnumbing place-holder - it's just there so we can create the publication..

Don't laugh.

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

--

-- This is the proc for the 'extras' subscription

--

--

ALTER PROCEDURE [dbo].[aaaaPlaceHolder]

AS

BEGIN

SET NOCOUNT ON;

SELECT 'fish', 'cow', 'dog', 'elephant', 'pig', 'moo', 's', 't', 'dog', 'sheep'

END

|||

Bruce,

Thanks for your help. I have repro-ed this scenario on my machine. I need to work with my peers to diagnoise the real cause of it. Should get back to you once I have the answer.

Regards,

Leo

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

|||

As a temp solution - a dummy table can be created to join the SP in the same publication so that SP can be modified and replicated to the subscriber.

Leo

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

|||

Hi

I'll try it - has this been logged as a bug ? If so, how do I keep track of it ?

Thanks

|||

The bug was definitely filed and once I heard the decision/status I post it immediately.

Thanks

|||

This issue/bug should be addressed in Yukon Service Pack 2.

Leo

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

can't modify stored proc which is in a publication

Hi

SQL 2005 sp1 - merge replication - HTTPS.

We have 2 publications for the database - one which has subscription.SyncType = SubscriptionSyncType.Automatic
and another with SyncType = SubscriptionSyncType.None.

The first publication is there so we can add new stored procs etc, the second contains the initial schema and the data.

When we try to modify a proc which is in the publication with SyncType = Automatic, the query never returns.

This is most urgent - thanks for your help.

Bruce

Do we have one or two subscriptions to publication one that contains the stored proc as a merge article in the publication?

|||

There are 2 subscriptions - one for each publication - but the proc is only in one publication

regards
Bruce

|||

1. Back to your original question - could the query (modifying the SP) be locked by another process so that it won't return?

2. Let us try to isolate this problem. If you can - colon the publication database, this publication (no second one), and subscribing database; does the same problem still occur?

Thanks.

|||

1. I doubt it..

2. 'colon the publication database' - what do you mean ?

thanks

|||

Create another publication database and create the same set of user tables/views/SPs. Just create one publication which includes the SP and other merge articles. See if you still can repro this issue with a single publication.

Thanks.

|||

We will try this tomorrow - but it's worth pointing out the following

a) the publication with SyncType = none -- has a few hundred stored procs - I can change any of these no worries

b) the publication with SyncType = automatic - initially just has 1 stored proc - so we can create the publication! - it's the procs we've added to this which we can't subsequently change.

Regards
Bruce

|||

Ok

I stripped it right back.

a. created a new database

b. added one proc

c. created the merge publication , snapshot etc

d. initalized ok

e. The query never returns....

Thanks
Bruce

|||

Can you share the proc prototype/definition (also I assume you use "ALTER PROCEDURE" to modify it)? I want to repro this case in house.

Thanks.

|||

Absolutely - it's a mindnumbing place-holder - it's just there so we can create the publication..

Don't laugh.

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

--

-- This is the proc for the 'extras' subscription

--

--

ALTER PROCEDURE [dbo].[aaaaPlaceHolder]

AS

BEGIN

SET NOCOUNT ON;

SELECT 'fish', 'cow', 'dog', 'elephant', 'pig', 'moo', 's', 't', 'dog', 'sheep'

END

|||

Bruce,

Thanks for your help. I have repro-ed this scenario on my machine. I need to work with my peers to diagnoise the real cause of it. Should get back to you once I have the answer.

Regards,

Leo

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

|||

As a temp solution - a dummy table can be created to join the SP in the same publication so that SP can be modified and replicated to the subscriber.

Leo

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

|||

Hi

I'll try it - has this been logged as a bug ? If so, how do I keep track of it ?

Thanks

|||

The bug was definitely filed and once I heard the decision/status I post it immediately.

Thanks

|||

This issue/bug should be addressed in Yukon Service Pack 2.

Leo

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

Can't get rid of distribution db

I have a separate server that is used as the distributor. We have our
production replication using a db called DistributorProd on this server
(this was built several weeks ago). I was doing some testing, so I created a
second db called DistributorTest on this server. After I was done testing, I
used the Wizard to remove the test replication. For some reason the
DistributorTest db is still there and I can't delete it. It says that it's
being used by replication. Any ideas on how to get rid of this? Is it
possible that my PROD replication is somehow now using both the
DistributorProd and DistributorTest dbs?
Thanks
On 2004-12-07, RML51 <mike.lacy@.courts.state.mn.us> wrote:
> I have a separate server that is used as the distributor. We have our
> production replication using a db called DistributorProd on this server
> (this was built several weeks ago). I was doing some testing, so I created a
> second db called DistributorTest on this server. After I was done testing, I
> used the Wizard to remove the test replication. For some reason the
> DistributorTest db is still there and I can't delete it. It says that it's
> being used by replication. Any ideas on how to get rid of this? Is it
> possible that my PROD replication is somehow now using both the
> DistributorProd and DistributorTest dbs?
>
I presume you tried 'stop publishing' wizzard from the Replication menu?
Mike
"I can do it quick. I can do it cheap. I can do it well. Pick any two."
Mario Splivalo
msplival@.jagor.srce.hr