What is dynamic SQL?

It is a batch of SQL statements that you can run by executing the batch.

Basically you declare a command variable and execute the command. The Command variable has the executable T-SQL code.  The executable code may also contain the following:

• System Stored Procedure
• User defined stored procedure
• CLR stored procedure
• Scalar valued user-defined function
• Extended stored procedure

Let me first give an example which is often used to demonstrate the use of Dynamic SQL. I am using SQL Server 2016 but using a database imported from SQL Server 2014.

D_SQL_00

The first line of code declares a variable @cmd of data type characters

The second line declares a variable @tbl also of data type characters

The third statement sets a value for the @tbl variable which in this case AdventureWorks2014.Person.Address table. This statement does not require the context of AdventureWorks2014, but can be run from 'master'

The fourth statement declares what the @cmd variable should do when executed. It is just a SELECT statement that selects the top 5 rows from the Person.Address table.

You can see that this dynamic SQL returns the results requested.

Read the MSDN article here:
https://msdn.microsoft.com/en-us/library/ms188332.aspx?f=255&MSPPError=-2147217396

How are User DSN, System DSN and File DSN are different?

ODBC User data source stores information needed to connect to the data source that is targeted for connection.

DSN is needed to connect to a data source and it stands for Data Source Name.
User DSN:
Only visible to the user who created it and can only be used on the computer on which it is created.

System DSN:
It is available for all users of the computer on which it is created including NT Services.

File DSN:
It can be shared by users who have the same drivers installed on their computer.

Obviously File DSN is more portable than User DSN

How do you create a DSN?

All Windows installations have what is called a ODBC Data Source Administrator, a program to create, maintain or modify data source access that use SQL as a data source standard language.

Search ODBC Data Source on our desktop. 

Can you code in TypeScript in Visual Studio 2015?

I have Visual Studio 2015 Community and I can say that you can indeed code in TypeScript and Visual Studio 2015 provides support. As you can see from the next image, VS Community 2015, TypeScript 1.8 is installed.

As to what type of templates exist, all you need to do is to create a new project and take a look at the installed templates and look for TypeScript as shown here:

What is Microsoft R Open and how do you get it?

Are you interested in doing statistical analysis of data on a SQL Server? If you say yes, you need to use the most developed statistical ananlysis language R to go with it.

Microsoft R Open is the enhanced distribution of R from Microsoft. It is complete and open source platform. The version R Open 3.3.0 is 100% based on the R-3.3.0 statistical language (R Foundation for Statistical Computing). It is available for 64-bit acrchitecture only.

Its features are:

• Improved performance
• Reproducibility
• Support for Windows and Linux-based Platforms

You may download it from here: https://mran.revolutionanalytics.com/download/

However, if you have already installed SQL Server 2016 and you want to use the R 3.3.0 features then
https://mran.revolutionanalytics.com/download/mro-for-mrs/

Note that R Open is automatically installed for you during installation of R Server.

What is the connection string to use while connecting to SQL Server 2016 from R?

Connection String is perhaps the most important parameter while connecting to SQL Server or for that matter any other source of data.

You should preferably launch the Microsoft's R Gui and specify a string variable to connect to SQL Server 2016 from within R Gui.

You need to create a string variable such as this for a trusted connection:
----------------------------------
.

-----------------------------------
If you have UserId (Uid) and Password (Pwd) with appropriate permissions you may use the following:
--------------------------------------------

-------------------------

Note 1: Your Server may be different and your database may be different
Note 2: For Server you may also use just a period as in:
-------------
Server=.;
-------------
Note 3: Keyword are case sensitive

My apologies for an ill formatted post which I have now corrected by using images instead of formatted text.

Does Evernote Web Clipper extension for Microsoft Edge really work?

What is Evernote Web Clipper?

It is an extension to Microsoft Edge for builds 14372 and beyond (hopefully).
This is a new extension that clips web pages and saves to Evernote (called the Evernote web Clipper) which adds the extension to Microsoft Edge (Clip to Evernote). Once in Evernote you can easily find them on any device. You can, not only clip, but also add annotations and share the web clips with others.

Here is the PR video:

Now does it work? I tried to work with the extension and here are the findings.

You get to the Microsoft Extensions page from the link shown earlier which brings up the page with several extensions. Evernote Web Clipper as shown below is also available if your version of Microsoft Edge supports it.

EvernoteWebClipper14372_00

Click Open in Store. From here you can download it.

After downloading you should be able to see the Clip to Evernote menu in the Ellipsis at the right most end of Microsoft Edge as shown.

When you click on it, you will be asked to provide the Evernote login or register if you do not have an account as shown.

Since I do not have an account I click on Create an Evernote account which opens up this pop-up.

