Showing posts with label column. Show all posts
Showing posts with label column. Show all posts

Monday, March 26, 2012

Gaps in borders between columns...

I have a row in my report that has 6 columns. I want to outline the row with a border, but not the column lines in between. So, I went into BorderColor, changed the Default to white and my Top and Bottom colors as Black. The problem is that where the "white" column lines are, they are displaying 1pt gaps in my outline. I tried changing the BorderStyle to Zero, but it wouldn't take it.

See sample of the report here.

Any ideas?

Thanks,

James H.

I always had problems in formatting these kind of things. You will have to try different possibilities to get this right. Sometimes, It looks different on preview when compared to the ones when deployed. Try changing the Border style, and try chaning the border color of the columns, Good Luck|||

Hii

For this you can merge the cells in the row,then put one rectangle over there. then according to the width of each column put the text boxes inside the rectangle without giving any border style for the text boxes.finally give the top and bottom border for the whole rectangle.

Hope this works fine

Thanks

Mahasweta

|||

>> I tried changing the BorderStyle to Zero but it wouldn't take it

I assume you mean BorderWidth here...

You're right, you can't put in 0. But you can do this:

=IIF(false,1,0)

This, plus a BorderStyle of None, for each column, seems to do what you want... not 100% positive but it looks right, check it out...

>L<

|||This would work, but gets jacked up when exporting out to excel.|||

I ended up getting some help on this from a thread on sqlservercentral.com.

This is what I had to do to get a top and bottom border (no left or right borders) without the gaps in the top and bottom borders...

BorderColor

Default = White Left = White Right = White Top = Black Bottom = Blacksql

Gaps in borders between columns...

I have a row in my report that has 6 columns. I want to outline the row with a border, but not the column lines in between. So, I went into BorderColor, changed the Default to white and my Top and Bottom colors as Black. The problem is that where the "white" column lines are, they are displaying 1pt gaps in my outline. I tried changing the BorderStyle to Zero, but it wouldn't take it.

See sample of the report here.

Any ideas?

Thanks,

James H.

I always had problems in formatting these kind of things. You will have to try different possibilities to get this right. Sometimes, It looks different on preview when compared to the ones when deployed. Try changing the Border style, and try chaning the border color of the columns, Good Luck|||

Hii

For this you can merge the cells in the row,then put one rectangle over there. then according to the width of each column put the text boxes inside the rectangle without giving any border style for the text boxes.finally give the top and bottom border for the whole rectangle.

Hope this works fine

Thanks

Mahasweta

|||

>> I tried changing the BorderStyle to Zero but it wouldn't take it

I assume you mean BorderWidth here...

You're right, you can't put in 0. But you can do this:

=IIF(false,1,0)

This, plus a BorderStyle of None, for each column, seems to do what you want... not 100% positive but it looks right, check it out...

>L<

|||This would work, but gets jacked up when exporting out to excel.|||

I ended up getting some help on this from a thread on sqlservercentral.com.

This is what I had to do to get a top and bottom border (no left or right borders) without the gaps in the top and bottom borders...

BorderColor

Default = White Left = White Right = White Top = Black Bottom = Black

Friday, March 23, 2012

Fuzzy lookup transform row scores 'inconsistent' with individual column scores

I am trying to interpret some of the results I observe when trying to match similar records using a fuzzy lookup transform, but it's not entirely clear how the overall row similarity score is calculated. In particular, sometimes rows with lower individual column similarity scores will achieve a higher similarity and confidence score than a matching row with higher individual column scores.

The transform is configured with 6 text fields set to fuzzy mapping and a minimum similarity of 0, and 3 additional numeric fields with an exact mapping. It is set to return a maximum of 2 matches per lookup and to do an exhaustive search of the reference table.

For example, from the following matching pair of records Match 1 is picked over Match 2 even though it's individual scores are lower.

Match 1 Match 2
-- --
_similarity_author 1.0 1.0
_similarity_title 0.85344648 1.0
_similarity_headline 0.0125 0.0125
_similarity_summary 0.0125 0.0125
_similarity_picture 1.0 1.0
_similarity_caption 1.0 1.0

