Table of contents
Collapse the table of content
Expand the table of content


j-martens|Last Updated: 12/15/2016
1 Contributor

Generates a SQL Server data source object.


RxSqlServerData(table = NULL, sqlQuery = NULL, connectionString = NULL,
           rowBuffering = TRUE, returnDataFrame = TRUE, stringsAsFactors = FALSE,
           colClasses = NULL, colInfo = NULL, rowsPerRead = 50000, verbose = 0,
           useFastRead = TRUE, server = NULL, databaseName = NULL,
           user = NULL, password = NULL, writeFactorsAsIndexes = FALSE)

## S3 method for class 'RxSqlServerData'
head(x, n = 6L, reportProgress = 0L, ...)
## S3 method for class 'RxSqlServerData'
tail(x, n = 6L, addrownums = TRUE, reportProgress = 0L, ...)


The following table shows the arguments to RxSqlServerData in order and their default values.

tableNULLSpecify either a table name or view name.
sqlQueryNULLIf you do not specify a table or view, provide a valid SQL statement.
connectionStringNULLA string or string variable that defines a valid connection string.
rowBufferingTRUEIndicates whether buffering should be used during reads. If you are having problems with your ODBC driver, try setting this to ‘FALSE’.
returnDataFrameTRUEIndicates whether the result should be converted from to a data frame. All results returned by SQL Server are data frames.
stringsAsFactorsFALSEIndicates whether strings should be converted to factors on import. By default, SQL Server treats strings as factors.
colClassesNULLA string specifying the data types for each of the columns returned from the SQL Server. For information about how to map SQL Server data types to R data types, see Working with R Data Types.
colInfoNULLA list containing the names of variables.
rowsPerRead50000The number of rows to read in each batch.
verbose0Indicates the level of ODBC error reporting. If 0, no progress is reported. A variety of options are available.
useFastReadTRUEIndicates whether to use the FastRead option.
serverNULLThe name of the SQL Server instance, or the IP address. If you specify the server name as part of the connection string, you can omit this argument.
databaseNameNULLSpecify the database name, if not already included in the connection string.
userNULLSpecify a SQL Server login or Windows user name.
passwordNULLFor SQL logins, provide the password associated with the login. Passwords should not be provided for Windows Integrated authentication; specify Trusted_Connection=true.
writeFactorsAsIndexesFALSEIndicates whether columns treated as factors should be indexed.

If values are provided using the user or password arguments, the values take precedence over equivalent information provided within the connectionString argument. For example, if you specify a user name as part of the connection string, you can override it by providing a value in the user argument.


This is the main generator for the class RxSqlServerData, which extends RxDataSource.

The object is prepared but not used until you use a command that begins to load the data or otherwise process it.

Return Value

An object of class RxSqlServerData.


The following example defines a SQL Server data source using a SQL login and password. The RxSqlServerData function instantiates the data source object but does not populate it until the rxImport function is called. Then the data is read in chunks from the database, using the predefined batch size, and written to a local XDF file.

     # Create an RxSqlServerData data source

     sqlServerConnString <- "SERVER=RTest01;DATABASE=TestDatabase;UID=DBUser;PWD=DBUserPassword;"

     dsSqlServerData <- RxSqlServerData(sqlQuery = "SELECT * FROM MyData",
                                    connectionString = sqlServerConnString)

     # Create an xdf file name
     localXdfFileName <- file.path(tempdir(), "importedData.xdf")

     # Import the data into the xdf file
     rxImport(dsSqlServerData, localXdfFileName, overwrite = TRUE)

     # Read xdf file into a data frame
     MyDataIn <- rxDataStep(localXdfFileName)

The following example defines a connection string using Windows integrated authentication.

# Create the connection string
instance_name <- "type instance name here";
database_name <- "type database name here";
myConnString <- paste("Driver= SQL Server;Server=",instance_name, ";Database=",database_name,";Trusted_Connection=true;",sep="");

# Set other variables used to define the compute context
sqlWait = TRUE;
sqlConsoleOutput = TRUE;

# Create the compute context
cc <-RxInSqlServer

See Also

Comparison of rx Functions and CRAN R Functions

ScaleR Functions for Working with SQL Server Data

© 2017 Microsoft