In this blog we will see how we can create on-premise SQL server linked serviceSteps Create Linked Service in ADF for SQL server It will ask for Server name, database and credentials along with integration runtime as we are using the On-premise SQL Server, we must configure Integration runtime. We have already created integration runtime (Self hosted) created. Below are steps to create Integration runtime (Self hosted) Click on new and select self-hosted integration runtime, Enter the name and Integration run time gets created, once it created it will displays following details, and click on express setup. Once downloaded, execute the setup, On azure portal we can see that, it is running Now we will create connection for the Salesforce and make sure that both source and destination running under same Integration runtime. Now we will create Datasets for the on-premise SQL and salesforce.  Hope this helps!

Sometimes we wanted to test SQL server connectivity, but we don’t have any tools to test then we can follow below steps. In this we are going to test connectivity with Azure SQL server.  Open Notepad and save file with the extension UDL.   Open this file and it asks for Server details and credentials.  We can provide all other details like type of data which we wanted, and connection time out etc.  Once we connected to data source then we see details in All tab,  Hope this helps! 

We can Make the SQL Server Accessible over Internet by following steps: 1. System should have static IP where SQL server is installed. 2. Open SSMS, right click on server > properties and check SQL Server and Windows Authentication mode 3. Go to Server > Security > Logins and configure password for users, who will be accessing the SQL server remotely. In this we are setting up password for the use ‘sa’. 4. Go to Server property and check  “allow remote connections to this server” 5. Go to SQL server configuration manager > SQL server network configuration > Protocols for SQL and make sure TCP/IP is enabled 6. Click on TCP/IP and enter port number in IPAll section 7. In firewall setting create Inbound rule for the port through which it will listen, in this case we are selecting port 4729   8. In next steps we will enable port forwarding (i.e. It will redirect request received on public IP and port to another IP and port combination) In mapped IP we can mention static IP of system also (i.e. 192.168.1.30) 9. Now from other system outside of current network, access the SQL server and enter server details and credentials. Enter server name eg 113.143.120.100, 4729\SQL Hope above Helps!    

In this blog, we will learn how to create blank Tabular model project. Pre-requisite: Visual Studio with Business Intelligence template. Data source to import data into Tabular model (in our case we are using SQL Server). Analysis Server to deploy Tabular model.   Go to Visual Studio, and create new project, go to Templates > Business Intelligence > Analysis Services > Analysis Services Tabular Project, give a name and click on OK. Once click on OK following window will appear to select Analysis services instance. Select the workspace server in our case workspace server name is “localhost”, Compatibility level as “SQL Server 2017/ Azure Analysis Services(1400)” and Click on Test connection to check whether, it is connected to workspace server or not. After clicking on OK project will be created and we can see the Tabular model Explorer, under that we can see the following sections: Data Sources, Expressions, KPI etc. as shown in below snapshot And in solution explorer we can see the solution details In this way we can create Tabular project, in next blog we will import data source in it.

Document Map makes navigation is easy to navigate through table in SSRS In our case we have dataset which contains Product and Product Subcategory. We created the 2 parent groups on Product key and Product Subcategory key. We have to go in the Parent group(ProductCategoryKey) >Advanced > Document Map and select the column used for document Map, in our case we are using ProductCaegoryName Go to Tablix property and in the Document Map Label and specify the Document Map Name. When we preview the report we can see the Product Index Label under the Document Map as shown in image, we can navigate though the table from the Product index. In this way we can create document Map in SSRS to navigate through the report.

Introduction: One of the common Errors while attaching a .mdf file for restoring Data is as follows: An error occurred when attaching the database(s).  Click the hyperlink in the Message column for details. The detailed message is as follows. Step 1: Right, Click on the .mdf that you want to attach and click on Properties. Step 2: Go to Security tab and give Full Control to all Users and Groups by clicking on Edit. Click on the Check Box under Allow section for Giving Full Control to all Users and Groups. Step 3: Right-click on the Main Folder containing your .mdf file and go to the properties section. Deselect the  “Read Only Option” in “General Tab” and click on Apply. Step 4: Go to the “Security Tab” and give “Full Control” to all “Groups and User Names” as shown in Step 2 and click on Apply. Select “Apply changes to this folder, subfolder and files” option and click on “Ok”. Click on “Ok” to apply all the changes. Step 5: Now go to SQL and Attach your .mdf file Again and your DB will be attached to the Databases folder and you can access it. Check out my other blog here https://www.cloudfronts.in/blog/dynamics-nav/performing-update-operation-in-microsoft-dynamics-nav-through-integration-in-tibco-cloud-integration/  

Problem: There is no in-built function in SSRS where we can count the number of Saturdays and Sundays between any two dates in SSRS. This is a needed function for scenarios where we only need to get a count of working days.’ Solution: Following is a formula that can be used for getting an accurate count of weekends. = (((DateDiff (DateInterval.Day, DateAdd(DateInterval.Day,7-WeekDay(Parameters!startDate.Value),Parameters!startDate.Value), DateAdd(DateInterval.Day,7-WeekDay(Parameters!endDate.Value),Parameters!endDate.Value).AddDays(1)) + 1)/ 7)*2) + iif(weekday(Parameters!endDate.Value)=7,1,0) + iif(weekday(Parameters!startDate.Value)=1,1,0) -1 Here instead of Parameters!startDate.Value and Parameters!endDate.Value, you can use any other Start Date or End Date.