Newbie question on replication failiure

Hi,
Replication to a MS-access database fails last week.
It has always been working, but now I get this message:
DISTRIBUTION AGENT ERROR
Error : The process could not connect to Subscriber 'MAB-EC-NW'.
Error information: Microsoft JET Database Engine (number = -534709256)
The Microsoft Jet database engine cannot open the file
'\\Server\Ok2\MA\SQL-SERVER\MABDAT-EC.mdb'. It is already opened
exclusively by another user, or you need permission to view its data.
I am working on a single server with 1 SQL-database:
I does replication to a MS-Access dababase on the server.
The SQL-server (2000) is running on a NT-server.
It seems to me that the problem is could be caused by 2 reasons:
a lock by another user: but ik can open the file MABDAT-EC.mdb' using
MS-Access.
a problem with the permissions on the server, but what kind of permissions
and where to check.
SQL-server is new to me, and I just started my job in this company, so all
help I can get is welcome.
Thank in advance.
Johan
Hi Johan
Pls check the user permission in the sql server users tab.
Regards
Nirvan Biswas
"Johan" wrote:

> Hi,
>
> Replication to a MS-access database fails last week.
> It has always been working, but now I get this message:
>
> DISTRIBUTION AGENT ERROR
> Error : The process could not connect to Subscriber 'MAB-EC-NW'.
>
> Error information: Microsoft JET Database Engine (number = -534709256)
>
> The Microsoft Jet database engine cannot open the file
> '\\Server\Ok2\MA\SQL-SERVER\MABDAT-EC.mdb'. It is already opened
> exclusively by another user, or you need permission to view its data.
>
> I am working on a single server with 1 SQL-database:
> I does replication to a MS-Access dababase on the server.
> The SQL-server (2000) is running on a NT-server.
>
> It seems to me that the problem is could be caused by 2 reasons:
> a lock by another user: but ik can open the file MABDAT-EC.mdb' using
> MS-Access.
> a problem with the permissions on the server, but what kind of permissions
> and where to check.
>
> SQL-server is new to me, and I just started my job in this company, so all
> help I can get is welcome.
>
> Thank in advance.
> Johan
>
>
>
|||Nirvan,
Thanks for the reply,
The problem seems to be that the administrator password was changed on the
server.
When removing the password from the administrator account on the server (NT)
replication does work. When a password is set on the administrator account,
replication fails, so it seems that authentation is the problem.
After changing the administrator password on the server, SQL-server didn't
start up. So i temporary removed the administrator password to start
SQL-server and placed the password back.
Where do I have to make changes in SQL interprise manager, to solve the
problem with the administrator password?
Regards
Johan
"Nirvan Biswas" <yourdisplayname@.discussions.microsoft.com> schreef in
bericht news:642517C8-9614-4706-9256-4345DFBCA58A@.microsoft.com...[vbcol=seagreen]
> Hi Johan
>
> Pls check the user permission in the sql server users tab.
> Regards
> Nirvan Biswas
> "Johan" wrote:
permissions[vbcol=seagreen]
all[vbcol=seagreen]

Newbie Needs Help ;)

