function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
sandra_AB34sandra_AB34 

Using Apex Data Loader command line to connect to external db

Hi,
 
I want to use the Apex Data Loader ( version 9.0 ) command line interface to get information from a SQL server 2000 db and import it into salesforce.  So far I've managed to successfully import from a csv using the command line ( and the process-conf.xml ).  But to connect to my SQL server database I need to know which driver to use ( I'm not a java programmer ).  I've looked about and the driver seems to be
 com.sun.sql.jdbc.sqlserver.SQLServerDriver
 
But when I use this I get the error 
 
ERROR com.salesforce.lexiloader.dao.database.DatabaseContext  - Database error encountered during connecting for database configuration: queryNewCustomers.  Sql error: Cannot load JDBC
driver class 'com.sun.sql.jdbc.sqlserver.SQLServerDriver'.
org.apache.commons.dbcp.SQLNestedException: Cannot load JDBC driver class 'com.sun.sql.jdbc.sqlserver.SQLServerDriver'
 at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:766)
 at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:540)
 at com.salesforce.lexiloader.dao.database.DatabaseContext.initConnection(DatabaseContext.java:89)
 at com.salesforce.lexiloader.dao.database.DatabaseContext.checkConnection(DatabaseContext.java:81)
 at com.salesforce.lexiloader.dao.database.DatabaseReader.checkConnection(DatabaseReader.java:231)
 at com.salesforce.lexiloader.process.ProcessRunner.run(ProcessRunner.java:121)
 at com.salesforce.lexiloader.process.ProcessRunner.main(ProcessRunner.java:228)
 
Is there something I'm missing - a driver, installation, location of class file etc?  I
downloaded the Microsoft SQL Server 2005 JDBC Driver 1.1, a Type 4 JDBC driver that provides database connectivity through the standard JDBC application program interfaces (APIs) available in J2EE (Java2 Enterprise Edition), but don't know if I have to do something further to use this??
 
Any help would be great!
 
Sandra
 
 
Rick.BanisterRick.Banister
Salesforce.com did not intend for end users to be using the command line interface, according to what we were told. It's a tool that their professional services teams use to bring up new customers. However, you may not get the level of support for the command line interface that you'd expect of a full product. There are a multitude of products on the AppExchange that will do this, including ours, and, depending on your buget and complexity of need, may be better suited to your needs.
sandra_AB34sandra_AB34
Thanks for that Rick.  Your software looks great, but its more than we really need at the moment.  I was told by salesforce that we could use the Apex Data loader to perform automatic imports / exports with our database so its worrying to hear that this may not be the case.  Anyone else out there used it to communicate with a SQL server database?
Rick.BanisterRick.Banister
Not sure what you mean by "more than you need". Relational Junction is EXACTLY what you need. We find continually that companies who buy another product to do one limited function end up buying several different products, one for a reporting database, one for an integration tool, one for data cleansing. Relational Junction is not only more functional than every other solution, but is less expensive than buying several products. To do what you need, it is less expensive than any commercial product, in fact. The only other product out there that is less expensive than Relational Junction drops tables when it replicates to the database, which doesn't do you any good.

All of the vendor products are on the AppExchange under Integration. Look for Certified products, which have been tested by Salesforce.com, and look for products that have been around for awhile. It takes a long product life to work the kinks out of a very complex application in order to make it reliable and easy to use.
sandra_AB34sandra_AB34

It looks like I've got the Apex Data loader working with our SQL Server database, so this will do fine for us in the meantime.  I can now schedule data import / export when needed.  If we need anything futher, we can look at your product, but for us its pricey!

 

Thanks
iSpaceiSpace

Hi Sandra,

Could you let me know how i can obtain Apex Data Loader9.0. I have a similar requirement and may be you can also mention the steps on how to connect to the external db?

Thanks,

Abhilash
sandra_AB34sandra_AB34
You can download the Apex Data Loader by clicking on Setup -> Data Management -> Apex Data Loader
 
As far as instructions for integration with an external DB goes, there's quite a bit of information in the Apex Data Loader user guide:-
 
 
plus examples of using the command line in the Samples folder of the data loader.
 
Good luck!
eewaldeewald
What driver name did you end up using?  I'm having a similar problem
sandra_AB34sandra_AB34
In the database-conf.xml file, I used the following ( with my db details of course!).
 
<bean id="dbDataSource"
      class="org.apache.commons.dbcp.BasicDataSource"
      destroy-method="close">
    <property name="driverClassName" value="com.microsoft.jdbc.sqlserver.SQLServerDriver"/>
    <property name="url" value="jdbc:microsoft:sqlserver://YourAddress;databaseName=YourDatabaseName;selectMethod=cursor;"/>
    <property name="username" value="yourloginname"/>
    <property name="password" value="yourpassword"/>
</bean>
 
I also had to install the Microsoft JDBC sql server driver :-
 
Plus you have to make sure that the classpath knows where the driver files are.  If you're using the batch command line then you can do that using the -classpath parameter e.g
 
java.exe -classpath ..\DataLoader.jar;"c:\program files\Microsoft SQL Server 2000 Driver for JDBC\lib\msbase.jar";"c:\program files\Microsoft SQL Server 2000 Driver for JDBC\lib\msutil.jar";"c:\program files\Microsoft SQL Server 2000 Driver for JDBC\lib\mssqlserver.jar"
etc 
 
Hope it works ok
 
Sandra
DaveIngramDaveIngram
Hi all,
 
I was able to get this to work and its importing into my database.

however when it tries to send a field which is blank (null) the whole process crashes,
 
any ideas?
 
thanks

Dave
shan876shan876

Use jTDS driver instead of JDBC.. faster and easier...

All you need to do is place the .jar file into your folder and you are set....

http://jtds.sourceforge.net/

 

your lines will look like this:

    <property name="driverClassName" value="net.sourceforge.jtds.jdbc.Driver"/>
    <property name="url" value="jdbc:jtds:sqlserver://localhost;databaseName=mitest"/>

 

Hope this helps

 

 
shan876shan876

Hi:

   I saw this post, just wanted to ask you, how did you schedule jobs using the command prompt in sql server...

Apparently this does not work in sql server but works on command prompt

 

C:\salesforce.com\Apex\bin\process "C:\salesforce.com\Apex\test" databasewsExtract

 

Thanks

Shan

NiloveNilove

Hi,

 

Sandra I am facing the same problem since couple of days and am not able to resolve it. Your post has given some hope that I could be able to solve it with your help.

 

I have also posted this into same forum

 

Please help me out. here is the description

 

----------------------------------------------------------------------------------------------------------

I am trying to Load data from Salesforce into SQL Server 2005 using command line. Here are my settings for SQL Server in database-conf file

 

----------------------------------

 

        <property name="driverClassName" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
        <property name="url" value="jdbc:sqlserver://MyServerName:1433:databasename=MyDatabase;"/>

 

----------------------------------

I have installed MSSQL Server jdbc2.0 on my machine at this location

 

"C:\Program Files\Microsoft SQL Server 2005 JDBC Driver\sqljdbc_2.0\enu"

 

 

 

When I run the integration it gives me following error.

 

**************************************

 

2438 [AccountExtractDB] ERROR com.salesforce.lexiloader.dao.database.DatabaseCon
text  - Database error encountered during connecting for database configuration:
 DBAccountInsert.  Sql error: Cannot load JDBC driver class 'com.microsoft.sqlse
rver.jdbc.SQLServerDriver'.
org.apache.commons.dbcp.SQLNestedException: Cannot load JDBC driver class 'com.m
icrosoft.sqlserver.jdbc.SQLServerDriver'
        at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSou
rce.java:766)
        at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource
.java:540)
        at com.salesforce.lexiloader.dao.database.DatabaseContext.initConnection
(DatabaseContext.java:89)
        at com.salesforce.lexiloader.dao.database.DatabaseContext.checkConnectio
n(DatabaseContext.java:81)
        at com.salesforce.lexiloader.dao.database.DatabaseWriter.checkConnection
(DatabaseWriter.java:92)
        at com.salesforce.lexiloader.process.ProcessRunner.run(ProcessRunner.jav
a:121)
        at com.salesforce.lexiloader.process.ProcessRunner.main(ProcessRunner.ja
va:228)
Caused by: java.lang.ClassNotFoundException: com.microsoft.sqlserver.jdbc.SQLSer
verDriver
        at java.net.URLClassLoader$1.run(URLClassLoader.java:200)
        at java.security.AccessController.doPrivileged(Native Method)
        at java.net.URLClassLoader.findClass(URLClassLoader.java:188)
        at java.lang.ClassLoader.loadClass(ClassLoader.java:306)
        at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:268)
        at java.lang.ClassLoader.loadClass(ClassLoader.java:251)
        at java.lang.ClassLoader.loadClassInternal(ClassLoader.java:319)
        at java.lang.Class.forName0(Native Method)
        at java.lang.Class.forName(Class.java:164)
        at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSou
rce.java:760)
        ... 6 more
2485 [AccountExtractDB] FATAL com.salesforce.lexiloader.process.ProcessRunner  -
 Database error encountered during connecting for database configuration: DBAcco
untInsert.  Sql error: Cannot load JDBC driver class 'com.microsoft.sqlserver.jd
bc.SQLServerDriver'.
Exception in thread "main" java.lang.RuntimeException: com.salesforce.lexiloader
.exception.DataAccessObjectInitializationException: Database error encountered d
uring connecting for database configuration: DBAccountInsert.  Sql error: Cannot
 load JDBC driver class 'com.microsoft.sqlserver.jdbc.SQLServerDriver'.
        at com.salesforce.lexiloader.process.ProcessRunner.run(ProcessRunner.jav
a:154)
        at com.salesforce.lexiloader.process.ProcessRunner.main(ProcessRunner.ja
va:228)
Caused by: com.salesforce.lexiloader.exception.DataAccessObjectInitializationExc
eption: Database error encountered during connecting for database configuration:
 DBAccountInsert.  Sql error: Cannot load JDBC driver class 'com.microsoft.sqlse
rver.jdbc.SQLServerDriver'.
        at com.salesforce.lexiloader.dao.database.DatabaseContext.initConnection
(DatabaseContext.java:97)
        at com.salesforce.lexiloader.dao.database.DatabaseContext.checkConnectio
n(DatabaseContext.java:81)
        at com.salesforce.lexiloader.dao.database.DatabaseWriter.checkConnection
(DatabaseWriter.java:92)
        at com.salesforce.lexiloader.process.ProcessRunner.run(ProcessRunner.jav
a:121)
        ... 1 more
Caused by: org.apache.commons.dbcp.SQLNestedException: Cannot load JDBC driver c
lass 'com.microsoft.sqlserver.jdbc.SQLServerDriver'
        at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSou
rce.java:766)
        at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource
.java:540)
        at com.salesforce.lexiloader.dao.database.DatabaseContext.initConnection
(DatabaseContext.java:89)
        ... 4 more
Caused by: java.lang.ClassNotFoundException: com.microsoft.sqlserver.jdbc.SQLSer
verDriver
        at java.net.URLClassLoader$1.run(URLClassLoader.java:200)
        at java.security.AccessController.doPrivileged(Native Method)
        at java.net.URLClassLoader.findClass(URLClassLoader.java:188)
        at java.lang.ClassLoader.loadClass(ClassLoader.java:306)
        at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:268)
        at java.lang.ClassLoader.loadClass(ClassLoader.java:251)
        at java.lang.ClassLoader.loadClassInternal(ClassLoader.java:319)
        at java.lang.Class.forName0(Native Method)
        at java.lang.Class.forName(Class.java:164)
        at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSou
rce.java:760)
        ... 6 more
Press any key to continue . . .

 

**************************************

 

Expecting a positive response from this forum :smileyhappy:

 

Nilove

 

----------------------------------------------------------------------------------------------------------

NiloveNilove

what is the location for copying the jar file?

 

Nilove

SuperfellSuperfell

Caused by: java.lang.ClassNotFoundException: com.microsoft.sqlserver.jdbc.SQLServerDriver

 

Your jdbc driver jar is not on the classpath, you either need to copy the jar into the classpath, or update the classpath you're using to start the app to include the jar. 
NiloveNilove

Thanks simon.

 

Thisis my config in process.bat file

 

..\_jvm\bin\java.exe -cp ..\DataLoader.jar -Dsalesforce.config.dir=%1 com.salesforce.lexiloader.process.ProcessRunner %PROCESS_OPTION%

 

I tried copying the sqljdbc.jar and sqljdbc4.jar in this bin folder and lib folder too. But its not picking up from there.

 

I am using jre 5.0 version.

 

can you suggest me how to set the classpath? I am using Windows XP. I am totally blank and have no idea what to do.

 

Appreciate your response.

 

Nilove

gazazellogazazello

Hi Sandra,

 

Thank you so much! You're a life saver!

 

There was a small issues with "selectMethod=cursor" in connection url. I had to remove that.