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 date field from Informix to SQL nvarchar

I copying data from our Informix 7.2 database into SQL Server 2K using DTS but hitting errors during the process. There appears to be date data within Informix that will not convert properly when moving into SQL. Since the error is appearing at the 1.5million (approx.) record. I figured on changing from datetime to nvarchar. Works like a charm! :-)

My new problem is converting it back to datetime so I can query against the date without having to create scripts to parse the field.

The data in SQL currently looks like this -> 2000-11-29 (nvarchar(50))
I would like to have it -> 11/29/00 (datetime)

Any help is greatly appreciated!

JT

The goodness we share is rewarded twice!try to find the record that cause the failure:
select * From <your table name> where isdate(<date varchar column name>)=0

varchars in yyyy-mm-dd format usually converted to dates data type without problems...|||Thank you for the reply.

So your saying, if the table name is findet (financial detail) and the column name is fdate (nvarchar, 50).

Run the select against the varchar column?|||You've got data that can't be converted to date...

To see the offending rows...

SELECT * FROM findet WHERE ISDATE(fdate) = 0|||OK. I am testing it now. When I attempted to convert the column in Enterprise Mgr, I got the following error:

/*

Tuesday, April 13, 2004 13:20:17

User: sa

Server: NYCRPSTOR01

Database: Mysis

Application: MS SQLEM - Data Tools

*/

'findet' table
- Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic overflow error converting expression to data type datetime.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.|||Originally posted by Brett Kaiser
You've got data that can't be converted to date...

To see the offending rows...

SELECT * FROM findet WHERE ISDATE(fdate) = 0

Did you run this?

What did you get?

$1,000 bucks you've got non date data in that column|||That worked!!! Thank you for your help!!!|||What worked?

Did you find non date data?|||before you change the column type find what cause the conversion error!|||There was data mis-entered 0004-04-10 (example). Since the year is out of range, converting it to datetime was not possible.

Now, how do I script my DTS package to exclude records that are out of range and put them in a separate table for exception reporting?

Again, thank you for your help! It is greatly appreciated!!!|||just load the entire data to table with varchar column then copy the wrong date format to errors table and delete these records from the original table, convert the good data from the original table into target table drop the original table fix the failed record and enter them into the target table

Converting CHAR/VARCHAR/TEXT into NCHAR/NVARCHAR/NTEXT!

Hi,
We are in process of converting all of the data type of the fields from CHAR/VARCHAR/TEXT into NCHAR/NVARCHAR/NTEXT (DBCS). Having more than 900 store procedure its look like real pain to make modification in all of the SPs.

After failed to find any help from GOOGLE, I am posting this request. I am basically looking for any automated tool which are convert data type in SP based on the field of the table used in the SP. Or at least which can provide me some sort of list which can helpful for doing manual reactoring.

PLEASE HELP ME!!!

Thanks,

Firoz AnsariIf you are, in batch, converting all types, you couls just script the database using Enterprise Manager, and then do a search and replace for each of the types. Be aware that converting from varchar to nvarchar could cause problems if rows already contain over 4000 characters.|||I have just created a small VB utility program which can take list of files (.sql files) and uses regular expression to replace data types in SPs files. I am still looking for some automated tool.

Regards

Converting AS2000 DSO from VB6 to VS2005

I have a VB6 program that uses Analysis Services 2000 DSO to Automatically Process an AS2000 Database. I ran the Upgrade Wizard in VS2005 for Visual Basic on this VB6 program but the result has an error I can't find how to correct.

Here is the original code -

Dim mdDatabase As DSO.MDStore
Dim dsoServer As DSO.Server

Set dsoServer = New DSO.Server
dsoServer.Connect ("CubeServer")
Set mdDatabase = dsoServer.MDStores("TestCubes")
mdDatabase.Process

Here is the code as a result of the 2005 upgrade wizard -

Dim mdDatabase As DSO.MDStore
Dim dsoServer As DSO.Server

Set dsoServer = New DSO.Server
dsoServer.Connect ("CubeServer")
mdDatabase = dsoServer.MDStores("TestCubes")

The above line displays the following error: 'DSO.OlapCollection Cannot be indexed'

mdDatabase.Process

Can anyone tell me what's wrong? I can't use AMO because it doesn't work against AS2000. How do I select a specific AS2000 Databse with VB in VS2005 and process it?

Thanks,

JohnWC

Try posting this question on the on the Visual Basic General discussion forum.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

I did find the answer in the OLAP Managed Newsgroup. For anyone who may be interested, the following code line works.

mdDatabase = dsoServer.MDStores.Item("TestCubes")

JohnWC

Convert varchar to uniqueidentifier

When I need to have more info about a process, I run this kind of query:
SELECT PROGRAM_NAME FROM master..sysprocesses WHERE program_name LIKE
'SQLAgent - TSQL JobStep (Job %'
The result might be something like this:
SQLAgent - TSQL JobStep (Job 0x9BD0927CE9086241B582AAAAD7D3B86D : Step
1)
When I want to have more info about that particular job, I run this
query:
SELECT * FROM msdb..sysjobs WHERE job_id =
0x9BD0927CE9086241B582AAAAD7D3B86D
Now how do I combine these two queries? The first version you might
suggest is:
SELECT *
FROM msdb..sysjobs
WHERE job_id IN
(SELECT SUBSTRING(program_name,30,34)
FROM master..sysprocesses
WHERE program_name LIKE 'SQLAgent - TSQL JobStep (Job %')
This gives the result
Syntax error converting from a character string to uniqueidentifier.
Can you help me with this? How to convert the result of the subquery to
uniqueidentifier? I didn't succeed to use the usual convert function
CONVERT(UNIQUEIDENTIFIER,SUBSTRING(progr
am_name,30,34)).
MarkTry this (untested):
SELECT *
FROM msdb..sysjobs
WHERE cast(job_id as nvarchar(128)) IN
(SELECT cast(SUBSTRING(program_name,30,34) as nvarchar(128))
FROM master..sysprocesses
WHERE program_name LIKE 'SQLAgent - TSQL JobStep (Job %')
ML|||"ML" <ML@.discussions.microsoft.com> wrote in message
news:9FB96207-78A4-4566-9AEC-8294830AABAB@.microsoft.com...
> Try this (untested):
> SELECT *
> FROM msdb..sysjobs
> WHERE cast(job_id as nvarchar(128)) IN
> (SELECT cast(SUBSTRING(program_name,30,34) as nvarchar(128))
> FROM master..sysprocesses
> WHERE program_name LIKE 'SQLAgent - TSQL JobStep (Job %')
>
> ML
Thanks, but that doesn't work either - I tested. But at least that doesn't
give any errors, just an empty result.
However, I found out, that this works. Quite ugly one, but it works.
SELECT *
FROM msdb..sysjobs
WHERE job_id IN
(SELECT SUBSTRING(program_name,38,2) +
SUBSTRING(program_name,36,2) +
SUBSTRING(program_name,34,2) +
SUBSTRING(program_name,32,2) + '-' +
SUBSTRING(program_name,42,2) +
SUBSTRING(program_name,40,2) + '-' +
SUBSTRING(program_name,46,2) +
SUBSTRING(program_name,44,2) + '-' +
SUBSTRING(program_name,48,4) + '-' +
SUBSTRING(program_name,52,12)
FROM master..sysprocesses
WHERE program_name LIKE 'SQLAgent - TSQL JobStep (Job %')
Mark

Convert Crystal Field Logic To SSRS 2005?

Hello all!
I am in the process of converting a client's Crystal Reports to SSRS 2005
reports. Some of the crystal report fields have a good amount of logic
behind them to determine what is displayed.
What is the best way to apply this logic in a SSRS 2005 RDL file? This
looks like a lot of logic to have in an expression...Would I want to create a
function and pass in the parameters (it would need about 10+ parameters)?
What implications would this type of logic have on processing time?
Here is a sample of some of the logic:
if {File001.type} = 1 or {File001.type} = 6 then
(
if {File001.category} = "Sold" then
{File001.numeric1}
else
(if ({File001.action11} <> 0 or {File001.action13} <> 0) then
{File001.numeric2} else {File001.numeric3})
)
else if {File001.type} = 5 then
(
if {File001.category} = "Sold" then
{File001.numeric4}
else
(if ({File001.xfield} <> 0 or {File001.yfield} <> 0) then
{File001.numeric5} else {File001.numeric6})
)
else
{File001.numeric7}
Thanks!
-BrianBrian,
I'd look at putting that code in the report's Custom Code, rather than
trying to convert it all to some sort of giant iif expression. The
other alternative would be to put it all in an assembly and reference
the assembly from the report.
Potter|||You could do several things..
Create a VB Function in the Reports->Report Properties->Code section. This
would be a standard VB funtion.
Create a field which includes all the smarts, then you can just refer to
this as a field
or
Do it in SQL ( I personally like SQL, but I am a sql guy)...
The sql might be something like
select ....
Case When File001.Type = 1 or File001.Type = 6 THEN
CASE WHEN File001.Category = 'Sold' THEN File001.numeric1
ELSE
CASE WHEN File001.action1 != 0 or File001.action13 != 0 THEN
File001.numeric2
ELSE File001.Numeric3
END
WHEN File001.type = 5 THEN
CASE WHEN File001.Category = 'Sold' THEN File001.numeric4
WHEN File001.xfield != 0 OR File001.yfield !=0 THEN
File001.numeric5
ELSE File001.numeric6
END
END
ELSE File001.numeric7
END
If you do this in a Field or function, use the switch statement instead.
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"brianpmccullough" wrote:
> Hello all!
> I am in the process of converting a client's Crystal Reports to SSRS 2005
> reports. Some of the crystal report fields have a good amount of logic
> behind them to determine what is displayed.
> What is the best way to apply this logic in a SSRS 2005 RDL file? This
> looks like a lot of logic to have in an expression...Would I want to create a
> function and pass in the parameters (it would need about 10+ parameters)?
> What implications would this type of logic have on processing time?
>
> Here is a sample of some of the logic:
> if {File001.type} = 1 or {File001.type} = 6 then
> (
> if {File001.category} = "Sold" then
> {File001.numeric1}
> else
> (if ({File001.action11} <> 0 or {File001.action13} <> 0) then
> {File001.numeric2} else {File001.numeric3})
> )
> else if {File001.type} = 5 then
> (
> if {File001.category} = "Sold" then
> {File001.numeric4}
> else
> (if ({File001.xfield} <> 0 or {File001.yfield} <> 0) then
> {File001.numeric5} else {File001.numeric6})
> )
> else
> {File001.numeric7}
>
> Thanks!
> -Brian
>|||One additional option if you're hitting SQL server 2005 databases is to
write the logic in stored procedures using C# or VB.NET. It is
sometimes easier to embed the logic in the database layer than in a
custom assembly (that needs to be deployed to all report servers), and
you get the flexibility of procedural programming that these languages
offer.
Matt A
Wayne Snyder wrote:
> You could do several things..
> Create a VB Function in the Reports->Report Properties->Code section. This
> would be a standard VB funtion.
> Create a field which includes all the smarts, then you can just refer to
> this as a field
> or
> Do it in SQL ( I personally like SQL, but I am a sql guy)...
> The sql might be something like
> select ....
> Case When File001.Type = 1 or File001.Type = 6 THEN
> CASE WHEN File001.Category = 'Sold' THEN File001.numeric1
> ELSE
> CASE WHEN File001.action1 != 0 or File001.action13 != 0 THEN
> File001.numeric2
> ELSE File001.Numeric3
> END
> WHEN File001.type = 5 THEN
> CASE WHEN File001.Category = 'Sold' THEN File001.numeric4
> WHEN File001.xfield != 0 OR File001.yfield !=0 THEN
> File001.numeric5
> ELSE File001.numeric6
> END
> END
> ELSE File001.numeric7
> END
> If you do this in a Field or function, use the switch statement instead.
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "brianpmccullough" wrote:
> > Hello all!
> >
> > I am in the process of converting a client's Crystal Reports to SSRS 2005
> > reports. Some of the crystal report fields have a good amount of logic
> > behind them to determine what is displayed.
> >
> > What is the best way to apply this logic in a SSRS 2005 RDL file? This
> > looks like a lot of logic to have in an expression...Would I want to create a
> > function and pass in the parameters (it would need about 10+ parameters)?
> > What implications would this type of logic have on processing time?
> >
> >
> >
> > Here is a sample of some of the logic:
> >
> > if {File001.type} = 1 or {File001.type} = 6 then
> > (
> > if {File001.category} = "Sold" then
> > {File001.numeric1}
> > else
> > (if ({File001.action11} <> 0 or {File001.action13} <> 0) then
> > {File001.numeric2} else {File001.numeric3})
> > )
> > else if {File001.type} = 5 then
> > (
> > if {File001.category} = "Sold" then
> > {File001.numeric4}
> > else
> > (if ({File001.xfield} <> 0 or {File001.yfield} <> 0) then
> > {File001.numeric5} else {File001.numeric6})
> > )
> > else
> > {File001.numeric7}
> >
> >
> >
> > Thanks!
> >
> > -Brian
> >
> >