Converting DBF to SQL Server

Hi,
I am working with a 3rd party application that uses DBF files. We are in the
process of discontinuing our contract with them and we need to convert the
data to SQL Server. I setup a Linked Server to access the data, but when I
try to select from it I get an error. Details below.
Any assistance you can provide is greatly appreciated.
Thank you,
Michael
SELECT * From
OpenQuery(DBF, 'select * from Person')
Error:
Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'select * from Person'. The OLE DB provider
'Microsoft.Jet.OLEDB.4.0' indicates that the object has no columns.
OLE DB error trace [Non-interface error: OLE DB provider unable to process
object, since the object has no columnsProviderName='Microsoft.Jet.OLEDB.4.0',
Query=select * from Person'].
--
Message posted via http://www.sqlmonster.comHi Michael,
First off, if your tables are FoxPro DBFs, try using the FoxPro and Visual
FoxPro OLE DB data provider, downloadable from
msdn.microsoft.com/vfoxpro/downloads/updates. Set up your linked server as
follows:
-- For the @.DataSrc value, if your Fox tables are "free" tables (no DBC file
is present in the directory) use only the path to the directory and put
double quotes around it if it has spaces. Something like '"C:\My
Directory\"'
-- If a DBC files is present the @.DataSrc value needs to point to the DBC
file as below.
EXEC master.dbo.sp_addlinkedserver
@.server = N'VFP_NORTHWIND', -- Your linked server name here
@.srvproduct=N'Visual FoxPro 9', -- can be anything
@.provider=N'VFPOLEDB',
@.datasrc=N'"C:\PROGRAM FILES\MICROSOFT VISUAL FOXPRO
9\Samples\Northwind\Northwind.dbc"', @.provstr=N'VFPOLEDB.1'
Then, you can query your tables like:
Select * From YourLinkedServer...YourTable
-- Note 3 dots, and don't use the DBF extension such as YourTable.dbf. Even
if your data source points to a DBC file just use the table name you want to
select from.
--
Cindy Winegarden MCSD, Microsoft Most Valuable Professional
cindy@.cindywinegarden.com
"michaelg via SQLMonster.com" <u13012@.uwe> wrote in message
news:63d4247ce59e5@.uwe...
> ... DBF files. ...I setup a Linked Server to access the data, but when I
> try to select from it I get an error. Details below.
> SELECT * From
> OpenQuery(DBF, 'select * from Person')
> Error:
> Server: Msg 7357, Level 16, State 2, Line 1
> Could not process object 'select * from Person'. The OLE DB provider
> 'Microsoft.Jet.OLEDB.4.0' ....|||Hi Cindy,
Thanks for the feedback. The setup we have is a DBF and a DBT file in the
folder. The options below do not work. When I run Select * from LinkedServer..|||Hi Michael,
The contents of FoxPro Memo fields are stored in FPT files with names
matching the name of the DBF they go with. Some other DBF file formats,
perhaps Clipper, use DBT files for their Memo fields.
If this is a one-time thing try changing the extension of the DBT file to
FPT. However, if the DBF's table header format is incompatible with Jet (or
FoxPro OLE DB if you use that) then I can't help you further. You'll have to
find out exactly what application created the files and probably use drivers
related to that table format.
--
Cindy Winegarden MCSD, Microsoft Most Valuable Professional
cindy@.cindywinegarden.com
"michaelgangeri via SQLMonster.com" <u13012@.uwe> wrote in message
news:63df901b8d1c5@.uwe...
> Thanks for the feedback. The setup we have is a DBF and a DBT ...
> OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
> [OLE/DB provider returned message: Cannot locate the requested Xbase memo
> file.]
> OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
> IDBSchemaRowset:
> :GetRowset returned 0x80004005: ].

Converting DBF to SQL Server