_similarity 7.8429267E-2 7.3196657E-2
_confidence 0.55728668 0.44271332

In another case both matching records have *identical* scores for every mapped column and yet their similarity and confidence scores are different.

Clearly there are other factors involved in calculating the overall row score. Anybody know what these are?


Fernando Tubio

Can't even begin to describe it in my own words. This article describes the Fuzzy Math real well. Don't know if you've seen it.

http://msdn.microsoft.com/msdnmag/issues/05/09/SQLServer2005/

|||

Thank you Martin.

I've read the article and it explains the lookup process well. Unfortunately it doesn't answer my question. Specifically, having found two matches, why does the matching algorithm discard what appears to be a better match, at least judging from individual column similarity scores.

I am trying to understand the mechanism to determine if there is anything I can tweak in order to force the algorithm to make a better choice.

Fuzzy Lookup error - Multiple Identity Columns?

I'm developing an ETL solution that needs to look for duplicate records using a fuzzy lookup. If the lookup table has an identity column, I get the following error. I can get this to work on a local desktop instance of SQL server, but not on my development server or production box. Any help is greatly appreciated.

Also I've stripped down the incoming data for the lookup to a very simplified version of what I'd like to use, but if I can't get it to work then I can't add addtional columns to match with.

It's like it's trying to add it's own Id to the temp table created for the tokens

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Multiple identity columns specified for table '##FLRef_070403_10:09:36_3532_aeac56a4-8bc0-4ff4-ac41-0984e293261a'. Only one identity column per table is allowed.".

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Database name 'tempdb' ignored, referencing object in tempdb.".

Error: 0xC004701A at Move Clean Records into Clean Enrollment, DTS.Pipeline: component "Fuzzy Lookup" (300) failed the pre-execute phase and returned error code 0xC0202009.

I have found an interesting solution to this problem, but I'm still left wondering if this is by design or error. The reference table must be in the dbo schema to work. I began with a reference table in a schema owned by me. I finally moved to another DB on the same server and it worked. The difference I then noticed was that the table was now in dbo. I went back to the original development DB and changed ownership on my schema to dbo. I ran the package and got the same error. So I then moved the table to dbo and it worked just fine. If anybody knows why this might be, I'd really like to know. If you'd like to see where I started out, get a copy of Hands-On SQL Server 2005 Integration Services by Ashwani Nanda. In Chapter 10 there is a good section on removing duplicates.

Unaswered questions

Is it a permission issue, a DB confituration issue or the design of the Fuzzy Lookup or an error.

It is interesting that if I remove the ID column (which uses identity(1,1))from my reference table in my schema it will work, but its sort of the point to be able to go back to the duplicate records for updates, inserts or deletes etc.

Monday, March 19, 2012

functions in view

I have a view on SQL server 2000.
Using enterprise mngr to admin it.
In my view I would like to add a column that calculates something with the
other column.
An example could be:
Column Table
a x
b x
a1 y
mycustomcolumn = (a+b)*a1 =>> how can I insert this.
It's a bit like in Ms Access, where you have the expression builder.
THX
nicholas wrote:
> I have a view on SQL server 2000.
> Using enterprise mngr to admin it.
> In my view I would like to add a column that calculates something
> with the other column.
> An example could be:
> Column Table
> a x
> b x
> a1 y
> mycustomcolumn = (a+b)*a1 =>> how can I insert this.
> It's a bit like in Ms Access, where you have the expression builder.
> THX
create table test123 (col1 int, col2 int)
create view vTest
as
Select col1, col2, col3 = col1 * col2
From test123
Don't rely on SQL EM for object maintenance. Learn to use actual DDL
scripting. SQL EM doesn't do everyting and maintaining from scripts give
you a good handle on object design.
David Gugick
Imceda Software
www.imceda.com
|||perfect !
THX a lot
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%23Zy9QfsEFHA.3924@.TK2MSFTNGP09.phx.gbl...
> nicholas wrote:
> create table test123 (col1 int, col2 int)
> create view vTest
> as
> Select col1, col2, col3 = col1 * col2
> From test123
>
> Don't rely on SQL EM for object maintenance. Learn to use actual DDL
> scripting. SQL EM doesn't do everyting and maintaining from scripts give
> you a good handle on object design.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>

