Only Small Subset Of Rows Replicates To MySQL

May 1, 2007

I have been messing around with trying to replicate sql server 2000 tables to postgres as well as mySQL. I'm starting with just one table and trying to get that working, it has around 9000 rows, all of which copy to postgres (with some datatype issues).

However, when I try to replicate to a mySQL database however, only the same 152 rows get copied every time.

I compared rows that were copied to ones that were not to see if there was any obvious differences between the two and couldn't find any.

View 1 Replies


ADVERTISEMENT

Returning A Subset Of Rows From A Query

Apr 19, 2001

This question has been posted on the site before but I could not find any resolution....I want to return rows 11 - 20 from a query that returns 100 records without using a cursor or temp table.

The closest query I have found is a query that numbers the rows, but I can't seem to use rownumber in a between clause...

Use Pubs
SELECT emp_id, lname, fname, job_id,
(SELECT COUNT(*) FROM employee e2 WHERE e2.emp_id <= e.emp_id AND e2.job_id = 10) AS rownumber
FROM employee e
WHERE job_id = 10
ORDER BY emp_id

Thanks,

Jim

View 1 Replies View Related

Most Efficient Way To Count Subset Of Rows In A Table?

Sep 14, 2005

Hi all,
I’ve a table with production objects, and another with possible items composition of the object. I need to count how many occurrences of each standard composition appears:
Table PROD:
PROD_ID    COMPONENT   TYPE
--------- ----------- -----
1         AAA         X1         BBB         Y2         AAA         X3         BBB         Y4         AAA         Y5         AAA         X5         BBB         YTable ITEM_COMPITEM_ID   COMPONENT   TYPE--------  ---------   -----7         AAA         X7         BBB         Y8         AAA         X9         BBB         Y
The result should be:
ITEM_ID   OCCURRENCES--------  ----7         28         19         1
Table PROD have millions of rows, my way is too slow (I’ve a loop where each PROD object are separately queried against the ITEM_COMP), some have an idea for a most efficient way?
 
ThX
 
NeuralC
 
 

View 2 Replies View Related

Not Able To Retrieve Rows From Small Table-So Many Time For So Long

Mar 8, 2005

Dear Participants,

We are using merge replication for multi locational database but we are facing one problem in only one table which is not included in replication-

Table name is xxxxmast has only 39 row static information, but it used by every users for all task as select only information from this table like-

Select fin_year into mem_variable from xxxxmast where co_name = :global_variable
go

Code validation from here only.

Right validation from here only.

Report retrival validation from here only.

It means its usage for select from every user frequentely for so many times but we have to only fetch information from this table.

It was working prior fine but rightnow get problem for while-

Today Dated 08-March this table not accessible fro three times in eight hours-

1st time for 10 minute.

2nd time 10 minutes

3rd time 52 minutes.

Users want to login but at the login time years and other validation from this table, so users awaited for above mention time.

We had have do following by yesterday-

Drop table xxxxmast.

Create table xxxxmast.

Insert required data.


This is realy trouble for our application.

Any help realy great for us.

Thanks

R.Mall

View 3 Replies View Related

How To I Enter A Small Square Or A Small Circle In A NVARCHAR Field?

Dec 25, 2006

I want to store a small cirle in a text field. Can anyone tell me how I can enter it in ascii code.

Thanks

View 4 Replies View Related

Update Rows From MSSQL To MySQL

Feb 7, 2004

Is there any way where I can add the MySQL Server as a Linked server to MS SQL server?
Is there any way where I can update a table in MySQL server based because a row was updated in MS SQL server using a trigger?

Thanks,
Amit

View 1 Replies View Related

How To Get Result From Query By Group Of (n) Rows Like LIMIT In MYSql

Sep 23, 1999

How to get result from Query by group of (n) rows ?

- Is cursors the better way (and the only) ?

-Is there something as ROWNUM in Oracle or LIMIT in MySql ?

Example plz !

Thanks.

