Truncate Within A Transaction

In the previous post, we have seen, what is the difference between in “Truncate vs. Delete”?


Actually if we use truncate then we can’t rollback the data, but the question is … What will happen if we use truncate within a transaction, Whether we can rollback or not?.


The following example reveals the answer for our question.


I created a table and inserted a rows, as shown below

Then I executed the below Query:

It Returns:

Conclusion:

Truncated Data can be roll backed if it is executed within a transaction

Truncate Vs Delete

Truncate	Delete
Truncate Table <Table Name>	Delete from <Table Name> Where <Condition>
Cannot use Where Condition	It is possible
It Removes all the data	It deletes specified data if where condition exists.
Faster in performance wise, because it doesn't keep any logs	Slower than truncate because, it keeps logs
Rollback is not possible	Rollback is possible

PL/SQL vs. T-SQL

PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation's procedural extension language for SQL and the Oracle relational database.

 

Transact-SQL (T-SQL) is Microsoft's and Sybase's proprietary extension to SQL. Transact-SQL is central to using SQL Server. All applications that communicate with an instance of SQL Server do so by sending Transact-SQL statements to the server, regardless of the user interface of the application.

SQL Server ERROR Messages Severity Level

In My Previous Post, I explained how to list all the errors from Database. Every error is mapped with severity code, This article Explains what is severity code.

When you or system executes any SQL Scripts or related tasks, SQL Server DB Engine raises the error as well as it indicates the severity of error.

 

Severity code helps to understand the type of error. 

The following table lists the severity code and its meaning:

 

Severity Code	Description
0-10	It is Informational messages not actual error, actually 0 means No Error, No Information, before invoke the Programs, DB Engine converts to 0 then start performing operations
11-16	Error can be corrected by user, this may be syntax error
11	Object Doesn't Exists
12	Don't allow to do lock on Any Object
13	Transaction Dead Lock Errors
14	Security related Error, access denied
15	Syntax Error
16	General Error like invalid arguments, string value not quoted properly etc.,
17-19	Software Error, not corrected by User
17	Out of memory exception, disk usage, lock, write protected, no access to resource etc.,
18	DB Engine related error
19	Non-Configurable limit exceeded with DB Engine
19-25	Note: 19-25 error will be updated in SQL Error Log
20-25	Fatal Error occurred based on single or batch process running currently
20	Problem with current Task only
21	problem affects all other process
22	Table or Index Damaged by software or hardware. It occurs rarely. Run DBCC CHECKDB to determine error
23	Problem with integrity of Database, corrupted
24	Need to restore database, database may be corrupted, may be hardware issue
25	System Error

 

You can create or define new error message using system procedure sp_addmessage, when you add, you can use any one of the severity type (1-25) listed above

List SQL Error Codes

The following Script helps to list All Error Codes and description of SQL Server

Run this Following Queries in Master Database
Code:
Use Master

SELECT * FROM sysmessages

Results:

Axes in MDX Query

An Axis is a group, or collection of members from one or more dimension, which is constructed as Tuples. The main purpose of axis is used extract or filters the specific part of the cube values.
Characteristics of Axis:

• Axis number is zero based.
• zero (0) for the x-axis, 1 for the y-axis, 2 for the z-axis
• MDX supports up to 128 axes.
• First 5 axes have names like COLUMNS, ROWS, PAGES, SECTIONS, and CHAPTERS, remaining are accessed thru axis number only like 5, 6, 7, etc.,
• We access specify the access in MDX Query in following different ways:
• on Axis(0), on Axis(1) , etc.,
• on 0, on 1, etc.,
• on columns, on rows, on pages, on sections, on chapters, on 5, on 6 etc.,
• we can’t skip any axis in MDX Query, we no need maintain the sequential axis, but sequence should exist in MDX Query

For Ex:
This Query contains one columns and Rows

SELECT
{[Measures].[Internet Sales Amount]} ON COLUMNS,
{[Date].[Calendar].MEMBERS} ON ROWS
FROM [Adventure Works]

The same query will work, even if the different sequence,

SELECT
{[Date].[Calendar].MEMBERS} ON ROWS,
{[Measures].[Internet Sales Amount]} ON COLUMNS
FROM [Adventure Works]

But it won’t work, if we miss or skip any sequence like, here axis 1 is not used, It is Invalid

SELECT
{[Date].[Calendar].MEMBERS} ON 0,
{[Measures].[Internet Sales Amount]} ON 2
FROM [Adventure Works]

But You can write SELECT clause without using any Axes, in this case, all the dimensions available in the cube will act as slicer, MDX fetches the first cell value

MDX: Tuple Vs Set

Tuple:

• It is a collection of members from different Dimension
• It Allows only one member from each dimension
• It is a Basic Unit to forming an axis
• As a rule, tuples are enclosed in parenthesis

Set:

• One or More tuples from same dimension,
• All tuples enclosed in a set with same order of dimensions

Set Can contains tuples and tuple can consist of dimensions.

SSRS: Report Builder

Report Builder is a tool provided by Microsoft, which can help the user the design the report their own. It supports to build a table, matrix and chart reports. User can design the report with the help of predefined data model. User can interactively explore the data within report model with the help of Report Builder Tool

You can download this tool from,

Report Builder 2.0

Report Builder 3.0

SSRS: Role Based Security and Types

Reporting Services uses a role-based security model to control access to reports, folders, and other items that are managed by a report server.

The followings Types of role can be defined by using report manager for a SSRS Items.

