hyphens change between w2K and w2K3 ???

On one system a search for 21-147 returns 1 result - which is the
correct way I need it to work.
However on my new system, this does an OR search, returning all results
with 21 in, and all results wth 147 in which clearly returns hundreds.
These are product part numbers, they /must/ be searchable as they look
on the printed litereature, prefereably only returning 1 correct
result!
I've tried all sorts of things, changing the collation, changing the
word breaker langauge etc
The only other diffrence now is that the first system is on Windows 2K,
and the new one that doesn't work is on Windows 2K3.
As a small fix, i looked for the - and replaced it with an AND. However
some part numbers are of the form N-234S and as such the initial letter
is removed by the noise list and the search returns no results.
This is REALLY important any help would be very greatly appreciated. I
have weeks to get htis working, or the entire web project is down the
pan.
Other info, using commerce server 2002.
Thanks in advance,
Martin
I think you'll have to use another character. For instance have two copies
of your data, one for show and one for indexing. Replace hyphenated words
and numbers with something like 123HYPHEN234.
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
"Martin" <bigmarts@.hotmail.com> wrote in message
news:1131125590.462357.125330@.z14g2000cwz.googlegr oups.com...
> On one system a search for 21-147 returns 1 result - which is the
> correct way I need it to work.
> However on my new system, this does an OR search, returning all results
> with 21 in, and all results wth 147 in which clearly returns hundreds.
> These are product part numbers, they /must/ be searchable as they look
> on the printed litereature, prefereably only returning 1 correct
> result!
> I've tried all sorts of things, changing the collation, changing the
> word breaker langauge etc
> The only other diffrence now is that the first system is on Windows 2K,
> and the new one that doesn't work is on Windows 2K3.
> As a small fix, i looked for the - and replaced it with an AND. However
> some part numbers are of the form N-234S and as such the initial letter
> is removed by the noise list and the search returns no results.
> This is REALLY important any help would be very greatly appreciated. I
> have weeks to get htis working, or the entire web project is down the
> pan.
> Other info, using commerce server 2002.
> Thanks in advance,
> Martin
>
|||That's crazy. Any other suggestions...? It works on one machine, I
must be able to get it to work again...? Can I not copy the
wordbreaker dll from a win 2K machine onto Win 2K3. And what does it
have to do with the OS anyway. Surely this is a DB issue. Wierd.
Also the reason we can't really get rid of the hyphens is due to the
fact that 21-147 and 21147 are two different parts. Silly I know, but I
have to work with it.
|||Are you using FreeText of Contains? I think a contains might work for you.
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
"Martin" <bigmarts@.hotmail.com> wrote in message
news:1131363687.867237.149540@.g49g2000cwa.googlegr oups.com...
> That's crazy. Any other suggestions...? It works on one machine, I
> must be able to get it to work again...? Can I not copy the
> wordbreaker dll from a win 2K machine onto Win 2K3. And what does it
> have to do with the OS anyway. Surely this is a DB issue. Wierd.
> Also the reason we can't really get rid of the hyphens is due to the
> fact that 21-147 and 21147 are two different parts. Silly I know, but I
> have to work with it.
>
|||It's a commerce server routine. I think it uses FREETEXT. But trying
it in query analyser works on the w2K machine for CONTAINS and
FREETEXT, but for niether on the W2K3 machine.
Any Microsoft people around? Can I use the W2K wordbreaker dll on the
2003 server machine...? It seems that this is what's causing the
problem.
Thanks,
MArtin

Hyphen padding in t-sql query result

A newbie question here:

In T-SQL my query to text file results in the padded second line containing a set of hyphens. How do I turn that off so that just the first line contains the headers and the results immediately follow in line 2.

Thanks!

You need to describe your problem a bit. It would help to have relevant code, data definition, what the incorrect results look like and the correct results look like. It would also help to have the associated sample data.|||

Certainly.

I'm composing a job in sql server using sqlcmd. Here's my command-line

EXEC master..xp_cmdshell 'sqlcmd -X -S server -q "SET NOCOUNT ON SELECT * FROM pubs.dbo.test" -s , -w 255 -u -o "\\server\output.csv"'

Output is just fine with the exception of that pesky line 2 of padded hyphens. Appears like this:

department,FundCode,Month

-,--,--,
DM ,SAZXZ , 8

DM ,ESJXZ , 7
DM ,DGLXS , 9
GN ,DGLXZ , 8

|||

That 'pesky line 2' is part of the output 'header'.

You can turn the headers off (that will include the line 1 with the column names) by adding this switch to the SQLCmd command line:

-h-1

(No space between the h and the -1.)

You probably don't need line 1 or 'pesky line 2' in your application.

As far as I am aware, those are your choices...

|||

Plus, if you don't like their output, it is pretty easy to build a console app to get the results. I had to do it once to deal with Text values that were very large and I couldn't get sqlcmd to do.

In 2005, you might even be able to write a CLR stored procedure (albeit an unsafe one) that will do the work directly that you have complete control over.

|||I was aware of the -h switch but I'd prefer to keep the headers to be explicit. If sqlcmd doesn't support it then I'll have to write something up. Thanks for your input everyone!|||This is kind of a cheesy solution, but it works.

Use this command:

EXEC master..xp_cmdshell 'sqlcmd -X -S server -q "SET NOCOUNT ON Select 'department','FundCode','Month' SELECT * FROM pubs.dbo.test" -s , -w 255 -u -o "\\server\output.csv" -h-1'

Notice the header switch (-h) has a value of -1 which means that it is off. The first Select statement returns one more row of results which contains the "headers"

Your output should look like this:

department,FundCode,Month

DM ,SAZXZ , 8

DM ,ESJXZ , 7
DM ,DGLXS , 9
GN ,DGLXZ , 8

|||

Brilliant! I had to use double single-quotes (e.g., ''Department'') to get it to parse correctly but this is what I needed.

Not cheesy at all. I too hide my inelegance with cleverness.