0

Here is the message I got from SQL Server when I try to create new linked server I tried a lot but no luck please help

oPreProcessExecution(RunType runType, ExecutionMode& executionResult)

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "MISYS_SBM11".
OLE DB provider "MSDASQL" for linked server "MISYS_SBM11" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified". (.Net SqlClient Data Provider)

Server Name: 192.XX.X.XX\MISYSSBMSE
Error Number: 7303
Severity: 16
State: 1
Procedure: sp_testlinkedserver
Line Number: 1

Program Location:

at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Kin
  • 145
  • 1
  • 11
  • Are you trying to create a linked server to another SQL Server? The error message suggests you are using ODBC instead of OLE DB. It would be best to use the SQL Server Native Client OLE DB provider in that scenario. – Dan Guzman Apr 16 '16 at 16:31
  • @DanGuzman The other Database is MySQL, I used `Microsoft OLE DB Providder for ODBC Driver` – Kin Apr 16 '16 at 16:32
  • you can't use a SQL Server ODBC driver to connect to MySQL. You'll need to use a MySQL ODBC driver. The linked server will then use the OLEDB Provider for ODBC drivers (MSDASQL) to call the MySQL ODBC driver. – Dan Guzman Apr 16 '16 at 16:40
  • @DanGuzman hummm but i cant see it listed in MSSQL i created ODBC connection using MySQL ODBC driver but its not listed in providers list in MSSQL – Kin Apr 16 '16 at 16:56
  • Only OLE DB providers will be listed. Like I mentioned, The MSDASQL OLE DB provider is needed to to use an ODBC driver. Check out http://www.sqlservercentral.com/Forums/Topic340912-146-1.aspx to see of that helps. – Dan Guzman Apr 16 '16 at 17:11
  • @DanGuzman Thanks but that was i was using if you see my question it tells you that i am using what you suggested to use but i still can get it work – Kin Apr 16 '16 at 21:13

0 Answers0