RODBC sqlSave 表创建问题 [英] RODBC sqlSave table creation problems

问题描述

我在使用 RODBC 的 sqlSave 创建表时遇到问题(或者更准确地说，将数据写入创建的表).

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

这与现有的 sqlSave 问题/答案不同，因为

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

1. 他们遇到的问题不同，我可以创建表而他们不能，
2. 我已经没有成功地合并他们的解决方案，例如在运行 sqlSave 之前关闭和重新打开连接，还有
3. 错误信息是不同的，唯一的例外是上述两种方式不同的帖子

我在 Windows RDP 上使用 MS SQL Server 2008 和 64 位 R.

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

我有一个简单的数据框，其中只有 1 列包含 3、4 或 5 位整数.

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

当我尝试使用 sqlSave 时，没有数据写入表中.此外，一条错误消息听起来像是无法创建该表，尽管该表实际上是用 0 行创建的.

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.

根据我发现的建议，我尝试在运行 sqlSave 之前关闭并重新打开 RODBC 连接.即使我使用 append = TRUE，我也尝试在执行此操作之前删除该表，但它不会影响任何事情.

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)'

我也试过在表创建后对它使用 sqlUpdate().如果我在 R 或 SQL Server Management Studio 中创建它并不重要，我收到错误 table not found on channel

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

最后，请注意，我也尝试过不使用 append = TRUE 以及在创建新表时以及使用和不使用 rownames 选项的情况.

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.

来自 Freenode 的 #R 的 Flick 先生让我检查是否可以使用 sqlQuery 读取空表，事实上，我可以.

Mr.Flick from Freenode's #R had me check if I could read in the empty table using sqlQuery and indeed, I can.

更新

我已经通过以下步骤更接近了:

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

1. 我创建了一个 ODBC 连接，该连接直接连接到我在 SQL Server 中的数据库，而不是仅连接到默认(主)数据库，然后在 table = 或 中指定表的路径>tablename = 语句
2. 在 SQL Server Management Studio 中创建表如下

GO

创建表 [dbo].[testing123]([Person_DIMKey] [int] NULL) ON [PRIMARY]

GO

1. 在 R 中，我将 sqlUpdate 与我的新 ODBC 连接一起使用，并且表名周围没有括号

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

现在 sqlUpdate() 看到了表，但是它抱怨它需要一个唯一的列

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

指示表中唯一的列是具有 index = colname 的唯一列会导致错误，指出该列不存在

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

我删除并重新创建了指定主键的表，

I dropped and recreated the table specifying a primary key,

GO

创建表 [dbo].[jive_BNR_Person_DIMKey]([jive_BNR_Person_DIMKey] [int] NOT NULL PRIMARY KEY) ON [PRIMARY]

GO

它生成了一个名为 PK__jive_BNR__2754EC2E30F848ED 的主键和索引(根据 SQL Sever Management Studio 的 GUI 界面)

which generated both a Primary Key and Index (according to the GUI interface of SQL Sever Management Studio) named PK__jive_BNR__2754EC2E30F848ED

1. 我将此索引/键指定为 sqlUpdate() 中的唯一列，但出现以下错误:

sqlUpdate 出错(db4, jive_BNR_Person_DIMKey, tablename = "jive_BNR_Person_DIMKey", :索引列PK__jive_BNR__2754EC2E30F848ED不在数据库表中

为了记录，我为索引指定了正确的列名(不是colname")；感谢 MrFlick 要求澄清.

For the record, I was specifying the correct column name (not "colname") for index; thanks to MrFlick for requesting clarification.

此外，这些步骤在我的帖子中从 1 到 7 编号，但 StackOverflow 会在列表显示时重置它的编号几次.如果有人能帮我清理这篇文章的那个方面，我将不胜感激.

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.

推荐答案

在重新阅读 RODBC 小插图后，这里是有效的简单解决方案:

After re-reading the RODBC vignette and here's the simple solution that worked:

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

完成.

经过几天的大量试验后，问题似乎源于使用附加选项，特别是 table = 或等效的 tablename =.这些应该是有效的选项，但不知何故，它们设法导致我的特定版本的 RStudio((Windows，64 位，桌面版本，当前版本)，R(Windows，64 位，v3)和/或 MS SQL Server 2008 出现问题.

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) 也可以在没有 sqlDrop(db, "df") 的情况下工作，如果该表从未存在，但作为最佳实践，我'在我的代码中的所有 sqlSave 语句之前编写 try(sqlDrop(db, "df", errors = FALSE), silent = TRUE).

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.

这篇关于RODBC sqlSave 表创建问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！