Replication to IP addressed server

we have an outside company that replicates to us and we are known to them via an IP address. We get an error now...Sql Server 2005 now does not support a server Alias, IP address, or any other alternate name are not supports. Specify the actual Server Name

I tried putting in the hosts file on their server

168.168.110.2 SQL2005

able to connect to our server through Studio Manager...no problem....

replication....no dice

what is the work around to make this happen, I am not a network guru...know enough to kill someone, but I need to get this to work before I can move to the new production server

Thanks

John

Exactly what part is failing? Have you tried creating an alias and mapping it to the IP address?|||

When the outside company tries to set the Subscription up it fails since they try to enter the IP address. From what the network people tell me, we have a T1 line between us and open addresses so they can ping the IP address. But, we are on two completely different networks....2 different companies.

They tried to put it into the hosts file on their server (which is SQL 2000) and on the PC they were using studio manager on, but that did not help

When you say create an alias, what exactly are you referring to, since my network skills are enough to kill someone right now.

On their side, any network operations will take some paper work so we are hoping to keep it on the server....paper work just takes a bit too long sometimes

|||

the error we get

SQL Server Replication requires the actual server name to make a connection to the server.
Connects through a server alias, IP addres, or any other alternate name are not supported.
Specify the actual server name 'SQL2005'

|||

Hi,

When you setup subscription, do you specify the subscriber using IP address or server name? I saw you have do the mapping with host file. You may want to check the following:

1) Make sure the outside company can connect to your server using name "SQL2005" using osql or management studio.

2) after connect to SQL2005, try "select @.@.SERVERNAME". Make sure the server name is "SQL2005" if it is default instance, or "SQL2005\instancename" if it is named instance

3) when create subscription, enter "SQL2005" instead of IP address.

Let me know if it still doesn't work for you.

Thanks,

-Peng

|||

It seems we have gotten past one problem and on to a new problem.

It looks like the Alias took care of the problem.

now, when the wizard gets to "New subscription Wizard"

Choose one or more Subscribers and specify each subscrition database

We can choose "Subscriber" as "SQL2005" - which is the server

Great!

but, there are no "Subscription Database" to choose from

The sql user has access to the DB, they have "Connect Replication" and "Connect" permission. The sql user also has public permission to the DB.

What else could be missing?

I feel we are close, just trying to finish this

One other thing, I have read that SQL 2000 distributor will work with a sql 2005 Subscription if it is transactional, is that correct?

from another post "If you are using a transactional read-only subscriber then the subscriber can be up to two versions higher than the publisher. SQL 2000 publisher and SQL 2005 subscriber should work, "

|||

Isn't "SQL2005" the publisher machine? The "Subscriber" should be the machine on the other end, the one receiving the data. You may need to click on the "Add Subscriber" button below and add the machine as a valid subscriber.

|||

SQL2000 at the outside company is the publisher and distributor

they also set the subscription for us (Push).

I can not access there SQL Server at all since it has windows auth

We are SQL Server 2005 (not yet, maybe this weekend)

So they are pushing the data from their DB to us

SQL2000 -> SQL2005

which, according to the docs does work. There just seems to be some bloody security issue going on....seems Microsoft went overboard on some of this and now has made it a real pain in the neck to even get working.

What do they permissions have to be to get this working

|||

Is "SQL2005" the actual name of your machine?

|||

SQL2005 is the name of my server which is also a SQL2005 server

Windows 2003 Server - 64 bit, SQL Server 2005 Enterprise Edition 64 bit

The server will be renamed to what the production server is currently this weekend...right now we are just trying to test this replication issue.

We had the replication working a few minutes before we got a

the user is not associated with a trusted SQL Server Connection

Source:SQL2005

The user they are using on my server (SQL2005) now has every role and has every permission on the DB they are trying to replicate to.

|||

So you're saying you got past the problem with adding a subscription?

And now you're having connection problems? Can you describe what exactly was working, and what failed suddenly?

And I assume this is transactional replication?

|||

This is transactional replication.

First, we had the issues with trying to replication from 2000-> 2005 because of the IP address issue, that was fixed with using an Alias.

which the error we got clearly stated that a server alias could not be used....so I guess it can ....which the error screen sure does say that does not work. Of course, no help button on that screen to help a person out. That was one of the bigger huddles to get over.

the SQL User, since we allow both connections, has access to the DB and we gave them the ability to do "Connection Replication"

That does not seem to be enough.

The person on the SQL2000 server (who is using Studio manger by the way) was using the Subscription Wizard and could not even get any DBs in the drop down to select from

Now, I have given all permissions for the user for the DB and all roles for the entire Server.

He can now see the DB to replicate too on the SQL2000 Subscription Wizard and is able to select the DB.

So, we crossed through some security issue to allow that, but we have no clue which one.

This use to be a basic thing to really do, now it is complicated with too much security.

We are right now waiting to see if the subscription actually takes place......and waiting and waiting....atleast no errors yet.

So, in a nutshell

do a transactional replication from SQL2000 -> SQL2005

where the sql2005 can not do any of the setup because of security

the SQL2000 server has to be accessed with Studio manager - check

an Alias must be set up...even though the subscription wizard says that this is wrong

and then there is some permission besides "Connection replication" that has to be set that we can't figure out

|||

Couple things:

- To create a subscription and see the list of databases at the subscriber, you need to be a minimum of db_owner of the subscriber database, or be a member of the sysadmin role at the subscriber machine. This is documented in the "Security Role Requirements for Replication Setup" topic in SQL Server 2005 Books Online.

Since they connect to your subscriber machine using a standard login/password, that login needs to be at least a dbo of the subscriber database.

- Where do you see the "Connection replication" permission thing? If I remember correctly, you need to set permissions for the agents to connect to the publisher, distributor and subscriber machine. Did you start the snapshot agent, logreader agent and distribution agent yet? Do you see any failures?

|||

They are failing on the Distribution Agent...snapshot and logreader does work

Login failed for user "The User is not associated with a Trusted SQL Server Connection. The Process Could not connect to Subscrivber "SQL2005" The Step Failed

The outside company can use SQL Server Man Studio to connect to our server using the same account, so we know the account works.....

something else is messed up still.

"Connection replication" is a permission of the user on the DB that we are trying to replicate to .....seemed like something we needed.

But right now, the user has every permission there is

|||

By default, the subscription will be created and use windows authentication for all connections to the publisher, distributor and subscriber.

To change the security settings for the subscriber to use standard security, you need to modify the security settings for the distribution agent.

TO learn how to do this, see SQL 2005 Books Online topic "How to: View and Modify Replication Security Settings".

You can also access search for, or access this topic at msdn.com.

http://msdn2.microsoft.com/ms151761.aspx

Replication setup via VB.NET

I have a VB.NET program that is executing the SQL commands to setup
replication. I am getting an error (A severe error has occured) on the
sp_addmergepubliction command.
I did setup a profiler and notice one area:
sp_adddistributor was found in the text of this event, the text has been
replaced with this comment for security reasons.
My entire text, before the sp_addmergepulication does:
use master
exec sp_replicationdboption @.dbname='InWare',etc
exec dbo.sp_helpditributor @.rpcsrvname=@.distributor OUTPUT
if @.distributor is null
begin
declare @.xpath varchar (255)
select @.xpath=substring(filename,1,charindex('InWare_data ',filename)-1)
FROM master..sysdatabases where name='inware'
exec sp_adddistributor @.distributor=@.@.servername
exec sp_adddistributiondb @.database='inxsqlDIST',etc
exec sp_adddistpublisher @.publisher=@.@.servername, etc
end
exec sp_replicationdboption @.dbname='InWare',@.optname='merge
publish',@.value='true'
I guess, now that I see Profiler has the line sp_adddistributor
commented out, this could cause the problem. What is commenting it out -
SQL or VB.NET?
I want to use VB (my program) to have the user setup replication so they
don't have to use the enterprise manager, and it allows me to control
the setup.
Thanks.
Darin
Darin
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Darin wrote:
> I want to use VB (my program) to have the user setup replication so they
> don't have to use the enterprise manager, and it allows me to control
> the setup.
How about using SQL-DMO through COM Interop?
|||Profiler is removing it because the command contains the keyword "password".
Profiler won't display anything that includes that keyword to eliminate a
security hole. The command is still likely being run, just not displayed in
Profiler.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
"Darin" <darin_nospam@.nospamever> wrote in message
news:exGVx28KFHA.2596@.TK2MSFTNGP10.phx.gbl...
>I have a VB.NET program that is executing the SQL commands to setup
> replication. I am getting an error (A severe error has occured) on the
> sp_addmergepubliction command.
> I did setup a profiler and notice one area:
> sp_adddistributor was found in the text of this event, the text has been
> replaced with this comment for security reasons.
> My entire text, before the sp_addmergepulication does:
> use master
> exec sp_replicationdboption @.dbname='InWare',etc
> exec dbo.sp_helpditributor @.rpcsrvname=@.distributor OUTPUT
> if @.distributor is null
> begin
> declare @.xpath varchar (255)
> select @.xpath=substring(filename,1,charindex('InWare_data ',filename)-1)
> FROM master..sysdatabases where name='inware'
> exec sp_adddistributor @.distributor=@.@.servername
> exec sp_adddistributiondb @.database='inxsqlDIST',etc
> exec sp_adddistpublisher @.publisher=@.@.servername, etc
> end
> exec sp_replicationdboption @.dbname='InWare',@.optname='merge
> publish',@.value='true'
> I guess, now that I see Profiler has the line sp_adddistributor
> commented out, this could cause the problem. What is commenting it out -
> SQL or VB.NET?
> I want to use VB (my program) to have the user setup replication so they
> don't have to use the enterprise manager, and it allows me to control
> the setup.
> Thanks.
> Darin
> Darin
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||Any idea what could be causing the error:
A severe error occured .... Any results should be discarded
on the exec sp_addreplication @.publication='InWareMerge'
when it works find on QA?
Darin
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||If you script a publication in EM, it assumes you have the publisher,
distributor, and subscribers already set up for all servers involved in
replication. If that is not the case, then creating a publication will
fail.
GNH
"Darin" <darin_nospam@.nospamever> wrote in message
news:ukmK5P9KFHA.3992@.TK2MSFTNGP15.phx.gbl...
> Any idea what could be causing the error:
> A severe error occured .... Any results should be discarded
> on the exec sp_addreplication @.publication='InWareMerge'
> when it works find on QA?
> Darin
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||My problem is all of my script run from the VB.NET program work fine
until that command.
Whereas if I take all of the script and paste it into a QA window it all
works perfectly.
Darin
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

replication question

Hi,
We basically want to have a second hot copy of our database and keep it
up-to-date via transactional replication (non-updating subsrcriber) We are
using SQL Server 7.0.
If we restore the latest backup of the published database onto the
subscriber so we have everything in sync to start, manual inserts on the
publisher database fail to replicate because of identity column insert
problems. When it trys to replicate the insert to our subsriber database, it
seems to blowup because the table on the subsriber has identity ids as well.
How do we get around this? We only want the identity columns on the
subsriber to function when we make the subscriber the live database in the
case that the publisher has problems. Otherwise during replication, we just
want the IDs origianlly issued on the publisher to replicate to the
subscriber as intended.
thanksRead BOL for details on this topic:
Managing Identity Values
You can manage identity values by:
a.. Allowing Microsoft® SQL ServerT 2000 replication to automatically
manage identity columns by dynamically allocating ranges of identity values
to the Publisher and all the Subscribers.
b.. Using the Transact-SQL NOT FOR REPLICATION option when defining the
identity column.
c.. Using a primary key other than the identity column (for example, a
composite key or a rowguid column), if an identity column is not necessary.
This strategy eliminates the overhead of managing identity columns on the
replicated data.
You have a few options as stated above. I'd suggest to use rowguid column if
you can, avoiding identity column in general, if possible, when it comes to
any type of replication. Otherwise, "NOT FOR REPLICATION" identity column
would be my 2nd pick. This will preserve the identity values coming from
Publisher, and Subscriber's indentity values won't get incremented via
replication.
However, once your production server goes down and your backup server goes
live, the Subscriber's ID values will increment because of INSERTS. Now, if
this column is your primary key, then there's a potential that ID values
from Subscriber's and Publisher's will collide at some point, unless you
plan ahead. Also, you have to think about sync this data back to the
Publisher once the machine is back online.
Hope it helps.
HH
"aaz" <aaz@.webcapacity.com> wrote in message
news:edCboTVjDHA.1740@.TK2MSFTNGP12.phx.gbl...
> Hi,
> We basically want to have a second hot copy of our database and keep it
> up-to-date via transactional replication (non-updating subsrcriber) We are
> using SQL Server 7.0.
> If we restore the latest backup of the published database onto the
> subscriber so we have everything in sync to start, manual inserts on the
> publisher database fail to replicate because of identity column insert
> problems. When it trys to replicate the insert to our subsriber database,
it
> seems to blowup because the table on the subsriber has identity ids as
well.
> How do we get around this? We only want the identity columns on the
> subsriber to function when we make the subscriber the live database in the
> case that the publisher has problems. Otherwise during replication, we
just
> want the IDs origianlly issued on the publisher to replicate to the
> subscriber as intended.
> thanks
>
>|||On the other hand, you could use log shipping instead,
problem solved.
Regards
John

Replication over VPN

I think that the configuration for replication via VPN is the same as that
used on a LAN. But I can not register remote SQLserver by name and it does
not work by IP.
1. Does it work in case dynamic IP ?
2. Use alias name instead of IP address ?
3. Other recomendations .
Please help.
Thank you.
a vpn should be the same as a local lan. It will work by dynamic ip if it
can resolve the subscriber by host name. However, it sounds like you can't
do this. You need to work on the name resolution issue. It could be a
network connection problem. Tracert should help you to figure out where the
break is.
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
"Thang Long" <Thang Long@.discussions.microsoft.com> wrote in message
news:0F60A7B1-DF73-41F3-AF6F-9D3B64A3780E@.microsoft.com...
> I think that the configuration for replication via VPN is the same as that
> used on a LAN. But I can not register remote SQLserver by name and it does
> not work by IP.
> 1. Does it work in case dynamic IP ?
> 2. Use alias name instead of IP address ?
> 3. Other recomendations .
> Please help.
> Thank you.
>
>
|||Thank you for your reply.
The remote server named SERVER. Does it break naming rules ?
Many thanks.
Thang Long
"Hilary Cotter" wrote:

