How to setup a fulltext index?

What is the sql syntax for creating a full text index on a table?
I have a table called bookdata with 20 some fields I would like to
index.
Then how do you construct the queries?
Is it something like 'where contains (colname, 'itemtofind')?
http://www.indexserverfaq.com/SQLFTITSQL.htm
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
<sdowney717@.msn.com> wrote in message
news:1110309891.262624.164200@.g14g2000cwa.googlegr oups.com...
> What is the sql syntax for creating a full text index on a table?
> I have a table called bookdata with 20 some fields I would like to
> index.
> Then how do you construct the queries?
> Is it something like 'where contains (colname, 'itemtofind')?
>

How to Set Start Index in a Data Control which is attahced to a Data Component

Hello All,

I have a SQLDataSource "sdsX" which has four records. There is a DataList "dlX" which is binded to this SQLDataSource "sdsX". Now I want that my datalist "dlX" take third record of "sdsX" as its first element. Is there any property which can be used.

Thanks

Hi ShailAtlas,

Base on my understanding, you want to move the third row to first row in DataList. For example:

Here is a DataList rendered likes below:

----------

1 | name1 |

----------

2 | name2 |

----------

3 | name3 |

----------

4 | name4 |

----------

You want to render it likes below without any change of datasource.

----------

3 | name3 |

----------

1 | name1 |

----------

2 | name2 |

----------

4 | name4 |

----------

If I have misunderstood your concern, please feel free to let me know.

DataList doesn't have property for this special request. If you want to implement it, you should add your own code in DataList_PreRender event handler. Here is the sample code:

int i = 0;

protectedvoid Page_Load(object sender,EventArgs e)

{

DataList1.DataBind();

}

protectedvoid Button1_Click(object sender,EventArgs e)

{

i =int.Parse(TextBox1.Text);

}

protectedvoid DataList1_PreRender(object sender,EventArgs e)

{

if (i != 0)

{

int j = i - 1;

string backupid = ((Label)DataList1.Items[j].Controls[1]).Text;

string backupname = ((Label)DataList1.Items[j].Controls[3]).Text;

for (; j > 0; j--)

{

((Label)DataList1.Items[j].Controls[1]).Text = ((Label)DataList1.Items[j-1].Controls[1]).Text;

((Label)DataList1.Items[j].Controls[3]).Text = ((Label)DataList1.Items[j-1].Controls[3]).Text;

}

((Label)DataList1.Items[0].Controls[1]).Text = backupid;

((Label)DataList1.Items[0].Controls[3]).Text = backupname;

}

}

When you input 3 in TextBox1 and then press Button1. DataList begin to render with the special order.

|||

Hello Ben,

Lets understand like this.I have a SQLDataSource which has 4 records. Now I have 2 datalist say DataListX and DataListY.

I want that DataListX should show record number 1 & 2, and DataListY should show records 3 & 4

SQLDataSource has records like this

----------

1 | name1 |

----------

2 | name2 |

----------

3 | name3 |

----------

4 | name4 |

----------

Now DataListX will show like this

----------

1 | name1 |

----------

2 | name2 |

And DataListY should be like this

----------

3 | name3 |

----------

4 | name4 |

Intention is that I do not want to use 2 SQLDataSource for same type of records

Thanks for your Reply

Shail

how to set SQL Server OLE DB provider index option , It gives error ?

All ,

I have created link server and i want to set all appropriate Setting for the Provider option

Provider used : - Microsoft OLE DB provider for SQL server
After setting “Index as Access Path” check box to true I have encountered
Following error

Server: Msg 7319, Level 16, State 1, Procedure Jobs, Line 2OLE DB provider 'SQLOLEDB' returned a 'NON-CLUSTERED and NOT INTEGRATED' index 'IX_T_Jobs' with incorrect bookmark ordinal 0.OLE DB error trace [Non-interface error: OLE/DB provider returned an invalid bookmark ordinal from the index rowset.].

Note :- Remote Query Icon Show 98%

Please let me know how to fix the problem and how to make sure distributed query Uses the proper index on remote Link server

Regards,
RahulB

microsoft ole db provider for sql server is not an index provider. it won't be able use index as access path from the remote sql server.

|||

I am facing the same issue. However, i don't much care which index on the oracle box a query uses...i just want the data. How do i avoid this error?

To test things, i took the table on oracle that was generating this error and duplicated it (with data) but without any indexes, and the data returns successfully.

Obviously the idea of removing indexes from oracle tables so the SQL Server can link-server connect to them is not reasonable. My method of connection is the 4-part method. Open query works even on the table...but i don't want to be forced to use that technique.

Here are the two queries...(Query 1 fails, Query 2 works):

1. SELECT * FROM ORCLPAO..SERVICE.BI_EVENT
2. SELECT TOP 10 * FROM OPENQUERY (orclpao, 'select * from service.bi_event')

Query 1 generates this error:

Server: Msg 7319, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' returned a 'NON-CLUSTERED and NOT INTEGRATED' index 'EVENT_APTNUM_XS' with incorrect bookmark ordinal 0.
OLE DB error trace [Non-interface error: OLE/DB provider returned an invalid bookmark ordinal from the index rowset.].

how to set SQL Server OLE DB provider index option , It gives error ?

All ,

I have created link server and i want to set all appropriate Setting for the Provider option

Provider used : - Microsoft OLE DB provider for SQL server
After setting “Index as Access Path” check box to true I have encountered
Following error

