Can't find NOT FOR REPLICATION option

» Create the subscription table manually using the IDENTITY property and
the NOT FOR REPLICATION option.Dan,
it looks like your post is a reply to an earlier post/thread, and all I have
to go on is the title but if you need to script out a table with this
attribute it should look something like this:
CREATE TABLE [dbo].[TestIdent] (
[ID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[descr] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
) ON [PRIMARY]
GO
BTW, if this is a transactional nosync initialization, used with a view to
using the identity property on the subscriber in a failover situation, you
should consider initializing with queued updating subscribers instead, as
the internal identity number will not get incremented on the subscriber and
DBCC CHECKIDENT can't be used on columns set with this attribute to reseed
it.
HTH,
Paul Ibison

Can't find NOT FOR REPLICATION option

Create the subscription table manually using the IDENTITY property and
the NOT FOR REPLICATION option.
Dan,
it looks like your post is a reply to an earlier post/thread, and all I have
to go on is the title but if you need to script out a table with this
attribute it should look something like this:
CREATE TABLE [dbo].[TestIdent] (
[ID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[descr] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
) ON [PRIMARY]
GO
BTW, if this is a transactional nosync initialization, used with a view to
using the identity property on the subscriber in a failover situation, you
should consider initializing with queued updating subscribers instead, as
the internal identity number will not get incremented on the subscriber and
DBCC CHECKIDENT can't be used on columns set with this attribute to reseed
it.
HTH,
Paul Ibison
|||Thanks Paul, this is actually my first post about this, I just copied
the message SQL gave me when trying to setup a snapshot replication. I
looked all over Enterprise Manager but couldn't find an option "NOT FOR
REPLICATION". I guess you can only do it via SQL script.
FYI, I am just trying to reset a test DB back to production state every
night at midnight (after they play in the test DB all day). Nothing
should replicate to the production server......only from the
productions server to the test server. Hope I am headed in the right
direction.
Thanks again,
Dan
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
|||Dan,
you are on the right track. What you are doing is called a nosync
initialization. You need to script out the tables and create them on the
subscriber before initializing and change the article properties so as to
not drop the table on the subscriber during a name conflict. The setting
"NOT FOR
REPLICATION" can be done in EM. It is on an identity's column in table
design, but can equally be done in a script.
Snapshot replication is good for this, but 'Database Shipping' can equally
be used and also takes users and permissions that your application might
require. It also saves you from adding articles as the application develops.
HTH,
Paul Ibison
|||Can I just create a blank database on the test server and restore the
last backup over top of the blank DB? Then just change each IDENITY
column to NOT FOR REPLICATION?
Thanks for all the help,
Dan
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
|||Dan,
if you mean a nosync initialization, this doesn't work, as when your
subscriber is ultimately being used as a test machine, your identity values
will clash. If the identity columns are used for PKs then you will end up
getting PK violations. This is because replication will not increment the
identity value when records are added to the subscriber and DBCC CHECKIDENT
cannot be used to reseed the identity value. I'd consider shipping database
backups for your scenario. You could alternatively avoid these problems by
using merge or transactional with queued updating subscribers, but there
will be a lot of unnecessary work going on behind the scenes on your
production server which you really don't want.
HTH,
Paul Ibison
|||So replication will not increment the identity value when records are
added to the subscriber? What value, if any, gets put in the identity
field during replication?
How does the shipping database option work?
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
|||Dan,
true - replication doesn't increment the internal identity value - it stays
as the initial seed. Don't confuse this with the population of the column
during replication which works OK, essentially doing an identity insert.
This setting is really used for updating subscribers - snapshot or
transactional, or merge.
By database shipping I was thinking of doing a backup of the production
database, copying it over to the test server and restoring it there. You'll
need to create your own jobs to implement this, but it is not difficult. In
fact if you do a search for log-shipping scripts you can hack these to do
what you want, which is essentially very similar.
HTH,
Paul Ibison
|||Thanks Paul, I will Google log-shipping scripts and see if I can get
that working.
Thanks again for all the help,
Dan
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

Can't find NOT FOR REPLICATION option

Create the subscription table manually using the IDENTITY property and
the NOT FOR REPLICATION option.Dan,
it looks like your post is a reply to an earlier post/thread, and all I have
to go on is the title but if you need to script out a table with this
attribute it should look something like this:
CREATE TABLE [dbo].[TestIdent] (
[ID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[descr] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
) ON [PRIMARY]
GO
BTW, if this is a transactional nosync initialization, used with a view to
using the identity property on the subscriber in a failover situation, you
should consider initializing with queued updating subscribers instead, as
the internal identity number will not get incremented on the subscriber and
DBCC CHECKIDENT can't be used on columns set with this attribute to reseed
it.
HTH,
Paul Ibison|||Thanks Paul, this is actually my first post about this, I just copied
the message SQL gave me when trying to setup a snapshot replication. I
looked all over Enterprise Manager but couldn't find an option "NOT FOR
REPLICATION". I guess you can only do it via SQL script.
FYI, I am just trying to reset a test DB back to production state every
night at midnight (after they play in the test DB all day). Nothing
should replicate to the production server......only from the
productions server to the test server. Hope I am headed in the right
direction.
Thanks again,
Dan
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!|||Dan,
you are on the right track. What you are doing is called a nosync
initialization. You need to script out the tables and create them on the
subscriber before initializing and change the article properties so as to
not drop the table on the subscriber during a name conflict. The setting
"NOT FOR
REPLICATION" can be done in EM. It is on an identity's column in table
design, but can equally be done in a script.
Snapshot replication is good for this, but 'Database Shipping' can equally
be used and also takes users and permissions that your application might
require. It also saves you from adding articles as the application develops.
HTH,
Paul Ibison|||Can I just create a blank database on the test server and restore the
last backup over top of the blank DB? Then just change each IDENITY
column to NOT FOR REPLICATION?
Thanks for all the help,
Dan
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!|||Dan,
if you mean a nosync initialization, this doesn't work, as when your
subscriber is ultimately being used as a test machine, your identity values
will clash. If the identity columns are used for PKs then you will end up
getting PK violations. This is because replication will not increment the
identity value when records are added to the subscriber and DBCC CHECKIDENT
cannot be used to reseed the identity value. I'd consider shipping database
backups for your scenario. You could alternatively avoid these problems by
using merge or transactional with queued updating subscribers, but there
will be a lot of unnecessary work going on behind the scenes on your
production server which you really don't want.
HTH,
Paul Ibison|||So replication will not increment the identity value when records are
added to the subscriber? What value, if any, gets put in the identity
field during replication?
How does the shipping database option work?
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!|||Dan,
true - replication doesn't increment the internal identity value - it stays
as the initial seed. Don't confuse this with the population of the column
during replication which works OK, essentially doing an identity insert.
This setting is really used for updating subscribers - snapshot or
transactional, or merge.
By database shipping I was thinking of doing a backup of the production
database, copying it over to the test server and restoring it there. You'll
need to create your own jobs to implement this, but it is not difficult. In
fact if you do a search for log-shipping scripts you can hack these to do
what you want, which is essentially very similar.
HTH,
Paul Ibison|||Thanks Paul, I will Google log-shipping scripts and see if I can get
that working.
Thanks again for all the help,
Dan
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!sql

Can't Drop table in Replication

Dear friends

I restore one database in two database servers which is running on SQL server 2000.I replicated these two through snapshot relication.Snapshot agent is creating snapshot.But when I am starting to synchronize it's telling can't drop table because that table is in replication. Backup I taken from a replicated database.so it's having rowguid both the servers.Another thing the table which it is telling not able to drop it's having primary key.please tell me what may be the problem.In replication why it is going to drop table it's only what to transport data na

Filson

The database that's restored at the subscriber probably still has replication bits set. You can clean up replication at the subscriber database by calling proc sp_removedbreplication.|||

Greg Y wrote:

The database that's restored at the subscriber probably still has replication bits set. You can clean up replication at the subscriber database by calling proc sp_removedbreplication.

I done like that.But I got a problem .I am using backup from replicated database .This backup only i restored on my publisher & subscriber.When I am subscribing three system stored procedure should be generate na for Del,Ins.Upd operations .This is not happening.I tried to delete this system generated procedures in the restored database .But it's not allowing.Then I applied replication Pubs database which is coming along with SQL Server .Then what I seen I published all Tables(10).But Stored procedure only for 5 tables is generated .in these tables whatever changes i am making it's affecting through Replication,Example publisher table in pubs database is not getting any stored procedure after subscription.So it's not getting any change through replication.Then I am getting error message'procedure Sp_MsIns_Publisher not found'.So please tell me what to do remove all old Stored procedures for replication in restored database and how to create the stored procedures for all the tables published

Thanks in Advance

Filson

|||

You need to restore the database at the subscriber and remove all replication components. When using the wizard to set up the subscription, specify the option that the subscriber has the data. if you're doing it via TSQL, then specify 'nosync' for paramter @.sync_type in sp_addsubscription. After the first sync, you can then run sp_scriptpublicationcustomprocs at the subscriber to create the necessary procs for the distribution agent.

|||

Greg Y wrote:

You need to restore the database at the subscriber and remove all replication components. When using the wizard to set up the subscription, specify the option that the subscriber has the data. if you're doing it via TSQL, then specify 'nosync' for paramter @.sync_type in sp_addsubscription. After the first sync, you can then run sp_scriptpublicationcustomprocs at the subscriber to create the necessary procs for the distribution agent.

Greg thanks for suggestion.Previously itself I tried these ways to remove replication components.But still System stored procedures for table Ins,Del,Upd is not dropping.I want a specific way to drop it out.I think i mentioned this in earlier post.So kindly suggest me a better way for that

Filson

|||Sorry, it's not clear to me what the problem is. Are you trying to drop or create the sp_MSins/upd/del stored procedures? And where - at the publisher or subscriber? I'm not too familiar with SQL 2000, maybe you have to manually delete these stored procedures.|||

Greg Y wrote:

Sorry, it's not clear to me what the problem is. Are you trying to drop or create the sp_MSins/upd/del stored procedures? And where - at the publisher or subscriber? I'm not too familiar with SQL 2000, maybe you have to manually delete these stored procedures.

I am describing my problem below .I taken a backup of replicated database.That I restored on another server.I made that one as a publisher.Another instance I made as a subscriber.I restored same database on this subscriber.Then I tried for transactional replication.While synchronizing I got error on my subscriber 'Sp_MsIns_AgentCode is not found'.This I got while Inserting records to AgentCode Table in publisher.Then I come to know for each table article published in Transactional replication will have three system generated stored procedures 1)insertion 2)updation 3)Deletion.This is not generating when i subscribing to my publisher..Here when I am pulling the subscription I am specifying 'No Shema & data transfer'.So I am not able to transport this System generated Procedures.If I am selecting 'Schema & data Tansfer'.It won't able to initialize in subscriber due to Foreign Key criterias. after completing Subscription through Wizard,Can I trnsfer UPD,INS,DEL Procedure Schema through any Sp_procedure call? Give me some better way

Filson

|||

I mentioned above to run stored procedure sp_scriptpublicationcustomprocs at the subscriber database after applying the snapshot, did you do that? That proc is supposed to generate the missing sp_MSins/upd/del procs that the distribution agent is trying to execute.

Can't drop procedure ... Snapshot won't run... Help!

Hi,
I have a server set up for Merge replication. It is the publisher and the
distributer. Has been working fine.
It has two publications.
I wanted the subscribers to get a new copy of the data next time they
replicate, so I went to the first publication, which is stored procs and
view, went to Status tab of the publication properties, and clicked the run
agent now. When it was done, I right clicked on the publication and selected
reinitialize subscriptions.
Then I went to the publication for tables. Right clicked and selected
Re-initialize subscritions. Then I went to the status tab and selected run
agent now. When it came back with a timestamp, red x's appeared on the
replication monitor on the snap shot agents.
The error message it give is :
Cannot drop the procedure 'dbo.sp_sel_B4AC8FE9123F47EDB952DCE5249B4F84_pal'
because it is being used for replication.
As a last resort, I tried to delete the publication, I was going to re do
it. When I try to delete it
it gives me the same error message as above, but with a different sp_sel_
name.
What should I do, or what did I do. I really need it back to working today.
.. .
Any help,
Thanks,
Steve
Well, here is what I did.
I kept trying to delete the publication that I could not get the snapshot
agent to run on, and kept getting the error above. So I delete the stored
proc publication, it went OK. Then I deleted the table publication, and this
time it let me. Then I went to each of the clients, deleted both
subscriptions, and re-created them. Ran fine then, and am able to do manual
pull replications from the subscriber boxes now.
If any one can help me understand what happened, please do. How did I get
in that mess, and what would have been a good way to get out. I was lucky
that I could get to all subscriber boxes and re-create the subscriptions.
Thanks,
Steve
"SteveInBeloit" wrote:

