Change font size w/ expression?

Hi I have a chart whose title I would like to format w different font sizes.
The chart title is:
<caption>="This is the chart main text" + vbcrlf +
First(Fields!subtitle.Value, "Text")</caption>
The main chart title is hard coded and the second line is a field.
I would like to change the font size on that second line. Is there a way to
reference the <caption> line in <style><fontsize> and parse through it?
I'm thinking this isn't possible, but why not ask.
Thanks!
MarcusOn Nov 27, 3:56 pm, Marcus K <Marc...@.discussions.microsoft.com>
wrote:
> Hi I have a chart whose title I would like to format w different font sizes.
> The chart title is:
> <caption>="This is the chart main text" + vbcrlf +
> First(Fields!subtitle.Value, "Text")</caption>
> The main chart title is hard coded and the second line is a field.
> I would like to change the font size on that second line. Is there a way to
> reference the <caption> line in <style><fontsize> and parse through it?
> I'm thinking this isn't possible, but why not ask.
> Thanks!
> Marcus
You will most likely want to create a custom ASP.NET application that
reads in the RDL file (possibly as an XML Document) and parses the XML
tags and writes the file back out -and/or- read the RDL file in via
Streamreader and assigns it to a string and then does a string replace
of the fontsize in the tags and then writes the file back out via
Streamwriter. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Thanks Enrique, but thats outside the scope of my knowledge.
Any chance there is a way to do it within the rdl?
"EMartinez" wrote:
> On Nov 27, 3:56 pm, Marcus K <Marc...@.discussions.microsoft.com>
> wrote:
> > Hi I have a chart whose title I would like to format w different font sizes.
> > The chart title is:
> >
> > <caption>="This is the chart main text" + vbcrlf +
> > First(Fields!subtitle.Value, "Text")</caption>
> >
> > The main chart title is hard coded and the second line is a field.
> >
> > I would like to change the font size on that second line. Is there a way to
> > reference the <caption> line in <style><fontsize> and parse through it?
> >
> > I'm thinking this isn't possible, but why not ask.
> >
> > Thanks!
> > Marcus
>
> You will most likely want to create a custom ASP.NET application that
> reads in the RDL file (possibly as an XML Document) and parses the XML
> tags and writes the file back out -and/or- read the RDL file in via
> Streamreader and assigns it to a string and then does a string replace
> of the fontsize in the tags and then writes the file back out via
> Streamwriter. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>

Change field with numeric and alphanumeric to numeric

I have a text field with records that mostly contain numbers, but in some of the records the field is alphanumeric, such as P234032. I want to convert the field to numeric, and just allow any alphanumeric fields to be ignored, and become null.

I'm used to doing this in Microsoft Access - it just ignores those strings and they become null - how can I do this in SQL Server 2000?

Thanks,

RandyDoes this help

set nocount on
go
create table #test (col1 char(10), col2 char(10))
go
insert #test values ('BU89090', 'record 1' )
insert #test values ('GHJ9213', 'record 2' )
insert #test values ('79898', 'record 3' )
insert #test values ('89067', 'record 4' )
insert #test values ('09889067', 'record 5' )
go

select *
from #test
go
select convert(int, case when isnumeric(col1) = 1 then col1 else NULL end) as intCol1, col2
from #test
go
drop table #test

Output
col1 col2
---- ----
BU89090 record 1
GHJ9213 record 2
79898 record 3
89067 record 4
09889067 record 5

intCol1 col2
---- ----
NULL record 1
NULL record 2
79898 record 3
89067 record 4
9889067 record 5|||achorozy,

Thanks for the reply! I haven't tried this yet, but will have a chance first thing tomorrow morning, and will let you know. The SQL Server 2000 course that I just completed was pretty thorough, but seems like it didn't cover the nitty gritty things that I need to know right now.

This is sure a different animal than MS Access!

Thanks again,

Randy

change date format

I have a table with a date field. I need to convert the date so that it
returns in a text format of DDMMYY - no separators.
How do I do this?
I have done it like as follows: Is there a better way of doing it?
Here is what I have:
right(RTrim(convert(char(8),TrnDate,12)),2) +
substring(convert(char(8),TrnDate,12),3,2) +
left(convert(char(8),TrnDate,12),2) as DteVal
"Newbie" <noidea@.nospam.com> wrote in message
news:%23gfQUJ0zEHA.1192@.tk2msftngp13.phx.gbl...
> I have a table with a date field. I need to convert the date so that it
> returns in a text format of DDMMYY - no separators.
> How do I do this?
>
|||On Sat, 20 Nov 2004 21:57:21 -0000, Newbie wrote:

>I have done it like as follows: Is there a better way of doing it?
Hi Newbie,
Have you tried
REPLACE (CONVERT(char(8), TrnDate, 103), '/', '')
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks for the reply. I gave it a go but for the date 17/04/2000 it
returned
170420 instead of
170400
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:55ivp01419rdl6enh9v0lolt1cak6a5r14@.4ax.com...
> On Sat, 20 Nov 2004 21:57:21 -0000, Newbie wrote:
>
> Hi Newbie,
> Have you tried
> REPLACE (CONVERT(char(8), TrnDate, 103), '/', '')
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hugo's solution needs format 3, not 103:
This should work:
REPLACE (CONVERT(char(8), TrnDate, 3), '/', '')
Steve Kass
Drew University
Newbie wrote:

>Thanks for the reply. I gave it a go but for the date 17/04/2000 it
>returned
>170420 instead of
>170400
>
>"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
>news:55ivp01419rdl6enh9v0lolt1cak6a5r14@.4ax.com.. .
>
>
>
|||On Sun, 21 Nov 2004 09:37:38 -0000, Newbie wrote:

>Thanks for the reply. I gave it a go but for the date 17/04/2000 it
>returned
>170420 instead of
>170400
Hi Newbie,
My bad. Halfway through my post, I forgot that you didn't want all four
digits of the year and I autopiloted back to a format that uses the
complete year.
Steve is correct: 103 should have been 3.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks - works a treat!
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:i342q0dcnf10r39nn5m35q4u38cblgtiib@.4ax.com...
> On Sun, 21 Nov 2004 09:37:38 -0000, Newbie wrote:
>
> Hi Newbie,
> My bad. Halfway through my post, I forgot that you didn't want all four
> digits of the year and I autopiloted back to a format that uses the
> complete year.
> Steve is correct: 103 should have been 3.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Change Dataset font size

Is there there a way to change font size of the text of the data set while working in the Report Designer?

In the menu: Go to "Tools" --> "Options"; then under "Environment" --> "Fonts and Colors"

