Hi,
We are using transactional replication and it failed and
showing following error
Error:
The process could not bulk copy into table 'TableName'.
Error Number:
22001
Category:ODBC
Source: ODBC
Full Message:
String data, right truncation
Is any one having solution for this?
Regards,
Manoj
Manoj,
can you check if the schemas are identical on publisher and subscriber if
you are doing a nosync initialization.
If you are sending over the table schema, can you ensure that you take the
collation.
Regards,
Paul Ibison
Showing posts with label table. Show all posts
Showing posts with label table. Show all posts
Friday, March 30, 2012
Replication errors - vicious circle
When I try to create a transactional publication, when I select the table I
want to replicate, I get the error "This table cannot be published because it
does not have a primary key column. Primary key columns are required for all
tables in transactional publications".
Then When I try to make the necessary changes to the table, such as adding a
column for the primary key, I get the error "Unable to modify table. Cannot
drop the table "tablename" because it is being used for replication."
I have never successfully replicated this table or any other. I can find no
evidence of any publications to remove.
I'm stuck!
Just answered my own question after reading thru a ton of posts on this forum.
"run sp_removedbreplication on each database..."
Thanks Paul Ibison!
"TahoePete" wrote:
> When I try to create a transactional publication, when I select the table I
> want to replicate, I get the error "This table cannot be published because it
> does not have a primary key column. Primary key columns are required for all
> tables in transactional publications".
> Then When I try to make the necessary changes to the table, such as adding a
> column for the primary key, I get the error "Unable to modify table. Cannot
> drop the table "tablename" because it is being used for replication."
> I have never successfully replicated this table or any other. I can find no
> evidence of any publications to remove.
> I'm stuck!
>
|||Issue the following statements
sp_MSunmarkreplinfo 'ProblemTableName'
Then you need to update the colstat column
sp_configure 'allow updates', 1
go
reconfigure with override
go
UPDATE syscolumns
SET colstat = colstat & ~4096
WHERE colstat & 4096 <>0
go
sp_configure 'allow updates', 2
go
reconfigure with override
go
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
"TahoePete" <TahoePete@.discussions.microsoft.com> wrote in message
news:346CE295-5031-45F3-90DA-9CA7A25424A5@.microsoft.com...
> When I try to create a transactional publication, when I select the table
> I
> want to replicate, I get the error "This table cannot be published because
> it
> does not have a primary key column. Primary key columns are required for
> all
> tables in transactional publications".
> Then When I try to make the necessary changes to the table, such as adding
> a
> column for the primary key, I get the error "Unable to modify table.
> Cannot
> drop the table "tablename" because it is being used for replication."
> I have never successfully replicated this table or any other. I can find
> no
> evidence of any publications to remove.
> I'm stuck!
>
|||I receive the error "ad hoc updates to system catalogues are not allowed"
from the update command.
"Hilary Cotter" wrote:
> Issue the following statements
> sp_MSunmarkreplinfo 'ProblemTableName'
> Then you need to update the colstat column
> sp_configure 'allow updates', 1
> go
> reconfigure with override
> go
> UPDATE syscolumns
> SET colstat = colstat & ~4096
> WHERE colstat & 4096 <>0
> go
> sp_configure 'allow updates', 2
> go
> reconfigure with override
> go
>
> --
> 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
> "TahoePete" <TahoePete@.discussions.microsoft.com> wrote in message
> news:346CE295-5031-45F3-90DA-9CA7A25424A5@.microsoft.com...
>
>
|||I'm not sure what is wrong here - this command should enable this
sp_configure 'allow updates', 1
go
reconfigure with override
go
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
"TahoePete" <TahoePete@.discussions.microsoft.com> wrote in message
news:793A5914-F3E9-4195-9F70-BA7C544E4427@.microsoft.com...[vbcol=seagreen]
>I receive the error "ad hoc updates to system catalogues are not allowed"
> from the update command.
> "Hilary Cotter" wrote:
want to replicate, I get the error "This table cannot be published because it
does not have a primary key column. Primary key columns are required for all
tables in transactional publications".
Then When I try to make the necessary changes to the table, such as adding a
column for the primary key, I get the error "Unable to modify table. Cannot
drop the table "tablename" because it is being used for replication."
I have never successfully replicated this table or any other. I can find no
evidence of any publications to remove.
I'm stuck!
Just answered my own question after reading thru a ton of posts on this forum.
"run sp_removedbreplication on each database..."
Thanks Paul Ibison!
"TahoePete" wrote:
> When I try to create a transactional publication, when I select the table I
> want to replicate, I get the error "This table cannot be published because it
> does not have a primary key column. Primary key columns are required for all
> tables in transactional publications".
> Then When I try to make the necessary changes to the table, such as adding a
> column for the primary key, I get the error "Unable to modify table. Cannot
> drop the table "tablename" because it is being used for replication."
> I have never successfully replicated this table or any other. I can find no
> evidence of any publications to remove.
> I'm stuck!
>
|||Issue the following statements
sp_MSunmarkreplinfo 'ProblemTableName'
Then you need to update the colstat column
sp_configure 'allow updates', 1
go
reconfigure with override
go
UPDATE syscolumns
SET colstat = colstat & ~4096
WHERE colstat & 4096 <>0
go
sp_configure 'allow updates', 2
go
reconfigure with override
go
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
"TahoePete" <TahoePete@.discussions.microsoft.com> wrote in message
news:346CE295-5031-45F3-90DA-9CA7A25424A5@.microsoft.com...
> When I try to create a transactional publication, when I select the table
> I
> want to replicate, I get the error "This table cannot be published because
> it
> does not have a primary key column. Primary key columns are required for
> all
> tables in transactional publications".
> Then When I try to make the necessary changes to the table, such as adding
> a
> column for the primary key, I get the error "Unable to modify table.
> Cannot
> drop the table "tablename" because it is being used for replication."
> I have never successfully replicated this table or any other. I can find
> no
> evidence of any publications to remove.
> I'm stuck!
>
|||I receive the error "ad hoc updates to system catalogues are not allowed"
from the update command.
"Hilary Cotter" wrote:
> Issue the following statements
> sp_MSunmarkreplinfo 'ProblemTableName'
> Then you need to update the colstat column
> sp_configure 'allow updates', 1
> go
> reconfigure with override
> go
> UPDATE syscolumns
> SET colstat = colstat & ~4096
> WHERE colstat & 4096 <>0
> go
> sp_configure 'allow updates', 2
> go
> reconfigure with override
> go
>
> --
> 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
> "TahoePete" <TahoePete@.discussions.microsoft.com> wrote in message
> news:346CE295-5031-45F3-90DA-9CA7A25424A5@.microsoft.com...
>
>
|||I'm not sure what is wrong here - this command should enable this
sp_configure 'allow updates', 1
go
reconfigure with override
go
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
"TahoePete" <TahoePete@.discussions.microsoft.com> wrote in message
news:793A5914-F3E9-4195-9F70-BA7C544E4427@.microsoft.com...[vbcol=seagreen]
>I receive the error "ad hoc updates to system catalogues are not allowed"
> from the update command.
> "Hilary Cotter" wrote:
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
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
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
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
Wednesday, March 28, 2012
Replication error
Hello,
I have 2BD which is installed on 2 serv #, they have same architecture (the same table, relation diagrams) I need 2rplications:
sens1=> BD1 (table1, table2) to BD2 (table1, table2)
sens2=> BD2 (table3, table4) to BD1 (table3, table4)
problem: when I want to retort one or 2 table of the BD1=>BD2
The diagram that I had on BD2 is crushed in other words the whole of the relation are re-initialized.
I found an explanation: when I notch (yes to initialize diagram and data) in the assistant of extraction of subscription the problem is to announce but the replication that passes but not as it is needed,
if not =>when I notch (Not the subscriber has already of the diagram and the data) another message is announced Impossible to find the procedure stored sp_MSupd_table1. is the replication is blocked,
ideas? thank you in advanceThe procedures mentioned above are generated by the subscription. In order to create them in your database, try this, although I can not guarantee this will work:
1) create a dummy database with no tables.
2) subscribe to the publication with this database.
3) Copy the procedures generated in the dummy database to your real subscriber.
4) Copy all the published data from the publisher to the subscriber.
5) Create the subscription with the "subscriber has already of the diagram and the data" checkbox checked.
This should get you through, but I have never tried it, so I can not be certain.
I have 2BD which is installed on 2 serv #, they have same architecture (the same table, relation diagrams) I need 2rplications:
sens1=> BD1 (table1, table2) to BD2 (table1, table2)
sens2=> BD2 (table3, table4) to BD1 (table3, table4)
problem: when I want to retort one or 2 table of the BD1=>BD2
The diagram that I had on BD2 is crushed in other words the whole of the relation are re-initialized.
I found an explanation: when I notch (yes to initialize diagram and data) in the assistant of extraction of subscription the problem is to announce but the replication that passes but not as it is needed,
if not =>when I notch (Not the subscriber has already of the diagram and the data) another message is announced Impossible to find the procedure stored sp_MSupd_table1. is the replication is blocked,
ideas? thank you in advanceThe procedures mentioned above are generated by the subscription. In order to create them in your database, try this, although I can not guarantee this will work:
1) create a dummy database with no tables.
2) subscribe to the publication with this database.
3) Copy the procedures generated in the dummy database to your real subscriber.
4) Copy all the published data from the publisher to the subscriber.
5) Create the subscription with the "subscriber has already of the diagram and the data" checkbox checked.
This should get you through, but I have never tried it, so I can not be certain.
Monday, March 26, 2012
Replication Disaster - Help
I had merge replication fully implemented and working beautifully. However,
I recently discovered a 1:many table (tblLoanException) that did not have a
foreign_key constraint created on it. So, I took the following actions:
1. I scripted out the creation of the foreign key constraint on
tblLoanException
2. I ran the script on my publisher to create the fk constraint - success
3. I ran the script on my subscriber via sp_addscriptexec - success
4. I used sp_addmergefilter to create the appropriate merge filter on the
table and it's foreignkey-reference table (tblLoan)
5. I reinitialized the snapshot (reluctantly, but I had to because of the
merge filter add) - success
6. I re-ran the merge agent, which reinitialized my subscriber data set -
success
-- THIS IS WHERE THINGS WENT INTO THE DEPTHS OF HELL
7. The merge agent initialized the subscriber, but then on the next merge
agent run, deleted all but 5 records from the tblLoan table.
8. I ran an "INSERT INTO" the tblLoan table on the publisher to get
backed-up data reinserted
9. Re-ran the merge agent
10. Same thing - merge agent deleted all but 5 additional tblLoan records,
so now I show 10 tblLoan records.
11. Repeats of steps 8 and 9 continue to yield the same results - 5 add'l
tblLoan records get added, but the rest are deleted.
What in the hell have I done here? At this point, I am unable to fix this
problem and have absolutely no idea why 5 records at a time remain in the
tblLoan table, but the rest continue to be deleted. I have several other
1:many filters on the tblLoan table and have had no issues to date. This is
very troubling, to say the least. Any help that can be offered here will be
extremely appreciated. Thanks!
Jeff Jones
Atlanta, GA
What does conflict viewer tell you? I suspect the answer is there. It is
also possible that your filter condition is incorrect. Make sure if you are
using the hostname parameter you over ride it with -HostName or you use a
pull subscription.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"J Jones" <JJones@.discussions.microsoft.com> wrote in message
news:B5FCD2B4-D15D-4CDD-94EC-B3BF7C9014DE@.microsoft.com...
>I had merge replication fully implemented and working beautifully.
>However,
> I recently discovered a 1:many table (tblLoanException) that did not have
> a
> foreign_key constraint created on it. So, I took the following actions:
> 1. I scripted out the creation of the foreign key constraint on
> tblLoanException
> 2. I ran the script on my publisher to create the fk constraint - success
> 3. I ran the script on my subscriber via sp_addscriptexec - success
> 4. I used sp_addmergefilter to create the appropriate merge filter on the
> table and it's foreignkey-reference table (tblLoan)
> 5. I reinitialized the snapshot (reluctantly, but I had to because of the
> merge filter add) - success
> 6. I re-ran the merge agent, which reinitialized my subscriber data set -
> success
> -- THIS IS WHERE THINGS WENT INTO THE DEPTHS OF HELL
> 7. The merge agent initialized the subscriber, but then on the next merge
> agent run, deleted all but 5 records from the tblLoan table.
> 8. I ran an "INSERT INTO" the tblLoan table on the publisher to get
> backed-up data reinserted
> 9. Re-ran the merge agent
> 10. Same thing - merge agent deleted all but 5 additional tblLoan
> records,
> so now I show 10 tblLoan records.
> 11. Repeats of steps 8 and 9 continue to yield the same results - 5 add'l
> tblLoan records get added, but the rest are deleted.
> What in the hell have I done here? At this point, I am unable to fix this
> problem and have absolutely no idea why 5 records at a time remain in the
> tblLoan table, but the rest continue to be deleted. I have several other
> 1:many filters on the tblLoan table and have had no issues to date. This
> is
> very troubling, to say the least. Any help that can be offered here will
> be
> extremely appreciated. Thanks!
> Jeff Jones
> Atlanta, GA
|||I receive the following message in the tblLoan conflict table:
The row was inserted at '[PublisherDB.tblLoan]' but could not be inserted at
'[SuscriberDB.tblLoan]'. INSERT statement conflicted with COLUMN FOREIGN KEY
constraint 'FK_Loan_TapeData'. The conflict occurred in database 'ResiDD',
table 'TapeData', column 'LoanID'.
This error makes absolutely no sense to me, as the records that get inserted
into tblLoan have a 1:1 relationship w/TapeData, where the TapeData.LoanID
column is an identity field, but the tblLoan.LoanID is a non-identity column.
The tblLoan records that exist on the publisher do in fact match 1:1 to
what's in the TapeData table on both the publisher and subscriber.
Once the above error occurs, the records get deleted from the
publisher.tblLoan. If I re-insert the remaining TapeData recs into tblLoan,
the merge agent will then insert 5 and only 5 tblLoan records at the
subscriber, and will delete the rest.
The merge agent worked flawlessly until I added the constraint and
mergefilter and re-initialized the snapshot. I've subsequently removed the
constraint and mergefilter and reinitialized the snapshot, but am still
receiving the same error. It just makes no sense at this point that it picks
up five records at a time and then deletes tblLoan records on both the
subscriber and publisher. There is absolutely no foreign key issue in this
scenario. The TapeData recs/keys are identical at both the publisher and
subscriber, and the tblLoan table is set to allow matching TapeData recs to
be added on a 1:1 basis on both the publisher and the subscriber.
I'm beyond frustrated with this...
Jeff
"Hilary Cotter" wrote:
> What does conflict viewer tell you? I suspect the answer is there. It is
> also possible that your filter condition is incorrect. Make sure if you are
> using the hostname parameter you over ride it with -HostName or you use a
> pull subscription.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> 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
>
> "J Jones" <JJones@.discussions.microsoft.com> wrote in message
> news:B5FCD2B4-D15D-4CDD-94EC-B3BF7C9014DE@.microsoft.com...
>
>
|||Also... if I insert the remaining TapeData records into tblLoan on the
SubscriberDB vs. the PublisherDB, the merge agent still only merges 5 recs
from the SubscriberDB.tblLoan back to PublisherDB.tblLoan.
I found a CHECK constraint on the PublisherDB.TapeData table:
([LoanID] > 38548 and [LoanID] < 42000)
The TapeData.LoanIDs on the publisher are within this range.
The CHECK constraint on the SubscriberDB.TapeData table is:
([LoanID] > 51000 and [LoanID] < 54000)
The TapeData records that are on the Subscriber do not fall within the
constraint, but they made it over ok from the publisher.
But, regardless of the CHECK constraint on the TapeData table, the records
that are getting deleted are in the tblLoan table - not the TapeData. Again,
makes no sense to me. I tried altering the CHECK constraint on the
subscriber to make sure that the TapeData records were in range, but still
experienced the same conflict and subsequent deletion of both publisher and
subscriber tblLoan records.
"Hilary Cotter" wrote:
> What does conflict viewer tell you? I suspect the answer is there. It is
> also possible that your filter condition is incorrect. Make sure if you are
> using the hostname parameter you over ride it with -HostName or you use a
> pull subscription.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> 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
>
> "J Jones" <JJones@.discussions.microsoft.com> wrote in message
> news:B5FCD2B4-D15D-4CDD-94EC-B3BF7C9014DE@.microsoft.com...
>
>
I recently discovered a 1:many table (tblLoanException) that did not have a
foreign_key constraint created on it. So, I took the following actions:
1. I scripted out the creation of the foreign key constraint on
tblLoanException
2. I ran the script on my publisher to create the fk constraint - success
3. I ran the script on my subscriber via sp_addscriptexec - success
4. I used sp_addmergefilter to create the appropriate merge filter on the
table and it's foreignkey-reference table (tblLoan)
5. I reinitialized the snapshot (reluctantly, but I had to because of the
merge filter add) - success
6. I re-ran the merge agent, which reinitialized my subscriber data set -
success
-- THIS IS WHERE THINGS WENT INTO THE DEPTHS OF HELL
7. The merge agent initialized the subscriber, but then on the next merge
agent run, deleted all but 5 records from the tblLoan table.
8. I ran an "INSERT INTO" the tblLoan table on the publisher to get
backed-up data reinserted
9. Re-ran the merge agent
10. Same thing - merge agent deleted all but 5 additional tblLoan records,
so now I show 10 tblLoan records.
11. Repeats of steps 8 and 9 continue to yield the same results - 5 add'l
tblLoan records get added, but the rest are deleted.
What in the hell have I done here? At this point, I am unable to fix this
problem and have absolutely no idea why 5 records at a time remain in the
tblLoan table, but the rest continue to be deleted. I have several other
1:many filters on the tblLoan table and have had no issues to date. This is
very troubling, to say the least. Any help that can be offered here will be
extremely appreciated. Thanks!
Jeff Jones
Atlanta, GA
What does conflict viewer tell you? I suspect the answer is there. It is
also possible that your filter condition is incorrect. Make sure if you are
using the hostname parameter you over ride it with -HostName or you use a
pull subscription.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"J Jones" <JJones@.discussions.microsoft.com> wrote in message
news:B5FCD2B4-D15D-4CDD-94EC-B3BF7C9014DE@.microsoft.com...
>I had merge replication fully implemented and working beautifully.
>However,
> I recently discovered a 1:many table (tblLoanException) that did not have
> a
> foreign_key constraint created on it. So, I took the following actions:
> 1. I scripted out the creation of the foreign key constraint on
> tblLoanException
> 2. I ran the script on my publisher to create the fk constraint - success
> 3. I ran the script on my subscriber via sp_addscriptexec - success
> 4. I used sp_addmergefilter to create the appropriate merge filter on the
> table and it's foreignkey-reference table (tblLoan)
> 5. I reinitialized the snapshot (reluctantly, but I had to because of the
> merge filter add) - success
> 6. I re-ran the merge agent, which reinitialized my subscriber data set -
> success
> -- THIS IS WHERE THINGS WENT INTO THE DEPTHS OF HELL
> 7. The merge agent initialized the subscriber, but then on the next merge
> agent run, deleted all but 5 records from the tblLoan table.
> 8. I ran an "INSERT INTO" the tblLoan table on the publisher to get
> backed-up data reinserted
> 9. Re-ran the merge agent
> 10. Same thing - merge agent deleted all but 5 additional tblLoan
> records,
> so now I show 10 tblLoan records.
> 11. Repeats of steps 8 and 9 continue to yield the same results - 5 add'l
> tblLoan records get added, but the rest are deleted.
> What in the hell have I done here? At this point, I am unable to fix this
> problem and have absolutely no idea why 5 records at a time remain in the
> tblLoan table, but the rest continue to be deleted. I have several other
> 1:many filters on the tblLoan table and have had no issues to date. This
> is
> very troubling, to say the least. Any help that can be offered here will
> be
> extremely appreciated. Thanks!
> Jeff Jones
> Atlanta, GA
|||I receive the following message in the tblLoan conflict table:
The row was inserted at '[PublisherDB.tblLoan]' but could not be inserted at
'[SuscriberDB.tblLoan]'. INSERT statement conflicted with COLUMN FOREIGN KEY
constraint 'FK_Loan_TapeData'. The conflict occurred in database 'ResiDD',
table 'TapeData', column 'LoanID'.
This error makes absolutely no sense to me, as the records that get inserted
into tblLoan have a 1:1 relationship w/TapeData, where the TapeData.LoanID
column is an identity field, but the tblLoan.LoanID is a non-identity column.
The tblLoan records that exist on the publisher do in fact match 1:1 to
what's in the TapeData table on both the publisher and subscriber.
Once the above error occurs, the records get deleted from the
publisher.tblLoan. If I re-insert the remaining TapeData recs into tblLoan,
the merge agent will then insert 5 and only 5 tblLoan records at the
subscriber, and will delete the rest.
The merge agent worked flawlessly until I added the constraint and
mergefilter and re-initialized the snapshot. I've subsequently removed the
constraint and mergefilter and reinitialized the snapshot, but am still
receiving the same error. It just makes no sense at this point that it picks
up five records at a time and then deletes tblLoan records on both the
subscriber and publisher. There is absolutely no foreign key issue in this
scenario. The TapeData recs/keys are identical at both the publisher and
subscriber, and the tblLoan table is set to allow matching TapeData recs to
be added on a 1:1 basis on both the publisher and the subscriber.
I'm beyond frustrated with this...
Jeff
"Hilary Cotter" wrote:
> What does conflict viewer tell you? I suspect the answer is there. It is
> also possible that your filter condition is incorrect. Make sure if you are
> using the hostname parameter you over ride it with -HostName or you use a
> pull subscription.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> 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
>
> "J Jones" <JJones@.discussions.microsoft.com> wrote in message
> news:B5FCD2B4-D15D-4CDD-94EC-B3BF7C9014DE@.microsoft.com...
>
>
|||Also... if I insert the remaining TapeData records into tblLoan on the
SubscriberDB vs. the PublisherDB, the merge agent still only merges 5 recs
from the SubscriberDB.tblLoan back to PublisherDB.tblLoan.
I found a CHECK constraint on the PublisherDB.TapeData table:
([LoanID] > 38548 and [LoanID] < 42000)
The TapeData.LoanIDs on the publisher are within this range.
The CHECK constraint on the SubscriberDB.TapeData table is:
([LoanID] > 51000 and [LoanID] < 54000)
The TapeData records that are on the Subscriber do not fall within the
constraint, but they made it over ok from the publisher.
But, regardless of the CHECK constraint on the TapeData table, the records
that are getting deleted are in the tblLoan table - not the TapeData. Again,
makes no sense to me. I tried altering the CHECK constraint on the
subscriber to make sure that the TapeData records were in range, but still
experienced the same conflict and subsequent deletion of both publisher and
subscriber tblLoan records.
"Hilary Cotter" wrote:
> What does conflict viewer tell you? I suspect the answer is there. It is
> also possible that your filter condition is incorrect. Make sure if you are
> using the hostname parameter you over ride it with -HostName or you use a
> pull subscription.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> 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
>
> "J Jones" <JJones@.discussions.microsoft.com> wrote in message
> news:B5FCD2B4-D15D-4CDD-94EC-B3BF7C9014DE@.microsoft.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
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 when use BEGIN TRANSACTION COMMIT TRANSACTION
Ok let me explain my problem
I update table abc to SERVER A
SERVER A push a replication to SERVER B
and SERVER B push to SERVER A and SERVER C
SERVER A and C are configure to replicate to SERVER B and
SERVER B is configure to replicate to SERVER A and SERVER C
(same tables are replicate)
I have no problem while I not use BEGIN TRANSACTION UPDATE... COMMIT
TRANSACTION
When I use BEGIN TRANSACTION UPDATE... COMMIT TRANSACTION I got a conflict
error
supp info:
2 Queued update conflict. Subscriber wins. Updating Publisher ...
"rl" <rlavoie@.lgs.com> wrote in message
news:%23jnEqEGjFHA.1232@.TK2MSFTNGP15.phx.gbl...
> Ok let me explain my problem
> I update table abc to SERVER A
> SERVER A push a replication to SERVER B
> and SERVER B push to SERVER A and SERVER C
> SERVER A and C are configure to replicate to SERVER B and
> SERVER B is configure to replicate to SERVER A and SERVER C
> (same tables are replicate)
> I have no problem while I not use BEGIN TRANSACTION UPDATE... COMMIT
> TRANSACTION
> When I use BEGIN TRANSACTION UPDATE... COMMIT TRANSACTION I got a conflict
> error
>
I update table abc to SERVER A
SERVER A push a replication to SERVER B
and SERVER B push to SERVER A and SERVER C
SERVER A and C are configure to replicate to SERVER B and
SERVER B is configure to replicate to SERVER A and SERVER C
(same tables are replicate)
I have no problem while I not use BEGIN TRANSACTION UPDATE... COMMIT
TRANSACTION
When I use BEGIN TRANSACTION UPDATE... COMMIT TRANSACTION I got a conflict
error
supp info:
2 Queued update conflict. Subscriber wins. Updating Publisher ...
"rl" <rlavoie@.lgs.com> wrote in message
news:%23jnEqEGjFHA.1232@.TK2MSFTNGP15.phx.gbl...
> Ok let me explain my problem
> I update table abc to SERVER A
> SERVER A push a replication to SERVER B
> and SERVER B push to SERVER A and SERVER C
> SERVER A and C are configure to replicate to SERVER B and
> SERVER B is configure to replicate to SERVER A and SERVER C
> (same tables are replicate)
> I have no problem while I not use BEGIN TRANSACTION UPDATE... COMMIT
> TRANSACTION
> When I use BEGIN TRANSACTION UPDATE... COMMIT TRANSACTION I got a conflict
> error
>
Friday, March 23, 2012
Replication can solve may problem ?
Hi,
In my db I have a big table with at least 40 millions rows.
In fact my appz soffer about performance and I had to put
offline the oldest data.
I keep online only the last 6 monthsm and I copy the oldest in
some tables with data costraints...
I mean I have a table called "TbPrices" and then I have
TbPrices_2003_12,
TbPrices_2003_11
and so on.
I think to use a replication db to have the entire archive with a clustered
view and
the OLTP with the last 10 days online.
But I don't know how replication works because I don't want to delete the
record in
the destination.
Thanks
You can use replication for this but you have to be careful.
I am confused by what you mean by a clustered view? Do you mean an indexed
view, or perhaps a distributed partitioned view?
Replication can be used to replicate a subset of data to an archive table,
ie only replicate data older than 6 months perhaps.
You should use a date filter to only replicate data which is less than 6
months old. Then when you are creating your publication ensure that you use
the 'delete all data which matches the row filter clause' you can find in
the specify articles dialog of the create publication wizard. Click the
three ellipses to the right of your article name, and select the snapshot
tab. In the name conflicts section select this option.
This will ensure when you reinitialize the archived data on your subscriber
is not wiped out.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"opianeta" <opianetaNONMISPAMMAREPLEASE@.yahoo.it> wrote in message
news:7R0Xc.458$v24.9883@.news.it.colt.net...
> Hi,
> In my db I have a big table with at least 40 millions rows.
> In fact my appz soffer about performance and I had to put
> offline the oldest data.
> I keep online only the last 6 monthsm and I copy the oldest in
> some tables with data costraints...
> I mean I have a table called "TbPrices" and then I have
> TbPrices_2003_12,
> TbPrices_2003_11
> and so on.
> I think to use a replication db to have the entire archive with a
clustered
> view and
> the OLTP with the last 10 days online.
> But I don't know how replication works because I don't want to delete the
> record in
> the destination.
> Thanks
>
In my db I have a big table with at least 40 millions rows.
In fact my appz soffer about performance and I had to put
offline the oldest data.
I keep online only the last 6 monthsm and I copy the oldest in
some tables with data costraints...
I mean I have a table called "TbPrices" and then I have
TbPrices_2003_12,
TbPrices_2003_11
and so on.
I think to use a replication db to have the entire archive with a clustered
view and
the OLTP with the last 10 days online.
But I don't know how replication works because I don't want to delete the
record in
the destination.
Thanks
You can use replication for this but you have to be careful.
I am confused by what you mean by a clustered view? Do you mean an indexed
view, or perhaps a distributed partitioned view?
Replication can be used to replicate a subset of data to an archive table,
ie only replicate data older than 6 months perhaps.
You should use a date filter to only replicate data which is less than 6
months old. Then when you are creating your publication ensure that you use
the 'delete all data which matches the row filter clause' you can find in
the specify articles dialog of the create publication wizard. Click the
three ellipses to the right of your article name, and select the snapshot
tab. In the name conflicts section select this option.
This will ensure when you reinitialize the archived data on your subscriber
is not wiped out.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"opianeta" <opianetaNONMISPAMMAREPLEASE@.yahoo.it> wrote in message
news:7R0Xc.458$v24.9883@.news.it.colt.net...
> Hi,
> In my db I have a big table with at least 40 millions rows.
> In fact my appz soffer about performance and I had to put
> offline the oldest data.
> I keep online only the last 6 monthsm and I copy the oldest in
> some tables with data costraints...
> I mean I have a table called "TbPrices" and then I have
> TbPrices_2003_12,
> TbPrices_2003_11
> and so on.
> I think to use a replication db to have the entire archive with a
clustered
> view and
> the OLTP with the last 10 days online.
> But I don't know how replication works because I don't want to delete the
> record in
> the destination.
> Thanks
>
Wednesday, March 21, 2012
Replication and User Defined Trigger
Hi, all:
We have 5 databases which contains invoice and invoiceHistory tables.
We have an update and insert trigger on invoice table to record every
modification into InvoiceHistory table. The invoiceHistory table is not
read only, it has some fields that the end user can edit (comments,
date ...etc). At the same time, we using scheduled snapshots every 5
minutes to 'refresh' the table at the suscribers. The problem is when
the replicate server replicate table invoice to a subscribe, the
trigger on invoice table will be triggered and update/insert
InvoiceHistory table in the subscribe. Then when the replicate server
replicate table InvoiceHistory, we will get duplicate record in
InvoiceTable, one is from Invoice table trigger and another is from
InvoiceHistory replicate.
If we do not replicate InvoiceHistory table, then those fields that end
user updated can not be sync to subscribes.
Can I disable the trigger on invoice table when replication?
Does anybody there have a better idea how to do something like this?
ThanksCheck for the NOT FOR REPLICATION option. If you put it, trigger will not be
fired for replication.
MC
<rockdale.green@.gmail.com> wrote in message
news:1143418413.367012.182670@.v46g2000cwv.googlegroups.com...
> Hi, all:
> We have 5 databases which contains invoice and invoiceHistory tables.
> We have an update and insert trigger on invoice table to record every
> modification into InvoiceHistory table. The invoiceHistory table is not
> read only, it has some fields that the end user can edit (comments,
> date ...etc). At the same time, we using scheduled snapshots every 5
> minutes to 'refresh' the table at the suscribers. The problem is when
> the replicate server replicate table invoice to a subscribe, the
> trigger on invoice table will be triggered and update/insert
> InvoiceHistory table in the subscribe. Then when the replicate server
> replicate table InvoiceHistory, we will get duplicate record in
> InvoiceTable, one is from Invoice table trigger and another is from
> InvoiceHistory replicate.
>
> If we do not replicate InvoiceHistory table, then those fields that end
> user updated can not be sync to subscribes.
> Can I disable the trigger on invoice table when replication?
> Does anybody there have a better idea how to do something like this?
>
> Thanks
>
We have 5 databases which contains invoice and invoiceHistory tables.
We have an update and insert trigger on invoice table to record every
modification into InvoiceHistory table. The invoiceHistory table is not
read only, it has some fields that the end user can edit (comments,
date ...etc). At the same time, we using scheduled snapshots every 5
minutes to 'refresh' the table at the suscribers. The problem is when
the replicate server replicate table invoice to a subscribe, the
trigger on invoice table will be triggered and update/insert
InvoiceHistory table in the subscribe. Then when the replicate server
replicate table InvoiceHistory, we will get duplicate record in
InvoiceTable, one is from Invoice table trigger and another is from
InvoiceHistory replicate.
If we do not replicate InvoiceHistory table, then those fields that end
user updated can not be sync to subscribes.
Can I disable the trigger on invoice table when replication?
Does anybody there have a better idea how to do something like this?
ThanksCheck for the NOT FOR REPLICATION option. If you put it, trigger will not be
fired for replication.
MC
<rockdale.green@.gmail.com> wrote in message
news:1143418413.367012.182670@.v46g2000cwv.googlegroups.com...
> Hi, all:
> We have 5 databases which contains invoice and invoiceHistory tables.
> We have an update and insert trigger on invoice table to record every
> modification into InvoiceHistory table. The invoiceHistory table is not
> read only, it has some fields that the end user can edit (comments,
> date ...etc). At the same time, we using scheduled snapshots every 5
> minutes to 'refresh' the table at the suscribers. The problem is when
> the replicate server replicate table invoice to a subscribe, the
> trigger on invoice table will be triggered and update/insert
> InvoiceHistory table in the subscribe. Then when the replicate server
> replicate table InvoiceHistory, we will get duplicate record in
> InvoiceTable, one is from Invoice table trigger and another is from
> InvoiceHistory replicate.
>
> If we do not replicate InvoiceHistory table, then those fields that end
> user updated can not be sync to subscribes.
> Can I disable the trigger on invoice table when replication?
> Does anybody there have a better idea how to do something like this?
>
> Thanks
>
Replication and User Defined Trigger
Hi, all:
We have 5 databases which contains invoice and invoiceHistory tables.
We have an update and insert trigger on invoice table to record every
modification into InvoiceHistory table. The invoiceHistory table is not
read only, it has some fields that the end user can edit (comments,
date ...etc). At the same time, we using scheduled snapshots every 5
minutes to 'refresh' the table at the suscribers. The problem is when
the replicate server replicate table invoice to a subscribe, the
trigger on invoice table will be triggered and update/insert
InvoiceHistory table in the subscribe. Then when the replicate server
replicate table InvoiceHistory, we will get duplicate record in
InvoiceTable, one is from Invoice table trigger and another is from
InvoiceHistory replicate.
If we do not replicate InvoiceHistory table, then those fields that end
user updated can not be sync to subscribes.
Can I disable the trigger on invoice table when replication?
Does anybody there have a better idea how to do something like this?
Thanks
Check for the NOT FOR REPLICATION option. If you put it, trigger will not be
fired for replication.
MC
<rockdale.green@.gmail.com> wrote in message
news:1143418413.367012.182670@.v46g2000cwv.googlegr oups.com...
> Hi, all:
> We have 5 databases which contains invoice and invoiceHistory tables.
> We have an update and insert trigger on invoice table to record every
> modification into InvoiceHistory table. The invoiceHistory table is not
> read only, it has some fields that the end user can edit (comments,
> date ...etc). At the same time, we using scheduled snapshots every 5
> minutes to 'refresh' the table at the suscribers. The problem is when
> the replicate server replicate table invoice to a subscribe, the
> trigger on invoice table will be triggered and update/insert
> InvoiceHistory table in the subscribe. Then when the replicate server
> replicate table InvoiceHistory, we will get duplicate record in
> InvoiceTable, one is from Invoice table trigger and another is from
> InvoiceHistory replicate.
>
> If we do not replicate InvoiceHistory table, then those fields that end
> user updated can not be sync to subscribes.
> Can I disable the trigger on invoice table when replication?
> Does anybody there have a better idea how to do something like this?
>
> Thanks
>
We have 5 databases which contains invoice and invoiceHistory tables.
We have an update and insert trigger on invoice table to record every
modification into InvoiceHistory table. The invoiceHistory table is not
read only, it has some fields that the end user can edit (comments,
date ...etc). At the same time, we using scheduled snapshots every 5
minutes to 'refresh' the table at the suscribers. The problem is when
the replicate server replicate table invoice to a subscribe, the
trigger on invoice table will be triggered and update/insert
InvoiceHistory table in the subscribe. Then when the replicate server
replicate table InvoiceHistory, we will get duplicate record in
InvoiceTable, one is from Invoice table trigger and another is from
InvoiceHistory replicate.
If we do not replicate InvoiceHistory table, then those fields that end
user updated can not be sync to subscribes.
Can I disable the trigger on invoice table when replication?
Does anybody there have a better idea how to do something like this?
Thanks
Check for the NOT FOR REPLICATION option. If you put it, trigger will not be
fired for replication.
MC
<rockdale.green@.gmail.com> wrote in message
news:1143418413.367012.182670@.v46g2000cwv.googlegr oups.com...
> Hi, all:
> We have 5 databases which contains invoice and invoiceHistory tables.
> We have an update and insert trigger on invoice table to record every
> modification into InvoiceHistory table. The invoiceHistory table is not
> read only, it has some fields that the end user can edit (comments,
> date ...etc). At the same time, we using scheduled snapshots every 5
> minutes to 'refresh' the table at the suscribers. The problem is when
> the replicate server replicate table invoice to a subscribe, the
> trigger on invoice table will be triggered and update/insert
> InvoiceHistory table in the subscribe. Then when the replicate server
> replicate table InvoiceHistory, we will get duplicate record in
> InvoiceTable, one is from Invoice table trigger and another is from
> InvoiceHistory replicate.
>
> If we do not replicate InvoiceHistory table, then those fields that end
> user updated can not be sync to subscribes.
> Can I disable the trigger on invoice table when replication?
> Does anybody there have a better idea how to do something like this?
>
> Thanks
>
Tuesday, March 20, 2012
Replication and User Defined Trigger
Hi, all:
We have 5 databases which contains invoice and invoiceHistory tables.
We have an update and insert trigger on invoice table to record every
modification into InvoiceHistory table. The invoiceHistory table is not
read only, it has some fields that the end user can edit (comments,
date ...etc). At the same time, we using scheduled snapshots every 5
minutes to 'refresh' the table at the suscribers. The problem is when
the replicate server replicate table invoice to a subscribe, the
trigger on invoice table will be triggered and update/insert
InvoiceHistory table in the subscribe. Then when the replicate server
replicate table InvoiceHistory, we will get duplicate record in
InvoiceTable, one is from Invoice table trigger and another is from
InvoiceHistory replicate.
If we do not replicate InvoiceHistory table, then those fields that end
user updated can not be sync to subscribes.
Can I disable the trigger on invoice table when replication?
Does anybody there have a better idea how to do something like this?
ThanksCheck for the NOT FOR REPLICATION option. If you put it, trigger will not be
fired for replication.
MC
<rockdale.green@.gmail.com> wrote in message
news:1143418413.367012.182670@.v46g2000cwv.googlegroups.com...
> Hi, all:
> We have 5 databases which contains invoice and invoiceHistory tables.
> We have an update and insert trigger on invoice table to record every
> modification into InvoiceHistory table. The invoiceHistory table is not
> read only, it has some fields that the end user can edit (comments,
> date ...etc). At the same time, we using scheduled snapshots every 5
> minutes to 'refresh' the table at the suscribers. The problem is when
> the replicate server replicate table invoice to a subscribe, the
> trigger on invoice table will be triggered and update/insert
> InvoiceHistory table in the subscribe. Then when the replicate server
> replicate table InvoiceHistory, we will get duplicate record in
> InvoiceTable, one is from Invoice table trigger and another is from
> InvoiceHistory replicate.
>
> If we do not replicate InvoiceHistory table, then those fields that end
> user updated can not be sync to subscribes.
> Can I disable the trigger on invoice table when replication?
> Does anybody there have a better idea how to do something like this?
>
> Thanks
>
We have 5 databases which contains invoice and invoiceHistory tables.
We have an update and insert trigger on invoice table to record every
modification into InvoiceHistory table. The invoiceHistory table is not
read only, it has some fields that the end user can edit (comments,
date ...etc). At the same time, we using scheduled snapshots every 5
minutes to 'refresh' the table at the suscribers. The problem is when
the replicate server replicate table invoice to a subscribe, the
trigger on invoice table will be triggered and update/insert
InvoiceHistory table in the subscribe. Then when the replicate server
replicate table InvoiceHistory, we will get duplicate record in
InvoiceTable, one is from Invoice table trigger and another is from
InvoiceHistory replicate.
If we do not replicate InvoiceHistory table, then those fields that end
user updated can not be sync to subscribes.
Can I disable the trigger on invoice table when replication?
Does anybody there have a better idea how to do something like this?
ThanksCheck for the NOT FOR REPLICATION option. If you put it, trigger will not be
fired for replication.
MC
<rockdale.green@.gmail.com> wrote in message
news:1143418413.367012.182670@.v46g2000cwv.googlegroups.com...
> Hi, all:
> We have 5 databases which contains invoice and invoiceHistory tables.
> We have an update and insert trigger on invoice table to record every
> modification into InvoiceHistory table. The invoiceHistory table is not
> read only, it has some fields that the end user can edit (comments,
> date ...etc). At the same time, we using scheduled snapshots every 5
> minutes to 'refresh' the table at the suscribers. The problem is when
> the replicate server replicate table invoice to a subscribe, the
> trigger on invoice table will be triggered and update/insert
> InvoiceHistory table in the subscribe. Then when the replicate server
> replicate table InvoiceHistory, we will get duplicate record in
> InvoiceTable, one is from Invoice table trigger and another is from
> InvoiceHistory replicate.
>
> If we do not replicate InvoiceHistory table, then those fields that end
> user updated can not be sync to subscribes.
> Can I disable the trigger on invoice table when replication?
> Does anybody there have a better idea how to do something like this?
>
> Thanks
>
Replication and table/SP change?
I build a table and stored procedure replication between 2 database.
seems when I want to change the stored procedure / or Table, the replication still hold the objects and won't let me to do.
is that the only way drop the replication( publisher/disbtribution/subscrib)
make the table / store procedure change, than rebuild replication again?? any other short cut??
GUYANGHi
SQL2K has a lot of improvements when it comes to changing the schema of replicated tables.
What Ver SQLServer R U Using & what types of replication merge/snapshot etc
Changes to tables must be done through the publication properties in 2K Enterprise Manager or using the system Stored Procedures provided for the purpose.
Stored Procedures can only be edited at the publisher U Can't merge changes to them - Urghhh What a thought. - U can Snapshot these out to subscribers.
If U R Using SQL7 you are out of Luck
GW|||Valid references by GW, for any schema changes on the source table, its advised to redefine the replication settings once its finished.
seems when I want to change the stored procedure / or Table, the replication still hold the objects and won't let me to do.
is that the only way drop the replication( publisher/disbtribution/subscrib)
make the table / store procedure change, than rebuild replication again?? any other short cut??
GUYANGHi
SQL2K has a lot of improvements when it comes to changing the schema of replicated tables.
What Ver SQLServer R U Using & what types of replication merge/snapshot etc
Changes to tables must be done through the publication properties in 2K Enterprise Manager or using the system Stored Procedures provided for the purpose.
Stored Procedures can only be edited at the publisher U Can't merge changes to them - Urghhh What a thought. - U can Snapshot these out to subscribers.
If U R Using SQL7 you are out of Luck
GW|||Valid references by GW, for any schema changes on the source table, its advised to redefine the replication settings once its finished.
replication and identity problem
Every time I start replication i get following error:
Cannot insert explicit value for identity column in table 'xxxx' when
IDENTITY_INSERT is seto to OFF
the publisher side:
xxxx table has identity set on one column
the subscriber side
xxxx table has identity set on one column.
How can I change or how can I set IDENTITY_INSERT to ON during replication
process?
regards,
DaliborNot sure if this is the right way but this is what I did:
Go to the insert stored proc for replication on the
Subscriber DB.(sp_msins_TableName)
Comment out thie insert line for that column.
>--Original Message--
>Every time I start replication i get following error:
>Cannot insert explicit value for identity column in
table 'xxxx' when
>IDENTITY_INSERT is seto to OFF
>the publisher side:
>xxxx table has identity set on one column
>the subscriber side
>xxxx table has identity set on one column.
>How can I change or how can I set IDENTITY_INSERT to ON
during replication
>process?
>regards,
>Dalibor
>
>.
>|||Use NOT FOR REPLICAITON on the identity.
e.g.
CREATE TABLE dbo.test (
id int IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
office_location varchar (10) NOT NULL ,
...
)
Linchi
>--Original Message--
>Every time I start replication i get following error:
>Cannot insert explicit value for identity column in
table 'xxxx' when
>IDENTITY_INSERT is seto to OFF
>the publisher side:
>xxxx table has identity set on one column
>the subscriber side
>xxxx table has identity set on one column.
>How can I change or how can I set IDENTITY_INSERT to ON
during replication
>process?
>regards,
>Dalibor
>
>.
>
Cannot insert explicit value for identity column in table 'xxxx' when
IDENTITY_INSERT is seto to OFF
the publisher side:
xxxx table has identity set on one column
the subscriber side
xxxx table has identity set on one column.
How can I change or how can I set IDENTITY_INSERT to ON during replication
process?
regards,
DaliborNot sure if this is the right way but this is what I did:
Go to the insert stored proc for replication on the
Subscriber DB.(sp_msins_TableName)
Comment out thie insert line for that column.
>--Original Message--
>Every time I start replication i get following error:
>Cannot insert explicit value for identity column in
table 'xxxx' when
>IDENTITY_INSERT is seto to OFF
>the publisher side:
>xxxx table has identity set on one column
>the subscriber side
>xxxx table has identity set on one column.
>How can I change or how can I set IDENTITY_INSERT to ON
during replication
>process?
>regards,
>Dalibor
>
>.
>|||Use NOT FOR REPLICAITON on the identity.
e.g.
CREATE TABLE dbo.test (
id int IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
office_location varchar (10) NOT NULL ,
...
)
Linchi
>--Original Message--
>Every time I start replication i get following error:
>Cannot insert explicit value for identity column in
table 'xxxx' when
>IDENTITY_INSERT is seto to OFF
>the publisher side:
>xxxx table has identity set on one column
>the subscriber side
>xxxx table has identity set on one column.
>How can I change or how can I set IDENTITY_INSERT to ON
during replication
>process?
>regards,
>Dalibor
>
>.
>
Monday, March 12, 2012
Replication and FK Constraint
Let me preface this w/ the fact that I may be stupid...
In trying to test replication in SQL 7 I created a staging database to hold one table (tblStaff) that I would try to replicate into the "live" version of tblStaff in another DB (on the same server).
The "live" version of tblStaff obviously (perhaps) has a number of dependent tables (and stored procedures - which are really only select queries) that rely on the StaffID key. I have turned off the "Enable key for replication" in all the dependent tables and, for good measure, in tblStaff. I then created a publication of the stagingDB tblStaff and a pull subscription on the live DB. But...(snapshot) replication *still* fails w/ an error that dropping the live tblStaff would violate foreign key constraints.
Thanks in advance.What was the error?|||"Could not drop/truncate (depending on the option chosen earlier in the process - tried it both ways) table due to foreign key constraint."
As an (inelegant) workaround I wrote two scripts to drop and add the constraints and then synch. in the middle. It works and the table only has to be replicated every now and again but... I would still like to know the "real" way to do it.|||Use the NOT FOR REPLICATION Option and refer to BOL for more information.|||I already did that.|||May check this Article (http://www.windowsitlibrary.com/Content/77/11/1.html) about do's and dont's.
In trying to test replication in SQL 7 I created a staging database to hold one table (tblStaff) that I would try to replicate into the "live" version of tblStaff in another DB (on the same server).
The "live" version of tblStaff obviously (perhaps) has a number of dependent tables (and stored procedures - which are really only select queries) that rely on the StaffID key. I have turned off the "Enable key for replication" in all the dependent tables and, for good measure, in tblStaff. I then created a publication of the stagingDB tblStaff and a pull subscription on the live DB. But...(snapshot) replication *still* fails w/ an error that dropping the live tblStaff would violate foreign key constraints.
Thanks in advance.What was the error?|||"Could not drop/truncate (depending on the option chosen earlier in the process - tried it both ways) table due to foreign key constraint."
As an (inelegant) workaround I wrote two scripts to drop and add the constraints and then synch. in the middle. It works and the table only has to be replicated every now and again but... I would still like to know the "real" way to do it.|||Use the NOT FOR REPLICATION Option and refer to BOL for more information.|||I already did that.|||May check this Article (http://www.windowsitlibrary.com/Content/77/11/1.html) about do's and dont's.
Replication and Deleting Rows
Hello,
I have a large table with over 20 million rows.
It is replicated accross a 4 subscribers using Pull subscription. One of the
subscribers is also a Distributor.
We are running some processed to "clean up" the data in this table by
issuing a lot of DELETE commands (using Primary Key as the where clause)
against the large table. The DELETE commands are enclosed in BEGIN TRAN -
COMIT TRAN batches of 20 each.
The deletion works extremely fast on the Publisher.
However, it takes very very long time to apply the very same commands on all
of the subscribers. The subscribers also have the PRIMARY KEY index that is
on the Publisher.
We need to delete a few million rows from the large table using this
technique. However, it makes the Subscribers very slow and unresponsive.
Any suggestions?
Thanks,
ArsenConsider doing this in even smaller batches. If that is not possible, and
transactional consistency is not really of importance, then you could use a
parameter called MaxCmdsInTran for logreader (introduced in SQL Server 2000
SP1 I believe). This breaks huge transactions into smaller chunks of
specified size, while storing the commands in distribution database.
There is another approach, that I would recommend. Consider using 'stored
procedure execution' replication. With this method, you keep a copy of the
archiving stored procedure on all subscribers. When you run this procedure
on the publisher, the distribution agent will call this sp on all
subscribers, instead of sending millions of DELETEs over the network. See
SQL Server Books Online for more information on this.
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Arsen V." <arsen@.community.nospam> wrote in message
news:%23hpi4tKoEHA.2612@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I have a large table with over 20 million rows.
> It is replicated accross a 4 subscribers using Pull subscription. One of
the
> subscribers is also a Distributor.
> We are running some processed to "clean up" the data in this table by
> issuing a lot of DELETE commands (using Primary Key as the where clause)
> against the large table. The DELETE commands are enclosed in BEGIN TRAN -
> COMIT TRAN batches of 20 each.
> The deletion works extremely fast on the Publisher.
> However, it takes very very long time to apply the very same commands on
all
> of the subscribers. The subscribers also have the PRIMARY KEY index that
is
> on the Publisher.
> We need to delete a few million rows from the large table using this
> technique. However, it makes the Subscribers very slow and unresponsive.
> Any suggestions?
> Thanks,
> Arsen
>
I have a large table with over 20 million rows.
It is replicated accross a 4 subscribers using Pull subscription. One of the
subscribers is also a Distributor.
We are running some processed to "clean up" the data in this table by
issuing a lot of DELETE commands (using Primary Key as the where clause)
against the large table. The DELETE commands are enclosed in BEGIN TRAN -
COMIT TRAN batches of 20 each.
The deletion works extremely fast on the Publisher.
However, it takes very very long time to apply the very same commands on all
of the subscribers. The subscribers also have the PRIMARY KEY index that is
on the Publisher.
We need to delete a few million rows from the large table using this
technique. However, it makes the Subscribers very slow and unresponsive.
Any suggestions?
Thanks,
ArsenConsider doing this in even smaller batches. If that is not possible, and
transactional consistency is not really of importance, then you could use a
parameter called MaxCmdsInTran for logreader (introduced in SQL Server 2000
SP1 I believe). This breaks huge transactions into smaller chunks of
specified size, while storing the commands in distribution database.
There is another approach, that I would recommend. Consider using 'stored
procedure execution' replication. With this method, you keep a copy of the
archiving stored procedure on all subscribers. When you run this procedure
on the publisher, the distribution agent will call this sp on all
subscribers, instead of sending millions of DELETEs over the network. See
SQL Server Books Online for more information on this.
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Arsen V." <arsen@.community.nospam> wrote in message
news:%23hpi4tKoEHA.2612@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I have a large table with over 20 million rows.
> It is replicated accross a 4 subscribers using Pull subscription. One of
the
> subscribers is also a Distributor.
> We are running some processed to "clean up" the data in this table by
> issuing a lot of DELETE commands (using Primary Key as the where clause)
> against the large table. The DELETE commands are enclosed in BEGIN TRAN -
> COMIT TRAN batches of 20 each.
> The deletion works extremely fast on the Publisher.
> However, it takes very very long time to apply the very same commands on
all
> of the subscribers. The subscribers also have the PRIMARY KEY index that
is
> on the Publisher.
> We need to delete a few million rows from the large table using this
> technique. However, it makes the Subscribers very slow and unresponsive.
> Any suggestions?
> Thanks,
> Arsen
>
Replication and Deleting Rows
Hello,
I have a large table with over 20 million rows.
It is replicated accross a 4 subscribers using Pull subscription. One of the
subscribers is also a Distributor.
We are running some processed to "clean up" the data in this table by
issuing a lot of DELETE commands (using Primary Key as the where clause)
against the large table. The DELETE commands are enclosed in BEGIN TRAN -
COMIT TRAN batches of 20 each.
The deletion works extremely fast on the Publisher.
However, it takes very very long time to apply the very same commands on all
of the subscribers. The subscribers also have the PRIMARY KEY index that is
on the Publisher.
We need to delete a few million rows from the large table using this
technique. However, it makes the Subscribers very slow and unresponsive.
Any suggestions?
Thanks,
Arsen
Consider doing this in even smaller batches. If that is not possible, and
transactional consistency is not really of importance, then you could use a
parameter called MaxCmdsInTran for logreader (introduced in SQL Server 2000
SP1 I believe). This breaks huge transactions into smaller chunks of
specified size, while storing the commands in distribution database.
There is another approach, that I would recommend. Consider using 'stored
procedure execution' replication. With this method, you keep a copy of the
archiving stored procedure on all subscribers. When you run this procedure
on the publisher, the distribution agent will call this sp on all
subscribers, instead of sending millions of DELETEs over the network. See
SQL Server Books Online for more information on this.
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Arsen V." <arsen@.community.nospam> wrote in message
news:%23hpi4tKoEHA.2612@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I have a large table with over 20 million rows.
> It is replicated accross a 4 subscribers using Pull subscription. One of
the
> subscribers is also a Distributor.
> We are running some processed to "clean up" the data in this table by
> issuing a lot of DELETE commands (using Primary Key as the where clause)
> against the large table. The DELETE commands are enclosed in BEGIN TRAN -
> COMIT TRAN batches of 20 each.
> The deletion works extremely fast on the Publisher.
> However, it takes very very long time to apply the very same commands on
all
> of the subscribers. The subscribers also have the PRIMARY KEY index that
is
> on the Publisher.
> We need to delete a few million rows from the large table using this
> technique. However, it makes the Subscribers very slow and unresponsive.
> Any suggestions?
> Thanks,
> Arsen
>
I have a large table with over 20 million rows.
It is replicated accross a 4 subscribers using Pull subscription. One of the
subscribers is also a Distributor.
We are running some processed to "clean up" the data in this table by
issuing a lot of DELETE commands (using Primary Key as the where clause)
against the large table. The DELETE commands are enclosed in BEGIN TRAN -
COMIT TRAN batches of 20 each.
The deletion works extremely fast on the Publisher.
However, it takes very very long time to apply the very same commands on all
of the subscribers. The subscribers also have the PRIMARY KEY index that is
on the Publisher.
We need to delete a few million rows from the large table using this
technique. However, it makes the Subscribers very slow and unresponsive.
Any suggestions?
Thanks,
Arsen
Consider doing this in even smaller batches. If that is not possible, and
transactional consistency is not really of importance, then you could use a
parameter called MaxCmdsInTran for logreader (introduced in SQL Server 2000
SP1 I believe). This breaks huge transactions into smaller chunks of
specified size, while storing the commands in distribution database.
There is another approach, that I would recommend. Consider using 'stored
procedure execution' replication. With this method, you keep a copy of the
archiving stored procedure on all subscribers. When you run this procedure
on the publisher, the distribution agent will call this sp on all
subscribers, instead of sending millions of DELETEs over the network. See
SQL Server Books Online for more information on this.
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Arsen V." <arsen@.community.nospam> wrote in message
news:%23hpi4tKoEHA.2612@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I have a large table with over 20 million rows.
> It is replicated accross a 4 subscribers using Pull subscription. One of
the
> subscribers is also a Distributor.
> We are running some processed to "clean up" the data in this table by
> issuing a lot of DELETE commands (using Primary Key as the where clause)
> against the large table. The DELETE commands are enclosed in BEGIN TRAN -
> COMIT TRAN batches of 20 each.
> The deletion works extremely fast on the Publisher.
> However, it takes very very long time to apply the very same commands on
all
> of the subscribers. The subscribers also have the PRIMARY KEY index that
is
> on the Publisher.
> We need to delete a few million rows from the large table using this
> technique. However, it makes the Subscribers very slow and unresponsive.
> Any suggestions?
> Thanks,
> Arsen
>
REplication and BLOB tables
Anyone ever see this error:
The process could not bulk copy into table '"JobApplBlob200501"'.
Expected the text length in data stream for bulk copy of text, ntext, or
image data.
(Source: ELMER (Data source); Error number: 4813)
Elmer is the Subscribing system.
The only odd thing I can see is the publisher and subscriber are build 922.
The distribution host server is build 818. I have other BLOB fields going
from the same publisher to the same subscriber through the same distributor.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
Is this transactional replication ? Is this error during initial snapshot or
during intial load on the subscribers. I have included the replication group
'cos you have some experts in there like Hilary and Paul who can help
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:e20gvdqAFHA.2584@.TK2MSFTNGP09.phx.gbl...
> Anyone ever see this error:
>
> The process could not bulk copy into table '"JobApplBlob200501"'.
> Expected the text length in data stream for bulk copy of text, ntext, or
> image data.
> (Source: ELMER (Data source); Error number: 4813)
>
> Elmer is the Subscribing system.
> The only odd thing I can see is the publisher and subscriber are build
922.
> The distribution host server is build 818. I have other BLOB fields going
> from the same publisher to the same subscriber through the same
distributor.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
>
The process could not bulk copy into table '"JobApplBlob200501"'.
Expected the text length in data stream for bulk copy of text, ntext, or
image data.
(Source: ELMER (Data source); Error number: 4813)
Elmer is the Subscribing system.
The only odd thing I can see is the publisher and subscriber are build 922.
The distribution host server is build 818. I have other BLOB fields going
from the same publisher to the same subscriber through the same distributor.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
Is this transactional replication ? Is this error during initial snapshot or
during intial load on the subscribers. I have included the replication group
'cos you have some experts in there like Hilary and Paul who can help
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:e20gvdqAFHA.2584@.TK2MSFTNGP09.phx.gbl...
> Anyone ever see this error:
>
> The process could not bulk copy into table '"JobApplBlob200501"'.
> Expected the text length in data stream for bulk copy of text, ntext, or
> image data.
> (Source: ELMER (Data source); Error number: 4813)
>
> Elmer is the Subscribing system.
> The only odd thing I can see is the publisher and subscriber are build
922.
> The distribution host server is build 818. I have other BLOB fields going
> from the same publisher to the same subscriber through the same
distributor.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
>
REplication and BLOB tables
Anyone ever see this error:
The process could not bulk copy into table '"JobApplBlob200501"'.
Expected the text length in data stream for bulk copy of text, ntext, or
image data.
(Source: ELMER (Data source); Error number: 4813)
Elmer is the Subscribing system.
The only odd thing I can see is the publisher and subscriber are build 922.
The distribution host server is build 818. I have other BLOB fields going
from the same publisher to the same subscriber through the same distributor.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.orgIs this transactional replication ? Is this error during initial snapshot or
during intial load on the subscribers. I have included the replication group
'cos you have some experts in there like Hilary and Paul who can help
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:e20gvdqAFHA.2584@.TK2MSFTNGP09.phx.gbl...
> Anyone ever see this error:
>
> The process could not bulk copy into table '"JobApplBlob200501"'.
> Expected the text length in data stream for bulk copy of text, ntext, or
> image data.
> (Source: ELMER (Data source); Error number: 4813)
>
> Elmer is the Subscribing system.
> The only odd thing I can see is the publisher and subscriber are build
922.
> The distribution host server is build 818. I have other BLOB fields going
> from the same publisher to the same subscriber through the same
distributor.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
>
The process could not bulk copy into table '"JobApplBlob200501"'.
Expected the text length in data stream for bulk copy of text, ntext, or
image data.
(Source: ELMER (Data source); Error number: 4813)
Elmer is the Subscribing system.
The only odd thing I can see is the publisher and subscriber are build 922.
The distribution host server is build 818. I have other BLOB fields going
from the same publisher to the same subscriber through the same distributor.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.orgIs this transactional replication ? Is this error during initial snapshot or
during intial load on the subscribers. I have included the replication group
'cos you have some experts in there like Hilary and Paul who can help
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:e20gvdqAFHA.2584@.TK2MSFTNGP09.phx.gbl...
> Anyone ever see this error:
>
> The process could not bulk copy into table '"JobApplBlob200501"'.
> Expected the text length in data stream for bulk copy of text, ntext, or
> image data.
> (Source: ELMER (Data source); Error number: 4813)
>
> Elmer is the Subscribing system.
> The only odd thing I can see is the publisher and subscriber are build
922.
> The distribution host server is build 818. I have other BLOB fields going
> from the same publisher to the same subscriber through the same
distributor.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
>
Subscribe to:
Posts (Atom)