Retrieve Column Names, Data Type from a table

A handy SQL query to retrieve column names, data type, and other info from a table.

SELECT

COLUMN_NAME,DATA_TYPE,

CHARACTER_MAXIMUM_LENGTH,

NUMERIC_PRECISION,

DATETIME_PRECISION,

IS_NULLABLE

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME='tablename'

Using Microsoft SSIS to access MySQL Server's data

This document will show you the steps in a situation when it is necessary to retrieve data from MySQL server into a Microsoft based data warehouse using SSIS, and viewing table schema using Microsoft SQL Management Studio(SMS).

From a Microsoft SQL Server it will access the data in MySQL server through a ODBC connection. A User DSN need to be created for SSIS ETL, and a System DSN need to be created for SMS.

In order to create MySQL DSN it is necessary to get MySQL ODBC drivers.

1. Download MySQL ODBC driver. Go to MySQL website, click on Download. Look for ODBC Connector.
http://dev.mysql.com/downloads/connector/odbc/5.2.html
Select the OS you have then download the client.
Once download complete. Double click on install package file to install it.

2. Create User DSN. After the MySQL ODBC driver installed. Go to Control Panel, Administrative Tools, ODBC Data Source Administrator. On User DSN tab, click Add..
Select MySQL ODBC 5.2 driver
An MySQL ODBC connection window opened. Enter the server name, database, user account and password to set it up.
Give a name of the User DSN connection.
You can the same steps to create System DSN as well.

3. Once a MySQL DSN is created it can be applied on SSIS package for ETL. Using Visual Studio SSIS package, in a Data Flow task, create ODBC source.
In SSIS Toolbox, under Other Sources, drag an ODBC Source into a developer window.
Right click on Edit, on the ODBC Connection manager field, click on New.. button
Click on New.. button again. on the Connection Manager window. click on the drop down box of "Use user or system data source name:" then search and select the DSN you created on step 2.
input user name and password, click Test Connection then click OK.
After that, tables in  the MySQL database is available for ETL by either select a table, view or write a SQL query to retrieve data.

Multiple Joins/Multiple Aggregations in Microsoft SSAS Tabular Model.

In Microsoft SSAS Tabular Model you can make multiple joins between fact and dimensions. The first join is shown as solid line. Any additional joins are shown as dash lines.

However in SQL Server 2012 Tabular Model is using only the first join for value aggregations. It is OK when the model in a simple star schema. However it will not be enough in conformed dimension.

For example: A fact order table has date_submitted and date_closed. both values joined to a Time Dimension table.
The goal is to aggregate data to report weekly, monthly aggregation based on date_submitted. But when attempt to count data based on date_closed the result are incorrect due to the tabular mode is using the first join only, but not the second join.

The solution is that in SSAS Tabular Model you can specify a aggregated value to use the second join. use the USERALTIONSHIP() expression to specify the join after the CALCULATE value.

Count of Date Closed:=CALCULATE(COUNTA('FactTableName'[ColumnName]),USERELATIONSHIP('FactTableName'[ColumnName],'DimensionTableName'[PK_Dim]))

      

Failed to create Timeline on Excel 2013 from SSAS 2012 Tabular Model: "We can't create a timeline for this report because it doesn't have a filed formatted as a Date"

Excel 2013 Pivot Table comes with Timeline feature which provide a visual date slicer to display data in a date range specified by users.



After a SSAS 2012 Tabular Model was built and deployed, which included a time dimension table, when you attempt to create a Timline but Excel failed the operation with the following error message:  "We can't create a timeline for this report because it doesn't have a filed formatted as a Date"

 

Although a time dimension was built and join within SSAS Tabular Model, the dimension table still need to be set as Date table.
1. Open the SSAS Tabular Model  in Visual Studio Development mode
2. Select the time dimension table, go to Table from menu bar.
3. Select Date, select mark as date table.

 
4. need to specify a primary key of the time table. then OK
5. Process, then redeploy the Tabular Model
6. Test it from Excel 2013 by create a Timeline.

 
For detail to how to multiselect dates in Excel 2013, please check out this web site:
http://thomasivarssonmalmo.wordpress.com/2013/03/03/multiselecting-dates-in-excel-2013-on-top-of-ssasbism-multidimensional/
End

  

