How To Connect SqlServer with Node.js In AWS(Amazon Services) -Part1

This proof of concept is created to check how node.js works with sqlserver database.

There are many drivers available to support sqlserver in node js. I have created this poc with most usable driver “MSSQL” and you can check available drivers by executing below command in nodejs environment.

Command: npm find sqlserver

Above command gives all available sqlserver drivier for node.js.

There are following steps to configure node.js with sqlserver and you have to create same steps in your machine to run this sample.

Step 1:  install node js package for sqlserver by using below command:

“Npm install mssql “

Step2: Create Sa login in sqlserver and also create database.here I have create sample database with name “EmpDb” with table name “Employees” with some records.

step2 step2-2 step2-3

Step3: Create below code in node.js application:

var sql = require(‘mssql’);

var config = {

user: “sa”,

password: “password@123”,

server: “Localhost”, // it can be servername if connecting directly with sqlserver or can be replace by RDS end point

example: ‘mydbinstance.abcdefghijkl.us-west-2.rds.amazonaws.com;dbname= EmpDb

database: “EmpDb”

}

function GetEmp() {

console.log(“step1”);

var connection = new sql.Connection(config);

var request = new sql.Request(connection); // or: var request = connection.request();

console.log(“step1 end”);

connection.connect(function (err) {

if (err) {

console.log(err);

return;

}

request.query(“select * from Employees”, function (err, recordset) {

if (err) {

console.log(err);

}

else {

console.log(recordset);

}

connection.close();

});

});

}

GetEmp();

Step4: Now run the application and below output well come:

step4

Note: I have used only select statement in this sample,If you want to perform update and delete then you can also use these operations.

Advertisements

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