General Database/Query and Form Design question

Currently working on an existing system written using an Access 2002 project
(.adp) and SQL Server 2000 and need to add some ehancements.
The system is a leasing system where a customer leases one or more assets
for a defined term (eg. 24, 36, 48 months...). Each lease may also be
associated with a fixed, or variable monthly repayment regime.
For example a customer wants to lease a Boat for 24 months for the first 12
months (period 1 - 12) they pay $50 per month, for period 13 - 18 they pay
$35 per month, and for the last 6 months (period 19 - 24) they pay $25 per
month.
The proposed table design is as follows:
tblLease
LeaseId int (identity) PK
CustomerId FK
TermId FK
...
tblLeaseAsset (1:M relationship to tblLease)
AssetId int PK
LeaseId int PK/FK to tblLease
AssetDescription
...
tblLeaseAssetRate (Intersection table - 1:M relationship to tblLeaseAsset,
1:M relationship to tblLeaseTerm)
AssetId int PK/FK
TermPeriodId int PK/FK
Payment decimal (19,4)
...
tblLeasePeriodTerm (1:M relationship to tblLease)
TermPeriodId int (identity) PK
LeaseId int FK to tblLease
FromTermPeriod small int
ToTermPeriod small int
...
Whilst the users are happy to enter the initial lease and period/term
information as a Parent/Main form and Child/Subform combination. They would
like to be able to enter the Asset and payment information together as a
single Child/Subform:
Period Period Period
Asset Description 1 - 12 13 - 18 19 - ...
A Boat $50 $35 $25
The problem is that this requires a pivot table/cross tabulation type view
of the data and these types of queries are not generally updatable.
Does anyone have any ideas how I might achieve the objective either in terms
of database, query or form design so that users can insert, update, delete
and view records?
Your assistance apreciated
Guy HortonWell, for each "asset", you got
AssetName AssetCost WherePurchased
Boat $15,000 WalMart
Car $5,000 MacDonalds
etc.
Now, just put another sub-form to the "right" of a above where you can enter
"many" values for each of the above.
If your cursor is in Boat, then you can enter:
Period Amount
1 - 12 $50
13 - 18 $35
etc.
I can think of "many" cases where you got a detail line, and need "many" for
that details. consider the QuickBooks when you write a single check, and a
split amount, you need to "split" out the funds to "many" values. So, the
solution is to make two side by side sub-forms.
Take a look at the following screen shots, and especially the last one where
I have a "classic" cheque "distribution" screen (for each check/person on
the left, I can enter "many" split values for that particlar amount
(donation in this example) on the right side...
http://www.members.shaw.ca/AlbertKa...ticles/Grid.htm
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@.msn.com
http://www.members.shaw.ca/AlbertKallal|||I'd strongly recommend Albert's way, but if you're absolutely forced to
doing it on the same line, you can try a few different things that I can
think of:
1. Play with Access' PivotTable features. I've never found them all that
useful, and very klunky to use, but it may get you where you're trying to
go. Don't ask me for more detailed info on how to do that, though, I
generally avoid PivotTables like the plague.
2. Implement a temporary table that goes across as you'd like it to, then
as each record is read/updated, transfer the values to/from the "real" table
in the OnCurrent and Before/AfterUpdate events.
3. Use an embedded control of some kind (Hierarchical FlexGrid?) to display
the data instead.
Good luck,
Rob
"Guy Horton" <guy.horton@.nospam.bigpond.com> wrote in message
news:%23RueVVwwFHA.2064@.TK2MSFTNGP09.phx.gbl...
> Currently working on an existing system written using an Access 2002
> project (.adp) and SQL Server 2000 and need to add some ehancements.
> The system is a leasing system where a customer leases one or more assets
> for a defined term (eg. 24, 36, 48 months...). Each lease may also be
> associated with a fixed, or variable monthly repayment regime.
> For example a customer wants to lease a Boat for 24 months for the first
> 12 months (period 1 - 12) they pay $50 per month, for period 13 - 18 they
> pay $35 per month, and for the last 6 months (period 19 - 24) they pay $25
> per month.
> The proposed table design is as follows:
> tblLease
> LeaseId int (identity) PK
> CustomerId FK
> TermId FK
> ...
> tblLeaseAsset (1:M relationship to tblLease)
> AssetId int PK
> LeaseId int PK/FK to tblLease
> AssetDescription
> ...
> tblLeaseAssetRate (Intersection table - 1:M relationship to tblLeaseAsset,
> 1:M relationship to tblLeaseTerm)
> AssetId int PK/FK
> TermPeriodId int PK/FK
> Payment decimal (19,4)
> ...
> tblLeasePeriodTerm (1:M relationship to tblLease)
> TermPeriodId int (identity) PK
> LeaseId int FK to tblLease
> FromTermPeriod small int
> ToTermPeriod small int
> ...
> Whilst the users are happy to enter the initial lease and period/term
> information as a Parent/Main form and Child/Subform combination. They
> would like to be able to enter the Asset and payment information together
> as a single Child/Subform:
> Period Period Period
> Asset Description 1 - 12 13 - 18 19 - ...
> A Boat $50 $35 $25
> The problem is that this requires a pivot table/cross tabulation type view
> of the data and these types of queries are not generally updatable.
> Does anyone have any ideas how I might achieve the objective either in
> terms of database, query or form design so that users can insert, update,
> delete and view records?
> Your assistance apreciated
> Guy Horton
>|||Albert,
Thank you for your excellent response. I reviewed your article and screen
shots and have to say they look very professional.
I briefly considered side by side subforms and agree with you that this is a
very workable option, and probably the option I will go with. Although, it
doesn't allow the users to view all lease rates for all the currently
visible leased assets, and they think of periods as running across as
opposed to down the form.
Your thoughts appreciated.
Best Regards,
Guy
"Albert D.Kallal" <PleaseNOOOsPAMmkallal@.msn.com> wrote in message
news:ukKGPmwwFHA.460@.TK2MSFTNGP15.phx.gbl...
> Well, for each "asset", you got
>
> AssetName AssetCost WherePurchased
> Boat $15,000 WalMart
> Car $5,000 MacDonalds
> etc.
> Now, just put another sub-form to the "right" of a above where you can
> enter "many" values for each of the above.
> If your cursor is in Boat, then you can enter:
> Period Amount
> 1 - 12 $50
> 13 - 18 $35
> etc.
> I can think of "many" cases where you got a detail line, and need "many"
> for that details. consider the QuickBooks when you write a single check,
> and a split amount, you need to "split" out the funds to "many" values.
> So, the solution is to make two side by side sub-forms.
> Take a look at the following screen shots, and especially the last one
> where I have a "classic" cheque "distribution" screen (for each
> check/person on the left, I can enter "many" split values for that
> particlar amount (donation in this example) on the right side...
> http://www.members.shaw.ca/AlbertKa...ticles/Grid.htm
> --
> Albert D. Kallal (Access MVP)
> Edmonton, Alberta Canada
> pleaseNOOSpamKallal@.msn.com
> http://www.members.shaw.ca/AlbertKallal
>|||Robert,
Thank you for your response. I agree that Albert's solution is the probably
the most sensible way to go, and that PivotTable features are klunky to use.
Your thoughts appreciated
Guy
"Robert Morley" <rmorley@.magma.ca.no.freakin.spam> wrote in message
news:eYrGDpxwFHA.3756@.tk2msftngp13.phx.gbl...
> I'd strongly recommend Albert's way, but if you're absolutely forced to
> doing it on the same line, you can try a few different things that I can
> think of:
> 1. Play with Access' PivotTable features. I've never found them all that
> useful, and very klunky to use, but it may get you where you're trying to
> go. Don't ask me for more detailed info on how to do that, though, I
> generally avoid PivotTables like the plague.
> 2. Implement a temporary table that goes across as you'd like it to, then
> as each record is read/updated, transfer the values to/from the "real"
> table in the OnCurrent and Before/AfterUpdate events.
> 3. Use an embedded control of some kind (Hierarchical FlexGrid?) to
> display the data instead.
>
> Good luck,
> Rob
> "Guy Horton" <guy.horton@.nospam.bigpond.com> wrote in message
> news:%23RueVVwwFHA.2064@.TK2MSFTNGP09.phx.gbl...
>

General Access Project info

Hi all, we currently have an access databse with linked odbc connections to an SQL server database.
Performance wise I don't think this is great.
Anyone have any general information about doing an upgrade to a micrsoft project database, and what are the pros and cons of access projects compared to Access databases with linked tables.
About 30 -40 users currently hammer the access front end during the day.Access Data Projects are MUCH better than Access MDB databases with linked tables. Off the top of my head I can't think of any advantage to using an MDB with linked tables instead of going with an ADP file.|||OK, one disadvantage: You cannot store ANY tables locally in an Access ADP file, so any setup or configuration data cannot be stored on the user's machine (at least in the database file).

Gathering Field Descriptions From SAP

I am at the documentation stage of our project and I need Field Descriptions. They have been defined in SAP R/3 (DB2) and I can view them using SE11 command line parameter (It may change fom version to version, I don't know. It is the place where you view tables).
Lets say table X have 10 columns and every column's description has already been entered in SAP. In my situation there is a total amount of 200 tables which comes out appr. 2000 descriptions in return.
My question is as you may guess, Is there a way to gather this descriptions in a form of table(s)? So I can easily use this info for documentation. It maybe a third party tool or a command or a script wahatever. I really need this.

Thanks in advance.I have forgotten to tell that I am using SQL SERVER 2000 and connecting to DB2 via ODBC driver (though it is so slow...).

Gantt chart in SQL reporting service

Hi all,

I need creat a gannt chart for project server report in SQL reporting service. Is somebody know some good third party tool can help to implement or how to creat by hand?

Thanks in advance!

I use Dundas Chart for Reporting Services and can thoroughly recommend it and it supports Gannt Charts

|||Thanks Jasper! But Dundas chart is a little bit expensive solution for us. It supports SQL 2000 reporting service?|||I downloaded the Dundas Chart for Reporting Services and try to create a Gantt chart. I found it's very difficult to do, as there're many settings on it. Is there any tutorial provided? Thanks!

Gantt chart in SQL reporting service

Hi all,

I need creat a gannt chart for project server report in SQL reporting service. Is somebody know some good third party tool can help to implement or how to creat by hand?

Thanks in advance!

I use Dundas Chart for Reporting Services and can thoroughly recommend it and it supports Gannt Charts

|||Thanks Jasper! But Dundas chart is a little bit expensive solution for us. It supports SQL 2000 reporting service?|||I downloaded the Dundas Chart for Reporting Services and try to create a Gantt chart. I found it's very difficult to do, as there're many settings on it. Is there any tutorial provided? Thanks!

Fuzzy Lookup[4506] Error in Integration Services

Hi:

I m developing Integration Services Project with Fuzzy Services.

as Provided in http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/FzDTSSQL05.asp

am running its Simple example with database AdventureWorks and table Products (I hve also tried other tables). but its failed to execute b/c of this error

[Fuzzy Lookup [4506]] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Multiple identity columns specified for table 'FuzzyLookupMatchIndexEmployee_FLRef_060705_10:21:09_2408_afc874d3-927b-4c70-95ad-a726ef6d7567'. Only one identity column per table is allowed.".

Can any buddy help me out.

Try deselecting the "Store new index" option.|||I hve tried it , but Error still same|||I'm having this problem. I'm curious if you were ever able to resolve it? Any help would be greatly appreciated.

Fuzzy Lookup[4506] Error in Integration Services

Hi:

I m developing Integration Services Project with Fuzzy Services.

as Provided in http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/FzDTSSQL05.asp

am running its Simple example with database AdventureWorks and table Products (I hve also tried other tables). but its failed to execute b/c of this error

[Fuzzy Lookup [4506]] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Multiple identity columns specified for table 'FuzzyLookupMatchIndexEmployee_FLRef_060705_10:21:09_2408_afc874d3-927b-4c70-95ad-a726ef6d7567'. Only one identity column per table is allowed.".

Can any buddy help me out.

Try deselecting the "Store new index" option.|||I hve tried it , but Error still same|||I'm having this problem. I'm curious if you were ever able to resolve it? Any help would be greatly appreciated.sql

Fuzzy lookup match issue

Hello,

I have a peculiar problem in my project. My project design is like this

The number in (...) are count of records.

File feed (1000)

|

|

Fuzzy Lookup

against Table2

|

|

Split Fz Lookup results

(_Similarity >= 0.60 && _Confidence >= 0.85)

| |

| |

| Write matches to Table1 (250)

|

Fuzzy Group

Remaining rows (750)

|

|

Split Fz Group results

| |

| |

Write Canonicals Write Dupes

to Table2 to Table1

(300) (450)

This is basically a customer de-dupification project.

The Table2 has the canonicals and Table1 has the dupes (of the canonicals).

I already have some data in these tables and the new data is matched against the existing data

in these tables and classified as new customers and duplicate customers.

In the above process one could notice that the rows identified as dupes of already exsting canonicals

by the Fuzzy Lookup task are written into the dupes table (Table1) and will not be processed further down

the line in the project.

But in my case I see that those matches identified by Fuzzy lookup are further being included in the

Fuzzy Grouping also.

When I run this in debug mode in BIDS, it shows the correct numbers as I have depicted in the

illustration above. But, after execution, when I query the tables it shows that all 1000 rows

went through Fuzzy Grouping.

Any thoughts?

Btw, is there anyway to upload attachments to the postings here?

I also tried introducing a Derived Column between the 'Split Fz Lookup Results' and 'Write matches to Table1' to write some string into one of the table columns. It did not.

Fuzzy Grouping in parallel

Hello,

I have created a project to do de-dupification of addresses.

I understand that Fuzzy Grouping will take less time if it has lesser data volume to process.

My source feed file is sometimes huge. So I am splitting the input into multiple branches based on

the first letter of the city. There are 7 branches in the process.

Source File Feed

|

Split data into 7 groups

|

| | | | | | |

FzGrpg FzGrpg FzGrpg FzGrpg FzGrpg FzGrpg FzGrpg

| | | | | | |

Split Split Split Split Split Split Split

| | | | | | |

- -- -- -- -- -- --

| | | | | | | | | | | | | |

<- - - - - - - Write the Canonicals and Dupes from each of these splits into database - - - - - - - - ->

When I designed this I was hoping that each of the Fuzzy Grouping tasks will execute in parallel.

But in reality they are processing one after the other.

Is there anyway to make them execute in parallel?

Appreciate your help.

Thanks

KM

How do you know they are not going in parallel and what kind of machine are you using?

I haven't use Fuzzy groups before, but parallel processing would depend on the hardware you are using. If the machine you use is a single processor, I doubt you can see parallelism on the process.

|||

Its a 4 dual core CPU machine with 8 GB RAM.

When I run in the debug mode you could see the data flowing in the pipelines at runtime.

|||

Which component are you using right before to fuzzy groups? I *think*, you should be using a multicast in order to get the parallelism you want; as it would generate 7 identical data sets to be consumed by each fuzzy grouping component. See if this article gives you some tips (Parallelism section):

http://www.microsoft.com/technet/prodtechnol/sql/2005/ssisperf.mspx

[Microsoft follow-up] perhaps somebody at MSFT can give you a better explanation

|||

Yes, I believe the current pipeline engine will not do a great job in optimizing this one. It would most probably end up using a single thread as Conditional Split is synchronous. You could try to artificially break synchronicity of the fuzzy grouping branches by adding a fake asynchronous transform (Union All with one input and one output should be good).

The next version of the pipeline scheduler should be able to better optimize distribution of threads.

Thanks.

|||

Bob and Rafael, thanks a lot for your input. So you are saying it should be like this?

Source File Feed

|

Split data into 7 groups

|

| | | | | | |

UnionAll UnionAll UnionAll UnionAll UnionAll UnionAll UnionAll

| | | | | | |

FzGrpg FzGrpg FzGrpg FzGrpg FzGrpg FzGrpg FzGrpg

| | | | | | |

Split Split Split Split Split Split Split

| | | | | | |

- -- -- -- -- -- --

| | | | | | | | | | | | | |

<- - - - - - - Write the Canonicals and Dupes from each of these splits into database - - - - - - - - ->

|||

As an alternative, could you do the split in one data flow, dumping each branch to a raw file, and then use a seperate data flow (or 7 data flows) to read in the raw files and do the Fuzzy Grouping?

The engine seems to optimize better with multiple data flows, than with multiple paths in the same data flow.

|||

Yes, that is how I meant to use Union Alls.

The idea of using raw files sounds good too. I would try both and see which one works better for youe scenario.

Thanks.

|||

Thanks a lot guys.

I tried the UnionAll approach and it works perfectly fine. I am able to see that all the threads go in parallel.

A run that used to take around 10 hours got completed in 2.5 hours and that's a lot of saving.

And utilitzation of the CPUs was 100%.

As and when I tweak this more I will keep you all posted.

Thanks again.

|||

KM68 wrote:

Thanks a lot guys.

I tried the UnionAll approach and it works perfectly fine. I am able to see that all the threads go in parallel.

A run that used to take around 10 hours got completed in 2.5 hours and that's a lot of saving.

And utilitzation of the CPUs was 100%.

As and when I tweak this more I will keep you all posted.

Thanks again.

[Microsoft follow-up] This sounds like a possitive feedback.

KM68,

We all will appreciate your updates.

|||

I am glad it worked.

Hopefully, with the next version of the data flow engine you will not need this workaround and the execution time can be trimmed even more.

Thanks.

|||

Thanks.

Any idea when the next version is scheduled for release?

Fuzzy Grouping in parallel

Hello,

I have created a project to do de-dupification of addresses.

I understand that Fuzzy Grouping will take less time if it has lesser data volume to process.

My source feed file is sometimes huge. So I am splitting the input into multiple branches based on

the first letter of the city. There are 7 branches in the process.

Source File Feed

|

Split data into 7 groups

|

| | | | | | |

FzGrpg FzGrpg FzGrpg FzGrpg FzGrpg FzGrpg FzGrpg

| | | | | | |

Split Split Split Split Split Split Split

| | | | | | |

- -- -- -- -- -- --

| | | | | | | | | | | | | |

<- - - - - - - Write the Canonicals and Dupes from each of these splits into database - - - - - - - - ->

When I designed this I was hoping that each of the Fuzzy Grouping tasks will execute in parallel.

But in reality they are processing one after the other.

Is there anyway to make them execute in parallel?

Appreciate your help.

Thanks

KM

How do you know they are not going in parallel and what kind of machine are you using?

I haven't use Fuzzy groups before, but parallel processing would depend on the hardware you are using. If the machine you use is a single processor, I doubt you can see parallelism on the process.

|||

Its a 4 dual core CPU machine with 8 GB RAM.

When I run in the debug mode you could see the data flowing in the pipelines at runtime.

|||

Which component are you using right before to fuzzy groups? I *think*, you should be using a multicast in order to get the parallelism you want; as it would generate 7 identical data sets to be consumed by each fuzzy grouping component. See if this article gives you some tips (Parallelism section):

http://www.microsoft.com/technet/prodtechnol/sql/2005/ssisperf.mspx

[Microsoft follow-up] perhaps somebody at MSFT can give you a better explanation

|||

Yes, I believe the current pipeline engine will not do a great job in optimizing this one. It would most probably end up using a single thread as Conditional Split is synchronous. You could try to artificially break synchronicity of the fuzzy grouping branches by adding a fake asynchronous transform (Union All with one input and one output should be good).

The next version of the pipeline scheduler should be able to better optimize distribution of threads.

Thanks.

|||

Bob and Rafael, thanks a lot for your input. So you are saying it should be like this?

Source File Feed

|

Split data into 7 groups

|

| | | | | | |

UnionAll UnionAll UnionAll UnionAll UnionAll UnionAll UnionAll

| | | | | | |

FzGrpg FzGrpg FzGrpg FzGrpg FzGrpg FzGrpg FzGrpg

| | | | | | |

Split Split Split Split Split Split Split

| | | | | | |

- -- -- -- -- -- --

| | | | | | | | | | | | | |

<- - - - - - - Write the Canonicals and Dupes from each of these splits into database - - - - - - - - ->

|||

As an alternative, could you do the split in one data flow, dumping each branch to a raw file, and then use a seperate data flow (or 7 data flows) to read in the raw files and do the Fuzzy Grouping?

The engine seems to optimize better with multiple data flows, than with multiple paths in the same data flow.

|||

Yes, that is how I meant to use Union Alls.

The idea of using raw files sounds good too. I would try both and see which one works better for youe scenario.

Thanks.

|||

Thanks a lot guys.

I tried the UnionAll approach and it works perfectly fine. I am able to see that all the threads go in parallel.

A run that used to take around 10 hours got completed in 2.5 hours and that's a lot of saving.

And utilitzation of the CPUs was 100%.

As and when I tweak this more I will keep you all posted.

Thanks again.

|||

KM68 wrote:

Thanks a lot guys.

I tried the UnionAll approach and it works perfectly fine. I am able to see that all the threads go in parallel.

A run that used to take around 10 hours got completed in 2.5 hours and that's a lot of saving.

And utilitzation of the CPUs was 100%.

As and when I tweak this more I will keep you all posted.

Thanks again.

[Microsoft follow-up] This sounds like a possitive feedback.

KM68,

We all will appreciate your updates.

|||

I am glad it worked.

Hopefully, with the next version of the data flow engine you will not need this workaround and the execution time can be trimmed even more.

Thanks.

|||

Thanks.

Any idea when the next version is scheduled for release?

Functionality Like Pivot Chart

There is a view on our server that has fields for project name and then 25
months, each row contains the descriptive name and the forecasted budget in
each month. I need to create a chart showing the total budget for month and
a running total for the year to date. In Excel this is easy with a Pivot
Chart, how do I do this in SSRS 2005?

TIA
DeanDiscover the joy of the 'matrix control' or as I like to think of it:

A design time pivot table, but much less fun to use.

You just set the groups on the columns or rows. Simple. You can even have (right click menu) subtotals.

If you need to have 'other data' displayed, you will need to fiddle about with the Inscope, and create phantom groupings.

see this:
http://www.sqlskills.com/blogs/liz/2006/07/21/ReportingServicesGettingTheMatrixToDisplayTwoSubtotalsForTheSameGroup.aspx

green bar matrix:
http://blogs.msdn.com/chrishays/archive/2004/08/30/GreenBarMatrix.aspx|||Can the matrix control display data as a CHART?|||http://msdn2.microsoft.com/en-us/library/aa964128(SQL.90).aspx

Function to seperate comma delimited db field into new table

Hi All,
I hope someone can help, i have taken on a project from someone and
have been given their db to work from, one thing they have done which i
know is bad design is seperate a lit of locations into one field with
commas, i.e 1,5,8,9. I now want to create a proper relationship by
adding a new relation table..
So instead of :
JobID LocationID
1 1,2,3
I will have in a new table:
JobID LocationID
1 1
1 2
1 3
Is there a function i can use or create to iterate through these old
values and insert them into my new table, the table is already to big
to do it manually, i only need to run it once so performance isnt an
issue..
Could someone point me in the right direction of where to search or a
start as i although i know a bit about sql this is above my head at the
mo..
Cheers
Anthonyhttp://www.aspfaq.com/2248
<anthonykallay@.hotmail.com> wrote in message
news:1133789661.982222.291240@.o13g2000cwo.googlegroups.com...
> Hi All,
> I hope someone can help, i have taken on a project from someone and
> have been given their db to work from, one thing they have done which i
> know is bad design is seperate a lit of locations into one field with
> commas, i.e 1,5,8,9. I now want to create a proper relationship by
> adding a new relation table..
> So instead of :
> JobID LocationID
> 1 1,2,3
> I will have in a new table:
> JobID LocationID
> 1 1
> 1 2
> 1 3
> Is there a function i can use or create to iterate through these old
> values and insert them into my new table, the table is already to big
> to do it manually, i only need to run it once so performance isnt an
> issue..
> Could someone point me in the right direction of where to search or a
> start as i although i know a bit about sql this is above my head at the
> mo..
> Cheers
> Anthony
>|||My personal favourite (by Dejan Sarka):
http://solidqualitylearning.com/blo.../10/22/200.aspx
ML
http://milambda.blogspot.com/|||Another possible option...
http://searchsqlserver.techtarget.c...1074960,00.html
"anthonykallay@.hotmail.com" wrote:

> Hi All,
> I hope someone can help, i have taken on a project from someone and
> have been given their db to work from, one thing they have done which i
> know is bad design is seperate a lit of locations into one field with
> commas, i.e 1,5,8,9. I now want to create a proper relationship by
> adding a new relation table..
> So instead of :
> JobID LocationID
> 1 1,2,3
> I will have in a new table:
> JobID LocationID
> 1 1
> 1 2
> 1 3
> Is there a function i can use or create to iterate through these old
> values and insert them into my new table, the table is already to big
> to do it manually, i only need to run it once so performance isnt an
> issue..
> Could someone point me in the right direction of where to search or a
> start as i although i know a bit about sql this is above my head at the
> mo..
> Cheers
> Anthony
>

Full-text Search, Query returns empty

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

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

Full-Text population problems

Hi

I'm getting the following error when I attempt to populate our Full Text Index on SQL
2000: The crawl seed in project SQL0000500005> cannot be accessed. Error: 800705b4 - This operation returned because
the timeout period expired. .

The crawl for project could not be started,
because no crawl seeds could be accessed. Fix the errors and try the crawl again.

I have seen in other threads and on the MS site how this is caused by the MSSQLService and the MS Search services not being syncronized and it can be fixed by changing the MSSQLService account to use the system account and back again.

The BUILTIN\Administrators group is present in SQL and has OWNER permissions. I even tried giving the SYSTEM group full access to the FTDATA folder.

This did NOT work for me. I am still getting the messages. One thing that is probably unique to my instalation is that it resides on the only server on this network so it is also the primary domain controler. Could this be why I cannot get the FT population to work?

I have another machine on a seperate network that is not a PDC and FT-Indexing works great on it but that doesn't help me for the network I need it to work on.

Does anyone have any other ideas I could try? I have been working on this for close to a week now.

The "Mav"How about the SQL Services account privileges and which accout is used?

Check what is the current state of process for indexing.

Refer to this
KBA (http://support.microsoft.com/default.aspx?scid=KB;en-us;q317746) to resovle the issue.|||I looked through that document and I have tried all that still no dice. The MS Search service is using the Local System account and the MSSQLServer service is using the local server administrator account. I tried changing this to the Local System acount using SQL Server Enterprise Manager(SSEM) this did not work so I changed it back to the local administrator account, once again using the SSEM.

Any other suggestions?

Thanks,
The Mav|||No one has any ideas? =(