Can I implement a replication between sql server 2005 and IBM AS400 DB?

Hello,

I want to set up As400 DB as a publisher and the sql server 2005 as a subscriper, so that I can read some data from AS400 DB in sql server 2005 and every evening, I need the replicated data from AS400 to be refreshed.

Can I do it ? If yes, how to do it?

Thanks

You cannot subscribe to an AS400 publication from SQL. SQL would have to be the publisher and push via an OLEDB driver.

You can probibly do it pushing to SQL but this would be a question for an AS400 forum.

Martin

Can CONTAINSTABLE be made to match unconditionally?

Say that users searching a Books database are able to search by Author,
Title, and Publisher using three separate text boxes. They can choose to
leave any or all text boxes empty if they do not care to filter by that
particular item.
Say I have a query that looks similar to this:
b.* FROM Books b
INNER JOIN CONTAINSTABLE( Books, Author, @.author) authorRank ON b.BookId =
authorRank.[KEY]
INNER JOIN CONTAINSTABLE( Books, Title, @.title) titleRank ON ( b.BookId =
titleRank.[KEY] )
INNER JOIN CONTAINSTABLE( Books, Publisher, @.publisher) publisherRank ON (
b.BookId = publisherRank.[KEY] )
This works great if the user chooses to enter something in each of the three
text boxes. However, CONTAINSTABLE does not accept an empty string for the
search condition (nor does it accept *, %, or other wildcards except when
searching for a prefix). How can I get the query to unconditionally match
title and publisher if they leave title and publisher blank but type
something for author?
If there is no way to allow any title and publisher to be returned when the
user leaves those text boxes blank, I will need to write a query for if they
type all three text boxes, a query for if they type in none of the text
boxes, a query if they type in just the publisher but not the title or
author, a query... etc. Not a good solution.
How can I achieve the results I want without writing multiple queries?
Thank you.Hi, Greg
Try something like this (untested):
SELECT * FROM Books
WHERE (@.author IS NULL OR BookId IN (
SELECT KEY FROM CONTAINSTABLE(Books, Author, @.author)
)) AND (@.title IS NULL OR BookId IN (
SELECT KEY FROM CONTAINSTABLE(Books, Title, @.title)
)) AND (@.publisher IS NULL OR BookId IN (
SELECT KEY FROM CONTAINSTABLE(Books, Publisher, @.publisher)
))
Razvan|||That doesn't let me use any of the ranking information provided by
CONTAINSTABLE. It may be workable. I may have found some sort of
sp_configure setting that will change the behavior of noise words. I'm not
sure about it yet.
"Razvan Socol" wrote:

> Hi, Greg
> Try something like this (untested):
> SELECT * FROM Books
> WHERE (@.author IS NULL OR BookId IN (
> SELECT KEY FROM CONTAINSTABLE(Books, Author, @.author)
> )) AND (@.title IS NULL OR BookId IN (
> SELECT KEY FROM CONTAINSTABLE(Books, Title, @.title)
> )) AND (@.publisher IS NULL OR BookId IN (
> SELECT KEY FROM CONTAINSTABLE(Books, Publisher, @.publisher)
> ))
> Razvan
>|||> That doesn't let me use any of the ranking information provided by
> CONTAINSTABLE.
In this case, you might want to use something like this (also
untested):
SELECT b.*, x.RANK, y.RANK, z.RANK FROM Books b
LEFT JOIN CONTAINSTABLE(Books, Author, @.author) x ON b.BookID=x.[KEY]
LEFT JOIN CONTAINSTABLE(Books, Title, @.title) y ON b.BookID=y.[KEY]
LEFT JOIN CONTAINSTABLE(Books, Publisher, @.publisher) z ON
b.BookID=z.[KEY]
WHERE (@.author IS NULL OR x.[KEY] IS NOT NULL)
AND (@.title IS NULL OR y.[KEY] IS NOT NULL)
AND (@.publisher IS NULL OR z.[KEY] IS NOT NULL)
Razvan|||If @.author is NULL (or blank), then CONTAINSTABLE will throw an error, which
is pretty much the entire problem here.
"Razvan Socol" wrote:

> In this case, you might want to use something like this (also
> untested):
> SELECT b.*, x.RANK, y.RANK, z.RANK FROM Books b
> LEFT JOIN CONTAINSTABLE(Books, Author, @.author) x ON b.BookID=x.[KEY]
> LEFT JOIN CONTAINSTABLE(Books, Title, @.title) y ON b.BookID=y.[KEY]
> LEFT JOIN CONTAINSTABLE(Books, Publisher, @.publisher) z ON
> b.BookID=z.[KEY]
> WHERE (@.author IS NULL OR x.[KEY] IS NOT NULL)
> AND (@.title IS NULL OR y.[KEY] IS NOT NULL)
> AND (@.publisher IS NULL OR z.[KEY] IS NOT NULL)
> Razvan
>|||> If @.author is NULL (or blank), then CONTAINSTABLE will throw an error [...]
Aha! I told you it was untested... :) I assumed that CONTAINSTABLE will
return an empty resultset when given a NULL search condition.
Obviously, I was wrong.
In this case, we can use a non-existent word instead of NULL, like
this:
SET @.title=ISNULL(@.title,'NotSpecified')
SET @.author=ISNULL(@.author,'NotSpecified')
SET @.publisher=ISNULL(@.publisher,'NotSpecifi
ed')
SELECT b.*, x.RANK, y.RANK, z.RANK FROM Books b
LEFT JOIN CONTAINSTABLE(Books, Author, @.author) x ON b.BookId=x.[KEY]
LEFT JOIN CONTAINSTABLE(Books, Title, @.title) y ON b.BookId=y.[KEY]
LEFT JOIN CONTAINSTABLE(Books, Publisher, @.publisher) z ON
b.BookId=z.[KEY]
WHERE (@.author='NotSpecified' OR x.[KEY] IS NOT NULL)
AND (@.title='NotSpecified' OR y.[KEY] IS NOT NULL)
AND (@.publisher='NotSpecified' OR z.[KEY] IS NOT NULL)
If you think the word "NotSpecified" may appear in a title of a book,
you can change it with another inexisting word.
Razvan

Can columns be aliased in Transactional Replication?

I have finally got all the bugs worked out of my TransRep publisher ->
Distributor/Subscriber set up when I got hit with a new question that I can't
answer. I've been asked to alias a few of the columns so that in the
replicated DB they show up with a different header than the columns in the
publisher DB.
IE, Server 1 has column name that says Enroller_ID. Boss wants Server 2 to
say User_ID so that the people reading the data won't get confused. Data in
the column will remain the same and has already been filtered on a row
(horizontal) level. I've also filtered on columns, only sending what is
necessary so the users don't see private information.
But no where I look can I find an "alias" box. BOL mentioned something
about using DTS in replication, but I can't find how to link the two. I know
how to change a column name in DTS (I've become quite the expert in using the
Import / Export Wizard to create packages). But, again, I can't figure out
how to link the two or if this is even necessary.
Help is appreciated. Thank you all in advance. Even if you could tell me
what references to search for in BOL would be a great help.
But doesn't replicating a vew (indexed or otherwise) require the base tables
be published with it?
The replication is getting sent to a customer's server, these people have
sysadmin rights on their machines, and if we send over both the table and the
view, they're probably not even going to look at the view.
"Paul Ibison" wrote:

> I wouldn't use transformable publications for this -
> there are easier ways with less overhead. You could
> replicate an indexed view, or alternatively use an
> @.creation_script.
> HTH,
> Paul Ibison, SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||No - if you use an indexed view, it will appear in a
separate section when you are selecting the articles, and
it doesn't require the underlying table(s). What you are
saying applies to standard views, which of course is
another posibility - you could replicate the table to
another named table and have a view on the subscriber
with the same name as the original table. This would
avoid the overhead on the publisher of maintaining the
indexed view behind the scenes.
HTH,
Paul Ibison, SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||you can modify the replication stored procedures to reflect the new names on
the subscriber.
To do this you will also need to use a pre-creation script containing the
schema of these tables with the new names.
Then you will need to use the delete existing data in the table option in
the snapshot tab of your table article properties dialog box.
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
"Catadmin" <Catadmin@.discussions.microsoft.com> wrote in message
news:D16A826B-586C-4FD6-AFFE-A22AE222A06F@.microsoft.com...
> I have finally got all the bugs worked out of my TransRep publisher ->
> Distributor/Subscriber set up when I got hit with a new question that I
can't
> answer. I've been asked to alias a few of the columns so that in the
> replicated DB they show up with a different header than the columns in the
> publisher DB.
> IE, Server 1 has column name that says Enroller_ID. Boss wants Server 2
to
> say User_ID so that the people reading the data won't get confused. Data
in
> the column will remain the same and has already been filtered on a row
> (horizontal) level. I've also filtered on columns, only sending what is
> necessary so the users don't see private information.
> But no where I look can I find an "alias" box. BOL mentioned something
> about using DTS in replication, but I can't find how to link the two. I
know
> how to change a column name in DTS (I've become quite the expert in using
the
> Import / Export Wizard to create packages). But, again, I can't figure
out
> how to link the two or if this is even necessary.
> Help is appreciated. Thank you all in advance. Even if you could tell me
> what references to search for in BOL would be a great help.
>
|||Well, the system is not letting me say both of you answered my question.
However, you both did (with different ideas) and I greatly appreciate all
your assistance. You just made my life a LOT easier. @.=)
Thank you very much for your time.

can avoid this rollback ??

Hi,
We're testing Transactional replication with updatable subscriber. We have
kept a machine @.branch office which is the publisher and the subscriber is at
the head office. Publisher is win 2003 server with win 2000 sp4 and
subscriber is a cluster with win 2003 and sql 2000 sp3. For small commands
replication is quick but for some specific processes invloving many
transactions, the replication backlog is huge.
We used profiler and found that there is a table whci is deleted completely
during the process and populated again with current data. The table has
almost 80 thousand rows. The profiler shows that sp_MSdel is run for every
row and then sp_MSins is run to insert data. this process takes lot of time
and creates backlog.
After the table got repopulated at subscriber and replication procs started
inserting data in another table, the publisher machine got switched off
accidently by a person at branch. When we started the machine next time, the
second tables at subscriber was empty and the profiler showed that again the
sp_MSdel was being executed at subscriber.
Does this mean that , during the process, if ne of the participating machine
is not available, the whole process will be restarted?
Why it strts with the table it had already replicated?can I stop this?
Ne suggestions?
regards,
have a look at the transaction on the publisher which caused this process.
It is probably a large update statement which is not being done as an update
in place on the subscriber, but rather being decomposed into a series of
delete and then update statements. See if you can identify if this action
was initiated by a stored procedure on the publisher and if so consider
replicating the execution of this stored procedure. You might also want to
look at using the trace flag mentioned in the following kb article which
will force an update in place.
http://support.microsoft.com/kb/302341/EN-US/
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
"k_s" <ks@.discussions.microsoft.com> wrote in message
news:40CEB69C-0E43-4A93-A89D-60A9BBFD08DE@.microsoft.com...
> Hi,
> We're testing Transactional replication with updatable subscriber. We have
> kept a machine @.branch office which is the publisher and the subscriber is
> at
> the head office. Publisher is win 2003 server with win 2000 sp4 and
> subscriber is a cluster with win 2003 and sql 2000 sp3. For small commands
> replication is quick but for some specific processes invloving many
> transactions, the replication backlog is huge.
> We used profiler and found that there is a table whci is deleted
> completely
> during the process and populated again with current data. The table has
> almost 80 thousand rows. The profiler shows that sp_MSdel is run for every
> row and then sp_MSins is run to insert data. this process takes lot of
> time
> and creates backlog.
> After the table got repopulated at subscriber and replication procs
> started
> inserting data in another table, the publisher machine got switched off
> accidently by a person at branch. When we started the machine next time,
> the
> second tables at subscriber was empty and the profiler showed that again
> the
> sp_MSdel was being executed at subscriber.
> Does this mean that , during the process, if ne of the participating
> machine
> is not available, the whole process will be restarted?
> Why it strts with the table it had already replicated?can I stop this?
> Ne suggestions?
> regards,
|||Thanks for the prompt reply Hilary.
The process is actually deleting a table completely and then inserting rows
into it. This is done by a stored procedure.
Could u give me ne link to get more idea about the 'replicatin execution of
sp'.
We told the application maintenance guys this issue, anmd asked them to
change the logic to update instead of competely emptying the table.
"Hilary Cotter" wrote:

> have a look at the transaction on the publisher which caused this process.
> It is probably a large update statement which is not being done as an update
> in place on the subscriber, but rather being decomposed into a series of
> delete and then update statements. See if you can identify if this action
> was initiated by a stored procedure on the publisher and if so consider
> replicating the execution of this stored procedure. You might also want to
> look at using the trace flag mentioned in the following kb article which
> will force an update in place.
> http://support.microsoft.com/kb/302341/EN-US/
> --
> 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
> "k_s" <ks@.discussions.microsoft.com> wrote in message
> news:40CEB69C-0E43-4A93-A89D-60A9BBFD08DE@.microsoft.com...
>
>
|||Is it possible that you are doing a cascading update of the entire table or
a large portion of it?
Regarding replicating the execution of stored procedures have a look at
Publishing Stored Procedure Execution in BOL.
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
"k_s" <ks@.discussions.microsoft.com> wrote in message
news:35203F89-5688-497D-B46B-F8D5B85728A1@.microsoft.com...[vbcol=seagreen]
> Thanks for the prompt reply Hilary.
> The process is actually deleting a table completely and then inserting
> rows
> into it. This is done by a stored procedure.
> Could u give me ne link to get more idea about the 'replicatin execution
> of
> sp'.
> We told the application maintenance guys this issue, anmd asked them to
> change the logic to update instead of competely emptying the table.
> "Hilary Cotter" wrote:
|||Couldn't get u. Presently it's not update. It's a history table which
maintens one day history. So everyday, it is cleared and working table's data
is shifted ti it
I saw and tried the sp execution , works well. But in my case it won't fit,
as I have my procedure calling another procedure and BOL says that won't b
supported.
: (
"Hilary Cotter" wrote:

> Is it possible that you are doing a cascading update of the entire table or
> a large portion of it?
> Regarding replicating the execution of stored procedures have a look at
> Publishing Stored Procedure Execution in BOL.
> --
> 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
> "k_s" <ks@.discussions.microsoft.com> wrote in message
> news:35203F89-5688-497D-B46B-F8D5B85728A1@.microsoft.com...
>
>

can a Subscriber be a Publisher?

sql2k sp3a
The configuration is currently.
Box1-- Publisher/ Distributor.
Several Subscribers all with Pull Subscriptions.
One of these Pull Subscriptions that my boss wants me to start Replicating
from. Can this type of deal cause problems?
TIA, ChrisR
Have a look at "republisher replication model" in SQL Server books online
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
"ChrisR" <noemail@.bla.com> wrote in message
news:eDAEYipSFHA.3332@.TK2MSFTNGP15.phx.gbl...
> sql2k sp3a
> The configuration is currently.
> Box1-- Publisher/ Distributor.
> Several Subscribers all with Pull Subscriptions.
>
> One of these Pull Subscriptions that my boss wants me to start Replicating
> from. Can this type of deal cause problems?
> TIA, ChrisR
>
|||Makes sense. But what will happen if I sp_replAddColumn on the Publisher. I
know that column will make it on the RePublisher, but will it arrive on the
RePublisher's Subscribers?
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:OJI0ZiqSFHA.616@.TK2MSFTNGP12.phx.gbl...
> Have a look at "republisher replication model" in SQL Server books online
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
> "ChrisR" <noemail@.bla.com> wrote in message
> news:eDAEYipSFHA.3332@.TK2MSFTNGP15.phx.gbl...
>
|||No idea...I'd have to test it against Pubs, Pubs1 and Pubs2 on my box (or
you can test it on yours...)
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
"ChrisR" <noemail@.bla.com> wrote in message
news:uu7xAwqSFHA.2648@.TK2MSFTNGP10.phx.gbl...
> Makes sense. But what will happen if I sp_replAddColumn on the Publisher.
> I know that column will make it on the RePublisher, but will it arrive on
> the RePublisher's Subscribers?
>
> "Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
> news:OJI0ZiqSFHA.616@.TK2MSFTNGP12.phx.gbl...
>