Find Server Name, Database name from Tables

When querying data from multiple database across multiple server, a simple two lines of SQL code can tell where the data comes from 

SELECT
@@servername,
DB_NAME() as 'Database name'

FROM dbo.tablename

Microsoft Visual Studio 2010 Tools Box Empty and Recovery

It has been an known issue in the middle of debugging a SSIS package might cause Visual Studio to reset its Tools Box into empty.

It looks like you could right click the area and drag items back to the toolbox area. However most of the options are pointers and they are grey out.

You need to do something in order to cause Visual Studio to reset/rebuild the tool box into its default state. To do so you need to delete the backup entries files AND remove cache registry keys.

1. Close and exit Visual Studio 2010, using windows explorer go to "c:\Users\<your_user_name>\AppData\Local\Microsoft\VisualStudio\10.0" 

2. delete all *.TBD files

3. Click "Start", "Run" type REGEDIT to open registry editor. go to "HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\10.0\ToolboxControlsInstaller_AssemblyFoldersExCache" 
and "HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\10.0\ToolboxControlsInstallerCache"

4. Remove all sub keys as highlighted.

5. Open Visual Studio. open a project and wait for the Toolbox recover itself. then ready to use.

End

DECLARE: apply value in multiple places

 What if you need to specify a value such as date in multiple places in a query? Try DECLARE The following example using DECLARE so that you only need to change the date range at one place it will apply to both order date and ship date statements in the WHERE clause below.

DECLARE @qstartdate date, @qenddate date
            SET @qstartdate = '2012-01-01'
            SET @qenddate = '2012-01-31'

SELECT *
FROM dbo.ORDER_ENTRY order

WHERE
order.ORDER_DATE BETWEEN CAST(@qstartdate as DATE) AND CAST(@qenddate as DATE)

OR 

order.SHIPPING_DATE BETWEEN CAST(@qstartdate as DATE) AND CAST(@qenddate as DATE)

Spliting a Full Name data into First Name, Last Name columns

Quite often when working with a old database where a single column been used to store both first name and last name. Now we have to separate them into two columns.

The following code is an example of how to split a full name columns into first name and last name columns. 


SELECT

LEFT (EMPLOYEE_FULL_NAME, CHARINDEX(' ',EMPLOYEE_FULL_NAME))as 'Employee First Name' 

,SUBSTRING (EMPLOYEE_FULL_NAME, CHARINDEX(' ',EMPLOYEE_FULL_NAME)+1, LEN(EMPLOYEE_FULL_NAME)-(CHARINDEX(' ',EMPLOYEE_FULL_NAME)-1 ))

 as 'Employee Last Name'

 

FROM [dbo].[HR_Employee_table]

Find out free space from a SQL server

You can right click a database to find out its size in SQL Server Management Studio, but the most important question for DBAs is how much disk space left for the databases to grow?


Here is a quick one liner.



exec master..xp_fixeddrives

Microsoft Denali/Crescent: From installation to Report Building

Background

By the time this document is written (2011/09) Microsoft releases the CTP3 (Community Technology Preview) of the next version of SQL Server (coded name Denali) with the application add-on for SharePoint (coded name Crescent).

The following document provides necessary steps to build a test sandbox environment in order to demo the Crescent web based reporting feature. The sandbox environment consists of the following:

1.       Build on a virtual machine using Microsoft Hyper V of Windows Server 2008.

2.       Single machine will host  as Active Directory Controller, SharePoint 2010, and SQL Server “Denali”

Evaluation download can be found from the following web site:

“Denali” CTP 3(available to registered partners only):

