Open SQL tables

[SBTBILL]

I'm trying to connect VFP 9 to an MSSQL 10.5 database with this connection string

lcconnstr="Driver = {SQL SERVER};SERVER=BILLCOUTURE-PC"

I've checked and this is the proper name for the server.

I get data source name not found and no default driver specified

I can open and view the tables in Management studio. I'm using Windows authorization and have no problems. Since these are test databases no paswword.

I can open using VFP at a client site with the following string.

DRIVER={SQL SERVER};server=serverplumbing;UID=at;PWD=HTECH

I made slight changes to the uid and pwd.

 

[bborissov]

Remove all spaces in connection string and put Trusted_Connection=yes:
Something like this:
[tt]
lcconnstr="Driver={SQL SERVER};SERVER=BILLCOUTURE-PC;Trusted_Connection=yes;DataBase=YourDataBaseName"
[/tt]

Borislav Borissov
VFP9 SP2, SQL Server

 

[Mike Lewis]

Several points come to mind.

First, given that the error is "data source not found",that suggests that VFP is looking for a DSN. Are you sure you are using SQLSTRINGCONNECT() as opposed to SQLCONNECT()?

Since upgrading to 10.0, I've used the SQL Native Client as the ODBC driver, in which case the first part of the string would be:

DRIVER=SQL Server Native Client 10.0;

It might be worth trying that.

Also, I'm not sure about this, but I wonder if the server name is case-sensitive. Again, something for you to try.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Indeed the spaces in "Driver = {...}" make a difference and would cause a dialog to appear.
Indeed SQLConnect doesn't make a connection, but returns -1 and AERROR() then reports the error you stated.

So almost anything, which could go wrong, was already addressed.

The remaining problem could be your installation is incomplete, eg you have no SQL Server ODBC driver installed, at least not in a 32bit version.

If you install an SQL Server instance, client side software like the ODBC drivers or the management studio are not installed, the server instance does not need them.

In most recent SQL Server verrsions there even is another extra step before syou install the server instance, you first only install a SQL Server Installation Center and from there you can install an instance (the server and it's services) or client side softwwre.

Pardon me, if you try to connect to an already installed server instance in your LAN and are not trying to get a developer system with a local SQL Server installation up and running, but still the question also is, if the ODBC driver you address is installed at all.

Bye, Olaf.

 

[SBTBILL]

This is the code used

sTORE "" TO lcconnstr
DO dolink WITH lcconnstr
oconnection=CREATEOBJECT("ADODB.Connection")
oconnection.OPEN(lcconnstr)

Do link should return the following.

lcconnstr="Driver = {SQL SERVER};SERVER=BILLCOUTURE-PC

 

[SBTBILL]

Turned out to be the spaces in the connection string.

Thanks, I would never have guessed.

 

[Olaf Doschke]

Why using an ADODB.Connection to SQL Server? Do you need an ADODB.Recordset for ActiveX? If not, by all means stop using ADODB.Connection even for cursoadapter, it has the overhead of creating a Recordset and then a VFP cursor and updating the remote data a TABLEUPDATE makes the cursoradapter update the recordset and only after that this goes to the SQL Server.

Inside VFP use ODBC, outside VFP to access VFP data, use OLEDB, but not because it's the faster technology, but having the latest driver supporting VFP9 queries.

Bye, Olaf.

 

[Olaf Doschke]

More precise:

With VFP
Access DBFs native (whatever suits your needs from USE over SQL to Cursoradapters, use cursors anyway).
Access remote data via ODBC (and prefer cursoradapters for readwrite of application data and SPT for mass updates, recursive SQL or other complex DML queries, all DDL queries, scripts, etc).
Create Recordsets for ActiveX via ADODB.Connection/ADODB.command/ADODB.recordset.

Without VFP
Access (foxpro) DBFs via VFPOLEDB or perhaps advantage database ODBC.
Access native data in whatever best practice for the language you use.

That would be my recommendations for data access. So that would leave ADODB access with VFP just for creating recordsets an ActiveX control might need. I'm not a fan of that, I use the exontrol treeview without Recordsets and rather address and automate the object model to create, expand, collapse, move and read tree nodes and synch all that with an updatable cursor. o let all data access go through the same business logic and data acccess objects based on the common denominator for internal,native and external,remote data access: Cursors.

Bye, Olaf.

 

[SBTBILL]

I used ADO because I was following what had been done previously. However, I also program in VB and find the similarities very nice. I can take the basic code with very minor tweaks and run it in either language as well as SQL stored procedures.