RODBC sqlSave table creation problems

Question

I'm having trouble creating a table using RODBC's sqlSave (or, more accurately, writing data to the created table).

This is different than the existing sqlSave question/answers, as

1. the problems they were experiencing were different, I can create tables whereas they could not and
2. I've already unsuccesfully incorporated their solutions, such as closing and reopening the connection before running sqlSave, also
3. The error message is different, with the only exception being a post that was different in the above 2 ways

I'm using MS SQL Server 2008 and 64-bit R on a Windows RDP.

I have a simple data frame with only 1 column full 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. Additionally, an error message makes it sound like the table can't be created though the table does in fact get created with 0 rows.

Based on a suggestion I found, I've tried closing and re-opening the RODBC connection right before running sqlSave. Even though I use append = TRUE, I've tried dropping the table before doing this but it doesn't affect anything.

> 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've also tried using sqlUpdate() on the table once it's been created. It doesn't matter if I create it in R or SQL Server Management Studio, I get the error table not found on channel

Finally, note that I have 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's #R had me check if I could read in the empty table using sqlQuery and indeed, I can.

Update

I've gotten a bit closer with the following steps:

1. I created an ODBC connection that goes directly to my Database within the SQL Server, instead of just to the default (Master) DB then specifying the path to the table within the table = or tablename = statements
2. Created the table in SQL Server Management Studio as follows

GO

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

GO

1. In R I used sqlUpdate with my new ODBC connection and no brackets around the tablename

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

3. Indicating that the only column in the table is the unique column with index = colname results in an error saying that the column does not exist

4. I dropped and recreated the table specifying a 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 a Primary Key and Index (according to the GUI interface of SQL Sever Management Studio) named PK__jive_BNR__2754EC2E30F848ED

1. I specified this index/key as the 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 was specifying the correct column name (not "colname") for index; thanks to MrFlick for requesting clarification.

Also, these steps are numbered 1 through 7 in my post but StackOverflow resets the numbering of the list a few times when it gets displayed. If anyone can help me clean that aspect of this post up I'd appreciate it.

Answer 1

We have had this same problem, which after a bit of testing we solved simply by not using square brackets in the schema and table name reference.

i.e. rather than writing

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

instead write

table = "Jason.dbo.df"

Appreciate this is now long past the original question, but just for anyone else who subsequently trips up on this problem, this is how we solved it. For reference, we found this out by writing a simple 1 item dataframe to a new table, which when inspected in SQL contained the square brackets in the table name.

Answer 2

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

• Open 32-bit ODBC Administrator and create a User DSN and configure it for your specific database. In my case, I am creating a global temp table so I linked to tempdb. Use this connection Name in your 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)").
• I then updated my data frame class types using as.character and as.Date to match the data types listed above.
• I already created the table since I've been working on it for hours so I had to drop the table using sqlDrop(myconn2, "##R_Claims_Data").
• I then ran: sqlSave(myconn2, MainClmDF2, tablename = "##R_Claims_Data", verbose=TRUE, rownames= FALSE, varTypes=columnTypes)

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

Table not found

sqlSave in R

RODBC