https://connect.microsoft.com/SQLServer/Downloads/DownloadDetails.aspx?DownloadID=37147

SQL Server 2008 R2 Eval: http://msdn.microsoft.com/en-us/evalcenter/ff459612

SharePoint 2010 Eval: http://sharepoint.microsoft.com/en-us/Pages/Try-It.aspx

Windows Server 2008 Eval: http://www.microsoft.com/en-us/server-cloud/windows-server/2008-r2-trial.aspx

“Denali” sample databases: http://msftdbprodsamples.codeplex.com/releases/view/55330

Overview of the installation step:

a.       Create a Hyper V vm .VHD file with Windows 2008 as guest operating system.

b.      Configure Windows 2008 as Active Directory Domain Controller.

c.       Install SQL Server 2008 R2, Database Engine Services Only.

d.      Install SharePoint only DO NOT CONFIGURE.

e.      Install SQL Server Denali with Report add-on for SharePoint .

f.        Run SharePoint Configuration Wizard.

g.       Create SharePoint Services Application for SQL Server Reporting Services.

h.      Configure SharePoint Content Type to add SQL Server Reporting features.

i.         Test it by creating report.

a.       Create a Hyper V vm .VHD file with Windows 2008 as guest operating system

1.       Login to a Windows 2008 R2 Server  (must have 500GB free hard disk space and 24GB of RAM)

2.       Open Hyper V manager. Select New to create a new virtual machine (250~300GB hard disk space and 12~16GB of RAM)

3.       On the CD-ROM drive of VM, select a Windows 2008 Server CD media or .ISO file.

4.       Click start to start up the vm, Windows 2008 installation begins automatically.

b.      Configure Windows 2008 as Active Directory Domain Controller.

1.       When guest OS installation is completed.  Renamed the machine name (for example: mscrescent)

2.       Go to Server Manager to install the following roles: IIS web server, application server, AD services, DNS server, WINS server.

3.       Also in Server Manager, install the following features:  Desktop Experience,  .NET Framework 3.5.1

4.       Disable all firewall.

5.       Run DCPROMO to promo the vm as its own AD domain controller (for example: denali.local) it requires server restart.

6.       After server restarted, check to confirm server is now a domain controller by verify the computer name now should be mscrescent.denali.local.

7.       Go to Active Directory Users and Computers. Add two user account with domain admin rights: sqladmin (for SQL Server instance access account) and spadmin (for SharePoint Services Application access account)

c.        Install SQL Server 2008 R2, Database Engine Services Only.

1.       As a SharePoint prerequisites. A Sharepoint_Config database is needed to host SharePoint’s content data before installation. And it CANNOT be SQL Express.

2.       Install SQL Server 2008 R2 Trail (standard version is preferred).

3.       Select Custom installation.

4.       Select component to install. Select only the Database Services Engine, then Next.

5.       When Configure database instance window appear, specify user account sqladmin as a SQL database administrator.

6.       Let the installation run and complete.

d.      Install SharePoint only DO NOT CONFIGURE.

1.       Install SharePoint 2010, click on “install prerequisites” to install and auto-configure any necessary updates before the installation begins.

2.       Install SharePoint. SharePoint setup will go through a pre-installation rules check to verify all necessary updates and configurations are present. If Rules check is failed, follow the error message to install/configure updates if necessary.

3.       When ask to specify a database to host content database (SharePoint_Config), specify the SQL server name (which is local host) and default instance.

4.       When ask to specify a access account for SharePoint Central  Administration, browse and select the domain\spadmin account.

5.       Installation runs. When complete. A SharePoint Configuration Wizard Window opens. Uncheck the box for Begin Configuration Wizard. Just install SharePoint. Do not configure it.

6.       Let the SharePoint installation run and complete.

e.      Install SQL Server Denali with Report add-on for SharePoint .

1.       Extract SQL Denali software package. Install Denali

2.       On Installation dialog, click New SQL Server stand-alone installation or add features to an existing installation.

