Replication Errors

I have three questions?
1. I have a Sql server instance running without any name, it just has a
default name (local), when i try to go configure replication its says that
replication does not support (local) name. This server is in production
right now. And it will be hard to shut it down. Is there anyway to change
the name of the instance? Even if i unregister and re-register a new
server, will all my databses and plans, etc. will be the exact same as on
the last instance? Whats the best possible way can i configure replication
on this instance?
2. I was subscribed to a couple push subscriptions replication from a
different server, but that server no longer exists, but my server still goes
out and looks for the subscription, I tried to remove the subscription, but
i get the same errror like on the question one, "replication does not
support (local) name." so i can't remove the subscription. What can i do to
remove this subscriptions?
3. When i try to configure the Replication, its says that Server Agent uses
the local service account you need to change the account? Whats the Local
service account? Can two services not use the same local service account?
How do i need to set this up, to use the account? Do i need to create
another account?
Thanks for all your help.
See inline
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Shash Goyal" <Shash703@.gmail.com> wrote in message
news:eiVPxa7vEHA.824@.TK2MSFTNGP11.phx.gbl...
> I have three questions?
> 1. I have a Sql server instance running without any name, it just has a
> default name (local), when i try to go configure replication its says that
> replication does not support (local) name. This server is in production
> right now. And it will be hard to shut it down. Is there anyway to change
> the name of the instance? Even if i unregister and re-register a new
> server, will all my databses and plans, etc. will be the exact same as on
> the last instance? Whats the best possible way can i configure
replication
> on this instance?
>
Every server has a name, the default name is the same as the name of the NT
server on which SQL runs... YOu simply have SQL registered in you SEM unding
(local) instead of the server name... Get the real servername, Un-register
it in SEM , then register it with the real name...
> 2. I was subscribed to a couple push subscriptions replication from a
> different server, but that server no longer exists, but my server still
goes
> out and looks for the subscription, I tried to remove the subscription,
but
> i get the same errror like on the question one, "replication does not
> support (local) name." so i can't remove the subscription. What can i do
to
> remove this subscriptions?
Re-register the server, and take a look at the sp_dropsubscription
> 3. When i try to configure the Replication, its says that Server Agent
uses
> the local service account you need to change the account? Whats the Local
> service account? Can two services not use the same local service account?
> How do i need to set this up, to use the account? Do i need to create
> another account?
> Set up a domain account for SQL Server. Add the domain account to each of
your SQL Servers as a SQL administrator. In SEM, go to SQL Agent and right
click and go to properties. I think the second tab is where you put the
login password ( instead of system checkbox.) Stop and re-start SQL Agent
and you should be good.
> Thanks for all your help.
>

Replication Errors

I have three questions'
1. I have a Sql server instance running without any name, it just has a
default name (local), when i try to go configure replication its says that
replication does not support (local) name. This server is in production
right now. And it will be hard to shut it down. Is there anyway to change
the name of the instance? Even if i unregister and re-register a new
server, will all my databses and plans, etc. will be the exact same as on
the last instance? Whats the best possible way can i configure replication
on this instance?
2. I was subscribed to a couple push subscriptions replication from a
different server, but that server no longer exists, but my server still goes
out and looks for the subscription, I tried to remove the subscription, but
i get the same errror like on the question one, "replication does not
support (local) name." so i can't remove the subscription. What can i do to
remove this subscriptions'
3. When i try to configure the Replication, its says that Server Agent uses
the local service account you need to change the account' Whats the Local
service account? Can two services not use the same local service account?
How do i need to set this up, to use the account? Do i need to create
another account?
Thanks for all your help.See inline
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Shash Goyal" <Shash703@.gmail.com> wrote in message
news:eiVPxa7vEHA.824@.TK2MSFTNGP11.phx.gbl...
> I have three questions'
> 1. I have a Sql server instance running without any name, it just has a
> default name (local), when i try to go configure replication its says that
> replication does not support (local) name. This server is in production
> right now. And it will be hard to shut it down. Is there anyway to change
> the name of the instance? Even if i unregister and re-register a new
> server, will all my databses and plans, etc. will be the exact same as on
> the last instance? Whats the best possible way can i configure
replication
> on this instance?
>
Every server has a name, the default name is the same as the name of the NT
server on which SQL runs... YOu simply have SQL registered in you SEM unding
(local) instead of the server name... Get the real servername, Un-register
it in SEM , then register it with the real name...
> 2. I was subscribed to a couple push subscriptions replication from a
> different server, but that server no longer exists, but my server still
goes
> out and looks for the subscription, I tried to remove the subscription,
but
> i get the same errror like on the question one, "replication does not
> support (local) name." so i can't remove the subscription. What can i do
to
> remove this subscriptions'
Re-register the server, and take a look at the sp_dropsubscription
> 3. When i try to configure the Replication, its says that Server Agent
uses
> the local service account you need to change the account' Whats the Local
> service account? Can two services not use the same local service account?
> How do i need to set this up, to use the account? Do i need to create
> another account?
> Set up a domain account for SQL Server. Add the domain account to each of
your SQL Servers as a SQL administrator. In SEM, go to SQL Agent and right
click and go to properties. I think the second tab is where you put the
login password ( instead of system checkbox.) Stop and re-start SQL Agent
and you should be good.
> Thanks for all your help.
>sql

Replication Errors

I have three questions?
1. I have a Sql server instance running without any name, it just has a
default name (local), when i try to go configure replication its says that
replication does not support (local) name. This server is in production
right now. And it will be hard to shut it down. Is there anyway to change
the name of the instance? Even if i unregister and re-register a new
server, will all my databses and plans, etc. will be the exact same as on
the last instance? Whats the best possible way can i configure replication
on this instance?
2. I was subscribed to a couple push subscriptions replication from a
different server, but that server no longer exists, but my server still goes
out and looks for the subscription, I tried to remove the subscription, but
i get the same errror like on the question one, "replication does not
support (local) name." so i can't remove the subscription. What can i do to
remove this subscriptions?
3. When i try to configure the Replication, its says that Server Agent uses
the local service account you need to change the account? Whats the Local
service account? Can two services not use the same local service account?
How do i need to set this up, to use the account? Do i need to create
another account?
Thanks for all your help.
Hi Shash Goyal,
Most of your problems relate to having registered your server as "(local)"
in Enterprise Manager. Try reregistering it as the name of your computer
instead. I think that will fix problems 1 and 2. With the 3rd one, all it's
trying to tell you is that if you want to connect to anything outside your
computer, you normally need a domain user account. The "Local Service"
account is a special account on your computer that only applies to that
computer. Try creating a new user that will be used only as a service
account (ie something like "sqlservice" for a name). Make it a domain user
if you are in a domain. Give it appropriate permissions and change SQL
Server and SQL Agent to run as that user (you can do this by right-clicking
each in Enterprise Manager) and choosing the "Security" option.
HTH,
Greg Low [MVP]
MSDE Manager SQL Tools
www.whitebearconsulting.com
"Shash Goyal" <Shash703@.gmail.com> wrote in message
news:%23j6aiu5vEHA.3080@.TK2MSFTNGP12.phx.gbl...
>I have three questions?
> 1. I have a Sql server instance running without any name, it just has a
> default name (local), when i try to go configure replication its says that
> replication does not support (local) name. This server is in production
> right now. And it will be hard to shut it down. Is there anyway to change
> the name of the instance? Even if i unregister and re-register a new
> server, will all my databses and plans, etc. will be the exact same as on
> the last instance? Whats the best possible way can i configure
> replication
> on this instance?
>
> 2. I was subscribed to a couple push subscriptions replication from a
> different server, but that server no longer exists, but my server still
> goes
> out and looks for the subscription, I tried to remove the subscription,
> but
> i get the same errror like on the question one, "replication does not
> support (local) name." so i can't remove the subscription. What can i do
> to
> remove this subscriptions?
> 3. When i try to configure the Replication, its says that Server Agent
> uses
> the local service account you need to change the account? Whats the Local
> service account? Can two services not use the same local service account?
> How do i need to set this up, to use the account? Do i need to create
> another account?
>
> Thanks for all your help.
>

Replication Errors

I have three questions'
1. I have a Sql server instance running without any name, it just has a
default name (local), when i try to go configure replication its says that
replication does not support (local) name. This server is in production
right now. And it will be hard to shut it down. Is there anyway to change
the name of the instance? Even if i unregister and re-register a new
server, will all my databses and plans, etc. will be the exact same as on
the last instance? Whats the best possible way can i configure replication
on this instance?
2. I was subscribed to a couple push subscriptions replication from a
different server, but that server no longer exists, but my server still goes
out and looks for the subscription, I tried to remove the subscription, but
i get the same errror like on the question one, "replication does not
support (local) name." so i can't remove the subscription. What can i do to
remove this subscriptions'
3. When i try to configure the Replication, its says that Server Agent uses
the local service account you need to change the account' Whats the Local
service account? Can two services not use the same local service account?
How do i need to set this up, to use the account? Do i need to create
another account?
Thanks for all your help.See inline
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Shash Goyal" <Shash703@.gmail.com> wrote in message
news:eiVPxa7vEHA.824@.TK2MSFTNGP11.phx.gbl...
> I have three questions'
> 1. I have a Sql server instance running without any name, it just has a
> default name (local), when i try to go configure replication its says that
> replication does not support (local) name. This server is in production
> right now. And it will be hard to shut it down. Is there anyway to change
> the name of the instance? Even if i unregister and re-register a new
> server, will all my databses and plans, etc. will be the exact same as on
> the last instance? Whats the best possible way can i configure
replication
> on this instance?
>
Every server has a name, the default name is the same as the name of the NT
server on which SQL runs... YOu simply have SQL registered in you SEM unding
(local) instead of the server name... Get the real servername, Un-register
it in SEM , then register it with the real name...
> 2. I was subscribed to a couple push subscriptions replication from a
> different server, but that server no longer exists, but my server still
goes
> out and looks for the subscription, I tried to remove the subscription,
but
> i get the same errror like on the question one, "replication does not
> support (local) name." so i can't remove the subscription. What can i do
to
> remove this subscriptions'
Re-register the server, and take a look at the sp_dropsubscription
> 3. When i try to configure the Replication, its says that Server Agent
uses
> the local service account you need to change the account' Whats the Local
> service account? Can two services not use the same local service account?
> How do i need to set this up, to use the account? Do i need to create
> another account?
> Set up a domain account for SQL Server. Add the domain account to each of
your SQL Servers as a SQL administrator. In SEM, go to SQL Agent and right
click and go to properties. I think the second tab is where you put the
login password ( instead of system checkbox.) Stop and re-start SQL Agent
and you should be good.
> Thanks for all your help.
>

Replication error with sqlserver mobile:A request to send data to the computer running IIS is fa

I have smilar kind of an issue with replication. I had sucessfully done replication with Sqlce2.0 on sql server 2000. In development machine I had VS 2003, sqlce development tools.

Now I use the same code done in VS 2003 into VS2005, it sucessfully converted the code.

In server machine (win 2003) I installed sql server 2000, sql server 2000 service pack 4, SP4 merge replication components, sql server 2005 mobile server tools and .Net framework 2.0. Created pubication sucessfuly. In this machine I configured IIS as per the web synchronization wizard. Gave anyonymous permissions. Virual direcory is created sucessfully. I tested the server agent in IE http://myservername/aliasname/sqlcesa30.dll.

SQL Server Mobile Server Agent 3.0 is shown

My application opened successfuly in windows mobile 5.0 Pocket PC emulator. When try to sync its dislays error "A request to send data to the computer running IIS is failed, see Hrresult"

No firewall in both development and server machines.

The same code works if its sqlce20 perfectly with vs2003 code. Not in this

Can anyone tell me whats wrong in this?

Regards

Arif

I have moved this to the SQL Mobile forum for someone in the SQL Mobile team to look at.

Thanks

Amit

|||

Hello All,

It worked for me. I read some of the messages in this forum and found that I need to connect the Windows Mobile PPC emulator using activesync. I did it and now its working fine.

I was now able to do sync with both PPC2003 on Sql server 2000 and sqlce20 (VS2003 environment)

AND also

Windows mobile 5 on Sql Server 2000, SQL Server SP4, SP4 replication components, Sql server mobile (VS2005) succesfuly

Thanks for the questions and responses in this forum

Arif

www.anteriorsoft.com

|||

Thanks a lot for your compliments!

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

Replication error thrown during normal T-SQL processing

Dear All,
During a long running piece of SQL script I received the following error:
Error Number: 2601
Error: "The job name '%s' was not generated for this replication agent,
delete the job manually when it is no longer in use."
There is no replication set up on this server at all, and the T-SQL is pure
with no calls to system stored or extended procedures.
Has anybody experience this before? A search of Google and MSDN has
returned no results at all for the assosiated message!
Spec:
Microsoft SQL Server 2000 - 8.00.760
Developer Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
HP ProLiant dual 3ghz HT Zeon
4gb ram
Thanks
Phil StandenOn my server,
SELECT *
FROM master..sysmessages
WHERE Error = 2601
returns:
Cannot insert duplicate key row in object '%.*ls' with unique index
'%.*ls'.
and
SELECT *
FROM master..sysmessages
WHERE Error = 21695
returns:
The job name '%s' was not generated for this replication agent, delete
the job manually when it is no longer in use.
Do you get the same results on your box?
Hope this helps.
Dan Guzman
SQL Server MVP
"Phil Standen" <PhilStanden@.discussions.microsoft.com> wrote in message
news:BEE3ED3E-FA80-4D98-A85D-36B4E37B374A@.microsoft.com...
> Dear All,
> During a long running piece of SQL script I received the following error:
> Error Number: 2601
> Error: "The job name '%s' was not generated for this replication agent,
> delete the job manually when it is no longer in use."
> There is no replication set up on this server at all, and the T-SQL is
> pure
> with no calls to system stored or extended procedures.
> Has anybody experience this before? A search of Google and MSDN has
> returned no results at all for the assosiated message!
> Spec:
> Microsoft SQL Server 2000 - 8.00.760
> Developer Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> HP ProLiant dual 3ghz HT Zeon
> 4gb ram
> Thanks
> Phil Standen

replication error on script

I am trying to replicate a database between 2 servers...I
have others running fine between these servers so I know
user and password are fine...I get this error everytime I
try
Error:
The schema script '\\CORPSQL\C$\Program Files\Microsoft
SQL
Server\MSSQL\ReplData\unc\CORPSQL_IRIMSAdmin_IRIMSAdmin\200
30923132049\ChangeUserPassword_316.sch' could not be
propagated to the subscriber. The step failed.
Here is the code:
SET QUOTED_IDENTIFIER ON
GO
drop procedure [dbo].[ChangeUserPassword]
GO
CREATE PROCEDURE [dbo].[ChangeUserPassword]
@.UserID int,
@.NewPassword varchar(50)
AS
DECLARE @.PasswordHistory int
DECLARE @.PasswordCount int
DECLARE @.Password varchar(50)
DECLARE @.DuplicateFlag bit
DECLARE @.ErrorMessage varchar(200)
DECLARE Password_Cursor CURSOR FOR
SELECT Password
FROM tblHistoryPasswords
WHERE UserRef = @.UserID
ORDER BY CreatedDateTime DESC
--SELECT @.USERID=USERID FROM USERDATA WHERE NTLOGON LIKE
@.NTLogon
SET @.DuplicateFlag=0
SET @.PasswordCount=0
SELECT @.PasswordHistory = CAST (varvalue AS int) FROM
tblsystemvariables WHERE varid = 'PasswordHistory'
OPEN Password_Cursor
FETCH NEXT FROM Password_Cursor
INTO @.Password
WHILE @.@.FETCH_STATUS = 0 AND @.DuplicateFlag = 0
BEGIN
SET @.PasswordCount = @.PasswordCount + 1
IF @.PasswordCount <= @.PasswordHistory
BEGIN
IF @.Password = @.NewPassword
SET @.DuplicateFlag=1
END
IF @.DuplicateFlag=0
BEGIN
IF @.PasswordCount = @.PasswordHistory
BEGIN
UPDATE tblHistoryPasswords
SET Password = @.NewPassword, CreatedDateTime=GetDate()
WHERE CURRENT OF Password_Cursor
END
IF @.PasswordCount > @.PasswordHistory
BEGIN
DELETE FROM tblHistoryPasswords
WHERE CURRENT OF Password_Cursor
END
FETCH NEXT FROM Password_Cursor
INTO @.Password
END
END
CLOSE Password_Cursor
DEALLOCATE Password_Cursor
IF @.DuplicateFlag=1
BEGIN
--raise error
SET @.ErrorMessage = "IRIMS Admin DB: User password has to
be different from last " + CAST(@.PasswordHistory AS varchar
(2)) + " passwords."
RaisError (@.ErrorMessage,16,1)
RETURN 1
END
ELSE
IF @.PasswordCount < @.PasswordHistory OR @.PasswordHistory
= 0
INSERT INTO tblHistoryPasswords (UserRef,Password)
VALUES (@.UserID,@.NewPassword)
RETURN 0
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOHi Jamie,
Thanks for your feedback. Providing definite problem and detailed
description of the problem helps us troubleshoot the issue more
efficiently. As I understand, the SQL Server version is SQL Server 2000 Sp1
and you have a merge replication with 2 machines. One is as publication and
distribution, the other is as pull subscription. Do you mean the error
occurs when you configure the subscription? In other words, you did not
finish setting up your subscription, or was it that applying a snapshot to
a subscriber failed with this error?
What is your type of your pull subscription, named subscription or
anonymous subscription? You can check it in the General tab in the property
dialog of the subscription.
What is the kind of snapshot, FTP or UNC? You can check it in the Snapshot
file location tab in the property dialog of the subscription.
Based on my research, this can occur if the publication includes a stored
procedure that references fields that do not exist in the underlying table.
Please check to see who the owner of the stored procedure is and who the
underlying tables tblsystemvariables and tblHistoryPasswords are. If they
are different, please specifying the owner of the underlying table in the
stored procedure. Please try to run the snapshot agent for the publication
by right-clicking it and then clicking Start on the shortcut menu.
Please let me know if this solves your problem, or if you would like
further assistance.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||work around...I am only merging the tables...the sp are
not neccessary on this database...merging the tables seem
to be working fine....
Quick question
when setting up the merge part it ask if you want to
do vertical or horizontal, is it best practices to select
both or just one of them..if one of them, which one?|||Hi Jamie,
Thanks for your feedback. I am glad to hear that this problem has been
resolved.
Regarding your further question, horizontal and vertical enable you to
create partitions of data to be published. The selection of them depends on
you requirements. By filtering the published data, you can:
1. Minimize the amount of data sent over the network.
2. Reduce the amount of storage space required at the Subscriber.
3. Customize publications and applications based on individual Subscriber
requirements.
4. Avoid or reduce conflicts because the different data partitions can be
sent to different Subscribers (no two Subscribers will be updating the same
data values).
Row and column filters can be used with snapshot, transactional, and merge
publications. Row filters use the WHERE clause of a SQL statement and
restrict the rows included in a publication based on specific criteria.
Column filters restrict the columns that are included in a publication.
For more information regarding this issue, please refer to the following
articles on SQL Server Books Online.
Topic:" Filtering Published Data"
Topic:" Filter Data"
Thank for using MSDN newsgroup.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

Replication error on a non-replicated DB?

