SQLGear

If you are facing the error while executing sp_updatestas inspite being in database owner role: Msg 15247, Level 16, state 1, procedure sp_updatestats, Line 15 User does not have permission to perform this action. Then following link mentions the workaround http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=436562

A formatted T-SQL code makes it easier to understand and debug, especially when it is too lengthy. There are many T-SQL code related plugins available for SQL Server management studio. One of the free tool is Notepad++ . It is free you can download it and then add "Poor Man's T-SQL Formatter". From Plugins menu inside Notepad++ add "Poor Man's T-SQL Formatter" from Plugin Manager as shown below:   Now to format the code select the options as below: Your formatted code will now look as below:

Make sure Powershell is installed and the execution policy is remote signed. To know more about how to check and set execution policy, see my post on  Executing a PowerShell script . Schedule the PowerShell script execution using the Windows task scheduler. Suppose your PowerShell script file is Main.ps1 and is located at “D:\Scripts” folder then following would be the command to run Powershell.exe D:\Scripts\Main.ps1

Following is the PowerShell script to list details of SQL Instances installed on a host. It gets details of even 32 bit installed on 64 bit host. Following is the script: $ComputerName=get-content env:computername $sqlInstLst = @() echo $ComputerName $RegPath = 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL' if (Test-Path $RegPath){      try{          Get-ItemProperty -Path $RegPath      }     catch{           Write-Error $_.Exception.Message           return $false     } } else{      echo $ComputerName+'does not have SQL Server installed' } $RegPath = 'HKLM:\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\Instance Names\SQL' if (Test-Path $RegPath){      echo '32 bit instances on'+$ComputerName      try...

When you are preparing for SQL Server inventory with any tool you will either have option to read entire AD for Server having SQL Server installed or you will need to feed the tool with a file having list of all the servers which you want to scan. Following is the PowerShell script to list all the windows machines from you domain: $objDomain = New-Object System.DirectoryServices.DirectoryEntry $objSearcher = New-Object System.DirectoryServices.DirectorySearcher $objSearcher.SearchRoot = $objDomain $objSearcher.PageSize = 9000 # Mention here how many results you need $objSearcher.Filter = '(OperatingSystem=*Window*)' # Filter for computer having words "Windows" in their operating system # Following options also can be used # '(OperatingSystem=*Window*Server*)'  # Above filter is for computer having words "Windows" and "Server" in their operating system # '(OperatingSystem=*Window*Server*2008*)'  # Above filter is for comput...

We had the requirement to migrate our cluster to a different domain. We knew that Microsoft does not recommend to migrate the existing cluster to a different domain (Read http://support.microsoft.com/kb/269196 ). The business could not afford the time to build a new cluster in the target domain and move the databases to the SQL Server instance created on target cluster. We uninstalled the SQL server after backing up each database. Server team removed the nodes from the cluster and then moved each node to a new domain. We then installed the SQL Server and attached (since time was constraint) each databases back. All the databases were running fine and we could use them. We restored the master and msdb database so that all the logins, jobs and integration packages are back in place. Later while setting up distribution for replication we faced the first error: There is no remote user 'distributor_admin' mapped to local user '(null)' from the remote server 'repl_dist...

Following is the query to list all the orphan users in a SQL Server database: