| Date | Day | Holiday |
| 26-Jan-2012 | Thursday | Republic Day |
| 01-May-2012 | Tuesday | May Day |
| 15-Aug-2012 | Wednesday | Independence Day |
| 02-Oct-2012 | Tuesday | Gandhi Jayanthi |
| 26-Oct-2012 | Friday | Bakrid |
| 01-Nov-2012 | Thursday | Kannada Rajyotsava Day |
| 12-Nov-2012 | Monday | Naraka Chaturdashi/ Deepavali |
| 25-Dec-2012 | Tuesday | Christmas |
22 November 2011
Humor - Holiday List of 2012
17 November 2011
Drop all Database Connections
Use Master
Go
Declare @dbname sysname
Set @dbname = 'DBNAME'
Declare @spid int
Select @spid = min(spid) from master.dbo.sysprocesses
where dbid = db_id(@dbname)
While @spid Is Not Null
Begin
Execute ('Kill ' + @spid)
Select @spid = min(spid) from master.dbo.sysprocesses
where dbid = db_id(@dbname) and spid > @spid
EndScript to identify tables without Primary Key
SELECT name
FROM sysobjects
WHERE xtype='U'
AND objectproperty(object_id(name),'TableHasPrimaryKey')= 0
ORDER BY 1
or
FROM sysobjects
WHERE xtype='U'
AND objectproperty(object_id(name),'TableHasPrimaryKey')= 0
ORDER BY 1
or
USE DatabaseName;
GO
SELECT SCHEMA_NAME(schema_id) AS SchemaName,name AS TableName
FROM sys.tables
WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasPrimaryKey') = 0
ORDER BY SchemaName, TableName;
GO Move Temp DB
use master
go
Alter database tempdb modify file (name = tempdev, filename = 'C:\Workarea\MDFLDF\2005\MDF\tempdb.mdf')
go
Alter database tempdb modify file (name = templog, filename = 'C:\Workarea\MDFLDF\2005\MDF\templog.ldf')
go
go
Alter database tempdb modify file (name = tempdev, filename = 'C:\Workarea\MDFLDF\2005\MDF\tempdb.mdf')
go
Alter database tempdb modify file (name = templog, filename = 'C:\Workarea\MDFLDF\2005\MDF\templog.ldf')
go
Who Used SQL Server WHEN
SELECT
I.NTUserName,
I.loginname,
I.SessionLoginName,
I.databasename,
Min(I.StartTime) as first_used,
Max(I.StartTime) as last_used,
S.principal_id,
S.sid,
S.type_desc,
S.name
FROM
sys.traces T CROSS Apply
::fn_trace_gettable(CASE
WHEN CHARINDEX( '_',T.[path]) <> 0 THEN
SUBSTRING(T.PATH, 1, CHARINDEX( '_',T.[path])-1) + '.trc'
ELSE T.[path]
End, T.max_files) I LEFT JOIN
sys.server_principals S ON
CONVERT(VARBINARY(MAX), I.loginsid) = S.sid
WHERE
T.id = 1 And
I.LoginSid is not null
Group By
I.NTUserName,
I.loginname,
I.SessionLoginName,
I.databasename,
S.principal_id,
S.sid,
S.type_desc,
S.name
I.NTUserName,
I.loginname,
I.SessionLoginName,
I.databasename,
Min(I.StartTime) as first_used,
Max(I.StartTime) as last_used,
S.principal_id,
S.sid,
S.type_desc,
S.name
FROM
sys.traces T CROSS Apply
::fn_trace_gettable(CASE
WHEN CHARINDEX( '_',T.[path]) <> 0 THEN
SUBSTRING(T.PATH, 1, CHARINDEX( '_',T.[path])-1) + '.trc'
ELSE T.[path]
End, T.max_files) I LEFT JOIN
sys.server_principals S ON
CONVERT(VARBINARY(MAX), I.loginsid) = S.sid
WHERE
T.id = 1 And
I.LoginSid is not null
Group By
I.NTUserName,
I.loginname,
I.SessionLoginName,
I.databasename,
S.principal_id,
S.sid,
S.type_desc,
S.name
Search all columns of all tables in a database for the keyword
CREATE PROC SearchAllTables
(
@SearchStr nvarchar(100)
)
AS
BEGIN
-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com
-- Tested on: SQL Server 7.0 and SQL Server 2000
-- Date modified: 28th July 2002 22:50 GMT
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
SET NOCOUNT ON
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)
WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)
IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END
SELECT ColumnName, ColumnValue FROM #Results
END
--To search all columns of all tables in Pubs database for the keyword "Computer"
EXEC SearchAllTables 'Computer'
GO
(
@SearchStr nvarchar(100)
)
AS
BEGIN
-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com
-- Tested on: SQL Server 7.0 and SQL Server 2000
-- Date modified: 28th July 2002 22:50 GMT
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
SET NOCOUNT ON
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)
WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)
IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END
SELECT ColumnName, ColumnValue FROM #Results
END
--To search all columns of all tables in Pubs database for the keyword "Computer"
EXEC SearchAllTables 'Computer'
GO
Rebuild and Reorganize Script
--Start of variables
DECLARE @dbName sysname
DECLARE @tableName sysname
DECLARE @databaseID INT
DECLARE @objectID INT
DECLARE @indexID INT
DECLARE @fragPercent INT
DECLARE @cmd VARCHAR(5000)
DECLARE @objectName sysname
DECLARE @indexName sysname
-- End of Variables
-- The databaseCursor cursor selects the user databases
DECLARE databaseCursor CURSOR FOR
SELECT name FROM sys.databases
WHERE name NOT IN ('master','msdb','model','tempdb','AdventureWorks2008','AdventureWorksDW','AdventureWorksDW2008','AdventureWorksLT','AdventureWorksLT2008','SQL2008SBS', 'SQL2008SBSFS')
OPEN databaseCursor
FETCH NEXT FROM databaseCursor INTO @dbName
WHILE @@FETCH_STATUS = 0
BEGIN
/* Selecting the required columns in the statsInfoTable table
for storing the selected values for the database. The table will be dropped
and recreated for each database*/
SELECT database_id, object_id, index_id, avg_fragmentation_in_percent
INTO statsInfoTable
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10 and index_id > 0
-- Declaring cursor for selecting each value from the statsInfoTable
DECLARE physicalStatsCursor CURSOR FOR
SELECT database_id, object_id, index_id, avg_fragmentation_in_percent
FROM statsInfoTable
OPEN physicalStatsCursor
FETCH NEXT FROM physicalStatsCursor INTO @databaseID, @objectID,@indexID,@fragPercent
WHILE @@FETCH_STATUS = 0
BEGIN
-- Select the Table name and Index name. The selected values will be
-- used for building the dynamic query for the Reorganize or Rebuild.
-- Reorganize is the online operation and Rebuild is the offline operation
SELECT @objectName = s.name + '.' + t.name FROM sys.objects t
JOIN sys.schemas s
ON t.schema_id = s.schema_id
WHERE t.object_id = @objectID
SELECT @indexName = i.name
FROM sys.indexes i
JOIN sys.objects o
ON i.object_id = o.object_id
WHERE o.object_id = @objectID AND i.index_id = @indexID
IF (@fragPercent > 10 AND @fragPercent < 30)
BEGIN
PRINT 'Rorganizing Index' + @indexName + 'ON' + @objectName
SET @cmd = 'ALTER INDEX' + ' ' + @indexName + ' ' + 'ON' + ' ' + @objectName + ' ' + 'REORGANIZE'
PRINT @cmd
EXECUTE (@cmd)
END
IF (@fragPercent = 30 OR @fragPercent > 30)
BEGIN
PRINT 'Rebuilding Index' + @indexName + ' ' + 'ON' + ' ' + @objectName
SET @cmd = 'ALTER INDEX' + ' ' + @indexName + ' ' + 'ON' + ' ' + @objectName + ' ' + 'REBUILD'
PRINT @cmd
EXECUTE (@cmd)
END
FETCH NEXT FROM physicalStatsCursor INTO @databaseID, @objectID,@indexID,@fragPercent
END
CLOSE physicalStatsCursor
DEALLOCATE physicalStatsCursor
FETCH NEXT FROM databaseCursor INTO @dbName
DROP TABLE statsInfoTable
END
CLOSE databaseCursor
DEALLOCATE databaseCursor
--select * from statsInfoTable
DECLARE @dbName sysname
DECLARE @tableName sysname
DECLARE @databaseID INT
DECLARE @objectID INT
DECLARE @indexID INT
DECLARE @fragPercent INT
DECLARE @cmd VARCHAR(5000)
DECLARE @objectName sysname
DECLARE @indexName sysname
-- End of Variables
-- The databaseCursor cursor selects the user databases
DECLARE databaseCursor CURSOR FOR
SELECT name FROM sys.databases
WHERE name NOT IN ('master','msdb','model','tempdb','AdventureWorks2008','AdventureWorksDW','AdventureWorksDW2008','AdventureWorksLT','AdventureWorksLT2008','SQL2008SBS', 'SQL2008SBSFS')
OPEN databaseCursor
FETCH NEXT FROM databaseCursor INTO @dbName
WHILE @@FETCH_STATUS = 0
BEGIN
/* Selecting the required columns in the statsInfoTable table
for storing the selected values for the database. The table will be dropped
and recreated for each database*/
SELECT database_id, object_id, index_id, avg_fragmentation_in_percent
INTO statsInfoTable
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10 and index_id > 0
-- Declaring cursor for selecting each value from the statsInfoTable
DECLARE physicalStatsCursor CURSOR FOR
SELECT database_id, object_id, index_id, avg_fragmentation_in_percent
FROM statsInfoTable
OPEN physicalStatsCursor
FETCH NEXT FROM physicalStatsCursor INTO @databaseID, @objectID,@indexID,@fragPercent
WHILE @@FETCH_STATUS = 0
BEGIN
-- Select the Table name and Index name. The selected values will be
-- used for building the dynamic query for the Reorganize or Rebuild.
-- Reorganize is the online operation and Rebuild is the offline operation
SELECT @objectName = s.name + '.' + t.name FROM sys.objects t
JOIN sys.schemas s
ON t.schema_id = s.schema_id
WHERE t.object_id = @objectID
SELECT @indexName = i.name
FROM sys.indexes i
JOIN sys.objects o
ON i.object_id = o.object_id
WHERE o.object_id = @objectID AND i.index_id = @indexID
IF (@fragPercent > 10 AND @fragPercent < 30)
BEGIN
PRINT 'Rorganizing Index' + @indexName + 'ON' + @objectName
SET @cmd = 'ALTER INDEX' + ' ' + @indexName + ' ' + 'ON' + ' ' + @objectName + ' ' + 'REORGANIZE'
PRINT @cmd
EXECUTE (@cmd)
END
IF (@fragPercent = 30 OR @fragPercent > 30)
BEGIN
PRINT 'Rebuilding Index' + @indexName + ' ' + 'ON' + ' ' + @objectName
SET @cmd = 'ALTER INDEX' + ' ' + @indexName + ' ' + 'ON' + ' ' + @objectName + ' ' + 'REBUILD'
PRINT @cmd
EXECUTE (@cmd)
END
FETCH NEXT FROM physicalStatsCursor INTO @databaseID, @objectID,@indexID,@fragPercent
END
CLOSE physicalStatsCursor
DEALLOCATE physicalStatsCursor
FETCH NEXT FROM databaseCursor INTO @dbName
DROP TABLE statsInfoTable
END
CLOSE databaseCursor
DEALLOCATE databaseCursor
--select * from statsInfoTable
Inventory Base LIne Script
--Step 1: Setting NULLs and quoted identifiers to ON and checking the version of SQL Server
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'prodver') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table prodver
create table prodver ([index] int, Name nvarchar(50),Internal_value int, Charcater_Value nvarchar(50))
insert into prodver exec xp_msver 'ProductVersion'
if (select substring(Charcater_Value,1,1)from prodver)!=8
begin
-- Step 2: This code will be used if the instance is Not SQL Server 2000
Declare @image_path nvarchar(100)
Declare @startup_type int
Declare @startuptype nvarchar(100)
Declare @start_username nvarchar(100)
Declare @instance_name nvarchar(100)
Declare @system_instance_name nvarchar(100)
Declare @log_directory nvarchar(100)
Declare @key nvarchar(1000)
Declare @registry_key nvarchar(100)
Declare @registry_key1 nvarchar(300)
Declare @registry_key2 nvarchar(300)
Declare @IpAddress nvarchar(20)
Declare @domain nvarchar(50)
Declare @cluster int
Declare @instance_name1 nvarchar(100)
-- Step 3: Reading registry keys for IP,Binaries,Startup type ,startup username, errorlogs location and domain.
SET @instance_name = coalesce(convert(nvarchar(100), serverproperty('InstanceName')),'MSSQLSERVER');
If @instance_name!='MSSQLSERVER'
Set @instance_name=@instance_name
Set @instance_name1= coalesce(convert(nvarchar(100), serverproperty('InstanceName')),'MSSQLSERVER');
If @instance_name1!='MSSQLSERVER'
Set @instance_name1='MSSQL$'+@instance_name1
EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL', @instance_name, @system_instance_name output;
Set @key=N'SYSTEM\CurrentControlSet\Services\' +@instance_name1;
SET @registry_key = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\MSSQLServer\Parameters';
If @registry_key is NULL
set @instance_name=coalesce(convert(nvarchar(100), serverproperty('InstanceName')),'MSSQLSERVER');
EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL', @instance_name, @system_instance_name output;
SET @registry_key = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\MSSQLServer\Parameters';
SET @registry_key1 = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\MSSQLServer\supersocketnetlib\TCP\IP1';
SET @registry_key2 = N'SYSTEM\ControlSet001\Services\Tcpip\Parameters\';
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@key,@value_name='ImagePath',@value=@image_path OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@key,@value_name='Start',@value=@startup_type OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@key,@value_name='ObjectName',@value=@start_username OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key,@value_name='SQLArg1',@value=@log_directory OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key1,@value_name='IpAddress',@value=@IpAddress OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key2,@value_name='Domain',@value=@domain OUTPUT
Set @startuptype= (select 'Start Up Mode' =
CASE
WHEN @startup_type=2 then 'AUTOMATIC'
WHEN @startup_type=3 then 'MANUAL'
WHEN @startup_type=4 then 'Disabled'
END)
--Step 4: Getting the cluster node names if the server is on cluster .else this value will be NULL.
declare @Out nvarchar(400)
SELECT @Out = COALESCE(@Out+'' ,'') + Nodename
from sys.dm_os_cluster_nodes
-- Step 5: printing Server details
SELECT
@domain as 'Domain',
serverproperty('ComputerNamePhysicalNetBIOS') as 'MachineName',
CPU_COUNT as 'CPUCount',
(physical_memory_in_bytes/1048576) as 'PhysicalMemoryMB',
@Ipaddress as 'IP_Address',
@instance_name1 as 'InstanceName',
@image_path as 'BinariesPath',
@log_directory as 'ErrorLogsLocation',
@start_username as 'StartupUser',
@Startuptype as 'StartupType',
serverproperty('Productlevel') as 'ServicePack',
serverproperty('edition') as 'Edition',
serverproperty('productversion') as 'Version',
serverproperty('collation') as 'Collation',
serverproperty('Isclustered') as 'ISClustered',
@out as 'ClusterNodes',
serverproperty('IsFullTextInstalled') as 'ISFullText'
From sys.dm_os_sys_info
-- Step 6: Printing database details
SELECT
serverproperty ('ComputerNamePhysicalNetBIOS') as 'Machine'
,@instance_name1 as InstanceName,
(SELECT 'file_type' =
CASE
WHEN s.groupid <> 0 THEN 'data'
WHEN s.groupid = 0 THEN 'log'
END) AS 'fileType'
, d.dbid as 'DBID'
, d.name AS 'DBName'
, s.name AS 'LogicalFileName'
, s.filename AS 'PhysicalFileName'
, (s.size * 8 / 1024) AS 'FileSizeMB' -- file size in MB
, d.cmptlevel as 'CompatibilityLevel'
, DATABASEPROPERTYEX (d.name,'Recovery') as 'RecoveryModel'
, DATABASEPROPERTYEX (d.name,'Status') as 'DatabaseStatus' ,
--, d.is_published as 'Publisher'
--, d.is_subscribed as 'Subscriber'
--, d.is_distributor as 'Distributor'
(SELECT 'is_replication' =
CASE
WHEN d.category = 1 THEN 'Published'
WHEN d.category = 2 THEN 'subscribed'
WHEN d.category = 4 THEN 'Merge published'
WHEN d.category = 8 THEN 'merge subscribed'
Else 'NO replication'
END) AS 'Is_replication'
, m.mirroring_state as 'MirroringState'
--INTO master.[dbo].[databasedetails]
FROM
sys.sysdatabases d INNER JOIN sys.sysaltfiles s
ON
d.dbid=s.dbid
INNER JOIN sys.database_mirroring m
ON
d.dbid=m.database_id
ORDER BY
d.name
--Step 7 :printing Backup details
Select distinct
b.machine_name as 'ServerName',
b.server_name as 'InstanceName',
b.database_name as 'DatabaseName',
d.database_id 'DBID',
CASE b.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END as 'BackupType'
--INTO [dbo].[backupdetails]
from sys.databases d inner join msdb.dbo.backupset b
On b.database_name =d.name
End
else
begin
--Step 8: If the instance is 2000 this code will be used.
declare @registry_key4 nvarchar(100)
declare @Host_Name varchar(100)
declare @CPU varchar(3)
declare @nodes nvarchar(400)
set @nodes =null /* We are not able to trap the node names for SQL Server 2000 so far*/
declare @mirroring varchar(15)
set @mirroring ='NOT APPLICABLE' /*Mirroring does not exist in SQL Server 2000*/
Declare @reg_node1 varchar(100)
Declare @reg_node2 varchar(100)
Declare @reg_node3 varchar(100)
Declare @reg_node4 varchar(100)
SET @reg_node1 = N'Cluster\Nodes\1'
SET @reg_node2 = N'Cluster\Nodes\2'
SET @reg_node3 = N'Cluster\Nodes\3'
SET @reg_node4 = N'Cluster\Nodes\4'
Declare @image_path1 varchar(100)
Declare @image_path2 varchar(100)
Declare @image_path3 varchar(100)
Declare @image_path4 varchar(100)
set @image_path1=null
set @image_path2=null
set @image_path3=null
set @image_path4=null
Exec master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@reg_node1, @value_name='NodeName',@value=@image_path1 OUTPUT
Exec master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@reg_node2, @value_name='NodeName',@value=@image_path2 OUTPUT
Exec master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@reg_node3, @value_name='NodeName',@value=@image_path3 OUTPUT
Exec master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@reg_node4, @value_name='NodeName',@value=@image_path4 OUTPUT
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'nodes') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table nodes
Create table nodes (name varchar (20))
insert into nodes values (@image_path1)
insert into nodes values (@image_path2)
insert into nodes values (@image_path3)
insert into nodes values (@image_path4)
--declare @Out nvarchar(400)
--declare @value nvarchar (20)
SELECT @Out = COALESCE(@Out+'/' ,'') + name from nodes where name is not null
-- Step 9: Reading registry keys for Number of CPUs,Binaries,Startup type ,startup username, errorlogs location and domain.
SET @instance_name = coalesce(convert(nvarchar(100), serverproperty('InstanceName')),'MSSQLSERVER');
IF @instance_name!='MSSQLSERVER'
BEGIN
set @system_instance_name=@instance_name
set @instance_name='MSSQL$'+@instance_name
SET @key=N'SYSTEM\CurrentControlSet\Services\' +@instance_name;
SET @registry_key = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\MSSQLServer\Parameters';
SET @registry_key1 = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\Setup';
SET @registry_key2 = N'SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\';
SET @registry_key4 = N'SYSTEM\CurrentControlSet\Control\Session Manager\Environment'
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key1,@value_name='SQLPath',@value=@image_path OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@key,@value_name='Start',@value=@startup_type OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@key,@value_name='ObjectName',@value=@start_username OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key,@value_name='SQLArg1',@value=@log_directory OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key2,@value_name='Domain',@value=@domain OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key4,@value_name='NUMBER_OF_PROCESSORS',@value=@CPU OUTPUT
END
IF @instance_name='MSSQLSERVER'
BEGIN
SET @key=N'SYSTEM\CurrentControlSet\Services\' +@instance_name;
SET @registry_key = N'Software\Microsoft\MSSQLSERVER\MSSQLServer\Parameters';
SET @registry_key1 = N'Software\Microsoft\MSSQLSERVER\Setup';
SET @registry_key2 = N'SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\';
SET @registry_key4 = N'SYSTEM\CurrentControlSet\Control\Session Manager\Environment'
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key1,@value_name='SQLPath',@value=@image_path OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@key,@value_name='Start',@value=@startup_type OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@key,@value_name='ObjectName',@value=@start_username OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key,@value_name='SQLArg1',@value=@log_directory OUTPUT
--EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key1,@value_name='IpAddress',@value=@IpAddress OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key2,@value_name='Domain',@value=@domain OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key4,@value_name='NUMBER_OF_PROCESSORS',@value=@CPU OUTPUT
END
set @startuptype= (select 'Start Up Mode' =
CASE
WHEN @startup_type=2 then 'AUTOMATIC'
WHEN @startup_type=3 then 'MANUAL'
WHEN @startup_type=4 then 'Disabled'
END)
--Step 10 : Using ipconfig and xp_msver to get physical memory and IP
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'tmp') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE tmp
create table tmp (server varchar(100)default cast( serverproperty ('Machinename') as varchar),[index] int, name sysname,internal_value int,character_value varchar(30))
insert into tmp([index],name,internal_value,character_value) exec xp_msver PhysicalMemory
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'ipadd') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table ipadd
create table ipadd (server varchar(100)default cast( serverproperty ('Machinename') as varchar),IP varchar (100))
insert into ipadd (IP)exec xp_cmdshell 'ipconfig'
delete from ipadd where ip not like '%IP Address.%' or IP is null
-- Step 11 : Getting the Server details
SELECT top 1
@domain as 'Domain',
serverproperty('Machinename') as 'MachineName',
@CPU as 'CPUCount',
cast (t.internal_value as bigint) as PhysicalMemoryMB,
cast(substring ( I.IP , 44,41) as nvarchar(20))as IP_Address,
serverproperty('Instancename') as 'InstanceName',
@image_path as 'BinariesPath',
@log_directory as 'ErrorLogsLocation',
@start_username as 'StartupUser',
@Startuptype as 'StartupType',
serverproperty('Productlevel') as 'ServicePack',
serverproperty('edition') as 'Edition',
serverproperty('productversion') as 'Version',
serverproperty('collation') as 'Collation',
serverproperty('Isclustered') as 'ISClustered',
@Out as 'ClustreNodes',
serverproperty('IsFullTextInstalled') as 'ISFullText'
From tmp t inner join IPAdd I
on t.server = I.server
-- Step 12 : Getting the instance details
SELECT
serverproperty ('Machinename') as 'Machine',
serverproperty ('Instancename') as 'InstanceName',
(SELECT 'file_type' =
CASE
WHEN s.groupid <> 0 THEN 'data'
WHEN s.groupid = 0 THEN 'log'
END) AS 'fileType'
, d.dbid as 'DBID'
, d.name AS 'DBName'
, s.name AS 'LogicalFileName'
, s.filename AS 'PhysicalFileName'
, (s.size * 8 / 1024) AS 'FileSizeMB' -- file size in MB
,d.cmptlevel as 'CompatibilityLevel'
, DATABASEPROPERTYEX (d.name,'Recovery') as 'RecoveryModel'
, DATABASEPROPERTYEX (d.name,'Status') as 'DatabaseStatus' ,
(SELECT 'is_replication' =
CASE
WHEN d.category = 1 THEN 'Published'
WHEN d.category = 2 THEN 'subscribed'
WHEN d.category = 4 THEN 'Merge published'
WHEN d.category = 8 THEN 'merge subscribed'
Else 'NO replication'
END) AS 'Is_replication',
@Mirroring as 'MirroringState'
FROM
sysdatabases d INNER JOIN sysaltfiles s
ON
d.dbid=s.dbid
ORDER BY
d.name
-- Step 13 : Getting backup details
Select distinct
b.machine_name as 'ServerName',
b.server_name as 'InstanceName',
b.database_name as 'DatabaseName',
d.dbid 'DBID',
CASE b.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END as 'BackupType'
from sysdatabases d inner join msdb.dbo.backupset b
On b.database_name =d.name
-- Step 14: Dropping the table we created for IP and Physical memory
Drop Table TMP
Drop Table IPADD
drop table Nodes
end
go
-- Step 15 : Setting Nulls and Quoted identifier back to Off
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'prodver') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table prodver
create table prodver ([index] int, Name nvarchar(50),Internal_value int, Charcater_Value nvarchar(50))
insert into prodver exec xp_msver 'ProductVersion'
if (select substring(Charcater_Value,1,1)from prodver)!=8
begin
-- Step 2: This code will be used if the instance is Not SQL Server 2000
Declare @image_path nvarchar(100)
Declare @startup_type int
Declare @startuptype nvarchar(100)
Declare @start_username nvarchar(100)
Declare @instance_name nvarchar(100)
Declare @system_instance_name nvarchar(100)
Declare @log_directory nvarchar(100)
Declare @key nvarchar(1000)
Declare @registry_key nvarchar(100)
Declare @registry_key1 nvarchar(300)
Declare @registry_key2 nvarchar(300)
Declare @IpAddress nvarchar(20)
Declare @domain nvarchar(50)
Declare @cluster int
Declare @instance_name1 nvarchar(100)
-- Step 3: Reading registry keys for IP,Binaries,Startup type ,startup username, errorlogs location and domain.
SET @instance_name = coalesce(convert(nvarchar(100), serverproperty('InstanceName')),'MSSQLSERVER');
If @instance_name!='MSSQLSERVER'
Set @instance_name=@instance_name
Set @instance_name1= coalesce(convert(nvarchar(100), serverproperty('InstanceName')),'MSSQLSERVER');
If @instance_name1!='MSSQLSERVER'
Set @instance_name1='MSSQL$'+@instance_name1
EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL', @instance_name, @system_instance_name output;
Set @key=N'SYSTEM\CurrentControlSet\Services\' +@instance_name1;
SET @registry_key = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\MSSQLServer\Parameters';
If @registry_key is NULL
set @instance_name=coalesce(convert(nvarchar(100), serverproperty('InstanceName')),'MSSQLSERVER');
EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL', @instance_name, @system_instance_name output;
SET @registry_key = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\MSSQLServer\Parameters';
SET @registry_key1 = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\MSSQLServer\supersocketnetlib\TCP\IP1';
SET @registry_key2 = N'SYSTEM\ControlSet001\Services\Tcpip\Parameters\';
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@key,@value_name='ImagePath',@value=@image_path OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@key,@value_name='Start',@value=@startup_type OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@key,@value_name='ObjectName',@value=@start_username OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key,@value_name='SQLArg1',@value=@log_directory OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key1,@value_name='IpAddress',@value=@IpAddress OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key2,@value_name='Domain',@value=@domain OUTPUT
Set @startuptype= (select 'Start Up Mode' =
CASE
WHEN @startup_type=2 then 'AUTOMATIC'
WHEN @startup_type=3 then 'MANUAL'
WHEN @startup_type=4 then 'Disabled'
END)
--Step 4: Getting the cluster node names if the server is on cluster .else this value will be NULL.
declare @Out nvarchar(400)
SELECT @Out = COALESCE(@Out+'' ,'') + Nodename
from sys.dm_os_cluster_nodes
-- Step 5: printing Server details
SELECT
@domain as 'Domain',
serverproperty('ComputerNamePhysicalNetBIOS') as 'MachineName',
CPU_COUNT as 'CPUCount',
(physical_memory_in_bytes/1048576) as 'PhysicalMemoryMB',
@Ipaddress as 'IP_Address',
@instance_name1 as 'InstanceName',
@image_path as 'BinariesPath',
@log_directory as 'ErrorLogsLocation',
@start_username as 'StartupUser',
@Startuptype as 'StartupType',
serverproperty('Productlevel') as 'ServicePack',
serverproperty('edition') as 'Edition',
serverproperty('productversion') as 'Version',
serverproperty('collation') as 'Collation',
serverproperty('Isclustered') as 'ISClustered',
@out as 'ClusterNodes',
serverproperty('IsFullTextInstalled') as 'ISFullText'
From sys.dm_os_sys_info
-- Step 6: Printing database details
SELECT
serverproperty ('ComputerNamePhysicalNetBIOS') as 'Machine'
,@instance_name1 as InstanceName,
(SELECT 'file_type' =
CASE
WHEN s.groupid <> 0 THEN 'data'
WHEN s.groupid = 0 THEN 'log'
END) AS 'fileType'
, d.dbid as 'DBID'
, d.name AS 'DBName'
, s.name AS 'LogicalFileName'
, s.filename AS 'PhysicalFileName'
, (s.size * 8 / 1024) AS 'FileSizeMB' -- file size in MB
, d.cmptlevel as 'CompatibilityLevel'
, DATABASEPROPERTYEX (d.name,'Recovery') as 'RecoveryModel'
, DATABASEPROPERTYEX (d.name,'Status') as 'DatabaseStatus' ,
--, d.is_published as 'Publisher'
--, d.is_subscribed as 'Subscriber'
--, d.is_distributor as 'Distributor'
(SELECT 'is_replication' =
CASE
WHEN d.category = 1 THEN 'Published'
WHEN d.category = 2 THEN 'subscribed'
WHEN d.category = 4 THEN 'Merge published'
WHEN d.category = 8 THEN 'merge subscribed'
Else 'NO replication'
END) AS 'Is_replication'
, m.mirroring_state as 'MirroringState'
--INTO master.[dbo].[databasedetails]
FROM
sys.sysdatabases d INNER JOIN sys.sysaltfiles s
ON
d.dbid=s.dbid
INNER JOIN sys.database_mirroring m
ON
d.dbid=m.database_id
ORDER BY
d.name
--Step 7 :printing Backup details
Select distinct
b.machine_name as 'ServerName',
b.server_name as 'InstanceName',
b.database_name as 'DatabaseName',
d.database_id 'DBID',
CASE b.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END as 'BackupType'
--INTO [dbo].[backupdetails]
from sys.databases d inner join msdb.dbo.backupset b
On b.database_name =d.name
End
else
begin
--Step 8: If the instance is 2000 this code will be used.
declare @registry_key4 nvarchar(100)
declare @Host_Name varchar(100)
declare @CPU varchar(3)
declare @nodes nvarchar(400)
set @nodes =null /* We are not able to trap the node names for SQL Server 2000 so far*/
declare @mirroring varchar(15)
set @mirroring ='NOT APPLICABLE' /*Mirroring does not exist in SQL Server 2000*/
Declare @reg_node1 varchar(100)
Declare @reg_node2 varchar(100)
Declare @reg_node3 varchar(100)
Declare @reg_node4 varchar(100)
SET @reg_node1 = N'Cluster\Nodes\1'
SET @reg_node2 = N'Cluster\Nodes\2'
SET @reg_node3 = N'Cluster\Nodes\3'
SET @reg_node4 = N'Cluster\Nodes\4'
Declare @image_path1 varchar(100)
Declare @image_path2 varchar(100)
Declare @image_path3 varchar(100)
Declare @image_path4 varchar(100)
set @image_path1=null
set @image_path2=null
set @image_path3=null
set @image_path4=null
Exec master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@reg_node1, @value_name='NodeName',@value=@image_path1 OUTPUT
Exec master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@reg_node2, @value_name='NodeName',@value=@image_path2 OUTPUT
Exec master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@reg_node3, @value_name='NodeName',@value=@image_path3 OUTPUT
Exec master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@reg_node4, @value_name='NodeName',@value=@image_path4 OUTPUT
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'nodes') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table nodes
Create table nodes (name varchar (20))
insert into nodes values (@image_path1)
insert into nodes values (@image_path2)
insert into nodes values (@image_path3)
insert into nodes values (@image_path4)
--declare @Out nvarchar(400)
--declare @value nvarchar (20)
SELECT @Out = COALESCE(@Out+'/' ,'') + name from nodes where name is not null
-- Step 9: Reading registry keys for Number of CPUs,Binaries,Startup type ,startup username, errorlogs location and domain.
SET @instance_name = coalesce(convert(nvarchar(100), serverproperty('InstanceName')),'MSSQLSERVER');
IF @instance_name!='MSSQLSERVER'
BEGIN
set @system_instance_name=@instance_name
set @instance_name='MSSQL$'+@instance_name
SET @key=N'SYSTEM\CurrentControlSet\Services\' +@instance_name;
SET @registry_key = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\MSSQLServer\Parameters';
SET @registry_key1 = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\Setup';
SET @registry_key2 = N'SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\';
SET @registry_key4 = N'SYSTEM\CurrentControlSet\Control\Session Manager\Environment'
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key1,@value_name='SQLPath',@value=@image_path OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@key,@value_name='Start',@value=@startup_type OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@key,@value_name='ObjectName',@value=@start_username OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key,@value_name='SQLArg1',@value=@log_directory OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key2,@value_name='Domain',@value=@domain OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key4,@value_name='NUMBER_OF_PROCESSORS',@value=@CPU OUTPUT
END
IF @instance_name='MSSQLSERVER'
BEGIN
SET @key=N'SYSTEM\CurrentControlSet\Services\' +@instance_name;
SET @registry_key = N'Software\Microsoft\MSSQLSERVER\MSSQLServer\Parameters';
SET @registry_key1 = N'Software\Microsoft\MSSQLSERVER\Setup';
SET @registry_key2 = N'SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\';
SET @registry_key4 = N'SYSTEM\CurrentControlSet\Control\Session Manager\Environment'
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key1,@value_name='SQLPath',@value=@image_path OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@key,@value_name='Start',@value=@startup_type OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@key,@value_name='ObjectName',@value=@start_username OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key,@value_name='SQLArg1',@value=@log_directory OUTPUT
--EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key1,@value_name='IpAddress',@value=@IpAddress OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key2,@value_name='Domain',@value=@domain OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key4,@value_name='NUMBER_OF_PROCESSORS',@value=@CPU OUTPUT
END
set @startuptype= (select 'Start Up Mode' =
CASE
WHEN @startup_type=2 then 'AUTOMATIC'
WHEN @startup_type=3 then 'MANUAL'
WHEN @startup_type=4 then 'Disabled'
END)
--Step 10 : Using ipconfig and xp_msver to get physical memory and IP
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'tmp') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE tmp
create table tmp (server varchar(100)default cast( serverproperty ('Machinename') as varchar),[index] int, name sysname,internal_value int,character_value varchar(30))
insert into tmp([index],name,internal_value,character_value) exec xp_msver PhysicalMemory
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'ipadd') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table ipadd
create table ipadd (server varchar(100)default cast( serverproperty ('Machinename') as varchar),IP varchar (100))
insert into ipadd (IP)exec xp_cmdshell 'ipconfig'
delete from ipadd where ip not like '%IP Address.%' or IP is null
-- Step 11 : Getting the Server details
SELECT top 1
@domain as 'Domain',
serverproperty('Machinename') as 'MachineName',
@CPU as 'CPUCount',
cast (t.internal_value as bigint) as PhysicalMemoryMB,
cast(substring ( I.IP , 44,41) as nvarchar(20))as IP_Address,
serverproperty('Instancename') as 'InstanceName',
@image_path as 'BinariesPath',
@log_directory as 'ErrorLogsLocation',
@start_username as 'StartupUser',
@Startuptype as 'StartupType',
serverproperty('Productlevel') as 'ServicePack',
serverproperty('edition') as 'Edition',
serverproperty('productversion') as 'Version',
serverproperty('collation') as 'Collation',
serverproperty('Isclustered') as 'ISClustered',
@Out as 'ClustreNodes',
serverproperty('IsFullTextInstalled') as 'ISFullText'
From tmp t inner join IPAdd I
on t.server = I.server
-- Step 12 : Getting the instance details
SELECT
serverproperty ('Machinename') as 'Machine',
serverproperty ('Instancename') as 'InstanceName',
(SELECT 'file_type' =
CASE
WHEN s.groupid <> 0 THEN 'data'
WHEN s.groupid = 0 THEN 'log'
END) AS 'fileType'
, d.dbid as 'DBID'
, d.name AS 'DBName'
, s.name AS 'LogicalFileName'
, s.filename AS 'PhysicalFileName'
, (s.size * 8 / 1024) AS 'FileSizeMB' -- file size in MB
,d.cmptlevel as 'CompatibilityLevel'
, DATABASEPROPERTYEX (d.name,'Recovery') as 'RecoveryModel'
, DATABASEPROPERTYEX (d.name,'Status') as 'DatabaseStatus' ,
(SELECT 'is_replication' =
CASE
WHEN d.category = 1 THEN 'Published'
WHEN d.category = 2 THEN 'subscribed'
WHEN d.category = 4 THEN 'Merge published'
WHEN d.category = 8 THEN 'merge subscribed'
Else 'NO replication'
END) AS 'Is_replication',
@Mirroring as 'MirroringState'
FROM
sysdatabases d INNER JOIN sysaltfiles s
ON
d.dbid=s.dbid
ORDER BY
d.name
-- Step 13 : Getting backup details
Select distinct
b.machine_name as 'ServerName',
b.server_name as 'InstanceName',
b.database_name as 'DatabaseName',
d.dbid 'DBID',
CASE b.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END as 'BackupType'
from sysdatabases d inner join msdb.dbo.backupset b
On b.database_name =d.name
-- Step 14: Dropping the table we created for IP and Physical memory
Drop Table TMP
Drop Table IPADD
drop table Nodes
end
go
-- Step 15 : Setting Nulls and Quoted identifier back to Off
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
Script to identify DeadLock -Longtime_Query
select s.session_id, s.login_time, s.login_name
, s.host_name, s.program_name, s.last_request_end_time
, r.start_time, r.command, r.open_transaction_count
, SUBSTRING(st.text, (r.statement_start_offset/2)+1,
((CASE r.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE r.statement_end_offset
END - r.statement_start_offset)/2) + 1) as statement_text
, coalesce(QUOTENAME(DB_NAME(st.dbid)) + N'.'
+ QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid, st.dbid)) + N'.'
+ QUOTENAME(OBJECT_NAME(st.objectid, st.dbid))
, '') as command_text
from sys.dm_exec_sessions as s
join sys.dm_exec_requests as r
on r.session_id = s.session_id
cross apply sys.dm_exec_sql_text(r.sql_handle) as st
order by s.last_request_end_time;
, s.host_name, s.program_name, s.last_request_end_time
, r.start_time, r.command, r.open_transaction_count
, SUBSTRING(st.text, (r.statement_start_offset/2)+1,
((CASE r.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE r.statement_end_offset
END - r.statement_start_offset)/2) + 1) as statement_text
, coalesce(QUOTENAME(DB_NAME(st.dbid)) + N'.'
+ QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid, st.dbid)) + N'.'
+ QUOTENAME(OBJECT_NAME(st.objectid, st.dbid))
, '
from sys.dm_exec_sessions as s
join sys.dm_exec_requests as r
on r.session_id = s.session_id
cross apply sys.dm_exec_sql_text(r.sql_handle) as st
order by s.last_request_end_time;
Script to script out logins and permissions
--select * from master.DBO.TEMP_TABLE_FOR_USERS
DECLARE @DBNAME VARCHAR(50),
@STORAGEDBNAME VARCHAR(50)
SET @DBNAME='ReportingDatabase'
SET @STORAGEDBNAME='master'
SET NOCOUNT ON
DECLARE @CMD VARCHAR(350)
SET @CMD= 'IF EXISTS (SELECT * FROM '+@STORAGEDBNAME+'.sys.objects WHERE object_id = OBJECT_ID('''+@STORAGEDBNAME+'.[dbo].[TEMP_TABLE_FOR_USERS]'')
AND type in (''U''))
DROP TABLE '+@STORAGEDBNAME+'.DBO.TEMP_TABLE_FOR_USERS
CREATE TABLE '+@STORAGEDBNAME+'.DBO.TEMP_TABLE_FOR_USERS
([COMMAND] TEXT)'
EXEC (@CMD)
CREATE TABLE #TEMP
( NUMBER INT IDENTITY(1,1) NOT NULL,
USERNAME VARCHAR(100),
ROLENAME VARCHAR(100)
)
CREATE TABLE #TEMP1
( NUMBER INT IDENTITY(1,1) NOT NULL,
OBJECTNAME VARCHAR(100),
TYPE_DESC VARCHAR(100),
PERMISSION_NAME VARCHAR(100),
STATE_DESC VARCHAR(100),
USERNAME VARCHAR(100)
)
CREATE TABLE #TEMP2
( NUMBER INT IDENTITY(1,1) NOT NULL,
USERNAME VARCHAR(100)
)
DECLARE @CMD1 VARCHAR(500),
@USER INT,
@USERNAME VARCHAR(50)
SET @CMD1= 'SELECT U.NAME , G.NAME
FROM '+ @DBNAME+'.DBO.SYSUSERS U,' +@DBNAME+'.DBO.SYSUSERS G,' +@DBNAME+'.DBO.SYSMEMBERS M
WHERE G.UID = M.GROUPUID
AND G.ISSQLROLE = 1 AND U.UID = M.MEMBERUID AND U.NAME<>''dbo'''
INSERT INTO #TEMP
EXEC (@CMD1)
INSERT INTO #TEMP2
SELECT DISTINCT USERNAME FROM #TEMP
DECLARE @CMD2 VARCHAR(5000)
SET @CMD2= 'DECLARE @COUNT INT,@USER INT
SET @COUNT=1 SELECT @USER=COUNT(*) FROM #TEMP2 WHILE @USER>=@COUNT
BEGIN
INSERT INTO '+@STORAGEDBNAME+'.DBO.TEMP_TABLE_FOR_USERS
SELECT ''IF NOT EXISTS (SELECT * FROM SYS.DATABASE_PRINCIPALS WHERE NAME = ''''''+USERNAME+'''''')
CREATE USER [''+USERNAME+''] FOR LOGIN ['' +USERNAME +''] WITH DEFAULT_SCHEMA=[DBO]'' FROM #TEMP2
WHERE NUMBER=@COUNT SET @COUNT=@COUNT+1
END'
EXEC (@CMD2)
-------------------- Schema Starts Here ------------------
CREATE TABLE #TEMP3
( NUMBER INT IDENTITY(1,1) NOT NULL,
SCHEMANAME VARCHAR(100)
)
INSERT INTO #TEMP3 SELECT NAME FROM SYS.SCHEMAS WHERE [SCHEMA_ID] BETWEEN 5 AND 16383
DECLARE @CMD5 VARCHAR(5000)
SET @CMD5= 'DECLARE @SCHEMA INT,
@COUNT3 INT
SET @COUNT3=1
SELECT @SCHEMA=COUNT(SCHEMANAME) FROM #TEMP3
WHILE @SCHEMA>=@COUNT3
BEGIN
INSERT INTO '+@STORAGEDBNAME+'.DBO.TEMP_TABLE_FOR_USERS
SELECT ''IF NOT EXISTS (SELECT * FROM SYS.SCHEMAS WHERE NAME = ''''''+SCHEMANAME+'''''')
EXEC SYS.SP_EXECUTESQL N''''CREATE SCHEMA [''+SCHEMANAME+''] AUTHORIZATION ['' +SCHEMANAME +'']'''''' FROM #TEMP3
WHERE NUMBER=@COUNT3 SET @COUNT3=@COUNT3+1
END'
EXEC (@CMD5)
-------------------- User Role Starts Here ---------------
DECLARE @CMD3 VARCHAR(5000)
SET @CMD3= 'DECLARE @ROLE INT,
@COUNT2 INT
SET @COUNT2=1
SELECT @ROLE=COUNT(ROLENAME) FROM #TEMP
WHILE @ROLE>=@COUNT2
BEGIN
INSERT INTO '+@STORAGEDBNAME+'.DBO.TEMP_TABLE_FOR_USERS SELECT ''EXEC sp_addrolemember ''''''+ROLENAME+ '''''' ,''''''+ USERNAME+'''''''' FROM #TEMP
WHERE NUMBER=@COUNT2 SET @COUNT2=@COUNT2+1
END'
EXEC (@CMD3)
--------------------- Secure Permission Starts Here ---------------
INSERT INTO #TEMP1
SELECT O.NAME COLLATE LATIN1_GENERAL_CI_AS_KS_WS AS OBJECTNAME ,TYPE_DESC,
PERMISSION_NAME,STATE_DESC,U.NAME AS USERNAME
FROM SYS.DATABASE_PERMISSIONS P
INNER JOIN SYS.OBJECTS O ON O.OBJECT_ID=MAJOR_ID
INNER JOIN SYSUSERS U ON U.UID=P.GRANTEE_PRINCIPAL_ID
DECLARE @CMD4 VARCHAR(5000)
SET @CMD4= 'DECLARE @SECUR INT,
@COUNT1 INT
SET @COUNT1=1
SELECT @SECUR=COUNT(*) FROM #TEMP1
WHILE @SECUR>=@COUNT1
BEGIN
INSERT INTO '+@STORAGEDBNAME+'.DBO.TEMP_TABLE_FOR_USERS SELECT ''''+STATE_DESC+'' ''+PERMISSION_NAME+'' ON ''+OBJECTNAME+'' TO [''+USERNAME +'']''FROM #TEMP1
WHERE NUMBER=@COUNT1 SET @COUNT1=@COUNT1+1
END'
EXEC (@CMD4)
-------------------- Final Output ---------------
DROP TABLE #TEMP
DROP TABLE #TEMP1
DROP TABLE #TEMP2
DROP TABLE #TEMP3
SET NOCOUNT OFF
DECLARE @DBNAME VARCHAR(50),
@STORAGEDBNAME VARCHAR(50)
SET @DBNAME='ReportingDatabase'
SET @STORAGEDBNAME='master'
SET NOCOUNT ON
DECLARE @CMD VARCHAR(350)
SET @CMD= 'IF EXISTS (SELECT * FROM '+@STORAGEDBNAME+'.sys.objects WHERE object_id = OBJECT_ID('''+@STORAGEDBNAME+'.[dbo].[TEMP_TABLE_FOR_USERS]'')
AND type in (''U''))
DROP TABLE '+@STORAGEDBNAME+'.DBO.TEMP_TABLE_FOR_USERS
CREATE TABLE '+@STORAGEDBNAME+'.DBO.TEMP_TABLE_FOR_USERS
([COMMAND] TEXT)'
EXEC (@CMD)
CREATE TABLE #TEMP
( NUMBER INT IDENTITY(1,1) NOT NULL,
USERNAME VARCHAR(100),
ROLENAME VARCHAR(100)
)
CREATE TABLE #TEMP1
( NUMBER INT IDENTITY(1,1) NOT NULL,
OBJECTNAME VARCHAR(100),
TYPE_DESC VARCHAR(100),
PERMISSION_NAME VARCHAR(100),
STATE_DESC VARCHAR(100),
USERNAME VARCHAR(100)
)
CREATE TABLE #TEMP2
( NUMBER INT IDENTITY(1,1) NOT NULL,
USERNAME VARCHAR(100)
)
DECLARE @CMD1 VARCHAR(500),
@USER INT,
@USERNAME VARCHAR(50)
SET @CMD1= 'SELECT U.NAME , G.NAME
FROM '+ @DBNAME+'.DBO.SYSUSERS U,' +@DBNAME+'.DBO.SYSUSERS G,' +@DBNAME+'.DBO.SYSMEMBERS M
WHERE G.UID = M.GROUPUID
AND G.ISSQLROLE = 1 AND U.UID = M.MEMBERUID AND U.NAME<>''dbo'''
INSERT INTO #TEMP
EXEC (@CMD1)
INSERT INTO #TEMP2
SELECT DISTINCT USERNAME FROM #TEMP
DECLARE @CMD2 VARCHAR(5000)
SET @CMD2= 'DECLARE @COUNT INT,@USER INT
SET @COUNT=1 SELECT @USER=COUNT(*) FROM #TEMP2 WHILE @USER>=@COUNT
BEGIN
INSERT INTO '+@STORAGEDBNAME+'.DBO.TEMP_TABLE_FOR_USERS
SELECT ''IF NOT EXISTS (SELECT * FROM SYS.DATABASE_PRINCIPALS WHERE NAME = ''''''+USERNAME+'''''')
CREATE USER [''+USERNAME+''] FOR LOGIN ['' +USERNAME +''] WITH DEFAULT_SCHEMA=[DBO]'' FROM #TEMP2
WHERE NUMBER=@COUNT SET @COUNT=@COUNT+1
END'
EXEC (@CMD2)
-------------------- Schema Starts Here ------------------
CREATE TABLE #TEMP3
( NUMBER INT IDENTITY(1,1) NOT NULL,
SCHEMANAME VARCHAR(100)
)
INSERT INTO #TEMP3 SELECT NAME FROM SYS.SCHEMAS WHERE [SCHEMA_ID] BETWEEN 5 AND 16383
DECLARE @CMD5 VARCHAR(5000)
SET @CMD5= 'DECLARE @SCHEMA INT,
@COUNT3 INT
SET @COUNT3=1
SELECT @SCHEMA=COUNT(SCHEMANAME) FROM #TEMP3
WHILE @SCHEMA>=@COUNT3
BEGIN
INSERT INTO '+@STORAGEDBNAME+'.DBO.TEMP_TABLE_FOR_USERS
SELECT ''IF NOT EXISTS (SELECT * FROM SYS.SCHEMAS WHERE NAME = ''''''+SCHEMANAME+'''''')
EXEC SYS.SP_EXECUTESQL N''''CREATE SCHEMA [''+SCHEMANAME+''] AUTHORIZATION ['' +SCHEMANAME +'']'''''' FROM #TEMP3
WHERE NUMBER=@COUNT3 SET @COUNT3=@COUNT3+1
END'
EXEC (@CMD5)
-------------------- User Role Starts Here ---------------
DECLARE @CMD3 VARCHAR(5000)
SET @CMD3= 'DECLARE @ROLE INT,
@COUNT2 INT
SET @COUNT2=1
SELECT @ROLE=COUNT(ROLENAME) FROM #TEMP
WHILE @ROLE>=@COUNT2
BEGIN
INSERT INTO '+@STORAGEDBNAME+'.DBO.TEMP_TABLE_FOR_USERS SELECT ''EXEC sp_addrolemember ''''''+ROLENAME+ '''''' ,''''''+ USERNAME+'''''''' FROM #TEMP
WHERE NUMBER=@COUNT2 SET @COUNT2=@COUNT2+1
END'
EXEC (@CMD3)
--------------------- Secure Permission Starts Here ---------------
INSERT INTO #TEMP1
SELECT O.NAME COLLATE LATIN1_GENERAL_CI_AS_KS_WS AS OBJECTNAME ,TYPE_DESC,
PERMISSION_NAME,STATE_DESC,U.NAME AS USERNAME
FROM SYS.DATABASE_PERMISSIONS P
INNER JOIN SYS.OBJECTS O ON O.OBJECT_ID=MAJOR_ID
INNER JOIN SYSUSERS U ON U.UID=P.GRANTEE_PRINCIPAL_ID
DECLARE @CMD4 VARCHAR(5000)
SET @CMD4= 'DECLARE @SECUR INT,
@COUNT1 INT
SET @COUNT1=1
SELECT @SECUR=COUNT(*) FROM #TEMP1
WHILE @SECUR>=@COUNT1
BEGIN
INSERT INTO '+@STORAGEDBNAME+'.DBO.TEMP_TABLE_FOR_USERS SELECT ''''+STATE_DESC+'' ''+PERMISSION_NAME+'' ON ''+OBJECTNAME+'' TO [''+USERNAME +'']''FROM #TEMP1
WHERE NUMBER=@COUNT1 SET @COUNT1=@COUNT1+1
END'
EXEC (@CMD4)
-------------------- Final Output ---------------
DROP TABLE #TEMP
DROP TABLE #TEMP1
DROP TABLE #TEMP2
DROP TABLE #TEMP3
SET NOCOUNT OFF
SQL Agent Script
strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colMonitoredProcesses = objWMIService. _
ExecNotificationQuery("select * from __instancedeletionevent " _
& "within 1 where TargetInstance isa 'Win32_Process'")
Do While i = 0
Set objLatestProcess = colMonitoredProcesses.NextEvent
If objLatestProcess.TargetInstance.Name = "sqlservr.exe" Then
Set objEmail = CreateObject("CDO.Message")
SmtpMail.SmtpServer = "indblrvmsg00"
objEmail.From = "Test@aditi.com"
objEmail.To = "pradyothanapd@aditi.com"
objEmail.Subject = "SQl Server is down on" & strcomputer
objEmail.Textbody = "SQL Sever is down"
objEmail.Send
End If
Loop
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colMonitoredProcesses = objWMIService. _
ExecNotificationQuery("select * from __instancedeletionevent " _
& "within 1 where TargetInstance isa 'Win32_Process'")
Do While i = 0
Set objLatestProcess = colMonitoredProcesses.NextEvent
If objLatestProcess.TargetInstance.Name = "sqlservr.exe" Then
Set objEmail = CreateObject("CDO.Message")
SmtpMail.SmtpServer = "indblrvmsg00"
objEmail.From = "Test@aditi.com"
objEmail.To = "pradyothanapd@aditi.com"
objEmail.Subject = "SQl Server is down on" & strcomputer
objEmail.Textbody = "SQL Sever is down"
objEmail.Send
End If
Loop
16 November 2011
The transaction log for database DBNAME is full
ERROR :-
Msg 9002, Level 17, State 2, Line 4
The transaction log for database DBNAME is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
http://sqlskills.com/BLOGS/PAUL/post/Search-Engine-QA-1-Running-out-of-transaction-log-space.aspx
Msg 9002, Level 17, State 2, Line 4
The transaction log for database DBNAME is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
http://sqlskills.com/BLOGS/PAUL/post/Search-Engine-QA-1-Running-out-of-transaction-log-space.aspx
15 November 2011
SSIS Deployment Issue - In cluster Environment
Resolution OR Workaround
-------------------------------
SSIS is not a part of cluster service, Its an individual component and server name in the xml will refer to default or local instance by .(DOT). This we need to change as SQL Server will be on a cluster with a VIRTUAL NAME.
1. Stop the SSIS Service
2. Open up MsDtsSrvr.ini.xml file which is available in "C:\Program Files\Microsoft SQL Server\100\DTS\Binn\” folder
3. Replace the Server Name node with Clustered Instance Name (replace “.” With cluster instance name)
4. Restart the SSIS Service
This will resolve the issue.Good Luck.
Note :- Will get error if we dint restart the SSIS services.
11 November 2011
Setup SSRS in SharePoint Integrated Mode
This post is about configuring SQL Server Reporting Services in SharePoint Integrated mode the quick and clean way with no fuzz in 40 steps.
I'm not discussing the default values, I will only talk about the values which need to be changed or selected in order to get SSRS set up in the least amount of steps.
The scenario:
SharePoint 2010 Enterprise Edition + named instance SQL Server 2008 Express Edition which is deployed during the SharePoint setup. Name of the SQL Server instance: SHAREPOINT.
Separate default SQL Server 2008 R2 instance (Enterprise Edition).
SSRS currently installed in native mode on the SQL Server 2008 R2 default instance.
AdventureWorksDW2008R2 hosted on the SQL Server 2008 R2 default instance.
The procedure contains 5 parts:
A. SSRS Configuration {SQL Server}
B. Web application {SharePoint}
C. Site collection {SharePoint}
D. SSRS integration {SharePoint}
E. Content types {SharePoint}
Let's start!
A. SSRS Configuration {SQL Server}
Log on to your SharePoint 2010 box.
Fire up the SQL Server Reporting Services Configuration Manager.
Connect to the Report Server instance. In my case this is the default SQL Server 2008 R2 instance (MSSQLSERVER)
The first thing you will notice is the Report Server Status. Check the Report Server Mode. It should say Native (we start from native mode in this scenario).
Click Database in the left pane.Click Change Database. Select Create a new report server database.
Enter the proper credentials.
Enter a database name and select SharePoint Integrated mode.
Enter the credentials and do not forget the\ (.\ will do as well).
Choose Report Manager URL in the left pane and click the apply button. This will configure the Report Manager virtual directory.
Backup the encryption keys.
B. Web application {SharePoint}
Open the SharePoint Central Administration website.
Choose Application Management > Manage web applications.
Click the New button in the ribbon. This will bring up the Create new web application popup.
Keep the default values to keep things simple except for:
Choose a name for the new IIS Website. I will choose SSRSDemo in this example.
Choose a name for the application pool. I will again choose SSRSDemo.
Make sure you connect to the right database instance. In my example I will connect to the named SQL Server 2008 Express Edition instance named SHAREPOINT.
Choose a database name or keep the default value with the GUID suffix. I will call the database WSS_Content_SSRSDemo.
Hold your horses before you hit the OK button when this information pops up:
The Microsoft SharePoint Foundation Web application has been created.
If this is the first time that you have used this application pool with a SharePoint Web application, you must wait until the Internet Information Services (IIS) Web site has been created on all servers. By default, no new SharePoint site collections are created with the Web application. If you have just created a Forms Based Authentication (FBA) Web application, then before creating a new site collection, you will need to perform some additional configuration steps.
Learn about how to configure a Web application for FBA.
Once you are finished, to create a new site collection, go to the Create Site Collection page.
5. Click Create Site Collection page to move on to C. Site collection {SharePoint}
C. Site collection {SharePoint}
Enter a title for the site collection. SSRSDemo for example.
Choose a template. I will choose Business Intelligence Center because I want to store my SSRS reports in a BI related environment.
Enter a username for Primary Collection Administrator. Make sure you enter the full domain name. e.g.:\ (.\username won't work here).
D. SSRS integration {SharePoint}
Go to the SharePoint Central Administration website > General Application Settings > Reporting Services > Reporting Services Integration.
Enter the Report Server Web Service URL which you can find the SQL Server Reporting Services Configuration Manager.
Choose the Authentication Mode. I will choose Windows Authentication. Entering .\ will do.
Go to the SharePoint Central Administration website > General Application Settings > Reporting Services > Add a Report Server to the Integration.
The server name should already be provided and enter the name of the SQL Server instance which hosts the report server database.
Enter the credentials.
E. Upload a report {SharePoint}
Open your new top level web site we created in C. Site collection {SharePoint}
In case you forgot the URL of the web site, go to SharePoint Central Administration > Web Application and look for the URL.
Go to All site Content > Documents.
In the ribbon, go to Library Tools > Library.
Click Library Settings.
Click Advanced Settings.
In Content Types, check Yes for Allow Management of content types and click OK.
In the columns section, click Add from existing site columns.
In the Select site columns from drop-down box, select Report Server Content Types.
Select all available site content types and click the Add button followed by OK.
Go back to All site content > Documents.
Click the Add Document link.
Browse for a Report provided by the AdventureWorksDW200R2 samples from CodePlex.
Enter a title for the report.
More Information on :-
-------------------------------
http://sqlug.be/blogs/steves_sql_blog/archive/2011/04/05/setup-ssrs-in-sharepoint-integrated-mode.aspx
------------------------------
I'm not discussing the default values, I will only talk about the values which need to be changed or selected in order to get SSRS set up in the least amount of steps.
The scenario:
SharePoint 2010 Enterprise Edition + named instance SQL Server 2008 Express Edition which is deployed during the SharePoint setup. Name of the SQL Server instance: SHAREPOINT.
Separate default SQL Server 2008 R2 instance (Enterprise Edition).
SSRS currently installed in native mode on the SQL Server 2008 R2 default instance.
AdventureWorksDW2008R2 hosted on the SQL Server 2008 R2 default instance.
The procedure contains 5 parts:
A. SSRS Configuration {SQL Server}
B. Web application {SharePoint}
C. Site collection {SharePoint}
D. SSRS integration {SharePoint}
E. Content types {SharePoint}
Let's start!
A. SSRS Configuration {SQL Server}
Log on to your SharePoint 2010 box.
Fire up the SQL Server Reporting Services Configuration Manager.
Connect to the Report Server instance. In my case this is the default SQL Server 2008 R2 instance (MSSQLSERVER)
The first thing you will notice is the Report Server Status. Check the Report Server Mode. It should say Native (we start from native mode in this scenario).
Click Database in the left pane.Click Change Database. Select Create a new report server database.
Enter the proper credentials.
Enter a database name and select SharePoint Integrated mode.
Enter the credentials and do not forget the
Choose Report Manager URL in the left pane and click the apply button. This will configure the Report Manager virtual directory.
Backup the encryption keys.
B. Web application {SharePoint}
Open the SharePoint Central Administration website.
Choose Application Management > Manage web applications.
Click the New button in the ribbon. This will bring up the Create new web application popup.
Keep the default values to keep things simple except for:
Choose a name for the new IIS Website. I will choose SSRSDemo in this example.
Choose a name for the application pool. I will again choose SSRSDemo.
Make sure you connect to the right database instance. In my example I will connect to the named SQL Server 2008 Express Edition instance named SHAREPOINT.
Choose a database name or keep the default value with the GUID suffix. I will call the database WSS_Content_SSRSDemo.
Hold your horses before you hit the OK button when this information pops up:
The Microsoft SharePoint Foundation Web application has been created.
If this is the first time that you have used this application pool with a SharePoint Web application, you must wait until the Internet Information Services (IIS) Web site has been created on all servers. By default, no new SharePoint site collections are created with the Web application. If you have just created a Forms Based Authentication (FBA) Web application, then before creating a new site collection, you will need to perform some additional configuration steps.
Learn about how to configure a Web application for FBA.
Once you are finished, to create a new site collection, go to the Create Site Collection page.
5. Click Create Site Collection page to move on to C. Site collection {SharePoint}
C. Site collection {SharePoint}
Enter a title for the site collection. SSRSDemo for example.
Choose a template. I will choose Business Intelligence Center because I want to store my SSRS reports in a BI related environment.
Enter a username for Primary Collection Administrator. Make sure you enter the full domain name. e.g.:
D. SSRS integration {SharePoint}
Go to the SharePoint Central Administration website > General Application Settings > Reporting Services > Reporting Services Integration.
Enter the Report Server Web Service URL which you can find the SQL Server Reporting Services Configuration Manager.
Choose the Authentication Mode. I will choose Windows Authentication. Entering .\
Go to the SharePoint Central Administration website > General Application Settings > Reporting Services > Add a Report Server to the Integration.
The server name should already be provided and enter the name of the SQL Server instance which hosts the report server database.
Enter the credentials.
E. Upload a report {SharePoint}
Open your new top level web site we created in C. Site collection {SharePoint}
In case you forgot the URL of the web site, go to SharePoint Central Administration > Web Application and look for the URL.
Go to All site Content > Documents.
In the ribbon, go to Library Tools > Library.
Click Library Settings.
Click Advanced Settings.
In Content Types, check Yes for Allow Management of content types and click OK.
In the columns section, click Add from existing site columns.
In the Select site columns from drop-down box, select Report Server Content Types.
Select all available site content types and click the Add button followed by OK.
Go back to All site content > Documents.
Click the Add Document link.
Browse for a Report provided by the AdventureWorksDW200R2 samples from CodePlex.
Enter a title for the report.
More Information on :-
-------------------------------
http://sqlug.be/blogs/steves_sql_blog/archive/2011/04/05/setup-ssrs-in-sharepoint-integrated-mode.aspx
------------------------------
08 November 2011
NO Start Button,, Need Manage Server ..Here you go
Manage Windows Server 2008 R2 Core with SCONFIG
The Server Core installation option of Windows Server 2008 and R2 provides the following benefits:
Reduced maintenance - Because the Server Core installation option installs only what is required to have a manageable server for the AD DS, AD LDS, AD CS, DHCP Server, DNS Server, File Services, Print Services, Web Server and Hyper-V server roles, less maintenance is required than on a full installation of Windows Server 2008.
Reduced attack surface - Because Server Core installations are minimal, there are fewer applications running on the server, which decreases the attack surface.
Reduced management - Because fewer applications and services are installed on a server running the Server Core installation, there is less to manage.
Less disk space required - A Server Core installation requires only about 1.5 gigabyte (GB) of disk space to install and approximately 2 GB for operations after the installation.
Lower risk of bugs - Reducing the amount of code can help reduce the amount of bugs.
To run SCONFIG simply enter sconfig.cmd in the command prompt window, and press Enter.
The Server Core installation option of Windows Server 2008 and R2 provides the following benefits:
Reduced maintenance - Because the Server Core installation option installs only what is required to have a manageable server for the AD DS, AD LDS, AD CS, DHCP Server, DNS Server, File Services, Print Services, Web Server and Hyper-V server roles, less maintenance is required than on a full installation of Windows Server 2008.
Reduced attack surface - Because Server Core installations are minimal, there are fewer applications running on the server, which decreases the attack surface.
Reduced management - Because fewer applications and services are installed on a server running the Server Core installation, there is less to manage.
Less disk space required - A Server Core installation requires only about 1.5 gigabyte (GB) of disk space to install and approximately 2 GB for operations after the installation.
Lower risk of bugs - Reducing the amount of code can help reduce the amount of bugs.
To run SCONFIG simply enter sconfig.cmd in the command prompt window, and press Enter.
18 October 2011
UPDATE STATS - FOR ALL DB's AND ALL OBJECTS
EXEC sp_msForEachDb
@command1='IF ''#'' NOT IN (''master'', ''model'', ''msdb'', ''pubs'', ''tempdb'') BEGIN PRINT ''#'';
EXEC #.dbo.sp_msForEachTable ''UPDATE STATISTICS ? WITH FULLSCAN'', @command2=''PRINT CONVERT(VARCHAR, GETDATE(), 9) + '''' - ? Stats Updated'''''' END',
@replaceChar = '#'
@command1='IF ''#'' NOT IN (''master'', ''model'', ''msdb'', ''pubs'', ''tempdb'') BEGIN PRINT ''#'';
EXEC #.dbo.sp_msForEachTable ''UPDATE STATISTICS ? WITH FULLSCAN'', @command2=''PRINT CONVERT(VARCHAR, GETDATE(), 9) + '''' - ? Stats Updated'''''' END',
@replaceChar = '#'
Interview Questions -DAC
1)What is a DAC. How do you connect to server through DAC?
This diagnostic connection allows an administrator to access SQL Server to execute diagnostic queries and troubleshoot problems even when SQL Server is not responding to standard connection requests.
This dedicated administrator connection (DAC) supports encryption and other security features of SQL Server. The DAC only allows changing the user context to another admin user.
SQL Server makes every attempt to make DAC connect successfully, but under extreme situations it may not be successful.
To connect to a server using the DAC
In SQL Server Management Studio, with no other DACs open, on the toolbar, click Database Engine Query.
In the Connect to Database Engine dialog box, in the Server name box, type ADMIN: followed by the name of the server instance. For example, to connect to a server instance named ACCT\PAYABLE, type ADMIN:ACCT\PAYABLE.
Complete the Authentication section, providing credentials for a member of the sysadmin group, and then click Connect.
The connection is made.
If the DAC is already in use, the connection will fail with an error indicating it cannot connect.
This diagnostic connection allows an administrator to access SQL Server to execute diagnostic queries and troubleshoot problems even when SQL Server is not responding to standard connection requests.
This dedicated administrator connection (DAC) supports encryption and other security features of SQL Server. The DAC only allows changing the user context to another admin user.
SQL Server makes every attempt to make DAC connect successfully, but under extreme situations it may not be successful.
To connect to a server using the DAC
In SQL Server Management Studio, with no other DACs open, on the toolbar, click Database Engine Query.
In the Connect to Database Engine dialog box, in the Server name box, type ADMIN: followed by the name of the server instance. For example, to connect to a server instance named ACCT\PAYABLE, type ADMIN:ACCT\PAYABLE.
Complete the Authentication section, providing credentials for a member of the sysadmin group, and then click Connect.
The connection is made.
If the DAC is already in use, the connection will fail with an error indicating it cannot connect.
Report Server Windows Service (MSSQLSERVER) cannot connect to the report server database.
http://msdn.microsoft.com/en-us/library/aa337324%28SQL.90%29.aspx
12 October 2011
To retrive Data from Excel
1. Step
http://www.microsoft.com/download/en/confirmation.aspx?id=23734
Install the Driver AccessDatabaseEngine.exe
2. Step
select * FROM OPENROWSET(
'Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=E:\details.xlsx;',
'SELECT * FROM [DServer$]')
More Help on http://blog.hoegaerden.be/2010/03/29/retrieving-data-from-excel/
http://www.microsoft.com/download/en/confirmation.aspx?id=23734
Install the Driver AccessDatabaseEngine.exe
2. Step
select * FROM OPENROWSET(
'Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=E:\details.xlsx;',
'SELECT * FROM [DServer$]')
More Help on http://blog.hoegaerden.be/2010/03/29/retrieving-data-from-excel/
Subscribe to:
Posts (Atom)