Convert Access Function to SQL

I'm going crazy trying to convert an Access Function to SQL.
From what I've read, it has to be done as a stored procedure.
I'm trying to take a field that is "minutes.seconds" and convert it to minutes.

This is what I have in Access:

Function ConvertToTime (myAnswer As Variant)
Dim myMinutes
myMinutes-(((((myAnswer * 100)Mod 100/100/0.6)+(CInt(myAnswer-0.4))))
ConvertToTime =(myMinutes)
End Function

When I tried to modify it in SQL:

CREATE PROCEDURE [OWNER].[PROCEDURE NAME] AS ConvertToTime
Function ConvertToTime(myAnswer As Variant)
Dim myMinutes
myMinutes = (((((myAnswer * 100)Mod 100)/100/0.6)+9CInt(myAnswer-0.4))))
ConvertToTime=(myMinutes)
End

I get an error after ConverToTime.Transact-SQL is not VB!

If you are using SQL2000 you can create a user-defined function:

CREATE FUNCTION dbo.ConvertToMinutes (@.minsec DECIMAL(5,2))
RETURNS DECIMAL(5,2)
BEGIN
RETURN ROUND(@.minsec,0,1)+(@.minsec-ROUND(@.minsec,0,1))*10/6
END

GO

SELECT dbo.ConvertToMinutes(100.30)

Result:

---
-100.50

(1 row(s) affected)

--
David Portas
----
Please reply only to the newsgroup
--|||Mich wrote:
> CREATE PROCEDURE [OWNER].[PROCEDURE NAME] AS ConvertToTime
> Function ConvertToTime(myAnswer As Variant)
> Dim myMinutes
> myMinutes = (((((myAnswer * 100)Mod 100)/100/0.6)+9CInt(myAnswer-0.4))))
> ConvertToTime=(myMinutes)
> End
T-SQL uses "Return <value>" for functions, like C or Java, not
"<Function Name> = <value>", like VB (including Access) or Pascal.

Bill

Convert Access 2003 Function

I am converting an Access DB to SQL2000 and opne of the queries uses the
Format$ function. How should the syntax be for SQL?
Access: Format$(TimeTrackerEntry.EntryDate,'yyyymm') AS EntryMonth
Use the CONVERT function with an optional date style argument. See the
following for syntax, samples and date arguments.
http://msdn.microsoft.com/library/de...ca-co_2f3o.asp
HTH
Jerry
"dj5md" <dj5md@.discussions.microsoft.com> wrote in message
news:073E808D-100D-4F1F-9842-4BEB4DF3F112@.microsoft.com...
>I am converting an Access DB to SQL2000 and opne of the queries uses the
> Format$ function. How should the syntax be for SQL?
> Access: Format$(TimeTrackerEntry.EntryDate,'yyyymm') AS EntryMonth
|||Thanks Jerry, I already looked at Cast and Convert but cannot figure out the
correct syntax. It's a bit confusing for me.
"Jerry Spivey" wrote:

> Use the CONVERT function with an optional date style argument. See the
> following for syntax, samples and date arguments.
> http://msdn.microsoft.com/library/de...ca-co_2f3o.asp
> HTH
> Jerry
> "dj5md" <dj5md@.discussions.microsoft.com> wrote in message
> news:073E808D-100D-4F1F-9842-4BEB4DF3F112@.microsoft.com...
>
>
|||Try this:
SELECT LEFT(CONVERT(VARCHAR(25),GETDATE(),112),6)
HTH
Jerry
"dj5md" <dj5md@.discussions.microsoft.com> wrote in message
news:8212D4FC-A530-47DC-9E07-934516904C6D@.microsoft.com...[vbcol=seagreen]
> Thanks Jerry, I already looked at Cast and Convert but cannot figure out
> the
> correct syntax. It's a bit confusing for me.
> "Jerry Spivey" wrote:
|||Thats was it. I changed the GetDate with my DB field and removed the Select
and it worked with no problems. Thanks much Jerry. I also understand the
function better now that I have the correct syntax.
"Jerry Spivey" wrote:

> Try this:
> SELECT LEFT(CONVERT(VARCHAR(25),GETDATE(),112),6)
> HTH
> Jerry
> "dj5md" <dj5md@.discussions.microsoft.com> wrote in message
> news:8212D4FC-A530-47DC-9E07-934516904C6D@.microsoft.com...
>
>

Convert Access 2003 Function

I am converting an Access DB to SQL2000 and opne of the queries uses the
Format$ function. How should the syntax be for SQL?
Access: Format$(TimeTrackerEntry.EntryDate,'yyyymm') AS EntryMonthUse the CONVERT function with an optional date style argument. See the
following for syntax, samples and date arguments.
http://msdn.microsoft.com/library/d...br />
2f3o.asp
HTH
Jerry
"dj5md" <dj5md@.discussions.microsoft.com> wrote in message
news:073E808D-100D-4F1F-9842-4BEB4DF3F112@.microsoft.com...
>I am converting an Access DB to SQL2000 and opne of the queries uses the
> Format$ function. How should the syntax be for SQL?
> Access: Format$(TimeTrackerEntry.EntryDate,'yyyymm') AS EntryMonth|||Thanks Jerry, I already looked at Cast and Convert but cannot figure out the
correct syntax. It's a bit confusing for me.
"Jerry Spivey" wrote:

> Use the CONVERT function with an optional date style argument. See the
> following for syntax, samples and date arguments.
> http://msdn.microsoft.com/library/d... />
o_2f3o.asp
> HTH
> Jerry
> "dj5md" <dj5md@.discussions.microsoft.com> wrote in message
> news:073E808D-100D-4F1F-9842-4BEB4DF3F112@.microsoft.com...
>
>|||Try this:
SELECT LEFT(CONVERT(VARCHAR(25),GETDATE(),112),
6)
HTH
Jerry
"dj5md" <dj5md@.discussions.microsoft.com> wrote in message
news:8212D4FC-A530-47DC-9E07-934516904C6D@.microsoft.com...[vbcol=seagreen]
> Thanks Jerry, I already looked at Cast and Convert but cannot figure out
> the
> correct syntax. It's a bit confusing for me.
> "Jerry Spivey" wrote:
>|||Thats was it. I changed the GetDate with my DB field and removed the Select
and it worked with no problems. Thanks much Jerry. I also understand the
function better now that I have the correct syntax.
"Jerry Spivey" wrote:

> Try this:
> SELECT LEFT(CONVERT(VARCHAR(25),GETDATE(),112),
6)
> HTH
> Jerry
> "dj5md" <dj5md@.discussions.microsoft.com> wrote in message
> news:8212D4FC-A530-47DC-9E07-934516904C6D@.microsoft.com...
>
>

Convert Access 2003 Function

I am converting an Access DB to SQL2000 and opne of the queries uses the
Format$ function. How should the syntax be for SQL?
Access: Format$(TimeTrackerEntry.EntryDate,'yyyymm') AS EntryMonthUse the CONVERT function with an optional date style argument. See the
following for syntax, samples and date arguments.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp
HTH
Jerry
"dj5md" <dj5md@.discussions.microsoft.com> wrote in message
news:073E808D-100D-4F1F-9842-4BEB4DF3F112@.microsoft.com...
>I am converting an Access DB to SQL2000 and opne of the queries uses the
> Format$ function. How should the syntax be for SQL?
> Access: Format$(TimeTrackerEntry.EntryDate,'yyyymm') AS EntryMonth|||Thanks Jerry, I already looked at Cast and Convert but cannot figure out the
correct syntax. It's a bit confusing for me.
"Jerry Spivey" wrote:
> Use the CONVERT function with an optional date style argument. See the
> following for syntax, samples and date arguments.
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp
> HTH
> Jerry
> "dj5md" <dj5md@.discussions.microsoft.com> wrote in message
> news:073E808D-100D-4F1F-9842-4BEB4DF3F112@.microsoft.com...
> >I am converting an Access DB to SQL2000 and opne of the queries uses the
> > Format$ function. How should the syntax be for SQL?
> >
> > Access: Format$(TimeTrackerEntry.EntryDate,'yyyymm') AS EntryMonth
>
>|||Try this:
SELECT LEFT(CONVERT(VARCHAR(25),GETDATE(),112),6)
HTH
Jerry
"dj5md" <dj5md@.discussions.microsoft.com> wrote in message
news:8212D4FC-A530-47DC-9E07-934516904C6D@.microsoft.com...
> Thanks Jerry, I already looked at Cast and Convert but cannot figure out
> the
> correct syntax. It's a bit confusing for me.
> "Jerry Spivey" wrote:
>> Use the CONVERT function with an optional date style argument. See the
>> following for syntax, samples and date arguments.
>> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp
>> HTH
>> Jerry
>> "dj5md" <dj5md@.discussions.microsoft.com> wrote in message
>> news:073E808D-100D-4F1F-9842-4BEB4DF3F112@.microsoft.com...
>> >I am converting an Access DB to SQL2000 and opne of the queries uses the
>> > Format$ function. How should the syntax be for SQL?
>> >
>> > Access: Format$(TimeTrackerEntry.EntryDate,'yyyymm') AS EntryMonth
>>|||Thats was it. I changed the GetDate with my DB field and removed the Select
and it worked with no problems. Thanks much Jerry. I also understand the
function better now that I have the correct syntax.
"Jerry Spivey" wrote:
> Try this:
> SELECT LEFT(CONVERT(VARCHAR(25),GETDATE(),112),6)
> HTH
> Jerry
> "dj5md" <dj5md@.discussions.microsoft.com> wrote in message
> news:8212D4FC-A530-47DC-9E07-934516904C6D@.microsoft.com...
> > Thanks Jerry, I already looked at Cast and Convert but cannot figure out
> > the
> > correct syntax. It's a bit confusing for me.
> >
> > "Jerry Spivey" wrote:
> >
> >> Use the CONVERT function with an optional date style argument. See the
> >> following for syntax, samples and date arguments.
> >>
> >> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp
> >>
> >> HTH
> >>
> >> Jerry
> >> "dj5md" <dj5md@.discussions.microsoft.com> wrote in message
> >> news:073E808D-100D-4F1F-9842-4BEB4DF3F112@.microsoft.com...
> >> >I am converting an Access DB to SQL2000 and opne of the queries uses the
> >> > Format$ function. How should the syntax be for SQL?
> >> >
> >> > Access: Format$(TimeTrackerEntry.EntryDate,'yyyymm') AS EntryMonth
> >>
> >>
> >>
>
>

Convert a date function in SQL Server

I have the date in the SQL server column as 2002-06-16 00:00:00.000 and I will be needing this to convert to 2003-06-16 00:00:00.000. Just the year from 2002 to 2003. I used an update statement like
UPDATE dbo.tableA
SET Datepart (yyyy,TranDt) = Datepart (2003,TranDt)

This did not work - is there anything I need to change in the update statement to make this query do what I want to -

Thanks!run this for an example
select DATEADD(yyyy, 1, getdate())

update x
set x.timefield = DATEADD(yyyy, 1, x.timefield)
from table x|||mkkmg,
Thanks a million it worked perfect!

convert a Boolean to either String or Text?

Hi,

Does any one know how to convert a Boolean to either String or Text?
I came across the ToText ( ) function but I can't seem to get it to work.
According to the Crystal Reports For Visual Studio .NET ( Wrox book )
the ToText ( ) the function should work to convert Booleans... but I have not IDEA HOW , since they don't provide any
example! Can any one please shed some light? or maybe provide a better solution?

thank you in advance.

C.What database are you talking about?|||Originally posted by Brett Kaiser
What database are you talking about?

SQL Server. Did I post this question on the wrong Forum?|||There is no ToText function in SQL server. That is a Crystal function. SQL Server does not even technically use "boolean" values. It uses the BIT value instead. It's possible that Crystal is misinterpreting the values it is receives from SQL server, because it is well-document that Crystal reports sucks big-time.

blindman|||Originally posted by blindman
There is no ToText function in SQL server. That is a Crystal function. SQL Server does not even technically use "boolean" values. It uses the BIT value instead. It's possible that Crystal is misinterpreting the values it is receives from SQL server, because it is well-document that Crystal reports sucks big-time.

blindman

I agree on the SUCK big time if we are talking about CR.NET. Version 8.5 seems to be pretty good to me. In any case, I guess I should have asked the question in terms of SQL Server since I want to create a VW and put it on the CR.NET as a DataSet. I have just realized that the way to convert a Boolean ( or Bit, thanks for the clarification) is as follows:

select tbl.fieldname = (case tbl_name when boolean_value then 'yourString' end) from tblName

Thank you,

P.|||The only technical equivalent to what I think you're asking is:

CASE
WHEN [MyColumn] = 0 THEN 'NO'
WHEN [MyColumn] = 1 THEN 'YES'
ELSE 'Null'
END CASE

That is assuming of course that the column is defined as:

MyColumn BIT NULL

The above CASE statement accepts that the field might be null. If that's not the case (the column allows no nulls), then you can just use two lines (omit the 'ELSE NULL').

Other than that, blindman is right. There isn't a data type called 'boolean' in SQL Server and ToText is definitely not a standard SQL function.

Good Luck,

hmscott|||Sorry for the duplicate post; you beat me by a couple of minutes. tHet's kuz i suk at tping.

hmscott|||Originally posted by hmscott
Sorry for the duplicate post; you beat me by a couple of minutes. tHet's kuz i suk at tping.

hmscott

Your query also worked. Thank you hmscott. I'll see you arround.

Conversion of procedure making crosstab to function

Hello Everybody,
I have the following problem. I found procedure alolowing me to create
dynamic crosstab and it works fine, but I cannot save the results as a
table. Is there any way to do this? Maybe someone is in possesion of
function which works as below procedure? Or someone is able to change
this procedure to function which returns table which could be used with
command CREATE TABLE?
Procedure looks as follows:
CREATE PROCEDURE sp_TRANSFORM
/*
Purpose: Creates a Pivot(tm) table for the specified table,
view or select statement
Author: svenh@.itrain.de
Version: 1.1
History: march 2000 version 1.0
july 2002 version 1.1
Input parameters:
@.Aggregate_Function (optional)
the aggregate function to use for the pivot
default function is SUM
@.Aggregate_Column
name of column for aggregate
@.TableOrView_Name
name of table or view to use
if name contains spaces or other special
characters [] should be used
Can also be a valid SELECT statement
@.Select_Column
Column for first column in result table
for this column row values are displayed
@.Pivot_Column
Column that is transformed into columns
for this column column values are displayed
@.DEBUG
Set this flag to 1 to get debug-information
Example usage:
Table given aTable
content: Product Salesman Sales
P1 Sa 12
P2 Sb 10
P2 Sb 3
P3 Sa 12
P1 Sc 8
P3 Sa 1
P2 Sa NULL
CALL
EXEC sp_Transform 'SUM', 'Sales', 'aTable', 'Product', 'Salesman'
or EXEC sp_Transform @.Aggregate_Column='Sales',
@.TableOrViewName='aTable',
@.Select_Column='Product',
@.Pivot_Column='Salesman'
Result:
Product| Sa | Sb | Sc | Total
--+--+--+--+--
P1 | 12,00 | 0,00 | 8,00 | 20,00
P2 | 0,00 | 13,00 | 0,00 | 13,00
P3 | 13,00 | 0,00 | 0,00 | 13,00
--+--+--+--+--
Total | 25,00 | 13,00 | 8,00 | 46,00
*/
@.Aggregate_Function nvarchar(30) = 'SUM',
@.Aggregate_Column nvarchar(255),
@.TableOrView_Name nvarchar(255),
@.Select_Column nvarchar(255),
@.Pivot_Column nvarchar(255),
@.DEBUG bit = 0
AS
SET NOCOUNT ON
DECLARE @.TransformPart nvarchar(4000)
DECLARE @.SQLColRetrieval nvarchar(4000)
DECLARE @.SQLSelectIntro nvarchar(4000)
DECLARE @.SQLSelectFinal nvarchar(4000)
IF @.Aggregate_Function NOT IN ('SUM', 'COUNT', 'MAX', 'MIN', 'AVG',
'STDEV', 'VAR', 'VARP', 'STDEVP')
BEGIN RAISERROR ('Invalid aggregate function: %s', 10, 1,
@.Aggregate_Function) END
ELSE
BEGIN
SELECT @.SQLSelectIntro = 'SELECT CASE WHEN (GROUPING(' +
QUOTENAME(@.Select_Column) +
') = 1) THEN ''Total'' ELSE ' +
'CAST( + ' +
QUOTENAME(@.Select_Column) +
' AS NVARCHAR(255)) END As ' +
QUOTENAME(@.Select_Column) +
', '
IF @.DEBUG = 1 PRINT @.sqlselectintro
SET @.SQLColRetrieval =
N'SELECT @.TransformPart = CASE WHEN @.TransformPart IS NULL THEN ' +
N'''' + @.Aggregate_Function + N'(CASE CAST(' +
QUOTENAME(CAST(@.Pivot_Column AS VARCHAR(255))) +
N' AS VARCHAR(255)) WHEN ''' + CAST(' +
QUOTENAME(@.Pivot_Column) +
N' AS NVarchar(255)) + ''' THEN ' + @.Aggregate_Column
+
N' ELSE 0 END) AS '' + QUOTENAME(' +
QUOTENAME(CAST(@.Pivot_Column AS VARCHAR(255))) +
N') ELSE @.TransformPart + '', ' + @.Aggregate_Function +
N' (CASE CAST(' + QUOTENAME(@.Pivot_Column) +
N' AS nVARCHAR(255)) WHEN ''' + CAST(' +
QUOTENAME(CAST(@.Pivot_Column As VarChar(255))) +
N' AS nVARCHAR(255)) + ''' THEN ' +
@.Aggregate_Column +
N' ELSE 0 END) AS '' + QUOTENAME(' +
QUOTENAME(CAST(@.Pivot_Column AS VARCHAR(255))) +
N') END FROM (SELECT DISTINCT ' +
QUOTENAME(CAST(@.Pivot_Column AS VARCHAR(255))) +
N' FROM ' + @.TableOrView_Name + ') SelInner'
IF @.DEBUG = 1 PRINT @.SQLColRetrieval
EXEC sp_executesql @.SQLColRetrieval,
N'@.TransformPart nvarchar(4000) OUTPUT',
@.TransformPart OUTPUT
IF @.DEBUG = 1 PRINT @.TransformPart
SET @.SQLSelectFinal =
N', ' + @.Aggregate_Function + N'(' +
CAST(@.Aggregate_Column As Varchar(255)) +
N') As Total FROM ' + @.TableOrView_Name + N'
GROUP BY ' +
@.Select_Column + N' WITH CUBE'
IF @.DEBUG = 1 PRINT @.SQLSelectFinal
EXEC (@.SQLSelectIntro + @.TransformPart + @.SQLSelectFinal)
END
GO
Thank you very much for any help,
Rafal
*** Sent via Developersdex http://www.examnotes.net ***You can try something like this...
INSERT INTO TableName
EXEC proc_name
For example
CREATE TABLE #HelpDB(name VARCHAR(255), db_size VARCHAR(255), owner
VARCHAR(255), dbid INT, created VARCHAR(255), status VARCHAR(255),
compatibility_level VARCHAR(255))
INSERT INTO #HelpDB
EXEC sp_helpdb
SELECT *
FROM #HelpDB
DROP TABLE #HelpDB
"Rafal Ba" <ash@.robertjanowski.pl> wrote in message
news:ONdDCvbmFHA.2904@.tk2msftngp13.phx.gbl...
> Hello Everybody,
> I have the following problem. I found procedure alolowing me to create
> dynamic crosstab and it works fine, but I cannot save the results as a
> table. Is there any way to do this? Maybe someone is in possesion of
> function which works as below procedure? Or someone is able to change
> this procedure to function which returns table which could be used with
> command CREATE TABLE?
> Procedure looks as follows:
> CREATE PROCEDURE sp_TRANSFORM
> /*
> Purpose: Creates a Pivot(tm) table for the specified table,
> view or select statement
> Author: svenh@.itrain.de
> Version: 1.1
> History: march 2000 version 1.0
> july 2002 version 1.1
> Input parameters:
> @.Aggregate_Function (optional)
> the aggregate function to use for the pivot
> default function is SUM
> @.Aggregate_Column
> name of column for aggregate
> @.TableOrView_Name
> name of table or view to use
> if name contains spaces or other special
> characters [] should be used
> Can also be a valid SELECT statement
> @.Select_Column
> Column for first column in result table
> for this column row values are displayed
> @.Pivot_Column
> Column that is transformed into columns
> for this column column values are displayed
> @.DEBUG
> Set this flag to 1 to get debug-information
> Example usage:
> Table given aTable
> content: Product Salesman Sales
> P1 Sa 12
> P2 Sb 10
> P2 Sb 3
> P3 Sa 12
> P1 Sc 8
> P3 Sa 1
> P2 Sa NULL
> CALL
> EXEC sp_Transform 'SUM', 'Sales', 'aTable', 'Product', 'Salesman'
> or EXEC sp_Transform @.Aggregate_Column='Sales',
> @.TableOrViewName='aTable',
> @.Select_Column='Product',
> @.Pivot_Column='Salesman'
> Result:
> Product| Sa | Sb | Sc | Total
> --+--+--+--+--
> P1 | 12,00 | 0,00 | 8,00 | 20,00
> P2 | 0,00 | 13,00 | 0,00 | 13,00
> P3 | 13,00 | 0,00 | 0,00 | 13,00
> --+--+--+--+--
> Total | 25,00 | 13,00 | 8,00 | 46,00
>
> */
> @.Aggregate_Function nvarchar(30) = 'SUM',
> @.Aggregate_Column nvarchar(255),
> @.TableOrView_Name nvarchar(255),
> @.Select_Column nvarchar(255),
> @.Pivot_Column nvarchar(255),
> @.DEBUG bit = 0
> AS
> SET NOCOUNT ON
> DECLARE @.TransformPart nvarchar(4000)
> DECLARE @.SQLColRetrieval nvarchar(4000)
> DECLARE @.SQLSelectIntro nvarchar(4000)
> DECLARE @.SQLSelectFinal nvarchar(4000)
> IF @.Aggregate_Function NOT IN ('SUM', 'COUNT', 'MAX', 'MIN', 'AVG',
> 'STDEV', 'VAR', 'VARP', 'STDEVP')
> BEGIN RAISERROR ('Invalid aggregate function: %s', 10, 1,
> @.Aggregate_Function) END
> ELSE
> BEGIN
> SELECT @.SQLSelectIntro = 'SELECT CASE WHEN (GROUPING(' +
> QUOTENAME(@.Select_Column) +
> ') = 1) THEN ''Total'' ELSE ' +
> 'CAST( + ' +
> QUOTENAME(@.Select_Column) +
> ' AS NVARCHAR(255)) END As ' +
> QUOTENAME(@.Select_Column) +
> ', '
> IF @.DEBUG = 1 PRINT @.sqlselectintro
> SET @.SQLColRetrieval =
> N'SELECT @.TransformPart = CASE WHEN @.TransformPart IS NULL THEN ' +
> N'''' + @.Aggregate_Function + N'(CASE CAST(' +
> QUOTENAME(CAST(@.Pivot_Column AS VARCHAR(255))) +
> N' AS VARCHAR(255)) WHEN ''' + CAST(' +
> QUOTENAME(@.Pivot_Column) +
> N' AS NVarchar(255)) + ''' THEN ' + @.Aggregate_Column
> +
> N' ELSE 0 END) AS '' + QUOTENAME(' +
> QUOTENAME(CAST(@.Pivot_Column AS VARCHAR(255))) +
> N') ELSE @.TransformPart + '', ' + @.Aggregate_Function +
> N' (CASE CAST(' + QUOTENAME(@.Pivot_Column) +
> N' AS nVARCHAR(255)) WHEN ''' + CAST(' +
> QUOTENAME(CAST(@.Pivot_Column As VarChar(255))) +
> N' AS nVARCHAR(255)) + ''' THEN ' +
> @.Aggregate_Column +
> N' ELSE 0 END) AS '' + QUOTENAME(' +
> QUOTENAME(CAST(@.Pivot_Column AS VARCHAR(255))) +
> N') END FROM (SELECT DISTINCT ' +
> QUOTENAME(CAST(@.Pivot_Column AS VARCHAR(255))) +
> N' FROM ' + @.TableOrView_Name + ') SelInner'
> IF @.DEBUG = 1 PRINT @.SQLColRetrieval
> EXEC sp_executesql @.SQLColRetrieval,
> N'@.TransformPart nvarchar(4000) OUTPUT',
> @.TransformPart OUTPUT
> IF @.DEBUG = 1 PRINT @.TransformPart
> SET @.SQLSelectFinal =
> N', ' + @.Aggregate_Function + N'(' +
> CAST(@.Aggregate_Column As Varchar(255)) +
> N') As Total FROM ' + @.TableOrView_Name + N'
> GROUP BY ' +
> @.Select_Column + N' WITH CUBE'
> IF @.DEBUG = 1 PRINT @.SQLSelectFinal
> EXEC (@.SQLSelectIntro + @.TransformPart + @.SQLSelectFinal)
> END
> GO
>
> Thank you very much for any help,
> Rafal
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Unfortunately, I can't make new table because number of her columns is
not constant in time. Probably, there is in all crosstables.
I have solved this problem as follows:
1) Create View from procedure using OPENROWSET
CREATE VIEW MyView AS
SELECT *
FROM OPENROWSET('SQLOLEDB','seattle1';'manage
r';'MyPass',
'EXEC MyProcedure')
2) Create table from view
SELECT * INTO NewTable FROM MyView
Maybe somebody has other idea?
*** Sent via Developersdex http://www.examnotes.net ***

containstable, top_n_rank, and additional where clause combination causes unexpected resul

Hello,
Something strange happens if I try and do the following:
If I use the containstable function, with top_n_rank, along with an
extra "and where" clause, then the top_n_rank does not seem to return
the correct number of rows.
Here is an example to explain my point:
select *, tempidentity = IDENTITY (INT) into #tempt
from Store_BasicSearchableShelves,
containstable(Store_BasicSearchableShelves, ShelfName, @.sSearchText,
@.nextitemsrecpointer) tblSearchResults
where [key] = Store_BasicSearchableShelves.ShelfId
and ParentAisleId = @.PID
order by rank desc
If @.nextitemsrecpointer is equal to 20, then the stored proc only
returns 14 rows. If I remove the additional "and ParentAisleId =
@.PID" where clause, then the stored proc returns 20 rows, which is
correct. If I begin to edit the value of @.nextitemsrecpointer for
experimentational purposes to a higher value such as 30 or 40, then
the stored proc returns more rows - 18 and 25 respectively.
The additional "and ParentAisleId = @.PID" seems to be affecting the
way that top_n_rank is behaving.
Can anyone please provide me with a work around for this?
Thank you,
Regards, dnw.
Your problem is that when you limit your result set that is returned from
MSSearch further rows are removed by the "and ParentAisleId = @.PID" clause.
The approaches to this problem are 1) knowing in advance the number of rows
which are returned by MSSearch for this query and entering this value for
@.nextitemrecpointer, 2) partitioning your table into multiple partitioned
tables one for each PartentAisleID value so your query would end up looking
something like this:
if @.ParentAisleID=1
begin
select *, tempidentity = IDENTITY (INT) into #tempt
from Store_BasicSearchableShelves_1,
containstable(Store_BasicSearchableShelves, ShelfName, @.sSearchText,
@.nextitemsrecpointer) tblSearchResults
where [key] = Store_BasicSearchableShelves.ShelfId
order by rank desc
end
if @.ParentAisleID=2
begin
select *, tempidentity = IDENTITY (INT) into #tempt
from Store_BasicSearchableShelves_2,
containstable(Store_BasicSearchableShelves, ShelfName, @.sSearchText,
@.nextitemsrecpointer) tblSearchResults
where [key] = Store_BasicSearchableShelves.ShelfId
order by rank desc
end
3) picking a larger number which will guarantee a larger number of hits that
when filtered by @.ParentAisleID will yield at least @.nextitemsrecpointer
hits. You have to be careful here as you don't want to pick too large a
number to guarantee hits.
In large search applications partitioning in frequently used and it does
work very well. They will often have a seperate catalog for each of the
partitioned tables and you will then get a seperate threads for each catalog
which will improve your overall querying and indexing.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Dot net work" <dotnw@.hotmail.com> wrote in message
news:77b8c5a9.0410280048.698a85c7@.posting.google.c om...
> Hello,
> Something strange happens if I try and do the following:
> If I use the containstable function, with top_n_rank, along with an
> extra "and where" clause, then the top_n_rank does not seem to return
> the correct number of rows.
> Here is an example to explain my point:
> select *, tempidentity = IDENTITY (INT) into #tempt
> from Store_BasicSearchableShelves,
> containstable(Store_BasicSearchableShelves, ShelfName, @.sSearchText,
> @.nextitemsrecpointer) tblSearchResults
> where [key] = Store_BasicSearchableShelves.ShelfId
> and ParentAisleId = @.PID
> order by rank desc
>
> If @.nextitemsrecpointer is equal to 20, then the stored proc only
> returns 14 rows. If I remove the additional "and ParentAisleId =
> @.PID" where clause, then the stored proc returns 20 rows, which is
> correct. If I begin to edit the value of @.nextitemsrecpointer for
> experimentational purposes to a higher value such as 30 or 40, then
> the stored proc returns more rows - 18 and 25 respectively.
> The additional "and ParentAisleId = @.PID" seems to be affecting the
> way that top_n_rank is behaving.
> Can anyone please provide me with a work around for this?
> Thank you,
> Regards, dnw.
|||DNW,
Could you post the SQL Server and OS platform information from -- SELECT
@.@.version -- as well as a row count from your table
Store_BasicSearchableShelves? As all of this information is important in
first understanding your environment before making recommendations as well
as understanding the existing RANK values that are returned from your query.
The simple answer to your as why you query is not returning the expected
number of rows when using Top_N_Rank and with an additional WHERE clause is
that all of the WHERE clause parameters are applied AFTER the MSSearch
service returns the Top_N_Rank (not Top_N_Row... but "top_n_by_RANK").
Additionally, and depending upon the number of rows in your table, the
actual number or "top" values for RANK may be not what you expect as in
order to calculate rank, a statically large number of rows need to be
present.
You should also review the following KB article on the use and cautions of
using Top_N_Rank: 240833 (Q240833) "FIX: Full-Text Search Performance
Improved via Support for TOP" at
http://support.microsoft.com//defaul...b;EN-US;240833
Regards,
John
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:#4HNUAOvEHA.2540@.TK2MSFTNGP09.phx.gbl...
> Your problem is that when you limit your result set that is returned from
> MSSearch further rows are removed by the "and ParentAisleId = @.PID"
clause.
> The approaches to this problem are 1) knowing in advance the number of
rows
> which are returned by MSSearch for this query and entering this value for
> @.nextitemrecpointer, 2) partitioning your table into multiple partitioned
> tables one for each PartentAisleID value so your query would end up
looking
> something like this:
> if @.ParentAisleID=1
> begin
> select *, tempidentity = IDENTITY (INT) into #tempt
> from Store_BasicSearchableShelves_1,
> containstable(Store_BasicSearchableShelves, ShelfName, @.sSearchText,
> @.nextitemsrecpointer) tblSearchResults
> where [key] = Store_BasicSearchableShelves.ShelfId
> order by rank desc
> end
> if @.ParentAisleID=2
> begin
> select *, tempidentity = IDENTITY (INT) into #tempt
> from Store_BasicSearchableShelves_2,
> containstable(Store_BasicSearchableShelves, ShelfName, @.sSearchText,
> @.nextitemsrecpointer) tblSearchResults
> where [key] = Store_BasicSearchableShelves.ShelfId
> order by rank desc
> end
> 3) picking a larger number which will guarantee a larger number of hits
that
> when filtered by @.ParentAisleID will yield at least @.nextitemsrecpointer
> hits. You have to be careful here as you don't want to pick too large a
> number to guarantee hits.
> In large search applications partitioning in frequently used and it does
> work very well. They will often have a seperate catalog for each of the
> partitioned tables and you will then get a seperate threads for each
catalog
> which will improve your overall querying and indexing.
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Dot net work" <dotnw@.hotmail.com> wrote in message
> news:77b8c5a9.0410280048.698a85c7@.posting.google.c om...
>
|||Thanks a lot for your advice.
-dnw.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:<#4HNUAOvEHA.2540@.TK2MSFTNGP09.phx.gbl>...[vbcol=seagreen]
> Your problem is that when you limit your result set that is returned from
> MSSearch further rows are removed by the "and ParentAisleId = @.PID" clause.
> The approaches to this problem are 1) knowing in advance the number of rows
> which are returned by MSSearch for this query and entering this value for
> @.nextitemrecpointer, 2) partitioning your table into multiple partitioned
> tables one for each PartentAisleID value so your query would end up looking
> something like this:
> if @.ParentAisleID=1
> begin
> select *, tempidentity = IDENTITY (INT) into #tempt
> from Store_BasicSearchableShelves_1,
> containstable(Store_BasicSearchableShelves, ShelfName, @.sSearchText,
> @.nextitemsrecpointer) tblSearchResults
> where [key] = Store_BasicSearchableShelves.ShelfId
> order by rank desc
> end
> if @.ParentAisleID=2
> begin
> select *, tempidentity = IDENTITY (INT) into #tempt
> from Store_BasicSearchableShelves_2,
> containstable(Store_BasicSearchableShelves, ShelfName, @.sSearchText,
> @.nextitemsrecpointer) tblSearchResults
> where [key] = Store_BasicSearchableShelves.ShelfId
> order by rank desc
> end
> 3) picking a larger number which will guarantee a larger number of hits that
> when filtered by @.ParentAisleID will yield at least @.nextitemsrecpointer
> hits. You have to be careful here as you don't want to pick too large a
> number to guarantee hits.
> In large search applications partitioning in frequently used and it does
> work very well. They will often have a seperate catalog for each of the
> partitioned tables and you will then get a seperate threads for each catalog
> which will improve your overall querying and indexing.
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Dot net work" <dotnw@.hotmail.com> wrote in message
> news:77b8c5a9.0410280048.698a85c7@.posting.google.c om...
|||Hi John,
[vbcol=seagreen]
SELECT
@.@.version
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002
14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Developer
Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
[vbcol=seagreen]
At the moment, only 39.
[vbcol=seagreen]
expected
number of rows when using Top_N_Rank and with an additional WHERE
clause is
that all of the WHERE clause parameters are applied AFTER the MSSearch
service returns the Top_N_Rank (not Top_N_Row... but "top_n_by_RANK").
As I am a newbie, please can you explain the difference between those
3 things please - Top_N_Rank, Top_N_Row and top_n_by_RANK. Thanks.
Thank you,
Regards, dnw.
"John Kane" <jt-kane@.comcast.net> wrote in message news:<#iLXshRvEHA.3840@.TK2MSFTNGP12.phx.gbl>...[vbcol=seagreen]
> DNW,
> Could you post the SQL Server and OS platform information from -- SELECT
> @.@.version -- as well as a row count from your table
> Store_BasicSearchableShelves? As all of this information is important in
> first understanding your environment before making recommendations as well
> as understanding the existing RANK values that are returned from your query.
> The simple answer to your as why you query is not returning the expected
> number of rows when using Top_N_Rank and with an additional WHERE clause is
> that all of the WHERE clause parameters are applied AFTER the MSSearch
> service returns the Top_N_Rank (not Top_N_Row... but "top_n_by_RANK").
> Additionally, and depending upon the number of rows in your table, the
> actual number or "top" values for RANK may be not what you expect as in
> order to calculate rank, a statically large number of rows need to be
> present.
> You should also review the following KB article on the use and cautions of
> using Top_N_Rank: 240833 (Q240833) "FIX: Full-Text Search Performance
> Improved via Support for TOP" at
> http://support.microsoft.com//defaul...b;EN-US;240833
> Regards,
> John
>
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:#4HNUAOvEHA.2540@.TK2MSFTNGP09.phx.gbl...
> clause.
> rows
> looking
> that
> catalog
|||Thanks, DNW,
While the version (SQL & OS platform) are less important for your questions,
overall the OS platform is most important for understanding expected FTS
query results when searching on specific words &/or punctuation characters
due to OS-specific wordbreaker issues, see
http://groups.google.com/groups?q=langwrbk+infosoft for details.
However, in this case the row count is the most important factor, especially
when used with Top_N_Rank. I'd recommend that you review SQL Server 2000 BOL
title "Full-Text Search Recommendations" and the next to last paragraph on
RANK for a better understanding of how RANK is calculated in SQL Sever 2000.
Rank needs a "statistically significant" number of rows (and therefore
number of unique non-noise words) in order to be useful and 39 rows is not a
"statistically significant" number of rows. While it may depend upon the
number of unique non-noise words, the number of rows is important as well,
and at least 10,000+ rows are generally the recommended number of rows to
start using RANK and you won't need Top_N_Rank for performance reasons
until at least 1 million rows.
As for "Top_N_Rank, Top_N_Row and top_n_by_RANK", there is only Top_N_Rank,
the other two were only metaphors that I used in my explanation as while
Top_N_Rank does limit the number of rows returned it is in fact a limit for
N (some number) of rows returned by RANK and not explicitly a row limiter as
is Top. Sorry, for the confusion, but with only 39 rows, I'd recommend that
you do not use Top_N_Rank as it was added as a fix in SQL Server 7.0 (and
included in SQL Server 2000) to improve the FTS query performance when used
against very large (1 to 2+ million) row tables that can generate large FT
Catalogs. See KB article 240833 (Q240833) for more info.
Again, thanks for providing the @.@.version as well as the row count info!
John
"Dot net work" <dotnw@.hotmail.com> wrote in message
news:77b8c5a9.0410290622.8147ced@.posting.google.co m...
> Hi John,
> SELECT
> @.@.version
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002
> 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Developer
> Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
>
> At the moment, only 39.
> expected
> number of rows when using Top_N_Rank and with an additional WHERE
> clause is
> that all of the WHERE clause parameters are applied AFTER the MSSearch
> service returns the Top_N_Rank (not Top_N_Row... but "top_n_by_RANK").
> As I am a newbie, please can you explain the difference between those
> 3 things please - Top_N_Rank, Top_N_Row and top_n_by_RANK. Thanks.
> Thank you,
> Regards, dnw.
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
news:<#iLXshRvEHA.3840@.TK2MSFTNGP12.phx.gbl>...[vbcol=seagreen]
well[vbcol=seagreen]
query.[vbcol=seagreen]
is[vbcol=seagreen]
of[vbcol=seagreen]
from[vbcol=seagreen]
for[vbcol=seagreen]
partitioned[vbcol=seagreen]
hits[vbcol=seagreen]
@.nextitemsrecpointer[vbcol=seagreen]
a[vbcol=seagreen]
does[vbcol=seagreen]
the[vbcol=seagreen]
return[vbcol=seagreen]
|||That info was really interesting! Thanks a lot.
-dnw.
"John Kane" <jt-kane@.comcast.net> wrote in message news:<emMzmVdvEHA.2200@.TK2MSFTNGP11.phx.gbl>...[vbcol=seagreen]
> Thanks, DNW,
> While the version (SQL & OS platform) are less important for your questions,
> overall the OS platform is most important for understanding expected FTS
> query results when searching on specific words &/or punctuation characters
> due to OS-specific wordbreaker issues, see
> http://groups.google.com/groups?q=langwrbk+infosoft for details.
> However, in this case the row count is the most important factor, especially
> when used with Top_N_Rank. I'd recommend that you review SQL Server 2000 BOL
> title "Full-Text Search Recommendations" and the next to last paragraph on
> RANK for a better understanding of how RANK is calculated in SQL Sever 2000.
> Rank needs a "statistically significant" number of rows (and therefore
> number of unique non-noise words) in order to be useful and 39 rows is not a
> "statistically significant" number of rows. While it may depend upon the
> number of unique non-noise words, the number of rows is important as well,
> and at least 10,000+ rows are generally the recommended number of rows to
> start using RANK and you won't need Top_N_Rank for performance reasons
> until at least 1 million rows.
> As for "Top_N_Rank, Top_N_Row and top_n_by_RANK", there is only Top_N_Rank,
> the other two were only metaphors that I used in my explanation as while
> Top_N_Rank does limit the number of rows returned it is in fact a limit for
> N (some number) of rows returned by RANK and not explicitly a row limiter as
> is Top. Sorry, for the confusion, but with only 39 rows, I'd recommend that
> you do not use Top_N_Rank as it was added as a fix in SQL Server 7.0 (and
> included in SQL Server 2000) to improve the FTS query performance when used
> against very large (1 to 2+ million) row tables that can generate large FT
> Catalogs. See KB article 240833 (Q240833) for more info.
>
> Again, thanks for providing the @.@.version as well as the row count info!
> John
>
> "Dot net work" <dotnw@.hotmail.com> wrote in message
> news:77b8c5a9.0410290622.8147ced@.posting.google.co m...
> news:<#iLXshRvEHA.3840@.TK2MSFTNGP12.phx.gbl>...
> well
> query.
> is
> of
> from
> clause.
> rows
> for
> partitioned
> looking
> hits
> that
> @.nextitemsrecpointer
> a
> does
> the
> catalog
> return