Role Name	Description
Browser	May view folders, reports and subscribe to reports.
Content Manager	May publish reports and linked reports; manage folders, reports and resources in a user's My Reports folder.
Publisher	May publish reports and linked reports to the Report Server.
Report Builder	May view report definitions.

SSRS: Permissions granted to user are insufficient

Problem:
I have installed SQL Server 2008 in my laptop, when I tried to browse the Report Server URL, http://localhost/Reportserver/, system throws following Error

The permissions granted to user 'Domain\User' are insufficient for performing this operation. (rsAccessDenied) Get Online Help

Solution:
1. Execute Internet Explore thru “Run as Administrator” Mode
2. Then browse this URL, it will works fine

Note: It works only on Internet Explorer, it won’t work at Google Chrome and Mozilla Firefox, even if you try with “Run as Administrator” Mode

Reporting Services Login box appears:

Today one of friend told me that he is facing the problem like "Report Services Login Screen Appears" while trying to deploy the report. Even it is not accepting after enter the proper credential.

I found the error that "TargetServerURL" path is invalid. It contains only http://localhost/

It is working after changed as http://localhost/ReportServer

Now if we deploy the report, it is working fine.

Sometimes problem may be,

1.http://localhost:81/ReportServer – Port Number

2.http://localhost/reportserve$SQL2005 – Different Instance

3.http://localhost:81/reportserve$SQL2005 – Both

If the "TargetServerURL" is proper, then it works fine. If you are deploying the report on server from your system, if you enter the proper credential then it will work!

Enable/Disable - SSRS Export Types

SQL Server Reporting Services (SSRS) supports to export different types like XML, CSV, HTML, PDF, etc., when you view the report.  This export option is available at report viewer Export drop-down list.

We can control this lists thru report services server configuration file which is available at "C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\ReportServer\rsreportserver.config".

In This File, In <Render /> tag elements, add attribute Visible="false" and save the file. Now if you check, it won't be available at Export drop-down list

Example:

<Render>

<Extension Name="XML"

Type="Microsoft.ReportingServices.Rendering.XmlDataRenderer.XmlDataReport,Microsoft.ReportingServices.XmlRendering" Visible="false" />

</Render>

Hope it Help!

ReportServer Vs Reports

http://localhost/ReportServer	http://localhost/Reports
It is a Web Service	It is a Web Application
Only you can access the report	Here you can view, manage the report, creating roles, enabling subscription, schedule the report, etc., we can call it as a Report Manager

Pupose of ampersand (&) character in MDX

In MDX Query, A member can be referenced by either its member name or by its member key


The ampersand (&) character is used in MDX to differentiate a member key from a member name, as shown in the following example:


Reference the member by its member Key:
[Time].[2nd half].&[Q4]


Reference the member by its member name:
[Time].[2nd half].[4th quarter]

What are the Components in Reporting Services (SSRS)?

The following Components are available in SSRS:

1. Report Builder
2. Report Designer
3. Report Manager
4. Model Designer
5. Report Server

Simple SSRS 2008 Architecture

Microsoft Visual Studio 2010 Express Prerequisites

The followings are necessary, if you want to install Microsoft Visual Studio 2010 Express in your system


System Requirements
1.1 Supported Architectures

• x86
• x64 (WOW)

1.2 Supported Operating Systems

• Windows XP (x86) with Service Pack 3 - all editions except Starter Edition
• Windows Vista (x86 & x64) with Service Pack 2 - all editions except Starter Edition
• Windows 7 (x86 and x64)
• Windows Server 2003 (x86 & x64) with Service Pack 2 - all editions
• Users must install MSXML6 if it is not already present.
• Windows Server 2003 R2 (x86 and x64) - all editions
• Windows Server 2008 (x86 and x64) with Service Pack 2 - all editions
• Windows Server 2008 R2 (x64) - all editions

1.3 Hardware Requirements

• 1.6 GHz or faster processor
• 1024 MB RAM (1.5 GB if running on a virtual machine)
• 3 GB of available hard-disk space
• 5400 RPM hard-disk drive
• DirectX 9-capable video card running at 1024 x 768 or higher display resolution
• DVD-ROM drive

Error: Visual Studio setup cannot run in compatibility mode

Today I tried to installing .. Windows Phone Developer Tools RTW in my system. i faced the following Error:

Problem:
---------------------------
setup.exe
---------------------------
Visual Studio setup cannot run in compatibility mode. For more information see the 'Installing' section in the
Visual Studio Readme at "http://go.microsoft.com/fwlink/?LinkId=143397".
--------------------------
OK
---------------------------


Solution:
After Google the solution for this problem, i got the followings steps..


1. Right click on setup.exe and click Properties
2. Switch to the Compatibility tab
3. Untick the "Run this program in compatibility mode for" checkbox
4. If you have a button at the bottom of the dialog entitled "Show settings for all users" click this and Untick the "Run this program in compatibility mode for" checkbox
5. Click OK until you have dismissed all dialog windows
6. Go back to the uninstall entry and uninstall as normal.

Wow... It works fine.. :)
Hope It Helps

101 LINQ Samples

Microsoft Provides LINQ Samples for developers, it helps us to start LINQ Easily.

• Restriction Operators
  
• Projection Operators
  
• Partitioning Operators
  
• Ordering Operators
  
• Grouping Operators
  
• Set Operators
  
• Conversion Operators
  
• Element Operators
  
• Generation Operators
  
• Quantifiers
  
• Aggregate Operators
  
• Miscellaneous Operators
  
• Custom Sequence Operators
  
• Query Execution
  
• GetProductList code
  
• Join Operators

Hope it Helps