RMO Equivalent for Merge Replication T-SQL Procedures

We want to have some information about RMO (Replication Management Objects in SQL Server 2005) equivalent for the following Merge Replication T-SQL procedures.

sp_changemergearticle
sp_helpmergearticleconflicts
sp_helpmergeconflictrows
sp_helpmergedeleteconflictrows
sp_deletemergeconflictrow

Since I was not aware of these RMO properties, here is a response from someone who has that knowledge.

Changing properties in MergeArticle object should be calling sp_changemergearticle.

Replicationdatabase.EnumCOnflcitTables should be equivalent to sp_hlepmergecarticleConflicts.

I don’t think we have any RMO methods that are equivalent to the last 3 proc calls

|||Thanks Mahesh, for your information. We will check that, if you can find any equivalent implementation for other three methods or any work around in order to use the other three methods that would be great ful.

Thanks
VDeevi.|||Sure, I will let you know.
Meanwhile, cant you wrap the other calls as a sql statement and make the calls?Also, you could add your logic in that! wouldnt that work for you?|||Thanks for your information. We will try to build the components based on the sproc calls to these stored procedures using ADO.Net. Once again thanks for your help.

Thankssql

right padding equivalent

Hi everyone,

Please excuse me if this has been asked before or sounds a bit dim.
This is a question asked on another forum but the solutions being
offered are focussing on programming rather than letting the DB server
do the work, which I'm not sure is the most efficient solution.
However, my confession is I dont use SQL server so can't help them
directly with the syntax. Hopefully you can help me help them and learn
a little about SQL Server in the process.

Trying to right pad a first name field so the padded string is a total
of 30 chars. It will be output concatenated with the last name field,
and each field separated with a "|". So that when output it reads
something like:

fname | mylastname

Syntax given was:

select id,
substring((last_name+','+rtrim(' '+isnull(level,'))+'
'+rtrim(isnull(first_name,'))+space(30)),1,30)+ ' | ' as student_name
from student

Issue: It appears this is padding correctly but the spaces are not
rendering in the browser. (I have no way to check this as I don't use
sqlserver. However, I can understand that multiple spaces are not going
to render in the client browser, if indeed the query is padding with
spaces.

Question: Instead of using space(), can replicate() be used and a
unicode space representation rather than an actual space be used? Or,
is there a better way that will ensure
the padding shows in browser?
I guess a fixed width font would also need to be used otherwise the
30-char blocks could wind up being different widths, which would defeat
the purpose.

If there is something I've missed, or you have any suggestions, I'm
keen to learn.

TYhanks in advance,

Lossedlossed wrote:
> Issue: It appears this is padding correctly but the spaces are not
> rendering in the browser. (I have no way to check this as I don't use
> sqlserver. However, I can understand that multiple spaces are not going
> to render in the client browser, if indeed the query is padding with
> spaces.

You can view source with the browser. Then highlight the text to see
whether the spaces are there or not.|||Yes but view what?
I do not use sql server so can't build the query to output to the
browser.
Sorry if I did not make that clear enough.

Bucky wrote:
> lossed wrote:
> > Issue: It appears this is padding correctly but the spaces are not
> > rendering in the browser. (I have no way to check this as I don't use
> > sqlserver. However, I can understand that multiple spaces are not going
> > to render in the client browser, if indeed the query is padding with
> > spaces.
> You can view source with the browser. Then highlight the text to see
> whether the spaces are there or not.|||HTML strips extra whitespace out when rendering. Maybe appending a bunch of
" " non-breaking spaces to the end would resolve your display issue?

"lossed" <hundyhunter@.gmail.com> wrote in message
news:1151445410.988182.165360@.x69g2000cwx.googlegr oups.com...
> Hi everyone,
> Please excuse me if this has been asked before or sounds a bit dim.
> This is a question asked on another forum but the solutions being
> offered are focussing on programming rather than letting the DB server
> do the work, which I'm not sure is the most efficient solution.
> However, my confession is I dont use SQL server so can't help them
> directly with the syntax. Hopefully you can help me help them and learn
> a little about SQL Server in the process.
> Trying to right pad a first name field so the padded string is a total
> of 30 chars. It will be output concatenated with the last name field,
> and each field separated with a "|". So that when output it reads
> something like:
> fname | mylastname
>
> Syntax given was:
> select id,
> substring((last_name+','+rtrim(' '+isnull(level,'))+'
> '+rtrim(isnull(first_name,'))+space(30)),1,30)+ ' | ' as student_name
> from student
> Issue: It appears this is padding correctly but the spaces are not
> rendering in the browser. (I have no way to check this as I don't use
> sqlserver. However, I can understand that multiple spaces are not going
> to render in the client browser, if indeed the query is padding with
> spaces.
> Question: Instead of using space(), can replicate() be used and a
> unicode space representation rather than an actual space be used? Or,
> is there a better way that will ensure
> the padding shows in browser?
> I guess a fixed width font would also need to be used otherwise the
> 30-char blocks could wind up being different widths, which would defeat
> the purpose.
> If there is something I've missed, or you have any suggestions, I'm
> keen to learn.
> TYhanks in advance,
> Lossed|||Tks Mike,

Do you know what would be the query syntax that will concatenate the
first_name field with the required number of no breaking spaces?

Mike C# wrote:
> HTML strips extra whitespace out when rendering. Maybe appending a bunch of
> " " non-breaking spaces to the end would resolve your display issue?
> "lossed" <hundyhunter@.gmail.com> wrote in message
> news:1151445410.988182.165360@.x69g2000cwx.googlegr oups.com...
> > Hi everyone,
> > Please excuse me if this has been asked before or sounds a bit dim.
> > This is a question asked on another forum but the solutions being
> > offered are focussing on programming rather than letting the DB server
> > do the work, which I'm not sure is the most efficient solution.
> > However, my confession is I dont use SQL server so can't help them
> > directly with the syntax. Hopefully you can help me help them and learn
> > a little about SQL Server in the process.
> > Trying to right pad a first name field so the padded string is a total
> > of 30 chars. It will be output concatenated with the last name field,
> > and each field separated with a "|". So that when output it reads
> > something like:
> > fname | mylastname
> > Syntax given was:
> > select id,
> > substring((last_name+','+rtrim(' '+isnull(level,'))+'
> > '+rtrim(isnull(first_name,'))+space(30)),1,30)+ ' | ' as student_name
> > from student
> > Issue: It appears this is padding correctly but the spaces are not
> > rendering in the browser. (I have no way to check this as I don't use
> > sqlserver. However, I can understand that multiple spaces are not going
> > to render in the client browser, if indeed the query is padding with
> > spaces.
> > Question: Instead of using space(), can replicate() be used and a
> > unicode space representation rather than an actual space be used? Or,
> > is there a better way that will ensure
> > the padding shows in browser?
> > I guess a fixed width font would also need to be used otherwise the
> > 30-char blocks could wind up being different widths, which would defeat
> > the purpose.
> > If there is something I've missed, or you have any suggestions, I'm
> > keen to learn.
> > TYhanks in advance,
> > Lossed|||you can also surround it with <pre> tags

save this in a file and open it up in a browser, you will see that the
spaces are preserved
<pre> b b </pre
Denis the SQL Menace
http://sqlservercode.blogspot.com/

lossed wrote:
> Tks Mike,
> Do you know what would be the query syntax that will concatenate the
> first_name field with the required number of no breaking spaces?
> Mike C# wrote:
> > HTML strips extra whitespace out when rendering. Maybe appending a bunch of
> > " " non-breaking spaces to the end would resolve your display issue?
> > "lossed" <hundyhunter@.gmail.com> wrote in message
> > news:1151445410.988182.165360@.x69g2000cwx.googlegr oups.com...
> > > Hi everyone,
> > > > Please excuse me if this has been asked before or sounds a bit dim.
> > > This is a question asked on another forum but the solutions being
> > > offered are focussing on programming rather than letting the DB server
> > > do the work, which I'm not sure is the most efficient solution.
> > > However, my confession is I dont use SQL server so can't help them
> > > directly with the syntax. Hopefully you can help me help them and learn
> > > a little about SQL Server in the process.
> > > > Trying to right pad a first name field so the padded string is a total
> > > of 30 chars. It will be output concatenated with the last name field,
> > > and each field separated with a "|". So that when output it reads
> > > something like:
> > > > fname | mylastname
> > > > > Syntax given was:
> > > > select id,
> > > substring((last_name+','+rtrim(' '+isnull(level,'))+'
> > > '+rtrim(isnull(first_name,'))+space(30)),1,30)+ ' | ' as student_name
> > > from student
> > > > Issue: It appears this is padding correctly but the spaces are not
> > > rendering in the browser. (I have no way to check this as I don't use
> > > sqlserver. However, I can understand that multiple spaces are not going
> > > to render in the client browser, if indeed the query is padding with
> > > spaces.
> > > > Question: Instead of using space(), can replicate() be used and a
> > > unicode space representation rather than an actual space be used? Or,
> > > is there a better way that will ensure
> > > the padding shows in browser?
> > > I guess a fixed width font would also need to be used otherwise the
> > > 30-char blocks could wind up being different widths, which would defeat
> > > the purpose.
> > > > If there is something I've missed, or you have any suggestions, I'm
> > > keen to learn.
> > > > TYhanks in advance,
> > > > Lossed
>|||Tks Denis,

Do you know what would be the query syntax that will concatenate the
first_name field with the required number of no breaking spaces?

SQL Menace wrote:
> you can also surround it with <pre> tags
> save this in a file and open it up in a browser, you will see that the
> spaces are preserved
> <pre> b b </pre>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
> lossed wrote:
> > Tks Mike,
> > Do you know what would be the query syntax that will concatenate the
> > first_name field with the required number of no breaking spaces?
> > Mike C# wrote:
> > > HTML strips extra whitespace out when rendering. Maybe appending a bunch of
> > > " " non-breaking spaces to the end would resolve your display issue?
> > > > "lossed" <hundyhunter@.gmail.com> wrote in message
> > > news:1151445410.988182.165360@.x69g2000cwx.googlegr oups.com...
> > > > Hi everyone,
> > > > > > Please excuse me if this has been asked before or sounds a bit dim.
> > > > This is a question asked on another forum but the solutions being
> > > > offered are focussing on programming rather than letting the DB server
> > > > do the work, which I'm not sure is the most efficient solution.
> > > > However, my confession is I dont use SQL server so can't help them
> > > > directly with the syntax. Hopefully you can help me help them and learn
> > > > a little about SQL Server in the process.
> > > > > > Trying to right pad a first name field so the padded string is a total
> > > > of 30 chars. It will be output concatenated with the last name field,
> > > > and each field separated with a "|". So that when output it reads
> > > > something like:
> > > > > > fname | mylastname
> > > > > > > > Syntax given was:
> > > > > > select id,
> > > > substring((last_name+','+rtrim(' '+isnull(level,'))+'
> > > > '+rtrim(isnull(first_name,'))+space(30)),1,30)+ ' | ' as student_name
> > > > from student
> > > > > > Issue: It appears this is padding correctly but the spaces are not
> > > > rendering in the browser. (I have no way to check this as I don't use
> > > > sqlserver. However, I can understand that multiple spaces are not going
> > > > to render in the client browser, if indeed the query is padding with
> > > > spaces.
> > > > > > Question: Instead of using space(), can replicate() be used and a
> > > > unicode space representation rather than an actual space be used? Or,
> > > > is there a better way that will ensure
> > > > the padding shows in browser?
> > > > I guess a fixed width font would also need to be used otherwise the
> > > > 30-char blocks could wind up being different widths, which would defeat
> > > > the purpose.
> > > > > > If there is something I've missed, or you have any suggestions, I'm
> > > > keen to learn.
> > > > > > TYhanks in advance,
> > > > > > Lossed
> >|||lossed (hundyhunter@.gmail.com) writes:
> Please excuse me if this has been asked before or sounds a bit dim.
> This is a question asked on another forum but the solutions being
> offered are focussing on programming rather than letting the DB server
> do the work, which I'm not sure is the most efficient solution.
> However, my confession is I dont use SQL server so can't help them
> directly with the syntax. Hopefully you can help me help them and learn
> a little about SQL Server in the process.
> Trying to right pad a first name field so the padded string is a total
> of 30 chars. It will be output concatenated with the last name field,
> and each field separated with a "|". So that when output it reads
> something like:
> fname | mylastname
>...
> Issue: It appears this is padding correctly but the spaces are not
> rendering in the browser. (I have no way to check this as I don't use
> sqlserver. However, I can understand that multiple spaces are not going
> to render in the client browser, if indeed the query is padding with
> spaces.

This sounds like an HTML design issue to me. Use a table with an invisible
border, rather than putting and all that stuff in SQL Server.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Tks Erland,

Do you know what would be the query syntax that will concatenate the
first_name field with the required number of no breaking spaces?

Erland Sommarskog wrote:
> lossed (hundyhunter@.gmail.com) writes:
> > Please excuse me if this has been asked before or sounds a bit dim.
> > This is a question asked on another forum but the solutions being
> > offered are focussing on programming rather than letting the DB server
> > do the work, which I'm not sure is the most efficient solution.
> > However, my confession is I dont use SQL server so can't help them
> > directly with the syntax. Hopefully you can help me help them and learn
> > a little about SQL Server in the process.
> > Trying to right pad a first name field so the padded string is a total
> > of 30 chars. It will be output concatenated with the last name field,
> > and each field separated with a "|". So that when output it reads
> > something like:
> > fname | mylastname
> >...
> > Issue: It appears this is padding correctly but the spaces are not
> > rendering in the browser. (I have no way to check this as I don't use
> > sqlserver. However, I can understand that multiple spaces are not going
> > to render in the client browser, if indeed the query is padding with
> > spaces.
> This sounds like an HTML design issue to me. Use a table with an invisible
> border, rather than putting and all that stuff in SQL Server.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||You could create a UDF like this to do it:

CREATE FUNCTION dbo.fn_HtmlHardPad(@.string VARCHAR(200), @.length INT)
RETURNS VARCHAR(500)
AS
BEGIN
RETURN RTRIM(@.string) + REPLICATE(' ', @.length - LEN(@.string))
END
GO

DECLARE @.test CHAR(30)
DECLARE @.padded VARCHAR(300)
SELECT @.test = 'Hello there'
SELECT @.padded = dbo.fn_HtmlHardPad(@.test, 20)
SELECT @.padded

"lossed" <lossedspam@.hotmail.com> wrote in message
news:1151528687.660240.231810@.d56g2000cwd.googlegr oups.com...
> Tks Denis,
> Do you know what would be the query syntax that will concatenate the
> first_name field with the required number of no breaking spaces?
> SQL Menace wrote:
>> you can also surround it with <pre> tags
>>
>> save this in a file and open it up in a browser, you will see that the
>> spaces are preserved
>> <pre> b b </pre>
>>
>> Denis the SQL Menace
>> http://sqlservercode.blogspot.com/
>>
>> lossed wrote:
>> > Tks Mike,
>>> > Do you know what would be the query syntax that will concatenate the
>> > first_name field with the required number of no breaking spaces?
>>> > Mike C# wrote:
>> > > HTML strips extra whitespace out when rendering. Maybe appending a
>> > > bunch of
>> > > " " non-breaking spaces to the end would resolve your display
>> > > issue?
>> >> > > "lossed" <hundyhunter@.gmail.com> wrote in message
>> > > news:1151445410.988182.165360@.x69g2000cwx.googlegr oups.com...
>> > > > Hi everyone,
>> > >> > > > Please excuse me if this has been asked before or sounds a bit dim.
>> > > > This is a question asked on another forum but the solutions being
>> > > > offered are focussing on programming rather than letting the DB
>> > > > server
>> > > > do the work, which I'm not sure is the most efficient solution.
>> > > > However, my confession is I dont use SQL server so can't help them
>> > > > directly with the syntax. Hopefully you can help me help them and
>> > > > learn
>> > > > a little about SQL Server in the process.
>> > >> > > > Trying to right pad a first name field so the padded string is a
>> > > > total
>> > > > of 30 chars. It will be output concatenated with the last name
>> > > > field,
>> > > > and each field separated with a "|". So that when output it reads
>> > > > something like:
>> > >> > > > fname | mylastname
>> > >> > >> > > > Syntax given was:
>> > >> > > > select id,
>> > > > substring((last_name+','+rtrim(' '+isnull(level,'))+'
>> > > > '+rtrim(isnull(first_name,'))+space(30)),1,30)+ ' | ' as
>> > > > student_name
>> > > > from student
>> > >> > > > Issue: It appears this is padding correctly but the spaces are not
>> > > > rendering in the browser. (I have no way to check this as I don't
>> > > > use
>> > > > sqlserver. However, I can understand that multiple spaces are not
>> > > > going
>> > > > to render in the client browser, if indeed the query is padding
>> > > > with
>> > > > spaces.
>> > >> > > > Question: Instead of using space(), can replicate() be used and a
>> > > > unicode space representation rather than an actual space be used?
>> > > > Or,
>> > > > is there a better way that will ensure
>> > > > the padding shows in browser?
>> > > > I guess a fixed width font would also need to be used otherwise the
>> > > > 30-char blocks could wind up being different widths, which would
>> > > > defeat
>> > > > the purpose.
>> > >> > > > If there is something I've missed, or you have any suggestions, I'm
>> > > > keen to learn.
>> > >> > > > TYhanks in advance,
>> > >> > > > Lossed
>> >|||Thanks very much Mike,

That's getting more along the lines I had hoped for.
Is there a way to simplify it into just the one select statement rather
than a udf?
I'm thinking along these lines:

SELECT RTRIM(last_name) + REPLICATE(' ', 30 - LEN(last_name)) + '
| ' + first_name AS student_name
FROM student

Would that work?

Thanks for your time and help thus far.

Mike C# wrote:
> You could create a UDF like this to do it:
> CREATE FUNCTION dbo.fn_HtmlHardPad(@.string VARCHAR(200), @.length INT)
> RETURNS VARCHAR(500)
> AS
> BEGIN
> RETURN RTRIM(@.string) + REPLICATE(' ', @.length - LEN(@.string))
> END
> GO
> DECLARE @.test CHAR(30)
> DECLARE @.padded VARCHAR(300)
> SELECT @.test = 'Hello there'
> SELECT @.padded = dbo.fn_HtmlHardPad(@.test, 20)
> SELECT @.padded
> "lossed" <lossedspam@.hotmail.com> wrote in message
> news:1151528687.660240.231810@.d56g2000cwd.googlegr oups.com...
> > Tks Denis,
> > Do you know what would be the query syntax that will concatenate the
> > first_name field with the required number of no breaking spaces?
> > SQL Menace wrote:
> >> you can also surround it with <pre> tags
> >>
> >> save this in a file and open it up in a browser, you will see that the
> >> spaces are preserved
> >> <pre> b b </pre>
> >>
> >> Denis the SQL Menace
> >> http://sqlservercode.blogspot.com/
> >>
> >> lossed wrote:
> >> > Tks Mike,
> >> >> > Do you know what would be the query syntax that will concatenate the
> >> > first_name field with the required number of no breaking spaces?
> >> >> > Mike C# wrote:
> >> > > HTML strips extra whitespace out when rendering. Maybe appending a
> >> > > bunch of
> >> > > " " non-breaking spaces to the end would resolve your display
> >> > > issue?
> >> > >> > > "lossed" <hundyhunter@.gmail.com> wrote in message
> >> > > news:1151445410.988182.165360@.x69g2000cwx.googlegr oups.com...
> >> > > > Hi everyone,
> >> > > >> > > > Please excuse me if this has been asked before or sounds a bit dim.
> >> > > > This is a question asked on another forum but the solutions being
> >> > > > offered are focussing on programming rather than letting the DB
> >> > > > server
> >> > > > do the work, which I'm not sure is the most efficient solution.
> >> > > > However, my confession is I dont use SQL server so can't help them
> >> > > > directly with the syntax. Hopefully you can help me help them and
> >> > > > learn
> >> > > > a little about SQL Server in the process.
> >> > > >> > > > Trying to right pad a first name field so the padded string is a
> >> > > > total
> >> > > > of 30 chars. It will be output concatenated with the last name
> >> > > > field,
> >> > > > and each field separated with a "|". So that when output it reads
> >> > > > something like:
> >> > > >> > > > fname | mylastname
> >> > > >> > > >> > > > Syntax given was:
> >> > > >> > > > select id,
> >> > > > substring((last_name+','+rtrim(' '+isnull(level,'))+'
> >> > > > '+rtrim(isnull(first_name,'))+space(30)),1,30)+ ' | ' as
> >> > > > student_name
> >> > > > from student
> >> > > >> > > > Issue: It appears this is padding correctly but the spaces are not
> >> > > > rendering in the browser. (I have no way to check this as I don't
> >> > > > use
> >> > > > sqlserver. However, I can understand that multiple spaces are not
> >> > > > going
> >> > > > to render in the client browser, if indeed the query is padding
> >> > > > with
> >> > > > spaces.
> >> > > >> > > > Question: Instead of using space(), can replicate() be used and a
> >> > > > unicode space representation rather than an actual space be used?
> >> > > > Or,
> >> > > > is there a better way that will ensure
> >> > > > the padding shows in browser?
> >> > > > I guess a fixed width font would also need to be used otherwise the
> >> > > > 30-char blocks could wind up being different widths, which would
> >> > > > defeat
> >> > > > the purpose.
> >> > > >> > > > If there is something I've missed, or you have any suggestions, I'm
> >> > > > keen to learn.
> >> > > >> > > > TYhanks in advance,
> >> > > >> > > > Lossed
> >> >|||That should do the trick. Just make sure that LEN(last_name) can't be
greater than 30 characters or you might get an error message from the
replicate() function.

"lossed" <lossedspam@.hotmail.com> wrote in message
news:1151552706.557852.103250@.b68g2000cwa.googlegr oups.com...
> Thanks very much Mike,
> That's getting more along the lines I had hoped for.
> Is there a way to simplify it into just the one select statement rather
> than a udf?
> I'm thinking along these lines:
> SELECT RTRIM(last_name) + REPLICATE(' ', 30 - LEN(last_name)) + '
> | ' + first_name AS student_name
> FROM student
> Would that work?
> Thanks for your time and help thus far.
> Mike C# wrote:
>> You could create a UDF like this to do it:
>>
>> CREATE FUNCTION dbo.fn_HtmlHardPad(@.string VARCHAR(200), @.length INT)
>> RETURNS VARCHAR(500)
>> AS
>> BEGIN
>> RETURN RTRIM(@.string) + REPLICATE(' ', @.length - LEN(@.string))
>> END
>> GO
>>
>> DECLARE @.test CHAR(30)
>> DECLARE @.padded VARCHAR(300)
>> SELECT @.test = 'Hello there'
>> SELECT @.padded = dbo.fn_HtmlHardPad(@.test, 20)
>> SELECT @.padded
>>
>> "lossed" <lossedspam@.hotmail.com> wrote in message
>> news:1151528687.660240.231810@.d56g2000cwd.googlegr oups.com...
>> > Tks Denis,
>>> > Do you know what would be the query syntax that will concatenate the
>> > first_name field with the required number of no breaking spaces?
>>> > SQL Menace wrote:
>> >> you can also surround it with <pre> tags
>> >>
>> >> save this in a file and open it up in a browser, you will see that the
>> >> spaces are preserved
>> >> <pre> b b </pre>
>> >>
>> >> Denis the SQL Menace
>> >> http://sqlservercode.blogspot.com/
>> >>
>> >> lossed wrote:
>> >> > Tks Mike,
>> >>> >> > Do you know what would be the query syntax that will concatenate the
>> >> > first_name field with the required number of no breaking spaces?
>> >>> >> > Mike C# wrote:
>> >> > > HTML strips extra whitespace out when rendering. Maybe appending
>> >> > > a
>> >> > > bunch of
>> >> > > " " non-breaking spaces to the end would resolve your display
>> >> > > issue?
>> >> >> >> > > "lossed" <hundyhunter@.gmail.com> wrote in message
>> >> > > news:1151445410.988182.165360@.x69g2000cwx.googlegr oups.com...
>> >> > > > Hi everyone,
>> >> > >> >> > > > Please excuse me if this has been asked before or sounds a bit
>> >> > > > dim.
>> >> > > > This is a question asked on another forum but the solutions
>> >> > > > being
>> >> > > > offered are focussing on programming rather than letting the DB
>> >> > > > server
>> >> > > > do the work, which I'm not sure is the most efficient solution.
>> >> > > > However, my confession is I dont use SQL server so can't help
>> >> > > > them
>> >> > > > directly with the syntax. Hopefully you can help me help them
>> >> > > > and
>> >> > > > learn
>> >> > > > a little about SQL Server in the process.
>> >> > >> >> > > > Trying to right pad a first name field so the padded string is a
>> >> > > > total
>> >> > > > of 30 chars. It will be output concatenated with the last name
>> >> > > > field,
>> >> > > > and each field separated with a "|". So that when output it
>> >> > > > reads
>> >> > > > something like:
>> >> > >> >> > > > fname | mylastname
>> >> > >> >> > >> >> > > > Syntax given was:
>> >> > >> >> > > > select id,
>> >> > > > substring((last_name+','+rtrim(' '+isnull(level,'))+'
>> >> > > > '+rtrim(isnull(first_name,'))+space(30)),1,30)+ ' | ' as
>> >> > > > student_name
>> >> > > > from student
>> >> > >> >> > > > Issue: It appears this is padding correctly but the spaces are
>> >> > > > not
>> >> > > > rendering in the browser. (I have no way to check this as I
>> >> > > > don't
>> >> > > > use
>> >> > > > sqlserver. However, I can understand that multiple spaces are
>> >> > > > not
>> >> > > > going
>> >> > > > to render in the client browser, if indeed the query is padding
>> >> > > > with
>> >> > > > spaces.
>> >> > >> >> > > > Question: Instead of using space(), can replicate() be used and
>> >> > > > a
>> >> > > > unicode space representation rather than an actual space be
>> >> > > > used?
>> >> > > > Or,
>> >> > > > is there a better way that will ensure
>> >> > > > the padding shows in browser?
>> >> > > > I guess a fixed width font would also need to be used otherwise
>> >> > > > the
>> >> > > > 30-char blocks could wind up being different widths, which would
>> >> > > > defeat
>> >> > > > the purpose.
>> >> > >> >> > > > If there is something I've missed, or you have any suggestions,
>> >> > > > I'm
>> >> > > > keen to learn.
>> >> > >> >> > > > TYhanks in advance,
>> >> > >> >> > > > Lossed
>> >> > >|||Thank you so much Mike.
Very much appreciated.

Mike C# wrote:
> That should do the trick. Just make sure that LEN(last_name) can't be
> greater than 30 characters or you might get an error message from the
> replicate() function.
> "lossed" <lossedspam@.hotmail.com> wrote in message
> news:1151552706.557852.103250@.b68g2000cwa.googlegr oups.com...
> > Thanks very much Mike,
> > That's getting more along the lines I had hoped for.
> > Is there a way to simplify it into just the one select statement rather
> > than a udf?
> > I'm thinking along these lines:
> > SELECT RTRIM(last_name) + REPLICATE(' ', 30 - LEN(last_name)) + '
> > | ' + first_name AS student_name
> > FROM student
> > Would that work?
> > Thanks for your time and help thus far.
> > Mike C# wrote:
> >> You could create a UDF like this to do it:
> >>
> >> CREATE FUNCTION dbo.fn_HtmlHardPad(@.string VARCHAR(200), @.length INT)
> >> RETURNS VARCHAR(500)
> >> AS
> >> BEGIN
> >> RETURN RTRIM(@.string) + REPLICATE(' ', @.length - LEN(@.string))
> >> END
> >> GO
> >>
> >> DECLARE @.test CHAR(30)
> >> DECLARE @.padded VARCHAR(300)
> >> SELECT @.test = 'Hello there'
> >> SELECT @.padded = dbo.fn_HtmlHardPad(@.test, 20)
> >> SELECT @.padded
> >>
> >> "lossed" <lossedspam@.hotmail.com> wrote in message
> >> news:1151528687.660240.231810@.d56g2000cwd.googlegr oups.com...
> >> > Tks Denis,
> >> >> > Do you know what would be the query syntax that will concatenate the
> >> > first_name field with the required number of no breaking spaces?
> >> >> > SQL Menace wrote:
> >> >> you can also surround it with <pre> tags
> >> >>
> >> >> save this in a file and open it up in a browser, you will see that the
> >> >> spaces are preserved
> >> >> <pre> b b </pre>
> >> >>
> >> >> Denis the SQL Menace
> >> >> http://sqlservercode.blogspot.com/
> >> >>
> >> >> lossed wrote:
> >> >> > Tks Mike,
> >> >> >> >> > Do you know what would be the query syntax that will concatenate the
> >> >> > first_name field with the required number of no breaking spaces?
> >> >> >> >> > Mike C# wrote:
> >> >> > > HTML strips extra whitespace out when rendering. Maybe appending
> >> >> > > a
> >> >> > > bunch of
> >> >> > > " " non-breaking spaces to the end would resolve your display
> >> >> > > issue?
> >> >> > >> >> > > "lossed" <hundyhunter@.gmail.com> wrote in message
> >> >> > > news:1151445410.988182.165360@.x69g2000cwx.googlegr oups.com...
> >> >> > > > Hi everyone,
> >> >> > > >> >> > > > Please excuse me if this has been asked before or sounds a bit
> >> >> > > > dim.
> >> >> > > > This is a question asked on another forum but the solutions
> >> >> > > > being
> >> >> > > > offered are focussing on programming rather than letting the DB
> >> >> > > > server
> >> >> > > > do the work, which I'm not sure is the most efficient solution.
> >> >> > > > However, my confession is I dont use SQL server so can't help
> >> >> > > > them
> >> >> > > > directly with the syntax. Hopefully you can help me help them
> >> >> > > > and
> >> >> > > > learn
> >> >> > > > a little about SQL Server in the process.
> >> >> > > >> >> > > > Trying to right pad a first name field so the padded string is a
> >> >> > > > total
> >> >> > > > of 30 chars. It will be output concatenated with the last name
> >> >> > > > field,
> >> >> > > > and each field separated with a "|". So that when output it
> >> >> > > > reads
> >> >> > > > something like:
> >> >> > > >> >> > > > fname | mylastname
> >> >> > > >> >> > > >> >> > > > Syntax given was:
> >> >> > > >> >> > > > select id,
> >> >> > > > substring((last_name+','+rtrim(' '+isnull(level,'))+'
> >> >> > > > '+rtrim(isnull(first_name,'))+space(30)),1,30)+ ' | ' as
> >> >> > > > student_name
> >> >> > > > from student
> >> >> > > >> >> > > > Issue: It appears this is padding correctly but the spaces are
> >> >> > > > not
> >> >> > > > rendering in the browser. (I have no way to check this as I
> >> >> > > > don't
> >> >> > > > use
> >> >> > > > sqlserver. However, I can understand that multiple spaces are
> >> >> > > > not
> >> >> > > > going
> >> >> > > > to render in the client browser, if indeed the query is padding
> >> >> > > > with
> >> >> > > > spaces.
> >> >> > > >> >> > > > Question: Instead of using space(), can replicate() be used and
> >> >> > > > a
> >> >> > > > unicode space representation rather than an actual space be
> >> >> > > > used?
> >> >> > > > Or,
> >> >> > > > is there a better way that will ensure
> >> >> > > > the padding shows in browser?
> >> >> > > > I guess a fixed width font would also need to be used otherwise
> >> >> > > > the
> >> >> > > > 30-char blocks could wind up being different widths, which would
> >> >> > > > defeat
> >> >> > > > the purpose.
> >> >> > > >> >> > > > If there is something I've missed, or you have any suggestions,
> >> >> > > > I'm
> >> >> > > > keen to learn.
> >> >> > > >> >> > > > TYhanks in advance,
> >> >> > > >> >> > > > Lossed
> >> >> > > >|||lossed (lossedspam@.hotmail.com) writes:
> Do you know what would be the query syntax that will concatenate the
> first_name field with the required number of no breaking spaces?

Apparently you did not read my post, but only reply to whatever come
in the way.

Not that I would ever get the idea to picture myself as a expert on
HTML authoring, but using to fill up text does not sound like
a good idea. Least of all with a proportional font...

So I maintain that a <TABLE> tag is the way to go.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

RIGHT OUTER JOIN

Hi,

i need an equivalent code to

RIGHT OUTER JOIN

i do not want to use that.

ex,

SELECT dbo.Test1.a, dbo.Test1.b, dbo.Test1.c, dbo.Test.t
FROM dbo.Test RIGHT OUTER JOIN
dbo.Test1 ON dbo.Test.a = dbo.Test1.a

thanks

Will you accept the left join equivalent? Why are you avoiding the RIGHT JOIN?|||

i ran the query in sql2005 thats fine

in access it wont.

|||Well, do you even need to join to the TEST table? Your query doesn't include any data from the TEST table.|||

Hi,

What version of access are you using? Just to see what syntax is supported.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

table a table b

a b c a d

1 10 x3 1 9

2 20 x2 2 8

3 30 x1 5 7

i need to get this without a join:

a b c d

1 10 x3 9

2 20 x2 8

3 30 x1 null

5 null null 7

I have conditions that i should not use right join, i know it is some access issue, i do not know versions.

so basically i write most codes without using joins, though i know ...

thanks,

reverse like?

I am trying to do the equivalent of following pseudo-code:
select id from tablename where %stringcolumn like 'mystring'
so I would have a record returned if the values in stringcolumn were:
ystring
string
tring
ring
etc..
To me this seems like some kind of backwards 'like' but I can't get it
to work.
Any ideas?
ChandyHi Chandy,
I once did that to implement a caller identitfication in our company.
Due to the fact that not every phone extension of the customer was know
by us I cutr the numbers one by one frome the right side on, so it was
something like:
Calling number is: 087776-37 (know Number is 087776-0) the attempts
are:
1: 0877763
2: 087776 (matches 087776 of 087776-0) --Hit
It cut these down one by one in a procedure till I reached a minimun
number which has to be matched. So (my 0.02 $), write a procedure which
does that in a loop.
HTH, Jens Suessmeyer.|||Hi Chandy,
Just trying to get my head round what you want.
At what part do you finish, do you mean also if it contains ing, ng and g?
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
<chandy@.totalise.co.uk> wrote in message
news:1137758853.742762.291710@.o13g2000cwo.googlegroups.com...
>I am trying to do the equivalent of following pseudo-code:
> select id from tablename where %stringcolumn like 'mystring'
> so I would have a record returned if the values in stringcolumn were:
> ystring
> string
> tring
> ring
> etc..
> To me this seems like some kind of backwards 'like' but I can't get it
> to work.
> Any ideas?
> Chandy
>|||Hi Tony,
Yes, it would match against ing, ng and g, but not an empty sting or
null.
In reality the data would be domains and sub-domains, so I would be
testing
subdomain.domain.com
and wanting to match a row with
domain.com
but there could be many levels of sub-domain and different levels of
domains in the data so I wouldn't want to keep splitting the string and
re-testing if possible.
Chandy|||Hi Chandy,
This something like what you want....
declare @.seq table (
numb tinyint not null
)
insert @.seq values( 1 )
insert @.seq values( 2 )
insert @.seq values( 3 )
insert @.seq values( 4 )
insert @.seq values( 5 )
insert @.seq values( 6 )
insert @.seq values( 7 )
insert @.seq values( 8 )
insert @.seq values( 9 )
insert @.seq values( 10 )
declare @.source table (
searchtext varchar(500) not null
)
insert @.source values( 'this should tring be shown' )
insert @.source values( 'not this' )
select right( 'mystring', q.numb )
from @.seq q
select *
from @.source s
inner join @.seq q on s.searchtext like '% ' + right( 'mystring',
q.numb ) + ' %'
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
<chandy@.totalise.co.uk> wrote in message
news:1137761909.961033.42150@.g43g2000cwa.googlegroups.com...
> Hi Tony,
> Yes, it would match against ing, ng and g, but not an empty sting or
> null.
> In reality the data would be domains and sub-domains, so I would be
> testing
> subdomain.domain.com
> and wanting to match a row with
> domain.com
> but there could be many levels of sub-domain and different levels of
> domains in the data so I wouldn't want to keep splitting the string and
> re-testing if possible.
> Chandy
>|||Chandy, does this work for you?
ALTER TABLE MyTable
ADD ReverseMyColumn AS REVERSE(MyColumn)
CREATE INDEX IX_MyTable_ReverseMyColumn ON MyTable(ReverseMyColumn)
SELECT MyColumn
FROM MyTable
WHERE ReverseMyTable LIKE REVERSE(@.search)+'%'
HTH,
Gert-Jan
chandy@.totalise.co.uk wrote:
> Hi Tony,
> Yes, it would match against ing, ng and g, but not an empty sting or
> null.
> In reality the data would be domains and sub-domains, so I would be
> testing
> subdomain.domain.com
> and wanting to match a row with
> domain.com
> but there could be many levels of sub-domain and different levels of
> domains in the data so I wouldn't want to keep splitting the string and
> re-testing if possible.
> Chandy|||drop table #t
drop table #n
go
create table #t(id int, token varchar(10))
--create table #n(n int)
insert into #t values(1, 'ing')
insert into #t values(2, 'ring')
insert into #t values(3, 'mystring!')
insert into #t values(4, 'mystring')
go
select 1 n
into #n
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9
union all select 10
union all select 11
union all select 12
union all select 13
go
select n,token,substring('mystring', #n.n,100) from #t, #n
where substring('mystring', #n.n,100)=#t.token
n token
-- -- --
6 ing ing
5 ring ring
1 mystring mystring
(3 row(s) affected)|||Thanks to all for the suggestions. The REVERSE column looks the most
interesting, with a trigger to update/populate it I guess, but before I
saw that I simply did a loop over substrings of decreasings length
until I get a result then break the loop. Not as efficient but works
for me just now.
Chandy

Reverse equivalent to TOP

Is there anyway I can return only the last row of a query, like TOP does for the top most items?

I would like to return something like this:

SELECT BOTTOM 1 Column_C
FROM Table1
WHERE Column_A = something

Any help would be greatly appreciated. Thank you!

gerardkcohen:

Is there anyway I can return only the last row of a query, like TOP does for the top most items?

I would like to return something like this:

SELECT BOTTOM 1 Column_C
FROM Table1
WHERE Column_A = something

Any help would be greatly appreciated. Thank you!

gerardkcohen --

Use a subquery. Get IDs in the order you want using an "order by desc". Get the IDs you want using a "TOP". Get the data you want using a "select * ... where in". And so on.

Here is some sample code.

use northwind
go
--get all the rows, in order, to see what we are working with...
select * from Shippers order by ShipperID asc
/* output...

ShipperID   CompanyName                              Phone                   
---- ------------ --------
1           Speedy Express                           (503) 555-9831
2           United Package                           (503) 555-3199
3           Federal Shipping                         (503) 555-9931
(3 row(s) affected)
*/
--get the top 2 rows
select * from Shippers where ShipperID in (select top 2 ShipperID from Shippers order by ShipperID asc)
/* output...

ShipperID   CompanyName                              Phone                   
---- ------------ --------
1           Speedy Express                           (503) 555-9831
2           United Package                           (503) 555-3199
(2 row(s) affected)
*/
--get the bottom 2 rows
select * from Shippers where ShipperID in (select top 2 ShipperID from Shippers order by ShipperID desc)
/* output...

ShipperID   CompanyName                              Phone                   
---- ------------ --------
3           Federal Shipping                         (503) 555-9931
2           United Package                           (503) 555-3199
(2 row(s) affected)
*/
 

HTH.

Thank you.

-- Mark Kamoski

|||

You can still use TOP 1 to get the bottom 1 by adding ORDER BY Column_C DESC

Like:

SELECT TOP 1 Column_C
FROM Table1
WHERE Column_A = something

ORDER BY Column_C DESC

--edited

|||

Yes remember to changeBOTTOM -> top

SELECTtop 1 Column_C
FROM Table1
WHERE Column_A = something

ORDER BY Column_C DESC