![\"\"](\"https:\/\/sqlshep.com\/wp-content\/uploads\/2017\/11\/Screen-Shot-2017-11-28-at-4.53.33-PM.png\")
<\/a>
\nLets start with something simple, connect to any version of SQL using ODBC. <\/p>\n\r\n\r\n#you only need to install the package once per machine \r\n#unless there is an update to the package, \r\n#be sure to look for errors in the console output\r\ninstall.packages(\"odbc\")\r\n\r\n# library will load the package into memory and make it available for use\r\n# This will need to be run every time R is started \r\n\r\nlibrary(odbc)\r\n\r\n# ODBC requires the timezone to be set,\r\n# If you skip this step it will remind you \r\n\r\nSys.setenv(TZ='GMT')\r\n\r\n# If you have ever connected using odbc, this will look familiar\r\n# build the connection \r\nmaster <- dbConnect(odbc::odbc(),\r\n Driver = \"SQL Server\", \r\n Server = \"localhost\",\r\n Database = \"master\",\r\n Trusted_Connection = 'Yes')\r\n\r\n# prepare a sql statement to send\r\nSQLStmt <- sprintf(\"SELECT [session_id]\r\n ,[login_time]\r\n ,[host_name]\r\n ,[program_name]\r\n FROM [master].[sys].[dm_exec_sessions]\")\r\n\r\n# Alternatively, you can run @@version, just to connect and get out\r\n# SQLStmt <- sprintf(\"select @@version\")\r\n\r\n\r\n# Submit the query, if your syntax is wrong this is where you will see it the error \r\nrs <- dbSendQuery(master, SQLStmt)\r\n\r\n# Receive the results into a data frame called MyStuff\r\nMyStuff <- dbFetch(rs)\r\n\r\n# house keeping \r\ndbClearResult(rs)\r\ndbDisconnect(master)\r\n<\/code>\r\n<\/pre>\nIf you received no errors you should now have a dataframe called MyStuff that has some number of rows and 4 columns, you will notice that R calls these observations (rows), and variables (columns). This is leftover from the statistical language of a study, a case is an observation or a row to you and me, and a variable is a column. <\/p>\n
If you change the query to Select * from sys].[dm_exec_sessions], and receive the error, \u201cnanodbc.cpp:3102: 07009: [Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index\u201d, in the case of this query it is because the tidyverse odbc has some difficulty with guids, so, take the guids in the query and cast them as varchar(32) and you will get past it(\u201ccast([original_security_id] as varchar(36))\u201d). Yes, you will have to blow out the entire column list and find all of the guids, but then again you should not be using select * anyway, or so people tell me. \ud83d\ude1b<\/p>\n
My Versions today:<\/p>\n
\r\n> version\r\n _ \r\nplatform x86_64-w64-mingw32 \r\narch x86_64 \r\nos mingw32 \r\nsystem x86_64, mingw32 \r\nstatus \r\nmajor 3 \r\nminor 3.3 \r\nyear 2017 \r\nmonth 03 \r\nday 06 \r\nsvn rev 72310 \r\nlanguage R \r\nversion.string R version 3.3.3 (2017-03-06)\r\nnickname Another Canoe \r\n\r\n> packageVersion(\"odbc\")\r\n\r\n[1] \u20181.0.1\u2019\r\n\r\n## If you ran \"select @@version\"\r\n\r\n> MyStuff\r\n \r\n1 Microsoft SQL Server 2016 (SP1-CU1) (KB3208177) - 13.0.4411.0 (X64) \\n\\tJan 6 2017 14:24:37 \\n\\tCopyright (c) Microsoft Corporation\\n\\tDeveloper Edition (64-bit) on Windows Server 2016 Datacenter 6.3 (Build 14393: ) (Hypervisor)\\n\r\n<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"So here you are, you know SQL or you at least do something with it everyday and are wondering what all the hoopla is about R and data science. Lets break down the first barrier, R and data science actually have little to do with each other, R is a language, data science is an […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[46,9,38,33,5],"tags":[41,40,42,43,45,44,10,39],"class_list":["post-386","post","type-post","status-publish","format-standard","hentry","category-odbc","category-r","category-rstudio","category-sql","category-sqlshep","tag-41","tag-2008-r2","tag-42","tag-43","tag-connectivity","tag-odbc","tag-r","tag-sql"],"_links":{"self":[{"href":"https:\/\/sqlshep.com\/index.php?rest_route=\/wp\/v2\/posts\/386","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlshep.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlshep.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlshep.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlshep.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=386"}],"version-history":[{"count":25,"href":"https:\/\/sqlshep.com\/index.php?rest_route=\/wp\/v2\/posts\/386\/revisions"}],"predecessor-version":[{"id":541,"href":"https:\/\/sqlshep.com\/index.php?rest_route=\/wp\/v2\/posts\/386\/revisions\/541"}],"wp:attachment":[{"href":"https:\/\/sqlshep.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=386"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlshep.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=386"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlshep.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=386"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
![\"\"](\"https:\/\/sqlshep.com\/wp-content\/uploads\/2017\/11\/Screen-Shot-2017-11-27-at-2.18.17-PM-1024x660.png\")
<\/a><\/p>\n