Sql Public Role: subscription

Showing posts with label subscription. Show all posts

Monday, March 26, 2012

Push works / Pull doesn't

When I set up a pull subscription through the wizard, I get the following error when I try to run the distribution agent:
-->
The subscription to publication '(null)' has expired and does not exist. The step failed.
<--
If I set up a push subscription, everything works fine. Any ideas what would cause this issue?
Thanks in advance!
Greg,
there was an issue with this in SQL 7.0:
http://www.kbalertz.com/Feedback_290054.aspx.
Hopefully this is relevant to your case (;-))
Regards,
Paul
|||Here's more info:
I'm using sql server 2000, sp3 on win2kadv server on both publisher and subscriber. It's a Transaction replication publication, no merge or updating back to the publisher. I set up a very simple database with one table that has a primary key and a varchar
(50) column, and 3 rows to isolate the problem. The snapshot runs quick, but the pull distribution agent keeps getting the before mentioned error. If I set up a push subscription, it works.
Any ideas?
|||Greg,
as far as I can tell, this error is raised from
sp_MShelp_distribution_agentid, which is undocumented. The error message
relates to the independant_agent setting when you created the publication.
Please could you run profiler on the subscriber while you try the pull
subscription to check if this is the case. If so please post up the
arguments sent to the procedure and your script which creates the
publication and I'll see if I can get any further.
Regards,
Paul Ibison
|||I figured it out! I set up aliases rather than using ip addresses and it is now working.
http://support.microsoft.com/?id=321822

push to subscription in non-trusted domain (Sql 2000)

Thanks for your response.
I don't know how to do pass-through authentication across domain
(particularly using SQL2k replication), I thought that would require a
trusted account on both sides.
I have been trying using the sql account and although I can connect to the
subscriber using the account from query tools and em, I keep receiving an
error when my distribution agent tries to connect: "The process could not
connect to Subscriber 'subscriptionservername'"
I've tried it before and after manipulating the Replication Distributor
authentication arguments and the Subscriber is already setup on the
Distributor to use sql authentication (I've reset this login and pwd at
least once for each trial, I'm reasonable sure it is not mistyped). Same
errant response always.
I even once tried populating the data 1st (in case the snapshot folder was
the only issue) but I did not try to populate the MS* subscription tables
which I figured would blow up anyway, but it couldn't connect so I got the
same response.
Got any ideas? Am I missing something?
Please ... Help!
Sorry, this should have been a reply to a different post.
"William Millett" <william.millett@.altivity.com> wrote in message
news:OHhOC68aHHA.4616@.TK2MSFTNGP03.phx.gbl...
> Thanks for your response.
> I don't know how to do pass-through authentication across domain
> (particularly using SQL2k replication), I thought that would require a
> trusted account on both sides.
> I have been trying using the sql account and although I can connect to the
> subscriber using the account from query tools and em, I keep receiving an
> error when my distribution agent tries to connect: "The process could not
> connect to Subscriber 'subscriptionservername'"
> I've tried it before and after manipulating the Replication Distributor
> authentication arguments and the Subscriber is already setup on the
> Distributor to use sql authentication (I've reset this login and pwd at
> least once for each trial, I'm reasonable sure it is not mistyped). Same
> errant response always.
> I even once tried populating the data 1st (in case the snapshot folder
> was the only issue) but I did not try to populate the MS* subscription
> tables which I figured would blow up anyway, but it couldn't connect so I
> got the same response.
> Got any ideas? Am I missing something?
> Please ... Help!
>

push subscription: production environment

may I push a subscription to a server while that 'machine' is running the
application which is accessing the database?
You can, but its not advisable. When you send your subscription the data in
the tables is replaced unless you are doing the nosync option. This
replacement can involve dropping the table and recreating it.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Robert A. DiFrancesco" <bob.difrancesco@.comcash.com> wrote in message
news:e8qmPZP%23EHA.1392@.tk2msftngp13.phx.gbl...
> may I push a subscription to a server while that 'machine' is running the
> application which is accessing the database?
>

Push subscription: "schema and data"

If I make a database-structure change on the Publisher, will the structure
change replicate to the Subscriber (and update the structure there) ?
When pushing a new subscription, the wizard prompts if you would like to
push schema and data. Does 'schema' mean ... the database structure?
Thank you,
Bob
Use the sp_repladcolumn and sp_repldropcolumn stored procedures to replicate
schema changes for existing publications with subscriptions.
When you get the message - push schema and data, the schema does refer to
the table (and other objects) creation scripts - or as you put it database
structure.
"Robert A. DiFrancesco" <bob.difrancesco@.comcash.com> wrote in message
news:eHTYxKGaEHA.3708@.TK2MSFTNGP10.phx.gbl...
> If I make a database-structure change on the Publisher, will the structure
> change replicate to the Subscriber (and update the structure there) ?
> When pushing a new subscription, the wizard prompts if you would like to
> push schema and data. Does 'schema' mean ... the database structure?
> Thank you,
> Bob
>
|||Thank you very much.
Just so that I am very clear, if I had made a manual change to the database
(not using the sp's you have pointed out) and I push out the subscription
without the database and schema because there is an existing database, then
the structure change would NOT replicate. Is this correct?
Thank you.
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:OTqXliGaEHA.2516@.TK2MSFTNGP10.phx.gbl...
> Use the sp_repladcolumn and sp_repldropcolumn stored procedures to
replicate[vbcol=seagreen]
> schema changes for existing publications with subscriptions.
> When you get the message - push schema and data, the schema does refer to
> the table (and other objects) creation scripts - or as you put it database
> structure.
> "Robert A. DiFrancesco" <bob.difrancesco@.comcash.com> wrote in message
> news:eHTYxKGaEHA.3708@.TK2MSFTNGP10.phx.gbl...
structure
>
|||its hard to tell what you mean.
If you make a schema change, and then create a publication and push it to a
subscription, yet the subscribers will get the schema change.
If you attempt to make a schema change to an existing publication and
subscription(s) you will be prevented from doing with an error message like
'This table is published for replication'.
The only way to do this is using the above mentioned replication stored
procedures.
"Robert A. DiFrancesco" <bob.difrancesco@.comcash.com> wrote in message
news:ei7Tb4GaEHA.2488@.tk2msftngp13.phx.gbl...
> Thank you very much.
> Just so that I am very clear, if I had made a manual change to the
database
> (not using the sp's you have pointed out) and I push out the subscription
> without the database and schema because there is an existing database,
then[vbcol=seagreen]
> the structure change would NOT replicate. Is this correct?
> Thank you.
>
>
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:OTqXliGaEHA.2516@.TK2MSFTNGP10.phx.gbl...
> replicate
to[vbcol=seagreen]
database[vbcol=seagreen]
> structure
to
>
|||The situation is this:
I had to delete my publication in order to make a database structure change
at the publisher ( I did get the error you mention).
I then re-created my publication and pushed a new subscription to my
subscriber. But I did not "check" the box to send schema and data.
So my subscriber indeed does not have the database change. This appears to
be my problem.
In the future it would appear I have three options:
1) update the databases at both the publisher and the subscriber.
Indicate that the schema and data do not have to be sent when pushing a new
subscription.
2) update the publisher and push the new database structure along with
the data, with the new subscription (which may take a while over the
Internet)
3) Just use those stored procedures and I am done!
Would you agree? Looks like I'll take curtain number 3...
thank you very much for your time and patience,
bob.
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:%23gb6XBHaEHA.1488@.TK2MSFTNGP09.phx.gbl...
> its hard to tell what you mean.
> If you make a schema change, and then create a publication and push it to
a
> subscription, yet the subscribers will get the schema change.
> If you attempt to make a schema change to an existing publication and
> subscription(s) you will be prevented from doing with an error message
like[vbcol=seagreen]
> 'This table is published for replication'.
> The only way to do this is using the above mentioned replication stored
> procedures.
> "Robert A. DiFrancesco" <bob.difrancesco@.comcash.com> wrote in message
> news:ei7Tb4GaEHA.2488@.tk2msftngp13.phx.gbl...
> database
subscription[vbcol=seagreen]
> then
> to
> database
?[vbcol=seagreen]
like[vbcol=seagreen]
> to
structure?
>
|||yes, I would try option 3.
It seems to me that in the past #3 has bitten me in certain situation like
when you have filters, but I have been able to repro it.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Robert A. DiFrancesco" <bob.difrancesco@.comcash.com> wrote in message
news:OLSBwMHaEHA.1448@.TK2MSFTNGP12.phx.gbl...
> The situation is this:
> I had to delete my publication in order to make a database structure
change
> at the publisher ( I did get the error you mention).
> I then re-created my publication and pushed a new subscription to my
> subscriber. But I did not "check" the box to send schema and data.
> So my subscriber indeed does not have the database change. This appears
to
> be my problem.
> In the future it would appear I have three options:
> 1) update the databases at both the publisher and the subscriber.
> Indicate that the schema and data do not have to be sent when pushing a
new[vbcol=seagreen]
> subscription.
> 2) update the publisher and push the new database structure along with
> the data, with the new subscription (which may take a while over the
> Internet)
> 3) Just use those stored procedures and I am done!
> Would you agree? Looks like I'll take curtain number 3...
> thank you very much for your time and patience,
> bob.
>
>
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:%23gb6XBHaEHA.1488@.TK2MSFTNGP09.phx.gbl...
to[vbcol=seagreen]
> a
> like
> subscription
refer[vbcol=seagreen]
message[vbcol=seagreen]
there)
> ?
> like
> structure?
>

