SQL DBA tips Archives » Page 3 of 4

Browsing: SQL DBA tips

Event id 133 from source report server cannot be found

by Dev on

This is about a fix for the error “Event id 133 from source report server cannot be found”. This is in continuation of SQL Server Reporting Services Not Starting.

If you have landed here directly and you are getting the exact error in the Application event log, you are in right place. In summary I had explained the troubleshooting steps and showed the exact error message in the first article.

Further troubleshooting steps:

Once I had checked the Application Event log, next I checked the Reporting Services log. The location of the log files is your SSRS install directory. In case you have observed, the error in the application event log contains the install directory:

event id 133 from source report server cannot be found — Application Event Log Showing SSRS Install Directory

Hence you can find the SSRS logs in the following location: Considering the above example.
“C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\LogFiles”

Looked at the latest log file and could find the following error message:

The error indicates some issues with the SSRS Config file. Refer the first screen shot in this article to find the path of the file.

“C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer\RSReportServer.config”

The most compelling evidence is the following part in the error message:

“configmanager!DefaultDomain!20!03/27/2020-04:21:32:: e ERROR: Error loading configuration file: The ‘SemanticQuery’ start tag on line 220 does not match the end tag of ‘Extension’. Line 251, position 6.”

With this in mind I opened the config file in Visual Studio and searched for “SemanticQuery” and “Extension”.

Note: Please create a copy of the original RSReportServer.config file before making any changes.

Visual Studio View of RSReportServer.config file:

The arrow shows the <SemanticQuery> block. If you have observed, once I put the cursor in the </Extension> it showed the dialog box saying “Expecting end tag </SemanticQuery>.

The next step is to observe the difference of <Extension> blocks between “SemanticQuery” block. If you can see the red underlines does not have the ending ‘/’ except the one (second last red circle).

Where as the last <Extension> before the ending </SemanticQuery> has ‘/’. I removed the ‘/’ from the “Extension” block which was expecting end tag </SemanticQuery> and saved it.

Bingo! that worked and Reporting Services came online. This is how I fixed it, there may be multiple issues which you may need to fix.

If you have faced any other issues let me know by leaving your comments below.

Tagged as: SQL Server Reporting Services, SSRS

{ 2 Comments }

SQL Server Reporting Services Not Starting

by Dev on

This article is about troubleshooting SQL Server Reporting services not starting issue. It was a nice weekend until I got a call saying SSRS is not working. First thing I asked the on shift DBA if the services online and he says “No”. Of-course the DBA tried to bring it up and it failed saying “The service did not respond to the start or control request in a timely fashion‘.

Further he informed that Windows Team performed maintenance activity and as part of that they rebooted the server. After reboot all services came online except SSRS service.

Not to mention, you must have already guessed the situation. It required deeper troubleshooting and I am going to explain the steps in the following section.

The SQL Server version: SQL 2012 (SP4-GDR), Standard Edition.

Troubleshooting SQL Server Reporting Services Not Starting

First thing I tried to start the service from Services.msc to check if I get a different error. And It threw the following generic error:

The above error was of not much help but it will log the error at the top (mostly) in application event log. This is what I could find in the event log:

Log Name: Application
Source: Report Server
Date: 3/27/2020 6:40:30 AM
Event ID: 133
Task Category: (4)
Level: Error
Keywords: Classic
User: N/A
Computer: <<ServerName>>
Description:
The description for Event ID 133 from source Report Server cannot be found. Either the component that raises this event is not installed on your local computer or the installation is corrupted. You can install or repair the component on the local computer.

If the event originated on another computer, the display information had to be saved with the event.

The following information was included with the event:

C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer\RSReportServer.config

The error image shows as follows:

The very first thing comes to mind after seeing the error is the SSRS service got corrupted and needs to be repaired/reinstalled. But that should always be the last resort. Continue to read to find the solution without reinstall. Next Page>>

{ Add a Comment }

Create SSMS shortcuts for different Windows Users

by Dev on

<<Previsous

In this article I am going to show how to create SSMS shortcuts for different Windows Users. This article is in continuation of Use Windows Authentication With a Different User in SSMS. If you haven’t read the parent article then please read that article first and then come back here.

In some cases you may have many Windows Authenticated logins for different SQL Instances and if you can create shortcuts for each, then it makes life a little easier.

