How to create indexes for views

Hi all,
It may be a stupid question but I'd be very happy if anyone could tell
my how to create an index on a view (creating an index on a table
works fine and is easy to do).
I have installed MS SQL Server 2000 running on a Windows 2000 machine.
Thanks in advance,
EdPlease read the page titled "Creating an Indexed View" for more information
on creating indexes on views. There are certain requirements to be met.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"Eddie" <e.beyer@.gmx.net> wrote in message
news:29d450bb.0310020627.489ee1ae@.posting.google.com...
Hi all,
It may be a stupid question but I'd be very happy if anyone could tell
my how to create an index on a view (creating an index on a table
works fine and is easy to do).
I have installed MS SQL Server 2000 running on a Windows 2000 machine.
Thanks in advance,
Ed|||I'd start by typing 'indexed views' into the keywords to find section of the
index in Books Online. You'll find a lot of information that will help you
get started and things you need to be aware of. Let us know if you have any
specific questions after reading some of the basics... I hope this helps!
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Eddie" <e.beyer@.gmx.net> wrote in message
news:29d450bb.0310020627.489ee1ae@.posting.google.com...
> Hi all,
> It may be a stupid question but I'd be very happy if anyone could tell
> my how to create an index on a view (creating an index on a table
> works fine and is easy to do).
> I have installed MS SQL Server 2000 running on a Windows 2000 machine.
>
> Thanks in advance,
> Ed|||Hi Ed
Indexed views work best when the underlying data is
infrequently updated. The maintenance of an indexed view
can be higher than the cost of maintaining a table index.
If the underlying data is updated frequently, then the
cost of maintaining the indexed view data may outweigh the
performance benefits of using the indexed view.
There is a lot more info. in Books Online that I would
consider reading.
>--Original Message--
>Hi all,
>It may be a stupid question but I'd be very happy if
anyone could tell
>my how to create an index on a view (creating an index on
a table
>works fine and is easy to do).
>I have installed MS SQL Server 2000 running on a Windows
2000 machine.
>
>Thanks in advance,
>Ed
>.
>

how to create index (Case when)

How to create index when there is a SQL statement like
Select count(1) as [Total],
sum(Case when Field1 < Field2 then 1 else 0 End) as [Selected]
from Table1
Thx in Adv
XLDBUse one index on each column ( field 1 field 2 ) ...use nonclustered for each

however, if table is small ( say less than 1000 row s ) SQL probably wont use indexes..it will scan whole table.|||the table has 2 million records|||actually, there are 2 similar tables that i use the query on...

the table with 2 million records is taking 57 seconds
the table with 40,000 records is taking 1 second

there is only 1 index on "Field1" in "Case When" statement

How to Create Index

