Help importing legacy FoxPro db into SQL 2000

Greetings,
Any help/tips appreciated. I'm charged with importing a legacy FoxPro
database appliction into SQL 2000 and building a new .NET front-end for it.
I've imported .mdb, Excel, text files, other MS SQL data, etc in the past,
but the FoxPro stuff is rather different. I see the .dbf files (which I'm
guessing will import as tables), but when I try to import, the DSN import bo
x
comes up. I try 'Visual FoxPro drivers' but they're not installed. A little
research shows MS stopped bundling FoxPro drivers in MDAC 2.6.
Does anyone have experience with this type of import?
Thanks in advance!
jgHi Johnny,
Download and install the FoxPro and Visual FoxPro OLE DB data provider from
msdn.microsoft.com/vfoxpro/downloads/updates.
When you set up your import you'll want to determine whether you have a
database container (a DBC file is present in the directory where the DBFs
are) or free tables. With a DBC you connect to the DBC and with free tables
you just connect to the directory they are in.
After that it's just like importing any other OLE DB compliant data.
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@.msn.com www.cindywinegarden.com
"johnnyG" <johnnyG@.discussions.microsoft.com> wrote in message
news:CBE76BA7-C433-4EDD-8C73-123690CAA92E@.microsoft.com...
> Greetings,
> Any help/tips appreciated. I'm charged with importing a legacy FoxPro
> database appliction into SQL 2000 and building a new .NET front-end for
> it.
> I've imported .mdb, Excel, text files, other MS SQL data, etc in the past,
> but the FoxPro stuff is rather different. I see the .dbf files (which I'm
> guessing will import as tables), but when I try to import, the DSN import
> box
> comes up. I try 'Visual FoxPro drivers' but they're not installed. A
> little
> research shows MS stopped bundling FoxPro drivers in MDAC 2.6.
> Does anyone have experience with this type of import?
> Thanks in advance!
> jg|||If the original appliation is pre-VFP, then it's probably not normalized, so
you'll want to re-think the database model as well. I've seen legacy -> SQL
Server ports where this was not done.
"Cindy Winegarden" <cindy_winegarden@.msn.com> wrote in message
news:eFWvPxZLGHA.2904@.TK2MSFTNGP10.phx.gbl...
> Hi Johnny,
> Download and install the FoxPro and Visual FoxPro OLE DB data provider
> from msdn.microsoft.com/vfoxpro/downloads/updates.
> When you set up your import you'll want to determine whether you have a
> database container (a DBC file is present in the directory where the DBFs
> are) or free tables. With a DBC you connect to the DBC and with free
> tables you just connect to the directory they are in.
> After that it's just like importing any other OLE DB compliant data.
> --
> Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
> cindy_winegarden@.msn.com www.cindywinegarden.com
>
> "johnnyG" <johnnyG@.discussions.microsoft.com> wrote in message
> news:CBE76BA7-C433-4EDD-8C73-123690CAA92E@.microsoft.com...
>|||Thanks guys...I downloaded the data provider and found the DBC and managed t
o
successfully import the tables. I'm reviewing the warnings now (some date
data type precision issues), but overall I'm underway. Thanks Cindy.
And JT thanks for the sound advice also...
johnnG
"johnnyG" wrote:

> Greetings,
> Any help/tips appreciated. I'm charged with importing a legacy FoxPro
> database appliction into SQL 2000 and building a new .NET front-end for it
.
> I've imported .mdb, Excel, text files, other MS SQL data, etc in the past,
> but the FoxPro stuff is rather different. I see the .dbf files (which I'm
> guessing will import as tables), but when I try to import, the DSN import
box
> comes up. I try 'Visual FoxPro drivers' but they're not installed. A litt
le
> research shows MS stopped bundling FoxPro drivers in MDAC 2.6.
> Does anyone have experience with this type of import?
> Thanks in advance!
> jg|||Hi Johnny,
In case you haven't figured it out yet, FoxPro Date data types (a date only,
no time) can be "empty" as well as null or having a valid date. SQL Server
doesn't know how to handle these and substitutes 12/31/1899 or 1/1/1900. The
same goes with DateTime data types.
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@.msn.com www.cindywinegarden.com
"johnnyG" <johnnyG@.discussions.microsoft.com> wrote in message
news:FFFBC100-6D09-41CC-BB40-0452495014D2@.microsoft.com...
> Thanks guys...I downloaded the data provider and found the DBC and managed
> to
> successfully import the tables. I'm reviewing the warnings now (some date
> data type precision issues), but overall I'm underway. Thanks Cindy.
> And JT thanks for the sound advice also...sql

Help building query

Hello,
I need help in finding the best way to build a query for the following.
I have a table let's call EQPDTL.
This table contains all equipment usage for a company.
The table contains the followin fields:
CompanyNo
EquipNo
JobNo
DateEntered
RateCode (Can be H, I, B, Z, O, W)
DayOfWeek (1-7)
HoursUsed
TypeOfCost (0, 1, 2, 3, 4, or 5)
Here is a select statement that narrows down the actual records that I
need to work with. In english the records need to belong to CompanyNo X,
JobNo X, and it has to be a RateCode of (H, I, B, Z, O, or W), the type of
cost must not equal 0, and we only want records between the dates of X and
Y.
SELECT *
FROM EQPDTL
WHERE ((CompanyNo = 20 AND JobNo = '0003') AND
(RateCode = 'H' OR RateCode = 'I' OR RateCode = 'B' OR
RateCode = 'Z' OR RateCode = 'O' OR RateCode = 'W'))
AND TypeOfCost <> 0 AND
(DateEntered >= 20030101 AND DateEntered <= 20030401)
So now that we have the list of records now I need to get some
summarization data and have it in the following format.
1) For each piece of equipment I need to know the hours it was available.
This is simply the number of days it was used times 8. This would be the
first column.
2) For each piece of equipment I need to know the number of HoursUsed
based on the RateCode. So if equipment X had hours for 3 different rate
codes then at a minimum there would be at least 3 records in the original
dataset to work with... there could be more. There would be 6 columns, 1
for each RateCode (H, I, B, Z, O, W)
3) There would be another colum that has the total hours for all of the
RateCodes in #2 above.
4) There would be a final column that gives the percent utilized which is
calculated from the HoursUsed of RateCode H + O divided by AvailableHours
times 100.
The following query kinda gives me a summary look at the RateCodes where
each RateCode type summary is a record.
SELECT CompanyNo, EquipNo, RateCode, SUM(HoursUsed) AS Hours
FROM EQPDTL
WHERE ((CompanyNo = 20 AND JobNo = '0003') AND
(RateCode = 'H' OR RateCode = 'I' OR RateCode = 'B' OR RateCode = 'Z' OR
RateCode = 'O' OR RateCode = 'W')) AND TypeOfCost <> 0 AND
(DateEntered >= 20030101 AND DateEntered <= 20030401)
GROUP BY CompanyNo, EquipNo, RateCode
ORDER BY CompanyNo, EquipNo, RateCode
In the end I would like something like this as the results grouped by the
CompanyNo, JobNo, and EquipNo:
EquipNo, AvailableHours, H Hours, I Hours, O Hours, W Hours, Z Hours, B
Hours, Total Hours, Percent Utilized.
However I am unsure on what I should to to get this into more of a column
based results for each piece of equipment as well as get the other
results.
I can definitely take the first select statement and write an external
program to build a resulting table. I am sure I can also do a series of
temp tables to get a final result.
What I would like assistance on is how you would recommend doing this...
what do you believe would be the best method. I run into this type of
need several times a year and I really would like find a solution I can
use in situations as this.
Thank you for your time and willingness to help.
Greg
On Thu, 8 Sep 2005 10:15:12 -0400, Greg Saunders wrote:

>Hello,
>I need help in finding the best way to build a query for the following.
(very big snip)
>What I would like assistance on is how you would recommend doing this...
>what do you believe would be the best method. I run into this type of
>need several times a year and I really would like find a solution I can
>use in situations as this.
>Thank you for your time and willingness to help.
Hi Greg,
This kind of problem is typically known as crosstab or pivot. The usual
recommendation is to do this in the client, as it's basically just
another formatting issue. However, there are times when that's not
practical; in that case, you can use a variation of this simple example:
USE pubs
go
SELECT pub_id,
SUM(CASE WHEN type = 'business' THEN ytd_sales END) AS
business_sales,
SUM(CASE WHEN type = 'psychology' THEN ytd_sales END) AS
psychology_sales,
SUM(CASE WHEN type NOT IN ('business','psychology') THEN
ytd_sales END) AS other_sales,
SUM(ytd_sales) AS total_sales
FROM titles
GROUP BY pub_id
go
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||In article <1q14i1porio6ug54pglonpop1k6emrec6s@.4ax.com>,
hugo@.pe_NO_rFact.in_SPAM_fo says...
> On Thu, 8 Sep 2005 10:15:12 -0400, Greg Saunders wrote:
> (very big snip)
> Hi Greg,
> This kind of problem is typically known as crosstab or pivot. The usual
> recommendation is to do this in the client, as it's basically just
> another formatting issue. However, there are times when that's not
> practical; in that case, you can use a variation of this simple example:
> USE pubs
> go
> SELECT pub_id,
> SUM(CASE WHEN type = 'business' THEN ytd_sales END) AS
> business_sales,
> SUM(CASE WHEN type = 'psychology' THEN ytd_sales END) AS
> psychology_sales,
> SUM(CASE WHEN type NOT IN ('business','psychology') THEN
> ytd_sales END) AS other_sales,
> SUM(ytd_sales) AS total_sales
> FROM titles
> GROUP BY pub_id
> go
>
> Best, Hugo
>
Hugo,
Thanks for the reply... I ended up going down the client route. I
appreciate your example... I started down that route as well and then just
decided I like the client solution for my needs.
Greg

Help building query

Hello,
I need help in finding the best way to build a query for the following.
I have a table let's call EQPDTL.
This table contains all equipment usage for a company.
The table contains the followin fields:
CompanyNo
EquipNo
JobNo
DateEntered
RateCode (Can be H, I, B, Z, O, W)
DayOfWeek (1-7)
HoursUsed
TypeOfCost (0, 1, 2, 3, 4, or 5)
Here is a select statement that narrows down the actual records that I
need to work with. In english the records need to belong to CompanyNo X,
JobNo X, and it has to be a RateCode of (H, I, B, Z, O, or W), the type of
cost must not equal 0, and we only want records between the dates of X and
Y.
SELECT *
FROM EQPDTL
WHERE ((CompanyNo = 20 AND JobNo = '0003') AND
(RateCode = 'H' OR RateCode = 'I' OR RateCode = 'B' OR
RateCode = 'Z' OR RateCode = 'O' OR RateCode = 'W'))
AND TypeOfCost <> 0 AND
(DateEntered >= 20030101 AND DateEntered <= 20030401)
So now that we have the list of records now I need to get some
summarization data and have it in the following format.
1) For each piece of equipment I need to know the hours it was available.
This is simply the number of days it was used times 8. This would be the
first column.
2) For each piece of equipment I need to know the number of HoursUsed
based on the RateCode. So if equipment X had hours for 3 different rate
codes then at a minimum there would be at least 3 records in the original
dataset to work with... there could be more. There would be 6 columns, 1
for each RateCode (H, I, B, Z, O, W)
3) There would be another colum that has the total hours for all of the
RateCodes in #2 above.
4) There would be a final column that gives the percent utilized which is
calculated from the HoursUsed of RateCode H + O divided by AvailableHours
times 100.
The following query kinda gives me a summary look at the RateCodes where
each RateCode type summary is a record.
SELECT CompanyNo, EquipNo, RateCode, SUM(HoursUsed) AS Hours
FROM EQPDTL
WHERE ((CompanyNo = 20 AND JobNo = '0003') AND
(RateCode = 'H' OR RateCode = 'I' OR RateCode = 'B' OR RateCode = 'Z' OR
RateCode = 'O' OR RateCode = 'W')) AND TypeOfCost <> 0 AND
(DateEntered >= 20030101 AND DateEntered <= 20030401)
GROUP BY CompanyNo, EquipNo, RateCode
ORDER BY CompanyNo, EquipNo, RateCode
In the end I would like something like this as the results grouped by the
CompanyNo, JobNo, and EquipNo:
EquipNo, AvailableHours, H Hours, I Hours, O Hours, W Hours, Z Hours, B
Hours, Total Hours, Percent Utilized.
However I am unsure on what I should to to get this into more of a column
based results for each piece of equipment as well as get the other
results.
I can definitely take the first select statement and write an external
program to build a resulting table. I am sure I can also do a series of
temp tables to get a final result.
What I would like assistance on is how you would recommend doing this...
what do you believe would be the best method. I run into this type of
need several times a year and I really would like find a solution I can
use in situations as this.
Thank you for your time and willingness to help.
GregOn Thu, 8 Sep 2005 10:15:12 -0400, Greg Saunders wrote:

