Windows XP Pro - Sql Server 2005 Management Studio (current) - current visual foxpro oledb driver
I have an origin data folder with many foxpro dbf files
When I go through the "wizard" to import data from the origin folder using the "free table" option, I can locate the folder just fine. The form shows me a list of the files in the folder - but I cannot scroll through the list.
When I select the folder and continue, I test the connection and get a message that it is fine.
Then I go on to actually import tables. The list that is presented of tables that I can import does not include all the dbf files in the folder. There are 207 files in the origin folder, but only a fraction (about 120) of those show up in the list as candidates to be imported. I cannot see a pattern to the ones that are excluded from the list.
Does anyone know what could be causing this strange behavior?
Today was my first day to use DTS. I tried to transfer a Visual FoxPro Database to my SQL Server. I noticed that I am getting errors on the data that contains date fields. So, for testing purposes I transformed the Visual FoxPro fields that contained datefields into char fields. And this works, but obviously I cannot hunt around in a huge database looking for datefields in tables and change them manually. Plus, by changing to char fields I noticed that those fields that are empty are transformed as 1899-12-30. :p
I know that the DTS Wizard is supposed to be able to handle heterognous data imports but I can't get it to work with a free FoxPro table. I have to export to a text file and then import from the text file and spend an hour renaming columns and farting around with datatypes.
CAN I GET A FOXPRO TABLE INTO SQL SERVER DIRECTLY OR NOT? HOW DO I DO IT?
I don't fully understand some of the questions the DTS Wizard is asking. Can anybody give me a blow by blow account before I ring the Samaritans?
Thank you and Happy Christmas - it may be my last if I can't speed up these imports. Either I'll jump under a bus or my boss will make mince pies out of me.
Im quite interested to see if any one is using Microsoft OLE DB Provider for Visual FoxPro 9.0 in an SSIS package. Is it possible to use this for free table directory like you can with the ODBC driver. ????
thanks kindly
My basic proccess is as follows: The source foxpro database has hundreds of dbf files ---the SSIS procress is as follows
1: Copy only required dbf files and/or matching .fpt files over to local drive on SSIS box(we only need 10 files)
use MS Visual Foxpro ODBC driver (free table option) and set up a system DSN--dataflow tab---datareader source--and away you go
Using: Visual Web Developer Express SQL Express IIS 6.0 with >net 2.0 Installed Visual Foxpro
I actually have two questions (but am making good progress so will focus on the issue at hand).
1. I setup a Login.aspx / Default.aspx / Register.apsx / Membership.aspx as specified in the examples in MSDN...all is working ok.
2. When I register a new user, I wanted to capture their Customer ID during login to store in their Membership view table. I did this and is working ok....and to make it easy, am storing the value in Comments field.
3. I want to query a Visual Fox free table (DBF) that contains my client's Customer Invoice data...and have the ODBC Connection setup and working properly.
Here's the problem....
I'm having trouble formulating the SELECT string within the asp code to capture the Membership.Comments field.
I need the proper functions to grab the Comments field data and then use it within my select statement.
Presently, the query works if I just hard-code the value i.e.
"SELECT * from [invdata] WHERE cus_no = '1' "
I need to replace the '1' with the comments field data.
Thanks:
On a side issue: it took me days to just to get this all working to where it is...does anyone have a detailed example of how to Login to a site and pass the parms to another page to query the Logged In user's data from a database......a real world example that I have yet to see highlighted anywhere in the Forums or books I've bought.
For example: I am customer ABC123, I log in to my vendor's site and want to see all my orders placed.
I am trying to add a FoxPro linked server to MS SQL 2005, and I can't seam to create a linked server that works. What am I doing wrong in linking the server?
I have an ODBC connection that worked but not OLEDB; how can I do this with OLEDB (either VFPOLEDB or Jet, if it will work) and not ODBC.
This is what I thought was right
Code Snippet
sp_addlinkedserver 'test',
'FoxPro',
'VFPOLEDB',
'C:DataSomeDatabase.dbc',
NULL,
NULL,
NULL
But it gives the error:
Cannot create an instance of OLE DB provider "VFPOLEDB" for linked server "test".
Also I know in MS SQL 2000 once you linked a server you could view it in EM, but when I linked the VFP via ODBC I could query agianst it, but I could not open it in Mangament Studio.
I've downloaded and installed the latest VFPOLEDB (12/04) on 2 separate SQL Server boxes.
In both cases, If I connect to SQL Server with Query Analyzer as (local) while on a box, the linked server to my foxpro database works fine with openquery().
However, If I'm at one box and attached to SQL Server on the other box, the openquery() fails.
--this works on either (local) box SELECT * FROM OPENQUERY(VFP, 'select * from tislists') Go
--but, the same openquery() above doesn't work if the box I'm running it from is attached to the SQL Server on the other box. I get:
Server: Msg 7302, Level 16, State 1, Line 1 Could not create an instance of OLE DB provider 'VFPOLEDB'. OLE DB error trace [Non-interface error: CoCreate of DSO for VFPOLEDB returned 0x80040154]. ===================== One other approach I tried that works while on the (local) box, but fails when attached to the SQL Server on the other box:
select * from openrowset('MSDASQL', 'Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB= \hdmcpdctis1 isrnddata ', 'select * from [tislists.DBF]')
With error: Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'MSDASQL' reported an error. [OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Driver does not support this function] OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: ]. ===========================
How can i create a linked server for Microsoft Visual Foxpro databases ?
I'm using Microsoft.ACE.OLEDB.12.0 driver.
I success create a linked server, and can browse all tables from linked server connections, but why when query data using
SELECT * FROM OPENQUERY(PIP_TEST,'select * from tbctrl')
It getting error message
Cannot process the object "select * from tbctrl". The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "pip_test" indicates that either the object has no columns or the current user does not have permissions on that object.
This is a Execute DTS from VFP Example I found. Does anyone have a snippet for executing an SSIS package?
Thanks, Shannon
*!* Create a DTS Package Object dtsPkg=CREATEOBJECT('dts.package')
*!* LoadFromStorageFile() requires the following parameters: *!* UNC path to the .DTS file to be executed *!* User Password
*!* Load the package to be executed from a file. dtsPkgName=dtsPkg.LoadFromStorageFile("\UNC_Path_toDTSPkgBeta.dts","User_Password") *!* Execute the DTS Package dtsPkg.Execute *!* Release the DTS Package RELEASE dtsPkg
I created a package that extracts records from a .DBF file into an SQL Server database. I installed a Visual FoxPro plugin (vfpoledb). When I run the package from my PC, it runs smoothly. But, when I try to run the same package in another PC, it displays a DTS_E_OLEDBERROR "Class not registered" error. Anyone has an idea about what this means? thanks in advance.
I am unsure if this is the correct forum to send this question, but I can't seem to find any information regarding this problem. If this is the wrong place, please direct me to the correct spot.
I am attempting to import data from a free tables FoxPro database to SQL 2000 using a DTS Package which has worked correctly every day for the past 2 years. Yesterday, I got an error.
The package has around 10 tables that it deletes, re-creates, and populates with data from the Foxpro. All of the tables except one work correctly.
When I try to do an explicit import using the ODBC connection to populate that one table, I get the following error: Context: Error calling Openrowset on the provider.
I created an access database on my local computer and setup an ODBC connection and link tables to the database to see if it would work, and it did. So I thought there might be something wrong with the ODBC data source on the SQL Server, so I deleted it and created a new one, used it and I receive the same error.
I thank you in advance for any assistance or direction you can provide me for finding an answer.
Hi, I have 2 separate clients experiencing this problem, Both systems are 1.Win 2000 Server with Small Business Server 2000 (SP4) 2.SQL Server 2000 (SP2 upgraded to SP4, SP4) 3.Exchange 4.IIS etc
We use SQL Server to connect to FoxPro 2.6 Tables.
We are using OPENROWSET as follows:
Select * from openrowset('VFPOLEDB.1','C:Client Data';;,'Select * from cm')
We are now getting this error: Server: Msg 7330, Level 16, State 2, Line 1 Could not fetch a row from OLE DB provider 'vfpoleDB.1'. OLE DB error trace [OLE/DB Provider 'vfpoleDB.1' IRowset::GetNextRows returned 0x80040155].
The statement worked up until around 20 days ago, which made us think that it may be windows updates/sps/hotfixes, but i have installed the above on a test machine and installed all windows updates and the test machine works AOK.
I have 2 separate clients experiencing this problem,
Both systems are
Win 2000 Server with Small Business Server 2000 (SP4) SQL Server 2000 (SP2 upgraded to SP4, SP4) Exchange IIS etc
We use SQL Server to connect to FoxPro 2.6 Tables.
We are using OPENROWSET as follows:
Select * from openrowset('VFPOLEDB.1','C:Client Data';;,'Select * from cm')
We are now getting this error:
Server: Msg 7330, Level 16, State 2, Line 1 Could not fetch a row from OLE DB provider 'vfpoleDB.1'. OLE DB error trace [OLE/DB Provider 'vfpoleDB.1' IRowset::GetNextRows returned 0x80040155].
The statement worked up until around 20 days ago, which made us think that it may be windows updates/sps/hotfixes, but i have installed the above on a test machine and installed all windows updates and the test machine works AOK.
I have a report that accessed a Visual FoxPro 6.0 database via ODBC. Since I upgraded to Visual FoxPro 9.0, now I am getting the error:
Query Server Error DMS-E-RBI_TABLE The table or view <table name> was not found in the dictionary
I've verified that the right path is setup and I've tried installing about every driver and none of them are working. I created a new database in 9.0 and the I can access it fine. Also, there are old tables that are accessible in the same path, just not the ones I need. Any ideas would be helpful..
Hi all,I am fairly new to using triggers and was seeking some help from thosethat have experience with them. I am looking to transfer data from aSQL 2000 database to a Visual FoxPro database on another computer. Iwould like to transfer about three fields of data to a VFP table eachtime an insert is made on the SQL table. I am some what familiar withthe structure of creating the trigger but here is what I would likehelp with: Selecting the SQL data to transfer, Connecting to VFPdatabase, Insert SQL data into VFP table.CREATE TRIGGER [xyz] ON [dbo].[AAA]FOR INSERT??? Select a,b,c from SQL table??? Connect to VFP Database and Table??? Insert into VFP table Values a,b,cAny information, tips, or even an example Trigger procedure would helpand be greatly appreciated.Thank you,Brett
Currently, I'm using the following steps to migrate millions of records from Foxpro tables to SQL Server tables:
1. Transfer Foxpro records to .dat files and then bcp to SQL Server tables in a dummy database. All the SQL tables have the same columns as the Foxpro tables. 2. Manipulate the data in the SQL tables of the dummy database and save the manipulated data into the SQL tables of the real database where the tables may have different structure from the corresponding Foxpro tables.
I only know the following ways to import Foxpro data into SQL Server:
#1. Transfer Foxpro records to .dat files and then bcp to SQL Server tables #2. Transfer Foxpro records to .dat files and then Bulk Insert to SQL Server tables #3. DTS Foxpro records directly to SQL Server tables
I'm thinking whether the following choices will be better than the current way:
1st choice: Change step 1 to use #2 instead of #1 2nd choice: Change step 1 to use #3 instead of #1 3rd choice: Use #3 plus manipulating in DTS to replace step 1 and step 2
I am using OleDbMicrosoft OLE DB Rpovider for Visual FoxPro for my Data Source Connection and it includes the deleted records from the Visual FoxPro database, how can it have it ignore the deleted records?
Is it possible to use SSIS to synchronize the data between a Foxpro .dbf and a compatible SQL Server table on a near realtime basis?
I have succesfully created an SSIS package that will insert data into the SQL Server Table but this is only useful for migrating data. What I need is a way to insure that the data in the SQL Server table matches that in the .dbf on a near realtime basis.
Or is there a way to link from SQL Server to the .dbf (similar to an Oracle DBLink).
I am trying load data from multiple Foxpro tables which are under a folder. I can have multiple folders with 17 foxpro tables. I was able to do it in DTS using ActiveX script. Here is the ACtiveX script.
'********************************************************************** ' Visual Basic ActiveX Script '************************************************************************ Option Explicit Dim conObj,DSNGosfbill,comObj,objRs,HostServer Dim sFolder,sFileFolder, Details,subFolderoccur,sFileFolderDBF,sFileFolderFPT,CheckFile,dFiles,Fil Dim fso, folderObj,subFolderList,dFolderObj Dim objPackage,oStep,objPackage_1,oStep_1,ConnObj_001,ConnObj_004,ConnObj_031,ConnObj_032,ConnObj_033 Dim ConnObj_Hclaimb, ConnObj_HProv, ConnObj_Hids, ConnObj_HCodes, ConnObj_HSpan, ConnObj_002, ConnObj_HCHGB Set conObj = CreateObject("ADODB.Connection") HostServer =DTSGlobalVariables("gvServer").Value
set comObj=CreateObject ("adodb.command") set comObj.ActiveConnection =conObj Function Main() Dim Dir_Name,DirFlag Dir_Name = "" DirFlag = "N" Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FileExists(checkFile) Then Else Details = "***** Success.Lst file is missing in Batch folder. BATCH job may not be successfull or there are no folders in UNZIP directory to process. Check the batch run.*****" Call Write_Log Main = DTSTaskExecResult_Failure Exit Function End If Set folderObj = fso.GetFolder(sFolder) Set subFolderList = folderObj.SubFolders For Each subFolderOccur in subFolderList DirFlag = "Y" Dir_Name = subFolderOccur.Name Call Process_Dir(1,subFolderOccur.Name) Next If DirFlag = "N" Then Details = "***** No directories to process in SSI UNZIP folder*****" Call Write_Log End If If DirFlag = "Y" Then Call Process_Dir(2,Dir_Name) If objRs.Eof Then Details = "***** No directories to process in SSI UNZIP folder*****" Call Write_Log End If While not objRs.EOF set sFileFolder = fso.GetFolder(sFolder & objRs("zip_file_name")) Details = "***** Start-Time " & sFileFolder & " " & Date & " " & Time & "*****" Call Write_Log Call Update_Process_Flag("L",objRs("zip_file_name")) '*******Execute the package for each directory****************' '********* Call the Package**************' Set objPackage = CreateObject("DTS.Package") Set objPackage_1 = CreateObject("DTS.Package")
Set ConnObj_001 = objPackage.Connections("SSIPATH001") ConnObj_001.DataSource = sFileFolder
Set ConnObj_002 = objPackage.Connections("SSIPATH002") ConnObj_002.DataSource = sFileFolder
Set ConnObj_004 = objPackage.Connections("SSIPATH004") ConnObj_004.DataSource = sFileFolder Set ConnObj_031 = objPackage.Connections("SSIPATH031") ConnObj_031.DataSource = sFileFolder Set ConnObj_032 = objPackage.Connections("SSIPATH032") ConnObj_032.DataSource = sFileFolder Set ConnObj_033 = objPackage.Connections("SSIPATH033") ConnObj_033.DataSource = sFileFolder
Set ConnObj_Hclaimb = objPackage.Connections("SSIPATHCLAIMB") ConnObj_Hclaimb.DataSource = sFileFolder
Set ConnObj_HProv = objPackage.Connections("SSIPATHPROV") ConnObj_HProv.DataSource = sFileFolder Set ConnObj_Hids = objPackage.Connections("SSIPATHHIDS") ConnObj_Hids.DataSource = sFileFolder Set ConnObj_HCodes = objPackage.Connections("SSIPATHCODES") ConnObj_HCodes.DataSource = sFileFolder Set ConnObj_HSpan = objPackage.Connections("SSIPATHSPAN") ConnObj_HSpan.DataSource = sFileFolder
Set ConnObj_HCHGB = objPackage.Connections("SSIPATHCHGB") ConnObj_HCHGB.DataSource = sFileFolder
objPackage.Execute For Each oStep In objPackage.Steps If oStep.ExecutionResult = DTSStepExecResult_Failure Then Details = "***** GOSFBILL_SSI_Staging_Load failed. " & Date & " " & Time & "*****" Call Write_Log Main = DTSTaskExecResult_Failure Exit Function End If Next
For Each oStep_1 In objPackage_1.Steps If oStep_1.ExecutionResult = DTSStepExecResult_Failure Then
Details = "***** GOSFBILL_SSI_Update_FileSource failed. " & Date & " " & Time & "*****" Call Write_Log Main = DTSTaskExecResult_Failure Exit Function End If Next
'********************************************' Details = "***** End-Time " & sFileFolder & " " & Date & " " & Time & "*****" Call Write_Log objPackage.Uninitialize objPackage_1.Uninitialize Set objPackage = Nothing Set objPackage_1 = Nothing sFileFolder = "" sFileFolderDBF = "" sFileFolderFPT = "" objRs.MoveNext Wend objRs.Close End If Call Close_Conn Main = DTSTaskExecResult_Success End Function Sub Process_Dir (Para_cntl,Dir_Name) comObj.CommandText ="dbo.Usp_Process_Dir" comObj.commandtype = 4 comobj.parameters.Refresh comobj.parameters("@Para_Cntl")= para_cntl comobj.parameters("@Dir_Nm")= Dir_Name comobj.parameters("@File_Type")= "SSI" If (Para_Cntl = 1)Then comObj.Execute() Else If Para_Cntl = 2 Then Set objRs = comObj.Execute() End If End If
End Sub Sub Update_Process_Flag(P_Flag,Dir_Name) comObj.CommandText ="dbo.Usp_Process_Flag" comObj.commandtype = 4 comObj.parameters.Refresh comObj.parameters("@Process_Flag")= P_Flag comobj.parameters("@Dir_Nm")= Dir_Name comObj.Execute() End Sub Sub Write_Log comObj.CommandText ="dbo.usp_etl_write_log" comObj.commandtype = 4 comobj.parameters.Refresh comobj.parameters("@Text")= Details Comobj.parameters("@NDC_SSI_IND")= "SSI" Comobj.parameters("@Process_Stage")= "Staging" comObj.Execute() End Sub
Sub Close_Conn Set comObj = Nothing Set objRs = Nothing conObj.Close Set conObj = Nothing Set fso = Nothing Set folderObj = Nothing Set subFolderList = Nothing End Sub
When I migrated this code to SSIS, its not working. How can I achive this functionality in SSIS. Any one pls help me.
I didn't want to maintain similar/identical tables in a legacy FoxPro system and another system with SQL Server back end. Both systems are active, but some tables are shared.
Initially I was going to use a Linked Server to the FoxPro to pull the FP data when needed. This works. But, I've come up with what I believe is a better solution. Keep in mind that these tables are largely static - occassional changes, edits.
I will do a 1 time DTS from FP into SQL Server tables.
I then create INSERT and UPDATE triggers within FoxPro.
These triggers fire a stored procedure in FoxPro that establishes a connection to the SQL Server and fire the appropriate stored procedure on SQL Server to CREATE and/or UPDATE the corresponding table there.
In the end - the tables are local to both apps.
If the UPDATES or TRIGGERS fail I write to an error log - and in that rare case - I can manually fix. I could set it up to email me from within FoxPro as well if needed.
Here's the FoxPro and SQL Server code for reference for the Record Insert:
Hi, I've just got my brand new shiny SQLServer and would like to set some new tables set up etc. But I'm really lazy and I was wondering if there is any way of importing table layouts, coulmns, whole structure type thing into SQLServer from Microsoft Access using DTS? I know it's a bit optimistic, but just wondered....
Not sure if this is the correct Forum, anyway lemme me know your views. I have SQL Server running in two remote machines (in different geographical locations). I need to import a database from one SQL Server installation to another installation. What is best possible solution:
1) Transfering the .mdf and .ldf file from one installation to another installation
2) Generating the records of all the tables in the database to a common file format CSV and then loading it in destination database. If this is ok then how to generate import and export data using CSV format
HiI am new to SQL and am having problems importing data from two tables to one.I have 3 tables : Franchise, Club and Reference.Franchise has FranchiseID as Primary key and FranchiseNumber as unique.Club has ClubID as Primary key.Reference has a combination of ClubID and FranchiseNumber as primary key. I am not able to insert data into table Reference from Franchise and Club. Any help or suggestions would be highly appreciated. Thanks in advance.
hello, i have a problem after importing my db tables to the aspnetdb the default database that come with visual studio i put my tables into that db but i added some columns to the aspnet_users and i made a relation between my tables and some of aspnetdb tables the problem is when i drag and drop a grid view and manage its datasource i cant find the columns that i add to the table? second problem is when i press the advanced button in the datasource the two checkboxes are not active i dont know why and i clicked on the column that has the primary key but i still have the problem i cant update or delete records and i tried on the original aspnetdb users table(without my columns or anything) also found that two check boxes not active? is it a problem in the aspnetdb or what? there also something strange when i choose the tables in the sqldatasource the aspnetdb tables names are appear like this : vw_aspnet_roles vw_aspnet_users ...etc they all has that vw word dont know why it also exist in the original aspnetdb, so hope anyone can help cos if it's no solution for that problem i will have to build my db again from the begining ,hope u reply soon.thanks
I am trying to import application tables from oracle but they do not show up in the list of tables. I suspect that this is because the tables are owned by System. Is there any way other than changing the owner to get these tables to appear in the import list?
I'm making an application in Delphi that use an SQL database, i want the user can import a table in DBF format (that its located in another computer) to the SQL Server (in the database that i want) by one click of a button.
I try with the import wizard but its not what i want because it requiere to do the process manually. Its there anyway of achive this (Stored Procedure or something?)
Ernesto Cisneros Sanchez Senior Software Engineer Credit and Trade Bank Havana, Cuba
I am having a problem to get all datas into one table I must get datas from 2 other tables datas from the first table are columns but datas of the second table are rows
I cannot change the structure of TableA because an other application is using it as it is
TableA num | value | key 1 | a | 1400 2 | b | 1401 3 | c | 1402 4 | d | 1403 ...|... | 1403
TableB key | name | descr | value 400 | john | ok | 451
TableC (the table where I want to insert datas from Table1 and Table2)
val1 | val2 | val3 | val4 |name | descr | value 1 | 2 | 3 |4 | john | ok | 451
TableA has thousands of rows and different numbers I must get 8 different datas from TableA
so I get an horrible Query !
SELECT
TableB.name, TableB.descr, TableB.value, TableA_1.value AS val1, TableA_2.value AS val2, TableA_3.value AS val3, TableA_4.value AS val4, TableA_5.value AS val5, TableA_6.value AS val6, TableA_7.value AS val7, TableA_8.value AS val8
FROM dbo.TableB INNER JOIN dbo.TableA_1 ON TableB.key = TableA_1.key INNER JOIN dbo.TableA_2 ON TableB.key = TableA_2.key INNER JOIN dbo.TableA_3 ON TableB.key = TableA_3.key INNER JOIN dbo.TableA_4 ON TableB.key = TableA_4.key INNER JOIN dbo.TableA_5 ON TableB.key = TableA_5.key INNER JOIN dbo.TableA_6 ON TableB.key = TableA_6.key INNER JOIN dbo.TableA_7 ON TableB.key = TableA_7.key INNER JOIN dbo.TableA_8 ON TableB.key = TableA_8.key
WHERE TableA_1.num = 145 AND TableA_2.num = 80 AND TableA_3.num = 3160 AND TableA_4.num = 41 AND TableA_5.num = 50 AND TableA_6.num = 51 AND TableA_7.num = 53 AND TableA_8.num = 56
i'm importing tables from another sql server - now the tables are coming is as esther.tablename (my username on the other server) instead of dbo.tablename
I'm trying to input a few thousand flat files into a few thousand tables in a sql databaseim using integration services with a for each loop to read all the files in a directorythe problem is i can only insert the data from all the files into one tabledoes anyone know a way to do multiple tables? maybe using some sort of variable?
