Saturday, July 30, 2016

Dynamic SQL and error messages

Dynamic SQL is a batch of SQL statements that you can run my 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

In this post I am not going into any of the above which require more space and time. Rather I try to look at the simplest of the examples where I have observed some problems and seen some error messages.

Let me first give an example which is often used to demonstrate the use of Dynamic SQL.


The first line of code declares a vaibale @cmd of data type ccharacters

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

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 seelct satement that selects the top 5 rows from the Person.Address table.

You can see that this dynamic SQL returns the results requested.
Basically this is how it works.

Now let me take a look at a little more basic example  to see how this execution takes place.

Let us consider this example to see if it works.

Declare @num as int = 12;
Declare @cmd as int;
Set @cmd='Select @num*@num*@num'
Execute @cmd

My expectation is it would return the (column) 12X12X12

However it returns the following error message:


Good. It only means that procname which in this case is @cmd can only be of type char, varchar, or nvarchar

The syntax is very critical.

The following statement which has just a parenthesis for the execute statement produces a different error message a shown.


Let us consider another example which is same as above but working with strings. Although the @wrd variable has be declared and set , there is still this unexplicable error.


This is one of common errors that people encounter dealing with dynamic SQL but the explanation may be different. I am still on the look out for a cool and reasonable explanation. You may observe that the statement 'Select @wrd' which is commented out does produce the correct result using the declared variable @wrd. beats me!

Read the MSDN article here:

Friday, July 29, 2016

You need this to connect to SQL Server 2016

Applications need access to data on the SQL Server. One of the methods is ODBC which stands for Open DataBase Connectivity, a standard way of accessing data using an intermediate piece called the driver. If you have a ODBC driver you can access any data from any application.

If you are writing an application to access data on SQL Server one of the easy ways is to go via using ODBC by creating a ODBC DSN which the application uses. This post is about creating a User DSN to connect to SQL Server 2016.

Just follow the steps from here on till the end of this post.

Click ODBCin the search box and pick up ODBC Data Source (x64) from the choices.


Click ODBC Data Sources(x64)

The ODBC Data Source Administrator (x64) windows opens as shown.


By default it opens in the UserDSN tab. We will create a UserDSN which limits us to this computr and this user (in this case, current user who has logged into this computer). Presently there are only two User DSN's.

Click Add to add a new UserDSN in this window. Create New Data Source window opens as shown.


The ODBC Driver 13 for SQL Server is the one you should click. It points to the DLL, MSODBCSQL13.dll

Click Finish.

Create a New Data Source to SQL Server window gets opened as shown.


You need to provide three pieces of information.
Which SQL Server do you want to connect to?:

Name can be your choice, here HSQL and description is optional; DSN for SQL Server 2K16

After entering these click on the handler for the next question, if there are no drop-down options it means there are no working SQL Server 2016 instance. Stay on this window and launch SQL Server 2016 from the start menu.


I assume this functionality of the wizard to bring up the instance of SQL Server on the computer in the drop-down list is lacking. It is suppsoed to discover the SQL Server.

I made sure that SQL Browser is running.


Click Next after entering the SQL Server Instance name for the Server in the format <computerName>\Instance name.


The next window gets displayed where you need to enter the authenticaiton information as shown.


Since windows integration was chosen during installation just click Next. The following window gets displayed.


Just accept the defaults for now unless you want to change some. Click Next.


In the next window you accept all the default choices

Click Finish.

Summary screen shows up as shown.


You can test the DSN by clicking Test Data Source... .

The image shows the cconnectivty was tested. For this test to be successful the server should be up and running.


Click OK.
Click OK.

Now you have the HSQL in the UserDSN tabbed page of the ODBC Data Source Admisnitrator as shown.

Click OK to say good bye to the administrator window.

This is what you need connecting to SQL Server 2016 from R

Once you have this you can connect to SQL Server 2016 and do statistics on the stored data.

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:
constrg <- database="AdventureWorks2014;" font="" river="SQL" server="Hodentek8\\OHANA;" trusted_connection="true">

If you have UserId (Uid) and Password (Pwd) with appropriate permissions you may use the following:
constrg2 <- color="#ff0000" river="SQL" server="<<font">your server&gt;; Database=AdventureWorks2014; Uid=; Pwd=";

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:
Note 3: Keyword are case sensitive

Saturday, July 23, 2016

Problem enabling R Service in SQL Server 2016

In my previous post I have compeltely described the installation of SQL Server 2016 Developer's edition during which the R Server was also installed as shown here:


In order to run R in SQL Server 2016 you need to use the sp_execute_external_scripts stored procedure. In order to do use this, you need first set the external scripts enabled option, which by default is OFF.

The suggested way is to configure by running the followwing statement:
sp_configure 'external scripts enabled', 1; 
When run on the named instance on which R Server is also configured (Hodentek8\Ohana) we see the following result:


The second line in the above statement does not seem to have any meaning since Reconfigure is the second line of the executed statement.

Now If I ignore the inconsistency and go ahead and run this suggested statement:
exec sp_execute_external_script  @language =N'R',   
@input_data_1 =N'select 1 as hello'   
with result sets (([hello] int not null));   

I get the following error.
Msg 39023, Level 16, State 1, Procedure sp_execute_external_script, Line 1 [Batch Start Line 6]
'sp_execute_external_script' is disabled on this instance of SQL Server. Use sp_configure 'external scripts enabled' to enable it.

Hence, I believe there is a problem.

Friday, July 15, 2016

Some problem of SUSER_NAME in SQL Server 2016

Which of these is the current user?

The syntax is:
SUSER_NAME ( [ server_user_id ] )
where server_usr_id is the login identification number of the user. It is optional but when used it is of data type int.

server_user_id can be the login for:
  • Any SQL Server login
  • Microsoft Windows user
  • Microsoft Windows User Group that has permission to connect to SQL Server.
If the server_user_id is not supplied, then the current user is returned.
Here are SUSER_NAMEs for a number of server_user-ids:

It works funny in SQL Server 2016 Developers edition on Windows 10 professional.
which is obviously wrong since the current user is:

How does one reconcile with the above?

Also how does one reconcile with the following statement and the results returned.

Friday, July 8, 2016

Temporal tables in SQL Server 2016 to track history

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:


Notice the necessity of an history table, and the extra columns to keep track of time line of content. The temporal table and the history table has the same schema which means schema modification would not be possible.

There are three ways to create temporal tables depending on how the history table is created:
Anonymous history table
default history table
user-defined history table

While you can create a new temporal table you can also modify existing table to be temporal.
If you want to use an existing table as temporal table you will use the Alter Table to add the necessary temporal attributes.

Read more here.

Monday, July 4, 2016

New update to SQL Server Management Studio (July 2016)

This is an update to the SSMS. This post describes the previous update.

The following change log shows the changes made in this update as itemized on Microsoft site. The bug fixes, quite a few of them have also been applied. Go to this URL to view the complete document as well as to download this update. If you already had the previous version, you may be asked to upgrade to this version when you launch SSMS.


  • Support for Azure SQL Data Warehouse in SSMS.
  • Significant updates to the SQL Server PowerShell module. This includes a new SQL PowerShell module and new CMDLETs for Always Encrypted, SQL Agent, and SQL Error Logs.
  • Support for PowerShell script generation in the Always Encrypted wizard.
  • Significantly improved connection times to Azure SQL databases.
  • New ‘Backup to URL’ dialog to support the creation of Azure storage credentials for SQL Server 2016 database backups. This provides a more streamlined experience for storing database backups in an Azure storage account.
  • New Restore dialog to streamline restoring a SQL Server 2016 database backup from the Microsoft Azure storage service.
  • Improved support for SQL Server 2016 (1200 compatibility level) tabular databases in the Analysis Services Process dialog.

Sunday, July 3, 2016

Setting up a table with hierarchical data in SQL Server 2014

Regarding hierarchical data in SQL Servers please review this post .

Here is an example of hierarchical data:


This post shows how to create a table; populate and query the table containing hierarchical data.

The data shown in the above figure can be placed in a SQL Server Table as shown which is displaying the graphic user interface table designer in SQL Server 2014.


This table FamilyHierarchy has three columns (Level in the hierarchy, Name and the hierarchy type) and can be populated using the following script:

Insert FamilyHierarchy
(/1/, 'John_Mary', 'parents'),
(/1/1/, 'Tom', 'Children'),
(/1/2/, 'Joan', 'Children'),
(/1/3/, 'Sam', 'Children'),
(/1/1/1/, 'Lily', '2ndGen'),
(/1/1/2/, 'Jim', '2ndGen'),
(/1/1/3/, 'Beth', '2ndGen'),
(/1/2/1/, 'Rose', '2ndGen'),
(1/2/2/, 'Tim', '2ndGen'),
(1/3/1/, 'Warren','2ndGen')

The table gets populated and can be queried as shown.