>Hello,
>I need help in finding the best way to build a query for the following.
(very big snip)
>What I would like assistance on is how you would recommend doing this...
>what do you believe would be the best method. I run into this type of
>need several times a year and I really would like find a solution I can
>use in situations as this.
>Thank you for your time and willingness to help.
Hi Greg,
This kind of problem is typically known as crosstab or pivot. The usual
recommendation is to do this in the client, as it's basically just
another formatting issue. However, there are times when that's not
practical; in that case, you can use a variation of this simple example:
USE pubs
go
SELECT pub_id,
SUM(CASE WHEN type = 'business' THEN ytd_sales END) AS
business_sales,
SUM(CASE WHEN type = 'psychology' THEN ytd_sales END) AS
psychology_sales,
SUM(CASE WHEN type NOT IN ('business','psychology') THEN
ytd_sales END) AS other_sales,
SUM(ytd_sales) AS total_sales
FROM titles
GROUP BY pub_id
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||In article <1q14i1porio6ug54pglonpop1k6emrec6s@.4ax.com>,
hugo@.pe_NO_rFact.in_SPAM_fo says...
> On Thu, 8 Sep 2005 10:15:12 -0400, Greg Saunders wrote:
>
> (very big snip)
> Hi Greg,
> This kind of problem is typically known as crosstab or pivot. The usual
> recommendation is to do this in the client, as it's basically just
> another formatting issue. However, there are times when that's not
> practical; in that case, you can use a variation of this simple example:
> USE pubs
> go
> SELECT pub_id,
> SUM(CASE WHEN type = 'business' THEN ytd_sales END) AS
> business_sales,
> SUM(CASE WHEN type = 'psychology' THEN ytd_sales END) AS
> psychology_sales,
> SUM(CASE WHEN type NOT IN ('business','psychology') THEN
> ytd_sales END) AS other_sales,
> SUM(ytd_sales) AS total_sales
> FROM titles
> GROUP BY pub_id
> go
>
> Best, Hugo
>
Hugo,
Thanks for the reply... I ended up going down the client route. I
appreciate your example... I started down that route as well and then just
decided I like the client solution for my needs.
Gregsql

Help building query

Hello,
I need help in finding the best way to build a query for the following.
I have a table let's call EQPDTL.
This table contains all equipment usage for a company.
The table contains the followin fields:
CompanyNo
EquipNo
JobNo
DateEntered
RateCode (Can be H, I, B, Z, O, W)
DayOfWeek (1-7)
HoursUsed
TypeOfCost (0, 1, 2, 3, 4, or 5)
Here is a select statement that narrows down the actual records that I
need to work with. In english the records need to belong to CompanyNo X,
JobNo X, and it has to be a RateCode of (H, I, B, Z, O, or W), the type of
cost must not equal 0, and we only want records between the dates of X and
Y.
SELECT *
FROM EQPDTL
WHERE ((CompanyNo = 20 AND JobNo = '0003') AND
(RateCode = 'H' OR RateCode = 'I' OR RateCode = 'B' OR
RateCode = 'Z' OR RateCode = 'O' OR RateCode = 'W'))
AND TypeOfCost <> 0 AND
(DateEntered >= 20030101 AND DateEntered <= 20030401)
So now that we have the list of records now I need to get some
summarization data and have it in the following format.
1) For each piece of equipment I need to know the hours it was available.
This is simply the number of days it was used times 8. This would be the
first column.
2) For each piece of equipment I need to know the number of HoursUsed
based on the RateCode. So if equipment X had hours for 3 different rate
codes then at a minimum there would be at least 3 records in the original
dataset to work with... there could be more. There would be 6 columns, 1
for each RateCode (H, I, B, Z, O, W)
3) There would be another colum that has the total hours for all of the
RateCodes in #2 above.
4) There would be a final column that gives the percent utilized which is
calculated from the HoursUsed of RateCode H + O divided by AvailableHours
times 100.
The following query kinda gives me a summary look at the RateCodes where
each RateCode type summary is a record.
SELECT CompanyNo, EquipNo, RateCode, SUM(HoursUsed) AS Hours
FROM EQPDTL
WHERE ((CompanyNo = 20 AND JobNo = '0003') AND
(RateCode = 'H' OR RateCode = 'I' OR RateCode = 'B' OR RateCode = 'Z' OR
RateCode = 'O' OR RateCode = 'W')) AND TypeOfCost <> 0 AND
(DateEntered >= 20030101 AND DateEntered <= 20030401)
GROUP BY CompanyNo, EquipNo, RateCode
ORDER BY CompanyNo, EquipNo, RateCode
In the end I would like something like this as the results grouped by the
CompanyNo, JobNo, and EquipNo:
EquipNo, AvailableHours, H Hours, I Hours, O Hours, W Hours, Z Hours, B
Hours, Total Hours, Percent Utilized.
However I am unsure on what I should to to get this into more of a column
based results for each piece of equipment as well as get the other
results.
I can definitely take the first select statement and write an external
program to build a resulting table. I am sure I can also do a series of
temp tables to get a final result.
What I would like assistance on is how you would recommend doing this...
what do you believe would be the best method. I run into this type of
need several times a year and I really would like find a solution I can
use in situations as this.
Thank you for your time and willingness to help.
GregOn Thu, 8 Sep 2005 10:15:12 -0400, Greg Saunders wrote:
>Hello,
>I need help in finding the best way to build a query for the following.
(very big snip)
>What I would like assistance on is how you would recommend doing this...
>what do you believe would be the best method. I run into this type of
>need several times a year and I really would like find a solution I can
>use in situations as this.
>Thank you for your time and willingness to help.
Hi Greg,
This kind of problem is typically known as crosstab or pivot. The usual
recommendation is to do this in the client, as it's basically just
another formatting issue. However, there are times when that's not
practical; in that case, you can use a variation of this simple example:
USE pubs
go
SELECT pub_id,
SUM(CASE WHEN type = 'business' THEN ytd_sales END) AS
business_sales,
SUM(CASE WHEN type = 'psychology' THEN ytd_sales END) AS
psychology_sales,
SUM(CASE WHEN type NOT IN ('business','psychology') THEN
ytd_sales END) AS other_sales,
SUM(ytd_sales) AS total_sales
FROM titles
GROUP BY pub_id
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||In article <1q14i1porio6ug54pglonpop1k6emrec6s@.4ax.com>,
hugo@.pe_NO_rFact.in_SPAM_fo says...
> On Thu, 8 Sep 2005 10:15:12 -0400, Greg Saunders wrote:
> >Hello,
> >
> >I need help in finding the best way to build a query for the following.
> (very big snip)
> >What I would like assistance on is how you would recommend doing this...
> >what do you believe would be the best method. I run into this type of
> >need several times a year and I really would like find a solution I can
> >use in situations as this.
> >
> >Thank you for your time and willingness to help.
> Hi Greg,
> This kind of problem is typically known as crosstab or pivot. The usual
> recommendation is to do this in the client, as it's basically just
> another formatting issue. However, there are times when that's not
> practical; in that case, you can use a variation of this simple example:
> USE pubs
> go
> SELECT pub_id,
> SUM(CASE WHEN type = 'business' THEN ytd_sales END) AS
> business_sales,
> SUM(CASE WHEN type = 'psychology' THEN ytd_sales END) AS
> psychology_sales,
> SUM(CASE WHEN type NOT IN ('business','psychology') THEN
> ytd_sales END) AS other_sales,
> SUM(ytd_sales) AS total_sales
> FROM titles
> GROUP BY pub_id
> go
>
> Best, Hugo
>
Hugo,
Thanks for the reply... I ended up going down the client route. I
appreciate your example... I started down that route as well and then just
decided I like the client solution for my needs.
Greg

Help building a #temp table

I have the following query:

--build query for #temp table
select 'L' as RECTYPE, identity(int, 1, 1) as JELINENO, max(i.jeno) + 1 as JENO, 'AJE' as JECODE,
--build the fiscal year, when month = October, November or December add 1 to actual year
FY = case
when datepart(month, GetDate()) = '10' then datepart(year, GetDate()) + 1
when datepart(month, GetDate()) = '11' then datepart(year, GetDate()) + 1
when datepart(month, GetDate()) = '12' then datepart(year, GetDate()) + 1
else datepart(year, GetDate())
end,
-- The PD starts in April and increases by 1 each month
PD = case
when datepart(month, GetDate()) = '1' then '10'
when datepart(month, GetDate()) = '2' then '11'
when datepart(month, GetDate()) = '3' then '12'
when datepart(month, GetDate()) = '4' then '1'
when datepart(month, GetDate()) = '5' then '2'
when datepart(month, GetDate()) = '6' then '3'
when datepart(month, GetDate()) = '7' then '4'
when datepart(month, GetDate()) = '8' then '5'
when datepart(month, GetDate()) = '9' then '6'
when datepart(month, GetDate()) = '10' then '7'
when datepart(month, GetDate()) = '11' then '8'
when datepart(month, GetDate()) = '12' then '9'
else 'error'
end,
'1' as SUBPD, 'N' as REVFLAG, 'Journal Entry from MAXIMO' as HEADDESC,
--If the issuetype is a return the AMOUNT is a negative value, multiply by -1
AMOUNT = case
when m.linecost < 0 then m.linecost * -1
else m.linecost
end
into #temp
from matusetrans m, issuesreturns i
go
select * from #temp
drop table #temp

When I run this query, I get the following error:

Server: Msg 8118, Level 16, State 1, Line 2
Column 'm.linecost' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 2
Column 'm.linecost' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 2
Column 'm.linecost' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name '#temp'.

I can find no reason for this error. If I run the query without the AMOUNT = case statement, it works fine. Can anyone help?

Thanks,

Dave

The mistake only arises in the AMOUNT=CASE statement since it's the only one (except from the MAX(i.jeno) column) that's accesing a real column in the database. The rest of the columns are calculated.

So, as you already have an aggregate function (the MAX) the rest of the columns from real tables must either have an aggregate function or the SELECT clause must contain a GROUP BY clause.

You can't form a SELECT with only one MAX column and the rest of them normal columns without telling SQL how it should group the results from where to choose the MAX value from.

If you really want to achieve that you have to calculate the MAX column either before in a variable or in the same SELECT with a sub-SELECT query.

IE: SELECT var1, var2, var3, (SELECT MAX(othervar) FROM table)

Hope i was clear.
Good luck,
Alan.

|||

First of all I would compress the code and rewrite it as following.

Please post DDLs as it's not clear what do you want to do. the problem is clear as you have a reference to the column m.linecost that is not in a group by list. Just to get rid of the error you can add a group by abs(m.linecost), but it doesn't look like what you want to do.

select
'L' as RECTYPE,
identity(int, 1, 1) as JELINENO,
max(i.jeno) + 1 as JENO,
'AJE' as JECODE,

--build the fiscal year, when month = October, November or December add 1 to actual year

FY = datepart(year, GetDate()) + (datepart(month, GetDate()) + 2) / 12,

-- The PD starts in April and increases by 1 each month

PD = (datepart(month, GetDate()) + 8) % 12 + 1,

'1' as SUBPD,

'N' as REVFLAG,

'Journal Entry from MAXIMO' as HEADDESC,

--If the issuetype is a return the AMOUNT is a negative value, multiply by -1

AMOUNT = abs(m.linecost)

into #temp

from matusetrans m, issuesreturns i

|||Dave -

You can't use an Aggregate Function on only one column without using a Group By for the other columns

So you either need to group by m.linecost OR put an Agrregate around it - like max() or min().

To simpify your query

select
max(i.jeno) + 1 as JENO, -- YOU USED MAX HERE
AMOUNT = case
when m.linecost < 0 then m.linecost * -1
-- SO YOU EITHER NEED TO USE AN AGRREGRATE HERE OR
else m.linecost
end
into #temp
from matusetrans m, issuesreturns i
-- OR GROUP BY m.linecost HERE

Hope that helps

AWAL

HELP - Storing Full Text Catalog to Different Location

Hi,
I am building a database install aplication that among all other
stuff, creates and populates full-text catalogs for my database.
All this is done in VB.net and SQL Server 2003
My application works fine, here is the come sample:
oSQLServer = CreateObject("SQLDMO.SQLServer")
oSQLServer.connect(strServer, strUserID, strPassword)
' enable full-text indexing for provided database
oSQLServer.Databases(strDBName).EnableFullTextCata logs()
' create full text catalog and add it to the collection
Dim strFullTextCatalogName As String
strFullTextCatalogName = strCatalogPrefix & "_documenttitle"
oFullTextCatalog = CreateObject("SQLDMO.FullTextCatalog")
oFullTextCatalog.Name = strFullTextCatalogName 'name
oFullTextCatalog.RootPath = strCatalogPath 'location
' create full-text catalog on the server and add it to
' the collection of full-text catalogs
oSQLServer.databases(strDBName).fulltextcatalogs.a dd(oFullTextCatalog)
However, I get an System.Runtime.InteropServices.COMException
exception with additional information as:
Additional information:[Microsoft][ODBC SQL Server Driver][SQL
Server]Access is denied to 'C:\Documents and
Settings\username\MyDocuments', or the path is invalid. Full-text
search was not installed properly.
The path to which access is denied is the value of my variable
strCatalogPath that I assign to my full-text catalog RootPath
property. (see code sample above)
How can I fix this error. Please help.
Any help will be appreciated,
_dino_
Dino,
Well, the error message is clear "Access is denied to 'C:\Documents and
Settings\username\MyDocuments', or the path is invalid."
Did you try using sp_fulltext_catalog and create a FT Catalog using the same
path? or perhaps specifically reference 'C:\Program Files\Microsoft SQL
Server\Mssql\Ftdata' enclosed in double quotes? Do the folders "username"
or "MyDocuments" exist?
SQL Server BOL title "sp_fulltext_catalog" for the path states
"[@.path =] 'root_directory'
Is the root directory (not the complete physical path) for a create action.
root_directory is nvarchar(100) and has a default value of NULL, which
indicates the use of the default location specified at setup. This is the
Ftdata subdirectory in the Mssql directory; for example, C:\Program
Files\Microsoft SQL Server\Mssql\Ftdata. The specified root directory must
reside on a drive on the same computer, consist of more than just the drive
letter, and cannot be a relative path. Network drives, removable drives,
floppy disks, and UNC paths are not supported. Full-text catalogs must be
created on a local hard drive associated with an instance of Microsoft SQL
ServerT.
@.path is valid only when action is create. For actions other than create
(stop, rebuild, and so on), @.path must be NULL or omitted."
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Dino Buljubasic" <dino@.noplacelikehome.com> wrote in message
news:fs05f1lapksvfr4i0lgot578hubm1te3gj@.4ax.com...
> Hi,
> I am building a database install aplication that among all other
> stuff, creates and populates full-text catalogs for my database.
> All this is done in VB.net and SQL Server 2003
> My application works fine, here is the come sample:
> oSQLServer = CreateObject("SQLDMO.SQLServer")
> oSQLServer.connect(strServer, strUserID, strPassword)
> ' enable full-text indexing for provided database
> oSQLServer.Databases(strDBName).EnableFullTextCata logs()
> ' create full text catalog and add it to the collection
> Dim strFullTextCatalogName As String
> strFullTextCatalogName = strCatalogPrefix & "_documenttitle"
> oFullTextCatalog = CreateObject("SQLDMO.FullTextCatalog")
> oFullTextCatalog.Name = strFullTextCatalogName 'name
> oFullTextCatalog.RootPath = strCatalogPath 'location
>
> ' create full-text catalog on the server and add it to
> ' the collection of full-text catalogs
> oSQLServer.databases(strDBName).fulltextcatalogs.a dd(oFullTextCatalog)
> However, I get an System.Runtime.InteropServices.COMException
> exception with additional information as:
> Additional information:[Microsoft][ODBC SQL Server Driver][SQL
> Server]Access is denied to 'C:\Documents and
> Settings\username\MyDocuments', or the path is invalid. Full-text
> search was not installed properly.
> The path to which access is denied is the value of my variable
> strCatalogPath that I assign to my full-text catalog RootPath
> property. (see code sample above)
> How can I fix this error. Please help.
> Any help will be appreciated,
> _dino_
|||Are you utilizing MSDE or SQL Server Express? Full-Text is not installed or
supported on either.
"Dino Buljubasic" wrote:

> Hi,
> I am building a database install aplication that among all other
> stuff, creates and populates full-text catalogs for my database.
> All this is done in VB.net and SQL Server 2003
> My application works fine, here is the come sample:
> oSQLServer = CreateObject("SQLDMO.SQLServer")
> oSQLServer.connect(strServer, strUserID, strPassword)
> ' enable full-text indexing for provided database
> oSQLServer.Databases(strDBName).EnableFullTextCata logs()
> ' create full text catalog and add it to the collection
> Dim strFullTextCatalogName As String
> strFullTextCatalogName = strCatalogPrefix & "_documenttitle"
> oFullTextCatalog = CreateObject("SQLDMO.FullTextCatalog")
> oFullTextCatalog.Name = strFullTextCatalogName 'name
> oFullTextCatalog.RootPath = strCatalogPath 'location
>
> ' create full-text catalog on the server and add it to
> ' the collection of full-text catalogs
> oSQLServer.databases(strDBName).fulltextcatalogs.a dd(oFullTextCatalog)
> However, I get an System.Runtime.InteropServices.COMException
> exception with additional information as:
> Additional information:[Microsoft][ODBC SQL Server Driver][SQL
> Server]Access is denied to 'C:\Documents and
> Settings\username\MyDocuments', or the path is invalid. Full-text
> search was not installed properly.
> The path to which access is denied is the value of my variable
> strCatalogPath that I assign to my full-text catalog RootPath
> property. (see code sample above)
> How can I fix this error. Please help.
> Any help will be appreciated,
> _dino_
>