RSS

Category Archives: Tutorial

Recover Backed-up query files in SQL Server Management Studio

Hi guys,

I just want to share quickly very handy tip regarding your SQL query sessions. When you open SQL queries in SSMS, you generally go on and work in newly opened pane/window but sometimes dont save that work.

SSMS by default saves them in the event of pre-mature closure or crash of SSMS or PC restart etc. You can find your SQL queries from following lcoation.

C:\Users\<UserName>\Documents\SQL Server Management Studio\Backup Files\Solution1

FYI, I have found my queries here:

SQL_Queries

P.S. I am using Windows Server 2008 Operating System. This path could vary based on the Operating System. You can figure it out for sure :)

I hope you find this interesting and helpful.

Thanks, Khilit

 

Tags: , ,

Retrieving id of inserted record: What to use @@IDENTITY or SCOPE_IDENTITY() or OUTPUT

Hi guys,
This seems very basic information but need to know properly.

Issue:
I need to find out or have the id of the records which I inserted in table.

Analysis:
This requirement seems very simple but there could be various cases where you would not get the correct id just by using @@IDENTITY function.

Think of the case where you are inserting a record in table A and there is a trigger which fires as soon as there is a insert in table A which insert a record in table B. so in such case @@IDENTITY wont give you the correct id/pointer of inserted record.

So to deal with this issue you can use SCOPE_IDENTITY(). You will always get the last value you have inserted in table. However, it is noticed that sometimes in some scenarios SCOPE_IDENTITY() does not promise the correctness of results. May be due to parallelism or multiple inserts or bulk inserts in table(s). E.g. when you do … Insert Into Select <columns> from Table

Well, Microsoft recommends that “You do not use either of these functions in your queries when parallel plans are involved as they are not always reliable. Instead use the OUTPUT clause of INSERT statement to retrieve the identity value“.

Check out MS articles:
http://support.microsoft.com/default.aspx?scid=kb;en-US;2019779
http://msdn.microsoft.com/en-us/library/ms177564.aspx

I hope this will help you somewhere.
Thanks, Khilit

 

Tags: , , , , , ,

The Story of Precision, Scale, and Length in SQL Server

Hi guys,

Did you ever faced issues with BIG mathematical figures – related to either overflow or any  other arithmetic errors. This may be applicable to SQL arithmetic operations or arithmetic  operations you do with SQL table in SSIS etc.

Well, check 1 more time, if you have any issues in your database design related to column  data type which stores the BIG numbers.

I ran into this issue couple of times, and reason behind this issue was same all the time. So  decided to do a quick post. And that reason is “Improper or insufficient precision, scale and length defined for storing Decimals and Numeric values in table“.

I wanted to describe this with an example but there is already a very nice explanation by MSDN  on this topic. Check out: http://msdn.microsoft.com/en-us/library/ms190476.aspx

You will notice in this article that, how any arithmetic operation will affect size and space  allocation of the resultant value during mathematical operation. You should use following shown table for defining and designing tables in such a way that can  handle BIG mathematical figures during operations.

From MSDN, for your quick view:

Operation

Result precision

Result scale *

e1 + e2 max(s1, s2) + max(p1-s1, p2-s2) + 1 max(s1, s2)
e1 – e2 max(s1, s2) + max(p1-s1, p2-s2) + 1 max(s1, s2)
e1 * e2 p1 + p2 + 1 s1 + s2
e1 / e2 p1 – s1 + s2 + max(6, s1 + p2 + 1) max(6, s1 + p2 + 1)
e1 { UNION | EXCEPT | INTERSECT } e2 max(s1, s2) + max(p1-s1, p2-s2) max(s1, s2)
e1 % e2 min(p1-s1, p2 -s2) + max( s1,s2 ) max(s1, s2)

* The result precision and scale have an absolute maximum of 38. When a result precision is greater than 38, the corresponding scale is reduced to prevent the integral part of a result from being truncated.

Important Note: Don’t forget to check for your SQL Server Version.

I hope you will find this informative.
Thanks, Khilit

 

Tags: , , , , ,

Tracking Down Ghost Characters: WHERE Clause Does Not Work Properly In T-SQL Query

Hi guys,
I would like to share an experience with you when I was working on 1 SSIS related issue.

Scenario was:
I have to exclude some records from Table A by matching some keys of Table B.

 Table A

Year Month CustomerCode <Extra Columns>
2012 JAN CX1000  
2012 JAN CX2000  
2012 JAN CD3000  

 

 Table B

Year Month RecCustomerCode <Extra Columns>
2012 JAN CX1000  
2012 JAN CX7000  
2012 JAN CD8000  

 

 What is expected was – Table A’s following records:

Year Month CustomerCode <Extra Columns>
2012 JAN CX2000  
2012 JAN CD3000  

  Read the rest of this entry »

 

Tags: , , , , , , , , ,

What is OLAP Database ? What is FASMI?

Hi guys,
i was reading an article and came across this term “FASMI“. i have done a little bit of RnD to find out its head & tail. This is what it means:

FASMI is an acronym of “Fast Analysis of Shared Multidimensional Information“.

A database when called “OLAP Database” if it satisfies following FASMI rules.

1. Fast Analysis (FA):
Data retrieval should be less than 5 seconds. Should support any business scenarios for any type of analysis. The structure should support such flexibility that new calculations can be easily and quickly defined.

Read the rest of this entry »

 

Tags: , , , , , ,

E-Book Gallery for Microsoft Technologies

Hi guys,
good morning !!!

here is the list of cool FREE E-Books from Microsoft Press.
Read the rest of this entry »

 

Tags: , , , , , , , , , , , , ,

Features Supported by the Editions of SQL Server 2012

Hi all,

I ran into a nice article which has a 1 consolidated list of all the features of SQL Server 2012 Editions. You can check the details for your infrastructure and solution planning whether what you wanted/needed is supported in SQL Server 2012 or not. And if supported then which edition?

Read the rest of this entry »

 

Tags: ,

SSRS Chart – Displaying % sign on Y axis + Displaying % sign on Data Labels

Hi guys,
Today i would like to show you the little trick for your SSRS graphs.

Issue:
I am displaying values (percentage values) in my bar charts as “Data Labels”. But the “Y” axis of my bar char still displays value without the “%” sign.

What I need:
The bar chart should display the “%” value in Data Labels as well as on my Y axis.

Simple try:
When you format the Data Label to apply “%” sign by making it of type “Percentage” in “Series Label Properties” -> “Number” -> “Percentage”. It actually multiply the value by 100 and put a % sign at the end. Woh … but the value i am displaying on my Data Labels are already percentage. I just need to display the % sign at the end.

Solution:
Let’s trick the SSRS to display that % sign on you Data Labels as well as on your Y axis.

Read the rest of this entry »

 

Tags: , , , , ,

View is not showing newly added column in table

Hi all,
This is for the people who still use “Select * From Table” in their SQL Views. I don’t know what is the reason that you are using this way to create your views, but you could be the victim of this scenario (see… what title says).

Let me show you how you can solve this issue… in-case you come across one.

When this happens??
You make changes in table which is the source of your view. Changes like modifying existing column definition, adding new columns or removing existing columns from table could cause this issue.

Where can I see this issue??
Check the meta-data of your table and view for comparison. (Tip: You can either use traditional method using SYS.OBJECTS joins SYS.COLUMNS or you can check using INFORMATION_SCHEMA.COLUMNS)

What is the solution?
There are different ways to circumvent this issue.

Read the rest of this entry »

 

Tags: , , , , ,

SSRS Report is asking for Username and Password while executing

Hi all,
I want to bring to your focus a very minor detail which could eat-up your time when it happens.

This is the case when your SSRS report is deployed on ReportServer and you are accessing thru any application or interface e.g. SharePoint. Your SSRS report is using Integrated Security and hence no need to provide Username and Password if you have access to the Report. And everything works fine up to this point.

After some days, if you notice that your SSRS Report is not working as expected and it has started asking for Username and Password while executing it. What could be the reason?

Read the rest of this entry »

 

Tags: , , , , , , ,