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

I have a subquery that is running really slow when i use a date from the parent query

* the function udfMinContact returns a table of the earliest contact for every child that occured after the date passed to the function

* the function udfMaxReferral returns a table of the latest Referral for every child that occured before the date passed to the function

* the referral happens first, then the child is contacted. i'm looking for contacts that happed over 45 days after the referral
*************************************************************************************
DECLARE
@.EndDate DateTime,
@.StartDate DateTime

SET @.StartDate = '4/1/2007'
SET @.EndDate = '6/30/2007'

SELECT c.ChildId, c.FN, c.LN, c.DOB

FROM Child c INNER JOIN udfMinContact(@.StartDate) ct ON c.ChildID = ct.ChildId

WHERE ct.ContactDate BETWEEN @.StartDate AND @.EndDate

AND EXISTS
(
SELECT ChildId
FROM udfMaxReferral(ct.ContactDate) r
WHERE r.ChildId = c.ChildId
AND r.ReferralDate < DATEADD(dd, -45, ct.ContactDate)

)
*********************************************************************************************

If i run as is, it takes over 40 min. If i replace 'ct.ContactDate' which i highlighted with a static date like '1/1/2007' it runs in just a few seconds.

any idea why the drastic time difference and any suggestions on how to speed it up?

thanks

Check the Execution Plan.

Your FUNCTION has to fully execute for each and every row in the table, perhaps two times per row if it needs to re-calculate for the sub-query..

You may be able to substanially improve execution speed if you JOIN with the data that has the earliest contact INSTEAD of using the function. (I'm assuming that the function is a query.)

Please post the entire FUNCTION code and we can better determine the optimal way to deal with your issue.

|||

Is udfMaxReferral a multi-statement or inline TVF? Look at the query plan to see how the join is being done. If it is a nested loop join then it is possible that the TVF is invoked for every row that is being joined in the outer SELECT statement. Also, if the TVF is multi-statement then there are no statistics on the rows being returned so the plan will be sub-optimal. You should consider using inline TVF so that the query can be optimized as a whole.

Now, as for the question why if you use a variable or column in the TVF it is slower than a value or constant is due to plan caching and query optimization. When you specify a constant in a predicate or parameter to SP or function etc then the query optimizer can use that value and determine the best plan based on the available statistics. On the other hand, if you specify a variable or column then the value is not known and it can be any value within the domain of a data type so the query optimizer will pick a plan that works optimally for any search value. See the white paper on compilation, recompilation for more details on how this works.

|||Function: udfMinContact
Description: Returns info on the earliest 'IF' Contact (that occured on or after the StartDate) for every child

ALTER FUNCTION [dbo].[udfMinContact]
(
@.StartDate datetime
)
RETURNS @.retChildList TABLE
(
ChildId uniqueidentifier,
ContactId uniqueidentifier,
ContactDate datetime
)

AS
BEGIN

INSERT @.retChildList
SELECT ChildId, ContactId, ContactDate
FROM Contact ct
WHERE ContactId =
(
SELECT TOP(1) ContactId
FROM Contact
WHERE ChildId = ct.ChildId
AND ContactDate >= @.StartDate
ORDER BY ContactDate ASC, CREATE_TIME ASC
)

RETURN
END;

*********************************************************************************

Function: udfMaxReferral
Description: Returns info on the most current Referral (that occured on or before the EndDate) for every child

ALTER FUNCTION [dbo].[udfMaxReferral]
(
@.EndDate datetime
)
RETURNS @.retChildList TABLE
(
ChildId uniqueidentifier,
ReferralId uniqueidentifier,
ReferralDate datetime
)

AS
BEGIN

INSERT @.retChildList
SELECT ChildId, ReferralId, ReferralDate
FROM Referral r
WHERE ReferralId =
(
SELECT TOP(1) ReferralId
FROM Referral
WHERE ChildId = r.ChildId
AND ReferralDate <= @.EndDate
ORDER BY ReferralDate DESC, CREATE_TIME DESC
)

RETURN
END;

*********************************************************************************************************

I know the functions are not ideal, but the way the database is set up, it's the best i could do. there are many cases where a child will have several contacts or referrals on the same day so this is how i forced only 1 to be returned
|||

you can try this, and check if it can change your query speed,

notice, the get_datetime is a function needed you defined it.

i think ORDER BY clause always waste resource!!!

ALTER FUNCTION [dbo].[udfMaxReferral]
(
@.EndDate datetime
)
RETURNS @.retChildList TABLE
(
ChildId uniqueidentifier,
ReferralId uniqueidentifier,
ReferralDate datetime
)

AS
BEGIN

INSERT @.retChildList
SELECT ChildId, ReferralId, ReferralDate
FROM Referral a inner join

(

SELECT ChildId,ReferralId,MAX(get_datetime(ReferralDate,ReferralTime) datetime
FROM Referral

WHERE ReferralDate <= @.EndDate

GROUP BY ChildId,ReferralId

) b on a.ChildId=b.ChildId and a.ReferralId=ReferralId.ReferralId and get_datetime(a.ReferralDate,a.ReferralTime)=b.datetime

WHERE ReferralDate <= @.EndDate

RETURN
END;

|||

As per Uma's suggestion you have to convert your Multilined TVF to Inline TVF,

Use the following functions,

Code Snippet

CREATE FUNCTION [dbo].[udfMinContact]

(

@.StartDate datetime

)

RETURNS TABLE

AS

RETURN (

SELECT ChildId, ContactId, ContactDate

FROM Contact ct

WHERE ContactId =

(

SELECT TOP(1) ContactId

FROM Contact

WHERE ChildId = ct.ChildId

AND ContactDate >= @.StartDate

ORDER BY ContactDate ASC, CREATE_TIME ASC

)

)

GO

CREATE FUNCTION [dbo].[udfMaxReferral]

(

@.EndDate datetime

)

RETURNS TABLE

AS

RETURN

(

SELECT ChildId, ReferralId, ReferralDate

FROM Referral r

WHERE ReferralId =

(

SELECT TOP(1) ReferralId

FROM Referral

WHERE ChildId = r.ChildId

AND ReferralDate <= @.EndDate

ORDER BY ReferralDate DESC, CREATE_TIME DESC

)

)

|||

Manivannan.D.Sekaran wrote:

As per Uma's suggestion you have to convert your Multilined TVF to Inline TVF,

What is the difference? Sorry I'm still pretty new to anything more the simple SQL

Functions

I have a string argument in a stored procedure that returns a string
value. I would like to replace that string argument with a function.
Does anyone know what the syntax would be?
Here is an example:
exec sp_send_cdosysmail
'sqladmin@.mycompany.com','DBAeMailAddress@.mycompany.com','Subject Of
e-mail','Body of e-mail message'
I would like to replace DBAeMailAddress@.mycompany.com with a function.
I already have the function written and it does work successfully but
not with the function call from within the arguments for the stored
procedure.
If I can find a way to successfully implement this, I can change the
on-call DBA's name in the function instead of within every single
scheduled job.
Toni
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!You cannot call a function inside a stored procedure call. But why not
declare a local variable assign a value to the variable (using the function
call) and then use that local variable in the parameter list?
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Toni" <teibner@.allina.com> wrote in message
news:e2OjRW7pDHA.2012@.TK2MSFTNGP12.phx.gbl...
> I have a string argument in a stored procedure that returns a string
> value. I would like to replace that string argument with a function.
> Does anyone know what the syntax would be?
> Here is an example:
> exec sp_send_cdosysmail
> 'sqladmin@.mycompany.com','DBAeMailAddress@.mycompany.com','Subject Of
> e-mail','Body of e-mail message'
> I would like to replace DBAeMailAddress@.mycompany.com with a function.
> I already have the function written and it does work successfully but
> not with the function call from within the arguments for the stored
> procedure.
> If I can find a way to successfully implement this, I can change the
> on-call DBA's name in the function instead of within every single
> scheduled job.
>
> Toni
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Function with local parameters

I am trying to write a function that returns different values regdaring to a
input value.
Why can't I write like this. What shoudl I do instead.
I want to be able to write a select Item from GetPermission ('user', 're')
Regards
/Magnus
alter function [dbo].[GetPermissions3]
(
@.UserName varchar(50),
@.ItemName varchar(50),
)
RETURNS TABLE
AS
declare @.objecttype int
if @.ItemType='RE' set @.objecttype=1
RETURN
(
If
select i.Item, o.[name] from ItemGroups i
full outer join Groups g
on g.ItemGroup = i.ItemGroup
full outer join Users u
on u.GroupName = g.GroupName
join Control.dbo.Object o on i.item = o.object and o.no=@.objecttype
where (UserName = @.UserName or i.ItemGroup = @.UserName) and ItemName =
@.ItemName
)Please have a look at the syntax used to create functions. You are
attempting to create an inline function which must not have a function body
and which must contain a single select query within the returns clause. If
you want to include logic that cannot be handled within a single select
statement, then you will need to use the more complex table-valued function
syntax.|||Thanks!
After some searching for complex table-valued function I found the syntax
necessary!
My simple test (if someone wants to know) became:
alter FUNCTION MBTest
( @.FirstColNumber int )
RETURNS @.MyTable TABLE
( FirstCol varchar(50),
SecondCol varchar(50) )
AS BEGIN
declare @.mynum varchar(50)
select @.mynum = case @.FirstColNumber
when 0 then 'zero'
when 1 then 'one' end
insert @.MyTable (FirstCol,SecondCol) select @.mynum,FirstName from
BookingsApril2006
RETURN END
Best Regards
/Magnus
"Scott Morris" <bogus@.bogus.com> wrote in message
news:uZhVjdhbGHA.3908@.TK2MSFTNGP02.phx.gbl...
> Please have a look at the syntax used to create functions. You are
> attempting to create an inline function which must not have a function
> body and which must contain a single select query within the returns
> clause. If you want to include logic that cannot be handled within a
> single select statement, then you will need to use the more complex
> table-valued function syntax.
>

Function with EXECUTE problem

Hello,

I'm running into some trouble writing a function that returns a table. I'm using OPENQUERY in the FROM clause to fetch data from a remote server (ORACLE). Because the query must be dynamic, I have to execute it using the EXECUTE command.

Basically, I first dynamically create a string containing my query and then I execute it by calling the EXECUTE function and passing my string as an argument.

Now, I'd like my function to return what's produced by the EXECUTE call. Here's how I wrote the function declaration:

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

CREATE FUNCTION [dbo].[beh_GetRemoteData] (@.STARTDATE varchar(20), @.POS int, @.ID_ANALOG varchar(10))
RETURNS @.RET TABLE (HIST_TIMESTAMP DATETIME, ID_ANALOG INT, STATUT INT, QUALITY INT, VALUE NUMERIC(12,5)) AS
BEGIN
DECLARE @.REMOTEQUERY varchar(300)
DECLARE @.LOCALQUERY varchar(400)

--======== Create remote Query =======--
SET @.REMOTEQUERY = 'SELECT * FROM [...]'


--======== Create local Query =======--

SET @.LOCALQUERY = 'SELECT [...] FROM OPENQUERY(REMOTE_SERVER, ''' + @.REMOTEQUERY + ''')'

INSERT INTO @.RET
EXEC(@.LOCALQUERY)

RETURN
END

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

Of course, this doesn't even passes the "Check syntax" because the EXEC statement cannot be used as as source when inserting into a table variable. This might be but it's EXACTELY what I want to do.

Any ideas on how I should write my function?

Thanks,

Skip.According to my reading of books on line for SQL Server 2000 you can't execute anything other than an extended stored procedure inside a function anyway - and those can't return results sets. Therefore, you can't use dynamic sql either.

Function that returns table

Hello,
Is there any way to write a function where I can write some code and at the end of the code return a entire table as parameter?

This function will return a variable temp table...

GO

SETANSI_NULLSOFF

GO

SETQUOTED_IDENTIFIERON

GO

CREATEFUNCTION [dbo].[UDF_ADMIN_GetNewSortList]

(

@.tText TEXT,

@.iSortStart INT

)

RETURNS @.NewList TABLE

(

iNewSortOrder INT

)

AS

BEGIN

Declare @.tmpList TABLE

(

iOldSortOrder INT

)

INSERTINTO @.tmpList(iOldSortOrder)

SELECT

[cValue]

FROM

[CPDB].[dbo].[udfCharListToTable]

(

@.tText

,','

)

DECLARE @.iCount INT

SELECT @.iCount =Count(*)

FROM @.tmpList

DECLARE @.iStart INT

SET @.iStart = 1

WHILE @.iStart <= @.iCount

BEGIN

INSERTINTO @.NewList(iNewSortOrder)

VALUES(@.iSortStart + @.iStart)

Set @.iStart = @.iStart + 1

END

RETURN

END

|||Yes, but a table with one column. I want to return a table that has 0 or more columns (depends on the application). In your example the function returns a table with only one column (iNewSortOrder).
Now I have:
CREATE PROCEDURE dbo.GetConfiguration
(
@.type varchar(MAX)
)
AS
IF @.type = 'Tank' SELECT * FROM Tank();
That stored procedure returns a table. Now I want to use this table in a Function... eg:
CREATE FUNCTION dbo.Configuration
(
@.type varchar(max)
)
RETURNS TABLE
AS
RETURN SELECT * FROM GetConfiguration @.type
Obviously that doesn't work. Thanks for any ideas!!!
|||"That does not work" answers are welcome, too (so I know that there's no way) ;-)
|||

Looking at the options for Create Function there are two choices that return a table. The first is an in-line function. In this case you do not have to define the columns of the table, but you can only have a single select statement. So, that won't work.

It is possible to have a general multi-statement function that returns a table, but that requires that the columns be known in advance. Assuming that there was value in having a function that had constant columns, but came from different tables -- maybe you have a number of name-value-pair type tables -- you now have another problem of how to populate the table variable. The most general approach would be to use dynamic SQL, but you cannot populate table variables with dynamic SQL. You could use a series of IF statements, but now you are pretty far away from your original goal.

In general, this is not the correct approach to take.

Have you looked into using Dynamic SQL to solve your problem?

Function that returns highest of two columns?

Is there a function that compares two columns in a row and will return
the highest of the two values? Something like:

Acct Total_Dollars Collected Total_Dollars_Due
11233 900.00 1000.00

Declare @.Value as money
set @.Value=GetHighest(Total_Dollars_Collected,TotalDol lars_Due)
Print @.Value

This function will return 1000.00 or the Total_dollars_Due??

Is there such a creature?On 21 Sep 2004 08:41:16 -0700, Philip Mette wrote:

>Is there a function that compares two columns in a row and will return
>the highest of the two values? Something like:
>Acct Total_Dollars Collected Total_Dollars_Due
>11233 900.00 1000.00
>Declare @.Value as money
>set @.Value=GetHighest(Total_Dollars_Collected,TotalDol lars_Due)
>Print @.Value
>
>This function will return 1000.00 or the Total_dollars_Due??
>Is there such a creature?

Hi Philip,

No. But you can create a user-defined function, if you wish. Or simply use
a CASE expression:

SET @.Value = CASE WHEN a > b THEN a ELSE b END

The user-defined function might be more friendly to the eyes. Using the
CASE expression wherever you need it might be a little bit less readable,
but it will probably perform better.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||>> Is there a function that compares two columns in a row and will
return the highest of the two values? <<

in Oracle, there is a general GREATEST(<list>) function, but in
Standard SQL and T-SQL you nedd to use a CASE expression.

Function that returns a table

I have a function that returns a single row table with two columns:

dbo.Fun1(@.param1) : colA and colB

I tried to create a stored procedure that use this function:

select col1, col2, dbo.Fun1(col1) from table1

The result is : Invalid object name 'dbo.Fun1'

There is no join between table1 and Fun1, how can I select the both columns of Fun1 ?

Thanks in advance.

Long

Can u paste the declaration of the function?|||You are trying to use a table-valued UDF like a scalar UDF which is incorrect. You can use a table-valued function only in the FROM clause or as a table source. In any case, what you are trying to do is not possible in SQL Server 2000 since you can only pass variables or constants as parameters to table-valued UDFs. In SQL Server 2005, you can use the APPLY operator to the same.|||

Thanks, Umachandar,

I have to do the selection like this:

select col1, col2, (select colA from dbo.Fun1(col1) ), ( select colB from dbo.Fun1(col1))

from table1

It works, but I'm not satisfied, as it calculates the function twice.

Any other ideas?

Thanks in advance.

Long

|||

Hi,
due to the fact that you have to execute the statement once per row, there is no way to do it ohter than your mentioned way.

Without knowing your Function I would assume that even this is very wacky, because your Return could return more than one value ?! So you have to make sure from your query / ir function that only one row will be returned.

HTH, Jens Suessmeyer.

|||

I don't see how this will work in SQL2000. If you are on SQL2005 then you can simplify the query by using APPLY operator like:

select t.col1, t.col2, f.colA, f.colB

from table1 as t

cross apply dbo.Fun1(t.col1) as f

Function Returns Data Type Error

I am writing my first function and it should be be a very simple one but
I am getting the error:
Server: Msg 245, Level 16, State 1, Procedure InvTypeUSR, Line 9
Syntax error converting the varchar value 'N' to a column of data type int.
Below is the funtion and then the sleect statement that causes the error
----
CREATE FUNCTION InvTypeOther (@.InvoiceID int)
RETURNS varchar(3)
AS
BEGIN
DECLARE @.Type varchar(3)
select @.Type=
(Case InvoiceType.Name
When 'IN' Then 'N'
Else 0
End)
FROM Invoice
INNER JOIN InvoiceType ON Invoice.InvoiceTypeID = InvoiceType.ID
WHERE (Invoice.ID = @.InvoiceID)
Return @.Type
END
--
Select dbo.InvTypeOther(ID) from Invoice where id = 2525Try to replace the line with this:
Else '0'
HTH, Jens Suessmeyer.|||Try to replace the line with this:
Else '0'
HTH, Jens Suessmeyer.|||your CASE expression is using type precedence to try to convert 'N' to
the 0 in the else.
not sure what it should be, but it shouldn't be an int. :)
either '0' or '' perhaps [or null]
Mike Harbinger wrote:
> I am writing my first function and it should be be a very simple one but
> I am getting the error:
> Server: Msg 245, Level 16, State 1, Procedure InvTypeUSR, Line 9
> Syntax error converting the varchar value 'N' to a column of data type int
.
> Below is the funtion and then the sleect statement that causes the error
> ----
> CREATE FUNCTION InvTypeOther (@.InvoiceID int)
> RETURNS varchar(3)
> AS
> BEGIN
> DECLARE @.Type varchar(3)
> select @.Type=
> (Case InvoiceType.Name
> When 'IN' Then 'N'
> Else 0
> End)
> FROM Invoice
> INNER JOIN InvoiceType ON Invoice.InvoiceTypeID = InvoiceType.ID
> WHERE (Invoice.ID = @.InvoiceID)
> Return @.Type
> END
> --
> Select dbo.InvTypeOther(ID) from Invoice where id = 2525
>|||That was it, thanks guys!
I am used to another programming language where numbers do not have to be
quoted when used in string variables.
"Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
news:OyGttQyEGHA.524@.TK2MSFTNGP09.phx.gbl...
>I am writing my first function and it should be be a very simple one but
> I am getting the error:
> Server: Msg 245, Level 16, State 1, Procedure InvTypeUSR, Line 9
> Syntax error converting the varchar value 'N' to a column of data type
> int.
> Below is the funtion and then the sleect statement that causes the error
> ----
> CREATE FUNCTION InvTypeOther (@.InvoiceID int)
> RETURNS varchar(3)
> AS
> BEGIN
> DECLARE @.Type varchar(3)
> select @.Type=
> (Case InvoiceType.Name
> When 'IN' Then 'N'
> Else 0
> End)
> FROM Invoice
> INNER JOIN InvoiceType ON Invoice.InvoiceTypeID = InvoiceType.ID
> WHERE (Invoice.ID = @.InvoiceID)
> Return @.Type
> END
> --
> Select dbo.InvTypeOther(ID) from Invoice where id = 2525
>

Function Return Value

I want to write a function that returns the physical filepath of the master database for its MDF and LDF files respectively. This information will then be used to create a new database in the same location as the master database for those servers that do not have the MDF and LDF files in the default locations.

Below I have the T-SQL for the function created and a test query I am using to test the results. If I print out the value of @.MDF_FILE_PATH within the funtion, I get the result needed. When making a call to the function and printing out the variable, all I get is the first letter of the drive and nothing else.

You may notice that in the function how CHARINDEX is being used. I am not sure why, but if I put a backslash "\" as expression1 within the SELECT statement, I do not get the value of the drive. In other words I get "MSSQL\Data" instead of "D:\MSSQL\Data" I then supply the backslash in the SET statement. I assume that this has something to do with my question.

Any suggestions? Thank you.

HERE IS T-SQL FOR THE FUNCTION
IF OBJECT_ID('fn_sqlmgr_get_mdf_filepath') IS NOT NULL
BEGIN
DROP FUNCTION fn_sqlmgr_get_mdf_filepath
END
GO

CREATE FUNCTION fn_sqlmgr_get_mdf_filepath (
@.MDF_FILE_PATH NVARCHAR(1000) --Variable to hold the path of the MDF File of a database.
)
RETURNS NVARCHAR
AS

BEGIN

--Extract the file path for the database MDF physical file.
SELECT @.MDF_FILE_PATH = SUBSTRING(mdf.filename, CHARINDEX('', filename)+1, LEN(filename))
FROM master..sysfiles mdf
WHERE mdf.groupid = 1

SET @.MDF_FILE_PATH = SUBSTRING(@.MDF_FILE_PATH, 1, LEN(@.MDF_FILE_PATH) - CHARINDEX('\', REVERSE(@.MDF_FILE_PATH)))

RETURN @.MDF_FILE_PATH

END

HERE IS THE TEST I AM USING AGAINST THE FUNCTION
SET NOCOUNT ON

DECLARE
@.MDF_FILE_PATH NVARCHAR(1000) --Variable to hold the path of the MDF File of a database.

SELECT @.MDF_FILE_PATH = dbo.fn_sqlmgr_get_mdf_filepath ( @.MDF_FILE_PATH )
PRINT @.MDF_FILE_PATHAny suggestions?

YEah, rethink what you're doing...

MOO|||If you didn't have any information that would possibly be helpful in my question, please leave future posts to those who would be more intelligent in their responses.

If you see something that I am doing wrong, then why not offer a suggestion instead of either keeping the answer to yourself or acting more intelligent than what you are. After all, this is what this forum is intended for.

Thank you.|||Sorry you feel that way...

If you can supply us with what you're doing, I'm sure the people here can assist...if you don't like what I say, I'm sure someone will step...need more details though...

This information will then be used to create a new database in the same location as the master database for those servers that do not have the MDF and LDF files in the default locations.

Any "AUTO-ADMIN" stuff is always risky...(my own opinion) MOO

Why do you have to do this? Are you releasing hundreds of databases?

Also, unless there are performance issues involved, why deviate from standard practices...

AND HOW DARE YOU ACCUSE ME OF BEING INTELLIGENT!

The nerve...|||CREATE FUNCTION fn_sqlmgr_get_mdf_filepath (
@.MDF_FILE_PATH NVARCHAR(1000) --Variable to hold the path of the MDF File of a database.
)
RETURNS NVARCHAR
AS

The return value of the function is declared as NVARCHAR, a single character. You need to declare the return value as a character array.

RETURNS NVARCHAR(1000)|||USE Northwind
GO

CREATE FUNCTION fn_sqlmgr_get_mdf_filepath (
@.MDF_FILE_PATH NVARCHAR(1000)
)
RETURNS VARCHAR(1000)
AS

BEGIN

SELECT @.MDF_FILE_PATH = SUBSTRING(mdf.filename, CHARINDEX('', filename)+1, LEN(filename))
FROM master..sysfiles mdf
WHERE mdf.groupid = 1

SET @.MDF_FILE_PATH = SUBSTRING(@.MDF_FILE_PATH, 1, LEN(@.MDF_FILE_PATH) - CHARINDEX('\', REVERSE(@.MDF_FILE_PATH)))

RETURN @.MDF_FILE_PATH

END
GO

SET NOCOUNT ON
DECLARE @.MDF_FILE_PATH NVARCHAR(1000) --Variable to hold the path of the MDF File of a database.
SELECT @.MDF_FILE_PATH = dbo.fn_sqlmgr_get_mdf_filepath ( @.MDF_FILE_PATH )
PRINT @.MDF_FILE_PATH
SET NOCOUNT OFF
GO

DROP FUNCTION fn_sqlmgr_get_mdf_filepath
GO|||Yeah .. but in spite of all the help from BK ... you need to rethink it anyway ;)|||I think that Brett saw you doing something potentially VERY dangerous, and was trying to get some more information so that we could either:

a) give you appropriate code
b) help you find a better (safer) solution to your problem
c) warn you that, as cartographers of olde would say: "here be dragons".

-PatP|||I think that Brett saw you doing something potentially [i[VERY[/i] dangerous, and was trying to get some more information so that we could either:

a) give you appropriate code
b) help you find a better (safer) solution to your problem
c) warn you that, as cartographers of olde would say: "here be dragons".

-PatP

You know the funny thing about this?

Trying to build a rocket ship, but can't get past retrun nvarchar|||And i dont remember exactly ... but isnt there a registry key we could read to get the default location where a file would be created ...

Hmmm ... what the hell I am thinking .. the files would be created int the default location anyway ... if you do not specify ... so whats with the function ... i am confused (once again ;))|||Thank you Homer37 for your assistance. Your answer worked just fine.|||Improper setting at the time of server installation leads to situations like this, where there is a need to write extra code. However, it also appears that you ARE trying to auto-create databases (I already picture a scene where something in other parts of your code got missed and you are sitting at a non-responsive server because your code ended up creating databases), unless I am misreading the post. To alter the default location you need to use xp_instance_regwrite rather than for every database to be created to reference the same server, following a call to xp_instance_regread.

Function results

I am trying to convert a string value, from a parameter field, into a date t
o
insert into a SQL2005 db smalldatetime field. The function returns a bit
value, which I test by using the isdate function, and if true convert this t
o
a smalldatetime value, else I set to null.
I get error that "Syntax incorrect at @.test".
What is incorrect about what I am trying to do'
...
DECLARE @.RecNumber bigint,
@.AGM1 smalldatetime,
@.AGM2 smalldatetime,
@.SDt smalldatetime,
@.Test bit
SET NOCOUNT ON;
@.test = dbo.fn_testdate(@.Adv_Met1) ***is something wrong here?
if @.test = 1 then
@.AGM1 = convert(varchar(10),@.Adv_Met1,101)
else
@.AGM1 = null
end if
Any help is appreciated.@.test = dbo.fn_testdate(@.Adv_Met1) ***is something wrong here?
Yes, there is! This is not VBScript, you need to use SET or SELECT
SET @.test = dbo.fn_testdate(@.Adv_Met1);
SELECT @.test = dbo.fn_testdate(@.Adv_Met1);
"sparty1022" <sparty1022@.discussions.microsoft.com> wrote in message
news:ABD935E6-40A8-4F73-9B12-D8B458D824F8@.microsoft.com...
>I am trying to convert a string value, from a parameter field, into a date
>to
> insert into a SQL2005 db smalldatetime field. The function returns a bit
> value, which I test by using the isdate function, and if true convert this
> to
> a smalldatetime value, else I set to null.
> I get error that "Syntax incorrect at @.test".
> What is incorrect about what I am trying to do'
> ...
> DECLARE @.RecNumber bigint,
> @.AGM1 smalldatetime,
> @.AGM2 smalldatetime,
> @.SDt smalldatetime,
> @.Test bit
> SET NOCOUNT ON;
> @.test = dbo.fn_testdate(@.Adv_Met1) ***is something wrong here?
> if @.test = 1 then
> @.AGM1 = convert(varchar(10),@.Adv_Met1,101)
> else
> @.AGM1 = null
> end if
> Any help is appreciated.|||> if @.test = 1 then
> @.AGM1 = convert(varchar(10),@.Adv_Met1,101)
> else
> @.AGM1 = null
> end if
Also a bunch of problems here. I'm going to assume you come from a VB or
VBScript background?
IF @.Test = 1 -- there is no THEN with IF in T-SQL
SET @.AGM1 = ... -- again, need SET or SELECT
ELSE -- this line was okay!
SET @.AGM1 = NULL -- you don't really need to do this though, it is
already NULL!
-- END IF -- there is no such thing as END IF in T-SQL
A more structured way, and my prefered way, if you really needed the ELSE
clause, would be:
IF @.Test = 1
BEGIN
SET @.AGM1 = ...
END
ELSE
BEGIN
SET @.AGM1 = NULL
END
While it makes the code more verbose, I see two benefits:
(1) you really know where the boundaries of the conditional are, unless your
indenting / coding conventions are so bad that a BEGIN/END struct don't
help.
(2) if you need to add more statements to the result of a conditional,
you're already set. I find the lazier syntax leads to unexpected behavior,
not sure if people coming from lisp or cobol expect indentation and white
space to mean more than the code itself, but they can't seem to understand
why the following always prints 'bar' no matter what the value of @.foo:
DECLARE @.foo TINYINT;
SET @.foo = 2;
IF @.foo = 1
PRINT 'foo';
PRINT 'bar'; -- they expect this to run
-- with BEGIN / END it would be much more intuitive
IF @.foo = 2
PRINT 'blat';|||>> I am trying to convert a string value, from a parameter field, into a date to insert int
o a SQL2005 db smalldatetime field [sic] The function returns a bit [sic] value, which I te
st by using the isdate function, and if true [sic] convert this to a smal
ldatetime value, else I set to null. <<
Your whole approach is wrong. Columns are not fields -- nothing alike.
Formatting of data is done in the front end and not in the database.
Good SQL do not write with BIT data since it is too low-level, avoid
BIGINT since it is absurdly large and avoid the proprietary
SMALLDATETIME type. SQL has no BOOLEAN data types. The ISO-11179
Standard prohibits that silly "fn-" affix on name. The use of numbers
in a data element name are a sign of repeating groups and 1NF
violations.
And you are full of syntax and conceptual errors in your effort to
mimic a non-SQL procedural language. Why did you use CONVERT()? To
get a string!! Temporal datatypes do not exist in the 3GL language you
are trying to mimci and you do not understand them. That is too
abstract, so you nee to see a picture.
Why did you use IF-THEN_ELSE constructs? Because you do not understand
CASE expressions. You do not understand declarative programming, so
you avoid it with the proprietary 20+ year old T-SQL 4GL instead.
If you must do this kind of kludging, try something like this:
SET @.agm = CAST (@.adv_met1 AS DATETIME);
Let the CAST() catch the errors then handle them. I also hope you know
about ISO-8601 formats for temporal data.|||It seems like your functions 'should' be doing the test and the conversion a
nd handing you back the date value you desire. Something like this:
CREATE FUNCTION dbo.fn_testdate
( @.StringDateToConvert as varchar(25) )
RETURNS datetime
AS
IF isdate( @.StringDateToConvert )
RETURN convert( datetime, @.StringDateToConvert, 101 )
ELSE
RETURN NULL
GO
This eliminates the entire 'If @.Test' block.
Check in Books on Line for the proper 'Style' value (the 101 above.) Look up
CAST and CONVERT and note the 'Style' indicators.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"sparty1022" <sparty1022@.discussions.microsoft.com> wrote in message news:ABD935E6-40A8-4F7
3-9B12-D8B458D824F8@.microsoft.com...
>I am trying to convert a string value, from a parameter field, into a date
to
> insert into a SQL2005 db smalldatetime field. The function returns a bit
> value, which I test by using the isdate function, and if true convert this
to
> a smalldatetime value, else I set to null.
>
> I get error that "Syntax incorrect at @.test".
>
> What is incorrect about what I am trying to do'
>
> ...
> DECLARE @.RecNumber bigint,
> @.AGM1 smalldatetime,
> @.AGM2 smalldatetime,
> @.SDt smalldatetime,
> @.Test bit
> SET NOCOUNT ON;
>
> @.test = dbo.fn_testdate(@.Adv_Met1) ***is something wrong here?
>
> if @.test = 1 then
> @.AGM1 = convert(varchar(10),@.Adv_Met1,101)
> else
> @.AGM1 = null
> end if
>
> Any help is appreciated.|||>> A more structured way, and my prefered way, if you really needed the ELSE
clause, would be: <<
A more **declarative** way and my prefered way, if you really needed
the ELSE
clause, would be:
SET @.AGM1 = CASE WHEN @.test = 1 THEN .. ELSE NULL END;
Why encourage newbies to mimic a 3GL in SQL when you do not have to?|||Thank you
"Aaron Bertrand [SQL Server MVP]" wrote:

> Also a bunch of problems here. I'm going to assume you come from a VB or
> VBScript background?
> IF @.Test = 1 -- there is no THEN with IF in T-SQL
> SET @.AGM1 = ... -- again, need SET or SELECT
> ELSE -- this line was okay!
> SET @.AGM1 = NULL -- you don't really need to do this though, it is
> already NULL!
> -- END IF -- there is no such thing as END IF in T-SQL
> A more structured way, and my prefered way, if you really needed the ELSE
> clause, would be:
> IF @.Test = 1
> BEGIN
> SET @.AGM1 = ...
> END
> ELSE
> BEGIN
> SET @.AGM1 = NULL
> END
> While it makes the code more verbose, I see two benefits:
> (1) you really know where the boundaries of the conditional are, unless yo
ur
> indenting / coding conventions are so bad that a BEGIN/END struct don't
> help.
> (2) if you need to add more statements to the result of a conditional,
> you're already set. I find the lazier syntax leads to unexpected behavior
,
> not sure if people coming from lisp or cobol expect indentation and white
> space to mean more than the code itself, but they can't seem to understand
> why the following always prints 'bar' no matter what the value of @.foo:
> DECLARE @.foo TINYINT;
> SET @.foo = 2;
> IF @.foo = 1
> PRINT 'foo';
> PRINT 'bar'; -- they expect this to run
> -- with BEGIN / END it would be much more intuitive
> IF @.foo = 2
> PRINT 'blat';
>
>|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1151678883.305430.63220@.p79g2000cwp.googlegroups.com...
> clause, would be: <<
> A more **declarative** way and my prefered way, if you really needed
> the ELSE
> clause, would be:
> SET @.AGM1 = CASE WHEN @.test = 1 THEN .. ELSE NULL END;
> Why encourage newbies to mimic a 3GL in SQL when you do not have to?
Because unlike you, I don't expect them to become SQL gurus overnight. :-)
Let's let them get the syntax figured out, then the optimal way to do
things. Much of this they'll find out on their own, because as you and I
both know, the "experts" don't always agree on the best way to do something.
A

Function for getting a extension from a filename

I need a function that returns the extension of a filename, im not so the T-SQL expert so i wanted to ask if this query is ok?

would it be faster to do this as a CLR function?

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER FUNCTION [dbo].[fn_GetFileExtension]
(
@.Name nvarchar(256)
)

RETURNS nvarchar(256)
AS
BEGIN
IF ( SUBSTRING( @.Name, LEN(@.Name) - 3, 1 ) = '.' )
RETURN LOWER(SUBSTRING( @.Name, LEN(@.Name) - 2, 3 ));

DECLARE @.i int;
SELECT @.i = 1;

WHILE ( @.i < LEN(@.Name) )
BEGIN
IF ( SUBSTRING( @.Name, LEN(@.Name) - @.i, 1 ) = '.' )
RETURN LOWER(SUBSTRING( @.Name, LEN(@.Name) - @.i + 1, @.i ));
ELSE
SELECT @.i = @.i + 1;
END

RETURN '';
END

In .NET, you can use the FileInfo class:

return new FileInfo(filename).Extension;

You need to run some tests to see if that's faster than SQL, though...|||I would recommend that you use some other method personally. If part of the consumer is using .NET or if you are using SQL 2k5 then you have Path.GetExtension (a static method that when provided with a filename returns the extension).
|||i tried the clr way (with Path.GetExtension) and it's the faster solution. it's over 10 times faster than the sp, that's much more than i expected...|||Yep. Glad the problem was solved.

Function call in Insert Statment

Hi

i m trying to call a function in insert statment

Insert Into (value, value1)

Value(@.value, dbo.function(@.value1)

dbo.function returns a value,

when i test the function in querry builder all goes fine.

In my program i become a error

"Parameterized Query '' ' expects parameter @.value1 , which was not supplied."

I m using visual studio , tableadapter.update function to insert datarecords in db

thx for help

Hi,

seems that you only provided 1 paramters within your query statement / parameter collection. The statement expects 2 value / value1 which both have to be supplied, if this is the same paramter you can just use the same name for them

Insert Into (value, value1)

Value(@.value, dbo.function(@.value)) --> There was also a closing parant. missing

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||supply a default value for parameter @.value in the front end. Just in case the function would not return one.

function -> first letter of each word - capital

Hello
I'm looking for a ready to use user function which takes a string as a
argument and returns string containing fist letter of each word as capital.
example:
initail: sUN iS ShINIng
result: Sun Is Shining
The similar role in Oracle has function called INITCAP
Best Regards
Darek T.http://www.devx.com/tips/Tip/17608
Aneesh
"Dariusz Tomon" <d.tomon@.mazars.pl> wrote in message
news:esb8iz2yGHA.3440@.TK2MSFTNGP06.phx.gbl...
> Hello
> I'm looking for a ready to use user function which takes a string as a
> argument and returns string containing fist letter of each word as
> capital.
> example:
> initail: sUN iS ShINIng
> result: Sun Is Shining
> The similar role in Oracle has function called INITCAP
> Best Regards
> Darek T.
>|||If you are using SQL Server 2005 then use the FOR XML enhancements, see my
blog entry:
http://sqlblogcasts.com/blogs/tonyr.../06/20/832.aspx
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"Dariusz Tomon" <d.tomon@.mazars.pl> wrote in message
news:esb8iz2yGHA.3440@.TK2MSFTNGP06.phx.gbl...
> Hello
> I'm looking for a ready to use user function which takes a string as a
> argument and returns string containing fist letter of each word as
> capital.
> example:
> initail: sUN iS ShINIng
> result: Sun Is Shining
> The similar role in Oracle has function called INITCAP
> Best Regards
> Darek T.
>

function -> first letter of each word - capital

Hello
I'm looking for a ready to use user function which takes a string as a
argument and returns string containing fist letter of each word as capital.
example:
initail: sUN iS ShINIng
result: Sun Is Shining
The similar role in Oracle has function called INITCAP
Best Regards
Darek T.http://www.devx.com/tips/Tip/17608
Aneesh
"Dariusz Tomon" <d.tomon@.mazars.pl> wrote in message
news:esb8iz2yGHA.3440@.TK2MSFTNGP06.phx.gbl...
> Hello
> I'm looking for a ready to use user function which takes a string as a
> argument and returns string containing fist letter of each word as
> capital.
> example:
> initail: sUN iS ShINIng
> result: Sun Is Shining
> The similar role in Oracle has function called INITCAP
> Best Regards
> Darek T.
>|||If you are using SQL Server 2005 then use the FOR XML enhancements, see my
blog entry:
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/06/20/832.aspx
--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"Dariusz Tomon" <d.tomon@.mazars.pl> wrote in message
news:esb8iz2yGHA.3440@.TK2MSFTNGP06.phx.gbl...
> Hello
> I'm looking for a ready to use user function which takes a string as a
> argument and returns string containing fist letter of each word as
> capital.
> example:
> initail: sUN iS ShINIng
> result: Sun Is Shining
> The similar role in Oracle has function called INITCAP
> Best Regards
> Darek T.
>

Full-text Search, Query returns empty

Hello,
I'm working on a project using SQL server 2000 full text search. My os is
windows XP professional. (And I also tried to remote
connect to a windows 2000 server computer to do the same thing.) The problem
is same: query returns empty rows.
I followed all the steps from msdn website:Administering Full-Text
Features Using SQL Enterprise
Manager(http://msdn.microsoft.com/library/de...ullad_6g1f.asp).
It goes on well. But after running full population, I checked the properties
of the full-text catalog. It shows item count: 6. Unique Key count: 12.
I think something wrong here. Because I have a table with a data column
name File_data is image datatype. And I wrote a C#.net program to insert
several word doc, pdf, jpg file into the table SearchFile. The number of
unique key count should much bigger than 12.
And when I do the query, e.g.
SELECT File_title, File_data
FROM SearchFile
WHERE CONTAINS (File_data, 'cookies');
It returns empty rows.
This is my design table:
Column Name Data Type length
File_id(PK) int 4
file_type nvarchar 50
file_size nvarchar 50
file_title nvarchar 50
File_data image 16
File-time timestamp 8
This is part of the content of the table:
File_id file_type File_size File_title File_data File_time
8 image/pjpeg 4216 image1.jpg 0xFFD8FFE00010... 0x00000000000000D7
9 application/msword 29696 Introduction to ASP.doc 0xD0CF11E0A1B1...
0x00000000000000D9
10 application/pdf 449473 asp_net_whitepaper.pdf 0x255044462D31...
0x00000000000000DF
11 application/msword 26112 How do I use cookies in ASP.doc ...........
I can insert word,pdf,jpg file into SQL server 2000 database, and
retreive it in my asp.net web application successfully.
I also checked the services list for "Microsoft Search". It already
started.
I checked the log file SQL0001800005.1.gthr under Program
Files\Microsoft SQL Server\MSSQL\FTDATA\SQLServer\GatherLogs.
It shows some error, but I don't know how to handle it.
3/17/2006 4:46:14 PM Add The gatherer has started
3/17/2006 4:46:14 PM Add The initialization has completed
3/17/2006 4:46:26 PM Add Started Full crawl
3/17/2006 4:46:28 PM MSSQL75://SQLServer/2c3393d0/00000013 Add
Error fetching URL, (80040e21 - Multiple-step OLE DB operation generated
errors. Check each OLE DB status value, if available. No work was done. )
Multiple-step OLE DB
operation generated errors. Check each OLE DB status value, if available.
No work was done.
3/17/2006 4:46:28 PM MSSQL75://SQLServer/2c3393d0/00000015 Add
Error fetching URL, (80040e21 - Multiple-step OLE DB operation generated
errors. Check each OLE DB status value, if available. No work was done. )
Multiple-step OLE DB
operation generated errors. Check each OLE DB status value, if available.
No work was done.
3/17/2006 4:46:28 PM MSSQL75://SQLServer/2c3393d0/00000014 Add
Error fetching URL, (80040e21 - Multiple-step OLE DB operation generated
errors. Check each OLE DB status value, if available. No work was done. )
Multiple-step OLE DB
operation generated errors. Check each OLE DB status value, if available.
No work was done.
3/17/2006 4:46:30 PM Add Completed Full crawl
My query for full text search always returns empty. And I'm
sure the string I searched is inside the document.
Can anyone please give me some idea what is wrong here? I really
appreciate any help.
Thanks in advance!
Sincerely,
Sherry
Actually I found out the problem. I should save the file_type just use the
file extention e.g. .doc instead of application/msword.
Thanks,
Sherry
"Sherry" wrote:

> Hello,
> I'm working on a project using SQL server 2000 full text search. My os is
> windows XP professional. (And I also tried to remote
> connect to a windows 2000 server computer to do the same thing.) The problem
> is same: query returns empty rows.
> I followed all the steps from msdn website:Administering Full-Text
> Features Using SQL Enterprise
> Manager(http://msdn.microsoft.com/library/de...ullad_6g1f.asp).
> It goes on well. But after running full population, I checked the properties
> of the full-text catalog. It shows item count: 6. Unique Key count: 12.
> I think something wrong here. Because I have a table with a data column
> name File_data is image datatype. And I wrote a C#.net program to insert
> several word doc, pdf, jpg file into the table SearchFile. The number of
> unique key count should much bigger than 12.
> And when I do the query, e.g.
> SELECT File_title, File_data
> FROM SearchFile
> WHERE CONTAINS (File_data, 'cookies');
> It returns empty rows.
> This is my design table:
> Column Name Data Type length
> File_id(PK) int 4
> file_type nvarchar 50
> file_size nvarchar 50
> file_title nvarchar 50
> File_data image 16
> File-time timestamp 8
> This is part of the content of the table:
> File_id file_type File_size File_title File_data File_time
> 8 image/pjpeg 4216 image1.jpg 0xFFD8FFE00010... 0x00000000000000D7
> 9 application/msword 29696 Introduction to ASP.doc 0xD0CF11E0A1B1...
> 0x00000000000000D9
> 10 application/pdf 449473 asp_net_whitepaper.pdf 0x255044462D31...
> 0x00000000000000DF
> 11 application/msword 26112 How do I use cookies in ASP.doc ...........
> I can insert word,pdf,jpg file into SQL server 2000 database, and
> retreive it in my asp.net web application successfully.
> I also checked the services list for "Microsoft Search". It already
> started.
> I checked the log file SQL0001800005.1.gthr under Program
> Files\Microsoft SQL Server\MSSQL\FTDATA\SQLServer\GatherLogs.
> It shows some error, but I don't know how to handle it.
> 3/17/2006 4:46:14 PM Add The gatherer has started
> 3/17/2006 4:46:14 PM Add The initialization has completed
> 3/17/2006 4:46:26 PM Add Started Full crawl
> 3/17/2006 4:46:28 PM MSSQL75://SQLServer/2c3393d0/00000013 Add
> Error fetching URL, (80040e21 - Multiple-step OLE DB operation generated
> errors. Check each OLE DB status value, if available. No work was done. )
> Multiple-step OLE DB
> operation generated errors. Check each OLE DB status value, if available.
> No work was done.
> 3/17/2006 4:46:28 PM MSSQL75://SQLServer/2c3393d0/00000015 Add
> Error fetching URL, (80040e21 - Multiple-step OLE DB operation generated
> errors. Check each OLE DB status value, if available. No work was done. )
> Multiple-step OLE DB
> operation generated errors. Check each OLE DB status value, if available.
> No work was done.
> 3/17/2006 4:46:28 PM MSSQL75://SQLServer/2c3393d0/00000014 Add
> Error fetching URL, (80040e21 - Multiple-step OLE DB operation generated
> errors. Check each OLE DB status value, if available. No work was done. )
> Multiple-step OLE DB
> operation generated errors. Check each OLE DB status value, if available.
> No work was done.
> 3/17/2006 4:46:30 PM Add Completed Full crawl
> My query for full text search always returns empty. And I'm
> sure the string I searched is inside the document.
> Can anyone please give me some idea what is wrong here? I really
> appreciate any help.
> Thanks in advance!
> Sincerely,
> Sherry

Fulltext search always returns no results.

Hello.
I am having a problem with fulltext search whereby it always returns no
data. I have enabled full text search on the table and successfully
created a catalogue, which according to the event log has been
populated.
I have tested with this query (found in this group) via Query Analyzer:
select FulltextCatalogProperty(N'resourceFile', N'PageID')
Which returns null (which i believe is correct).
The query i am using is:
SELECT * FROM tblPages,
FREETEXTTABLE(tblPages, *,@.searchTerm)searchTable
WHERE [Key] = tblPages.PageID ORDER BY RANK DESC
Which i also believe is correct. Anyone any ideas?
On an unrelated (or possibly related) subject, i also often get this in
my error logs - anyone know how to fix?
17052 : This SQL Server has been optimized for 8 concurrent queries.
This limit has been exceeded by 1 queries and performance may be
adversely affected.
Thanks.
marc
Is this MSDE? SQL FTS is not supported on MSDE,
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Marc" <marc.birkett@.gmail.com> wrote in message
news:1121073540.588455.25420@.z14g2000cwz.googlegro ups.com...
> Hello.
> I am having a problem with fulltext search whereby it always returns no
> data. I have enabled full text search on the table and successfully
> created a catalogue, which according to the event log has been
> populated.
> I have tested with this query (found in this group) via Query Analyzer:
> select FulltextCatalogProperty(N'resourceFile', N'PageID')
> Which returns null (which i believe is correct).
> The query i am using is:
> SELECT * FROM tblPages,
> FREETEXTTABLE(tblPages, *,@.searchTerm)searchTable
> WHERE [Key] = tblPages.PageID ORDER BY RANK DESC
> Which i also believe is correct. Anyone any ideas?
> On an unrelated (or possibly related) subject, i also often get this in
> my error logs - anyone know how to fix?
> 17052 : This SQL Server has been optimized for 8 concurrent queries.
> This limit has been exceeded by 1 queries and performance may be
> adversely affected.
> Thanks.
> marc
>
|||Marc,
Could you post the full output of -- SELECT @.@.version -- as this is most
important information when troubleshooting SQL FTS issues! While I suspect
that you're using SQL Server 7.0, I also need the service pack level that
you have installed.
Because of the error message (17052), you may be hitting the issues in the
following KB articles relative to SQL Server 7.0 Full-text Search:
230036 BUG: Heavy Full Text Query Activity Results in Unexpected Timeout
Errors
http://support.microsoft.com/default...;en-us;Q230036
230103 BUG: Cannot Have More than Eight Full Text Joins and Operations
http://support.microsoft.com/default...;en-us;Q230103
Unfortunately and again assuming that you're using SQL Server 7.0, this
error cannot be fixed as it is by design for SQL Server 7.0, and your only
solution is to upgrade to SQL Server 2000. If you are not using SQL Server
7.0, could you provide more details about other SQL Full-text Search queries
you may have executing on your server?
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Marc" <marc.birkett@.gmail.com> wrote in message
news:1121073540.588455.25420@.z14g2000cwz.googlegro ups.com...
> Hello.
> I am having a problem with fulltext search whereby it always returns no
> data. I have enabled full text search on the table and successfully
> created a catalogue, which according to the event log has been
> populated.
> I have tested with this query (found in this group) via Query Analyzer:
> select FulltextCatalogProperty(N'resourceFile', N'PageID')
> Which returns null (which i believe is correct).
> The query i am using is:
> SELECT * FROM tblPages,
> FREETEXTTABLE(tblPages, *,@.searchTerm)searchTable
> WHERE [Key] = tblPages.PageID ORDER BY RANK DESC
> Which i also believe is correct. Anyone any ideas?
> On an unrelated (or possibly related) subject, i also often get this in
> my error logs - anyone know how to fix?
> 17052 : This SQL Server has been optimized for 8 concurrent queries.
> This limit has been exceeded by 1 queries and performance may be
> adversely affected.
> Thanks.
> marc
>
|||one more point SQL 2005 does interpolation, so this will work
declare @.searchTerm varchar(200)
set @.searchTerm="microsoft"
SELECT * FROM tblPages,
FREETEXTTABLE(tblPages, *,@.searchTerm)searchTable
WHERE [Key] = tblPages.PageID ORDER BY RANK DESC
In previous versions of SQL Server you would have to do something like this
declare @.searchTerm varchar(200)
set @.searchTerm="microsoft"
declare @.searchphase varchar(2000)
select @.searchphrase= "SELECT * FROM tblPages,FREETEXTTABLE(tblPages, *,"
+char(39) +char(34)+ @.searchphrase
select @.searchphrase=@.searchphrase+ char(34)+char(39)+")searchTable WHERE
[Key] = tblPages.PageID ORDER BY RANK DESC"
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Marc" <marc.birkett@.gmail.com> wrote in message
news:1121073540.588455.25420@.z14g2000cwz.googlegro ups.com...
> Hello.
> I am having a problem with fulltext search whereby it always returns no
> data. I have enabled full text search on the table and successfully
> created a catalogue, which according to the event log has been
> populated.
> I have tested with this query (found in this group) via Query Analyzer:
> select FulltextCatalogProperty(N'resourceFile', N'PageID')
> Which returns null (which i believe is correct).
> The query i am using is:
> SELECT * FROM tblPages,
> FREETEXTTABLE(tblPages, *,@.searchTerm)searchTable
> WHERE [Key] = tblPages.PageID ORDER BY RANK DESC
> Which i also believe is correct. Anyone any ideas?
> On an unrelated (or possibly related) subject, i also often get this in
> my error logs - anyone know how to fix?
> 17052 : This SQL Server has been optimized for 8 concurrent queries.
> This limit has been exceeded by 1 queries and performance may be
> adversely affected.
> Thanks.
> marc
>
|||Apologies for the lateness of this reply, ive been a bit busy! the
results of select @.@. version are:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002
14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Personal
Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
Also this is the first time ive tried to use full text search so its
the only fulltext search query on the server.
thanks,
marc
|||Hilary.
Your first query still returns the same, the second returns nothing -
not even column names!
ta
marc
|||I am still not further forward with this, i have reduced my query to:
CREATE PROCEDURE spGetResults1
@.searchTerm1 varchar
AS
SELECT * FROM tblPages
WHERE FREETEXT(*,@.searchTerm1)
GO
and still it returns nothing. It is almost as though my table is empty
(which it isnt). My catalogue is also definately populated. Anyone have
any ideas?
Thanks,
Marc
|||"Marc" <marc.birkett@.gmail.com> wrote in message
news:1121782154.811864.158350@.f14g2000cwb.googlegr oups.com...
>I am still not further forward with this, i have reduced my query to:
> CREATE PROCEDURE spGetResults1
> @.searchTerm1 varchar
> AS
> SELECT * FROM tblPages
> WHERE FREETEXT(*,@.searchTerm1)
> GO
> and still it returns nothing. It is almost as though my table is empty
> (which it isnt). My catalogue is also definately populated. Anyone have
> any ideas?
As Hilary pointed out, that syntax only works in SQL Server 2005 - you have
SQL Server 2000 so you cannot use a variable in the FREETEXT call. Also you
didn't specify the size of @.searchTerm1 so it's set to a 1 character string.
Try this:
CREATE PROCEDURE spGetResults1
@.searchTerm1 varchar(20)
AS
DECLARE @.sql nvarchar(100)
SET @.sql = 'SELECT * FROM STK WHERE FREETEXT(*,' + char(39) + char(34) +
@.searchTerm1 + char(34) + char(39) + ')'
EXEC sp_executesql @.sql
GO
This allows up to 20 characters to be passed in as the search term -
obviously you can increase this as needed. If you increase it by a lot make
sure you increase the size of @.sql too or else you'll have errors caused by
truncating the constructed sql.
Dan
|||Ah. The only problem was i had missed out the size of the @.searchTerm1
variable on the original query. This works:
"CREATE PROCEDURE spGetResults
@.searchTerm varchar(20)
AS
SELECT * FROM tblPages,
FREETEXTTABLE(tblPages, *,@.searchTerm)searchTable
WHERE [Key] = tblPages.PageID ORDER BY RANK DESC
GO"
Thanks for your help people.
Marc

full-text search : noun & verb variatrions

My full-text search works fine and it returns correct results, including
records that contain the variation of the keywords.
I bind the result to a gridview (ASP.NET 2.0), and programatically replace
the matched keywords with a highlighted background etc.
The problem is, my program only highlights the exact keywords, but not the
variations of the keywords. (because I don't know what the latter is)
Is there a way that I can query from SQL server what the noun & verb
variations of a specific search word is?
Thanks.Not without building a lookup table of variations. The algorithm which does
the stemming seems to be an implementation of Porter Stemming algorithm.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"News User" <NewsUser@.newsuser.com> wrote in message
news:OTSoM9gpGHA.148@.TK2MSFTNGP04.phx.gbl...
> My full-text search works fine and it returns correct results, including
> records that contain the variation of the keywords.
> I bind the result to a gridview (ASP.NET 2.0), and programatically replace
> the matched keywords with a highlighted background etc.
> The problem is, my program only highlights the exact keywords, but not the
> variations of the keywords. (because I don't know what the latter is)
> Is there a way that I can query from SQL server what the noun & verb
> variations of a specific search word is?
> Thanks.
>|||"News User" <NewsUser@.newsuser.com> wrote in message
news:OTSoM9gpGHA.148@.TK2MSFTNGP04.phx.gbl...
> My full-text search works fine and it returns correct results, including
> records that contain the variation of the keywords.
> I bind the result to a gridview (ASP.NET 2.0), and programatically replace
> the matched keywords with a highlighted background etc.
> The problem is, my program only highlights the exact keywords, but not the
> variations of the keywords. (because I don't know what the latter is)
> Is there a way that I can query from SQL server what the noun & verb
> variations of a specific search word is?
>
Try asking in news:microsoft.public.sqlserver.fulltext

> Thanks.
>|||Thanks for the great tip. I went to this page:
http://www.tartarus.org/~martin/PorterStemmer/
and found a T-SQL script that I hope I can implement on my SQL server. I
will give it a shot.
THanks!
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%235C3eOhpGHA.3584@.TK2MSFTNGP03.phx.gbl...
> Not without building a lookup table of variations. The algorithm which
> does the stemming seems to be an implementation of Porter Stemming
> algorithm.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> 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
>
> "News User" <NewsUser@.newsuser.com> wrote in message
> news:OTSoM9gpGHA.148@.TK2MSFTNGP04.phx.gbl...
>

full-text search : noun & verb variatrions

My full-text search works fine and it returns correct results, including
records that contain the variation of the keywords.
I bind the result to a gridview (ASP.NET 2.0), and programatically replace
the matched keywords with a highlighted background etc.
The problem is, my program only highlights the exact keywords, but not the
variations of the keywords. (because I don't know what the latter is)
Is there a way that I can query from SQL server what the noun & verb
variations of a specific search word is?
Thanks.Not without building a lookup table of variations. The algorithm which does
the stemming seems to be an implementation of Porter Stemming algorithm.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"News User" <NewsUser@.newsuser.com> wrote in message
news:OTSoM9gpGHA.148@.TK2MSFTNGP04.phx.gbl...
> My full-text search works fine and it returns correct results, including
> records that contain the variation of the keywords.
> I bind the result to a gridview (ASP.NET 2.0), and programatically replace
> the matched keywords with a highlighted background etc.
> The problem is, my program only highlights the exact keywords, but not the
> variations of the keywords. (because I don't know what the latter is)
> Is there a way that I can query from SQL server what the noun & verb
> variations of a specific search word is?
> Thanks.
>|||"News User" <NewsUser@.newsuser.com> wrote in message
news:OTSoM9gpGHA.148@.TK2MSFTNGP04.phx.gbl...
> My full-text search works fine and it returns correct results, including
> records that contain the variation of the keywords.
> I bind the result to a gridview (ASP.NET 2.0), and programatically replace
> the matched keywords with a highlighted background etc.
> The problem is, my program only highlights the exact keywords, but not the
> variations of the keywords. (because I don't know what the latter is)
> Is there a way that I can query from SQL server what the noun & verb
> variations of a specific search word is?
>
Try asking in news:microsoft.public.sqlserver.fulltext
> Thanks.
>|||Thanks for the great tip. I went to this page:
http://www.tartarus.org/~martin/PorterStemmer/
and found a T-SQL script that I hope I can implement on my SQL server. I
will give it a shot.
THanks!
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%235C3eOhpGHA.3584@.TK2MSFTNGP03.phx.gbl...
> Not without building a lookup table of variations. The algorithm which
> does the stemming seems to be an implementation of Porter Stemming
> algorithm.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> 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
>
> "News User" <NewsUser@.newsuser.com> wrote in message
> news:OTSoM9gpGHA.148@.TK2MSFTNGP04.phx.gbl...
>> My full-text search works fine and it returns correct results, including
>> records that contain the variation of the keywords.
>> I bind the result to a gridview (ASP.NET 2.0), and programatically
>> replace the matched keywords with a highlighted background etc.
>> The problem is, my program only highlights the exact keywords, but not
>> the variations of the keywords. (because I don't know what the latter is)
>> Is there a way that I can query from SQL server what the noun & verb
>> variations of a specific search word is?
>> Thanks.
>