How to download a facebook video?

Thursday, 8 May 2014

Create Linked Servers (SQL Server)

Scenario:
In an application there is a 'SSRS' report which is fetching data from 'SSAS Cube'. The cube refresh is happened every morning in the week. Once cube is refreshed then latest data is available to 'SSRS' reports. For user interface, this 'SSRS' report is integrated with .Net page, contains few filters.

Issue
Filters data should dynamic and available data range should based on data into 'SSRS' report. Some times it is observed that filters data is more than available data into 'SSRS' reports.

Example
Filter's data is available till December 2013 while in 'SSRS' data is present only till November. As filters data is dynamic so it is expected that filters data and 'SSRS' data range should be same.

Reason
It is happening because filters data is coming from database(Relational database) while 'SSRS' data is coming from 'SSAS' cube(OLAP).

Solution
To keep data same in 'filters area' and 'SSRS' report, data source should be same. Data source for 'SSRS' cannot be changed. So better if we fill filters area with cube data.

In order to implement same it is required to configure a linked sever which will help to fetch data from cube using simple T-SQL.

Configuration of Linked Server

Step 1 – Connect SQL server, using ‘Database Engine’ as server type along with proper Server name and authentication.

Step 2 – Into object explorer, navigate to linked server under ‘Server Objects’.
(Refer the below screen shot for reference)

















Step 3 – Right click on ‘Linked Servers’ - select ‘New Linked Server’. A dialog will come as below -

General Tab - Fill the required details as per below screen shots. 
(Refer the below screen shot for reference)

















Security Tab - Provide account detail for remote login “Here administrator account has been provided”.
(Refer the below screen shot for reference)


















Server Options -
(Refer the below screen shot for reference)
























Step 4 – After filling the entire details click on Ok button, Linked server is configured and it should work. In case if it doesn’t respond, once restart the SQL server.

Fetch data from SSAS Cube using T-SQL

Below is the sample query which i used to solve my problem-
declare @GeoMDX nvarchar(max) --Declare a variable to hold the SQL query
 
SET @GeoMDX = 'SELECT "[Measures].[TC_Caption]" AS TC_Caption,"[Measures].[TC_Value]" AS TC_Value,
"[Measures].[TSC_Caption]" AS TSC_Caption,  
"[Measures].[TSC_Value]" AS TSC_Value, "[Measures].[Lab_Caption]" AS Lab_Caption, "[Measures].[Lab_Value]" AS Lab_Value, 
"[Measures].[Brand_Caption]" AS Brand_Caption, "[Measures].[Brand_Value]" AS Brand_Value,
"[Measures].[Reimbursement_Caption]" As Reimbursement_Caption,"[Measures].[Reimbursement_Value]" As Reimbursement_Value SQL Query
FROM OPENQUERY(ASSERVER--Linked server object
''WITH MEMBER [Measures].[TC_Caption] AS 
 [Product Details].[Therapeutic Class - Thea].CURRENTMEMBER.MEMBER_CAPTION 
MEMBER [Measures].[TC_Value] AS  
 [Product Details].[Therapeutic Class - Thea].CURRENTMEMBER.UNIQUE_NAME 
MEMBER [Measures].[TSC_Caption] AS  
 [Product Details].[Therapeutic Sub Class].CURRENTMEMBER.MEMBER_CAPTION 
MEMBER [Measures].[TSC_Value] AS  
 [Product Details].[Therapeutic Sub Class].CURRENTMEMBER.UNIQUE_NAME  
MEMBER [Measures].[Lab_Caption] As 
 [Product Details].[Laboratories - Thea].CURRENTMEMBER.MEMBER_CAPTION 
MEMBER [Measures].[Lab_Value] As  
 [Product Details].[Laboratories - Thea].CURRENTMEMBER.UNIQUE_NAME  
MEMBER [Measures].[Brand_Caption] As 
 [Product Details].[Product Consolidated].CURRENTMEMBER.MEMBER_CAPTION 
MEMBER [Measures].[Brand_Value] As 
 [Product Details].[Product Consolidated].CURRENTMEMBER.UNIQUE_NAME  
MEMBER [Measures].[Reimbursement_Caption] As
Case [Product Details].[Reimbursement].CURRENTMEMBER.MEMBER_CAPTION
When ''''R'''' then ''''Reimbursed'''' when ''''NR'''' then ''''Not Reimbursed'''' else ''''Not Applicable''''end
MEMBER [Measures].[Reimbursement_Value] As
[Product Details].[Reimbursement].CURRENTMEMBER.UNIQUE_NAME
SELECT { 
 [Measures].[TC_Caption], 
 [Measures].[TC_Value], 
 [Measures].[TSC_Caption], 
 [Measures].[TSC_Value], 
 [Measures].[Lab_Caption], 
 [Measures].[Lab_Value], 
 [Measures].[Brand_Caption], 
 [Measures].[Brand_Value],
 [Measures].[Reimbursement_Caption],
 [Measures].[Reimbursement_Value] 
 } 
ON 0, 
[Product Details].[Therapeutic Class - Thea].CHILDREN* 
[Product Details].[Therapeutic Sub Class].CHILDREN* 
EXCEPT([Product Details].[Laboratories - Thea].CHILDREN, [Product Details].[Laboratories - Thea].&[])* 
EXCEPT([Product Details].[Product Consolidated].CHILDREN, [Product Details].[Product Consolidated].&[])*
[Product Details].[Reimbursement].CHILDREN
} ON 1 
FROM  
 [TheaCube] MDX Query      