Steps to Create shortcut for different Windows Authenticated Users:

First of all locate the path for ssms.exe. I have explained in the previous article how to quickly find the file location on your workstation.

e.g. The path is C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\ssms.exe

Go to your desktop (Windows Key+D). Right Click > New > Shortcut as shown below:

Create SSMS shortcuts for different Windows Users — Create New Shortcut

Put the runas command with the ssms.exe file loation:

In this example the command would be:

The runas command would be:

C:\WINDOWS\system32>runas /user:Domain\username "C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\ssms.exe"

Type the name for the shortcut:

You can create multiple shortcuts for different Windows logins. You can also use /savecred in the runas command so that you don’t have to enter the password each time you open SSMS.

The command would be as follows:
C:\WINDOWS\system32>runas /user:Domain\username /savecred "C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\ssms.exe"

Hope this helps.
You may also like the below links:

Remove or Delete Tempdb data file without restarting SQL Server

User Defined Profiler Template Location

Myth about Remote Query Timeout Option

Facts about Dedicated Administrator Connection

Tagged as: SSMS

{ Add a Comment }

Use Windows Authentication With a Different User in SSMS

by Dev on

Today I am going to show how you can use Windows Authentication With a Different User. Yes, you heard it right, it is possible and it’s one of those Wow factors in SQL Server.
First of all let’s talk about why even someone would need to use the functionality.

Customer may login to their laptop with a different domain account than the account used to connect to SQL Server. This is because of security reasons.

Another situation is customer have different domain account to connect to different SQL Servers. In some organizations this is very common practice.

The below screen shots are of Window 1o and SQL Server 2017.

How to run SSMS as a different user

Create a shortcut of SSMS in taskbar.
Hold Shift Key and right click on the shortcut as shown below:

Windows Authentication With a Different User

The next screen will need the credentials for the different user:

Once you click on Ok the login screen of SSMS will appear with the domain user grayed out just like the way we use windows authentication for normal account (the account used to login to the laptop\desktop).

You can open SSMS as a different user without creating a shortcut as well. You need to go to the path of ssms.exe and use the same method. The file resides in Binn folder of SQL install directory.

Well, you do not have to remember the path. There is an easy way to locate the path as shown below.

Go to Window Search (Windows Key + S) and type SSMS. Right Click on the SSMS icon and choose Open file location.

Windows Authentication With Different User — Open SSMS file Location

This will open the SSMS location of Windows Start Menu as shown below. Again right click and open file location.

Windows Authentication With a Different — Open SSMS file location

This is Open the physical file location of ssms.exe as shown below:

Once you reach here you can use the same method as described above to open SSMS using Windows Authentication with a Different User.

Do you have to do this every time you need to use a different windows authenticated user? Let’s see how we can create different shortcuts for different logins. Continue Reading>>

Tagged as: SQL DBA Tips

{ Add a Comment }

Uninstall SQL Server Components Continue

by Dev on

This is in continuation of Completely Uninstall SQL Server Components article. If you have not read that please click on the above like and then comeback to this page.

If msiinv.exe as mentioned in the first page works fine then it is good for you. Run the command once again and verify the output file.

PS C:\msiinv> .\msiinv.exe -s | Select-String "SQL Server" -Context 1,1 > C:\msiinv\msiinv_result2.txt

This time there should not be any SQL Components which you removed. If you see the components that means even msiinv.exe did not work. What is next?

You need to use Windows Installer CleanUp Utility. It is developed by Microsoft for Windows OS to resolve uninstallation problems of programs that use the Windows Installer technology. It wipes Invalid or corrupted entries from registry for the component.

There are many links available in the internet to download the tool. You can directly get it from here as well Windows CleanUp Utility

The utility displays a list of all the Windows Installer-based applications on the system as shown below:

Uninstall SQL Server

Select the component you want to uninstall and click on Remove:

Uninstall SQL Server

Note: Never Ever click on Select All. Tools are good until you make a mistake.

Here is the techNet article:

https://blogs.technet.microsoft.com/tnmag/2008/07/30/utility-spotlight-windows-installer-cleanup-utility/

Tagged as: Uninstall SQL Server

{ Add a Comment }

Completely Uninstall SQL Server Components

by Dev on