3.       Click Next, SQL Denali will process a series of support rules. If passed, click Next to continue the installation process.

4.       On the  Setup Role page, select SQL Server Feature Installation

5.       Select the following features (the minimum requirement for SharePoint add-on over SQL Server reporting “Crescent”)

1.       Database Engine Services

2.       Reporting Services – SharePoint

3.       Reporting Services Add –in for SharePoint Products

4.       Management Tools – Basic

5.       Management Tools – Complete

6.       Click Next on the Installation Rules page. If passed, click Next again to continue.

7.       Instance Configuration page open, accept the default instance of MSSQLSERVER (The goal is to make both SQL Denali and SharePoint 2010 using the same database instance.) click Next.

8.       Report Services Configuration page open, a default selection “Install only” should be selected. If not, select it. ( the goal is to make configuration wizard to be the last step to run after all components are installed.) click Next.

9.       Installation Configuration  Rules page will run again, please review and make sure all components are selected and installation requirement is passed. Click Next to begin Denali installation.

f.        Run SharePoint Configuration Wizard.

1.       Now the test server vm should have AD controller, SQL Denali and SharePoint installed. Login as domain admin, click on Start, All Programs, SharePoint Products, SharePoint Configuration Wizard.

2.       Modify Server farm Settings page opened, the database server name should be the local test server name (in that case: mscrescent. Just the host name should be fine. Should not use the “localhost” as server name)

3.       The Database name should be “SharePoint_Config”

4.       Click Next, select “This machine will host the web site”

5.       A complete list of configuration should be listed.  Click Advanced Settings to specify a TCP port for SharePoint Central Administration (default is 8080)

6.       Click Finish. SharePoint Configuration Wizard begins to run the configuration process. It will be long while.

g.       Create SharePoint Services Application for SQL Server Reporting Services.

Now the SharePoint server configuration database is up and running, the next step will be configure the SharePoint Report services.

1.       Click Start, go to Microsoft SharePoint  2010 Products.

2.       Right click on SharePoint 2010 Management Shell, click on Run as Administrator

3.       A command prompt window opened. Type the following then enter to install Report  Services:

Install-SPRSService

4.       Type the following then enter to install the services proxy:

Install-SPRSServiceProxy

5.       Go to SharePoint Central Administration, on the System Settings group, click on Manage Services on Server.

6.       Locate SQL Server Reporting Services Service, then click Start.

h.      Configure SharePoint Content Type to add SQL Server Reporting features.

First we will create a default site so users can access

1.       From SharePoint Central Administration, under Application Management group, click on Manage web applications.

2.       On the Web ribbon, click New

3.       Leave every setting as default. Under IIS Web Site check the radio button Use an existing IIS web site.  You can name the Host Header as “Crescent Demo”

4.       Under the Service Application Connections, make sure SQL Server Reporting Services is checked to select. Then click OK to create a default web site in SharePoint.

Next we will logon to our default web site to configure the SharePoint document content type.

1.       Logon to the SharePoint web site we just created (http://sharepoint-server-name/)

2.       On the left hand side of the site page, click on Shared Documents. Then click on Library ribbon. Click on Library Settings.

3.       Under General Settings group, click on Advanced Settings. Look for a radio button for “Allow management of content types? “  click Yes, then click OK.

4.       Back in Document Library Settings page. Scroll down to Content Types section, click “Add from existing site content types” then select all SQL report services content types (including Report Data Source, Report Builder Model and Report Builder Report) then click OK.

5.       Back in Content Types section, make sure the selected content types are “Visible on New Button”

i.         Test it by creating report.

There are multiple methods to create BI reports in SharePoint. BI reports are created based on published data sources from SQL server. Currently (CTP3) supports Analysis Service Tabular Project from Microsoft Studio 2010. So develop a tabular project and deploy it.

When a tabular project deployed into SharePoint, it will be saved in Share Documents as data source. The quickest way to create a report is to click on the data source, select  Create “Crescent” Report from the drop down list.

End