Tag Archives: server

Resolve SQL Server Fatal error: cannot create ‘R_TempDir’

I was trying to test an install of R in SQL Server 2016 and when running a script I received this error: Fatal error: cannot create ‘R_TempDir’

Following the instructions here, I enabled external scripts, restarted the sql server service, and then tried to run the following test script:

This is when the fatal error occurred.  As the error suggests, R is having some issues creating a temporary directory.  After some internet searching and trial and error I got past the issue.

Enable  8dot3 File Names

R configuration uses the 8dot3 file name convention, also known as “short names”.  To enable this on windows 10, run the following command in CMD (command prompt):

For more options and information look here: https://support.microsoft.com/en-us/kb/121007

Give access to the working directory to R

Locate and open “rlauncher.config” file in a text editor.  This file will be under the “<sqlserver_instance>\binn” directory.  Take a look at the location of WORKING_DIRECTORY.  This should have a “short name” file path.  The path should be something like “<sqlserver_instance>\EXTENS~1”, and “\EXTENS~1” is equivalent to “\ExtensibilityData”.  We need to give access to R to this folder. I did this by changing the permissions to full control to everyone.  You may want to be more restrictive here, but in my case this did not matter.

  • Right click folder > Properties > Security tab > Advanced > Add
  • Select a principal (I entered “Everyone”
  • Tick “Full control” under basic permissions and click “OK”
  • Tick “Replace all child object and permissions entries with inheritable permissions entries from this object” and click “OK”

Now if you rerun the script above you should get a result of “hello, 1”.

2016 Learning Goals

MCSA & MCSE SQL Server Certification
  • MCSA: SQL Server
  • MCSE: Data Platform
    • 70-464 – Developing Microsoft SQL Server Databases
    • 70-465 – Designing Solutions for SQL Server
  • MCSE: Business Intelligence
    • 70-466 – Implementing Data Models and Reports with Microsoft SQL Server
    • 70-467 – Designing Business Intelligence Solutions with Microsoft SQL Server
Neural Networks and Machine Learning

Training Sources:

Other Interests
  • Internet of Things Development
  • Mobile Application Development
  • Python
  • Ruby on Rails
  • Node.js, MongoDB, and Express
  • Quartz Composer
  • Actuary Exams
Learning Websites

Check SQL Server Version Using T-SQL

If you are building some code that would be different depending on the version of SQL Server then it may be useful to check the SQL server version using T-SQL instead of going through the object explorer in SSMS.  For example, I have some code that checks for SSIS package execution status across multiple servers and the database design is slightly different between server versions so I need to check the version and then execute the proper block of code.

 

Helpful SQL Server Links

icrosoft White Papers:
https://msdn.microsoft.com/en-us/library/ee410014(v=sql.105).aspx

Microsoft Books Online:
https://technet.microsoft.com/en-us/library/ms130214(v=sql.105).aspx

Pragmatic Works Training Resources:
http://pragmaticworks.com/Training/Resources

SQL Server Database Engine Permission Posters:
http://social.technet.microsoft.com/wiki/contents/articles/11842.sql-server-database-engine-permission-posters.aspx

TechFunda SQL Server Tutorials:
http://techfunda.com/howto/sql-server

Microsoft SQL Server Tutorials:
https://msdn.microsoft.com/en-us/library/ms167593

Microsoft SQL Server Community Projects & Samples:
http://sqlserversamples.codeplex.com/

SQL Pass DBA Fundamentals – Web Presentation Archives
http://fundamentals.sqlpass.org/MeetingArchive.aspx

Blogs and Other Great Resources:
https://blogs.msdn.microsoft.com/sql_server_team/ – MSSQL Tiger Team
https://www.mssqltips.com/
http://blog.sqlauthority.com/
http://www.brentozar.com/
http://www.edwinmsarmiento.com/
http://kevinsgoff.net/
https://www.sqlskills.com/
http://blog.datainspirations.com/
 – BI blog by Stacia

A better list put together by New Horizons:
http://nhlearningsolutions.com/Blog/TabId/145/ArtMID/16483/ArticleID/1186/Free-Resources-for-SQL-Server.aspx

SQL Injections:
http://www.unixwiz.net/techtips/sql-injection.html

List SQL Servers On Network

A list of servers can be obtained using windows command prompt (cmd) or powershell (ps).  CMD and PS both provide Server Name and Instance Name, however, powershell provides two additional fields: IsClustered, and SQL Server Version.  One challenge I have noticed is that running these commands from my local machine only returns a short list with names that look like computer names and not server names.  I had to run the commands from a production server, a QA server, and a development server to obtain the server lists from those environments.  There must be some network separation between them, which I am still investigating.

CMD: OSQL -L or SQLCMD -L

PS: [System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources()