如何解决MSBI依赖项错误无法加载类'com.microsoft.sqlserver.jdbc.SQLServerDriver'？

Question

我有基于maven的接口，我需要连接MSBI数据库并将数据提取到mule中。

我已经将所需的jar sqljdbc42.jar添加到构建路径中。

PFB是MSBI连接配置：

<poll doc:name="Poll">
                <schedulers:cron-scheduler expression="${msbi.poll.schedule.cdo}"/>
                <db:select config-ref="MSBI_Database_Configuration" streaming="true" fetchSize="1000" doc:name="MSBI Select Contact CDO data">
                    <db:parameterized-query><![CDATA[SELECT [Cstmr_Acct_Id] AS SAP_Account_ID1
      ,[Accnt_Nm] AS CRM_Account_Name1
      ,[Accnt_Type] AS APL_Account_Attributes___Account_Type1
      ,[Cstmr_Sgmnt] AS CRM_Customer_Segment1
      ,[Trnprttn_role] AS CRM_Transportation_Role1
      ,CASE WHEN [Accnt_Stts]='A' THEN 'Active' WHEN [Accnt_Stts]='I' THEN 'Inactive' ELSE NULL END AS CRM_Account_Status1
      ,[Rgn] AS Region1
      ,[Rgn_desc] AS Region_Text1
      ,[Clster] AS Cluster1
      ,[Clster_desc] AS Cluster_Text1
      ,[Dstrct] AS District1
      ,[Dstrct_desc] AS District_Text1
      ,[Cntry] AS Country1
      ,[Cntry_desc] AS Country_Text1
      ,[Brnch] AS Branch1
      ,[Brnch_desc] AS Branch_Text1
      ,[Trrtry] AS Territory1
      ,[Trrtry_desc] AS Territory_Desc1
      ,[New_BT_Cd] AS BT_Code1
      ,[Lgcy_BT_Cd] AS Legacy_BTCode1
      ,[Last_Update_Dt] AS LAST_UPDATE_Date1
  FROM [dbo].[vw_elqa_CRM_Accnt_Sales_Hierarchy]
  WHERE
(
  CAST(Last_Update_Dt AS date) >= CAST(GETDATE() AS date) OR 
  CAST(Last_Update_Dt AS date) >= CAST(#[server.systemProperties['mule.env']=='dev'?server.systemProperties['msbi.debug.csr.query.filterDate']:'2100-01-01'] AS date)
) AND Cstmr_Acct_Id IS NOT NULL]]></db:parameterized-query>
                </db:select>
            </poll>

当我运行该接口时，它被成功部署，但同时触发了以下错误

ERROR 2017-10-30 20:58:26,792 [nol-integration-v1.3-polling://MSBItoEloquaContactCDODataUpdate/541182371_Worker-1] org.mule.exception.DefaultSystemExceptionStrategy: 
********************************************************************************
Message               : org.mule.module.db.internal.domain.connection.ConnectionCreationException: Error trying to load driver: com.microsoft.sqlserver.jdbc.SQLServerDriver : Cannot load class 'com.microsoft.sqlserver.jdbc.SQLServerDriver' (java.sql.SQLException)
Element               : /MSBI_Database_Configuration @ app:bulk-integration.xml:26 (Generic Database Configuration)
--------------------------------------------------------------------------------
Root Exception stack trace:
java.sql.SQLException: Error trying to load driver: com.microsoft.sqlserver.jdbc.SQLServerDriver : Cannot load class 'com.microsoft.sqlserver.jdbc.SQLServerDriver'
    at org.enhydra.jdbc.standard.StandardDataSource.getConnection(StandardDataSource.java:184)
    at org.enhydra.jdbc.standard.StandardDataSource.getConnection(StandardDataSource.java:144)
    at org.mule.module.db.internal.domain.connection.SimpleConnectionFactory.doCreateConnection(SimpleConnectionFactory.java:30)
    at org.mule.module.db.internal.domain.connection.AbstractConnectionFactory.create(AbstractConnectionFactory.java:23)
    at org.mule.module.db.internal.domain.connection.TransactionalDbConnectionFactory.createDataSourceConnection(TransactionalDbConnectionFactory.java:84)
    at org.mule.module.db.internal.domain.connection.TransactionalDbConnectionFactory.createConnection(TransactionalDbConnectionFactory.java:53)
    at org.mule.module.db.internal.processor.AbstractDbMessageProcessor.process(AbstractDbMessageProcessor.java:72)
    at org.mule.transport.polling.MessageProcessorPollingMessageReceiver$1.process(MessageProcessorPollingMessageReceiver.java:165)
    at org.mule.transport.polling.MessageProcessorPollingMessageReceiver$1.process(MessageProcessorPollingMessageReceiver.java:149)
    at org.mule.execution.ExecuteCallbackInterceptor.execute(ExecuteCallbackInterceptor.java:16)
    at org.mule.execution.CommitTransactionInterceptor.execute(CommitTransactionInterceptor.java:35)
    at org.mule.execution.CommitTransactionInterceptor.execute(CommitTransactionInterceptor.java:22)
    at org.mule.execution.HandleExceptionInterceptor.execute(HandleExceptionInterceptor.java:30)
    at org.mule.execution.HandleExceptionInterceptor.execute(HandleExceptionInterceptor.java:14)
    at org.mule.execution.BeginAndResolveTransactionInterceptor.execute(BeginAndResolveTransactionInterceptor.java:67)
    at org.mule.execution.ResolvePreviousTransactionInterceptor.execute(ResolvePreviousTransactionInterceptor.java:44)
    at org.mule.execution.SuspendXaTransactionInterceptor.execute(SuspendXaTransactionInterceptor.java:50)
    at org.mule.execution.ValidateTransactionalStateInterceptor.execute(ValidateTransactionalStateInterceptor.java:40)
    at org.mule.execution.IsolateCurrentTransactionInterceptor.execute(IsolateCurrentTransactionInterceptor.java:41)
    at org.mule.execution.ExternalTransactionInterceptor.execute(ExternalTransactionInterceptor.java:48)
    at org.mule.execution.RethrowExceptionInterceptor.execute(RethrowExceptionInterceptor.java:28)
    at org.mule.execution.RethrowExceptionInterceptor.execute(RethrowExceptionInterceptor.java:13)
    at org.mule.execution.TransactionalErrorHandlingExecutionTemplate.execute(TransactionalErrorHandlingExecutionTemplate.java:110)
    at org.mule.execution.TransactionalErrorHandlingExecutionTemplate.execute(TransactionalErrorHandlingExecutionTemplate.java:30)
    at org.mule.transport.polling.MessageProcessorPollingMessageReceiver.pollWith(MessageProcessorPollingMessageReceiver.java:148)
    at org.mule.transport.polling.MessageProcessorPollingMessageReceiver.poll(MessageProcessorPollingMessageReceiver.java:139)
    at org.mule.transport.AbstractPollingMessageReceiver.performPoll(AbstractPollingMessageReceiver.java:216)
    at org.mule.transport.PollingReceiverWorker.poll(PollingReceiverWorker.java:84)
    at org.mule.transport.PollingReceiverWorker.run(PollingReceiverWorker.java:48)
    at org.mule.modules.schedulers.cron.CronJob.execute(CronJob.java:33)
    at org.quartz.core.JobRunShell.run(JobRunShell.java:202)
    at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:573)

添加依赖项后出现以下错误

ERROR

********************************************************************************
Message               : Response code 500 mapped as failure.
Payload               : org.glassfish.grizzly.utils.BufferInputStream@cb5d5af
Payload Type          : org.mule.module.db.internal.result.resultset.ResultSetIterator
Element               : /MSBItoEloquaContactCDODataUpdate/input/0/0/EloquaLookupContactsCDOBulk/subprocessors/1/EloquaLookupFields/subprocessors/0/0/1/2 @ nol-integration-v1:bulk-integration.xml:92 (Eloqua Get CDO fields)
Element XML           : <http:request config-ref="Eloqua_Bulk_API" path="/customObjects/{customObjectId}/fields" method="GET" doc:name="Eloqua Get CDO fields">
                        <http:request-builder>
                        <http:uri-param paramName="customObjectId" value="#[flowVars.cdo.id]"></http:uri-param>
                        </http:request-builder>
                        </http:request>
--------------------------------------------------------------------------------
Root Exception stack trace:
org.mule.module.http.internal.request.ResponseValidatorException: Response code 500 mapped as failure.
    at org.mule.module.http.internal.request.SuccessStatusCodeValidator.validate(SuccessStatusCodeValidator.java:37)
    at org.mule.module.http.internal.request.DefaultHttpRequester.validateResponse(DefaultHttpRequester.java:413)
    at org.mule.module.http.internal.request.DefaultHttpRequester.innerProcess(DefaultHttpRequester.java:401)
    at org.mule.module.http.internal.request.DefaultHttpRequester.processBlocking(DefaultHttpRequester.java:221)
    at org.mule.processor.AbstractNonBlockingMessageProcessor.process(AbstractNonBlockingMessageProcessor.java:43)  

Answer 1

似乎在运行时找不到sqljdbc4.jar。

如果您使用的是Maven build(pom.xml)

将依赖项添加到pom.xml

<dependency>
    <groupId>com.microsoft.sqlserver</groupId>
    <artifactId>sqljdbc4</artifactId>
    <version>4.0</version>
</dependency>

注释:仅供参考-

如果您在pom.xml中引用的Maven repo中没有任何jar，则需要自己将其添加到本地存储库/公司存储库。

要添加到本地存储库的，

1.请检查您的驱动程序jar的版本是否正确。

2.执行以下命令，将其添加到本地Maven存储库

mvn install:install-file -Dfile=<jar_name>.jar -DgroupId=<group_id_of_jar> -DartifactId=<artifact_id_of_jar> -Dversion=<version_of_jar> -Dpackaging=jar

Answer 2

我正在使用Mulesoft，使用上面的答案和@Mahesh_Loya的评论，我能够让它工作。

1. 将依赖项添加到pom.xml，就在关闭</dependencies>标记之前：

com.microsoft.sqlserver sqljdbc4 4.0

• 将Clojars存储库添加到pom.xml，就在关闭</repositories>标记之前

Clojars http://clojars.org/repo/默认

保存pom.xml并重新运行Maven，一切都应该正常工作。快乐的时光。