SQLGear

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:

When ever you are renaming a database object you want to make sure you have changed all the code referencing that object. Following is the T-SQL to find a phrase of word in all the database object codes: DECLARE @Text AS NVARCHAR(50) SET @Text = 'DBObjectName' /*Set the variable with the DB Object name, Linked Server name, Database name or just a word string to be searched*/ SELECT DISTINCT sysobjects.NAME ,sysobjects.xtype ,SUBSTRING(syscomments.TEXT, CHARINDEX(@Text, syscomments.TEXT) - ( CASE WHEN CHARINDEX(@Text, syscomments.TEXT) It will give you the name of the object referencing it. Type of the object and also extract of the code around the name of the database object for your reference.

If you have just set up the SQL server 2008 cluster server you will find a job namely SYSPOLICY_PURGE_HISTORY already created under the jobs folder. By default it would be scheduled for daily run. The very next day if you see the job history you will find that this job has failed. If so then  check the PowerShell script inside the job if it refers to the node name as below: (Get-Item SQLSERVER:\SQLPolicy\NodeName\DEFAULT).EraseSystemHealthPhantomRecords() Make this check as a part of you SQL Server Cluster setup checklist. To resolve the failure modify the script by replacing the NodeName with VirtualClusterName  as below: (Get-Item SQLSERVER:\SQLPolicy\VirtualClusterName\DEFAULT).EraseSystemHealthPhantomRecords() This will resolve the issue.

I frequently get request to provide RDLs to developer teams. They do have repository for most of the servers but for those servers which do not have any repository and you need to download RDLs from certain folder it get tedious if there are numerous reports under a folder and its sub folder. The below query will download reports under each folder and subfolder and create same folder structure in the mentioned download folder on the ReportServer. Note: xp_cmdshell should be enabled on the server. --//-- www.SQLGear.net --//-- DECLARE @FullDirectoryPathStatement AS VARCHAR(500) = 'C:\DownloadReport\' SET @FullDirectoryPathStatement = LTRIM(RTRIM(@FullDirectoryPathStatement)) IF RIGHT(@FullDirectoryPathStatement, 1) = '\' SET @FullDirectoryPathStatement = SUBSTRING(@FullDirectoryPathStatement, 1, LEN(@FullDirectoryPathStatement) - 1) DECLARE @FilterReportPath AS VARCHAR(500) = NULL DECLARE @SSRSFolderPath NVARCHAR(850); DECLARE @SSRSFolder NVAR...

Recently trying to restore a database following message was thrown: System.Data.SqlClient.SqlError: File 'F:\DATA\DatabaseName.mdf' is claimed by 'DBFileGroup1'(3) and 'DBFile'(1). The WITH MOVE clause can be used to relocate one or more files. (Microsoft.SqlServer.Smo) The issue might be because of the restore process using same physical file name and path for two different database files. Go to the options tab and see. Following is the screenshot: In any file system there would be no physical files with same name in any given folder. This is why the error comes. Just modify the file name as per your requirement as below: Now hit the OK button. This will restore the database without any error.