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 subscription

Either through EM or using sp_dropsubscription. Whole system locks up. I've
even tried to drop it on a per article basis. First few table/articles went
fine, but when went to drop an article for a very large table, whole system
locked up again.
--Thanks,
Kristy
try sp_dropsubscription from QA. You might want to stop your distribution,
merge, log reader agents to be able to do this. Then start them up one by
one.
How many subscribers do you have? are they push or pull? Are you running
them continuously or staggering them. If you have a large number >50 (or
perhaps even >20) use pull, and stagger your schedules, perhaps every 7 or
11 minutes.
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
"Kristy" <pleasereplyby@.posting.com> wrote in message
news:uAVjjbIMFHA.3832@.TK2MSFTNGP12.phx.gbl...
> Either through EM or using sp_dropsubscription. Whole system locks up.
I've
> even tried to drop it on a per article basis. First few table/articles
went
> fine, but when went to drop an article for a very large table, whole
system
> locked up again.
> --Thanks,
> Kristy
>
|||I have run the sp_dropsubscriptions from QA and it locks up. I have also run
it from QA and have been trying to select on a pure article basis. The first
15 articles went fine, but the 16th locked everything up again. This
particular table is massive, so I didn't know if this had anything to do
with it.
We have 1 publisher and 1 subscriber. distributor is currently on publisher.
THe subscriber is really just a hot backup for the publisher since we don't
have clustering. If something were to happen to pub and we couldn't bring
back up then we would switch to sub.
It is currently a push subscription, but we will be using the pull with a
new subscription. the immediate_sync is 1 (which I think means immediate)
and sync_method = 3 (which I have no idea what that means.) The agents have
not been running for 2 days as we are dropping this subscription and
starting over with a remote distributor and pull subscription. I have even
backed up the distribution database, truncated the ms_repltransactions and
ms_replcommands tables and then shrink the distribution database files.
Previously the dist db had grown to about 60 GB with the combined file size.
Now its about 4 MBs.
I have searched all over online and through the repl book and can't find
anything. I don't know what else to do!!!!
Many thanks for your help,
Kristy
we are using tran repl and I have stopped the agents 2 days ago.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%232YjarIMFHA.3080@.TK2MSFTNGP10.phx.gbl...
> try sp_dropsubscription from QA. You might want to stop your distribution,
> merge, log reader agents to be able to do this. Then start them up one by
> one.
> How many subscribers do you have? are they push or pull? Are you running
> them continuously or staggering them. If you have a large number >50 (or
> perhaps even >20) use pull, and stagger your schedules, perhaps every 7 or
> 11 minutes.
> --
> 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
> "Kristy" <pleasereplyby@.posting.com> wrote in message
> news:uAVjjbIMFHA.3832@.TK2MSFTNGP12.phx.gbl...
> I've
> went
> system
>
|||I figured it out. The previous DBA had a job that put an UPDLOCK, HOLDLOCK
on the table I was trying to drop from replication. I can probably do a drop
all now instead of a per article basis.
--K
"Kristy" <pleasereplyby@.posting.com> wrote in message
news:%23a98n9IMFHA.3988@.tk2msftngp13.phx.gbl...
> I have run the sp_dropsubscriptions from QA and it locks up. I have also
run
> it from QA and have been trying to select on a pure article basis. The
first
> 15 articles went fine, but the 16th locked everything up again. This
> particular table is massive, so I didn't know if this had anything to do
> with it.
> We have 1 publisher and 1 subscriber. distributor is currently on
publisher.
> THe subscriber is really just a hot backup for the publisher since we
don't
> have clustering. If something were to happen to pub and we couldn't bring
> back up then we would switch to sub.
> It is currently a push subscription, but we will be using the pull with a
> new subscription. the immediate_sync is 1 (which I think means immediate)
> and sync_method = 3 (which I have no idea what that means.) The agents
have
> not been running for 2 days as we are dropping this subscription and
> starting over with a remote distributor and pull subscription. I have even
> backed up the distribution database, truncated the ms_repltransactions and
> ms_replcommands tables and then shrink the distribution database files.
> Previously the dist db had grown to about 60 GB with the combined file
size.[vbcol=seagreen]
> Now its about 4 MBs.
> I have searched all over online and through the repl book and can't find
> anything. I don't know what else to do!!!!
> Many thanks for your help,
> Kristy
> we are using tran repl and I have stopped the agents 2 days ago.
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%232YjarIMFHA.3080@.TK2MSFTNGP10.phx.gbl...
distribution,[vbcol=seagreen]
by[vbcol=seagreen]
or
>

Can't drop SQL Mobile subscription at Publisher

I have a program running on Pocket PCs that synchronizes with a desktop SQL database using SQL Mobile. My problem is that I can't delete old subscriptions from the publication; now the publication has 25 subscriptions and won't accept any more (limit of SQL Workgroup edition). I can delete non-SQL Mobile subcriptions just fine.

Running sp_replmonitorhelpsubscription provides this information:

subscriber: LVCONTACTSMERGE - 5FA305234F0C
subscriber_db: Program Files\PocketLVContacts\LVContacts.sdf
publisher_db: LVContacts
publication: LVContactsMerge

So I set up this bit of code:

USE LVContacts
EXEC sp_dropmergesubscription
@.publication = 'LVContactsMerge',
@.subscriber = 'LVCONTACTSMERGE - 5FA305234F0C',
@.subscriber_db = 'Program Files\PocketLVContacts\LVContacts.sdf',
@.subscription_type='all';
GO

No errors are reported, but the subscription doesn't get deleted.

I've tried shutting down SQL Server Agent (mentioned in another thread); makes no difference.

Any ideas?

--Evan

If you query sysmergesubscriptions, do you get the same values returned as|||

If you query sysmergesubscriptions, do you get the same values returned as sp_replmonitorhelpsubscription? I'm just wondering if the subscriber_db name returned is correct or not.

|||

Yes, both sysmergesubscriptions and sp_replmonitorhelpsubscription give the same subscriber_db name. Note that the name returned, i.e. 'Program Files\PocketLVContacts\LVContactsData.sdf', is the path on the mobile device.

I wonder if it is safe to manually delete a row from sysmergesubscriptions. It seems to work, but no Distributor clean-up is done.

|||

I've found where I was going wrong. The code I cited in my original post DOES work, but to see the changes you must use sysmergesubscriptions, NOT sp_replmonitorhelpsubscription. The latter apparently reflects the distribution database, which is not cleaned up when you use sp_dropmergesubscription.

When you use SQL Server Management Studio to delete a subscription on a mobile device running SQL Mobile, the subscription on the Publisher is NOT also deleted; you must do it manually (or wait until the subscription expires). But it is difficult to determine which subscription on the Publisher corresponds to the one you want to delete on the mobile Subscriber. What I did was first query the mobile Subscriber system table __sysMergeSubscriptions, note the SubscriptionID field, then query the sysmergesubscriptions on the Publisher database, and find the row with a SubID that matches. Then you can run sp_dropmergesubscription using the subscriber_server field as @.subscriber and the db_name field as @.subscriber_db.

Can't delete subscription

Hello,

I delete the publication, but the subscription still exists.

