Add new primary key column for replicated table with pull subscrip

I need to add a new column to a replicated table. But in my case a new column should be a part of a primary key. So, I can't use sp_repladdcolumn procedure since we need to completely repopulate the whole table and there is no default value for a new colu
mn. In addition to that this column should be first in the table's columns list, not the last one what happens when you usually alter the table adding a new column.
More there, I have a pull subscription and it looks like there is no way to drop subscription for a specified article only, like it is possible for a push subscription. I would appreciate any help pointing to an easiest way to add a new primary key column
for a replicated table with pull subscription.
TIA
Libra,
ordinarily you should be able to use sp_droparticle, sp_addarticle and
sp_refreshsubscriptions however to add the new column in a specific position
you'll need to drop the published article then recreate it and resubscribe.
You might use a nosync subscription to make things easier if the snapshot is
particularly large, but this will have restrictions if you later want to add
an article or column, so I'd recommend initializing with the complete
snapshot as per usual.
HTH,
Paul Ibison
(BTW no doubt you have seen other threads on this but placing columns in a
particular order is not generally considered 'good form' as TSQL should
ideally use explicit column names rather than rely on position.)

Add article to Merge Repl. Publication

I am running merge replication (SQL 2000 with SP2) with an anonymous pull subscription. The application vendor has come out with update that requires adding a table to a database. The vendor has created scripts that will add the table, as well as some stored procedures. If I apply the scripts to both servers and add the table as a new article to the publication, am I going to have to apply a snapshot of the entire database (which is very large)?

Your help is greatly appreciated.

GaryHi, don't add the table to both sides. Just add the table at the publisher and include it in the publication using the ARTICLES tab of Replication-Properties in EM. Start the Snapshot-Agent. The merge agent would apply only the new table to the subscriber. It's for the subscribers running SQL2000.|||Thanks for the prompt reply. The scripts will also modify some stored procedures - is that handled by the snapshot, or are the changes just replicated?|||Hi, the merge-agent applies the new tables to the subscriber.
About stored-procedures i am not sure, can u modify the stored-procedure if it's being published? Published tables can't be modified unless we use sp_repladdcolumn/sp_repldropcolumn to add or drop columns within the published tables. See if u can modify a published stored procedure at the publisher.

Howdy!

AD

Is there anyway to pull data out of Active Directory and into say a SQL
table.

Like a user list...

Any examples..."AHartman" <Hoosbruin@.Kconline.com> wrote in message
news:Rs6dnWmnsYCnVfDcRVn-qQ@.kconline.com...
> Is there anyway to pull data out of Active Directory and into say a SQL
> table.
> Like a user list...
>
> Any examples...

There's an OLE DB provider for Active Directory, so you should be able to
create a linked server:

http://groups.google.com/groups?hl=...%40TK2MSFTNGP12

Simon

ActveX Pull error

I have a publication created using SQLDMO on the server.
.PublicationAttributes = SQLDMO_PUBATTRIB_TYPE.SQLDMOPubAttrib_AllowPull
+ _
SQLDMO_PUBATTRIB_TYPE.SQLDMOPubAttrib_ImmediateSyn c + _
SQLDMO_PUBATTRIB_TYPE.SQLDMOPubAttrib_IndependentA gent + _
SQLDMO_PUBATTRIB_TYPE.SQLDMOPubAttrib_AllowAnonymo us
I am trying to get a Pull to work from a laptop (in test mode connected
to the main SQL machine via ethernet, but in the end will be connected
via the internet).
The Pull is done via ActiveX:
With myMergeObj
.Distributor = "IIS-SQL"
.DistributorLogin = login
.DistributorPassword = password
.DistributorSecurityMode = SECURITY_TYPE.DB_AUTHENTICATION
.Publisher = inxRS.Globals.gServer
.PublisherDatabase = "SAMPLE"
.Publication = "SAMPLE-RS"
.PublisherLogin = login
.PublisherPassword = password
.PublisherSecurityMode = SECURITY_TYPE.DB_AUTHENTICATION
.Subscriber = Trim(Environment.MachineName)
.SubscriberDatabase = "SAMPLE"
.SubscriberSecurityMode = SECURITY_TYPE.DB_AUTHENTICATION
.SubscriptionType = SUBSCRIPTION_TYPE.PULL
.SubscriberLogin = login
.SubscriberPassword = password
.SubscriptionName = "SAMPLE-RS"
.HostName = Trim(inxRS.Globals.gLogin)
.ExchangeType = EXCHANGE_TYPE.BIDIRECTIONAL
End With
myMergeObj.Initialize()
myMergeObj.Run()
myMergeObj.Terminate()
This errors with:
Server 'IIS-LAPTOP' is not registered at server 'IIS-SQL'.
I thought for PULL all I had to do was create the subscription for
anonymous access. What am I missing?
Thanks.
Darin
*** Sent via Developersdex http://www.codecomments.com ***
You will need to use the PublisherNetwork and DistributorNetwork and set
them to TCPIP_SOCKETS (a value of 1), and set the PublisherAddress and
DistributorAddress to the IP address of the Publisher or its FQDN (i.e.
Publisher.Microsoft.com).
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
"Darin" <darin_nospam@.nospamever> wrote in message
news:OApUxNvgFHA.3220@.TK2MSFTNGP10.phx.gbl...
> I have a publication created using SQLDMO on the server.
> PublicationAttributes = SQLDMO_PUBATTRIB_TYPE.SQLDMOPubAttrib_AllowPull
> + _
> SQLDMO_PUBATTRIB_TYPE.SQLDMOPubAttrib_ImmediateSyn c + _
> SQLDMO_PUBATTRIB_TYPE.SQLDMOPubAttrib_IndependentA gent + _
> SQLDMO_PUBATTRIB_TYPE.SQLDMOPubAttrib_AllowAnonymo us
> I am trying to get a Pull to work from a laptop (in test mode connected
> to the main SQL machine via ethernet, but in the end will be connected
> via the internet).
> The Pull is done via ActiveX:
> With myMergeObj
> .Distributor = "IIS-SQL"
> .DistributorLogin = login
> .DistributorPassword = password
> .DistributorSecurityMode = SECURITY_TYPE.DB_AUTHENTICATION
> .Publisher = inxRS.Globals.gServer
> .PublisherDatabase = "SAMPLE"
> .Publication = "SAMPLE-RS"
> .PublisherLogin = login
> .PublisherPassword = password
> .PublisherSecurityMode = SECURITY_TYPE.DB_AUTHENTICATION
> .Subscriber = Trim(Environment.MachineName)
> .SubscriberDatabase = "SAMPLE"
> .SubscriberSecurityMode = SECURITY_TYPE.DB_AUTHENTICATION
> .SubscriptionType = SUBSCRIPTION_TYPE.PULL
> .SubscriberLogin = login
> .SubscriberPassword = password
> .SubscriptionName = "SAMPLE-RS"
> .HostName = Trim(inxRS.Globals.gLogin)
> .ExchangeType = EXCHANGE_TYPE.BIDIRECTIONAL
> End With
> myMergeObj.Initialize()
> myMergeObj.Run()
> myMergeObj.Terminate()
>
> This errors with:
> Server 'IIS-LAPTOP' is not registered at server 'IIS-SQL'.
> I thought for PULL all I had to do was create the subscription for
> anonymous access. What am I missing?
> Thanks.
> Darin
> *** Sent via Developersdex http://www.codecomments.com ***
|||I got further, thanks. But, no I get the error:
The schema script
'c:\inware\data\unc\IIS-SQL_inware_inware-RS\20050707140259\UTCompany_1.
sch' could not be propagated to the subscriber.
The process could not read file 'same file as above' due to OS error 3.
The system cannot find the path specified.
Darin
*** Sent via Developersdex http://www.codecomments.com ***
|||Darin,
the problem would appear to be with the location of the working folder -
it's using the c drive of the distributor. If you change it to use a file
share, reinitialize, run the snapshot agent then synchronize it should be
fine.
HTH
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)