> Hi,
> I have a server set up for Merge replication. It is the publisher and the
> distributer. Has been working fine.
> It has two publications.
> I wanted the subscribers to get a new copy of the data next time they
> replicate, so I went to the first publication, which is stored procs and
> view, went to Status tab of the publication properties, and clicked the run
> agent now. When it was done, I right clicked on the publication and selected
> reinitialize subscriptions.
> Then I went to the publication for tables. Right clicked and selected
> Re-initialize subscritions. Then I went to the status tab and selected run
> agent now. When it came back with a timestamp, red x's appeared on the
> replication monitor on the snap shot agents.
> The error message it give is :
> Cannot drop the procedure 'dbo.sp_sel_B4AC8FE9123F47EDB952DCE5249B4F84_pal'
> because it is being used for replication.
> As a last resort, I tried to delete the publication, I was going to re do
> it. When I try to delete it
> it gives me the same error message as above, but with a different sp_sel_
> name.
> What should I do, or what did I do. I really need it back to working today.
> . .
> Any help,
> Thanks,
> Steve
>
sql

CANT DROP DATABASE

ok I got a database I cant drop because it says its connected with
replication ... I have checked replication and replication monitor there is
nothing there replicated to the database I want to delete. Where else can I
find replication info so that I know why it wont let me delete the database/Edgar,
Try
sp_removedbreplication 'databasename'
It may be true that the replication is removed properly but may be the
sequence seems to be wrong.Try deleting the subscriptions first, followed by
the publications.
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Edgar Engibarian" <edgar@.bellcpa.com> wrote in message
news:O2Xo51xSDHA.2152@.TK2MSFTNGP12.phx.gbl...
> ok I got a database I cant drop because it says its connected with
> replication ... I have checked replication and replication monitor there
is
> nothing there replicated to the database I want to delete. Where else can
I
> find replication info so that I know why it wont let me delete the
database/
>

