Cannot open database during merge replication

Hi All,
I have previously posted in the MSDN forums with no responses, so any
hints on this would be greatly appreciated.
I am replicating an SQL2005 express machine to SQL2005 standard
edition server using merge replication.
I have a simple VB application using ADO polling msMerge_history at
the subscriber every second so that i can show the end user the
progress of the replication.
When the subscription first subscribes and after the initial snapshot
is applied replication downloads all changes from the server that have
been changed since the snapshot was created.
Towards the end of this download faze the VB polling application fails
with "Cannot open database "DBName" requested by the login. The login
failed."
This connection fails 4 or 5 times and then normal polling resumes.
The scary thing is that sometimes when the polling connection fails
the merge replication does not complete and if i check
msMerge_Sessions the "runstatus" is stuck at 3 (InProgress).
1) Is the reason the ADO polling connection fails because of some sort
of lock between replication and reading the system tables?
2) Is replication getting stuck because of locks?
3) What can i do to get around this?
cheers,
Tim
The status event is the best way to monitor progress of your sync.
Merge replication does put schema and application locks on your tables,
including the sysmergerarticles table - this is probably what you are
seeing.
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
"Mar72Vin" <mar72vin@.gmail.com> wrote in message
news:1173826362.218126.53350@.l75g2000hse.googlegro ups.com...
> Hi All,
> I have previously posted in the MSDN forums with no responses, so any
> hints on this would be greatly appreciated.
> I am replicating an SQL2005 express machine to SQL2005 standard
> edition server using merge replication.
> I have a simple VB application using ADO polling msMerge_history at
> the subscriber every second so that i can show the end user the
> progress of the replication.
> When the subscription first subscribes and after the initial snapshot
> is applied replication downloads all changes from the server that have
> been changed since the snapshot was created.
> Towards the end of this download faze the VB polling application fails
> with "Cannot open database "DBName" requested by the login. The login
> failed."
> This connection fails 4 or 5 times and then normal polling resumes.
> The scary thing is that sometimes when the polling connection fails
> the merge replication does not complete and if i check
> msMerge_Sessions the "runstatus" is stuck at 3 (InProgress).
> 1) Is the reason the ADO polling connection fails because of some sort
> of lock between replication and reading the system tables?
> 2) Is replication getting stuck because of locks?
> 3) What can i do to get around this?
> cheers,
> Tim
>
|||Hi Hilary,
Thanks for the quick reply.
We are not using the replication component to start replication so we
can't use the "status event"
We run replmerg.exe independently so that replication can be started
via windows Task Scheduler. This way we do not need to have our
replication monitor open whilst replicating.
However when we do have our replication monitor open the replmerg.exe
disappears out of the taskmanager and leaves replication stuck at the
"in progress" status. This only happens after applying the initial
snapshot. (subsequent replications succeed)
The last message recorded to msMerge_History is usually "Web
Syncronization progress xx% Complete."
Why would the replmerge.exe just "give up" at this stage?
thanks for your time.
Cheers,
Tim
On Mar 14, 12:45 pm, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:[vbcol=seagreen]
> The status event is the best way to monitor progress of your sync.
> Merge replication does put schema and application locks on your tables,
> including the sysmergerarticles table - this is probably what you are
> seeing.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTShttp://www.indexserverfaq.com
> "Mar72Vin" <mar72...@.gmail.com> wrote in message
> news:1173826362.218126.53350@.l75g2000hse.googlegro ups.com...
>
>
>
>
|||Looks like i have been barking up the wrong tree. The connections from
the ado application are not causing this error.
I ran replmerg.exe and captured the results (see below), It looks like
replication is trying to use the SYSTEM login at some point to access
the database and it does not have access.
The SQL2005Express instance is running under the system account, I
can't see why it should have troubles connecting to this database.
Any Ideas?
cheers,
Tim
2007-03-14 04:56:34.347 Connecting to Subscriber 'BARNEYXP\MDSZINC'
2007-03-14 04:56:34.347 Connecting to OLE DB Subscriber at datasource:
'BARNEYXP\MDSZINC', location: '', catalog: 'MDS200703146',
providerstring: '' using provider 'SQLNCLI'
2007-03-14 04:56:36.811 Percent Complete: 0
2007-03-14 04:56:36.811 Category:AGENT
Source: BARNEYXP\MDSZINC
Number: 20052
Message: The process could not access database 'MDS200703146' on
server 'BARNEYXP\MDSZINC'.
2007-03-14 04:56:36.811 Percent Complete: 0
2007-03-14 04:56:36.811 Category:SQLSERVER
Source: BARNEYXP\MDSZINC
Number: 4060
Message: Cannot open database "MDS200703146" requested by the login.
The login failed.
2007-03-14 04:56:36.811 Percent Complete: 0
2007-03-14 04:56:36.811 Category:SQLSERVER
Source: BARNEYXP\MDSZINC
Number: 18456
Message: Login failed for user 'NT AUTHORITY\SYSTEM'.
|||It sounds like one of the accounts you are trying to connect to the
publisher/distributor/or subscriber is not defined. You will need to use an
account which is in the pal on the publisher and in the sysadmin or dbo_role
on the subscriber. It will need rights to read, and list files and folders
on the snapshot share.
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
"Mar72Vin" <mar72vin@.gmail.com> wrote in message
news:1173852824.327599.170440@.n59g2000hsh.googlegr oups.com...
> Looks like i have been barking up the wrong tree. The connections from
> the ado application are not causing this error.
> I ran replmerg.exe and captured the results (see below), It looks like
> replication is trying to use the SYSTEM login at some point to access
> the database and it does not have access.
> The SQL2005Express instance is running under the system account, I
> can't see why it should have troubles connecting to this database.
> Any Ideas?
> cheers,
> Tim
>
> 2007-03-14 04:56:34.347 Connecting to Subscriber 'BARNEYXP\MDSZINC'
> 2007-03-14 04:56:34.347 Connecting to OLE DB Subscriber at datasource:
> 'BARNEYXP\MDSZINC', location: '', catalog: 'MDS200703146',
> providerstring: '' using provider 'SQLNCLI'
> 2007-03-14 04:56:36.811 Percent Complete: 0
> 2007-03-14 04:56:36.811 Category:AGENT
> Source: BARNEYXP\MDSZINC
> Number: 20052
> Message: The process could not access database 'MDS200703146' on
> server 'BARNEYXP\MDSZINC'.
> 2007-03-14 04:56:36.811 Percent Complete: 0
> 2007-03-14 04:56:36.811 Category:SQLSERVER
> Source: BARNEYXP\MDSZINC
> Number: 4060
> Message: Cannot open database "MDS200703146" requested by the login.
> The login failed.
> 2007-03-14 04:56:36.811 Percent Complete: 0
> 2007-03-14 04:56:36.811 Category:SQLSERVER
> Source: BARNEYXP\MDSZINC
> Number: 18456
> Message: Login failed for user 'NT AUTHORITY\SYSTEM'.
>
sql