error from trasational repl

Hi all,
i found an error in my transactional replication, from the MSrepl_errors table, one of the error_text is "{CALL
sp_MSins_MisFlightScheduleSector (N'KUL', N'CHI', N'KUL;SIN;CHI', 2004-12-29 04:03:00)}", when i run this query in the query
analyzer with the subscriber's database, it show me this error "[Microsoft][ODBC SQL Server Driver]Invalid character value for cast
specification", can anyone tell me some hints or I can't do this action directly as i want to find what error about this query.
Thx
Hi,
Thanks for your posting!
From your descriptions, I understood that you encountered error message
"Invalid character value for cast specification"
First of all, Please know that replication issues might tend to be very
complex and hard to troubleshoot in newsgroups. I recommend that you open a
Support incident with Microsoft Product Support Services (PSS) so that a
dedicated Support Professional can work with you in a more timely and
efficient manner. If you need any help in this regard, please let me know.
For a complete list of Microsoft Product Support Services phone numbers,
please go to the following address on the World Wide Web:
http://support.microsoft.com/directory/overview.asp
Secondly, I noticed there is an Knowledge Base article that mentioned this
error message
FIX: Merge Agent may fail with an "Invalid character value for cast
specification" error message
http://support.microsoft.com/kb/867880
Note that it will be a FREE incident asking the hotfix and this hotfix has
been scheduled to be included in SP4
Thirdly, does your replication works as your expected? Is it possible for
you to provide more information about your scenario, such as how do you
create the replication. Does the same steps work well in other client? Is
is possible for you to show me a detailed description, with which I could
reproduce it on my side?
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||The interpretation of '2004-12-29 04:03:00' as a valid date format depends
on the language employed. This works for English, but not British English
(we're always awkward ). So, I wonder if you are using a login which has a
different default language on the publisher and subscriber?
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||if the language is different, will the first time snapshot replication success and then transactional replication is fail? Since the
snapshot replication is success. thanks
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message news:uuUeVMq7EHA.3236@.TK2MSFTNGP15.phx.gbl...
> The interpretation of '2004-12-29 04:03:00' as a valid date format depends
> on the language employed. This works for English, but not British English
> (we're always awkward ). So, I wonder if you are using a login which has a
> different default language on the publisher and subscriber?
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||The snapshot runs using BCP text files which is an entirely different
mechanism to the synchronization using stored procedures. If it still
exists, please have a look at the format of the date values in the relevant
file to see if this is on the right track.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||thx Paul
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message news:%230p4bZ27EHA.3076@.TK2MSFTNGP15.phx.gbl...
> The snapshot runs using BCP text files which is an entirely different
> mechanism to the synchronization using stored procedures. If it still
> exists, please have a look at the format of the date values in the relevant
> file to see if this is on the right track.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
sql

Error during Merge Replication initialization

Hi,
We had a healthy 2-way merge replication set up with a SQL 2005
publisher/distributor and a SQL 2000 subscriber which had been running for
about a week. I re-created the publication so I could add automatic identity
range management to an article. I sucessfully re-created a push subscription
to a SQL 2005 subscriber but when I tried to create one to the same SQL 2000
machine that had been quite happy a few hours earlier, I kept getting this
error during initialization: "bulk data stream was incorrectly specified as
sorted". Nothing much has changed as far as I can see. Can anyone give me
some pointers?
Thanks,
Pete
Thanks for your response.
The collations were different and, having read about a similar problem on
another newsgroup, I changed the collation on my subscriber. The problem went
away so guess that fixed it. It's odd that the subscription had been
synchronizing fine up to the point that I changed the identity range
management options because I certainly didn't change the collation in between
dropping the subscriber and re-creating it. I'm sure there's a good reason
for it but it's no longer urgent.
Pete
"JPD" wrote:

> Hi Pete,
> Are you using the same collation on both the publishing database and the
> subscribing database? Have you tried using sp_dboption to set the
> select into/bulkcopy option of the subscriber database to ON?
> Jonathan
>
> Pete wrote:
>

Error creating new push subscription after deleting the existing o

I created a transactional replication publication and pushed a subscriber,
and that works fine. I deleted existing push subscription in the publisher
with enterprise manager, and also deleted automatically generated replication
jobs related to that push subscription. I noticed that the push subscription
was not automatically deleted in the subscriber as it should. Moreover, when
I created a new push subscription with the same article, I encountered an
error. The error message is as following.
Subscriptions were created successfully at the following subscribers.
…
SQL Server Enterprise manager could not start the snapshot agent.
Error 14262:the specified @.job_id … does not exist.
Please help.
The error indicates that a replication agent job was deleted accidentally.
When this issue occurs, the most efficient way would be rebuilding the
replication from scratch.
Sincerely,
William Wang
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
This posting is provided "AS IS" with no warranties, and confers no rights.

Error creating merge replication

Hello:
I have implemented many trasactional replications on my SQL Server 2005
standar and thay are working ok.
Now, I am trying to implement a merge replication (in the same server) of a
table with a filter and when I click the finish option of the wizard in the
last step I receive the next error:
"Object reference not set to an instance of an object. (CreatePubWizard)"
What is happening?
Please, can anybody help my to solve this problem?
Thanks in advance
Jesús
its hard to say what the problem is. Recreate your publication using the
wizard, but this time when you get to the Wizard Actions dialog, select
Generate a script file, and deselect create the publication. Click next,
give the publication a name and click Finish. Then using Query Analyzer or
SSMS recreate the publication.
http://www.zetainteractive.com - Shift Happens!
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
"Cetel Sistemas" <CetelSistemas@.discussions.microsoft.com> wrote in message
news:067B6719-0D4F-4E8E-9BDB-0A8189FF981B@.microsoft.com...
> Hello:
> I have implemented many trasactional replications on my SQL Server 2005
> standar and thay are working ok.
> Now, I am trying to implement a merge replication (in the same server) of
> a
> table with a filter and when I click the finish option of the wizard in
> the
> last step I receive the next error:
> "Object reference not set to an instance of an object. (CreatePubWizard)"
> What is happening?
> Please, can anybody help my to solve this problem?
> Thanks in advance
> Jess

Error Creating a New Publication

When Attempting to set up replication on my SQL server 2005 server, I right
click and select new publication and imediately get the following error
message:
SQL Server is unable to connect to server 'spi-svr6'. (New Publication Wizard)
For help, click:
http://go.microsoft.com/fwlink?ProdN...t&LinkId=20476
===================================
SQL Server replication requires the actual server name to make a connection
to the server. Connections through a server alias, IP address, or any other
alternate name are not supported. Specify the actual server name,
'SPI-SVR203'. (Replication.Utilities)
Program Location:
at
Microsoft.SqlServer.Management.UI.ReplicationSqlCo nnection.CheckServerAlias(ServerConnection conn)
at Microsoft.SqlServer.Management.UI.ReplicationSqlCo nnection.Open()
at
Microsoft.SqlServer.Management.UI.CreatePublicatio nWizard.PrepareToShow()
The help link provides nothing on this and I have been unable to locate any
info on other user sites. I am at a loss on what the communication problem
may be as the replication wizzard does not even begin.
Any help would be appreciated
"Lance" wrote:

> When Attempting to set up replication on my SQL server 2005 server, I right
> click and select new publication and imediately get the following error
> message:
> SQL Server is unable to connect to server 'spi-svr6'. (New Publication Wizard)
> --
> For help, click:
> http://go.microsoft.com/fwlink?ProdN...t&LinkId=20476
> ===================================
> SQL Server replication requires the actual server name to make a connection
> to the server. Connections through a server alias, IP address, or any other
> alternate name are not supported. Specify the actual server name,
> 'SPI-SVR203'. (Replication.Utilities)
> --
> Program Location:
> at
> Microsoft.SqlServer.Management.UI.ReplicationSqlCo nnection.CheckServerAlias(ServerConnection conn)
> at Microsoft.SqlServer.Management.UI.ReplicationSqlCo nnection.Open()
> at
> Microsoft.SqlServer.Management.UI.CreatePublicatio nWizard.PrepareToShow()
> The help link provides nothing on this and I have been unable to locate any
> info on other user sites. I am at a loss on what the communication problem
> may be as the replication wizzard does not even begin.
> Any help would be appreciated
|||If you look at management studio and check the name of the server at the top
of the tree
that is the name under which the server is registered...
Also do a sp_helpserver query to determine the name corresponding to id 0
the registered name should be the same as that for id 0 (which normally is the
name of the server where SQL is installed.
"Lance" wrote:

> When Attempting to set up replication on my SQL server 2005 server, I right
> click and select new publication and imediately get the following error
> message:
> SQL Server is unable to connect to server 'spi-svr6'. (New Publication Wizard)
> --
> For help, click:
> http://go.microsoft.com/fwlink?ProdN...t&LinkId=20476
> ===================================
> SQL Server replication requires the actual server name to make a connection
> to the server. Connections through a server alias, IP address, or any other
> alternate name are not supported. Specify the actual server name,
> 'SPI-SVR203'. (Replication.Utilities)
> --
> Program Location:
> at
> Microsoft.SqlServer.Management.UI.ReplicationSqlCo nnection.CheckServerAlias(ServerConnection conn)
> at Microsoft.SqlServer.Management.UI.ReplicationSqlCo nnection.Open()
> at
> Microsoft.SqlServer.Management.UI.CreatePublicatio nWizard.PrepareToShow()
> The help link provides nothing on this and I have been unable to locate any
> info on other user sites. I am at a loss on what the communication problem
> may be as the replication wizzard does not even begin.
> Any help would be appreciated
|||Thanks for great help gopal!
"Lance" wrote:

> When Attempting to set up replication on my SQL server 2005 server, I right
> click and select new publication and imediately get the following error
> message:
> SQL Server is unable to connect to server 'spi-svr6'. (New Publication Wizard)
> --
> For help, click:
> http://go.microsoft.com/fwlink?ProdN...t&LinkId=20476
> ===================================
> SQL Server replication requires the actual server name to make a connection
> to the server. Connections through a server alias, IP address, or any other
> alternate name are not supported. Specify the actual server name,
> 'SPI-SVR203'. (Replication.Utilities)
> --
> Program Location:
> at
> Microsoft.SqlServer.Management.UI.ReplicationSqlCo nnection.CheckServerAlias(ServerConnection conn)
> at Microsoft.SqlServer.Management.UI.ReplicationSqlCo nnection.Open()
> at
> Microsoft.SqlServer.Management.UI.CreatePublicatio nWizard.PrepareToShow()
> The help link provides nothing on this and I have been unable to locate any
> info on other user sites. I am at a loss on what the communication problem
> may be as the replication wizzard does not even begin.
> Any help would be appreciated
|||gopal,
You are right on target. The helpserver SP returns the old server name.
Will the drop/add sp's fix this?
"gopal" wrote:
[vbcol=seagreen]
> If you look at management studio and check the name of the server at the top
> of the tree
> that is the name under which the server is registered...
> Also do a sp_helpserver query to determine the name corresponding to id 0
> the registered name should be the same as that for id 0 (which normally is the
> name of the server where SQL is installed.
> "Lance" wrote:
|||You can use the @.@.SERVERNAME global variable or the
SERVERPROPERTY('ServerName') function in SQL Server to find the network name
of the computer running SQL Server. The ServerName property of the
SERVERPROPERTY function automatically reports the change in the network name
of the computer when you restart the computer and the SQL Server service.
The @.@.SERVERNAME global variable retains the original SQL Server computer
name until the SQL Server name is manually reset.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

error configure

Hi every1,

I have problem about configuration in Merge Replication.

First, I configure for SQL Server 2000, expand Replication> Publication>New Publication>Choose Publication Database, I choose pubs>Select Publication Type, choose Merge Replication>Specify Subscribers Type, choose Servers running SQL Server 2000 and Devices running SQL Server CE>Specify article (ex. authors)…>Finish. I configure succeeded (A snapshot of 1 article(s) is generated).

Snapshot folder reference to path: \\server_name\C$\Program Files\Microsoft SQL Server 2005 Mobile Edition\Server\Replication

Next, I configure for SQL Server Mobile Agent, Subscriber Type, choose SQL Server Mobile Edition>Web server, choose Create a new virtual directory>Alias: Replication (reference to the path: C:\Program Files\Microsoft SQL Server 2005 Mobile Edition\Server\Replication)..>Client Authentication, choose Client will connect anonymously….>Snapshot share access, enter \\server_name\Replication =>error: The share '\\server_name\Replication'cannot be accessed. Please specify a new share. Why that?

I want to ask u I configure that correctly? If Subscriber is Pocket PC Emulator, what is subscriber ’sproperty of SqlCeReplication (Windows PocketPC Emulator?)?

Plz help me. Thanks so much.

I need your help very much. Please.|||

Firstly you should not use \\server_name\c$\.... By C$ you are assuming that the login that syncs has access to this machine's c$.

You should use a share: \\servername\repl.

Then give this share name created in the Configure virtual directory.

And I do not understand your last question about emulator.

error configure

Hi every1,

I have problem about configuration in Merge Replication.

First, I configure for SQL Server 2000, expand Replication> Publication>New Publication>Choose Publication Database, I choose pubs>Select Publication Type, choose Merge Replication>Specify Subscribers Type, choose Servers running SQL Server 2000 and Devices running SQL Server CE>Specify article (ex. authors)…>Finish. I configure succeeded (A snapshot of 1 article(s) is generated).

Snapshot folder reference to path: \\server_name\C$\Program Files\Microsoft SQL Server 2005 Mobile Edition\Server\Replication

Next, I configure for SQL Server Mobile Agent, Subscriber Type, choose SQL Server Mobile Edition>Web server, choose Create a new virtual directory>Alias: Replication (reference to the path: C:\Program Files\Microsoft SQL Server 2005 Mobile Edition\Server\Replication)..>Client Authentication, choose Client will connect anonymously….>Snapshot share access, enter \\server_name\Replication =>error: The share '\\server_name\Replication'cannot be accessed. Please specify a new share. Why that?

I want to ask u I configure that correctly? If Subscriber is Pocket PC Emulator, what is subscriber ’sproperty of SqlCeReplication (Windows PocketPC Emulator?)?

Plz help me. Thanks so much.

I need your help very much. Please.|||

Firstly you should not use \\server_name\c$\.... By C$ you are assuming that the login that syncs has access to this machine's c$.

You should use a share: \\servername\repl.

Then give this share name created in the Configure virtual directory.

And I do not understand your last question about emulator.

Error at parameter 93 during datastream processing of parameterize

Has anyone seen this error before? It ulimately causes replication to fail.
The error message is:
Error at parameter 93 during datastream processing of parameterized command.
Keino Jason
SQL DBA/Developer
Never seen or heard of this error before. Are you using the same service
pack/hotfix level on each of the servers involved in this replication setup?
If so, and it is >=sp3, can you give some more details on the setup you
have - replication type, which agent fails, at what stage does it fail, what
is the complete error message etc.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Error at parameter 34 during datastream processing of parameterized command.

replication is failing with mssg "Error at parameter 34 during datastream processing of parameterized command." any input what could be the reason for this failure and what action should i take to sort out this issue ?
From http://www.developmentnow.com/g/114_2003_7_22_0_0/sql-server-replication.htm
Posted via DevelopmentNow.com Groups
http://www.developmentnow.com
Not sure about this message. Have seen it reported a couple of times in the
newsgroup without there ever being a definitive fix (AFAIR). Please can you
tell us all the relevant info: type of replication, sp levels etc. Also do
some logging to try to get a more detailed message.
Rgds,
Paul Ibison

error applying initial snapshot

Hi

Using HTTPS web synchronisation - merge replication.

I have a very strange error where it bombs out half-way through downloading the snapshot

Downloaded a total of 3 chunks.
Downloaded snapshot file 'snapshot.pre'.
Downloaded snapshot file 'myView_6.sch'.
Downloaded snapshot file 'table2_2.sch'.
Downloaded snapshot file 'table2_2.cft'.
Downloaded snapshot file 'table3_3.sch'.
Downloaded snapshot file 'table3_3.cft'.
Downloaded snapshot file 'mytestProc1_4.sch'.
Downloaded snapshot file 'mytestProc3_5.sch'.
Downloaded snapshot file 'MSmerge_tombstone.bcp'.
Downloaded snapshot file 'MSmerge_contents90_forall.bcp'.
Downloaded snapshot file 'MSmerge_genhistory90.bcp'.
Downloaded snapshot file 'sysmergesubsetfilters90.bcp'.
Downloaded snapshot file 'table2_2.bcp'.

A failure occurred when accessing 'MSmerge_contents_table290_forall.bcp' due to an operating system error [3='The system cannot find the path specified.'] during Web synchronization. Ensure that the -InternetLogin user when using basic authentication and the user running the merge when using Windows integrated authentication has been granted access to the snapshot share.

A failure occurred when accessing 'MSmerge_contents_table290_forall.bcp' due to an operating system error [3='The system cannot find the path specified.'] during Web synchronization. Ensure that the -InternetLogin user when using basic authentication and the user running the merge when using Windows integrated authentication has been granted access to the snapshot share.

The processing of the response message failed.

I've tried compressing the snapshot, and can see the bcp file in the snapshot.

HELP !!!

Bruce...

Did you have a snapshot agent job (from SQL Agent) while merge is running? Was the job disabled? It is very likely that some process is holding that file when downloading the snapshot files.

Thanks.

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

No - definitely not

The subscriptions is setup as follows:

subscription.CreateSyncAgentByDefault = False -- I wrote a windows service to synchronise
subscription.UseWebSynchronization = True
subscription.InternetSecurityMode = AuthenticationMethod.BasicAuthentication
subscription.SubscriberType = MergeSubscriberType.Anonymous

We've tried compressing and not-compressing the snapshot....

It used the snapshot fine when initialising the subscription - it's only after we added a proc to the publication and recreated the subscription. We've tried a number of times to synchronise - we've even rebooted the server (publisher)...

Thanks
Bruce

|||

Is it necessary to re-create the snapshot after adding a SP to the publication?

Also I am not sure why you need to recreate the subscription in this scenario?

Thanks.

|||

Yes it is necessary - this is covered in BOL.

I didn't say we recreated the subscription in this scenario

Thanks
Bruce

|||

Let us get back to your original question.

1. Since you used basic authentication in the web sync, so I assuem you have provided NT user name and password to access the web virtual directory.

2. Is 'MSmerge_contents_table290_forall.bcp' a big file to get downloaded? Please also check the timeout value on both the web server and the merge subscription.

3. I assum you have tried multiple times of the initial snapshot. Is it always failed on the same file ('MSmerge_contents_table290_forall.bcp' )?

Thanks.

|||

1. yes

2. no - it is not timing out- it even downloads the cabinet file with the file in it

3. yes

|||

Let us try to isolate this problem. Does this problem also appear if you run the merge agent to sync in fully connected mode, not web sync?

Thanks.

|||

Hi

We don't create the sync agent - so it a bit hard to test - some of our subscribers are sqlExpress - which doesn't have the agent....

(see earlier append - (subscription.CreateSyncAgentByDefault = False)

Bruce

|||

For debugging purpose, you can start the initial sync by invoking replmerg.exe. This executable should be available on SQL Express.

To run the sync agent in fully connected mode, you may want to try

replmerg.exe" -Publisher [publisher server name] -PublisherDB [publisher database name] -Publication [publication name] -PublisherSecurityMode 0 -PublisherLogin [xxxxxx] -PublisherPassword xxxxxx -Distributor [distributor server name] -DistributorLogin [xxxxxx] -DistributorPassword xxxxxx -DistributorSecurityMode 0 -Subscriber [subscriber server name] -SubscriberDB [subscriber database name] -SubscriberLogin [xxxxxx] -SubscriberPassword xxxxxx -SubscriberSecurityMode 0 -SubscriptionType (?) -QueryTimeout 0 -LoginTimeout 0

To run the sync agent in web sync mode, additional parameters needed as

-InternetURL https://your_web_server_name/virtual_directory_path/replisapi.dll -InternetTimeout 5000 -InternetLogin [domain user name] -InternetPassword [domain user password].

Let's see if the same problem shown again.

Thanks.

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Ok

I've spent most of the day on this problem (and another one you are also helping me on!)

The problem is such:

1. The initial snapshot downloads fine

2. If I add a proc to the publication, I need to recreate the snapshot - the next time it sync's I get the error listed earlier (about not being able to find the file etc)

3. If I mark the subscription for initilization, it works successfully

Sorry if I've been obtuse in explaining this. The only difference in my code in scenarios 2. and 3. is the addition of the following line:

subscription.Reinitialize(False)

All other parameters are the same - this is what is so strange...

4. Also - if I mark all subscriptions to be reinitialized (at the publisher) - it is able to download the snapshot ok next time we try and synchronise at the subscriber.

Bruce

|||

Hello Bruce,

1. Looks like you got a workaround already.

2. I still have no luck to repro your scenario at this moment but I will continuous to try.

3. Can you profile (if you prefer) the TSQLs which a "NEW" SP is added to the existing publication. Particularly when sp_addmergearticle is invoked, what is the value set for @.force_reinit_subscription? If you got "1", can you somehow modify the RMO code to make it "0" and see if this issue still occurs.

Thanks.

Leo

This posting is provided "AS IS" with no warranties, and confers no rights.

Error after Windows Update

Hi to all,
i have a win2k machine with sql server 7 on it, that is beeing fed with
data by another one, using replication.
The other machine is an sql server 2k and is the Publisher of the
replication
The machine with sql server 7, needed a windows update and so i did it this
morning.
After the reboot, the Publisher machine cannot push data to sql 7,and it
displays the
following error :
"Unable to find the remote agent on the remote server. Upgrade SQL server on
the Subscriber if version is <= 7.0 or rerun SQL Server setup on the remote
server. The step failed."
Agent is of cource running on the sql 7 machine.
Does anyone else has encountered this ?
Thanks a lot for any help,
anthony
what is the db compatibility mode on the subscriber in the subscription
database?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"objectref" <objectref@.mediatrel.com> wrote in message
news:uR4tgdV%23EHA.1260@.TK2MSFTNGP12.phx.gbl...
> Hi to all,
> i have a win2k machine with sql server 7 on it, that is beeing fed with
> data by another one, using replication.
> The other machine is an sql server 2k and is the Publisher of the
> replication
> The machine with sql server 7, needed a windows update and so i did it
this
> morning.
> After the reboot, the Publisher machine cannot push data to sql 7,and it
> displays the
> following error :
> "Unable to find the remote agent on the remote server. Upgrade SQL server
on
> the Subscriber if version is <= 7.0 or rerun SQL Server setup on the
remote
> server. The step failed."
> Agent is of cource running on the sql 7 machine.
>
> Does anyone else has encountered this ?
> Thanks a lot for any help,
> anthony
>

Error after Disabling Merge Replication

Hi...
I disabled merge replication via the Ent. Mgr wizard and when I try to update a table that was previously part of the replication (either via stored proc or manually in query analyzer) I get this error:
Invalid object name 'dbo.sysmergearticles'
I am guessing that there are still some references or parts of the replication that were somehow left around even after disabling replication. I don't have this table in my database anymore.
Does anyone know how to resolve this issue?
Thanks
- dw
dw,
if the database is no longer involved in replication, either as a subscriber
or as a publisher, then you can run sp_removedbreplication.
HTH,
Paul Ibison
|||It sounds like the merge triggers are still in place. To get this working correctly you should run this script in your former subscription database.
DECLARE @.name varchar(129)
DECLARE list_pubs CURSOR FOR
SELECT name FROM syspublications
OPEN list_pubs
FETCH NEXT FROM list_pubs INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'dropping publication ' +@.name
EXEC sp_dropsubscription @.publication=@.name,
@.article='all', @.subscriber ='all'
EXEC sp_droppublication @.name
FETCH NEXT FROM list_pubs INTO @.name
END
CLOSE list_pubs
DEALLOCATE list_pubs
GO
DECLARE @.name varchar(129)
DECLARE list_replicated_tables CURSOR FOR
SELECT name FROM sysobjects WHERE replinfo 0
UNION
SELECT name FROM sysmergearticles
OPEN list_replicated_tables
FETCH NEXT FROM list_replicated_tables INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'unmarking replicated table ' +@.name
--select @.name='drop Table ' + @.name
EXEC sp_msunmarkreplinfo @.name
FETCH NEXT FROM list_replicated_tables INTO @.name
END
CLOSE list_replicated_tables
DEALLOCATE list_replicated_tables
GO
UPDATE syscolumns set colstat = colstat & ~4096 WHERE
colstat &4096 0
GO
UPDATE sysobjects set replinfo=0
GO
DECLARE @.name nvarchar(129)
DECLARE list_views CURSOR FOR
SELECT name FROM sysobjects WHERE type='V' and (name
like 'syncobj_%' or name like 'ctsv_%' or name
like 'tsvw_%')
OPEN list_views
FETCH NEXT FROM list_views INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'dropping View ' +@.name
select @.name='drop View ' + @.name
EXEC sp_executesql @.name
FETCH NEXT FROM list_views INTO @.name
END
CLOSE list_views
DEALLOCATE list_views
GO
DECLARE @.name nvarchar(129)
DECLARE list_procs CURSOR FOR
SELECT name FROM sysobjects WHERE type='p' and (name
like 'sp_ins_%' or name like 'sp_MSdel_%' or name
like 'sp_MSins_%'or name like 'sp_MSupd_%' or name
like 'sp_sel_%' or name like 'sp_upd_%')
OPEN list_procs
FETCH NEXT FROM list_procs INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'dropping procs ' +@.name
select @.name='drop procedure ' + @.name
EXEC sp_executesql @.name
FETCH NEXT FROM list_procs INTO @.name
END
CLOSE list_procs
DEALLOCATE list_procs
GO
DECLARE @.name nvarchar(129)
DECLARE list_conflict_tables CURSOR FOR
SELECT name From sysobjects WHERE type='u' and name
like '_onflict%'
OPEN list_conflict_tables
FETCH NEXT FROM list_conflict_tables INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'dropping conflict_tables ' +@.name
select @.name='drop Table ' + @.name
EXEC sp_executesql @.name
FETCH NEXT FROM list_conflict_tables INTO @.name
END
CLOSE list_conflict_tables
DEALLOCATE list_conflict_tables
GO
UPDATE syscolumns set colstat=2 WHERE name='rowguid'
GO
Declare @.name nvarchar(129), @.constraint nvarchar(129)
DECLARE list_rowguid_constraints CURSOR FOR
select object_name(sysobjects.parent_obj), sysobjects.name
from sysobjects, syscolumns where sysobjects.type ='d'
and syscolumns.id=sysobjects.parent_obj
and syscolumns.name='rowguid'
OPEN list_rowguid_constraints
FETCH NEXT FROM list_rowguid_constraints INTO @.name,
@.constraint
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'dropping rowguid constraints ' +@.name
select @.name='ALTER TABLE ' + rtrim(@.name ) + '
DROP CONSTRAINT ' +@.constraint
EXEC sp_executesql @.name
FETCH NEXT FROM list_rowguid_constraints INTO
@.name, @.constraint
END
CLOSE list_rowguid_constraints
DEALLOCATE list_rowguid_constraints
GO
Declare @.name nvarchar(129), @.constraint nvarchar(129)
DECLARE list_rowguid_indexes CURSOR FOR
select object_name(id), name from sysindexes where name
like 'index%'
OPEN list_rowguid_indexes
FETCH NEXT FROM list_rowguid_indexes INTO @.name,
@.constraint
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'dropping rowguid indexes ' +@.name
select @.name='drop index ' + rtrim(@.name ) + '.'
+@.constraint
EXEC sp_executesql @.name
FETCH NEXT FROM list_rowguid_indexes INTO @.name,
@.constraint
END
CLOSE list_rowguid_indexes
DEALLOCATE list_rowguid_indexes
GO
Declare @.name nvarchar(129)
DECLARE list_rowguid_columns CURSOR FOR
select object_name(syscolumns.id) from syscolumns,
sysobjects where syscolumns.name like 'rowguid' and
object_Name(sysobjects.id) not like 'msmerge%'
and sysobjects.id=syscolumns.id
and sysobjects.type='u' order by 1
OPEN list_rowguid_columns
FETCH NEXT FROM list_rowguid_columns INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'dropping rowguid columns ' +@.name
select @.name='Alter Table ' + rtrim(@.name ) + '
drop column rowguid'
print @.name
EXEC sp_executesql @.name
FETCH NEXT FROM list_rowguid_columns INTO @.name
END
CLOSE list_rowguid_columns
DEALLOCATE list_rowguid_columns
GO
DELETE FROM sysmergepublications
GO
DELETE FROM sysmergesubscriptions
GO
DELETE FROM syssubscriptions
GO
DELETE FROM sysarticleupdates
GO
DELETE FROM systranschemas
GO
DELETE FROM sysmergearticles
GO
DELETE FROM sysmergeschemaarticles
GO
DELETE FROM sysmergesubscriptions
GO
DELETE FROM sysarticles
GO
DELETE FROM sysschemaarticles
GO
DELETE FROM syspublications
GO
DELETE FROM sysmergeschemachange
GO
DELETE FROM sysmergesubsetfilters
GO
DELETE FROM MSdynamicsnapshotjobs
GO
DELETE FROM MSdynamicsnapshotviews
GO
DELETE FROM MSmerge_altsyncpartners
GO
DELETE FROM MSmerge_contents
GO
DELETE FROM MSmerge_delete_conflicts
GO
DELETE FROM MSmerge_errorlineage
GO
DELETE FROM MSmerge_genhistory
GO
DELETE FROM MSmerge_replinfo
GO
DELETE FROM MSmerge_tombstone
GO
DELETE FROM MSpub_identity_range
GO
DELETE FROM MSrepl_identity_range
GO
DELETE FROM MSreplication_subscriptions
GO
DELETE FROM MSsubscription_agents
GO
|||Thanks folks. The problem was the triggers. I manually deleted them (I didn't see the replies to this message earlier) and now it works. However, I will be making a copy of the script that Hilary provided.
great! - dw