Wednesday, December 29, 2010

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

Wednesday, December 15, 2010

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.

Friday, December 3, 2010

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

Thursday, December 2, 2010

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:

Wednesday, November 24, 2010

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

Tuesday, November 23, 2010

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,

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

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!

Monday, November 22, 2010

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

Friday, November 19, 2010

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

Thursday, November 18, 2010

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