There is no message if it has been accepted or not, but there seems to be a piece of mail from Evernote advising me to download Evernote.

I download the application (Evernote_6.1.2.2292.exe). Then follows a series of wizard screens such as this one.

Looks like you need to register again as the 'Get a Free Account' got launched. I will just try if my initial registration is still current. Looks like the account is current.

Launch the Evernote app to open the following (after providing the credentials).

Click the radio button, Clip content from the web launches a web page reminding you that you can say Good Bye to Bookmarks and welcome Evernote.

Test Web clipping with extension:

Launch a web page (any page) in Microsoft Edge.

Try to click Clip to Evernote and you may not succeed couple of times and you may get this message.

Close Microsoft Edge (all browsers) and start again and finally this will come up.

I saved it as a Simplified article and clicked Save. The synchronize button started working, probably synching, what it captured with the Evernote on the desktop. Finally this came up.

Now Launch Evernote to see if the clip has been added. You may have to click on the menu item Sync to see the note as shown.

Wunderbar! It finally worked.

What is the difference between Standard HTML5 and HTML5+Cordova Project in Intel XDK?

In all my previous posts in this blog we only looked at Standard HTML5 projects. In Standard HTML5, the project was packaged as a web app and host it on a server (the Intel XDK server in this case) as an HTML5 web app, or build it for distribution via popular mobile app stores. For testing purposes, you would use the Intel XDK Preview app available from app stores. There are couple of examples and posts on this site. Standard HTML5 Projects are built for Desktops, smartphones and tablets.

The HTML5+Cordova projects on the other hand are built for smartphones and tablets for all of the following OSs: android, iOS, Windows and also as a web app.

The pages you create use both the Standard HTML5 and Cordova APIs and can be distributed via popular app stores.

You will be devleoping as a hybrid mobile app that can,

• Use sensor data on the phone including camera, gps, accelerometer etc
• Access device hardware;Near field communication(NFC); Bluetooth, Camera etc
• Manage file storage and caching. Also access databases like calendars, contacts
• Work with third-party plug-ins

More on Cross-Walk here:

Cross-Walk is also android specific but built on Open Source foundation.

https://software.intel.com/en-us/xdk/blog/what-is-crosswalk

Can you send a post to your blog with Office 365?

Yes it is possible although there are quite a few places you may get stuck. I tried to post to Blogger to which I have been posting a large number of posts, but the Microsoft UI comes with a message that it cannot contact the provider. I assume it is going to be fixed.

It will be a nice feature, if this can be made to work  easily. It can embellish the blog and most of the typos can be resolved before publishing.

Well how do you post to Blogger from Word?

I followed the procedure outlined here to create a post. I did not completely succeed due to the above reason and hope it will be cleared. I will of course update this post when that happens. The other problem seems to do with how to manage images, how to manage videos, etc.

Open Office 365 (after April 2016 update) Word document and go to file to display the following:

BlogPost_01

Click Share to open the Share pane to display; click the navigation for Post to blog. You can see couple of blog provider options to which you can post.

 
BlogPost_02

Click on the icon Post to Blog to open the following. If it is the first time you need to register the blog accounts:

BlogPost_03

Click Register Now. You will need to provider the blog provider you are using.

BlogPost_04

My blog provider is 'Blogger'. If you do not have a provider or it is not listed, you can use the links on the above to create one.

Click on 'Blogger' in the drop-down. It gets entered and the screen changes as shown.

 
BlogPost_05

Click Next to open the username/password for you blogger account as shown.


 BlogPost_06

You need to enter your credentials for the Blog provider (Blogger). As to Picture options there are only two options, don't upload pictures or provide the address of My Server. 

Just to test how far I can go, I choose the option not to worry with images by choosing this option.

 
BlogPost_07
This is where the application needs to be improved! If I create a post with images inserted, the blog should be posted with the post with images. If I need to contend with yet another service, it gets complicated.
Also there appears to be some problem with registering.

After inserting credentials and clicking OK you may get this message.


BlogPost_08




I take the remedy suggested by Microsoft to go to my Blogger site here and enable LessSecureApps as shown:


BlogPost_09

However, I still get the same registering failed message.

Once the registering is  resolved, I may need to find a solution for the images.

What is a temporal table in SQL Server?

A temporal table helps you to access historical changes to the content of the table. It is primarliy for Azure SQL Database table but it can also apply to SQL Server 2016. In fact it is one of the new features of SQL Server 2016.

This is what a temporal table in SQL Server 2016 according to Microsoft:

"SQL Server 2016 introduces support for system-versioned temporal tables as a database feature that brings built-in support for providing information about data stored in the table at any point in time rather than only the data that is correct at the current moment in time. Temporal is a database feature that was introduced in ANSI SQL 2011 and is now supported in SQL Server 2016
"

This is how it looks like in Object Explorer with the temporal table using an anonymous history table:

 TempTbl_00

Notice the necessity of a history table, and the extra columns to keep track of time line of content.

While you can create a new temporal table you can also modify existing table to be temporal.

How do you get at the details of the XML content in R?

In a 2006 post regarding getting the innards of an XML document, I had used Microsoft.XMLDOM as an ActiveX object.

The Document is the root of an XML Document and has constituent parts, Element, Node, Attribute and Text. In the present post I am using the same XML document I probed in my previous posts shown here.

In a recent post I showed how to parse (see here as well)  an XML document using R. In this post I describe how to get the 'innards' of an XML document using the function xmlSApply().

With these three lines of statements in R you can parse the XML document here and here.
> library(XML)
>fileName="C:\\Users\\Jayaram\\Desktop\\SQLServer2016D\\R Server\\Mystudents.xml"
> xmlFile

The xmlFile has the document.

The document (r) is the root of the XML document and you get it using:
-------------
> r < -xmlRoot(xmlFile)
-----------<-xmlroot br="" xmlfile="">
Now you can look at the details of the elements as shown here using xmlName, xmlValue, xmlAttrs and xmlChildren:
---------
> xmlSApply(r[[2]], xmlName)
         name   legacySkill
       "name" "legacySkill" --------------------
> xmlSApply(r[[1]],xmlValue)
           name     legacySkill
  "Linda Jones" "Access, VB5.0" ------------
> xmlSApply(r[[1]],xmlAttrs)$name
NULL
$legacySkill
NULL
---------------
> xmlSApply(r[[1]], xmlChildren)
$name.text
Linda Jones
$legacySkill.text
Access, VB5.0
>

Which of these online translators are good, Google, Bing or Babylon?

Most of them can do well, some better, some worse. It also depends on what language you want translate from/to. The better ones can auto-detect the language and for some of them you need to specify the source and destination languages.

In my recent experiment, I tried to translate a few lines from a receipt in Czech. Google did definitely better than Bing or Babylon. Babylon cannot auto-detect. Here are the results which is easy for you to rate them.

Bing

Some correct and for some no clue. The online tool should correctly recognize end of line.

Google

Perfect score!

Babylon

OK but does not recognize end of line

How can I parse an XML file in one of my folders in R programming?

In the previous post, the file was placed on the local internet server and a URL reference was given. It does not have to be that way. The file can be in the folders/files and you just need to change the code a little bit as shown. I am using the same file I used in the previous post.

These are the three lines needed to parse the XML file in the following directory:
------------------
> library(XML)
> fileName="C:\\Users\\Jayaram\\Desktop\\SQLServer2016D\\R Server\\Mystudents.xml"
> xmlFile <- filename="" font="" readlines="" xmltreeparse="">
-------------
And this produces the same result as in the previous post.

---------
$doc
$file
[1] ""
$version
[1] "1.0"
$children
$children$root

 
  Linda Jones
  Access, VB5.0
 
 
  Adam Davidson
  Cobol, Mainframe
 
 
  Charles Boyer
  HTML, Photoshop
 
 
  Charles Amos
  Cobol, Mainframe
 
-------------------
Follow the warnings in the previous post. Also make sure that the file name is typed in as shown in the post (note the double slashes).

How to parse a XML document in R?

This post shows how to parse a XML document in R. I often use the following simple xml file for my posts and examples. Please find some precautions at the end of this post.

MyStudentsXMLDoc

If your xml doccument is not on the local server, you can easily place it on the server by copying and pasting the xml document(file) to the local server root (inetpub/wwwroot).

The steps to parse use the xml function xmlTreeParse as shown by the following:
The program uses the readLines() function to read the document as shown and you code these after launching R Studio or R Gui.

MyStudnetsXMLDoc_2

You immediately get the following response as shown here:

$doc
$file
[1] ""
$version
[1
$children
$children$wclass

 
  Linda Jones
  Access, VB5.0
 
 
  Adam Davidson
  Cobol, Mainframe
 
 
  Charles Boyer
  HTML, Photoshop
 
 
  Charles Amos
  Cobol, Mainframe
 

attr(,"class")
[1] "XMLDocumentContent"
$dtd
$external
NULL
$internal
NULL
attr(,"class")
[1] "DTDList"

1. R programs are case sensitive and pay attention to how they are typed-in
2. The Mystudents.xml file should have a final carriage return. If not you will end up with an error:
 incomplete final line found on 'http://localhost/Mystudents.xml'