Write Database/ODBC connection

KellyMKellyM Member Posts: 21 Maven
edited December 2018 in Help

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.

 

RM_ODBC.png

 

Thank you,

 

Kelly

Tagged:

Best Answer

  • Edin_KlapicEdin_Klapic Moderator, Employee, RMResearcher, Member Posts: 299 RM Data Scientist
    Solution Accepted

    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

Answers

  • Thomas_OttThomas_Ott RapidMiner Certified Analyst, RapidMiner Certified Expert, Member Posts: 1,761 Unicorn

    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?

  • KellyMKellyM Member Posts: 21 Maven

    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_KlapicEdin_Klapic Moderator, Employee, RMResearcher, Member Posts: 299 RM Data Scientist

    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

     

     

     

     

  • KellyMKellyM Member Posts: 21 Maven

    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.

     

    RM_CreateTableError.png

     

    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.

  • Thomas_OttThomas_Ott RapidMiner Certified Analyst, RapidMiner Certified Expert, Member Posts: 1,761 Unicorn
    @KellyM based on the error in your screenshot, it says you don't have permissions to create a table in the Master schema.
  • KellyMKellyM Member Posts: 21 Maven

    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. 

  • sgenzersgenzer Administrator, Moderator, Employee, RapidMiner Certified Analyst, Community Manager, Member, University Professor, PM Moderator Posts: 2,959 Community Manager

    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

     

  • KellyMKellyM Member Posts: 21 Maven

    @Edin_Klapic @sgenzer 

     

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

     

    Kelly

Sign In or Register to comment.