Push Subscription Wizard

Hello,
I am trying to setup a push subscription between two SQL 2000 Servers using
the Push Subscription Wizard through Enterprise Manager. After clicking
"Finish" at the end of the wizard, Enterprise Manager hangs at the first
step "Enabling this server as a subscriber". Can anyone help me identify
the problem, I can't see anything in the logs.
Thanks,
Ben Gibson
Ben,
additionally, you could use sp_who2 and dbcc inputbuffer(spid) as per usual
to get some more info.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Push Subscription not applying Successful Snapshot

Hello;
I have worked my way through numerous connection problems and login
issues, but Im left with one problem Im hoping I can get some help
on:
Ive got an SQL 2000 v 8.00.760 SP3a, with a production database Im
trying to replicate from one workgroup to another (yes, workgroups
ack!) Ive got the connections all worked out, it was a few days of
fun...
Ive setup a transactional replication between a production database
and a blank database.
The database is about 1.5Gb so its a hefty size.
I setup the transactional replication to include an initialization of
the Schema and Data, and asked the wizard to start the snapshot agent
and begin the initialization process immediately.
The Subscription creates and the snapshot is successful.
However, the Push fails with the following error: "Unable to
replicate a view or function because the referenced objects or columns
are not present on the subscriber"
Any help would be greatly appreciated!
1st time poster, Im impressed with the knowledge of the boards
members, but Im new, so go easy on me if its obvious to you :D
EDIT: After looking through the replication, I have noticed that there
are 3x as many tables in the Production database as in the replicated
database, is this the snapshot not pulling properly, or implementing
properly?
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Replication-...ict233874.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=810666
"SNA2" wrote:
> Hello;
> I have worked my way through numerous connection problems and
> login issues, but I'm left with one problem I'm hoping I can
> get some help on:
> I've got an SQL 2000 v 8.00.760 SP3a, with a production
> database I'm trying to replicate from one workgroup to another
> (yes, workgroups ack!) I've got the connections all worked
> out, it was a few days of fun...
> I've setup a transactional replication between a production
> database and a blank database.
> The database is about 1.5Gb so it's a hefty size.
> I setup the transactional replication to include an
> initialization of the Schema and Data, and asked the wizard to
> start the snapshot agent and begin the initialization process
> immediately.
> The Subscription creates and the snapshot is successful.
> However, the Push fails with the following error: "Unable to
> replicate a view or function because the referenced objects or
> columns are not present on the subscriber"
> Any help would be greatly appreciated!
>
> 1st time poster, I'm impressed with the knowledge of the
> board's members, but I'm new, so go easy on me if it's obvious
> to you :D
> EDIT: After looking through the replication, I have noticed
> that there are 3x as many tables in the Production database as
> in the replicated database, is this the snapshot not pulling
> properly, or implementing properly?
On a side note, I did try initially to do a snapshot replication of
this database (before I realized how large it was) and was told by the
wizard that it couldnt do more than 255 columns in a replication...
In my humble opinion thats a little limited considering who would be
doing replications, most of our databases are over 255 columns, is
that normal? Or is there any way you can get around that limit in
replication?
For instance this current database is 46,000 Rows, and about 3,500
Columns. The only way I can think of getting the correct schema &
data over (aside from a knowledgeable answer to my above post?) is
doing a backup & restore to the blank database. Would that work?
|||I suspect you are getting your error, because you are replicating some views
and not the base tables for these views. I would script out the view and
then run them in the subscriber to determine which views are missing the
base tables.
I believe you can only replicate tables less than 255 columns. In SQL 2005
the limit is 1000 column tables.
From BOL entitled Publishing data and database objects -
A table used in a snapshot or transactional publication can have a maximum
of 255 columns and a maximum row size of 8,000 bytes. A table used in a
merge publication can have a maximum of 246 columns and should have a
maximum row size of 6,000 bytes, because conflict-tracking columns may
consume up to 2,000 bytes. If row size exceeds 6,000 bytes in a merge
publication, conflict-tracking meta data may be truncated.
Horizontal, vertical, dynamic, and join filters enable you to create
partitions of data to be published. By filtering published
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
"SNA2" <DoNotEmail@.dbForumz.com> wrote in message
news:4_810668_841eddc87755ed7380f614344396c5b6@.dbf orumz.com...
> "SNA2" wrote:
> On a side note, I did try initially to do a snapshot replication of
> this database (before I realized how large it was) and was told by the
> wizard that it couldn't do more than 255 columns in a replication...
> In my humble opinion that's a little limited considering who would be
> doing replications, most of our databases are over 255 columns, is
> that normal? Or is there any way you can get around that limit in
> replication?
> For instance this current database is 46,000 Rows, and about 3,500
> Columns. The only way I can think of getting the correct schema &
> data over (aside from a knowledgeable answer to my above post?) is
> doing a backup & restore to the blank database. Would that work?
|||"Hilary Cotter3" wrote:
> I suspect you are getting your error, because you are
> replicating some views
> and not the base tables for these views. I would script out
> the view and
> then run them in the subscriber to determine which views are
> missing the
> base tables.
> I believe you can only replicate tables less than 255 columns.
> In SQL 2005
> the limit is 1000 column tables.
> From BOL entitled Publishing data and database objects -
> A table used in a snapshot or transactional publication can
> have a maximum
> of 255 columns and a maximum row size of 8,000 bytes. A table
> used in a
> merge publication can have a maximum of 246 columns and should
> have a
> maximum row size of 6,000 bytes, because conflict-tracking
> columns may
> consume up to 2,000 bytes. If row size exceeds 6,000 bytes in
> a merge
> publication, conflict-tracking meta data may be truncated.
> Horizontal, vertical, dynamic, and join filters enable you to
> create
> partitions of data to be published. By filtering published
>
> --
> 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
> "SNA2" <DoNotEmail@.dbForumz.com> wrote in message
> news:4_810668_841eddc87755ed7380f614344396c5b6@.dbf orumz.com...
>  > > Hello;
>  > >
>  > > I have worked my way through numerous connection
> problems and
>  > > login issues, but I'm left with one problem I'm
> hoping I can
>  > > get some help on:
>  > >
>  > > I've got an SQL 2000 v 8.00.760 SP3a, with a
> production
>  > > database I'm trying to replicate from one workgroup
> to another
>  > > (yes, workgroups ack!) I've got the connections all
> worked
>  > > out, it was a few days of fun...
>  > >
>  > > I've setup a transactional replication between a
> production
>  > > database and a blank database.
>  > > The database is about 1.5Gb so it's a hefty size.
>  > > I setup the transactional replication to include an
>  > > initialization of the Schema and Data, and asked
> the wizard to
>  > > start the snapshot agent and begin the
> initialization process
>  > > immediately.
>  > > The Subscription creates and the snapshot is
> successful.
>  > >
>  > > However, the Push fails with the following error:
> "Unable to
>  > > replicate a view or function because the referenced
> objects or
>  > > columns are not present on the subscriber"
>  > >
>  > > Any help would be greatly appreciated!
>  > >
>  > >
>  > > 1st time poster, I'm impressed with the knowledge
> of the
>  > > board's members, but I'm new, so go easy on me if
> it's obvious
>  > > to you :D
>  > >
>  > > EDIT: After looking through the replication, I have
> noticed
>  > > that there are 3x as many tables in the Production
> database as
>  > > in the replicated database, is this the snapshot
> not pulling
>  > > properly, or implementing properly?
> replication of
> told by the
> replication...
> would be
> columns, is
> limit in
> 3,500
> schema &
> post?) is
> work?
Thank you Hillary! That was exactly the problem. [edit] I thought
Id post the solution I came up with and see if you had any input, see
below:
Im still in a bit of a bind with the Database, I understand that
replication, even with SQL 2005 will be an impossibility with the
database as it exists currently, we are looking at pearing it down and
streamlining it over the next year to bring down the size and the
number of columns, as it is quite a mess. We should be able to bring
it under 1000 columns, so replication can take place.
In the meantime, I have thought up a couple of scenarios to do simple
replication through backup/restore.
The full reason for the replication, is that we have a secure site for
the database, which is the reason for it residing on a workgroup, we
are looking to replicate it over the internet to another secure backup
location, so if (heaven forbid) our main secure site goes down, we can
flip the switch on the secondary location and have a working copy up
to date.
And no, Im not being paranoid, our so-called secure location went
down for a full hour recently... oh joy.
Anyway, the solution we came up with was doing a full backup weekly
and transferring the 1.5gb file over the link on an off-peak time, and
every 2 hours during the week, do a differential backup and send the
smaller differential backup file over as well. (the differential
backup is at the most 10-20mb)
THis gives us the option if something goes wrong at the primary
location to flip the switch and just run the quick differential
restore. It is unfortunately a manual process, so depending on the
timing and how quickly the issue is noticed, sometimes the flip may
take a while.
from SQL BOL I picked up the following example script and have
incorporated it successfully:
BACKUP DATABASE MyNwind
TO MyNwind_1
WITH INIT
GO
-- Wait
-- then create differential
BACKUP DATABASE MyNwind
TO MyNwind_2
WITH DIFFERENTIAL
GO
For ease of use, I have it setup through Enterprise Manager to run it
every 2 hours, however Im curious to find out if its possible to
time-stamp the filename within an SQL script like the above to create
a new file every time instead of writing over the old one? ie,
MyNwind_0624050230.bak or something similar?
THis solution seems to work well in place of the problem I had above
of trying to replicate a database that SQL has issues with if the
column count is too large.
Any thoughts? I was quite happy with this approach, as the
differential backup files are relatively small and transfer over the
net quickly enough as to not cause any bandwidth hogging. As I
mentioned, even after a week of changes with our database, the file
was only 10-20mb at the most. But, as pointed out, the way I have it
setup currently, SQL overwrites the file every time the differential
backup is done.
Thanks! Mucho appreciation.
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Replication-...ict233874.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=815727