Server: Msg 7319, Level 16, State 1, Procedure Jobs, Line 2OLE DB provider 'SQLOLEDB' returned a 'NON-CLUSTERED and NOT INTEGRATED' index 'IX_T_Jobs' with incorrect bookmark ordinal 0.OLE DB error trace [Non-interface error: OLE/DB provider returned an invalid bookmark ordinal from the index rowset.].

Note :- Remote Query Icon Show 98%

Please let me know how to fix the problem and how to make sure distributed query Uses the proper index on remote Link server

Regards,
RahulB

microsoft ole db provider for sql server is not an index provider. it won't be able use index as access path from the remote sql server.

|||

I am facing the same issue. However, i don't much care which index on the oracle box a query uses...i just want the data. How do i avoid this error?

To test things, i took the table on oracle that was generating this error and duplicated it (with data) but without any indexes, and the data returns successfully.

Obviously the idea of removing indexes from oracle tables so the SQL Server can link-server connect to them is not reasonable. My method of connection is the 4-part method. Open query works even on the table...but i don't want to be forced to use that technique.

Here are the two queries...(Query 1 fails, Query 2 works):

1. SELECT * FROM ORCLPAO..SERVICE.BI_EVENT
2. SELECT TOP 10 * FROM OPENQUERY (orclpao, 'select * from service.bi_event')

Query 1 generates this error:

Server: Msg 7319, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' returned a 'NON-CLUSTERED and NOT INTEGRATED' index 'EVENT_APTNUM_XS' with incorrect bookmark ordinal 0.
OLE DB error trace [Non-interface error: OLE/DB provider returned an invalid bookmark ordinal from the index rowset.].

How to set ANSI_NULLS on on a existing table?

Hi,
I was trying to create an index on the indexed view, when I got this
error
Msg 1935 : Cannot create index.Object 'tablename' was created with the
following SET option off: ANSI_NULLS.
Is there anyway inside the database to set ANSI_NULLS on for the
existing table?
Thanks a lot for your help.
AJ> Is there anyway inside the database to set ANSI_NULLS on for the
> existing table?
You'll need to recreate the table with SET ANSI_NULL ON.
Hope this helps.
Dan Guzman
SQL Server MVP
<aj70000@.hotmail.com> wrote in message
news:1144964972.095350.55480@.t31g2000cwb.googlegroups.com...
> Hi,
> I was trying to create an index on the indexed view, when I got this
> error
> Msg 1935 : Cannot create index.Object 'tablename' was created with the
> following SET option off: ANSI_NULLS.
> Is there anyway inside the database to set ANSI_NULLS on for the
> existing table?
> Thanks a lot for your help.
> AJ
>|||Hi Dan,
Thanks for the reply. that is a bummer since I have atleast 12 tables
that i need to use for indexed view. and these tables contain financial
information.
AJ|||aj70...@.hotmail.com wrote:
> Hi Dan,
> Thanks for the reply. that is a bummer since I have atleast 12 tables
> that i need to use for indexed view. and these tables contain financial
> information.
> AJ
Were your tables created with ANSI_NULLS OFF by design or due to
ignorance? Unfortunately, ignorance is the usual reason. Take care
always to have ANSI_NULLS set to ON when creating tables or other
objects.
Note that Enterprise Manager defaults to ANSI_NULLS OFF when it's
installed. Those people who create tables with Enterprise Manager often
forget to change that setting first. Your procs and other objects may
suffer the same problem if you create them in EM. The smartest option
is to avoid EM altogether. Don't use EM to create objects.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

How to set ANSI_NULLS on on a existing table?

Hi,
I was trying to create an index on the indexed view, when I got this
error
Msg 1935 : Cannot create index.Object 'tablename' was created with the
following SET option off: ANSI_NULLS.
Is there anyway inside the database to set ANSI_NULLS on for the
existing table?
Thanks a lot for your help.
AJ> Is there anyway inside the database to set ANSI_NULLS on for the
> existing table?
You'll need to recreate the table with SET ANSI_NULL ON.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<aj70000@.hotmail.com> wrote in message
news:1144964972.095350.55480@.t31g2000cwb.googlegroups.com...
> Hi,
> I was trying to create an index on the indexed view, when I got this
> error
> Msg 1935 : Cannot create index.Object 'tablename' was created with the
> following SET option off: ANSI_NULLS.
> Is there anyway inside the database to set ANSI_NULLS on for the
> existing table?
> Thanks a lot for your help.
> AJ
>|||Hi Dan,
Thanks for the reply. that is a bummer since I have atleast 12 tables
that i need to use for indexed view. and these tables contain financial
information.
AJ|||aj70...@.hotmail.com wrote:
> Hi Dan,
> Thanks for the reply. that is a bummer since I have atleast 12 tables
> that i need to use for indexed view. and these tables contain financial
> information.
> AJ
Were your tables created with ANSI_NULLS OFF by design or due to
ignorance? Unfortunately, ignorance is the usual reason. Take care
always to have ANSI_NULLS set to ON when creating tables or other
objects.
Note that Enterprise Manager defaults to ANSI_NULLS OFF when it's
installed. Those people who create tables with Enterprise Manager often
forget to change that setting first. Your procs and other objects may
suffer the same problem if you create them in EM. The smartest option
is to avoid EM altogether. Don't use EM to create objects.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--