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...
> &nbsp;> > Hello;
> &nbsp;> >
> &nbsp;> > I have worked my way through numerous connection
> problems and
> &nbsp;> > login issues, but I'm left with one problem I'm
> hoping I can
> &nbsp;> > get some help on:
> &nbsp;> >
> &nbsp;> > I've got an SQL 2000 v 8.00.760 SP3a, with a
> production
> &nbsp;> > database I'm trying to replicate from one workgroup
> to another
> &nbsp;> > (yes, workgroups ack!) I've got the connections all
> worked
> &nbsp;> > out, it was a few days of fun...
> &nbsp;> >
> &nbsp;> > I've setup a transactional replication between a
> production
> &nbsp;> > database and a blank database.
> &nbsp;> > The database is about 1.5Gb so it's a hefty size.
> &nbsp;> > I setup the transactional replication to include an
> &nbsp;> > initialization of the Schema and Data, and asked
> the wizard to
> &nbsp;> > start the snapshot agent and begin the
> initialization process
> &nbsp;> > immediately.
> &nbsp;> > The Subscription creates and the snapshot is
> successful.
> &nbsp;> >
> &nbsp;> > However, the Push fails with the following error:
> "Unable to
> &nbsp;> > replicate a view or function because the referenced
> objects or
> &nbsp;> > columns are not present on the subscriber"
> &nbsp;> >
> &nbsp;> > Any help would be greatly appreciated!
> &nbsp;> >
> &nbsp;> >
> &nbsp;> > 1st time poster, I'm impressed with the knowledge
> of the
> &nbsp;> > board's members, but I'm new, so go easy on me if
> it's obvious
> &nbsp;> > to you :D
> &nbsp;> >
> &nbsp;> > EDIT: After looking through the replication, I have
> noticed
> &nbsp;> > that there are 3x as many tables in the Production
> database as
> &nbsp;> > in the replicated database, is this the snapshot
> not pulling
> &nbsp;> > 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 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!

Pure MS-DOS Connection to SQL-Server?

Hi,

I want to use pure MS-DOS-Handhelds for warehouse keeping. Therefore I would like to get a connection to our SQL-Server from MS-DOS. Unfortunately, ISQL or OSQL just work in a DOS-Box from Windows with ODBC or ADO installed.

Is there a possibility or a program that I can use to question our database from a DOS-program?

Thanks in advance

-Oliver-You will need something to handle the datasteam if you connect from the handheld - the equivalent of mssqlrv32.

Another option is to connect to something else on the server - like a com object which then handles the database access - bit like iis and mts.

I'm sure there are plenty of ways of doing this if you could find someone who knows how to code in dos.

A not very nice way would be to put the create a text file on the server with the request. A task runs on the server looking for these text files, executes the request and creates another text file with the result - the handheld opens that text file and reads the result.|||isql & osql use DB-Library to connect to SQL Server. I have used this sollution (may years ago) to connect to Sybase on Win 3.1, Should work just fine on MS-DOS/SQL Server.sql

pulling my hair out!

I'm trying to do a simple DTS data transfer from an ODBC connection to a fox pro database to MS SQL 2000.

THe package runs fine, data moves over.

HOwever, one of the tables contains what used to be sales dollars from the originating fox pro table which is a currency data type.

the sql table is using a money data type.

what's happening, i finally noticed, is that all negative dollars are being translated into 0 values.

i've tried making the sql table a decimal data type, i've switch the vfpodbc.dll from the June 2003 version to the 1999 version and it's all the same.

when i connect to the fox pro database using the same driver, it shows the negative values in the db. yet when I run (select * from sales where salesamount <0) there are no negative values at all. there either positive or zero.

i'm going to simply scream i'm so freakin frustrated. why the heck in 2003 cant we exchange a simple currency data element from a **MICROSOFT** product to a **MICROSOFT** product.

Sorry for the tone. It's crap like this that adds to project time. I've spent 8 hours now, one whole developer day trying to fix. Of course, Microsoft no longer provides an easily downloadeable driver upgrade. It's missing from there site. So finding the latest fox pro driver is not an easy task.

Trying to download and install MDAC 2.8 says right on the download page that it does not include OLEDB and ODBC drivers any longer. Well what the heck good is that? Great...we get the latest ado or ado.net updates. Whoopee! Retards! You need the drivers to utilize ADO or ADO.NET...HELLO!

sorry again guys, but i'm about to go postal. i just hate this stupid crap. And I hate it when I peruse MS KB and it doesn't come up with crap. never does. have to use google. Perhaps MS should buy out google and replace there search technology to something that actually WORKS.

So, now that i've had my little anit-microsoft tired, is there any one who would be willing to lend any advice about why negative currency values are being translated to 0 values from a foxpro currency type to a microsoft sql decimal or money data type?are you using the SQL 2000 Import/Export tool?

you might need to delve into the 'transform' dialog and do a manual CAST on the field.

I've had to do something broadly similar for the last month because some genius in an [unnamed] government department thinks that my data needs are based on some guy from Ballarat inputting 125000 fieldsby hand. Why the [****] you'd do unnecessary casts or processing in exporting data to go from one SQL Server database to another I really will never, ever know. I guess stupidity never goes out of fashion, but it has to be dealt with.

In your case, some pre-processing may be the thing. going from one platform from another may screw up by default but can be conquered by DTS in the vast majority of cases.

j|||As Atrzx mentioned, you may have to do an ActiveX Script on your transform of that data element. Since it's vbscript, something like
DTSDestination("elem_id") = CCurr(DTSSource("Current Element ID"))

might do the trick.