function or formula that convert numbers into words

Hello
I am using crystal report 9. i get total of all item purchsed by customer. what is need a any function or formula that convert numbers into words. i.e 32125 should be converted to thirty two thousand one hundred twenty five only.
With regardsCreate a formula and in forumula section (module) there were so many options like functions conversions etc and in that section look for numbers to text option and using that you can achieve what you are looking for.|||I'm not sure about CR9, but in CR10 you can use the ToWords function.

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 Compiling Error 2005(2153)

Hello!

I have scalar function which calls from stored procedure. When SP recompiling (before start) in profiler I see Exception "Error: 536, Severity: 16, State: 5" - Invalid length parameter passed to the SUBSTRING function. at next line of function (original text changed for easy sample) :

if @.p1=4 set @.s1=substring(@.ipstr,1,@.p1-1)

but at same line like:

if @.p1=4 set @.s1=substring(@.ipstr,1,3)

works good.

Somebody can explain such?

I think you should proivide full repro with variables declaration and initialization.

Don't forget to mention MSSQL version you run this script on.

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 - List Windows Group Members

Hello!
I thinking of creating a SQL Server clr function that works like this.
Input paremeter: A Windows group, e.g. Domain1\Group1
Resultset: A list of all accounts that directely or indirectely belongs to
that Windows group.
e.g.
Domain1\Account1
Domain1\Account2
Domain1\Account3
The idea is to use this together with the security catalog views to get a
list of accounts that is for example sysadmins.
Could it be that a function like this exist in SQL Server?
I also thought about what AD permisssions that would be needed?
Best regards
Ola Hallengren
Hi
You can create an ADSI linked server and query the active directory, but
recursing through the groups not always that easy. There is a script to list
all group members on http://www.rlmueller.net/freecode3.htm
John
"Ola Hallengren" wrote:

> Hello!
> I thinking of creating a SQL Server clr function that works like this.
> Input paremeter: A Windows group, e.g. Domain1\Group1
> Resultset: A list of all accounts that directely or indirectely belongs to
> that Windows group.
> e.g.
> Domain1\Account1
> Domain1\Account2
> Domain1\Account3
> The idea is to use this together with the security catalog views to get a
> list of accounts that is for example sysadmins.
> Could it be that a function like this exist in SQL Server?
> I also thought about what AD permisssions that would be needed?
> Best regards
> Ola Hallengren
|||Thanks, John. As I understand it I have about these options.
1. Create an ADSI linked server and query against that with TSQL. This can
be packaged into a SQL Server function.
2. Use the VBScript at http://www.rlmueller.net/freecode3.htm from
xp_cmdshell. This can be packaged into a stored procedure.
3. Create some CLR code with the same logic as in the VBScript and use that
assambly in a SQL Server function.
Is this right?
/Ola
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> You can create an ADSI linked server and query the active directory, but
> recursing through the groups not always that easy. There is a script to list
> all group members on http://www.rlmueller.net/freecode3.htm
> John
> "Ola Hallengren" wrote:
|||I think so, I would opt for either 2 or 3 but have never tried 3!.
John
"Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
news:05FC89C1-3816-41B9-9123-5D9E4FFBEFC0@.microsoft.com...[vbcol=seagreen]
> Thanks, John. As I understand it I have about these options.
> 1. Create an ADSI linked server and query against that with TSQL. This can
> be packaged into a SQL Server function.
> 2. Use the VBScript at http://www.rlmueller.net/freecode3.htm from
> xp_cmdshell. This can be packaged into a stored procedure.
> 3. Create some CLR code with the same logic as in the VBScript and use
> that
> assambly in a SQL Server function.
> Is this right?
> /Ola
>
> "John Bell" wrote:

function

Hello
I use ms sql2000
If i would like to do a function
that gives me a value of "*"
if a specific field is empty
how will i do please helpThis is how it looks

two tables
Activity
Actparttime

i try whith this function

CREATE FUNCTION dbo.Actact (@.ID AS int)
RETURNS varchar(1)
AS
BEGIN
DECLARE @.Aact AS varchar(1)
SELECT DISTINCT @.Aact = "*" WHERE (SELECT dbo.Activity.ID
FROM dbo.Activity INNER JOIN
dbo.Actparttime ON dbo.Activity.ID = dbo.Actparttime.Actid
WHERE (dbo.Actparttime.Actpartend IS NULL) AND (dbo.Activity.ID = @.ID)) >""


RETURN @.Aact
END

and a view

SELECT ID, ActStart, ActEnd, Activity, dbo.Actact(ID) AS Aact
FROM dbo.Activity

i get that subquery returned more than 1 reply

please help|||another way to check:

CREATE FUNCTION dbo.IsEmpty ( @.COL sql_variant )
RETURNS varchar(1)
AS
BEGIN
DECLARE @.Ret AS varchar(1)
SELECT @.Ret = case when convert(varchar,IsNull(@.COL,''))='' then '*' else '' end
RETURN @.Ret
END
go

select dbo.sEmpty( <ColumnName> ), ...|||Thanks

But how do i do when i will have
it sorted on activity table
If there is more than one field i get an error for
many rows|||Originally posted by u31115057
Thanks

But how do i do when i will have
it sorted on activity table
If there is more than one field i get an error for
many rows

something like this ?

CREATE FUNCTION dbo.IsEmpty (
@.COL1 sql_variant ,
@.COL2 sql_variant ,
@.COL3 sql_variant ,
@.COL4 sql_variant ,
@.COL5 sql_variant
) RETURNS varchar(1)
AS BEGIN
DECLARE @.Ret AS varchar(1)
SELECT @.Ret = case when
convert(varchar,IsNull(@.COL1,''))+
convert(varchar,IsNull(@.COL2,''))+
convert(varchar,IsNull(@.COL3,''))+
convert(varchar,IsNull(@.COL4,''))+
convert(varchar,IsNull(@.COL5,''))
='' then '*' else '' end
RETURN @.Ret
END
go

AND

select dbo.IsEmpty( <colname1>, <colname2>, null, null, null ), ...

Full-text search - catalog refuses to populate

Hello

I'm trying to get full-text searching to work on SQL Server 2000 (with SP3 installed).

I have a table containing many text fields that I want to search and so far have successfully gone through the "Full-Text wizard" selecting the text fields from the table I want as searchable. I then try and start full population of the catalog but its always remains zero in size when I check its properties. Hence when I try a query like:

SELECT Name FROM tblTextDocs WHERE CONTAINS(mainbody, 'find this')

It results in the error message:

Server: Msg 7619, Level 16, State 1, Line 1
Execution of a full-text operation failed. The catalog does not exist or is currently unavailable. Please retry the action again later and if this symptom persists, contact the system administrator.

It would appear that the catalog simply refuses to populate. I've tried starting population via Enterprise Manager and also by doing sp_fulltext_catalog 'MyCatalog', 'start_full'. The Full-text support service says it is running correctly and no other problems with the server have been experienced.

Any suggestions would be much appreciated.

Regards

SamBy the "Full Text Support Service" do you mean the "Microsoft Search" service?|||Originally posted by MCrowley
By the "Full Text Support Service" do you mean the "Microsoft Search" service?

Yes - the Microsoft Search service. I have no problems in stopping/starting it too.|||This error might occur if changing SQL Server Account to Non-Admin for Full-Text Search Makes Existing Catalogs Unusable.

To access existing full-text search catalogs, rebuild and repopulate them. The existing catalogs can also be accessed by switching back to an administrator account.