Can't drop a table

I have a table that used to be in a merge replication. How ever it is not
being replicated any more, I can't drop it because it thinks it is been
replicated. How can I drop it?
Thanks a lot, Lina
If you don't replicate anything else from your database run the
sp_removedbreplication procedure. It'll remove all traces of replication.
Then you'll be able to drop the table.
If the first option is not available then you need to reset the replication
status bit in sysobjects for your table. Although you need to know that
manual updating of the sysobjects table is not recommend.
Yury
"Lina Manjarres" <LinaManjarres@.discussions.microsoft.com> wrote in message
news:52C9D2D2-61D0-4D7B-9D5A-1950AFA1E8E5@.microsoft.com...
>I have a table that used to be in a merge replication. How ever it is not
> being replicated any more, I can't drop it because it thinks it is been
> replicated. How can I drop it?
> Thanks a lot, Lina
|||issue the following
EXEC sp_configure 'allow',1
go
reconfigure with override
go
use DataBaseName
go
update sysobjects set replinfo = 0 where name = 'TableName'
go
EXEC sp_configure 'allow',0
go
reconfigure with override
go
sp_MSunmarkreplinfo 'TableName'
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
"Lina Manjarres" <LinaManjarres@.discussions.microsoft.com> wrote in message
news:52C9D2D2-61D0-4D7B-9D5A-1950AFA1E8E5@.microsoft.com...
> I have a table that used to be in a merge replication. How ever it is not
> being replicated any more, I can't drop it because it thinks it is been
> replicated. How can I drop it?
> Thanks a lot, Lina

Can't Disable, getting Server Cannot obtain LOCK

Internet merge replication. Need to detach to move to a new box. Deleted
all the publication, now when I try to disable it as the publisher. I tried
to run sp_removedbreplication 'db_name' and get the same message.
Any thoughts?
Thanks
After lots more reading and searching, I found low memory could cause this.
I added another 512 to the box, and it worked.
"SteveInBeloit" wrote:

> Internet merge replication. Need to detach to move to a new box. Deleted
> all the publication, now when I try to disable it as the publisher. I tried
> to run sp_removedbreplication 'db_name' and get the same message.
> Any thoughts?
> Thanks
|||I would have bounced the server and then tried this - this is abnormal.
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
"SteveInBeloit" <SteveInBeloit@.discussions.microsoft.com> wrote in message
news:17E5CBD0-5D2A-444A-97BD-4629B2AE3559@.microsoft.com...[vbcol=seagreen]
> After lots more reading and searching, I found low memory could cause
> this.
> I added another 512 to the box, and it worked.
>
> "SteveInBeloit" wrote:

Can't detach database

1. Trying to detach in EM but get message 'can't detach while db being
replicated'. OK button is disabled.
2. Replication has been disabled vua Enterprise manager and
sp_removedbreplication.
3. I know that at one time a user had tried unsuccessfullt to set up
replication between this db and a SQLServer installation on her workstation.
I don't know the details of that except that she couldn't get it to work.
4. Is there anything I can do in a system table to clean out everything
related to this replication attempt. so I can detach?
Thanks.
Alan
Alan,
try using sp_dboption to reset the database status:
sp_dboption 'dbname', 'published', 'false'
sp_dboption 'dbname', 'merge publish', 'false'
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
Thanks for your reply.
I get following message after
sp_dboption 'Contacts', 'published', 'false'
Server: Msg 208, Level 16, State 1, Procedure sp_dropsubscription, Line 78
Invalid object name 'syssubscriptions'.
Would that be due to fact I tried cleaning out things in EM and
sp_removedbreplication already?
No luck in detaching. EM still says 'Database being replicated - Yes'.
Thanks.
Alan
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:ObTG0HpxEHA.3908@.TK2MSFTNGP12.phx.gbl...
> Alan,
> try using sp_dboption to reset the database status:
> sp_dboption 'dbname', 'published', 'false'
> sp_dboption 'dbname', 'merge publish', 'false'
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Alan,
this shouldn't be related. It looks as though things are
very wrong here. If you don't have any other
publications, I'd consider disabling publishing on this
instance and DTS out the data from the database - perhaps
a copydatabase would also work. Then try dropping the
database.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
Thanks again for your reply.
I will try your suggestions.
Fortunately, it's a test machine. I can even re-intstall SQLServer if I
have to.
If I do re-intall, do you know if there is anything in registry that might
get left over from existing problem?
Needless to say, my client will have to rope in employees who stab around at
things.
Regards,
Alan
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:59a001c4c749$2945b230$a301280a@.phx.gbl...
> Alan,
> this shouldn't be related. It looks as though things are
> very wrong here. If you don't have any other
> publications, I'd consider disabling publishing on this
> instance and DTS out the data from the database - perhaps
> a copydatabase would also work. Then try dropping the
> database.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>

Can't delete Replicated Database

I was just experimenting with something in replication and created a test
database called "ReplicationTest"
This is on a server that is currently a subscriber for other databases, but I
thought I'd also be able to set up a publication from "ReplicationTest" Db.
I tried to create a publication and got the error:
Error 14294 supply either @.Job_id or @.Job_name to identify the job.
What does that mean ?
So I said "to heck with it" (or something like that) and decided to just
delete ReplicationTest database since I got stuck & had real work to do and
no publication had been created. But now it won't let me delete
ReplicationTest database because it says it's being used for replication ....
.... So, what should I try ?
Before you can delete it.
Right click on the replication folder on your enterprise manager treeview,
click on the configure publishers, subscribers, distributions on the context
menu that pops up, go to the publication database tabs and make sure you
untick the boxes next to Replication Test.
Once that is done you can delete the db.
"Gabriel D via droptable.com" wrote:

> I was just experimenting with something in replication and created a test
> database called "ReplicationTest"
> This is on a server that is currently a subscriber for other databases, but I
> thought I'd also be able to set up a publication from "ReplicationTest" Db.
>
> I tried to create a publication and got the error:
> Error 14294 supply either @.Job_id or @.Job_name to identify the job.
> What does that mean ?
>
> So I said "to heck with it" (or something like that) and decided to just
> delete ReplicationTest database since I got stuck & had real work to do and
> no publication had been created. But now it won't let me delete
> ReplicationTest database because it says it's being used for replication ....
> .... So, what should I try ?
>

Can't delete database that has been part of replication

I had a publication (merge) setup for a database. Deleted the publication and tried to delete the database. Sql server says it can't be deleted because it's has replication setup. Bug?

Found the answer. I had to run a stored procedure to get all replication objects removed from my db.

sp_removedbreplication @.dbname = 'dbname', @.type = 'merge'

/Magnus

can't delete a row?

I have transaction replication setup between publisher server and subscriber
server.
I tried to delete a row from publisher's table,
but it looks like it's running forever.
delete from sym_type where type_id=1
And I don't know what to troubleshoot. but if I do an insert, then it's
working. i also see the row being replicated to subscriber when I do an
insert.
insert into sym_type values (-3, 'test', null)
scraching my head hard. replication monitor show no errors.
kevin
Kevin,
sounds like a contention issue - you could use sp_who2 and look for blocking
(use dbcc inputbuffer to see the cause of the block).
If not that, then try running select * from sym_type (nolock) where
type_id=1 to see how many rows there are - it might be a big delete?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||no... there is only 10 rows in the table.
and I inserted a row type_id = 1 to test replication, which it works
fine.
So I tried to delete the same row to rollback the change, but then it failed
to delete the row.
I think it maybe has to do with replication.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OgpkHnn1FHA.3568@.TK2MSFTNGP15.phx.gbl...
> Kevin,
> sounds like a contention issue - you could use sp_who2 and look for
> blocking (use dbcc inputbuffer to see the cause of the block).
> If not that, then try running select * from sym_type (nolock) where
> type_id=1 to see how many rows there are - it might be a big delete?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Kevin - what about the blocking results from sp_who2 - see any?
Paul Ibison
|||yes, sp_who2 shows BlockBy - spid 60.
Command delete.
interesting huh? why can't I delete it?
I even did dbcc checktable, nothing wrong.
so i go to spid 60
it's command "delete from sym_type where type_id =1"
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OGrJB6n1FHA.2212@.TK2MSFTNGP15.phx.gbl...
> Kevin - what about the blocking results from sp_who2 - see any?
> Paul Ibison
>
|||Kevin,
strange - what's the spid/command command that is blocked by spid 60 - are
you saying it is another spid doing exactly the same command?
Paul
|||no.. there is [BlockBy] column in sp_who2 recordset,
there is value 60
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OuuDpro1FHA.2072@.TK2MSFTNGP14.phx.gbl...
> Kevin,
> strange - what's the spid/command command that is blocked by spid 60 - are
> you saying it is another spid doing exactly the same command?
> Paul
>
|||I'm still a little confused here You have 2 processes, and one blocks the
other. If I understand properly the one which is causing the block is the
delete, and the other one is blocked by the delete. If so, I agree that this
is strange and is the opposite of what I'd have expected. I suppose you
don't have some cunning triggers at work, or cascade deletes in action? -
worth checking out. DBCC OPENTRAN might also reveal something more but I
doubt it (still, worth a try). In your case I'd kill the delete spid, then
confirm that there's no blocking at all, and no open transactions. After
that, do a corresponding select, or perhaps just run the delete again if
it's ok.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||oh... I think I do have cascade delete in action.
my primary table is sym_type, foreign key table is portfolio_position ,
symbol_id is the key on both tables.
- it's enforce relationship for replication.
- it's enforce relationship for INSERTS and DELETES
it's cascading update related fields.
it's cascading delete related fields.
That must be it. when I do INSERT statement, there is no problem.
but when I do update or delete statement, query runs forever.
The next question is, why is this happen? I'm inserting a new symbol_id
which doesn't exist in foreign table,
why would this become a problem?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eLNLDCN2FHA.2444@.TK2MSFTNGP10.phx.gbl...
> I'm still a little confused here You have 2 processes, and one blocks
> the other. If I understand properly the one which is causing the block is
> the delete, and the other one is blocked by the delete. If so, I agree
> that this is strange and is the opposite of what I'd have expected. I
> suppose you don't have some cunning triggers at work, or cascade deletes
> in action? - worth checking out. DBCC OPENTRAN might also reveal something
> more but I doubt it (still, worth a try). In your case I'd kill the delete
> spid, then confirm that there's no blocking at all, and no open
> transactions. After that, do a corresponding select, or perhaps just run
> the delete again if it's ok.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Kevin,
is there a self join, or a circular-relationship between the tables perhaps?
Do you see the same behaviour in the absence of replication (if this is
possible to test). Actually you could DTS the tables to a test server and
test there.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Can't create a new publication