'')' 
exec sp_executesql @GeoMDX  --Execute T-SQL Query

Wednesday, 30 April 2014

SSRS: line chart is not displaying during integration in live server

Scenario: I created one SSRS (SQL Server Reporting Services) report which was containing few line charts. During quality check of these reports, it was observed that some graphs were not displaying which were containing less or no data.
(Refer the below screen shot: SSRS Chart)

We did some analysis on this and observed that an unwanted style sheet has been applied into this chart, which is affecting visibility of the graph.

This style sheet was containing the following properties -
(Refer the below screen shot)


We tried to override these CSS properties with custom style sheet but unfortunately it couldn't get succeed.

Then i tried to change height and width for chart components(Horizontal axis ) and found an easy solution.

Easy solution:
If font size would be increased in chart horizontal axis then charts were coming fine. So i changed in all the charts and it solved my problem.
(Refer the below screen shot)

Change in font size -
(Refer the below screen shot)



After changing the font size -
(Refer the below screen shot)

And also when checked the style now from developer tool- 
(Refer the below screen shot)

Please share your thoughts if you experienced the same kind of problem and found better solution. Your knowledge sharing will be appreciated.

Other links
SSRS: Use single transaction when processing the queries

SSRS: Use single transaction when processing the queries

Question 1: In SSRS report, What is the functionality behind the check box displaying 
"Use single transaction when processing the queries" in data source properties?
(Follow the below screen shot for your reference)



















Answer: This option will be useful if any report is using more than one data set under common datasource. By checking this option we can reduce the network traffic. If this option is checked then all datasets under a report will use same open connection to hit the database. 

Example - If any report is using more than one datasets under a common data source along with checked box "Use single transaction when processing the queries".
So only one connection will maintain for all data set.

In the other hand if same option is unchecked then all data sets will open separate connections to hit the database.

 Please share your comments if this post is useful.


Monday, 21 April 2014

Difference between DTS and SQL Server Integration Services (SSIS)

1. DTS stands for 'Data Transformation Services', while SSIS stands for 'Sql Server Integration Services'.

2. DTS is a set of objects using an ETS tool(tool to extracts, transforms, and loads information into a database for warehousing)in order to extract, transform, and load this information to and/or from a database, while SSIS is an ETL tool provided by Microsoft to extra data from different data sources.

3. DTS was originally part of the Microsoft SQL Server 2000, while SSIS is a component of the Microsoft SQL Server 2005.

4. In order solve complex calculations, DTS uses Activex Script which is similar to vb script, while SSIS uses programming languages like as c# or VB.NET.

5. There is no deployment wizard available in DTS, while SSIS contains Deployment wizard.

6. Very limited Set of Transformation available under DTS, while in SSIS almost all possible transformations are available.


7. In DTS there is no parallel processing available(Only one task at a time), while in SSIS multiple tasks can execute parallel. 


8. DTS doesn' support BI in all aspects, while SSIS supports end to end process of BI.


9. In DTS script is Unmanaged(Not running under CLR), while  in SSIS script is managed and runs under CLR.


10. DTS can develop using Enterprise manager, while SSIS can develop using BIDS(Business Intelligence Development Studio).



11. DTS can be deploy in local server only, while SSIS   package can be deploy in all available servers(local/remote) using BIDS.

12. In DTS designer there is single pane available, while SSIS designer contains four pane - 
  • Control Flow
  • Data Flow
  • Event Handlers
  • Package Explorer
13. There is no event handler available in DTS, while in SSIS event handlers are available like as 'OnError', 'OnProgress'.

14. There is no solution explorer available in DTS, while in SSIS solution  explorer is available including packages, connectionsData Source and Data Source Views (DSV).

15. There is no variables in DTS so connections and other values are always static, while in SSIS these values can be dynamic as can be achieved using variables.

Saturday, 19 April 2014

Run batch file during installation

I got new experience on my recent window based application. A requirement came to create windows task scheduler using batch file and also run that batch file along with installation of the application. Follow the below steps to perform same - 

1. Create windows task scheduler using command prompt -

As per requirement, to create a windows task scheduler which will repeat after every three days. 

Task scheduler through command prompt-


  • Delete task scheduler, if it already exist -
schtasks /delete /tn "TestScheduler" /F


  • Create task scheduler which will repeat after every three days

schtasks /create /tn "TestScheduler" /sc DAILY /MO 3 /tr "\"C:\Windows\Notepad.exe" \"AUTOEXTRACTSCHEDULER\""

abbreviation -

tn   - Task name
sc   - Schedule type
MO - Modifier
tr   - Task run

and also we can include an command line argument at last, follow by \"<Argument>".

Check the below links for more detail -

http://technet.microsoft.com/en-us/library/cc748993.aspx

http://msdn.microsoft.com/en-us/library/windows/desktop/bb736357(v=vs.85).aspx


2. Copy and paste required command into notepad file and create a batch file and save it into desktop. (ie.  TestScheduler.bat)

3. Include batch file into setup file of window based applicatoin/WPF setup.

Follow the below steps -

  • In the setup folder of windows application, right click and select           view - [File system]



 

  • Go to application folder and add cmd.exe (C:\Windows\System32\cmd.exe) and also batch file which you have created above(TestScheduler.bat).

  • Once file is added to the application folder, again go back to application setup folder then right click and Go to view - [Custom actions]
  • Requirement was to Create windows task in scheduler during installation and remove windows task during application un-install. So for that we created two batch file for create and delete task respectively.


  • At installation - Under command actions go to commit, right click and add custom action, select cmd.exe file from application folder


  • Right click on cmd.exe under commit, select property window -








  • Replace /commit by /c "[TARGETDIR]\AutoExtraction.bat" in the argument section.





  • Rebuild the solution and create new setup and install the application, windows task should be created under windows task library. In the same manner if you uninstall application then same windows task should be removed from windows task library. To apply the same follow the below steps -
Step 1 - In the same custom action block add one more custom action under Uninstall - (Find the below screen shot for reference)

Step 2 - Go to application folder and add cmd.exe.
(Find the below screen shot for reference)


Step 3 - Right click on cmd.exe and select Property Window and make changes as per below screen shot -
where RemoveAutoExtraction.bat is the batch file which contains delete command of same task scheduler.

Conclusion: In this post i tried to explain how to create and delete a windows task scheduler using a batch file during install and uninstall of WPF application setup.

I hope this will help, Please feel free to post if there are more methods.

Rate this post if it is helpful.


Other references -








How to: find Connection String in Visual Studio 2010

This article is about how to generate or find  correct Database Connection String for your application in visual studio 2010. Most of the time people  download project from internet link, there might be chance for error due to database connection string failure. This article will helps and show a simple way to find the exact Connection String.

Getting Started
Open Visual Studio, create/open a Web Site, and click on Tools - Connect to Database option.

Step 1: In the Server Explorer window. provide Server Name and user authentication detail then select the appropriate database.
(Find the below screenshot for your reference)


Step 2: Click on Test Connection, Success dialog should come. If it is not coming then repeat Step 1 process.
(Find the below screenshot for your reference)


Step 3: Click on Advanced Properties button and copy the connection string.

In order to use this connection string into visual studio web.config, copy and paste it into connection string node under web.config -


I tried to present to get the correct Connection String using Visual Studio. Hope this article helped you, please  feel free to post more about it. Please share comments.


Thursday, 3 April 2014

How to run a batch(.bat) file during installation of window based application in c#?

Question: In my recent window based application(WPF), a requirement came to create a Window task scheduler during installation of application setup, and also same task scheduler should be deleted during un-installation of same application setup.

Can someone please help me to achieve this?

By blog owner -

After searched so many links/references, I found the solutions -

Some are the below references - 
http://stackoverflow.com/questions/2414956/how-to-run-a-bat-file-during-installation

http://shrinandvyas.blogspot.in/2010/10/run-exe-or-bat-file-from-c-wpf-with.html


In order to easy understanding about this process, Later I wrote my own blog. Please check this out -

http://vsstack.blogspot.in/2014/04/run-batch-file-during-installation.html

Please share your post/reference if any better approach to implement this process.