Changing User Database Owner - sp_MSforeachdb

Declare @Command varchar(max)
Select @Command = 'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') BEGIN USE ? EXEC dbo.sp_changedbowner @loginame = N''sa'', @map = false END'
Print @Command
EXEC sp_MSforeachdb @Command

GO

Poor Performance Query SQL Trace Profiler

SQL Trace causes overhead to the server which is experiencing performance issue. When do we need to use SQL trace? Which template to choose to trace poor performance query? 

RPC:Completed
    
SP:StmtCompleted
    
SQL:BatchStarting
    
SQL:BatchCompleted
    
Showplan XML

Credit goes to- http://blogs.msdn.com/b/dstfs/archive/2016/01/05/how-to-troubleshoot-slow-performance-issue-in-team-foundation-server-using-sql-profiler.aspx

Fix - 'dbproviderfactories' section can only appear once per config file'. SQLPW is not installed

We tried to create a new data source in a brand new Visual Studio reporting service solution 2012. We got an error prompt.

SQLPW is not installed

'dbproviderfactories' section can only appear once per config file'.

After we clicked Ok button on the prompt, a data source is created without connection string. We cannot update the data source in connection string. Edit button next to the text field for the connection string is disabled.

I did some research on google and found that the error is due to a corrupted version of machine.config which is related to Dot Net Framework.

machine.config is located in two places and they are 32 and 64 bit version. On my computer, they are located in 

64 bit

C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Config

32 bit

C:\Windows\Microsoft.NET\Framework\v4.0.30319\Config 

The file is corrupted due to an extra tag highlighted in yellow.To fix the error, we will need to delete the extra tag.

Step 1 : Back up machine.config file in both location(Copy the file and paste in the same location, Machine- Copy.config will be created.

Step 2: Open a notepad as an administrator and open  machine.config file from 32 bit location

Step 3: Find dbproviderfactories

Step 4. Remove the extra tag and save it.

Repeat step 2 to 4 for machine.config 64 bit version.

Close your visual studio reporting soultion. Reopen it again. In my case, Edit button is working now.

Thank you for reading. Have a nice day!

Dynamic DataSource in SSRS report

I ended 2015 in a good way by passing 70-462 Microsoft certification.

Hello 2016 ! 

I am writing the first blog of 2016. I wish Everyone Happy New Year. May this year bring you joy,peace,good health and prosperous.

I want to blog about how to set up a dynamic datasource in SSRS reports. You might have already known how to set it up. Hope my blog comes in handy when you need to recall how to do it.

In this example, I am creating SSRS Execution Log Report. We have three different datasources (Dev/QA/PROD)- SSRS reportserverdb servers. I have created a report which queries SSRS executionlog3 view from reportserverdb. In my case, I am connecting different datasource one at a time. In order to dynamically connect to each datasource upon choosing a server name from a drop down box, I will need to create a dynamic datasource.

The report below is already created. I want to show you how you can set up dynamic datasource.

Under Data Source, Create a datasource which is named DDS in my case. DDS is my dynamic datasource which will connect to different server upon request (drop down server name choice). 

Open DDS datasource by double clicking it. Choose Embedded connection and open expression (fx)

In expression, enter   ="Data Source=" & Parameters!ServerName.Value

Under credential, choose to use windows authentication

I have two datasets in my report - Serverlist and Executionlog

Under Datasets, Serverlist dataset will get servername from a table called SQLServers. (SQLServers table should be created and populated with data(server names)before this report)

ExecutionLog Dataset has a query which retrieves ssrs executionlog from report server.

I used the below query in Executionlog dataset

use ReportServer

SELECT  

InstanceName,

UserName,

Itempath,

ItemAction,

TimeStart,

TimeEnd,

DateDiff(MILLISECOND,TimeStart,TimeEnd) TimeTakenToggle,

TimeDataRetrieval + TimeProcessing + TimeRendering as [total time_ms],

TimeDataRetrieval TimetoRetrieveAllDataset, 

        TimeProcessing TimeSpentInProcessEngine,

TimeRendering TimeSpentOnReportingService,

ByteCount NoOfBytes,

[RowCount],

AdditionalInfo,

CHARINDEX('\', Instancename) Getposition,

Substring(InstanceName,1,CHARINDEX('\', Instancename)-1) ServerName,

'\\'+Substring(InstanceName,1,CHARINDEX('\', Instancename)-1)+'\e$\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\LogFiles' Logfilelink

FROM ExecutionLog3(nolock)

where convert(varchar(10),timestart,101) >= @DateStart and 

convert(varchar(10),timestart,101) <= @DateEnd

ORDER BY Timestart DESC

When I preview the report, I will have my SSRS reportserverdb server list. I can choose one server and choose datestart and dateend (which I set it up in parameters)

Preview did not give errors. I am going to deploy the report in SSRS reporting service.

After deploying the report to reporting service, I will need to configure permission for this report in reporting service. For example, I am using domain credential MYDOMAIN\SSRSEA and it's password to connect to each servers. MYDOMAIN\SSRSEA account must be added to each SSRS server as db reader in reportserverdb. To use MYDOMAIN\SSRSEA to connect to each server, we will need to configure as below.

In username : MYDOMAIN\SSRSEA and enter it's password accordingly.

NOTE : Check Use as windows credential when connecting to the data source 

Click Apply at the bottom of the page.

Now, you will be able to view the report and it is working!