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.


D_SQL_00

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:


D_SQL_01

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.


D_SQL_02

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.

D_SQL_03

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:
https://msdn.microsoft.com/en-us/library/ms188332.aspx?f=255&MSPPError=-2147217396

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.

ODBC_00

Click ODBC Data Sources(x64)

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

ODBC_01

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.


ODBC_02

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.


ODBC_03

You need to provide three pieces of information.
Name:
Description:
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.

ODBC_04

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.

ODBC_06

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


ODBC_05

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


ODBC_07

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


ODBC_08

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


ODBC_09

In the next window you accept all the default choices

ODBC_10
Click Finish.

Summary screen shows up as shown.


ODBC_11

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.


ODBC_12

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:
-------------
Server=.;
-------------
Note 3: Keyword are case sensitive
l

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:


Rserver_00

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; 
RECONFIGURE;
 
============
When run on the named instance on which R Server is also configured (Hodentek8\Ohana) we see the following result:

EnableXternalScrpts_01

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',   
@script=N'OutputDataSet<-InputDataSet',     
@input_data_1 =N'select 1 as hello'   
with result sets (([hello] int not null));   
go

============
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.
SELECT SUSER_NAME() returns
MicrosoftAccount\hodentek@live.com
which is obviously wrong since the current user is:
Hodentek8\Jayaram



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:


TempTbl_01

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.

Changelog

  • 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:

Hierarchy_00

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.


familyHierarchy_00

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
Values
(/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.


familyHierarchy_01

I am not paying this invoice. I am disregarding it, becuase....

 I am not paying this invoice because MICROSOFT has not provided me with a satisfactory way to access my account since June of this year des...