Hello,
I'm new to replication. I have got a subscriber, publisher
ans distributer working ok, so now does the snapshot
replication, then the transactional ones.
It was working fine for 5 minutes then I had the following
error
Cannot update identity column 'ID'.
{CALL sp_MSupd_tblPerson
(NULL,0,NULL,NULL,NULL,NULL,NULL,' ',1,0,7146,0x82
03)}
ID is a primary key with an Identity setting get to 'Yes
but not for replication'.
Any pointers please
Peter
Peter,
are you using transactional with queued updating subscribers? If it is
standard transactional or transactional with updating subscribers, then on
the subscriber there should be no identity property, as the publisher will
manage the values.
Regards,
Paul Ibison
|||run this script on your subscriber.
sp_configure 'allow updates', 1
GO
reconfigure with override
GO
update syscolumns set colstat = colstat | 0x0008 where colstat & 0x0001 <> 0
and colstat & 0x0008 = 0
GO
sp_configure 'allow updates', 0
GO
reconfigure with override
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eCvcKcGNEHA.2704@.TK2MSFTNGP10.phx.gbl...
> Peter,
> are you using transactional with queued updating subscribers? If it is
> standard transactional or transactional with updating subscribers, then on
> the subscriber there should be no identity property, as the publisher will
> manage the values.
> Regards,
> Paul Ibison
>
|||If you want to do this ( ie: keeping identify columns on the subscribers
)_INS
you can do this:sp_MSupd_tblPerson
edit
this proc:
sp_MSupd_tblPerson
and remove the case where the pk_key column is updated ( ths will never
happen anyway )
and this proc:
sp_MSins_tblPerson
add
SET IDENTITY_INSERT "sp_MSins_tblPerson" on
here, replace the insert statement with something like
insert into "tblPerson" ("col1",... ) VALUES (@.param1, ... )
SET IDENTITY_INSERT "sp_MSins_tblPerson" off
Peter wrote:
> Hello,
> I'm new to replication. I have got a subscriber, publisher
> ans distributer working ok, so now does the snapshot
> replication, then the transactional ones.
> It was working fine for 5 minutes then I had the following
> error
> Cannot update identity column 'ID'.
> {CALL sp_MSupd_tblPerson
> (NULL,0,NULL,NULL,NULL,NULL,NULL,' ',1,0,7146,0x82
> 03)}
> ID is a primary key with an Identity setting get to 'Yes
> but not for replication'.
> Any pointers please
> Peter
|||while I agree that using set identity_insert in your insert procs will solve
this problem, you would be better to partition using different seeds and
probably increments on your publisher and subscriber, and put the Not For
Replication option on your identity columns on the Subscriber.
The primary key's are sometimes updated - normally this is a sign of poor
database design, however it is the default behavior of the datagrid control.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Olivier" <olivierwarez@.netscape.net> wrote in message
news:0Xcnc.43829$zm5.21011@.nntpserver.swip.net...
> If you want to do this ( ie: keeping identify columns on the subscribers
> )_INS
> you can do this:sp_MSupd_tblPerson
> edit
> this proc:
> sp_MSupd_tblPerson
> and remove the case where the pk_key column is updated ( ths will never
> happen anyway )
> and this proc:
> sp_MSins_tblPerson
> add
> SET IDENTITY_INSERT "sp_MSins_tblPerson" on
> here, replace the insert statement with something like
> insert into "tblPerson" ("col1",... ) VALUES (@.param1, ... )
> SET IDENTITY_INSERT "sp_MSins_tblPerson" off
>
>
> Peter wrote:
>
|||
> while I agree that using set identity_insert in your insert procs will solve
> this problem, you would be better to partition using different seeds and
> probably increments on your publisher and subscriber, and put the Not For
> Replication option on your identity columns on the Subscriber.
In this case there is one publisher and one subscriber, and the 'not for
replication' is set

> The primary key's are sometimes updated - normally this is a sign of poor
> database design, however it is the default behavior of the datagrid control.
It is not possible to update the identity column. Usually the identity
column is the primary key one
By the, what does
update syscolumns set colstat = colstat | 0x0008 where colstat & 0x0001 <> 0
and colstat & 0x0008 = 0
do exactly?

Newbie Merge replication question

Hi!

I'm setting up a merge replication for sql ce. Ten guys with windows mobile 5 pocket pc works as service technicians and need to synchronize data in the field.

The backend database is huge and I just want each person to have their own data on the pda and synchronized, they have unique userid's in the database. Is it possible to filter data for each person or do all have to have all data that's in the backend database?

/Magnus

Merge replication permits the agents to have subsets of the data based on how the Publication is programmed. One alternate approach would be to publish individual agent tables, but this gets messy as agents are added and removed.|||Could I use a "translator" table to translate suser_name()'s to applications userid's? Then in the filter query against this table to identify the user? One solution could be to have one Publication for each employee? Yes it would become messy to administer. But my problem can't be unique. How do you all query out data for each employee?|||You could have each user identifying themselves when using the application, and setting their userid as HostName on your replication object. Then you can reference this in your publication filter with HOST_NAME().

Newbie Merge replication question

Hi!

I'm setting up a merge replication for sql ce. Ten guys with windows mobile 5 pocket pc works as service technicians and need to synchronize data in the field.

The backend database is huge and I just want each person to have their own data on the pda and synchronized, they have unique userid's in the database. Is it possible to filter data for each person or do all have to have all data that's in the backend database?

/Magnus

Merge replication permits the agents to have subsets of the data based on how the Publication is programmed. One alternate approach would be to publish individual agent tables, but this gets messy as agents are added and removed.|||Could I use a "translator" table to translate suser_name()'s to applications userid's? Then in the filter query against this table to identify the user? One solution could be to have one Publication for each employee? Yes it would become messy to administer. But my problem can't be unique. How do you all query out data for each employee?|||You could have each user identifying themselves when using the application, and setting their userid as HostName on your replication object. Then you can reference this in your publication filter with HOST_NAME().

newbie can't drop Table as table is in use

newbie to transactional replication (replication in general). I have one
server that is publisher/distributor. Another server as subscriber. both
sql 2000 sp4. I have setup replication and when trying to apply the initial
snapshot it says that it can't drop table as the table is in use. I have
restored the same database on both servers and are now trying to replicate.
Is this correct or do I just have a blank database on the subscriber side and
let the replication create everything?
Unless it is a specific requirement (eg low bandwidth or huge amount of
data) I'd not use a nosync method as a default. In other words for your case
you could have an empty database on the subscriber and just allow the
initialization process to set things up for you.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks for the help Paul. So I should drop the database that I have
restored, create a snapshot, let that replicate and go from there?
"Paul Ibison" wrote:

> Unless it is a specific requirement (eg low bandwidth or huge amount of
> data) I'd not use a nosync method as a default. In other words for your case
> you could have an empty database on the subscriber and just allow the
> initialization process to set things up for you.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Yes - the easiest thing is to drop the database then create a new one with
the same name. This can be done using the subscription wizard (the creation
of a new database). After that, the initialization process will create the
objects and data you need.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I think you should try to find out how the table is in use. What happens if
you issue a sp_lock and then look at the list of id's returned. Use
sp_helpdb to identify your database, and then use object_name(ObjId) to
identify the tables. Then notice the spid. Use sp_who2 to identify it.
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
"Dgragg" <Dgragg@.discussions.microsoft.com> wrote in message
news:FE9D526C-EE55-44A1-8F58-1B8384E1DF11@.microsoft.com...
> newbie to transactional replication (replication in general). I have one
> server that is publisher/distributor. Another server as subscriber. both
> sql 2000 sp4. I have setup replication and when trying to apply the
> initial
> snapshot it says that it can't drop table as the table is in use. I have
> restored the same database on both servers and are now trying to
> replicate.
> Is this correct or do I just have a blank database on the subscriber side
> and
> let the replication create everything?

Newbie at Replication

There aren't many primary keys on the tables in the Publishers. If I add primary keys to the tables on the replicated database, when I subscribe again to the Publications, will they disappear from my replicated database (i.e. will the schema be overwritten)?The response below is from a Merge replication perspective:

Firstly, after adding the new PKs to the tables at the Publisher, adding a new subscription to it will not overwrite the publisher schema. All reinitializing an existing subscriber will not overwrite the Publisher schema (It will however overwrite the subscriber schema, provided you ran the snapshot)

However if you want the added PKs to propagate to the subscriber, then you will fall into one of the cases below:

If you add the new PKs on the publisher before the snapshot is generated and delivered to the subscriber, the subscriber will get the new PKs.

However if the subscriber already has the snapshot and is synched up, and then you add the new PKs at the publisher, the subscriber will not get PKs incrementally on subsequent synchs. You will either need to explicitly create them at the subscriber, or add the PKs at the publisher, regenerate the snapshot, and reinitialize the subscriber.

Please refer to Books Online for furhter information on Initial Synchronization, Subsequent Synchronization, Incremental changes and replicating DDL changes.|||Yes it does help very much, thank you! By trial and error I arrived at the same answer. Thank you for the reference to the solution in Books Online. Sometimes I don't have the right keywords in my search.

Newbe question about replication

Hi ppl

I just started with all this 'replication' subject and run into some 'stange ?' problem. I suscesfully set-up publisher and distributor on one computer. create subscriber on another. And seems that this is working with no errors as I see in Replication monitor, details. All agent turned to verbose output and showing no errors at all. but the strange problem that the database does not (!) appear at subscriber (?) I mean - no tables, no stored procedures, no nothing - how it can be possible ? What I'm doing wrong ? Or what I should do ?

P.S. Forgot to add: Both servers running latest SQL server 2005 with latest Service Pack. Subscribtion of type 'push'

Hi Jasper

I think, you're talking about merge replication.
One question:
Have you generated a snapshot before synchronizing?

kind regards
Aline