So there are some odd known issues with the csv/text interface such as https://support.microsoft.com/en-us/kb/235889 but once its setup it works in my experience.
I have a couple use cases like yours but instead I have the remote end script the copying of the file local, that makes the whole process slightly more robust. You might try that approach and ditch the fragile mapped drive aspect. jlc -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Kurt Buff Sent: Wednesday, March 23, 2016 5:19 PM To: ntsysadm <[email protected]> Subject: Re: [NTSysADM] Win7/8.1 and ODBC - it's a bit complicated No, we are not. There is no SQL Server anywhere in this chain. I *so* *much* wish there were. It would make life very simple. Currently our ERP system exports the data meant for UPS Worldship to a CSV file on a share local to itself. The workstations running UPS Worldship map a drive to that share on the ERP server in the DSN for the MSFT Text/CSV Driver. The DSN only really understands a drive letter, but lets us point the drive letter to the UNC of the share on the server. We are doing it this way for historical reasons - The ERP system (an old version of Avante from Epicor, which uses a Pick-style database) doesn't natively understand SQL. We've been doing this export this way since implementation, which was a very long time ago. About 6 years ago we purchased a product that allows data to be exported from the ERP system to SQL Server, and use it to export lots of data to SQL Server, but this particular bit of data is still being output as CSV. Kurt On Wed, Mar 23, 2016 at 3:24 PM, D R <[email protected]> wrote: > Kurt, > > Are you using an SQL Database from a server? > > Installation instruction state that you can use the SQL Express 2012 that > comes with the install software. > > Have you tried to do a reinstall on that particular computer and let it > install the MS SQL Express 2012? Just to see what it does? > > Daniel > > On Wed, Mar 23, 2016 at 4:46 PM, Kurt Buff <[email protected]> wrote: >> >> So, it doesn't seem to be wireless that's a fault. >> >> I've checked your suggestion, and went even further with it. >> >> I've used both versions of odbcad32.exe (the 64bit one in >> c:\windows\system32 and the 32bit one in c:\windows\syswow64), to >> create system and user DSNs, and experimented with both. No go. >> >> I removed the drive mapping that was created/updated via GPP, on the >> theory that the drive mapping in explorer.exe was being updated or >> refreshed and killing the mapping in the DSN - this does not seem to >> be the case. >> >> I also made him an administrator on the box. >> >> Nothing works. >> >> I've exported the registry settings for ODBC in both spots >> (HKEY_LOCAL_MACHINE\SOFTWARE\ODBC and HKEY_CURRENT_USER\Software\ODBC) >> to see if there is any corruption (I don't see any odd characters, so >> I don't think there is any) >> >> What really chaps me is that I don't see where/how the text driver >> makes the mapping between the drive letter and the requisite UNC path. >> >> I've even searched the entire registry for the share, and see nothing >> but MRU references to it - nothing that looks like it's relevant to >> ODBC. >> >> I have also found no documentation on how/where the text driver makes >> the connection between the drive letter and the share, in spite of a >> lot of searching. >> >> I'm going to punt on this one. >> >> I've asked the user to simply never log out - just lock the >> workstation at night. In the case of the machine kicking him out or >> rebooting for patches, we can manually recreate the connection via the >> GUI in about 10 seconds - after we walk downstairs, which is a minor >> PITA. >> >> I've engaged the ERP administrator, and will probably request that he >> work on exporting the data to a SQL server somewhere, so that we can >> set up a more standard SQL DSN - but that's going to take time and >> effort on his part, and I probably won't see results for some months. >> >> Kurt >> >> On Tue, Feb 2, 2016 at 1:04 PM, Andrew Tallarita >> <[email protected]> wrote: >> > Kurt, >> > >> > >> > We had a similar issue recently with ODBC connections being dropped/lost >> > and >> > for us it was related to a change M$ made in 7 and 8 in how in handles >> > the >> > 32 and 64 bit connections differently than from before with a ::Cough:: >> > "Security update". I for the life of me can't remember nor find the >> > details >> > right now. >> > >> > We had to use the ODBC Data Source Administrator (32-bit) and make sure >> > that >> > under the Platform column for that connection it listed it as >> > "32/64-bit" >> > and in order to do so we basically removed the existing connections, >> > rebooted and reestablished the connections. We've been fine after, >> > although >> > this is also with a connection to our SQL servers and Win 7(64-bit) >> > clients. >> > >> > Hope this gives you some headway. >> > >> > >> > Regards, >> > >> > Andrew Tallarita >> > IT Support Specialist >> > BNL Industries Inc,. >> > [email protected] >> > ph: 860.870.6222 >> > cell: 860.849.1654 >> > http://www.bnl.com >> > >> > CONFIDENTIALITY NOTICE: This e-mail message, including any attachments, >> > is >> > for the sole use of the intended recipient(s) and may contain >> > confidential >> > and privileged information or otherwise be protected by law. This notice >> > serves as a confidentiality marking for the purpose of any >> > confidentiality >> > or nondisclosure agreement. Any unauthorized review, use, disclosure or >> > distribution is prohibited. If you are not the intended recipient, you >> > should not disseminate, distribute or copy this e-mail. Please notify >> > the >> > sender immediately by e-mail, if you have received this e-mail by >> > mistake >> > and delete this e-mail from your system. E-mail transmissions cannot be >> > guaranteed to be secure or error-free as information could be >> > intercepted, >> > corrupted, lost, destroyed, arrive late or contain viruses. The sender, >> > therefore, does not accept liability for any errors, omissions or damage >> > caused by any virus transmitted by this error. >> > >> > Information contained herein is subject to the Code of Federal >> > Regulations >> > Chapter 22 International Traffic in Arms Regulations. This data may not >> > be >> > resold, diverted, transferred, transshipped, made available to a foreign >> > national within the United States, or otherwise disposed of in any other >> > country outside of its intended destination, either in original form or >> > after being incorporated through an intermediate process into other data >> > without the prior written approval of the US Department of State. >> > >> > On Tue, Feb 2, 2016 at 1:26 PM, Kurt Buff <[email protected]> wrote: >> >> >> >> All, >> >> >> >> We have three machines in our shipping department running UPS >> >> Worldship. >> >> >> >> There used to be only one workstation, and it was wired, but we >> >> reworked the shipping area, and now there are three workstations in an >> >> area that's never been wired, so all three stations are wireless - I >> >> don't know if this is relevant or not, but it probably is. >> >> >> >> The program gets data from our ancient ERP system - it generates text >> >> files that it places on a share - via an ODBC connection that's mapped >> >> to that share for those text files. >> >> >> >> What's happening is that frequently (more than once a week) the Win8.1 >> >> workstation is losing its ODBC connection (it also happens, but much >> >> less often, to the Win7 machines). The Explorer drive mapping remains, >> >> or is remapped, I'm not sure which - we're using a GPP for the drive >> >> mapping. >> >> >> >> Once this happens, I must use odbcad32.exe to remap the drive, because >> >> even though it's already mapped in Explorer, the drive letter doesn't >> >> show for ODBC. >> >> >> >> So far, it only seems to happen if the user logs off or shuts down the >> >> machine overnight - never during the day after they've logged in. I've >> >> checked the power settings for the workstations, and they're set as I >> >> would expect - no sleeping, hibernating, etc. >> >> >> >> I'm not finding anything in the workstation event logs that seems >> >> relevant. >> >> >> >> I'm running a trial now, asking them to just lock the workstations at >> >> night, to see if that helps, but has anyone run into this kind of >> >> problem, or have thoughts on how to mitigate it? >> >> >> >> >> >> Thanks, >> >> >> >> Kurt >> >> >> >> >> > >> >> > > > > -- > Daniel Rodriguez > [email protected]
