Monday, June 27, 2016

DMV Queries: To Get All Attributes in a cube

Use below Dynamic Management View (DMV) queries to get all the dimension and attributes of your cube

SELECT
       [CATALOG_NAME] as [Database],
       CUBE_NAME AS [Cube],
       [DIMENSION_UNIQUE_NAME] AS [Dimension],
       HIERARCHY_DISPLAY_FOLDER AS [FOLDER],
       HIERARCHY_CAPTION AS [DIMENSION ATTRIBUTE],
       HIERARCHY_IS_VISIBLE AS [VISIBLE]
FROM
       $system.MDSchema_hierarchies
WHERE 
       CUBE_NAME  ='My Cube Name' AND 
       HIERARCHY_ORIGIN=2
ORDER BY
       [DIMENSION_UNIQUE_NAME]
 
Note: Replace 'My Cube Name' with your cube name

Wednesday, June 22, 2016

Fix: Unable to create the type with the name 'SPCRED'

Problem:
I tried to export one of the SSIS packages from Integration Services catalog. I got the below error
 
"Unable to create the type with the name 'SPCRED'. (Microsoft.SqlServer.ManagedDTS)"
 
Solution:
When I explore this issue, found that the package extracts data from SharePoint list. but we don't have inbuilt component in SSIS to fetch the data from SharePoint. So We need to use third party connecter which is exists in CodePlex "SharePoint List Source and Destination"
 
 
after Install this connector, I can export the  SSIS packages from Integration Services catalog without any issues.
 
Also, you can see this connector added in SSIS connection Manger
 
Note:
  • If it is not appeared in connection manager then you've to restart your Visual studio if it opened
  • If not appeared in connection Manager after restart your VS, then the version you installed is not compatible with your VS, so choose the Proper version and install it.

Tuesday, September 8, 2015

Introducing JSON for SQL Server 2016

Problem

On my current project, there is a requirement to extract data, which is stored in one database (D1) and load it into another database (D2). That should be simple, right? But, the client is not ready to give access to the D2 database due to confidential data. We have to push data to the D2 database via the REST API which accepts JSON as an input. We are trying to find an option to convert the data into JSON format at the database level. Unfortunately, SQL Server does not support converting data into JSON format. To resolve this issue, we developed a .NET utility to convert the data to JSON format with the help of the “JavaScriptSerializer” class then pushed the data to the D2 database via REST API I see SQL Server 2016 now supports JSON. Is it possible to replace our .NET utility? In this tip, we will demonstrate how use JSON in SQL Server 2016.

Solution

JSON support in SQL Server is one of the highly ranked requests in Connect #673824, which received 1000+ votes.

What is JSON?

To Continue Reading >> Please Refer My Articles at MsSQLTips.com

Wednesday, July 29, 2015

SQL 2016 - Skip Prompting Save

Problem
Sometime we may write a SQL script to verify the data or monitor any activities or adhoc tasks in database. In that case, We opens "N" number of Query windows and continue our activities in SSMS. We may not required to keep/save the scripts, but when we  close the windows we will get as many number of prompts for save/cancel the changes. also you will be forced to click each and every annoying save prompts.



Solution:
This Problem is resolved in SSMS 2016 release.

Microsoft has provided a new option to avoid prompting for saving unsaved files. 

To use this feature, Go to "Tools" Menu > Options > Query Execution > SQL Server > General > Un-check "Prompt to save unsaved T-SQL query windows on close"



Note:
You have to disable this option before open any query window, otherwise SSMS will prompt the save changes window at the time of closing


Warning:
Do not disable this option, if you do, you may lose valid scripts one day if you forget to save the changes when you close any script accidentally. so don't forget to enable this option after use.

Requested By:
A connect item was raised to add this feature by Erland Sommarskog, Refer 
Connect #308372, which replaced Connect #124686. It was denied by Microsoft earlier and closed the ticket stating as "Won't Fix", But due to Many People requests, Now Microsoft has introduced this feature in SQL 2016