An ODBC connection has to be created on every workstation, as well as installing a SQL Client as well. I'd avoid the ODBC route and go straight SQL Client that does have to be installed on each machine. Pick a higher Client version number such as 10 or 11 today.
connectionString="Data Source=SERVERNAME\INSTANCE; Initial Catalog=DATABASE YOU ARE USING; Persist Security Info=True;User ID=SQLUSER ;Password=THE PW for that USER" I recommend making an AD user that will be used to work with SQL Server. Change it on some sort of security basis you set up. You can download SQL Server Management Studio for SQL Express today giving you full access to all wizards in working with SQL Server. You can also install SSIS & SSAS for pulling data out of FOX data into SQL Tables on a daily basis for your reporting and or Data Warehouse that will probably be set up. Adding insult to injury, you can install SSRS as well and start to do reporting. It is not the Fox report writer and it will give you some heartache getting good with it but over the long haul, it will be worthwhile. The last point to make is that you can add in Power BI and start to learn how to make dashboards that is based off of your starting DW cube(s). We use the cube concept for every business center at our company. Purchasing has it's cube as well as Sales. Accounting gets one too. Planning loves theirs to sum gross volumes of resin across all orders and break that out to either weekly, monthly or quarterly totals. On Wed, Aug 21, 2019 at 7:08 AM Peter Cushing <pcush...@whisperingsmith.com> wrote: > Now got it working with SQLSTRINGCONNECT() thanks. > I will have a look at this native client. Does that give you any > advantages over the ODBC connection? > > Also noticed in an archive post someone using this to check the > connection before doing a real query/update. > > nResult=SQLEXEC(datahandle,'SELECT @@identity') > If nResult is 0 then assume a problem and disconnect, throw away the old > handle, and then reconnect. > > How necessary is that check? Should I use it before every call? > > Thanks, > > Peter Cushing > IT Department > WHISPERING SMITH > > > On 21/08/2019 11:34, Frank Cazabon wrote: > > I use SQL native client (which is an option to install when installing > SQL server): > > > > https://www.microsoft.com/en-us/download/details.aspx?id=50402 > > > > And then I use a connection string like: > > > > ConnectionString=DRIVER=SQL Server Native Client > 11.0;Trusted_Connection=Yes;DATABASE=GoldBuy;SERVER=MyServer;App=MyApp; > > > > Or: > > > > ConnectionString=DRIVER=SQL Server Native Client > 11.0;Trusted_Connection=No;uid=myuserid;PWD=mypassword;DATABASE=GoldBuy;SERVER=MyServer;App=MyApp; > > > > But you could use a user or system dsn. > > > > On 20 August 2019 12:26:35 GMT-04:00, Peter Cushing < > pcush...@whisperingsmith.com> wrote: > >> Hi, > >> > >> Thanks to everyone that replied. I don't have to worry about getting > >> fired, so all good there. > >> SPT seems the easiest to begin with, but not got it working yet. > >> I have created an ODBC link called X2live. I set it up as a system DSN > >> as I tried a user DSN first and that didn't work. > >> Just wondering what version of the ODBC driver are you using. Mine is: > >> Microsoft SQL Server ODBC Driver Version 06.01.7601 > >> Did a few quick searches and can't seem to establish if this is a 32 or > >> 64 bit driver. > >> > >> When I click test data source it comes back with Tests completed > >> successfully but if I try > >> ?sqlconnect('x2live') && returns -1 > >> > >> I have tried putting in the username/password and didn't make any > >> difference. > >> > >> What actual commands do you use to connect? > >> > >> TIA > >> > >> Peter Cushing > >> IT Department > >> WHISPERING SMITH > >> > >> > >> On 20/08/2019 15:11, Paul H. Tarver wrote: > >>> Peter, > >>> > >>> Personally, I like SPT because I can build valid and safe > >> pass-through commands in my code based on user selections, generate the > >> string, hand it off to the server with an ASYNCH connection and easily > >> provide users with progress feedback while data is returned. > >>> For some reason, I've never been able to wrap my head around cursor > >> adapters completely. Maybe it’s a control issue! :) I think SPT just > >> made more sense to me upfront. Thankfully, Foxpro gives you plenty of > >> different ways to accomplish the same things and you can pick the one > >> that works the way you do! I use it to query data for reporting > >> purposes and it gives me a lot of control over how I construct the > >> query string since I can use all of the string functions within Foxpro > >> to build exactly the query I want based upon users' selections. > >>> Another advantage is I able to dump the query string to my audit > >> files just before the SQLEXEC() command fires, so if something goes > >> wrong, I can just copy the query to my SQL management tool and see if I > >> can manually figure out what is going on. > >>> So far, I've used SPT successfully to backend Foxpro with SQL, MySQL, > >> Oracle, Access, PostgreSQL, SQLite, etc. I just tweak queries to > >> customize for the database and the rest of my code remains the same. > >>> It works for me, but I'm sure, someone else here will provide you > >> with the benefits of cursor adapters. > >>> Paul H. Tarver > >>> > >>> > >>> -----Original Message----- > >>> From: ProfoxTech [mailto:profoxtech-boun...@leafe.com] On Behalf Of > >> Peter Cushing > >>> Sent: Tuesday, August 20, 2019 4:40 AM > >>> To: profoxt...@leafe.com > >>> Subject: SQL server connection > >>> > >>> Hi, > >>> > >>> After running my app (VFP 9) on our local network (with a DBC) for > >> many > >>> years, we are upgrading it (in stages) to a visual studio app with > >> SQL > >>> server back end. > >>> I have read many people here talking about using SQL as a back end. > >> At > >>> the beginning I will just be accessing the SQL data for reporting > >>> purposes but may need to write to it at some point. Looking through > >> the > >>> archives some people mentioned SPT and others cursor adapters. Just > >>> wondering what people recommend to connect, so I can avoid the usual > >>> gotchas. > >>> > >>> Thanks, > >> > >> This communication is intended for the person or organisation to whom > >> it is addressed. The contents are confidential and may be protected in > >> law. Unauthorised use, copying or disclosure of any of it may be > >> unlawful. If you have received this message in error, please notify us > >> immediately by telephone or email. > >> > >> www.whisperingsmith.com > >> > >> Whispering Smith Ltd Head Office:61 Great Ducie Street, Manchester M3 > >> 1RR. > >> Tel:0161 831 3700 > >> Fax:0161 831 3715 > >> > >> London Office: 101 St. Martin's Lane,London, WC2N 4AZ Tel:0207 299 > >> 7960 > >> > >> [excessive quoting removed by server] _______________________________________________ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech Searchable Archive: https://leafe.com/archives This message: https://leafe.com/archives/byMID/cajidmyjidivo7hhahvt9ao8wo4ithvhoz-nwomfe-tvf12d...@mail.gmail.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
