How to download a facebook video?

Wednesday, 18 March 2015

Scheduled process to print and delete of a PDF file using VBScript

Scheduled process to the print and delete of a PDF file has been already Implemented using by a batch file in my previous post, but I got the feedback from few users that the batch file does,'t work properly as it is unable to close the adobe reader editor window after the completion of the printing process. 

This post is explaining the solution for the same observation but the resolution into this post is provide through a VBScript file rather than a batch file.

Problem with batch file:
Using batch file when printing process is completed for a PDF file then there is a adobe reader editor which was used during the printing process, remains open while it should be closed to get the right impression from end users/clients.

Resolution: To achieve the right scenario, I suggest to use the VBScript file rather than a batch file because customization and code writing is lot more easier than the command line.

VBScritp sample code:
'Assignments on global variables
strPrinterName = "HP LaserJet 3055 PCL6 Class Driver" 
strPrinterDriver = "HP LaserJet 3055 PCL6 Class Driver" 
strPrinterPort = "IP_172.16.16.103"
strAcroRead = "C:\Program Files (x86)\Adobe\Reader 11.0\Reader\AcroRd32.exe" 
strPDFFolder="D:\san"
strPDFFile="pdf-sample.pdf"
intSleepTime=15000

dim filesys 

'Get the file object
Set filesys = CreateObject("Scripting.FileSystemObject")  

If Not filesys.FileExists(strPDFFolder & "\" & strPDFFile) Then 
WScript.Quit 
End If

'Get an object of WScript
Set oShell = CreateObject("WScript.Shell")

'Integration of folder, files and printer detail

oShell.Run Chr(34) & strAcroRead & Chr(34) & " /t  "  _ 
    & Chr(34) & strPDFFolder & "\" & strPDFFile  & Chr(34) & " " _ 
    & Chr(34) & strPrinterName & Chr(34) & " " & Chr(34) & strPrinterDriver & Chr(34) & " " & Chr(34) & strPrinterPort & Chr(34)

'Printing of the file will be completed at this point


'After allowing few seconds for spooling print job
WScript.Sleep 15000

'Delete the file from particular folder
'Check if file is existing
If filesys.FileExists(strPDFFolder & "\" & strPDFFile) Then 
'Delete the file 
filesys.DeleteFile strPDFFolder & "\" & strPDFFile 
End If

'May be there is an open application of adobe reader
'Check if there is an open instance of adobe reader then it should be closed
strComputer = "." 
Set objWMIService = GetObject _ 
    ("winmgmts:\\" & strComputer & "\root\cimv2") 
Set colProcessList = objWMIService.ExecQuery _ 
    ("Select * from Win32_Process Where Name = 'AcroRd32.exe'") 
For Each objProcess in colProcessList 
   objProcess.Terminate() 

Next 

Assumption:
   1. The above requirement has been analyzed on 32 bit Microsoft windows operating system.
   2.  There is only one pdf file, above methodology will not be supporting in case of multiple pdf files.

Mandatory items: There are following items must be available into the respective computer system.
   1.     Adobe reader must be installed to read the PDF file.
   2.     Printer must be configured into the source laptop to capture the print command.

Input Required:
   1.     Adobe reader exe location (Example- C:\Program Files (x86)\Adobe\Reader 11.0\Reader>AcroRd32.exe )
   2.     Subscribed SSRS pdf report location which is required to print on scheduled basis.
   3.     Printer name, Printer driver, Printer Port

Process flow:
   1.     Get the deployed SSRS report into PDF format using report subscription functionality
   2.     Print the same SSRS pdf report using command line utility which is a batch file

   3.     Schedule the batch file execution using Windows Task Scheduler or SQL Job Agent.

Windows Task Scheduler - Scheduled printing on single SSRS PDF file

Follow the below steps to configure the Windows Task Scheduler
1.     Open Task Scheduler by clicking the Start button, clicking Control Panel, clicking System and Security, clicking Administrative Tools, and then double-clicking Task Scheduler.‌if you're prompted for an administrator password or confirmation, type the password or provide confirmation.


2. Click the Action menu, and then click Create Basic Task.

3.     Do one of the following:
a.     To select a schedule based on the calendar, click Daily, Weekly, Monthly, or One time, click Next; specify the schedule you want to use, and then click Next.
b.     To select a schedule based on common recurring events, click When the computer starts or When I log on, and then click Next.
c.      To select a schedule based on specific events, click When a specific event is logged, click Next; specify the event log and other information using the drop-down lists, and then click Next.


4.     To schedule a program to start automatically, click Start a program, and then click Next.

5. Click Browse to find the program you want to start, and then click Next.


6. Click Finish.

I believe this post will help to close the adobe reader editor after completion of the printing process.

See also:

Thursday, 12 March 2015

SQL Server Reporting Services: Schedule printing of the PDF file

I faced one situation in which it was recommended to implement the mechanism for scheduled printing of a PDF file which is captured through the SSRS Windows share subscription. 

Below is the ticket description -

Ticket Description: Create the mechanism to schedule the printing of the SSRS reports in PDF format.

Understanding: As per the above description we have to propose/find out an approach to print the SSRS report in pdf format on schedule basis.


Resolution: The requested functionality will be implemented by using the batch file and Windows Task Scheduler/SQL Server Agent.


Batch file sample code:
/*Take to the root*/
CD/
/*Get to the installed adobe reader driver, this location may differ based on the configuration*/
CD Program Files (x86)\Adobe\Reader 11.0\Reader

/*execute the adobe drive file and follow up with the printer properties*/

AcroRd32.exe /t "C:\Users\vipul.sachan\Desktop\Vipul\pdf-sample.pdf" "HP LaserJet 3055 PCL6 Class Driver" "HP LaserJet 3055 PCL6 Class Driver" "IP_172.16.16.103"

Assumption:
   1. The above requirement has been analyzed on 32 bit Microsoft windows operating system.
   2.  There is only one pdf file, above methodology will not be supporting in case of multiple pdf files.

Mandatory items: There are following items must be available into the respective computer system.
   1.     Adobe reader must be installed to read the PDF file.
   2.     Printer must be configured into the source laptop to capture the print command.

Input Required:
   1.     Adobe reader exe location (Example- C:\Program Files (x86)\Adobe\Reader 11.0\Reader>AcroRd32.exe )
   2.     Subscribed SSRS pdf report location which is required to print on scheduled basis.
   3.     Printer name, Printer driver, Printer Port

Process flow:
   1.     Get the deployed SSRS report into PDF format using report subscription functionality
   2.     Print the same SSRS pdf report using command line utility which is a batch file
   3.     Schedule the batch file execution using Windows Task Scheduler or SQL Job Agent.

Windows Task Scheduler - Scheduled printing on single SSRS PDF file
Follow the below steps to configure the Windows Task Scheduler
1.     Open Task Scheduler by clicking the Startbutton, clicking Control Panel, clicking System and Security, clicking Administrative Tools, and then double-clicking Task Scheduler.‌if you're prompted for an administrator password or confirmation, type the password or provide confirmation.

2Click the Action menu, and then click Create Basic Task.


       
    3.     Do one of the following:
a.     To select a schedule based on the calendar, click DailyWeeklyMonthly, or One time, click Next; specify the schedule you want to use, and then click Next.
b.     To select a schedule based on common recurring events, click When the computer starts or When I log on, and then click Next.
c.      To select a schedule based on specific events, click When a specific event is logged, click Next; specify the event log and other information using the drop-down lists, and then click Next.


4.     To schedule a program to start automatically, click Start a program, and then click Next.

5. Click Browse to find the program you want to start, and then click Next.


6. Click Finish.


SQL Server Agent - Scheduled printing on single SSRS PDF file using
Follow the below steps to configure the SQL Server Agent –

    1.     Go to SQL Server Agent from SQL Management Studio and explore the Job-



    2.     Right click to the jobs and Select new job.

    3.     In general tab, named the job and select the category as uncategorized (local).























    1. Select steps tab and select new steps. Then name the step name, select type as Operating system (CmdExe). In the command open the batch file or write the command.

     2.     Then select OK and job is ready.

See also:
Complete the Scheduled printing of PDF file using VBScript file

Friday, 27 February 2015

C#: Read and Write Excel (*.xls and *.xlsx) Files Content without Excel Automation using ADO.NET ?

This implementation is regarding how to fetch the data from available excel files using System.Data.Oledb name space. Below are two examples which contains fetching the data from .xls file as well as with .xlsx file.

Fetch the data from an excel .xls file -

Let's create an excel file with few rows and save it as Excel 97-2003 Workbook (*.xls) in your system.

For the reference both the excel files will look like with some sample records-













Fetch the records from excel file using C# .Net, Asp.Net -

Step 1 - Let's create an interface which will contain two buttons for Refresh data from .xls and Refresh data from .xlsx respectively.

Step 2 -  Create a gridview in Asp.Net which will present the data fetched from the excel.

Source code for the reference
<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">
    <asp:Button ID="btnGetData" runat="server" Text="Refresh xls" OnClick="btnGetData_Click" />
    <asp:Button ID="Button1" runat="server" Text="Refresh xlsx" OnClick="Button1_Click" />
    <br />
    <asp:GridView ID="grdExcel" runat="server">
    </asp:GridView>
</asp:Content>


Step 3 - Required name spaces -
using System.Data.OleDb; //To operate the Oledb operations
using System.Data;       //To have the data objects like data tables/sets


Step 3 - Write the logic under click event for both buttons respectively.

For .xls file type
/// <summary>
/// Click event: To fetch data from the excel file of .xls type
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnGetData_Click(object sender, EventArgs e)
{
//Specify the excel provider for .xls file type and the file path which contain the excel file
string con = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:/Users/vsachan/Desktop/Aditya/Sample_Data.xls;Extended Properties='Excel 8.0;HDR=Yes;'";

using (OleDbConnection connection = new OleDbConnection(con))
   {
//Open the Oledb connection
connection.Open();
//Specify the command, assume that the sheet name is Sheet1
OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection);
//Define the object of data adaper to run the query
OleDbDataAdapter adap = new OleDbDataAdapter(command);
//Define the dataset to hold the records
DataSet ds=new DataSet();
//Fill the data set
adap.Fill(ds);
//Check the condition if dataset contains any table, It should be at least greater than one
if (ds.Tables.Count >= 1)
      {
grdExcel.DataSource = ds.Tables[0];
grdExcel.DataBind();
      }
   }
}


For .xlsx file type
/// <summary>
/// Click event: To fetch data from the excel file of .xlsx type
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Button1_Click(object sender, EventArgs e)
{
//Specify the excel provider for .xlsx file type and the file path which contain the excel file
string con = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:/Users/vsachan/Desktop/Aditya/Sample_Data.xlsx;Extended Properties=Excel 12.0 Xml";


using (OleDbConnection connection = new OleDbConnection(con))
   {
//Open the Oledb connection
connection.Open();
//Specify the command, assume that the sheet name is Sheet1
OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection);
//Define the object of data adaper to run the query
OleDbDataAdapter adap = new OleDbDataAdapter(command);
//Define the dataset to hold the records
DataSet ds=new DataSet();
//Fill the data set
adap.Fill(ds);
//Check the condition if dataset contains any table, It should be at least greater than one
if (ds.Tables.Count >= 1)
      {
grdExcel.DataSource = ds.Tables[0];
grdExcel.DataBind();
      }
   }
}

Difference in logic - In both the logic only difference is in the connection strings which is highlighted in the above explanation else everything remains same.

At the Interface - Once all the source code writing and logic building have been completed then run the application. and click on the respective buttons. The user screen or web site should be look like the below screen shot -



















I hope the above explanation make you comfortable with the data handling on excel file types .xls and .xlsx respectively.

Please share your comments for any further information/help.

Wednesday, 25 February 2015

Can we open a SSIS package 2012 solution in BIDS 2008 or 2008 R2 ?

I got a scenario in which few SSIS packages have been created in BIDS 2012. Now I need to run those SSIS packages in BIDS 2008 R2 from BIDS 2012. I tried hard with the Google but I did not see any positive response. Can anyone please provide the help.



Tuesday, 6 January 2015

Access to the remote server is denied because no login -mapping exists.

I had a scenario in which there was a configuration error in a Linked Server. There was an error populating when tried to access few SSRS reports.

Error was as per the below screen shot -


The above error was coming due to the access/mapping problem of the system/administrator account with Linked server, To resolve this problem, it is required to map the system/administrator account with the particular linked server.

Right click to the linked server and select Properties :


Under security tab, change the radio button selection from Be made without using a security context to Be made using this security context, and enter your system/administrator account and password.


After successful configuration, click on OK button. it will resolve the problem.

Please mark as comment if it solved your problem.

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