I'm creating a DPE to accept a serialized dataset as input to my
report at runtime. But I can't get the parameters working correctly
in VB. I assume the reporting service has some mechanism to set the
parameters, but what is it?
I have the readonly property Parameters that implements
IDbCommand.Parameters. But, it's readonly. So how does the property
get a value to start with?
ThanksIf the extension implements the required interfaces, RS will call the
parameter collection (IDataParameterCollection) Add method automatically
when the report is processed. As a prerequisite, the parameter has to be
declared as a query-level parameter. If it is only report-level param, it
won't be passed.
The sample data extension that comes with RS should get you started. I would
suggest you debug the extension to learn how it works. The easiest way to do
this is to add the extension to your RS solution.
Accidentally, I implemented a custom data extension to support reporting of
serialized or saved to files ADO.NET datasets that you may find useful.
http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B8468707-56EF-4864-AC51-D83FC3273FE5
--
Hope this helps.
---
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
http://www.prologika.com
"Steven" <montgoss@.hotmail.com> wrote in message
news:a73f4c07.0408060918.60dc6eb4@.posting.google.com...
> I'm creating a DPE to accept a serialized dataset as input to my
> report at runtime. But I can't get the parameters working correctly
> in VB. I assume the reporting service has some mechanism to set the
> parameters, but what is it?
> I have the readonly property Parameters that implements
> IDbCommand.Parameters. But, it's readonly. So how does the property
> get a value to start with?
> Thanks|||"Teo Lachev" <teo@.nospam.prologika.com> wrote in message news:<eh9vH1#eEHA.3428@.TK2MSFTNGP11.phx.gbl>...
> As a prerequisite, the parameter has to be
> declared as a query-level parameter. If it is only report-level param, it
> won't be passed.
I want to make sure I understand this. This means that I can't simply
define the parameter in the report designer. I have to also define it
in my DPE? And I do this via the IDbCommandAnalysis.GetParameters
implementation? Or is that simply for prompting the user at design
time for parameter?
Thx.|||Steven,
Please find my comments inline?
--
Hope this helps.
---
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
http://www.prologika.com
"Steven" <montgoss@.hotmail.com> wrote in message
news:a73f4c07.0408090515.8688bff@.posting.google.com...
> "Teo Lachev" <teo@.nospam.prologika.com> wrote in message
news:<eh9vH1#eEHA.3428@.TK2MSFTNGP11.phx.gbl>...
> > As a prerequisite, the parameter has to be
> > declared as a query-level parameter. If it is only report-level param,
it
> > won't be passed.
> I want to make sure I understand this. This means that I can't simply
> define the parameter in the report designer.
Teo: No, what I was saying is that the parameter has to be defined as a
query (dataset)-level perameter in order to be passed to the custom database
extension. Just defining a parameter as a report-level parameter (Reports
menu->Parameters) is not enough.
I have to also define it
> in my DPE?
Teo: You don't define your parameters in the custom database extension.
Instead, RS (report designer during design time, Report Server during
runtime) will pass them to your extension.
And I do this via the IDbCommandAnalysis.GetParameters
> implementation? Or is that simply for prompting the user at design
> time for parameter?
Teo: Yes, IDbCommandAnalysis is optional and it is useful only during design
time to prompt the report author for parameters. For example, if you may use
a query statement like this:
select * from ... where given_column = @.some_value
In IDbCommandAnalysis.GetParameters you can parse the query statement,
discover that it contains a parameter called some_value and create a new
parameters collection and pass it back. The Report Designer then will
display the parameter collection.
> Thx.|||Thanks for your help so far.
Do you know why your sample doesn't automatically populate the fields
toolbox with values like the FSI sample does?|||Steven,
It should. Moreover, if the dataset has an XML Schema included, the custom
dataset extension should also expose the type of the fields. Did you look at
the readme file accompanying the extension for configuration instructions?
--
Hope this helps.
---
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
http://www.prologika.com
"Steven" <montgoss@.hotmail.com> wrote in message
news:a73f4c07.0408091207.2613bc07@.posting.google.com...
> Thanks for your help so far.
> Do you know why your sample doesn't automatically populate the fields
> toolbox with values like the FSI sample does?|||Yes, I looked at the readme. I get to the point after executing the
query where it says the fields box should now be populated, and it
isn't... This is something my DPE did automatically, but I don't see
how it did it or why yours doesn't seem to.
Just now, I clicked the "..." next to the dataset list and I got an
error. "Could not generate a list of fields for the query. Check the
query syntax or click Refresh Fields on the query toolbar. The path is
not of a legal form". Sure enough, after I clicked "Refresh Fields",
it populated the fields box. Any idea why it doesn't refresh fields
automatically? It seems like the illegal path is a reference to the
@.DataSource param which wasn't yet set(since I hadn't executed the
query).
Thanks.
"Teo Lachev" <teo@.nospam.prologika.com> wrote in message news:<#wNp8AlfEHA.2764@.TK2MSFTNGP11.phx.gbl>...
> Steven,
> It should. Moreover, if the dataset has an XML Schema included, the custom
> dataset extension should also expose the type of the fields. Did you look at
> the readme file accompanying the extension for configuration instructions?
> --
> Hope this helps.
> ---
> Teo Lachev, MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> http://www.prologika.com
>
> "Steven" <montgoss@.hotmail.com> wrote in message
> news:a73f4c07.0408091207.2613bc07@.posting.google.com...
> > Thanks for your help so far.
> >
> > Do you know why your sample doesn't automatically populate the fields
> > toolbox with values like the FSI sample does?|||Do you specify Nothing as a query text or the ADO.NET table name?
If yes, please send me your serialized ADO.NET dataset (remove nospam from
my e-mail address). I will be happy to look at it.
--
Hope this helps.
---
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
http://www.prologika.com
"Steven" <montgoss@.hotmail.com> wrote in message
news:a73f4c07.0408100558.50830c01@.posting.google.com...
> Yes, I looked at the readme. I get to the point after executing the
> query where it says the fields box should now be populated, and it
> isn't... This is something my DPE did automatically, but I don't see
> how it did it or why yours doesn't seem to.
> Just now, I clicked the "..." next to the dataset list and I got an
> error. "Could not generate a list of fields for the query. Check the
> query syntax or click Refresh Fields on the query toolbar. The path is
> not of a legal form". Sure enough, after I clicked "Refresh Fields",
> it populated the fields box. Any idea why it doesn't refresh fields
> automatically? It seems like the illegal path is a reference to the
> @.DataSource param which wasn't yet set(since I hadn't executed the
> query).
> Thanks.
> "Teo Lachev" <teo@.nospam.prologika.com> wrote in message
news:<#wNp8AlfEHA.2764@.TK2MSFTNGP11.phx.gbl>...
> > Steven,
> >
> > It should. Moreover, if the dataset has an XML Schema included, the
custom
> > dataset extension should also expose the type of the fields. Did you
look at
> > the readme file accompanying the extension for configuration
instructions?
> >
> > --
> > Hope this helps.
> >
> > ---
> > Teo Lachev, MCSD, MCT
> > Author: "Microsoft Reporting Services in Action"
> > http://www.prologika.com
> >
> >
> > "Steven" <montgoss@.hotmail.com> wrote in message
> > news:a73f4c07.0408091207.2613bc07@.posting.google.com...
> > > Thanks for your help so far.
> > >
> > > Do you know why your sample doesn't automatically populate the fields
> > > toolbox with values like the FSI sample does?|||Yes, I was using "Nothing". I also tried "SalesOrderHeader" with the
same result. I'm using the EntitySalesOrder.xsd from your sample when
doing this.(I assume I don't need to send that to you)
Is it maybe some configuration error on my machine?
Thanks,
Steven
"Teo Lachev" <teo@.nospam.prologika.com> wrote in message news:<ukBywqufEHA.904@.TK2MSFTNGP09.phx.gbl>...
> Do you specify Nothing as a query text or the ADO.NET table name?
> If yes, please send me your serialized ADO.NET dataset (remove nospam from
> my e-mail address). I will be happy to look at it.
> --
> Hope this helps.
> ---
> Teo Lachev, MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> http://www.prologika.com|||So, when you run the query in the Generic Query Designer what columns do you
see in the results grid?
--
Hope this helps.
---
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
http://www.prologika.com
"Steven" <montgoss@.hotmail.com> wrote in message
news:a73f4c07.0408110902.4a0007c3@.posting.google.com...
> Yes, I was using "Nothing". I also tried "SalesOrderHeader" with the
> same result. I'm using the EntitySalesOrder.xsd from your sample when
> doing this.(I assume I don't need to send that to you)
> Is it maybe some configuration error on my machine?
> Thanks,
> Steven
> "Teo Lachev" <teo@.nospam.prologika.com> wrote in message
news:<ukBywqufEHA.904@.TK2MSFTNGP09.phx.gbl>...
> > Do you specify Nothing as a query text or the ADO.NET table name?
> >
> > If yes, please send me your serialized ADO.NET dataset (remove nospam
from
> > my e-mail address). I will be happy to look at it.
> >
> > --
> > Hope this helps.
> >
> > ---
> > Teo Lachev, MCSD, MCT
> > Author: "Microsoft Reporting Services in Action"
> > http://www.prologika.com|||SalesOrderID, CustomerID, PurchaseOrderNumber and OrderDate.
Steven
"Teo Lachev" <teo@.nospam.prologika.com> wrote in message news:<uL2dV18fEHA.2984@.tk2msftngp13.phx.gbl>...
> So, when you run the query in the Generic Query Designer what columns do you
> see in the results grid?
> --
> Hope this helps.
> ---
> Teo Lachev, MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> http://www.prologika.com|||When you click the Refresh Fields button on the Data tab toolbar do you see
the same fields?
--
Hope this helps.
---
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
http://www.prologika.com
"Steven" <montgoss@.hotmail.com> wrote in message
news:a73f4c07.0408120920.70ea1d42@.posting.google.com...
> SalesOrderID, CustomerID, PurchaseOrderNumber and OrderDate.
> Steven
> "Teo Lachev" <teo@.nospam.prologika.com> wrote in message
news:<uL2dV18fEHA.2984@.tk2msftngp13.phx.gbl>...
> > So, when you run the query in the Generic Query Designer what columns do
you
> > see in the results grid?
> >
> > --
> > Hope this helps.
> >
> > ---
> > Teo Lachev, MCSD, MCT
> > Author: "Microsoft Reporting Services in Action"
> > http://www.prologika.com|||Yes, and then they also show up in the fields box.
"Teo Lachev" <teo@.nospam.prologika.com> wrote in message news:<OTdWdVJgEHA.3964@.TK2MSFTNGP12.phx.gbl>...
> When you click the Refresh Fields button on the Data tab toolbar do you see
> the same fields?
> --
> Hope this helps.
> ---
> Teo Lachev, MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> http://www.prologika.com
>
> "Steven" <montgoss@.hotmail.com> wrote in message
> news:a73f4c07.0408120920.70ea1d42@.posting.google.com...
> > SalesOrderID, CustomerID, PurchaseOrderNumber and OrderDate.
> >
> > Steven
> >
> > "Teo Lachev" <teo@.nospam.prologika.com> wrote in message
> news:<uL2dV18fEHA.2984@.tk2msftngp13.phx.gbl>...
> > > So, when you run the query in the Generic Query Designer what columns do
> you
> > > see in the results grid?
> > >
> > > --
> > > Hope this helps.
> > >
> > > ---
> > > Teo Lachev, MCSD, MCT
> > > Author: "Microsoft Reporting Services in Action"
> > > http://www.prologika.com|||I get the same behavior with my custom DPE. I have an ADO.net dataset. The
designer shows the columns, but the fields list is empty until I click the
refresh fields button. My dataset is named "NewDataSet" and my table is
named "ResultTable". Is the table name supposed to be set to Nothing?
Was there resolution to this thread?
-David
"Steven" wrote:
> Yes, and then they also show up in the fields box.
> "Teo Lachev" <teo@.nospam.prologika.com> wrote in message news:<OTdWdVJgEHA.3964@.TK2MSFTNGP12.phx.gbl>...
> > When you click the Refresh Fields button on the Data tab toolbar do you see
> > the same fields?
> >
> > --
> > Hope this helps.
> >
> > ---
> > Teo Lachev, MCSD, MCT
> > Author: "Microsoft Reporting Services in Action"
> > http://www.prologika.com
> >
> >
> > "Steven" <montgoss@.hotmail.com> wrote in message
> > news:a73f4c07.0408120920.70ea1d42@.posting.google.com...
> > > SalesOrderID, CustomerID, PurchaseOrderNumber and OrderDate.
> > >
> > > Steven
> > >
> > > "Teo Lachev" <teo@.nospam.prologika.com> wrote in message
> > news:<uL2dV18fEHA.2984@.tk2msftngp13.phx.gbl>...
> > > > So, when you run the query in the Generic Query Designer what columns do
> > you
> > > > see in the results grid?
> > > >
> > > > --
> > > > Hope this helps.
> > > >
> > > > ---
> > > > Teo Lachev, MCSD, MCT
> > > > Author: "Microsoft Reporting Services in Action"
> > > > http://www.prologika.com
>
Showing posts with label creating. Show all posts
Showing posts with label creating. Show all posts
Tuesday, March 27, 2012
Sunday, March 11, 2012
double quotes
Hi,
I am creating a flat file connection to a .csv file
In the columns section of the flatt file connection manager editor, I am not sure why the texts in the .csv file are shown with double quotes arouond them.
They do not have "" in the .csv file.
ThanksDo you have " set as the text qualifier for the flat file connection manager? You may have to put this in there. Also, how are you looking at and/or creating the csv file? If you are using Excel, it will automatically enclose text with " and when you view it in Excel it will parse it for you. Try looking at the csv file using Notepad or another basic text editor.
I am creating a flat file connection to a .csv file
In the columns section of the flatt file connection manager editor, I am not sure why the texts in the .csv file are shown with double quotes arouond them.
They do not have "" in the .csv file.
ThanksDo you have " set as the text qualifier for the flat file connection manager? You may have to put this in there. Also, how are you looking at and/or creating the csv file? If you are using Excel, it will automatically enclose text with " and when you view it in Excel it will parse it for you. Try looking at the csv file using Notepad or another basic text editor.
Sunday, February 26, 2012
Donno how to work on MSDE?
Hi,
I downloaded and installed MSDE successfully on my comp, but since i have this practice of creating my data base, tables.. using the enterprise manager, i donno how exactly to do the same in. MSDE.
I am new to MSDE, so plz someone explain me to create the database and the tables in MSDE.
and another question is that.. i have created 2 instances of the same.. but donno what these instances exactly are ?
can anyone plz explain me all these..?
Try downloading The ASP Enterprise Manager, which is a web based toolwhich allows you to do many Enterprise Manager type tasks in MSDEwithout SQLEM.
http://www.aspenterprisemanager.com/
Jason
Friday, February 17, 2012
doing a FREETEXTTABLE on more than one columns -OR- creating a SECOND FT INDEX for a tab
Is it possible to create a second FT-INDEX for the same table? I want to
index different fields in each one to use it in different areas.
Alternatively, is it possible to do a FT search on MULTIPLE fields in an
Index? I know that the FREETEXTTABLE can only do it on either one or all
indexed columns. But what if i want to do it for more columns.
Note: Full-Text seach MUST be used. I know I could write an OR statement,
but it must be done with a FT search. Any ideas?
SQL 2005 allows you to select which column you want returned in a full-text
search - this is for both Contains and FreeText. SQL FTS also allows you to
full-test index indexed views, which might also help you.
You can only create a single Full-Text index per table. I think what you
might have to do in SQL 2000 is to partition your table into multiple child
tables containing the columns you wish and then full-text index these
tables.
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
"Denis" <denis@.pharmiweb.com> wrote in message
news:e1bz5NJKFHA.2772@.TK2MSFTNGP14.phx.gbl...
> Is it possible to create a second FT-INDEX for the same table? I want to
> index different fields in each one to use it in different areas.
> Alternatively, is it possible to do a FT search on MULTIPLE fields in an
> Index? I know that the FREETEXTTABLE can only do it on either one or all
> indexed columns. But what if i want to do it for more columns.
> Note: Full-Text seach MUST be used. I know I could write an OR
statement,
> but it must be done with a FT search. Any ideas?
>
|||SQL2000 also lets you select a column, but only one! Does 2005 allow
multiple columns?
Partition the table to smaller ones? How do you mean that exactly?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ePHaWlKKFHA.3348@.TK2MSFTNGP10.phx.gbl...
> SQL 2005 allows you to select which column you want returned in a
> full-text
> search - this is for both Contains and FreeText. SQL FTS also allows you
> to
> full-test index indexed views, which might also help you.
> You can only create a single Full-Text index per table. I think what you
> might have to do in SQL 2000 is to partition your table into multiple
> child
> tables containing the columns you wish and then full-text index these
> tables.
|||Denis,
Yes, this can be done, see "SQL Server FTS across multiple tables or
columns" at:
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!316.entry
and substitute freetexttable for containstable in the examples.
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Denis" <denis@.pharmiweb.com> wrote in message
news:e1bz5NJKFHA.2772@.TK2MSFTNGP14.phx.gbl...
> Is it possible to create a second FT-INDEX for the same table? I want to
> index different fields in each one to use it in different areas.
> Alternatively, is it possible to do a FT search on MULTIPLE fields in an
> Index? I know that the FREETEXTTABLE can only do it on either one or all
> indexed columns. But what if i want to do it for more columns.
> Note: Full-Text seach MUST be used. I know I could write an OR
statement,
> but it must be done with a FT search. Any ideas?
>
|||I looked at what you said but I am not sure how that would work. Let me
give you an idea of the type of how i am trying to do the search.
-- Old Search
--SET @.dynQuery = @.dynQuery + ' INNER JOIN
FREETEXTTABLE(tblJobsDataWareHouse, *, ''' + @.Keywords + ''') as KW ON
FT_TBL.uID = KW.[KEY]'
--SET @.RankField = 'KW.RANK'
-- NEW SEARCH
SET @.dynQuery = @.dynQuery + ' INNER JOIN
FREETEXTTABLE(tblJobsDataWareHouse, fldRequirementshtm, ''' + @.Keywords +
''') as KW ON FT_TBL.uID = KW.[KEY] '
SET @.RankField = 'KW.RANK'
SET @.dynQuery = @.dynQuery + ' FULL OUTER JOIN
FREETEXTTABLE(tblJobsDataWareHouse, fldJobTitle, ''' + @.Keywords + ''') as
KWw ON FT_TBL.uID = KWw.[KEY]'
SET @.RankField = 'KWw.RANK'
SET @.dynQuery = @.dynQuery + ' FULL OUTER JOIN
FREETEXTTABLE(tblJobsDataWareHouse, fldCompanyName, ''' + @.Keywords + ''')
as KWww ON FT_TBL.uID = KWww.[KEY]'
SET @.RankField = 'KWww.RANK'
I want my new search to look at Title, Description and Company name only,
rather than every field that was included in the index. Any ideas?
Or perhaps you could show me how CONTAINSTABLE could be applied in this
case?
"John Kane" <jt-kane@.comcast.net> wrote in message
news:uKI3DMLKFHA.2628@.tk2msftngp13.phx.gbl...
> Denis,
> Yes, this can be done, see "SQL Server FTS across multiple tables or
> columns" at:
> http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!316.entry
> and substitute freetexttable for containstable in the examples.
> Thanks,
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Denis" <denis@.pharmiweb.com> wrote in message
> news:e1bz5NJKFHA.2772@.TK2MSFTNGP14.phx.gbl...
> statement,
>
|||No SQL 2000 allows you to search one column or all columns. SQL 2005 lets
you search one column, all columns, or any combination of columns.
To partition a table you have to make one or more tables with the same PK.
So if you have a table like this:
Create Table parent
( pk int not null identity constraint primarykey primary key,
charcol1 char(20),
charcol2 char(20),
charcol3 char(20),
charcol4 char(20))
You could create the following child tables
Create Table child1
( pk int not null identity constraint primarykey1 primary key,
charcol1 char(20))
Create Table child2
( pk int not null identity constraint primarykey2 primary key,
charcol2 char(20))
Create Table child3
( pk int not null identity constraint primarykey3 primary key,
charcol3 char(20))
And then use triggers or replication to keep them in sync.
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
"Denis" <denis@.pharmiweb.com> wrote in message
news:eIOgqtKKFHA.1500@.TK2MSFTNGP09.phx.gbl...
> SQL2000 also lets you select a column, but only one! Does 2005 allow
> multiple columns?
> Partition the table to smaller ones? How do you mean that exactly?
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:ePHaWlKKFHA.3348@.TK2MSFTNGP10.phx.gbl...
>
|||I looked at what you said but I am not sure how that would work. Let me
give you an idea of the type of how i am trying to do the search.
-- Old Search
--SET @.dynQuery = @.dynQuery + ' INNER JOIN
FREETEXTTABLE(tblJobsDataWareHouse, *, ''' + @.Keywords + ''') as KW ON
FT_TBL.uID = KW.[KEY]'
--SET @.RankField = 'KW.RANK'
-- NEW SEARCH
SET @.dynQuery = @.dynQuery + ' INNER JOIN
FREETEXTTABLE(tblJobsDataWareHouse, fldRequirementshtm, ''' + @.Keywords +
''') as KW ON FT_TBL.uID = KW.[KEY] '
SET @.RankField = 'KW.RANK'
SET @.dynQuery = @.dynQuery + ' FULL OUTER JOIN
FREETEXTTABLE(tblJobsDataWareHouse, fldJobTitle, ''' + @.Keywords + ''') as
KWw ON FT_TBL.uID = KWw.[KEY]'
SET @.RankField = 'KWw.RANK'
SET @.dynQuery = @.dynQuery + ' FULL OUTER JOIN
FREETEXTTABLE(tblJobsDataWareHouse, fldCompanyName, ''' + @.Keywords + ''')
as KWww ON FT_TBL.uID = KWww.[KEY]'
SET @.RankField = 'KWww.RANK'
I want my new search to look at Title, Description and Company name only,
rather than every field that was included And if EITHER of the fields
contain the search string i want them displayed.
|||SQL 2000 CAN indeed look either in one or all fields. Look at the
definition of FREETEXTTABLE.
|||Create Table tblJobsDataWareHouse
(uid int not null identity constraint primarykey4 primary key,
fldRequirementshtm char(20),
fldJobTitle char(20),
fldCompanyName char(20))
insert into tblJobsDataWareHouse
(fldRequirementshtm,fldJobTitle,fldCompanyName)
values ('test',null,null)
insert into tblJobsDataWareHouse
(fldRequirementshtm,fldJobTitle,fldCompanyName)
values (NULL,'test',null)
insert into tblJobsDataWareHouse
(fldRequirementshtm,fldJobTitle,fldCompanyName)
values (NULL, NULL,'test')
insert into tblJobsDataWareHouse
(fldRequirementshtm,fldJobTitle,fldCompanyName)
values (NULL, NULL,'rest')
GO
sp_fulltext_database 'enable'
GO
sp_fulltext_catalog 'test','create'
GO
sp_fulltext_table 'tblJobsDataWareHouse', 'create', 'test', 'primarykey4'
GO
sp_fulltext_column 'tblJobsDataWareHouse', 'fldRequirementshtm', 'add', 1033
GO
sp_fulltext_column 'tblJobsDataWareHouse', 'fldJobTitle', 'add', 1033
GO
sp_fulltext_column 'tblJobsDataWareHouse', 'fldCompanyName', 'add', 1033
GO
sp_fulltext_table 'tblJobsDataWareHouse', 'activate'
GO
sp_fulltext_catalog 'test','start_full'
go
select * from tblJobsDataWareHouse where contains(*,'test')
go
select * from tblJobsDataWareHouse as tbl join (
select * from containstable(tblJobsDataWareHouse,fldRequirements htm,'test')
union
select * from containstable(tblJobsDataWareHouse,fldJobTitle,'te st')
union
select * from containstable(tblJobsDataWareHouse,fldCompanyName, 'test')
)as ft
on ft.[key]=tbl.uid
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
"Denis" <denis@.pharmiweb.com> wrote in message
news:uVFcITUKFHA.2724@.TK2MSFTNGP10.phx.gbl...
> I looked at what you said but I am not sure how that would work. Let me
> give you an idea of the type of how i am trying to do the search.
>
> -- Old Search
> --SET @.dynQuery = @.dynQuery + ' INNER JOIN
> FREETEXTTABLE(tblJobsDataWareHouse, *, ''' + @.Keywords + ''') as KW ON
> FT_TBL.uID = KW.[KEY]'
> --SET @.RankField = 'KW.RANK'
>
> -- NEW SEARCH
> SET @.dynQuery = @.dynQuery + ' INNER JOIN
> FREETEXTTABLE(tblJobsDataWareHouse, fldRequirementshtm, ''' + @.Keywords +
> ''') as KW ON FT_TBL.uID = KW.[KEY] '
> SET @.RankField = 'KW.RANK'
> SET @.dynQuery = @.dynQuery + ' FULL OUTER JOIN
> FREETEXTTABLE(tblJobsDataWareHouse, fldJobTitle, ''' + @.Keywords + ''') as
> KWw ON FT_TBL.uID = KWw.[KEY]'
> SET @.RankField = 'KWw.RANK'
> SET @.dynQuery = @.dynQuery + ' FULL OUTER JOIN
> FREETEXTTABLE(tblJobsDataWareHouse, fldCompanyName, ''' + @.Keywords + ''')
> as KWww ON FT_TBL.uID = KWww.[KEY]'
> SET @.RankField = 'KWww.RANK'
>
> I want my new search to look at Title, Description and Company name only,
> rather than every field that was included And if EITHER of the fields
> contain the search string i want them displayed.
>
|||many thanks for your code example.
After some thinking i decided that due to the structure of the database is
best not to break-down the table into smaller ones, so i guess i will have
to find another way of doing it.
But thank ytou very much for the time you took to answer my question.
again, many thanks.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:e%23msOZZKFHA.656@.TK2MSFTNGP14.phx.gbl...
> Create Table tblJobsDataWareHouse
> (uid int not null identity constraint primarykey4 primary key,
> fldRequirementshtm char(20),
> fldJobTitle char(20),
> fldCompanyName char(20))
> insert into tblJobsDataWareHouse
> (fldRequirementshtm,fldJobTitle,fldCompanyName)
> values ('test',null,null)
> insert into tblJobsDataWareHouse
> (fldRequirementshtm,fldJobTitle,fldCompanyName)
> values (NULL,'test',null)
> insert into tblJobsDataWareHouse
> (fldRequirementshtm,fldJobTitle,fldCompanyName)
> values (NULL, NULL,'test')
> insert into tblJobsDataWareHouse
> (fldRequirementshtm,fldJobTitle,fldCompanyName)
> values (NULL, NULL,'rest')
> GO
> sp_fulltext_database 'enable'
> GO
> sp_fulltext_catalog 'test','create'
> GO
> sp_fulltext_table 'tblJobsDataWareHouse', 'create', 'test', 'primarykey4'
> GO
> sp_fulltext_column 'tblJobsDataWareHouse', 'fldRequirementshtm', 'add',
> 1033
> GO
> sp_fulltext_column 'tblJobsDataWareHouse', 'fldJobTitle', 'add', 1033
> GO
> sp_fulltext_column 'tblJobsDataWareHouse', 'fldCompanyName', 'add', 1033
> GO
> sp_fulltext_table 'tblJobsDataWareHouse', 'activate'
> GO
> sp_fulltext_catalog 'test','start_full'
> go
> select * from tblJobsDataWareHouse where contains(*,'test')
> go
> select * from tblJobsDataWareHouse as tbl join (
> select * from
> containstable(tblJobsDataWareHouse,fldRequirements htm,'test')
> union
> select * from containstable(tblJobsDataWareHouse,fldJobTitle,'te st')
> union
> select * from containstable(tblJobsDataWareHouse,fldCompanyName, 'test')
> )as ft
> on ft.[key]=tbl.uid
>
> --
> 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
> "Denis" <denis@.pharmiweb.com> wrote in message
> news:uVFcITUKFHA.2724@.TK2MSFTNGP10.phx.gbl...
>
index different fields in each one to use it in different areas.
Alternatively, is it possible to do a FT search on MULTIPLE fields in an
Index? I know that the FREETEXTTABLE can only do it on either one or all
indexed columns. But what if i want to do it for more columns.
Note: Full-Text seach MUST be used. I know I could write an OR statement,
but it must be done with a FT search. Any ideas?
SQL 2005 allows you to select which column you want returned in a full-text
search - this is for both Contains and FreeText. SQL FTS also allows you to
full-test index indexed views, which might also help you.
You can only create a single Full-Text index per table. I think what you
might have to do in SQL 2000 is to partition your table into multiple child
tables containing the columns you wish and then full-text index these
tables.
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
"Denis" <denis@.pharmiweb.com> wrote in message
news:e1bz5NJKFHA.2772@.TK2MSFTNGP14.phx.gbl...
> Is it possible to create a second FT-INDEX for the same table? I want to
> index different fields in each one to use it in different areas.
> Alternatively, is it possible to do a FT search on MULTIPLE fields in an
> Index? I know that the FREETEXTTABLE can only do it on either one or all
> indexed columns. But what if i want to do it for more columns.
> Note: Full-Text seach MUST be used. I know I could write an OR
statement,
> but it must be done with a FT search. Any ideas?
>
|||SQL2000 also lets you select a column, but only one! Does 2005 allow
multiple columns?
Partition the table to smaller ones? How do you mean that exactly?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ePHaWlKKFHA.3348@.TK2MSFTNGP10.phx.gbl...
> SQL 2005 allows you to select which column you want returned in a
> full-text
> search - this is for both Contains and FreeText. SQL FTS also allows you
> to
> full-test index indexed views, which might also help you.
> You can only create a single Full-Text index per table. I think what you
> might have to do in SQL 2000 is to partition your table into multiple
> child
> tables containing the columns you wish and then full-text index these
> tables.
|||Denis,
Yes, this can be done, see "SQL Server FTS across multiple tables or
columns" at:
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!316.entry
and substitute freetexttable for containstable in the examples.
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Denis" <denis@.pharmiweb.com> wrote in message
news:e1bz5NJKFHA.2772@.TK2MSFTNGP14.phx.gbl...
> Is it possible to create a second FT-INDEX for the same table? I want to
> index different fields in each one to use it in different areas.
> Alternatively, is it possible to do a FT search on MULTIPLE fields in an
> Index? I know that the FREETEXTTABLE can only do it on either one or all
> indexed columns. But what if i want to do it for more columns.
> Note: Full-Text seach MUST be used. I know I could write an OR
statement,
> but it must be done with a FT search. Any ideas?
>
|||I looked at what you said but I am not sure how that would work. Let me
give you an idea of the type of how i am trying to do the search.
-- Old Search
--SET @.dynQuery = @.dynQuery + ' INNER JOIN
FREETEXTTABLE(tblJobsDataWareHouse, *, ''' + @.Keywords + ''') as KW ON
FT_TBL.uID = KW.[KEY]'
--SET @.RankField = 'KW.RANK'
-- NEW SEARCH
SET @.dynQuery = @.dynQuery + ' INNER JOIN
FREETEXTTABLE(tblJobsDataWareHouse, fldRequirementshtm, ''' + @.Keywords +
''') as KW ON FT_TBL.uID = KW.[KEY] '
SET @.RankField = 'KW.RANK'
SET @.dynQuery = @.dynQuery + ' FULL OUTER JOIN
FREETEXTTABLE(tblJobsDataWareHouse, fldJobTitle, ''' + @.Keywords + ''') as
KWw ON FT_TBL.uID = KWw.[KEY]'
SET @.RankField = 'KWw.RANK'
SET @.dynQuery = @.dynQuery + ' FULL OUTER JOIN
FREETEXTTABLE(tblJobsDataWareHouse, fldCompanyName, ''' + @.Keywords + ''')
as KWww ON FT_TBL.uID = KWww.[KEY]'
SET @.RankField = 'KWww.RANK'
I want my new search to look at Title, Description and Company name only,
rather than every field that was included in the index. Any ideas?
Or perhaps you could show me how CONTAINSTABLE could be applied in this
case?
"John Kane" <jt-kane@.comcast.net> wrote in message
news:uKI3DMLKFHA.2628@.tk2msftngp13.phx.gbl...
> Denis,
> Yes, this can be done, see "SQL Server FTS across multiple tables or
> columns" at:
> http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!316.entry
> and substitute freetexttable for containstable in the examples.
> Thanks,
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Denis" <denis@.pharmiweb.com> wrote in message
> news:e1bz5NJKFHA.2772@.TK2MSFTNGP14.phx.gbl...
> statement,
>
|||No SQL 2000 allows you to search one column or all columns. SQL 2005 lets
you search one column, all columns, or any combination of columns.
To partition a table you have to make one or more tables with the same PK.
So if you have a table like this:
Create Table parent
( pk int not null identity constraint primarykey primary key,
charcol1 char(20),
charcol2 char(20),
charcol3 char(20),
charcol4 char(20))
You could create the following child tables
Create Table child1
( pk int not null identity constraint primarykey1 primary key,
charcol1 char(20))
Create Table child2
( pk int not null identity constraint primarykey2 primary key,
charcol2 char(20))
Create Table child3
( pk int not null identity constraint primarykey3 primary key,
charcol3 char(20))
And then use triggers or replication to keep them in sync.
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
"Denis" <denis@.pharmiweb.com> wrote in message
news:eIOgqtKKFHA.1500@.TK2MSFTNGP09.phx.gbl...
> SQL2000 also lets you select a column, but only one! Does 2005 allow
> multiple columns?
> Partition the table to smaller ones? How do you mean that exactly?
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:ePHaWlKKFHA.3348@.TK2MSFTNGP10.phx.gbl...
>
|||I looked at what you said but I am not sure how that would work. Let me
give you an idea of the type of how i am trying to do the search.
-- Old Search
--SET @.dynQuery = @.dynQuery + ' INNER JOIN
FREETEXTTABLE(tblJobsDataWareHouse, *, ''' + @.Keywords + ''') as KW ON
FT_TBL.uID = KW.[KEY]'
--SET @.RankField = 'KW.RANK'
-- NEW SEARCH
SET @.dynQuery = @.dynQuery + ' INNER JOIN
FREETEXTTABLE(tblJobsDataWareHouse, fldRequirementshtm, ''' + @.Keywords +
''') as KW ON FT_TBL.uID = KW.[KEY] '
SET @.RankField = 'KW.RANK'
SET @.dynQuery = @.dynQuery + ' FULL OUTER JOIN
FREETEXTTABLE(tblJobsDataWareHouse, fldJobTitle, ''' + @.Keywords + ''') as
KWw ON FT_TBL.uID = KWw.[KEY]'
SET @.RankField = 'KWw.RANK'
SET @.dynQuery = @.dynQuery + ' FULL OUTER JOIN
FREETEXTTABLE(tblJobsDataWareHouse, fldCompanyName, ''' + @.Keywords + ''')
as KWww ON FT_TBL.uID = KWww.[KEY]'
SET @.RankField = 'KWww.RANK'
I want my new search to look at Title, Description and Company name only,
rather than every field that was included And if EITHER of the fields
contain the search string i want them displayed.
|||SQL 2000 CAN indeed look either in one or all fields. Look at the
definition of FREETEXTTABLE.
|||Create Table tblJobsDataWareHouse
(uid int not null identity constraint primarykey4 primary key,
fldRequirementshtm char(20),
fldJobTitle char(20),
fldCompanyName char(20))
insert into tblJobsDataWareHouse
(fldRequirementshtm,fldJobTitle,fldCompanyName)
values ('test',null,null)
insert into tblJobsDataWareHouse
(fldRequirementshtm,fldJobTitle,fldCompanyName)
values (NULL,'test',null)
insert into tblJobsDataWareHouse
(fldRequirementshtm,fldJobTitle,fldCompanyName)
values (NULL, NULL,'test')
insert into tblJobsDataWareHouse
(fldRequirementshtm,fldJobTitle,fldCompanyName)
values (NULL, NULL,'rest')
GO
sp_fulltext_database 'enable'
GO
sp_fulltext_catalog 'test','create'
GO
sp_fulltext_table 'tblJobsDataWareHouse', 'create', 'test', 'primarykey4'
GO
sp_fulltext_column 'tblJobsDataWareHouse', 'fldRequirementshtm', 'add', 1033
GO
sp_fulltext_column 'tblJobsDataWareHouse', 'fldJobTitle', 'add', 1033
GO
sp_fulltext_column 'tblJobsDataWareHouse', 'fldCompanyName', 'add', 1033
GO
sp_fulltext_table 'tblJobsDataWareHouse', 'activate'
GO
sp_fulltext_catalog 'test','start_full'
go
select * from tblJobsDataWareHouse where contains(*,'test')
go
select * from tblJobsDataWareHouse as tbl join (
select * from containstable(tblJobsDataWareHouse,fldRequirements htm,'test')
union
select * from containstable(tblJobsDataWareHouse,fldJobTitle,'te st')
union
select * from containstable(tblJobsDataWareHouse,fldCompanyName, 'test')
)as ft
on ft.[key]=tbl.uid
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
"Denis" <denis@.pharmiweb.com> wrote in message
news:uVFcITUKFHA.2724@.TK2MSFTNGP10.phx.gbl...
> I looked at what you said but I am not sure how that would work. Let me
> give you an idea of the type of how i am trying to do the search.
>
> -- Old Search
> --SET @.dynQuery = @.dynQuery + ' INNER JOIN
> FREETEXTTABLE(tblJobsDataWareHouse, *, ''' + @.Keywords + ''') as KW ON
> FT_TBL.uID = KW.[KEY]'
> --SET @.RankField = 'KW.RANK'
>
> -- NEW SEARCH
> SET @.dynQuery = @.dynQuery + ' INNER JOIN
> FREETEXTTABLE(tblJobsDataWareHouse, fldRequirementshtm, ''' + @.Keywords +
> ''') as KW ON FT_TBL.uID = KW.[KEY] '
> SET @.RankField = 'KW.RANK'
> SET @.dynQuery = @.dynQuery + ' FULL OUTER JOIN
> FREETEXTTABLE(tblJobsDataWareHouse, fldJobTitle, ''' + @.Keywords + ''') as
> KWw ON FT_TBL.uID = KWw.[KEY]'
> SET @.RankField = 'KWw.RANK'
> SET @.dynQuery = @.dynQuery + ' FULL OUTER JOIN
> FREETEXTTABLE(tblJobsDataWareHouse, fldCompanyName, ''' + @.Keywords + ''')
> as KWww ON FT_TBL.uID = KWww.[KEY]'
> SET @.RankField = 'KWww.RANK'
>
> I want my new search to look at Title, Description and Company name only,
> rather than every field that was included And if EITHER of the fields
> contain the search string i want them displayed.
>
|||many thanks for your code example.
After some thinking i decided that due to the structure of the database is
best not to break-down the table into smaller ones, so i guess i will have
to find another way of doing it.
But thank ytou very much for the time you took to answer my question.
again, many thanks.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:e%23msOZZKFHA.656@.TK2MSFTNGP14.phx.gbl...
> Create Table tblJobsDataWareHouse
> (uid int not null identity constraint primarykey4 primary key,
> fldRequirementshtm char(20),
> fldJobTitle char(20),
> fldCompanyName char(20))
> insert into tblJobsDataWareHouse
> (fldRequirementshtm,fldJobTitle,fldCompanyName)
> values ('test',null,null)
> insert into tblJobsDataWareHouse
> (fldRequirementshtm,fldJobTitle,fldCompanyName)
> values (NULL,'test',null)
> insert into tblJobsDataWareHouse
> (fldRequirementshtm,fldJobTitle,fldCompanyName)
> values (NULL, NULL,'test')
> insert into tblJobsDataWareHouse
> (fldRequirementshtm,fldJobTitle,fldCompanyName)
> values (NULL, NULL,'rest')
> GO
> sp_fulltext_database 'enable'
> GO
> sp_fulltext_catalog 'test','create'
> GO
> sp_fulltext_table 'tblJobsDataWareHouse', 'create', 'test', 'primarykey4'
> GO
> sp_fulltext_column 'tblJobsDataWareHouse', 'fldRequirementshtm', 'add',
> 1033
> GO
> sp_fulltext_column 'tblJobsDataWareHouse', 'fldJobTitle', 'add', 1033
> GO
> sp_fulltext_column 'tblJobsDataWareHouse', 'fldCompanyName', 'add', 1033
> GO
> sp_fulltext_table 'tblJobsDataWareHouse', 'activate'
> GO
> sp_fulltext_catalog 'test','start_full'
> go
> select * from tblJobsDataWareHouse where contains(*,'test')
> go
> select * from tblJobsDataWareHouse as tbl join (
> select * from
> containstable(tblJobsDataWareHouse,fldRequirements htm,'test')
> union
> select * from containstable(tblJobsDataWareHouse,fldJobTitle,'te st')
> union
> select * from containstable(tblJobsDataWareHouse,fldCompanyName, 'test')
> )as ft
> on ft.[key]=tbl.uid
>
> --
> 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
> "Denis" <denis@.pharmiweb.com> wrote in message
> news:uVFcITUKFHA.2724@.TK2MSFTNGP10.phx.gbl...
>
Subscribe to:
Posts (Atom)