Sunday, May 17, 2020

How to run SQL Query on Multiple MSSQL servers using windows authentication and save result to excel

Step 1: Create a notepad with list of SQL servers on which you want to execute the SQL statement and save it as SQL_servers.txt on D:\DBA_Scripts location. 

Server names can be as follows in text file.

server1,port_number,
server2,port_number
server2\instance_name2,port_number
.
.
server_n,port_number


Step 2: I have Created a folder called DBA_Scripts in my D: then Copy the below script into another text file and save it as D:\DBA_Scripts\script.ps1 

=======================================================
# Script Runs on servers listed in SQL_servers.txt
# use sqlps.exe to run this script
# In cmd, sqlps.exe D:\DBA_Scripts\script.ps1

$query = " Your SQL Query"

#Path to the excel file to be saved.

$csvFilePath = "D:\DBA_Scripts\queryresults.csv"
$excelFilePath = "D:\DBA_Scripts\queryresults.xls"


$instanceNameList = get-content D:\DBA_Scripts\SQL_servers.txt
$results=@()

foreach($instanceName in $instanceNameList)
{
write-host "Executing query against server: " $instanceName
$results += Invoke-Sqlcmd -Query $query -ServerInstance $instanceName
}

# Output to CSV

write-host "Saving Query Results in CSV format..."
$results | export-csv $csvFilePath -NoTypeInformation
# Convert CSV file to Excel
# Reference : http://gallery.technet.microsoft.com/scriptcenter/da4c725e-3487-42ff-862f-c022cf09c8fa

write-host "Converting CSV output to Excel..."

$excel = New-Object -ComObject excel.application
$excel.visible = $False
$excel.displayalerts=$False
$workbook = $excel.Workbooks.Open($csvFilePath)
$workSheet = $workbook.worksheets.Item(1)
$resize = $workSheet.UsedRange
$resize.EntireColumn.AutoFit() | Out-Null
$xlExcel8 = 56
$workbook.SaveAs($excelFilePath,$xlExcel8)
$workbook.Close()
$excel.quit()
$excel = $null

write-host "Results are saved in Excel file: " $excelFilePath
=========================================================
Step3: Copy and paste the SQL Query into the above script where it says like this 
$query = ” Your SQL Query "  
Step 4: Open CMD and run the script like this
Sqlps.exe D:\DBA_scripts\script.ps1
The results from the SQL Query from each server will be combined and saved as queryresults.xls into your DBA_scripts folder.

References: Pavan's Blog

Tuesday, February 14, 2017

MSSQL: CheckDB failed for large user database which is of 2 TB

On one of the weekend, I received call from our team member who was in support that CheckDB maintenance job got failed on a large user database whose size was around 2 TB.

We have identified the table 'Table_abc' on which there is consistency issue from the SQL Error log based on the object_id. Table which got corrupted is of 50 GB. We ran below command to see if it fixes the issue.

DBCC CHECKTABLE (<table_name>, REPAIR_REBUILD) WITH ALL_ERRORMSGS.
GO

To verify whether still consistency error exists, we ran below command.

DBCC CHECKTABLE ('dbo.Table_abc');    
GO

But it was of no use still there was consistency error.

We created a new table 'Table_abc_new' and imported data into it using Export and Import wizard (BCP option also can be used).

Later created indexes and constraints similar to the old table 'Table_abc' and ran DBCC CHECKTABLE on the newly created table and there was no consistency issue.

Renamed the table 'Table_abc' to 'Table_abc_Old' and also renamed Table_abc_new' to 'Table_abc'.

Post Application team confirmation and after appropriate cooling period, dropped 'Table_abc_old' from the database.

You may refer below links for more information about CHECKDB and Repair options:
1. https://www.sqlskills.com/blogs/paul/misconceptions-around-database-repair/
2. https://msdn.microsoft.com/en-us/library/ms176064.aspx

Saturday, February 11, 2017

Typical Issue with SQL server Agent-- Agent Jobs not starting internally

Today I faced a typical isue with SQL agent.

When I am invkoing a job (by right clicking on the job and selecting start) it is displaying as executing. But I cannot find any proces ID related to it in the sysprocesses.

When I click on the job history also, I cannot find the history for that job.

When checked in the SQL Agent log file foudn below messages.

![298] SQLServer Error: 87, SQL Server Network Interfaces: Connection string is not valid [87]. [SQLSTATE 08001]
! [165] ODBC Error: 0, Login timeout expired [SQLSTATE HYT00]
![298] SQLServer Error: 87, A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. [SQLSTATE 08001]
! [382] Logon to server '' failed (ConnLogJobHistory)
! [298] SQLServer Error: 87, SQL Server Network Interfaces: Connection string is not valid [87]. [SQLSTATE 08001]
! [165] ODBC Error: 0, Login timeout expired [SQLSTATE HYT00]
! [298] SQLServer Error: 87, A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. [SQLSTATE 08001]
! [382] Logon to server '' failed (ConnAttemptCachableOp)
! [298] SQLServer Error: 87, SQL Server Network Interfaces: Connection string is not valid [87]. [SQLSTATE 08001]
! [165] ODBC Error: 0, Login timeout expired [SQLSTATE HYT00]
! [298] SQLServer Error: 87, A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. [SQLSTATE 08001]
! [382] Logon to server ''failed (ConnLogJobHistory)
! [298] SQLServer Error: 87, SQL Server Network Interfaces: Connection string is not valid [87]. [SQLSTATE 08001]
! [298] SQLServer Error: 87, SQL Server Network Interfaces: Connection string is not valid [87]. [SQLSTATE 08001] "

