Search this blog

Thursday, May 15, 2014

To Lists all the Databases by SQL

How can I view the List of databases that available in a SQL server instance via query?

There are many ways to see all the databases, here i've listed 3 types

1. master..sysdatabases - system table added as view for backward compatabilty

2. sys.databases - view available in master & user databases. minimum permission is enough to view the output

3. sp_databases - Inbuilt stored Procedure, to fetch DB lists.
Requires CREATE DATABASE, or ALTER ANY DATABASE, or VIEW ANY DEFINITION permission, and must have access permission to the database. Cannot be denied VIEW ANY DEFINITION permission. everyone can not access this SP, if you dont have permission, then you will see blank rows.
USE master;
GO

SELECT Name FROM master..sysdatabases  

SELECT * from sys.databases

EXEC sp_databases; 

Sample Output: 

Monday, May 12, 2014

SSRS: Deserialization failed: The 'DataType' attribute is not declared

Problem:
Today, We have downloaded a report from Report Manager, If try to open that in Visual Studio IDE, it throws error as "Deserialization failed: The 'DataType' attribute is not declared. Line 1279, position 20.". (refer the exhibit below)


Cause:
When I reviewed the code (By Click Edit code or View code), I found that DataType attribute was not recognized by report render engine. so I guess DataType was an attribute of report element and it has introduced by VS2008 IDE, this has not been corrected and performing some action in the report designer while add the DataType attribute which is not allowed in the schema.



Solution: 
 To fix this issue, I have replaced the DataType with blank as mentioned in the screenshot below

after replaced it, when I check the report via designer, it render the report content properly.

Note: 
I faced this issues with float datatype, you may experience with anyother datatype like Integer, Strings, Date, etc., you can follow the same approach. Note that, before try this approach, backup your report to avoid any surprises and then try this. Hope this may helps you!

Tuesday, March 11, 2014

Table Modified/Updated Details

Scripts to find when Last transactions are done in a table:
SELECT
      last_user_update
FROM
      sys.dm_db_index_usage_stats
WHERE
      object_id=object_id('TableName')
Scripts to find when the table's Schema are changed last:
SELECT
      name as Table_Name,
      [modify_date]
FROM
      sys.tables

Monday, March 10, 2014

Cannot login to SSAS 2008 from SSMS?

Issue: 
When I tried to connect to a named instance of SQL Server 2008 Analysis Services (SSAS) by using SQL Server Management Studio, I received the following error message intermittently:

The 'InstanceName' instance was not found on the 'ServerName' server. (Microsoft.AnalysisServices.AdomdClient)

Solution:
 When I checked my services all the services was up & running, even I had restarted my SSAS services, but it didn't help me. but the issue was resolved after restart the SQL Server Browser service. 


If you aware, how to to restart any windows services that related SQL server then Ignore the steps below:

Steps to Restart "SQL Server Browser" service:
1.On the Start menu, right-click My Computer, and then click Manage.
2.In Computer Management, expand Services and Applications, and then click Services.
3.In the list of services, double-click SQL Server Browser.
4.In the SQL Server Browser Properties window, click Start or Stop.
5.When the service starts or stops, click OK.


After restarting the "SQL Server Browser" service, I tried to connect SSAS via SSMS, it works. Hope it may help you.