Remove double results inside a delimited result

Hi,

I have now this (1 record) result:
;Admins;Sales;SalesAdmin;Other;Sales;Admins;All users;

Now, as you can see, there are some double rolenames.
I see admins and Sales 2 times between ";".

Is there a way to filter that out?
So that this will be the result:
;SalesAdmin;Other;Sales;Admins;All users;

Thanks!Concatenate the result set into one ";"-delimited string
parse the string and put each value into a table variable where the value doesn't already exist
Use this to create a string out of the values in your table variable

declare @.s varchar(8000)
select @.s = value + '; ' + COALESCE(@.s, '')
from @.table
if @.s is not null
 set @.s = substring(@.s, 1, LEN(@.s) - 1)
else
 set @.s = ''
return (left(@.s, 2000))

sql

Remove all copies of the same record from merge-replicated table in multiple databases

I would like to know if this is OK to remove all the copies of the
same record from a merge-replicated table in multiple databases
servers.
What I am trying to do is:
- Let say we have a table called tbProductionBatch in database
servers in three branch offices.
- Each branch office can add/update info in the local copy of this
table in the local database server.
- The data in this table is being replicated between these three
database servers using merge replication.
- Let say we want to get rid of a "batch" record from this table and
we want to get rid of all the copies of this "batch" record from all
three database server (then, we can see this "batch" goes away sooner
rather than waiting for merge replication to kick in).
- Soon after this, we want to add a similar "batch" record back to
this table in one database server, and we let the hourly merge
replication to replicate the info on this "batch" to other database
servers.
My questions are:
- Will this works?
- Is there a better alternative?
Thanks in advance for any info.
Jay Chan
yes it will work, but the "conflict", ie trying to delete a record which has
already being deleted will be logged in the conflict tables.
To the end user this will be transparent and will only show up when the dba
or user use conflict viewer to examine conflicts.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Jay Chan" <jaykchan@.hotmail.com> wrote in message
news:c7e5acb2.0407210720.63035aaa@.posting.google.c om...
> I would like to know if this is OK to remove all the copies of the
> same record from a merge-replicated table in multiple databases
> servers.
> What I am trying to do is:
> - Let say we have a table called tbProductionBatch in database
> servers in three branch offices.
> - Each branch office can add/update info in the local copy of this
> table in the local database server.
> - The data in this table is being replicated between these three
> database servers using merge replication.
> - Let say we want to get rid of a "batch" record from this table and
> we want to get rid of all the copies of this "batch" record from all
> three database server (then, we can see this "batch" goes away sooner
> rather than waiting for merge replication to kick in).
> - Soon after this, we want to add a similar "batch" record back to
> this table in one database server, and we let the hourly merge
> replication to replicate the info on this "batch" to other database
> servers.
> My questions are:
> - Will this works?
> - Is there a better alternative?
> Thanks in advance for any info.
> Jay Chan
|||> yes it will work, but the "conflict", ie trying to delete a record which has
> already being deleted will be logged in the conflict tables.
> To the end user this will be transparent and will only show up when the dba
> or user use conflict viewer to examine conflicts.
Great! I have this question at the back of my mind for years. Finally,
I get this answered. Now, I can go ahead to remove the copies of the
same record from all the databases.
Thanks a lot!
Jay Chan

Remote server time

Hi,

I have a datetime field with the default as getdate() for every record that is added to the DB.

However my server is 12 hours back from where I am.
How can I adjust the default time so it will show my time?

Many thanks,

JackYou could use DATEADD(hour,-12,GetDate()) as the default...