RODBC sqlSave table creation problems
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 codemyconn2 <- 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
andas.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:
After re-reading the RODBC vignette and here's the simple 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 stemmed from the use of the additional options, particularlly table =
or, equivalently, tablename =
. Those should be valid options but somehow they manage to cause problems with my particular version of RStudio ((Windows, 64 bit, 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 has never existed, but as a best practice I'm writing try(sqlDrop(db, "df", errors = FALSE), silent = TRUE)
before all sqlSave
statements in my code.
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.