Push subscription failing

SQL Server Enterprise Manager encountered errors creating push subscriptions
for the following Subscribers:
JANUS: Error 208: Invalid object name 'sysextendedarticlesview'.
The error above shows up each time that I attempt to push the subscription.
THe view sysextendedarticlesview exist in the publisher database. DOes
anybody know how to fix this thing? THanks
issue this on the subscriber in the subscription database.
create view dbo.sysextendedarticlesview
as
select * from sysarticles
union all
select artid, NULL, creation_script, NULL, description,
dest_object, NULL, NULL, NULL, name, objid, pubid,
pre_creation_cmd, status, NULL, type, NULL,
schema_option, dest_owner from sysschemaarticles
go
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Pete Ocasio" <pete.ocasio@.execupay.com> wrote in message
news:OKRoJYKsEHA.2788@.TK2MSFTNGP09.phx.gbl...
> SQL Server Enterprise Manager encountered errors creating push
subscriptions
> for the following Subscribers:
> JANUS: Error 208: Invalid object name 'sysextendedarticlesview'.
> The error above shows up each time that I attempt to push the
subscription.
> THe view sysextendedarticlesview exist in the publisher database. DOes
> anybody know how to fix this thing? THanks
>
sql

push subscription error Xprepl.dll

I used a working publication as a template to create a publication.
Everything was good
I then opened the propeties and added a push subscribtion and
selected execute snapshot immediately.
The merge snapshot agent errored out.
A review of the session shows the following:
connection to publisher is good
connection to subscriber is good
connection to database good
execute <article>.sc good
fail: can not find DLL Xprepl.dll
Any ideas?
Is this in your binn directory? It is called xprepl.dll or xpcobrepl.dll
there.
I don't have a sql 2000 system here, but can you try this
sp_helpextendedproc and see if any of the procs reference 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
"mj" <mj@.discussions.microsoft.com> wrote in message
news:9F4BBB11-3610-4E9C-B961-554BB322D938@.microsoft.com...
> I used a working publication as a template to create a publication.
> Everything was good
> I then opened the propeties and added a push subscribtion and
> selected execute snapshot immediately.
> The merge snapshot agent errored out.
> A review of the session shows the following:
> connection to publisher is good
> connection to subscriber is good
> connection to database good
> execute <article>.sc good
> fail: can not find DLL Xprepl.dll
>
> Any ideas?
>
|||I will verify this shortly.
2nd question:
does the subscribing server have to be enterprise, standard or does it
matter?
"Hilary Cotter" wrote:

> Is this in your binn directory? It is called xprepl.dll or xpcobrepl.dll
> there.
> I don't have a sql 2000 system here, but can you try this
> sp_helpextendedproc and see if any of the procs reference 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
>
> "mj" <mj@.discussions.microsoft.com> wrote in message
> news:9F4BBB11-3610-4E9C-B961-554BB322D938@.microsoft.com...
>
>
|||Latest update:
my subscriber is running sql server standard edition, service pack 3a
my publisher is running sql server enterprise edition, service pack 4
the subscriber has xprelp.dll in binn directory
and is going to run sp_helpextendedproc and get back.
I will update this link shortly.
Thanks
"mj" wrote:
[vbcol=seagreen]
> I will verify this shortly.
> 2nd question:
> does the subscribing server have to be enterprise, standard or does it
> matter?
>
>
> "Hilary Cotter" wrote:
|||This topology is supported. MS recommends running consistent service packs
between all servers of the same version.
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
"mj" <mj@.discussions.microsoft.com> wrote in message
news:0917B4D9-C17B-43E6-A481-620DAAB5F1FB@.microsoft.com...[vbcol=seagreen]
> Latest update:
> my subscriber is running sql server standard edition, service pack 3a
> my publisher is running sql server enterprise edition, service pack 4
> the subscriber has xprelp.dll in binn directory
> and is going to run sp_helpextendedproc and get back.
> I will update this link shortly.
> Thanks
>
> "mj" wrote:

Push Subscription Connection Fails

I cannot get internet connection to work for push subscription to transactional publication.

Publisher/distributor: SQL Server 2000
Subscriber: SQL Server 2000
Connection: TCP/IP, SQL Authentication. Works fine from SQLEM or Query Analyzer.
Subscription: push, transactional, schema/data already initialized.

When I run distributor agent fails with "The process could not connect to subscriber 'TEST'", error "Server does not exist or access denied".

Agent output does not give anything more specific. It fails after...
Connecting to Subscriber 'TEST'
Connecting to Subscriber 'TEST.REPL'

Profiler shows nothing happening at all.

If change subscriber setup on distributor to use trusted connection instead of SQL Auth it works fine.

Any ideas?OK, so all the replication experts are on vacation this week but I eventually managed to figure it out myself.

When defining subscribers (through 'distributor properties') use the subscriber TCP/IP address directly and not a server alias like I was doing. Silly Billy!

Push Subscription "Deactivated", How can I Activat

I'm a little on the illiterate side on SQL, but I cannot seem to get
my subscription pushed. I'm not sure what details I need to provide
to get help, so I'm having to do it in pieces as I find clues. See
the image below:
http://www.chuckchamblee.com/tcifiles/Image2.jpg
It shows as DEACTIVATED. How do I ACTIVATE it?
You need to run the snapshot agent.
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
"CChamblee" <cchamblee@.telcontrol-dot-com.no-spam.invalid> wrote in message
news:YqydnZQ_S_GxFn3fRVn_vQ@.giganews.com...
> I'm a little on the illiterate side on SQL, but I cannot seem to get
> my subscription pushed. I'm not sure what details I need to provide
> to get help, so I'm having to do it in pieces as I find clues. See
> the image below:
> http://www.chuckchamblee.com/tcifiles/Image2.jpg
> It shows as DEACTIVATED. How do I ACTIVATE it?
>

Push Subscription - will it overwrite existing data first?

If I push a new subscription to an existing target database, and I choose or
check the box to include schema and data, will replication first delete or
empty tables at the target before pushing a new?
I did this, that is to push the subscription and checked the box to send
schema and data and killed the process mid-stream. At the target I found
certain tables to be empty. I am concluding that replication will first
empty or delete tables at the target.
Am I correct?
thanks,
bob
Bob,
on the article properties of a table, on the snapshot tab there is the
option to control this behaviour. By default if you look at the bcp files
per article, they will start with a drop table then a create table and after
that the rows will be inserted. It looks like you caught the process in
between these batches.
HTH,
Paul Ibison

push subscriber in merge replication

