Subhash Subramanyam' s BI Blog

Some Shortcuts in SSMS Query Editor

noreply@blogger.com (Subhash Subramanyam) — Wed, 21 Sep 2011 12:16:00 +0000

List of Shortcuts which you can use while using SSMS Query editor.
1) Ctrl+Shift+Q -> Pops up the Query Builder
2) Highlight and Press Alt+ F1 Brings all the structural details for which an equivalent replacement is sp_help
3) sp_helptext 'SPName' for scripting out the code contents of a Procedure.
4) Ctrl+K, then Ctrl+C to comment lines
5) Ctrl+K, then Ctrl+U to uncomment lines

Few SSRS Issues and Workarounds I encountered

noreply@blogger.com (Subhash Subramanyam) — Wed, 21 Sep 2011 12:13:00 +0000

SSRS 2005:

Sno	Problem/ Issue	Workaround / Comments
1	Once the data is exported to excel, few columns are merged in the excel sheet.	a. Keep the header into excel header This can be done by including SimplePageHeaders=true in device info. Another tips is to remove the blank columns by adding RemoveSpace item in device info. A sample of reconfiguring device info thro Reportserver.config is pasted below. <Extension Name="EXCEL" Type="Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer,Microsoft.ReportingServices.ExcelRendering"> <Configuration> <DeviceInfo> <SimplePageHeaders>true</SimplePageHeaders> <RemoveSpace>0.012in</RemoveSpace> </DeviceInfo> </Configuration> </Extension> b. Change width to points c. Left and right edges of all report items line up with each other.
2	1. Table control header contents within Matrix Row header will not be displayed in Excel export. 2. Interactive Sorting in Matrix, 3. Hiding columns in matrix due to inavailability of Visibility property	These are limitations in 2005 edition. Try to get the display of the Matrix in Dataset itself by modifying SQL script and use Table instead.
3	You may have learned about sparklines or you want to add a chart to your Tablix. You either copy/paste your working(!!!) chart into the tablix and then you preview and you get: "The chart 'Chart1' has a detail member with inner members. Detail members can only contain static inner members."	It is a simple logic behind. The chart you are adding already have categories/series axis which is going to grow dynamically, now when you put this chart inside a detail level row of a tablix it is going to throw the below error. You should add the chart in the group header level, in that way the chart gets replicated w.r.t each group item in the tablix. For example “Product Category” can be row header in the tablix and the chart categories/series can have “Product”. Suman.sundar@accenture.com. In a Tablix you cannot put dynamic member in a Detail row. You have to put it into a Group header or Group Footer row. Steps: 1.Insert a table into your report. 2.Set the DataSetName for the Tablix (either in the Properties pane or by dragging a field into the tablix). 3.In Row Groups, right click the (Details1) group. Select Add Group –> Parent Group… 4.In the Tablix group dialog, Select your Group by: clause. 5.Check the Add group header checkbox. 6.Press OK. Notice the two rows next to big parenthesis on the left. These are the rows in your group. The top row (where I has the highlighted cell) is the group header row. You can put a chart in any group header row. The bottom row (where the three link icon is) is the details row. If you put your chart in this row, you will get the aforementioned error. From http://blogs.msdn.com/b/lukaszp/archive/2009/12/21/charts-in-a-tablix-cell-don-t-work-for-me-but-i-ve-see-these-nice-demos-where-they-do.aspx
4	Determine number of Columns shown in matrix	Would you be able to do a count on the dataset to get your column count? Count(Fields!FieldUsedAsColumnGrouping.Value,"DatasetName") Not sure how your data and matrix are setup, I do something similar where I have to know the number of columns so that I can draw an appropriate border line. But I think you should be able to a Count on either the dataset or grouping to get what you are looking for. (I can paste some code come Monday if needed.). From http://stackoverflow.com/questions/495174/reporting-services-determine-number-of-columns-shown-in-matrix

SSRS 2008:

SNo	Issues	Workaround
1	Does not display all the Labels on the axis of a Bar Chart	Check one of the following : 1. Select X-Axis (where we show task names example) -> Right Click Axis Properties -> Axis Options -> check the option "Enable Variable Interval". 2. Right click on the chart, Click Show Data Labels
2	Column Headers in pages are not repeating and they appear only in first page.	Choose Advanced Mode in the Grouping pane (Click on V Button near Column Groups, Select Advanced Mode). Select "Static" that appear under Row Groups,Go to Properties Pane by pressing F4. Under the Properties List, Set the value True for FixedData and RepeatOnNewPage.
3	Query Execution Failed for Dataset	Check one of the following: 1. Remove or comment those Declared variables which is already in the Parameters list 2. If the Stored Procedure was not set to public in the properties section,it will work on local computer but not on the report server. (Right click the stored procedure in Sql server, and click properties, when the box opens up , select Permissions and click add, type in public and click ok.) 3. Increase the SessionTimeout on the SQL Server Reporting Service server to extend the time the session is alive which can increase the time before a failure could occur.Please follow the detailed steps: a) Open Report Manager using http://<servername>/Reports. b) Navigate to the specific report, and then click the tab “Properties”. c) In “Properties” tab, click “Execution” d) Then, select “Limit report execution to the following number of seconds:”, and set it to a new value. 4. If the step3 does not help, please use the following steps: a) Please run the Query string of DataSet in SQL Server Manager Studio, and then check if there have Dead locks in SQL Server b) If so, please re-design the Query String, or optimization the database. c) If not, try rebuilding the report. 5. Make sure that the dataset name is changed everywhere in the report layout, not only in the data tab. All the controls in the report will be pointing to the old dataset name, change these. 6. If you are viewing the report and then you update it, there is some metadata which needs to be rebound when you press the View Report button. This doesn't get rebound correctly. The solution is to navigate back to the folder containing the report and run it again.
4	Display Total Rows for the Dataset. Show table based on record count, hide the table when row count is 0	Add "=CountRows("DataSetName")=0" in the Visibility expression does the trick
5	"Unclosed Quotation Mark" or "Incorrect Syntax near"	Possible Causes:1) The Column values that are part of IN filter may contain a quote - Eliminate these quotes using REPLACE function. 2) While using Dynamic SQL, the Column values built for IN Criteria assigned to a VARCHAR Variable getting truncated due to limitation of the length - All IN filters should be cascaded directly to the EXEC Statement.
6	Report when exported as Pdf inserts alternate blank pages	1. Adjust the Page width by resisizing text, label and other object placed on the report 2. In the Report Properties -> Page set up -> Adjust the Margins, set Left = 1.5cm , right = 0cm, top = 0cm and bottom = 0cm

Thanks and Regards,
Subhash Subramanyam

This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, please notify the sender immediately and delete the original. Any other use of the email by you is prohibited.

Converting non-ansi = or = to SQL 92 type / ansi joins

noreply@blogger.com (Subhash Subramanyam) — Thu, 04 Jun 2009 05:28:00 +0000

Not only such a tool doesn't exist but I would suggest that for thosequeries with more than a single left or right outer join, you really doublecheck the result of your translation to see if they are really OK. You may notice SQL Server 2000 Query Analyzer on higher versions does its best to convert the non-ansi sql code to ansi easily. However you cannot rely on this conversion results unless you verify. Remember there are some post conversion steps:

1. Identify tables participating in outer joins and make sure that all columns addressed for these tables in the WHERE clause should move to JOIN clause. - Hari Suthan, DBA, CTE

2. Since Sql Server 2000 databases are compatible with both ANSI and NON ANSI queries, you can test both legacy and converted code with this version. - Khushwanth, Business Analyst, CTE

3. Beware of changing queries that are part of branches, Validate results for every branch and see if the results for legacy and the ANSI converted are same.

4. You may want check out for another converison tool called SwisSQL Console 4.2 that can be downloaded from - http://www.swissql.com/products/sql-translator/sql-converter.html
(Transact SQL Forums ).
will be adding more as I come across scenarios ...

Thanks
Subhash Subramanyam

Did you know that ISNULL function can take Query as a parameter ?

noreply@blogger.com (Subhash Subramanyam) — Sat, 04 Apr 2009 03:04:00 +0000

I found it tricky to write a single query to achieve a task given by Geetansu Behera. The question can be instantiated as below:

CityID CityName CityDescription

-----------------------------------------------

2 WA Washington

3 CH Chicago

4 OH Ohio

NULL CA California

99 US United States

He wants a query that should return maximum value of CityID if a particular CityDescription all in a single query. Geetansu gave me the first one and I worked it out another way. Many thanks if you could post some new way around:

1) SELECT ISNULL((SELECT CityID FROM City WHERE CityDescription LIKE 'New York') , (SELECT MAX(CityID) FROM City))

2) SELECT CASE WHEN CityID IS NULL THEN MaxCityID ELSE CityID END 'CityID' FROM (SELECT (SELECT CityID FROM City WHERE CityDescription LIKE 'New York') CityID, (SELECT MAX(CityID) FROM City) MaxCityID) B

System Table Queries (2000)

noreply@blogger.com (Subhash Subramanyam) — Fri, 03 Apr 2009 14:56:00 +0000

-- 1) Transact-SQL script to report which columns are identity columns:
SELECT O.name, C.name from sysobjects O
INNER JOIN syscolumns C on O.id = C.id
WHERE o.type='U' AND objectproperty (o.id, 'TABLEHASIDENTITY') = 1
AND columnproperty (o.id, c.name, 'IsIdentity') = 1
ORDER BY O.name, C.name

-- 2) Transact-SQL script to report which tables have triggers
SELECT O.name from sysobjects O
WHERE
o.type='U' AND (objectproperty (o.id, 'TableHasDeleteTrigger') = 1
OR objectproperty (o.id, 'TableHasInsertTrigger') = 1
OR objectproperty (o.id, 'TableHasUpdateTrigger') = 1)
ORDER BY O.name

-- 3) Transact-SQL script to report which tables do not have primary keys
SELECT O.name from sysobjects OWHERE o.type='U' AND objectproperty (o.id, 'TableHasPrimaryKey') = 0ORDER BY O.name

--4) Transact-SQL script to report which tables have identity columns or time stamps:
SELECT O.name from sysobjects O
WHERE o.type='U' AND (objectproperty (o.id, 'TableHasIdentity') = 1
OR objectproperty (o.id, 'TableHasTimestamp') = 1)
ORDER BY O.name

Passing DataTable into a StoredProcedure

noreply@blogger.com (Subhash Subramanyam) — Sun, 29 Mar 2009 22:20:00 +0000

Thanks to Geetanshu Behera who informed me to try this.
-- Run this into one of your Database
-- 1. Create a table to populate via our StoredProcedure

