Gathering view from sysobjects

Is the statement below full-proof in gathering views defined in a sql server 7.0 database
select a.name from sysobjects a where a.type = 'V' and a.status >
ThanksSELECT table_name FROM information_schema.views
is even easier. Microsoft advises not to access system tables directly, as
they might changed between versions and services packs. The
information_schema views are a set of ANSI standard views to represent
system information that are guaranteed not to change.
--
Jacco Schalkwijk
SQL Server MVP
"T" <anonymous@.discussions.microsoft.com> wrote in message
news:05B941B1-A86A-40C1-B2EB-CDDA459ED456@.microsoft.com...
> Is the statement below full-proof in gathering views defined in a sql
server 7.0 database?
> select a.name from sysobjects a where a.type = 'V' and a.status > 0
> Thanks|||gotya! Thanks a lot!|||To add to Jacco's response, you can exclude system objects using the
OBJECTPROPERTY function like the example below.
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'VIEW' AND
OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' +
QUOTENAME(TABLE_NAME)
), 'IsMSShipped') = 0
--
Hope this helps.
Dan Guzman
SQL Server MVP
"T" <anonymous@.discussions.microsoft.com> wrote in message
news:05B941B1-A86A-40C1-B2EB-CDDA459ED456@.microsoft.com...
> Is the statement below full-proof in gathering views defined in a sql
server 7.0 database?
> select a.name from sysobjects a where a.type = 'V' and a.status > 0
> Thanks|||This is actually what I was looking for. Many thanks

Funny! More offers to select a search engine! (PP)

wycareersterThet's really nice. And what am I looking for?
ML

funny sql

The following sql updates 300 records(3000 records in the
marc_POt_lu_rd_post_code table) but the select element only returns one row.
I am attempting to update the 3000 rows which it does but it does it
incorrectly in that the results set from the select portion does not match
what the results set returns after the update. I added the extra postcode
criteria in the select to isolate what the update does but it still updates
the 3000 rows. Weird?
UPDATE marc_POt_lu_rd_post_code
SET County_id = c.County_id,
County_desc = c.County_Desc,
Parent_County_Id = c.Parent_County_Id,
Parent_County_desc = c.County_desc,
Sector_Id = d.Sector_Id,
Sector_Desc = d.Sector_Desc,
Area_Id = e.Area_Id,
Area_Desc = e.Area_Desc
-- Select *
FROM Pot_lu_County_Area_PostCodes a,
QUINN_st..GET_BCP_H_POSTCODES b,
Pot_lu_county c,
Pot_lu_Sectors d,
Pot_lu_Areas e
WHERE a.Postcode = b.Four_Char_Post_Codes
AND b.COUNTY = c.County_Desc
AND b.SECTOR = d.Sector_Desc
AND b.AREA = e.Area_Desc
and a.Postcode = b.Four_Char_Post_Codes
and b.Four_Char_Post_Codes = 'mk40'found the issue
"marcmc" wrote:

> The following sql updates 300 records(3000 records in the
> marc_POt_lu_rd_post_code table) but the select element only returns one ro
w.
> I am attempting to update the 3000 rows which it does but it does it
> incorrectly in that the results set from the select portion does not match
> what the results set returns after the update. I added the extra postcode
> criteria in the select to isolate what the update does but it still update
s
> the 3000 rows. Weird?
> UPDATE marc_POt_lu_rd_post_code
> SET County_id = c.County_id,
> County_desc = c.County_Desc,
> Parent_County_Id = c.Parent_County_Id,
> Parent_County_desc = c.County_desc,
> Sector_Id = d.Sector_Id,
> Sector_Desc = d.Sector_Desc,
> Area_Id = e.Area_Id,
> Area_Desc = e.Area_Desc
> -- Select *
> FROM Pot_lu_County_Area_PostCodes a,
> QUINN_st..GET_BCP_H_POSTCODES b,
> Pot_lu_county c,
> Pot_lu_Sectors d,
> Pot_lu_Areas e
> WHERE a.Postcode = b.Four_Char_Post_Codes
> AND b.COUNTY = c.County_Desc
> AND b.SECTOR = d.Sector_Desc
> AND b.AREA = e.Area_Desc
> and a.Postcode = b.Four_Char_Post_Codes
> and b.Four_Char_Post_Codes = 'mk40'
>

Funny DateTime Issues

Hello this is weird when I run this on a Friday
SELECT DATENAME(dw,5) --> returns 'Saturday'
Select DATEPART(dw,GETDATE()) --> returns 5
Can anyone explain why this would occur?Check your @.@.DATEFIRST value. It might be set to 1 ( Monday ). You can
change it using SET DATEFIRST statement.
Anith|||I think the problem in the first query is that sql see the number that you
pass as the day of the month or a Julian date. The value of the second
parameter to DATENAME should be a valid date. If you run SELECT DATENAME(dw,
GETDATE()) then you will get "Friday". The second query you have returns the
wday number. For example Sunday = 1...Saturday = 7. That can be changed
by SET DATEFIRST. But by default 5 is the wday number for Friday.
Hope that helps
Tim
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:ewb3BkG7FHA.2628@.TK2MSFTNGP11.phx.gbl...
> Check your @.@.DATEFIRST value. It might be set to 1 ( Monday ). You can
> change it using SET DATEFIRST statement.
> --
> Anith
>|||The second parameter for DATENAME is a date - this call is getting the
day of the w for 1900-01-06, which is a Saturday. The implicit
conversion is based on 0=1900-01-01, ergo 5=1900-01-06.
As Anith has said, DATEPART is dependent on DATEFIRST, which is probably
set to Monday in your system.
yurps wrote:
> Hello this is weird when I run this on a Friday
> SELECT DATENAME(dw,5) --> returns 'Saturday'
> Select DATEPART(dw,GETDATE()) --> returns 5
> Can anyone explain why this would occur?
>|||Ok, if this is the case, try this out and explain why it works
set datefirst 1
declare @.bd datetime
select @.bd = '2005-12-01 00:00:00';
with dd (FullDateAlternateKey,
DayNumberOfW,HourNumber,EnglishDayNam
eOfW)
as
(
select @.bd,datepart(dw,@.bd),datepart(hh,@.bd),da
tename(dw,@.bd)
union all
select dateadd(hh,1,FullDateAlternateKey)
,datepart(dw,dateadd(hh,1,FullDateAltern
ateKey))
,datepart(hh,dateadd(hh,1,FullDateAltern
ateKey))
,datename(dw,day(dateadd(dw,2,dateadd(hh
,1,FullDateAlternateKey))))
from dd
where FullDateAlternateKey<='2005-12-31'
)
select * from dd
option (maxrecursion 0)
You will notice that I have added a dateadd(dw,2,...) in the recursive part.
Try pulling it out you will find (at least that is what happens in my
system) that the day returned is two days back from the actual day.
Am I doing something wrong?
"Trey Walpole" wrote:

