SansSQL

Monday, June 30, 2014

The connected user is not an Analysis Services server administrator

Consider a situation where you have installed the SQL Server Analysis Services and during installation, you forget to add the Analysis Services server administrator.
When you connect to the analysis services and try to do some administrative tasks then you are presented with the error "The connected user is not an Analysis Services server administrator."
So now you do not have administrative privilege and no one is added into this rights and how to fix it?
  • Start SQL Server Management Studio as administrator >> "Run as Administrator" 
  • Go to "Security" Page
  • Add user to the "Server Administrators" group
Related Posts Widget For Blogger with ThumbnailsBlogger Templates

Tuesday, May 27, 2014

Reporting Services Error - The report server installation is not initialized. (rsReportServerNotActivated)

Recently, I had a task for restoring the Reporting Services database from a live environment to a test environment.
Right after the successful restore, I tried opening the Report Manager website and I was presented with the error "The report server installation is not initialized. (rsReportServerNotActivated)"

This is because of the mismatch in the encryption key.
To fix this, either restore the encryption key from the live environment or delete the encryption key from the restored reporting services database.
The restoration or deletion of encryption key can be done by using the Reporting Services Configuration Manager.


Monday, May 26, 2014

T-SQL to find backup location

Here is a T-SQL query to find the location of the backup files.

SELECT database_name AS DBName
    ,physical_device_name AS BackupLocation
    ,CASE WHEN [TYPE]='D' THEN 'FULL'  
    WHEN [TYPE]='I' THEN 'DIFFERENTIAL' 
    WHEN [TYPE]='L' THEN 'LOG'
    WHEN [TYPE]='F' THEN 'FILE / FILEGROUP'
    WHEN [TYPE]='G'  THEN 'DIFFERENTIAL FILE'
    WHEN [TYPE]='P' THEN 'PARTIAL'
    WHEN [TYPE]='Q' THEN 'DIFFERENTIAL PARTIAL'
  END AS BackupType
    ,backup_finish_date AS BackupFinishDate
FROM msdb.dbo.backupset JOIN msdb.dbo.backupmediafamily
ON(backupset.media_set_id=backupmediafamily.media_set_id)
ORDER BY backup_finish_date DESC

Tuesday, May 20, 2014

T-SQL to find the SQL Server Installation date

Here is an handy T-SQL to find the installation date of SQL Server.

DECLARE @date AS VARCHAR(50), @ServerName AS VARCHAR(100)

SELECT @ServerName = CAST(SERVERPROPERTY('SERVERNAME') AS VARCHAR)

SELECT @date = convert(varchar(50),create_date,107) 
FROM sys.server_principals
WHERE name='NT AUTHORITY\SYSTEM'

PRINT 'This SQL Server Instance "' + @ServerName + '" was insalled on '+ @date 

Sunday, May 11, 2014

The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. (System.Data)

When trying to load an excel spread sheet into SQL Server you may be presented with the below error.


This occurs because the Office System Driver and Data Connectivity Components are not installed on the server and SQL Server is unable to interact with the MS Office document. For the SQL Server to interact, open and load the data from excel spreadsheet, it requires the Office System Driver and Data Connectivity Components to be installed.

To fix this issue, download and install the Office System Driver and Data Connectivity Components from the below link.
http://www.microsoft.com/en-us/download/details.aspx?id=23734

This will install a set of components that can be used to facilitate transfer of data between Microsoft Office System files and non-Microsoft Office applications.