Containstable Filter Input

Hi
We are using the CONTAINSTABLE function in a query, the search condition of
the query is derived from a free text for the user to enter whatever they
please.
I am attempting to replace or filter out text that has been input to resolve
potential errors.
We are replacing double and single spaces with " AND "
We are replacing comma's and apostrophes with ""
But is there a more effective way of doing this?
Thanks
BSorry this has been answered in another of my posts regarding a slightly
different problem, answer:
B
Quote
There have been lots of posts in microsoft.public.sqlserver.fulltext with
solutions to this, many using regular expressions to quickly create clauses.
I myself use a lump of code I wrote about 10 years ago which deals with this
and parentheses and quoted phrases, but it's messy and I'd rather clean it
up before posting.
A search on google groups for fulltext parsing should pull up some useful
info, such as
http://groups.google.co.uk/group/mi...fulltext&hl=en
Dan
Dan
"Ben" <Ben@.NoSpam.com> wrote in message
news:eL3IrikUFHA.2136@.TK2MSFTNGP10.phx.gbl...
> Hi
> We are using the CONTAINSTABLE function in a query, the search condition
> of the query is derived from a free text for the user to enter whatever
> they please.
> I am attempting to replace or filter out text that has been input to
> resolve potential errors.
> We are replacing double and single spaces with " AND "
> We are replacing comma's and apostrophes with ""
> But is there a more effective way of doing this?
> Thanks
> B
>

