11

I have a legacy VB6 app which builds a DSN based on a parameter in a config file. The parameter is an ODBC connection, and the connection has a name (DSN-NAME) which maps a server (DBSERVER) to a driver ("SQL Server Native Client").

Generally, it builds a DSN like this:

DSN=DSN-NAME;User=foo;Password=bar

If I specify a hostname in the file, it builds a connection string which says

DSN=DBSERVER;User=foo;Password=bar

The error message reported is:

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

This suggests to me that there is perhaps a way of specifying a default driver, which may mean I can specify just the server name in the config file, and not need to create the ODBC connection.

(I am aware these can be created automatically; this is just to simply installation, and to satisfy my curiosity).

How do you specify a default driver? If I can set the default driver to SQL Server Native Client, can I then say DSN=DBSERVER and connect?

Edit: the point was to try and do this without changing the connection string. All the research suggested this isn't actually possible, but he wording of the dialog suggested it might be.

crb
  • 8,132
  • 6
  • 37
  • 48

4 Answers4

5

I had the same problem and fixed it by using the 32-bit ODBC admin to create a 32-bit DSN rather than the 64-bit admin in Administrative Tools which only creates 64-bit DSN which don't work.

The 32-bit ODBC Manager is located at C:\Windows\SysWOW64\odbcad32.exe

See this article "Data source name not found and no default driver specified" on Corey Gilmore's blog.

Nate Anderson
  • 18,334
  • 18
  • 100
  • 135
mark.murphy
  • 51
  • 1
  • 2
  • The link is not available anymore. – Patrick Jan 17 '19 at 13:14
  • 3
    @AstralisSomnium Here is an archive of the linked blog post: http://web.archive.org/web/20120623001206/http://coreygilmore.com/blog/2010/04/14/odbc-driver-manager-data-source-name-not-found-and-no-default-driver-specified/ – ThatMatthew May 17 '19 at 18:13
1

To specify a default driver, use DRIVER= in the connection string:

DRIVER=driver name here;DATABASE=mydb;USER=foo;PASSWORD=bar

The driver name is the name that appears in the Control Panel ODBC config tool for each driver. Note you will need to supply the info which would normally come from the DSN, in this case the database name.

  • 5
    Thanks, but to me, that isn't a "default" driver, that's a "user specified" driver. – crb May 12 '09 at 00:50
1

You can achieve what you want by appending ";SERVER=dbserver" to your connection string.

There is already a server specified in your DSN, but the SERVER keyword in your connectionstring will override that.

http://msdn.microsoft.com/en-us/library/ms715433(VS.85).aspx

ErikE
  • 857
  • 8
  • 18
  • Handy, but I can't change the connection string that much, just change how it is generated. Eventually I hope we'll rewrite that app to use DSN-less connection strings :) – crb Jul 12 '09 at 19:31
0

Use a DSN-less connection string...it can be created 'on the fly' to exactly suite your purposes....no piddling with odbcad32.cpl or reg/ini files to control/configure a dsn required.

see http://support.microsoft.com/kb/147875

for details

CMB
  • 456
  • 3
  • 6
  • Thanks, but that doesn't clear up my question about a default driver. The connection string is generated by code I can't change. – crb Jul 12 '09 at 19:31
  • 1
    Sorry, I misunderstood. To clarify, I have a couple of followup questions. Are you are using ADO to connect with an ODBC targeted database? Is this target Database always SQL Server? Always connected via Native Client? Your desire is to know how to specify the default driver in the OLEDB connect string and not the ODBC DSN? So in essense, you desire to use ADO, specify an OLEDB provider of ODBC, provide additional information in the OLEDB Connection string to specify the SQLServer Target? – CMB Jul 13 '09 at 03:45
  • In this case, it's always yes. But I can't change the generated connection string, else I would just put SERVER=dbserver in it. – crb Aug 25 '09 at 14:04