> The second parameter for DATENAME is a date - this call is getting the
> day of the w for 1900-01-06, which is a Saturday. The implicit
> conversion is based on 0=1900-01-01, ergo 5=1900-01-06.
> As Anith has said, DATEPART is dependent on DATEFIRST, which is probably
> set to Monday in your system.
> yurps wrote:
>

Functions in SQL Server7

Is it possible to create Functions in SqlServer 7?
I have a huge query > 500,000 rows that I want to select a subset of using a
function
Select IdentityInd, ColA, ColB
From TableA Where
UDFContains(IdentityInd, ColB ) = 1
**************************************
--Function
And UDFContains will looklike
UDFContains(@.IdentityInd, @.ColB )
Returns Bit
Begin
IF EXISTS(Select IdentityInd From TableA Where
IdentityInd = @.IdentityInd AND CONTAINS(ColA,
@.ColB)) BEGIN
Return 1
End
ELSE BEGIN
Retuen 0
End
End
****************************************
******Just in SQL Server 2000 for now.
AMB
"Sanjay Pais" wrote:

> Is it possible to create Functions in SqlServer 7?
> I have a huge query > 500,000 rows that I want to select a subset of using
a
> function
> Select IdentityInd, ColA, ColB
> From TableA Where
> UDFContains(IdentityInd, ColB ) = 1
> **************************************
> --Function
> And UDFContains will looklike
> UDFContains(@.IdentityInd, @.ColB )
> Returns Bit
> Begin
> IF EXISTS(Select IdentityInd From TableA Where
> IdentityInd = @.IdentityInd AND CONTAINS(ColA,
> @.ColB)) BEGIN
> Return 1
> End
> ELSE BEGIN
> Retuen 0
> End
> End
> ****************************************
******
>
>|||No, but you can do this in the where clause:
Select IdentityInd, ColA, ColB
From TableA
Where EXISTS( Select inExists.IdentityInd
From TableA as inExists
Where IdentityInd = tableA.IdentityInd
AND CONTAINS(inExists.ColA, tableA.ColB))
Can't you? It should be preferrable performancewise anyhow, I would expect.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Sanjay Pais" <spaisatnospammarketlinksolutions.com> wrote in message
news:eeK6c$UBFHA.3924@.TK2MSFTNGP10.phx.gbl...
> Is it possible to create Functions in SqlServer 7?
> I have a huge query > 500,000 rows that I want to select a subset of using
> a function
> Select IdentityInd, ColA, ColB
> From TableA Where
> UDFContains(IdentityInd, ColB ) = 1
> **************************************
> --Function
> And UDFContains will looklike
> UDFContains(@.IdentityInd, @.ColB )
> Returns Bit
> Begin
> IF EXISTS(Select IdentityInd From TableA Where
> IdentityInd = @.IdentityInd AND CONTAINS(ColA,
> @.ColB)) BEGIN
> Return 1
> End
> ELSE BEGIN
> Retuen 0
> End
> End
> ****************************************
******
>|||You can't use two columns in a contains clause which caused my dilema in the
first place :)
Sanjay
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:ul5SFhWBFHA.4004@.tk2msftngp13.phx.gbl...
> No, but you can do this in the where clause:
> Select IdentityInd, ColA, ColB
> From TableA
> Where EXISTS( Select inExists.IdentityInd
> From TableA as inExists
> Where IdentityInd = tableA.IdentityInd
> AND CONTAINS(inExists.ColA, tableA.ColB))
> Can't you? It should be preferrable performancewise anyhow, I would
> expect.
> --
> ----
--
> Louis Davidson - drsql@.hotmail.com
> SQL Server MVP
> Compass Technology Management - www.compass.net
> Pro SQL Server 2000 Database Design -
> http://www.apress.com/book/bookDisplay.html?bID=266
> Note: Please reply to the newsgroups only unless you are interested in
> consulting services. All other replies may be ignored :)
> "Sanjay Pais" <spaisatnospammarketlinksolutions.com> wrote in message
> news:eeK6c$UBFHA.3924@.TK2MSFTNGP10.phx.gbl...
>|||Ah, sorry :)
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Sanjay Pais" <spaisatnospammarketlinksolutions.com> wrote in message
news:uI%23xQvWBFHA.3700@.tk2msftngp13.phx.gbl...
> You can't use two columns in a contains clause which caused my dilema in
> the first place :)
> Sanjay
> "Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
> news:ul5SFhWBFHA.4004@.tk2msftngp13.phx.gbl...
>

Function vs. Sub-Query

When my sproc selects a function (which in itself has a select statement to gather data) it takes substantially longer time (minutes) than if I replace the function with a sub query in the sproc (split second). What is the reason for this?
BjornI've seen this too. In my case when I looked at the execution plan and the server trace it appears the udf is called for each row returned where the sub query doesn't. I was using a udf to calculate the status of the records. I ended up using a view instead to calculate the status and joined my original query to the view. This is similiar to a sub query and much much faster than the udf.|||Thanks! It makes sense. It seems that the sub query runs first and only once in an sproc, extracting all the data needed for the main query.

Bjorn

Function that replaces ntext and compares ntext with nvarchar

I am running this query to an sql server 2000 database from my asp
code:
"select * from MyTable where
MySqlServerRemoveStressFunction(MyNtextColumn) = '" &
MyAdoRemoveStressFunction(MyString) & "'"

The problem is that the replace function doesn't work with the ntext
datatype (so as to replace the stresses with an empty string). I had
to implement the MySqlServerRemoveStressFunction, i.e. a function that
takes a column name as a parameter and returns the text contained in
this column having replaced some letters of the text (the letters with
stress). Unfortunately, I could not do that because user-defined
functions cannot return a value of ntext.

