I am trying to load a data frame in to a SQL Server database table. The table, may or may not be in the default "dbo" schema. The statement errors out when the table name does not contain at least one (1) "underscore". The same code works perfectly when the table name does contain an "underscore".

Here is the statement that I execute :-

table_id <- Id(schema = "dbo", name = "STGasFlatLoadAdjustments")
res <- dbWriteTable(conn = dbi_pfdb, name = table_id, value = testdf, append = TRUE)

I have seen a lot of comments on line by people who have reported similar issues using the DBI package.

Is there a known bug in the package or is there an known get around for this error?

These are the commands that work and don't work with the same source data frame and same target database table - the only difference being the table name.

dbi_pfdb <- DBI::dbConnect(odbc::odbc(), .connection_string = "Driver={SQL Server}; server=DEEPAKPC; database=MyDB; trustedConnection=true;")

This is the source data (use this to create a data frame or a csv file for creating a data frame)

Entity,MIRN,FromDate,ToDate,AdjustmentsValue,DerivedFlatLoadSite,UserSpecifiedFlatLoadSite,Comments,LoadDate
NSW_TRU,5240433621,2019-01-01 00:00:00.000,2020-12-31 00:00:00.000,4500.0000000000,1,1,TTest Comment,2019-05-15 00:00:00.000
NSW_TRU,5240433621,2019-01-05 00:00:00.000,2019-01-05 00:00:00.000,-4500.0000000000,1,1,TTest Comment,2019-05-15 00:00:00.000
NSW_TRU,5240433621,2019-01-06 00:00:00.000,2019-01-06 00:00:00.000,-4500.0000000000,1,1,TTest Comment,2019-05-15 00:00:00.000
NSW_TRU,5240433621,2019-01-05 00:00:00.000,2019-01-05 00:00:00.000,3800.0000000000,1,1,TTest Comment,2019-05-15 00:00:00.000
NSW_TRU,5240433621,2019-01-06 00:00:00.000,2019-01-06 00:00:00.000,3900.0000000000,1,1,TTest Comment,2019-05-15 00:00:00.000
NSW_TRU,0000000000,2019-05-20 00:00:00.000,2019-05-20 00:00:00.000,-4200.0000000000,1,1,TTest Comment,2019-05-15 00:00:00.000
NSW_TRU,5240433621,2019-05-18 00:00:00.000,2019-05-18 00:00:00.000,-4500.0000000000,1,1,TTest Comment,2019-05-15 00:00:00.000
NSW_TRU,5240433621,2019-05-19 00:00:00.000,2019-05-19 00:00:00.000,-4500.0000000000,1,1,TTest Comment,2019-05-15 00:00:00.000
NSW_TRU,5240433621,2019-05-18 00:00:00.000,2019-05-18 00:00:00.000,3800.0000000000,1,1,TTest Comment,2019-05-15 00:00:00.000
NSW_TRU,5240433621,2019-05-19 00:00:00.000,2019-05-19 00:00:00.000,3900.0000000000,1,1,TTest Comment,2019-05-15 00:00:00.000

Code to create an SQL Server table for the source data to be uploaded into.

USE [MyDB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[STGas_FlatLoadAdjustments](
    [Entity] [varchar](50) NULL,
    [MIRN] [varchar](50) NULL,
    [FromDate] [datetime] NULL,
    [ToDate] [datetime] NULL,
    [AdjustmentsValue] [numeric](18, 10) NULL,
    [DerivedFlatLoadSite] [int] NULL,
    [UserSpecifiedFlatLoadSite] [int] NULL,
    [Comments] [varchar](max) NULL,
    [LoadDate] [datetime] NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

With the table name not containing any underscores, this is the error I get :-

Error in connection_sql_tables([email protected], catalog_name = if ("catalog" %in%  :
  nanodbc/nanodbc.cpp:2525: 24000: [Microsoft][ODBC SQL Server Driver]Invalid cursor state

0 Answers