In this article I am going to show you how to Completely Uninstall SQL Server Components which are left behind. In other words what to do if the regular uninstall process from Control Panel fails.
Even more, sometimes the control panel shows that the SQL component has been uninstalled. But when you try to reinstall, it fails again. And this is because of the left behind components.

There are many articles in the internet suggests to change registry entry. Modifying registry is always associated with risk. For me it didn’t work, may be because the registry was corrupted.

You need to use the MSI inventory tools to check and uninstall the components. I have made it available for you in the following link Download msiinv.zip

The original source is Download from One Drive

Once downloaded open Powershell and run the below command. In the below example I have downloaded the msiinv in D drive under msiinv folder. Please put valid paths and then run the command. This will create the out put file with all the GUID of the SQL Components.

PS D:\msiinv> .\msiinv.exe -s | Select-String "SQL Server" -Context 1,1 > D:\msiinv\msiinv_result1.txt

If the execution is successful it will go to the prompt again as shown below:

Uninstall SQL Server

The result set will look like as below:

Uninstall SQL Server

You can find out the components you want to remove and note the product code from the file.
Execute the below command in PowerShell to uninstall the component.

 msiexec /x "{6BD8D100-B16C-409E-B0EA-BF508D7874EC}"

This will ask for final confirmation:

Uninstall SQL Server

If you are not comfortable with PowerShell or facing issues, then you can use command prompt to use the msiinv tool as shown below:

Command to execute is as follows:

msiinv.exe -p > D:\msiinv\msiinv_result1.txt

Uninstall SQL Server

The output file will have all the Windows components and not only SQL Server. You have to search through the file for SQL Server and use the same command as shown above to Uninstall SQL Server.

Sample file:

Uninstall SQL Server

Thanks to the following post:
https://www.mssqltips.com/sqlservertip/4050/cleanly-uninstalling-stubborn-sql-server-components/

Tagged as: Uninstall SQL Server

{ 5 Comments }

Issues with Uninstall OLE DB Provider for DB2

by Dev on

After reading this article you’ll be able to fix if standard method of uninstall OLE DB Provider for DB2 does not work. The requirement was to upgrade OLE DB provider for DB2 from version 3.0 to version 5.0. As per Microsoft Document you cannot upgrade OLE DB Provider for DB2 without uninstall of prior versions.

The following instructions are listed in Microsoft Document named “Microsoft OLE DB Provider for DB2 Version 5.0

Upgrade from Previous Version

Microsoft OLE DB Provider for DB2 V 5.0 does not upgrade previous releases. If you have the following previous versions installed, then you must remove them prior to installing the Microsoft OLE DB Provider for DB2 V 5.0.”

To uninstall the product

You can use Windows Programs and Features to remove the product.

Click Control Panel, click Programs, and then click Programs and Features. The Uninstall or change a program dialog appears.
In the Name list, double click Microsoft OLE DB Provider for DB2 Version 5.0. The Data Provider Installation Wizard appears.
Click Next to get started.
On the Program Maintenance dialog, click Remove.
On the Remove the Program dialog, click Remove.
When prompted by Windows User Account Control, click Yes.
On the Completion page, click Finish

This works in ideal condition. Uninstall of OLE DB Provider for DB2 does not always go well and throw weird error messages. The Uninstall may fail due to different reasons like existing DB2 version was not properly installed, someone deleted some of the MSI files or the registry corruption etc.

One of the error message looks like as follows:


MSI (s) (78:64) [21:44:40:923]: Note: 1: 1725
MSI (s) (78:64) [21:44:40:923]: Product: Microsoft OLE DB Provider for DB2 -- Removal failed.
MSI (s) (78:64) [21:44:40:923]: Windows Installer removed the product; Product Name: Microsoft OLE DB Provider for DB2 Product Version: 8.0.4294.0. Product Language: 1033. Manufacturer:Microsoft.Removal success or error status: 1603

As I mentioned earlier the error messages are strange and generic and googling did not help to resolve this particular issue. After this article it may help though 😉
In our case what worked was voiding the existing version of DB2 by changing the registry settings.
Please be careful and make sure to take a backup of your registry settings before following the below mentioned steps.

Steps to Rename The Registry Settings For Existing Version of DB2:

Go to the path HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Sna Server and rename “Sna Server” to “_Sna Server” as shown in the following screen shot:

Uninstall OLE DB Provider for DB2