CREATE TABLE Artist
(
[Artist#] int Primary Key Identity,
[Name] Varchar(50) Not Null
)

-- 2. Create a tabletype for Passing the DataTable
CREATE TYPE ArtistType AS TABLE (
[Name] varchar(100) NOT NULL
);

-- 3. Create Procedure that Populates a Table and
CREATE PROCEDURE [dbo].[AddShoppings](
@Artists dbo.ArtistType READONLY
)
AS
BEGIN
-- variables to use
DECLARE @name varchar(100);

-- cursor for artists parameter
DECLARE artistCursor CURSOR FOR SELECT [Name] FROM @Artists;
-- loop through artists
OPEN artistCursor;
FETCH NEXT FROM artistCursor INTO @name;
WHILE @@FETCH_STATUS = 0 BEGIN
-- insert the artist
INSERT INTO Artist ([Name]) VALUES (@name);
FETCH NEXT FROM artistCursor INTO @name;
END;
CLOSE artistCursor;
-- clean-up
DEALLOCATE artistCursor;
END;

-- 6. Modify the ConnectionString in the below code (VB.NET) to Point to your Database

Imports System.Collections
Imports System.Data.SqlClient
Imports System.Data

Public Module PassingDataTableIntostoredProcedure

Public Sub Main()
Dim connectionString As String = "Data Source=SUBHASH;Initial Catalog=A;Integrated Security=SSPI;"
InsertArticle(connectionString)
End Sub

Public Sub InsertArticle(ByVal connectionString As String)
Try
Dim CommandText As String = "AddShoppings"
Dim connection As New SqlConnection(connectionString)
Dim Command As New SqlCommand(CommandText, connection)
connection.Open()
Dim artist As New DataTable
Dim dcol As DataColumn
Dim drow As DataRow

' Create new DataColumn, set DataType, ColumnName
' and add to DataTable.
dcol = New DataColumn()
dcol.DataType = System.Type.GetType("System.String")
dcol.ColumnName = "Name"
dcol.Caption = "Name"
'dcol.AutoIncrement = True
'dcol.AutoIncrementSeed = 1
'dcol.AutoIncrement = True
'dcol.AutoIncrementStep = 1
dcol.ReadOnly = True
dcol.Unique = True

' Add the Column to the DataColumnCollection.
artist.Columns.Add(dcol)

'' Create second column.
'dcol = New DataColumn()
'dcol.DataType = System.Type.GetType("System.String")
'dcol.ColumnName = "Name"
'dcol.AutoIncrement = False
'dcol.Caption = "Nme"
'dcol.ReadOnly = False
'dcol.Unique = False

'' Add the column to the table.
'artist.Columns.Add(dcol)

' Create three new DataRow objects and add
' them to the DataTable
Dim i As Integer
For i = 0 To 2
drow = artist.NewRow()
' drow("Artist#") = i
drow("Name") = "NameValue - " + i.ToString()
artist.Rows.Add(drow)
Next i

'Adding New Data Rows
'Dim dr1, dr2, dr3 As DataRow
'dr1 = artist.NewRow
'dr1.Item("ArtistName") = "Aaron"
'dr2 = artist.NewRow
'dr2.Item("ArtistName") = "Vidya"
'dr3 = artist.NewRow
'dr3.Item("ArtistName") = "Cameroon"

Command.CommandType = CommandType.StoredProcedure
Command.Parameters.AddWithValue("@Artists", artist)
Command.ExecuteNonQuery()
Console.WriteLine("Passing DataTable into Stored Procedure completed")
Console.WriteLine("Test your Table Article for data")
Console.ReadLine()
connection.Close()

Catch ex As Exception

End Try
End Sub 'End Of InsertArticle Procedure

End Module 'End of PassingDataTableIntostoredProcedure Module

-- 7. Run the Code.
-- 8. Check if the Table Artist is populated

List of all Procedures and dependant Tables that are updated using linked server

noreply@blogger.com (Subhash Subramanyam) — Thu, 19 Mar 2009 13:49:00 +0000

Thanks to Deana Ritter for the queries below. Used to retrieve list of all updating stored procedures where we have use of linked servers for replication.
Select object_name(c.id) Proc_Name , object_name(d.depid) Table_Name, d.resultobj
From syscomments c inner join sysdepends d on c.id = d.id
Where d.resultobj = 1 and text like '%LinkedServerName%' and Text like '%OtherDatabaseName%'
Group by object_name(c.id) , object_name(d.depid) , d.resultobj

Query to get a list of foreign key constraints for a table

noreply@blogger.com (Subhash Subramanyam) — Wed, 18 Mar 2009 18:54:00 +0000

SELECT
K_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT ON PT.TABLE_NAME = PK.TABLE_NAME
-- optional:
--ORDER BY
-- 1,2,3,4
WHERE PK.TABLE_NAME='User_table'
Note: You can also see list of Foreign keys for a given table by using sp_help / highlight table with alt+f1

Keep a check on your IDENTITY columns in SQL Server

noreply@blogger.com (Subhash Subramanyam) — Tue, 03 Mar 2009 12:27:00 +0000

Found the helpful link here. Code is very helpful to have an eye on Identity columns that may be likely to encounter Arithmetic overflow error.
/* The SQL Server 2005 version of the stored procedure. It uses new catalog views */
CREATE PROC dbo.CheckIdentities
AS
BEGIN
SET NOCOUNT ON
SELECT QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME(t.name) AS TableName,
c.name AS ColumnName,
CASE c.system_type_id
WHEN 127 THEN 'bigint'
WHEN 56 THEN 'int'
WHEN 52 THEN 'smallint'
WHEN 48 THEN 'tinyint'
END AS 'DataType',
IDENT_CURRENT(SCHEMA_NAME(t.schema_id) + '.' + t.name) AS CurrentIdentityValue,
CASE c.system_type_id
WHEN 127 THEN (IDENT_CURRENT(SCHEMA_NAME(t.schema_id) + '.' + t.name) * 100.) / 9223372036854775807
WHEN 56 THEN (IDENT_CURRENT(SCHEMA_NAME(t.schema_id) + '.' + t.name) * 100.) / 2147483647
WHEN 52 THEN (IDENT_CURRENT(SCHEMA_NAME(t.schema_id) + '.' + t.name) * 100.) / 32767
WHEN 48 THEN (IDENT_CURRENT(SCHEMA_NAME(t.schema_id) + '.' + t.name) * 100.) / 255
END AS 'PercentageUsed'
FROM sys.columns AS c
INNER JOIN
sys.tables AS t
ON t.[object_id] = c.[object_id]
WHERE c.is_identity = 1
ORDER BY PercentageUsed DESC
END

If you try to create the above stored procedure in SQL Server 2000, you will get the following error:
Server: Msg 195, Level 15, State 10, Procedure a, Line 4
'SCHEMA_NAME' is not a recognized function name.
So, here are some SQL Server 2000 compatible versions.

/* The SQL Server 2000 version of the stored procedure. Uses system tables. This should work in SQL Server 7.0 too */
CREATE PROC dbo.CheckIdentities
AS
BEGIN
SET NOCOUNT ON
SELECT QUOTENAME(USER_NAME(t.uid))+ '.' + QUOTENAME(t.name) AS TableName,
c.name AS ColumnName,
CASE c.xtype
WHEN 127 THEN 'bigint'
WHEN 56 THEN 'int'
WHEN 52 THEN 'smallint'
WHEN 48 THEN 'tinyint'
END AS 'DataType',
IDENT_CURRENT(USER_NAME(t.uid) + '.' + t.name) AS CurrentIdentityValue,
CASE c.xtype
WHEN 127 THEN (IDENT_CURRENT(USER_NAME(t.uid) + '.' + t.name) * 100.) / 9223372036854775807
WHEN 56 THEN (IDENT_CURRENT(USER_NAME(t.uid) + '.' + t.name) * 100.) / 2147483647
WHEN 52 THEN (IDENT_CURRENT(USER_NAME(t.uid) + '.' + t.name) * 100.) / 32767
WHEN 48 THEN (IDENT_CURRENT(USER_NAME(t.uid) + '.' + t.name) * 100.) / 255
END AS 'PercentageUsed'
FROM syscolumns AS c
INNER JOIN
sysobjects AS t
ON t.id = c.id
WHERE COLUMNPROPERTY(t.id, c.name, 'isIdentity') = 1
AND OBJECTPROPERTY(t.id, 'isTable') = 1
ORDER BY PercentageUsed DESC
END

/* The SQL Server 2000 version of the stored procedure. Uses INFORMATION_SCHEMA views. */
CREATE PROC dbo.CheckIdentities
AS
BEGIN
SET NOCOUNT ON
SELECT QUOTENAME(t.TABLE_SCHEMA) + '.' + QUOTENAME(t.TABLE_NAME) AS TableName,
c.COLUMN_NAME AS ColumnName,
c.DATA_TYPE AS 'DataType',
IDENT_CURRENT(t.TABLE_SCHEMA + '.' + t.TABLE_NAME) AS CurrentIdentityValue,
CASE c.DATA_TYPE
WHEN 'bigint' THEN (IDENT_CURRENT(t.TABLE_SCHEMA + '.' + t.TABLE_NAME) * 100.) / 9223372036854775807
WHEN 'int' THEN (IDENT_CURRENT(t.TABLE_SCHEMA + '.' + t.TABLE_NAME) * 100.) / 2147483647
WHEN 'smallint' THEN (IDENT_CURRENT(t.TABLE_SCHEMA + '.' + t.TABLE_NAME) * 100.) / 32767
WHEN 'tinyint' THEN (IDENT_CURRENT(t.TABLE_SCHEMA + '.' + t.TABLE_NAME) * 100.) / 255
END AS 'PercentageUsed'
FROM INFORMATION_SCHEMA.COLUMNS AS c
INNER JOIN
INFORMATION_SCHEMA.TABLES AS t
ON c.TABLE_SCHEMA = t.TABLE_SCHEMA AND c.TABLE_NAME = t.TABLE_NAME
WHERE COLUMNPROPERTY(OBJECT_ID(t.TABLE_SCHEMA + '.' + t.TABLE_NAME), c.COLUMN_NAME, 'isIdentity') = 1
AND c.DATA_TYPE IN ('bigint', 'int', 'smallint', 'tinyint')
AND t.TABLE_TYPE = 'BASE TABLE'
ORDER BY PercentageUsed DESC
END / 2147483647 WHEN 52 THEN (IDENT_CURRENT(SCHEMA_NAME(t.schema_id) + '.' + t.name) * 100.) / 32767 WHEN 48 THEN (IDENT_CURRENT(SCHEMA_NAME(t.schema_id) + '.' + t.name) * 100.) / 255 END AS 'PercentageUsed' FROM sys.columns AS c INNER JOIN sys.tables AS t ON t.[object_id] = c.[object_id] WHERE c.is_identity = 1 ORDER BY PercentageUsed DESC
END
If you try to create the above stored procedure in SQL Server 2000, you will get the following error:Server: Msg 195, Level 15, State 10, Procedure a, Line 4'SCHEMA_NAME' is not a recognized function name.So, here are some SQL Server 2000 compatible versions.
/* The SQL Server 2000 version of the stored procedure. Uses system tables. This should work in SQL Server 7.0 too */
CREATE PROC dbo.CheckIdentitiesASBEGIN SET NOCOUNT ON SELECT QUOTENAME(USER_NAME(t.uid))+ '.' + QUOTENAME(t.name) AS TableName, c.name AS ColumnName, CASE c.xtype WHEN 127 THEN 'bigint' WHEN 56 THEN 'int' WHEN 52 THEN 'smallint' WHEN 48 THEN 'tinyint' END AS 'DataType', IDENT_CURRENT(USER_NAME(t.uid) + '.' + t.name) AS CurrentIdentityValue, CASE c.xtype WHEN 127 THEN (IDENT_CURRENT(USER_NAME(t.uid) + '.' + t.name) * 100.) / 9223372036854775807 WHEN 56 THEN (IDENT_CURRENT(USER_NAME(t.uid) + '.' + t.name) * 100.) / 2147483647 WHEN 52 THEN (IDENT_CURRENT(USER_NAME(t.uid) + '.' + t.name) * 100.) / 32767 WHEN 48 THEN (IDENT_CURRENT(USER_NAME(t.uid) + '.' + t.name) * 100.) / 255 END AS 'PercentageUsed' FROM syscolumns AS c INNER JOIN sysobjects AS t ON t.id = c.id WHERE COLUMNPROPERTY(t.id, c.name, 'isIdentity') = 1 AND OBJECTPROPERTY(t.id, 'isTable') = 1 ORDER BY PercentageUsed DESCEND/* The SQL Server 2000 version of the stored procedure. Uses INFORMATION_SCHEMA views. */
CREATE PROC dbo.CheckIdentities
AS
BEGIN SET
NOCOUNT ON
SELECT QUOTENAME(t.TABLE_SCHEMA) + '.' + QUOTENAME(t.TABLE_NAME) AS TableName, c.COLUMN_NAME AS ColumnName, c.DATA_TYPE AS 'DataType', IDENT_CURRENT(t.TABLE_SCHEMA + '.' + t.TABLE_NAME) AS CurrentIdentityValue, CASE c.DATA_TYPE WHEN 'bigint' THEN (IDENT_CURRENT(t.TABLE_SCHEMA + '.' + t.TABLE_NAME) * 100.) / 9223372036854775807 WHEN 'int' THEN (IDENT_CURRENT(t.TABLE_SCHEMA + '.' + t.TABLE_NAME) * 100.) / 2147483647 WHEN 'smallint' THEN (IDENT_CURRENT(t.TABLE_SCHEMA + '.' + t.TABLE_NAME) * 100.) / 32767 WHEN 'tinyint' THEN (IDENT_CURRENT(t.TABLE_SCHEMA + '.' + t.TABLE_NAME) * 100.) / 255 END AS 'PercentageUsed' FROM INFORMATION_SCHEMA.COLUMNS AS c INNER JOIN INFORMATION_SCHEMA.TABLES AS t ON c.TABLE_SCHEMA = t.TABLE_SCHEMA AND c.TABLE_NAME = t.TABLE_NAME WHERE COLUMNPROPERTY(OBJECT_ID(t.TABLE_SCHEMA + '.' + t.TABLE_NAME), c.COLUMN_NAME, 'isIdentity') = 1 AND c.DATA_TYPE IN ('bigint', 'int', 'smallint', 'tinyint') AND t.TABLE_TYPE = 'BASE TABLE' ORDER BY PercentageUsed DESC
END

About Data Profiling Task in SSIS 2008

noreply@blogger.com (Subhash Subramanyam) — Fri, 28 Mar 2008 08:04:00 +0000

It was hard in the beginning to get started with the Data Profiling as I was looking for "how-to" information and happily found them all in a precise series of Jamie and John Welch has stepped ahead to show how the XML of Data Profiling task taken from the Package can be used to profile all the tables in a database (That's a clever attempt...) and Curious .. when Matt Masson will announce the release of Refresh CTP that can allow us to assign expressions directly on Data Profiling task via expression builder? Unfortunately you need to wait. In the meanwhile he has blogged about compromizing this functionality by accessing a data Profile programmatically with a well commented, easy lines of code. I would like to extend here, if Microsoft can integrate the Data Profile viewer into SSIS Package (like a browser tab or dataviewer to see results on the fly) adding bit more visualization enhancements, it would be of great help.

Thanks,
Subhash Subramanyam

My feedback for SQL Server 2008 February CTP-6.

noreply@blogger.com (Subhash Subramanyam) — Wed, 26 Mar 2008 04:27:00 +0000

I invested my time downloading and installing this latest CTP (February 2008 release). This to make you understand why I really felt embarrassing from the beginning I installed this release. Initially I downloaded to install the 1.5 GB huge CTP on 29th of Feb. While installing the splash screen displayed November Release, I was mislead by the information that I had downloaded a wrong setup file by chance. But infact I was wrong to realize it was the same copy that I downloaded once again. Imagine that there is no means that you can help yourself saying that it's a february release and Do not worry, Refresh build will also mislead you by initially giving you a splash screen saying it's a feb release but on the main Installation page, you still see as November CTP Edition.. confusing don't you.
Suddenly on 2nd of March, the Sql server and Sql Server agent stopped working and you cannot start them no matter you try hard attempts for resoluton. I thought that the problem was with privileges, but not until too late that I found the solution here that we need to install the build available after 29th of feb. I re-installed the fresh build and got ultimately disappointed when I saw that Installation failed - Database Engine and Reporting Services failed. Then I scanned through the logs to understand all the internal things that went on during installation and reason why Pre CTP-6 didn't actually do a clean uninstallation. So I had to clean up everything manually to do a fresh install successfully.
I greatly appreciate those who are competing to submit their bugs to win a prize in Bug bash contest . You may watch some comments provided by Microsoft for the feedback (bugs and suggestions) I submitted about the installation failure below.
1 ) Katmai CTP-6 Refresh Build: Reporting Services and Database Engine Services and Reporting Services Fresh Installation failure
Further let me take you through some of the bugs in Katmai CTP-6.
My friend Pedro has recently identified a bug about warning that is missing in SSIS 2008 which was prevalent in SSIS 2005
The next part of the story is about pretty open blocking issues in SSAS and SSRS 2008 when I started working with reporting Services Project, you may look at them here:
2) Katmai CTP 6: Analysis Services 2008 Migration Wizard cannot recognise Analysis Services 2005 Default Instance as a Source
3) Katmai CTP-6: SSRS 2008 MDX Query Builder issue while choosing (Critical) .NET Framework for Analysis Services as a Datasource
4) Katmai CTP-6: Reporting services Project crashes in BIDS often unexpectedly and recovery does not reflect changes done during the last 5th min
5) Katami CTP-6: SSRS 2008 Datasource Wizard pops up T-SQL Query editor in place of MDX Query Builder after choosing OLEDB 9.0/10.0 Provider
I also added a couple of suggestions for improvements in SSRS 2008 (6th one is very much an essential need from developer's perspective). You may look at them here:
6) Katmai : Require list of MDX Query Designer / Builder and MDX Query Editor Improvements
7) Katmai: Require Dundas Maps and Calendar for the RTM or commercial release.
One thing to note here - "I'd rather expect not to hurry in releasing a product, boasting about it's features before ensuring that things are in place and they perfectly work as the way I boasted earlier.. Well Positively, I believe the reason behind Bug bash that helps me understand more..Gr8.. "
Guys, wishing you all the best for the contest and BTW be open about your opinions to comment them out here and take a few minutes to rate my feedback submissions, if you think they are important from your perspective.
Keep looking at this post for new bugs and suggestions submitted by me.
Thanks, Subhash Subramanyam

