declare @id int
declare @type character(2)
declare @pages int
declare @dbname sysname
declare @dbsize dec(15,0)
declare @bytesperpage dec(15,0)
declare @pagesperMB dec(15,0)
create table #spt_space
(
objid int null,
rows int null,
reserved dec(15) null,
data dec(15) null,
indexp dec(15) null,
unused dec(15) null
)
set nocount on
-- Create a cursor to loop through the user tables
declare c_tables cursor for
select id
from sysobjects
where xtype = 'U'
open c_tables
fetch next from c_tables
into @id
while @@fetch_status = 0
begin
/* Code from sp_spaceused */
insert into #spt_space (objid, reserved)
select objid = @id, sum(reserved)
from sysindexes
where indid in (0, 1, 255)
and id = @id
select @pages = sum(dpages)
from sysindexes
where indid < 2
and id = @id
select @pages = @pages + isnull(sum(used), 0)
from sysindexes
where indid = 255
and id = @id
update #spt_space
set data = @pages
where objid = @id
/* index: sum(used) where indid in (0, 1, 255) - data */
update #spt_space
set indexp = (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @id)
- data
where objid = @id
/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
update #spt_space
set unused = reserved
- (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @id)
where objid = @id
update #spt_space
set rows = i.rows
from sysindexes i
where i.indid < 2
and i.id = @id
and objid = @id
fetch next from c_tables
into @id
end
select TableName = (select left(name,60) from sysobjects where id = objid),
Rows = convert(char(11), rows),
ReservedKB = ltrim(str(reserved * d.low / 1024.,15,0) + ' ' + 'KB'),
DataKB = ltrim(str(data * d.low / 1024.,15,0) + ' ' + 'KB'),
IndexSizeKB = ltrim(str(indexp * d.low / 1024.,15,0) + ' ' + 'KB'),
UnusedKB = ltrim(str(unused * d.low / 1024.,15,0) + ' ' + 'KB')
from #spt_space, master.dbo.spt_values d
where d.number = 1
and d.type = 'E'
order by reserved desc
drop table #spt_space
close c_tables
deallocate c_tables
Wednesday, December 2, 2009
Monday, November 23, 2009
Permissions
SELECT prin.[name] [User], sec.state_desc + ' ' + sec.permission_name [Permission]
FROM [sys].[database_permissions] sec
JOIN [sys].[database_principals] prin
ON sec.[grantee_principal_id] = prin.[principal_id]
WHERE sec.class = 0
ORDER BY [User], [Permission];
FROM [sys].[database_permissions] sec
JOIN [sys].[database_principals] prin
ON sec.[grantee_principal_id] = prin.[principal_id]
WHERE sec.class = 0
ORDER BY [User], [Permission];
List counts of all tables in DB
select o.name as "Table Name",i.rowcnt as "Row Count"
from sysobjects o, sysindexes i
where i.id = o.id
and indid in(0,1)
order by o.name
from sysobjects o, sysindexes i
where i.id = o.id
and indid in(0,1)
order by o.name
Thursday, November 19, 2009
composable DML for Auditing
You can achieve this by using composable DML. You write an UPDATE statement with an OUTPUT clause, and define a derived table based on the UPDATE statement. You write an INSERT SELECT statement that queries the derived table, filtering only the subset of rows that is needed. Here's the complete solution code:
INSERT INTO dbo.ProductsAudit(productid, colname, oldval, newval)
SELECT productid, N'unitprice', oldval, newval
FROM (UPDATE dbo.Products
SET unitprice *= 1.15
OUTPUT
inserted.productid,
deleted.unitprice AS oldval,
inserted.unitprice AS newval
WHERE SupplierID = 1) AS D
WHERE oldval <>= 20.0;
INSERT INTO dbo.ProductsAudit(productid, colname, oldval, newval)
SELECT productid, N'unitprice', oldval, newval
FROM (UPDATE dbo.Products
SET unitprice *= 1.15
OUTPUT
inserted.productid,
deleted.unitprice AS oldval,
inserted.unitprice AS newval
WHERE SupplierID = 1) AS D
WHERE oldval <>= 20.0;
SET
Category
Statements
Date and time statements
SET DATEFIRST
SET DATEFORMAT
Locking statements
SET DEADLOCK_PRIORITY
SET LOCK_TIMEOUT
Miscellaneous statements
SET CONCAT_NULL_YIELDS_NULL
SET CURSOR_CLOSE_ON_COMMIT
SET FIPS_FLAGGER
SET IDENTITY_INSERT
SET LANGUAGE
SET OFFSETS
SET QUOTED_IDENTIFIER
Query Execution Statements
SET ARITHABORT
SET ARITHIGNORE
SET FMTONLY
SET NOCOUNT
SET NOEXEC
SET NUMERIC_ROUNDABORT
SET PARSEONLY
SET QUERY_GOVERNOR_COST_LIMIT
SET ROWCOUNT
SET TEXTSIZE
ISO Settings statements
SET ANSI_DEFAULTS
SET ANSI_NULL_DFLT_OFF
SET ANSI_NULL_DFLT_ON
SET ANSI_NULLS
SET ANSI_PADDING
SET ANSI_WARNINGS
Statistics statements
SET FORCEPLAN
SET SHOWPLAN_ALL
SET SHOWPLAN_TEXT
SET SHOWPLAN_XML
SET STATISTICS IO
SET STATISTICS XML
SET STATISTICS PROFILE
SET STATISTICS TIME
Transactions statements
SET IMPLICIT_TRANSACTIONS
SET REMOTE_PROC_TRANSACTIONS
SET TRANSACTION ISOLATION LEVEL
SET XACT_ABORT
Statements
Date and time statements
SET DATEFIRST
SET DATEFORMAT
Locking statements
SET DEADLOCK_PRIORITY
SET LOCK_TIMEOUT
Miscellaneous statements
SET CONCAT_NULL_YIELDS_NULL
SET CURSOR_CLOSE_ON_COMMIT
SET FIPS_FLAGGER
SET IDENTITY_INSERT
SET LANGUAGE
SET OFFSETS
SET QUOTED_IDENTIFIER
Query Execution Statements
SET ARITHABORT
SET ARITHIGNORE
SET FMTONLY
SET NOCOUNT
SET NOEXEC
SET NUMERIC_ROUNDABORT
SET PARSEONLY
SET QUERY_GOVERNOR_COST_LIMIT
SET ROWCOUNT
SET TEXTSIZE
ISO Settings statements
SET ANSI_DEFAULTS
SET ANSI_NULL_DFLT_OFF
SET ANSI_NULL_DFLT_ON
SET ANSI_NULLS
SET ANSI_PADDING
SET ANSI_WARNINGS
Statistics statements
SET FORCEPLAN
SET SHOWPLAN_ALL
SET SHOWPLAN_TEXT
SET SHOWPLAN_XML
SET STATISTICS IO
SET STATISTICS XML
SET STATISTICS PROFILE
SET STATISTICS TIME
Transactions statements
SET IMPLICIT_TRANSACTIONS
SET REMOTE_PROC_TRANSACTIONS
SET TRANSACTION ISOLATION LEVEL
SET XACT_ABORT
Tuesday, November 17, 2009
Resource Database in SQL server 2005/2008
Resource Database in SQL server 2005/2008
Helpful for service pack installation for DBA
The Resource database is a read only, hidden system database that contains all the SQL Server system objects such as sys.objects which are physically available only in the Resource database, even though they logically appear in the SYS schema of every database. The Resource Database does not contain any user data or any user metadata
SELECT SERVERPROPERTY('ResourceVersion')
SELECT SERVERPROPERTY('ResourceLastUpdateDateTime');
Helpful for service pack installation for DBA
The Resource database is a read only, hidden system database that contains all the SQL Server system objects such as sys.objects which are physically available only in the Resource database, even though they logically appear in the SYS schema of every database. The Resource Database does not contain any user data or any user metadata
SELECT SERVERPROPERTY('ResourceVersion')
SELECT SERVERPROPERTY('ResourceLastUpdateDateTime');
Tuesday, November 10, 2009
DW 2.0
DW 2.0 offers some major signifi cant improvements over fi rstgeneration data warehouses.
DW 2.0 offers the recognition that there
is a life cycle of data once it enters the data warehouse, the notion
that structured and unstructured data belong in the data warehouse,
and the idea that metadata is an essential and normal part of the data
warehouse.
Four Sectors are
1. Interactive
2. Integrated
3. Near Line
4. Archival
DW 2.0 offers the recognition that there
is a life cycle of data once it enters the data warehouse, the notion
that structured and unstructured data belong in the data warehouse,
and the idea that metadata is an essential and normal part of the data
warehouse.
Four Sectors are
1. Interactive
2. Integrated
3. Near Line
4. Archival
Monday, November 9, 2009
SQL Server 2008 T-SQL enhanceMents
SQL Server 2008 T-SQL enhanceMents
-----------------------------------------------
1. Initialize the varaible inline as part of the variable declaration statement instead of using separate
DECLARE and SET statements
2. Compound Assignment Operators.
3. Table Value Constructor Support through the Values clause. Use a single VALUES clause to construct a set of rows.
A table value constructor can be used to define table expressions such as key derived tables and CTEs, and can be used where
table expressions are allowed (such as in the FROM clause of a SELECT statement or as the source table in a MERGE statement).
4. Enhanced CONVERT function with Style 0 (default) , Style 1 and Style 2
eg :
SELECT
CONVERT(VARCHAR(12) , 0x49747A696B , 2) ,
CONVERT(VARBINARY(5), '49747A696B' , 2);
This code produces the following output:
49747A696B 0x49747A696B
5. New Date and Time Data Types
4 New data types - DATE, TIME, DATETIME2, and DATETIMEOFFSET
new and Enhanced Functions - SYSDATETIME, SYSUTCDATETIME, SYSDATETIMEOFFSET, SWITCHOFFSET, and TODATETIMEOFFSET
6. Large UDTS ( User Defined Types) case of spatial data
In SQL Server 2005, user-defined types (UDTs) in the CLR were limited to 8,000 bytes, but now 2 GB
7. HIERARCHYID Data Type
The new HIERARCHYID data type in SQL Server 2008 is a system-supplied CLR UDT that can be useful for storing and manipulating
hierarchies
8. Table Types
Table types enable you to save a table definition in the database and use it later to define table variables and parameters to
stored procedures and functions. Because table types let you reuse a table definition, they ensure consistency and reduce
chances for errors
To get metadata information about table types in the database, query the view sys.table_types.
9. Table-Valued Parameters
you cannot set variables and parameters of a table type to NULL.
able-valued parameters are treated internally like table variables. Their scope is the batch (procedure, function). They have several
advantages in some cases over temporary tables and other alternative methods:
· They are strongly typed.
· SQL Server does not maintain distribution statistics (histograms) for them; therefore, they do not cause recompilations.
· They are not affected by a transaction rollback.
· They provide a simple programming model
10. MERGE Statement
The new MERGE statement is a standard statement that combines INSERT, UPDATE, and DELETE actions as a single atomic operation based on
conditional logic
11. GROUPING SETS, CUBE, and ROLLUP Subclauses
eg :
SELECT custid, empid, YEAR(orderdate) AS orderyear, SUM(qty) AS qty
FROM dbo.Orders
GROUP BY GROUPING SETS (
( custid, empid, YEAR(orderdate) ),
( custid, YEAR(orderdate) ),
( empid, YEAR(orderdate) ),
() );
CUBE( a, b, c ) is logically equivalent to: GROUPING SETS((a),(b),(c),(a, b),(a, c),(b, c),(a, b, c),())
For n elements. CUBE produces 2^n grouping sets.
ROLLUP( country, region, city )
is logically equivalent to:
GROUPING SETS((country, region, city),(country, region),(country),())
Notice that cases that have no business value, assuming a hierarchy between the elements—such as (city)—were not produced. There might
be multiple cities with the same name in the world, and even within the same country, so there is no business value in aggregating
them.
12. Grouping Sets Algebra
The following code represents a Cartesian product between two GROUPING SETS subclauses:
GROUPING SETS ( (a, b), (c, d) ), GROUPING SETS ( (w, x), (y, z) )
This code is logically equivalent to:
GROUPING SETS ( (a, b, w, x), (a, b, y, z), (c, d, w, x), (c, d, y, z) )
13. GROUPING_ID Function
The GROUPING_ID function lets you identify the grouping set that each result row belongs to
14. Sparse Columns
Sparse columns are columns that are optimized for the storage of NULLs. To define a column as sparse, specify the SPARSE
attribute as part of the column definition. Sparse columns consume no storage for NULLs, even with fixed-length types; however, when a
column is marked as sparse, storage of non-NULL values becomes more expensive than usual.
15. Filtered Indexes and Statistics
SQL Server 2008 introduces filtered indexes and statistics. You can now create a nonclustered index based on a predicate, and
only the subset of rows for which the predicate holds true are stored in the index B-Tree. Similarly, you can manually create
statistics based on a predicate. The optimizer has the logic to figure out when such filtered indexes and statistics are
useful.Well-designed filtered indexes can improve query performance and plan quality because they are smaller than nonfiltered
indexes. Also, filtered statistics—whether created automatically for a filtered index or manually—are more accurate than
nonfiltered statistics because they need to cover only a subset of rows from the table.
-----------------------------------------------
1. Initialize the varaible inline as part of the variable declaration statement instead of using separate
DECLARE and SET statements
2. Compound Assignment Operators.
3. Table Value Constructor Support through the Values clause. Use a single VALUES clause to construct a set of rows.
A table value constructor can be used to define table expressions such as key derived tables and CTEs, and can be used where
table expressions are allowed (such as in the FROM clause of a SELECT statement or as the source table in a MERGE statement).
4. Enhanced CONVERT function with Style 0 (default) , Style 1 and Style 2
eg :
SELECT
CONVERT(VARCHAR(12) , 0x49747A696B , 2) ,
CONVERT(VARBINARY(5), '49747A696B' , 2);
This code produces the following output:
49747A696B 0x49747A696B
5. New Date and Time Data Types
4 New data types - DATE, TIME, DATETIME2, and DATETIMEOFFSET
new and Enhanced Functions - SYSDATETIME, SYSUTCDATETIME, SYSDATETIMEOFFSET, SWITCHOFFSET, and TODATETIMEOFFSET
6. Large UDTS ( User Defined Types) case of spatial data
In SQL Server 2005, user-defined types (UDTs) in the CLR were limited to 8,000 bytes, but now 2 GB
7. HIERARCHYID Data Type
The new HIERARCHYID data type in SQL Server 2008 is a system-supplied CLR UDT that can be useful for storing and manipulating
hierarchies
8. Table Types
Table types enable you to save a table definition in the database and use it later to define table variables and parameters to
stored procedures and functions. Because table types let you reuse a table definition, they ensure consistency and reduce
chances for errors
To get metadata information about table types in the database, query the view sys.table_types.
9. Table-Valued Parameters
you cannot set variables and parameters of a table type to NULL.
able-valued parameters are treated internally like table variables. Their scope is the batch (procedure, function). They have several
advantages in some cases over temporary tables and other alternative methods:
· They are strongly typed.
· SQL Server does not maintain distribution statistics (histograms) for them; therefore, they do not cause recompilations.
· They are not affected by a transaction rollback.
· They provide a simple programming model
10. MERGE Statement
The new MERGE statement is a standard statement that combines INSERT, UPDATE, and DELETE actions as a single atomic operation based on
conditional logic
11. GROUPING SETS, CUBE, and ROLLUP Subclauses
eg :
SELECT custid, empid, YEAR(orderdate) AS orderyear, SUM(qty) AS qty
FROM dbo.Orders
GROUP BY GROUPING SETS (
( custid, empid, YEAR(orderdate) ),
( custid, YEAR(orderdate) ),
( empid, YEAR(orderdate) ),
() );
CUBE( a, b, c ) is logically equivalent to: GROUPING SETS((a),(b),(c),(a, b),(a, c),(b, c),(a, b, c),())
For n elements. CUBE produces 2^n grouping sets.
ROLLUP( country, region, city )
is logically equivalent to:
GROUPING SETS((country, region, city),(country, region),(country),())
Notice that cases that have no business value, assuming a hierarchy between the elements—such as (city)—were not produced. There might
be multiple cities with the same name in the world, and even within the same country, so there is no business value in aggregating
them.
12. Grouping Sets Algebra
The following code represents a Cartesian product between two GROUPING SETS subclauses:
GROUPING SETS ( (a, b), (c, d) ), GROUPING SETS ( (w, x), (y, z) )
This code is logically equivalent to:
GROUPING SETS ( (a, b, w, x), (a, b, y, z), (c, d, w, x), (c, d, y, z) )
13. GROUPING_ID Function
The GROUPING_ID function lets you identify the grouping set that each result row belongs to
14. Sparse Columns
Sparse columns are columns that are optimized for the storage of NULLs. To define a column as sparse, specify the SPARSE
attribute as part of the column definition. Sparse columns consume no storage for NULLs, even with fixed-length types; however, when a
column is marked as sparse, storage of non-NULL values becomes more expensive than usual.
15. Filtered Indexes and Statistics
SQL Server 2008 introduces filtered indexes and statistics. You can now create a nonclustered index based on a predicate, and
only the subset of rows for which the predicate holds true are stored in the index B-Tree. Similarly, you can manually create
statistics based on a predicate. The optimizer has the logic to figure out when such filtered indexes and statistics are
useful.Well-designed filtered indexes can improve query performance and plan quality because they are smaller than nonfiltered
indexes. Also, filtered statistics—whether created automatically for a filtered index or manually—are more accurate than
nonfiltered statistics because they need to cover only a subset of rows from the table.
Saturday, November 7, 2009
Wednesday, November 4, 2009
Friday, October 30, 2009
Code Samples - Analysis
Extending Excel OLAP Functionalityhttp://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnexcl2k2/html/odc_xlextendolap.asp
Excel XP Add-ins - Sample code for add-ins in Excel XP by Tom Conlon as shown in TechEd presentations
Writeback sample
Drillthrough sample
Excel VBA driven applet that executes MDX queries and places results on a worksheet - By Tom Chesterhttp://www.tomchester.net/articlesdownloads/excel_mdx_applet.html
Reviews
Nigel Pendse's review in OLAP Reporthttp://www.olapreport.com/products/OLAP_spreadsheets.htm
Microsoft Analysis Services + Full Function Excel Add-Ins. The Perfect Combination - whitepaper by Nigel Pendsehttp://www.misag.com.au/include/news/Analysis%20Services.pdf
John Keeley's reviews
http://www.johnkeeley.com/excel_add-ins_functionality.htm
http://www.johnkeeley.com/excel_add-ins_speed_test.htm
Thin Clients
OLAP Services: Web Browser Samplehttp://www.microsoft.com/downloads/details.aspx?FamilyID=d13da3cd-d84e-45a1-8f12-3bfb326ea53b&DisplayLang=en
Microsoft SQL Server 2000 Resource Kit - Analysis Services Thin Web Client Browser by Richard Tkachuk
WebOLAP by Mosaic Interactive
ThinSlicer and ThinSlicer.NET - by Richard Lees http://easternmining.com.au/Default_files/Downloads.htm
IntraSight http://www.activeinterface.com/intrasight.html
Visual Studio .NET Web Control for Business Intelligencehttp://www.microsoft.com/downloads/details.aspx?FamilyId=4599B793-B3C6-4ED5-ACB3-820D0E832151&displaylang=en
XMLA Browser by XMLA Consulting Inchttp://www.xmlabrowser.com
ASP.NET controls http://www.eblocks.com
Office
Using Microsoft Office XP as a BI Clienthttp://msdn.microsoft.com/library/default.asp?url=/servers/books/sqlserver/using.asp
Code Walkthrough: Data Drillthrough from the OWC PivotTable Componenthttp://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnowc/html/odc_pvtdrillthrough.asp
Distributing Enterprise Data via XML with SQL Server 2000 and Excel 2002http://msdn.microsoft.com/library/?url=/library/en-us/dnexcl2k2/html/odc_excelsqlxml.asp
Exploring Microsoft Data Analyzer Programmabilityhttp://msdn.microsoft.com/library/?url=/library/en-us/dnoxpta/html/odc_daprog.asp
Using the Microsoft Data Analyzer ActiveX Control in Web Pageshttp://msdn.microsoft.com/library/?url=/library/en-us/dnoxpta/html/odc_dawebctrl.asp
Max3API Library VBA Code Sample Reference for Microsoft Data Analyzerhttp://msdn.microsoft.com/library/?url=/library/en-us/dnmda/html/odc_daref.asp
MdhInterfacesLib Library VBA Code Sample Reference for Microsoft Data Analyzerhttp://msdn.microsoft.com/library/?url=/library/en-us/dnmda/html/odc_dahierref.asp
MapPoint 2002 OLAP Wizard Add-in - This MapPoint 2002 COM add-in allows you to perform queries on OLAP cube files and create data maps from the query resultshttp://msdn.microsoft.com/library/default.asp?url=/library/en-us/Dnmap02/html/Map_olap.asp
Portfolio Analyzer OLAP Extensionshttp://msdn.microsoft.com/library/default.asp?url=/library/en-us/pjsdk/html/pjsdkCubesOverview_HV01096275.asp
Microsoft Office XP Web Component Toolpack
Conditional Cell Coloring
Drillthrough sample Microsoft Office XP Web Component Toolpack
Actions sample
Excel XP Add-ins - Sample code for add-ins in Excel XP by Tom Conlon as shown in TechEd presentations
Writeback sample
Drillthrough sample
Excel VBA driven applet that executes MDX queries and places results on a worksheet - By Tom Chesterhttp://www.tomchester.net/articlesdownloads/excel_mdx_applet.html
Reviews
Nigel Pendse's review in OLAP Reporthttp://www.olapreport.com/products/OLAP_spreadsheets.htm
Microsoft Analysis Services + Full Function Excel Add-Ins. The Perfect Combination - whitepaper by Nigel Pendsehttp://www.misag.com.au/include/news/Analysis%20Services.pdf
John Keeley's reviews
http://www.johnkeeley.com/excel_add-ins_functionality.htm
http://www.johnkeeley.com/excel_add-ins_speed_test.htm
Thin Clients
OLAP Services: Web Browser Samplehttp://www.microsoft.com/downloads/details.aspx?FamilyID=d13da3cd-d84e-45a1-8f12-3bfb326ea53b&DisplayLang=en
Microsoft SQL Server 2000 Resource Kit - Analysis Services Thin Web Client Browser by Richard Tkachuk
WebOLAP by Mosaic Interactive
ThinSlicer and ThinSlicer.NET - by Richard Lees http://easternmining.com.au/Default_files/Downloads.htm
IntraSight http://www.activeinterface.com/intrasight.html
Visual Studio .NET Web Control for Business Intelligencehttp://www.microsoft.com/downloads/details.aspx?FamilyId=4599B793-B3C6-4ED5-ACB3-820D0E832151&displaylang=en
XMLA Browser by XMLA Consulting Inchttp://www.xmlabrowser.com
ASP.NET controls http://www.eblocks.com
Office
Using Microsoft Office XP as a BI Clienthttp://msdn.microsoft.com/library/default.asp?url=/servers/books/sqlserver/using.asp
Code Walkthrough: Data Drillthrough from the OWC PivotTable Componenthttp://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnowc/html/odc_pvtdrillthrough.asp
Distributing Enterprise Data via XML with SQL Server 2000 and Excel 2002http://msdn.microsoft.com/library/?url=/library/en-us/dnexcl2k2/html/odc_excelsqlxml.asp
Exploring Microsoft Data Analyzer Programmabilityhttp://msdn.microsoft.com/library/?url=/library/en-us/dnoxpta/html/odc_daprog.asp
Using the Microsoft Data Analyzer ActiveX Control in Web Pageshttp://msdn.microsoft.com/library/?url=/library/en-us/dnoxpta/html/odc_dawebctrl.asp
Max3API Library VBA Code Sample Reference for Microsoft Data Analyzerhttp://msdn.microsoft.com/library/?url=/library/en-us/dnmda/html/odc_daref.asp
MdhInterfacesLib Library VBA Code Sample Reference for Microsoft Data Analyzerhttp://msdn.microsoft.com/library/?url=/library/en-us/dnmda/html/odc_dahierref.asp
MapPoint 2002 OLAP Wizard Add-in - This MapPoint 2002 COM add-in allows you to perform queries on OLAP cube files and create data maps from the query resultshttp://msdn.microsoft.com/library/default.asp?url=/library/en-us/Dnmap02/html/Map_olap.asp
Portfolio Analyzer OLAP Extensionshttp://msdn.microsoft.com/library/default.asp?url=/library/en-us/pjsdk/html/pjsdkCubesOverview_HV01096275.asp
Microsoft Office XP Web Component Toolpack
Conditional Cell Coloring
Drillthrough sample Microsoft Office XP Web Component Toolpack
Actions sample
Excel Add-ons for Analysis ...
Microsoft Office Excel Add-in for SQL Server Analysis Serviceshttp://www.microsoft.com/office/solutions/accelerators/exceladdin/default.mspx
XLCubed http://www.xlcubed.com
IntelligentApps http://www.intelligentapps.com
MIS Plain http://www.misag.com
BIXL http://www.bixl.com
QBEX http://www.advanceinfo.com/qbex
Visual OLAP http://www.visualolap.com
O2OLAP http://www.o2olap.com
OLAP NaviGo http://www.olapnavigo.com
Panorama NovaView Excel http://www.panoramasoftware.com
CALUMO - For Excel http://www.calumo.com
XLCubed http://www.xlcubed.com
IntelligentApps http://www.intelligentapps.com
MIS Plain http://www.misag.com
BIXL http://www.bixl.com
QBEX http://www.advanceinfo.com/qbex
Visual OLAP http://www.visualolap.com
O2OLAP http://www.o2olap.com
OLAP NaviGo http://www.olapnavigo.com
Panorama NovaView Excel http://www.panoramasoftware.com
CALUMO - For Excel http://www.calumo.com
Stand-Alone OLAP clients
Panorama NovaView BI Platform http://www.panoramasoftware.com
Proclarity http://www.proclarity.com
TemTec Executive Viewer http://www.temtec.com
Cognos PowerPlay - http://www.cognos.com
BusinessObjects - http://www.businessobjects.com
Crystal Analysis and Reports - http://www.businessobjects.com
MIS onVision - http://www.misag.com
Arcplan inSight and dynaSight http://www.arcplan.com
SoftPro Manager - http://www.softpro.hr
SPSS Olap Hub Information Intelligence Platform - http://www.spss.com/olap_hub
Targit Analysis - http://www.targit.com
Databeacon - http://www.databeacon.com/
Visual Spreadsheet - http://www.tableausoftware.com/
Q4bis - http://www.q4bis.com/
Hummingbird - http://www.hummingbird.com/products/bi/index.html
Vista - http://www.beyond2020.com/products/Vista.html
DeltaMaster - http://www.bissantz.de/index_e.html
Strategy Analyzer - http://www.strategycompanion.com
USoft - http://www.u-soft.com.cn
Proclarity http://www.proclarity.com
TemTec Executive Viewer http://www.temtec.com
Cognos PowerPlay - http://www.cognos.com
BusinessObjects - http://www.businessobjects.com
Crystal Analysis and Reports - http://www.businessobjects.com
MIS onVision - http://www.misag.com
Arcplan inSight and dynaSight http://www.arcplan.com
SoftPro Manager - http://www.softpro.hr
SPSS Olap Hub Information Intelligence Platform - http://www.spss.com/olap_hub
Targit Analysis - http://www.targit.com
Databeacon - http://www.databeacon.com/
Visual Spreadsheet - http://www.tableausoftware.com/
Q4bis - http://www.q4bis.com/
Hummingbird - http://www.hummingbird.com/products/bi/index.html
Vista - http://www.beyond2020.com/products/Vista.html
DeltaMaster - http://www.bissantz.de/index_e.html
Strategy Analyzer - http://www.strategycompanion.com
USoft - http://www.u-soft.com.cn
Microsoft Software for Analysis ( Mosha.com)
Microsoft Analysis Serviceshttp://www.microsoft.com/sql/evaluation/bi/bianalysis.asp
Microsoft Excel PivotTableshttp://www.microsoft.com/excel
Microsoft Office Web Components (OWC)http://www.microsoft.com/downloads/details.aspx?familyid=7287252C-402E-4F72-97A5-E0FD290D4B76&displaylang=en
Microsoft Office Excel Add-in for SQL Server Analysis Serviceshttp://www.microsoft.com/office/solutions/accelerators/exceladdin/default.mspx
Microsoft Data Analyzerhttp://www.microsoft.com/office/dataanalyzer/default.asp
Microsoft Office Business Scorecard Managerhttp://office.microsoft.com/en-us/FX012225041033.aspx
Microsoft SQL Server Reporting Serviceshttp://www.microsoft.com/sql/reporting/default.asp
SQL Server Accelerator for Business Intelligencehttp://www.microsoft.com/sql/ssabi/
Microsoft Business Intelligence Portal by Tomer Ben-Moshehttp://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnowc/html/odc_bip.asp
Visual Studio .NET Web Control for Business Intelligencehttp://www.microsoft.com/downloads/details.aspx?FamilyId=4599B793-B3C6-4ED5-ACB3-820D0E832151&displaylang=en
Microsoft XML for Analysis SDKhttp://www.microsoft.com/downloads/details.aspx?FamilyId=7564A3FD-4729-4B09-9EE7-5E71140186EE&displaylang=en
ADOMD.NEThttp://www.microsoft.com/downloads/details.aspx?FamilyID=790D631B-BFF9-4F4A-B648-E9209E6AC8AD&displaylang=en
Business Scorecards Acceleratorhttp://www.microsoft.com/downloads/details.aspx?FamilyID=3c4a9762-646b-41bd-9d42-5765b262624b&DisplayLang=en
OLAP Manager Add-In Kithttp://www.microsoft.com/sql/downloads/OLAPaddin.asp
DSO/XML conversion utilityhttp://www.microsoft.com/downloads/details.aspx?FamilyID=10744518-a2be-4534-8092-6be753fcc7bf&DisplayLang=en
OLAPScribe - A utility for documenting Analysis Services databasehttp://www.microsoft.com/downloads/details.aspx?FamilyID=D763175B-4022-429F-A9D5-DFE00E8545BD&DisplayLang=en
Analysis Services Parallel Processing Utility (PPU) v3 by Dave Wickert.http://www.microsoft.com/downloads/details.aspx?FamilyId=A2EEF773-6DF7-4688-8211-E02CF13CBDB4&displaylang=en
Microsoft Excel PivotTableshttp://www.microsoft.com/excel
Microsoft Office Web Components (OWC)http://www.microsoft.com/downloads/details.aspx?familyid=7287252C-402E-4F72-97A5-E0FD290D4B76&displaylang=en
Microsoft Office Excel Add-in for SQL Server Analysis Serviceshttp://www.microsoft.com/office/solutions/accelerators/exceladdin/default.mspx
Microsoft Data Analyzerhttp://www.microsoft.com/office/dataanalyzer/default.asp
Microsoft Office Business Scorecard Managerhttp://office.microsoft.com/en-us/FX012225041033.aspx
Microsoft SQL Server Reporting Serviceshttp://www.microsoft.com/sql/reporting/default.asp
SQL Server Accelerator for Business Intelligencehttp://www.microsoft.com/sql/ssabi/
Microsoft Business Intelligence Portal by Tomer Ben-Moshehttp://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnowc/html/odc_bip.asp
Visual Studio .NET Web Control for Business Intelligencehttp://www.microsoft.com/downloads/details.aspx?FamilyId=4599B793-B3C6-4ED5-ACB3-820D0E832151&displaylang=en
Microsoft XML for Analysis SDKhttp://www.microsoft.com/downloads/details.aspx?FamilyId=7564A3FD-4729-4B09-9EE7-5E71140186EE&displaylang=en
ADOMD.NEThttp://www.microsoft.com/downloads/details.aspx?FamilyID=790D631B-BFF9-4F4A-B648-E9209E6AC8AD&displaylang=en
Business Scorecards Acceleratorhttp://www.microsoft.com/downloads/details.aspx?FamilyID=3c4a9762-646b-41bd-9d42-5765b262624b&DisplayLang=en
OLAP Manager Add-In Kithttp://www.microsoft.com/sql/downloads/OLAPaddin.asp
DSO/XML conversion utilityhttp://www.microsoft.com/downloads/details.aspx?FamilyID=10744518-a2be-4534-8092-6be753fcc7bf&DisplayLang=en
OLAPScribe - A utility for documenting Analysis Services databasehttp://www.microsoft.com/downloads/details.aspx?FamilyID=D763175B-4022-429F-A9D5-DFE00E8545BD&DisplayLang=en
Analysis Services Parallel Processing Utility (PPU) v3 by Dave Wickert.http://www.microsoft.com/downloads/details.aspx?FamilyId=A2EEF773-6DF7-4688-8211-E02CF13CBDB4&displaylang=en
Tuesday, October 27, 2009
Find Foreign Key in the DB
SELECT f.name AS ForeignKey,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id,
fc.parent_column_id) AS ColumnName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id,
fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id,
fc.parent_column_id) AS ColumnName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id,
fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id
Thursday, October 22, 2009
Alter Columns.
SELECT 'ALTER TABLE ' + TABLE_SCHEMA + '.' + TABLE_NAME + ' ALTER
COLUMN ' + COLUMN_NAME + ' nVARCHAR(' + CAST(CHARACTER_MAXIMUM_LENGTH
AS VARCHAR(10)) + ')'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = 'VARCHAR'
COLUMN ' + COLUMN_NAME + ' nVARCHAR(' + CAST(CHARACTER_MAXIMUM_LENGTH
AS VARCHAR(10)) + ')'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = 'VARCHAR'
Wednesday, October 21, 2009
GetAllTableSizes
CREATE PROCEDURE GetAllTableSizesAS/* Obtains spaced used data for ALL user tables in the database*/DECLARE @TableName VARCHAR(100) --For storing values in the cursor
--Cursor to get the name of all user tables from the sysobjects listingDECLARE tableCursor CURSORFOR select [name]from dbo.sysobjects where OBJECTPROPERTY(id, N'IsUserTable') = 1FOR READ ONLY
--A procedure level temp table to store the resultsCREATE TABLE #TempTable( tableName varchar(100), numberofRows varchar(100), reservedSize varchar(50), dataSize varchar(50), indexSize varchar(50), unusedSize varchar(50))
--Open the cursorOPEN tableCursor
--Get the first table name from the cursorFETCH NEXT FROM tableCursor INTO @TableName
--Loop until the cursor was not able to fetchWHILE (@@Fetch_Status >= 0)BEGIN --Dump the results of the sp_spaceused query to the temp table INSERT #TempTable EXEC sp_spaceused @TableName
--Get the next table name FETCH NEXT FROM tableCursor INTO @TableNameEND
--Get rid of the cursorCLOSE tableCursorDEALLOCATE tableCursor
--Select all records so we can use the reultsSELECT * FROM #TempTable
--Final cleanup!DROP TABLE #TempTable
GO
--Cursor to get the name of all user tables from the sysobjects listingDECLARE tableCursor CURSORFOR select [name]from dbo.sysobjects where OBJECTPROPERTY(id, N'IsUserTable') = 1FOR READ ONLY
--A procedure level temp table to store the resultsCREATE TABLE #TempTable( tableName varchar(100), numberofRows varchar(100), reservedSize varchar(50), dataSize varchar(50), indexSize varchar(50), unusedSize varchar(50))
--Open the cursorOPEN tableCursor
--Get the first table name from the cursorFETCH NEXT FROM tableCursor INTO @TableName
--Loop until the cursor was not able to fetchWHILE (@@Fetch_Status >= 0)BEGIN --Dump the results of the sp_spaceused query to the temp table INSERT #TempTable EXEC sp_spaceused @TableName
--Get the next table name FETCH NEXT FROM tableCursor INTO @TableNameEND
--Get rid of the cursorCLOSE tableCursorDEALLOCATE tableCursor
--Select all records so we can use the reultsSELECT * FROM #TempTable
--Final cleanup!DROP TABLE #TempTable
GO
Check Table Spaces...............
create table DataAdmin..tblTableSizes(TableEntryID int identity(1,1) primary key,EntryDate smalldatetime not null default getdate(),TableName varchar(50),Row_Count int,ReservedSize varchar(20),DataSize varchar(20),IndexSize varchar(20),UnusedSize varchar(20),CreationDate smalldatetime )
set quoted_identifier off
GO
Use AdventureWorks2000declare @tablename varchar(50), @statement nvarchar(300), @creationdate smalldatetime
declare csrTables cursor for select top 100 percent [name] from AdventureWorks2000..sysobjects (nolock) where xtype = 'U' and left(name, 3) != 'dt_' open csrTablesfetch next from csrTables into @tablenamewhile @@fetch_status = 0beginset @statement = "insert DataAdmin..tblTableSizes(TableName, Row_Count, ReservedSize, DataSize, IndexSize, UnusedSize) exec sp_spaceused '" + @tablename + "'"set @CreationDate = (select crdate from AdventureWorks2000..sysobjects (nolock) where [name] = @tablename)exec sp_executesql @statementupdate DataAdmin..tblTableSizes set CreationDate = @CreationDate where TableName = @TableNamefetch next from csrTables into @tablenameend
close csrTablesdeallocate csrTables
set quoted_identifier onGO
set quoted_identifier off
GO
Use AdventureWorks2000declare @tablename varchar(50), @statement nvarchar(300), @creationdate smalldatetime
declare csrTables cursor for select top 100 percent [name] from AdventureWorks2000..sysobjects (nolock) where xtype = 'U' and left(name, 3) != 'dt_' open csrTablesfetch next from csrTables into @tablenamewhile @@fetch_status = 0beginset @statement = "insert DataAdmin..tblTableSizes(TableName, Row_Count, ReservedSize, DataSize, IndexSize, UnusedSize) exec sp_spaceused '" + @tablename + "'"set @CreationDate = (select crdate from AdventureWorks2000..sysobjects (nolock) where [name] = @tablename)exec sp_executesql @statementupdate DataAdmin..tblTableSizes set CreationDate = @CreationDate where TableName = @TableNamefetch next from csrTables into @tablenameend
close csrTablesdeallocate csrTables
set quoted_identifier onGO
Monday, October 12, 2009
Find lock on MY Table
select convert (smallint, req_spid) As spid, rsc_dbid As dbid, object_name(rsc_objid) As ObjectName, rsc_indid As IndId, substring (v.name, 1, 4) As Type, substring (rsc_text, 1, 32) as Resource, substring (u.name, 1, 8) As Mode, substring (x.name, 1, 5) As Status from master.dbo.syslockinfo, master.dbo.spt_values v, master.dbo.spt_values x, master.dbo.spt_values u where master.dbo.syslockinfo.rsc_type = v.number and v.type = 'LR' and master.dbo.syslockinfo.req_status = x.number and x.type = 'LS' and master.dbo.syslockinfo.req_mode + 1 = u.number and u.type = 'L' and object_name(rsc_objid) = 'My Table ' order by spid
Friday, October 9, 2009
Find Reporting Services DATA
--Find the procedure names that are being used along with report, datasource names
;WITH XMLNAMESPACES (
DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition',
'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd
)
SELECT
name,
x.value('CommandType[1]', 'VARCHAR(50)') AS CommandType,
x.value('CommandText[1]','VARCHAR(50)') AS CommandText,
x.value('DataSourceName[1]','VARCHAR(50)') AS DataSource
FROM (
select name,
CAST(CAST(content AS VARBINARY(MAX)) AS XML) AS reportXML
from dbo.Catalog
where content is not null
and type = 2
) a
CROSS APPLY reportXML.nodes('/Report/DataSets/DataSet/Query') r(x)
WHERE x.value('CommandType[1]', 'VARCHAR(50)') = 'StoredProcedure'
ORDER BY name
--Find the procedure names that are used in reports
;WITH XMLNAMESPACES (
DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition',
'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd
)
SELECT
distinct x.value('CommandText[1]','VARCHAR(50)') AS CommandText
FROM (
select name,
CAST(CAST(content AS VARBINARY(MAX)) AS XML) AS reportXML
from dbo.Catalog
where content is not null
and type = 2
) a
CROSS APPLY reportXML.nodes('/Report/DataSets/DataSet/Query') r(x)
WHERE x.value('CommandType[1]', 'VARCHAR(50)') = 'StoredProcedure'
--Get all text adhoc sql in the reports
;WITH XMLNAMESPACES (
DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition',
'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd
)
SELECT
name,
x.value('CommandType[1]', 'VARCHAR(50)') AS CommandType,
x.value('CommandText[1]','VARCHAR(50)') AS CommandText,
x.value('DataSourceName[1]','VARCHAR(50)') AS DataSource
FROM (
select name,
CAST(CAST(content AS VARBINARY(MAX)) AS XML) AS reportXML
from dbo.Catalog
where content is not null
and type != 3
) a
CROSS APPLY reportXML.nodes('/Report/DataSets/DataSet/Query') r(x)
WHERE x.value('CommandType[1]', 'VARCHAR(50)') is null --= 'CommandText'
ORDER BY name--Find the distinct reports
;WITH XMLNAMESPACES (
DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition',
'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd
)
SELECT
distinct name
FROM (
select name,
CAST(CAST(content AS VARBINARY(MAX)) AS XML) AS reportXML
from dbo.Catalog
where content is not null
and type = 2
) a
ORDER BY name
--Get the data sources
select Path, Name from dbo.Catalog
where Type = 5
--Get the report names and their paths
select Path, Name from dbo.Catalog
where Type = 2
--Get the supporting docs used in the reports
select Path, Name from dbo.Catalog
where Type = 3
--Is there a cache policy to expire after n minutes?
select Path, Name, ExpirationFlags, CacheExpiration
from dbo.Catalog c join dbo.CachePolicy cp
on c.ItemID = cp.reportID
where ExpirationFlags = 1
--Is there a cache policy to expire on a report specific/shared schedule?
select Path, Name, ExpirationFlags, CacheExpiration
from dbo.Catalog c join dbo.CachePolicy cp
on c.ItemID = cp.reportID
where ExpirationFlags = 2
--Which users are using these reports
select UserName from dbo.Users
where UserName not in
('Everyone', 'NT AUTHORITY\SYSTEM', 'NT AUTHORITY\NETWORK SERVICE',
'BUILTIN\Administrators', 'Domain Users')
--find the favorite rendering types
select format, count(1) as cnt
from dbo.ExecutionLog
group by format
order by cnt
--Get the most frequently used reports
select c.Path, c.Name, count(1) as cnt
from dbo.ExecutionLog e join dbo.Catalog c
on e.ReportID = c.ItemID
group by c.Path, c.Name
order by cnt desc
--Get the reports that take longer to retrieve the data
select c.Path, c.Name, max(TimeDataRetrieval)/1000. as seconds
from dbo.ExecutionLog e join dbo.Catalog c
on e.ReportID = c.ItemID
group by c.Path, c.Name
order by seconds desc
--Get the most frequent users
select UserName, count(1) as cnt from dbo.ExecutionLog
group by UserName
order by cnt desc
--Get the latest subscription reports delivered
select c.Path, c.Name, LastRunTime, replace(LastStatus, 'Mail sent to ','') as lastStatus
from dbo.Subscriptions s join dbo.Catalog c
on s.Report_OID = c.ItemID
order by LastRunTime desc
--get users and thier roles
select UserName, RoleName, Description
from dbo.Roles r join dbo.PolicyUserRole pur
on r.roleid = pur.roleid
join dbo.Users u
on pur.userid = u.userid
where UserName not in ('BUILTIN\Administrators', 'Domain Users', 'IUSR_REPORT02')
order by UserName
;WITH XMLNAMESPACES (
DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition',
'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd
)
SELECT
name,
x.value('CommandType[1]', 'VARCHAR(50)') AS CommandType,
x.value('CommandText[1]','VARCHAR(50)') AS CommandText,
x.value('DataSourceName[1]','VARCHAR(50)') AS DataSource
FROM (
select name,
CAST(CAST(content AS VARBINARY(MAX)) AS XML) AS reportXML
from dbo.Catalog
where content is not null
and type = 2
) a
CROSS APPLY reportXML.nodes('/Report/DataSets/DataSet/Query') r(x)
WHERE x.value('CommandType[1]', 'VARCHAR(50)') = 'StoredProcedure'
ORDER BY name
--Find the procedure names that are used in reports
;WITH XMLNAMESPACES (
DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition',
'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd
)
SELECT
distinct x.value('CommandText[1]','VARCHAR(50)') AS CommandText
FROM (
select name,
CAST(CAST(content AS VARBINARY(MAX)) AS XML) AS reportXML
from dbo.Catalog
where content is not null
and type = 2
) a
CROSS APPLY reportXML.nodes('/Report/DataSets/DataSet/Query') r(x)
WHERE x.value('CommandType[1]', 'VARCHAR(50)') = 'StoredProcedure'
--Get all text adhoc sql in the reports
;WITH XMLNAMESPACES (
DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition',
'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd
)
SELECT
name,
x.value('CommandType[1]', 'VARCHAR(50)') AS CommandType,
x.value('CommandText[1]','VARCHAR(50)') AS CommandText,
x.value('DataSourceName[1]','VARCHAR(50)') AS DataSource
FROM (
select name,
CAST(CAST(content AS VARBINARY(MAX)) AS XML) AS reportXML
from dbo.Catalog
where content is not null
and type != 3
) a
CROSS APPLY reportXML.nodes('/Report/DataSets/DataSet/Query') r(x)
WHERE x.value('CommandType[1]', 'VARCHAR(50)') is null --= 'CommandText'
ORDER BY name--Find the distinct reports
;WITH XMLNAMESPACES (
DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition',
'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd
)
SELECT
distinct name
FROM (
select name,
CAST(CAST(content AS VARBINARY(MAX)) AS XML) AS reportXML
from dbo.Catalog
where content is not null
and type = 2
) a
ORDER BY name
--Get the data sources
select Path, Name from dbo.Catalog
where Type = 5
--Get the report names and their paths
select Path, Name from dbo.Catalog
where Type = 2
--Get the supporting docs used in the reports
select Path, Name from dbo.Catalog
where Type = 3
--Is there a cache policy to expire after n minutes?
select Path, Name, ExpirationFlags, CacheExpiration
from dbo.Catalog c join dbo.CachePolicy cp
on c.ItemID = cp.reportID
where ExpirationFlags = 1
--Is there a cache policy to expire on a report specific/shared schedule?
select Path, Name, ExpirationFlags, CacheExpiration
from dbo.Catalog c join dbo.CachePolicy cp
on c.ItemID = cp.reportID
where ExpirationFlags = 2
--Which users are using these reports
select UserName from dbo.Users
where UserName not in
('Everyone', 'NT AUTHORITY\SYSTEM', 'NT AUTHORITY\NETWORK SERVICE',
'BUILTIN\Administrators', 'Domain Users')
--find the favorite rendering types
select format, count(1) as cnt
from dbo.ExecutionLog
group by format
order by cnt
--Get the most frequently used reports
select c.Path, c.Name, count(1) as cnt
from dbo.ExecutionLog e join dbo.Catalog c
on e.ReportID = c.ItemID
group by c.Path, c.Name
order by cnt desc
--Get the reports that take longer to retrieve the data
select c.Path, c.Name, max(TimeDataRetrieval)/1000. as seconds
from dbo.ExecutionLog e join dbo.Catalog c
on e.ReportID = c.ItemID
group by c.Path, c.Name
order by seconds desc
--Get the most frequent users
select UserName, count(1) as cnt from dbo.ExecutionLog
group by UserName
order by cnt desc
--Get the latest subscription reports delivered
select c.Path, c.Name, LastRunTime, replace(LastStatus, 'Mail sent to ','') as lastStatus
from dbo.Subscriptions s join dbo.Catalog c
on s.Report_OID = c.ItemID
order by LastRunTime desc
--get users and thier roles
select UserName, RoleName, Description
from dbo.Roles r join dbo.PolicyUserRole pur
on r.roleid = pur.roleid
join dbo.Users u
on pur.userid = u.userid
where UserName not in ('BUILTIN\Administrators', 'Domain Users', 'IUSR_REPORT02')
order by UserName
Encrpt all Stored Procedures.
CREATE FUNCTION [dbo].[fncGetAsIndex](@searchText nvarchar(max))RETURNS intASBEGIN
DECLARE @asIndex_current int,@asIndex_total int declare @preChar char(1),@postChar char(1)
set @asIndex_current = 0 ; set @asIndex_total = 0
while (charindex ('as',lower(@searchText)) > 0 )
begin
set @asIndex_current = charindex ('as',lower(@searchText)) set @asIndex_total = @asIndex_total + @asIndex_current + case when @asIndex_total=0 then 0 else len('as')-1 end
set @preChar = substring(@searchText,@asIndex_current - 1 , 1) set @postChar = substring(@searchText,@asIndex_current + len('as') ,1) if (@preChar = '' or @preChar = ')' or @preChar = char(10) or @preChar = char(9))
begin
if (@preChar = '' and ( @postChar = '' or @postChar = char(13) or @postChar = char(9)) )
return @asIndex_total
if (@preChar = ')' and ( @postChar = '' or @postChar = char(13) or @postChar = char(9)) )
return @asIndex_total
if (@preChar = char(10) and ( @postChar = '' or @postChar = char(13) or @postChar = char(9)) )
return @asIndex_total
if (@preChar = char(9) and ( @postChar = '' or @postChar = char(13)) )
return @asIndex_total
end
set @searchText = substring(@searchText,@asIndex_current + len('as') ,len(@searchText) - @asIndex_current)
end
RETURN 0
END
----------------------------------------------------
CREATE FUNCTION [dbo].[fncGetCreateIndex](@type varchar(2),@searchText nvarchar(max))RETURNS intASBEGIN
declare @typeStatement varchar(10)
if @type = 'P' set @typeStatement = 'PROC' else set @typeStatement = 'FUNCTION' DECLARE @createIndex_current int,@createIndex_total int set @createIndex_current = 0 ; set @createIndex_total = 0
while (charindex ('create',lower(@searchText)) > 0 )
begin
set @createIndex_current = charindex ('create',lower(@searchText)) set @createIndex_total = @createIndex_total + @createIndex_current + case when @createIndex_total=0 then 0 else len('create')-1 end
if (lower(left(ltrim(rtrim(substring(@searchText,@createIndex_current + len('create'),len(@searchText)))),len(@typeStatement))) = lower(@typeStatement))
return @createIndex_total
set @searchText = substring(@searchText,@createIndex_current + len('create') ,len(@searchText) - @createIndex_current) end
RETURN 0
END
------------------------------------------------------------------------
CREATE proc [dbo].[NwdicEncrypt]@type varchar(2),@physicalTableName nvarchar(100)
as
begin
IF OBJECT_ID('tempdb..#backup') IS NOT NULL DROP TABLE #backup
CREATE TABLE #backup ( id BIGINT IDENTITY(1, 1), spname NVARCHAR(100) NOT NULL, sptext NVARCHAR(MAX) NOT NULL, encrypttext NVARCHAR(MAX) NULL, encryptstatus BIT NOT NULL DEFAULT ( 0 ), errMessage NVARCHAR(max) NULL )
DECLARE @sptexttable TABLE ( id BIGINT IDENTITY(1, 1), spname NVARCHAR(100), sptext NVARCHAR(MAX) )
INSERT INTO @sptexttable (spname , sptext) select [name], object_definition(object_id) as [text] from sys.objects where [type]=@type and object_definition(object_id) is not null and [name] not in ('NwdicEncrypt','fncGetAsIndex')
DECLARE @sptext NVARCHAR(MAX), @spname NVARCHAR(max)DECLARE @errMessage NVARCHAR(MAX)DECLARE @counter BIGINT
SET @counter = 1
WHILE (@counter <= ( SELECT MAX(id) FROM @sptexttable )) BEGIN
INSERT INTO #backup ( spname , sptext) SELECT spname, sptext FROM @sptexttable WHERE id = @counter
DECLARE @tempproc NVARCHAR(MAX) DECLARE @procindex INT DECLARE @newProc NVARCHAR(MAX) DECLARE @newheader NVARCHAR(MAX) DECLARE @header NVARCHAR(MAX) DECLARE @asindex INT DECLARE @replacetext NVARCHAR(MAX)
select @tempproc=sptext FROM @sptexttable WHERE id = @counter
BEGIN TRY
set @procindex = dbo.[fncGetCreateIndex](@type,@tempproc)
set @asindex = dbo.fncGetAsIndex(@tempproc)
select @spname = spname FROM @sptexttable WHERE id = @counter
set @header = SUBSTRING(@tempproc, @procindex, @asindex - @procindex)
set @newheader = 'ALTER ' + SUBSTRING(@header,LEN('create') + 1 ,len(@header) ) set @newheader = STUFF(@newheader, len(@newheader), 1, CHAR(13) + 'WITH ENCRYPTION' + CHAR(13))
set @newProc = REPLACE(@tempproc, @header, @newheader)
BEGIN TRY
begin EXEC ( @newProc ) UPDATE #backup SET encrypttext = @newProc, encryptstatus = 1 WHERE id = @counter end
END TRY BEGIN CATCH
begin set @errMessage = error_message() UPDATE #backup SET encrypttext = @newProc, encryptstatus = 0 , errMessage = @errMessage WHERE id = @counter end
END CATCH
END TRY BEGIN CATCH
begin set @errMessage = error_message() UPDATE #backup SET encrypttext = @newProc, encryptstatus = 0 , errMessage = @errMessage WHERE id = @counter end
END CATCH
SET @counter = @counter + 1
END exec ('IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + @physicalTableName + ']'') AND type in (N''U''))DROP TABLE [dbo].[' + @physicalTableName + ']
create table ' + @physicalTableName + ' ( id BIGINT , spname NVARCHAR(100) NOT NULL, sptext NVARCHAR(MAX) NOT NULL, encrypttext NVARCHAR(MAX) NULL, encryptstatus BIT NOT NULL DEFAULT ( 0 ), errMessage NVARCHAR(max) NULL ) insert into ' + @physicalTableName + ' select * from #backup'
)
end
DECLARE @asIndex_current int,@asIndex_total int declare @preChar char(1),@postChar char(1)
set @asIndex_current = 0 ; set @asIndex_total = 0
while (charindex ('as',lower(@searchText)) > 0 )
begin
set @asIndex_current = charindex ('as',lower(@searchText)) set @asIndex_total = @asIndex_total + @asIndex_current + case when @asIndex_total=0 then 0 else len('as')-1 end
set @preChar = substring(@searchText,@asIndex_current - 1 , 1) set @postChar = substring(@searchText,@asIndex_current + len('as') ,1) if (@preChar = '' or @preChar = ')' or @preChar = char(10) or @preChar = char(9))
begin
if (@preChar = '' and ( @postChar = '' or @postChar = char(13) or @postChar = char(9)) )
return @asIndex_total
if (@preChar = ')' and ( @postChar = '' or @postChar = char(13) or @postChar = char(9)) )
return @asIndex_total
if (@preChar = char(10) and ( @postChar = '' or @postChar = char(13) or @postChar = char(9)) )
return @asIndex_total
if (@preChar = char(9) and ( @postChar = '' or @postChar = char(13)) )
return @asIndex_total
end
set @searchText = substring(@searchText,@asIndex_current + len('as') ,len(@searchText) - @asIndex_current)
end
RETURN 0
END
----------------------------------------------------
CREATE FUNCTION [dbo].[fncGetCreateIndex](@type varchar(2),@searchText nvarchar(max))RETURNS intASBEGIN
declare @typeStatement varchar(10)
if @type = 'P' set @typeStatement = 'PROC' else set @typeStatement = 'FUNCTION' DECLARE @createIndex_current int,@createIndex_total int set @createIndex_current = 0 ; set @createIndex_total = 0
while (charindex ('create',lower(@searchText)) > 0 )
begin
set @createIndex_current = charindex ('create',lower(@searchText)) set @createIndex_total = @createIndex_total + @createIndex_current + case when @createIndex_total=0 then 0 else len('create')-1 end
if (lower(left(ltrim(rtrim(substring(@searchText,@createIndex_current + len('create'),len(@searchText)))),len(@typeStatement))) = lower(@typeStatement))
return @createIndex_total
set @searchText = substring(@searchText,@createIndex_current + len('create') ,len(@searchText) - @createIndex_current) end
RETURN 0
END
------------------------------------------------------------------------
CREATE proc [dbo].[NwdicEncrypt]@type varchar(2),@physicalTableName nvarchar(100)
as
begin
IF OBJECT_ID('tempdb..#backup') IS NOT NULL DROP TABLE #backup
CREATE TABLE #backup ( id BIGINT IDENTITY(1, 1), spname NVARCHAR(100) NOT NULL, sptext NVARCHAR(MAX) NOT NULL, encrypttext NVARCHAR(MAX) NULL, encryptstatus BIT NOT NULL DEFAULT ( 0 ), errMessage NVARCHAR(max) NULL )
DECLARE @sptexttable TABLE ( id BIGINT IDENTITY(1, 1), spname NVARCHAR(100), sptext NVARCHAR(MAX) )
INSERT INTO @sptexttable (spname , sptext) select [name], object_definition(object_id) as [text] from sys.objects where [type]=@type and object_definition(object_id) is not null and [name] not in ('NwdicEncrypt','fncGetAsIndex')
DECLARE @sptext NVARCHAR(MAX), @spname NVARCHAR(max)DECLARE @errMessage NVARCHAR(MAX)DECLARE @counter BIGINT
SET @counter = 1
WHILE (@counter <= ( SELECT MAX(id) FROM @sptexttable )) BEGIN
INSERT INTO #backup ( spname , sptext) SELECT spname, sptext FROM @sptexttable WHERE id = @counter
DECLARE @tempproc NVARCHAR(MAX) DECLARE @procindex INT DECLARE @newProc NVARCHAR(MAX) DECLARE @newheader NVARCHAR(MAX) DECLARE @header NVARCHAR(MAX) DECLARE @asindex INT DECLARE @replacetext NVARCHAR(MAX)
select @tempproc=sptext FROM @sptexttable WHERE id = @counter
BEGIN TRY
set @procindex = dbo.[fncGetCreateIndex](@type,@tempproc)
set @asindex = dbo.fncGetAsIndex(@tempproc)
select @spname = spname FROM @sptexttable WHERE id = @counter
set @header = SUBSTRING(@tempproc, @procindex, @asindex - @procindex)
set @newheader = 'ALTER ' + SUBSTRING(@header,LEN('create') + 1 ,len(@header) ) set @newheader = STUFF(@newheader, len(@newheader), 1, CHAR(13) + 'WITH ENCRYPTION' + CHAR(13))
set @newProc = REPLACE(@tempproc, @header, @newheader)
BEGIN TRY
begin EXEC ( @newProc ) UPDATE #backup SET encrypttext = @newProc, encryptstatus = 1 WHERE id = @counter end
END TRY BEGIN CATCH
begin set @errMessage = error_message() UPDATE #backup SET encrypttext = @newProc, encryptstatus = 0 , errMessage = @errMessage WHERE id = @counter end
END CATCH
END TRY BEGIN CATCH
begin set @errMessage = error_message() UPDATE #backup SET encrypttext = @newProc, encryptstatus = 0 , errMessage = @errMessage WHERE id = @counter end
END CATCH
SET @counter = @counter + 1
END exec ('IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + @physicalTableName + ']'') AND type in (N''U''))DROP TABLE [dbo].[' + @physicalTableName + ']
create table ' + @physicalTableName + ' ( id BIGINT , spname NVARCHAR(100) NOT NULL, sptext NVARCHAR(MAX) NOT NULL, encrypttext NVARCHAR(MAX) NULL, encryptstatus BIT NOT NULL DEFAULT ( 0 ), errMessage NVARCHAR(max) NULL ) insert into ' + @physicalTableName + ' select * from #backup'
)
end
Wednesday, September 30, 2009
Script to Check SQL Server Connection Pooling from different Application with Different Logins
Create the table in the Master db as
--Create the dbo.ServerLogonHistory TableCREATE TABLE dbo.ServerLogonHistory ( EventType VARCHAR(512), PostTime DATETIME, SPID INT, ServerName VARCHAR(512), LoginName VARCHAR(512), LoginType VARCHAR(512), SID VARCHAR(512), ClientHost VARCHAR(512), IsPooled BIT )GO --Create the Logon Trigger Trigger_ServerLogonCREATE TRIGGER Trigger_ServerLogon ON ALL SERVER WITH EXECUTE AS 'sa' FOR LOGON AS BEGIN DECLARE @data XML SET @data = EVENTDATA() INSERT INTO dbo.ServerLogonHistory SELECT @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(512)') , @data.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime') , @data.value('(/EVENT_INSTANCE/SPID)[1]', 'nvarchar(4)') , @data.value('(/EVENT_INSTANCE/ServerName)[1]', 'nvarchar(512)') , @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(512)') , @data.value('(/EVENT_INSTANCE/LoginType)[1]', 'nvarchar(512)') , @data.value('(/EVENT_INSTANCE/SID)[1]', 'nvarchar(512)') , @data.value('(/EVENT_INSTANCE/ClientHost)[1]', 'nvarchar(512)') , @data.value('(/EVENT_INSTANCE/IsPooled)[1]', 'nvarchar(1)') ENDGO
IsPooled determines whether the connection is pooled or not !!!!!
--Create the dbo.ServerLogonHistory TableCREATE TABLE dbo.ServerLogonHistory ( EventType VARCHAR(512), PostTime DATETIME, SPID INT, ServerName VARCHAR(512), LoginName VARCHAR(512), LoginType VARCHAR(512), SID VARCHAR(512), ClientHost VARCHAR(512), IsPooled BIT )GO --Create the Logon Trigger Trigger_ServerLogonCREATE TRIGGER Trigger_ServerLogon ON ALL SERVER WITH EXECUTE AS 'sa' FOR LOGON AS BEGIN DECLARE @data XML SET @data = EVENTDATA() INSERT INTO dbo.ServerLogonHistory SELECT @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(512)') , @data.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime') , @data.value('(/EVENT_INSTANCE/SPID)[1]', 'nvarchar(4)') , @data.value('(/EVENT_INSTANCE/ServerName)[1]', 'nvarchar(512)') , @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(512)') , @data.value('(/EVENT_INSTANCE/LoginType)[1]', 'nvarchar(512)') , @data.value('(/EVENT_INSTANCE/SID)[1]', 'nvarchar(512)') , @data.value('(/EVENT_INSTANCE/ClientHost)[1]', 'nvarchar(512)') , @data.value('(/EVENT_INSTANCE/IsPooled)[1]', 'nvarchar(1)') ENDGO
IsPooled determines whether the connection is pooled or not !!!!!
Subscribe to:
Comments (Atom)