So I have the following idea:
"select * from MyTable where
CheckIfTheyAreEqualIngoringTheStesses(MyNtextColum n, '" & MyString &
"')"

How can I implement the CheckIfTheyAreEqualIngoringTheStesses
function? (I don't know how to combine these functions to do what I
want: TEXTPTR, UPDATETEXT, WRITETEXT, READTEXT)(verb13@.hotmail.com) writes:

Quote:

Originally Posted by

I am running this query to an sql server 2000 database from my asp
code:
"select * from MyTable where
MySqlServerRemoveStressFunction(MyNtextColumn) = '" &
MyAdoRemoveStressFunction(MyString) & "'"
>
The problem is that the replace function doesn't work with the ntext
datatype (so as to replace the stresses with an empty string). I had
to implement the MySqlServerRemoveStressFunction, i.e. a function that
takes a column name as a parameter and returns the text contained in
this column having replaced some letters of the text (the letters with
stress). Unfortunately, I could not do that because user-defined
functions cannot return a value of ntext.
>
So I have the following idea:
"select * from MyTable where
CheckIfTheyAreEqualIngoringTheStesses(MyNtextColum n, '" & MyString &
"')"
>
How can I implement the CheckIfTheyAreEqualIngoringTheStesses
function? (I don't know how to combine these functions to do what I
want: TEXTPTR, UPDATETEXT, WRITETEXT, READTEXT)

I will have to admit that I don't really follow what this
CheckIfTheyAreEqualIngoringTheStesses is supposed to achieve. But
there are a lot of problems working with ntext. In SQL 2005 there
is a new data type nvarchar(MAX) which has the same limit as ntext,
but without the limitations.

However, if I understand you right, you want to make an accent-insensitive
comparision, so that "rsum" = "resume". This you can do easily without
any replace business, just use an accent-insentive collation:

SELECT * FROM MyTable
WHERE MyNtextColumn
COLLATE Finnish_Swedish_CI_AI = ?

(As for the question mark, that's an indiciation that you should use
parameterised statements and not interpolate parameters into your SQL
commands.)

Note that Finnish_Swedish_CI_AI is just an example, and you should pick
the CI_AI collation that matches the language(s) you work with.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||It is just what I needed. Thanks a lot.

On Nov 29, 12:42 am, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

(ver...@.hotmail.com) writes:

Quote:

Originally Posted by

I am running this query to an sql server 2000 database from my asp
code:
"select * from MyTable where
MySqlServerRemoveStressFunction(MyNtextColumn) = '" &
MyAdoRemoveStressFunction(MyString) & "'"

>

Quote:

Originally Posted by

The problem is that the replace function doesn't work with the ntext
datatype (so as to replace the stresses with an empty string). I had
to implement the MySqlServerRemoveStressFunction, i.e. a function that
takes a column name as a parameter and returns the text contained in
this column having replaced some letters of the text (the letters with
stress). Unfortunately, I could not do that because user-defined
functions cannot return a value of ntext.

>

Quote:

Originally Posted by

So I have the following idea:
"select * from MyTable where
CheckIfTheyAreEqualIngoringTheStesses(MyNtextColum n, '" & MyString &
"')"

>

Quote:

Originally Posted by

How can I implement the CheckIfTheyAreEqualIngoringTheStesses
function? (I don't know how to combine these functions to do what I
want: TEXTPTR, UPDATETEXT, WRITETEXT, READTEXT)

>
I will have to admit that I don't really follow what this
CheckIfTheyAreEqualIngoringTheStesses is supposed to achieve. But
there are a lot of problems working with ntext. In SQL 2005 there
is a new data type nvarchar(MAX) which has the same limit as ntext,
but without the limitations.
>
However, if I understand you right, you want to make an accent-insensitive
comparision, so that "rsum" = "resume". This you can do easily without
any replace business, just use an accent-insentive collation:
>
SELECT * FROM MyTable
WHERE MyNtextColumn
COLLATE Finnish_Swedish_CI_AI = ?
>
(As for the question mark, that's an indiciation that you should use
parameterised statements and not interpolate parameters into your SQL
commands.)
>
Note that Finnish_Swedish_CI_AI is just an example, and you should pick
the CI_AI collation that matches the language(s) you work with.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx- Hide quoted text -
>
- Show quoted text -

Function Syntax

Below is the function that I am trying to create. I am failing in trying to
select from a random table. So I pass the table name in the function but it
won't let me select from that. Can someone help?
CREATE FUNCTION dbo.GetZoneID
(
@.ID int,
@.tableName varchar(50)
)
RETURNS money AS
BEGIN
DECLARE @.MasterZoneID INT,
@.Flag INT,
@.ZoneID money,
@.TempZoneID money,
@.TempTableName varchar(50)
SET @.Flag = 0
SET @.MasterZoneID = (Select cast(ZoneID as int) from System)
SET @.TempZoneID = (@.MasterZoneID * 1000000000) + @.ID
WHILE(@.Flag = 0)
BEGIN
--Failing on the @.tableName--
SET @.ZoneID = (Select ZoneID from @.tableName where ZoneID = @.TempZoneID)
--Failing there--
IF(@.ZoneID = 0)
BEGIN
RETURN (@.MasterZoneID * 1000000000) + @.ID
END
ELSE
BEGIN
SET @.TempZoneID = @.TempZoneID + 0.0001
END
END
RETURN (@.MasterZoneID * 1000000000) + @.ID
ENDYou can't do this, sorry. The only way to "pass in" a table name is by
using dynamic SQL, which is not allowed in a UDF. What do you need this
for? Why don't you know the table name ahead of time?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Sean McKaharay" <sean@.classactweb.com> wrote in message
news:Oy540EMJFHA.2936@.TK2MSFTNGP15.phx.gbl...
> Below is the function that I am trying to create. I am failing in trying
to
> select from a random table. So I pass the table name in the function but
it
> won't let me select from that. Can someone help?
>
> CREATE FUNCTION dbo.GetZoneID
> (
> @.ID int,
> @.tableName varchar(50)
> )
> RETURNS money AS
> BEGIN
> DECLARE @.MasterZoneID INT,
> @.Flag INT,
> @.ZoneID money,
> @.TempZoneID money,
> @.TempTableName varchar(50)
> SET @.Flag = 0
> SET @.MasterZoneID = (Select cast(ZoneID as int) from System)
> SET @.TempZoneID = (@.MasterZoneID * 1000000000) + @.ID
>
> WHILE(@.Flag = 0)
> BEGIN
> --Failing on the @.tableName--
> SET @.ZoneID = (Select ZoneID from @.tableName where ZoneID = @.TempZoneID)
> --Failing there--
> IF(@.ZoneID = 0)
> BEGIN
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
> ELSE
> BEGIN
> SET @.TempZoneID = @.TempZoneID + 0.0001
> END
> END
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
>
>|||The reason why it fails is because your running dynamic code.
Have a look at the following
Declare @.SQL as varchar(100)
set @.SQL = 'Select ZoneID from + ' + @.tableName + ' where ZoneID = ' +
@.TempZoneID
nb if any of these are ints then convert them to varchars
EXEC sp_executesql @.sql, N'@.ZoneID int OUTPUT', @.ZoneID OUTPUT
The sp_executesql will execute the sql then return the value into @.ZoneID.
If you are trying to random values why don't you do
declare @.Random int
set @.Random= rand() * 1000000
instead ?
Peter
"Information is the oxygen of the modern age. It seeps through the walls
topped by barbed wire, it wafts across the electrified borders.""
Ronald Reagan
"Sean McKaharay" wrote:
> Below is the function that I am trying to create. I am failing in trying to
> select from a random table. So I pass the table name in the function but it
> won't let me select from that. Can someone help?
>
> CREATE FUNCTION dbo.GetZoneID
> (
> @.ID int,
> @.tableName varchar(50)
> )
> RETURNS money AS
> BEGIN
> DECLARE @.MasterZoneID INT,
> @.Flag INT,
> @.ZoneID money,
> @.TempZoneID money,
> @.TempTableName varchar(50)
> SET @.Flag = 0
> SET @.MasterZoneID = (Select cast(ZoneID as int) from System)
> SET @.TempZoneID = (@.MasterZoneID * 1000000000) + @.ID
>
> WHILE(@.Flag = 0)
> BEGIN
> --Failing on the @.tableName--
> SET @.ZoneID = (Select ZoneID from @.tableName where ZoneID = @.TempZoneID)
> --Failing there--
> IF(@.ZoneID = 0)
> BEGIN
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
> ELSE
> BEGIN
> SET @.TempZoneID = @.TempZoneID + 0.0001
> END
> END
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
>
>
>|||If it's a temp table, it's unique per user. So why can't you hard-code that
name in the function? See http://www.aspfaq.com/2248 for an example.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Sean McKaharay" <sean@.classactweb.com> wrote in message
news:Oy540EMJFHA.2936@.TK2MSFTNGP15.phx.gbl...
> Below is the function that I am trying to create. I am failing in trying
to
> select from a random table. So I pass the table name in the function but
it
> won't let me select from that. Can someone help?
>
> CREATE FUNCTION dbo.GetZoneID
> (
> @.ID int,
> @.tableName varchar(50)
> )
> RETURNS money AS
> BEGIN
> DECLARE @.MasterZoneID INT,
> @.Flag INT,
> @.ZoneID money,
> @.TempZoneID money,
> @.TempTableName varchar(50)
> SET @.Flag = 0
> SET @.MasterZoneID = (Select cast(ZoneID as int) from System)
> SET @.TempZoneID = (@.MasterZoneID * 1000000000) + @.ID
>
> WHILE(@.Flag = 0)
> BEGIN
> --Failing on the @.tableName--
> SET @.ZoneID = (Select ZoneID from @.tableName where ZoneID = @.TempZoneID)
> --Failing there--
> IF(@.ZoneID = 0)
> BEGIN
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
> ELSE
> BEGIN
> SET @.TempZoneID = @.TempZoneID + 0.0001
> END
> END
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
>
>|||Sorry, my bad, I forgot that example uses a procedure, not a function... I
was completely thinking of a different kind of problem.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Sean McKaharay" <sean@.classactweb.com> wrote in message
news:Oy540EMJFHA.2936@.TK2MSFTNGP15.phx.gbl...
> Below is the function that I am trying to create. I am failing in trying
to
> select from a random table. So I pass the table name in the function but
it
> won't let me select from that. Can someone help?
>
> CREATE FUNCTION dbo.GetZoneID
> (
> @.ID int,
> @.tableName varchar(50)
> )
> RETURNS money AS
> BEGIN
> DECLARE @.MasterZoneID INT,
> @.Flag INT,
> @.ZoneID money,
> @.TempZoneID money,
> @.TempTableName varchar(50)
> SET @.Flag = 0
> SET @.MasterZoneID = (Select cast(ZoneID as int) from System)
> SET @.TempZoneID = (@.MasterZoneID * 1000000000) + @.ID
>
> WHILE(@.Flag = 0)
> BEGIN
> --Failing on the @.tableName--
> SET @.ZoneID = (Select ZoneID from @.tableName where ZoneID = @.TempZoneID)
> --Failing there--
> IF(@.ZoneID = 0)
> BEGIN
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
> ELSE
> BEGIN
> SET @.TempZoneID = @.TempZoneID + 0.0001
> END
> END
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
>
>|||Sorry forgot you can't do a rand() in function duh
"Peter 'Not Peter The Spate' Nolan" wrote:
> The reason why it fails is because your running dynamic code.
> Have a look at the following
> Declare @.SQL as varchar(100)
> set @.SQL = 'Select ZoneID from + ' + @.tableName + ' where ZoneID = ' +
> @.TempZoneID
> nb if any of these are ints then convert them to varchars
> EXEC sp_executesql @.sql, N'@.ZoneID int OUTPUT', @.ZoneID OUTPUT
> The sp_executesql will execute the sql then return the value into @.ZoneID.
> If you are trying to random values why don't you do
> declare @.Random int
> set @.Random= rand() * 1000000
> instead ?
> Peter
> "Information is the oxygen of the modern age. It seeps through the walls
> topped by barbed wire, it wafts across the electrified borders.""
> Ronald Reagan
>
> "Sean McKaharay" wrote:
> > Below is the function that I am trying to create. I am failing in trying to
> > select from a random table. So I pass the table name in the function but it
> > won't let me select from that. Can someone help?
> >
> >
> > CREATE FUNCTION dbo.GetZoneID
> > (
> > @.ID int,
> > @.tableName varchar(50)
> > )
> > RETURNS money AS
> > BEGIN
> > DECLARE @.MasterZoneID INT,
> > @.Flag INT,
> > @.ZoneID money,
> > @.TempZoneID money,
> > @.TempTableName varchar(50)
> >
> > SET @.Flag = 0
> > SET @.MasterZoneID = (Select cast(ZoneID as int) from System)
> > SET @.TempZoneID = (@.MasterZoneID * 1000000000) + @.ID
> >
> >
> > WHILE(@.Flag = 0)
> > BEGIN
> > --Failing on the @.tableName--
> > SET @.ZoneID = (Select ZoneID from @.tableName where ZoneID = @.TempZoneID)
> > --Failing there--
> > IF(@.ZoneID = 0)
> > BEGIN
> > RETURN (@.MasterZoneID * 1000000000) + @.ID
> > END
> > ELSE
> > BEGIN
> > SET @.TempZoneID = @.TempZoneID + 0.0001
> > END
> > END
> >
> > RETURN (@.MasterZoneID * 1000000000) + @.ID
> >
> > END
> >
> >
> >
> >
> >|||..or dynamic code, bad post or what ;(
"Sean McKaharay" wrote:
> Below is the function that I am trying to create. I am failing in trying to
> select from a random table. So I pass the table name in the function but it
> won't let me select from that. Can someone help?
>
> CREATE FUNCTION dbo.GetZoneID
> (
> @.ID int,
> @.tableName varchar(50)
> )
> RETURNS money AS
> BEGIN
> DECLARE @.MasterZoneID INT,
> @.Flag INT,
> @.ZoneID money,
> @.TempZoneID money,
> @.TempTableName varchar(50)
> SET @.Flag = 0
> SET @.MasterZoneID = (Select cast(ZoneID as int) from System)
> SET @.TempZoneID = (@.MasterZoneID * 1000000000) + @.ID
>
> WHILE(@.Flag = 0)
> BEGIN
> --Failing on the @.tableName--
> SET @.ZoneID = (Select ZoneID from @.tableName where ZoneID = @.TempZoneID)
> --Failing there--
> IF(@.ZoneID = 0)
> BEGIN
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
> ELSE
> BEGIN
> SET @.TempZoneID = @.TempZoneID + 0.0001
> END
> END
> RETURN (@.MasterZoneID * 1000000000) + @.ID
> END
>
>
>

function on linked server

I have my own function, which I can use:
select * from index_gold_iif('20040121')
When I try do it from linked serwer:
select * from
lewiatan.e_.dbo.index_gold_iif('20040121')
I have massage:
Server: Msg 170, Level 15, State 31, Line 2
Line 4: Incorrect syntax near '('.
As I understand server can't see function from linked server. Is it true or
I have done some error (what)?
Marek
PS. all done on "sa" userMarek
No, server can see functions
select * from openquery
(servername,'select * from
dbo.fn_dates(''20040101'',''20040110'')') --this function returns range
of date between given dates
"Marek Wierzbicki" <marek.wierzbicki.___@.azymut.pl> wrote in message
news:bv35q6$cuh$1@.news2.ipartners.pl...
> I have my own function, which I can use:
> select * from index_gold_iif('20040121')
> When I try do it from linked serwer:
> select * from
> lewiatan.e_.dbo.index_gold_iif('20040121')
> I have massage:
> Server: Msg 170, Level 15, State 31, Line 2
> Line 4: Incorrect syntax near '('.
> As I understand server can't see function from linked server. Is it true
or
> I have done some error (what)?
> Marek
> PS. all done on "sa" user
>
>|||> No, server can see functions
> select * from openquery
> (servername,'select * from
> dbo.fn_dates(''20040101'',''20040110'')') --this function returns
range
> of date between given dates
OPENQUERY - I don't know this function up today. What about optimalization
with this function - isn't it so slow as exec('query string")?
Can I use this function to make query like:
select * from va_name_table
for example:
select * from openquery(localhost, 'select * from '+@.table_name)?
Marek|||> No, server can see functions
> select * from openquery
> (servername,'select * from
> dbo.fn_dates(''20040101'',''20040110'')') --this function returns
range
> of date between given dates
there something wrongs with openquery - it didn't recognize query string
bild on the fly. So what for call openquery with string to connect to
function, which I need to write parametr of this funcion once forewer?
Marek
PS. example with error:
declare @.dt as datetime
declare @.s as varchar(520)
select @.dt='20040121 12:22:33'
select @.s='select * from e_.dbo.index_gold_iif('+convert(varchar(22), @.dt,
112)+' '+convert(varchar(22), @.dt, 108)+')'
print @.s -- up to this place workong OK
select * from OPENQUERY(lewiatan , @.s) -- this is not working
Server: Msg 170, Level 15, State 1, Line 8
Line 8: Incorrect syntax near '@.s'.|||> No, server can see functions
> select * from openquery
> (servername,'select * from
> dbo.fn_dates(''20040101'',''20040110'')') --this function returns
range
> of date between given dates
I think its wrong answer beacouse openquery is run (as I read) on linked
server, not local
Marek

function on indexed field

Hi, I am using sql server 2000 SP1.

select * from document_display where upper(document_name) = upper(v_document_name)

v_document_name is a variable.

The table is over 200,000 records and presently has index on column document_name

Could anyone help on how to improve the performance of above query ?

? Can you store the document_name in a column that uses a non-case-sensitive collation? Then you won't need to use the UPPER function to compare them... -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <gtisupport@.discussions.microsoft.com> wrote in message news:6a3d9de8-dd46-4977-87cf-dadb89f09ad0_WBRev1_@.discussions..microsoft.com...This post has been edited either by the author or a moderator in the Microsoft Forums: http://forums.microsoft.com Hi, I am using sql server 2000 SP1. select * from document_display where upper(document_name) = upper(v_document_name) v_document_name is a variable. The table is over 200,000 records and presently has index on column document_name Could anyone help on how to improve the performance of above query ?|||

depending on collation, there shouldnt really be a distinction between

select * from document_display where upper(document_name) = upper(v_document_name)

and

select * from document_display where document_name = v_document_name

|||

Thanks, but the effort is to too great to implement. I know that Oracle DB has feature called function index. Is there a similar one in SQL Server ?

|||Another thing you could to to speed performance is not do a select * from the table. Specify the specific columns in you table that you require for the query. Then have your index cover the document_name and the columns that are specified in the query. This will eliminated any bookmark lookups that might have been going on.|||? No, there are no function indexes in SQL Server. Why is a non-case sensitive column too difficult to implement? All you have to do is: ALTER TABLE document_displayADD document_name_ci AS (document_name) COLLATE SQL_Latin1_General_CP1_CI_AS Then you can create an index on the new column: CREATE INDEX IX_document_name_ci ON document_display (document_name_ci) ... and that's pretty much it! Now you can use the new column in your queries, without worrying about the UPPER function. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <gtisupport@.discussions.microsoft.com> wrote in message news:f4393446-77fc-4634-bc45-5bde6ecdc828@.discussions.microsoft.com... Thanks, but the effort is to too great to implement. I know that Oracle DB has feature called function index. Is there a similar one in SQL Server ?

Function like ToString

Hi

I want to deleveop a dateTime datatype for Arabic Calender.

how can i write a fucntion like ToString() function?

select ArabicDateTime::GetDate().ToWeek()

thanks every body

I don't quite understand what you are looking for. You can write your own User Defined Types (UDT's), and you can do it using the CLR. Look in BOL about UDT's for that. In that scenario, you would then just write a method on the UDT which would be whatever you wanted it to be.

Niels
|||Thank you nielsb

I write function ToString() like this

VB Code

Public Overrides Function ToString() As String
Return Me.Year & "/" & Me.Month & "/" & Me.Day
End Function

and i can call this Function in SQL Server :

SQL Code

select PersianDateTime::GetDate().ToString()

now i want to write a function named Toweeday() and call it in SQL server like this:

Code Snippet

select PersianDateTime::GetDate().Toweeday()

but i've problem. my Function Code is:

Code Snippet

public Overrides Function Toweeday() As String

Return Me.Yearname & " " & Me.Monthname & " " & Me.Dayname
End Function

VB.Net notify me to Delete Overrides Keyword. and i must use Shared keyword. and when i use Shared keyword then i cant use Me.Yearname.

thank you agin

|||

Mohsen,

Just remove the Overrides keyword and don't use Shared either. You only need Overrides when there is a method on the base class that you wish to replace the functionality of in your base class. Since Toweeday is not defined on the the base class of your class, you don't need to override it. Object is the base class for all classes, and it is where ToString is defined, which is why you need Overrides for ToString. Shared makes the method an aspect of the class not any particular instance of the class, so it cannot access instance fields. So, don't use that if you want to define a method which accesses instance fields.

If you would like to see an example of DateTime for other calendar systems, you might want to look at the CADateTime sample at http://www.codeplex.com/MSFTEngProdSamples/Wiki/View.aspx?title=SS2005%21Calendar-Aware%20Date%2fTime%20UDTs&referringTitle=Home.

|||thank you so much. my promlem solved.

i test it befor i make this topic but i catch this erorr

Msg 6573, Level 16, State 1, Line 0
Method, property or field 'ToWeekday' of class 'ArbicDateTime.ArabicDateTime' in assembly 'ArbicDateTime' is not static.

but now there isn't any problem. why?

Function in select statement

How can I put a function in a select statement such as

SUM(code.GetValue( A, B, C, D, E)) AS TC_Reserve

I want to pass the function several values and have it perform a complex formula

and return a value. And then sum the value returned for each row.

This data is then used to create a chart "Dollars by Product Category"

Is this possible. I've only been at this for a week so I have no idea if it can done.

I get the message below

===============================================================

TITLE: Microsoft Report Designer

Could not generate a list of fields for the query.
Check the query syntax, or click Refresh Fields on the query toolbar.

ADDITIONAL INFORMATION:

Cannot find either column "code" or the user-defined function or aggregate "code.GetValue", or the name is ambiguous. (Microsoft SQL Server, Error: 4121)

It isn't possible to use a function that you've created in your report in a SQL statement. If you give an in-depth explanation of what you are trying to accomplish there may be a workaround I could help you with.

Another alternative is you could create a User Defined Function (UDF) and store it in your SQL Server database or you can create a stored procedure. Then you could reference it from a Select statement.

See this link for an intro to UDFs

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

See this link for an intro to Stored Procedures

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

|||

Thanks for the offer so here goes:

I need to pass

sales data (25 comma seperated value) - create an array (split function works great)

date of first activity

quantity on hand

factor (.59, .80. 1.00, blank, etc)

units (1.00, 60.0, blank)

months of sales to use ( 6 or 12)

function code (1 or 2)

cost (9999.9999)

what I forgot 1 (I'm sure I left 1 or 2 out)

what I forgot 2

=====================================================================

If function code = 1 then

if the date of first activity is < 365 days from today's date then

return 0.0

else

if the sum of the first 12 values in the sales data array = 0 then

return ( (qoh * (units * cost) ) * .90 )

else

if qoh > the sum of the first 6 values in the sales data array then

return ( (qoh * (units * cost) ) .50)

else

return 0.0

end

If fuction code = 2 then same as above but multiple the return value by the factor value

example: return ( ( (qoh * (units * cost) ) * .90 ) * factor )

Some of the data is string used as numeric values so it has to tested and converted to number.

Some of the string data could be blank so it has to tested and a default inserted.

Thanks for your efforts.

Note: Don't spend a lot of time on this it's not a required part of the report. I can do the basic report as above but I wanted to insert a chart (jazz it up and great learning experience) and I needed to have all the values done in the query so I can call it as a " jump to report". I got everything to work except the sum(function)) part. If I replace it with something like "sum(QOH) as TC_Reserve" it works great just the data is not correct.

|||

I think I understand what you want to do. This blog post has some details on a workaround. It's basically a custom aggregate hacked into Reporting Services:

http://blogs.msdn.com/bwelcker/archive/2005/05/10/416306.aspx

Let me know if this will work for you or you have some questions.

|||You may want look into Calculated Fields. You could use one based on an expression, which calls your custom code. Calculated field expressions allow you to access other field values--they will be the values from the current row.

In Report Designer, to add a calculated field right-click on the Data Set, and choose Add.... In the Add New Field dialog enter the name for the field, choose Calculated field, and then enter the expression for the Calculated Field. In your case, the expression would look something like what you mentioned. Aggregates are not supported in Calculated field expressions, though.

Your query would not have a reference to this calculation, so you would simply remove it from the field list.

Ian

Function for counting distance

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

Function doesn't use indexes

Hello!
I have a function on SQL 2000 sp3a that executes a simple select statement.
It takes input parameter and joins two tables based on that parameter and
then returns the result as a table.
Problem is that the function does not use any indexes. Select is performed
by using full scans on both tables.
If I then execute that same select statement not using that function just
select statement with the same input parameter,
execution plan changes and it uses the right indexes. Sure it's a lot
faster...
Why the function doesn't use indexes?
Why would select statement use indexes correctly and the function that
executes the same select statement would't?
Tom
It would help a lot if we could see this "function". Is it really a
function or a stored procedure?
Andrew J. Kelly SQL MVP
"Tom" <mcseman2002@.hotmail.com> wrote in message
news:Oio7zth7FHA.4076@.tk2msftngp13.phx.gbl...
> Hello!
> I have a function on SQL 2000 sp3a that executes a simple select
> statement.
> It takes input parameter and joins two tables based on that parameter and
> then returns the result as a table.
> Problem is that the function does not use any indexes. Select is performed
> by using full scans on both tables.
> If I then execute that same select statement not using that function just
> select statement with the same input parameter,
> execution plan changes and it uses the right indexes. Sure it's a lot
> faster...
> Why the function doesn't use indexes?
> Why would select statement use indexes correctly and the function that
> executes the same select statement would't?
> Tom
>
>
|||HI,
yeah as said if you can post a query and function it would be great for us
to resolve issue , have you check it with index hint !?
Regards
Andy Davis
Active Crypt Team
---SQL Server Encryption
Decryption Software
http://www.activecrypt.com
"Tom" wrote:

> Hello!
> I have a function on SQL 2000 sp3a that executes a simple select statement.
> It takes input parameter and joins two tables based on that parameter and
> then returns the result as a table.
> Problem is that the function does not use any indexes. Select is performed
> by using full scans on both tables.
> If I then execute that same select statement not using that function just
> select statement with the same input parameter,
> execution plan changes and it uses the right indexes. Sure it's a lot
> faster...
> Why the function doesn't use indexes?
> Why would select statement use indexes correctly and the function that
> executes the same select statement would't?
> Tom
>
>

Function doesn't use indexes

Hello!
I have a function on SQL 2000 sp3a that executes a simple select statement.
It takes input parameter and joins two tables based on that parameter and
then returns the result as a table.
Problem is that the function does not use any indexes. Select is performed
by using full scans on both tables.
If I then execute that same select statement not using that function just
select statement with the same input parameter,
execution plan changes and it uses the right indexes. Sure it's a lot
faster...
Why the function doesn't use indexes?
Why would select statement use indexes correctly and the function that
executes the same select statement would't?
TomIt would help a lot if we could see this "function". Is it really a
function or a stored procedure?
Andrew J. Kelly SQL MVP
"Tom" <mcseman2002@.hotmail.com> wrote in message
news:Oio7zth7FHA.4076@.tk2msftngp13.phx.gbl...
> Hello!
> I have a function on SQL 2000 sp3a that executes a simple select
> statement.
> It takes input parameter and joins two tables based on that parameter and
> then returns the result as a table.
> Problem is that the function does not use any indexes. Select is performed
> by using full scans on both tables.
> If I then execute that same select statement not using that function just
> select statement with the same input parameter,
> execution plan changes and it uses the right indexes. Sure it's a lot
> faster...
> Why the function doesn't use indexes?
> Why would select statement use indexes correctly and the function that
> executes the same select statement would't?
> Tom
>
>|||HI,
yeah as said if you can post a query and function it would be great for us
to resolve issue , have you check it with index hint !?
Regards
--
Andy Davis
Active Crypt Team
---SQL Server Encryption
Decryption Software
http://www.activecrypt.com
"Tom" wrote:

> Hello!
> I have a function on SQL 2000 sp3a that executes a simple select statement
.
> It takes input parameter and joins two tables based on that parameter and
> then returns the result as a table.
> Problem is that the function does not use any indexes. Select is performed
> by using full scans on both tables.
> If I then execute that same select statement not using that function just
> select statement with the same input parameter,
> execution plan changes and it uses the right indexes. Sure it's a lot
> faster...
> Why the function doesn't use indexes?
> Why would select statement use indexes correctly and the function that
> executes the same select statement would't?
> Tom
>
>

Function doesn't use indexes

Hello!
I have a function on SQL 2000 sp3a that executes a simple select statement.
It takes input parameter and joins two tables based on that parameter and
then returns the result as a table.
Problem is that the function does not use any indexes. Select is performed
by using full scans on both tables.
If I then execute that same select statement not using that function just
select statement with the same input parameter,
execution plan changes and it uses the right indexes. Sure it's a lot
faster...
Why the function doesn't use indexes?
Why would select statement use indexes correctly and the function that
executes the same select statement would't?
TomIt would help a lot if we could see this "function". Is it really a
function or a stored procedure?
--
Andrew J. Kelly SQL MVP
"Tom" <mcseman2002@.hotmail.com> wrote in message
news:Oio7zth7FHA.4076@.tk2msftngp13.phx.gbl...
> Hello!
> I have a function on SQL 2000 sp3a that executes a simple select
> statement.
> It takes input parameter and joins two tables based on that parameter and
> then returns the result as a table.
> Problem is that the function does not use any indexes. Select is performed
> by using full scans on both tables.
> If I then execute that same select statement not using that function just
> select statement with the same input parameter,
> execution plan changes and it uses the right indexes. Sure it's a lot
> faster...
> Why the function doesn't use indexes?
> Why would select statement use indexes correctly and the function that
> executes the same select statement would't?
> Tom
>
>|||HI,
yeah as said if you can post a query and function it would be great for us
to resolve issue , have you check it with index hint !?
Regards
--
Andy Davis
Active Crypt Team
---SQL Server Encryption
Decryption Software
http://www.activecrypt.com
"Tom" wrote:
> Hello!
> I have a function on SQL 2000 sp3a that executes a simple select statement.
> It takes input parameter and joins two tables based on that parameter and
> then returns the result as a table.
> Problem is that the function does not use any indexes. Select is performed
> by using full scans on both tables.
> If I then execute that same select statement not using that function just
> select statement with the same input parameter,
> execution plan changes and it uses the right indexes. Sure it's a lot
> faster...
> Why the function doesn't use indexes?
> Why would select statement use indexes correctly and the function that
> executes the same select statement would't?
> Tom
>
>

Full-text Search with CONTAINSTABLE is slow for first time

I have a Full-text Search query as mentioned below.
SELECT * FROM CONTAINSTABLE(TABLE_NAME, COL_Name,
N'ISABOUT(FORMSOF(INFLECTIONAL, TEXT_HERE))') ORDER BY Rank DESC
The table, TABLE_NAME contains around 800 rows and the column COL_Name is
data type of nvarchar(255).
The above query taking around 60 seconds when i execute it very first time.
And in next sub-sequent executions it is taking less than 1 second.
If i execute the same query after 30 minutes (without performing any thing),
in the first time it is taking again 60 seconds.
What whould be the problem here?
Thanks,
Naveen
does this apply?
http://support.microsoft.com/kb/915850
http://www.zetainteractive.com - Shift Happens!
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
"Naveen Kumar" <Naveen Kumar@.discussions.microsoft.com> wrote in message
news:87A1EE89-C7EC-4290-9DBA-F383396AB7A0@.microsoft.com...
>I have a Full-text Search query as mentioned below.
> SELECT * FROM CONTAINSTABLE(TABLE_NAME, COL_Name,
> N'ISABOUT(FORMSOF(INFLECTIONAL, TEXT_HERE))') ORDER BY Rank DESC
> The table, TABLE_NAME contains around 800 rows and the column COL_Name is
> data type of nvarchar(255).
> The above query taking around 60 seconds when i execute it very first
> time.
> And in next sub-sequent executions it is taking less than 1 second.
> If i execute the same query after 30 minutes (without performing any
> thing),
> in the first time it is taking again 60 seconds.
> What whould be the problem here?
> Thanks,
> Naveen
>

Fulltext search miss records in select

Running
SELECT productid,productname from productsFullText
where
freetext (*, 'x2000')
or
SELECT productid,productname from productsFullText
where
contains (*, 'x2000')
gives 1 hit.
SELECT productid,productname from productsFullText
where
productname like '%x2000%'
gives 7 hits.
I use the english wordbreaker and the FullIndex is populated with all
ItemCount.
Whats wrong in my setup
Regards
Henrik JuelHi, Henrik,
Per my understanding, you got less records from a full-text query than a
regular SQL query with LIKE statement.
If I have misunderstood, please let me know.
For further research, I would like to know:
1. What is your SQL Server version?
2. Has the latest service pack been installed?
If the latest service pack has not been installed on your computer, please
install it first.
If the issue perists, please try rebuilding your full text catalog. You may
refer to:
ALTER FULLTEXT CATALOG (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms176095.aspx
Also, I recommend that you check if there are some error informaion in SQL
error logs and Event logs.
Please feel free to let me know if you have any other questions or concerns.
Sincerely yours,
Charles Wang
Microsoft Online Community Support
========================================
=============
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Hi, Henrik,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
Have a good day!
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Thanks for your reply Charles
I found the answer.
The fulltext seachs command FREETEXT will only hit on full words and LIKE %%
can also find a searchtext that is in a word. Thats why I always will get
more hits using the LIKE when performing a search on one searchtext.
rgs Henrik
--
Regards
Henrik Juel
"Charles Wang[MSFT]" wrote:

> Hi, Henrik,
> I am interested in this issue. Would you mind letting me know the result o
f
> the suggestions? If you need further assistance, feel free to let me know.
>
> Have a good day!
> Charles Wang
> Microsoft Online Community Support
> ========================================
==============
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ========================================
==============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> ========================================
==============
>|||Hi, Henrik,
Thank you for your reply and the detailed additional feedback on how you
were successful in resolving this issue. This information has been added to
Microsoft's database. Your solution will benefit many other users, and we
really value having you as a Microsoft customer.
If you have any other questions or concerns, please do not hesitate to
contact us. It is always our pleasure to be of assistance.
Have a nice day!
Charles Wang
Microsoft Online Community Support
========================================
=============
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============

Fulltext search miss records in select

Running
SELECT productid,productname from productsFullText
where
freetext (*, 'x2000')
or
SELECT productid,productname from productsFullText
where
contains (*, 'x2000')
gives 1 hit.
SELECT productid,productname from productsFullText
where
productname like '%x2000%'
gives 7 hits.
I use the english wordbreaker and the FullIndex is populated with all
ItemCount.
Whats wrong in my setup
--
Regards
Henrik JuelHi, Henrik,
Per my understanding, you got less records from a full-text query than a
regular SQL query with LIKE statement.
If I have misunderstood, please let me know.
For further research, I would like to know:
1. What is your SQL Server version?
2. Has the latest service pack been installed?
If the latest service pack has not been installed on your computer, please
install it first.
If the issue perists, please try rebuilding your full text catalog. You may
refer to:
ALTER FULLTEXT CATALOG (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms176095.aspx
Also, I recommend that you check if there are some error informaion in SQL
error logs and Event logs.
Please feel free to let me know if you have any other questions or concerns.
Sincerely yours,
Charles Wang
Microsoft Online Community Support
=====================================================Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hi, Henrik,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
Have a good day!
Charles Wang
Microsoft Online Community Support
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Thanks for your reply Charles
I found the answer.
The fulltext seachs command FREETEXT will only hit on full words and LIKE %%
can also find a searchtext that is in a word. Thats why I always will get
more hits using the LIKE when performing a search on one searchtext.
rgs Henrik
--
Regards
Henrik Juel
"Charles Wang[MSFT]" wrote:
> Hi, Henrik,
> I am interested in this issue. Would you mind letting me know the result of
> the suggestions? If you need further assistance, feel free to let me know.
>
> Have a good day!
> Charles Wang
> Microsoft Online Community Support
> ======================================================> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
> ======================================================>|||Hi, Henrik,
Thank you for your reply and the detailed additional feedback on how you
were successful in resolving this issue. This information has been added to
Microsoft's database. Your solution will benefit many other users, and we
really value having you as a Microsoft customer.
If you have any other questions or concerns, please do not hesitate to
contact us. It is always our pleasure to be of assistance.
Have a nice day!
Charles Wang
Microsoft Online Community Support
=====================================================Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

Fulltext search miss records in select

Running
SELECT productid,productname from productsFullText
where
freetext (*, 'x2000')
or
SELECT productid,productname from productsFullText
where
contains (*, 'x2000')
gives 1 hit.
SELECT productid,productname from productsFullText
where
productname like '%x2000%'
gives 7 hits.
I use the english wordbreaker and the FullIndex is populated with all
ItemCount.
Whats wrong in my setup
Regards
Henrik Juel
Hi, Henrik,
Per my understanding, you got less records from a full-text query than a
regular SQL query with LIKE statement.
If I have misunderstood, please let me know.
For further research, I would like to know:
1. What is your SQL Server version?
2. Has the latest service pack been installed?
If the latest service pack has not been installed on your computer, please
install it first.
If the issue perists, please try rebuilding your full text catalog. You may
refer to:
ALTER FULLTEXT CATALOG (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms176095.aspx
Also, I recommend that you check if there are some error informaion in SQL
error logs and Event logs.
Please feel free to let me know if you have any other questions or concerns.
Sincerely yours,
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||Hi, Henrik,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
Have a good day!
Charles Wang
Microsoft Online Community Support
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||Thanks for your reply Charles
I found the answer.
The fulltext seachs command FREETEXT will only hit on full words and LIKE %%
can also find a searchtext that is in a word. Thats why I always will get
more hits using the LIKE when performing a search on one searchtext.
rgs Henrik
Regards
Henrik Juel
"Charles Wang[MSFT]" wrote:

> Hi, Henrik,
> I am interested in this issue. Would you mind letting me know the result of
> the suggestions? If you need further assistance, feel free to let me know.
>
> Have a good day!
> Charles Wang
> Microsoft Online Community Support
> ================================================== ====
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ================================================== ====
> This posting is provided "AS IS" with no warranties, and confers no rights.
> ================================================== ====
>
|||Hi, Henrik,
Thank you for your reply and the detailed additional feedback on how you
were successful in resolving this issue. This information has been added to
Microsoft's database. Your solution will benefit many other users, and we
really value having you as a Microsoft customer.
If you have any other questions or concerns, please do not hesitate to
contact us. It is always our pleasure to be of assistance.
Have a nice day!
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====