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;

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

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)

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.

 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.

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!