Right function doesn't handle ending whitespaces?

Have problem with the RIGHT function.

SELECT LEN(RIGHT('ABCD ',5))

Above return 4, but I expects 5. Is this a bug? The compability level i 90

Thanks,

John

The len function automatically trim the spaces on the tail end of the string.|||

The result from this one is "ABCD " with a withspace after D.

SELECT '"' + RIGHT('ABCD ',5) + '"'

How can LEN do so? It isn't a true value|||

Ok in SQL Server all the following values are equal..(while compare)

'ABCD'

'ABCD '

'ABCD '

'ABCD '

'ABCD '

Bcs SQL Server never consider the white space (any number of) at the end of the string. (while compare)

So if all these strings are equal then the length of the each string also equal ie, 4.

Got it?

|||

It depends what kind of operator or function you are using.

select 1

where'ABCD'like'ABCD '

select datalength('ABCD ')

go

AMB

|||Yep, use datalength() if you are interested in counting trailing blanks as part of the length.|||

Mani,

That's not complete accurate. SQL Server knows the values are NOT equal. The LEN() is designed to ignore the trailing spaces.

For example, using the values you have above:

Code Snippet

DECLARE @.MyTable table
( RowID int IDENTITY,
MyValue varchar(20)
)

INSERT INTO @.MyTable VALUES ( 'ABCD' )
INSERT INTO @.MyTable VALUES ( 'ABCD ' )
INSERT INTO @.MyTable VALUES ( 'ABCD ' )
INSERT INTO @.MyTable VALUES ( 'ABCD ' )
INSERT INTO @.MyTable VALUES ( 'ABCD ' )

SELECT SpaceNotTrimmed = ( MyValue + '<--' )
FROM @.MyTable

SpaceNotTrimmed
--
ABCD<--
ABCD <--
ABCD <--
ABCD <--
ABCD <--

As you can see from the example, the trailing spaces are definitely not ignored -they are there.

However the LEN() function does 'ignore' the trailing spaces, if you need to know the character count for a varchar field, you need to use the DATALENGTH() function.

This is one of those 'ANSI' related pecularities...

|||Thanks for all help!sql

RI, Replication Conflicts and architecture

Hi there,
We're looking at setting up a database infrastructure to handle the
following.
2 Physical sites on an 85% uptime VPN Link
There are multiple (10+) IT systems for various businesses on both sides of
this link.
We need to store 1 shared client table and then each systemwill store
auxillary client data and other tables with RI to the this client table.
We have large amounts of data in this client table with about 15% bad data
(Dups etc)
A hot topic of debate at current is:
If I have DB X (on Side A of the link) that deletes a client record after
deleting all RI, what happens to the client record in DB Y (on Side B of the
link) that still has RI. How should we architect for this - Merge Repl?
Transactional? Web Services? could these sorts of conflicts be handled via a
custom resolver? This is complexed by the fact that we may need to merge the
RI from 2 dups and delete one of them. This needs to be replicated out to
the other side of the link until all (10+) databases are accurate. As near
to real time as possible is required.
Any help much appreciated
For transactional replication:
If you have a parent table with a child table and do cascading
deletes/updates, and do not maintain the DRI on the Subscriber or you do
maintain it, but uncheck the enforce relationship for replication option on
the Subscriber, and both tables are published DRI will be maintained on the
subscriber automatically.
So if you delete a parent record, the corresponding child records will be
deleted on the Publisher. The delete on the parent will be replicated to the
subscriber and the delete of the child records will also be replicated to
the Subscriber as part of the same transaction.
For merge replication:
You need to set the cascading updates and deletes option on the Publisher
and deselect the enforce relationship for replication. The problem here is
that the deletes on the parent table might arrive on the subscriber before
or after the delete on the child. If these deletes or updates happen
between a batch boundary you may run into problems. Microsoft's solution to
this problem is to increate the UploadGenerationsPerBatch and
DownloadGenerationsPerBatch to something large like 1000, to ensure that the
deletes or updates which are part of the same transaction occur in the same
batch.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Murray Foxcroft" <murray(UseADot)foxcroft@.ast.co.za> wrote in message
news:ejvjTnJdEHA.2520@.TK2MSFTNGP12.phx.gbl...
> Hi there,
> We're looking at setting up a database infrastructure to handle the
> following.
> 2 Physical sites on an 85% uptime VPN Link
> There are multiple (10+) IT systems for various businesses on both sides
of
> this link.
> We need to store 1 shared client table and then each systemwill store
> auxillary client data and other tables with RI to the this client table.
> We have large amounts of data in this client table with about 15% bad data
> (Dups etc)
> A hot topic of debate at current is:
> If I have DB X (on Side A of the link) that deletes a client record after
> deleting all RI, what happens to the client record in DB Y (on Side B of
the
> link) that still has RI. How should we architect for this - Merge Repl?
> Transactional? Web Services? could these sorts of conflicts be handled via
a
> custom resolver? This is complexed by the fact that we may need to merge
the
> RI from 2 dups and delete one of them. This needs to be replicated out to
> the other side of the link until all (10+) databases are accurate. As near
> to real time as possible is required.
> Any help much appreciated
>
>
>
|||Hi Hilary, thanks for the reply - our complexity comes in when we need to
de-dup on the databases involved in the replication.
So: We find a duplicate client record - now we need to move all the DRI
through all systems to one of the clients THEN delete the other once all DRI
is removed.
How and which replication would you use for this?
Thanks in advance
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:esY7v9JdEHA.3476@.tk2msftngp13.phx.gbl...
> For transactional replication:
> If you have a parent table with a child table and do cascading
> deletes/updates, and do not maintain the DRI on the Subscriber or you do
> maintain it, but uncheck the enforce relationship for replication option
> on
> the Subscriber, and both tables are published DRI will be maintained on
> the
> subscriber automatically.
> So if you delete a parent record, the corresponding child records will be
> deleted on the Publisher. The delete on the parent will be replicated to
> the
> subscriber and the delete of the child records will also be replicated to
> the Subscriber as part of the same transaction.
> For merge replication:
> You need to set the cascading updates and deletes option on the Publisher
> and deselect the enforce relationship for replication. The problem here is
> that the deletes on the parent table might arrive on the subscriber before
> or after the delete on the child. If these deletes or updates happen
> between a batch boundary you may run into problems. Microsoft's solution
> to
> this problem is to increate the UploadGenerationsPerBatch and
> DownloadGenerationsPerBatch to something large like 1000, to ensure that
> the
> deletes or updates which are part of the same transaction occur in the
> same
> batch.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Murray Foxcroft" <murray(UseADot)foxcroft@.ast.co.za> wrote in message
> news:ejvjTnJdEHA.2520@.TK2MSFTNGP12.phx.gbl...
> of
> the
> a
> the
>
sql

reusing a single conversation handle

Hi

I have a replicated table that has a trigger attached to the it. The trigger fires off a service broker message for inserts. Originally for every insert, I would begin a conversation, send, and end the conversation when target send an end conversation. Since replication process is only using a single spid, I would like to reuse 1 conversation. the following is what I have for the send procedure in the initiator. I check the conversation_endpoints for any open conversation, if it's null, I start a new conversation and send else just send with the existing conversation. Is there anything wrong with this code? What could cause the conversation on the initiator to be null if I never end the conversation on the initiator side? thanks

DECLARE @.dialog_handle uniqueidentifier

select @.dialog_handle = conversation_handle from sys.conversation_endpoints where state = 'CO'

IF @.dialog_handle is NULL

BEGIN DIALOG CONVERSATION @.dialog_handle

FROM SERVICE [initiator]

TO SERVICE 'target'

ON CONTRACT [portcontract];

SEND ON CONVERSATION @.dialog_handle

MESSAGE TYPE [Port] (@.msg)

Hi

Reusing conversations is generally a good idea and one good way to achieve this is to store a mapping of spid to conversations handle in a lookup table. http://blogs.msdn.com/remusrusanu/archive/2007/05/02/recycling-conversations.aspx goes into the details and helps answer your questions.

Thanks

Ketan