Hi,
I am working with a 3rd party application that uses DBF files. We are in the
process of discontinuing our contract with them and we need to convert the
data to SQL Server. I setup a Linked Server to access the data, but when I
try to select from it I get an error. Details below.
Any assistance you can provide is greatly appreciated.
Thank you,
Michael
SELECT * From
OpenQuery(DBF, 'select * from Person')
Error:
Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'select * from Person'. The OLE DB provider
'Microsoft.Jet.OLEDB.4.0' indicates that the object has no columns.
OLE DB error trace [Non-interface error: OLE DB provider unable to proc
ess
object, since the object has no columnsProviderName='Microsoft.Jet.OLEDB.4.0
',
Query=select * from Person'].
Message posted via http://www.droptable.comHi Michael,
First off, if your tables are FoxPro DBFs, try using the FoxPro and Visual
FoxPro OLE DB data provider, downloadable from
msdn.microsoft.com/vfoxpro/downloads/updates. Set up your linked server as
follows:
-- For the @.DataSrc value, if your Fox tables are "free" tables (no DBC file
is present in the directory) use only the path to the directory and put
double quotes around it if it has spaces. Something like '"C:\My
Directory\"'
-- If a DBC files is present the @.DataSrc value needs to point to the DBC
file as below.
EXEC master.dbo.sp_addlinkedserver
@.server = N'VFP_NORTHWIND', -- Your linked server name here
@.srvproduct=N'Visual FoxPro 9', -- can be anything
@.provider=N'VFPOLEDB',
@.datasrc=N'"C:\PROGRAM FILES\MICROSOFT VISUAL FOXPRO
9\Samples\Northwind\Northwind.dbc"', @.provstr=N'VFPOLEDB.1'
Then, you can query your tables like:
Select * From YourLinkedServer...YourTable
-- Note 3 dots, and don't use the DBF extension such as YourTable.dbf. Even
if your data source points to a DBC file just use the table name you want to
select from.
Cindy Winegarden MCSD, Microsoft Most Valuable Professional
cindy@.cindywinegarden.com
"michaelg via droptable.com" <u13012@.uwe> wrote in message
news:63d4247ce59e5@.uwe...
> ... DBF files. ...I setup a Linked Server to access the data, but when I
> try to select from it I get an error. Details below.

> SELECT * From
> OpenQuery(DBF, 'select * from Person')
> Error:
> Server: Msg 7357, Level 16, State 2, Line 1
> Could not process object 'select * from Person'. The OLE DB provider
> 'Microsoft.Jet.OLEDB.4.0' ....|||Hi Cindy,
Thanks for the feedback. The setup we have is a DBF and a DBT file in the
folder. The options below do not work. When I run Select * from LinkedServer
.
Table, I get the following error:
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: Cannot locate the requested Xbase mem
o
file.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBSchemaR
owset:
:GetRowset returned 0x80004005: ].
Hopefully this sheds some light on my issue?
Thanks again,
Michael
Cindy Winegarden wrote:[vbcol=seagreen]
>Hi Michael,
>First off, if your tables are FoxPro DBFs, try using the FoxPro and Visual
>FoxPro OLE DB data provider, downloadable from
>msdn.microsoft.com/vfoxpro/downloads/updates. Set up your linked server as
>follows:
>-- For the @.DataSrc value, if your Fox tables are "free" tables (no DBC fil
e
>is present in the directory) use only the path to the directory and put
>double quotes around it if it has spaces. Something like '"C:\My
>Directory\"'
>-- If a DBC files is present the @.DataSrc value needs to point to the DBC
>file as below.
>EXEC master.dbo.sp_addlinkedserver
> @.server = N'VFP_NORTHWIND', -- Your linked server name here
> @.srvproduct=N'Visual FoxPro 9', -- can be anything
> @.provider=N'VFPOLEDB',
> @.datasrc=N'"C:\PROGRAM FILES\MICROSOFT VISUAL FOXPRO
>9\Samples\Northwind\Northwind.dbc"', @.provstr=N'VFPOLEDB.1'
>Then, you can query your tables like:
>Select * From YourLinkedServer...YourTable
>-- Note 3 dots, and don't use the DBF extension such as YourTable.dbf. Even
>if your data source points to a DBC file just use the table name you want t
o
>select from.
>
>
>[quoted text clipped - 3 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200607/1|||Hi Michael,
The contents of FoxPro Memo fields are stored in FPT files with names
matching the name of the DBF they go with. Some other DBF file formats,
perhaps Clipper, use DBT files for their Memo fields.
If this is a one-time thing try changing the extension of the DBT file to
FPT. However, if the DBF's table header format is incompatible with Jet (or
FoxPro OLE DB if you use that) then I can't help you further. You'll have to
find out exactly what application created the files and probably use drivers
related to that table format.
Cindy Winegarden MCSD, Microsoft Most Valuable Professional
cindy@.cindywinegarden.com
"michaelgangeri via droptable.com" <u13012@.uwe> wrote in message
news:63df901b8d1c5@.uwe...