CONTAINSTABLE doesn't return expected number of rows

Hello,
I've discovered a strange behaviour of the CONTAINSTABLE function.
When I use the TOP statement to return a number of rows, less rows are
returned then when I don't use this statement (while total affected
rows are > 250)
for example:
SELECT tblKeys.[KEY], tblKeys.RANK
FROM CONTAINSTABLE(sb_product_import, *, 'wandbidet', 250) AS
tblKeys
returns 49 rows.
When I execute the same query, but without the TOP statement:
SELECT tblKeys.[KEY], tblKeys.RANK
FROM CONTAINSTABLE(sb_product_import, *, 'wandbidet') AS tblKeys
the query returns 317 rows.
In my opinion the first query has to return 250 rows, because the
total number of affected rows is 317.
Does anyone know this problem, or is there something I do wrong?
best regards,
Jens
Its been a while since I played with it, but that is for ranking. Since Fuzzy
Logic is used in executing the Contains against a FTS, it gives rank to each
result set. And when you specifiy the value Top n (Top_n_by_rank), it's just
doing a filter on that field.
Try using the Top n in the select statement as in Select Top 10 * From ...
Thanks!
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/
"Jens" wrote:

> Hello,
> I've discovered a strange behaviour of the CONTAINSTABLE function.
> When I use the TOP statement to return a number of rows, less rows are
> returned then when I don't use this statement (while total affected
> rows are > 250)
> for example:
> SELECT tblKeys.[KEY], tblKeys.RANK
> FROM CONTAINSTABLE(sb_product_import, *, 'wandbidet', 250) AS
> tblKeys
> returns 49 rows.
> When I execute the same query, but without the TOP statement:
> SELECT tblKeys.[KEY], tblKeys.RANK
> FROM CONTAINSTABLE(sb_product_import, *, 'wandbidet') AS tblKeys
> the query returns 317 rows.
> In my opinion the first query has to return 250 rows, because the
> total number of affected rows is 317.
> Does anyone know this problem, or is there something I do wrong?
> best regards,
> Jens
>