Alex

View 3 Replies View Related

Trigger-assigned Value Changes When Row Replicates - Why?

Feb 14, 2008

Greetings,

We have a standard audit trigger on one of our tables. The trigger type is "after insert, update". The trigger populates four table columns, telling us the login and time the row was created and last updated.

We use replication to synchronize three servers. The trigger specifies "not for replication" on all servers.

The code used to grab the identity and time of the last update is shown below.


LastEditedDate = GETUTCDATE(),

LastEditedBy = SUSER_SNAME()

What I observe is that the LastEditedBy value is sometimes different for the same row on different servers even though the time value is identical. I don't know how to explain this. It looks like the login value is being recalculated during replication while the edit time is not.

For the record, the correct login will be something like "MYDOMjoe" while the incorrect value on another server will read "NT AUTHORITYNETWORK SERVICE". Do these symptoms ring a bell with anyone?

Thanks,
BCB

View 5 Replies View Related

DDL Still Replicates Even With DDL Replication Off (@replicate_ddl = 0).

Mar 29, 2007

There are times when we need to make DDL changes and have them NOT replicate to the subscription database. The documented approach is to disable DDL replication using sp_changepublication (replicate_ddl = 0). See http://msdn2.microsoft.com/en-us/ms147331.aspx



However, our DDL still replicates even when following those steps. I've even tried the very simple steps below to reproduce the error.

Set up transactional replication of one small test table. Replication is working fine.
Set @replicate_ddl = 0 with sp_changepublication. I manually verify the setting (using the GUI) before continuing.
Run a simple alter on the publication DB; change one column from varchar(25) to varchar(30). "ALTER TABLE <tablename> ALTER COLUMN <colname> VARCHAR(30) NULL".
The DDL still replicates. The subscription table still has the column altered to the new definition.

I've even tried manually setting the option in the GUI and also stopping and restarting the synchronization after changing the setting; nothing works.



We are using SQL Server 2005 and just upgraded to SP2 a few days ago.



I have not found any other articles/postings on the topic so it appears we are the only ones having this problem. If anyone has any advice or input, it is greatly appreciated.



Regards,

Adam

View 4 Replies View Related

What Replicates?? Whole Row Or Just Updated Columns??

Oct 12, 2007

I was asked this question due to performance issues..

If you update a 1 or two columns in table, what gets replicated?? Is it the whole row or just those columns that were updated??

I am trying to find this answer cause it will have an effect on the way we do some updates in the future.
Thanks...

also if you have any links to information about this that would be great.

View 1 Replies View Related

Do Not Replicate DELETE Statements Still Replicates Deletes To Subscribers

Dec 4, 2006

I previously asked What does "Do not replicate DELETE statements" do? to make sure I was correct on my thinking of what "Do not replicate DELETE statements" does. And after finding out it does what I would like it to do, create an archive db, I've tried several testing scenarios to see if it would work. So far I have been unable to not replicate delete statements. I'm not sure if I'm not setting a property right or what and any guidance would be appreciated.

Here is what I've done.

Created a blank db to be used as a subscriber and created a test db with some random data in a table.
Setup a New Publication on the db with the random data as "Transactional Publication"
Selected the following articles and properties in the publication

Tables

DELETE Delivery Format = Do not replicate DELETE statements
Views

Default Values
Stored Procedures

Default Values
User Functions

Default Values
Selected the default options for the rest of the New Publication Wizard steps and clicked finish.
Created a Pull Subscription on the new publication that I just created.
Let it initialize.
Then did a select count(*) query on the test table on the publication (18k+ rows) and subscriber (18k+ rows)
Then did a delete t-sql from the test table on the publication.
Then did a select count(*) query on the test table on the publication (0 rows) and subscriber (0 rows).

Now shouldn't it not delete the records on the subscription db?

View 6 Replies View Related

Linked Server To MYSQL Using OLEDB Provider For MYSQL Cherry

Feb 12, 2007