Similarly make the following changes:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\_Host Integration Server
HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\ _Sna Server
HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\ _Host Integration Server
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Installer\UserData\S-1-5-18\Products\_00FAC227E6ED834428ED6E468B6B22B2
HKEY_CLASSES_ROOT\Installer\Products\ _00FAC227E6ED834428ED6E468B6B22B2
Than go to the Program Files Directory and for the Microsoft OLEDB Driver for DB2 and just put an (Underscore) in front of the directory name.

You may be thinking why only (Underscore), you can put anything meaningful like “DB2V3_Old”. It’ll work, but if you put symbol it’ll appear at the top and easy to identify what you have changed or for future reference.

I am leaving that decision up to you. The below screen shot shows how it looks in the registry after renaming:

Uninstall OLE DB Provider for DB2

Once you are done with all the changes your new install should work.

Programs and features looks like both the versions stays happily:

Uninstall OLE DB provider for DB2

You also like to know How to Download OLE DB Provider for DB2

SQL Database Restore using NetBackup
SSRS Encryption Key Backup Information
Remove or Delete Tempdb data file without restarting SQL Server
How to Fix if All logins get deleted due to Trigger Execution
Myth around Remote Query Timeout Option

Tagged as: OLE DB Provider for DB2

{ Add a Comment }

How to Download OLE DB Provider for DB2

by Dev on

This article is about how to download OLE DB Provider for DB2. This is a Microsoft Data Provider which offers tools and technologies using which SQL Server can access DB2 Databases.

The requirement was just to install DB2 driver and so change time was kept minimal. But I had to spend more than an hour just to find the required file in Microsoft Site. So this five minutes read can save your lot of time.

You may give it a try to search it yourself before reading this article and will get you know why simple things are not always easy.

If you are in the middle of change like me and your search engine lands you here then you can directly go to the end of this article where I have provided direct links to download. Later you can come back and continue to read.

The following instructions are listed in Microsoft Document. This example shows the download of OLE DB Provider for DB2 Version 5.

To Install the Product:

Go to the Microsoft Download Center.
Download either the x86 (32-bit) or the x64 (64-bit) version of DB2OLEDB5_x64.msi installation program.
Double-click the .msi file to start the Installation Wizard
Click Next to get started.
License Agreement page, review the license terms, click the I accept the terms in the license agreement option, and then click Next.
Registration Information page, enter your Name and Company, and then click Next.
Feature Selection page, optionally click Browse to change the Folder name in which to install the product, optionally click Disk Cost to space required to install the product, and then click Next.
Ready to Install the Program page, click Install.
When prompted by Windows User Account Control, click Yes.
On the Installing page, view the status of the installation process.
On the Completion page, click Finish.

Install looks like a cakewalk until you pass the very 1st step.

Once you go to Microsoft Download Center:

Download OLE DB Provider for DB2

Wow! Now what? Which option to choose or what should you search for?

Right here I wasted a lot of time. What you should search for is Feature Packs of SQL Server Version for which you want to install the OLE DB provider for DB2.

Download OLEDB Provider For DB2 Microsoft Download Center

Choose the link showing Feature Pack as shown below:

OLEDB Provider for DB2 Microsoft Download Center

You will get the download option as shown:

OLEDB Provider for DB2 Microsoft Download Center

Click on Download and you get the options of DB2OLEDB5_x64.msi file to choose from:

Download OLE DB Provider for DB2

Select the checkbox for DB2 and click on Next and the download process should start.

For SQL Server 2012 and prior versions you need to expand Install Instructions:

Download OLE DB Provider for DB2

Then search for DB2 keyword and you will get the option to download:

Download OLE DB2 Provider for DB2

Hope this is useful and please let me know by your valuable comments.

Direct links to Download OLE DB provider for DB2:

SQL Server 2016 Feature Pack:

https://www.microsoft.com/en-us/download/details.aspx?id=52676

SQL Server 2014 Feature Pack:

https://www.microsoft.com/en-us/download/details.aspx?id=42295

SQL Server 2012 Feature Pack:

https://www.microsoft.com/en-us/download/confirmation.aspx?id=29065

SQL Server 2008 R2 Feature Pack

https://www.microsoft.com/en-us/download/confirmation.aspx?id=16978

Tagged as: OLE DB Provider for DB2

{ Add a Comment }

User Defined Profiler Template Location

by Dev on

