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

Find more posts tagged with

AI Studio

Databases

Accepted answers

Edin_Klapic

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

All comments

Thomas_Ott

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?

KellyM

Hi Thomas,

I do have the necessary permissions and when I test the connection, I get a green arrow that the connection is good.

Edin_Klapic

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

image.png

KellyM

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.

RM_CreateTableError.png

Thomas_Ott

@KellyM based on the error in your screenshot, it says you don't have permissions to create a table in the Master schema.

KellyM

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.

sgenzer

Hi @KellyM - are you able to share your rapidminer-studio.log file with us? If you don't want to post it publicly, you can send it to me via DM and I can take a look.

Scott

Edin_Klapic

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

KellyM

@Edin_Klapic @sgenzer

Edin and Scott, thank you both again for your help with this!

Kelly