An approach to Load fact and Dimension tables

noreply@blogger.com (Subhash Subramanyam) — Fri, 21 Mar 2008 07:50:00 +0000

Friends,

Today I got into mood of testing Jamie Thomson's way of loading a fact table using sort and merge join as we see tenfold improvement in performance as opposed to the use of both I/O & memory intensive Look up in SSIS 2005 [You will soon run out of memory or experience unexpected delays while using look ups on bulk dimension on a system with low configuration (2GB, Intel P-IV X86)] . You can see how monthly Ticket Transactions, the excel sheet archive is loaded into Ticket Fact table using sort and inner join on each of the Dimensions as shown below:

For Incremental load of Type 1 Dimensions from the same excel archive before loading fact tables, Andy Leonards method really helps. The same Ticket Transactions Worksheet contain dimensional data which might be updates or new records. shown below is a typical incremental load of Dimension (Here an alternative method I suggest is to multicast the same source to parallel load Dimension as well as Fact table from the same worksheet). what you see below is incremental loading of two dimensions using look ups on the target Dimension sourced from the same worksheet.

In my forthcoming posts, I'd like to cover in brief about the pratical approach taken to test all the new features availed at the end of Feb CTP- 6 SSIS 2008 . Another important post that I wish to include is the SSIS Future Version Wishlist.
Hope everyone is enjoying good friday.
Cheers, Subhash Subramanyam

Useful Oracle SQL PL/SQL Code Snippets for my Reference

noreply@blogger.com (Subhash Subramanyam) — Sun, 30 Dec 2007 06:57:00 +0000

A sample PL / SQL that has 2 Input Paremeters and 2 output parameters that intake and return the values.

CREATE OR REPLACE PROCEDURE SP_WEEKDAYS(START_DT IN DATE, END_DT IN DATE, CALENDARID IN CHAR, TOTALDAYS OUT NUMBER, WORKINGDAYS OUT NUMBER)
IS
BEGIN
SELECT COUNT(WORKDAY_FLG)INTO TOTALDAYS FROM RIP_CAL_FACTORY_CALENDAR
WHERE CALENDAR_DATE BETWEEN START_DT AND END_DT AND (CALENDAR_ID = CALENDARID) ;
SELECT SUM(WORKDAY_FLG) INTO WORKINGDAYS FROM RIP_CAL_FACTORY_CALENDAR
WHERE CALENDAR_DATE BETWEEN START_DT AND END_DT AND (CALENDAR_ID = CALENDARID) ;
END ;

The followng PL/SQL displays the values returned by the above code:

DECLARE
RET_TOTALDAYS NUMBER;
RET_WORKINGDAYS NUMBER;
BEGIN
SP_WEEKDAYS('06-JUN-08','05-DEC-08', 'H9', RET_TOTALDAYS, RET_WORKINGDAYS);
Dbms_Output.Put_line('Working Days = ' || RET_WORKINGDAYS || ' Total_days = ' || RET_TOTALDAYS ) ;
END ;

Useful T-Sql Snippets for my Reference

noreply@blogger.com (Subhash Subramanyam) — Wed, 26 Dec 2007 07:08:00 +0000

Get the list of all Primary Keys and associated foreign keys:

select a.name,c.name as pk_table ,b.name fk_table
from sys.foreign_keys a
inner join sys.sysobjects b on b.id = a.parent_object_id
inner join sys.sysobjects c on c.id = a.referenced_object_id

Richard Campbell's session on Error Handling using Sql Server 2005's Try Catch:

RETRY:
BEGIN TRY
BEGIN TRANSACTION
UPDATE tblContact SET LastName = 'SP_LastName_1' WHERE ContactID = 1
UPDATE tblContact SET LastName = 'SP_LastName_2' WHERE ContactID = 2
COMMIT TRANSACTION
END TRY
BEGIN CATCH
SET @Err = @@ERROR
IF @Err = 1205
ROLLBACK TRANSACTION
INSERT INTO ErrorLog (ErrID, ErrMsg) VALUES (@Err, 'Deadlock recovery attempt.')
WAITFOR DELAY '00:00:10'
GOTO RETRY
IF @Err = 2627
SET @ErrMsg = 'PK Violation.'
IF @ErrMsg IS NULL
SET @ErrMsg = 'Other Error.'
INSERT INTO ErrorLog (ErrID, ErrMsg) VALUES (@Err, @ErrMsg)
END CATCH

If you carefully notice, Catch exception above handles the logic based on the type of error captured.

Conversion of BRIO to SSIS Made Easy

noreply@blogger.com (Subhash Subramanyam) — Tue, 18 Dec 2007 02:49:00 +0000

Prerequisite is that we must know how each BRIO query work and some knowledge of Practical implementation of things in SSIS. A BRIO query may be composed of a set of dependant or independant queries.

Let's summarize essential steps for migration:

1) Each BRIO query may consist of list of queries and each of those results may have dependencies for further stages. Study how they actually work.
a) What are the Sources used
b) How many are the Results output
c) How many are computed columns
d) what are the limits for the rows
e) On what basis to sort the rows
d) Whether they have joins ? If yes what type of join and so on.

2) Create all connections required for querying using Connection Managers in SSIS:
a) Make sure to use appropriate drivers. For instance, IBM Red Brick Driver is essentially an ODBC driver. Sql Server can use Native Client, If we use Oracle connection on 64 bit Box, MSDAORA works with RunTime64bit Property set to false. If we use ORACLE 64-bit Driver for OLEDB, then we must cast those columns in the query mentioning appropriate precisions of defined type.

3) Dealing with BRIO Computed Columns by using matching functionalities in SSIS or SSIS transformations on the fly:

a) Replacing BRIO functions - "Prior" , "Next", "Cume" and other cumulative functions to SSIS equivalent functionality (Note: Require extra attention)
Beware of these functions as they require traversing between prev and next rows or require row wise manipulation. Some of my colleagues were more comfortable using cursors after they found no better way of tracking previous or next rows for manipulation of the outgoing rows, as the usage of SSIS design time variables degrade performance. However we should keep in mind that we can arrive at the right output choosing the best among different ways (You might think I'm very conservative..). Well in our case we tracked that "Next" function was used to remove duplicates in a Column after sorting, for which luckily we had Sort transformation with delete duplicate option selected in SSIS. But in the other case we used Cume, Brio syntax: Computed_Col_C = Cume(A,B), Where A is by default 1. and B increments the cumulative value of A based on the repitition of Key column. Cursor was a bruteforce method to be used here since even newly available functionality like "CTEs using ROW_NUMBER OVER (ORDER BY ID)" was unable replace the equivalent functionality.
However its always better to avoid cursors (impact of study of Inside Sql Server 2005 Programming, Microsoft Press by Grant Fritchey) wherever applicable as they are I/O intensive and main cause for performance degrade as I've dicussed about this already while speaking about dealing with tempdb issues.

b) Solution for BRIO If Else, Nested If Else, Nvl and Nested Nvls computed Columns using Derived Column transformation in SSIS.

NVL Equivaent script in SSIS Derived Transform:
ISNULL(ColA)? (ISNULL(ColB)?ColC:ColB):ColA

A Nested if else equivalent in SSIS Script Component Transform, will be very much straight foward.

4) Limits in BRIO can be replaced by Conditional Split Transform, where the output row can be named based on the filtering condition and can be routed to further transformations or to Destination.

5) BRIO joins using source tables coming from different sources in SSIS involves using Sort transform for every query extract based on a key column and then use merge join selecting the nature of join appropriately. Note that while sorting you may be interest in removing duplicate key columns if appropriate.

6) Lookup or SCD transformations can be applied in some cases where we need to keep track of the changes in records or in case we require to track or re-direct the duplicate rows for further processing.

7) You can parallelize loads in situations where BRIO extracts data from different sources that are to be merged into a single dimension table. Do all the transformations necessary for each of the source query, finally adding the Merge Transformation that simply merge the results which you can further Process or send it to the destination.

Refer Screenshots of few SSIS Packages that are results of BRIO query conversion:

Figure1

Figure 2

Fig 1 and 2, Showing how we can convert BRIO Logic to extract from different sources, clean, massage, sort, merge and join to load finally into a dimension.

Get the Row Counts for all tables in Database in Sql Server 2005

noreply@blogger.com (Subhash Subramanyam) — Fri, 16 Nov 2007 14:44:00 +0000

One way of doing the above is using sys.partitions according to mandar in which the T-SQL can be further cleaned up to eliminate all the System tables and views as given below:

DBCC UPDATEUSAGE(AdventureWorksDW)

SELECT OBJECT_NAME(object_id) TableName,
SUM(Rows) NoOfRows --total up if there is a partition
FROM sys.partitions
WHERE index_id <>) --Restrict the Table Names
GROUP BY object_id

but the problem with the above Sql Statment is that the statistics does not get updated unless we use DCC Updateuage.

Credit goes to Dipendra Baghel, Gidwani Sunil and Prashant tiwari today who were able to write a simple T-SQL using CURSOR ( pasted below) that can take count of rows for all tables and export to a table in a database . Astonishing that atlast I found the same code already written by Madhu K Nair.

Drop table #TEMP

DECLARE @TableName nvarchar(255)
DECLARE @sqlStatement nvarchar(255)
DECLARE @tblRowCount int
CREATE TABLE #temp (table_name sysname,row_count int)

DECLARE @tableNameCursor CURSOR
SET @tableNameCursor = CURSOR FOR SELECT name from sys.tables
OPEN @tableNameCursor
FETCH NEXT FROM @tableNameCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM @tableNameCursor INTO @TableName
select @sqlstatement= 'Select ''' + @TableName + ''', Count(*) from '+ @TableName
select @sqlStatement= 'Insert into #temp '+ @sqlstatement
exec(@sqlStatement)
END
CLOSE @tableNameCursor
DEALLOCATE @tableNameCursor

--Check to see the records
SELECT * FROM #TEMP

Using a stored procedure called sp_spaceused can get you more details along with count of rows as well space used for the table:

Drop table #TEMP

DECLARE @TableName nvarchar(255)
DECLARE @sqlStatement nvarchar(255)
DECLARE @tblRowCount int
CREATE TABLE #temp (table_name sysname,row_count int)

DECLARE @tableNameCursor CURSOR
SET @tableNameCursor = CURSOR FOR SELECT name from sys.tables
OPEN @tableNameCursor
FETCH NEXT FROM @tableNameCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM @tableNameCursor INTO @TableName
select @sqlstatement= 'sp_spaceused '+ '"'+ @TableName +'"'
exec(@sqlStatement)
END
CLOSE @tableNameCursor
DEALLOCATE @tableNameCursor

SELECT * FROM #TEMP

Solutions to space and performance related issues associated with tempdb in Sql Server 2005

noreply@blogger.com (Subhash Subramanyam) — Thu, 15 Nov 2007 04:54:00 +0000

Last week I was given a bunch of stored procedures and informed to modify in such a way that it should not cause TEMPDB space errors in the future (one of the usual error is - "The transaction log for database 'PRO_DB' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases".)

Prior to this, the task had been assigned to my Colleague Deepesh Pathak contributed to adding Begin Transaction and commit transaction for every batch update statement which they found it to resolve nearly 5-10% of the issue. Print messages added with time record for every T-SQL statement, gave clear hints that there were couple of long running transactions. Particulary some transactions nearly took 14 hours to complete and main traps were the index build statements.

So to troubleshoot the issue, I read about "Working with tempdb in Sql Server 2005" a very useful article written by Wei Xiao, Matt Hink, Mirek and Sunil Agarwal . Compiling this article with the extract of other articles like Troubleshotting Performance Problems and Sql Server Security, Performance & Tuning , I was able to implement a solution that not only resolved the tempdb issues but also improved performance remarkably. Following are usual steps involved:

1) Read about the Tempdb objects and when, why and how they are used. Best idea will be to create a test environment.

2) Performance Counters to get tempdb usage statistics(Database Log file(s) Size KB, Log File(s) Used, Free Space in Tempdb,Version store size (KB), Version generation rate (KB), Version Clean rate(KB)) .

3) Using the rich set of DMVs (Dynamic Management Views) provided by Sql Server 2005 : sys.dm_db_file_space_usage, sys.dm_db_session_file_usage and Sys.dm_db_task_space_usage helps to further investigate.
eg:
The following query shows the top five sessions that have allocated a maximum space for user objects and internal objects in tempdb

SELECT top 5 *
FROM sys.dm_db_session_space_usage
ORDER BY (user_objects_alloc_page_count +
internal_objects_alloc_page_count) DESC

The following query shows the top five tasks that are currently executing tasks and consuming the most tempdb space

SELECT top 5 *
FROM sys.dm_db_task_space_usage
ORDER BY (user_objects_alloc_page_count +
internal_objects_alloc_page_count) DESC

4) Version Store: If the version store is not shrinking, it is likely that long running queries is preventing version store clean up. You can query the sys.dm_db_task_space_usage DMV to find out which tasks are consuming the most space in tempdb. and can kill them if appropriate.

SELECT top 5 transaction_id, transaction_sequence_num,
elapsed_time_seconds
FROM sys.dm_tran_active_snapshot_database_transactions
ORDER BY elapsed_time_seconds DESC

5) Tempdb can only be configured in the Simple recovery model. An active long running query can use up all the space preventing the transaction log clean up. sys.dm_tran_active_transactions DMV can be used to find the longest running transactions and can kill the appropriate ones.

Off Tempdb issue, Bit an essential performance tip
7) Tuning the I/O intensive queries (Cursor based) require:
a) Usage of related Performance counters for learning about the Current state of I/O.
b) Monitoring contentions caused by DML and DDL operations by using DMVs such as sys.dm_exec_query_stats, sys.dm_exec_sql_text and sys.dm_exec_query_plan. Eg: Mixing set-based queries wherever cursors can be replaced for a better performance. This will speed up by taking advantage of the best query plan eventually.
c) Use of task Manager to monitor the resource usage.
d) Making sure that we have Minimal I/O bandwidth (replace with a better configuration)

8) Tempdb settings:
a) Enabling Auto grow setting leads to fragmentation usually when the growth exceeds the allocated space. This can be applied as a last resort when there are many unplanned exceptions.
b) Avoid shrinking files as there are limitations tied up with this.

One of the best approach to load data into Oracle data mart using SSIS

noreply@blogger.com (Subhash Subramanyam) — Thu, 15 Nov 2007 03:55:00 +0000

I tried all the drivers MSDAORA, ORAOLEDB and .NET Providers to load from Sql Server to Oracle, but the load time didn't differ. Performance aggravated as we did not have any control of increasing the commit size on Oracle . I understood from Scott Barrett's and Sutha Thiru's blog that using Persistent's SSIS Oracle bulk Load connector gave an outstanding performance. But again the Client Organization standard didn't allow buying a third party tool for the sake of this.

So finally, I read about SQL* Loader and Eureka ...! how about dumping the required Sql Server Data into Flatfiles and then bulk import these files to Oracle using SQL* Loader. As the SQL* Loader has better options to load full or incremental data with a robust performance, it did a miracle..Nearly 80% of the time was saved. I used a Flat File Source Option to overwrite the file for every SQL Server Export in the SSIS and then Import to Sql Loader by running a batch file via Execute Process Task which calls a control file that has a SQL to Bulk Import this flatfile. (You may want to create a branch here for Incremental and Full Load with a precedence expression that validates a boolean variable. This boolean variable can be used in Package Configurations to preset the value whether you want Incremental or Full load).

Th batch file(Full_Load.bat) script is as below:

sqlldr userid=user_ro/user_ro00@ora_datamart control="F:\APPLICATION_JOBS\MY_ASSET\SQL_LOADER_JOBS\FULL_LOAD.ctl" log="F:\APPLICATION_LOGS\MY_ASSET\FULL_LOAD.txt"

/** sqlldr - sql loader utility in the Oracle bin folder. Make sure this is added into PATH (Environmental variable) **/

The Control File (Full_Load.ctl) looks like the one below:
LOAD DATA
INFILE 'F:\APPLICATION_DEPENDENTFILES\MY_ASSET\TABLEEXPORTS\DUMP_FILE.txt'
BADFILE 'F:\APPLICATION_DEPENDENTFILES\MY_ASSET\TABLEEXPORTS\BADFILE.txt'
REPLACE
INTO TABLE SCHEMA.LOAD_TABLE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
COL1,
COL2,
COL3 DATE(19) "YYYY-MM-DD HH24:MI:SS"
)

Note: * COL3 is casted to Date on the fly with a precison and format mentioned.
* Specifying a Bad file assist in tracking inconsistent rows.

Extract Last 12 months data if you have a Month_Code Column of the format 'YYYYMM'

noreply@blogger.com (Subhash Subramanyam) — Wed, 24 Oct 2007 07:50:00 +0000

SELECT * FROM REPORT_DATA
WHERE (MONTH_CODE >=
Replace(LEFT(CONVERT([varchar],dateadd(month,-1,getdate()) , 120), 7),'-','') )

Thanks to Dipendra Baghel who assited in framing this.

Synchronizing all Instances for NLB using ascmd Script

noreply@blogger.com (Subhash Subramanyam) — Tue, 23 Oct 2007 04:08:00 +0000

Thanks to Lin Li for her support on NLB.
1. Download the ascmd Project (used to trigger MDX, XMLA or DMX from batch windows).
2. Compile and run the Project (This creates an exe file called ascmd.exe) . This may require decryption
3. Generate XMLA files that Synchronize one instance with rest of all and store this into a file with xmla extension, say SyncInstance02.xmla, SyncInstance03.xmla and so on.

4. Create a batch file, say Sync.cmd, containing script that use ascmd file to execute XMLA:

ascmd -S -i "SyncInstance02.xmla"
asmcd -S -i "SyncInstance03.xmla"
.... -S i "SyncInstance0n.xmla"

5. Place all the Script and executable files into a folder (.cmd, .xmla, ascmd.exe) into a folder.

6. To Automate the Process:
Use SSASInstance01 for Processing, and the rest of the instance should be synced for all kinds of refreshes done on Instance01. You may add an Execute process task to execute the cmd script after Incrementally or Fully processing SSASInstance01 Objects.

Way to Install Sql Server Integration Services (IS) and Notification Services (NS) on Fail Over Cluster Environment

noreply@blogger.com (Subhash Subramanyam) — Mon, 15 Oct 2007 13:15:00 +0000

I'd like to brief the steps for IS and NS Set up on Clusters:

Add MSDTC as a Clustered Service before starting the IS and NS Installation. Distributed Transaction Coordinator service should be running on both the nodes.
Deploy the SSIS and NS installation separately on both the nodes. One must be in active mode and the other in Passive mode while installing.
Apply Service Pack 2A and Cumulative Hot fix Latest build (Recommended for boxes running analysis Services also, Prevents memory leakage, Avoids Cache memory issues)
Add IS as a Clustered Service
Add NS as a Clustered Service

How to Send a Mail to a Personal Distribution List ?

noreply@blogger.com (Subhash Subramanyam) — Sat, 13 Oct 2007 03:58:00 +0000

Sending a PDL reuires a special email address created by the exchange server to multicast mails to array of Email IDS,
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1922591&SiteID=1

Way to know Sql Server or Analysis Server 2005 Version, SPs and Edition installed

noreply@blogger.com (Subhash Subramanyam) — Tue, 18 Sep 2007 11:25:00 +0000

Sunil (my Lead) asked me about this. When I googled, I found it here. But our problem was that we had only Analysis Services and Integration Services running on our machine. Finally I found that in MSDN forums, that by looking at the version number displayed along with the Server Instance it is also possible to the SP installed on the machine:
- 9.00.1399 for RTM
- 9.00.2047 for SP1
- 9.00.3042 for SP2 and so on.
There are 2 ways to know the Edition of Analysis Server :
1) Run Regedit, Open the Path: %HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\Microsoft SQL Server\MSSQL 2.0\Setup. You will see the Edition Details on the right Pane
2) On SSMS, Connect to Analysis Server, Right Click on the SSAS Server, Report-> Standard Reports -> General

Resolutions for common Issues and Facts while using SSAS 2005

noreply@blogger.com (Subhash Subramanyam) — Mon, 10 Sep 2007 06:13:00 +0000

In general these are some common that we must be aware of while working on SSAS else we end up wasting our precious time googling and reading articles in detail.

Locking mechanisms are inherent in Sql server 2005 (OLAPLockTypes). Hence this feature is deprecated.
* By default if a user is trying to update or process the objects in SSAS 2005. Other user will not be able to access that object until it becomes free.
* Explicit Rollback and Commit transactions provided by the server can help in granting both of the users or neither of them in updating or reading the cube simultaneously
* Query Log table availed in the Sql Server 2000 Analysis Server repository by default was helpful to determine the OLAP usage statistics.
Sql Server 2005 allows this option via its properties where you should manually set a data source where you want to create query log table will be created.
* Structure of this table is fixed and you cannot alter them.
* Binary values are stored in DataSet Column
* You can run trace files at the background initiated by Sql Profiler that helps to store recent information about the query run. However if you want to log every single query or activity then it’s better to set up a trace using.
* How to leverage the Querylog Table? Usage based optimization wizard is meant for this. Required details like begin and end dates are prompted, set to choose: Performance gain of 30%.
Purpose of doing count estimates while partitioning the cube helps in adjusting the performance of the query as well as determining if the limitation has reached (Not more than 20 million rows). Key to choice of storage settings( MOLAP or ROLAP) is here:
* Set the Performance based on frequency of use. eg set 45% Performance with MOLAP on Partition 200708, set 30% Performance with MOLAP on Partition 200707 and so on.
* For rarely queried partitions, use ROLAP with slice value setting.
* Do apply Proactive caching for frequently accessed MOLAP Partitions- An enhancement feature.
While Processing Dimensions, beware of those which exceed 4 GB since an exception is thrown. Finally Sunil has found a workaround that a property for a dimension should be manually set to attributes rather than tables. Take a look at this link for settings required to increase the size of string store. Usually a common error that occur here is "File system error: A File Store error from Write File occurred.Physical file...". Solution is to change all the ProcessingGroup properties to ByAttribute
AMO can be used in ASP.NET by making sure that the Account Name you add in your Application pool has OLAP Admin privileges. Once done you can even delegate the privileges to users using AMO. You can Change the account in Identity tab of your selected Application Pool's Properties of inetmgr.
Clearing Analysis services 2000 Process logs (mdb files or tempdb, incase if you have migrated the repository, Prashant is much aware of this issue)
* Few possible methods mentioned in Microsoft web link:
* Right Click Analysis Services Properties , Logging - >clear the log - > Specify drive having enough space. Dbcc shrinkdatabase tempdb or dbcc shrinkfile(,’space in MB’).
* Alter database tempdb... command to specify the least size. Stop and restart the analysis service.
* But in fact the second step considerably increased my tempdb space.
Have you come across an error which is very common: "Class Not Registered", when you click browse cube. Solution is to install the OWC – Office Web Components.
Validation of Data by browsing dimensions and cubes consists of the following Steps:
* Check if all the levels show data after migration.
* Browse the Cube, Check every dimension against the calculated members, and drill down to the lowest level and match this against legacy.
* Note: Pivoting should also show the similarity in data up to lower levels.
The attribute hierarchy relations will get grouped down at the lowest level attribute (key attribute). In the above case, every attribute must be re-linked manually to its immediate parent attribute.
Recreating the dimensions are required incase of Attributes having Parent Child relationships
Have you come across an error: OLEDB or ODBC error, Login failed for user 'NT AUTHORITY\NetworkService'.;42000. This is because we are supposed to add/ create the above account for the Sql Server in which your data source reside. Do apply sysadmin privlege to it if you are using AS2000. You may want to look at this link for detailed help.
You might get an error - "Server Option cancelled.. check the driver..." , Solution is to check if IIS, Network DTC, Network COM+ in Windows Installation Componenents - > Application Server has been checked. and the respective Services are running in Services snap-in.
"Error in OLAP Storage Engine: Invalid Key Error", Solution is to Check for the Data Quality issues and While processing if you require to ignore these errors , then Change settings under Dimension key errors tab to ignore errors.
"Drill through failed. Error in the OLAP Engine .." . Solution : Under Analysis Services -> Advanced Properties -> Set Value of OLAP\Process\ROLAPProcessingEffort = count(Fact table ) + probable growth of rows. Provided the fact Dimensions have been added for the drill through with ROLAP Storage set.
"Connection timed out .." Solution: Under Analysis Server -> Advanced Properties -> set Value of ConnectionTimeout= 0 and ExceedConnectionTimeout =0.

Populating Oracle Timestamp values into Sql Server Datetime

noreply@blogger.com (Subhash Subramanyam) — Wed, 25 Jul 2007 13:27:00 +0000

This is not as easy as melting the ice. If we do not know how to CAST or convert, we may end up googling around for days.. My special thanks to Gidwani, Sunil (Project Lead, KPIT) and Umachandar who literally answered this issue on MSDN forums.

1) First of all we must know the valid ranges supported by Oracle timestamp and Sql Server Datetime.

2) Understand that: "Migrating values from Oracle to SQL Server is a different ballgame. You will lose precision, values etc. Oracle has more richer support and wider ranges & ANSI SQL implementation. - Jayashankar,Umachandar "

Now Switch to one of these Solutions to cast to Sql Server Datetime:

3) Try to work from oracle side by casting the timestamp columns to return the valid format and range required to populate Sql server Datetime Columns.

"Using Oracle Sql, Use Extract function to extract year, Validate the year, Use Decode Function to subtitute suitable values for the cases as shown - Gidwani, Sunil"

Solution:

REQUESTED_ETA_DATE_CUST (Oracle timestamp)
--------------------------------------------------------
Decode(trunc((Extract(YEAR from REQUESTED_ETA_DATE_CUST))/1753), 0,'01/01/1753 12:00:00 AM', TO_CHAR(REQUESTED_ETA_DATE_CUST, 'MM/DD/YYYY hh:mm:ss AM')) as REQUESTED_ETA_DATE_CUST

4) CAST the timestamp to string which is acceptable format for Sql Server Datetime, Do valid range filtering using SSIS transform.

Solution:

REQUESTED_ETA_DATE_CUST (Oracle timestamp)
--------------------------------------------------------
TO_CHAR(REQUESTED_ETA_DATE_CUST, 'MM/DD/YYYY hh:mm:ss AM')

Use an expression in derived Transform to filter out valid ranges for Sql Server Datetime as follows:

SUBSTRING([REQUESTED_ETA_DATE_CUST], 6,10) < 1753 ? (DT_DBTIMESTAMP)'01/01/1753 12:00 AM' : (DT_DBTIMESTAMP)[REQUESTED_ETA_DATE_CUST] Now change the type of the column now to DT_DBTIMESTAMP to accept the valid datetime value.

Hints:
1) The supported range for SMALLDATETIME is January 1, 1900, through June 6, 2079
2) Notice that the Millisecond part hasn't been solved here yet. If there is a need, Comment on this forum and I'll get back to you ASAP.