I am using merge replication with a push subscription type. I am wondering if the updates of the tables on the subscriber side are push to the publisher from the subscriber or pulled from the subscriber by the publisher when the syncronisation takes place. this makes a big diferrence for me and i can't find the answer to this question anywhere...

if anyone could answer it would be really appreciated

ThanksData Synchronization is performed by the Merge agent, wherever it runs from. In your case it's the Publisher.|||The Merge agent runs on the publisher but my question is : does the merge agent pull information from the subcriber or the subscriber push the information to the merge agent ??|||Well, it's a PULL if you look at it this way. The Agent runs on the Publisher, connects to Subscriber (see the agent's job definition in Scheduled Tasks), etc.sql

Push over the Internet?

Is it possible to have a push subscription for merge replication over the Internet?
If so, how?
If not, does anybody know why not?
Hi,
Have a look into the below article.
http://support.microsoft.com/?id=321822
FYI, I have never tried replication over internet.
Thanks
Hari
MCDBA
"Kyle" <Kyle@.discussions.microsoft.com> wrote in message
news:9B38B0BC-8700-46A9-A456-9432F24CFBAF@.microsoft.com...
> Is it possible to have a push subscription for merge replication over the
Internet?
> If so, how?
> If not, does anybody know why not?
|||Hari,
Thanks for the reply. Since writing my post I have become VERY familiar with that article. From what I understand, you can push over the Internet if the subscriber already has the schema. To get the initial schema, however, the subscriber must have a snap
shot agent that is configured to pull via FTP. While this is not my ideal solution, I may be able to make it work. Some of my subscribers will be using MSDE though so I don't know how well that will work.
Thanks,
Kyle
"Hari" wrote:

> Hi,
> Have a look into the below article.
> http://support.microsoft.com/?id=321822
> FYI, I have never tried replication over internet.
> --
> Thanks
> Hari
> MCDBA
> "Kyle" <Kyle@.discussions.microsoft.com> wrote in message
> news:9B38B0BC-8700-46A9-A456-9432F24CFBAF@.microsoft.com...
> Internet?
>
>
|||you can't push over the internet.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Kyle" <Kyle@.discussions.microsoft.com> wrote in message
news:7682CF86-87D4-4D5B-A9D1-6765278FEA62@.microsoft.com...
> Hari,
> Thanks for the reply. Since writing my post I have become VERY familiar
with that article. From what I understand, you can push over the Internet if
the subscriber already has the schema. To get the initial schema, however,
the subscriber must have a snapshot agent that is configured to pull via
FTP. While this is not my ideal solution, I may be able to make it work.
Some of my subscribers will be using MSDE though so I don't know how well
that will work.[vbcol=seagreen]
> Thanks,
> Kyle
>
> "Hari" wrote:
the[vbcol=seagreen]
|||Hilary,
I clicked on your book link and was disappointed to see that you wrote a book on transactional and snapshot replication but not merge. I then checked out the nwsu site and was delighted to see that you are devoting an ENTIRE book on merge replication. THA
TS GREAT! because so far, I have not been able to find a book that deals with the subject in depth. Usually I just see a chapter on replication that just repeates the Microsoft BOL. Please let me know when it is released.
That said, I HAVE been able to push over the Internet. It's not easy, it's not obvious and it's not well documented but it IS possible. I'm still working on my application but here's what I have learned so far which has enabled me to push over the Interne
t with dynamic filtering:
1. The publication must be saved to an FTP site that is available to the subscribers.
2. You must make a server alias (with Client Network Utility) of the subscriber on the publisher server that MATCHES the actual NetBIOS computer name of the subscriber. It seems dumb that you have to do this but it works.
3. For a dynamic filter to work with a push subscription, you must filter on HOST_NAME() and put a -hostname [somename] parameter in the Run Agent step of the subscription.
If I've got something wrong in my head or you have any other tips, I'd love to hear about it. Also, if I can be of any help with your upcoming book, please let me know.
Thanks,
Kyle
"Hilary Cotter" wrote:

> you can't push over the internet.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Kyle" <Kyle@.discussions.microsoft.com> wrote in message
> news:7682CF86-87D4-4D5B-A9D1-6765278FEA62@.microsoft.com...
> with that article. From what I understand, you can push over the Internet if
> the subscriber already has the schema. To get the initial schema, however,
> the subscriber must have a snapshot agent that is configured to pull via
> FTP. While this is not my ideal solution, I may be able to make it work.
> Some of my subscribers will be using MSDE though so I don't know how well
> that will work.
> the
>
>
|||Hilary,
I clicked on your book link and was disappointed to see that you wrote a book on transactional and snapshot replication but not merge. I then checked out the nwsu site and was delighted to see that you are currently working on a book devoted to merge repl
ication. Selfishly, I'm hoping you don't have good surf conditions for the next few months. Please let me know when it is released.
That said, I HAVE been able to push subscriptions over the Internet. It's not obvious or easy but it IS possible. I'm still working on my application but here's what I have learned so far which has enabled me to push a merge subscription over the internet
with dynamic filtering:
1. The publication must be put on an FTP site that is accessable to the subscribers.
2. The publishing server must have an alias to the subscriber that matches the actual NetBIOS computer name of the subscriber machine. This seems really dumb to me but it's the only way I could get it to work.
3. To enable dynamic filtering with a push subscription, you must filter on HOST_NAME() and you must put a -hostname [somename] parameter in the Run Agent step of the merge agent. This allows you to specify a particular subset of data to each subscriber i
ndependant of the subscriber's actual name or user (although, as I mentioned in point 2, you do have to know the subscriber's computer name).
If I've got something wrong or you have any other tips for me, please let me know. Also, if I can be of any help with your upcoming book, I'd be glad to contribute.
Thanks,
Kyle
"Hilary Cotter" wrote:

> you can't push over the internet.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Kyle" <Kyle@.discussions.microsoft.com> wrote in message
> news:7682CF86-87D4-4D5B-A9D1-6765278FEA62@.microsoft.com...
> with that article. From what I understand, you can push over the Internet if
> the subscriber already has the schema. To get the initial schema, however,
> the subscriber must have a snapshot agent that is configured to pull via
> FTP. While this is not my ideal solution, I may be able to make it work.
> Some of my subscribers will be using MSDE though so I don't know how well
> that will work.
> the
>
>
|||But if FTP is in the mix you are using a pull.
You don't have to register you Netbios name of the Subscriber on the
publisher. It can be any name as long as that name matches the ip address of
your subscriber and this name is entered in your hosts file.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Kyle" <Kyle@.discussions.microsoft.com> wrote in message
news:39C08D3F-D3FC-47E2-BB32-1AB5C04C8919@.microsoft.com...
> Hilary,
> I clicked on your book link and was disappointed to see that you wrote a
book on transactional and snapshot replication but not merge. I then checked
out the nwsu site and was delighted to see that you are devoting an ENTIRE
book on merge replication. THATS GREAT! because so far, I have not been able
to find a book that deals with the subject in depth. Usually I just see a
chapter on replication that just repeates the Microsoft BOL. Please let me
know when it is released.
> That said, I HAVE been able to push over the Internet. It's not easy, it's
not obvious and it's not well documented but it IS possible. I'm still
working on my application but here's what I have learned so far which has
enabled me to push over the Internet with dynamic filtering:
> 1. The publication must be saved to an FTP site that is available to the
subscribers.
> 2. You must make a server alias (with Client Network Utility) of the
subscriber on the publisher server that MATCHES the actual NetBIOS computer
name of the subscriber. It seems dumb that you have to do this but it works.
> 3. For a dynamic filter to work with a push subscription, you must filter
on HOST_NAME() and put a -hostname [somename] parameter in the Run Agent
step of the subscription.
> If I've got something wrong in my head or you have any other tips, I'd
love to hear about it. Also, if I can be of any help with your upcoming
book, please let me know.[vbcol=seagreen]
> Thanks,
> Kyle
> "Hilary Cotter" wrote:
familiar[vbcol=seagreen]
Internet if[vbcol=seagreen]
however,[vbcol=seagreen]
well[vbcol=seagreen]
over[vbcol=seagreen]
|||I see your point about the FTP but I don't think I'm creating a pull subscription. For the initial data... on the publisher I must tell the subscribers how to get to the FTP site (IP, login, password, folder). This is done via the Snapshot Location tab on
the publisher properties form, but this information does not show up anywhere (that I can find) on the subscriber. The subscriber shows that it has one subscription and that it is a push type. From the subscriber, I cannot modify or delete the subscripti
on - meaning that the publisher has complete control (as long as the connection remains in tact). Maybe internally there is an invisible pull subscription but from both sides all I see is push and the publisher maintains complete control (which is what I
want). Also, on the Subscription Options tab of the publication properties, I have un-checked the Allow pull subscriptions checkbox further convincing me that there are no pull subscriptions (even while moving the initial snapshot).
I tried using the lmhosts file instead of registering an alias with the Client Network Utility to map the subscriber IP to a name other than it's actual NetBIOS name. That allowed me to connect and it allowed me to push the initial snapshot. The merge age
nt failed, however, giving me the following error:
The subscription to publication 'TestPush1' is invalid.
(Source: Merge Replication Provider (Agent); Error number: -2147201019)
-----
The remote server is not defined as a subscription server.
(Source: SACRAMENTO (Data source); Error number: 14010)
-----
SACRAMENTO is the name in the lmhost file that is mapped to the subscriber's IP address. It IS defined as a subscription server. The same publication 'TestPush1' works fine with the other subscription (where the alias = the actual NetBIOS name).
This is all pretty much consistent with the KB article 321822 except that the article implies that you can't move the initial schema and data without a pull subscription. It does say, however, that you need to use the actual NetBIOS name although I would
sure like to find a way around this requirement.
Kyle
"Hilary Cotter" wrote:

> But if FTP is in the mix you are using a pull.
> You don't have to register you Netbios name of the Subscriber on the
> publisher. It can be any name as long as that name matches the ip address of
> your subscriber and this name is entered in your hosts file.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Kyle" <Kyle@.discussions.microsoft.com> wrote in message
> news:39C08D3F-D3FC-47E2-BB32-1AB5C04C8919@.microsoft.com...
> book on transactional and snapshot replication but not merge. I then checked
> out the nwsu site and was delighted to see that you are devoting an ENTIRE
> book on merge replication. THATS GREAT! because so far, I have not been able
> to find a book that deals with the subject in depth. Usually I just see a
> chapter on replication that just repeates the Microsoft BOL. Please let me
> know when it is released.
> not obvious and it's not well documented but it IS possible. I'm still
> working on my application but here's what I have learned so far which has
> enabled me to push over the Internet with dynamic filtering:
> subscribers.
> subscriber on the publisher server that MATCHES the actual NetBIOS computer
> name of the subscriber. It seems dumb that you have to do this but it works.
> on HOST_NAME() and put a -hostname [somename] parameter in the Run Agent
> step of the subscription.
> love to hear about it. Also, if I can be of any help with your upcoming
> book, please let me know.
> familiar
> Internet if
> however,
> well
> over
>
>
|||Can you script out your publication so we can see exactly what you have
done. It is possible you did a no sync push which can be done over the
internet.
FTP information is stored on the publisher not the subscriber in SQL 2000,
on SQL 7 IIRC it was also stored on the subcriber. The subscriber connects
to the publisher (using pull) to get ftp information.
Don't modify the lmhosts file, but the hosts file. LMhosts is primarily used
during netbios name resolution and if you are using TCPIP in the client
network utiltity it will use cache-hosts-DNS resolution to get the IP
address. It may then do a broadcast and consult the LMHosts files (IIRC).
You have to enable Sacremento as an enabled subscriber which won't work over
the internet. So you should be using anonymous.
Please contact me offline so we can resolve this quickly and I can get a
handle on what you are doing.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Kyle" <Kyle@.discussions.microsoft.com> wrote in message
news:985B1BF9-2345-4DB0-A803-FF5F4EBA7D80@.microsoft.com...
> I see your point about the FTP but I don't think I'm creating a pull
subscription. For the initial data... on the publisher I must tell the
subscribers how to get to the FTP site (IP, login, password, folder). This
is done via the Snapshot Location tab on the publisher properties form, but
this information does not show up anywhere (that I can find) on the
subscriber. The subscriber shows that it has one subscription and that it is
a push type. From the subscriber, I cannot modify or delete the
subscription - meaning that the publisher has complete control (as long as
the connection remains in tact). Maybe internally there is an invisible pull
subscription but from both sides all I see is push and the publisher
maintains complete control (which is what I want). Also, on the Subscription
Options tab of the publication properties, I have un-checked the Allow pull
subscriptions checkbox further convincing me that there are no pull
subscriptions (even while moving the initial snapshot).
> I tried using the lmhosts file instead of registering an alias with the
Client Network Utility to map the subscriber IP to a name other than it's
actual NetBIOS name. That allowed me to connect and it allowed me to push
the initial snapshot. The merge agent failed, however, giving me the
following error:
> The subscription to publication 'TestPush1' is invalid.
> (Source: Merge Replication Provider (Agent); Error number: -2147201019)
> ----
--
> The remote server is not defined as a subscription server.
> (Source: SACRAMENTO (Data source); Error number: 14010)
> ----
--
> SACRAMENTO is the name in the lmhost file that is mapped to the
subscriber's IP address. It IS defined as a subscription server. The same
publication 'TestPush1' works fine with the other subscription (where the
alias = the actual NetBIOS name).
> This is all pretty much consistent with the KB article 321822 except that
the article implies that you can't move the initial schema and data without
a pull subscription. It does say, however, that you need to use the actual
NetBIOS name although I would sure like to find a way around this
requirement.[vbcol=seagreen]
> Kyle
> "Hilary Cotter" wrote:
address of[vbcol=seagreen]
a[vbcol=seagreen]
checked[vbcol=seagreen]
ENTIRE[vbcol=seagreen]
able[vbcol=seagreen]
a[vbcol=seagreen]
me[vbcol=seagreen]
it's[vbcol=seagreen]
has[vbcol=seagreen]
the[vbcol=seagreen]
computer[vbcol=seagreen]
works.[vbcol=seagreen]
filter[vbcol=seagreen]
via[vbcol=seagreen]
work.[vbcol=seagreen]
replication[vbcol=seagreen]
|||Kyle, thanks for sending me your scripts off line.
Yes! You are successfully doing a push over the internet, and yes, it is
possible to do a push over the internet.
The reason it is working for you is that you are connecting over DSL (I did
a tracert to the IP address you provided me with and found out you are using
pacbell DSL). The reason it is working is that the you have the netbios
ports open. On most corporate internets this is locked down as it is a
security risk.
I assumed you were on a corporate network, or behind a firewall, and that
these ports will be blocked.
So my advise to you is to use a personal firewall, and some form of internet
security software, and migrate to a pull subscription.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:%237wZnlSYEHA.4092@.TK2MSFTNGP11.phx.gbl...
> Can you script out your publication so we can see exactly what you have
> done. It is possible you did a no sync push which can be done over the
> internet.
> FTP information is stored on the publisher not the subscriber in SQL 2000,
> on SQL 7 IIRC it was also stored on the subcriber. The subscriber connects
> to the publisher (using pull) to get ftp information.
> Don't modify the lmhosts file, but the hosts file. LMhosts is primarily
used
> during netbios name resolution and if you are using TCPIP in the client
> network utiltity it will use cache-hosts-DNS resolution to get the IP
> address. It may then do a broadcast and consult the LMHosts files (IIRC).
> You have to enable Sacremento as an enabled subscriber which won't work
over
> the internet. So you should be using anonymous.
> Please contact me offline so we can resolve this quickly and I can get a
> handle on what you are doing.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Kyle" <Kyle@.discussions.microsoft.com> wrote in message
> news:985B1BF9-2345-4DB0-A803-FF5F4EBA7D80@.microsoft.com...
> subscription. For the initial data... on the publisher I must tell the
> subscribers how to get to the FTP site (IP, login, password, folder). This
> is done via the Snapshot Location tab on the publisher properties form,
but
> this information does not show up anywhere (that I can find) on the
> subscriber. The subscriber shows that it has one subscription and that it
is
> a push type. From the subscriber, I cannot modify or delete the
> subscription - meaning that the publisher has complete control (as long as
> the connection remains in tact). Maybe internally there is an invisible
pull
> subscription but from both sides all I see is push and the publisher
> maintains complete control (which is what I want). Also, on the
Subscription
> Options tab of the publication properties, I have un-checked the Allow
pull[vbcol=seagreen]
> subscriptions checkbox further convincing me that there are no pull
> subscriptions (even while moving the initial snapshot).
> Client Network Utility to map the subscriber IP to a name other than it's
> actual NetBIOS name. That allowed me to connect and it allowed me to push
> the initial snapshot. The merge agent failed, however, giving me the
> following error:
> ----
> --
> ----
> --
> subscriber's IP address. It IS defined as a subscription server. The same
> publication 'TestPush1' works fine with the other subscription (where the
> alias = the actual NetBIOS name).
that
> the article implies that you can't move the initial schema and data
without[vbcol=seagreen]
> a pull subscription. It does say, however, that you need to use the actual
> NetBIOS name although I would sure like to find a way around this
> requirement.
> address of
wrote[vbcol=seagreen]
> a
> checked
> ENTIRE
been[vbcol=seagreen]
> able
see[vbcol=seagreen]
> a
let[vbcol=seagreen]
> me
easy,[vbcol=seagreen]
> it's
> has
> the
> computer
> works.
> filter
Agent[vbcol=seagreen]
I'd[vbcol=seagreen]
upcoming[vbcol=seagreen]
pull[vbcol=seagreen]
> via
> work.
how
> replication
>