If I run query mostly on column expressions, like I have two columns in table
one for date and other is for time, when i want to build index, then i build
index on both columns, but will these separate index work when i write query
with this expression,
where convert(varchar(10),saleDate,121) + ' ' +
right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
both columns have data type of datetime.
My question is, will the separate indexes of both columns will be utilized?
Or what is the best practice, when you are searching on expressions in
queries? not on just simply column.
The expression is not sargable because you are applying functions to the
column value. If your data contains the default values for the time and
date components (i.e. '1900-01-01' and '00:00:00.000'), try:
WHERE
saleDate = '20050101' AND
saleTime = '11:30:00'
Hope this helps.
Dan Guzman
SQL Server MVP
"Imran" <Imran@.discussions.microsoft.com> wrote in message
news:D59B0606-9A7B-4B09-973B-6B7E59C7A135@.microsoft.com...
> If I run query mostly on column expressions, like I have two columns in
> table
> one for date and other is for time, when i want to build index, then i
> build
> index on both columns, but will these separate index work when i write
> query
> with this expression,
> where convert(varchar(10),saleDate,121) + ' ' +
> right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
> both columns have data type of datetime.
> My question is, will the separate indexes of both columns will be
> utilized?
> Or what is the best practice, when you are searching on expressions in
> queries? not on just simply column.
>
>
|||This (convert(varchar(23),saletime,121) will cause a table scan
Doesn't saletime inlude the date? why is this in 2 fields?
you could use where date > and time > (2 conditions)
http://sqlservercode.blogspot.com/
"Imran" wrote:

> If I run query mostly on column expressions, like I have two columns in table
> one for date and other is for time, when i want to build index, then i build
> index on both columns, but will these separate index work when i write query
> with this expression,
> where convert(varchar(10),saleDate,121) + ' ' +
> right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
> both columns have data type of datetime.
> My question is, will the separate indexes of both columns will be utilized?
> Or what is the best practice, when you are searching on expressions in
> queries? not on just simply column.
>
>
|||sorry my question was, how to create index, as i want to create index on some
expression and when i provide same expression in query then sql server should
automatically search on that expression.
like if my search expression is usually is
left(citycode,5) = '12345'
then i should build index on expression
left(citycode,5)
not on column citycode, because i know that i will never use citycode in
search but i must use left(citycode,5) in search and sql server auto detect
that if expression exists for given expression then i search in index of that
expr
"Dan Guzman" wrote:

> The expression is not sargable because you are applying functions to the
> column value. If your data contains the default values for the time and
> date components (i.e. '1900-01-01' and '00:00:00.000'), try:
> WHERE
> saleDate = '20050101' AND
> saleTime = '11:30:00'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Imran" <Imran@.discussions.microsoft.com> wrote in message
> news:D59B0606-9A7B-4B09-973B-6B7E59C7A135@.microsoft.com...
>
>
|||sorry my question was, how to create index, as i want to create index on some
expression and when i provide same expression in query then sql server should
automatically search on that expression.
like if my search expression is usually is
left(citycode,5) = '12345'
then i should build index on expression
left(citycode,5)
not on column citycode, because i know that i will never use citycode in
search but i must use left(citycode,5) in search and sql server auto detect
that if expression exists for given expression then i search in index of that
expr
"SQL" wrote:
[vbcol=seagreen]
> This (convert(varchar(23),saletime,121) will cause a table scan
> Doesn't saletime inlude the date? why is this in 2 fields?
> you could use where date > and time > (2 conditions)
> http://sqlservercode.blogspot.com/
> "Imran" wrote:
|||You would have to create a computed column containing that expression and then index that computed
column. Or, create view with the expression and index the view (note that optimizer will only use
indexes on view by itself if you have enterprise edition).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Imran" <Imran@.discussions.microsoft.com> wrote in message
news:7D3B3CD5-BD21-4C32-8B1F-AE6AA9832C49@.microsoft.com...[vbcol=seagreen]
> sorry my question was, how to create index, as i want to create index on some
> expression and when i provide same expression in query then sql server should
> automatically search on that expression.
> like if my search expression is usually is
> left(citycode,5) = '12345'
> then i should build index on expression
> left(citycode,5)
> not on column citycode, because i know that i will never use citycode in
> search but i must use left(citycode,5) in search and sql server auto detect
> that if expression exists for given expression then i search in index of that
> expr
>
> "Dan Guzman" wrote:
|||Imran,
SQL-Server does not have the feature to index expressions. You can only
index one or more columns. Although you could work around this by
creating a computed column and index this column, it will (probably) not
be used automatically if your query specifies "left(citycode,5) =
'12345'".
But it would be so much simpler to rewrite your predicate to "citycode
LIKE '12345%'". This basically does the same, and it will almost
certainly use an index on column citycode.
HTH,
Gert-Jan
Imran wrote:[vbcol=seagreen]
> sorry my question was, how to create index, as i want to create index on some
> expression and when i provide same expression in query then sql server should
> automatically search on that expression.
> like if my search expression is usually is
> left(citycode,5) = '12345'
> then i should build index on expression
> left(citycode,5)
> not on column citycode, because i know that i will never use citycode in
> search but i must use left(citycode,5) in search and sql server auto detect
> that if expression exists for given expression then i search in index of that
> expr
> "Dan Guzman" wrote:

How to Create Index

If I run query mostly on column expressions, like I have two columns in table
one for date and other is for time, when i want to build index, then i build
index on both columns, but will these separate index work when i write query
with this expression,
where convert(varchar(10),saleDate,121) + ' ' +
right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
both columns have data type of datetime.
My question is, will the separate indexes of both columns will be utilized'
Or what is the best practice, when you are searching on expressions in
queries? not on just simply column.The expression is not sargable because you are applying functions to the
column value. If your data contains the default values for the time and
date components (i.e. '1900-01-01' and '00:00:00.000'), try:
WHERE
saleDate = '20050101' AND
saleTime = '11:30:00'
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Imran" <Imran@.discussions.microsoft.com> wrote in message
news:D59B0606-9A7B-4B09-973B-6B7E59C7A135@.microsoft.com...
> If I run query mostly on column expressions, like I have two columns in
> table
> one for date and other is for time, when i want to build index, then i
> build
> index on both columns, but will these separate index work when i write
> query
> with this expression,
> where convert(varchar(10),saleDate,121) + ' ' +
> right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
> both columns have data type of datetime.
> My question is, will the separate indexes of both columns will be
> utilized'
> Or what is the best practice, when you are searching on expressions in
> queries? not on just simply column.
>
>|||This (convert(varchar(23),saletime,121) will cause a table scan
Doesn't saletime inlude the date? why is this in 2 fields?
you could use where date > and time > (2 conditions)
http://sqlservercode.blogspot.com/
"Imran" wrote:
> If I run query mostly on column expressions, like I have two columns in table
> one for date and other is for time, when i want to build index, then i build
> index on both columns, but will these separate index work when i write query
> with this expression,
> where convert(varchar(10),saleDate,121) + ' ' +
> right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
> both columns have data type of datetime.
> My question is, will the separate indexes of both columns will be utilized'
> Or what is the best practice, when you are searching on expressions in
> queries? not on just simply column.
>
>|||sorry my question was, how to create index, as i want to create index on some
expression and when i provide same expression in query then sql server should
automatically search on that expression.
like if my search expression is usually is
left(citycode,5) = '12345'
then i should build index on expression
left(citycode,5)
not on column citycode, because i know that i will never use citycode in
search but i must use left(citycode,5) in search and sql server auto detect
that if expression exists for given expression then i search in index of that
expr
"Dan Guzman" wrote:
> The expression is not sargable because you are applying functions to the
> column value. If your data contains the default values for the time and
> date components (i.e. '1900-01-01' and '00:00:00.000'), try:
> WHERE
> saleDate = '20050101' AND
> saleTime = '11:30:00'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Imran" <Imran@.discussions.microsoft.com> wrote in message
> news:D59B0606-9A7B-4B09-973B-6B7E59C7A135@.microsoft.com...
> > If I run query mostly on column expressions, like I have two columns in
> > table
> > one for date and other is for time, when i want to build index, then i
> > build
> > index on both columns, but will these separate index work when i write
> > query
> > with this expression,
> > where convert(varchar(10),saleDate,121) + ' ' +
> > right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
> > both columns have data type of datetime.
> > My question is, will the separate indexes of both columns will be
> > utilized'
> > Or what is the best practice, when you are searching on expressions in
> > queries? not on just simply column.
> >
> >
> >
>
>|||sorry my question was, how to create index, as i want to create index on some
expression and when i provide same expression in query then sql server should
automatically search on that expression.
like if my search expression is usually is
left(citycode,5) = '12345'
then i should build index on expression
left(citycode,5)
not on column citycode, because i know that i will never use citycode in
search but i must use left(citycode,5) in search and sql server auto detect
that if expression exists for given expression then i search in index of that
expr
"SQL" wrote:
> This (convert(varchar(23),saletime,121) will cause a table scan
> Doesn't saletime inlude the date? why is this in 2 fields?
> you could use where date > and time > (2 conditions)
> http://sqlservercode.blogspot.com/
> "Imran" wrote:
> > If I run query mostly on column expressions, like I have two columns in table
> > one for date and other is for time, when i want to build index, then i build
> > index on both columns, but will these separate index work when i write query
> > with this expression,
> > where convert(varchar(10),saleDate,121) + ' ' +
> > right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
> > both columns have data type of datetime.
> > My question is, will the separate indexes of both columns will be utilized'
> > Or what is the best practice, when you are searching on expressions in
> > queries? not on just simply column.
> >
> >
> >|||You would have to create a computed column containing that expression and then index that computed
column. Or, create view with the expression and index the view (note that optimizer will only use
indexes on view by itself if you have enterprise edition).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Imran" <Imran@.discussions.microsoft.com> wrote in message
news:7D3B3CD5-BD21-4C32-8B1F-AE6AA9832C49@.microsoft.com...
> sorry my question was, how to create index, as i want to create index on some
> expression and when i provide same expression in query then sql server should
> automatically search on that expression.
> like if my search expression is usually is
> left(citycode,5) = '12345'
> then i should build index on expression
> left(citycode,5)
> not on column citycode, because i know that i will never use citycode in
> search but i must use left(citycode,5) in search and sql server auto detect
> that if expression exists for given expression then i search in index of that
> expr
>
> "Dan Guzman" wrote:
>> The expression is not sargable because you are applying functions to the
>> column value. If your data contains the default values for the time and
>> date components (i.e. '1900-01-01' and '00:00:00.000'), try:
>> WHERE
>> saleDate = '20050101' AND
>> saleTime = '11:30:00'
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Imran" <Imran@.discussions.microsoft.com> wrote in message
>> news:D59B0606-9A7B-4B09-973B-6B7E59C7A135@.microsoft.com...
>> > If I run query mostly on column expressions, like I have two columns in
>> > table
>> > one for date and other is for time, when i want to build index, then i
>> > build
>> > index on both columns, but will these separate index work when i write
>> > query
>> > with this expression,
>> > where convert(varchar(10),saleDate,121) + ' ' +
>> > right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
>> > both columns have data type of datetime.
>> > My question is, will the separate indexes of both columns will be
>> > utilized'
>> > Or what is the best practice, when you are searching on expressions in
>> > queries? not on just simply column.
>> >
>> >
>> >
>>|||Imran,
SQL-Server does not have the feature to index expressions. You can only
index one or more columns. Although you could work around this by
creating a computed column and index this column, it will (probably) not
be used automatically if your query specifies "left(citycode,5) ='12345'".
But it would be so much simpler to rewrite your predicate to "citycode
LIKE '12345%'". This basically does the same, and it will almost
certainly use an index on column citycode.
HTH,
Gert-Jan
Imran wrote:
> sorry my question was, how to create index, as i want to create index on some
> expression and when i provide same expression in query then sql server should
> automatically search on that expression.
> like if my search expression is usually is
> left(citycode,5) = '12345'
> then i should build index on expression
> left(citycode,5)
> not on column citycode, because i know that i will never use citycode in
> search but i must use left(citycode,5) in search and sql server auto detect
> that if expression exists for given expression then i search in index of that
> expr
> "Dan Guzman" wrote:
> > The expression is not sargable because you are applying functions to the
> > column value. If your data contains the default values for the time and
> > date components (i.e. '1900-01-01' and '00:00:00.000'), try:
> >
> > WHERE
> > saleDate = '20050101' AND
> > saleTime = '11:30:00'
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "Imran" <Imran@.discussions.microsoft.com> wrote in message
> > news:D59B0606-9A7B-4B09-973B-6B7E59C7A135@.microsoft.com...
> > > If I run query mostly on column expressions, like I have two columns in
> > > table
> > > one for date and other is for time, when i want to build index, then i
> > > build
> > > index on both columns, but will these separate index work when i write
> > > query
> > > with this expression,
> > > where convert(varchar(10),saleDate,121) + ' ' +
> > > right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
> > > both columns have data type of datetime.
> > > My question is, will the separate indexes of both columns will be
> > > utilized'
> > > Or what is the best practice, when you are searching on expressions in
> > > queries? not on just simply column.
> > >
> > >
> > >
> >
> >
> >sql

How to Create Index

If I run query mostly on column expressions, like I have two columns in tabl
e
one for date and other is for time, when i want to build index, then i build
index on both columns, but will these separate index work when i write query
with this expression,
where convert(varchar(10),saleDate,121) + ' ' +
right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
both columns have data type of datetime.
My question is, will the separate indexes of both columns will be utilized'
Or what is the best practice, when you are searching on expressions in
queries? not on just simply column.The expression is not sargable because you are applying functions to the
column value. If your data contains the default values for the time and
date components (i.e. '1900-01-01' and '00:00:00.000'), try:
WHERE
saleDate = '20050101' AND
saleTime = '11:30:00'
Hope this helps.
Dan Guzman
SQL Server MVP
"Imran" <Imran@.discussions.microsoft.com> wrote in message
news:D59B0606-9A7B-4B09-973B-6B7E59C7A135@.microsoft.com...
> If I run query mostly on column expressions, like I have two columns in
> table
> one for date and other is for time, when i want to build index, then i
> build
> index on both columns, but will these separate index work when i write
> query
> with this expression,
> where convert(varchar(10),saleDate,121) + ' ' +
> right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
> both columns have data type of datetime.
> My question is, will the separate indexes of both columns will be
> utilized'
> Or what is the best practice, when you are searching on expressions in
> queries? not on just simply column.
>
>|||This (convert(varchar(23),saletime,121) will cause a table scan
Doesn't saletime inlude the date? why is this in 2 fields?
you could use where date > and time > (2 conditions)
http://sqlservercode.blogspot.com/
"Imran" wrote:

> If I run query mostly on column expressions, like I have two columns in ta
ble
> one for date and other is for time, when i want to build index, then i bui
ld
> index on both columns, but will these separate index work when i write que
ry
> with this expression,
> where convert(varchar(10),saleDate,121) + ' ' +
> right(convert(varchar(23),saletime,121) ,13) > '2005-01-01 11:30:00.000'
> both columns have data type of datetime.
> My question is, will the separate indexes of both columns will be utilized
'
> Or what is the best practice, when you are searching on expressions in
> queries? not on just simply column.
>
>|||sorry my question was, how to create index, as i want to create index on som
e
expression and when i provide same expression in query then sql server shoul
d
automatically search on that expression.
like if my search expression is usually is
left(citycode,5) = '12345'
then i should build index on expression
left(citycode,5)
not on column citycode, because i know that i will never use citycode in
search but i must use left(citycode,5) in search and sql server auto detect
that if expression exists for given expression then i search in index of tha
t
expr
"Dan Guzman" wrote:

> The expression is not sargable because you are applying functions to the
> column value. If your data contains the default values for the time and
> date components (i.e. '1900-01-01' and '00:00:00.000'), try:
> WHERE
> saleDate = '20050101' AND
> saleTime = '11:30:00'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Imran" <Imran@.discussions.microsoft.com> wrote in message
> news:D59B0606-9A7B-4B09-973B-6B7E59C7A135@.microsoft.com...
>
>|||sorry my question was, how to create index, as i want to create index on som
e
expression and when i provide same expression in query then sql server shoul
d
automatically search on that expression.
like if my search expression is usually is
left(citycode,5) = '12345'
then i should build index on expression
left(citycode,5)
not on column citycode, because i know that i will never use citycode in
search but i must use left(citycode,5) in search and sql server auto detect
that if expression exists for given expression then i search in index of tha
t
expr
"SQL" wrote:
[vbcol=seagreen]
> This (convert(varchar(23),saletime,121) will cause a table scan
> Doesn't saletime inlude the date? why is this in 2 fields?
> you could use where date > and time > (2 conditions)
> http://sqlservercode.blogspot.com/
> "Imran" wrote:
>|||You would have to create a computed column containing that expression and th
en index that computed
column. Or, create view with the expression and index the view (note that op
timizer will only use
indexes on view by itself if you have enterprise edition).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Imran" <Imran@.discussions.microsoft.com> wrote in message
news:7D3B3CD5-BD21-4C32-8B1F-AE6AA9832C49@.microsoft.com...[vbcol=seagreen]
> sorry my question was, how to create index, as i want to create index on s
ome
> expression and when i provide same expression in query then sql server sho
uld
> automatically search on that expression.
> like if my search expression is usually is
> left(citycode,5) = '12345'
> then i should build index on expression
> left(citycode,5)
> not on column citycode, because i know that i will never use citycode in
> search but i must use left(citycode,5) in search and sql server auto detec
t
> that if expression exists for given expression then i search in index of t
hat
> expr
>
> "Dan Guzman" wrote:
>|||Imran,
SQL-Server does not have the feature to index expressions. You can only
index one or more columns. Although you could work around this by
creating a computed column and index this column, it will (probably) not
be used automatically if your query specifies "left(citycode,5) =
'12345'".
But it would be so much simpler to rewrite your predicate to "citycode
LIKE '12345%'". This basically does the same, and it will almost
certainly use an index on column citycode.
HTH,
Gert-Jan
Imran wrote:[vbcol=seagreen]
> sorry my question was, how to create index, as i want to create index on s
ome
> expression and when i provide same expression in query then sql server sho
uld
> automatically search on that expression.
> like if my search expression is usually is
> left(citycode,5) = '12345'
> then i should build index on expression
> left(citycode,5)
> not on column citycode, because i know that i will never use citycode in
> search but i must use left(citycode,5) in search and sql server auto detec
t
> that if expression exists for given expression then i search in index of t
hat
> expr
> "Dan Guzman" wrote:
>

How to create full text index by SQL Server Express?

Hi all..

I tried too much to create FULL TEXT INDEX by using SQL Server 2005 Management Studio Express, it returns this ERR MSG:

Informational: No full-text supported languages found.

Informational: No full-text supported languages found.

Msg 7680, Level 16, State 1, Line 1

Default full-text index language is not a language supported by full-text search

This problem dos not come when I use Microsoft SQL Server 2005 Management Studio, to create FULL TEXT INDEX!

My DB collation is: "Arabic_CI_AS"

But I don’t need this, I can use English Language.

Please, what can I do?

Full Text Indexing is not available in the 'basic' SQL Server 2005 Express.

You must download and install SQL Server 2005 Express Edition with Advanced Services.

Overview

Microsoft SQL Server 2005 Express Edition with Advanced Services (SQL Server Express) is a new, free, easy-to-use version of SQL Server Express that includes a new graphical management tool and powerful features for reporting and advanced text-based searches. SQL Server Express provides powerful and reliable data management tools and rich features, data protection, and fast performance. It is ideal for embedded application clients, light Web applications, and local data stores.

SQL Server Express with Advanced Services has all of the features in SQL Server 2005 Express Edition, plus you can:

Easily manage and administer SQL Server Express with a new easy-to-use graphical management tool -- SQL Server 2005 Management Studio Express (SSMSE).

Issue full-text queries against plain character-based data in SQL Server tables. Full-text queries can include words and phrases, or multiple forms of a word or phrase.

Run SQL Server Reporting Services reports on local relational data.|||

Of course I'm using sql server with advanced edition, and this problem appears only with it, you may see this URL:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1109200&SiteID=1

In that URL there is no correct answer, so he changed his mind, and as I think, decided to use SQL- Server 2005 Trial instead of (SQL SERVER EXPRESS ADV).

However; as we (me & my friend Ibrahim) understood this ERR message:

Informational: No full-text supported languages found.

Informational: No full-text supported languages found.

Msg 7680, Level 16, State 1, Line 1

Default full-text index language is not a language supported by full-text search

My friend thought that the correct question is about: how to add languages to support full-text, so he wrote the other question as a correction to this one, not as a repetition.

Thank you very much.

|||

See the follwing links for clarification:

default full-text language Option
"The default value of this option is the language of the server."
http://msdn2.microsoft.com/en-us/library/ms180854.aspx

Setting Server Configuration Options
http://msdn2.microsoft.com/en-us/library/ms189631.aspx

sys.fulltext_languages (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms176076.aspx

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi…

When I execute this query:

select * from sys.fulltext_languages

It returns 0 rows, nothing…

Also when I tried to update sys.fulltext_languages or sys.configurations by using UPDATE or sp_configure it returns this msg:

Ad hoc update to system catalogs is not supported

Also when I tried to enable 'allow updates' it returns same error msg.

Any way, thank you so much…

I will buy SQL Server 2005 Developer edition.

Thank you.

sql

How to create file using field from a table.

I am using SQL 8.0 and have the challenge of writing text files from a table.
My table contains three fields, file name, file data, and an index. I need to
create a file for every row that uses the 1st field as the file name, which
is already in the correct format [filename.txt] and I need the value from the
second column, filed data, to be the text within the text file.
Can anyone assit me in creating these files? I cannot find anything to
assist me with this in the online books nor MSDN.
You can use this demo code as a start:
-- OJ: TEXTCOPY example
-- Loading files into db &
-- exporting files out to folder
--
--TEXTCOPY IN
--create tb to hold data
create table tmp(fname varchar(100),img image default '0x0')
go
declare @.sql varchar(255),
@.fname varchar(100),
@.path varchar(50),
@.user sysname,
@.pass sysname
set @.user='myuser'
set @.pass='mypass'
--specify desired folder
set @.path='c:\winnt\'
set @.sql='dir ' + @.path + '*.bmp /c /b'
--insert filenames into tb
insert tmp(fname)
exec master..xp_cmdshell @.sql
--loop through and insert file contents into tb
declare cc cursor
for select fname from tmp
open cc
fetch next from cc into @.fname
while @.@.fetch_status=0
begin
set @.sql='textcopy /s"'+@.@.servername+'" /u"'+@.user+'" /p"'+@.pass+'"
/d"'+db_name()+'" /t"tmp" /c"img" /w"where fname=''' + @.fname + '''"'
set @.sql=@.sql + ' /f"' + @.path + @.fname + '" /i' + ' /z'
print @.sql
exec master..xp_cmdshell @.sql ,no_output
fetch next from cc into @.fname
end
close cc
deallocate cc
go
select * from tmp
go
--TEXTCOPY OUT
declare @.sql varchar(255),
@.fname varchar(100),
@.path varchar(50),
@.user sysname,
@.pass sysname
set @.user='myuser'
set @.pass='mypass,'
--specify desired output folder
set @.path='c:\tmp\'
set @.sql='md ' + @.path
--create output folder
exec master..xp_cmdshell @.sql
--loop through and insert file contents into tb
declare cc cursor
for select fname from tmp
open cc
fetch next from cc into @.fname
while @.@.fetch_status=0
begin
set @.sql='textcopy /s"'+@.@.servername+'" /u"'+@.user+'" /p"'+@.pass+'"
/d"'+db_name()+'" /t"tmp" /c"img" /w"where fname=''' + @.fname + '''"'
set @.sql=@.sql + ' /f"' + @.path + @.fname + '" /o' + ' /z'
print @.sql
exec master..xp_cmdshell @.sql ,no_output
fetch next from cc into @.fname
end
close cc
deallocate cc
set @.sql='dir ' + @.path + '*.bmp /c /b'
exec master..xp_cmdshell @.sql
go
drop table tmp
go
"Dorian Foster" <DorianFoster@.discussions.microsoft.com> wrote in message
news:94D63419-1F96-4437-A07E-C293141D84C8@.microsoft.com...
> I am using SQL 8.0 and have the challenge of writing text files from a
table.
> My table contains three fields, file name, file data, and an index. I need
to
> create a file for every row that uses the 1st field as the file name,
which
> is already in the correct format [filename.txt] and I need the value from
the
> second column, filed data, to be the text within the text file.
> Can anyone assit me in creating these files? I cannot find anything to
> assist me with this in the online books nor MSDN.
|||Refer to Books Online; search for "Write File Transformation"
"Dorian Foster" wrote:

> I am using SQL 8.0 and have the challenge of writing text files from a table.
> My table contains three fields, file name, file data, and an index. I need to
> create a file for every row that uses the 1st field as the file name, which
> is already in the correct format [filename.txt] and I need the value from the
> second column, filed data, to be the text within the text file.
> Can anyone assit me in creating these files? I cannot find anything to
> assist me with this in the online books nor MSDN.

How to create file using field from a table.

I am using SQL 8.0 and have the challenge of writing text files from a table
.
My table contains three fields, file name, file data, and an index. I need t
o
create a file for every row that uses the 1st field as the file name, which
is already in the correct format [filename.txt] and I need the value fro
m the
second column, filed data, to be the text within the text file.
Can anyone assit me in creating these files? I cannot find anything to
assist me with this in the online books nor MSDN.You can use this demo code as a start:
-- OJ: TEXTCOPY example
-- Loading files into db &
-- exporting files out to folder
--
--TEXTCOPY IN
--
--create tb to hold data
create table tmp(fname varchar(100),img image default '0x0')
go
declare @.sql varchar(255),
@.fname varchar(100),
@.path varchar(50),
@.user sysname,
@.pass sysname
set @.user='myuser'
set @.pass='mypass'
--specify desired folder
set @.path='c:\winnt'
set @.sql='dir ' + @.path + '*.bmp /c /b'
--insert filenames into tb
insert tmp(fname)
exec master..xp_cmdshell @.sql
--loop through and insert file contents into tb
declare cc cursor
for select fname from tmp
open cc
fetch next from cc into @.fname
while @.@.fetch_status=0
begin
set @.sql='textcopy /s"'+@.@.servername+'" /u"'+@.user+'" /p"'+@.pass+'"
/d"'+db_name()+'" /t"tmp" /c"img" /w"where fname=''' + @.fname + '''"'
set @.sql=@.sql + ' /f"' + @.path + @.fname + '" /i' + ' /z'
print @.sql
exec master..xp_cmdshell @.sql ,no_output
fetch next from cc into @.fname
end
close cc
deallocate cc
go
select * from tmp
go
--
--TEXTCOPY OUT
--
declare @.sql varchar(255),
@.fname varchar(100),
@.path varchar(50),
@.user sysname,
@.pass sysname
set @.user='myuser'
set @.pass='mypass,'
--specify desired output folder
set @.path='c:\tmp'
set @.sql='md ' + @.path
--create output folder
exec master..xp_cmdshell @.sql
--loop through and insert file contents into tb
declare cc cursor
for select fname from tmp
open cc
fetch next from cc into @.fname
while @.@.fetch_status=0
begin
set @.sql='textcopy /s"'+@.@.servername+'" /u"'+@.user+'" /p"'+@.pass+'"
/d"'+db_name()+'" /t"tmp" /c"img" /w"where fname=''' + @.fname + '''"'
set @.sql=@.sql + ' /f"' + @.path + @.fname + '" /o' + ' /z'
print @.sql
exec master..xp_cmdshell @.sql ,no_output
fetch next from cc into @.fname
end
close cc
deallocate cc
set @.sql='dir ' + @.path + '*.bmp /c /b'
exec master..xp_cmdshell @.sql
go
drop table tmp
go
"Dorian Foster" <DorianFoster@.discussions.microsoft.com> wrote in message
news:94D63419-1F96-4437-A07E-C293141D84C8@.microsoft.com...
> I am using SQL 8.0 and have the challenge of writing text files from a
table.
> My table contains three fields, file name, file data, and an index. I need
to
> create a file for every row that uses the 1st field as the file name,
which
> is already in the correct format [filename.txt] and I need the value from[/vbc
ol]
the[vbcol=seagreen]
> second column, filed data, to be the text within the text file.
> Can anyone assit me in creating these files? I cannot find anything to
> assist me with this in the online books nor MSDN.|||Refer to Books Online; search for "Write File Transformation"
"Dorian Foster" wrote:

> I am using SQL 8.0 and have the challenge of writing text files from a tab
le.
> My table contains three fields, file name, file data, and an index. I need
to
> create a file for every row that uses the 1st field as the file name, whic
h
> is already in the correct format [filename.txt] and I need the value f
rom the
> second column, filed data, to be the text within the text file.
> Can anyone assit me in creating these files? I cannot find anything to
> assist me with this in the online books nor MSDN.

How to create file using field from a table.

I am using SQL 8.0 and have the challenge of writing text files from a table.
My table contains three fields, file name, file data, and an index. I need to
create a file for every row that uses the 1st field as the file name, which
is already in the correct format [filename.txt] and I need the value from the
second column, filed data, to be the text within the text file.
Can anyone assit me in creating these files? I cannot find anything to
assist me with this in the online books nor MSDN.You can use this demo code as a start:
-- OJ: TEXTCOPY example
-- Loading files into db &
-- exporting files out to folder
--
--
--TEXTCOPY IN
--
--create tb to hold data
create table tmp(fname varchar(100),img image default '0x0')
go
declare @.sql varchar(255),
@.fname varchar(100),
@.path varchar(50),
@.user sysname,
@.pass sysname
set @.user='myuser'
set @.pass='mypass'
--specify desired folder
set @.path='c:\winnt\'
set @.sql='dir ' + @.path + '*.bmp /c /b'
--insert filenames into tb
insert tmp(fname)
exec master..xp_cmdshell @.sql
--loop through and insert file contents into tb
declare cc cursor
for select fname from tmp
open cc
fetch next from cc into @.fname
while @.@.fetch_status=0
begin
set @.sql='textcopy /s"'+@.@.servername+'" /u"'+@.user+'" /p"'+@.pass+'"
/d"'+db_name()+'" /t"tmp" /c"img" /w"where fname=''' + @.fname + '''"'
set @.sql=@.sql + ' /f"' + @.path + @.fname + '" /i' + ' /z'
print @.sql
exec master..xp_cmdshell @.sql ,no_output
fetch next from cc into @.fname
end
close cc
deallocate cc
go
select * from tmp
go
--
--TEXTCOPY OUT
--
declare @.sql varchar(255),
@.fname varchar(100),
@.path varchar(50),
@.user sysname,
@.pass sysname
set @.user='myuser'
set @.pass='mypass,'
--specify desired output folder
set @.path='c:\tmp\'
set @.sql='md ' + @.path
--create output folder
exec master..xp_cmdshell @.sql
--loop through and insert file contents into tb
declare cc cursor
for select fname from tmp
open cc
fetch next from cc into @.fname
while @.@.fetch_status=0
begin
set @.sql='textcopy /s"'+@.@.servername+'" /u"'+@.user+'" /p"'+@.pass+'"
/d"'+db_name()+'" /t"tmp" /c"img" /w"where fname=''' + @.fname + '''"'
set @.sql=@.sql + ' /f"' + @.path + @.fname + '" /o' + ' /z'
print @.sql
exec master..xp_cmdshell @.sql ,no_output
fetch next from cc into @.fname
end
close cc
deallocate cc
set @.sql='dir ' + @.path + '*.bmp /c /b'
exec master..xp_cmdshell @.sql
go
drop table tmp
go
"Dorian Foster" <DorianFoster@.discussions.microsoft.com> wrote in message
news:94D63419-1F96-4437-A07E-C293141D84C8@.microsoft.com...
> I am using SQL 8.0 and have the challenge of writing text files from a
table.
> My table contains three fields, file name, file data, and an index. I need
to
> create a file for every row that uses the 1st field as the file name,
which
> is already in the correct format [filename.txt] and I need the value from
the
> second column, filed data, to be the text within the text file.
> Can anyone assit me in creating these files? I cannot find anything to
> assist me with this in the online books nor MSDN.|||Refer to Books Online; search for "Write File Transformation"
"Dorian Foster" wrote:
> I am using SQL 8.0 and have the challenge of writing text files from a table.
> My table contains three fields, file name, file data, and an index. I need to
> create a file for every row that uses the 1st field as the file name, which
> is already in the correct format [filename.txt] and I need the value from the
> second column, filed data, to be the text within the text file.
> Can anyone assit me in creating these files? I cannot find anything to
> assist me with this in the online books nor MSDN.

How to correct index problems in system tables

Hi friends, I have one corrupted index in sysobjects from
one user database (not master). The SQL Server don't
accept to use DBCC or drop and recreate the index to
system tables. In my backup the index alread is corrupted.
How can I fix this problem? I'm using SQL 2000 with the
last SP.DBCC DBREINDEX would help in rebuilding the corrupted index. I notice you
say the SQL Server doesn't accept DBCC though.
Why is this..? Permissions .. ?
--
HTH
Ryan Waight, MCDBA, MCSE
"Khayman" <himura@.click21.com.br> wrote in message
news:08d101c3b997$f77eabe0$a401280a@.phx.gbl...
> Hi friends, I have one corrupted index in sysobjects from
> one user database (not master). The SQL Server don't
> accept to use DBCC or drop and recreate the index to
> system tables. In my backup the index alread is corrupted.
> How can I fix this problem? I'm using SQL 2000 with the
> last SP.|||Hi ,
Please use the below undocumented system procedure This stored procedure can
be used to fix a corruption in a system table.
sp_fixindex dbname,tabname,indid
Before using this stored procedure the database has to be in single user
mode
See this link for more information: "How can I fix a corruption in a system
table?"
http://www.windows2000faq.com/Articles/Index.cfm?ArticleID=14051
Thanks
Hari
MCDBA
"Khayman" <himura@.click21.com.br> wrote in message
news:08d101c3b997$f77eabe0$a401280a@.phx.gbl...
> Hi friends, I have one corrupted index in sysobjects from
> one user database (not master). The SQL Server don't
> accept to use DBCC or drop and recreate the index to
> system tables. In my backup the index alread is corrupted.
> How can I fix this problem? I'm using SQL 2000 with the
> last SP.|||i have tried this yet.
This only works for nonclustered indexes, but the
corrupted index is clustered.
Khayman.
>--Original Message--
>Hi ,
>Please use the below undocumented system procedure This
stored procedure can
>be used to fix a corruption in a system table.
>sp_fixindex dbname,tabname,indid
>Before using this stored procedure the database has to be
in single user
>mode
>See this link for more information: "How can I fix a
corruption in a system
>table?"
>http://www.windows2000faq.com/Articles/Index.cfm?
ArticleID=14051
>Thanks
>Hari
>MCDBA
>
>"Khayman" <himura@.click21.com.br> wrote in message
>news:08d101c3b997$f77eabe0$a401280a@.phx.gbl...
>> Hi friends, I have one corrupted index in sysobjects
from
>> one user database (not master). The SQL Server don't
>> accept to use DBCC or drop and recreate the index to
>> system tables. In my backup the index alread is
corrupted.
>> How can I fix this problem? I'm using SQL 2000 with the
>> last SP.
>
>.
>|||In Microsoft Documentation says:
"DBCC DBREINDEX is not supported for use on system tables."
I don't know why.
Khayman
>--Original Message--
>DBCC DBREINDEX would help in rebuilding the corrupted
index. I notice you
>say the SQL Server doesn't accept DBCC though.
>Why is this..? Permissions .. ?
>--
>HTH
>Ryan Waight, MCDBA, MCSE
>"Khayman" <himura@.click21.com.br> wrote in message
>news:08d101c3b997$f77eabe0$a401280a@.phx.gbl...
>> Hi friends, I have one corrupted index in sysobjects
from
>> one user database (not master). The SQL Server don't
>> accept to use DBCC or drop and recreate the index to
>> system tables. In my backup the index alread is
corrupted.
>> How can I fix this problem? I'm using SQL 2000 with the
>> last SP.
>
>.
>|||Please contact Product Support who are best placed to help you with this.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Khayman" <himura@.click21.com.br> wrote in message
news:000101c3b9b7$53f1dfe0$a301280a@.phx.gbl...
> i have tried this yet.
> This only works for nonclustered indexes, but the
> corrupted index is clustered.
> Khayman.
>
> >--Original Message--
> >Hi ,
> >
> >Please use the below undocumented system procedure This
> stored procedure can
> >be used to fix a corruption in a system table.
> >
> >sp_fixindex dbname,tabname,indid
> >
> >Before using this stored procedure the database has to be
> in single user
> >mode
> >
> >See this link for more information: "How can I fix a
> corruption in a system
> >table?"
> >
> >http://www.windows2000faq.com/Articles/Index.cfm?
> ArticleID=14051
> >
> >Thanks
> >Hari
> >MCDBA
> >
> >
> >"Khayman" <himura@.click21.com.br> wrote in message
> >news:08d101c3b997$f77eabe0$a401280a@.phx.gbl...
> >> Hi friends, I have one corrupted index in sysobjects
> from
> >> one user database (not master). The SQL Server don't
> >> accept to use DBCC or drop and recreate the index to
> >> system tables. In my backup the index alread is
> corrupted.
> >> How can I fix this problem? I'm using SQL 2000 with the
> >> last SP.
> >
> >
> >.
> >|||We have the same issue. Did you resolve your problem? How did it go?