CONTAINSTABLE doesn't return expected number of rows

Hello,
I've discovered a strange behaviour of the CONTAINSTABLE function.
When I use the TOP statement to return a number of rows, less rows are
returned then when I don't use this statement (while total affected
rows are > 250)
for example:
SELECT tblKeys.[KEY], tblKeys.RANK
FROM CONTAINSTABLE(sb_product_import, *, 'wandbidet', 250) AS
tblKeys
returns 49 rows.
When I execute the same query, but without the TOP statement:
SELECT tblKeys.[KEY], tblKeys.RANK
FROM CONTAINSTABLE(sb_product_import, *, 'wandbidet') AS tblKeys
the query returns 317 rows.
In my opinion the first query has to return 250 rows, because the
total number of affected rows is 317.
Does anyone know this problem, or is there something I do wrong?
best regards,
JensIts been a while since I played with it, but that is for ranking. Since Fuzz
y
Logic is used in executing the Contains against a FTS, it gives rank to each
result set. And when you specifiy the value Top n (Top_n_by_rank), it's jus
t
doing a filter on that field.
Try using the Top n in the select statement as in Select Top 10 * From ...
Thanks!
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/
"Jens" wrote:

> Hello,
> I've discovered a strange behaviour of the CONTAINSTABLE function.
> When I use the TOP statement to return a number of rows, less rows are
> returned then when I don't use this statement (while total affected
> rows are > 250)
> for example:
> SELECT tblKeys.[KEY], tblKeys.RANK
> FROM CONTAINSTABLE(sb_product_import, *, 'wandbidet', 250) AS
> tblKeys
> returns 49 rows.
> When I execute the same query, but without the TOP statement:
> SELECT tblKeys.[KEY], tblKeys.RANK
> FROM CONTAINSTABLE(sb_product_import, *, 'wandbidet') AS tblKeys
> the query returns 317 rows.
> In my opinion the first query has to return 250 rows, because the
> total number of affected rows is 317.
> Does anyone know this problem, or is there something I do wrong?
> best regards,
> Jens
>

CONTAINSTABLE doesn't return expected number of rows

Hello,
I've discovered a strange behaviour of the CONTAINSTABLE function.
When I use the TOP statement to return a number of rows, less rows are
returned then when I don't use this statement (while total affected
rows are > 250)
for example:
SELECT tblKeys.[KEY], tblKeys.RANK
FROM CONTAINSTABLE(sb_product_import, *, 'wandbidet', 250) AS
tblKeys
returns 49 rows.
When I execute the same query, but without the TOP statement:
SELECT tblKeys.[KEY], tblKeys.RANK
FROM CONTAINSTABLE(sb_product_import, *, 'wandbidet') AS tblKeys
the query returns 317 rows.
In my opinion the first query has to return 250 rows, because the
total number of affected rows is 317.
Does anyone know this problem, or is there something I do wrong?
best regards,
JensIts been a while since I played with it, but that is for ranking. Since Fuzzy
Logic is used in executing the Contains against a FTS, it gives rank to each
result set. And when you specifiy the value Top n (Top_n_by_rank), it's just
doing a filter on that field.
Try using the Top n in the select statement as in Select Top 10 * From ...
Thanks!
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/
"Jens" wrote:
> Hello,
> I've discovered a strange behaviour of the CONTAINSTABLE function.
> When I use the TOP statement to return a number of rows, less rows are
> returned then when I don't use this statement (while total affected
> rows are > 250)
> for example:
> SELECT tblKeys.[KEY], tblKeys.RANK
> FROM CONTAINSTABLE(sb_product_import, *, 'wandbidet', 250) AS
> tblKeys
> returns 49 rows.
> When I execute the same query, but without the TOP statement:
> SELECT tblKeys.[KEY], tblKeys.RANK
> FROM CONTAINSTABLE(sb_product_import, *, 'wandbidet') AS tblKeys
> the query returns 317 rows.
> In my opinion the first query has to return 250 rows, because the
> total number of affected rows is 317.
> Does anyone know this problem, or is there something I do wrong?
> best regards,
> Jens
>

Contains function in sql server 2005

H!
I use SQL Server 2005 and ASP.Net to program a dynamic web site. I use
full-text queries against plain character-based data with
'contains' predicat.
When my research contains more than one word separated by space (for
example: pierre baby), I have this error:
System.Data.SqlClient.SqlException: Syntax error near 'baby' in the
full-text search condition 'pierre baby'.
But the research works perfectly with (pierre|baby).
To have more user friendly tool, I would like to replace space by
"|", "&" by "+" etc.
I learn in the Internet about thesaurus function. And I performed the
following steps:
=B7 I add to the tsGLOBAL.xml file (in ../ Microsoft SQL
Server\MSSQL.1\MSSQL\FTDATA\ directory) the lines :
<XML ID=3D"Microsoft Search Thesaurus">
<thesaurus xmlns=3D"x-schema:tsSchema.xml">
<diacritics_sensitive>0</diacritics_sensitive>
<expansion>

_|
</expansion>
<expansion>
₊
_&
</expansion>
<replacement>
<pat> </pat>
_|
</replacement>
<replacement>
<pat>+</pat>
_&
</replacement>
</thesaurus>
</XML>
=B7 I modified my SQLquery like that:
dim requeteMC as string =3D "Select id , title from Table_V where
contains(*, 'formsof(thesaurus, " & keywords & ") ');"
But I had the same error:
System.Data.SqlClient.SqlException: Syntax error near 'baby' in the
full-text search condition 'pierre baby'.
Can any one help me about that? So when the user enter (baby Pierre)
the program converts it on baby|Pierre and we will not have an error.
Thank you very much,
regrads,
Djamila.Perhaps you get better responses by posting this to
microsoft.public.sqlserver.fulltext, which by the way, is one of the
newsgroups that you missed as you post-bombed multiple newsgroups.
Often, the quality of the responses received is related to our ability to
'bounce' ideas off of each other. In the future, to make it easier for us to
give you ideas, and to prevent folks from wasting time on already answered
questions, please:
Don't post to multiple newsgroups. Choose the one that best fits your
question and post there. Only post to another newsgroup if you get no answer
in a day or two (or if you accidentally posted to the wrong newsgroup -and
you indicate that you've already posted elsewhere).
If you really think that a question belongs into more than one newsgroup,
then use your newsreader's capability of multi-posting, i.e., posting one
occurrence of a message into several newsgroups at once. If you multi-post
appropriately, answers 'should' appear in all the newsgroups. Folks
responding in different newsgroups will see responses from each other, even
if the responses were posted in a different newsgroup.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"djamila" <djamilabouzid@.gmail.com> wrote in message
news:1164826388.137537.34310@.j72g2000cwa.googlegroups.com...
H!
I use SQL Server 2005 and ASP.Net to program a dynamic web site. I use
full-text queries against plain character-based data with
'contains' predicat.
When my research contains more than one word separated by space (for
example: pierre baby), I have this error:
System.Data.SqlClient.SqlException: Syntax error near 'baby' in the
full-text search condition 'pierre baby'.
But the research works perfectly with (pierre|baby).
To have more user friendly tool, I would like to replace space by
"|", "&" by "+" etc.
I learn in the Internet about thesaurus function. And I performed the
following steps:
I add to the tsGLOBAL.xml file (in ../ Microsoft SQL
Server\MSSQL.1\MSSQL\FTDATA\ directory) the lines :
<XML ID="Microsoft Search Thesaurus">
<thesaurus xmlns="x-schema:tsSchema.xml">
<diacritics_sensitive>0</diacritics_sensitive>
<expansion>

_|
</expansion>
<expansion>
₊
_&
</expansion>
<replacement>
<pat> </pat>
_|
</replacement>
<replacement>
<pat>+</pat>
_&
</replacement>
</thesaurus>
</XML>
I modified my SQLquery like that:
dim requeteMC as string = "Select id , title from Table_V where
contains(*, 'formsof(thesaurus, " & keywords & ") ');"
But I had the same error:
System.Data.SqlClient.SqlException: Syntax error near 'baby' in the
full-text search condition 'pierre baby'.
Can any one help me about that? So when the user enter (baby Pierre)
the program converts it on baby|Pierre and we will not have an error.
Thank you very much,
regrads,
Djamila.

CONTAINS function and OR

I am attempting to create a query with a "dynamic" CONTAINS query, e.g.
DECLARE @.Keywords VARCHAR(128)
SET @.Keywords = NULL
SELECT * FROM Jobs WHERE (@.Keywords IS NULL OR CONTAINS(JobTitle,
@.Keywords))
However, the CONTAINS function seems to behave very weirdly when OR is
involved. Even though @.Keywords is null, it still evaluates the contains,
and fails because of the null.
Server: Msg 7603, Level 15, State 1, Line 40
Syntax error in search condition, or empty or null search condition ''.
On the other and, if I change the query to this:
SELECT * FROM Jobs WHERE (@.Keywords IS NULL OR CONTAINS(JobTitle,
@.Keywords)) AND JobID IN (SELECT JobID FROM JobSkills WHERE SkillID = 2)
It works fine.
But that's not it. If I move the order of the clauses:
SELECT * FROM Jobs WHERE JobID IN (SELECT JobID FROM JobSkills WHERE SkillID
= 2) AND (@.Keywords IS NULL OR CONTAINS(JobTitle, @.Keywords))
I get the original error. Same goes for adding another contains clause at
the beginning. It seems that the original CONTAINS works fine, others defy
all logic.
Can someone suggest what is causing this behaviour (or why it works like
this - doesn't seem to make any sense) and a possible workaround short of
using hideous dynamic SQL?
Edward wrote on Fri, 20 Jan 2006 09:38:58 +1100:

> I am attempting to create a query with a "dynamic" CONTAINS query, e.g.
> DECLARE @.Keywords VARCHAR(128)
> SET @.Keywords = NULL
> SELECT * FROM Jobs WHERE (@.Keywords IS NULL OR CONTAINS(JobTitle,
> @.Keywords))
> However, the CONTAINS function seems to behave very weirdly when OR is
> involved. Even though @.Keywords is null, it still evaluates the contains,
> and fails because of the null.
> Server: Msg 7603, Level 15, State 1, Line 40
> Syntax error in search condition, or empty or null search condition
> ''.
> On the other and, if I change the query to this:
> SELECT * FROM Jobs WHERE (@.Keywords IS NULL OR CONTAINS(JobTitle,
> @.Keywords)) AND JobID IN (SELECT JobID FROM JobSkills WHERE SkillID = 2)
> It works fine.
> But that's not it. If I move the order of the clauses:
> SELECT * FROM Jobs WHERE JobID IN (SELECT JobID FROM JobSkills WHERE
> SkillID = 2) AND (@.Keywords IS NULL OR CONTAINS(JobTitle, @.Keywords))
> I get the original error. Same goes for adding another contains clause at
> the beginning. It seems that the original CONTAINS works fine, others defy
> all logic.
> Can someone suggest what is causing this behaviour (or why it works like
> this - doesn't seem to make any sense) and a possible workaround short of
> using hideous dynamic SQL?
It depends on the query parser and how it decides to process the query.
Depending on the order it processes clauses, and what they contain, it might
skip the CONTAINS clause completely (which it appears to do in the 2nd
case). Using FTS clauses when unnecessary will impact performance as the FTS
process is external to SQL Server. You could try doing the following:
IF COALESCE(@.Keywords,'') = ''
SELECT * FROM Jobs
ELSE
SELECT * FROM Jobs WHERE CONTAINS(JobTitle, @.Keywords)
END
This avoids dynamic SQL, and prevents the error is @.Keywords is NULL or
empty (an empty string will also cause an error, not just a NULL)
Dan

CONTAINS full-text XML problem

I've noticed that the CONTAINS function in the SQL SELECT statement has a
strange behavior on XML. When I use a statement like:
SELECT * FROM T where CONTAINS(dcxml,'pesca')
where "dcXML" is the column name and "pesca" is the word I'm searching for,
it only finds XML docs in the form of:
<tag1>
<key>...</key>
<key>...</key>
<key>... pesca ...</key>
</tag1>
(pesca always in the last <key> of the set)
and skips those like
<tag1>
<key>...</key>
<key>... pesca ...</key>
<key>...</key>
</tag1>
(pesca somewhere else in the repeated tag set)
Any help?
Are you indexing the xml as text or in an image column? If you are indexing
xml in an image column which word breaker are you using?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"MytyMyky" <MytyMyky@.discussions.microsoft.com> wrote in message
news:26C01878-70FB-4CBB-82AA-979CC8196460@.microsoft.com...
> I've noticed that the CONTAINS function in the SQL SELECT statement has a
> strange behavior on XML. When I use a statement like:
> SELECT * FROM T where CONTAINS(dcxml,'pesca')
> where "dcXML" is the column name and "pesca" is the word I'm searching
for,
> it only finds XML docs in the form of:
> <tag1>
> <key>...</key>
> <key>...</key>
> <key>... pesca ...</key>
> </tag1>
> (pesca always in the last <key> of the set)
> and skips those like
> <tag1>
> <key>...</key>
> <key>... pesca ...</key>
> <key>...</key>
> </tag1>
> (pesca somewhere else in the repeated tag set)
> Any help?
>
|||MytyMyky,
Could you provide the full output from the below SQL script as this is
helpful in troubleshooting SQL FTS issues.
use <your_database_name_here>
go
SELECT @.@.language
SELECT @.@.version
-- May require setting advance sp_configure settings
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
Thanks,
John
"MytyMyky" <MytyMyky@.discussions.microsoft.com> wrote in message
news:26C01878-70FB-4CBB-82AA-979CC8196460@.microsoft.com...
> I've noticed that the CONTAINS function in the SQL SELECT statement has a
> strange behavior on XML. When I use a statement like:
> SELECT * FROM T where CONTAINS(dcxml,'pesca')
> where "dcXML" is the column name and "pesca" is the word I'm searching
for,
> it only finds XML docs in the form of:
> <tag1>
> <key>...</key>
> <key>...</key>
> <key>... pesca ...</key>
> </tag1>
> (pesca always in the last <key> of the set)
> and skips those like
> <tag1>
> <key>...</key>
> <key>... pesca ...</key>
> <key>...</key>
> </tag1>
> (pesca somewhere else in the repeated tag set)
> Any help?
>
|||I forgot to mention a crucial detail. I'm using SQL 2005 Beta 2, so I store
the xml in a xml column.
"Hilary Cotter" wrote:

> Are you indexing the xml as text or in an image column? If you are indexing
> xml in an image column which word breaker are you using?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "MytyMyky" <MytyMyky@.discussions.microsoft.com> wrote in message
> news:26C01878-70FB-4CBB-82AA-979CC8196460@.microsoft.com...
> for,
>
>
|||I get the result in 11 tables:
--1--
us_english
--2--
Microsoft SQL Server Yukon - 9.00.852 (Intel X86) Jul 19 2004 22:09:12
Copyright (c) 1988-2003 Microsoft Corporation Beta Edition on Windows NT 5.2
(Build 3790: )
--3--
5 testeCatalog C:\fulltextcatalog\testeCatalog 0 1
--4--
dboTPK_T10testeCatalog
--5--
dbo2073058421TdcXml2NULLNULL2070
--6--
Tdbouser table2004-09-13 14:17:54.903
--7--
IDintno410 0 no(n/a)(n/a)NULL
dcXmlxmlno-1 no(n/a)(n/a)NULL
--8--
ID110
--9--
No rowguidcol column defined.
--10--
PK_Tclustered, unique, primary key located on PRIMARYID
--11--
PRIMARY KEY (clustered)PK_T(n/a)(n/a)(n/a)(n/a)ID
"John Kane" wrote:

> MytyMyky,
> Could you provide the full output from the below SQL script as this is
> helpful in troubleshooting SQL FTS issues.
> use <your_database_name_here>
> go
> SELECT @.@.language
> SELECT @.@.version
> -- May require setting advance sp_configure settings
> 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
> Thanks,
> John
|||Yes, that is somewhat crucial. Can we see an example of your query?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"MytyMyky" <MytyMyky@.discussions.microsoft.com> wrote in message
news:69CAA62A-FD60-474D-BBDB-1B45D09B23E2@.microsoft.com...
> I forgot to mention a crucial detail. I'm using SQL 2005 Beta 2, so I
store[vbcol=seagreen]
> the xml in a xml column.
> "Hilary Cotter" wrote:
indexing[vbcol=seagreen]
has a[vbcol=seagreen]
|||I included an example in my first post:
select * from T where contains(dcXML,'something')
"Hilary Cotter" wrote:

> Yes, that is somewhat crucial. Can we see an example of your query?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "MytyMyky" <MytyMyky@.discussions.microsoft.com> wrote in message
> news:69CAA62A-FD60-474D-BBDB-1B45D09B23E2@.microsoft.com...
> store
> indexing
> has a
>
>

constrained flag in the STRTOSET function violated

I am having a really hard time trying to get around the auto generated MDX when I use a date as a parameter. It is forcing the values to be string and this is not allowing me to use the date picker on the reports. Can anyone help me figure this one out? Is there any way to use the date picker when using a cube dataset?

The constrained flag is not your problem, it is simply a flag for the STRTOSET function, and you can get rid of it.

The output of the datepicker is a string, the format of that string depends on the location you have your browser set to (eg IE is set to en-US by default). The approach i have used for this in the past is to CDate the output from the datepicker, then use Format to make it into a string that matches your cube's date heirarchy so that you can use STRTOSET on it. So, in your MDX where you have:

STRTOSET(@.yourDateParameter, CONSTRAINED)

change it to:

STRTOSET( Format( CDate(@.yourDateParameter), "<suitable format code>"), CONSTRAINED)

the <suitable format code> bit could be something like "yyyy/MM/dd", what i ended up needing to resemble my date heirarchy was "yyyy-MM-ddT00:00:00".

Hope this helps.

|||

Thank yo so much for your help! I tried your suggested and got this error Query (1, 112) The '[Format]' function does not exist. (Microsoft SQL Server 2005 Analysis Services)

I must have done something wrong... please advise.

|||

I use this is SSRS2005 with no problems, i don't know if it is permissable in 2000. Which version are you using?

||| I am also using SSRS2005....|||

Here are a couple of samples of using the Format() function in real code. The first one is used for filtering dates for a parameter dropdown:

WITH

MEMBER [Measures].[ParameterValue] AS '[Sale Date].[Date Description].CURRENTMEMBER.UNIQUENAME'

SELECT {[Measures].[ParameterValue] } on columns,

{ Filter( [Sale Date].[Date Description].[Date Description], Format(CDate( [Sale Date].[Date Description].CURRENTMEMBER.MEMBER_CAPTION), "dd Mon yyyy") = Format(Now(), "dd Mon yyyy")) } on rows

FROM [MyCube]

The second one is a subset of a much larger query. The first STRTOSET shows me manipulating an actual return string from a calendar control (you can insert @.YourParameterName instead of the actual datetime string) to fit the look of my heirarchy member.

SELECT NON EMPTY { [Measures].[Capacity], [Measures].[Booked] } ON COLUMNS

FROM ( SELECT (

STRTOMEMBER("[Sale Date].[Date].&[" + Format(CDate("2006/05/02 12:00:00 AM"), "yyyy-MM-ddT00:00:00") + "]", CONSTRAINED) :

STRTOMEMBER("[Sale Date].[Date].&[2006-05-06T00:00:00]", CONSTRAINED)

)

ON COLUMNS FROM [MyCube]

)

Hope this helps!

constrained flag in the STRTOSET function violated

I am having a really hard time trying to get around the auto generated MDX when I use a date as a parameter. It is forcing the values to be string and this is not allowing me to use the date picker on the reports. Can anyone help me figure this one out? Is there any way to use the date picker when using a cube dataset?

The constrained flag is not your problem, it is simply a flag for the STRTOSET function, and you can get rid of it.

The output of the datepicker is a string, the format of that string depends on the location you have your browser set to (eg IE is set to en-US by default). The approach i have used for this in the past is to CDate the output from the datepicker, then use Format to make it into a string that matches your cube's date heirarchy so that you can use STRTOSET on it. So, in your MDX where you have:

STRTOSET(@.yourDateParameter, CONSTRAINED)

change it to:

STRTOSET( Format( CDate(@.yourDateParameter), "<suitable format code>"), CONSTRAINED)

the <suitable format code> bit could be something like "yyyy/MM/dd", what i ended up needing to resemble my date heirarchy was "yyyy-MM-ddT00:00:00".

Hope this helps.

|||

Thank yo so much for your help! I tried your suggested and got this error Query (1, 112) The '[Format]' function does not exist. (Microsoft SQL Server 2005 Analysis Services)

I must have done something wrong... please advise.

|||

I use this is SSRS2005 with no problems, i don't know if it is permissable in 2000. Which version are you using?

||| I am also using SSRS2005....|||

Here are a couple of samples of using the Format() function in real code. The first one is used for filtering dates for a parameter dropdown:

WITH

MEMBER [Measures].[ParameterValue] AS '[Sale Date].[Date Description].CURRENTMEMBER.UNIQUENAME'

SELECT {[Measures].[ParameterValue] } on columns,

{ Filter( [Sale Date].[Date Description].[Date Description], Format(CDate( [Sale Date].[Date Description].CURRENTMEMBER.MEMBER_CAPTION), "dd Mon yyyy") = Format(Now(), "dd Mon yyyy")) } on rows

FROM [MyCube]

The second one is a subset of a much larger query. The first STRTOSET shows me manipulating an actual return string from a calendar control (you can insert @.YourParameterName instead of the actual datetime string) to fit the look of my heirarchy member.

SELECT NON EMPTY { [Measures].[Capacity], [Measures].[Booked] } ON COLUMNS

FROM ( SELECT (

STRTOMEMBER("[Sale Date].[Date].&[" + Format(CDate("2006/05/02 12:00:00 AM"), "yyyy-MM-ddT00:00:00") + "]", CONSTRAINED) :

STRTOMEMBER("[Sale Date].[Date].&[2006-05-06T00:00:00]", CONSTRAINED)

)

ON COLUMNS FROM [MyCube]

)

Hope this helps!

Constants to Sets

Hi,

I guess that's an easy one... I'm quite sure that my brain is gone for weekend... And I'm still getting used to MDX...

I'm using the min function to find out the minimum of two measures, so basically:

min({[Measures].[MeasureA],[Measures].[MeasureB]})

This works perfectly. But what I need is to have something like that:

min({[Measures].[MeasureA],[Measures].[MeasureB],0})

So that when A and B are both greater than 0 the result is 0. OK, I can do that with iif but that's not nice... There must be a way to add a constant to a set... I even need something like that:

min ({sum(...),sum(...),0})

I guess it's the same problem and the same solution...

Any help is very appreciated...

is this what you mean?

func(1,2) -> 0

func(-1,2) -> -1

func(-1,-2) -> -2

its not much prettier than iif -

Min(Min(0, a), Min(0, b))

|||

Well, you understood what I'm looking for... However I'm quite sure that this doesn't work...

The MDX min function expects a set. What you provide is value (as far as I know normally called an expression), that doesn't seam to work at all...

|||

sorry. . mdx is not my forte. . .

from the help -

min(Set-Expression, [Numeric-Expression]) evaluates Numeric over Set

can't you make a set from two constants?

Evalute Min Zero over a, And Min Zero over b,

'set' results and min -

Min( { Min({a},0), Min({b},0) } )

|||

ught. . Im not thinking. . .

isnt it

Min({a,b},0)

|||That was my first try... Doesn't work... The result is always 0, no idea why...|||

Hi Thomas,

How about using a calculated measure for the constant lower limit, like in this Adventure Works example:

>>

With Member [Measures].[LowLimitOrder] as 2500

Member [Measures].[MinOrderCount] as

Min({[Measures].[Internet Order Count],

[Measures].[Reseller Order Count],

[Measures].[LowLimitOrder]})

select {[Measures].[Internet Order Count],

[Measures].[Reseller Order Count],

[Measures].[MinOrderCount]} on 0,

[Product].[Product Categories].[Category].Members on 1

from [Adventure Works]

Internet Order Count Reseller Order Count MinOrderCount
Accessories 18,208 1,315 1315
Bikes 15,205 3,153 2500
Clothing 7,461 2,410 2410
Components (null) 2,646 2500

>>

|||

Deepak,

once again... THANKS for your superb support! Works like a charme...