Friday, March 23, 2012

Push and Pull subscription?

As I understand replication in Sql2K the only difference in push or pull subscriptions is where the agent runs. If I wanted changes in the publisher to be sent to the subscribers immediately after the change then I thought push would be better. But, if I am equally interested in changes made at the subscriber then what should I use? Or does the agent monitor both the publisher and subscriber at the same time?

Thanks for your help

Graham

Graham, assuming you're talking about merge replication, changes will be propagated up/down whenever the agent runs. If you run the agent in continuous mode, then the changes should be sent within the polling period of the agent. If you run the agent in a scheduled mode, or on demand, then the changes will be sent when the agent runs.

Wednesday, March 21, 2012

Purchasing Microsoft SQL Server for small company

I currently have a database through a company that has been upkeeping SQL and
storing data. I am cancelling subscription and want to retrieve data. They
are sending me a backup DVD with all the files. How easy is it to use
Microsoft SQL and can the files be transfered to another program such as
Access or Excel'
ThanksAre you saying that you have data stored in a SQL Server database, which is
hosted by other company, and you now want to sotre your data by yourself? If
so, the best way to do is to set up your own SQL Server, have you data on
the other SQL Server backed up and restore it on your own SQL Server. (or
use detach/attach database from/to SQL Server).
There are different edition of SQL Server, from very expensive Enterprise
Edition, to free version of SQL Server Express/MSDE. If your data value is
not so big (2G or 4G), you can use MSDE or SQL Server Express for free. Of
course, some basic knowledge on SQL Server is required (I mean a bit more
and complicated that required by using Access).
If you want those data being transferred to Access DB, then you can ask your
SQL Server host to export it to a *.mdb file. However, in general, only data
can be transferred, server object used in that database (view, sp... they
may contain critical logics for using the database) would not be
transferred. So, you would need to build a sort of front end app inorder to
use your data. Unless the only thing you need is the data.
"Eddie" <Eddie@.discussions.microsoft.com> wrote in message
news:389AA3B8-1996-487C-A65D-29F4E5404246@.microsoft.com...
>I currently have a database through a company that has been upkeeping SQL
>and
> storing data. I am cancelling subscription and want to retrieve data.
> They
> are sending me a backup DVD with all the files. How easy is it to use
> Microsoft SQL and can the files be transfered to another program such as
> Access or Excel'
> Thanks|||You are taking a big step backward going from SQL Server to MS Access and an
even bigger step if you go to Excel.
Microsoft SQL Server is an enterprise level relational database management
system.
Microsoft Access is a database engine but it is not quite enterprise level.
MS Excel is can be used to display information to management types and
create pretty reports, but it is by no means an enterprise storage
technology.
IF your host sends you the correct information -- backup files created via
the T-SQL BACKUP command OR database and log files after they ran
sp_detach_db -- you should be able to restore the database(s) to a SQL
Server in your environment.
SQL Server does not have "files" (like Excel). It is more similar to MS
Access in that it has tables within one container (a database).
Keith Kratochvil
"Eddie" <Eddie@.discussions.microsoft.com> wrote in message
news:389AA3B8-1996-487C-A65D-29F4E5404246@.microsoft.com...
>I currently have a database through a company that has been upkeeping SQL
>and
> storing data. I am cancelling subscription and want to retrieve data.
> They
> are sending me a backup DVD with all the files. How easy is it to use
> Microsoft SQL and can the files be transfered to another program such as
> Access or Excel'
> Thanks|||The company is not using the system to it's fullest potential. The users are
not computer savvy and would prefer a more simple system ie: Access. They
don't want to be paying the fees associated to a program they don't use.
They have 7 years of data they need to access and I have to figure out how.
Once we access the data and can retrieve the reports and pictures we won't be
using the system. We want to transfer it to Access.
Thanks
"Norman Yuan" wrote:
> Are you saying that you have data stored in a SQL Server database, which is
> hosted by other company, and you now want to sotre your data by yourself? If
> so, the best way to do is to set up your own SQL Server, have you data on
> the other SQL Server backed up and restore it on your own SQL Server. (or
> use detach/attach database from/to SQL Server).
> There are different edition of SQL Server, from very expensive Enterprise
> Edition, to free version of SQL Server Express/MSDE. If your data value is
> not so big (2G or 4G), you can use MSDE or SQL Server Express for free. Of
> course, some basic knowledge on SQL Server is required (I mean a bit more
> and complicated that required by using Access).
> If you want those data being transferred to Access DB, then you can ask your
> SQL Server host to export it to a *.mdb file. However, in general, only data
> can be transferred, server object used in that database (view, sp... they
> may contain critical logics for using the database) would not be
> transferred. So, you would need to build a sort of front end app inorder to
> use your data. Unless the only thing you need is the data.
> "Eddie" <Eddie@.discussions.microsoft.com> wrote in message
> news:389AA3B8-1996-487C-A65D-29F4E5404246@.microsoft.com...
> >I currently have a database through a company that has been upkeeping SQL
> >and
> > storing data. I am cancelling subscription and want to retrieve data.
> > They
> > are sending me a backup DVD with all the files. How easy is it to use
> > Microsoft SQL and can the files be transfered to another program such as
> > Access or Excel'
> >
> > Thanks
>
>|||The company is not using the system to it's fullest potential. The users are
not computer savvy and would prefer a more simple system ie: Access. They
don't want to be paying the fees associated to a program they don't use.
They have 7 years of data they need to access and I have to figure out how.
Once we access the data and can retrieve the reports and pictures we won't be
using the system. We want to transfer it to Access.
Thanks
"Keith Kratochvil" wrote:
> You are taking a big step backward going from SQL Server to MS Access and an
> even bigger step if you go to Excel.
> Microsoft SQL Server is an enterprise level relational database management
> system.
> Microsoft Access is a database engine but it is not quite enterprise level.
> MS Excel is can be used to display information to management types and
> create pretty reports, but it is by no means an enterprise storage
> technology.
>
> IF your host sends you the correct information -- backup files created via
> the T-SQL BACKUP command OR database and log files after they ran
> sp_detach_db -- you should be able to restore the database(s) to a SQL
> Server in your environment.
>
> SQL Server does not have "files" (like Excel). It is more similar to MS
> Access in that it has tables within one container (a database).
>
> --
> Keith Kratochvil
>
> "Eddie" <Eddie@.discussions.microsoft.com> wrote in message
> news:389AA3B8-1996-487C-A65D-29F4E5404246@.microsoft.com...
> >I currently have a database through a company that has been upkeeping SQL
> >and
> > storing data. I am cancelling subscription and want to retrieve data.
> > They
> > are sending me a backup DVD with all the files. How easy is it to use
> > Microsoft SQL and can the files be transfered to another program such as
> > Access or Excel'
> >
> > Thanks
>
>|||Maybe SQL Server Express combined with MS Access front-ends is the answer.
SS Express is free.
Randall Arnold
"Eddie" <Eddie@.discussions.microsoft.com> wrote in message
news:49490C53-3ADC-418F-8E8F-A7468A98DEE4@.microsoft.com...
> The company is not using the system to it's fullest potential. The users
> are
> not computer savvy and would prefer a more simple system ie: Access. They
> don't want to be paying the fees associated to a program they don't use.
> They have 7 years of data they need to access and I have to figure out
> how.
> Once we access the data and can retrieve the reports and pictures we won't
> be
> using the system. We want to transfer it to Access.
> Thanks
> "Keith Kratochvil" wrote:
>> You are taking a big step backward going from SQL Server to MS Access and
>> an
>> even bigger step if you go to Excel.
>> Microsoft SQL Server is an enterprise level relational database
>> management
>> system.
>> Microsoft Access is a database engine but it is not quite enterprise
>> level.
>> MS Excel is can be used to display information to management types and
>> create pretty reports, but it is by no means an enterprise storage
>> technology.
>>
>> IF your host sends you the correct information -- backup files created
>> via
>> the T-SQL BACKUP command OR database and log files after they ran
>> sp_detach_db -- you should be able to restore the database(s) to a SQL
>> Server in your environment.
>>
>> SQL Server does not have "files" (like Excel). It is more similar to MS
>> Access in that it has tables within one container (a database).
>>
>> --
>> Keith Kratochvil
>>
>> "Eddie" <Eddie@.discussions.microsoft.com> wrote in message
>> news:389AA3B8-1996-487C-A65D-29F4E5404246@.microsoft.com...
>> >I currently have a database through a company that has been upkeeping
>> >SQL
>> >and
>> > storing data. I am cancelling subscription and want to retrieve data.
>> > They
>> > are sending me a backup DVD with all the files. How easy is it to use
>> > Microsoft SQL and can the files be transfered to another program such
>> > as
>> > Access or Excel'
>> >
>> > Thanks
>>|||Eddie,
You can certainly use Access as the front-end program for
your users, even if you maintain the data in SQL Server.
SQL Server in fact has no front-end, really - just a set of
management tools, so you have many options for your
users.
Since no one has mentioned it, don't overlook the Workgroup
Edition of SQL Server, which might be good for your needs.
http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx
Steve Kass
Drew University
Eddie wrote:
>The company is not using the system to it's fullest potential. The users are
>not computer savvy and would prefer a more simple system ie: Access. They
>don't want to be paying the fees associated to a program they don't use.
>They have 7 years of data they need to access and I have to figure out how.
>Once we access the data and can retrieve the reports and pictures we won't be
>using the system. We want to transfer it to Access.
>Thanks
>"Norman Yuan" wrote:
>
>>Are you saying that you have data stored in a SQL Server database, which is
>>hosted by other company, and you now want to sotre your data by yourself? If
>>so, the best way to do is to set up your own SQL Server, have you data on
>>the other SQL Server backed up and restore it on your own SQL Server. (or
>>use detach/attach database from/to SQL Server).
>>There are different edition of SQL Server, from very expensive Enterprise
>>Edition, to free version of SQL Server Express/MSDE. If your data value is
>>not so big (2G or 4G), you can use MSDE or SQL Server Express for free. Of
>>course, some basic knowledge on SQL Server is required (I mean a bit more
>>and complicated that required by using Access).
>>If you want those data being transferred to Access DB, then you can ask your
>>SQL Server host to export it to a *.mdb file. However, in general, only data
>>can be transferred, server object used in that database (view, sp... they
>>may contain critical logics for using the database) would not be
>>transferred. So, you would need to build a sort of front end app inorder to
>>use your data. Unless the only thing you need is the data.
>>"Eddie" <Eddie@.discussions.microsoft.com> wrote in message
>>news:389AA3B8-1996-487C-A65D-29F4E5404246@.microsoft.com...
>>
>>I currently have a database through a company that has been upkeeping SQL
>>and
>>storing data. I am cancelling subscription and want to retrieve data.
>>They
>>are sending me a backup DVD with all the files. How easy is it to use
>>Microsoft SQL and can the files be transfered to another program such as
>>Access or Excel'
>>Thanks
>>
>>|||So, summarize what you want:
1. You have data stored in database of someone's SQL Server;
2. You need to get all data out in this database and svae them in some
formats you choose;
3. You do not need application related server objects/logics in the database
(views, stored procedures, UDFs) once you retrieved the data;
4. The SQL Server host would not do the data retrieving for you. They can
only give you the backup or data file of the database.
Since you want to transfer data from SQL Server to Access (more accurately,
Jet database), there may be some degree of data or data type or data
accuracy loss because of the differences of the two data engine.
Here is what I would do:
1. Get full backup or detached database file (latter would be better);
2. Install a MSDE, which is free version of SQL Server, if the data file of
the database (*.mdf) is less than 2GB.
3. Restore/attach the databse to MSDE.
4. Use Access as tool to connect to MSDE and start retrieve data. You can
create an Access ADP project to use as MSDE front end UI. Then you can open
each table to export, or write some macro/VBA code to retireve specific data
and output to specific format.
5. Since I have the database on my onw, no-cost MSDE engine, I can try and
try until satisfied totally (of course do it on a copy of the data).
6, Do the final retrieving and then uninstall the MSDE.
"Eddie" <Eddie@.discussions.microsoft.com> wrote in message
news:E426270B-5851-456C-B190-1D747BAFA433@.microsoft.com...
> The company is not using the system to it's fullest potential. The users
> are
> not computer savvy and would prefer a more simple system ie: Access. They
> don't want to be paying the fees associated to a program they don't use.
> They have 7 years of data they need to access and I have to figure out
> how.
> Once we access the data and can retrieve the reports and pictures we won't
> be
> using the system. We want to transfer it to Access.
> Thanks
> "Norman Yuan" wrote:
>> Are you saying that you have data stored in a SQL Server database, which
>> is
>> hosted by other company, and you now want to sotre your data by yourself?
>> If
>> so, the best way to do is to set up your own SQL Server, have you data on
>> the other SQL Server backed up and restore it on your own SQL Server. (or
>> use detach/attach database from/to SQL Server).
>> There are different edition of SQL Server, from very expensive Enterprise
>> Edition, to free version of SQL Server Express/MSDE. If your data value
>> is
>> not so big (2G or 4G), you can use MSDE or SQL Server Express for free.
>> Of
>> course, some basic knowledge on SQL Server is required (I mean a bit more
>> and complicated that required by using Access).
>> If you want those data being transferred to Access DB, then you can ask
>> your
>> SQL Server host to export it to a *.mdb file. However, in general, only
>> data
>> can be transferred, server object used in that database (view, sp... they
>> may contain critical logics for using the database) would not be
>> transferred. So, you would need to build a sort of front end app inorder
>> to
>> use your data. Unless the only thing you need is the data.
>> "Eddie" <Eddie@.discussions.microsoft.com> wrote in message
>> news:389AA3B8-1996-487C-A65D-29F4E5404246@.microsoft.com...
>> >I currently have a database through a company that has been upkeeping
>> >SQL
>> >and
>> > storing data. I am cancelling subscription and want to retrieve data.
>> > They
>> > are sending me a backup DVD with all the files. How easy is it to use
>> > Microsoft SQL and can the files be transfered to another program such
>> > as
>> > Access or Excel'
>> >
>> > Thanks
>>

