Hi,
Is there any detailed documentation on how to connect KNIME to;
(a) Microsoft MS SQL SERVER (specifically the 2012 edition) (Specific example of connecting to Adventureworks would be of great assistance).
(b) Teradata (V14, but V12 or V13 should be nearly identical).
Thanks in advance.
GJ.
hi
I have got similar question in relation to SQL Server 2012 (on windows 2007) ...see
I have problem connecting to SQL Server 2012 , I have put the Jar and DLL files in the right folder C:\Program Files (x86)\KNIME_2.10.3\jre\bin\ext\. I am using Kmine 2.10.3 and JDBC4
I keep getting the following error when executing the Database Reader node
ERROR Database Reader Execute failed: com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user 'myUserName'.
DatabaseURL = jdbc:sqlserver://MyServer:1433;databaseName=DbName;
User Name = myUserName
Password = myPassWord
SQL Statement = SELECT TOP 5 * FROM [MY_CHANNEL_DIM]
But when using the Java Snippet node, I can prove it that I can connect to the database just fine
using the following code
// system variables
public class JSnippet extends AbstractJSnippet {
// Your custom variables:
private static final NodeLogger logger = NodeLogger.getLogger(JSnippet.class);
// expression start
public void snippet() throws TypeException, ColumnException, Abort {
// Enter your code here:
try
{
String url = "jdbc:sqlserver://MyServer:1433;databaseName=DbBane;Username=myUserName;Password=myPassWord";
logger.error( url );
Connection conn = DriverManager.getConnection(url);
logger.error("connection created");
Statement st=conn.createStatement();
String sql="SELECT TOP 5 * FROM MY_CHANNEL_DIM";
ResultSet rs=st.executeQuery(sql);
while(rs.next()) {
logger.error("Name1: " + rs.getString(1) );
logger.error("Name2: " + rs.getString(2) );
logger.error("Name3: " + rs.getString(3) );
logger.error("Name4: " + rs.getString(4) );
logger.error("Name5: " + rs.getString(5) );
}
if(st!=null)
st.close();
if(conn!=null)
conn.close();
}
catch(SQLException sqle)
{
logger.error("Sql Exception "+sqle);
}
I cannot get any further meaningful error message fom the log, what seems to be the problem with the Database Reader node?
Thanks
Hello,
the Database Documentation page describes how to connect to a database in KNIME. The driver must support at least JDBC 4.0. If the JDBC driver requires additional files such as dlls you need to make sure that they use the same architecture as your KNIME installation e.g. 64/32 bit.
For MS SQL you can use the Microsoft JDBC Driver 4.0 for SQL Server which should also work for MS SQL Server 2012.
Bye,
Tobias
The description on the mentioned KNIME, "Database Documentation" is fairly scant on detail.
When comparing to other vendors documentation on making connections to various databases, there is substantially more detail, which most commonly includes step by step walk throughs including screen shots. For the most part, detailed documentation is provided to connect to Oracle, Microsoft SQL Server, and Teradata.
We are conducting evaluations various analytics products, windows based, and as a minimun, native connection must be easily configuraable for Oracle, MS SQL Server, and Teradata. We don't have available time to spend days "working it out for ouselves". Additionally, we don't have a DBA, .NET or java developer on our team. Most of us, have done undergraduate and in some case masters/Ph.D's in disciplines like Business/Finance/Accounting/Actuarial studies/Business Law at UNI. Heavy on analysis, what-if solutions, but we admit up front, quite thin on things like DB configuration, networking etc. Looking at another product we are evaluating, there is a 47 page section in the documentation PDF, for connecting to Teradata, covers typics like, what's supported, data types, data conversions, performance condsiderations. The product has similar details sections for other supported databases.
Compared to other products we are evaluating, the KNIME documentation falls well short of the mark.
John,
I guess the half-hour video tutorial might be more useful to you (not sure how detailed the material on initial setup is, though):
http://www.youtube.com/watch?v=MHblrs6sPpE
Slightly less efficient to go through than written documentation, but much more efficient for the KNIME team to produce. Note that KNIME is extremely versatile and feature-rich, because that's where their man-hours go to for the most part. You'll notice the difference when it comes to your functional assessment. They also do a stellar job when it comes to individually supporting commercial customers, which is usually where IT's biggest names with the biggest call centres fall short (unless you're *really* big yourself).
Once a DB connection is set up in KNIME it's very easy to maintain and adapt, but admittedly you'll have to do some additional research up front if you're not wholly into "the DB stuff" you could be sourcing from. Note that KNIME also allows you to compose efficient, re-usable queries with minimal to no SQL skills, which is what will surely be as useful to you as it is to me. ;-)
Note: I'm a professional techie but not a DBA / computer scientist either, and more and more into the biz analysis part (well over a decade now). I'm very picky with my analytical functionality requirements, which is where I have yet to see another code-less environment equal KNIME's versatility (at the unbeatble "free" price tag for sole analysts).
Regards,
E
Thanks for the input, I'll watch the video.
We are a BIG COMPANY, 65K employees, presence in 15 countries, ( and that's not including little one like Fiji) we are on track this year to break the $10Billion in NPAT, so I think by any usable measure, that qualifies us a a pretty big company.
We live in SQL land, and can pretty much make it sing and dance to what ever tune we want. Some of the scripts that my team has delivered, are 10K + lines long. Our primary database is Teradata, with an installation that rates in the list of largest in the world, and MS SQL Server.
But, the downside of being a big company, is, that we are by and large, isolated from the configuration side of things. A project runs to stand up 'X' capability. And resources are drawn from what ever diverse teams are necessary, to make that happen. So, normally, we are delivered, working functional software, on our notebooks, pre configured, running. That has the advantage of 'experts' doing the bits that need to happen along the way, with the downside of taking longer, and costing more, and the added downside of, we don't get to see how the config is done, and most usually, very little if any input, into how it's done. But, there is an advantage as well, for pretty much everything, we have a 'local' expert, who is our first contact point, in the event of an issue, and they'll have received training, in the capability they are supporting.
That puts us at a decided disadvantage, in situations like our current one, where we need to verify some software, before we proceed to engaging with IT engineering, to have the commercial version certified to be included in our SOE.
Hi John,
Understood, thanks for the backgrounder. There are indeed various catches in your situation which I wouldn't have expected as such, and which explain your needs perfectly. Getting a costly enterprise-grade solution is therefore not an issue for you, the only question then being whether it'll live up to its promise or not - which includes customer support.
Let me put it this way: There are really big and really costly enterprise software solutions out there which will cost you an arm and a leg. And as a big enterprise customer you'll receive the undivided attention of at least the sales & customer development staff. Inhowfar you can rely on this undivided attention on the support end is an open question - I've seen some really poor support from some really big names given to 25K+ employee organisations. Maybe because they didn't quite spend enough dough on the big vendors' solutions, maybe because there's something intrinsically wrong in the "no-one ever got fired for choosing XXX" adage, maybe because of something else. I remain skeptical, no least because I have seen the most annoying kind of bugs in professional software which never got fixed, provided at a fairly steep annual price tag.
Having said all that, getting a JDBC connection string composed isn't too hard for a team fluent in SQL, it can be achieved with a few Google queries and a bit of experimentation quite easily. I can help when it comes to translating e.g. MS Excel settings ino SQL Server connection strings, just post here and we'll get it sorted. I have no personal experience with Teradata, but this can surely be sorted out just as easily. Shoot me a PM for undivided attention, I'll make sure notifications work for me.
Regards,
E
Major step forward today with 2 issues resolved.
Issue #1: IT engineering, had placed the server, with the data we are to work on, on the TEST instead of the release VLAN, hence we could not access it.
Issue #2: The install package placed the trusted authentication DLL into the KNIME install directory under "program files", this DLL need to be in a directory in the windows path.
These are now resolved, and we can connect to the database.
Excellent, good luck with your tests then.
Regards,
E
hi guy
| I want to connect to a database but the software displays an error message: Execute failed: com.microsoft.sqlserver.jdbc.SQLServerException: El número de puerto 1433/DB_creditos_Cobros no es válido. What can i do to solve this error? |
Hello,
the error message seems to indicate a problem with the database port. Have you checked the jdbc url that it follows the MS SQL Server specific pattern as described here and that it contains the right settings for your database?
Bye,
Tobias
Hello guys
After some pain I now successfully connect to an sqlserver2014.
A big help was the guideline from Troy on another post:
Hi,
I know this thread is living on but I just wanted to chime in with a successful set up of using KNIME with Windows integrated Active Directory log in.
then close and reopen KNIME
Database driver: com.microsoft.sqlserver.jdbc.SQLserverDriver (this is the new additional driver that we just installed and now availableamong the driver selection)
Database URL: jdbc:sqlserver://###.##.###.#:1433;databaseName=MYDATABASENAME;
then the Authentication username and pw (in my case username was the same as the MYDATABASE name
- When the connector is set you can connect the" Database Table Selector" Node where in th econfic you can now select the table and
already put som esql query in..
- Finnaly you can for example use the "Database Connection Table Reader" Node to start work in Knime
Hope this helps someone else out there stuck in a Windows and SQL Server environment :-),
Adaptagis with a special thanks to Troy
Hello Adaptagis,
this is great. Thanks a lot for the detailed documentation.
Bye,
Tobias
I followed adaptagis instructions and this worked for me.
The only difference is I didn't find the file ntlmauth.dll. So I added sqljdbc_xa.dll instead.
I am not sure if that did it or if I could have just added the one file. But either way, I can now connect.
Much thanks adaptagis for the solution and Troy for the alternative connections code.