You can edit alot of elements within the dev. studio. (I just didn't find the way to change the font size of the generic query builder (maybe that only happens after restart which I didn't do))

Change Color of Textbox Depending on Value

He is my question depending on the code field from my database I need to display a different color. The field is a detail in my table with just text displaying the title of the report. I have the actual code value in another column. I then go to the background color expression and am using this code:

=Iif(First(Fields!Code.Value, "CodeDataset") = 3, "Green", Iif(First(Fields!Code.Value, "CodeDataset") = 4, "Blue", "Red" ))

They all work if you pull the report up one at a time, but when selecting multi-values you get the color from the first record on each report page no matter what the second, third or so on values are. The code field does display each correct code. Is this being hard-coded to the first record?

Any Idea's ?|||

if your statement:

=Iif(First(Fields!Code.Value, "CodeDataset") = 3, "Green", Iif(First(Fields!Code.Value, "CodeDataset") = 4, "Blue", "Red" ))

You are evaluting the First Fields value "Literally" if you change it to

=Iif(Fields!Code.Value= 3, "Green", Iif(Fields!Code.Value = 4, "Blue", "Red" )) - red will be the default value I think you will see the proper results.

|||

Great thanks it was simple good I tried it with out the first,but I did still have the "DataSet" in there. What is first called is it a function so I can do some research. Also how do you do is null in an expression, and convert data types for a value? Thanks so much for your help!!!

=Iif(Fields!Days.Value is Null, "AliceBlue", "White")

|||

I tried this code for my returntime field: Do I need to do a data conversion?

=Iif(Fields!ReturnTime.Value <= 0, "AliceBlue", "Transparent")

Build complete -- 0 errors, 0 warnings

[rsRuntimeErrorInExpression] The BackgroundColor expression for the textbox ‘Days’ contains an error: Operator '<=' is not defined for type 'Date' and type 'Integer'.

Preview complete -- 0 errors, 1 warnings

|||

Humm,

I think its the "<=" should work try Fields!ReturnTime.Value < 0 or Fields!ReturnTime.Value = 0 instead.

change collation of a type of text column

Hi!
I have to change a complate database collation. After I had changed the
database collation I go through the tables and its columns and make a DDL
command on collated columns :
"ALTER TABLE table ALTER COLUMN column text COLLATE Hungarian_CI_AS NULL"
It work on varchar and char columns but I receive an error message in case
of text columns :
"Server: Msg 4928, Level 16, State 1, Line 1
Cannot alter column 'Ic_DbFields' because it is 'text'."
I use OLEDB.
Enterprise Manager can change the collation on text columns.
What can I do?
I nedd to use SQL-DMO?
thanks for any help :
ImreCheck if you have a full-text index using that column. if so, then you have
to remove it before changing the collation.
AMB
"Imre Ament" wrote:

> Hi!
> I have to change a complate database collation. After I had changed the
> database collation I go through the tables and its columns and make a DDL
> command on collated columns :
> "ALTER TABLE table ALTER COLUMN column text COLLATE Hungarian_CI_AS NULL"
> It work on varchar and char columns but I receive an error message in case
> of text columns :
> "Server: Msg 4928, Level 16, State 1, Line 1
> Cannot alter column 'Ic_DbFields' because it is 'text'."
> I use OLEDB.
> Enterprise Manager can change the collation on text columns.
> What can I do?
> I nedd to use SQL-DMO?
> thanks for any help :
> Imre|||I don't think you can alter collation for blob columns.
One option can be to add a new column with desired collation, update the new
column with the value
of the old column, drop the old column and rename the new column. Column ord
er will not be
preserved, of course.
Another option is to create a new table. My guess is that this is what Enter
prise Manager does.
Imre: pressing "save change script" will show you how EM does this. I doubt
it is exposed in DMO, as
the DaVinci tools (the diagramming etc tools) are not exposed in DMO. But yo
u could have a look, of
course.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:F8284F2D-0D4B-4910-8481-E594FEDB3F40@.microsoft.com...
> Check if you have a full-text index using that column. if so, then you hav
e
> to remove it before changing the collation.
>
> AMB
> "Imre Ament" wrote:
>|||You are right. Thanks for the comment.
AMB
"Tibor Karaszi" wrote:

> I don't think you can alter collation for blob columns.
> One option can be to add a new column with desired collation, update the n
ew column with the value
> of the old column, drop the old column and rename the new column. Column o
rder will not be
> preserved, of course.
> Another option is to create a new table. My guess is that this is what Ent
erprise Manager does.
> Imre: pressing "save change script" will show you how EM does this. I doub
t it is exposed in DMO, as
> the DaVinci tools (the diagramming etc tools) are not exposed in DMO. But
you could have a look, of
> course.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:F8284F2D-0D4B-4910-8481-E594FEDB3F40@.microsoft.com...
>

Change buffer size for the text datafile data

When populating a table with a column of TEXT datatype, an error
message was shown, indicating that the data is too large for the
specified buffer size. How we I change the buffer size from the SQL
Server Enterprise Manager?
TIA,
Jeffrey
what mechanism where you using to populate your table?
Does this kb apply
http://support.microsoft.com/default...&Product=sql2k
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Jeffrey" <cjeffwang@.gmail.com> wrote in message
news:bb2899d2.0410011423.5bf86f40@.posting.google.c om...
> When populating a table with a column of TEXT datatype, an error
> message was shown, indicating that the data is too large for the
> specified buffer size. How we I change the buffer size from the SQL
> Server Enterprise Manager?
> TIA,
> Jeffrey
|||Jeffery,
Could you provide more information on exactly what error message and error
message number you received?
How were you populating the column with the TEXT datatype? Via an INSERT
statement, BULK INSERT or BCP?
The more precise information we have, the better and faster we can answer
your question!
Thanks,
John
"Jeffrey" <cjeffwang@.gmail.com> wrote in message
news:bb2899d2.0410011423.5bf86f40@.posting.google.c om...
> When populating a table with a column of TEXT datatype, an error
> message was shown, indicating that the data is too large for the
> specified buffer size. How we I change the buffer size from the SQL
> Server Enterprise Manager?
> TIA,
> Jeffrey
|||I tried to populate a table with an Excel data file. To import the data
file I opened the DTS Import/Export wizard from Enterprise Manager. The
datatype is TEXT. Thanks.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||John,
I tried to populate a table of a SQL Server database with an Excel
data file. To import the data file I opened the DTS Import/Export
wizard from Enterprise Manager. The datatype of the column is TEXT
and the error message indicated that the text data is too large for
the specified buffer size.
How do I cahnge the buffer size of the database or the column?
Thanks,
Jeffrey
"John Kane" <jt-kane@.comcast.net> wrote in message news:<#FVNToAqEHA.592@.TK2MSFTNGP11.phx.gbl>...[vbcol=seagreen]
> Jeffery,
> Could you provide more information on exactly what error message and error
> message number you received?
> How were you populating the column with the TEXT datatype? Via an INSERT
> statement, BULK INSERT or BCP?
> The more precise information we have, the better and faster we can answer
> your question!
> Thanks,
> John
>
> "Jeffrey" <cjeffwang@.gmail.com> wrote in message
> news:bb2899d2.0410011423.5bf86f40@.posting.google.c om...

Change buffer size for the text datafile data

When populating a table with a column of TEXT datatype, an error
message was shown, indicating that the data is too large for the
specified buffer size. How we I change the buffer size from the SQL
Server Enterprise Manager?
TIA,
Jeffrey
what mechanism where you using to populate your table?
Does this kb apply
http://support.microsoft.com/default...&Product=sql2k
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Jeffrey" <cjeffwang@.gmail.com> wrote in message
news:bb2899d2.0410011423.5bf86f40@.posting.google.c om...
> When populating a table with a column of TEXT datatype, an error
> message was shown, indicating that the data is too large for the
> specified buffer size. How we I change the buffer size from the SQL
> Server Enterprise Manager?
> TIA,
> Jeffrey
|||Jeffery,
Could you provide more information on exactly what error message and error
message number you received?
How were you populating the column with the TEXT datatype? Via an INSERT
statement, BULK INSERT or BCP?
The more precise information we have, the better and faster we can answer
your question!
Thanks,
John
"Jeffrey" <cjeffwang@.gmail.com> wrote in message
news:bb2899d2.0410011423.5bf86f40@.posting.google.c om...
> When populating a table with a column of TEXT datatype, an error
> message was shown, indicating that the data is too large for the
> specified buffer size. How we I change the buffer size from the SQL
> Server Enterprise Manager?
> TIA,
> Jeffrey
|||John,
I tried to populate a table of a SQL Server database with an Excel
data file. To import the data file I opened the DTS Import/Export
wizard from Enterprise Manager. The datatype of the column is TEXT
and the error message indicated that the text data is too large for
the specified buffer size.
How do I cahnge the buffer size of the database or the column?
Thanks,
Jeffrey
"John Kane" <jt-kane@.comcast.net> wrote in message news:<#FVNToAqEHA.592@.TK2MSFTNGP11.phx.gbl>...[vbcol=seagreen]
> Jeffery,
> Could you provide more information on exactly what error message and error
> message number you received?
> How were you populating the column with the TEXT datatype? Via an INSERT
> statement, BULK INSERT or BCP?
> The more precise information we have, the better and faster we can answer
> your question!
> Thanks,
> John
>
> "Jeffrey" <cjeffwang@.gmail.com> wrote in message
> news:bb2899d2.0410011423.5bf86f40@.posting.google.c om...

Change "Report is being generated" text

Is there a way to modify or suppress the "Report is being generated" text and
image? I am trying to setup reports in the Report Viewer webpart on a
Sharepoint site (Sharepoint 2007, SQL Server 2005 SP2, Reporting Services in
Sharepoint integrated mode), but do not want the user to see that message.
Any suggestions?
Thanks!
dawI think that I answered my own question in this scenario. There is an
Asynchronous Rendering checkbox in the properties of the webpart. That seems
to solve it.
I would be interested to know if there is a way to do this outside of
Sharepoint. I'm not a programmer, so it may be something obvious.
Thanks!
daw
"daw" wrote:
> Is there a way to modify or suppress the "Report is being generated" text and
> image? I am trying to setup reports in the Report Viewer webpart on a
> Sharepoint site (Sharepoint 2007, SQL Server 2005 SP2, Reporting Services in
> Sharepoint integrated mode), but do not want the user to see that message.
> Any suggestions?
> Thanks!
> daw

Category and Subcategory Problem

Hi everyone, I am having trouble with a particular problem with SQL. I have a table that defines product categories like so:

Id (int)
Text varchar(50)
ParentId (int)

It holds all our categories, with subcategories having the appropriate ParentId relating to the above category. I am trying to write a stored procedure that takes in a single Id, and finds out all the related subcategories and subcategories all the way down the tree. I need to produce a resultset with a single column of Id's of all the subcategories etc

For example if the table had the following records:

8 - General - 1
9 - Academic - 1
10 - Science - 1
11 - History - 8
12 - Maths - 8
13 - English - 9
14 - Spanish - 9
15 - England - 13

So if I was to feed in Id 9 the resulting table that I want is like this

9
13
15

My problem is that there isn't a defined number of subcategory levels. General has only 1 subcategory level, but Academic has 2 subcategory levels.

The only part solution I have found was this:

CREATE TABLE #Categories (
CategoryId int)

insert #Categories (CategoryId)
select Id
from Category as c1
where c1.ParentId=8 or c1.Id=8

however it only brings back the first level of subcategories. I was intending to loop this over and over however because of the inconsistent number of levels I can't put in a predefined number of loops.

I have never really faced a problem like this before. I am pretty new to T-SQL and am not sure if there is an easy way to overcome this, but any help would be very much appreciated. I was pretty much ready to pull out my hair yesterday trying to solve this .

I am using SQL Server Express 2005 on Windows Server 2003

Thanks in advance for any help,

Dylan

Please take a look at the link below:

http://msdn2.microsoft.com/en-us/library/ms186243.aspx

You can use recursive CTEs in SQL Server 2005 to write the queries. You can encapsulate those in views or inline TVFs.

|||Thanks that worked great, exactly what I needed.

Catalog Not populating

I can not get the full text catalog to populate.
I have tried it on the most basic datababase and table.
The catalog creates, and it says it successfully
populates, but yet the item count is 0.
I have restored the same DB on a different server, and it
works fine. I have tried so many KB articles with no
luck. Has anyone every seen this?
Any help is appreciated.
The most obvious causes of this problem is:
http://support.microsoft.com/default...&Product=sql2k
http://support.microsoft.com/default...&Product=sql2k
Have you already followed the advice in these kb's?
Also please post the any messages you are getting in the application log
from MSSearch or MSSCI.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Steve" <scusick@.datapipe.com> wrote in message
news:023601c46f54$2f102620$3a01280a@.phx.gbl...
> I can not get the full text catalog to populate.
> I have tried it on the most basic datababase and table.
> The catalog creates, and it says it successfully
> populates, but yet the item count is 0.
> I have restored the same DB on a different server, and it
> works fine. I have tried so many KB articles with no
> luck. Has anyone every seen this?
> Any help is appreciated.
|||Here are the warnings
The crawl on project <SQLServer SQL0015500005> cannot be
started. All of the crawl seeds have been ignored because
of host, extension, or other URL restrictions. Error:
80040d07 - The specified URL is excluded and will not be
cataloged. The URL restriction rules may have to be
modified to include this URL.
The crawl seed <MSSQL75://SQLServer/6497e884> in project
<SQLServer SQL0015500005> cannot be accessed. Error:
80040d07 - The specified URL is excluded and will not be
cataloged. The URL restriction rules may have to be
modified to include this URL
One or more warnings or errors for Gatherer project
<SQLServer SQL0015500005> were logged to file
<e:\MSSQL\FTDATA\SQLServer\GatherLogs\SQL001550000 5.1.gthr>
.. If you are interested in these messages, please, look at
the file using the gatherer log query object (gthrlog.vbs,
log viewer web page).
I also ran thhrlog.vbs and got the following.
7/21/2004 4:10:56 PM Add The gatherer has
started
7/21/2004 4:10:56 PM Add The initialization
has completed
7/21/2004 4:11:10 PM Add Started Full
crawl
7/21/2004 4:11:10 PM MSSQL75://SQLServer/6497e884
Add URL is excluded
because the URL protocol is not recognized or restricted
7/21/2004 4:11:10 PM Add Completed Full
crawl
Anything anyone can do, would be amazing. I have been
trying for days!
Thanks

>--Original Message--
>The most obvious causes of this problem is:
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;317746&Product=sql2k
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;295772&Product=sql2k
>Have you already followed the advice in these kb's?
>Also please post the any messages you are getting in the
application log[vbcol=seagreen]
>from MSSearch or MSSCI.
>--
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
>"Steve" <scusick@.datapipe.com> wrote in message
>news:023601c46f54$2f102620$3a01280a@.phx.gbl...
it
>
>.
>
|||Steve,
The primary error is "URL is excluded because the URL protocol is not
recognized or restricted", and a quick search on Google turned up:
"Troubleshooting and Checking Protocol Handler Performance" at
http://msdn.microsoft.com/library/de... formance.asp
Problem:
Gatherer log error "URL is excluded because the URL protocol is not
recognized or restricted"
Solution:
Protocol handler registration may have failed. Run regedit to look at
HKLM\Software\Microsoft\Search\1.0\ProtocolHandler s or the DllRegisterServer
function.
Does this server have both SQL Server and SharePoint installed on it? Is
this an upgrade from SPPS to SQL Server from WSS?
Regards,
John
<anonymous@.discussions.microsoft.com> wrote in message
news:19a801c46f5f$cf3cefb0$a301280a@.phx.gbl...[vbcol=seagreen]
> Here are the warnings
> --
> The crawl on project <SQLServer SQL0015500005> cannot be
> started. All of the crawl seeds have been ignored because
> of host, extension, or other URL restrictions. Error:
> 80040d07 - The specified URL is excluded and will not be
> cataloged. The URL restriction rules may have to be
> modified to include this URL.
> The crawl seed <MSSQL75://SQLServer/6497e884> in project
> <SQLServer SQL0015500005> cannot be accessed. Error:
> 80040d07 - The specified URL is excluded and will not be
> cataloged. The URL restriction rules may have to be
> modified to include this URL
> One or more warnings or errors for Gatherer project
> <SQLServer SQL0015500005> were logged to file
> <e:\MSSQL\FTDATA\SQLServer\GatherLogs\SQL001550000 5.1.gthr>
> . If you are interested in these messages, please, look at
> the file using the gatherer log query object (gthrlog.vbs,
> log viewer web page).
> I also ran thhrlog.vbs and got the following.
> 7/21/2004 4:10:56 PM Add The gatherer has
> started
> 7/21/2004 4:10:56 PM Add The initialization
> has completed
> 7/21/2004 4:11:10 PM Add Started Full
> crawl
> 7/21/2004 4:11:10 PM MSSQL75://SQLServer/6497e884
> Add URL is excluded
> because the URL protocol is not recognized or restricted
> 7/21/2004 4:11:10 PM Add Completed Full
> crawl
> Anything anyone can do, would be amazing. I have been
> trying for days!
> Thanks
> us;317746&Product=sql2k
> us;295772&Product=sql2k
> application log
> it
|||can you try to regregister the various SQL FTS components?
Here is a script to do this:
save this to a batch file and run it from a command window:
cd "C:\Program Files\Common Files\System\MSSearch\Bin\"
%windir%\system32\Regsvr32 mssadmin.dll
%windir%\system32\Regsvr32 mssmmcsi.dll
%windir%\system32\Regsvr32 offfilt.dll
%windir%\system32\Regsvr32 %WINDIR%\System32\athprxy.dll
%windir%\system32\Regsvr32 ftsqlpar.dll
%windir%\system32\Regsvr32 msscntrs.dll
%windir%\system32\Regsvr32 mssmsg.dll
%windir%\system32\Regsvr32 mssmulpi.dll
%windir%\system32\Regsvr32 mssph.dll
%windir%\system32\Regsvr32 mssrch.dll
%windir%\system32\Regsvr32 msstools.dll
%windir%\system32\Regsvr32 objcreat.dll
%windir%\system32\Regsvr32 propdefs.dll
%windir%\system32\Regsvr32 srchadm.dll
%windir%\system32\Regsvr32 srchidx.dll
%windir%\system32\Regsvr32 tquery.dll
%windir%\system32\Regsvr32 %WINDIR%\Cluster\gathercl.dll
%windir%\system32\Regsvr32 nlhtml.dll
cd "C:\Program Files\Common Files\System\MSSearch\common\"
%windir%\system32\Regsvr32 mssitlb.dll
%windir%\system32\Regsvr32 %windir%\System32\athprxy.dll
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"John Kane" <jt-kane@.comcast.net> wrote in message
news:%23YkfjZ4bEHA.2972@.TK2MSFTNGP12.phx.gbl...
> Steve,
> The primary error is "URL is excluded because the URL protocol is not
> recognized or restricted", and a quick search on Google turned up:
> "Troubleshooting and Checking Protocol Handler Performance" at
>
http://msdn.microsoft.com/library/de... formance.asp
> Problem:
> Gatherer log error "URL is excluded because the URL protocol is not
> recognized or restricted"
> Solution:
> Protocol handler registration may have failed. Run regedit to look at
> HKLM\Software\Microsoft\Search\1.0\ProtocolHandler s or the
DllRegisterServer
> function.
> Does this server have both SQL Server and SharePoint installed on it? Is
> this an upgrade from SPPS to SQL Server from WSS?
> Regards,
> John
>
>
> <anonymous@.discussions.microsoft.com> wrote in message
> news:19a801c46f5f$cf3cefb0$a301280a@.phx.gbl...
>

catalog is empty after populating

Hello,
I've got a problem with the Full Text Search of MS SQL=20
Server 2000 (SP3).
I've created a new Full Text catalog (FTC) on a database,=20
including one table and one textfield for searching. Up to=20
this everything goes fine. Population runs without=20
creating an error. But after population the catalog-size=20
is only 1 MB and the catalog doesn't contain any items. A=20
query with CONTAINS is returning 0 results.=20
I've tried the total procedure in Enterprise Manager and=20
in Query Analyzer. I've tried it with the example=20
databases NORTHWIND and PUBS, but everytime the same=20
result.
I've already searched a lot of articles and newsgroup-
entries without finding a solution for my problem.=20
Hopefully one of You may help.=20
Some facts that may help to find a solution:
-SQL Server 2000 SP3 (not upgraded from 7.0)
-Windows 2003 Server as Operating System
-MS Search Service runs correctly
Thank You in advance for every help
Andr=E9
what are the error messages you are getting in the application log in event
viewer from MSSearch and MSSCi?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Andr Bretschneider" <andre.bretschneider@.freenet.de> wrote in message
news:2a0701c47df8$4bd41c50$a601280a@.phx.gbl...
Hello,
I've got a problem with the Full Text Search of MS SQL
Server 2000 (SP3).
I've created a new Full Text catalog (FTC) on a database,
including one table and one textfield for searching. Up to
this everything goes fine. Population runs without
creating an error. But after population the catalog-size
is only 1 MB and the catalog doesn't contain any items. A
query with CONTAINS is returning 0 results.
I've tried the total procedure in Enterprise Manager and
in Query Analyzer. I've tried it with the example
databases NORTHWIND and PUBS, but everytime the same
result.
I've already searched a lot of articles and newsgroup-
entries without finding a solution for my problem.
Hopefully one of You may help.
Some facts that may help to find a solution:
-SQL Server 2000 SP3 (not upgraded from 7.0)
-Windows 2003 Server as Operating System
-MS Search Service runs correctly
Thank You in advance for every help
Andr
|||Hi Hillary,
thanks for your fast answer, But I'm sorry. I'm obviously=20
a greenhorn in SQLServer-Administration. Where can I find=20
this application log?
Andr=E9

>--Original Message--
>what are the error messages you are getting in the=20
application log in event
>viewer from MSSearch and MSSCi?
>--=20
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
>"Andr=E9 Bretschneider" <andre.bretschneider@.freenet.de>=20
wrote in message
>news:2a0701c47df8$4bd41c50$a601280a@.phx.gbl...
>Hello,
>I've got a problem with the Full Text Search of MS SQL
>Server 2000 (SP3).
>I've created a new Full Text catalog (FTC) on a database,
>including one table and one textfield for searching. Up to
>this everything goes fine. Population runs without
>creating an error. But after population the catalog-size
>is only 1 MB and the catalog doesn't contain any items. A
>query with CONTAINS is returning 0 results.
>I've tried the total procedure in Enterprise Manager and
>in Query Analyzer. I've tried it with the example
>databases NORTHWIND and PUBS, but everytime the same
>result.
>I've already searched a lot of articles and newsgroup-
>entries without finding a solution for my problem.
>Hopefully one of You may help.
>Some facts that may help to find a solution:
>-SQL Server 2000 SP3 (not upgraded from 7.0)
>-Windows 2003 Server as Operating System
>-MS Search Service runs correctly
>Thank You in advance for every help
>Andr=E9
>
>.
>
|||Andre,
Hilary's referring to the Window's Application Event log on the server where
SQL Server is installed as the "Microsoft Search" service cannot write it's
errors to SQL Server's errorlog files as SQL Server has a lock on these
files. Specifically, you should click on Start -> Programs -> Administrator
Tools -> Event Viewer and review the Application event log. You can click on
View and Filter and then select "Microsoft Search" as the Event Source to
see only these events. Below is an example error, you may find or you may
find a different error.
Microsoft Search
Warning Gatherer
3036
JTKNT0
Unable to access crawl seed <MSSQL://LOCALHOST/7.1333579789> in project
<SQLServer SQL0000700006>.
Error: 8007052e - Logon failure: unknown user name or bad password. .
Post any related errors in your server's Application event log.
Regards,
John
"Andr Bretschneider" <andre.bretschneider@.freenet.de> wrote in message
news:2b0901c47e0b$f559ce60$a601280a@.phx.gbl...
Hi Hillary,
thanks for your fast answer, But I'm sorry. I'm obviously
a greenhorn in SQLServer-Administration. Where can I find
this application log?
Andr

>--Original Message--
>what are the error messages you are getting in the
application log in event
>viewer from MSSearch and MSSCi?
>--
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
>"Andr Bretschneider" <andre.bretschneider@.freenet.de>
wrote in message
>news:2a0701c47df8$4bd41c50$a601280a@.phx.gbl...
>Hello,
>I've got a problem with the Full Text Search of MS SQL
>Server 2000 (SP3).
>I've created a new Full Text catalog (FTC) on a database,
>including one table and one textfield for searching. Up to
>this everything goes fine. Population runs without
>creating an error. But after population the catalog-size
>is only 1 MB and the catalog doesn't contain any items. A
>query with CONTAINS is returning 0 results.
>I've tried the total procedure in Enterprise Manager and
>in Query Analyzer. I've tried it with the example
>databases NORTHWIND and PUBS, but everytime the same
>result.
>I've already searched a lot of articles and newsgroup-
>entries without finding a solution for my problem.
>Hopefully one of You may help.
>Some facts that may help to find a solution:
>-SQL Server 2000 SP3 (not upgraded from 7.0)
>-Windows 2003 Server as Operating System
>-MS Search Service runs correctly
>Thank You in advance for every help
>Andr
>
>.
>
|||Hi John,=20
thanks for your advice, now I've found them.
I got five entries (3 informational, 2 errors) in the=20
application log. Here the 2 errors (I try to translate=20
them correctly from German):
Source: Microsoft Search
Type/Category: Warning Gatherer
Event Identification: 3036
Description:=20
Unable to access crawl seed <MSSQL75://SQLServer/6fe99f9f>=20
in project <SQLServer SQL0002400005>.
Error: 800700e9 - No process at the other end of the pipe.
Source: Microsoft Search
Type/Category: Warning Gatherer
Event Identification: 3024
Description:=20
Unable to start crawl for project <SQLServer=20
SQL0002400005> because there is no access to the crawl=20
seeds. Patch the error and then start again the crawl.
Obviously the second is an aftereffect of the first. So=20
I'll try to find the reason for the first error. Any idea?
Andr=E9

>--Original Message--
>Andre,
>Hilary's referring to the Window's Application Event log=20
on the server where
>SQL Server is installed as the "Microsoft Search" service=20
cannot write it's
>errors to SQL Server's errorlog files as SQL Server has a=20
lock on these
>files. Specifically, you should click on Start ->=20
Programs -> Administrator
>Tools -> Event Viewer and review the Application event=20
log. You can click on
>View and Filter and then select "Microsoft Search" as the=20
Event Source to
>see only these events. Below is an example error, you may=20
find or you may
>find a different error.
>Microsoft Search
>Warning Gatherer
>3036
>JTKNT0
>Unable to access crawl seed=20
<MSSQL://LOCALHOST/7.1333579789> in project
><SQLServer SQL0000700006>.
>Error: 8007052e - Logon failure: unknown user name or bad=20
password. .
>Post any related errors in your server's Application=20
event log.
>Regards,
>John
|||You're welcome, Andre,
The key error is Error: 800700e9 - No process at the other end of the pipe.
By chance did you or someone else remove the BUILTIN\Administrator login? If
so, you will need to either add it back with the default settings (master db
& sysadmin rights) as the MSSearch service requires that either the
BUILTIN\Administrators login
be present or that [NT Authority\System] localsystem login have the below
rights and this is why you are seeing ("Login failed for NT
AUTHORITY\SYSTEM" ) after removing the BUILTIN\Administrators login:
exec sp_grantlogin N'NT Authority\System'
exec sp_defaultdb N'NT Authority\System', N'master'
exec sp_defaultlanguage N'NT Authority\System','us_english'
exec sp_addsrvrolemember N'NT Authority\System', sysadmin
See also KB article Q263712 "INF: How To Prevent Windows NT Administrators
From Administering a Clustered SQL Server"
at http://support.microsoft.com/default...;EN-US;q263712 for more
info.
Additionally, and depending upon if you're FT Indexing documents imported
into SQL Server, you might also want to review KB article 308771 "PRB: A
Full-Text Search May Not Return Any Hits If It Fails to Index a File" at:
http://support.microsoft.com/default...&Product=sql2k
Regards,
John
<anonymous@.discussions.microsoft.com> wrote in message
news:2c6901c47e2a$8ed69410$a501280a@.phx.gbl...
Hi John,
thanks for your advice, now I've found them.
I got five entries (3 informational, 2 errors) in the
application log. Here the 2 errors (I try to translate
them correctly from German):
Source: Microsoft Search
Type/Category: Warning Gatherer
Event Identification: 3036
Description:
Unable to access crawl seed <MSSQL75://SQLServer/6fe99f9f>
in project <SQLServer SQL0002400005>.
Error: 800700e9 - No process at the other end of the pipe.
Source: Microsoft Search
Type/Category: Warning Gatherer
Event Identification: 3024
Description:
Unable to start crawl for project <SQLServer
SQL0002400005> because there is no access to the crawl
seeds. Patch the error and then start again the crawl.
Obviously the second is an aftereffect of the first. So
I'll try to find the reason for the first error. Any idea?
Andr

>--Original Message--
>Andre,
>Hilary's referring to the Window's Application Event log
on the server where
>SQL Server is installed as the "Microsoft Search" service
cannot write it's
>errors to SQL Server's errorlog files as SQL Server has a
lock on these
>files. Specifically, you should click on Start ->
Programs -> Administrator
>Tools -> Event Viewer and review the Application event
log. You can click on
>View and Filter and then select "Microsoft Search" as the
Event Source to
>see only these events. Below is an example error, you may
find or you may
>find a different error.
>Microsoft Search
>Warning Gatherer
>3036
>JTKNT0
>Unable to access crawl seed
<MSSQL://LOCALHOST/7.1333579789> in project
><SQLServer SQL0000700006>.
>Error: 8007052e - Logon failure: unknown user name or bad
password. .
>Post any related errors in your server's Application
event log.
>Regards,
>John

Catalog and characters with accent

I have a question about SQL Server 2000 Full Text Index.
I want to create a catalog in a field (varchar(255)), but Im with 2
problems:
1. The characters of this field can have accent. But when I do a search
I want to see the rows with and without the accent. For example:
SELECT NAME
FROM TABLE
WHERE CONTAINS (FIELD, '"PLASTICO*"')
With this command I want to see the row PLASTICO and the row PLSTICO.
Is it possible? Now, Im just receiving only the row PLASTICO. I need
that the catalog be accent insensitive. Can I do that?
2. My sencond problem is: I need to see also the rows that have the word
PLASTICO inside the complete word. For example: I want to see also the
rows with INTERPLASTICO, 2PLASTICO, XPTOPLASTICO. But whe I wrote the
following command I dont receive these words:
SELECT NAME
FROM TABLE
WHERE CONTAINS (FIELD, '"*PLASTICO*"')
Is it possible to do that? I wnat to see the rows that have the word
PLASTIC in the begin, middle or end of the words.
Thaks,
Paulo
*** Sent via Developersdex http://www.codecomments.com ***
SQL 2005 can solve both your problems. You can configure your catalog for
accent insensitive searches. You can also use the thesaurus option to expand
your search on plastico to search on interpastico, 2plastico, and
xptoplastico, as long as you enter all of these expansion terms into your
thesaurus file in advance.
In SQL 2000 you have to expand your search terms for accented or unaccented
versions as well as the alternate word forms.
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
"Paulo Andre Ortega Ribeiro" <paulo.andre.66@.terra.com.br> wrote in message
news:O%23k7qFyeFHA.2740@.TK2MSFTNGP10.phx.gbl...
> I have a question about SQL Server 2000 Full Text Index.
> I want to create a catalog in a field (varchar(255)), but Im with 2
> problems:
> 1. The characters of this field can have accent. But when I do a search
> I want to see the rows with and without the accent. For example:
> SELECT NAME
> FROM TABLE
> WHERE CONTAINS (FIELD, '"PLASTICO*"')
> With this command I want to see the row PLASTICO and the row PLSTICO.
> Is it possible? Now, Im just receiving only the row PLASTICO. I need
> that the catalog be accent insensitive. Can I do that?
> 2. My sencond problem is: I need to see also the rows that have the word
> PLASTICO inside the complete word. For example: I want to see also the
> rows with INTERPLASTICO, 2PLASTICO, XPTOPLASTICO. But whe I wrote the
> following command I dont receive these words:
> SELECT NAME
> FROM TABLE
> WHERE CONTAINS (FIELD, '"*PLASTICO*"')
> Is it possible to do that? I wnat to see the rows that have the word
> PLASTIC in the begin, middle or end of the words.
> Thaks,
> Paulo
>
>
> *** Sent via Developersdex http://www.codecomments.com ***

Cast as Integer problem.

I have DB that contains a field named GarmentSize. The field type is TEXT as it can contain for example

8
10
12
14

or

L
XL
XXL

The problem is when I run a query and order by GarmentSize the results displayed are as follows if it contains numbers

10
12
14
8

Instead of

8
10
12
14

If I use order by CAST(GarmentSize as Integer) this works fine, unless the field contains text then is throws the following error.

Syntax error converting the varchar value 'XL' to a column of data type int

Is there a way of determining if the field contains characters that can be casted before doing the CAST?I'd convert the single digit numbers to a more managable form, something like: ORDER BY CASE WHEN GarmentSize LIKE '[0-9]'
THEN ' ' + GarmentSize ELSE GarmentSize END-PatP|||yeah, but pat, that still doesn't solve the sequencing problem

appending '42' to a space is still gonna come after appending '105' to a space

gosman, i urge you most strenuously, downsize your TEXT field to at least VARCHAR(8000)

in fact, i would take bets that you could probably downsize it to VARCHAR(100) and still accommodate all the GarmentSizes in your database|||Wouldn't a size 42 have to be about five meters tall, and weigh 1000 Kg or more? ...and I thought that Americans were big! I'm not sure I'm prepared to explore the size 105.

To futher address this problem so it can handle more cases, how about:CASE WHEN IsNumeric(GarmentSize)
THEN Cast(Cast(GarmentSize AS MONEY)) AS CHAR(50))
ELSE GarmentSize END-PatP ;)|||varchar?...if we're changing datatypes, why not santize the data and make it int?

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Col1 text)
GO

INSERT INTO myTable99(Col1)
SELECT 'XL' UNION ALL
SELECT '1'
GO

SELECT CASE WHEN ISNUMERIC(CONVERT(varchar(8000),Col1)) = 0 THEN 'NO' ELSE 'YES' END AS [A Number?], Col1
FROM myTable99
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO|||sanitize the data and make it integer? hitting the 'ritas at lunch again, were we?

what do you propose as integer equivalents of XS, S, M, L, XL?

oh, oh, oh, please say "use foreign keys to look up the size name"

:) :) :)|||Thanks Guys.

Pat the field type is actually VARCHAR 50

I've tried the following

Select GarmentSize from PHOOLRATIOS order by CASE WHEN IsNumeric(GarmentSize)THEN Cast(Cast(GarmentSize AS integer)) AS CHAR(50))ELSE GarmentSize END

But I get this message. Incorrect syntax near the keyword 'THEN'.

Forgive my ignorance but I'm a complete SQL novice.|||sanitize the data and make it integer? hitting the 'ritas at lunch again, were we?

what do you propose as integer equivalents of XS, S, M, L, XL?

oh, oh, oh, please say "use foreign keys to look up the size name"

:) :) :)

I would say that the data is not normalized, since XL is a representation of a size range, not the actuall size...

And the Foreign key bit for a surrogate

puuuuleeeze

http://weblogs.sqlteam.com/brettk/archive/2004/06/09/1530.aspx

You might want a "size fits from" and a "size fits to" columns

Now aint that nitpicky...|||gosman, you lied, your first post distinctly says TEXT :)

try WHEN IsNumeric(GarmentSize) = 1 THEN ...

sorry, brett, garmentsize is normalized

if a range is your criterion of not normalized, then any of your tables that uses a datetime column is not normalized, since we all know that a datetime value is not a single value but rather represents a range

;)|||SELECT GarmentSize
FROM PHOOLRATIOS
ORDER BY CASE WHEN ISNUMERIC(GarmentSize) = 1
THEN CONVERT(int, GarmentSize)
ELSE 0
END
, GarmentSize|||gosman, you lied, your first post distinctly says TEXT :)

try WHEN IsNumeric(GarmentSize) = 1 THEN ...

sorry, brett, garmentsize is normalized

if a range is your criterion of not normalized, then any of your tables that uses a datetime column is not normalized, since we all know that a datetime value is not a single value but rather represents a range

;)

Who's been drinking at lunch?|||Sorry, that was my bad! How about:Select GarmentSize
from PHOOLRATIOS
order by CASE WHEN IsNumeric(GarmentSize) = 1
THEN Cast(Cast(GarmentSize AS integer)) AS CHAR(50))
ELSE GarmentSize
END-PatP|||Hi Pat

Select GarmentSize
from PHOOLRATIOS
order by CASE WHEN IsNumeric(GarmentSize) = 1
THEN Cast(Cast(GarmentSize AS integer)) AS CHAR(50))
ELSE GarmentSize
END

Server: Msg 1035, Level 15, State 10, Line 4
Incorrect syntax near 'Cast', expected 'AS'.|||USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(GarmentSize char(50))
GO

INSERT INTO myTable99(GarmentSize)
SELECT 'XL' UNION ALL
SELECT '1' UNION ALL
SELECT '2' UNION ALL
SELECT '3' UNION ALL
SELECT '4' UNION ALL
SELECT '5' UNION ALL
SELECT '6' UNION ALL
SELECT '10' UNION ALL
SELECT '11' UNION ALL
SELECT '112'
GO

SELECT GarmentSize
FROM myTable99
ORDER BY CASE WHEN ISNUMERIC(GarmentSize) = 1
THEN CONVERT(int, GarmentSize)
ELSE 0
END
, GarmentSize

--Pat's off his meds..forgive him

Select GarmentSize
from myTable99
order by CASE WHEN IsNumeric(GarmentSize) = 1
THEN Cast(Cast(GarmentSize AS integer) AS CHAR(50))
ELSE GarmentSize
END

SET NOCOUNT OFF
DROP TABLE myTable99
GO|||brett obviously is dropping his myTable99 before looking at the results

this --Select GarmentSize
from myTable99
order by CASE WHEN IsNumeric(GarmentSize) = 1
THEN Cast(Cast(GarmentSize AS integer) AS CHAR(50))
ELSE GarmentSize
ENDdoes not sort the "numeric" values into numeric sequence!!!

insert the value '42' and you'll see what i mean

and yes, every datetime value represents a range

and i don't start drinking until after 5:00 p.m.

:) :) :)|||Well I'll be a Hunkey's Monkle! Brett is correct.

It appears that CAST does a right fill with spaces. My example ought to read:SELECT GarmentSize
FROM PHOOLRATIOS
ORDER BY CASE WHEN 1 = IsNumeric(GarmentSize)
THEN Str(GarmentSize, 50)
ELSE GarmentSize
ENDThe alternative that he posted would also be good, although I'd reverse the order to put the numeric values first, something like:SELECT GarmentSize
FROM myTable99
ORDER BY CASE WHEN ISNUMERIC(GarmentSize) = 1
THEN 1
ELSE 2
END, GarmentSize-PatP|||brett obviously is dropping his myTable99 before looking at the results

this --Select GarmentSize
from myTable99
order by CASE WHEN IsNumeric(GarmentSize) = 1
THEN Cast(Cast(GarmentSize AS integer) AS CHAR(50))
ELSE GarmentSize
ENDdoes not sort the "numeric" values into numeric sequence!!!

insert the value '42' and you'll see what i mean

and yes, every datetime value represents a range

and i don't start drinking until after 5:00 p.m.

:) :) :)

Rudy...ya gotta stop smokin crack...it's not good for you...

Yes, I know..that's Pat's query...did you see the first query?

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(GarmentSize char(50))
GO

INSERT INTO myTable99(GarmentSize)
SELECT 'XL' UNION ALL
SELECT '1' UNION ALL
SELECT '2' UNION ALL
SELECT '3' UNION ALL
SELECT '4' UNION ALL
SELECT '5' UNION ALL
SELECT '6' UNION ALL
SELECT '10' UNION ALL
SELECT '40' UNION ALL
SELECT '11' UNION ALL
SELECT '112'
GO

SELECT GarmentSize
FROM myTable99
ORDER BY CASE WHEN ISNUMERIC(GarmentSize) = 1
THEN CONVERT(int, GarmentSize)
ELSE 0
END
, GarmentSize

SET NOCOUNT OFF
DROP TABLE myTable99
GO

And what's with the very specific point in time being a range?|||sorry, pal, i don't do crack either

keep fishing, you're sure to hit one of my vices eventually

there is no such thing as a point in time

what i was trying to get you to see goes something like this --

if A implies B, and C is A, then C implies B

A = a range
B = not normalized
C = some value

you claimed a range was not normalized

i'm saying that by this very same logic, since a datetime value is a range, therefore any of your tables using a datetime value is not normalized

actually, i have no problem with datetime values, and i know exactly how to handle them

what i was trying to point out is that your claim that a size is actually a range and therefore not nomalized is nonsense

no offence, good buddy, but it was

and if you were only joking, then you simply must learn how to use smileys correctly|||It appears that CAST does a right fill with spaces. My example ought to read...It's not the CAST, it's the datatype that you use within the CAST, - CHAR(50). If you used VARCHAR(50) there won't be any "right fill"-ing ;)|||OK...attempt to be clear.

Our friends Column, GarmentSize, is retaining 2 types of data in the same column.

One is the very specific size of a garmet, let's say a size 10.

Recording the size of the garment as M or medium presents no specific size. M is a range of sizes, like 10-12. Not 1 size.

Apples and Oranges. They're both fruit, but they are essentially different.

And I did say I was being picky, but I'd still say they'd need another table that addresses this and descibes what each (M, L, XL, ect) actually means.

I still don't get what you mean by that a datetime column represents a range.

I never said a "range was not normalized". It's the combination of a specific size and a range of sizes (XL, for example) is not normalized. The size range values in their own column would be fine.

OK, Vices, how about Fishing?|||yes, XL is a range, it is the range between where L leaves off and XXL begins

just like size 8 is a range, between where 7 leaves off and 9 begins!!

nope, i don't fish, nor do i hunt

i do like baiting gullible DBAs, though, and you are providing a world of entertainment for us today!!

have you ever gone into the Senior VP of Marketing's office and told her she cannot use XL as a size, and should use a numeric range instead?

what utter nonsense

"The size range values in their own column would be fine" -- they already are in their own column, it's called GarmentSize, and it has a perfectly valid domain consisting of sizes which happen to be either numbers or letters

but you DBAs don't know what a domain is, so let's leave the modelling nuances alone and go back to talking about other stuff like raid stripes and scuzzies and other important stuff

:)|||Then it's got to be some sort of hallucinogen

Is an apple a range, is an airplane a range, is a shirt a range?

Isn't our freinds problem proof enough that their data is not normalized?|||Is an apple a range, is an airplane a range, is a shirt a range?no, they are not

Isn't our freinds problem proof enough that their data is not normalized?no, the fact that numerics in a VARCHAR column sort as characters is certainly not proof that the data is not normalized

i hesitate to ask, but do you even know what normalized means?

like i suggested in my last post, perhaps (for your sake) we should just drop the subject|||Scrapped! Looked good on paper...|||OK, try again:

ORDER BY right('000000000...50 of these...0000000' + GarmentSize, 50)|||Since the data itself is not going to be sorted very well, dare I suggest a sortorder column? This may curdle Rudy's blood, but hey, why should he have all the fun, eh? ;-)|||fantastic idea!! a sortorder column!!

okay, now, let's see, how should we populate this column

I KNOW!! let's make it numberic, and for "numeric" sizes, we'll use the actual numbers!!

now, what about those pesky alpha sizes, like S, M, XL, and so on...

HEY!! BRILLIANT IDEA!! let's assign S to 7, M to 9, XL to 14, ...

uh oh, wait a sec

that would make a man's XL shirt, which is approximately a chest 42" the same as a woman's XL dress, which is a size 6...

brett? where are you, brett? could you please normalize this sortorder column for us?

pretty please?|||You just aren't seeing it Rudy..

XL is not 1 size.

And I gotta admit, you're a little edgy today...

In pants a men's XL would be 38-40...

woops, gotta run...

Don't forget your midol

Cast and convert

How do I convert a colomn that is of data type varbinary to varchar so that the hex values saved there are shown as readable text usable with crystal reports.In SQL2K there is a function
master.dbo.fn_varbintohexstr (@.pbinin varbinary(8000) ) returns
nvarchar(4000)
Don't know why @.pbinin is varbinary(8000), since nvarchar(4000) can only
handle 2000 bytes!
It's just pretty simple code for doing the translation; if nvarchar(4000)
doesn't suit you, the source for the function is easy to modify (to create a
different function!)
"shaun" <shaun@.discussions.microsoft.com> wrote in message
news:F8EAF61A-2844-4C7C-BE22-46C72B9A7A77@.microsoft.com...
> How do I convert a colomn that is of data type varbinary to varchar so
that the hex values saved there are shown as readable text usable with
crystal reports.