Tuesday, March 20, 2012

Pull:shared agent

I had a problem and lost, with no backups, a couple of data bases.
When I was recreating the subscriptions, a subscription just appeared
the name is 'ServerName:database' & the type is 'Pull:shared
agent'. I cannot delete the publication, I get the message 'SQL
Server Enterprise Manager could not retrieve information about
subscription ServerName:database' Start, Stop & job History are all
grayed out on the right click.
Now the questions...
What is a Pullshared agent?
How can I delete this?
What is in this subscription?
TIA,
Larry...
If you are not using the independent agent option and you have two
subscriptions to the same publication database, the two subscriptions will
share a single distribution agent.
You should be able to delete it by using exec sp_dropmergepullsubscription
or sp_droppullsubscription. Fill in the details by referencing the
MSsubscription_properties table in your subscription database.
This subscription is really very similar to any other subscription only
there are two of them

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
"LPR-3rd" <lreames@.gmail.com> wrote in message
news:1130782861.818300.175690@.z14g2000cwz.googlegr oups.com...
> I had a problem and lost, with no backups, a couple of data bases.
> When I was recreating the subscriptions, a subscription just appeared
> the name is 'ServerName:database' & the type is 'Pull:shared
> agent'. I cannot delete the publication, I get the message 'SQL
> Server Enterprise Manager could not retrieve information about
> subscription ServerName:database' Start, Stop & job History are all
> grayed out on the right click.
> Now the questions...
> What is a Pullshared agent?
> How can I delete this?
> What is in this subscription?
>
> TIA,
> Larry...
>
|||Where is this decided independent agent or shared agent? I have
created many many agents and never recall seeing this..

Monday, March 12, 2012

Pull Subscriptions not working at all

I cannot get a pull subscription to work at all, push works, anyone have any suggestions as to why?
I currently have 1 subscription pulling from this server, but I can't add any new pull subscriptions, I receive an error message:
From EM, on Choose Publication (Using Wizard) - Click the + next to the server I want to pull from
Could not retrieve information about publications from Publisher
Error 208: Invalid object name 'sysextendedarticlesview'
I do see this view in some of the db's that are currently replicating, but I'm not sure of where it's looking for the view in this particular instance, as I have not gotten to the point of selecting a publication.
Did my Distribution db become corrupt in some way?
Any suggestions will be greatly appreciated.
You should find this view in the subscriber. You can copy it from another
database of the same version to this one. Open up the database in EM, locate
the view, right click on it and select copy. Then paste it into QA on the
subscriber.
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
"JLS" <jlshoop@.hotmail.com> wrote in message
news:O8h6N1wCGHA.344@.TK2MSFTNGP11.phx.gbl...
I cannot get a pull subscription to work at all, push works, anyone have any
suggestions as to why?
I currently have 1 subscription pulling from this server, but I can't add
any new pull subscriptions, I receive an error message:
From EM, on Choose Publication (Using Wizard) - Click the + next to the
server I want to pull from
Could not retrieve information about publications from Publisher
Error 208: Invalid object name 'sysextendedarticlesview'
I do see this view in some of the db's that are currently replicating, but
I'm not sure of where it's looking for the view in this particular instance,
as I have not gotten to the point of selecting a publication.
Did my Distribution db become corrupt in some way?
Any suggestions will be greatly appreciated.
|||I did as you instructed & recreated this view & other missing objects on each subscribing db. It worked, I now see the publications to pull.
Thanx! You're the best!!!!
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:%23fBQ6y1CGHA.344@.TK2MSFTNGP11.phx.gbl...
You should find this view in the subscriber. You can copy it from another
database of the same version to this one. Open up the database in EM, locate
the view, right click on it and select copy. Then paste it into QA on the
subscriber.
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
"JLS" <jlshoop@.hotmail.com> wrote in message
news:O8h6N1wCGHA.344@.TK2MSFTNGP11.phx.gbl...
I cannot get a pull subscription to work at all, push works, anyone have any
suggestions as to why?
I currently have 1 subscription pulling from this server, but I can't add
any new pull subscriptions, I receive an error message:
From EM, on Choose Publication (Using Wizard) - Click the + next to the
server I want to pull from
Could not retrieve information about publications from Publisher
Error 208: Invalid object name 'sysextendedarticlesview'
I do see this view in some of the db's that are currently replicating, but
I'm not sure of where it's looking for the view in this particular instance,
as I have not gotten to the point of selecting a publication.
Did my Distribution db become corrupt in some way?
Any suggestions will be greatly appreciated.

pull subscription, subscriber configuration

Hi,
I was trying a pull subscription and I got all kinds of error messages
all suggesting that the merge agent isn't being run or it isn't
accessible. But, the moment I did a push subscription, it ran and
replication happened without an error message. I don't understand why.
My subscriber is an ordinary windows 2000 OS and is not of a server
configuration. Could that be a reason, since, in a pull subscription,
the agent is supposed to run on the subscriber(which isn't a server)?
Any suggestions or thoughts are welcome!
Also, is there a way a subscriber can tell the publisher to push a
subscription?
Thanks,
PS.
By definition a pull subscriber "pulls" the subscription on its own schedule - it doesn't tell the publisher push now.
Its hard to figure out exactly what the error is from your description. Please provide the complete error messages you are getting.
In general when you have pull subscription problems.
1) check that the SQL Server agent account has rights to read the snapshot share and the underlying path on the publisher, likely to be \\publisherName\C$\Program Files\Microsoft SQL Server\MSSQL\ReplData\unc
2) check on your publisher that you have enabled your subscriber to impersonate the Server agent account on the Publisher. Go to Tools, point to replication, click configure publishers, subscribers, and distributors, click on the browse button to the righ
t of your subscriber and select the impersonate option.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"ps" wrote:

> Hi,
> I was trying a pull subscription and I got all kinds of error messages
> all suggesting that the merge agent isn't being run or it isn't
> accessible. But, the moment I did a push subscription, it ran and
> replication happened without an error message. I don't understand why.
> My subscriber is an ordinary windows 2000 OS and is not of a server
> configuration. Could that be a reason, since, in a pull subscription,
> the agent is supposed to run on the subscriber(which isn't a server)?
> Any suggestions or thoughts are welcome!
> Also, is there a way a subscriber can tell the publisher to push a
> subscription?
> Thanks,
> PS.
>

Pull Subscription, Merge Replication

Hi,
I was trying to pull a subscription to an MSDE machine(subscriber)
from a sql server 2000 machine acting as the publisher and
distributor. when I configure for the pull, it generates a snapshot
immediately which is successfully completed. But then, the pull
actually never happens and I get the message saying the pull
subscription has 'Never Started'. I found that, if the name of the job
exceeds 100 characters, then the problem arises, but my job name is
very small and hence, I dont think that is my problem. I tried to
manually start and reinitialize all the agents, but that does not
solve my problem.
Can someone suggest what else could be done?
Thanks
PS.
you will get long job names when you are using a pull subscription, and
short names when you are doing a push subscription.
can you enable logging to see what it reports. Follow the steps in this kb
article to enable logging.
http://support.microsoft.com/default...b;EN-US;312292
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"ps" <ps_sr2001@.yahoo.com> wrote in message
news:1a7be36d.0406131046.2aa8d35e@.posting.google.c om...
> Hi,
> I was trying to pull a subscription to an MSDE machine(subscriber)
> from a sql server 2000 machine acting as the publisher and
> distributor. when I configure for the pull, it generates a snapshot
> immediately which is successfully completed. But then, the pull
> actually never happens and I get the message saying the pull
> subscription has 'Never Started'. I found that, if the name of the job
> exceeds 100 characters, then the problem arises, but my job name is
> very small and hence, I dont think that is my problem. I tried to
> manually start and reinitialize all the agents, but that does not
> solve my problem.
> Can someone suggest what else could be done?
> Thanks
> PS.
|||Hi Hilary,
Thanks for the response. Well, I went to the agent, right clicked on
it and went to 'Agent Properties'. under 'Steps' tab, I found 'Run
Agent' for which I clicked on Edit and appended '-Output
C:\Repl_out.txt -Outputverboselevel 2'.
Upon starting the agent, I am not finding any such file or if I create
the file myself, the file is empty.
Also, I am getting an error in the subscriber under subscriptions
saying "The process could not connect to Distributor 'Name'. Login
failed for user". The subscriber is listed under the 'Remote Servers'
of the publisher. Is there anything else that could be done?
Thanks,
PS
"Hilary Cotter" <hilaryk@.att.net> wrote in message news:<#0A$ylbUEHA.4064@.TK2MSFTNGP11.phx.gbl>...[vbcol=seagreen]
> you will get long job names when you are using a pull subscription, and
> short names when you are doing a push subscription.
> can you enable logging to see what it reports. Follow the steps in this kb
> article to enable logging.
> http://support.microsoft.com/default...b;EN-US;312292
>
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "ps" <ps_sr2001@.yahoo.com> wrote in message
> news:1a7be36d.0406131046.2aa8d35e@.posting.google.c om...
|||Hi,
From the verbose mode 2, I got this output. This is the last part of
the log:
A snapshot of 1 article(s) was generated.
Disconnecting from Publisher '********'
Microsoft SQL Server Snapshot Agent 8.00.760
Copyright (c) 2000 Microsoft Corporation
Microsoft SQL Server Replication Agent: *************-Repl-5
Connecting to Distributor '*********'
Connecting to Publisher '*************'
But in the subscriber database, under subscriptions, I got a message
stating:
"SQL Server Agent could not access the replication agent. Use the
DCOMCNFG utility".
I have SP3a installed on both publisher and subscriber, hence it
should not be the bug because of it.
Upon refreshing the same, I got a message stating - "The step did not
generate any output. The step failed".
Any ideas or suggestions will be greatly appreciated!
Thanks,
PS.
"Hilary Cotter" <hilaryk@.att.net> wrote in message news:<#0A$ylbUEHA.4064@.TK2MSFTNGP11.phx.gbl>...[vbcol=seagreen]
> you will get long job names when you are using a pull subscription, and
> short names when you are doing a push subscription.
> can you enable logging to see what it reports. Follow the steps in this kb
> article to enable logging.
> http://support.microsoft.com/default...b;EN-US;312292
>
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "ps" <ps_sr2001@.yahoo.com> wrote in message
> news:1a7be36d.0406131046.2aa8d35e@.posting.google.c om...

Monday, March 26, 2012

Push works / Pull doesn't

push to subscription in non-trusted domain (Sql 2000)

push subscription: production environment

Push subscription: "schema and data"

Push Subscription Wizard

Push Subscription not applying Successful Snapshot

Push subscription failing

push subscription error Xprepl.dll

Push Subscription Connection Fails

Push Subscription "Deactivated", How can I Activat

Push Subscription - will it overwrite existing data first?

push subscriber in merge replication

Push over the Internet?

Friday, March 23, 2012

Push and Pull subscription?

Wednesday, March 21, 2012

Purchasing Microsoft SQL Server for small company

Purchasing Microsoft SQL Server for small company

Tuesday, March 20, 2012

Pull:shared agent

Monday, March 12, 2012

Pull Subscriptions not working at all

pull subscription, subscriber configuration

Pull Subscription, Merge Replication

Sql Public Role

Blog Archive

About Me