Cannot Connect to SQL Server 2008 Express FromVFP 9 Program (exe)

[carolx]

I installed SQL Server 2008 Express R2 on a computer with windows 7 Home Premium OS. Created the DSN (ODBC). The connection is OK when you test it. When I try to access the database using a VFP 9 program (exe) on the same computer I get the message SQL Server does not exist or access denied.

I get the same message when I turn off UAC and the firewall.

All the protocols for SQL Server 2008 Express R2 are enabled.

Both SQL Server and SQL Server Browser services are started during the run.

The connection string is sqlstringconnect([dsn=Mortgage;trusted_connection=yes]). It works OK with SQL Server on Windows XP.

 

[Mike Lewis]

You might need to add the server name to the connection string, and also the name of the SQL Server driver.

This is what I've got in a similar connection string. This actual string won't work for you, of course, but it might serve as a model:

DRIVER=SQL Server;UID=MyUserName;Trusted_Connection=Yes;
WSID=MyWorkstationName;SERVER=MyServerName

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

dsn=Mortgage

A DSN is a predefined connection made via ODBC Administrator and includes infos about what server, what driver used etc. You need to define tis same DSN as you have in XP on the Windows 7 machine, this is not there by default, nor by magic.

Or try a DSN-less connection with Server=(local)\SqlExpress in it.

But sorry, I can't see from here, how your xp dsn is setup, you need to look yourself.

Bye, Olaf.

 

[finsys]

As this is a connection to SQL2008, then rather than setting the driver as {SQL Server}, you should use {SQL Server Native Client 10.0}. {SQL Server} is for SQL2000 and doesn't support a lot of the new data types in SQL2008 e.g. DATETIME2 will come through as a character field when using remote views.

Olaf, the OP stated in their second sentence that they have created the DSN on the Windows 7 PC and that it tests ok.

I would agree that a DSN-less connection would be a better option, it makes the app more "portable" IMO, you don't have to remember to create DSNs on new or re-imaged PCs and it's easier to make a change (once), say if the server name changes.

 

[JRB-Bldr]

Created the DSN (ODBC). The connection is OK when you test it.

But did you create it as a 32-bit ODBC connection (using odbcad32.exe in the Syswow64 directory) or did you do it as a 64-bit ODBC connection in the System32 directory?

Regardless, a DSN-less approach would be best.

Good Luck,
JRB-Bldr