Why does this work under Microsoft SQL Server Management Studio but not under R?

Question

I received this SQL statement:

use MYDATABASE; 
if object_id('tempdb..#mapDT') is not null drop table #mapDT; 
create table #mapDT (SqlDatatype varchar (64), MyNewDataType varchar(64));
insert into #mapDT SELECT 'varchar','type text' UNION ALL SELECT 'datetime','type datetime'UNION ALL SELECT 'tinyint','int64.Type' UNION ALL SELECT 'int','int64.Type' UNION ALL SELECT 'float','type number';
SELECT COLUMN_NAME, DATA_TYPE, 'MyString1' + COLUMN_NAME + 'MyString2' + m.MyNewDataType + 'MyString3' FROM INFORMATION_SCHEMA.COLUMNS C JOIN #mapDT m on m.SqlDatatype = C.DATA_TYPE WHERE TABLE_NAME = 'MYTABLE';

and it runs nicely under MS SSMS. The relevant part of the result is:

MyString1COLUMN01MyString2type textMyString3
MyString1COLUMN02MyString2type datetimeMyString3
MyString1COLUMN03MyString2type textMyString3
MyString1COLUMN04MyString2type textMyString3
MyString1COLUMN05MyString2int64.TypeMyString3
MyString1COLUMN06MyString2type datetimeMyString3
MyString1COLUMN07MyString2type datetimeMyString3
    ...     ...    ...

When I run it under R I must leave out use MYDATABASE; and be sure I am connected at the right database, which is the case. But the SQL-statement is problematic. The output of:

> library(odbc)
> conn <- dbConnect(odbc(), Driver = "SQL Server", Server = "MYSERVER\\MYINSTANCE", Database = "MYDATABASE", Trusted_Connection = "True")
> dbGetQuery(conn, "
+                   if object_id('tempdb..#mapDT') is not null drop table #mapDT; create table #mapDT (SqlDatatype varchar (64), MyNewDataType varchar(64));
+                   insert into #mapDT SELECT 'varchar', 'type text' UNION ALL SELECT 'datetime', 'type datetime' UNION ALL SELECT 'tinyint', 'int64.Type' UNION ALL SELECT 'int', 'int64.Type' UNION ALL SELECT 'float', 'type number';
+                   SELECT COLUMN_NAME, DATA_TYPE, 'MyString1' + COLUMN_NAME + 'MyString2' + m.MyNewDataType + 'MyString3' FROM INFORMATION_SCHEMA.COLUMNS C JOIN #mapDT m on m.SqlDatatype = C.DATA_TYPE WHERE TABLE_NAME = 'MYTABLE';
+                  ")

is data frame with 0 columns and 0 rows.

Is it the use of "temporal tables" (i.e. #mapDT) the cause of the empty data frame? If so, is there a way to get R and/or Python to understand/use them?

Prepend `SET NOCOUNT ON;` to your command text and see if that helps. — Gord Thompson, Feb 19 '20 at 12:16
Hello, yes! that solves the issue. Could you throw some light on why? — scjorge, Feb 19 '20 at 14:30
Thanx! and for completion: https://stackoverflow.com/questions/2578194/what-is-ddl-and-dml — scjorge, Feb 19 '20 at 15:23

score 2 · Answer 1 · edited Feb 19 '20 at 16:31

2

Nonetheless, consider avoiding any DML statement that triggers NOCOUNT as your temp table is unnecessary. Simply run a subquery JOIN for a simpler, readable, maintainable query. Below CONCAT replaces the + on varchar fields and names calculated column with alias, OUTPUT:

SELECT COLUMN_NAME, DATA_TYPE, 
       CONCAT('MyString1', COLUMN_NAME,
              'MyString2', m.MyNewDataType, 
              'MyString3') AS OUTPUT
FROM INFORMATION_SCHEMA.COLUMNS C 
INNER JOIN 
   ( SELECT 'varchar' AS SqlDatatype, 'type text' AS MyNewDataType 
     UNION ALL SELECT 'datetime', 'type datetime'
     UNION ALL SELECT 'tinyint', 'int64.Type' 
     UNION ALL SELECT 'int', 'int64.Type'
     UNION ALL SELECT 'float', 'type number'
   ) m 

ON m.SqlDatatype = C.DATA_TYPE 
WHERE TABLE_NAME = 'MYTABLE';

edited Feb 19 '20 at 16:31

scjorge

169
1
12

answered Feb 19 '20 at 16:14

Parfait

104,375
17
94
125

Curious @scjorge, did this solution help? Avoiding temp tables is optimal. You can even use [CTE](https://learn.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver15). – Parfait Feb 19 '20 at 21:30
yes! this statement generates the desired output in a much more elegant way... Runs under R and under Python. may be I should change the title of my question to place the focus on this answer? hmm.. – scjorge Feb 20 '20 at 06:35
Sometimes on StackOverflow we suggest better answers than simple fixes to original issues. Future readers are part of our audience. But happy coding! – Parfait Feb 20 '20 at 15:50
Good. Then I must ask you to please merge both answers to mark one as the acceped answer. I cannot merge them 'cuz the edit is too heavy for StackExchange to allow it. :) – scjorge Feb 25 '20 at 17:13

scjorge · Accepted Answer · 2020-02-19T16:19:14.940

as Gord Thompson knows, the code should include SET NOCOUNT ON;. Thus,

> library(odbc)
> conn <- dbConnect(odbc(), Driver = "SQL Server", Server = "MYSERVER\\MYINSTANCE", Database = "MYDATABASE", Trusted_Connection = "True")
> dbGetQuery(conn, "
+                   SET NOCOUNT ON; if object_id('tempdb..#mapDT') is not null drop table #mapDT; create table #mapDT (SqlDatatype varchar (64), MyNewDataType varchar(64));
+                   insert into #mapDT SELECT 'varchar', 'type text' UNION ALL SELECT 'datetime', 'type datetime' UNION ALL SELECT 'tinyint', 'int64.Type' UNION ALL SELECT 'int', 'int64.Type' UNION ALL SELECT 'float', 'type number';
+                   SELECT COLUMN_NAME, DATA_TYPE, 'MyString1' + COLUMN_NAME + 'MyString2' + m.MyNewDataType + '},' FROM INFORMATION_SCHEMA.COLUMNS C JOIN #mapDT m on m.SqlDatatype = C.DATA_TYPE WHERE TABLE_NAME = 'MYTABLE';
+                  ")

and in my case the row numbers are not wanted, so I also used formals(print.data.frame)$row.names <- FALSE:

> library(odbc)
> conn <- dbConnect(odbc(), Driver = "SQL Server", Server = "MYSERVER\\MYINSTANCE", Database = "MYDATABASE", Trusted_Connection = "True")
> formals(print.data.frame)$row.names <- FALSE
> dbGetQuery(conn, "
+                   SET NOCOUNT ON; if object_id('tempdb..#mapDT') is not null drop table #mapDT; create table #mapDT (SqlDatatype varchar (64), MyNewDataType varchar(64));
+                   insert into #mapDT SELECT 'varchar', 'type text' UNION ALL SELECT 'datetime', 'type datetime' UNION ALL SELECT 'tinyint', 'int64.Type' UNION ALL SELECT 'int', 'int64.Type' UNION ALL SELECT 'float', 'type number';
+                   SELECT COLUMN_NAME, DATA_TYPE, 'MyString1' + COLUMN_NAME + 'MyString2' + m.MyNewDataType + 'MyString3' FROM INFORMATION_SCHEMA.COLUMNS C JOIN #mapDT m on m.SqlDatatype = C.DATA_TYPE WHERE TABLE_NAME = 'MYTABLE';
+                  ")
> formals(print.data.frame)$row.names <- TRUE

Why does this work under Microsoft SQL Server Management Studio but not under R?

2 Answers2