ALL_USERS vs. DBA_USERS vs. USER_USERS

There are 3 different sets of views ALL_, DBA_, and USER_.

ALL_ views display all the information accessible to the current user, i.e. it can looks at all the shcemas the user has permissions to, DBA_ views display infor for the entire database and is intended only for admins. Then USER_ views display info from the schema of the current user.

Error when exporting data to Excel

While exporting data from SQL Server 2005 to Excel, I got an error "Columns "Field1" and "F1" cannot convert between unicode and non-unicode string data types". I was exporting data returned by a particular view. In order to fix the issue I had to modify the view, utilizing CAST function to cast the columns to their existing data type. Weird, huh?

Here is my original view. Please note that Field1 and Field2 are of type varchar(5) and varchar(100) respectively:

CREATE VIEW [dbo].[myView]
AS
SELECT Field1,
Field2
FROM Table1

Here is what I modified my view to and what worked for me:

CREATE VIEW [dbo].[myView]
AS
SELECT CAST(Field1 As Varchar(50)) as Field1, 
CAST (Field2 as Varchar(100)) As Field2
FROM Table1

What is INFORMATION_SCHEMA?

System views are predefined Microsoft created views for extracting SQL Server metadata. System Views can be found under System Databases -> master -> Views -> System Views.
The first group of System Views belongs to the Information Schema set. INFORMATION_SCHEMA contains 20 different views. Most of the Information Schema view names are self-explanatory. For example INFORMATION_SCHEMA.TABLES returns a row for each table. INFORMATION_SCHEMA.COLUMNS returns a row for each column.

INFORMATION_SCHEMA is contained in each database and each INFORMATION_SCHEMA view contains meta data for all data objects stored in that particular database. For example one can retirieve all the constraint information on a particular table etc.