Based on the above error log checked the Aliases under SQL Ntive client cofiguration in configuration manager.

Alias was created giving wrong port number, so SQL was not able to connect to SQL instance.

After rectifying the alias, my jobs worked without any issues.

Thursday, March 20, 2014

What is Windows Internal Database(MICROSOFT##SSEE)

What is Windows Internal Database(MICROSOFT##SSEE)

If you are running a Windows 2008/2008R2 server, you may come across a service called the Windows Internal Database (Sometimes referred to as “SQL Server Embedded Edition”, or see a reference to a database called MICROSOFT##SSEE and be wondering what it actually is, and how can you connect to it. This article describes both.

Windows Internal Database is a relational data store used by the following applications and services.

•Active Directory Rights Management Services (AD RMS)

•Windows Server Update Services

•Windows System Resource Manager

These applications and services share a single instance of the data store.

The Windows Internal Database is a varient of Microsoft SQL Server Express 2005, and is included with Windows Server 2008 and Windows Server 2008 R2. It is also included in Windows Sharepoint 3.0 and Windows Server Update Services (WSUS) 3.0/3.2, where a full version of Microsoft SQL Server may not be available. Other services can also use this database engine if needed, such as Active Directory Rights Managements Services, and Windows System Resource Manager. Once the Windows Internal Database has been installed, it can’t be uninstalled via the “add/remove features” wizard (it appears greyed out, so you can’t de-select it).

The tool used to connect to the Windows Internal Database is the same as used for the full version (and express editions) of Microsoft SQL Server, which is the SQL Server Management Studio (or SQL Server Management Studio Express). The main thing to note with the Windows Internal Database, is the fact that you cant make remote connections to the database. It is designed so that only the computer that has it installed, can access the databases. As such, the only connection method you can use to connect to a Windows Internal Database, is to use the Named Pipes protocol. When connecting to a Windows Internal Database, you must used the fairly obscure named pipes syntax, which will be listed below, as the server/instance name in SQL Server Management Studio.

The server/instance name to connect to is:
\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query

You must also use “Windows Authentication” as the authentication method to connect to this database.

If you get Login failure error with below error message "Error: 18456, Severity: 14, State: 11."

Please run the SQL Server Management Studio application by first right clicking the shortcut, and selecting “Run as Administrator”. Even if your account has admin rights on the server, it still may be necessary to perform this step.

Tuesday, March 11, 2014

Could not start Service Broker for database id

Could not start Service Broker for database id: 27. A problem is preventing SQL Server from starting Service Broker. Check the SQL Server error log for additional messages.


I ran into this issue and was happy to find this solution. Thought of sharing this fix and also for my future reference purpose.

When I checked in the error log, below message was logged.

Starting up database 'master4IDR'.
The database 'master4IDR' is marked RESTORING and is in a state that does not allow recovery to be run.
Starting up database 'master4IDR'.
CHECKDB for database 'master4IDR' finished without errors on 2014-03-10 22:07:42.393 (local time). This is an 22:52:21.10 Backup Database was restored: Database: master4IDR, creation date(time): 2014/03/08(23:03:49), first LSN: 78191:16:37, last LSN: 78191:40:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'RESTORE_restore_'}). Informational message. No user action required.
Could not start Service Broker for database id: 27. A problem is preventing SQL Server from starting Service Broker. Check the SQL Server error log for additional messages.


The windows and application logs do not give me any for information. I have checked the database instance and found that Database ID 27 does not exist in the sys.databases table.

Solution/Fix: To avoid this alert, I have set the "Enable Disaster Recovery File Updates" parameter to 0 in the regedit at the below location.

MY Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Symantec\Backup Exec For Windows\Backup Exec\Engine\SQL Server

For futher details, please refer to the below links.

1) http://www.symantec.com/connect/forums/backup-exec-2010-r3-idr-sql-backup-will-not-uninstall
2) http://www.symantec.com/business/support/index?page=content&id=TECH49015

Monday, March 3, 2014

The report server installation is not initialized

The report server installation is not initialized. (rsReportServerNotActivated)


when users were trying to access SSRS reports via the browser they got the following error :
"The report server installation is not initialized. (rsReportServerNotActivated)

Error: The report server installation is not initialized. (rsReportServerNotActivated)

Solution:

Checked the Scale-out Deployment tab in the Reporting services configuration manager where it displayed the status as not joined.

Restored the encryption keys with the previously generated backup key and now clicked on on scale out deployment which showed the status as joined.

After this change, users were able to browse the SSRS service.

The attempt to connect to the report server failed. Check your connection information and that the report server is a compatible version

The attempt to connect to the report server failed. Check your connection information and that the report server is a compatible version.


I ran into this issue and was happy to find this solution. Thought of sharing this fix and also for my future reference purpose.

Error: The attempt to connect to the report server failed. Check your connection information and that the reportserver is a compatible version.

Solution:
Found the below error message in the SSRS log file located at C:\MSSQL\MSRS10_50.MSSQLSERVER\Reporting Services\LogFiles.

e ERROR: RsHttpRuntime::VerifyAuthMode(): caught exception System.Configuration.ConfigurationErrorsException: The value of the property 'mode' cannot be parsed. The error is: The enumeration value must be one of the following: None, Windows, Passport, Forms. (C:\MSSQL\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer\web.config line 9)

I changed the authentication option to Windows (Previously it was none) in web.config file (located at C:\MSSQL\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer)and restarted the SSRS service which fixed the issue.

authentication mode="Windows"

Now users are able to browse the SSRS service.