ERROR -- Data source name not found and no default driver specified.

Question

<!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} -->

I have a VB6 application that retrieves and updates data stored  in SQL Server 2000 tables. The application works fine using Windows XP. When I run the application using Windows Vista it retrieves data successfully but returns the following error message when I try to update data:

 

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

 

My connection string in the application:

 .ConnectionString = "driver={SQL Server}; server=JUV-PRO-04svr\sqlserver2000;user id = jowens; password=9999; Initial Catalog=" & cDBName 

 

Please tell me how to fix this problem.

Answer 1

Hi,

This is a bit confusing - are you sure that you can retrieve data successfully? If you are getting the error above, you are not able to load the driver and connect to the SQL Server at all. Or maybe the data retrieval is done under a different connection/connection string? Could you take a look at this and elaborate?

Regarding the error - are you by any chance running an x64 edition of Vista? The connection string above implies the usage of the OLEDB Provider for ODBC (aka Kagera). Microsoft didn't ship a 64-bit version of MSDASQL for Vista RTM, it got included into Vista SP1.

If you are not using x64-bit version of Vista or at any case on Vista, could you try to include "Provider=MSDASQL;" before all the entries in your connection string? Because MSDASQL was the default OLEDB provider and it wasn't provided on x64 Vista RTM, WDAC had to force the user to specify a provider.

HTH,
Jivko Dobrev - MSFT
--------------------------------------------------------------------------------
This posting is provided "AS IS" with no warranties, and confers no rights.

Answer 2

The error you are quoting is a connection error. So I assume you open one connection to select, and another connection to update. Correct? Can you provide each connection string. Is there any chance different connections are opened on behalf of different Windows users?

 

When you refer to Windows versions, do you mean the OS where the client app is running or where SQL Server is running?

 

                                                                               

Zlatko Michailov

Program Manager, SQL Server Protocols

Microsoft Corp.

 

If you are satisfied by this post, please mark it as “Answer”.

 

---

This posting is provided "AS IS" with no warranties, and confers no rights.

Answer 3

In Vista there were some changes to turn off persisiting security info, extended properties etc; by default (unless they are explicitly set to true). So if for update data you are doing something like cn.ConnectionString then your connection string may not have all the necessary information for a successful connection. So please provide us the connection strings (if indeed they are different) and we can look into it

---

This posting is provided "AS IS" with no warranties, and confers no rights

Answer 4

I only have 1 connection object in the application and it is used for queries and updates. I am sure the connection can retrieve data from the database tables.  The SQL Server database and the application .exe both reside on a server. The users all have an icon on their pc desktops that reference the .exe. The server and all pcs are 32 bit machines.

Answer 5

There is only 1 connection and connection string for all users. When I specify XP and Vista I am referring to the client's computers.

Answer 6

There is only 1 connection string and connection object. They reside in a function (RSOPEN) in a module. Wherever the application code opens a recordset it calls the RSOPEN function.

Answer 7

Hi,

Sorry about the multiple simultaneous posts, you didn't need to reply to each. :-) Unfortunately it's not easy to tell if another person is working on an answer.

Regarding the implementation - the users will click the shortcut icon to the server-based file and will run it on the actual users machine, XP or Vista. My understanding is that the *Vista* users get the error, is this correct?

Let's leave the 2-connection-strings question aside for now. Would you be able to change the connection string to include the "Provider=MSDASQL;" in the beginning and recompile the application? Does it work with Vista then, does anything change?

Also - can you create a test DSN in the ODBC Administrator (Start - Run - odbcad32) on Vista and verify if it works?

HTH,
Jivko Dobrev - MSFT
--------------------------------------------------------------------------------
This posting is provided "AS IS" with no warranties, and confers no rights.

Answer 8

I hadn’t noticed you were trying to connect to a named instance. Can you double-check the SQL Server Browser service is running on the server and the firewall on the server allows inbound traffic to SQL Browser (or UDP 1434).

                                                                               

Zlatko Michailov

Program Manager, SQL Server Protocols

Microsoft Corp.

 

If you are satisfied by this post, please mark it as “Answer”.

 

---

This posting is provided "AS IS" with no warranties, and confers no rights.

Answer 9

One more detail: I've tested your connection string (slightly modified) on my Win2k8 and Win2k3 machines:

Dim cn
Set cn = CreateObject("ADODB.Connection")
cn.Open "driver={SQL Server}; server=mymachine;user id = myuser; password=mypassword; Initial Catalog=mydb"

MsgBox "Open!"
cn.Execute "SELECT @@VERSION"
cn.Close

and it is working nicely. However, if you provide "Data Source=something", you start getting the error:

testcript.vbs(4, 1) Microsoft OLE DB Provider for ODBC Drivers: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

Are you sure that there's no second connection string in the application which relies on a DSN and uses "Data Source=dsnname;" instead of the DSN-less one above? My proposal is to check the defined DSNs on the XP machines and see if all of them have one in common (Start - Run - odbcad32).

HTH,
Jivko Dobrev - MSFT
--------------------------------------------------------------------------------
This posting is provided "AS IS" with no warranties, and confers no rights.

Answer 10

Moonflower, what is the current status on this issue? Is it still pending, or was it resolved?

                                                                               

Zlatko Michailov

Program Manager, SQL Server Protocols

Microsoft Corp.

 

If you are satisfied by this post, please mark it as “Answer”.

 

---

This posting is provided "AS IS" with no warranties, and confers no rights.

Answer 11

adding PROVIDER=SQLOLEDB; to connection string solved my problem.