Problems creating sqlSeries RODBC tables

Question

I am unable to create the table using RODBC sqlSave (or, more precisely, writing data to the created table).

This is different from the existing sqlSave question / answer as

• the problems they encountered were different, I can create tables, while they could not and
• I have already misused their solutions, such as closing and reopening a connection before starting sqlSave, as well
• The error message is different, but the only exception is the message, which differs in two ways.

I am using MS SQL Server 2008 and 64-bit R on Windows RDP.

I have a simple data frame containing only 1 column consisting of 3, 4 or 5 digit integers.

> head(df) colname 1 564 2 4336 3 24810 4 26206 5 26433 6 26553 

When I try to use sqlSave, no data is written to the table. In addition, the error message makes it sound as if the table could not be created, although the table is actually created using 0 rows.

Based on the assumption I found, I tried to close and reopen the RODBC connection right before starting sqlSave. Despite the fact that I use append = TRUE , I tried to reset the table before doing this, but nothing affected.

 > sqlSave(db3, df, table = "[Jason].[dbo].[df]", append = TRUE, rownames = FALSE) Error in sqlSave(db3, df, table = "[Jason].[dbo].[df]", : 42S01 2714 [Microsoft][ODBC SQL Server Driver][SQL Server]There is already an object named 'df' in the database. [RODBC] ERROR: Could not SQLExecDirect 'CREATE TABLE [Jason].[dbo].[df] ("df" int)' 

I also tried using sqlUpdate () in the table after creating it. It doesn’t matter if I create it in R or SQL Server Management Studio, I get a table not found on channel error message

Finally, note that I also tried this without append = TRUE and when creating a new table, as well as with and without the rownames option.

Mr.Flick from Freenode #R asked me to check if I can read in an empty table using sqlQuery, and indeed I can.

Update

I came a little closer to the following steps:

• I created an ODBC connection that is directly connected to my database in SQL Server, and not just the default database (Master), and then indicating the table path in the table = or tablename = statements
• Created a table in SQL Server Management Studio as follows

GO

CREATE TABLE [dbo].[testing123]( [Person_DIMKey] [int] NULL ) ON [PRIMARY]

GO

• In R, I used sqlUpdate with my new ODBC connection and without parentheses around the tab name

• Now sqlUpdate () sees the table, however it complains that it needs a unique column

• Indicating that the only column in the table is a unique column with index = colname , an error occurs saying the column does not exist

• I reset and recreate the table with the primary key,

GO

CREATE TABLE [dbo].[jive_BNR_Person_DIMKey]( [jive_BNR_Person_DIMKey] [int] NOT NULL PRIMARY KEY ) ON [PRIMARY]

GO

which generated both the primary key and the index (in accordance with the GUI SQL Sever Management Studio interface) with the name PK__jive_BNR__2754EC2E30F848ED

• I indicated this index / key as a unique column in sqlUpdate (), but I get the following error:

Error in sqlUpdate(db4, jive_BNR_Person_DIMKey, tablename = "jive_BNR_Person_DIMKey", : index column(s) PK__jive_BNR__2754EC2E30F848ED not in database table

For the record, I provided the correct column name (not "colname") for the index; thanks MrFlick for requesting clarifications.

In addition, these steps are numbered 1 through 7 in my post, but StackOverflow resets the list numbering several times when it is displayed. If someone can help me clear this aspect of this post, I would appreciate it.

Answer 1

After reading the RODBC widget again, here is the simplest solution that worked:

 sqlDrop(db, "df", errors = FALSE) sqlSave(db, df) 

Done.

After experimenting with this a lot more for several days, it seems that the problems arose due to the use of additional options, especially table = or, equivalently, tablename = . These must be valid parameters, but somehow they manage to create problems with my specific version of RStudio ((Windows, 64-bit version, desktop version, current build), R (Windows, 64 bit, v3) and / or MS SQL Server 2008

sqlSave(db, df) will also work without sqlDrop(db, "df") if the table never existed, but as a best practice I write try(sqlDrop(db, "df", errors = FALSE), silent = TRUE) before all sqlSave in my code.

Answer 2

After hours of working on this, I was finally able to get sqlSave to work by specifying the name of the table — deep breathing, where to start. Here is a list of things I did to get this working:

• Open the 32-bit ODBC administrator and create a user DSN and configure it for your specific database. In my case, I create a global temp table, so I am bound to tempdb. Use this connection name in odbcConnection(Name) . Here is my code myconn2 <- odbcConnect("SYSTEMDB") .
• Then I defined my data types with the following code: columnTypes <- list(Record = "VARCHAR(10)", Case_Number = "VARCHAR(15)", Claim_Type = "VARCHAR(15)", Block_Date = "datetime", Claim_Processed_Date = "datetime", Status ="VARCHAR(100)") .
• Then I updated the data frame class types using as.character and as.Date to match the data types listed above.
• I already created the table since I worked on it for several hours, so I had to abandon the table using sqlDrop(myconn2, "##R_Claims_Data") .
• Then I ran: sqlSave(myconn2, MainClmDF2, tablename = "##R_Claims_Data", verbose=TRUE, rownames= FALSE, varTypes=columnTypes)

Then my head fell because it worked! I really hope this helps someone move forward. Here are the links that helped me get to this:

Table not found

sqlSave in R

Rodbc

Answer 3

In addition to some of the answers posted earlier, here is my workaround. NOTE. I use this as part of a small ETL process, and the destination table in the database is discarded and recreated every time.

Basically, you want to name your framework your destination table:

 RodbcTest <- read.xlsx('test.xlsx', sheet = 4, startRow = 1, colNames = TRUE, skipEmptyRows = TRUE) 

Then make sure your connection string contains the target database (not just the server):

 conn <- odbcDriverConnect(paste("DRIVER={SQL Server};Server=localhost\\sqlexpress;Database=Charter;Trusted_Connection=TRUE")) 

after that I run a simple sqlQuery, which conditionally deletes the table if it exists:

 sqlQuery(conn, "IF OBJECT_ID('Charter.dbo.RodbcTest') IS NOT NULL DROP TABLE Charter.dbo.RodbcTest;") 

Then, finally, run sqlSave without the tablename parameter, which will create the table and populate it with your data framework:

 sqlSave(conn, RodbcTest, safer = FALSE, fast = TRUE) 

Answer 4

We had the same problem, which after a little testing, we solved simply without using square brackets in the link to the diagram and table.

i.e. instead of writing

 table = "[Jason].[dbo].[df]" 

write instead

 table = "Jason.dbo.df" 

Evaluate that this has long preceded the first question, but only for those who subsequently deal with this problem, so we decided this. For reference, we found this by writing a simple 1 data element to a new table, which, when checked in SQL, contained square brackets in the table name.

Answer 5

Here are some rules of thumb:

• If something does not work, manually specify the column types as shown by @ d84_n1nj4 .

---

 columnTypes <- list(Record = "VARCHAR(10)", Case_Number = "VARCHAR(15)", Claim_Type = "VARCHAR(15)", Block_Date = "datetime", Claim_Processed_Date = "datetime", Status ="VARCHAR(100)") sqlSave(myconn2, MainClmDF2, tablename = "##R_Claims_Data", verbose=TRUE, rownames= FALSE, varTypes=columnTypes) 

---

2. If # 1 does not work, keep specifying the columns, but specify them as VARCHAR(255) . Think of it as a temporary or staging table and move the data using sqlQuery next step, as suggested by @ danas.zuokas . This should work, but even if it isn’t, it brings you closer to the metal and allows you to better debug the problem with SQL Server Profiler if you need it. <- And yes, if you still have a problem, probably due to a parsing error or type conversion.

   ---

 columnTypes <- list(Record = "VARCHAR(255)", Case_Number = "VARCHAR(255)", Claim_Type = "VARCHAR(255)", Block_Date = "VARCHAR(255)", Claim_Processed_Date = "VARCHAR(255)", Status ="VARCHAR(255)") sqlSave(myconn2, MainClmDF2, tablename = "##R_Claims_Data", verbose=TRUE, rownames= FALSE, varTypes=columnTypes) sqlQuery(channel, 'insert into real_table select * from R_Claims_Data') 

---

3. Due to the implementation of RODBC and not due to any inherent restriction in T-SQL, the R logical type (ie [TRUE, FALSE] ) will not be converted to the T-SQL BIT (i.e. [1, 0]), so do not try to do this. Either convert the logical type in [1, 0] to the R-layer, or transfer it to the SQL level as VARCHAR(5) and convert it to BIT at the SQL level.

Answer 6

I ran into the same problem - the way I found this is to create an empty table using the regular CREATE TABLE SQL syntax and then add to it via sqlSave . For some reason, when I tried it my own way, I could see the table name in the MSSQL database - even after R threw the error message you showed above, but it will be empty.