Create SQL Server 2016 ODBC DSN using Report Builder

ODBC source is one of the many sources that you can connect to in Reporting

Services using the Report Builder.

Here are the steps. It is assumed you have SQL Server 2016 SP1 installed; Report Builder is also

installed on the same machine. For these steps it is assumed that the SQL Server is up and running

and you are the owner of the computer who installed the SQL Server.

1. Launch Report Builder with Administrative Privileges.

2. Right click Data Sources and Click Add DataSource... to bring up the Data Source properties window.

3.  Provide a name for the data source, DS_ODBC

4. Choose Use a connection embedded in my report

5. Select connection type: click handle and choose ODBC at the bottom of list

6. Click Build... to bring up the Connection properties window

7. In the Data source specification section choose Use connection string:

8. Click the Build... button that gets activated to display Select Data Source window

9. Click Machine Data Source

10. In Machine Data Source tabbed page, click New... to display Create New Data Source

11. Accept the default, User Data Source and click Next

12. In the Select a driver for which you want to set up a data source, scroll down and

    select SQL Server (SQLSRV32.dll); click Next to display the next window

13  Click Finish. Create a New Data Source to SQL Server is displayed

14. Provide a name of your choice (ODBC_DSN); Description:(ODBC for Report Builder); Server

    use handle to locate: Hodentek9\OHANA. Click Next

15. Accept the default in the displayed page: with Windows NT authentication using the

    Network login id. Click Next

16. Change the default database by clicking handle to Northwind. accept all other

    defaults. Click Next

17. Accept all defaults on the displayed page. Click Finish

18. In the ODBC Microsoft SQL Server Setup page test the connection and make sure it works. Click OK

19. ODBC_DSN enters the Machine Data Source tabbed page. Click OK

20. SQL Server Login window gets displayed. Enter creator owner's credentials

21. Copy the connection string from the connection properties window.

Dsn=ODBC_DSN;description=ODBC for Report Builder;trusted_connection=Yes;app=Microsoft SQL Server;wsid=HODENTEK9;network=DBNMPNTW

22. Test Connection to make sure it works

23. Click OK

The Connection string enters the Data Source Properties window as shown.

Bacpac file on GitHub can also be used to construct Wide World Database on SQL Server 2016

In my previous post I showed you how easy it is to bring the Wide World Importers (WWI) database into a named instance of SQL Server 2016 SP1 on your computer(desktop / laptop).

In this post I describe how you may construct the database on the named instance using a BacPac file.
If you have not used this kind of file please review the following link here.

In what follows I describe using the .bacpac file to construct the WWI database
Go here:
https://github.com/Microsoft/sql-server-samples/releases/tag/wide-world-importers-v1.0

Get this bacpac file:
Azure SQL Database Standard tier

    WideWorldImporters-Standard.bacpac - standard edition OLTP sample database in bacpac format. For Azure SQL Databases in the Standard tier.

Download the 58.4 MB file from the above site.


SQL Server 2016 SP1 like the other versions has a wizard to bring the database using a .bacpac file such the one you downloaded.

You invoke the wizard as shown in the next image and follow through.

bakpacWWI_01.png

Click Import Data-tier Applicaiton... to open the window shown in the next image.  Read the steps in this introductory page.

bakpacWWI_02.png

 Click Next. You click the Browse... button to locate the  indicated file. This is the location to which I saved the WideWorldImporters-Standard.bacpac file. 
It does not matter it is for Azure SQL Database.

bakpacWWI_03.png

After getting the file location from the location as shown in the next image proceed to next step.

bakpacWWI_04.png


After getting the file location in the wizard's interface (the image one above the previous) click Next.

bakpacWWI_05.png

 Just verify that the location for files are what you want. Herein defaults are accepted. You could browse and park elsewhere.

The database name was changed to WWI_Bacpac.

Click Next. The Summary of processing is presented as shown.

bakpacWWI_06.png


Click Next. The next four images shows the progress captured in images. Indexes are dropped and created, etc.

 bakpacWWI_07.png

 bakpacWWI_08.png

 bakpacWWI_09.png

bakpacWWI_10.png

Object explorer refreshed - last processing step.

The WWI database is imported successfully.

Here is the final verification of the import into the named instance.

 

bakpacWWI_10.png

This method of importing the database appears to be slower than the one in the previous post. as there is a lot of processing related to deconstructing and reconstructing the indexes.

Get SQL Server 2016 SP1 before you construct WWI

Getting the SQL Server 2016 SP1:

 I wanted to construct the Wide World Imports database on SQL Server 2016 and found that SP1 is a requirement. Hence, I needed to first apply SP1 to the existing instance.

Go to this link, select the language and click Download

The file name is ENU\SQLServer2016SP1-KB3182545-x64-ENU.exe (551.8 MB).
This SP1 contains SQL Server 2016 RTM Cumulative update 3(CU3).

The supported OS are:
Windows 10 , Windows 8, Windows 8.1, Windows Server 2012, Windows Server 2012 R2, Windows Server 2016

  For complete system requirements, please reference the detailed Systems Requirements page
        6 GB of available hard disk space for the Service Pack update, 10 GB for a Slipstream installation
        Service Pack 1 can be applied to any of the following Microsoft SQL Server 2016 editions:

            Microsoft SQL Server 2016 Enterprise
            Microsoft SQL Server 2016 Developer
            Microsoft SQL Server 2016 Standard
            Microsoft SQL Server 2016 Web
            Microsoft SQL Server 2016 Express

Run (as Administrator) the downloaded file. In the beginning I had some problem and ran the compatibility program. Here are some of the screen shots. It tool quite a while with the R Service related step and the final updating step, otherwise it was without any trouble.

 

 

 

About in SSMS 2016
Microsoft SQL Server Management Studio                        13.0.15600.2
Microsoft Analysis Services Client Tools                           13.0.1605.86
Microsoft Data Access Components (MDAC)                   10.0.14986.1000
Microsoft MSXML                                                              3.0 6.0
Microsoft Internet Explorer                                                 9.11.14986.0
Microsoft .NET Framework                                                4.0.30319.42000
Operating System                                                                6.3.14986
----------------------------------------------

Get moving installing SQL Server 2016 SP1

SQL Server 2016 Service Pack 1 is released  and now generally available(GA).

According to the above release blog:

Many of the features (listed in the table here) available in Enterprise Edition are now available in several other editions as well after installing the SP1 (Standard, web, express and LocalDB).

SQLSrvrSp1_00

Note that Change Data Capture requires SQL Server Agent not available in Express and In Memory OLTP not possible in LocalDB due to security reasons

DBCC CLONEDATABASE added supports cloning of CLR, Filestream/Filetable, Hekaton and Query Store objects are available after installing SP1

DBCC CLONEDATABASE  (source_database_name, target_database_name)    –– Default CLONE WITH SCHEMA, STATISTICS and QUERYSTORE metadata.
DBCC CLONEDATABASE  (source_database_name, target_database_name) WITH NO_STATISTICS                                                                                               –– SCHEMA AND QUERY STORE ONLY CLONE
DBCC CLONEDATABASE  (source_database_name, target_database_name) WITH NO_QUERYSTORE                                                                                              –– SCHEMA AND STATISTICS ONLY CLONE
DBCC CLONEDATABASE  (source_database_name, target_database_name) WITH NO_STATISTICS,NO_QUERYSTORE                                                          –– SCHEMA ONLY CLONE
New Create/Alter supports modifying and deploying database objects such as
Stored Procedures, 
Triggers, 
User–Defined Functions, and 
Views

This is just a short list from more than a dozen others. Make sure you visit the link referenced at the top.

The SQL Server 2016 Enterprise specific sample database, WorldWorldImporters can now be used in Standard and Express editions.

Download SQL Server 2016 SP1 here .