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 appreciated with this select statement please

How can I do this in one query
I've got a table like this
ID RESOURCEID DATE QUANTITY
The Id is unique (but just a counter not of interest here), there may be one
resourceid for each date and an asscoiated quantity.
I want to select all resourceid (distinct) and I just want just the
resourceid and the date and quantity associated
with that post that is less than today but nearast in time.
To examplifY
if some records look like like this
ID RESOURCEID DATE QUANTITY
1 1 2003-12-01 1.3
2 1 2004-01-25 1.4
3 2 2002-01-01 1.7
4 2 2003-07-27 2.8
I want my select statement to return only the two following post
2 1 2004-01-25 1.4
4 2 2003-07-27 2.8
That is one post for each resource and the post should be the one were the
date is less than today but closest in time
I want to do this without using stored procedure or view, since I'm not
allowed to create objects in the database.SELECT id, resourceid, date, quantity
FROM SomeTable AS S
WHERE date = (SELECT MAX(date)
FROM SomeTable
WHERE date < CONVERT(CHAR(8),CURRENT_TIMESTAMP,112)
AND resourceid = S.resourceid)
--
David Portas
SQL Server MVP
--

help appreciated with this select statement please

How can I do this in one query
I've got a table like this
ID RESOURCEID DATE QUANTITY
The Id is unique (but just a counter not of interest here), there may be one
resourceid for each date and an asscoiated quantity.
I want to select all resourceid (distinct) and I just want just the
resourceid and the date and quantity associated
with that post that is less than today but nearast in time.
To examplifY
if some records look like like this
ID RESOURCEID DATE QUANTITY
1 1 2003-12-01 1.3
2 1 2004-01-25 1.4
3 2 2002-01-01 1.7
4 2 2003-07-27 2.8
I want my select statement to return only the two following post
2 1 2004-01-25 1.4
4 2 2003-07-27 2.8
That is one post for each resource and the post should be the one were the
date is less than today but closest in time
I want to do this without using stored procedure or view, since I'm not
allowed to create objects in the database.SELECT id, resourceid, date, quantity
FROM SomeTable AS S
WHERE date =
(SELECT MAX(date)
FROM SomeTable
WHERE date < CONVERT(CHAR(8),CURRENT_TIMESTAMP,112)
AND resourceid = S.resourceid)
David Portas
SQL Server MVP
--

Help Altering Database Table from NULL to NOT NULL

I would drop and add the table but the data can't be deleted. So if anyone could help with the statement it would be greatly appreciated. ThanksYou alter columns to NOT NULL, not tables. Can you explain why you're trying to do this? What are you going to put in all the columns that have NULL? Why does the table have NULL in the first place. You should post what the table looks like with a couple sample rows.|||My apologies I meant a column. Currently a remote table that I'm adding a new column to where the people there have no idea how to use SQL Server. I've made a new SQL script to add a new column and have to set the column to NULL since its a new column being added to an existing table. After the apropriate data is in the new column I need to set the column to NOT NULL for future entries.....|||Could you add the column allowed to have NULL, then update the table where the column get's some value, then alter the table setting the column to NOT NULL?|||Thats pretty much what I'm trying to do but via scripts. Circumstances keep me from doing this manually so I'm trying to get it setup via a script.....|||so at which of the three steps are you stuck?|||Are you just trying to change the column null to not null if something is meet?|||if exists (select 1 from your_table where new_column is null)
raiserror ('You can't make column NON-NULLABLE if there is at least 1 NULL in it!, 15, 1)
else
alter table your_table alter column new_column <data_type> not null

HELP - Need a very quick code sample for calling report

Hi - any help would be tremendously appreciated.

I have been asked to create a quick project that calls a report from a hyperlink and need just a very basic way to do so.

The report is published to a SRS Server so all I need to be able to do is have a hypelink that will show the report on screen

Thanks in advance for any help

Rendering a Report using URL Access

If the info above is not sufficient you can google for the above keywords and am sure you will find what you need.

|||

If you need more detail, the full syntax is available athttp://msdn.microsoft.com/library/en-us/RSPROG/htm/rsp_prog_urlaccess_7kbq.asp

The basic syntax is

http://server/virtualroot?[/pathinfo]&prefix:param=value[&prefix:param=value]...n]

sohttp://reportbox/reportserver?/CorpReports/SalesSummary&rs:Command=Render&rs:format=HTML4.0

wouldrender theSalesSummary report in theCorpReports folder on thereportserver vroot on thereportbox server inHTML 4.0 format