> a vpn should be the same as a local lan. It will work by dynamic ip if it
> can resolve the subscriber by host name. However, it sounds like you can't
> do this. You need to work on the name resolution issue. It could be a
> network connection problem. Tracert should help you to figure out where the
> break is.
> --
> 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
> "Thang Long" <Thang Long@.discussions.microsoft.com> wrote in message
> news:0F60A7B1-DF73-41F3-AF6F-9D3B64A3780E@.microsoft.com...
>
>
|||it might, I would try a fully qualified domain name in the alias section.
Also make sure there are no entries for it in
%windir%\system32\drivers\etc\hosts
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
"Thang Long" <ThangLong@.discussions.microsoft.com> wrote in message
news:61B0C5A5-B8AD-4003-A1E4-4EF2B64FA6C9@.microsoft.com...[vbcol=seagreen]
> Thank you for your reply.
> The remote server named SERVER. Does it break naming rules ?
> Many thanks.
> Thang Long
> "Hilary Cotter" wrote:
it[vbcol=seagreen]
can't[vbcol=seagreen]
the[vbcol=seagreen]
that[vbcol=seagreen]
does[vbcol=seagreen]
|||Doesn't break rules, try to the following.
ping SERVER
If you see the IP address in the lines follwoing this request and it's the
correct IP the DNS is funcitoning properly.
If the IP address is wrong or no IP address is given you may need the
assistance of the network administrator. I've seen it where a new remote
client VPN tunnel had an access-list misconfigured that then wouldn't let it
pull DHCP/DNS information accross the tunnel.
HTH,
GTM
"Thang Long" <ThangLong@.discussions.microsoft.com> wrote in message
news:61B0C5A5-B8AD-4003-A1E4-4EF2B64FA6C9@.microsoft.com...[vbcol=seagreen]
> Thank you for your reply.
> The remote server named SERVER. Does it break naming rules ?
> Many thanks.
> Thang Long
> "Hilary Cotter" wrote:
|||Where could I find step-by-step guide to setup sql replication between
non-trusted domains.
Thank you.
"Hilary Cotter" wrote:

> it might, I would try a fully qualified domain name in the alias section.
> Also make sure there are no entries for it in
> %windir%\system32\drivers\etc\hosts
> --
> 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
> "Thang Long" <ThangLong@.discussions.microsoft.com> wrote in message
> news:61B0C5A5-B8AD-4003-A1E4-4EF2B64FA6C9@.microsoft.com...
> it
> can't
> the
> that
> does
>
>
|||Paul, thank you. I try replication using VPN, but your article outlines what
to if a VPN is not available. Please tell me other articles.
Rgds,
"Paul Ibison" wrote:

> Thang,
> please take a look at this article:
> http://www.replicationanswers.com/InternetArticle.asp
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||If can not resolve the subscriber by host name. Could I use client network
utility to create alias name and cofigure replication ?
Thank you.
"Greg" wrote:

> Doesn't break rules, try to the following.
> ping SERVER
> If you see the IP address in the lines follwoing this request and it's the
> correct IP the DNS is funcitoning properly.
> If the IP address is wrong or no IP address is given you may need the
> assistance of the network administrator. I've seen it where a new remote
> client VPN tunnel had an access-list misconfigured that then wouldn't let it
> pull DHCP/DNS information accross the tunnel.
> HTH,
> GTM
>
> "Thang Long" <ThangLong@.discussions.microsoft.com> wrote in message
> news:61B0C5A5-B8AD-4003-A1E4-4EF2B64FA6C9@.microsoft.com...
>
>
|||Thang,
sorry - your question related to 'non-trusted domains' so I supposed you
were trying an alternative to the VPN setup. I don't know of any specialized
VPN articles.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Yes, use client network utility to create alias name and specify
alternate snapshot folder using IP adress.
This worked for me,
Adrian

Replication Over Internet via FTP

What is the syntax for the snapshop location setup referring to the "Client
path to this folder from the FTP root"? When my pull connects, it doesn't
seem to issue any change directory commands and does not find the snapshot
files which are there.
The client path is the path from the FTP root directory, so if your snapshot
directory is called TRSnapshot and is in the ftp root directory, the path
would be \TRSnapshot\ftp
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks, worked like a charm but my main issue turned out to be the space in
the order details table in the Northwind db. I am doing a proof of concept
with that DB and it tried to run that script first and bombed.
"Paul Ibison" wrote:

> The client path is the path from the FTP root directory, so if your snapshot
> directory is called TRSnapshot and is in the ftp root directory, the path
> would be \TRSnapshot\ftp
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Jeffrey,
this isn't such a large table - at what point does it fail?
Might be worth using the compression if you have a slow link?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Replication or Update via Trigger ?

Hi SQL Gurus,
I have an application where the end user insist that there should be nine(9)
databases, same server within the system.
There are 'shared tables' on one of the database where any
insert/update/delete on those 'shared tables' must appear immediately on
other databases.
In this case, should I use replication or doing updates via Trigger ?
Thanks for your comments,
KristI am not sure why he/she is insisting for identical databases? Perhaps,
instead of creating tables in all these databases, you can opt to create
views which simply SELECT from a table in a single a database. You don't
have to worry about the data being in sync either.
Replication is not a solution for addressing such requirements. You can opt
for a trigger, but with the information from your post, I rather would
conclude it is an overkill to support redundant data for no apparent
reasons.
--
- Anith
( Please reply to newsgroups only )|||Hi Anith,
I used to think of View.
But I need to create FK from transaction table to these 'shared tables'
e.g : SalesOrder must have FK to Salesman and Area table, where Salesman
and Area are 'shared tables'
How can I do this with view ?
Thanks,
Krist
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:#uM6uAfxDHA.1680@.TK2MSFTNGP12.phx.gbl...
> I am not sure why he/she is insisting for identical databases? Perhaps,
> instead of creating tables in all these databases, you can opt to create
> views which simply SELECT from a table in a single a database. You don't
> have to worry about the data being in sync either.
> Replication is not a solution for addressing such requirements. You can
opt
> for a trigger, but with the information from your post, I rather would
> conclude it is an overkill to support redundant data for no apparent
> reasons.
> --
> - Anith
> ( Please reply to newsgroups only )
>|||Krist, first find out the reason that your user needs 9 databases.
1. If is for security then perhaps create 9 different users or roles instead
and assign permissions accordingly.
2. If it's a limitation in the front end application then perhaps you'll
need to stick with several databases
3. If the user later wants to distribute the 9 databases to 9 different
servers then cross-database views become more tricky.
It's a rather strange requirement, and the underlying reason for it probably
constrains your options even further.
"tristant" <krislioe@.cbn.net.id> wrote in message
news:eAJAYgfxDHA.4064@.tk2msftngp13.phx.gbl...
> Hi Anith,
> I used to think of View.
> But I need to create FK from transaction table to these 'shared tables'
> e.g : SalesOrder must have FK to Salesman and Area table, where Salesman
> and Area are 'shared tables'
> How can I do this with view ?
> Thanks,
> Krist
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:#uM6uAfxDHA.1680@.TK2MSFTNGP12.phx.gbl...
> > I am not sure why he/she is insisting for identical databases? Perhaps,
> > instead of creating tables in all these databases, you can opt to create
> > views which simply SELECT from a table in a single a database. You don't
> > have to worry about the data being in sync either.
> >
> > Replication is not a solution for addressing such requirements. You can
> opt
> > for a trigger, but with the information from your post, I rather would
> > conclude it is an overkill to support redundant data for no apparent
> > reasons.
> >
> > --
> > - Anith
> > ( Please reply to newsgroups only )
> >
> >
>|||Hi Anthony,
You are absolutely right : the reason the number (3)
> 3. If the user later wants to distribute the 9 databases to 9 different
> servers then cross-database views become more tricky.
So, that is my reason, what options do I have now ?
Thanks,
Krist
"Anthony Faull" <anthony.faull@.NOSPAMsanlam.co.za> wrote in message
news:egFyt5fxDHA.536@.tk2msftngp13.phx.gbl...
> Krist, first find out the reason that your user needs 9 databases.
> 1. If is for security then perhaps create 9 different users or roles
instead
> and assign permissions accordingly.
> 2. If it's a limitation in the front end application then perhaps you'll
> need to stick with several databases
> 3. If the user later wants to distribute the 9 databases to 9 different
> servers then cross-database views become more tricky.
> It's a rather strange requirement, and the underlying reason for it
probably
> constrains your options even further.
> "tristant" <krislioe@.cbn.net.id> wrote in message
> news:eAJAYgfxDHA.4064@.tk2msftngp13.phx.gbl...
> > Hi Anith,
> > I used to think of View.
> > But I need to create FK from transaction table to these 'shared tables'
> > e.g : SalesOrder must have FK to Salesman and Area table, where
Salesman
> > and Area are 'shared tables'
> >
> > How can I do this with view ?
> >
> > Thanks,
> > Krist
> >
> > "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> > news:#uM6uAfxDHA.1680@.TK2MSFTNGP12.phx.gbl...
> > > I am not sure why he/she is insisting for identical databases?
Perhaps,
> > > instead of creating tables in all these databases, you can opt to
create
> > > views which simply SELECT from a table in a single a database. You
don't
> > > have to worry about the data being in sync either.
> > >
> > > Replication is not a solution for addressing such requirements. You
can
> > opt
> > > for a trigger, but with the information from your post, I rather would
> > > conclude it is an overkill to support redundant data for no apparent
> > > reasons.
> > >
> > > --
> > > - Anith
> > > ( Please reply to newsgroups only )
> > >
> > >
> >
> >
>|||Your options:
1. Partitioned views
2. Replication
3. Log shipping (backup & restore)
"tristant" <krislioe@.cbn.net.id> wrote in message
news:OQxaWDgxDHA.1760@.TK2MSFTNGP10.phx.gbl...
> Hi Anthony,
> You are absolutely right : the reason the number (3)
> > 3. If the user later wants to distribute the 9 databases to 9 different
> > servers then cross-database views become more tricky.
> So, that is my reason, what options do I have now ?
> Thanks,
> Krist
> "Anthony Faull" <anthony.faull@.NOSPAMsanlam.co.za> wrote in message
> news:egFyt5fxDHA.536@.tk2msftngp13.phx.gbl...
> > Krist, first find out the reason that your user needs 9 databases.
> > 1. If is for security then perhaps create 9 different users or roles
> instead
> > and assign permissions accordingly.
> > 2. If it's a limitation in the front end application then perhaps you'll
> > need to stick with several databases
> > 3. If the user later wants to distribute the 9 databases to 9 different
> > servers then cross-database views become more tricky.
> >
> > It's a rather strange requirement, and the underlying reason for it
> probably
> > constrains your options even further.
> >
> > "tristant" <krislioe@.cbn.net.id> wrote in message
> > news:eAJAYgfxDHA.4064@.tk2msftngp13.phx.gbl...
> > > Hi Anith,
> > > I used to think of View.
> > > But I need to create FK from transaction table to these 'shared
tables'
> > > e.g : SalesOrder must have FK to Salesman and Area table, where
> Salesman
> > > and Area are 'shared tables'
> > >
> > > How can I do this with view ?
> > >
> > > Thanks,
> > > Krist
> > >
> > > "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> > > news:#uM6uAfxDHA.1680@.TK2MSFTNGP12.phx.gbl...
> > > > I am not sure why he/she is insisting for identical databases?
> Perhaps,
> > > > instead of creating tables in all these databases, you can opt to
> create
> > > > views which simply SELECT from a table in a single a database. You
> don't
> > > > have to worry about the data being in sync either.
> > > >
> > > > Replication is not a solution for addressing such requirements. You
> can
> > > opt
> > > > for a trigger, but with the information from your post, I rather
would
> > > > conclude it is an overkill to support redundant data for no apparent
> > > > reasons.
> > > >
> > > > --
> > > > - Anith
> > > > ( Please reply to newsgroups only )
> > > >
> > > >
> > >
> > >
> >
> >
>

Replication Monitor Question

Hello,

We have some mobile devices that are setup to replicate with our sql 2005 server via web sync. It seems that the devices are added multiple times within replication monitor. Is there a way to purge the bogus items. For example we have a mobile unit labeled as Device12 we have it listed under the subscriptions 5x. I would like to purge four of them as they are not currently being the replicated one.

Device12-A6EE69A0944 is the valid subscription

Device12-EE44D1EF3E24 is an invalid one.

Thanks in advance.

John

You may want to query table dbo.MSsubscriptions in the distribution database to see if it has those extra entries. I'm not saying it's safe to delete them, but I'm guessing that's where sql monitor is pulling those rogue entries from.

replication monitor

i have setup replication on server mssql v7.0, but the replication monitor
does not display. have also tried via tools -> replication -> show
replication monitor.But after selecting the server with the distribution
database, it does not expand any further to show the agents. has anyone seen
this before?
Do you have a remote distributor? It will show up there if this is the case.
If not, something seems hosed. I would remove replication and reinstall it.
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
"bjones" <bjones@.discussions.microsoft.com> wrote in message
news:131B5C87-9D38-42E6-9627-E4C8AF022C4F@.microsoft.com...
>i have setup replication on server mssql v7.0, but the replication monitor
> does not display. have also tried via tools -> replication -> show
> replication monitor.But after selecting the server with the distribution
> database, it does not expand any further to show the agents. has anyone
> seen
> this before?
|||publication, distribution and subscription agents run on the same server. i
have tried reinstalling with the same result. is there a sysservers or some
other system related value which might be causing this to fail?
"Hilary Cotter" wrote:

> Do you have a remote distributor? It will show up there if this is the case.
> If not, something seems hosed. I would remove replication and reinstall it.
> --
> 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
> "bjones" <bjones@.discussions.microsoft.com> wrote in message
> news:131B5C87-9D38-42E6-9627-E4C8AF022C4F@.microsoft.com...
>
>
|||No, do you have the show replication monitor group in Tools - Replication?
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
"bjones" <bjones@.discussions.microsoft.com> wrote in message
news:94C95374-EDD5-40C9-AC80-FCBE95FA02EC@.microsoft.com...
> publication, distribution and subscription agents run on the same server.
i
> have tried reinstalling with the same result. is there a sysservers or
some[vbcol=seagreen]
> other system related value which might be causing this to fail?
> "Hilary Cotter" wrote:
case.[vbcol=seagreen]
it.[vbcol=seagreen]
monitor[vbcol=seagreen]
distribution[vbcol=seagreen]
anyone[vbcol=seagreen]
|||Hi Hilary
I have the below and tried registring the distribution server. However
nothing appears under the server when expanding the monitor.
"Hilary Cotter" wrote:

> No, do you have the show replication monitor group in Tools - Replication?
> --
> 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
> "bjones" <bjones@.discussions.microsoft.com> wrote in message
> news:94C95374-EDD5-40C9-AC80-FCBE95FA02EC@.microsoft.com...
> i
> some
> case.
> it.
> monitor
> distribution
> anyone
>
>
|||The distribution and log agents seem to be down because they couldn't connect
to the subscriber. I am not sure how to check their status and restart the
agents without the monitor gui. any advise on how to do this?
"bjones" wrote:
[vbcol=seagreen]
> Hi Hilary
> I have the below and tried registring the distribution server. However
> nothing appears under the server when expanding the monitor.
> "Hilary Cotter" wrote:
|||you should be able to find them in the management folder. It might be a
little hard to identify them - but the category should help.
BTW - did you script out your publications and then re-rerun them. If you
are not careful when editing your publication scripts and assigning new
agent names and publication name it can cause your jobs to disappear when
you look in the agents folder.
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
"bjones" <bjones@.discussions.microsoft.com> wrote in message
news:F8437637-C2E3-42A0-B2EF-3158646F3D36@.microsoft.com...
> The distribution and log agents seem to be down because they couldn't
connect[vbcol=seagreen]
> to the subscriber. I am not sure how to check their status and restart the
> agents without the monitor gui. any advise on how to do this?
> "bjones" wrote:
Replication?[vbcol=seagreen]
server.[vbcol=seagreen]
or[vbcol=seagreen]
the[vbcol=seagreen]
reinstall[vbcol=seagreen]
replication[vbcol=seagreen]
show[vbcol=seagreen]
|||i did script it out because the gui would not allow me to set up the
publication and distribution which is why i was thinking it was related to
sysservers or something. i appear to have 4 distribution jobs and not sure
which was is valid. is there supposed to be only one distribution job for 1
publication other than the distribution/subscription cleanup jobs?
"Hilary Cotter" wrote:

> you should be able to find them in the management folder. It might be a
> little hard to identify them - but the category should help.
> BTW - did you script out your publications and then re-rerun them. If you
> are not careful when editing your publication scripts and assigning new
> agent names and publication name it can cause your jobs to disappear when
> you look in the agents folder.
> --
> 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
> "bjones" <bjones@.discussions.microsoft.com> wrote in message
> news:F8437637-C2E3-42A0-B2EF-3158646F3D36@.microsoft.com...
> connect
> Replication?
> server.
> or
> the
> reinstall
> replication
> show
>
>
|||Can you go to your subscriber or another machine and register the publisher
in EM, and then take a look at replication monitor? I'm trying to find out
if there is something wrong with your publisher setup, or just the EM MMC on
your machine.
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
"bjones" <bjones@.discussions.microsoft.com> wrote in message
news:0F7D1163-6200-40AB-9BF6-5E00AA7DA8FF@.microsoft.com...
> i did script it out because the gui would not allow me to set up the
> publication and distribution which is why i was thinking it was related to
> sysservers or something. i appear to have 4 distribution jobs and not sure
> which was is valid. is there supposed to be only one distribution job for
1[vbcol=seagreen]
> publication other than the distribution/subscription cleanup jobs?
> "Hilary Cotter" wrote:
you[vbcol=seagreen]
when[vbcol=seagreen]
the[vbcol=seagreen]
However[vbcol=seagreen]
same[vbcol=seagreen]
sysservers[vbcol=seagreen]
this is[vbcol=seagreen]
replication ->[vbcol=seagreen]
has[vbcol=seagreen]

Replication mechanism

Hello,
My situation is as follows:
I use Replication in my Mobile Application and when the PocketPc is
connected via USB the replication works fine. But when I tried to synchonize
the data via WLAN it does not work. My quoted url in my code looks as follows:
oRpl.InternetUrl = "http://IP-Adress/HelpDesk/sscesa20.dll"; //I also tried
it to replace the IP-Address with the machineName of my Desktop but it does
also not work.
I also tried to type in this address in my PocketPc when the WLAN is
activated but it occurs always an error that this page could not be loaded.
Does anybody know what else could be go wrong?
thx
patrick
what is the error message you are getting?
Also what happens when you use IE to connect to this url from your PocketPC?
You should get a message saying SQL Server CE agent or something like that.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"pat" <pat@.discussions.microsoft.com> wrote in message
news:09678E54-FBB5-44EA-9327-6ED5FCBD28D7@.microsoft.com...
> Hello,
> My situation is as follows:
> I use Replication in my Mobile Application and when the PocketPc is
> connected via USB the replication works fine. But when I tried to
> synchonize
> the data via WLAN it does not work. My quoted url in my code looks as
> follows:
> oRpl.InternetUrl = "http://IP-Adress/HelpDesk/sscesa20.dll"; //I also
> tried
> it to replace the IP-Address with the machineName of my Desktop but it
> does
> also not work.
> I also tried to type in this address in my PocketPc when the WLAN is
> activated but it occurs always an error that this page could not be
> loaded.
> Does anybody know what else could be go wrong?
> thx
> patrick
|||That's the strange thing no error message occurs. When it is possible to
call this url "http://IP-Adress/HelpDesk/sscesa20.dll"; with my PocketPc and
the SQL Server CE Agent message appears, does then the replication must work?
patrick
|||Not necessarily, all it means is that your Pocket PC can connect to the web
server and the virtual directory. I think it also means that your web server
security is set up correctly. Now check the underlying security of your
snapshot folder. Does this reflect read access for the anonymous account
(IUSER_MachineName, or whatever account you are authenticating with)?
Then you should run profiler to determine how far into the replication
process your pull is going. This will help to trouble shoot further.
What would be really helpful is if you could post the error message your
program is raising.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"pat" <pat@.discussions.microsoft.com> wrote in message
news:732A1E5F-E844-4D0C-B119-B75692989AF7@.microsoft.com...
>
> That's the strange thing no error message occurs. When it is possible
> to
> call this url "http://IP-Adress/HelpDesk/sscesa20.dll"; with my PocketPc
> and
> the SQL Server CE Agent message appears, does then the replication must
> work?
> patrick

Replication Mechanism

Hello,
My Replication works fine when my PocketPc is connected via USB Cable with
the Desktop. Do I have to implement any additional things that the
Replication Mechanism also works with WLAN? (My PocketPc has WLAN). Is the
oRpl.InternetUrl the decisive factor? - because at this url I have stated the
following url: http://machineName/HelpDesk/sscesa20.dll.
thx
robert
That depends.
On your Publisher do this: http://machineName/HelpDesk/sscesa20.dll
I think this gives you a message saying something like "SQL Server Agent CE
Runnning" or something like this.
Then on your Subscriber issue the same query through the Ineternet Exporer
program. You should see the same result.
If you do, you have connectivity and your problem is likely with setting up
security in your script. If you can't you have to configure security on your
publisher.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"robert" <robert@.discussions.microsoft.com> wrote in message
news:770EC375-0C9F-4304-9D33-E03AE7583C48@.microsoft.com...
> Hello,
> My Replication works fine when my PocketPc is connected via USB Cable with
> the Desktop. Do I have to implement any additional things that the
> Replication Mechanism also works with WLAN? (My PocketPc has WLAN). Is the
> oRpl.InternetUrl the decisive factor? - because at this url I have stated
> the
> following url: http://machineName/HelpDesk/sscesa20.dll.
> thx
> robert
|||Okay thank you for your reply, unfortunately I have two more questions:
1)Do I not have to replace the machineName with my IP-Adress of the desktop
when I call this in the IE of the PocketPc?
2)And what can be the problem if this would not work, although with
connectivity of the USB it works?
thx
robert
|||1) You may have to. The pocket pc will attempt to do name resolution on its
own using a local broadcast. If the IP address works and the name doesn't
you have a problem with name resolution.
2) with the USB you have two options in active sync, connecting to the
INternet or work. If you were connected to work it will find your host no
problem as it is using USB. If not, it has to do the same name resolution as
it will using Wireless.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"robert" <robert@.discussions.microsoft.com> wrote in message
news:C6222C3B-F64B-4219-BF2C-2DFEB166EA93@.microsoft.com...
>
> Okay thank you for your reply, unfortunately I have two more questions:
> 1)Do I not have to replace the machineName with my IP-Adress of the
> desktop
> when I call this in the IE of the PocketPc?
> 2)And what can be the problem if this would not work, although with
> connectivity of the USB it works?
> thx
> robert