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?
Advertisement
Answer
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