Hi,
I'm trying to create a publication in a SQL Server 2005 server. I
right clicked on Replication tab in management studio and selected
'New Publication'. The management studio waits for a long time and
then nothing happens. It does not start any 'New Publication' wizard
nor does it report any error. Could you please let me know if there
are any server settings that need to be enabled before configuring
Publications.
Thanks and Regards,
Thyagu.D
Does anything appear in the sql server log, or the windows log? What happens
if you run sp_adddistributor and sp_adddistributiondb directly?
Cheers,
Paul Ibison
|||You probably have some locking going on from a previous replication process.
Issue calls to sp_who2 or select * from sysprocesses where blocked<>0 order
by waittime desc to see if you can see these processes and kill them.
You might also want to try to create they publication by using replication
stored procedures.
Try sp_replicationdboption 'MyDatabase', 'published', true for a start.
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
"Thyagu" <tdelli@.gmail.com> wrote in message
news:1184566869.270526.315190@.e16g2000pri.googlegr oups.com...
> Hi,
> I'm trying to create a publication in a SQL Server 2005 server. I
> right clicked on Replication tab in management studio and selected
> 'New Publication'. The management studio waits for a long time and
> then nothing happens. It does not start any 'New Publication' wizard
> nor does it report any error. Could you please let me know if there
> are any server settings that need to be enabled before configuring
> Publications.
> Thanks and Regards,
> Thyagu.D
>
|||On Jul 16, 2:14 pm, Paul Ibison <Paul.Ibi...@.Pygmalion.Com> wrote:
> Does anything appear in the sql server log, or the windows log? What happens
> if you run sp_adddistributor and sp_adddistributiondb directly?
> Cheers,
> Paul Ibison
This worked. Thanks!
Regards,
Thyagu.D

Can't connect to Distributor

Hello,
I am trying to set up transactional replication between a server (that is both the distributor and publisher) and a client (msde). The server (sql2000) is configured for mixed mode authentication - windows & sql. Our client subscribers will be using a
nonymous pull subscriptions...so they are not part of our domain.
We have a vb.net application and I am using the code from http://support.microsoft.com/default...;en-us;Q319648 as a starting point. The only changes I made to the sample code was to change the SECURITY_TYPE.NT_AUTHENTICATION to DB_AUTHENTICATI
ON and add my login and password.
I cannot get past the "Process could not connect to Distributor "SERVER" error.
As a quick test...I ran a trace with SQL Profiler...and there isn't even a login attempt. I can ping the server from my client/subscriber.
Is there something else I am missing? I searched through some of the archives and none of the suggestions have worked for me so far.
Thanks
DD,
have you set up an alias in the client network utility on the client
computer? This article may help any other settings:
http://support.microsoft.com/default...22&Product=sql
Regards,
Paul Ibison
|||I did not set up an alias on the client. I didn't think that this step was necessary because we are replicating(well, trying) over the internet via ftp. We will have many clients and I didn't know if we will have to set up this alias.
I read through that article and went through the pieces that had to do with replicating over the internet with ftp. I have everything set according to those documents. One thing..HOWEVER...I actually cannot ping the server. The network admin turned off
or disabled ping on that server. I can go to a command prompt and type "ftp myserver.mydomain.com" and get to the ftp prompt. My question now is...by disabling the ability for machines outside our network and firewall to ping the server...does this me
an that replication won't work even though ftp has been enabled?
"Paul Ibison" wrote:

> DD,
> have you set up an alias in the client network utility on the client
> computer? This article may help any other settings:
> http://support.microsoft.com/default...22&Product=sql
> Regards,
> Paul Ibison
>
>
|||if you can't ping try tracert. or ftp.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"DD" <DD@.discussions.microsoft.com> wrote in message
news:0FA8DD44-B9D6-48E9-8AA9-0478322F6B6B@.microsoft.com...
> I did not set up an alias on the client. I didn't think that this step
was necessary because we are replicating(well, trying) over the internet via
ftp. We will have many clients and I didn't know if we will have to set up
this alias.
> I read through that article and went through the pieces that had to do
with replicating over the internet with ftp. I have everything set
according to those documents. One thing..HOWEVER...I actually cannot ping
the server. The network admin turned off or disabled ping on that server.
I can go to a command prompt and type "ftp myserver.mydomain.com" and get to
the ftp prompt. My question now is...by disabling the ability for machines
outside our network and firewall to ping the server...does this mean that
replication won't work even though ftp has been enabled?[vbcol=seagreen]
> "Paul Ibison" wrote:
http://support.microsoft.com/default...22&Product=sql[vbcol=seagreen]
|||Well...I can go to a command prompt and connect to the server with:
ftp MyServer.Domain.com
If this is enough to indicate that a pull subscription should be able to connect to the server...then I am not sure what else to look at to resolve this issue.
I suppose I could remove the publication, disable replication and start over again.
"Hilary Cotter" wrote:

> if you can't ping try tracert. or ftp.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "DD" <DD@.discussions.microsoft.com> wrote in message
> news:0FA8DD44-B9D6-48E9-8AA9-0478322F6B6B@.microsoft.com...
> was necessary because we are replicating(well, trying) over the internet via
> ftp. We will have many clients and I didn't know if we will have to set up
> this alias.
> with replicating over the internet with ftp. I have everything set
> according to those documents. One thing..HOWEVER...I actually cannot ping
> the server. The network admin turned off or disabled ping on that server.
> I can go to a command prompt and type "ftp myserver.mydomain.com" and get to
> the ftp prompt. My question now is...by disabling the ability for machines
> outside our network and firewall to ping the server...does this mean that
> replication won't work even though ftp has been enabled?
> http://support.microsoft.com/default...22&Product=sql
>
>
|||no, no, no, go to your publisher, and in
c:\%windir%\system32\logfiles\MSFTPSVC look for the log file with today's
date stamp and locate entries that correspond to your attempt to connect.
There will be some numbers associated with the attempt, check this link out
to see what the error codes mean
http://www.4d.com/docs/CMU/CMU88906.HTM
Look in the FTP RFC Values section
This will tell you exactly why you are failing.
"DD" <DD@.discussions.microsoft.com> wrote in message
news:EB413FD6-D8AD-4128-80EA-CF7C95C34602@.microsoft.com...
> Well...I can go to a command prompt and connect to the server with:
> ftp MyServer.Domain.com
> If this is enough to indicate that a pull subscription should be able to
connect to the server...then I am not sure what else to look at to resolve
this issue.
> I suppose I could remove the publication, disable replication and start
over again.[vbcol=seagreen]
> "Hilary Cotter" wrote:
step[vbcol=seagreen]
via[vbcol=seagreen]
up[vbcol=seagreen]
ping[vbcol=seagreen]
server.[vbcol=seagreen]
get to[vbcol=seagreen]
machines[vbcol=seagreen]
that[vbcol=seagreen]
http://support.microsoft.com/default...22&Product=sql[vbcol=seagreen]
|||Thanks for the correction. I am still trying to figure this one out...but as a quick test, I changed the settings for the user login and password from the one I had been using (a sql long with limited access) to the "sa" account...and it works great. S
o...there must be some sort of permission problem I need to wrangle with. I have noticed since that server was updated from Win2K server to Win2003 Server...the security access/permissions have been tightened down and many things that use to work...no lo
nger.
"Hilary Cotter" wrote:

> no, no, no, go to your publisher, and in
> c:\%windir%\system32\logfiles\MSFTPSVC look for the log file with today's
> date stamp and locate entries that correspond to your attempt to connect.
> There will be some numbers associated with the attempt, check this link out
> to see what the error codes mean
> http://www.4d.com/docs/CMU/CMU88906.HTM
> Look in the FTP RFC Values section
> This will tell you exactly why you are failing.
> "DD" <DD@.discussions.microsoft.com> wrote in message
> news:EB413FD6-D8AD-4128-80EA-CF7C95C34602@.microsoft.com...
> connect to the server...then I am not sure what else to look at to resolve
> this issue.
> over again.
> step
> via
> up
> ping
> server.
> get to
> machines
> that
> http://support.microsoft.com/default...22&Product=sql
>
>
|||DD,
I am having the same problem here. It's stuck in the Snapshot Agent's connecting attempt to the distributor. It's not even related to Subscription. It just give up during initializing snapshot.
The distributor_admin and sa password are set to the same and mapped in remote_server login.
The only thing I haven't tried is restart the MSSQL and SQLAgent because all my clients are using the databases.
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
|||You shouldn't touch the distributor admin's password. To fix things you
really should disable publishing and reenable it.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message
news:Or5756wiEHA.2448@.TK2MSFTNGP12.phx.gbl...
> DD,
> I am having the same problem here. It's stuck in the Snapshot Agent's
connecting attempt to the distributor. It's not even related to
Subscription. It just give up during initializing snapshot.
> The distributor_admin and sa password are set to the same and mapped in
remote_server login.
> The only thing I haven't tried is restart the MSSQL and SQLAgent because
all my clients are using the databases.
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.