Good Morning

Has anyone successfully used cherry's oledb provider for MYSQL to create a linked server from MS SQLserver 2005 to a Linux red hat platform running MYSQL.

I can not get it to work.

I've created a UDL which tests fine. it looks like this

[oledb]

; Everything after this line is an OLE DB initstring

Provider=OleMySql.MySqlSource.1;Persist Security Info=False;User ID=testuser;

Data Source=databridge;Location="";Mode=Read;Trace="""""""""""""""""""""""""""""";

Initial Catalog=riverford_rhdx_20060822

Can any on help me convert this to corrrect syntax for sql stored procedure

sp_addlinkedserver



I've tried this below but it does not work I just get an error saying it can not create an instance of OleMySql.MySqlSource.

I used SQL server management studio to create the linked server then just scripted this out below.

I seem to be missing the user ID, but don't know where to put it in.

EXEC master.dbo.sp_addlinkedserver @server = N'DATABRIDGE_OLEDB', @srvproduct=N'mysql', @provider=N'OleMySql.MySqlSource', @datasrc=N'databridge', @catalog=N'riverford_rhdx_20060822'

GO

EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'collation compatible', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'data access', @optvalue=N'true'

GO

EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'dist', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'pub', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'rpc', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'rpc out', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'sub', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'connect timeout', @optvalue=N'0'

GO

EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'collation name', @optvalue=null

GO

EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'lazy schema validation', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'query timeout', @optvalue=N'0'

GO

EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'use remote collation', @optvalue=N'false'



Many Thanks



David Hills



View 7 Replies View Related

How Would I Get A Subset Of This?

Apr 30, 2008

I have an employee table (empl), and a labor table (lab). The labor table is populated whenever an employee clocks in/out. If an employee does not clock in, they do not appear in the labor table.

I need to capture employees whose time is less than 8 hours, or whoever did not log in at all that date. If I run the following query without a WHERE clause, I get all of my employees, with NULL data in the labor table if they did not log in that day. This is good.

However, if I add

WHERE (DATEDIFF(s, tt.StartTime, tt.EndTime) < 28800) OR (tt.StartTime = NULL)

I get all employees whose time is less than 8 hours, but do not get any of the employees who did not work at all that date, and that's expected... there should NEVER be a NULL in the labor table - it is populated whenever somebody clocks in/out.

So, how would I query the resulting table to get those that worked less than 8 hours, and those that did not?


select
et.EmpNo
et.FirstName,
et.LastName,
tt.StartTime,
tt.EndTime,
DATEDIFF(s, tt.StartTime, tt.EndTime) as Seconds