If I will delete the abonement I get following error 21776(Can't find Name xxx in the list of the TransPublications).

I tryed to craete the publication for the failed subscription again, but then I get the error, that a subscription for this publication still exists.

What can I do? How can I delete the subscription ?

Many thx for help!

Which SQL version are you using? Do you use scripts or UI to drop publication and subscription? Is subscription pull and located on the remote server? Could you please provide detailed repro step by step?

Thanks

Yunjing

|||

hello,

we use sql server 2000 and droped the replication via tsql

subscription is push and located on the remote server. I think I must delete all informations manuell in the server protocols, but how?

thx

Cant Create Subscription...

I've got a strange problem that I can't quite figure out. Whenever I go to setup a new subscription for a report (any report) I can't select a schedule or create the subscription.
I get to the Subscription page, but pressing any of the buttons: "Ok", "Cancel", "Select Schedule" does nothing.
Anyone else ever have this issue?
Any feedback would be greatly appreciated.

Hi
can you able to see SubscriptionProperties.aspx Page after clicking on New Subscription.
Thanx
Rohit|||

Yeah, pressing "New Subscription" takes me to the form (SubscriptionProperties.aspx), but not matter what button I press on the form - Ok, Cancel, Select Schedule... nothing happens.
It's as if you were creating an .aspx form and set "AutoPostBack" on a control to false. Clicking on any of the buttons does nothing. Therefore, I'm not able to create a new subscription or alter
any of the subscriptions schedules that I currently have.
I've restarted the reportserver service and I've restarted the application pool. Neither helps.

|||

Just wanted to follow up on this in case that someone else runs accross this. This was a framework issue with SP1. Not really related to Reporting Services, but possibly relevant nonetheless.

http://channel9.msdn.com/showpost.aspx?postid=21650

Can''t Creat Subscriptions

We're new to SQL Reporting Services and have a new SQL 2005 server.

I'm having a problem activating scubscriptions.

- "New Subscription" is greyed out in Server Management Studio.

- Subscribing via the browser, even as the Content Manager, allows you to fill in the form for the subscription but then reports the following error;

"The current action cannot be completed because the user data source credentials that are required to execute this report are not stored in the report server database. (rsInvalidDataSourceCredentialSetting)"

Any suggestions?

Found the problem.

When configuring the Data Source the "Impersonate the authenticated user after a connection has been made to the data source" button should be off.

Thanks for the tip!|||Be careful! This might sound very fool but sometimes the problem is that the paramethers are bad passed to the report, or the report's paramethers has non-english characters, like '?'.

I've passed 'a?o' (year, in spanish) as a paramether, and nothing seems to work'till I changed that.

I hope this help all of you.

Saludos desde el sur del mundo.|||What screen are you configuring the data source? I can't find the button you are referring to.

Can't Creat Subscriptions

We're new to SQL Reporting Services and have a new SQL 2005 server.

I'm having a problem activating scubscriptions.

- "New Subscription" is greyed out in Server Management Studio.

- Subscribing via the browser, even as the Content Manager, allows you to fill in the form for the subscription but then reports the following error;

"The current action cannot be completed because the user data source credentials that are required to execute this report are not stored in the report server database. (rsInvalidDataSourceCredentialSetting)"

Any suggestions?

Found the problem.

When configuring the Data Source the "Impersonate the authenticated user after a connection has been made to the data source" button should be off.

Thanks for the tip!|||Be careful! This might sound very fool but sometimes the problem is that the paramethers are bad passed to the report, or the report's paramethers has non-english characters, like '?'.

I've passed 'a?o' (year, in spanish) as a paramether, and nothing seems to work'till I changed that.

I hope this help all of you.

Saludos desde el sur del mundo.|||

What screen are you configuring the data source? I can't find the button you are referring to.

Can't Creat Subscriptions

We're new to SQL Reporting Services and have a new SQL 2005 server.

I'm having a problem activating scubscriptions.

- "New Subscription" is greyed out in Server Management Studio.

- Subscribing via the browser, even as the Content Manager, allows you to fill in the form for the subscription but then reports the following error;

"The current action cannot be completed because the user data source credentials that are required to execute this report are not stored in the report server database. (rsInvalidDataSourceCredentialSetting)"

Any suggestions?

Found the problem.

When configuring the Data Source the "Impersonate the authenticated user after a connection has been made to the data source" button should be off.

Thanks for the tip!|||Be careful! This might sound very fool but sometimes the problem is that the paramethers are bad passed to the report, or the report's paramethers has non-english characters, like '?'.

I've passed 'a?o' (year, in spanish) as a paramether, and nothing seems to work'till I changed that.

I hope this help all of you.

Saludos desde el sur del mundo.|||

What screen are you configuring the data source? I can't find the button you are referring to.

Can't Creat Subscriptions

We're new to SQL Reporting Services and have a new SQL 2005 server.

I'm having a problem activating scubscriptions.

- "New Subscription" is greyed out in Server Management Studio.

- Subscribing via the browser, even as the Content Manager, allows you to fill in the form for the subscription but then reports the following error;

"The current action cannot be completed because the user data source credentials that are required to execute this report are not stored in the report server database. (rsInvalidDataSourceCredentialSetting)"

Any suggestions?

Found the problem.

When configuring the Data Source the "Impersonate the authenticated user after a connection has been made to the data source" button should be off.

Thanks for the tip!|||Be careful! This might sound very fool but sometimes the problem is that the paramethers are bad passed to the report, or the report's paramethers has non-english characters, like '?'.

I've passed 'a?o' (year, in spanish) as a paramether, and nothing seems to work'till I changed that.

I hope this help all of you.

Saludos desde el sur del mundo.|||

What screen are you configuring the data source? I can't find the button you are referring to.