Table 1 | id | name | coupn | | ----- | ----- | ----- | | PR001 | DC001 | 5 | | PR002 | DC001 | 5 | | PR003 | DC001 | 6 | | PR004 | DC002 | 7 | | PR005 | DC003 | 3 | | PR006 | DC003 | 11 | | PR007 | DC004 | 10 | | PR008 | DC005 | 66 | Table 2 | id | name | bonus | | ----- | ---- | ----: | | PR001 | res | 10 | | PR002 | des | 2 | | PR003 | DC | 1 | | PR004 | Drf | 7 | | PR005 | wed | 3 |

I am using SSIS to extract data from a .dbf-based ERP system. In SSIS, I have the Visual FoxPro OLEDB provider as a source and SQL Server OLEDB provider as a destination. The data copies quickly from the files into the sql table, but I have found that my results differ slightly depending on the Data Access Mode I use in the OLEDB source tool. - When I opt for the "Table or view" setting and then select the table I want from the dropdown, I get 49,725 rows. - When I opt for the "SQL command" setting and then use a SELECT * FROM [Table] query, I get 49,705 rows. - The results are consistent between runs. Looking in the folder that contains all the .dbf files, I find matching .cdx files for each table. Apparently .cdx files contain indexing and schema information for their accompanying .dbf? Is one of these access methods (select * vs. table dropdown) utilizing the .cdx while copying data, therefore producing more accurate results? While validating with an ancient revision of DBeaver: - I have found that the 49,705 rowcount dataset is missing some rows that are found if you use WHERE clause to locate them individually - I have found that the 49,725 rowcount dataset produces some rows that look like duplicates save a few columns, and looking these rows up individually returns just one of the rows

I am attempting to export data stored by a client's Visual FoxPro database to be used in a MySQL context. The data given is a folder of .dbf, .cdx, and .tbk files, as well as a few .dct, .dcx, and .dbc files. I have access to the directory with the files, but I do not have access to FoxPro. I have tried opening them with dBASE, which has been more supported and is supposed to be able to read DBF files, but that was to no avail: dBASE kept claiming that there were corrupted table/index headers, which prevented it from opening the DBF files even when in the same directory as the associated CDX files. I have tried opening them with data recovery tools to see what I could gather, and those appear to show strings stored in the rows, but due to what I believe is an encoding issue, they're surrounded by gibberish and not well organized. I have even tried opening them with spreadsheet tools like LibreOffice, but they gave similar results. Are there any other tools or methods I can attempt to get the data from these files and store them in a more modern format? I tried obtaining FoxPro, but it refuses to run on my version of Windows 10 due to its discontinuation. I appreciate any assistance or advice that you can give. Thank you in advance!

I need to SELECT amount FROM table WHERE type='1' AND type='2' AND type'3' Depending on the types, I need them to be pulled into different columns. For example, IF type=1 THEN amount1 IF type=2 THEN amount2 IF type=3 THEN amount3. Is it possible to this in one statement?

I have column with numeric values that I need to replace into text values. I used the sql statement: > REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(loanstatus, > '1', 'Apple'), '2', 'Banana'), '3', 'Citrus'), '4', 'Deer'), '5', > 'Elephant'), '6', 'Fruits'), '7', 'Grey'), '8', 'Hope'), '9', > 'Island'), '10', 'June'), '11', 'Kate'), '12', 'Live'), '13', 'Moon') I've tried using Double Quotations (") in place of Singles ('). I've also tried using String('1') and String("1"). Values 1 to 9 works fine. The problem is if the insert value is "11" then the output is AppleApple If "12" then "AppleBanana" when it should come out as "Kate" for 11 and "Live" for 12.

I have a column with numeric values I am querying SELECT column FROM table WHERE column='1' OR column='2' OR column='3' OR column='4' THEN REPLACE (column, "1", "ABC"); REPLACE (column, "2", "DEF"); REPLACE (column, "3", "GHI"); REPLACE (column, "4", "JKL") I am not sure if I may be using the semicolons wrong. Please help!

I've been searching for Visual FoxPro ODBC Driver and finally found it. I am using a middleware to connect a .DBC (foxpro) > MIDDLEWARE > SaaS. I see from postings on MSDN that Microsoft suggests using OLEDB instead of ODBC, however I'm trying to interface a data source with an application that connects to data sources using ODBC connections. Would this be possible?

We were having some issues with our old Microsoft Visual Fox Pro ODBC driver that we shoehorned into Windows 7 and is no longer supported by Microsoft and has not been for quite a long time... So when our anti-virus software finally stopped it from working, we needed to find another way to make it work. The Anti-virus company couldn't help us; they said it was a Microsoft problem, but they did recommend a couple of related KBs from Microsoft, which stated that we should use the Microsoft OLE DB Provider for Visual Fox Pro. After making sure that the Microsoft OLE DB Provider for Visual Fox Pro was installed, I created a linked server in Microsoft SQL Server Management Studio: 1. Right clicked the following element in the left hand pane, -> Server Objects -> Linked Servers and clicking New Linked Server ... from the context menu. 1. Then in the resulting New Linked Server dialog: 1. On the General page: 1. Entered a name in the Linked server: textbox 1. In the Server type: radio button, selected Other data source 1. In the Provider: drop down selected Microsoft OLE DB Provider for Visual Fox Pro 1. In the Product name: textbox entered VFPOLEDB 1. Entered the path to the data in the Data source: textbox. 1. Clicked OK I was then able to query the data in the Linked Server, and in order to make this accessible to MS Access on the workstations over an ODBC connection (as well as make it more secure) I created VIEWs with only the necessary fields for each of the tables in question using the following syntax: USE GO CREATE VIEW [dbo].[name-of-view-likely-the-same-name-as-referred-to-in-access] AS SELECT * FROM [name-of-linked-server]...[name-of-dbf-file-without-extension] GO Then on the workstations with the MS-Access Database that used to use the VFP ODBC driver, I created a new ODBC connection to the SQL Server containing the Linked Server, so I could access the VIEWs I just created. Then in the MS-Access database I added the linked tables (VIEWs really) from the ODBC connection, they were imported being named as dbo_ and very similar to `. So I renamed the with a old_ prefix, and renamed the new ones to removing the dbo_` prefix. It now appears that the Microsoft Access database is able to access those tables correctly, via Linked Table, via ODBC, via SQL Server, via VIEW, via Linked-Server, via Microsoft OLE DB Provider for Visual Fox Pro without the anti-virus getting in the way; however, I'm using test data to do this at the moment and NOT production; production has several other people accessing it, modifying it, etc...so provided that it turns out to be the case that the MS-Access database is just pulling data, and not writing it out, do you think this will work alright in production? And what if it is writing the data out? Will I be okay then too?

In a department where they were complaining that a report was being run slowly, I jumped on one of the empty machines there and tried it out. It took a good 6 and 15 seconds to run. Run from a client on the VM on the Hypervisor that contains the files, it runs almost instantaneously. And run from a client on a separate VM on the same Hypervisor, it takes about 19 seconds. (Of course the VM on the Hypervisor is probably a good bit faster than the workstations in question). I asked tech support for the app in question to confirm, that, in order for the report to be run, the files for every table (and it's index files) involved needed to be copied over to the local machine first by the client software (since there is not a server-side process for it); they confirmed it. I also asked which files/tables were involved in the query, and I was told that about six tables were involved; do to the nature of the worst case query being one that spans before and after final quarter close (last years archived check lines are involved as well as this years). The tables in question were the following: employees, employerconfig, paychecklinehistory, paychecklinesthisyear, payrollCodes, paygroups I summed up the total sizes of these six tables, and additionally the .cdx index files for each. I also noted that all computers in HR are: 1. Attached to the network via their IP phones...which contain a switch which is 100 Mbps 2. The phone is then attached to a PoE device (to power the said IP phone) and that device, despite having a Gigbit ethernet port that connects it to the rest of the network (the network is Gigbit); is limited by 24 ports that are 100 Mbs. So this lead me to believe that summing the file sizes and taking into account the speed of the file transfer would result in how long it might take to copy the files over to the client. So to look at the full picture here the time involved would be the following: 1. The amount of time it takes to copy the tables and their indexes to the client. 2. The amount of time it takes to join/filter the tables (in whatever order that takes place) 3. The amount of time it takes to display / generate the report with the results of that query For #1: Using an equation and a website , I figured out that it should take about 3 minutes and 15 seconds to run given the size of the table and index files and the 100 Mbs lan speed. For #2: It will depend on the workstation specs which vary, so I can't include it. For #3: This too will depend on the workstation's specs which vary, so I can't include it. Now my boss says he thinks it's something that can be repaired with the database, since there's a Gigabit Ethernet connection, but from what I can tell this isn't the case, and short of purging the tables, or hooking the HR workstations directly into the Gigabit Ethernet switch instead of their phones, I don't think anything else with the database can be done to speed this up unless we give them access to a VM on the same machine as the "server". Am I correct in thinking that this is an issue with the dual bottlenecks in the network, and not the database itself?

I select data from dbf files through OPENROWSET Visual FoxPro OLEDB. Despite forcing collation on columns (e.g. SELECT name COLLATE Polish_CI_AS FROM Employees) the polish specific characters keep being different characters. Due to the data model complexity, nested replace statements are not desired. Any idea how to fix it?

We have a really old data source (file-based) in Visual FoxPro, that we currently use via linked table through an ODBC data source. Recently, we started having an issue because the latest update to our Anti-virus Suite is stopping it from connecting in this manner. There are other ODBC data sources which do not use the same driver which do not have this issue. Also the Visual FoxPro driver ODBC driver is being shoehorned in using the registry because it's no longer supported by Windows 7. Is adding my Visual FoxPro data source as a linked server in SQL server and then in the MS-Access database, relinking the effected tables to it a possible solution to this issue? Note: the MS-Access databases use VBA and queries to retrieve and manipulate the data.