Gaps in my reports

I have a report with 10 subreports in it. The initial report only contains 2
parameters which are used to populate the sub-reports.
The subreports are situated under eachother in the designer with nogaps in
between and the tables within the sub-reports have visibility set to hidden
when the RowCount of the dataset is 0... I would expect this to mean hide the
contents of the report when there is no data.
Why does Reporting Services add gaps where my sub-reports should be when the
main report renders ?.. Is there a way to stop this.
Help.. Ive been pulling my hair out over this one!.. ThanksJust an idea, you may want to try applying the expression in the Visibility
Properties for the ROW (not the subreport). Hopefully that will take care of
the blank row(s).
"DecksTerrorT" wrote:
> I have a report with 10 subreports in it. The initial report only contains 2
> parameters which are used to populate the sub-reports.
> The subreports are situated under eachother in the designer with nogaps in
> between and the tables within the sub-reports have visibility set to hidden
> when the RowCount of the dataset is 0... I would expect this to mean hide the
> contents of the report when there is no data.
> Why does Reporting Services add gaps where my sub-reports should be when the
> main report renders ?.. Is there a way to stop this.
> Help.. Ive been pulling my hair out over this one!.. Thanks|||Maybe my post was a litle cryptic.. let me try again.
I have a report.
That report has 10 subreports all underneath each other.
Within the subreports the all items have a visibility to false when RowCount
= 0.
So why does the main report display a gap once rendered where the sub-report
should be ?
Hope this makes it alittle clearer.
Decks.
"Nick P." wrote:
> Just an idea, you may want to try applying the expression in the Visibility
> Properties for the ROW (not the subreport). Hopefully that will take care of
> the blank row(s).
> "DecksTerrorT" wrote:
> > I have a report with 10 subreports in it. The initial report only contains 2
> > parameters which are used to populate the sub-reports.
> >
> > The subreports are situated under eachother in the designer with nogaps in
> > between and the tables within the sub-reports have visibility set to hidden
> > when the RowCount of the dataset is 0... I would expect this to mean hide the
> > contents of the report when there is no data.
> >
> > Why does Reporting Services add gaps where my sub-reports should be when the
> > main report renders ?.. Is there a way to stop this.
> >
> > Help.. Ive been pulling my hair out over this one!.. Thanks|||I had to overcome a similar "gap" problem where there could be a LOT of
hidden lines.... the only way I was able to do this was to :
1. select the entire "table" individually in each report
2. edit the size of each table by dividing the length by 10 (so 2.74
inches bacame .274 inches)
3. This gives a totally scrunched up view in the layout tab (which is a
pain for editing later on ... have to multiply by 10 to be able to view
it and then change it back again...)
4. But now ... when I get to actually view the result ... it looks a
LOT better.
5. This solution did not get rid of all the gaps all the time, but it
REALLY made a huge significant difference.
good luck...

funny search results with 'contains' clause

hi,
it is really funny how ft search works:
it seems to depend highly on what could be indexed, but it still doesn't
follow a determined order.
consider the following queries:
select name from person where contains(name, 'hammer')
select name from person where contains(name, '"hammer*"')
the first is a query with a "simple term" and the second one with a
"prefix term".
mssql docu says that a simple term "specifies a match for an exact word",
but the first query returns other matches like "oberhammer" or
"hammerschmied" - this is not an exact match for me; the found words
rather consist of two words "ober", "schmied" and "hammer".
one would expect that the 1st and 2nd query work absolutely different but,
even the 2nd returns a lot more results, they seem to differ just slightly.
when searching for 'ober' (instead of 'hammer'), the 2nd query returns
"obermayr" and "oberlehner", but the 1st results only in "oberlehner",
although "ober", "mayr" and "lehner" are single words themselves.
and why does it find "Tober" or "Dober" if i only searched for 'ober'?
i need to know the rules which mssql uses for fulltext search. can someone
point me to a book or a website where i can find more information? or does
somebody have a good explanation?
btw, we use sql2000 and a german collation.
klaus triendl
unfortunately this behavior is caused by the German wordbreaker. You might
get better results with the neutral word breaker.
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
"klaus triendl" <triendl.kj@.m-box.at> wrote in message
news:opsmm8g1r7cm4gx4@.dev4xp.wasser.local...
> hi,
> it is really funny how ft search works:
> it seems to depend highly on what could be indexed, but it still doesn't
> follow a determined order.
> consider the following queries:
> select name from person where contains(name, 'hammer')
> select name from person where contains(name, '"hammer*"')
> the first is a query with a "simple term" and the second one with a
> "prefix term".
> mssql docu says that a simple term "specifies a match for an exact word",
> but the first query returns other matches like "oberhammer" or
> "hammerschmied" - this is not an exact match for me; the found words
> rather consist of two words "ober", "schmied" and "hammer".
> one would expect that the 1st and 2nd query work absolutely different but,
> even the 2nd returns a lot more results, they seem to differ just
slightly.
> when searching for 'ober' (instead of 'hammer'), the 2nd query returns
> "obermayr" and "oberlehner", but the 1st results only in "oberlehner",
> although "ober", "mayr" and "lehner" are single words themselves.
> and why does it find "Tober" or "Dober" if i only searched for 'ober'?
>
> i need to know the rules which mssql uses for fulltext search. can someone
> point me to a book or a website where i can find more information? or does
> somebody have a good explanation?
> btw, we use sql2000 and a german collation.
>
> --
> klaus triendl
|||klaus,
Could you post the full output of the below SQL code as it is most helpful
in troubleshooting SQL FTS issues.
use <your_database_name_here>
go
SELECT @.@.language
SELECT @.@.version
sp_configure 'default full-text language'
EXEC sp_help_fulltext_catalogs
EXEC sp_help_fulltext_tables
EXEC sp_help_fulltext_columns
EXEC sp_help <your_FT-enable_table_name_here>
go
Specifically, the OS-supplied and language-specific wordbreaker along with
the actual text (single language or mixed?) you are storing in your
FT-enabled table's column is most important information!
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"klaus triendl" <triendl.kj@.m-box.at> wrote in message
news:opsmm8g1r7cm4gx4@.dev4xp.wasser.local...
> hi,
> it is really funny how ft search works:
> it seems to depend highly on what could be indexed, but it still doesn't
> follow a determined order.
> consider the following queries:
> select name from person where contains(name, 'hammer')
> select name from person where contains(name, '"hammer*"')
> the first is a query with a "simple term" and the second one with a
> "prefix term".
> mssql docu says that a simple term "specifies a match for an exact word",
> but the first query returns other matches like "oberhammer" or
> "hammerschmied" - this is not an exact match for me; the found words
> rather consist of two words "ober", "schmied" and "hammer".
> one would expect that the 1st and 2nd query work absolutely different but,
> even the 2nd returns a lot more results, they seem to differ just
slightly.
> when searching for 'ober' (instead of 'hammer'), the 2nd query returns
> "obermayr" and "oberlehner", but the 1st results only in "oberlehner",
> although "ober", "mayr" and "lehner" are single words themselves.
> and why does it find "Tober" or "Dober" if i only searched for 'ober'?
>
> i need to know the rules which mssql uses for fulltext search. can someone
> point me to a book or a website where i can find more information? or does
> somebody have a good explanation?
> btw, we use sql2000 and a german collation.
>
> --
> klaus triendl
|||I am raising a support incident on this one Klaus.
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
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:u3I$G3ZGFHA.4004@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> unfortunately this behavior is caused by the German wordbreaker. You might
> get better results with the neutral word breaker.
> --
> 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
> "klaus triendl" <triendl.kj@.m-box.at> wrote in message
> news:opsmm8g1r7cm4gx4@.dev4xp.wasser.local...
word",[vbcol=seagreen]
but,[vbcol=seagreen]
> slightly.
someone[vbcol=seagreen]
does
>

Funky Problem with Invisible Chart

So, I'm pretty new to Reporting Services and this is my first post in this forum. So basically I was creating a report which contains a chart. I configure the dataset, chart values, etc and the chart shows up blank. The title of the chart shows, but nothing else shows. The way I created the report in the designer is no different that another report that actually works.

I've searched google with every word I could think of and I haven't been able to find this issue anywhere. Is this some crazy Microsoft hiccup?

Here is the Stored Procedure that I am using as my dataset:

CREATE PROCEDURE YearlyTotalsInPercentages(@.Yearint) ASBEGINDECLARE @.TotalSumintSELECT SUM(dbo.Main.Hours) AS CBDCYearlyTotals, dbo.Project.ProductLine AS ProductLineINTO #tempTotalsFROM dbo.Main INNER JOIN dbo.Department ON dbo.Main.DeptNo = dbo.Department.DeptNo INNER JOIN dbo.Project ON dbo.Main.ProjectNo = dbo.Project.ProjectNoWHERE dbo.Main.UserID LIKE'CI%' AND dbo.Project.ControlLocation ='IND' AND DATEPART(yyyy, dbo.Main.DataDate) = @.Year AND dbo.Main.Active = 1GROUP BY dbo.Project.ProductLine SET @.TotalSum = (SELECT SUM(dbo.Main.Hours)FROM dbo.Main INNER JOIN dbo.Department ON dbo.Main.DeptNo = dbo.Department.DeptNo INNER JOIN dbo.Project ON dbo.Main.ProjectNo = dbo.Project.ProjectNoWHERE dbo.Main.UserID LIKE'CI%' AND dbo.Project.ControlLocation ='IND' AND DATEPART(yyyy, dbo.Main.DataDate) = @.Year AND dbo.Main.Active = 1) SELECT t.CBDCYearlyTotals AS CBDCYearlyTotals, t.ProductLine AS ProductLine, ROUND((t.CBDCYearlyTotals/@.TotalSum) * 100, 1) AS Percentage FROM #tempTotals tENDGO

I can present the rdl if necessary.

Muchos Gracias

So, in doing deeper searching I found another forum that said temp tables may not be supported and that table variables should be used instead. So, I'm attempting that route, but running into problems with the query when it comes to dividing by @.TotalSum. It returns 0 now and not the number that I had gotten previously.

Just one road block after the other. Ah, the joys of programming.

Anybody got any clues as to why this would happen?

Functionality Like Pivot Chart

There is a view on our server that has fields for project name and then 25
months, each row contains the descriptive name and the forecasted budget in
each month. I need to create a chart showing the total budget for month and
a running total for the year to date. In Excel this is easy with a Pivot
Chart, how do I do this in SSRS 2005?

TIA
DeanDiscover the joy of the 'matrix control' or as I like to think of it:

A design time pivot table, but much less fun to use.

You just set the groups on the columns or rows. Simple. You can even have (right click menu) subtotals.

If you need to have 'other data' displayed, you will need to fiddle about with the Inscope, and create phantom groupings.

see this:
http://www.sqlskills.com/blogs/liz/2006/07/21/ReportingServicesGettingTheMatrixToDisplayTwoSubtotalsForTheSameGroup.aspx

green bar matrix:
http://blogs.msdn.com/chrishays/archive/2004/08/30/GreenBarMatrix.aspx|||Can the matrix control display data as a CHART?|||http://msdn2.microsoft.com/en-us/library/aa964128(SQL.90).aspx

Function to convert a 'date range' to table of starting and ending

I have a table that contains two fields: effectiveFrom, effectiveTo.
The time elapsed between these day could be greater than one year.
I would like to develop a UDF that would convert these dates into a
table of values. For example, if the dates are 7/1/03 and 4/1/06 the
resulting table would look like:
startingDate endingDate
07/01/03 12/31/03
01/01/04 12/31/04
01/01/05 12/31/05
01/01/06 04/01/06
I am confident that I can do this in a stored procedure, but I'm having
difficulty in a table-set UDF.
Suggestions appreciated.
Craig BuchananAdi-
Not exactly what I was looking for, but useful in other situations.
Thanks a lot for your response.
Craig
Adi wrote:
> You can do it this way:
> create function ShowDates (@.StartDate smalldatetime, @.EndDate
> smalldatetime)
> returns @.DatesTable table (DateCol smalldatetime)
> as
> begin
> while @.startDate <= @.EndDate
> BEGIN
> insert into @.DatesTable (DateCol) values (@.StartDate)
> set @.StartDate = dateadd(dd,1, @.StartDate)
> END
> return
> END
> If you are using SQL Server 2005, then you can use recursive CTE to do
> the same thing
>|||Razvan-
Perfect! Thanks a lot.
Craig Buchanan
Razvan Socol wrote:
> Hello, Craig
> You can use this function (after you give it a better name):
> CREATE FUNCTION YourFunctionName(
> @.EffectiveFrom datetime,
> @.EffectiveTo datetime
> )
> RETURNS @.Result TABLE (
> StartingDate datetime NOT NULL PRIMARY KEY,
> EndingDate datetime NOT NULL UNIQUE,
> CHECK (startingDate<=endingDate)
> ) AS BEGIN
> WHILE YEAR(@.EffectiveFrom)<YEAR(@.EffectiveTo) BEGIN
> DECLARE @.EndOfYear datetime
> SET @.EndOfYear=DATEADD(year,DATEDIFF(year,0,
@.EffectiveFrom)+1,0)-1
> INSERT INTO @.Result VALUES (@.EffectiveFrom, @.EndOfYear)
> SET @.EffectiveFrom=@.EndOfYear+1
> END
> IF @.EffectiveFrom<=@.EffectiveTo BEGIN
> INSERT INTO @.Result VALUES (@.EffectiveFrom, @.EffectiveTo)
> END
> RETURN
> END
> Razvan
>|||Uri-
Thanks for the reply. Not exactly what I needed, but I can use this in
another situation.
Thanks,
Craig
Uri Dimant wrote:
> Try this one
> CREATE FUNCTION fn_dates(@.from AS DATETIME, @.to AS DATETIME)
> RETURNS @.Dates TABLE(dt DATETIME NOT NULL PRIMARY KEY)
> AS
> BEGIN
> DECLARE @.rc AS INT
> SET @.rc = 1
> INSERT INTO @.Dates VALUES(@.from)
> WHILE @.from + @.rc * 2 - 1 <= @.to
> BEGIN
> INSERT INTO @.Dates
> SELECT dt + @.rc FROM @.Dates
> SET @.rc = @.rc * 2
> END
> INSERT INTO @.Dates
> SELECT dt + @.rc FROM @.Dates
> WHERE dt + @.rc <= @.to
> RETURN
> END
> GO
> SELECT dt FROM fn_dates('20060101', '20060130')
>
> "Craig Buchanan" <user@.example.net> wrote in message
> news:OAAuwYKMGHA.2992@.tk2msftngp13.phx.gbl...
>

Function sequence error with bcp call from a stored procedure

I have a stored procedure that contains a series of bcp calls to export data
.
The bcp calls are executed by xp_cmdshell and are all the same (other than
the table name of course).
I have SET NOCOUNT ON as the first line in the stored procedure and it is
not returning any data. The bcp calls are in the format of:
bcp "select * from DBName..TableName WHERE UID = 'SomeUniqueNumber' queryout
SomePath\Tablename.txt -m0 -e SomePath\TableName.err -c -T -k'
exec @.RC = master..xp_cmdshell @.Query
On one of the bcp shells I'm receiving the following errors but not on
others. The errors are:
SQLState = S1010, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Function sequence error
NULL
Server: Msg 60003, Level 11, State 1, Procedure ExportData, Line 98
[Microsoft][ODBC SQL Server Driver][SQL Server]Data export operation failed.
Failed to get the call stack!
ODBC: Msg 0, Level 19, State 1
[Microsoft][ODBC SQL Server Driver][SQL Server]SqlDumpExceptionHandler:
Process 64 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQ
L
Server is terminating this process.
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionWrite (send()).
Server: Msg 11, Level 16, State 1, Line 0
[Microsoft][ODBC SQL Server Driver][DBNETLIB]General network error. Check
your network documentation.
@.RETURN_VALUE = N/A
The stored procedure is being called from a .Net class library and is
executing on SQL Server 2000 with following version info:
Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
May 31 2003 16:08:15
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: )
Any ideas would be appreciated. Thanks.AV normally means a bug in sqlserver code. I would suggest you contact PSS
regarding this.
-oj
"jacob4408" <jacob4408@.discussions.microsoft.com> wrote in message
news:6C3D821F-14E3-42BA-AC45-891C24D83451@.microsoft.com...
>I have a stored procedure that contains a series of bcp calls to export
>data.
> The bcp calls are executed by xp_cmdshell and are all the same (other than
> the table name of course).
> I have SET NOCOUNT ON as the first line in the stored procedure and it is
> not returning any data. The bcp calls are in the format of:
> bcp "select * from DBName..TableName WHERE UID = 'SomeUniqueNumber'
> queryout
> SomePath\Tablename.txt -m0 -e SomePath\TableName.err -c -T -k'
> exec @.RC = master..xp_cmdshell @.Query
>
> On one of the bcp shells I'm receiving the following errors but not on
> others. The errors are:
> SQLState = S1010, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]Function sequence error
> NULL
> Server: Msg 60003, Level 11, State 1, Procedure ExportData, Line 98
> [Microsoft][ODBC SQL Server Driver][SQL Server]Data export operation
> failed.
> Failed to get the call stack!
>
> ODBC: Msg 0, Level 19, State 1
> [Microsoft][ODBC SQL Server Driver][SQL Server]SqlDumpExceptionHandler:
> Process 64 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION.
> SQL
> Server is terminating this process.
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionWrite (send()).
> Server: Msg 11, Level 16, State 1, Line 0
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]General network error. Check
> your network documentation.
> @.RETURN_VALUE = N/A
> The stored procedure is being called from a .Net class library and is
> executing on SQL Server 2000 with following version info:
> Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
> May 31 2003 16:08:15
> Copyright (c) 1988-2003 Microsoft Corporation
> Enterprise Edition on Windows NT 5.2 (Build 3790: )
> Any ideas would be appreciated. Thanks.
>|||AV? PSS? Sorry, but I'm not familiar with the acronyms. Could you elaborat
e?
"oj" wrote:

> AV normally means a bug in sqlserver code. I would suggest you contact PSS
> regarding this.
> --
> -oj
>
> "jacob4408" <jacob4408@.discussions.microsoft.com> wrote in message
> news:6C3D821F-14E3-42BA-AC45-891C24D83451@.microsoft.com...
>
>|||Jacob,
Sorry. I mean Access Violation and Product Support Services.
http://support.microsoft.com/oas/de...aspx?gprid=2852
-oj
"jacob4408" <jacob4408@.discussions.microsoft.com> wrote in message
news:3DF8F91A-D0B8-46AE-9946-AF8EC9D87CFF@.microsoft.com...
> AV? PSS? Sorry, but I'm not familiar with the acronyms. Could you
> elaborate?
> "oj" wrote:
>|||Thanks, I'll look into that.
"oj" wrote:

> Jacob,
> Sorry. I mean Access Violation and Product Support Services.
> http://support.microsoft.com/oas/de...aspx?gprid=2852
> --
> -oj
>
> "jacob4408" <jacob4408@.discussions.microsoft.com> wrote in message
> news:3DF8F91A-D0B8-46AE-9946-AF8EC9D87CFF@.microsoft.com...
>
>

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?
> >
>
>

Fulltext Search, Contains, Varbinary, AdvantureWorks

Hello all

I am trying to full text search in the Documnt table in AdvantureWorks DB.

as in the video sample microsoft provided.

I create the unique index on DocumentID column and created the FullTextIndex:

create fulltext index on Production.Document

(

Document

Type column FileExtension

Language 0X0

)

Key index ui_ProductionDocument on MyFullTextCatalog

With change_tracking auto

Now I am trying to run the next search:

select * from Production.Document

where Contains(Document,'?????')

and I get nothing back, what do I do wrong?

I was having a pending restart that fixed the all thing.

Don't know why....

Full-Text Search Query Question - Performance

I have a table with 3M rows that contains a varchar(2000) field with
various keywords. Here is the table structure:

PKColumn
ImageID
FullTextColumn

There is an association table:
ImageID
ContractID

Now, I want to do a query where the ContractID = x and Contains some
word in the FullTextColumn. There is an association table that maps
Images to Contracts - so I can't use the trick of putting the Contract
code in the FullTextColumn.

I'm finding that first the FTS service is performing a search on the
Keyword (which can take a long time if 100K rows are returned) then
joining to the association table for the particular contract.

Is there anyway to make this faster by telling the FTS service, only
search this subset of rows for the keyword based on the contract.

Sorry if this sounds convoluted. Appreciate any help you can suggest.

Thanks!jimdandy@.shaw.ca (Jim Dandy) wrote in message news:<705c8539.0405271024.5ce1d19b@.posting.google.com>...
> I have a table with 3M rows that contains a varchar(2000) field with
> various keywords. Here is the table structure:
> PKColumn
> ImageID
> FullTextColumn
> There is an association table:
> ImageID
> ContractID
> Now, I want to do a query where the ContractID = x and Contains some
> word in the FullTextColumn. There is an association table that maps
> Images to Contracts - so I can't use the trick of putting the Contract
> code in the FullTextColumn.
> I'm finding that first the FTS service is performing a search on the
> Keyword (which can take a long time if 100K rows are returned) then
> joining to the association table for the particular contract.
> Is there anyway to make this faster by telling the FTS service, only
> search this subset of rows for the keyword based on the contract.
> Sorry if this sounds convoluted. Appreciate any help you can suggest.
> Thanks!

You might want to post this in microsoft.public.sqlserver.fulltext to
see if you get a better reply.

Simon

Full-Text Search - How to determine word offset in CONTAINS query.

Does anyone know if it is possible to determine the relative word offset (the Occ) from a simple-term query such as:-

SELECT Comments
FROM Production.ProductReview
WHERE CONTAINS(Comments, ' "mountain biking" ');

So, given the text:-

"Maybe it's just because I'm new to mountain biking, but I had a terrible time getting used to these pedals."

I would like the query would return both the text and the word offset of 8. To me, it seems like this would be quite useful as I want to highlight the found text for the user to see. Obviously I can do a post-SELECT scan of the string to find the values but this would seem unnecessary.

If anyone can give me any pointers I'd be very grateful.

Thanks

Mark,
While the word offset (8 in your example) is not directly available via a SQL FTS query, you can use PATINDEX to achieve the same effect:

The following SQL FTS query on the pubs table pub_info will return rows that match the FTS search word (books) and the near by words from 20 characters before the searched keyword(books) for a total lenght of 100 characters.

SELECT pub_id, SubString(pr_info,PatIndex ('%books%',pr_info)-20,100)
FROM pub_info
WHERE Contains(pr_info, 'books')

Highlighting the word or sometimes referred to as Hit-Highlighting is a very important psychological help to searchers looking for the keyword or phrase "in context" with the words near (before and following) the keyword/phrase. This is a FAQ in the public fulltext newsgroup that doesn't have a clean implemention yet...

Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/

|||John

I suppose there must be a reason why the offset is not available - I just can't think of it at the moment. It would be good to ask the SQL Server 2005 development team for their response as to why it's not available. Probably something to do with inflectional forms and stemming, as well as the more advanced query features available through the FREETEXT predicate.

I agree the hit-highlighting is an important aid to the user - I think I'll have to implement something along the lines that you've suggested, and maybe if Microsoft provide with a mechanism in the future then retro-fit it.

Many thanks.

Regards
Mark|||

Mark,
I've waited until a SQL Server 2005 development team member would reply to this posting, but they are most likely busy getting Yukon ready to ship... In the meantime, I can only speculate why the offsets are not available... Most likely this is due to either disk space considerations (related to performance) or functionality as offsets are normally used to help determine the "nearness" of one token (word or term) to another. The "nearness" in SQL Server 2000 and SQL Server 2005 is hardcoded to approx. 50 words / tokens / terms. Nearness is also affected by noise (stop) words as well as end-of-sentence and end-of-paragraph characters. I know for a fact (direct experiene) that at least one potential SQL Server (2000 and 2005) FTS customer decided to go with a non-Microsoft solution solely based upon the lack of functionality and lack of customization of the NEAR functionality, I'm sad to say... Note, they didn't implement ORACLE or IBM DB2, but stayed with SQL Server as their appliation was written to SQL Server, it was just the FTS functionality that was not sufficient .

As for hit-highlighting (or highlighting of search keywords or phrases), I too agree that this is an important functionality that is missing in SQL Server 2000 and 2005 Full-text Search (SQL FTS) and hopefully this will be implemented in a post-Yukon version of SQL Server. Note, this feature would have to be newly implemented and most likely Microsoft will not be able to retro-fit it back to SQL Server 2000 or SQL Server 2005, I'm sad to say...

In retrospect, both of these issues are well known to the Microsoft SQL Server 2005 FTS development team, but the overall performance of SQL FTS (greatly improved!) had to take priority and in the long term, there is only so much that can be done with a software server product as large as SQL Server 2005 and still keep it stable, reliable, performant and meet the RTM shipping deadline! My hat is off to the SQL FTS Dev team!

Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/

|||

John

We've come to the same conclusion that they have other priorities - and we can't blame them. FTS is so close to what we need/want, but falls at the final hurdle for our particular requirements. We will continue to use SQL Server 2005 for all other data management because it's a superb platform, and we will use it as the basis for engineering our own specific FTS tool.

We will, of course, keep abreast of all new FTS features in future releases with much anticipation.

Many thanks for your feedback.

Regards
Mark

Feedback-Ferret?
www.metriq.co.uk