Is it possible to format part of an expression in a textbox? I have this
expression which displays the parameter value(s) that are selected by the
user:
="Selected Region(s): " & Join(Parameters!paramRegion.Label,", ")
I wish to put "Selected Region(s): " in bold. I can use a second textbox in
bold "Selected Region(s): " and leave just the expression in the other one,
but I'm trying to do everything in a single textbox.
--
Thank you,
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.comYou cannot do this in RS 2005 (rich text in a single textbox)
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Alain Quesnel" <alainsansspam@.logiquel.com> wrote in message
news:ON$sP4yQIHA.748@.TK2MSFTNGP04.phx.gbl...
> Is it possible to format part of an expression in a textbox? I have this
> expression which displays the parameter value(s) that are selected by the
> user:
> ="Selected Region(s): " & Join(Parameters!paramRegion.Label,", ")
> I wish to put "Selected Region(s): " in bold. I can use a second textbox
> in bold "Selected Region(s): " and leave just the expression in the other
> one, but I'm trying to do everything in a single textbox.
> --
> Thank you,
> Alain Quesnel
> alainsansspam@.logiquel.com
> www.logiquel.com
>
Friday, March 30, 2012
Partial field search
I was just wondering if anyone could tell me how to do a search for a partial data match. Say one data field is 123, 234, 345, 456 and another is 111, 222, 333, 444 and another is 555, 666, 777, 888 and I want to search for the unique number 234 but not the whole number 123, 234, 345, 456 ... is there any way to do that or does every search have to be exactly like the data in the field?
Thanks for any help.
Dennis
yeah. UseSELECT
...
FROM
<table>
WHERE
<colname> = <Value>
|||
Look in Books on Line for the LIKE operator. Using wildcards (%), you can find what you're looking for.
Select field1 from table1 where field1 LIKE '%234%'
|||Why are you storing multiple values in a single column? You should get a book on database design -- such asthis one -- and design your databases such that you don't have to play games with text parsing.|||
I have to add this ability to an existing application that is accessing the DB from a Visual Studio Web Api. I realize that I could use one field for each data entry but the existing api has only one field to enter all the sub order data into the DB and I don't have time to rewrite the whole program. In the future, I will update this so that there is a seperate table for this function but right now I just need to figure out a workaround. Also, for this api there could be anywhere from 2 to 200 entries which is why it was written that way in the first place. See example:
Order Number: 12345
Sub Order Numbers: 2345, 5654, 923827, 00o09p87, 537654, w54327653, d54637364, u76y76tt, 6272527jhyr, ...... etc
I am trying to figure out a way to come up with the Order Number when only one of the Sub Order Numbers is available. Also, I have no control over how many or when the Sub Order Numbers are entered. The end users will be from a large variety of businesses and they are only aware of the Sub Order Number that is for them.
Thanks for all the speedy replies and please keep them comin'!
Dennis
partial DISTINCT...
Hi,
I need a way to do a 'partial' DISTINCT. What I try to say with that is:
some fields should be distinct, others not. For the non-distinct-fields is
should take the first occuring value or something like that.
For exepmle: I'm havng this query:
SELECT DISTINCT tblIsabel.IsabelID, tblIsabel.NameIsabel,
tblIsabelChild.IsabelChildID, tblIsabelChild.ChildNumber,
tblExported.ExportModeID
FROM tblIsabel JOIN tblIsabelChild ON tblIsabel.IsabelID = tblIsabelChild.IsabelID
LEFT JOIN tblExported ON tblIsabel.IsabelID = tblExported.IsabelID
ORDER BY tblIsabel.NameIsabel DESC, tblIsabelChild.IsabelChildID ASC
What the application has to do is:
It has to show all the tblIsabelChild-records ONLY ONCE, with the name of
the parent (tblIsabel), and an indication that shows me how the
tblIsabel-record has been exported (depeninding on the
tblExported.ExportModeID).
Yhe query I used works fine if the tblIsabel-records has not been exported,
or when it was exported only once.
But, when it is exported twice or more: it will find two or more
ExportModeID's for the tblIsabel-records, and like this show the
tblIsabelChild-records two or more times!
So what I need is something like this: a query that says that every
tblIsabelChildID should be unique (DISTINCT) in the recordset returned, and
for the ExportModeID it should take the first occurence or something liek
that.
It tryed something like this:
SELECT DISTINCT(tblIsabel.IsabelID, tblIsabel.NameIsabel,
tblIsabelChild.IsabelChildID, tblIsabelChild.ChildNumber),
tblExported.ExportModeID
hoping that it would use the distinct only for these values, but that
doesn't work unfortunately, hehe. I tryed using subquery's, but I didn't
manage to fidn something I need. Although I really think it shoudl be
possible to do with subquery's.
Anybody got a brilliant idea overhere?
Thanks a lot in advance,
PieterUse GROUP BY.
> It has to show all the tblIsabelChild-records ONLY ONCE, with the name of
> the parent (tblIsabel), and an indication that shows me how the
> tblIsabel-record has been exported (depeninding on the
> tblExported.ExportModeID).
If there is more than one value of ExportModeID then which one do you want
to show? I'll assume you just want to see the maximum value:
SELECT I.isabelid, I.nameisabel,
C.isabelchildid, C.childnumber,
MAX(E.exportmodeid) AS exportmodeid
FROM tblIsabel AS I
JOIN tblIsabelChild AS C
ON I.isabelid = C.isabelid
LEFT JOIN tblExported AS E
ON I.isabelid = E.isabelid
GROUP BY I.isabelid, I.nameisabel, C.isabelchildid, C.childnumber
ORDER BY I.nameisabel DESC, C.isabelchildid ASC
--
David Portas
SQL Server MVP
--|||Thanks,
I just found another solution:
adding the folowing code before the ORDER BY clause:
AND ((tblExported.ExportModeID IN
(SELECT MIN(tblExported.ExportModeID) FROM tblExported WHERE
tblExported.IsabelID = tblIsabel.IsabelID)
) OR (tblExported.ExportModeID IS NULL))
But your solution looks much nicer, hehe :-)
Funny (and stupid) thing is: I tryed the GROUP BY before, but it gave ma a
whole bunch of errors, just because I forgot to put all the fields in the
GROUP BY :-( And I just didn't try further :-(
Thanks a lot!
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:DtCdnfRr557s6b7d4p2dnA@.giganews.com...
> Use GROUP BY.
> > It has to show all the tblIsabelChild-records ONLY ONCE, with the name
of
> > the parent (tblIsabel), and an indication that shows me how the
> > tblIsabel-record has been exported (depeninding on the
> > tblExported.ExportModeID).
> If there is more than one value of ExportModeID then which one do you want
> to show? I'll assume you just want to see the maximum value:
> SELECT I.isabelid, I.nameisabel,
> C.isabelchildid, C.childnumber,
> MAX(E.exportmodeid) AS exportmodeid
> FROM tblIsabel AS I
> JOIN tblIsabelChild AS C
> ON I.isabelid = C.isabelid
> LEFT JOIN tblExported AS E
> ON I.isabelid = E.isabelid
> GROUP BY I.isabelid, I.nameisabel, C.isabelchildid, C.childnumber
> ORDER BY I.nameisabel DESC, C.isabelchildid ASC
> --
> David Portas
> SQL Server MVP
> --
>
I need a way to do a 'partial' DISTINCT. What I try to say with that is:
some fields should be distinct, others not. For the non-distinct-fields is
should take the first occuring value or something like that.
For exepmle: I'm havng this query:
SELECT DISTINCT tblIsabel.IsabelID, tblIsabel.NameIsabel,
tblIsabelChild.IsabelChildID, tblIsabelChild.ChildNumber,
tblExported.ExportModeID
FROM tblIsabel JOIN tblIsabelChild ON tblIsabel.IsabelID = tblIsabelChild.IsabelID
LEFT JOIN tblExported ON tblIsabel.IsabelID = tblExported.IsabelID
ORDER BY tblIsabel.NameIsabel DESC, tblIsabelChild.IsabelChildID ASC
What the application has to do is:
It has to show all the tblIsabelChild-records ONLY ONCE, with the name of
the parent (tblIsabel), and an indication that shows me how the
tblIsabel-record has been exported (depeninding on the
tblExported.ExportModeID).
Yhe query I used works fine if the tblIsabel-records has not been exported,
or when it was exported only once.
But, when it is exported twice or more: it will find two or more
ExportModeID's for the tblIsabel-records, and like this show the
tblIsabelChild-records two or more times!
So what I need is something like this: a query that says that every
tblIsabelChildID should be unique (DISTINCT) in the recordset returned, and
for the ExportModeID it should take the first occurence or something liek
that.
It tryed something like this:
SELECT DISTINCT(tblIsabel.IsabelID, tblIsabel.NameIsabel,
tblIsabelChild.IsabelChildID, tblIsabelChild.ChildNumber),
tblExported.ExportModeID
hoping that it would use the distinct only for these values, but that
doesn't work unfortunately, hehe. I tryed using subquery's, but I didn't
manage to fidn something I need. Although I really think it shoudl be
possible to do with subquery's.
Anybody got a brilliant idea overhere?
Thanks a lot in advance,
PieterUse GROUP BY.
> It has to show all the tblIsabelChild-records ONLY ONCE, with the name of
> the parent (tblIsabel), and an indication that shows me how the
> tblIsabel-record has been exported (depeninding on the
> tblExported.ExportModeID).
If there is more than one value of ExportModeID then which one do you want
to show? I'll assume you just want to see the maximum value:
SELECT I.isabelid, I.nameisabel,
C.isabelchildid, C.childnumber,
MAX(E.exportmodeid) AS exportmodeid
FROM tblIsabel AS I
JOIN tblIsabelChild AS C
ON I.isabelid = C.isabelid
LEFT JOIN tblExported AS E
ON I.isabelid = E.isabelid
GROUP BY I.isabelid, I.nameisabel, C.isabelchildid, C.childnumber
ORDER BY I.nameisabel DESC, C.isabelchildid ASC
--
David Portas
SQL Server MVP
--|||Thanks,
I just found another solution:
adding the folowing code before the ORDER BY clause:
AND ((tblExported.ExportModeID IN
(SELECT MIN(tblExported.ExportModeID) FROM tblExported WHERE
tblExported.IsabelID = tblIsabel.IsabelID)
) OR (tblExported.ExportModeID IS NULL))
But your solution looks much nicer, hehe :-)
Funny (and stupid) thing is: I tryed the GROUP BY before, but it gave ma a
whole bunch of errors, just because I forgot to put all the fields in the
GROUP BY :-( And I just didn't try further :-(
Thanks a lot!
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:DtCdnfRr557s6b7d4p2dnA@.giganews.com...
> Use GROUP BY.
> > It has to show all the tblIsabelChild-records ONLY ONCE, with the name
of
> > the parent (tblIsabel), and an indication that shows me how the
> > tblIsabel-record has been exported (depeninding on the
> > tblExported.ExportModeID).
> If there is more than one value of ExportModeID then which one do you want
> to show? I'll assume you just want to see the maximum value:
> SELECT I.isabelid, I.nameisabel,
> C.isabelchildid, C.childnumber,
> MAX(E.exportmodeid) AS exportmodeid
> FROM tblIsabel AS I
> JOIN tblIsabelChild AS C
> ON I.isabelid = C.isabelid
> LEFT JOIN tblExported AS E
> ON I.isabelid = E.isabelid
> GROUP BY I.isabelid, I.nameisabel, C.isabelchildid, C.childnumber
> ORDER BY I.nameisabel DESC, C.isabelchildid ASC
> --
> David Portas
> SQL Server MVP
> --
>
partial DISTINCT...
Hi,
I need a way to do a 'partial' DISTINCT. What I try to say with that is:
some fields should be distinct, others not. For the non-distinct-fields is
should take the first occuring value or something like that.
For exepmle: I'm havng this query:
SELECT DISTINCT tblIsabel.IsabelID, tblIsabel.NameIsabel,
tblIsabelChild.IsabelChildID, tblIsabelChild.ChildNumber,
tblExported.ExportModeID
FROM tblIsabel JOIN tblIsabelChild ON tblIsabel.IsabelID =
tblIsabelChild.IsabelID
LEFT JOIN tblExported ON tblIsabel.IsabelID = tblExported.IsabelID
ORDER BY tblIsabel.NameIsabel DESC, tblIsabelChild.IsabelChildID ASC
What the application has to do is:
It has to show all the tblIsabelChild-records ONLY ONCE, with the name of
the parent (tblIsabel), and an indication that shows me how the
tblIsabel-record has been exported (depeninding on the
tblExported.ExportModeID).
Yhe query I used works fine if the tblIsabel-records has not been exported,
or when it was exported only once.
But, when it is exported twice or more: it will find two or more
ExportModeID's for the tblIsabel-records, and like this show the
tblIsabelChild-records two or more times!
So what I need is something like this: a query that says that every
tblIsabelChildID should be unique (DISTINCT) in the recordset returned, and
for the ExportModeID it should take the first occurence or something liek
that.
It tryed something like this:
SELECT DISTINCT(tblIsabel.IsabelID, tblIsabel.NameIsabel,
tblIsabelChild.IsabelChildID, tblIsabelChild.ChildNumber),
tblExported.ExportModeID
hoping that it would use the distinct only for these values, but that
doesn't work unfortunately, hehe. I tryed using subquery's, but I didn't
manage to fidn something I need. Although I really think it shoudl be
possible to do with subquery's.
Anybody got a brilliant idea overhere?
Thanks a lot in advance,
PieterUse GROUP BY.
> It has to show all the tblIsabelChild-records ONLY ONCE, with the name of
> the parent (tblIsabel), and an indication that shows me how the
> tblIsabel-record has been exported (depeninding on the
> tblExported.ExportModeID).
If there is more than one value of ExportModeID then which one do you want
to show? I'll assume you just want to see the maximum value:
SELECT I.isabelid, I.nameisabel,
C.isabelchildid, C.childnumber,
MAX(E.exportmodeid) AS exportmodeid
FROM tblIsabel AS I
JOIN tblIsabelChild AS C
ON I.isabelid = C.isabelid
LEFT JOIN tblExported AS E
ON I.isabelid = E.isabelid
GROUP BY I.isabelid, I.nameisabel, C.isabelchildid, C.childnumber
ORDER BY I.nameisabel DESC, C.isabelchildid ASC
David Portas
SQL Server MVP
--|||Thanks,
I just found another solution:
adding the folowing code before the ORDER BY clause:
AND ((tblExported.ExportModeID IN
(SELECT MIN(tblExported.ExportModeID) FROM tblExported WHERE
tblExported.IsabelID = tblIsabel.IsabelID)
) OR (tblExported.ExportModeID IS NULL))
But your solution looks much nicer, hehe :-)
Funny (and stupid) thing is: I tryed the GROUP BY before, but it gave ma a
whole bunch of errors, just because I forgot to put all the fields in the
GROUP BY :-( And I just didn't try further :-(
Thanks a lot!
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:DtCdnfRr557s6b7d4p2dnA@.giganews.com...
> Use GROUP BY.
>
of
> If there is more than one value of ExportModeID then which one do you want
> to show? I'll assume you just want to see the maximum value:
> SELECT I.isabelid, I.nameisabel,
> C.isabelchildid, C.childnumber,
> MAX(E.exportmodeid) AS exportmodeid
> FROM tblIsabel AS I
> JOIN tblIsabelChild AS C
> ON I.isabelid = C.isabelid
> LEFT JOIN tblExported AS E
> ON I.isabelid = E.isabelid
> GROUP BY I.isabelid, I.nameisabel, C.isabelchildid, C.childnumber
> ORDER BY I.nameisabel DESC, C.isabelchildid ASC
> --
> David Portas
> SQL Server MVP
> --
>
I need a way to do a 'partial' DISTINCT. What I try to say with that is:
some fields should be distinct, others not. For the non-distinct-fields is
should take the first occuring value or something like that.
For exepmle: I'm havng this query:
SELECT DISTINCT tblIsabel.IsabelID, tblIsabel.NameIsabel,
tblIsabelChild.IsabelChildID, tblIsabelChild.ChildNumber,
tblExported.ExportModeID
FROM tblIsabel JOIN tblIsabelChild ON tblIsabel.IsabelID =
tblIsabelChild.IsabelID
LEFT JOIN tblExported ON tblIsabel.IsabelID = tblExported.IsabelID
ORDER BY tblIsabel.NameIsabel DESC, tblIsabelChild.IsabelChildID ASC
What the application has to do is:
It has to show all the tblIsabelChild-records ONLY ONCE, with the name of
the parent (tblIsabel), and an indication that shows me how the
tblIsabel-record has been exported (depeninding on the
tblExported.ExportModeID).
Yhe query I used works fine if the tblIsabel-records has not been exported,
or when it was exported only once.
But, when it is exported twice or more: it will find two or more
ExportModeID's for the tblIsabel-records, and like this show the
tblIsabelChild-records two or more times!
So what I need is something like this: a query that says that every
tblIsabelChildID should be unique (DISTINCT) in the recordset returned, and
for the ExportModeID it should take the first occurence or something liek
that.
It tryed something like this:
SELECT DISTINCT(tblIsabel.IsabelID, tblIsabel.NameIsabel,
tblIsabelChild.IsabelChildID, tblIsabelChild.ChildNumber),
tblExported.ExportModeID
hoping that it would use the distinct only for these values, but that
doesn't work unfortunately, hehe. I tryed using subquery's, but I didn't
manage to fidn something I need. Although I really think it shoudl be
possible to do with subquery's.
Anybody got a brilliant idea overhere?
Thanks a lot in advance,
PieterUse GROUP BY.
> It has to show all the tblIsabelChild-records ONLY ONCE, with the name of
> the parent (tblIsabel), and an indication that shows me how the
> tblIsabel-record has been exported (depeninding on the
> tblExported.ExportModeID).
If there is more than one value of ExportModeID then which one do you want
to show? I'll assume you just want to see the maximum value:
SELECT I.isabelid, I.nameisabel,
C.isabelchildid, C.childnumber,
MAX(E.exportmodeid) AS exportmodeid
FROM tblIsabel AS I
JOIN tblIsabelChild AS C
ON I.isabelid = C.isabelid
LEFT JOIN tblExported AS E
ON I.isabelid = E.isabelid
GROUP BY I.isabelid, I.nameisabel, C.isabelchildid, C.childnumber
ORDER BY I.nameisabel DESC, C.isabelchildid ASC
David Portas
SQL Server MVP
--|||Thanks,
I just found another solution:
adding the folowing code before the ORDER BY clause:
AND ((tblExported.ExportModeID IN
(SELECT MIN(tblExported.ExportModeID) FROM tblExported WHERE
tblExported.IsabelID = tblIsabel.IsabelID)
) OR (tblExported.ExportModeID IS NULL))
But your solution looks much nicer, hehe :-)
Funny (and stupid) thing is: I tryed the GROUP BY before, but it gave ma a
whole bunch of errors, just because I forgot to put all the fields in the
GROUP BY :-( And I just didn't try further :-(
Thanks a lot!
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:DtCdnfRr557s6b7d4p2dnA@.giganews.com...
> Use GROUP BY.
>
of
> If there is more than one value of ExportModeID then which one do you want
> to show? I'll assume you just want to see the maximum value:
> SELECT I.isabelid, I.nameisabel,
> C.isabelchildid, C.childnumber,
> MAX(E.exportmodeid) AS exportmodeid
> FROM tblIsabel AS I
> JOIN tblIsabelChild AS C
> ON I.isabelid = C.isabelid
> LEFT JOIN tblExported AS E
> ON I.isabelid = E.isabelid
> GROUP BY I.isabelid, I.nameisabel, C.isabelchildid, C.childnumber
> ORDER BY I.nameisabel DESC, C.isabelchildid ASC
> --
> David Portas
> SQL Server MVP
> --
>
Partial database copy - kind of replication
Hi,
I have to update regularly (twice a day) an SQLServer 2000 DB on a remote
location, what has only a 128 k line for Internet (even that in not working
always). The source DB is on SQLSeerver 2k too. Only some tables from the
given DB are important, but I can not make any structural change (column
addition) on the tables. The full database is pretty big (> 500 MBytes), so
I can not make a snapshot replication.
My first idea was to create a second DB, where I would make a shadow table
for every original table: record by record I would store the primary key of
the original record, and a kind of "CRC" from the rest of the data. When I
have to send the update, I would compare the stored CRC and a newly
generated one, and send only the records, where this CRC is not match. (I
hope it is understandable)
My questions are:
1. What simplier solutions you have
2. If none, what algorithm should I use for the "CRC", what insure that I
will recognise the change of the record? The tables holds all kind of
fields, even binary :(.
Thanks:
PeterPeter,
Did you look into merge replication? It seems to me that it might be a good
answer to your problem. You can choose what tables to replicate, and it will
be sending only changes to the remote server. If you need you can also apply
filters to your tables, you can filter rows as well as columns.
HTH, Igor
"Peter Baranyi" <bp@.cdfwebb.hu> wrote in message
news:uTSFEnfFFHA.4052@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have to update regularly (twice a day) an SQLServer 2000 DB on a remote
> location, what has only a 128 k line for Internet (even that in not
> working always). The source DB is on SQLSeerver 2k too. Only some tables
> from the given DB are important, but I can not make any structural change
> (column addition) on the tables. The full database is pretty big (> 500
> MBytes), so I can not make a snapshot replication.
> My first idea was to create a second DB, where I would make a shadow table
> for every original table: record by record I would store the primary key
> of the original record, and a kind of "CRC" from the rest of the data.
> When I have to send the update, I would compare the stored CRC and a newly
> generated one, and send only the records, where this CRC is not match. (I
> hope it is understandable)
> My questions are:
> 1. What simplier solutions you have
> 2. If none, what algorithm should I use for the "CRC", what insure that I
> will recognise the change of the record? The tables holds all kind of
> fields, even binary :(.
> Thanks:
> Peter
>|||I would use transactional replication for this.
If you want to do some sort of CRC comparison you should look at checksum.
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
"Peter Baranyi" <bp@.cdfwebb.hu> wrote in message
news:uTSFEnfFFHA.4052@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have to update regularly (twice a day) an SQLServer 2000 DB on a remote
> location, what has only a 128 k line for Internet (even that in not
working
> always). The source DB is on SQLSeerver 2k too. Only some tables from the
> given DB are important, but I can not make any structural change (column
> addition) on the tables. The full database is pretty big (> 500 MBytes),
so
> I can not make a snapshot replication.
> My first idea was to create a second DB, where I would make a shadow table
> for every original table: record by record I would store the primary key
of
> the original record, and a kind of "CRC" from the rest of the data. When I
> have to send the update, I would compare the stored CRC and a newly
> generated one, and send only the records, where this CRC is not match. (I
> hope it is understandable)
> My questions are:
> 1. What simplier solutions you have
> 2. If none, what algorithm should I use for the "CRC", what insure that I
> will recognise the change of the record? The tables holds all kind of
> fields, even binary :(.
> Thanks:
> Peter
>
I have to update regularly (twice a day) an SQLServer 2000 DB on a remote
location, what has only a 128 k line for Internet (even that in not working
always). The source DB is on SQLSeerver 2k too. Only some tables from the
given DB are important, but I can not make any structural change (column
addition) on the tables. The full database is pretty big (> 500 MBytes), so
I can not make a snapshot replication.
My first idea was to create a second DB, where I would make a shadow table
for every original table: record by record I would store the primary key of
the original record, and a kind of "CRC" from the rest of the data. When I
have to send the update, I would compare the stored CRC and a newly
generated one, and send only the records, where this CRC is not match. (I
hope it is understandable)
My questions are:
1. What simplier solutions you have
2. If none, what algorithm should I use for the "CRC", what insure that I
will recognise the change of the record? The tables holds all kind of
fields, even binary :(.
Thanks:
PeterPeter,
Did you look into merge replication? It seems to me that it might be a good
answer to your problem. You can choose what tables to replicate, and it will
be sending only changes to the remote server. If you need you can also apply
filters to your tables, you can filter rows as well as columns.
HTH, Igor
"Peter Baranyi" <bp@.cdfwebb.hu> wrote in message
news:uTSFEnfFFHA.4052@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have to update regularly (twice a day) an SQLServer 2000 DB on a remote
> location, what has only a 128 k line for Internet (even that in not
> working always). The source DB is on SQLSeerver 2k too. Only some tables
> from the given DB are important, but I can not make any structural change
> (column addition) on the tables. The full database is pretty big (> 500
> MBytes), so I can not make a snapshot replication.
> My first idea was to create a second DB, where I would make a shadow table
> for every original table: record by record I would store the primary key
> of the original record, and a kind of "CRC" from the rest of the data.
> When I have to send the update, I would compare the stored CRC and a newly
> generated one, and send only the records, where this CRC is not match. (I
> hope it is understandable)
> My questions are:
> 1. What simplier solutions you have
> 2. If none, what algorithm should I use for the "CRC", what insure that I
> will recognise the change of the record? The tables holds all kind of
> fields, even binary :(.
> Thanks:
> Peter
>|||I would use transactional replication for this.
If you want to do some sort of CRC comparison you should look at checksum.
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
"Peter Baranyi" <bp@.cdfwebb.hu> wrote in message
news:uTSFEnfFFHA.4052@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have to update regularly (twice a day) an SQLServer 2000 DB on a remote
> location, what has only a 128 k line for Internet (even that in not
working
> always). The source DB is on SQLSeerver 2k too. Only some tables from the
> given DB are important, but I can not make any structural change (column
> addition) on the tables. The full database is pretty big (> 500 MBytes),
so
> I can not make a snapshot replication.
> My first idea was to create a second DB, where I would make a shadow table
> for every original table: record by record I would store the primary key
of
> the original record, and a kind of "CRC" from the rest of the data. When I
> have to send the update, I would compare the stored CRC and a newly
> generated one, and send only the records, where this CRC is not match. (I
> hope it is understandable)
> My questions are:
> 1. What simplier solutions you have
> 2. If none, what algorithm should I use for the "CRC", what insure that I
> will recognise the change of the record? The tables holds all kind of
> fields, even binary :(.
> Thanks:
> Peter
>
Partial database copy - kind of replication
Hi,
I have to update regularly (twice a day) an SQLServer 2000 DB on a remote
location, what has only a 128 k line for Internet (even that in not working
always). The source DB is on SQLSeerver 2k too. Only some tables from the
given DB are important, but I can not make any structural change (column
addition) on the tables. The full database is pretty big (> 500 MBytes), so
I can not make a snapshot replication.
My first idea was to create a second DB, where I would make a shadow table
for every original table: record by record I would store the primary key of
the original record, and a kind of "CRC" from the rest of the data. When I
have to send the update, I would compare the stored CRC and a newly
generated one, and send only the records, where this CRC is not match. (I
hope it is understandable)
My questions are:
1. What simplier solutions you have
2. If none, what algorithm should I use for the "CRC", what insure that I
will recognise the change of the record? The tables holds all kind of
fields, even binary
.
Thanks:
Peter
Peter,
Did you look into merge replication? It seems to me that it might be a good
answer to your problem. You can choose what tables to replicate, and it will
be sending only changes to the remote server. If you need you can also apply
filters to your tables, you can filter rows as well as columns.
HTH, Igor
"Peter Baranyi" <bp@.cdfwebb.hu> wrote in message
news:uTSFEnfFFHA.4052@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have to update regularly (twice a day) an SQLServer 2000 DB on a remote
> location, what has only a 128 k line for Internet (even that in not
> working always). The source DB is on SQLSeerver 2k too. Only some tables
> from the given DB are important, but I can not make any structural change
> (column addition) on the tables. The full database is pretty big (> 500
> MBytes), so I can not make a snapshot replication.
> My first idea was to create a second DB, where I would make a shadow table
> for every original table: record by record I would store the primary key
> of the original record, and a kind of "CRC" from the rest of the data.
> When I have to send the update, I would compare the stored CRC and a newly
> generated one, and send only the records, where this CRC is not match. (I
> hope it is understandable)
> My questions are:
> 1. What simplier solutions you have
> 2. If none, what algorithm should I use for the "CRC", what insure that I
> will recognise the change of the record? The tables holds all kind of
> fields, even binary.
> Thanks:
> Peter
>
|||I would use transactional replication for this.
If you want to do some sort of CRC comparison you should look at checksum.
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
"Peter Baranyi" <bp@.cdfwebb.hu> wrote in message
news:uTSFEnfFFHA.4052@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have to update regularly (twice a day) an SQLServer 2000 DB on a remote
> location, what has only a 128 k line for Internet (even that in not
working
> always). The source DB is on SQLSeerver 2k too. Only some tables from the
> given DB are important, but I can not make any structural change (column
> addition) on the tables. The full database is pretty big (> 500 MBytes),
so
> I can not make a snapshot replication.
> My first idea was to create a second DB, where I would make a shadow table
> for every original table: record by record I would store the primary key
of
> the original record, and a kind of "CRC" from the rest of the data. When I
> have to send the update, I would compare the stored CRC and a newly
> generated one, and send only the records, where this CRC is not match. (I
> hope it is understandable)
> My questions are:
> 1. What simplier solutions you have
> 2. If none, what algorithm should I use for the "CRC", what insure that I
> will recognise the change of the record? The tables holds all kind of
> fields, even binary.
> Thanks:
> Peter
>
|||Unless the dataflow is in both directionns, and involves
autonomy or conflicts, I would recommend transactional
over merge, as it is significvantly faster and less
resource-intensive.
If you want a non-replication version, then
binary_checksums can be used although there is some
discussion as to whether these can always be relied upon
100%. Redgate do a DataCompare tool to do this type of
thing also.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
I have to update regularly (twice a day) an SQLServer 2000 DB on a remote
location, what has only a 128 k line for Internet (even that in not working
always). The source DB is on SQLSeerver 2k too. Only some tables from the
given DB are important, but I can not make any structural change (column
addition) on the tables. The full database is pretty big (> 500 MBytes), so
I can not make a snapshot replication.
My first idea was to create a second DB, where I would make a shadow table
for every original table: record by record I would store the primary key of
the original record, and a kind of "CRC" from the rest of the data. When I
have to send the update, I would compare the stored CRC and a newly
generated one, and send only the records, where this CRC is not match. (I
hope it is understandable)
My questions are:
1. What simplier solutions you have
2. If none, what algorithm should I use for the "CRC", what insure that I
will recognise the change of the record? The tables holds all kind of
fields, even binary
.Thanks:
Peter
Peter,
Did you look into merge replication? It seems to me that it might be a good
answer to your problem. You can choose what tables to replicate, and it will
be sending only changes to the remote server. If you need you can also apply
filters to your tables, you can filter rows as well as columns.
HTH, Igor
"Peter Baranyi" <bp@.cdfwebb.hu> wrote in message
news:uTSFEnfFFHA.4052@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have to update regularly (twice a day) an SQLServer 2000 DB on a remote
> location, what has only a 128 k line for Internet (even that in not
> working always). The source DB is on SQLSeerver 2k too. Only some tables
> from the given DB are important, but I can not make any structural change
> (column addition) on the tables. The full database is pretty big (> 500
> MBytes), so I can not make a snapshot replication.
> My first idea was to create a second DB, where I would make a shadow table
> for every original table: record by record I would store the primary key
> of the original record, and a kind of "CRC" from the rest of the data.
> When I have to send the update, I would compare the stored CRC and a newly
> generated one, and send only the records, where this CRC is not match. (I
> hope it is understandable)
> My questions are:
> 1. What simplier solutions you have
> 2. If none, what algorithm should I use for the "CRC", what insure that I
> will recognise the change of the record? The tables holds all kind of
> fields, even binary
.> Thanks:
> Peter
>
|||I would use transactional replication for this.
If you want to do some sort of CRC comparison you should look at checksum.
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
"Peter Baranyi" <bp@.cdfwebb.hu> wrote in message
news:uTSFEnfFFHA.4052@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have to update regularly (twice a day) an SQLServer 2000 DB on a remote
> location, what has only a 128 k line for Internet (even that in not
working
> always). The source DB is on SQLSeerver 2k too. Only some tables from the
> given DB are important, but I can not make any structural change (column
> addition) on the tables. The full database is pretty big (> 500 MBytes),
so
> I can not make a snapshot replication.
> My first idea was to create a second DB, where I would make a shadow table
> for every original table: record by record I would store the primary key
of
> the original record, and a kind of "CRC" from the rest of the data. When I
> have to send the update, I would compare the stored CRC and a newly
> generated one, and send only the records, where this CRC is not match. (I
> hope it is understandable)
> My questions are:
> 1. What simplier solutions you have
> 2. If none, what algorithm should I use for the "CRC", what insure that I
> will recognise the change of the record? The tables holds all kind of
> fields, even binary
.> Thanks:
> Peter
>
|||Unless the dataflow is in both directionns, and involves
autonomy or conflicts, I would recommend transactional
over merge, as it is significvantly faster and less
resource-intensive.
If you want a non-replication version, then
binary_checksums can be used although there is some
discussion as to whether these can always be relied upon
100%. Redgate do a DataCompare tool to do this type of
thing also.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Partial config on a non-replicating DB?
I have an issue on one of my test servers that I'm not entirely sure
how to solve, and would appreciate any advice people have.
We are not using replication on any of our DBs, and so far as I know
this instance/DB has never been configured for replication. Currently,
Enterprise Manager is saying that it's not set up as a publisher or
subscriber, yet one of our stored procedures is throwing error 7139
("Length of text, ntext, or image data (%ld) to be replicated exceeds
configured maximum %ld.") in some cases where a text column is being
updated.
After some investigation, it turns out that of the 400 tables in the
DB, 10 of them have related syncobj_0x... views in the DB. So it
appears to me at some point in the past there may have been an aborted
attempt at setting up snapshot replication by one of my developers, or
by some package, or something.
My question now is twofold.
First, has anyone seen this kind of thing before or have any idea what
may have gotten our DB to this state? Is it the expected state of
things if replication was configured and then turned off?
Second, what do I need to do to diagnose how extensive this
partial-setup is, and how do I clean it up? Is it as simple as
dropping the syncobj views? Are there other things I should look for
in the system tables to make sure there aren't other parts incorrectly
set up?
Thanks in advance for any help.
-Aaron
This error is normally generated by the log reader, but you don't have a log
reader - which is what makes it puzzling. Its highly abnormal, and
definitely not expected. If I could identity the problem table I would back
it up, drop it, recreate it, and restore its contents in the hope that it
solved the problem. You might want to drop a dime (actually $245 I think) on
PSS and see if they can help you.
I would try to drop the sync object views - however this should have no
impact on your system.
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
<alouts@.hotmail.com> wrote in message
news:1136509281.066817.40350@.z14g2000cwz.googlegro ups.com...
>I have an issue on one of my test servers that I'm not entirely sure
> how to solve, and would appreciate any advice people have.
> We are not using replication on any of our DBs, and so far as I know
> this instance/DB has never been configured for replication. Currently,
> Enterprise Manager is saying that it's not set up as a publisher or
> subscriber, yet one of our stored procedures is throwing error 7139
> ("Length of text, ntext, or image data (%ld) to be replicated exceeds
> configured maximum %ld.") in some cases where a text column is being
> updated.
> After some investigation, it turns out that of the 400 tables in the
> DB, 10 of them have related syncobj_0x... views in the DB. So it
> appears to me at some point in the past there may have been an aborted
> attempt at setting up snapshot replication by one of my developers, or
> by some package, or something.
> My question now is twofold.
> First, has anyone seen this kind of thing before or have any idea what
> may have gotten our DB to this state? Is it the expected state of
> things if replication was configured and then turned off?
> Second, what do I need to do to diagnose how extensive this
> partial-setup is, and how do I clean it up? Is it as simple as
> dropping the syncobj views? Are there other things I should look for
> in the system tables to make sure there aren't other parts incorrectly
> set up?
> Thanks in advance for any help.
> -Aaron
>
|||Have I seen this? More times than I can count. Is it normal? No. But it
happens often enough. If you have synchobj... in the database, then it was
most likely merge which was setup and then removed which didn't take it out
cleanly. (This normally happens when you restore a replicated database to a
server which isn't configured for publishing.) If you look at the table,
you will more than likely have 3 triggers created against the table. Those
triggers are going to reference the views you mentioned. You should also
have a rowguid column on the table.
You can have triggers, views, procs, etc. in there. sp_removedbreplication
should clean everything up. If it doesn't, then you can manually drop any
triggers, views, procedures, indexes, and columns that are related to
replication. You then need to issue an sp_dboption to turn off the
replication related database options if they aren't off already. You may
have to go as far as turning on allow updates and resetting the replinfo
column in sysobjects to 0.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
<alouts@.hotmail.com> wrote in message
news:1136509281.066817.40350@.z14g2000cwz.googlegro ups.com...
>I have an issue on one of my test servers that I'm not entirely sure
> how to solve, and would appreciate any advice people have.
> We are not using replication on any of our DBs, and so far as I know
> this instance/DB has never been configured for replication. Currently,
> Enterprise Manager is saying that it's not set up as a publisher or
> subscriber, yet one of our stored procedures is throwing error 7139
> ("Length of text, ntext, or image data (%ld) to be replicated exceeds
> configured maximum %ld.") in some cases where a text column is being
> updated.
> After some investigation, it turns out that of the 400 tables in the
> DB, 10 of them have related syncobj_0x... views in the DB. So it
> appears to me at some point in the past there may have been an aborted
> attempt at setting up snapshot replication by one of my developers, or
> by some package, or something.
> My question now is twofold.
> First, has anyone seen this kind of thing before or have any idea what
> may have gotten our DB to this state? Is it the expected state of
> things if replication was configured and then turned off?
> Second, what do I need to do to diagnose how extensive this
> partial-setup is, and how do I clean it up? Is it as simple as
> dropping the syncobj views? Are there other things I should look for
> in the system tables to make sure there aren't other parts incorrectly
> set up?
> Thanks in advance for any help.
> -Aaron
>
|||Sync objects are used by the bcp process while generating the snapshot for
every replication type - EXCEPT merge.Try it.
Nor are these views are not referenced in the merge replication triggers. Do
the following
sp_helptrigger MergePublishedTable
and look for the triggers, they will look like this
ins_8C282332351F48AC95B3B81552F867BA
upd_8C282332351F48AC95B3B81552F867BA
del_8C282332351F48AC95B3B81552F867BA
then issue a sp_helptext against the triggers and you will not find the sync
objects referenced in the triggers, nor the views of the form ctsv_GUID and
tsvw_GUID which the triggers themselves reference.
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
"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:eD7DYEpEGHA.3000@.TK2MSFTNGP14.phx.gbl...
> Have I seen this? More times than I can count. Is it normal? No. But
> it happens often enough. If you have synchobj... in the database, then it
> was most likely merge which was setup and then removed which didn't take
> it out cleanly. (This normally happens when you restore a replicated
> database to a server which isn't configured for publishing.) If you look
> at the table, you will more than likely have 3 triggers created against
> the table. Those triggers are going to reference the views you mentioned.
> You should also have a rowguid column on the table.
> You can have triggers, views, procs, etc. in there.
> sp_removedbreplication should clean everything up. If it doesn't, then
> you can manually drop any triggers, views, procedures, indexes, and
> columns that are related to replication. You then need to issue an
> sp_dboption to turn off the replication related database options if they
> aren't off already. You may have to go as far as turning on allow updates
> and resetting the replinfo column in sysobjects to 0.
> --
> Mike
> Mentor
> Solid Quality Learning
> http://www.solidqualitylearning.com
>
> <alouts@.hotmail.com> wrote in message
> news:1136509281.066817.40350@.z14g2000cwz.googlegro ups.com...
>
how to solve, and would appreciate any advice people have.
We are not using replication on any of our DBs, and so far as I know
this instance/DB has never been configured for replication. Currently,
Enterprise Manager is saying that it's not set up as a publisher or
subscriber, yet one of our stored procedures is throwing error 7139
("Length of text, ntext, or image data (%ld) to be replicated exceeds
configured maximum %ld.") in some cases where a text column is being
updated.
After some investigation, it turns out that of the 400 tables in the
DB, 10 of them have related syncobj_0x... views in the DB. So it
appears to me at some point in the past there may have been an aborted
attempt at setting up snapshot replication by one of my developers, or
by some package, or something.
My question now is twofold.
First, has anyone seen this kind of thing before or have any idea what
may have gotten our DB to this state? Is it the expected state of
things if replication was configured and then turned off?
Second, what do I need to do to diagnose how extensive this
partial-setup is, and how do I clean it up? Is it as simple as
dropping the syncobj views? Are there other things I should look for
in the system tables to make sure there aren't other parts incorrectly
set up?
Thanks in advance for any help.
-Aaron
This error is normally generated by the log reader, but you don't have a log
reader - which is what makes it puzzling. Its highly abnormal, and
definitely not expected. If I could identity the problem table I would back
it up, drop it, recreate it, and restore its contents in the hope that it
solved the problem. You might want to drop a dime (actually $245 I think) on
PSS and see if they can help you.
I would try to drop the sync object views - however this should have no
impact on your system.
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
<alouts@.hotmail.com> wrote in message
news:1136509281.066817.40350@.z14g2000cwz.googlegro ups.com...
>I have an issue on one of my test servers that I'm not entirely sure
> how to solve, and would appreciate any advice people have.
> We are not using replication on any of our DBs, and so far as I know
> this instance/DB has never been configured for replication. Currently,
> Enterprise Manager is saying that it's not set up as a publisher or
> subscriber, yet one of our stored procedures is throwing error 7139
> ("Length of text, ntext, or image data (%ld) to be replicated exceeds
> configured maximum %ld.") in some cases where a text column is being
> updated.
> After some investigation, it turns out that of the 400 tables in the
> DB, 10 of them have related syncobj_0x... views in the DB. So it
> appears to me at some point in the past there may have been an aborted
> attempt at setting up snapshot replication by one of my developers, or
> by some package, or something.
> My question now is twofold.
> First, has anyone seen this kind of thing before or have any idea what
> may have gotten our DB to this state? Is it the expected state of
> things if replication was configured and then turned off?
> Second, what do I need to do to diagnose how extensive this
> partial-setup is, and how do I clean it up? Is it as simple as
> dropping the syncobj views? Are there other things I should look for
> in the system tables to make sure there aren't other parts incorrectly
> set up?
> Thanks in advance for any help.
> -Aaron
>
|||Have I seen this? More times than I can count. Is it normal? No. But it
happens often enough. If you have synchobj... in the database, then it was
most likely merge which was setup and then removed which didn't take it out
cleanly. (This normally happens when you restore a replicated database to a
server which isn't configured for publishing.) If you look at the table,
you will more than likely have 3 triggers created against the table. Those
triggers are going to reference the views you mentioned. You should also
have a rowguid column on the table.
You can have triggers, views, procs, etc. in there. sp_removedbreplication
should clean everything up. If it doesn't, then you can manually drop any
triggers, views, procedures, indexes, and columns that are related to
replication. You then need to issue an sp_dboption to turn off the
replication related database options if they aren't off already. You may
have to go as far as turning on allow updates and resetting the replinfo
column in sysobjects to 0.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
<alouts@.hotmail.com> wrote in message
news:1136509281.066817.40350@.z14g2000cwz.googlegro ups.com...
>I have an issue on one of my test servers that I'm not entirely sure
> how to solve, and would appreciate any advice people have.
> We are not using replication on any of our DBs, and so far as I know
> this instance/DB has never been configured for replication. Currently,
> Enterprise Manager is saying that it's not set up as a publisher or
> subscriber, yet one of our stored procedures is throwing error 7139
> ("Length of text, ntext, or image data (%ld) to be replicated exceeds
> configured maximum %ld.") in some cases where a text column is being
> updated.
> After some investigation, it turns out that of the 400 tables in the
> DB, 10 of them have related syncobj_0x... views in the DB. So it
> appears to me at some point in the past there may have been an aborted
> attempt at setting up snapshot replication by one of my developers, or
> by some package, or something.
> My question now is twofold.
> First, has anyone seen this kind of thing before or have any idea what
> may have gotten our DB to this state? Is it the expected state of
> things if replication was configured and then turned off?
> Second, what do I need to do to diagnose how extensive this
> partial-setup is, and how do I clean it up? Is it as simple as
> dropping the syncobj views? Are there other things I should look for
> in the system tables to make sure there aren't other parts incorrectly
> set up?
> Thanks in advance for any help.
> -Aaron
>
|||Sync objects are used by the bcp process while generating the snapshot for
every replication type - EXCEPT merge.Try it.
Nor are these views are not referenced in the merge replication triggers. Do
the following
sp_helptrigger MergePublishedTable
and look for the triggers, they will look like this
ins_8C282332351F48AC95B3B81552F867BA
upd_8C282332351F48AC95B3B81552F867BA
del_8C282332351F48AC95B3B81552F867BA
then issue a sp_helptext against the triggers and you will not find the sync
objects referenced in the triggers, nor the views of the form ctsv_GUID and
tsvw_GUID which the triggers themselves reference.
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
"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:eD7DYEpEGHA.3000@.TK2MSFTNGP14.phx.gbl...
> Have I seen this? More times than I can count. Is it normal? No. But
> it happens often enough. If you have synchobj... in the database, then it
> was most likely merge which was setup and then removed which didn't take
> it out cleanly. (This normally happens when you restore a replicated
> database to a server which isn't configured for publishing.) If you look
> at the table, you will more than likely have 3 triggers created against
> the table. Those triggers are going to reference the views you mentioned.
> You should also have a rowguid column on the table.
> You can have triggers, views, procs, etc. in there.
> sp_removedbreplication should clean everything up. If it doesn't, then
> you can manually drop any triggers, views, procedures, indexes, and
> columns that are related to replication. You then need to issue an
> sp_dboption to turn off the replication related database options if they
> aren't off already. You may have to go as far as turning on allow updates
> and resetting the replinfo column in sysobjects to 0.
> --
> Mike
> Mentor
> Solid Quality Learning
> http://www.solidqualitylearning.com
>
> <alouts@.hotmail.com> wrote in message
> news:1136509281.066817.40350@.z14g2000cwz.googlegro ups.com...
>
Subscribe to:
Posts (Atom)