functions in view

I have a view on SQL server 2000.
Using enterprise mngr to admin it.
In my view I would like to add a column that calculates something with the
other column.
An example could be:
Column Table
a x
b x
a1 y
mycustomcolumn = (a+b)*a1 =>> how can I insert this.
It's a bit like in Ms Access, where you have the expression builder.
THXnicholas wrote:
> I have a view on SQL server 2000.
> Using enterprise mngr to admin it.
> In my view I would like to add a column that calculates something
> with the other column.
> An example could be:
> Column Table
> a x
> b x
> a1 y
> mycustomcolumn = (a+b)*a1 =>> how can I insert this.
> It's a bit like in Ms Access, where you have the expression builder.
> THX
create table test123 (col1 int, col2 int)
create view vTest
as
Select col1, col2, col3 = col1 * col2
From test123
Don't rely on SQL EM for object maintenance. Learn to use actual DDL
scripting. SQL EM doesn't do everyting and maintaining from scripts give
you a good handle on object design.
David Gugick
Imceda Software
www.imceda.com|||perfect !
THX a lot
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%23Zy9QfsEFHA.3924@.TK2MSFTNGP09.phx.gbl...
> nicholas wrote:
> > I have a view on SQL server 2000.
> > Using enterprise mngr to admin it.
> >
> > In my view I would like to add a column that calculates something
> > with the other column.
> > An example could be:
> >
> > Column Table
> > a x
> > b x
> > a1 y
> > mycustomcolumn = (a+b)*a1 =>> how can I insert this.
> >
> > It's a bit like in Ms Access, where you have the expression builder.
> >
> > THX
> create table test123 (col1 int, col2 int)
> create view vTest
> as
> Select col1, col2, col3 = col1 * col2
> From test123
>
> Don't rely on SQL EM for object maintenance. Learn to use actual DDL
> scripting. SQL EM doesn't do everyting and maintaining from scripts give
> you a good handle on object design.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>

functions in view

I have a view on SQL server 2000.
Using enterprise mngr to admin it.
In my view I would like to add a column that calculates something with the
other column.
An example could be:
Column Table
a x
b x
a1 y
mycustomcolumn = (a+b)*a1 =>> how can I insert this.
It's a bit like in Ms Access, where you have the expression builder.
THXnicholas wrote:
> I have a view on SQL server 2000.
> Using enterprise mngr to admin it.
> In my view I would like to add a column that calculates something
> with the other column.
> An example could be:
> Column Table
> a x
> b x
> a1 y
> mycustomcolumn = (a+b)*a1 =>> how can I insert this.
> It's a bit like in Ms Access, where you have the expression builder.
> THX
create table test123 (col1 int, col2 int)
create view vTest
as
Select col1, col2, col3 = col1 * col2
From test123
Don't rely on SQL EM for object maintenance. Learn to use actual DDL
scripting. SQL EM doesn't do everyting and maintaining from scripts give
you a good handle on object design.
David Gugick
Imceda Software
www.imceda.com|||perfect !
THX a lot
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%23Zy9QfsEFHA.3924@.TK2MSFTNGP09.phx.gbl...
> nicholas wrote:
> create table test123 (col1 int, col2 int)
> create view vTest
> as
> Select col1, col2, col3 = col1 * col2
> From test123
>
> Don't rely on SQL EM for object maintenance. Learn to use actual DDL
> scripting. SQL EM doesn't do everyting and maintaining from scripts give
> you a good handle on object design.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>

Monday, March 12, 2012

Function to lookup row in dataset?

