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:

Replication Errors

Hello,
I am getting the following error when trying to sync from my local site to
my dr site.
Could not locate entry in sysdatabases for database '10.1.200.70'. No entry
found with that name. Make sure that the name is entered correctly.
(Source: 10.1.200.70 (Data source); Error number: 911)
I am trying to setup replication from my local site to my DR site. I can
register my dr sql box using the IP but not the hostname.
Any thoughts on this error?
Lontae,
try creating an alias for the server's IP address. You might also need an
entry in the hosts/lmhosts (I can't remember which to locate the snapshot
share.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Alias in DNS?
"Paul Ibison" wrote:

> Lontae,
> try creating an alias for the server's IP address. You might also need an
> entry in the hosts/lmhosts (I can't remember which to locate the snapshot
> share.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Lontae,
alias in the server network utility (I'm sure you now this but if not it's
located at start, programs, microsoft sql server...).
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I think Paul means client network utility
Easy mistake to make, I've made it thousands of times, myself.
For netbios resolution use lmhosts (ie a unc snapshot), for ftp use hosts.
name resolution goes through a sequence so it really doesn't matter as both
files are checked, but there are slight performance increases (milli
seconds) if you make the correct entries.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:umx%23oCGvEHA.940@.TK2MSFTNGP14.phx.gbl...
> Lontae,
> alias in the server network utility (I'm sure you now this but if not it's
> located at start, programs, microsoft sql server...).
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Thanks Hilary,
I'm not sure about the setup, but Lontae is registering
the DR site at the local site so I was thinking it was
push rather than pull.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

replication error? (somewhat urgent)

Sorry, this is a long winded post I know. But all help is appreciated as me
and my managers need to explain all this to our CEO on Monday.
sql2k sp3
Transactional, non immediate Updating, continuous, no Transformations
Replication.
This last Tuesday morning a report against was run against the same
Replicated db that it is every morning. It is run automatically at the same
time Monday thru Friday. With the exception of errors on my end, Replication
has been purring along just fine with absolutely no problems for the 3
months its been up and running. However, this last Tuesday the report was
run and the numbers generated didn't match the numbers the end user was
expecting to see. They ran it again 10 minutes later and the numbers were
more to they're liking. As a group, our IT dept has ruled out pretty much
everything we can think of. So our only theory is that Replication was
having problems. We have narrowed down all possibilities to 1 table. This
table is imported into the Publisher from a file using BCP every morning
before this report is run. This table has a column with a GETDATE() function
on it so we know what time the data made it into the Publisher for sure. The
time it says is 45 minutes before the report was run. So the theory is that:
(finally)
There was about a 50 minute lag in Replication.
That the first time the report was run, not all of the data was in the
Subscriber.
That the second time it was run (10 minutes later) all of the data made it
in and life was good.
There is nothing supporting anything like this in the error log. Nor is
there anything supporting this theory in either the Log/ Distribution Agents
history. (although on Thursday I did stop/ restart both Agents for unrelated
reasons and I dont know if that clears them.)
So, does anyone have any way I can prove/ disprove these theories? It seems
a bit far stretched to me since Ive got a fair amount of Replication
experience under my belt and the only time I got anything like a "lag" was
along the lines of "cant exec sp_repldone" and then it blew up totally
untill fixed manually.
All ideas are greatly appreciated.
TIA, ChrisR
You might want to query msrepl_errors to find out if there was any errors in
this time period, like a general network error, or query the
msdistribution_history table to see how many commands were replicated in
this time period. Unfortunately these tables are pruned by default every 2
days so you might not see anything there.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"ChrisR" <ChrisR@.noEmail.com> wrote in message
news:O6qegci2EHA.3244@.TK2MSFTNGP11.phx.gbl...
> Sorry, this is a long winded post I know. But all help is appreciated as
> me
> and my managers need to explain all this to our CEO on Monday.
> sql2k sp3
> Transactional, non immediate Updating, continuous, no Transformations
> Replication.
> This last Tuesday morning a report against was run against the same
> Replicated db that it is every morning. It is run automatically at the
> same
> time Monday thru Friday. With the exception of errors on my end,
> Replication
> has been purring along just fine with absolutely no problems for the 3
> months its been up and running. However, this last Tuesday the report was
> run and the numbers generated didn't match the numbers the end user was
> expecting to see. They ran it again 10 minutes later and the numbers were
> more to they're liking. As a group, our IT dept has ruled out pretty much
> everything we can think of. So our only theory is that Replication was
> having problems. We have narrowed down all possibilities to 1 table. This
> table is imported into the Publisher from a file using BCP every morning
> before this report is run. This table has a column with a GETDATE()
> function
> on it so we know what time the data made it into the Publisher for sure.
> The
> time it says is 45 minutes before the report was run. So the theory is
> that:
> (finally)
> There was about a 50 minute lag in Replication.
> That the first time the report was run, not all of the data was in the
> Subscriber.
> That the second time it was run (10 minutes later) all of the data made it
> in and life was good.
> There is nothing supporting anything like this in the error log. Nor is
> there anything supporting this theory in either the Log/ Distribution
> Agents
> history. (although on Thursday I did stop/ restart both Agents for
> unrelated
> reasons and I dont know if that clears them.)
> So, does anyone have any way I can prove/ disprove these theories? It
> seems
> a bit far stretched to me since Ive got a fair amount of Replication
> experience under my belt and the only time I got anything like a "lag" was
> along the lines of "cant exec sp_repldone" and then it blew up totally
> untill fixed manually.
> All ideas are greatly appreciated.
> TIA, ChrisR
>
|||Hillary do you know if theres adjust these settings to keep the history for
a longer time period? Im guessing the proc in the "dist cleanup" job?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:#Rigw1i2EHA.3468@.TK2MSFTNGP14.phx.gbl...
> You might want to query msrepl_errors to find out if there was any errors
in[vbcol=seagreen]
> this time period, like a general network error, or query the
> msdistribution_history table to see how many commands were replicated in
> this time period. Unfortunately these tables are pruned by default every 2
> days so you might not see anything there.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "ChrisR" <ChrisR@.noEmail.com> wrote in message
> news:O6qegci2EHA.3244@.TK2MSFTNGP11.phx.gbl...
as[vbcol=seagreen]
was[vbcol=seagreen]
were[vbcol=seagreen]
much[vbcol=seagreen]
This[vbcol=seagreen]
it[vbcol=seagreen]
was
>
|||Right click on Replication Monitor, select distributor properties. I believe
you set it there.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"ChrisR" <bla@.noemail.com> wrote in message
news:e0vQb3j2EHA.304@.TK2MSFTNGP11.phx.gbl...
> Hillary do you know if theres adjust these settings to keep the history
> for
> a longer time period? Im guessing the proc in the "dist cleanup" job?
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:#Rigw1i2EHA.3468@.TK2MSFTNGP14.phx.gbl...
> in
> as
> was
> were
> much
> This
> it
> was
>

Replication Error: 4138

If our subcribers try to synchronize the following error occurs. The Upload of the data works but the download failes.

Error messages:
The merge process was unable to perform data validation on
article 'tbl_xTableFieldValue'. Check for SQL Server
errors in the Windows application event log or retry at a
later time. (Source: MSSQL_REPL, Error number:
MSSQL_REPL2147766343)
Get help: http://help/MSSQL_REPL2147766343

Conflicting locking hints are specified for
table "fncReplicationFilterTableFieldValue". This may be
caused by a conflicting hint specified for a view.
(Source: MSSQLServer, Error number: 4138)
Get help: http://help/4138

What does this mean and how can we resolve this?

Best regards,
Markus

This error means that when the merge agent attempted to validate that the replicated data at the Publisher and Subscriber was the same, it was not the same. From the "Validating Replicated Data" topic in SQL Server Books Online:

"Is the fact that validation failed an issue for your application? If the validation failure is an issue, manually update the data so that it is synchronized, or reinitialize the subscription:

"Data can be updated using the tablediff Utility. For more information about using this utility, see How to: Compare Replicated Tables for Differences (Replication Programming).

"For more information about reinitializaton, see Reinitializing a Subscription."

Phillip Garding
Senior Program Manager
Microsoft SQL Server Replication Management Tools

sql

Replication error....

We try to perform replication. We configured our server for
replication. Steps that we followed:
1) Create Publishing and distribution wizard
2) Create Publication wizard
3) Create Pull subscription wizard
We did not meet any errors until while doing them. But at the end of
the process in the replication monitor red cross signs (As seen in
http://stx.cs.deu.edu.tr/~zbektas/er.JPG ) on the publishers and
subscribers and their status seem as "failed". Why?
We can not start the SQL agent because of "the service did not start
due to a logon failure" error. Is this error related with our
replication failure?
Zafer,
SQL Server Replication infrastructure involves many things including a series of jobs which are scheduled and run by the SQL Server Agent. So, if your agent is stopped, replication won't work. Try changing the SQL Server Agent service to use the same doma
in account as your SQL Server Service and then restart it, and then run the Replication Agents.
Regards,
Paul Ibison
|||This is likely your problem. Locate your SQL Server agent and ensure it is
running with a valid account and password.
"zafer" <zaferbektas2000@.yahoo.com> wrote in message
news:c71dc7b.0404020741.2519351e@.posting.google.co m...
> We try to perform replication. We configured our server for
> replication. Steps that we followed:
> 1) Create Publishing and distribution wizard
> 2) Create Publication wizard
> 3) Create Pull subscription wizard
> We did not meet any errors until while doing them. But at the end of
> the process in the replication monitor red cross signs (As seen in
> http://stx.cs.deu.edu.tr/~zbektas/er.JPG ) on the publishers and
> subscribers and their status seem as "failed". Why?
> We can not start the SQL agent because of "the service did not start
> due to a logon failure" error. Is this error related with our
> replication failure?

Replication Error...

I have a SQL server which has the databases installed with transactional replciation enabled. I got an error which is mentioned below in my distribution agent history :-

Violation of PRIMARY KEY constraint 'PK__@.snapshot_seqnos__3647D946'. Cannot insert duplicate key in object '#3553B50D'.

Violation of PRIMARY KEY constraint 'PK__@.snapshot_seqnos__3647D946'. Cannot insert duplicate key in object '#3553B50D'.
(Source: FLORA\INSTANCE1 (Data source); Error number: 2627)
--------------------------

Can anyone let me know the reason for the above mentioned error?

Thanks,
Santhosh.C.Your subscriber is not in sync with your publisher. If the database is not too big or you can shut down the app, go ahead and reinitialize the subscription to sync the replication.

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

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

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

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

SQL Server Mobile Server Agent 3.0 is shown

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

No firewall in both development and server machines.

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

Can anyone tell me whats wrong in this?

Regards

Arif

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

Thanks

Amit

|||

Hello All,

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

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

AND also

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

Thanks for the questions and responses in this forum

Arif

www.anteriorsoft.com

|||

Thanks a lot for your compliments!

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

replication error with oracle

Hi,
I have a transactional replication with Oracle Server, and when it generate the initial snapshot, I got an error, saying ORA-00906, missing left parenthesis. It's actually just because when the snapshot agent tried to genenrate a primary key index, it has
a word of "CLUSTERED" in the script. Is there a way that I can modify the script or at least get rid of the "CLUSTERED' word?
Thanks
Hong Wang,
have a look at sp_addarticle. It has a @.creation_script parameter that
perhaps you could use to apply your own script.
HTH,
Paul Ibison
|||right click on your publication, select properties, go to the articles tab,
click on the browse button to the right of your table. in the snapshot tab,
uncheck include DRI.
Regenerate your snapshot and try again.
Thanks to Allan Mitchell (http://www.sqldts.com,
http://www.intmalemodel.com) for this solution.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eghZmwnXEHA.3944@.tk2msftngp13.phx.gbl...
> Hong Wang,
> have a look at sp_addarticle. It has a @.creation_script parameter that
> perhaps you could use to apply your own script.
> HTH,
> Paul Ibison
>

replication error while copy data

Hi,
I have a transactional replication between SQL Server and Oracle. We already have the schema exist in both databases. So the the snapshut I check the 'If the table exists, leave it unchanged' box. But when the data start to load for the intial snapshut, I
got an error saying' The process encountered invalid column data in bcp file ...' for an existing table. How can me trace down which column has error? What is the data type for the Rowguid in my subscriber ( I do not have a rowguid column setup in the su
bscriber)?
Thanks
Hong,
was this error encountered when creating the snapshot file or when
distributing it to Oracle?
You can get some more (hopefully useful) info by logging:
http://support.microsoft.com/?id=312292
HTH,
Paul Ibison
|||It is encountered when distributing it to Oracle.
Hong Wang
"Paul Ibison" wrote:

> Hong,
> was this error encountered when creating the snapshot file or when
> distributing it to Oracle?
> You can get some more (hopefully useful) info by logging:
> http://support.microsoft.com/?id=312292
> HTH,
> Paul Ibison
>
>
|||OK, then try the logging I mentioned but I had hoped that it was a snapshot
agent error, as this is almost certainly an Oracle issue - mapping of
datatypes etc. Anyway, let's see if the logging returns anything.
Regards,
Paul Ibison
sql

Replication Error while Configuring Distributor

Hi,
I am trying to setup transactional replication on a database within 1
sql instance. When I try to configure the pulisher/distributor, it errors out
with this message.
"SQL Server Enterprise Manager could not configure 'DZDEV007' as the
distributor for 'DZDEV007'.
Error 14234 : The specified '@.server' in invalid (valid values are returned
by sp_helpserver"
What's going on ? Please help.
TIA
Mohan
Hi Mohan,
The following KB seems to match most of your symptoms although your server
name appear to be too short for the problem occur:
http://support.microsoft.com/default...b;en-us;310639
You may also want to consider some other possibilities such as subtle
comparison issues related to the collation that you are using for your server.
-Raymond
"MO" wrote:

> Hi,
> I am trying to setup transactional replication on a database within 1
> sql instance. When I try to configure the pulisher/distributor, it errors out
> with this message.
> "SQL Server Enterprise Manager could not configure 'DZDEV007' as the
> distributor for 'DZDEV007'.
> Error 14234 : The specified '@.server' in invalid (valid values are returned
> by sp_helpserver"
> What's going on ? Please help.
> TIA
> Mohan

Replication Error using Ras Connection win2003srv

I use Dial up (RAS) Connection for syncronize
If I disable my LAN syncronize success
but if I enable My LAN syncronize Fail (cannot connecting to subscriber)
It sounds like you lan connection can't resolve the host name of your subscriber, but it can for the ras dial up.
Can you try to check your hosts file entry for this subscriber? It will be in c:\%windir%\system32\drivers\etc and is called hosts. open it up in a text editor.
if it is not there check the client network setting for this server and verify that the ip address is valid.
Is the subscriber parked in the DMZ by chance? If so it may have a different address.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Nur Firmanullah" wrote:

> I use Dial up (RAS) Connection for syncronize
> If I disable my LAN syncronize success
> but if I enable My LAN syncronize Fail (cannot connecting to subscriber)
>

Replication error thrown during normal T-SQL processing

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

Replication Error process could not read file HELP PLZ

I am getting this error on replication and don't know why. Any ideas?
The process could not read file '\\NJRARSVR00E9\d$\sqldata\system\MSSQL$P001\ReplD ata\unc\NJRARSVR00E9$P001_PTR_PTR\20040707104224\s napshot.pre' due to OS error 5.Why is the extension "pre"? My understanding you're using an alternate location for distribution agent to read snapshot files from. If the file name is snapshot, I also assume that you chose "compression" for it, which means that the extension should be "cab"...What gives? And also post the entire error or errors.

Replication error on tables with different owners

Hi Freinds,
SQL2000
I want to replicate a database that has tables with different schemas.
user1.invoice
user2.invoice
user2.invoice
user1.invoice_detail
user2.invoice_detail
..
..
..
When t run the snapshot, itcreates the tables for dbo and causing problems.
What are my options to roll over this error and problem?
Thanks in advance,
Pat
I assume you mean
user1.invoice
user2.invoice
user3.invoice
If this is the case, put the accounts in place in your subscriber using a
pre snapshot command. Then use sp_addarticle to create the different tables.
The @.destination_owner parameter allows you to specify different owner
names.
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
"Patrick" <patriarck@.gmail.com> wrote in message
news:OUA5aUYuFHA.904@.tk2msftngp13.phx.gbl...
> Hi Freinds,
> SQL2000
> I want to replicate a database that has tables with different schemas.
> user1.invoice
> user2.invoice
> user2.invoice
> user1.invoice_detail
> user2.invoice_detail
> .
> .
> .
> When t run the snapshot, itcreates the tables for dbo and causing
problems.
> What are my options to roll over this error and problem?
> Thanks in advance,
> Pat
>
|||So , basicly it is a manual proces for creating the article and pushing the
replication !
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:e6YeGyZuFHA.1196@.TK2MSFTNGP10.phx.gbl...
>I assume you mean
> user1.invoice
> user2.invoice
> user3.invoice
> If this is the case, put the accounts in place in your subscriber using a
> pre snapshot command. Then use sp_addarticle to create the different
> tables.
> The @.destination_owner parameter allows you to specify different owner
> names.
>
>
> --
> 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
> "Patrick" <patriarck@.gmail.com> wrote in message
> news:OUA5aUYuFHA.904@.tk2msftngp13.phx.gbl...
> problems.
>
|||Well, not really. Most of the functionality is wizard driven, however to do
things outside of the scope of the wizards you have to do some manual steps.
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
"Patrick" <patriarck@.gmail.com> wrote in message
news:OG5qvuhuFHA.904@.tk2msftngp13.phx.gbl...
> So , basicly it is a manual proces for creating the article and pushing
> the replication !
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:e6YeGyZuFHA.1196@.TK2MSFTNGP10.phx.gbl...
>

replication error on script

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

Replication error on a non-replicated DB?

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

Replication error on a non-replicated DB?

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

Replication error on a non-replicated DB?

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

Replication Error of MS SQL server2k

Distribution Agents come with a error and stop between two SQL2000 server.
The error messages is:

The query processor could not produce a query plan from the optimizer
because a query cannot update a text, ntext, or image column and a
clustering key at the same time. (Source: Some Server (Data source); Error number:
8629)

How can I fix it?Is it transactional replication?
If yes - and you are using Identity fields (with not for replication) then check prsp_MSupd_**** stored procedures on subscriber and remove updating Identity field.

replication error message in SQL Server 2005 (June CTP)

I upgraded from earlier version of SQL Server 2005 CTP to the June version.
When I try to create a new Publication for Replication (using the Management
Studio), I get the error message:
Invalid object name msdb.dbo.MSdistributiondbs
Yes, I do not have the above table and I don't see any old publications (the
older ones were removed via Publisher Properties). Why does SQL Server think
that I still have some old publications? Where does it store the info? I
don't find any tables like MSDdistribution, MSdistpublishers etc.http://communities.microsoft.com/newsgroups/default.asp?icp=sqlserver2005
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jason Manfield" wrote:
> I upgraded from earlier version of SQL Server 2005 CTP to the June version.
> When I try to create a new Publication for Replication (using the Management
> Studio), I get the error message:
> Invalid object name msdb.dbo.MSdistributiondbs
> Yes, I do not have the above table and I don't see any old publications (the
> older ones were removed via Publisher Properties). Why does SQL Server think
> that I still have some old publications? Where does it store the info? I
> don't find any tables like MSDdistribution, MSdistpublishers etc.
>