A data connection fails with:
Connector connect error: SQL##f — SqlState: IM002, ErrorCode: 0, ErrorMsg: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
Resolution:
There are three likely reasons for this issue to occur. Neither of the issues are Qlik related, but depends on ODBC setup in Windows.
- The database drivers used in the DSN needs to be updated
- Referred Data Source Name (DSN) name is not available for the user/server
- Database driver used in DSN is damaged or not available
Validate that DSN is available to the reloading user/server.
- Open the document using QlikView Desktop client
- Go to Script Editor (Ctrl+E). If reload issue occurs with server, skip to step 12.
- Open ODBC editor based on the driver being 32- or 64-bit
- Tools > ODBC Adminsitrator 32 bit..
- Tools > ODBC Adminsitrator 64 bit..
- Validate that the used Data Source Name (DSN) is on the correct tab
- User DSN — These DSNs will be available for the currently logged in Windows user, i.e. the user running QlikView desktop
- System DSN — These DSNs will be available for all users on the machine, i.e. both the devleoper user and any QlikView service user
- Add DSN to System DSN if required to be used by multiple users
Validate that DSN works in Windows
- Select the DSN that fails in QlikView
- Click on Configure…
- Click on Finish
- Click on Test Data Source…
- Validate that the connection works
- Success — The connection now also works in QlikView
- Failed — The DSN settings are incorrect. Contact database administrator for details.
- Close the ODBC administrator and reload the QlikView document
- If issue occurs while QlikView Server is reloading the document, obtain the Data Source name from the Scrip editor > Data > Database > ODBC > Select or from the connection string itself. (i.e «ODBC CONNECT TO <System DSN name>)
- Make sure to create the same Data Source using Windows «ODBC Data Source Administrator» on the specific QlikView server reloading the document.
Cause:
The Qlik Engine cannot find a corresponding or the correct ODBC driver. Typically not an error in the Qlik product, but an issue with the environment configuration. Qlik is unable to find the correct ODBC driver or an incorrect one is configured.
Environment:
Qlik Sense Enterprise on Windows
Платформа 1С:Предприятие 8.3 (8.3.5.1383)
УТ11
В конфигураторе есть внешний источник MySQL — таблицы и поля втащились без проблем.
Есть запрос
———————————-
ВЫБРАТЬ
ps_orders.Ссылка,
ps_orders.id_order
ИЗ
ВнешнийИсточникДанных.ТКС.Таблица.ps_orders КАК ps_orders
————————————
Но при выполнении запроса [Запрос.Выполнить().Выгрузить()] выдается такая вот ошибка
Ошибка ODBC. SQLSTATE: IM002
Номер ошибки: 0
Описание: [Microsoft][Диспетчер драйверов ODBC] Источник данных не найден и не указан драйвер, используемый по умолчанию
В чем, собственно, ошибка? все перелазил в гугле — все не то.
Кто уже решил — направьте, пожалуйста.
- Remove From My Forums
-
Question
-
Hi.
First of all I want thank you for everything what this team create for PHP community.I am trying to run SQL Server Driver for PHP under this configuration:
— PHP 5.2.6 as ISAPI
— IIS 7
— SQL server 2008 express
— Windows 2008 Web trial— I can successfuly connect via Microsoft Management Studio(in both mixed or windows authenticated mode) with login credentials, which you will see bellow in PHP code.
— Database and PHP are on the same computer(port 1433 enabled in firewall).
— Pipelines na TCP/IP are allowed in SQL server configuration(in SQL server network and in SQL native client too)I have already successfully added extension to PHP and in Phpinfo() I see:
sqlsrv
sqlsrv support enabled
Directive Local Value Master Value
sqlsrv.LogSeverity 0 0
sqlsrv.LogSubsystems 0 0
sqlsrv.WarningsReturnAsErrors On OnI have this PHP page:
Code Snippet
<?php
$connectionInfo = array(«UID» => «sa», «PWD» => «myStrongPassword»);
$serverName = «HONZASQLEXPRESS»;
$connectionInfo = array( «Database»=>»AdventureWorks2008»);
$conn = sqlsrv_connect( $serverName, $connectionInfo);if( $conn )
{
echo «Connection established.n»;
}
else
{
echo «Connection could not be established.n»;
die( print_r( sqlsrv_errors(), true));
}?>
The database AdventureWorks2008 really exists and sa user has got access to database.
After run this code I receive in browser:
Code Snippet
Connection could not be established.
Array
(
[0] => Array
(
[0] => IM002
[SQLSTATE] => IM002
[1] => 0
[code] => 0
[2] => [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
[message] => [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
)
)Please can you help me? What means IM002? I spend a few hours today with this problem, but I have no idea what is wrong.
Thank you very much!
Honza
Answers
-
You also overwrote your connection info array, you should combine those lines and try again
-
Honza,
In examples 2 and 3, you’re relying on a component (the SQL Server OLE DB provider) that communicates directly with the database.
Example 1 uses the SQL Server Driver for PHP, which does not communicate directly with SQL Server. Instead, it relies on a lower level compnent (the SQL Server Native Client) to handle the communication with the database. As Brian Swan pointed out earlier in the thread, the error you’re receiving («[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified») indicates that the expected SQL Server Native Client components are not installed.
You can download the SQL Server Native Client using the following link:
http://download.microsoft.com/download/2/7/c/27c60d49-6dbe-423e-9a9e-1c873f269484/sqlncli.msi
Joel pointed out earlier in the thread that you’re still going to have problems connecting once you install the SQL Server Native Client because of how you’ve created your connectionInfo array. In your code, you create the array and then overwrite it with different information, which means that when you’re attempting to connect you’re currently only specifying the database but not the user ID and password:
Code Snippet
$connectionInfo = array(«UID» => «sa», «PWD» => «myPass»);
$serverName = «lmsunifor.com»;
$connectionInfo = array( «Database»=>»unifor_stable»);Instead, combine the information into a single array, as shown here:
Code Snippet
$connectionInfo = array(«UID» => «sa», «PWD» => «myPass»,
«Database»=>»unifor_stable»);Once you’ve addressed both of these issues, I expect you’ll be able to connect to your database successfully. If not, please let us know.
David Sceppa
Program Manager, Microsoft SQL Server Driver for PHP
Пересказ статьи Rayis Imayev. Error [IM002] [Microsoft][ODBC Driver Manager] «Data source name not found and no default driver specified» and who do you trust?
Это очень короткий пост, просто чтобы напомнить себе, но, если вы когда-либо пытались подключиться к базе данных PostgreSQL с помощью интерфейса ODBC (знаю, что это уже звучит, как очень интересный вызов), то могли столкнуться с таким сообщением об ошибке: “ERROR [IM002] [Microsoft][ODBC Driver Manager] Источник данных не найден и не указан драйвер по умолчанию.”
Я допускаю, что мой поиск онлайн этой ошибки сразу показал ресурсы Microsoft и Stackoverflow, которые только смутили меня и не помогли решить проблему с подключением к ресурсу PostgreSQL через ODBC:
- https://docs.microsoft.com/en-us/answers/questions/227987/34error-im002-microsoftodbc-driver-manager-data-so.html
- https://stackoverflow.com/questions/17115632/microsoftodbc-driver-manager-data-source-name-not-found-and-no-default-drive
Microsoft в своей документации на SSIS дает очень хорошее объяснение, как подключиться к источнику данных PostgreSQL, и даже приводит пример строки подключения с драйвером PostrgreSQL ODBC.
Driver={PostgreSQL ODBC Driver(UNICODE)};Server=<сервер>;Port=<порт>;Database=<база данных>;UID=<ид пользователя>;PWD=<пароль>
Мои попытки воспользоваться этим примером не привели к успеху, я по-прежнему получаю свое сообщение об ошибке: “data source name not found”.
Смущал один момент, и я захотел его проверить. Если посмотреть на список 64- или 32-битных подключений ODBC PostgreSQL, которые я сделал, обнаружилось, что они не согласуются с тем, что я читал в технической статье Microsoft.
Мой список драйверов был другим по сравнению с этой технической статьей, и изменить имена этих драйверов, чтобы они соответствовали “PostgreSQL ODBC Driver(UNICODE)”, было не в моих силах.
Тогда я решил изменить мою строку подключения на такой формат “Driver={PostgreSQL Unicode};Server=<сервер>;Port=<порт>;Database=<база данных>;UID=<ИД пользователя>;PWD=<пароль>” в соответствии со списком драйверов, которые я имел. Я тестировал её с обоими 32- и 64-битными драйверами. И только тогда все заработало, и я смог подключиться к моему экземпляру PostgreSQL. Это не означает, что такое соглашение об именовании будет работать при всех обстоятельствах: драйвер ODBC может быть обновлен до новой версии, или другие факторы могут изменить успешный путь подключения к данным. Это изменчивый мир!
Я все время вспоминаю рекомендации Илона Маска делать требования к бизнесу менее тупыми, поскольку может найтись умный человек, который предоставит вам требования, которые вы не сможете опровергнуть.
Не поймите меня неправильно, я никого не виню. Это просто доказательство того, что доверять — это нормально, но стоит все же проверять и проверять. Еще один урок для меня и не новый. 
Автор не разрешил комментировать эту запись
This article, provides useful troubleshooting tips, for the following error which you might get, under certain circumstances, when trying to connect to SQL Server from Python, using pyodbc: [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect).
Prior to sharing our tips for troubleshooting the issue, let’s discuss about the two main ways of connecting to SQL Server from Python.
Ways of Connecting to SQL Server from Python
In Python, there are 2 main ways for connecting to SQL Server, using the pyodbc module and Microsoft ODBC Driver 17 for SQL Server.
Specifying the Full Connection String in Python
The first way, is to define the full connection string in you Python code.
For example, in case you are using a username/password instead of a trusted connection, here’s a relevant example:
conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=server_name;DATABASE=database_name;UID=user;PWD=password')
In case you are using a trusted connection, here’s another relevant example:
conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=server_name;DATABASE=database_name;Trusted_Connection=yes;')
Referencing an ODBC System DSN
The second way, is to reference an ODBC DSN.
Here’s a code example, that references and ODBC DSN:
conn = pyodbc.connect('DSN=mynewdsn;UID=user;PWD=password')
Learn more about SQL Server Data Access from Python – Enroll to our Course!
Enroll to our online course “Working with Python on Windows and SQL Server Databases” with an exclusive discount, and get started with Python data access programming for SQL Server databases, fast and easy!
(Lifetime Access, Q&A, Certificate of Completion, downloadable resources and more!) Enroll from $12.99
Troubleshooting the Issue
The error message we are examining in this post, indicates that we are trying to connect to SQL Server, using the second method, that is referencing an ODBC DSN and the process fails.
In this case, we can further troubleshoot the issue, by performing the below checks.
Check 1 – Verify that the DSN Name is Valid
Check in ODBC Data Source Administrator that the DSN exists with the exact same name you are referencing it in your Python code.
Additionally, you must check that the DSN indeed works.
For checking the DSN:
- 64-bit ODBC Data Source Administrator if you are using a 64-bit version of Windows:
C:WINDOWSSystem32odbcad32.exe
- 32-bit ODBC Data Source Administrator if you are using a 32-bit version of Windows:
C:WINDOWSSysWOW64odbcad32.exe
Check 2 – Verify that the DSN is Correctly Referenced in Python Code
If the DSN uses a username/password, you need to also specify it in your Python code as per below example:
conn = pyodbc.connect('DSN=dsn_name;UID=user;PWD=password')
If the DSN uses a trusted connection, you need to also specify it in your Python code as per below example:
conn = pyodbc.connect('DSN= dsn_name;Trusted_Connection=yes;')
If None of the Above Helps
If none of the above helps, then you may consider instead of using a DSN, to define the full connection in your Python code (see first way above).
Featured Online Courses:
- Working with Python on Windows and SQL Server Databases
- SQL Server 2022: What’s New – New and Enhanced Features
- Introduction to Azure Database for MySQL
- Boost SQL Server Database Performance with In-Memory OLTP
- Introduction to Azure SQL Database for Beginners
- Essential SQL Server Administration Tips
- SQL Server Fundamentals – SQL Database for Beginners
- Essential SQL Server Development Tips for SQL Developers
- Introduction to Computer Programming for Beginners
- .NET Programming for Beginners – Windows Forms with C#
- SQL Server 2019: What’s New – New and Enhanced Features
- Entity Framework: Getting Started – Complete Beginners Guide
- Data Management for Beginners – Main Principles
- A Guide on How to Start and Monetize a Successful Blog
Read Also:
- Python Data Access Fundamentals
- How to Connect to SQL Server Databases from a Python Program
- What is Abstraction in Object Oriented Programming?
- How to Run the SQL Server BULK INSERT Command from Within a Python Program
- Useful Python Programming Tips
- Main Data Structures in Python
- IndentationError: expected an indented block in Python – How to Resolve it
- Working with Python on Windows and SQL Server Databases (Course Preview)
- How to Write to a Text File from a C++ Program
- How to Establish a Simple Connection from a C# Program to SQL Server
- The timeout period elapsed prior to obtaining a connection from the pool
- Closing a C# Application (including hidden forms)
- Changing the startup form in a C# project
- Using the C# SqlParameter Object for Writing More Secure Code
- Cannot implicitly convert type ‘string’ to ‘System.Windows.Forms.DataGridViewTextBoxColumn
- Missing parentheses in call to ‘print’. did you mean print(…) – How to Resolve in Python
Check our online courses!
Check our eBooks!
Subscribe to our YouTube channel!
Subscribe to our newsletter and stay up to date!
Rate this article: 
(1 votes, average: 5.00 out of 5)
Loading…
Reference: SQLNetHub.com (https://www.sqlnethub.com)
© SQLNetHub
Artemakis Artemiou is a Senior SQL Server Architect, Author, a 9 Times Microsoft Data Platform MVP (2009-2018). He has over 20 years of experience in the IT industry in various roles. Artemakis is the founder of SQLNetHub and {essentialDevTips.com}. Artemakis is the creator of the well-known software tools Snippets Generator and DBA Security Advisor. Also, he is the author of many eBooks on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). Moreover, Artemakis teaches on Udemy, you can check his courses here.
Views: 12,516