FROM (select
empl.EmpNum as EmpNo,
empl.FFName as FirstName,
empl.FLName as LastName
from
empl where empl.ftermdate IN ('1900-01-01 00:00:00.000') AS et

LEFT JOIN (select
MIN(lab.StartDt) as StartTime,
MAX(lab.EndDt) as EndTime,
lab.WorkDt as WorkDate,
lab.EmpNum as EmpNo
from
lab where lab.WorkDt in ('2008-03-03 00:00:00.000')
group by lab.WorkDT, lab.EmpNum) AS TT

ON et.EmpNo = tt.EmpNo

View 2 Replies View Related

Max Col In A Table Subset

May 1, 2007

hi i have a table as follows:

col1 col2 col3
2 BB 2
1 AB 3
3 CA 3
1 AC 1
2 BA 4
3 CB 2
2 BC 6
1 AA 2
3 CC 5

i want to output the whole row of each unique col1 where its col3 is max, so that i will get the ff result set
1 AB 3
2 BC 6
3 CC 5

i have an idea which is to use a cursor but i don't know the best/fastest way of doing it.
thanks heaps in advance!

View 1 Replies View Related

Subset A Dataset

Jul 11, 2007

I am trying to exclude patients from a dataset. There are multiple records per patid in this dataset. I have the following code:

SELECT meds.PATID, meds.MEDICATION, meds.MEDTYPE
FROM meds INNER JOIN patient ON meds.PATID = patient.PATID
WHERE (((meds.MEDTYPE) Not In ("FI (Fusion Inhibitor)","NNUC (","Non-nucleoside","NRTI & NNUC","NRTI (Nucleoside/tide Rev","PI (Protease Inhibitor)")));

I want to exclude all patient records if the patient had any of the above exclusions ever. If they have the exclusion if one record get rid of the rest of the records for that patient. Right now the code only excludes the particular record.

View 6 Replies View Related

Use DISTINCT On Subset Of All Fields

Jul 5, 2001

My table contains customer records with multiple records per customer. As a result of a query, I´m only interested in one record per customer with the highest value of a certain field in the record.

I thought of using DISTINCT, but can I use DISTINCT on a subset of all fields? Or sort the table in a certain way that the query result only shows the first unique records for a customer.

Other ideas are welcome to.

View 4 Replies View Related

Updates All Records Instead Of A Subset?

Dec 28, 2012

I am trying to update a small subset of records of a given table (TRValue) using the records contained in ParcelTemp. The difficult part is getting the summation from a child file, TRGreen, for those same parcels contained in ParcelTemp. Instead of updating just a few records, all the records in TRValue are being updated, with the wrong values of course!

Basically, Update records in TRValue that are equal to:

Year = P.Year
Code = 'LG01'
Parcel = P.Parcel

with the summation of child records where the child records needed are:

Year = P.Year
Parcel = P.Parcel

Code:
UPDATE TRValue SET
Acres = SumAcres,
CurrentMarket = SumMarket,
CurrentTaxable = SumTaxable,
CurrentTaxAmt = ((SumTaxable * D.CertifiedRate) + 0.50)
FROM ParcelTemp P

[code]....

View 4 Replies View Related

Picking A Representative Subset?

Feb 5, 2013

The problem is to find a subset of rows such that each value in each of two columns (animals and food brands in this example) appears in at least one row. The purpose is to produce a set of samples from a large table. The table has a animal_name column and an food_brand column; I want a set of samples that contains at least one of each animal_name and at least one of each food_brand, but no more than necessary.

CREATE TABLE Feeding_Options
(license_nbr INTEGER NOT NULL PRIMARY KEY,
animal_name VARCHAR (10) NOT NULL,
food_brand VARCHAR(15) NOT NULL);
INSERT INTO Feeding_Options
VALUES
(6401715, 'rat', 'IAMS'),

[code]....

To frame the problem better, her are the values in each column:

animals = {'rat', 'dog', 'cat', 'fish', 'fox'}
food = {'IAMS', 'Sci Diet', 'Purina', 'Alpo'}

In this data, (6401715, 'rat', 'IAMS') and (1058337, 'rat', 'IAMS') are interchangeable, as are some of the other rows. There can be more than one minimal solution whcihmight be the whole set.

View 2 Replies View Related

Select A Subset Of Data

Feb 13, 2007

How would I write a select statement that would return multiple fields in a records based on a a distinct of one of those fiels.

Example

Table Name : Sales Table
Field Name : Name Address Phone Zip Sale
Rec1: Peter Smith 12 Market St 999-999-9999 12345 99.99
Rec2: John Jones 73 Broadway 999-999-8888 12345 12.34
Rec3: Charle Brown 42 Peanuts Ave 999-999-7777 12345 34.56
Rec4: Peter Smith 12 Market St 999-999-6666 12345 67.89
Rec5: John Jone 73 Broadway 999-999-5555 12345 36.52

How would I be able to return the columns Name Address and Phone based on the distinct of Name.

View 4 Replies View Related

Same Table - Subset Of Info

Oct 25, 2007

I'm beating my head up against a wall here. I have one very LARGE table called op_hist.

CREATE TABLE [dbo].[op_hist] (
[op_trnbr] [int] NOT NULL ,
[op_type] [varchar] (80) ,
[op_date] [smalldatetime] NULL ,
[op_act_setup] [decimal](38, 10) NULL ,
[op_act_run] [decimal](38, 10) NULL ,
[op_qty_comp] [decimal](38, 10) NULL ,
[op_wkctr] [varchar] (80) ,
[op_part] [varchar] (30)

)

I need to be able to sum items grouped by op_wkctr and op_part and op_date. Here's my latest version which of course does not work.

SELECT op_wkctr, pcs, hrs.b
FROM
dbo.op_hist
INNER JOIN
(
SELECT op_part, SUM(op_qty_comp) as pcs
FROM dbo.op_hist
where op_type='BACKFLSH'
group by op_hist.op_part

UNION ALL

SELECT op_part, SUM(op_act_setup + op_act_run) as b
FROM dbo.op_hist
where op_type='LABOR'
group by op_hist.op_part
) AS hrs
ON op_hist.op_part = hrs.op_part
where (op_wkctr = 'P-P36' or op_wkctr = 'P-P38' or op_wkctr='P39') and op_date ='10/22/07'

Help me oby one - you're my only hope.

Sincerely,
Frustrated!

View 4 Replies View Related

Incrementing Within A Group/Subset

Oct 6, 2006

I could do this even with my limited knowledge of SQL but I'm surethere's a slick way that might be dead easy. My way hardly seems worththe effort . If not thanks anywayCol1Col2Col3QWQWQWQAQBQBQXWQXWQXWQXAQXBQXBEvery time there is a change of group (Col1 and Col2), I want to startand increment Col3 so I end up with;Col1Col2Col3QW001QW002QW003QA001QB001QB002QXW001QXW002QXW003QXA001QXB001QXB002

View 1 Replies View Related

SQL Server On A Subset Of Processors

Jul 10, 2006

Can I install SQL Server on a machine and use less than the # of processor on the machine. In a UNIX world, I'd call it LPARing with Oracle and AIX, and they only let me do this with Enterprise Edition. With Windows, I think the only way is using virtual machines and attaching processors to them? Do any vendors offering LPARing? Can I take any edition of SQL Server and subcapacity price so that I only pay for the processors I'm using?



What about SS Express? It only scheds to a single core - so could I put that on a larger machine?



Thanks!

View 5 Replies View Related

Calculate Check Digit On Subset Of Ids

Jul 17, 2006

how do i calculate all the checkdigits for a subset of ids and return all the ids and checkdigits? basic calculation isn't the problem, just how to select the ids and utilize the ids in the calc. I am new to trying to do calculations on data.

View 7 Replies View Related

Selecting The First Instance Of Each Record From A Subset

May 21, 2001

Hi

I'm sure this is an easy problem but my brain is fried today...however how do I do the following:


I have a two column table. One is a key field where duplicates can arise and the other is a datetime field. So you might have some records looking like this:

1231999-06-14 12:17:11.000
1231999-06-14 12:17:31.310
1231999-06-14 12:17:31.000
1231999-06-14 12:22:56.000
1231999-06-14 12:22:58.000
8901999-06-15 10:00:18.000
8901999-06-15 10:03:30.340
8901999-06-15 10:03:30.000
8901999-06-15 10:03:40.000

OK, how do I get the top 1 of each key so that I get a subset of records looking like the following:
1231999-06-14 12:17:11.000
8901999-06-15 10:00:18.000


Thanks in advance


Bazza

View 1 Replies View Related

Transfer Of Subset Of Data To/from Notebook

Mar 25, 2004

I have a need to dump a subset of a database from the server (SQL Server) to a notebook via the network, for data entry to be done on the notebook when it is in the field & not connected to the network & then the changes made to this data on the notebook to be applied to the database on the server.

The application for the front end to this is in Access. Would MSDE be the way to go for the database on the notebook ?

It's a small application with not many users, likelihood of conflicting edits is small.

Would the data transfer best be done with replication or with DTS ?
Presumably replication would allow options for control over conflicts, such as the same bit of data being changed on the server & on the notebook’s copy of the data ?

I need guidance re direction to head in with this.

Thanks

View 2 Replies View Related

Count Of Contacts - Data Subset?

Jan 29, 2014

I am trying to get counts of contacts.

So how do I subset, to gain a the accounts with the products then query to gain the contact counts from those accounts?

But for some reason it's giving me counts of products.

select distinct
a.region__c as [Region],
a.SFTX_ID as [SFTRX ID],
count(c.Contact_ID) as [Contact Count]
from vwContact c

[Code] .....

View 1 Replies View Related

Nth Subset Of Data From Current Table

Jul 20, 2005

Hi,I was wondering if you guys have any nth script to reads from tableand outputs into a temp table subset of records. There was a nth toolI used to use it was GROUP1 which was written in C and it used to bevery fast on nth -in a flat file. In this program we used to pass fewparamaeters. For example if I want 30,000 records from the file of500,000. The function seams to work something like this. you dividethe 30,000 records of 500,000 which will result with .090909090909.Now we would pass only the first 7 digit (0909090) as parameter thatwould nth the file down to 30,000 records. This function allwaysworked whichever number you use as long as the read file is largerthan output fileI like to use the similar concept in Sql Server and I was wondering ifanyone has any script to do this or how to go about this?Thank you. I appreciate your feedbackagron

View 1 Replies View Related

Selecting A Subset Of The Search Results

Nov 2, 2007

I want to break up a set of search results into small chunks. For instance, think about how Google displays a block of ten results out of the entire set. Selecting the top 10 is dead easy with "TOP 10"; how do I select the next 10?


Obviously one option would be to select the top 20, and programmatically discard the first 10, but surely there is a better way? I am doing this for a ASP.NET 2.0 application, and if I can retrieve just what I want, I can DataBind to a Repeater, and let ASP.NET do all the hard work of displaying the data.

View 4 Replies View Related

Analysis :: Subset Function In MDX Query

May 18, 2015

I need other function act like subset function using mdxquery.

In my mdxqueries,contain subset,order functions are available.

subset and order is getting poor query performance.

View 8 Replies View Related

Return Subset With All Remaining Records

Feb 22, 2008

Hello,

I need to pull records from single table such that I get a subset defined like this:

(
acctcode = 'xh364'
and
product = 'T&E'
)

And return all of the rest of the records:

(
acctcode = '%'
and
product = '%'
)

Can I do this within a WHERE clause, or will this require CASE / ELSE? There will be other specific acctcode/product rules that will be added later. I could do this with a UNION, but I need to avoid that if possible.


Thank you for your help!


cdun2

View 6 Replies View Related

Can I Select Subset Based On Time Of Entry

Jul 5, 2001

A table gets data every 4 minutes, I only need spread of every 15 minutes. Can I select only records spread every 15 minutes apart from this table without having to run a scheduled job every 15 minutes and loading one record closest to getdate() at that point into another table(this is how I am doing it now) Is there a better way. Please help
Thanks

View 2 Replies View Related

Move A Subset Of Data From Production To Test

Nov 18, 2005

Here is my requirement.

There is a production database which has ever increasing data. For testing purposes though, I would like to build a test database with exactly the same schema but only a subset of data copied from the production database . I'll specify the criteria (something like a where clause in select query) for copying the data from the production database.

Is there a tool that anyone has come across to do this job ?

View 2 Replies View Related

Should I Have A Table That Stores A Subset Of Data From Other Tables???

Mar 21, 2007

DB design question

I have multiple tables with information about a user. The tables are Roles, Users, Groups and Profiles.

For a user session I need information from all those tables. Would it be better to make a table called UserSession and collect the necessary data from the above mentioned tables and stick them in one the UserSession table or should I just write a query that goes out and gets the data from the different tables.

What is better practice and what is faster?

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved