2PC transaction replication problem

I created a 2PC transaction replication on a simple database for testing.
Changes in the publisher are successfully replicated to the subscriber.
However, when changes are made to the subscriber, the following error
message is shown:
"Another user has modified the contents of this table or view; the database
row you are modifying no longer exists in the database."
Could somebody help to solve the problem?
Thanks in advance!
KM
KM,
can you do a search on hte publisher for the PK value of the row you are
changing. Presumably it is not there and sp_browsereplcmds on the
distributor should reveal a relevant delete statement.
HTH,
Paul Ibison
|||where are you seeing this message? DataGrid?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"krygim" <krygim@.hotmail.com> wrote in message
news:OAZEcl2hEHA.3348@.TK2MSFTNGP12.phx.gbl...
> I created a 2PC transaction replication on a simple database for testing.
> Changes in the publisher are successfully replicated to the subscriber.
> However, when changes are made to the subscriber, the following error
> message is shown:
>
> "Another user has modified the contents of this table or view; the
database
> row you are modifying no longer exists in the database."
>
> Could somebody help to solve the problem?
>
> Thanks in advance!
>
> KM
>
|||Hi Paul,
There are only 3 rows in my table. The PK values in the subscriber and
publisher are exactly the same. The PK column of both the subscriber and the
publisher are marked as Identity (Not For Replication). I got the same
message even if I add a new row in the subscriber.
In the Query Analyzer on the publishing database, I tried the
sp_browsereplcmds and got the message:
"Could not find stored procedure 'sp_browsereplcmds'."
KM
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:em3ZXU3hEHA.4064@.TK2MSFTNGP12.phx.gbl...
> KM,
> can you do a search on hte publisher for the PK value of the row you are
> changing. Presumably it is not there and sp_browsereplcmds on the
> distributor should reveal a relevant delete statement.
> HTH,
> Paul Ibison
>
|||Hi Hilary,
I opened the subscriber database table by selecting "Open Table | Return All
Rows" in the Enterprise Manager. Made change to one of the rows. When I
tried to leave the row, a dialog box popped up which showed the message:
Another user has modified the contents of this table or view; the database
row you are modifying no longer exists in the database.
Database error: '[Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB
provider returned message: New transaction cannont enlist in the specified
transaction coordinator]...
.... The operation could not be performed because the OLE DB provider
'SQKOLEDB' was unable to begin a distributed transaction'
KM
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:%23XSgUZ3hEHA.244@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> where are you seeing this message? DataGrid?
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "krygim" <krygim@.hotmail.com> wrote in message
> news:OAZEcl2hEHA.3348@.TK2MSFTNGP12.phx.gbl...
testing.
> database
>
|||try running this command in the distributor.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Krygim" <krygim@.hotmail.com> wrote in message
news:u24De94hEHA.3476@.tk2msftngp13.phx.gbl...
> Hi Paul,
> There are only 3 rows in my table. The PK values in the subscriber and
> publisher are exactly the same. The PK column of both the subscriber and
the
> publisher are marked as Identity (Not For Replication). I got the same
> message even if I add a new row in the subscriber.
> In the Query Analyzer on the publishing database, I tried the
> sp_browsereplcmds and got the message:
> "Could not find stored procedure 'sp_browsereplcmds'."
> KM
>
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:em3ZXU3hEHA.4064@.TK2MSFTNGP12.phx.gbl...
>
|||Hi Hilary,
I get the same message when running the command in both the distributor and
the subscriber.
KM
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:uCyeWO6hEHA.3548@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> try running this command in the distributor.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Krygim" <krygim@.hotmail.com> wrote in message
> news:u24De94hEHA.3476@.tk2msftngp13.phx.gbl...
> the
are
>
|||KM,
I wasn't very precise but by distributor I meant distribution database on
the distributor - the sp should be there. Please post back after running it
to tell us if the rows you refer to are have been modified on the publisher.
TIA,
Paul Ibison
|||Hi Paul,
I found the stored procedure in BOL. I think I must have done something
wrong. Please let me know if I have carried out the steps correctly or not:
1. In the Query Analyzer, connect to the distribution SQL server.
2. Select the distribution database in the dropdown list
3. Enter exec sp_browsereplcmds and press F5
The following message was displayed in the message pane:
Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'sp_browsereplcmds'.
Thanks in advance.
KM
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23qrm%23XCiEHA.592@.TK2MSFTNGP11.phx.gbl...
> KM,
> I wasn't very precise but by distributor I meant distribution database on
> the distributor - the sp should be there. Please post back after running
it
> to tell us if the rows you refer to are have been modified on the
publisher.
> TIA,
> Paul Ibison
>
|||Krygim,
everything you've done looks correct, but I've never heard of this before.
Can you run this:
sp_browsereplcmds
go
select db_name()
(just to check that you are in the correct database). If it returns
'distribution' then perhaps a service-pack install didn't succeed and you
could check sqlsp.log file from the c:\windows directory to see if this is
the case.
HTH,
Paul Ibison

2005: Disabled SQL Login

Is there something that would cause a SQL Login to get disabled

automatically? The login was used successfully yesterday but today we

were getting an error:

Login failed for user '<sql login>'. Reason: The account is disabled. [CLIENT: ipaddress]

Perhaps someone accidentally disabled this but that sounds unlikely.

I didn't see anything in the log about the account getting disabled.

Michelle

If you run

select * from sys.server_principals where name = '<sql login>'

You should be able to check to see if the login actually is disabled and what the last modified date is. This way you can check to see if it was actually changed.

Which log did you check? I believe that if you enabled trace for this, you should have received an event when the login was altered as well.

Sung

|||I had already enabled the login - the 'disabled' checkbox was checked

so I'm fairly certain that it really was disabled. The log file that

showed the error when the login was used to log in to the database was

the SQL Server error log.

I'm just trying to figure out how it got disabled and the only thing

that I can come up with is that someone did it accidentally.|||

The only thing I can think of that would cause something similar is when the account gets locked out.

But I believe the warning message is different and the control panel would show that the account is locked, not disabled.

I would guess the account was disabled by accident. You may want to create a trace for this action so you can monitor and audit this in the future?

Sung

|||

Logins are not disabled automatically and the action of disabling a login does not issue a log entry. Someone must have explicitly disabled the login. Was this login created by some application or was it a regular user login?

Thanks
Laurentiu

2005 SP1 (remote Reporting Services upgrade failures)

Why can it never be easy? I applied SP1 to one of our 2005 database servers and upgraded all components successfully. However when trying to install on a stand alone Reporting Services instance that uses the above database server I can't get past the Login verification. Windows or Sql...Per the ReadMe I am using an acccount that is administrator on both servers and sysadmin in sql, also am running the exe as same admin account.

There are no logs under %Program Files%\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files but I found this error in the hotfix log.

[SQL Native Client]TCP Provider: The requested address is not valid in
its context.
[SQL Native Client]Login timeout expired
[SQL Native Client]An error has occurred while establishing a
connection to the server. When connecting to SQL Server 2005, this
failure may be caused by the fact that under the default settings SQL
Server does not allow remote connections.

Verified that the SQL Server allows remote connections (TCP\IP is enabled, Named Pipes is not); even started the Browser sevice that hasn't been running and still get the same error.

Will the steps outlined by Lukasz at http://blogs.msdn.com/lukaszp/archive/2006/05/18/601340.aspx solve my problem or will I still need to upgrade the RS files somehow? I think I belong to 'Sometimes does not work" #3. The Native Client upgrade runs fine by itself.

Advice and commiserations appreciated.

AB

Solved error above by recreating the client alias to the sql server. The port designation had disappeared even though it was default port.

Now am experiencing locked file errors - SQL Reporting Services service is disabled and the web site is stopped. What other services need to be stopped? Do services on the remote sql server also need to be stopped?

AB

2005 SP1

Why can it never be easy? I applied SP1 to one of our 2005 database servers and upgraded all components successfully. However when trying to install on a stand alone Reporting Services instance that uses the above database server I can't get past the Login verification. Windows or Sql...Per the ReadMe I am using an acccount that is administrator on both servers and sysadmin in sql, also am running the exe as same admin account.

There are no logs under %Program Files%\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files but I found this error in the hotfix log.

[SQL Native Client]TCP Provider: The requested address is not valid in
its context.
[SQL Native Client]Login timeout expired
[SQL Native Client]An error has occurred while establishing a
connection to the server. When connecting to SQL Server 2005, this
failure may be caused by the fact that under the default settings SQL
Server does not allow remote connections.

Verified that the SQL Server allows remote connections (TCP\IP is enabled, Named Pipes is not); even started the Browser sevice that hasn't been running and still get the same error.

Will the steps outlined by Lukasz at http://blogs.msdn.com/lukaszp/archive/2006/05/18/601340.aspx solve my problem or will I still need to upgrade the RS files somehow? I think I belong to 'Sometimes does not work" #3. The Native Client upgrade runs fine by itself.

Advice and commiserations appreciated.

AB

Solved error above by recreating the client alias to the sql server. The port designation had disappeared even though it was default port.

Now am experiencing locked file errors - SQL Reporting Services service is disabled and the web site is stopped. What other services need to be stopped? Do services on the remote sql server also need to be stopped?

AB

2005 restored msdb, cant see SSIS packages ?

We recently rebuilt a dev server, and as part of that process, I restored
msdb (successfully) from backup.
But now I can't see any SSIS packages from Management Studio !
If I run this query ->
use msdb
select *
from sysdtspackages90
then I can see them all !
How do I get Management Studio to see them as well ?
TIAHi Jim
My guess this is possibly related to ownership. Did you restore the master
database as well?
John
"Jim Trowbridge" wrote:
> We recently rebuilt a dev server, and as part of that process, I restored
> msdb (successfully) from backup.
> But now I can't see any SSIS packages from Management Studio !
> If I run this query ->
> use msdb
> select *
> from sysdtspackages90
> then I can see them all !
> How do I get Management Studio to see them as well ?
> TIA|||John,
I didn't restore master. It turned out to be a problem with the registry
setting that points to the (cluster resource copy of the ) MsDtsSrvr.ini.xml
file had the wrong address. Thats why the tree didn't even have the usual
File System and MSDB sub-tree entries.
Once that registry setting was fixed and the Integration Services service
was bounced, everything behaved properly.
"John Bell" wrote:
> Hi Jim
> My guess this is possibly related to ownership. Did you restore the master
> database as well?
> John
> "Jim Trowbridge" wrote:
> > We recently rebuilt a dev server, and as part of that process, I restored
> > msdb (successfully) from backup.
> > But now I can't see any SSIS packages from Management Studio !
> > If I run this query ->
> > use msdb
> > select *
> > from sysdtspackages90
> > then I can see them all !
> >
> > How do I get Management Studio to see them as well ?
> > TIA|||Hi Jim
Thanks for the information, it's good to know you found the solution.
John
"Jim Trowbridge" wrote:
> John,
> I didn't restore master. It turned out to be a problem with the registry
> setting that points to the (cluster resource copy of the ) MsDtsSrvr.ini.xml
> file had the wrong address. Thats why the tree didn't even have the usual
> File System and MSDB sub-tree entries.
> Once that registry setting was fixed and the Integration Services service
> was bounced, everything behaved properly.
> "John Bell" wrote:
> > Hi Jim
> >
> > My guess this is possibly related to ownership. Did you restore the master
> > database as well?
> >
> > John
> >
> > "Jim Trowbridge" wrote:
> >
> > > We recently rebuilt a dev server, and as part of that process, I restored
> > > msdb (successfully) from backup.
> > > But now I can't see any SSIS packages from Management Studio !
> > > If I run this query ->
> > > use msdb
> > > select *
> > > from sysdtspackages90
> > > then I can see them all !
> > >
> > > How do I get Management Studio to see them as well ?
> > > TIA

2005 replication outside my Domain errors

---

I currently have replication (transactional) set up within my domain successfully using 2000 and 2005.
I need to start replicating to a hosting center for a web app from my server out on the DMZ. I can use SSIS and SMS to connect, update data, etc. but when I try to replicate out there it gives me the following errors:

1. Tells me it needs a named instance so I give it the hosted server name : ABC123

2. Then I get the following:

TITLE: Connect to Server

Cannot connect to ABC123.

ADDITIONAL INFORMATION:

Failed to connect to server ABC123. (Microsoft.SqlServer.ConnectionInfo)

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)

Any help would be greatly appreciated!!

TIA

Tena

Come on, there has to be someone (please, please) who knows something about this? Ideas, chants, sacrifices of small animals I can make to the SQL Gods? (OK, the last one I would not REALLY do).

TIA

~Tena~

|||Well, the error message basically says that it can't connect. Since you can connect with other methods, that would rule out not having remote connections enabled. So, that means the replication engine simply can not connect to the subscriber. When you set it up, you had to specify account credentials to use to connect. Those are the credentials that you need to check, because they are not authenticating.|||I have exactly the same problem here, but with 2 servers in the same hosting center. tried to do the ftp snapshot but the subscriber is unable to see the distributor. I got the same error message.

did you find an answer?

Share pls.

|||

Make sure you enabled remote connections on sql server 2005 in Surface Area Configuration (SAC) and use port number along with server...some times it can't resolve port number...

ex: servername\instancename,portnumber

Note: If you can't connect by name you can't configure replication with ip address so better alias the server to configure the replication.