Monday, March 27, 2017

SQL Server PolyBase engines starts and stops in SQL Server 2016 - Part 2

Tried various suggestions from the SQL Server 2016 forum. However none of the checks / measures reveal the reason for this behavior.
According to documentation one can confirm whether Polybase is installed or, not by running the query:

SELECT SERVERPROPERTY ('IsPolybaseInstalled') AS IsPolybaseInstalled;

You should get 1 if it is installed, or 0 if it is not.


However, the problem arose when there was a third answer to the above query.


The above query runs and the response is as shown.



Immediately after this you get a screen to save the response. If you do indicate a location to save the response, the program saves it to a file with the extension .rpt.

The report file can be opened with Notepad which is just the message in the response to this query shown above.

While this problem was being examined on the machine there was an update to the OS on March 23, 2017.

This morning when I tried again to start the Polybase server, there was no problem. It started without any error messages.

However, after making sure the Polybase was up and running, I tried the 'IsPolybaseInstalled?' query and I still get the same third option.



Creating a database in Ultralite 17 using SQL Central

In this post I describe the steps to create a database with all the default settings using the SQL Central interface. If you are new to SQL Central (was previously called Sybase Central) you may want to read my older posts here.

Launch SQL Central from All Programs under SQL Anywhere 17.
Click Tools and choose under UltraLite to create a database as shown.


Create_00.png

 Creating a database is driven by the Wizard which appears as soon as you click Create Database... in the above window. The following sequence of windows shows how the database is created. Although you can make many changes to the database settings, all defaults are used in this post.

You start with the welcome window. Read the notes here.


Click Next,


Use the Browse... button to find the location for the database you are going to create and provide a name. Herein the name is Mar25_2017.

Click Next. Accept the default storage settings.

 Click Next.
Next choose the Collation and character set. Herein Ultralite defaults are accepted.

 Click Next.
Accept the default creation parameters. Have a look at what they are (these are Ultralite defaults).
You can change any or all of them.

 Click Next.
You may leave this as is. Synchronization is important for synchronizing the UltraLite with the main server. For the moment you may leave this empty.


 Click Next after placing a check mark for connecting to the new database. This makes the database available in the SQL Central Management interface.


 Click Next. Oh! you get the command line way of creating this database. What the wizard delivered is the execution of the command line with the parameters (Switches) shown here. This is neat. Next time around you may just use the command line.


 Click Close. The database is created and now appears in the SQL Central under Ultralite 17 (Compare this to the very first image in this post)


Here is the Properties page for this database. Presently it has objects such as Tables, Users etc.


Saturday, March 25, 2017

Installing Report Builder for SQL Server 2016

Download Report Builder 3 for SQL Server as described in my previous post .

You will have downloaded the ReportBuilder.msi file published on 3/9/3017. Note that there are other Report Builder.msi related to other versions (for example ReportBuilder.msi for SQL Server 2014 etc).

The installation is driven by a wizard and it installs without any problem. You need not indicate a Reporting Services Report Server during installation.



Here are screen shots of the installation:

Welcome Screen


License agreement window. Click I Accept...


 It will be installed on the local hard drive. Note that this product may also work with SQL Server vNext CTP1.3.


SQL Server 2016 reporting services has two modes and you need not have these installed and configured. You may keep this empty. Click Next.


Click Next and you are ready to install.


 Click Install. Begins installing with a progress screen.


 It is done installing.


 You should see these in All Programs on Windows 10.


Double click and you are ready to Author a report. If you want to put it out on the Report Server, you need a Report Server.

You are ready to go!

Importing a CSV file into SAP's SQL Anywhere 17 - Part 2

An error in import is corrected in this post.

In my previous post on the above I did not correctly associate the date related fields with the correct data type. The correct date and time related fields must be associated with the 'timestamp' field. This particular data type is not present in SQL Server which lead to this incorrect assumption.

In the following image you should scroll and find timestamp to associate with data type.


Sap_14.png
Once you do this association and follow through the wizard you end up with the correct result as shown.


Sap_13.png

You can check the following generated code in the Interactive SQL by steeping back on the query using the right and left arrows at the top.


Sap_15.png

CREATE TABLE "DBA"."FFD_Mar25_2" ("ProductName" varchar(254) NULL,"UnitPrice" smallmoney NULL,"Quantity" tinyint NULL,"OrderDate" timestamp NULL,"RequiredDate" timestamp NULL)

--  Generated by the Import Wizard
input into "DBA"."FFD_mar25" from 'F:\\FFDL.csv' format text escapes on escape character '\\' delimited by ',' encoding 'Cp1252' skip 1


I thank John Smirnios of

Friday, March 24, 2017

Timestamp data type in database servers


Timestamp data type is one of the data types that can create problems which migrating data from one kind of server to another. Although most database products consider timestamp as a data type, Microsoft SQL Server does not consider it as a data type. Some databases just store date information in text, integer and real.

Review the following taken from various database products:


SQL Server:
SQL Server 2000. timestamp is a data type that exposes automatically generated binary numbers, which are guaranteed to be unique within a database. timestamp is used typically as a mechanism for version-stamping table rows.

MySql:
The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

Oracle:

 
Timestamp.png


DB2:

A timestamp is a seven-part value representing a date and time by year, month, day, hour, minute, second, and microsecond, in the range of 0001-01-01-00.00.00.000000000 to 9999-12-31-24.00.00.000000000 with nanosecond precision. Timestamps can also hold timezone information.

PostgreSQL:
The timestamp data type allows you to store both date and time. However, it does not have any time zone data. It means that when you change your database server’s time zone, the timestamp value stored in the database does not change.

The timestamptz data is the timestamp with time zone. The timestamptz is a time zone-aware date and time data type. PostgreSQL stores the timestamptz in UTC value. When you insert a value into a timestamptz column, PostgreSQL converts the timestamptz value into a UTC value and stores the UTC value in the table.

SQL Anywhere 12:
The fraction is stored to 6 decimal places. A TIMESTAMP value requires 8 bytes of storage.

Although the range of possible dates for the TIMESTAMP data type is the same as the DATE type (covering years 0001 to 9999), the useful range of TIMESTAMP date types is from 1600-02-28 23:59:59 to 7911-01-01 00:00:00. Before and after this range the time portion of the TIMESTAMP may be incomplete.

When a TIMESTAMP value is converted to TIMESTAMP WITH TIME ZONE, the connection's time_zone_adjustment setting is used for the time zone offset in the result. In other words, the value is considered to be "local" to the connection. When a TIMESTAMP WITH TIME ZONE value is converted to TIMESTAMP, the offset is discarded.

SQLite:
No timestamp. Even date/time are stored as Text, real or integers
"SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:

    TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
    REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
    INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC. "

Netezza:
Type_name       DataType              Precision   Nullable   Case Sens   Maximum Scale
DATE               91 [DATE]           10               true           false            0
TIME               92 [TIME]            15               true           false            6
TIMESTAMP  93 [TIMESTAMP]26              true           false            6

Download Report Builder for SQL Server 2016

Report Builder is a highly productive, stand-alone tool for authoring reports and deploying them on configured Microsoft Reporting services report servers. It can use data from different kinds of data sources to produce  a variety of reports, from hard copies to web based reporting.

Report Builder 3 Details:
--------------
Version:
14.0.344.82
File Name:
ReportBuilder3.msi
Date Published:
3/9/2017
File Size:
22.6 MB
-----------------

You need Microsoft .NET Framework 4.5
. The memory and CPU requirements are average.

This is supported on  the following OSs:
Windows 10 , Windows 7, Windows 8, Windows 8.1, Windows Server 2008 R2, Windows Server 2008 R2 SP1, Windows Server 2012, Windows Server 2012 R2

download from here:
https://www.microsoft.com/en-us/download/confirmation.aspx?id=53613

Please review the earlier versions of Report builder in two of my published books here:


The second book Learning SQL Server 2008 Reporting Services is ideal for beginners who want to spin-out reports from a variety of data sources and deploy them to report servers. The book is replete with step-by-step, hands-on exercises where the users can hone their skills.


 
The fifth book Learning SQL Server Reporting Services 2012 is a revised edition and a deals with both Native and SharePoint Integrated modes of Report Servers. This covers every aspect of the first edition(second book) with lots of added material to include development in the intervening years.

  As the author of these Microsoft Business Intelligence related books I have great confidence in their usefulness. I would love to hear from you. Email me at: hodentek@live.com.

Saturday, March 18, 2017

Querying a SQLite database using the RSQLite Package

SQLite is a server-less SQL Database that does not require a separate server process. You do not need a configuration (Zero configuration) and it does not need any administration. The whole database is stored in a single cross-platform disk (or on your mobile device). It is very popular and widely used with mobile applications as the database is stored in the device and can be used without network connection. No external dependencies.

RSQLite is a R Package that is useful in manipulating a SQLite database. The Package can be downloaded from on of the CRAN mirrors. The one I got was from a mirror in France.
When you install the package you will be installing the following:


Packages.png

The RSQLite package has a built-in database with many tables called datasets db which is actually a file called datasets.sqlite.

In a previous post I described in detail creating a SQLite database table  using Visual Studio 2015 Community and an extension. The database created has an extension .db.

SQLite database can be extension .db or .sqlite. The database created using the Visual Studio 2015 called Sept27-2016.db was renamed Sept27-2016.sqlite.

After installing the package you can run the following code in RGUI to connect to the database created using Visual Studio by indicating the path to the .db file as shown.

---------------------
library("RSQLite")
Warning message:
package ‘RSQLite’ was built under R version 3.2.5

> db <-dbConnect(SQLite(),dbname="C:\\Users\\Jayaram\\Documents\\Blog2017\\MSSS\\SQLite\\RSQLite\\Sept17-2016.sqlite")

> dbListTables(db)
[1] "Sept22"
>
---------------------
Once you get the table, you can query as shown:

> dbReadTable(db,"Sept22")
[1] Id    Fname LName Age 
<0 rows> (or 0-length row.names)
> dbGetQuery(db, "SELECT * from Sept22")
[1] Id    Fname LName Age 
<0 rows> (or 0-length row.names)
---------------
Here is the table created in Visual Studio 2015 which has no data in it at present.