added article to current transactional repl. How to replicate additional article?

Hi, I already have transactional replication setup between two servers
(production server to reporting server).
I have created a new table witha primary key and have added this table as
an article to the publication.
How can i get my setup to take an initial snapshot of the table and apply it
to the reporting server?
Any help most appreciated!
thanks
John
John,
you will need to run the snapshot agent then synchronize. The whole process
from start to finish is:
exec sp_addarticle @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.source_table = 'tEmployees'
exec sp_addsubscription @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.subscriber = 'RSCOMPUTER'
, @.destination_db = 'testrep'
Start snapshot agent, start distribuition agent.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks Paul,
Are you saying that I have to take a snapshot of the whole database all over
again?
I thought I could take a snapshot of just the article that has been newely
added to the publication?
In actual fact, the 'Last Action' column on my agent (subsicriber' is saying
"The initial snapshot for article 'myTable' is not yet available"
Many thanks!
John
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OQ2XvSVEGHA.4000@.TK2MSFTNGP10.phx.gbl...
> John,
> you will need to run the snapshot agent then synchronize. The whole
> process from start to finish is:
> exec sp_addarticle @.publication = 'tTestFNames'
> , @.article = 'tEmployees'
> , @.source_table = 'tEmployees'
> exec sp_addsubscription @.publication = 'tTestFNames'
> , @.article = 'tEmployees'
> , @.subscriber = 'RSCOMPUTER'
> , @.destination_db = 'testrep'
> Start snapshot agent, start distribuition agent.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||John,
if you add a new table and subscription to that table, running the snapshot
agent won't create a complete snapshot - just the new article.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Add column with fixed number of values (text) to the select statement

Hello,

I have such a problem. Need to add additional column to my query. The column should consist of set of fixed number (same as number of query rows) values (text). At start thought it's simple but now Im lost. Is there any chance to do it. Apreciate any help. I need to tell that I have only access to select on this database so no use of operation on tables.

I found on the net something to start with

Adding Columns in SQL SELECT statements by Andy Kramek http://weblogs.foxite.com/andykramek/archive/2005/09/18/921.aspx

SELECT *, CAST( 0 AS INT) AS newint FROM sample

but it just add a column with value 0 to the set of results.

|||

What is the version you are using... (2000/2005) Give some sample data & required output..

|||

I'm using ver. 2005. The select is very simple concerns the tables with historical rates for curves.

So there are four columns:

1) Date

2) Name of the curve

3) DF

4) Implied rate

there are 17 rows of data

and I need to add column with fixed tenors

O/N

T/N

S/N

1W

2W

1M

2M

3M

4M

5M

6M

7M

8M

9M

10M

11M

1Y

|||

Ok.. do you want the result as bellow,

1) Date

2) Name of the curve

3) DF

4) Implied rate

5)O/N

6)T/N

7)S/N

8)1W

9)2W

10)1M

..

..

17)IY

if yes what data you want to display on this 17 columns

|||

Your questions not clear properly. But I think this will help you,

USE PUBS
Go

SELECT COUNT(*) row_num, A.au_lname
FROM authors A, authors B
WHERE A.au_lname > B.au_lname
GROUP BY A.au_lname
ORDER BY COUNT(*)

|||

I want results to look like this:

Tenor / Date / Name of the curve/ DF / Implied rate

O/N 1/08/07 EUR_CURVE 0.9999987 4.020000

T/N 1/08/07 EUR_CURVE . .

S/N 1/08/07 EUR_CURVE . .

1W 1/08/07 EUR_CURVE . .

2W 1/08/07 EUR_CURVE . .

1M 1/08/07 EUR_CURVE . .

.

.

.

1Y 1/08/07 EUR_CURVE . .

tks

Add additional Subscribers

Hi all,
Have Transactional Replication on SQL 2000 EE SP4 for some time now as
follows:
Publisher/Distributor is an 8 way Server
8GB RAM, RAID 1 (ldf), RAID 10 (mdf), RAID 0 (snapshot folder)
Dist Agents run at Distributor as PUSH to existing Subscribers as follows
Subscriber1 - ServerA
Subscriber2 - ServerB
Subscriber3 - ServerC
This works great, and I now want to ADD:
Subscriber4 - ServerD
Subscriber5 - ServerE
What I want to do is add Server D and E as Subscribers (these are all
separate physical servers) without having to re-initialize any data on
Servers A, B, or C which have been running just fine.
I can prevent access to the PUBLISHER database (~20GB) and quiesce the
Publisher and Subscribers 1/2/3 on Servers A, B, C for several hours if need
be.
What is the best way overall to approach this ?
When the Replication was first setup, I set the Snapshot Agent to use 8 BCP
Threads and it took about 30 mins to generate the Initial Snapshot, and
about 1.5 hours to apply to each Subscriber A, B, C across the network
(these servers are all well connected with dedicated nics on a private LAN
solely for Server to Server Replication traffic).
If I go through the Publication Properties dialog and ADD my two new
Subscribers 4/5 (Servers D/E) using the Wizards...my questions are as
follows:
1 - Will the addition of the two NEW Subscription 4/5 for Servers D/E cause
the Snapshot Agent to actually create a fresh snapshot ? I assume it will
"invalidate", but there is no existing snapshot currently available, and the
SS Agent has only been run manually on occasion since the intitial setup to
publish new articles to the subscribers which has worked fine. Otherwise the
SS Agent remains disabled.
2 - Will the Snapshot attempt to be applied to, or mess up anything for the
existing (already in-synch) Subscriptions on Servers A, B, C or should it
*only* apply itself to the NEW Subscribers 4/5 on Servers D/E ?
I hope the above makes sense and ariticulates what I'm trying to do. Thanks
in advance for any advice, or hints/tips you can provide.
Thanks,
Frank
Just as a follow up question, if I have the Publisher DB and Subscriber DB's
all in synch, and not accessible to users can I:
1 - Take a FULL Backup of one of the Subscription DB on any of the existing
Subscriber Servers (A/B or C), then restore it to Servers D/E
2 - Use the Publication Wizard to PUSH a new Subscription to Servers D/E
using the No Synch option ?
Does that do all the necessary behind the scenes processing correctly ? I
assume because I've restored a backup of an existing Subscriber DB to the
NEW Subscriber DB that it will have all the INS, UPD, DEL StoredProcs in
place as well ?
I read you can do a backup/restore of the Publication DB on a Subscriber for
a no-synch initialization, but the Subscription DB's have auto created
ClusteredIndexes for the PriKeys that exist on the Publication so would that
be an issue ?
In addition, there are some different non-clustered indexes on the
Subscribers as they are used for read-only queries, and mostly reporting
purposes that are offloaded from running on the Publisher DB.
Thanks,
Frank
"Frank Conte" <Frank.Conte@.discussions.ms.com> wrote in message
news:%23GPqOqpwHHA.4640@.TK2MSFTNGP03.phx.gbl...
> Hi all,
> Have Transactional Replication on SQL 2000 EE SP4 for some time now as
> follows:
> Publisher/Distributor is an 8 way Server
> 8GB RAM, RAID 1 (ldf), RAID 10 (mdf), RAID 0 (snapshot folder)
> Dist Agents run at Distributor as PUSH to existing Subscribers as follows
> Subscriber1 - ServerA
> Subscriber2 - ServerB
> Subscriber3 - ServerC
> This works great, and I now want to ADD:
> Subscriber4 - ServerD
> Subscriber5 - ServerE
> What I want to do is add Server D and E as Subscribers (these are all
> separate physical servers) without having to re-initialize any data on
> Servers A, B, or C which have been running just fine.
> I can prevent access to the PUBLISHER database (~20GB) and quiesce the
> Publisher and Subscribers 1/2/3 on Servers A, B, C for several hours if
> need be.
> What is the best way overall to approach this ?
> When the Replication was first setup, I set the Snapshot Agent to use 8
> BCP Threads and it took about 30 mins to generate the Initial Snapshot,
> and about 1.5 hours to apply to each Subscriber A, B, C across the network
> (these servers are all well connected with dedicated nics on a private LAN
> solely for Server to Server Replication traffic).
> If I go through the Publication Properties dialog and ADD my two new
> Subscribers 4/5 (Servers D/E) using the Wizards...my questions are as
> follows:
> 1 - Will the addition of the two NEW Subscription 4/5 for Servers D/E
> cause the Snapshot Agent to actually create a fresh snapshot ? I assume it
> will "invalidate", but there is no existing snapshot currently available,
> and the SS Agent has only been run manually on occasion since the intitial
> setup to publish new articles to the subscribers which has worked fine.
> Otherwise the SS Agent remains disabled.
> 2 - Will the Snapshot attempt to be applied to, or mess up anything for
> the existing (already in-synch) Subscriptions on Servers A, B, C or should
> it *only* apply itself to the NEW Subscribers 4/5 on Servers D/E ?
> I hope the above makes sense and ariticulates what I'm trying to do.
> Thanks in advance for any advice, or hints/tips you can provide.
> Thanks,
> Frank
>
|||(1) for an initialisation you'll need to create a new snapshot.
(2) initialization of the 2 new subscriptions won't affect existing
subscriptions.
HTH,
Paul Ibison
|||You can use a backup of the subscriber - it is much less work than taking the
publisher backup. You don't have to be concerned about identity columns,
deleteing data from filtred articles, removing redundant tables and so on.
There are more details which might be relevant here:
http://www.replicationanswers.com/NoSyncInitializations.asp
HTH,
Paul Ibison
|||Hi Paul,
Thanks for the quick reply, and helpful info.
So, according to section 2.1 "Adding Additional Articles" in the link you
provided; if I do this by way of backup an existing Subscriber, restore it
to a new Subscriber(s) than do a no-sych Subscription through the wizard I
LOSE the ability to run the snapshot agent thereafter where it would
ordinarily:
1 - Generate schema, index and data files for a NEWLY added article on the
publisher. For example, I add a new table on the Publisher, then go to the
Publication Properties and check off the new table to be added in the
Article tab. Normally I would run the Snapshot Agent manually and it
generated the files for just that newly added article to the Publication.
2 - Have the Distribution Agent pickup the new articles and push them to my
new Subscribers, like it would for my existing ones that were originally
initialized via Snapshot (sync_type=automatic).
If that is the case, I think I should take the other approach as per my
other POST ?
That is...add the two new Subscribers, generate a fresh snapshot, and let
it apply it to the two new Subscribers all while the Publication DB, and
existing Subscription DB's are quiesced and offline (as in no changes
allowed) ?
Hassan's workaround you noted doesn't apply if adding articles via the
Publication Properties, correct ? It would always require using
sp_addsubscription going forward, and as you mentioned make for a less
maintainable setup than I would like.
Thanks,
Frank
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:5C31C7A4-96EE-4DC5-963E-0D8751CA04B3@.microsoft.com...
> You can use a backup of the subscriber - it is much less work than taking
> the
> publisher backup. You don't have to be concerned about identity columns,
> deleteing data from filtred articles, removing redundant tables and so on.
> There are more details which might be relevant here:
> http://www.replicationanswers.com/NoSyncInitializations.asp
> HTH,
> Paul Ibison
>
|||Hi Paul,
I think this is the better approach for me (see my other reply to yours) in
this thread.
So provided I do not delete and re-add my existing Subscribers in the
Publication Properties they will remain completely uneffected by the new
snapshot files that I do want applied to my new Subscribers.
That sounds like the way to go to keep the maintainable setup going forward.
Is there any other known workaround similiar to Hassan's that doesn't
require using scripting after using the backup/restore method instead of the
initialize with snapshot files ?
Many thanks for you insight, and on going assistance you provide in these
NG's and via your web site.
Regards,
Frank
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:971720D2-2205-43B4-96B9-91FC56D37051@.microsoft.com...
> (1) for an initialisation you'll need to create a new snapshot.
> (2) initialization of the 2 new subscriptions won't affect existing
> subscriptions.
> HTH,
> Paul Ibison
|||Hi Frank - this is correct. For addition of new 'automatic' articles, you'd
need to do this in scripts. Therefore adding new subscriptions normally will
be more suitable. Actually I do the nosync method primarily when the
bandwidth is small and the snapshot is huge - in which case I zip it up
before FTPing over - quite different to your case as far as I can determine.
Cheers,
Paul Ibison
|||What I've seen some people do is to add the new articles to a new publication
in order for the process to be automatic (after having already done a nosync
initialization). This is fine for a one-off provided the articles aren't
related.
HTH,
Paul Ibison

add additional right scale on chart report

Hi there,

I have a case. I have 3 numeric fields and 1 category field to be displayed on bar chart reports. the problem is 2 of the 3 numeric fields have significant different scale value. so I need to add additonal scale at the right side of bar chart to represent 1 numeric fields + the 2 other numeric fields at the left side scale (Y). Please advice. Thanks.

R'gards

Toni

A secondary y-axis is currently not supported natively. Adding this feature in RS charts natively is under consideration for a future release.

Note: RS 2005 also has the new CustomReportItem feature. This enables ISVs to offer add-on components which integrate with RS 2005 and e.g. provide secondary y-axis in charts already today. E.g. Dundas Software offers such a product which needs to be licensed separately.

-- Robert

Add a new disk

Since the existing disk is almost full, we are going to
add an additional disk to the SQL Server 2000.
We would like to move all SQL Server Backup files to the
new disk. We would like to know what we have to do is as
follow:
1) Add a new disk (With a different drive name)
2) Copy the whole folder of "data\MSSQL\BACKUP" to the new
drive
3) Change the Directory used for backup in the Database
Maintenance Plan
ThanksI wouldn't move the folder. I'd just create the desired folder on the new
drive and specify this as the backup directory in your jobs/plane, and then
for a few days clean up the old backup files manually.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Mark" <anonymous@.discussions.microsoft.com> wrote in message
news:0eb501c3c046$d9917c60$a001280a@.phx.gbl...
> Since the existing disk is almost full, we are going to
> add an additional disk to the SQL Server 2000.
> We would like to move all SQL Server Backup files to the
> new disk. We would like to know what we have to do is as
> follow:
> 1) Add a new disk (With a different drive name)
> 2) Copy the whole folder of "data\MSSQL\BACKUP" to the new
> drive
> 3) Change the Directory used for backup in the Database
> Maintenance Plan
> Thanks