RODBC command 'sqlQuery' has problems with table variables in t-SQL

Question

I am using the RODBC package which I am applying on a Microsoft SQL Server 2012.

Now I have discovered a phenomenon that puzzles me.

If I run the following query with the RODBC command sqlQuery, then, in R, I will get back an empty data frame with the columns Country, CID, PriceID and WindID.

DECLARE @tbl_IDs TABLE 
(
    Country nvarchar(30),
    CID nvarchar(5),
    PriceID int,
    WindID int
)

SELECT * FROM @tbl_Ids

So far, everything is fine.

However, if I try to write a record to the table variable and execute

DECLARE @tbl_IDs TABLE 
(
    Country nvarchar(30),
    CID nvarchar(5),
    PriceID int,
    WindID int
)

INSERT INTO @tbl_IDs
VALUES 
    ('Germany', 'DE', 112000001, 256000002);

SELECT * FROM @tbl_Ids

Then, in R, the result will be an empty character instead of a dataframe with one record. Still the same query works perfectly with SQL Server Management Studio. Also, we have traced the behaviour of the DB Server while the R-Query is executed and it seems the server handles it perfectly. It seems that the RODBC interface has a problem to return the result to the R console.

Does anybody have an idea how this issue can be resolved?

Try changing table variable to temporary table. CREATE TABLE #tbl_IDs ( Country nvarchar(30), CID nvarchar(5), PriceID int, WindID int ); INSERT INTO #tbl_IDs VALUES ('Germany', 'DE', 112000001, 256000002); SELECT * FROM #tbl_Ids — Lukasz Szozda
– Lukasz Szozda, Commented Oct 28, 2015 at 16:18
Thanks for your answer but this didn't resolve the problem. We had already tested that before. — Andro
– Andro, Commented Oct 29, 2015 at 17:24

nrussell · Accepted Answer · 2015-10-28 16:22:34Z

9

Try toggling NOCOUNT as below:

old_qry <- "
DECLARE @tbl_IDs TABLE 
(
    Country nvarchar(30),
    CID nvarchar(5),
    PriceID int,
    WindID int
)

INSERT INTO @tbl_IDs
VALUES 
    ('Germany', 'DE', 112000001, 256000002);

SELECT * FROM @tbl_Ids
"
##
new_qry <- "
SET NOCOUNT ON;
DECLARE @tbl_IDs TABLE 
(
    Country nvarchar(30),
    CID nvarchar(5),
    PriceID int,
    WindID int
);

INSERT INTO @tbl_IDs
VALUES 
    ('Germany', 'DE', 112000001, 256000002);
SET NOCOUNT OFF;
SELECT * FROM @tbl_Ids
"

R> sqlQuery(tcon, gsub("\\n", " ", old_qry))
#character(0)
R> sqlQuery(tcon, gsub("\\n", " ", new_qry))
#  Country CID   PriceID    WindID
#1 Germany  DE 112000001 256000002

Basically you want to SET NOCOUNT ON at the beginning of your code, and SET NOCOUNT OFF just before the final SELECT statement.

answered Oct 28, 2015 at 16:22

nrussell

18.6k4 gold badges53 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Parfait · Accepted Answer · 2015-10-28 17:23:27Z

1

Since database server handles query correctly, save the multiple line action TSQL query as a SQL Server Stored Procedure and have R call it retrieving the resultset.

Do note you can even pass parameters in the EXEC sp line from R to MSSQL. Also as mentioned, include the SET NOCOUNT ON declaration in the query to avoid undesired result character(0):

library("RODBC");
conn <- odbcConnect("DSN Name",uid="***",pwd="***");   # WITH DSN
#conn <-odbcDriverConnect('driver={SQL Server};server=servername;database=databasename;
                        #trusted_connection=yes;UID=username;  PWD=password')  # WITH DRIVER

df<-sqlQuery(conn, "EXEC dbo.StoredProcName");

answered Oct 28, 2015 at 17:23

Parfait

108k19 gold badges103 silver badges138 bronze badges

1 Comment

Andro Over a year ago

Thanks for your answer but your solution goes a bit too far for my DB knowledge. I wasn't able to test it.

Collectives™ on Stack Overflow

RODBC command 'sqlQuery' has problems with table variables in t-SQL

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related