Write Database/ODBC connection
Hello,
I am having a problem with the Write Database operator; more specifically, the database connection between RapidMiner Studio and SQL Server. I have created a new connection using the below parameters (masking the actual names of the server and instance that I'm attempting to connect to). It seems that I can only get connected to the master database even though I'm putting a specific database within the master into the Database scheme box. I need to write the results of a model to a specific database in SQL but when I try to use the write database operator, only master database tables are shown as options. Am I missing something in the set up? SQL Server isn't housed on my machine so I cannot use the server name or IP address in the set up details.
Thank you,
Kelly
Best Answer
-
Hi @KellyM,
one last idea I have is to integrate the database name in the connection string.
This would mean you enter
<server>:<port>;instance=<instance>;databaseName=<schema>;integratedSecurity=true
in the parameter "host" of your MS SQL database connection and remove the values for Port and Database Scheme.
The resulting URL should look as follows:
jdbc:jtds:sqlserver://<server>:<port>;instance=<instance>;databaseName=<schema>;integratedSecurity=true
Best regards,
Edin
1
Answers
-
The first question is if you have the write permissions to do so to your specific table. When you enter all the info and set the schema, does the green check mark show up when it press Test?
1 -
Hi Thomas,
I do have the necessary permissions and when I test the connection, I get a green arrow that the connection is good.
0 -
Hi @KellyM,
within the Operator "Write Database" you can choose if you want to use the default database scheme you defined in your connections or if you want to use a different scheme. Just uncheck the Parameter "use default schema" and the Parameter "schema name" appears (see screenshot).
By the way, if you already know the name of the table you want to deal with, you can also just enter the name in the Combobox.
Best,
Edin
1 -
Hey @Edin_Klapic,
Thank you for the response. Sorry for not replying sooner. This doesn't work either. I have write permissions to the table I am trying to append my predictions to. However, when I follow your steps, I get the below error message that I do not have permissions to the master database, even though I've pointed the schema name to the database and the table name to the table I am trying to write to.
Within the parameters, I still have to define a connection and the only option I have is the database connection I set up before. So I'm assuming that this is still where the problem lies.
@Thomas_Ott as another follow-up to your question, no matter what I put in the database scheme box, I get a green check. I've actually put in a database name that doesn't exist and I still get a green check that the connection is good. Why is that? I am setting the host name to the SQL instance I am connecting to. Is there another step that I can take to get it to connect to the database I need from this step?
Thanks for the help.
0 -
@KellyM based on the error in your screenshot, it says you don't have permissions to create a table in the Master schema.0
-
Hi @Thomas_Ott,
Yes, that's correct. But the table is already there and that's where the issue lies. I am trying to append predictions from RapidMiner to an already existent table in SQL using the Write Database operator. I am trying to set up an ODBC connection to do this. However, after setting up a connection, all I'm seeing as options for table names are tables in the master database. My connection is supposed to be to a database and table within the master (which I've designated in the connection details). That is why Edin Klapic suggested turning off the default schema and manually entering in the schema name and table name. I have done this and am getting this error still. The problem isn't creating a table in the master database. The problem is that I can't seem to get past the master database connection to connect to a database and table within it.
0 -
Hi @KellyM,
one last idea I have is to integrate the database name in the connection string.
This would mean you enter
<server>:<port>;instance=<instance>;databaseName=<schema>;integratedSecurity=true
in the parameter "host" of your MS SQL database connection and remove the values for Port and Database Scheme.
The resulting URL should look as follows:
jdbc:jtds:sqlserver://<server>:<port>;instance=<instance>;databaseName=<schema>;integratedSecurity=true
Best regards,
Edin
1 -
2
Altair Employee