Is there a function to lookup a row in a dataset? Say, find the row where
column x is equal to 10?
Find(Fields!ProductNumber.Value, 10, "Category")
--
McGeeky
http://mcgeeky.blogspot.comNo, but what is it that you are trying to do. There might be another way to
accomplish it.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"McGeeky" <anon@.anon.com> wrote in message
news:usbEceldFHA.3452@.TK2MSFTNGP10.phx.gbl...
> Is there a function to lookup a row in a dataset? Say, find the row where
> column x is equal to 10?
> Find(Fields!ProductNumber.Value, 10, "Category")
> --
> McGeeky
> http://mcgeeky.blogspot.com
>
>|||I will be returning a small data set with a known number of rows in it. I
don't want to display the data in a table but rather selectively pick values
out of the data set and display them in various parts of my report.
--
McGeeky
http://mcgeeky.blogspot.com
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:#5jSf8ldFHA.3184@.TK2MSFTNGP15.phx.gbl...
> No, but what is it that you are trying to do. There might be another way
to
> accomplish it.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "McGeeky" <anon@.anon.com> wrote in message
> news:usbEceldFHA.3452@.TK2MSFTNGP10.phx.gbl...
> > Is there a function to lookup a row in a dataset? Say, find the row
where
> > column x is equal to 10?
> >
> > Find(Fields!ProductNumber.Value, 10, "Category")
> >
> > --
> > McGeeky
> > http://mcgeeky.blogspot.com
> >
> >
> >
>

Function to create comma separated list from any given column/table.

Hi,

I'm sure this is a common problem.. to create a single field from a
whole column, where each row would be separated by a comma.

I can do this for a specified table, and column.. and I've created a
function using VBA to achieve a more dynamic (and very slow) solution..
so I would like to implement it using a user defined function in sql server.

The problems I'm facing are, that I can't use dynamic sql in a
function.. and I also can't use temporary tables which could build up a
'standard' table from parameters given to then perform the function on.

So, with these limitations, what other options do I have?

Cheers,
ChrisDynamic SQL is so so passe'

Firstly it is a security risk , as you can't lock down rights to procedures
that use it.

secondly it is inefficient as it has to be re-generated and compiled each
time you use it

Code Generation is the all the rage now.

And how often will you be adding new code tables to do this with?

Here's how to roll your own code generator in SQL:

in Query Analyzer (aka ISQL)

write a template code block to get it to work (I've done this below)

wrap that in print statements.

create a cursor to loop thru sysobjects and/or syscolumns (or the SCHEMA
Views) to locate all your tables with the columns you need.

loop thru and generate all the script you need.

you can create a separate function/procedure for each or create a single one
with a large case in it (your call).

run the output to create the function(s).

save the script that generates the stuff and hold on to it.

every time a change is made to a code table (i.e. add new table, change
column names, etc..) rerun the script to regenerated (make sure there is a
drop in there somewhere as well)

Tally Ho! - (which is a liquor store her in Delaware, and not a Fox hunting
term, which I understand is now outlawed in the UK).

------------------
-- declare an empty string (not null)
declare @.list varchar 4000
set @.list = ''

-- select multiple rows into a single variable
select @.list = @.list + ',' + ColumnName from TableName

-- get rid of the last comma
select @.list = substring( @.list , 1, len( @.list ) -1 )
------------------

"Not Me" <Noone.is.home@.here.com> wrote in message
news:cl87ou$fam$1@.ucsnew1.ncl.ac.uk...
> Hi,
> I'm sure this is a common problem.. to create a single field from a whole
> column, where each row would be separated by a comma.
> I can do this for a specified table, and column.. and I've created a
> function using VBA to achieve a more dynamic (and very slow) solution.. so
> I would like to implement it using a user defined function in sql server.
> The problems I'm facing are, that I can't use dynamic sql in a function..
> and I also can't use temporary tables which could build up a 'standard'
> table from parameters given to then perform the function on.
> So, with these limitations, what other options do I have?
> Cheers,
> Chris

Function Suppressing Index

I have the following query that needs to be rewritten (if possible) so that
the clustered index on column "coldate" can be utilized. Any suggestions
would be appreciated. I realize that getting the functions off the column is
the goal, but I am not seeing how that might be done in this instance. Thank
s
in advance.
SELECT *
FROM
table t
WHERE
DateDiff(n, DateAdd(n, t.col1 - 1, t.coldate), @.enddate) <= 0
Message posted via http://www.droptable.comthis looks like homework.
you really can't because you are simultaneoulsy doing a calc on t.col1
for every row.
you could probably increase performance though by getting rid of
datediff, and just comparing the dates of hte dateadd results vs
@.enddate.|||Hi
If you don't need to have the date as a date then you may want to store
coldate as a offset for the number of minutes.
John
"cbrichards" wrote:

> I have the following query that needs to be rewritten (if possible) so tha
t
> the clustered index on column "coldate" can be utilized. Any suggestions
> would be appreciated. I realize that getting the functions off the column
is
> the goal, but I am not seeing how that might be done in this instance. Tha
nks
> in advance.
> SELECT *
> FROM
> table t
> WHERE
> DateDiff(n, DateAdd(n, t.col1 - 1, t.coldate), @.enddate) <= 0
> --
> Message posted via http://www.droptable.com
>

Function Suppressing Index

I have the following query that needs to be rewritten (if possible) so that
the clustered index on column "coldate" can be utilized. Any suggestions
would be appreciated. I realize that getting the functions off the column is
the goal, but I am not seeing how that might be done in this instance. Thanks
in advance.
SELECT *
FROM
table t
WHERE
DateDiff(n, DateAdd(n, t.col1 - 1, t.coldate), @.enddate) <= 0
--
Message posted via http://www.sqlmonster.comthis looks like homework.
you really can't because you are simultaneoulsy doing a calc on t.col1
for every row.
you could probably increase performance though by getting rid of
datediff, and just comparing the dates of hte dateadd results vs
@.enddate.|||Hi
If you don't need to have the date as a date then you may want to store
coldate as a offset for the number of minutes.
John
"cbrichards" wrote:
> I have the following query that needs to be rewritten (if possible) so that
> the clustered index on column "coldate" can be utilized. Any suggestions
> would be appreciated. I realize that getting the functions off the column is
> the goal, but I am not seeing how that might be done in this instance. Thanks
> in advance.
> SELECT *
> FROM
> table t
> WHERE
> DateDiff(n, DateAdd(n, t.col1 - 1, t.coldate), @.enddate) <= 0
> --
> Message posted via http://www.sqlmonster.com
>

Wednesday, March 7, 2012

Function for counting distance

Hi,
I want to create function which will return column of maximal distance.
SELECT MaxDistance(X,Y) FROM Table;
Is it possible ?
(Something like MAX function but I will have two arguments in MaxDistance on
which will be calculated distance e.g.: x-y).
Thank you very much.Are you simply attempting to identify max of a difference operation?
If so then calling MAX(x-y) will provide this. The MAX() function
belongs to the Aggregate functions. An Aggregate UDF will not be
available until SQL Server 2005.|||Like this?
SELECT MAX(ABS(x-y)) FROM Table
David Portas
SQL Server MVP
--

Function Column

I have a question. I am relatively new to databases, and I have
question that I am not sure of. I have a table consisting of computer
information (name, type, location, etc). One of the columns that I
have is warrantyDate which is of type DateTime and it tells when a
machine goes out of warranty.
I would like to have another column in which gives the days left of
warranty. I would like this to be updated constantly, so any day I
query the table, the proper amount of days left on the warranty of a
machine will be available. Is this possible? If so, where do I start
to look for the answers?
Thanks!You can have:
1) a computed column
2) a view that does the equivalent of 1
3) do the query on the fly
Here's the computed column:
alter table MyTable
add
DaysLeft as datediff (dd, getdate(), warrantyDate)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
<leshanster@.gmail.com> wrote in message
news:1148824834.873869.236910@.g10g2000cwb.googlegroups.com...
I have a question. I am relatively new to databases, and I have
question that I am not sure of. I have a table consisting of computer
information (name, type, location, etc). One of the columns that I
have is warrantyDate which is of type DateTime and it tells when a
machine goes out of warranty.
I would like to have another column in which gives the days left of
warranty. I would like this to be updated constantly, so any day I
query the table, the proper amount of days left on the warranty of a
machine will be available. Is this possible? If so, where do I start
to look for the answers?
Thanks!|||Thank you so much! That was exactly what I wanted!

Function Column

I have a question. I am relatively new to databases, and I have
question that I am not sure of. I have a table consisting of computer
information (name, type, location, etc). One of the columns that I
have is warrantyDate which is of type DateTime and it tells when a
machine goes out of warranty.
I would like to have another column in which gives the days left of
warranty. I would like this to be updated constantly, so any day I
query the table, the proper amount of days left on the warranty of a
machine will be available. Is this possible? If so, where do I start
to look for the answers?
Thanks!You can have:
1) a computed column
2) a view that does the equivalent of 1
3) do the query on the fly
Here's the computed column:
alter table MyTable
add
DaysLeft as datediff (dd, getdate(), warrantyDate)
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
<leshanster@.gmail.com> wrote in message
news:1148824834.873869.236910@.g10g2000cwb.googlegroups.com...
I have a question. I am relatively new to databases, and I have
question that I am not sure of. I have a table consisting of computer
information (name, type, location, etc). One of the columns that I
have is warrantyDate which is of type DateTime and it tells when a
machine goes out of warranty.
I would like to have another column in which gives the days left of
warranty. I would like this to be updated constantly, so any day I
query the table, the proper amount of days left on the warranty of a
machine will be available. Is this possible? If so, where do I start
to look for the answers?
Thanks!|||Thank you so much! That was exactly what I wanted!

Sunday, February 26, 2012

Full-Text-Search

Hi,
I have a full-text search enabled table and an image data type column that stores the extracts from the documents stored in the files. I have created a full text catalog that indexes few columns in the table and image data type column as well and I assigned the extension column to this image data type column.
Everything works fine except that search on the image data type column called, in my case "body" does not return anything. The documents exist, there is an information in the "body" column.

My code goes like this:
select FT_TBL.resourceID, FT_TBL.lang, FT_TBL.keywords, FT_TBL.description, FT_TBL.extension, b.rank
from full_text_search as FT_TBL inner join containstable(full_text_search, *, 'Microsoft') as b
on b.[key] = FT_TBL.resourceID
order by b.rank desc

any help appreciated,
thanksI figured the solution to the previous post. I was trying to search on the .pdf files, but I do not have filter installed on the server to filter through these files, other files extension work fine.

Another problem:
I have two tables that are full-text search enabled. And I need to search one table first, and than the columns in the second one. This works fine if the search criteria is found in both tables, but if not the query returns nothing.

help much appreciated!
Thanks|||use union all.

Full-text search: Stemming

In a column that I'm searching for, I have 'rod' and 'rods' for fishing gear.
I'm using WHERE CONTAINS ( p.*, 'FORMSOF(INFLECTIONAL, 'rod')')
It only returns rows with 'rod'. I'm expecting rows with 'rod' and 'rods'.
What am I missing?
I would think what you have should work...but try
contains(p.*, 'Formsof(Inflectional, rod)' ) -- note the keyword is not
quoted
or
contains (p.*, "rod*")
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"MGBloomfield" <MGBloomfield@.discussions.microsoft.com> wrote in message
news:24EC5637-8762-4A4C-9354-A7C5BE414864@.microsoft.com...
> In a column that I'm searching for, I have 'rod' and 'rods' for fishing
gear.
> I'm using WHERE CONTAINS ( p.*, 'FORMSOF(INFLECTIONAL, 'rod')')
> It only returns rows with 'rod'. I'm expecting rows with 'rod' and 'rods'.
> What am I missing?
>
|||Good suggestion. I tried that, too.
Same result, only rows with 'rod'.
If I query using LIKE '%rod%', then I get 20 rows so that's what I'm
expecting with full-text indexing.
What else should I be doing? Is stemming supposed to "turned on" or
something? The full-text language for all the columns is English.
"Wayne Snyder" wrote:

> I would think what you have should work...but try
> contains(p.*, 'Formsof(Inflectional, rod)' ) -- note the keyword is not
> quoted
> or
> contains (p.*, "rod*")
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "MGBloomfield" <MGBloomfield@.discussions.microsoft.com> wrote in message
> news:24EC5637-8762-4A4C-9354-A7C5BE414864@.microsoft.com...
> gear.
>
>

Full-text search: Stemming

In a column that I'm searching for, I have 'rod' and 'rods' for fishing gear
.
I'm using WHERE CONTAINS ( p.*, 'FORMSOF(INFLECTIONAL, 'rod')')
It only returns rows with 'rod'. I'm expecting rows with 'rod' and 'rods'.
What am I missing?I would think what you have should work...but try
contains(p.*, 'Formsof(Inflectional, rod)' ) -- note the keyword is not
quoted
or
contains (p.*, "rod*")
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"MGBloomfield" <MGBloomfield@.discussions.microsoft.com> wrote in message
news:24EC5637-8762-4A4C-9354-A7C5BE414864@.microsoft.com...
> In a column that I'm searching for, I have 'rod' and 'rods' for fishing
gear.
> I'm using WHERE CONTAINS ( p.*, 'FORMSOF(INFLECTIONAL, 'rod')')
> It only returns rows with 'rod'. I'm expecting rows with 'rod' and 'rods'.
> What am I missing?
>|||Good suggestion. I tried that, too.
Same result, only rows with 'rod'.
If I query using LIKE '%rod%', then I get 20 rows so that's what I'm
expecting with full-text indexing.
What else should I be doing? Is stemming supposed to "turned on" or
something? The full-text language for all the columns is English.
"Wayne Snyder" wrote:

> I would think what you have should work...but try
> contains(p.*, 'Formsof(Inflectional, rod)' ) -- note the keyword is not
> quoted
> or
> contains (p.*, "rod*")
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "MGBloomfield" <MGBloomfield@.discussions.microsoft.com> wrote in message
> news:24EC5637-8762-4A4C-9354-A7C5BE414864@.microsoft.com...
> gear.
>
>

Full-text search: Stemming

In a column that I'm searching for, I have 'rod' and 'rods' for fishing gear.
I'm using WHERE CONTAINS ( p.*, 'FORMSOF(INFLECTIONAL, 'rod')')
It only returns rows with 'rod'. I'm expecting rows with 'rod' and 'rods'.
What am I missing?I would think what you have should work...but try
contains(p.*, 'Formsof(Inflectional, rod)' ) -- note the keyword is not
quoted
or
contains (p.*, "rod*")
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"MGBloomfield" <MGBloomfield@.discussions.microsoft.com> wrote in message
news:24EC5637-8762-4A4C-9354-A7C5BE414864@.microsoft.com...
> In a column that I'm searching for, I have 'rod' and 'rods' for fishing
gear.
> I'm using WHERE CONTAINS ( p.*, 'FORMSOF(INFLECTIONAL, 'rod')')
> It only returns rows with 'rod'. I'm expecting rows with 'rod' and 'rods'.
> What am I missing?
>|||Good suggestion. I tried that, too.
Same result, only rows with 'rod'.
If I query using LIKE '%rod%', then I get 20 rows so that's what I'm
expecting with full-text indexing.
What else should I be doing? Is stemming supposed to "turned on" or
something? The full-text language for all the columns is English.
"Wayne Snyder" wrote:
> I would think what you have should work...but try
> contains(p.*, 'Formsof(Inflectional, rod)' ) -- note the keyword is not
> quoted
> or
> contains (p.*, "rod*")
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "MGBloomfield" <MGBloomfield@.discussions.microsoft.com> wrote in message
> news:24EC5637-8762-4A4C-9354-A7C5BE414864@.microsoft.com...
> > In a column that I'm searching for, I have 'rod' and 'rods' for fishing
> gear.
> >
> > I'm using WHERE CONTAINS ( p.*, 'FORMSOF(INFLECTIONAL, 'rod')')
> >
> > It only returns rows with 'rod'. I'm expecting rows with 'rod' and 'rods'.
> >
> > What am I missing?
> >
>
>

Sunday, February 19, 2012

Full-Text Search

I am trying to create a full text index on a column in a table and the data type is Image, but the TYPE COLUMN FILE EXTENSION is in another table. there is no way of creating the type in the same table. the wizard will not allow me to pick the data type field from another table. is there a work around this issue?

Emadkb

Adding a column to the table is not an option?|||It is a fram work table and making any changes now would be impossible. Do you see any other solutions to this issue. I really appreciate your quick response. Emadkb|||If you are using SQL 2005, you can make an indexed view and build fulltext index on the view. However that do means you dup the storage for the image columns.|||Thank you so much....I was wishing there is another solution. emadkb|||

hey Feng Tian
I hope you could help me in FullText Search. No one else seems to be knowing about Fulltext Search.
I'm able to create Full text indexing catalogs, populate them etc.. when i login directly from the server using Sql Server Enterprise manager but when I try to use the same login (db owner) remotely from some other machine using SQL Server Enterprise Manager, I see disabled(stopped) Full-text Service and all full-text features disabled. So does it mean i cannot populate or create catalogs remotely using sql server enterprise manager.
I have tried this on two different servers and then accessing from two different remote/client machines. But the problem is there any way.

Server: Mssql 2000 server
Remote/Client system: Win XP service Pack2
Access Software: Microsoft Sql Server 2000 Enterprise Manager.

Thanks
Sattva

|||

Sattva,
The behievor you are experiencing with your SQL Server 2000 Enterprise Manager (EM) is typical and expected. The EM cannnot "see remote" services such as the "Microsoft Search" (MSSearch) service on remote servers when accessed across the internet or the server is registered as an IP address. However, SQL FTS is still functional and you can use the system stored procedures to remotely manage and populate the FT Catalogs, for example:

To Create/Remove the Existing Full-Text Table Index, Catalog
-- If Full-Text Index exists, DROP that Index,
-- If Full-Text Index does not exist, CREATE that Index.
--
use pubs
go
IF OBJECTPROPERTY ( object_id('pub_info'),'TableHasActiveFulltextIndex') = 1
BEGIN
print 'Table pub_info is Full-Text Enabled, dropping Full-Text Index & Catalog...'
EXEC sp_fulltext_table 'pub_info', 'drop'
EXEC sp_fulltext_catalog 'PubInfo', 'drop'
END
ELSE IF OBJECTPROPERTY ( object_id('pub_info'),'TableHasActiveFulltextIndex') = 0
BEGIN
print 'Table pub_info is NOT Full-Text Enabled, creating FT Catalog, Index & Activating...'
EXEC sp_fulltext_catalog 'PubInfo', 'create'
EXEC sp_fulltext_table 'pub_info', 'create', 'PubInfo', 'UPKCL_pubinfo'
EXEC sp_fulltext_column 'pub_info', 'pub_id', 'add'
EXEC sp_fulltext_column 'pub_info', 'pr_info', 'add'
EXEC sp_fulltext_table 'pub_info', 'activate'
END

EXEC sp_fulltext_catalog 'PubInfo', 'start_full' -- "Full Crawl"
Wait for crawl to complete
--
Confirm above results with:
--
SELECT pub_id, pr_info
FROM pub_info WHERE CONTAINS(pr_info, '"book*"')

See my blog entry "SQL Server 2000 Full-Text Search Resources and Links" for more SQL 2000 FTS information.

Hope that helps,
John

SQL Full Text Search Blog
http://jtkane.spaces.live.com/

|||

Thanks John. It was a great help. Now I hav it all working.

Cheers mate.

FullText Results from what column

I am running SQL 2000 - upgrading to 2005 hopefully Q1-2007

I have a table that has 12 columns fulltext indexed for searching. Is there a way to tell what column has the search string?

If it is not possible in 2000 what about 2005?

Thanks

If you do a query like select * from t1 where contains(*,'term') you cannot tell which column the hit was in.

You could do a set of queries with each column in turn if you wanted to tell where the term was found:

select * from t1 where contains(col1, 'term')

select * from t1 where contains(col2, 'term')

etc..

In SQL 2005 you can specify a column list in the query (rather than just a single column or *) so you could do queries like:

select * from t1 where conatins((col1,col2,col3), 'term')

Depending on your application that may be useful to you.

Hope this helps.

Dave