I'm running in to a problem that I can't seem to find any answers for,
and I'd appreciate any help people can give.
I have a table with a couple integers and a single text column in it.
Recently an update stored procedure started occasionally throwing the
following error:
7139:
Length of text, ntext, or image data (65637) to be replicated exceeds
configured maximum 65536.
All my searching in the BOL and newsgroups comes up with are cases
where people are replicating these columns and just don't correctly
size the config value for 'max text repl size'.
However, in this case I'm totally stumped as to why the error is
occurring in the first place because we *aren't using replication* on
any of our DBs.
As expected, Enterprise Manager says the DB is not configured as either
a publisher or subscriber. Is there any other place I can look to make
sure that the server isn't actually flagged as being replicated? Any
ideas as to a secondary problem that may be returning this error
number? Anything I'm totally missing here that I should have caught?
In EM. Tools>>Replication>>Configure Publishing....
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"AaronL" <alouts@.hotmail.com> wrote in message
news:1136415873.886603.103150@.o13g2000cwo.googlegr oups.com...
> I'm running in to a problem that I can't seem to find any answers for,
> and I'd appreciate any help people can give.
> I have a table with a couple integers and a single text column in it.
> Recently an update stored procedure started occasionally throwing the
> following error:
> 7139:
> Length of text, ntext, or image data (65637) to be replicated exceeds
> configured maximum 65536.
> All my searching in the BOL and newsgroups comes up with are cases
> where people are replicating these columns and just don't correctly
> size the config value for 'max text repl size'.
> However, in this case I'm totally stumped as to why the error is
> occurring in the first place because we *aren't using replication* on
> any of our DBs.
> As expected, Enterprise Manager says the DB is not configured as either
> a publisher or subscriber. Is there any other place I can look to make
> sure that the server isn't actually flagged as being replicated? Any
> ideas as to a secondary problem that may be returning this error
> number? Anything I'm totally missing here that I should have caught?
>
|||You should not be getting this message. There is something very wrong here.
Can we see the trigger and the table schema to try to repro it. Also we need
to know the SQL Server version and sp.
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
"AaronL" <alouts@.hotmail.com> wrote in message
news:1136415873.886603.103150@.o13g2000cwo.googlegr oups.com...
> I'm running in to a problem that I can't seem to find any answers for,
> and I'd appreciate any help people can give.
> I have a table with a couple integers and a single text column in it.
> Recently an update stored procedure started occasionally throwing the
> following error:
> 7139:
> Length of text, ntext, or image data (65637) to be replicated exceeds
> configured maximum 65536.
> All my searching in the BOL and newsgroups comes up with are cases
> where people are replicating these columns and just don't correctly
> size the config value for 'max text repl size'.
> However, in this case I'm totally stumped as to why the error is
> occurring in the first place because we *aren't using replication* on
> any of our DBs.
> As expected, Enterprise Manager says the DB is not configured as either
> a publisher or subscriber. Is there any other place I can look to make
> sure that the server isn't actually flagged as being replicated? Any
> ideas as to a secondary problem that may be returning this error
> number? Anything I'm totally missing here that I should have caught?
>
|||Fair enough - I suspected I'd either get a quick hit answer if this
were a known problem I had overlooked or a request for more info.
Unfortunately, after trying to create a concise script to duplicate the
problem, I ended up just thoroughly confused.
Performing the update against a cleanly created test table in the same
DB (using SELECT * INTO ... syntax) wont't fail, but the table in
question does.
After doing some more research, it looks like even though the DB is
showing up in Enterprise Manager as not being set up for replication,
10 of the 400 tables in this test environment actually have related
entries named syncobj_XXXX... appearing in the information_schema
views. Two of those 10 tables contain a text column and those are the
only two cases where I can force an update to fail with this error.
So, at this point the urgency is down quite a bit; I've narrowed the
problem down significantly, and confirmed that it is limited to my test
environment.
It appears as if there may have been an aborted/corrupt replication
configuration set up by someone/something. I'm still not entirely sure
I understand how we could have arrived in this situation, but it
requires a little more research and most likely a separate posting.
In the meantime, if anyone has any ideas how a DB can arrive in a state
where there are these syncobj_XXX entries in the system tables while
the DB still reports itself as not being either a publisher or
subscriber, I'd appreciate the insight. We don't use replication
anywhere, so I wouldn't expect this to be the case, but on the off
chance one of my developers had tried configuring this environment to
replicate through the wizard, then tried to undo those steps in the
wizard, would this be the result?
Better yet - any idea how/where I can safely remove these things?
Thanks.
-Aaron

Replication error on a non-replicated DB?

I'm running in to a problem that I can't seem to find any answers for,
and I'd appreciate any help people can give.
I have a table with a couple integers and a single text column in it.
Recently an update stored procedure started occasionally throwing the
following error:
7139:
Length of text, ntext, or image data (65637) to be replicated exceeds
configured maximum 65536.
All my searching in the BOL and newsgroups comes up with are cases
where people are replicating these columns and just don't correctly
size the config value for 'max text repl size'.
However, in this case I'm totally stumped as to why the error is
occurring in the first place because we *aren't using replication* on
any of our DBs.
As expected, Enterprise Manager says the DB is not configured as either
a publisher or subscriber. Is there any other place I can look to make
sure that the server isn't actually flagged as being replicated? Any
ideas as to a secondary problem that may be returning this error
number? Anything I'm totally missing here that I should have caught?In EM. Tools>>Replication>>Configure Publishing....
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"AaronL" <alouts@.hotmail.com> wrote in message
news:1136415873.886603.103150@.o13g2000cwo.googlegroups.com...
> I'm running in to a problem that I can't seem to find any answers for,
> and I'd appreciate any help people can give.
> I have a table with a couple integers and a single text column in it.
> Recently an update stored procedure started occasionally throwing the
> following error:
> 7139:
> Length of text, ntext, or image data (65637) to be replicated exceeds
> configured maximum 65536.
> All my searching in the BOL and newsgroups comes up with are cases
> where people are replicating these columns and just don't correctly
> size the config value for 'max text repl size'.
> However, in this case I'm totally stumped as to why the error is
> occurring in the first place because we *aren't using replication* on
> any of our DBs.
> As expected, Enterprise Manager says the DB is not configured as either
> a publisher or subscriber. Is there any other place I can look to make
> sure that the server isn't actually flagged as being replicated? Any
> ideas as to a secondary problem that may be returning this error
> number? Anything I'm totally missing here that I should have caught?
>|||You should not be getting this message. There is something very wrong here.
Can we see the trigger and the table schema to try to repro it. Also we need
to know the SQL Server version and sp.
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
"AaronL" <alouts@.hotmail.com> wrote in message
news:1136415873.886603.103150@.o13g2000cwo.googlegroups.com...
> I'm running in to a problem that I can't seem to find any answers for,
> and I'd appreciate any help people can give.
> I have a table with a couple integers and a single text column in it.
> Recently an update stored procedure started occasionally throwing the
> following error:
> 7139:
> Length of text, ntext, or image data (65637) to be replicated exceeds
> configured maximum 65536.
> All my searching in the BOL and newsgroups comes up with are cases
> where people are replicating these columns and just don't correctly
> size the config value for 'max text repl size'.
> However, in this case I'm totally stumped as to why the error is
> occurring in the first place because we *aren't using replication* on
> any of our DBs.
> As expected, Enterprise Manager says the DB is not configured as either
> a publisher or subscriber. Is there any other place I can look to make
> sure that the server isn't actually flagged as being replicated? Any
> ideas as to a secondary problem that may be returning this error
> number? Anything I'm totally missing here that I should have caught?
>|||Fair enough - I suspected I'd either get a quick hit answer if this
were a known problem I had overlooked or a request for more info.
Unfortunately, after trying to create a concise script to duplicate the
problem, I ended up just thoroughly confused.
Performing the update against a cleanly created test table in the same
DB (using SELECT * INTO ... syntax) wont't fail, but the table in
question does.
After doing some more research, it looks like even though the DB is
showing up in Enterprise Manager as not being set up for replication,
10 of the 400 tables in this test environment actually have related
entries named syncobj_XXXX... appearing in the information_schema
views. Two of those 10 tables contain a text column and those are the
only two cases where I can force an update to fail with this error.
So, at this point the urgency is down quite a bit; I've narrowed the
problem down significantly, and confirmed that it is limited to my test
environment.
It appears as if there may have been an aborted/corrupt replication
configuration set up by someone/something. I'm still not entirely sure
I understand how we could have arrived in this situation, but it
requires a little more research and most likely a separate posting.
In the meantime, if anyone has any ideas how a DB can arrive in a state
where there are these syncobj_XXX entries in the system tables while
the DB still reports itself as not being either a publisher or
subscriber, I'd appreciate the insight. We don't use replication
anywhere, so I wouldn't expect this to be the case, but on the off
chance one of my developers had tried configuring this environment to
replicate through the wizard, then tried to undo those steps in the
wizard, would this be the result?
Better yet - any idea how/where I can safely remove these things?
Thanks.
-Aaronsql

Replication error on a non-replicated DB?

I'm running in to a problem that I can't seem to find any answers for,
and I'd appreciate any help people can give.
I have a table with a couple integers and a single text column in it.
Recently an update stored procedure started occasionally throwing the
following error:
7139:
Length of text, ntext, or image data (65637) to be replicated exceeds
configured maximum 65536.
All my searching in the BOL and newsgroups comes up with are cases
where people are replicating these columns and just don't correctly
size the config value for 'max text repl size'.
However, in this case I'm totally stumped as to why the error is
occurring in the first place because we *aren't using replication* on
any of our DBs.
As expected, Enterprise Manager says the DB is not configured as either
a publisher or subscriber. Is there any other place I can look to make
sure that the server isn't actually flagged as being replicated? Any
ideas as to a secondary problem that may be returning this error
number? Anything I'm totally missing here that I should have caught?In EM. Tools>>Replication>>Configure Publishing....
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"AaronL" <alouts@.hotmail.com> wrote in message
news:1136415873.886603.103150@.o13g2000cwo.googlegroups.com...
> I'm running in to a problem that I can't seem to find any answers for,
> and I'd appreciate any help people can give.
> I have a table with a couple integers and a single text column in it.
> Recently an update stored procedure started occasionally throwing the
> following error:
> 7139:
> Length of text, ntext, or image data (65637) to be replicated exceeds
> configured maximum 65536.
> All my searching in the BOL and newsgroups comes up with are cases
> where people are replicating these columns and just don't correctly
> size the config value for 'max text repl size'.
> However, in this case I'm totally stumped as to why the error is
> occurring in the first place because we *aren't using replication* on
> any of our DBs.
> As expected, Enterprise Manager says the DB is not configured as either
> a publisher or subscriber. Is there any other place I can look to make
> sure that the server isn't actually flagged as being replicated? Any
> ideas as to a secondary problem that may be returning this error
> number? Anything I'm totally missing here that I should have caught?
>|||You should not be getting this message. There is something very wrong here.
Can we see the trigger and the table schema to try to repro it. Also we need
to know the SQL Server version and sp.
--
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
"AaronL" <alouts@.hotmail.com> wrote in message
news:1136415873.886603.103150@.o13g2000cwo.googlegroups.com...
> I'm running in to a problem that I can't seem to find any answers for,
> and I'd appreciate any help people can give.
> I have a table with a couple integers and a single text column in it.
> Recently an update stored procedure started occasionally throwing the
> following error:
> 7139:
> Length of text, ntext, or image data (65637) to be replicated exceeds
> configured maximum 65536.
> All my searching in the BOL and newsgroups comes up with are cases
> where people are replicating these columns and just don't correctly
> size the config value for 'max text repl size'.
> However, in this case I'm totally stumped as to why the error is
> occurring in the first place because we *aren't using replication* on
> any of our DBs.
> As expected, Enterprise Manager says the DB is not configured as either
> a publisher or subscriber. Is there any other place I can look to make
> sure that the server isn't actually flagged as being replicated? Any
> ideas as to a secondary problem that may be returning this error
> number? Anything I'm totally missing here that I should have caught?
>|||Fair enough - I suspected I'd either get a quick hit answer if this
were a known problem I had overlooked or a request for more info.
Unfortunately, after trying to create a concise script to duplicate the
problem, I ended up just thoroughly confused.
Performing the update against a cleanly created test table in the same
DB (using SELECT * INTO ... syntax) wont't fail, but the table in
question does.
After doing some more research, it looks like even though the DB is
showing up in Enterprise Manager as not being set up for replication,
10 of the 400 tables in this test environment actually have related
entries named syncobj_XXXX... appearing in the information_schema
views. Two of those 10 tables contain a text column and those are the
only two cases where I can force an update to fail with this error.
So, at this point the urgency is down quite a bit; I've narrowed the
problem down significantly, and confirmed that it is limited to my test
environment.
It appears as if there may have been an aborted/corrupt replication
configuration set up by someone/something. I'm still not entirely sure
I understand how we could have arrived in this situation, but it
requires a little more research and most likely a separate posting.
In the meantime, if anyone has any ideas how a DB can arrive in a state
where there are these syncobj_XXX entries in the system tables while
the DB still reports itself as not being either a publisher or
subscriber, I'd appreciate the insight. We don't use replication
anywhere, so I wouldn't expect this to be the case, but on the off
chance one of my developers had tried configuring this environment to
replicate through the wizard, then tried to undo those steps in the
wizard, would this be the result?
Better yet - any idea how/where I can safely remove these things?
Thanks.
-Aaron

Replication error 28037

I am using merge replication between SQL Server 2000 sp3a running on windows
2000 server sp4 and sql server ce 2.0 over gprs.
First time when I synchronize is working fine, but when I add or modify data
in sql server ce database and try to replicate over gprs I get error 28037
(A request to send data to the computer running IIS has failed), during this
time my pocket pc is connected with the server (it respond to ping from the
computer running sql server, and pocket ie shows SQL Server CE Server
Agent ).
I tried to test replication on another database on only one table, it works
ok first time but when I modify many records on pocket pc database (100) and
replication fails with error 28037. If I modify the same amount of records
on server replication is working.
When I get this error I can not longer replicate over gprs but with cradle
it works. After I replicate on cradle and there is not data modified if I
replicate over gprs it works.
I believed that the amount of data uploaded from pocket pc to sql server is
to much so I created a new default merge agent profile with
downloadgenerationsperbatch set to 10 and uploadgenerationsperbatch set to 1
and recreated the publication, but nothing changed.
Any suggestion is welcome
thanks
Marius
Marius,
I am having exactly the same problem...did you manage to find out any solution?
Are we the only two people in the worldusing merge replication over GPRS? I hope not!
Cheers,
Nick
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
|||Hi Nick,
Till now I did not found any solution, we contacted microsoft support but
the problem still persist although I tried a hot fix and defined custom
merge agents. When we lead to a conclusion I'll let you know.
The strange thing is that the problem appears over GPRS with RDA also if
application try to pull a table that contains more then 20 changes (updates,
inserts, deletes), this number may vary but in small bounds.

> Marius,
> I am having exactly the same problem...did you manage to find out any
solution?
> Are we the only two people in the worldusing merge replication over GPRS?
I hope not!
> Cheers,
> Nick
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.

Replication ERROR 208: invalid object name 'syspublications'

Hello,
Server A and B are both running on NT4.0 SP6.
Server A and B both have SQL Server 2000 SP2 installed.
Server A is the Publisher and Distributor.
Server B is the subscriber.
At one time B was able to see and subscribe to Publication on Server A.
I manually deleted all traces of replications from syspublications,
sysarticles, syssubscriptions, basically from all of the replication tables
in the user database.
I then recreated the publication on Server A.
I then open Enterprise Manager on Server B, Right clicked on Subscriptions
and selected "New Pull Scriptions...". The wizard appears and list Server A
and when I try to expand the server to list the Publication an error message
appears stating:
"ERROR 208: Invalid object name 'syspublications'. Invalid object name
'sysextendedarticlesview'."
Any idea what might be causing this problem?
Thank you.
Scott
Are these objects on the subscriber? If not, locate them on a different
subscriber/publisher, right click on them in EM and select copy. Then go
back to your subscriber and paste them into query analyzer and execute the
script.
Then try again.
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
"Scott Yu" <ScottYu@.discussions.microsoft.com> wrote in message
news:344E037E-9CB8-4D5E-BF53-CE16EBB564E2@.microsoft.com...
> Hello,
> Server A and B are both running on NT4.0 SP6.
> Server A and B both have SQL Server 2000 SP2 installed.
> Server A is the Publisher and Distributor.
> Server B is the subscriber.
> At one time B was able to see and subscribe to Publication on Server A.
> I manually deleted all traces of replications from syspublications,
> sysarticles, syssubscriptions, basically from all of the replication
> tables
> in the user database.
> I then recreated the publication on Server A.
> I then open Enterprise Manager on Server B, Right clicked on Subscriptions
> and selected "New Pull Scriptions...". The wizard appears and list Server
> A
> and when I try to expand the server to list the Publication an error
> message
> appears stating:
> "ERROR 208: Invalid object name 'syspublications'. Invalid object name
> 'sysextendedarticlesview'."
> Any idea what might be causing this problem?
> Thank you.
> Scott
|||Hello Hilary,
Are these objects on the subscriber?
######################################
Every article in the publication reside on Server A, which act as the
Publisher and Distributor. All articles that are part of the replication job
are dropped from Server B before Server B take part in the replication. From
Server B, I would like to create a Pull subscription and set the option to
create the schema from the snapshot on Server A, but every time I do this I
get an error message. The message states "SQL Server Enterprise Manager
Could Not Retrieve Information About The Publication From the Publisher.
ERROR 208: Invalid object name 'syspublications'. Invalid object name."
I was able to ping Server A from Server B and all of the linked services
between Server A and B are working fine in both direction.
I am currently able to Push the subscription down to Server B from Server A,
but would like to have Server B Pull the subscription instead.
Hope this answers your question. Thank you so much for your help.
Scott
"Hilary Cotter" wrote:

> Are these objects on the subscriber? If not, locate them on a different
> subscriber/publisher, right click on them in EM and select copy. Then go
> back to your subscriber and paste them into query analyzer and execute the
> script.
> Then try again.
> --
> 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
> "Scott Yu" <ScottYu@.discussions.microsoft.com> wrote in message
> news:344E037E-9CB8-4D5E-BF53-CE16EBB564E2@.microsoft.com...
>
>
sql

Replication Error

I have three questions?
1. I have a Sql server instance running without any name, it just has a
default name (local), when i try to go configure replication its says that
replication does not support (local) name. This server is in production
right now. And it will be hard to shut it down. Is there anyway to change
the name of the instance? Even if i unregister and re-register a new
server, will all my databses and plans, etc. will be the exact same as on
the last instance? Whats the best possible way can i configure replication
on this instance?
2. I was subscribed to a couple push subscriptions replication from a
different server, but that server no longer exists, but my server still goes
out and looks for the subscription, I tried to remove the subscription, but
i get the same errror like on the question one, "replication does not
support (local) name." so i can't remove the subscription. What can i do to
remove this subscriptions?
3. When i try to configure the Replication, its says that Server Agent uses
the local service account you need to change the account? Whats the Local
service account? Can two services not use the same local service account?
How do i need to set this up, to use the account? Do i need to create
another account?
Thanks for all your help.
1. Use the netbios name of the computer (select @.@.servername)
2. REgister the netbiosname as above in EM (after removing the local one).
3. This should be set as a domain user in settings, comtrol panel, admin
tools, services, SQLSERVERAGENT. This account should be a sysadmin in sql
server, and a local administrator. The easiest thing to do is to set the sql
server service and sqlserver agent to use the same account having the
attributes above.
You can use local system accounts to test replication when setting it up on
one machine.
Replication functions as a series of jobs running as the sql server agent,
so this account should have access to the snapshot share on the publisher
and access to the publication (more details in BOL, 'publication access
list' and 'replication security').
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql

Replication Distribution Agent cpu continues to grow

We have transactional replication set up. The agent is running on the
subscriber. However the CPU continues to grow on the subscriber. Yesterday
morning the cpu was about ten thousand and this afternoon it reached over a
million cpu and continued to grow until I stopped and restarted the the pull
subscription agent on the distribution server. Then it went down to about a
hundred cpu. As I'm writing this article its already up to three thousand cpu.
Is there a profile setting that I'm missing?
Thanks all in advance!!
Message posted via http://www.droptable.com
On Mar 1, 1:39 pm, "fnadal via droptable.com" <u10790@.uwe> wrote:
> We have transactional replication set up. The agent is running on the
> subscriber. However the CPU continues to grow on the subscriber. Yesterday
> morning the cpu was about ten thousand and this afternoon it reached over a
> million cpu and continued to grow until I stopped and restarted the the pull
> subscription agent on the distribution server. Then it went down to about a
> hundred cpu. As I'm writing this article its already up to three thousand cpu.
> Is there a profile setting that I'm missing?
> Thanks all in advance!!
> --
> Message posted viahttp://www.droptable.com
Can you explain a little more clearly? Exactly what statistic are you
looking at? What value is growing?
|||The cpu usage. When I do a select spid,loginame,program_name, cpu from master.
sysprocesses where kpid > 0, I see Replication Distribution Agent cpu at
3000. Earlier this afternoon the cpu from sysprocesses was over a million for
that agent.
Tracy McKibben wrote:
>[quoted text clipped - 9 lines]
>Can you explain a little more clearly? Exactly what statistic are you
>looking at? What value is growing?
Message posted via http://www.droptable.com
|||That is a cumulative value since the connection started, not an immediate
usage value.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"fnadal via droptable.com" <u10790@.uwe> wrote in message
news:6e8b921a334f3@.uwe...
> The cpu usage. When I do a select spid,loginame,program_name, cpu from
> master.
> sysprocesses where kpid > 0, I see Replication Distribution Agent cpu at
> 3000. Earlier this afternoon the cpu from sysprocesses was over a million
> for
> that agent.
> Tracy McKibben wrote:
> --
> Message posted via http://www.droptable.com
>
|||How would I obtain an immediate usage value?
Geoff N. Hiten wrote:[vbcol=seagreen]
>That is a cumulative value since the connection started, not an immediate
>usage value.
>[quoted text clipped - 12 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200703/1

Replication Distribution Agent cpu continues to grow

We have transactional replication set up. The agent is running on the
subscriber. However the CPU continues to grow on the subscriber. Yesterday
morning the cpu was about ten thousand and this afternoon it reached over a
million cpu and continued to grow until I stopped and restarted the the pull
subscription agent on the distribution server. Then it went down to about a
hundred cpu. As I'm writing this article its already up to three thousand cp
u.
Is there a profile setting that I'm missing?
Thanks all in advance!!
Message posted via http://www.droptable.comOn Mar 1, 1:39 pm, "fnadal via droptable.com" <u10790@.uwe> wrote:
> We have transactional replication set up. The agent is running on the
> subscriber. However the CPU continues to grow on the subscriber. Yesterday
> morning the cpu was about ten thousand and this afternoon it reached over
a
> million cpu and continued to grow until I stopped and restarted the the pu
ll
> subscription agent on the distribution server. Then it went down to about
a
> hundred cpu. As I'm writing this article its already up to three thousand
cpu.
> Is there a profile setting that I'm missing?
> Thanks all in advance!!
> --
> Message posted viahttp://www.droptable.com
Can you explain a little more clearly? Exactly what statistic are you
looking at? What value is growing?|||The cpu usage. When I do a select spid,loginame,program_name, cpu from maste
r.
sysprocesses where kpid > 0, I see Replication Distribution Agent cpu at
3000. Earlier this afternoon the cpu from sysprocesses was over a million fo
r
that agent.
Tracy McKibben wrote:
>[quoted text clipped - 9 lines]
>Can you explain a little more clearly? Exactly what statistic are you
>looking at? What value is growing?
Message posted via http://www.droptable.com|||That is a cumulative value since the connection started, not an immediate
usage value.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"fnadal via droptable.com" <u10790@.uwe> wrote in message
news:6e8b921a334f3@.uwe...
> The cpu usage. When I do a select spid,loginame,program_name, cpu from
> master.
> sysprocesses where kpid > 0, I see Replication Distribution Agent cpu at
> 3000. Earlier this afternoon the cpu from sysprocesses was over a million
> for
> that agent.
> Tracy McKibben wrote:
> --
> Message posted via http://www.droptable.com
>|||How would I obtain an immediate usage value?
Geoff N. Hiten wrote:[vbcol=seagreen]
>That is a cumulative value since the connection started, not an immediate
>usage value.
>
>[quoted text clipped - 12 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200703/1sql

Replication Distribution Agent cpu continues to grow

We have transactional replication set up. The agent is running on the
subscriber. However the CPU continues to grow on the subscriber. Yesterday
morning the cpu was about ten thousand and this afternoon it reached over a
million cpu and continued to grow until I stopped and restarted the the pull
subscription agent on the distribution server. Then it went down to about a
hundred cpu. As I'm writing this article its already up to three thousand cpu.
Is there a profile setting that I'm missing?
Thanks all in advance!!
--
Message posted via http://www.sqlmonster.comOn Mar 1, 1:39 pm, "fnadal via SQLMonster.com" <u10790@.uwe> wrote:
> We have transactional replication set up. The agent is running on the
> subscriber. However the CPU continues to grow on the subscriber. Yesterday
> morning the cpu was about ten thousand and this afternoon it reached over a
> million cpu and continued to grow until I stopped and restarted the the pull
> subscription agent on the distribution server. Then it went down to about a
> hundred cpu. As I'm writing this article its already up to three thousand cpu.
> Is there a profile setting that I'm missing?
> Thanks all in advance!!
> --
> Message posted viahttp://www.sqlmonster.com
Can you explain a little more clearly? Exactly what statistic are you
looking at? What value is growing?|||The cpu usage. When I do a select spid,loginame,program_name, cpu from master.
sysprocesses where kpid > 0, I see Replication Distribution Agent cpu at
3000. Earlier this afternoon the cpu from sysprocesses was over a million for
that agent.
Tracy McKibben wrote:
>> We have transactional replication set up. The agent is running on the
>> subscriber. However the CPU continues to grow on the subscriber. Yesterday
>[quoted text clipped - 9 lines]
>> --
>> Message posted viahttp://www.sqlmonster.com
>Can you explain a little more clearly? Exactly what statistic are you
>looking at? What value is growing?
--
Message posted via http://www.sqlmonster.com|||That is a cumulative value since the connection started, not an immediate
usage value.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"fnadal via SQLMonster.com" <u10790@.uwe> wrote in message
news:6e8b921a334f3@.uwe...
> The cpu usage. When I do a select spid,loginame,program_name, cpu from
> master.
> sysprocesses where kpid > 0, I see Replication Distribution Agent cpu at
> 3000. Earlier this afternoon the cpu from sysprocesses was over a million
> for
> that agent.
> Tracy McKibben wrote:
>> We have transactional replication set up. The agent is running on the
>> subscriber. However the CPU continues to grow on the subscriber.
>> Yesterday
>>[quoted text clipped - 9 lines]
>> --
>> Message posted viahttp://www.sqlmonster.com
>>Can you explain a little more clearly? Exactly what statistic are you
>>looking at? What value is growing?
> --
> Message posted via http://www.sqlmonster.com
>|||How would I obtain an immediate usage value?
Geoff N. Hiten wrote:
>That is a cumulative value since the connection started, not an immediate
>usage value.
>> The cpu usage. When I do a select spid,loginame,program_name, cpu from
>> master.
>[quoted text clipped - 12 lines]
>>Can you explain a little more clearly? Exactly what statistic are you
>>looking at? What value is growing?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200703/1

Replication Deadlocks

Hello,
I am running multiple replication publication on a SQL 2k database, with
a separate server as distribution agent, then finally push subscriptions
to multiple databases across 5 servers.
All publications use row and column filtering in them.
My question is, can I use locking hints on the selection criteria for
the row filtering?
ie. Can I specify WITH (NOLOCK) on the row filtering so that it
eliminates the majority of the blocking / deadlocks?
Thanks
Dave
*** Sent via Developersdex http://www.codecomments.com ***
You could if it was a subquery. Do you have indexes on the join
condition/filter key in your filter?
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
"Dave" <Anonymous@.devdex.com> wrote in message
news:eqm8zv1THHA.5060@.TK2MSFTNGP02.phx.gbl...
>
> Hello,
> I am running multiple replication publication on a SQL 2k database, with
> a separate server as distribution agent, then finally push subscriptions
> to multiple databases across 5 servers.
> All publications use row and column filtering in them.
> My question is, can I use locking hints on the selection criteria for
> the row filtering?
> ie. Can I specify WITH (NOLOCK) on the row filtering so that it
> eliminates the majority of the blocking / deadlocks?
> Thanks
> Dave
> *** Sent via Developersdex http://www.codecomments.com ***
|||There is an index on the originating table/column, but the deadlocking
appears to be happening on the distribution database.
David
*** Sent via Developersdex http://www.codecomments.com ***

Replication Deadlock...I think

I am running a select cursor on spid:61 and an update statement (on a table common with the select in spid:61) on spid:51. While running, I experience a deadlock. The Enterprise Manager reports that spid:61 (Blocking) and spid:51 (Blocked by 61). SP_LOCK reports the following (with object_name resolved):

object_name spid mode status
------- ---- ---- --
NULL 51 S GRANT
sp_MSaddpub_snapshot 51 IX WAIT
sp_replflush 61 IS GRANT
sp_replcounters 61 IS GRANT
sp_changesubscriptio 61 IS GRANT
sp_MSscript_sync_del 61 IS GRANT
sp_MSscript_sync_ins 61 IS GRANT
sp_replpostschema 61 IS GRANT
sp_articleview 61 IS GRANT
sp_MSaddschemaarticl 61 IS GRANT
sp_changemergepublic 61 IS GRANT
sp_MSmakeconflicttab 61 IS GRANT
sp_MScopyscriptfile 61 IS GRANT
sp_MSuniquecolname 61 IS GRANT
sp_MSget_synctran_co 61 IS GRANT
sp_MShelptranconflic 61 IS GRANT
sp_replsetsyncstatus 61 IS GRANT
sp_MSaddpub_snapshot 61 S GRANT
NULL 61 S GRANT

(19 row(s) affected)

I see that the two SPIDs have the sp_MSaddpub_snapshot in common. This stored procedure attempts to update the syspublications table however I fail to see why this would generate a deadlock. I have rebooted the server, disabled and re-enabled replication. Any help would be appreciated.

--Other info--

Microsoft SQL Server 2000 Enterprise sp3 (active-passive cluster)
4 x 1.4ghz Xeon, 8gig (/3gb /pae)
Microsoft Windows 2000 Advanced Server sp3 (Identical active-passive nodes)

Database connection thru Trifox's "Vortex" DBlib linked client-server driver.What type of replication are you using ? Can you post what you are doing with the select cursor/update ?|||Also, does the deadlock occur immediately ?|||>>What kind of replication?
The server was initially set up with trans replication. I have since rebooted and removed replication alltogether and selectivly readded components, reproducing the deadlock behavior at every step.

>>does it happen immediately?
There are a number of other SELECT statements between the (Blocking) SELECT statement and when the UPDATE fires (approx 60-90 sec later). I can see the SQL:STMTStarting in the Profiler for the UPDATE statement however it never completes before the one hour connection timeout from the Vortex driver. I can KILL the SPID for the (Blocking) SELECT statement and then the (Blocked) UPDATE will fire. . . however I am not done fetching records from the SELECT recordset and the "fetch next row" from that cursor will fail (ConnectionWrite(Send())) or something like that -- typical MS message when trying to work with a connection killed at the server level. The SP_LOCK information indicates that the initial SELECT does not have any table locks and only low level child REPLICATION stored procedures are holding any locks.

>>What are the statements?
The initial SELECT statement is about 3 pages long with a number of sub-selects and derived tables from about 12 different tables. I really hope the solution does not depend on the nature of the SELECT statement; however I will post it if you feel it will help. I can run the SELECT statement in the QA without any problems. It returns the thousand or so expected records in about 30 seconds.
The UPDATE statement is trival e.g.

UPDATE owner.table
set column_a = 1
where column_b = 'value'

I appreciate your time.|||Have you tried adding readuncommitted/nolock to your select statement (because this sounds like nested transactions that are attempting to lock the same resource) ? Realize that this will give you dirty records - but test it to see if you still have the problem.|||I see now... The profiler reveals Lock Escalations (after a bit) which then cause the blocking. The NOLOCK seems to supress the lock escalation. Thank you.|||Originally posted by Makallus
I see now... The profiler reveals Lock Escalations (after a bit) which then cause the blocking. The NOLOCK seems to supress the lock escalation. Thank you.

Yeah, and let's you read uncommited transactions...

Expect miracles to start happening...|||Originally posted by Makallus
>>>>What are the statements?
The initial SELECT statement is about 3 pages long with a number of sub-selects and derived tables from about 12 different tables. I really hope the solution does not depend on the nature of the SELECT statement; however I will post it if you feel it will help. I can run the SELECT statement in the QA without any problems. It returns the thousand or so expected records in about 30 seconds.


What do you do with the result set? ARe youdragging thousands of rows to the client?

Is it done in a sproc?

Are you placing the results in table variables?

How does your update coorelate to the thousands of rows?

Why do the SELECT at all? Why not make the UPDATE a JOIN?|||I would look at your logic and modify it to prevent the lock escalation. Depending on your database/applications, reading dirty records could cause some major problems.|||All excellent replies. The reasons we (the company) do things this way come directly from Dilbert comic strips.

Brett, ever code in Dibol? What's better than coding in Dibol with database access is the fantasy we will be database independent. The idea being we can slap in any database the customer likes with no impact on the application. Shall I go on? However I understand your questions and I believe in time the application will behave more like it was written for a database than an ISAM file.

Rnealejr, this problem only occurs at a site where replication is enabled . . . given that there is no replication I do not appear to exceed the lock threshold.

As for doing things right. . . all good things in time.|||The lock escalation issue may appear in non-replication scenarios as well - but be intermittent. I just wanted to make sure that you understood the ramifications.sql

Replication conflict tables

Greetings!
We have been running transactional replication environment in development and QA test. The other evening in a "emergency", development requested several table changes to many of the published tables. I was out of the office and with no laptop available,
I tried to blindly guide a coworker through the process of reestablishing the articles and the subscription (not entirely sure of what he had removed). Apparently, we erred and he published and subscribed everything, but now we have a conflict_ table fo
r every single table in the publication. None of the tables have any rows. The published and subscriber database are both on the same server (as is the distribution database).
I can remove the conflict tables for the subscriber database, but they also exist in the published database as system tables which can't be dropped. I haven't run across an sp that can remove these and am thinking I won't be able to short of rebuilding t
he database. Might've SQL Server gotten the idea that this was to be merge replication? That seems like the only reason I got these. Is there any clean way to remove these conflict tables without rebuilding the database?
Thanks much in advance!
Steve
It sounds like he created a merge publication and then deleted it. You can
safely drop these conflict tables (as long as you aren't running any merge,
immediate updating or queued updating subscribers) by runing the following
command in QA
select 'drop table '+name from sysobjects where name like 'conflict_%'
Then in your results pane select the results and then paste it back in the
Query pane, and exectue it.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Steve H" <shoerle@.charter.net> wrote in message
news:19042C30-8B0A-41D3-AD44-4DE62725C3C2@.microsoft.com...
> Greetings!
> We have been running transactional replication environment in development
and QA test. The other evening in a "emergency", development requested
several table changes to many of the published tables. I was out of the
office and with no laptop available, I tried to blindly guide a coworker
through the process of reestablishing the articles and the subscription (not
entirely sure of what he had removed). Apparently, we erred and he
published and subscribed everything, but now we have a conflict_ table for
every single table in the publication. None of the tables have any rows.
The published and subscriber database are both on the same server (as is the
distribution database).
> I can remove the conflict tables for the subscriber database, but they
also exist in the published database as system tables which can't be
dropped. I haven't run across an sp that can remove these and am thinking I
won't be able to short of rebuilding the database. Might've SQL Server
gotten the idea that this was to be merge replication? That seems like the
only reason I got these. Is there any clean way to remove these conflict
tables without rebuilding the database?
> Thanks much in advance!
> Steve

Replication Conflict Auto Notification (How)

Hi

I'm Running SQL 2k - Mainly Merge Replication.

Can Someone point me in the right direction on how best to Alert myself/Operator via email when a conflict occurs.

I use xp_sendmail regularly so can deal with that part

I've tried looking for a solution but with no success.

Having to keep opening the Conflict Viewer to check is becomming a Bit Tiresome.

The Only way I can think of is running a batch Job to look at the rowcount of conflict tables (Possibly using a cursor and dynamically built Select Count(*) statements from sysobjects to get conflict table names).

or

If Conflicts are flagged in the system Replication tables query them but I don't know which table if any exist.

Theres gotta be an easier way - I must be missing something

Any Help Greatly appreciated

thx

GWCheck Lumigent (http://www.lumigent.com) website for latest tool on notification and its free.|||Cool

thx again Satya

GW