PivotViewer Extension for Reporting Services

PivotViewer Extension for Reporting Services is a utility that enables users to effortlessly build stunning data experiences on top of their Business Intelligence data.

Please visit Microsoft web site for more information about Pivot viewer.
http://www.microsoft.com/sqlserver/tour/en/videos/pivot-viewer.aspx

You Cannot drop the guest user from master or tempdb

---------------------------
Microsoft SQL-DMO (ODBC SQLState: 42000)
---------------------------
Error 15182: Cannot drop the guest user from master or tempdb.
---------------------------
OK
---------------------------

Find Database collation

-- List SQL Server default collation
sp_helpsort

Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page 1252 for non-Unicode Data

select SERVERPROPERTY('Collation')
SQL_Latin1_General_CP1_CI_AS



-- Find a collation name of users database
SELECT CONVERT (varchar, DATABASEPROPERTYEX('database_name','collation'))
eg:
-- find master database collation
SELECT CONVERT (varchar, DATABASEPROPERTYEX('master','collation'))


--List out all Collations
SELECT * from ::fn_helpcollations()

SQL ServerProperty Info

SELECT SERVERPROPERTY('ServerName')
ASIAPAC06

SELECT SERVERPROPERTY('Machinename')
ASIAPAC06


SELECT SERVERPROPERTY('edition')
Enterprise Evaluation Edition

SELECT SERVERPROPERTY('enginedition')
NULL

SELECT SERVERPROPERTY('Instancename')
NULL

SELECT SERVERPROPERTY('ProductVersion')
10.50.1600.1

SELECT SERVERPROPERTY('ProductLevel')
RTM

SELECT SERVERPROPERTY('ResourceVersion')
10.50.1600

SELECT SERVERPROPERTY('ResourceLastUpdateDateTime')
2010-04-02 17:38:24.957

Creating Database Snapshot on SQL Server 2005

When you Creating Database Snapshot on SQL Server 2005 Standard Edtion you will receive the following error message. This feature will available on SQL Server Enterprise Edition only.

--Error
Msg 1844, Level 16, State 1, Line 1
Database Snapshot is not supported on Standard Edition.


-- Script to Create Database Snapshot
CREATE DATABASE NorthWindb_test_Snapshot
ON ( NAME = N'NorthWindb_test_datafile',
FILENAME = N'C:\MSSQL\Data\NorthWindb_test_Snapshot.snap' ) AS SNAPSHOT OF NorthWindb_test;

How to check Transaction log space used in SQL Server Databases?

Displays Transaction Logspace information for all databases in the current instance.

dbcc sqlperf(logspace)
go

SQL Server 2008 Express Edition Storage Limit

SQL Server 2008 Express Edition supports 10 GB database storage.

Designed to work with Visual Studio and ASP.NET.

Difference between sysxlogins and syslogins in SQL 2000 and 2005

In SQL 2005
System table sysxlogins(master database) is removed from SQL 2005.
and SysObjects and SysUsers are changed from System table to View



-- In SQL 2000
use master
go
select * from sysobjects where name in ('sysxlogins' ,'syslogins') order by xtype
select * from sysobjects where name in ('sysobjects','sysusers') order by xtype


sp_help sysxlogins --system table
go
sp_help syslogins-- view

sp_help sysobjects --system table
go
sp_help sysusers --system table
----------------------------------------------------------

-- In SQL 2005
use master
go
select * from sysobjects where name in ('sysxlogins' ,'syslogins') order by xtype
select * from sysobjects where name in ('sysobjects','sysusers') order by xtype


sp_help sysxlogins --system table is REMOVED no longer exists
go
sp_help syslogins-- view


sp_help sysobjects --VIEW
go
sp_help sysusers --VIEW