First of all let me explain you why you need to know the user defined profiler template location.

In situations you may need to define a template and share it with other DBAs to run or vice versa. You have saved the profiler and request the other person to run by providing the name of the profiler. The other person called you (when you are not in shift) since he/she is not able to find the profiler you created, but that is hard for you to believe as you are quite sure you did it right.

Once you go through this article you will get to know:

How to create a user defined template?
Why others are not able to see the template?
How to resolve this?

You can watch the following video demo which shows the stuffs I am going to explain here:

1. How to create a user defined template?

Launch SQL Server Management Studio (SSMS) and open SQL Server profiler:

Open a new Template:

Provide a template name and choose a base template from the drop down menu. In the below example I have chosen Standard (default) template. Then click on Events Selection tab.

In the above example I have not selected the check box for “Use as a default template for selected server type”. You can choose this option if you want your template to be default template.

Choose the Events as per your requirement. Click on “Show all events” and “Show all columns” to view all the events and columns to choose from. Then click on “Column Filters” to put filter for the trace.

In the below example I have put a filter for database id since I wanted to capture the transactions only for one database. The filter may differ for your requirement.

Once you are done with the filter selection click on OK and then Save the template.
Now if you go back and check the drop down list of templates you will find your custom template.
Go to File > New Trace and then connect to any SQL Instance

The following screen shot shows the newly created template “Demo Template (user)” in the drop down list.

2. Why others can’t see this template under the same drop down?

Here is the physical location of the user defined template file:

C:\Users\UserName\AppData\Roaming\Microsoft\SQL Profiler\11.0\Templates\Microsoft SQL Server\110

The main point to note here is the template gets created under the user profile folder of the login id, used to login to the server and that is why it is not visible to others.
AppData is a hidden folder and 11.0 and 110 will change depending upon the SQL Server version.

3. How to resolve this?

Let’s see the location of SQL Server Provided profiler templates.

C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Profiler\Templates\Microsoft SQL Server\110

You guessed it right! You just need to copy the user defined template from
C:\Users\UserName\AppData\Roaming\Microsoft\SQL Profiler\11.0\Templates\Microsoft SQL Server\110
To
C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Profiler\Templates\Microsoft SQL Server\110

Hope you enjoyed this article and feel free to share if you think it is helpful. Please let me know if you have any questions on this.

You might also want to check Myth around Remote Query Timeout Option

Tagged as: SQL Server Profiler

{ Add a Comment }

SQL Cluster Instance Failover Time and Node Name prior to Failover

by Dev on

You may have come across situations where SQL Server cluster instance has failed over from one node to other and you were asked to find the SQL Cluster Instance failover time and node name prior to failover.

Who can ask this question? Your approver, manager or colleague anyone can ask you for different reasons, to know since when the instance is running on wrong/passive node, what alert system you have and it worked properly or not etc.

Whatever may be the reason, you want to find it quickly. In this article I am going to explain how to find the required information from SQL error log.

You need to search for the messages containing the text NETBIOS in the error log files.

Launch SSMS (SQL Server Management Studio) and start with the first error log to view as shown below:

Click on Filter and type in “netbios” against the field “Message Contains Text”. Mark the check box for Apply filter and click on OK as shown below. The message you are searching for, can be typed in Capital/Lower case.

Start checking each error logs until you find the required information as shown in the following screen shot.

The Message is like:

“The NETBIOS name of the local node that is running the server is ‘NodeName’. This is an informational message only. No user action is required.”

The message provides the information about the current Node, hosting the SQL Server Instance and the time it came up on the Node. Hence you need to keep checking the prior error logs which will show a different Node name and time in case it failed over from other node.

In the above example it shows the particular SQL Instance was running on Node “N02” prior to the failover.

The following information you can find using the NETBIOS filter:

What is the current node? (N01)
Is failover happened? Yes, as it shows a different node (N02) in prior error log file.
Which node it was running prior to failover?(N02)
What time failover started (Between 9/9/2017 11:28:10 PM the instance was on N02 and 9/9/2017 11:47:07 PM the instance started on No1. Check the error log around the time removing the filter and you will be able to find the exact time as shown in the below screen shot.)

You can also find what time SQL Instance came online/ what is the last SQL restart time. Of course you can find it from tempdb creation time? (Date 9/9/2017 11:47:07 PM the instance started on No1).