> Thanks for the feedback. The setup we have is a DBF and a DBT ...

> OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
> [OLE/DB provider returned message: Cannot locate the requested Xbase m
emo
> file.]
> OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
> IDBSchemaRowset:
> :GetRowset returned 0x80004005: ].

Convert Int to Time

I have a DB that gets telephone call information from a telephone switch. We use 3rd part software and a db to capture all of this. However for some reason most of the fields are stored as int DateTime is stored as an int. I am able to convert most of the date field from int by doing select convert(datetime,convert(varchar(8), localday)) that works fine the int are in the db as 20061029 for 10-29-2006.
How ever I can't convert the columns starttime and endtime. They are also stored as int but
They don't seem to store a date.
For instance for the date 9/29/2006 the start time of one particular call is
1159533056 and the endtime is 1159533264 All i need to get from this is the time since I already have the date from another column. I can't figure out what this data represents.
How can I convert this to datetime?

Thanks!

Quote:

"The datetime and smalldatetime data types are stored internally as integers. The datetime data type is stored as a pair of four-bytes integers, which together represent the number of milliseconds since midnight of January 1, 1753. The first four bytes store the date, and the second four bytes store the time. The smalldatetime data type is stored as a pair of two-byte integers, which together represent the number of minutes since midnight on January 1, 1900. The first two bytes store the date, and the second two bytes store the time."

Also, check out this: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp (see the first table on that page).

So, if your columns' data types are datetime or smalldatetime (but not "int", which would be bizarre), you don't need to concern yourself with converting it manually and/or trying to figure out what exactly this integer means. Just call the appropriate version of CONVERT (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp).

|||

It looks to me like your integers are storing number of seconds since the epoch, or 1 January 1970. This is pretty common on UNIX systems. (Cf. http://en.wikipedia.org/wiki/Unix_time)

Try:

declare @.d1 datetime
declare @.d2 datetime
set @.d1 = dateadd(second, 1159533056, '1 January 1970')
set @.d2 = dateadd(second, 1159533264, '1 January 1970')
print @.d1
print @.d2

Result:

Sep 29 2006 12:30PM
Sep 29 2006 12:34PM

Cheers,
-Isaac

|||

Great that did it. yes they are stored as integers. I hate this db everything that is not text is stored as INT I mean Everything.
I have had to create several calculated fields for them. It's 3rd party db if i modify existing colums it won't function.

What would be the best way to create a calculated field for this data?
Thanks again

|||nevermind i got it by doing this...

alter TABLE [CDRMAin] add
[starttimetimecalc] AS dateadd(second, endtime, '1 January 1970')|||I just realized that this code is giving me the proper date from my date that is stored as an int, but it is not giving me the proper time
for this datetime as an int 1163517315
i get 2006-11-14 15:15:15.000, the correct time should be 10:16 am
the code i used to create my table is
alter TABLE [CDRMAin] add
[starttimetimecalc] AS dateadd(second, endtime, '1 January 1970')

how can i correct this.
thanks

Convert Int to Time

I have a DB that gets telephone call information from a telephone switch. We use 3rd part software and a db to capture all of this. However for some reason most of the fields are stored as int DateTime is stored as an int. I am able to convert most of the date field from int by doing select convert(datetime,convert(varchar(8), localday)) that works fine the int are in the db as 20061029 for 10-29-2006.
How ever I can't convert the columns starttime and endtime. They are also stored as int but
They don't seem to store a date.
For instance for the date 9/29/2006 the start time of one particular call is
1159533056 and the endtime is 1159533264 All i need to get from this is the time since I already have the date from another column. I can't figure out what this data represents.
How can I convert this to datetime?

Thanks!

Quote:

"The datetime and smalldatetime data types are stored internally as integers. The datetime data type is stored as a pair of four-bytes integers, which together represent the number of milliseconds since midnight of January 1, 1753. The first four bytes store the date, and the second four bytes store the time. The smalldatetime data type is stored as a pair of two-byte integers, which together represent the number of minutes since midnight on January 1, 1900. The first two bytes store the date, and the second two bytes store the time."

Also, check out this: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp (see the first table on that page).

So, if your columns' data types are datetime or smalldatetime (but not "int", which would be bizarre), you don't need to concern yourself with converting it manually and/or trying to figure out what exactly this integer means. Just call the appropriate version of CONVERT (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp).

|||

It looks to me like your integers are storing number of seconds since the epoch, or 1 January 1970. This is pretty common on UNIX systems. (Cf. http://en.wikipedia.org/wiki/Unix_time)

Try:

declare @.d1 datetime
declare @.d2 datetime
set @.d1 = dateadd(second, 1159533056, '1 January 1970')
set @.d2 = dateadd(second, 1159533264, '1 January 1970')
print @.d1
print @.d2

Result:

Sep 29 2006 12:30PM
Sep 29 2006 12:34PM

Cheers,
-Isaac

|||

Great that did it. yes they are stored as integers. I hate this db everything that is not text is stored as INT I mean Everything.
I have had to create several calculated fields for them. It's 3rd party db if i modify existing colums it won't function.

What would be the best way to create a calculated field for this data?
Thanks again

|||nevermind i got it by doing this...

alter TABLE [CDRMAin] add
[starttimetimecalc] AS dateadd(second, endtime, '1 January 1970')|||I just realized that this code is giving me the proper date from my date that is stored as an int, but it is not giving me the proper time
for this datetime as an int 1163517315
i get 2006-11-14 15:15:15.000, the correct time should be 10:16 am
the code i used to create my table is
alter TABLE [CDRMAin] add
[starttimetimecalc] AS dateadd(second, endtime, '1 January 1970')

how can i correct this.
thanks

Convert COM objects to Stored Procedure

Is there a 3rd party utility program that can convert complex COM objects
and DLLs to SQL Server stored procedures?
Thanks,
I doubt it. Are you asking about SQL Server 2000 or 2005? In SQL Server 2000
stored procedures are primarily for data manipulation. Data manipulation
should generally be set-based and declarative so the most efficient approach
to implementing procedural code in Transact SQL is usually to rewrite it as
set-based SQL - not something that is ever likely to automate well. As for
calling external COM code from SQL Server 2000, your main options are to
write an extended stored procedure or to use the sp_OA procs to invoke COM
objects from your procs.
SQL Server 2005 is a different matter. You can now catalogue .NET2.0 CLR
assemblies and call them directly as stored procedures. If you can implement
your COM as CLR code then in principle you may be able to run that code in
SQL Server 2005. That's not to say that it would always be a good idea to do
so. Data manipulation stuff will generally still be best implemented in SQL.
David Portas
SQL Server MVP

Convert COM objects to Stored Procedure

Is there a 3rd party utility program that can convert complex COM objects
and DLLs to SQL Server stored procedures?
Thanks,I doubt it. Are you asking about SQL Server 2000 or 2005? In SQL Server 2000
stored procedures are primarily for data manipulation. Data manipulation
should generally be set-based and declarative so the most efficient approach
to implementing procedural code in Transact SQL is usually to rewrite it as
set-based SQL - not something that is ever likely to automate well. As for
calling external COM code from SQL Server 2000, your main options are to
write an extended stored procedure or to use the sp_OA procs to invoke COM
objects from your procs.
SQL Server 2005 is a different matter. You can now catalogue .NET2.0 CLR
assemblies and call them directly as stored procedures. If you can implement
your COM as CLR code then in principle you may be able to run that code in
SQL Server 2005. That's not to say that it would always be a good idea to do
so. Data manipulation stuff will generally still be best implemented in SQL.
David Portas
SQL Server MVP
--

Convert COM objects to Stored Procedure

Is there a 3rd party utility program that can convert complex COM objects
and DLLs to SQL Server stored procedures?
Thanks,I doubt it. Are you asking about SQL Server 2000 or 2005? In SQL Server 2000
stored procedures are primarily for data manipulation. Data manipulation
should generally be set-based and declarative so the most efficient approach
to implementing procedural code in Transact SQL is usually to rewrite it as
set-based SQL - not something that is ever likely to automate well. As for
calling external COM code from SQL Server 2000, your main options are to
write an extended stored procedure or to use the sp_OA procs to invoke COM
objects from your procs.
SQL Server 2005 is a different matter. You can now catalogue .NET2.0 CLR
assemblies and call them directly as stored procedures. If you can implement
your COM as CLR code then in principle you may be able to run that code in
SQL Server 2005. That's not to say that it would always be a good idea to do
so. Data manipulation stuff will generally still be best implemented in SQL.
--
David Portas
SQL Server MVP
--