What is UDL(Universal Data Link)

One of the most common problem in any data driven application working is the database connection string. How many times in your development life have you heard “I can’t connect to the database” and the problem ends up a minor detail in the connection string?

“How to verify sql server connection without SSMS?” or “How to find installed sql server instance names on a machine without SSMS or any tools?”

The Microsoft Data Access Components (MDAC) provide the Data Link Properties dialog box as the common user interface for specifying connection information to a data provider on Windows 2000 and later operating systems. You can use the Data Link Properties dialog box to save connection information in a universal data link (.udl) file.

To create a Universal Data Link (.udl) file

  • RUN → Notepad
  • Once the notepad is opened, File → Save As… (Do not type anything in the notepad)
  • Choose the path → Save as type: All Files (*.*) → File name: Rakesh.udl (File name can be anything but the extension must be .udl)

U1

  • Double click on the udl file.

U2

  • Select provider name for whatever you want test database connection.here  “Microsoft OleDb Provider for SqlServer” is used for testing.
  • Now go to the Connection tab and select the database to connect to. You may just browse through the list but SQLExpress instances do not always show up. You may also type the server name into the list box. select appropriate authentication mode(windows or sqlserver).
  • Now you will see all databases in drop down and select any one database.
  • Assuming all’s well, select the database and click the Test Connection button.

U5.JPG

  • Now open the file in notepad – remember, the .udl file is simply a text file. You should see something like this:

U6.JPG

 

In the file is the connection string you just tested. Copy and paste were needed in your application and you should be good to go.

Advertisements

SSRS -Multi-level drill down report?

You can organize data in a variety of ways to show the relationship of the general to the detailed. You can put all the data in the report, but set it to be hidden until a user clicks to reveal details; this is a drill-down action.

You can display the data in a data region, such as a table or chart, which is nested inside another data region, such as a table or matrix.

In order to define a drill down action, a tablix must first be created with the appropriate level of aggregation all the way to the level of drill down detail desired. This detail includes both the row and columns desired for the lowest level viewing needed. The drill down functionality works either at the row level, the column level, or the group (column or row) level.

Let’s start step by step:

  1. Create a table “DimEmployee” with following employee details.
EmployeeKey ParentEmployeeKey SalesTerritoryKey EmployeeName
1 NULL 101 Rakesh
2 NULL 102 Amit
3 2 103 Sunil
4 2 104 Avay
5 3 105 Sachin
6 3 106 Gaurav
7 5 107 Rupesh

2. Create another table “EmpSalesAmount” having sales details for each employee.

EmployeeKey Amount
1 5000
2 5225
3 4879
4 6589
5 412
6 5987

3. Create a query to read the details from database.

 

;WITH cte AS (

SELECT

EmployeeKey ,  EmployeeName , ParentEmployeeKey , 1 EmployeeLevel

FROM dbo.DimEmployee WHERE ParentEmployeeKey IS NULL

UNION ALL

SELECT

c.EmployeeKey ,c. EmployeeName , c.ParentEmployeeKey , p.EmployeeLevel + 1

FROM dbo.DimEmployee c

JOIN cte p ON p.EmployeeKey = c.ParentEmployeeKey

)

SELECT cte.*, ISNULL(f.Amount, 0) SalesAmount

FROM cte

LEFT JOIN dbo.Empsalesamount f

ON f.EmployeeKey = cte.EmployeeKey  ORDER BY EmployeeLevel, EmployeeName

4. Create Table on Report and drag and drop EmpName field from the dataset :

Capture1

5. Right click on the row and select row group properties :

Capture2.JPG

6. Configure the row group properties as per below:

Capture3.JPG

Capture4Capture5

8. Set Left Indent properties:

Capture6.JPG

9.  So we have done with our drill-down report. To see the preview, click on preview tab:

Capture7Capture8

RS Utility(SSRS) And Back Up of SSRS Reports Using RS Utility

RS Utility:

The RS.exe utility processes script that you provide in an input file. Use this utility to automate report server deployment and administration tasks.

Developers and report server administrators can perform operations on a report server through the use of the rs utility (RS.exe).

Reporting Services scripts can be used to run any of the Report Server Web service operations. Scripting can be used to copy security to multiple reports on a server, to add and delete items, to copy report server items from one server to another and more.

Syntax:

rs {-?}
{-i input_file=}
{-s serverURL}
{-u username}
{-p password}
{-e endpoint}
{-l time_out}
{-b batchmode}
{-v globalvars=}
{-t trace}

Example:

rs –i c:\BackupReports\BackupReports.rss -s http://localhost/reportserver

Read more about RS utility.

Backup SSRS Reports from Deployed Servers To Local Hard Drives

To take backup using RS utility we have to create two files:

  1. BackupSSRS.rss(.rss Report server scripting file)
  2. BackupSSRS.bat

Let’s start step by step:

Step1: Create a new directory in C: drive with name “BackupReports”.

Step2: Copy and paste below lines of code in notepad and save the file as “BackupSSRS.rss” into new created folder defined in step 1.This script will take each reports under the path we provide, iterate through all the reports , gets report definitions from the server we provide, and save it into .rdl file under the location we gave through command line

Public Sub Main()

Try
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
Dim Items as CatalogItem()
Dim Item as CatalogItem
Dim ReportName As String
Items = rs.ListChildren(ItemPath, false)
Console.Writeline()
Console.Writeline(“Reports Back Up Started.”)
For Each Item in Items
ReportName = ItemPath + “/” + Item.Name
Dim reportDefinition As Byte() = Nothing
Dim rdlReport As New System.Xml.XmlDocument
reportDefinition = rs.GetReportDefinition(ReportName)
Dim Stream As New MemoryStream(reportDefinition)
Dim curDate as Date = Date.Now()
Dim strDate as String = curDate.ToString(“dd-MM-yyyy”)
Dim BackupFolderNew as String = BackupFolder+”\”+strDate+”\”+ItemPath

If(Not System.IO.Directory.Exists(BackupFolderNew )) Then
System.IO.Directory.CreateDirectory(BackupFolderNew)
End If

rdlReport.Load(Stream)
rdlReport.Save(BackupFolderNew + “\” + Item.Name +”.rdl”)

Console.Writeline(“Report ” + Item.Name +”.rdl Backed up Successfully”)

Next
Console.Writeline(“Reports Back Up Completed.”)
Console.Writeline()
catch e As Exception
Console.Writeline(e.Message)
End Try
End Sub

Step3: Copy and paste below script in new notepad file and save file as “backupSSRS.bat” into new created folder defined in step 1.

In this script, ItemPath is the location where reports are stored in the report server. ReportServerURL is the URL of ReportServer where reports are deployed. BackupReports is the location where backup of report is stored.

set ItemPath=/Report Project2

set ReportServerURL=http://localhost/Reportserver   

set BackupFolder=C:\BackupReports

rs -i “C:\BackupReports\BackupSSRS.rss” -s %ReportServerURL% -v ItemPath=”%ItemPath%” -v BackupFolder=”%BackupFolder%”

pause

Be ensure now you have 2 files in “c:\BackupReports” with .rss and .bat extension.

1folderstructure

Step 4:  Now go to new created folder “C:\BackupReports” and right click on batch file (.bat extension) and run as an administrator and you will see result looks like following screenshot.

2batchfile

Step 5: after executing the batch file as mentioned in step 4, you can find the folder with the name of current date in dd-mm-yyyy format which contains back up of all the reports. Please check below screen shot..

3.final output.

We successfully taken Back Up of SSRS Reports using RS Utility.You can also execute batch file from windows task scheduler. So you can create one schedule which execute daily on particular time. This schedule will generate day-to-day folder with backup of reports into your BackupFolder.

Download Files:  .Rss and .bat files