Monday, December 21, 2009
Working With NULLS
The above situation arises when:
When SET ANSI_NULLS is ON, a comparison in which one or more of the expressions is NULL does not yield either TRUE or FALSE; it yields UNKNOWN. This is because a value that is unknown cannot be compared logically against any other value. This occurs if either an expression is compared to the literal NULL, or if two expressions are compared and one of them evaluates to NULL. For example, the following comparison always yields UNKNOWN when ANSI_NULLS is ON:
When ANSI_NULLS IS OFF, the above behavior mentioned is not applicable.
Thursday, December 17, 2009
SSRS:Master-Detail Reports...
This will open the expression window where you will type:
="javascript:void(window.open(" & Code.GoToDetail("XYZ","Manager",Fields!SalesmanId.Value,Code.ShowParameterValues(Parameters!as_accttype),Code.ShowParameterValues(Parameters!as_city)) & ",'_Blank','Toolbar=no resizable=yes'))"
In the above example the windows.open calls a function called GoToDetail which is defined in the Code Section of Report Properties. In the GotoDetail function, one would build the URL String to access the detail report. The URL construct would like:
URL = "'" & ReportPath & ReportName & "&rs:Command=Render&rs:Format=" & RenderFormat & "&rc:Parameters=false" & as_Type & as_Section & as_accttype & as_city & "'"
The URL String returned from the function. When the user runs report, under the SalesmanID column the icon would change to pointing finger. When the user clicks on the SalesmanID value it would open the Detail Report. In case one has dual monitors the master and the detail report can be compared side by side.
Thursday, December 10, 2009
PowerShell
http://www.powershellpro.com/, the lessons are very well laid out with lots of examples:
Here is a sample of how a Powershell script will look like: For example, if the need is to delete files which are more than 3 days old:
$Now = Get-Date
$Days = "3"
$TargetFolder = "Your Path Where the Files are Located"
set-location $TargetFolder
$ModifiedDate = $Now.AddDays(-$days)
$Files = get-childitem $TargetFolderWhere{$_.LastWriteTime -le "$ModifiedDate"}
If($Files -eq $Null)
{exit}
Else
{
ForEach($File in $Files)
{Remove-Item $File}
}
Tuesday, December 8, 2009
PowerPivot
http://www.powerpivot.com/
I decided to check out the virtual lab of powerpivot. It was a nice tutorial lab experience to begin with. The lab takes you through how to launch the powerpivot window and connect to a sql server database. Then there is a series of steps in the lab manual which appears on the side. This instructs the user as to how to go about creating a Pivot table report along with a chart. Once i went through these steps it became evident that it is going to give the Power Users/Analysts of an organisation a self service BI component. The users can use Powerpivot to build out very useful Pivot tables/charts, it also gives the ability the user to refresh the reports/charts real time. The one thing i noticed as i was going through Powerpivot was that the interface could take some time to get used to along with plethora of other options. I guess main deal with the user interface is the office 2010 layout along with the placement of options. The lab also takes the user through on how to create a powerpivot report using data from the web. I guess with this way of extracting data from the web, it leads the way to accessing unstructured data present on the web. In the final analysis based on the initial looks Powerpivot is a powerful tool for doing BI for power users. It will be interesting to see how Powerpivot coexists with sql server analysis services in terms of how the lines will be drawn between the two tools in terms of usage.
Monday, December 7, 2009
SSRS 2008 - Tablix Control
Wednesday, November 18, 2009
SSIS - Sequence Container
Wednesday, November 4, 2009
SSAS 2008 - Member Properties
Thursday, October 22, 2009
Source Control...TFS
Monday, October 12, 2009
ReportViewer
They are LocalReport,ProcessingMode,ServerReport,(DisplayName,HistoryId,ReportPath,ReportServerUrl,TimeOut)
For the reports which are deployed on a reportserver, set the processingmode to Remote, set the reportpath to the path on the server, set the reportserverurl to the webserver having the reports. Once this is set, run the application, the report which is deployed on the server will appear on the application. The user will now be able to access the SSRS reports in the application.
Tuesday, September 29, 2009
SSRS-Keep Together
Tuesday, September 15, 2009
SSRS 2008 - Export Report
Monday, August 31, 2009
Report Builder 3.0
http://msdn.microsoft.com/en-us/library/ee240845(SQL.105).aspx
Friday, August 21, 2009
SQL Server Profiler-SSAS
When looking at the results generated from these events, it gives a better idea of what happens beneath the cube in terms of which dimensions are being queried. Based on the findings this aspect of sql profiler adds an another useful aspect to the already existing important features.
Tuesday, August 18, 2009
SSIS-Package Property
The number was changed to 20 on the property and the package execution time was better than prior to changing the value for this property. There is a definition of the MaxConcurrentExecutables at MSDN.
Monday, August 10, 2009
Generating Reports Through WebService...
Sample Code:
ReportExecutionService rs = new ReportExecutionService();
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
rs.Url = "http://myserver/reportserver/reportexecution2005.asmx";
The link to the msdn article:
http://msdn.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsexecutionservice2005.reportexecutionservice.render.aspx
Tuesday, July 28, 2009
SSRS 2008...
One of the neat features available in SSRS 2008 is the adjacent Group feature in the tablix control.
This provides a great of flexibility in grouping, it allows grouping of the data in 2 different ways in the same control. One of the things I had to do was represent 2 parent child relationships in the same report, for this the adjacent group feature was very handy.
Friday, July 17, 2009
Data Mining...
Friday, July 10, 2009
Data Compression-SQL Server 2008
When you choose the Manage Compression Option, the Data Compression wizard comes up.
In this under the Compression Type dropdown there are the Page and Row Compression. When any one of these is chosen one can choose the calculate option and figure out the space savings as listed under the Current Space and Requested Compressed Space. In my Scenario i achieved more savings using Page Compression.
Tuesday, July 7, 2009
Enterprise Database Management Report...
Microsoft: The most aggressive DBMS vendor with a strong road map. Microsoft has done reasonably well in the database business but over the past three years has shown increasing focus and commitment to going after the enterprise market. SQL Server 2005 and, more recently, SQL Server 2008 have enabled Microsoft to take market share in moderately sized to large enterprises, delivering good performance, scalability, security, and availability functionality. Five years ago, hardly any enterprises ran multiterabyte databases with SQL Server to support critical applications. Today, hundreds of enterprises are running 10-terabyte and larger transactional SQL Server databases.
For a complete read on the report: Use the link mentioned below
http://www.microsoft.com/presspass/itanalyst/docs/06-30-09EnterpriseDatabaseManagementSystems.PDF
Tuesday, June 30, 2009
SSMS-Table Changes
Under Table Options, Uncheck the box which says "Prevent Saving Changes that require table re-creation" and click the OK button.
Once this is done, this should allow table changes to be performed from SSMS, this is a new feature in SQL Server 2008.
Thursday, June 25, 2009
SQL Server 2008-Execution Plan
Once the query is executed, the execution plan can be see in the results window, one of the neat features in 2008 is that it points out where indexes are missing and gives recommendation on the kind of indexes that need to be created on a table in order to improve query performance.
Wednesday, June 17, 2009
SQL Server 2008-Change Data Capture
Use DatabaseName
GO
EXECUTE sys.sp_cdc_enable_db;
GO
Once this is done, One can selectively enable Change Data Capture on tables that need to be audited, this can be done by:
EXEC sys.sp_cdc_enable_table
@source_schema = N'Test'
,@source_name = N'Test_Table'
,@role_name = N'dbo'
,@capture_instance = N'tbl_audit_Test_Table'
,@captured_column_list = N'Col1Primarykey,Col2,Col3,Col4
,@filegroup_name = N'PRIMARY';
GO
One of the requirement here is that there needs to be a Primary key defined on the Source Table. Here the Test_table has Col1 has the Primary key.
This would enable CDC on the table called Test_table, the audit data would be captured in the database object called tbl_audit_test_table_CT(_CT is added to the name given in the capture instance by the system) , in order to query the data in this, the Select statement would be
SELECT Col1,Col2,Col3,Col4 FROM cdc.tbl_test_table_CT.
There are supporting functions which can be used to access data from the captured instance. MSDN and Books line have lot of information regarding these.
In order to Disable CDC on a table run this command:
EXECUTE sys.sp_cdc_disable_table
@source_schema =N'Test',
@source_name = N'Test_Table',
@capture_instance = N'tbl_audit_Test_Table';
To disable CDC on the Database, run the following command:
USE DatabaseName
GO
EXECUTE sys.sp_cdc_disable_db;
GO
Friday, May 29, 2009
SQL Server 2008-32 Bit Runtime...
Wednesday, May 20, 2009
New Features in SQL Server 2008...
This will launch the Script Wizard, Choose Next, In the next screen choose the database, then choose the Choose Script Options. In this there is a option for Script for SQL Server Version, in this choose SQL Server 2005. This will generate the scripts compatible with SQL Server 2005. This would enable to create all the objects required for a SQL Server 2005 database.
There is another neat feature in this screen which the Script Data option, in case the sql server 2005 database needs to populated with Data, this can be set to true. This would enable to load a sql server 2005 database. These features came in handy when i had to go back to a SQL Server 2005 database.
Wednesday, May 13, 2009
Upgrade to SQL Server 2008...
When I opened up BIDS(Visual Stusio 2008) and selected my SSIS 2005 Solution, the upgrade wizard popped up.
When I clicked the Next Button, it came up with the list of packages which needed to migrated. The window had three columns Existing Package names, upgrade package name and passowrd. In case the upgrade package name needs to be changed it can be changed here.
In the next screen is where there is a couple of important options. One of the things that has changed between ssis 2005 ans ssis 2008 is the connection strings to use new provider names. There is a checkbox which provides the option to do the conversion by the wizard itself. Having this box checked is highly recommended, this would make the upgrade process a lot more easier. There is a option to validate pacakges after upgrade and one for backing up original packages.
In the next screen one gets a summary of what needs to be done, once you hit finish the upgrade process starts and provided warning messages/errors once it is complete.
One thing i noticed after the upgrade was if i were using configuration files from SSIS 2005 in SSIS 2008, in some cases i got a XML badly formed error when i executed the package in SSIS 2008. In such cases i recreated the XML config file in SSIS 2008 environment.
Monday, May 4, 2009
SSIS 2008...
XML file Configuration
Parent Package Variable
Environment Variable
Registry Entry
SQL Server
The options I choose were XML configuration and the SQL Server method. In the XML configuration option, In the Configuration wizard, Once has to choose the location for the XML File, Once that is done in the next screen, the properties for the different connection objects can be saved. This would typically contain the connection strings for different databases. In the final screen one can see all the different properties that are going to save in the XML file. Once this is complete the XML file will contain the important properties needed for the package. When the package is executed it uses the XML file to set the different connections. While moving across environments one has to create am XML file for QA and Production with the connection strings modified accordingly.
There are various other blogs across the web which describe the SQL Server method of configuring, In this case the configurations are stored in a table within a sql server database.
Thursday, April 30, 2009
SSRS 2008/Visual Studio 2008...
Wednesday, April 29, 2009
Hoshin
Focus on a shared goal
Communicate that goal to all leaders
Involve all leaders in planning to achieve the goal
Hold participants accountable for achieving their part of the plan
In Japanese, hoshin means shining metal, compass, or pointing the direction, this technique has/is being used by companies like Toyota,Hewlett Packard and Bank of America to name a few.
Business Intelligence...
There is an other important aspect of Business Intelligence which are Reports,DashBoards,Scorecards. These provide a Business a visual outlook of Key Performance Indicators,Trends and Projections which in turn a gives a good birds eye view of overall Busniess Performance. To summarise, Business Intelligence when utilised in a proper manner will provide very good benefits and can be a Value Add for a Business/Organisation.
iPhone Vs gPhone
http://apple20.blogs.fortune.cnn.com/2008/09/24/g1-vs-iphone-the-tale-of-the-tape/
Hopefully the above link will give a headstart for folks wanting to buy one these 2 devices/phones.
Blackberry...
The Storm model was the first blackberry model to bring in touch screen to the Blackberry world keeping in touch with competition from the iphone.
For more detailed reviews on different Blackberry models, please refer to this link.
http://www.mobiletechreview.com/BlackBerry-Reviews.htm
Smokey Mountains...
Staying in log cabins is a great way to save money on accomodation for large groups of people. For example if 2 or more families are planning a trip, log cabins can be a great way to save. When I travelled we had a total of 8 people (2 families) stay for 4 nights. The total expense was 260 dollars which worked out to around 65 dollars a night. These cabins have fully furnished kitchen which means money can be saved on food expenses. During vacations food expenses tend to be very high, when planned in advance, one would take certain food items which could be cooked. The smokey mountain, pigeon forge area has a large amount of log cabins, the place where we stayed was at:
http://www.blackbearlodging.com/ (BlackBear Lodging,Pigeon Forge,TN)
Report Builder 2.0
http://www.microsoft.com/downloads/details.aspx?familyid=9f783224-9871-4eea-b1d5-f3140a253db6&displaylang=en