Advertisements

SQL-BOX.COM

–SQL Server Storage

How to run a SQL Query on list of servers and save output to excel using power shell?

You will see  how much is powershell is useful for DBA  after using this script.

Step 1: Create a text file with list of servers you want to run the script on and name it ‘serverlist’ to C:\serverlist.txt

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


# Script Runs on servers listed in serverlist.txt
# use sqlps.exe to run this script
# In cmd, sqlps.exe c:\scripts\script.ps1

$query = " Your SQL Query"

#Path to the excel file to be saved.

$csvFilePath = "c:\Scripts\queryresults.csv"
$excelFilePath = "c:\scripts\queryresults.xls"

# Run Query against multiple servers, combine results
# Replace "Server1", "Server2\INSTANCE1" with names of your SQL Server instances

$instanceNameList = get-content c:\serverlist.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 : <a href="http://gallery.technet.microsoft.com/scriptcenter/da4c725e-3487-42ff-862f-c022cf09c8fa">http://gallery.technet.microsoft.com/scriptcenter/da4c725e-3487-42ff-862f-c022cf09c8fa</a>

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 to run against multiple servers”  

Step 4: Open CMD and run the script like this

Sqlps.exe c:\scripts\script.ps1

The results from the SQL Query from each server will be combined and saved as queryresults.xls into your scripts folder.

Note :The script uses windows authentication to connect to the SQL servers.

Advertisements

What “Open Table “in SQL Server 2005 replaced with in SQL Server 2008? How to change this Edit top 200 to edit all?

 

Today, a customer asked me how to open a table in SQL Server 2008 to edit the rows in a table manually as she is able to do it in SQL 2005 using open table option in right click of table but in SQL Server 2008 it was replaced with edit top 200 rows. Initially I though there is no way to change from the default option Edit top 200 to all but after searching in BOL ,I found this

 

SSMS -> Tools -> Options-> SQL Server Object Explorer-> Commands-> Set value 0 for Value for Edit Top Rows Command

 

Note: Setting value 0 lets you retrieve all or you can customize this value to your requirement.                 

 

Source: BOL

 

HOW TO VERIFY IF A BACKUP FILE IS CORRUPTED?

There are times when we do a backup as a DBA, we come across problems when we restore the backup we made.

We may get error like this

Msg 3242,Level 16,State 2, Line 1

The fileon device C:\DBBackupCorrupted.BAK isnot a valid Format. The backup file made is not properly formed. 

Msg 3013,Level 16,State 1, Line 1

VERIFY DATABASEis terminating abnormally.

If we do not want this to get error when we restore. Always good to verify our backup file immediately after the backup is made and checkwhether it is corrupted or not.

In order  to verify run this command on a backup file

RESTORE VERIFYONLY FROM DISK = '<<location of database backup>>'

The above query results in vaid or not valid backup we have. We can sleep happily with the backup we have.

 

 

 

HOW TO CHECK IF A USER ACCOUNT IS LOCKED? IF LOCKED HOW TO UNLOCK IT?

There are times a DBA gets a request to see the user account got locked or the user is unable to login.

Here is the simple script to see if a login account is locked or not.

Use Master
Go
select loginproperty('UserName','Islocked')
GO

If  the script above returns

1- Account got Locked

0- Not Locked

If Retuns 1 and need to Unlock the account Run the below script.

ALTER LOGIN [UserName] WITHPASSWORD='****'UNLOCK;
GO

 

 

 

 

 

 

Finally SQL Server 2012 is Out now

After several CTP releases of code named “denali ” is now official released as SQL Server 2012.

You can download the RTM version of SQL Server 2012 from here

24 Hours PASS Training Video Recordings

Click on Image below to check out the recorded training videos from experts for who missed it out…

script to merge column rows to single row seperated by comma,

Using STUFF function in sql serevr a column values can be retrieved as single value row, values seperated by comma’s.

 

SELECT STUFF( (
SELECT ', ' + [Name] FROM -- create comma separated values(
SELECT [Name] FROM sys.databases WHERE database_id >6 --Your query here
) T FOR XML PATH('')
) ,1,1,'') AS [Name]
Go
STUFF- Reference 

 

SQL Server management studio (SSMS) is invisible !

Today I was messing with the SQL Server root folder in program files in looking for tablediff  .exe utility that ships with SQL Server installation but accidently I moved the tools folder in C:\Program Files\Microsoft SQL Server\100\  to the other folder shared in the same root then I came back and looking for my SSMS in the windows shortcuts but I couldn’t find it   and surprisingly when I type ssms in run as it is 2008 version  , even this experiment also could not find  SSMS. And all my .sql files are became orphaned with unknown file types , then after sometime I found that I moved the folder tools to shared folder in the above directory path then I replaced the folder back to its original path then the SSMS is able to visible.

Might be help full…

%d bloggers like this: