SET NO COUNT OFF in sql stored procedures

SET NO COUNT OFF:



In most of the SQL server procedures, we used to see this command. In the top of the procedure, we will see SET NO COUNT OFF and in the end, they wll turn on the same. FUNNY RIGHT..... WHY THEY ARE DOING THIS FOR EACH AND EVERY PROCEDURE. GO AND DO IT IN THE DATABASE OR SERVER LEVEL....WHY WE ARE NOT DOING IT? LETS SEE.



CREATE PROCEDURE VENKATPROC
AS
BEGIN
SET NOCOUNT OFF
--- YOUR SQL STATEMENTS.
SET NOCOUNT ON
END




Funn
Yes, its correct. From performance point of view. Set no count off is essential for the stored procedures and functions.

Considering, you have an insert statement which inserts 3 records. SQL server will insert three records and it will update to the client as 3 records got updated. In some cases, this data is not needed for the client. Unnecessarily, this data will be pumped out from the server to client.
This can be avoided by put set no count off in the start of the procedure and it will be removed at the end of the procedure.

Thanks and Regards,
Venkatesan Prabu .J

Logged in user details in sql server

Am planning to write an article under the topic "logged in user details" in sql server.
There are scenarios, where we need to find the logged in user. In that case, you can use the below sql builtin functions to identify the logged in users.

select host_name()

Above command is used to find the logged in user in a network. Here Venkat is the host name. Machine connected to the sql server in the network.

If you want to find the user accessing the server, you can use the below command,

select suser_sname()
In our example, suba is the user "logged in" into the server.

An alternative command to fetch the logged in user.

select system_user

There are scenarios where you want to find all the logged in user. Considering am having an issue like database deadlock or my resource utilisation is 100% and I need to check which resource is utilising more CPU. In that case, sp_who command will be useful to identify the users details.

sp_who2 is an alternative command of sp_who. sp_who2 will provide you more options when compared to sp_who command.

Thanks and Regards,

Venkatesan Prabu .J

Datediff function in sql server

Datediff function in SQL Server:

Datediff function is used to differentiate two different dates/month/year.
The syntax is,

Datediff(Month or day or year, Date1, Date2)

If needed, Date1 can be current date to get the desired values. Let's see some examples,

declare @val datetime
declare @val2 int
declare @val3 datetime
declare @val4 int
set @val='11/3/2009'
select @val2 = (datediff(month ,@val,getdate() ))-- + (@val1 *12) )
print @val2 -- The ouptut is -1
set @val3='11/3/2007'
select @val4= (datediff(month ,@val3,getdate() ))-- + (@val1 *12) )
print @val4 -- The ouptut is 23

Let's try with day difference,


declare @val datetime
declare @val2 int
declare @val3 datetime
declare @val4 int
set @val='11/3/2009'
select @val2 = (datediff(day ,@val,getdate() ))-- + (@val1 *12) )
print @val2 -- The ouptut is -4
set @val3='11/3/2007'
select @val4= (datediff(day ,@val3,getdate() ))-- + (@val1 *12) )
print @val4 -- The ouptut is 727

Thanks and Regards,
Venkatesan Prabu .J

Numeric datatype in SQL Server

Numeric datatype:

Numeric datatype is equivalent to the decimal datatype. Decimal (ANSI supported) is recent version of numeric datatype.
Numeric datatype will have be declared with a scale and precision.

Syntax:
variableName numeric(scale, precision)

Let's see some samples,

create table venkat (id numeric(1,3))

On executing the above command, am getting the below error.
Msg 183, Level 15, State 1, Line 1
The scale (3) for column 'id' must be within the range 0 to 1.

Reason for this SQL error:
The scale value should be bigger than the precision value. If we declare numeric (5,2). Then, the value will be 3 digits with 2 decimal points. (Example 456.45, 453.23)

So, the number followed with the numeric keyword should be bigger.

Lets try this,

create table venkat (id numeric(5,3))

Our table is created.
Happy Learning!!!

Thanks and Regards,
Venkatesan Prabu .J

Datatype conversion in SQL server (Float to Int)

Let's see an interesting article on data type conversion,
select 8.5

-- The output is 8.5

Using convert operator to convert a float value into an integer:
select convert(int,8.9)

The above statement will try to convert the value into a possible integer. An equivalent of floor function.

Floor and ceiling functions in SQL Server:
Floor function is used to get the lowest possible integer value from the given float value

select floor(8.9)
-- The output will be 8

Ceiling function is used to fetch the possible maximum value(next possible integer value) from the given float value.

select ceiling (8.9)
-- The output will be 9

select ceiling (8.4)
-- The output will be 9

Using Cast operator in the conversion process:

select cast (8.9 as int)

The above statement will try to cast the value into a possible integer. An equivalent to floor function.

Thanks and Regards,

Venkatesan Prabu .J

Sys.Index table in sql server

All about Sys. Indexes table:

Sys.Indexes table will list down all the indexes available in the database. In detail, it will list down the clustered indexes, Non-clustered indexes and heap.

Clustered indexes will restructure the entire data inside the table where as non clustered index will create a pointed inside the table.

On executing the below query,

select * from sys.indexes

The table will hold the name of the index/type of the index/fill factor/ its working nature /hold primary key/unique key etc..,

To find the disabled indexes:

select * from sys.indexes where is_disabled = 1

To find, whether the index holds primary key (Index created due to the primary key on the table)
select * from sys.indexes where is_primary_key=1

To find, whether the index holds unique key on the column:
select * from sys.indexes where is_unique_constraint=1

Thanks and Regards,

Venkatesan Prabu .J

Special symbols in the column names in sql server table

Usage of special characters and keywords as the column name:

It's obvious that, we used to use keywords or special characters in the column name. SQL Server won't permit us to use the same directly. In that case, they will provide an option (Brackets [ ]) to use it.

Let see our example,

create table venkattable(kg/m varchar(10))

Output:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '/'.


To resolve this issue, I have used brackets.

create table venkattable([kg/m] varchar(10))

Output:

Command(s) completed successfully.

Thanks and Regards,
Venkatesan Prabu .J

Identity usage across table in SQL Server

I have come up with an interesting topic of using IDENTITY option in SQL Server.

As you know, Identity option is used to create unique values in regular intervals. One of the forum person have asked an interview question like, "Is it possiblel to spawn the identity value across the tables. Since identity is table specific item. How can we acheieve it?"

Nice question right!!!! Let' see how to acheive it?

Option 1:
Create a primary key in the table 1 and refer this column in the other tables. Make the columns in other two tables as primary key.

Option 2:
Create a primary key in one table and refer the column in other two tables. Make the other two tabels column as unique non-clustered index column (or) unique clustered index column else add a unique constraints on the columns in the other table.

Thanks and Regards,
Venkatesan Prabu .J

Remote query timeout in SQL Server

Ocassionally, Application will throw Query timeout errors. Do we have analysed why its happening?

Remote query timeout is due to the following reasons,

1. Server performance is too slow due to large number of transactions. So, it couldn't allocate necessary resources for the current session.

2. Network issue.

3. May be a very long complex query which will take more time to execute.

In that case, we can set the option of remote query timout to infinite or we can increase the timings.

Cheers,
Venkatesan Prabu .J

Decode function in Oracle

Decode function in Oracle:
Decode is an oracle specific function which is equivalent to "IF else" function in SQL Server.

For example:

Am having a query in oracle
SELECT ID, decode(id, 1, 'Venkat', 2,'Arun', 'NoName')NAME FROM VenkatTable

The equivalent function in SQL Server is.

decode(id, 1, 'Venkat', 2,'Arun', 'NoName')NAME

->

IF ID = 1 THEN NAME ='Venkat'

ELSEIF ID=2 THEN NAME ='Arun'

ELSE NAME ='NoName'

Thanks and Regards,

Venkatesan Prabu .J

Script SQL Databases

Scripting SQL Server 2005 database:
This is one of an interesting topic which I like to cover it in my blog. How to script a SQL Server database?

Right click on the database -> Goto tasks -> Generate scripts

You will get a small window to generate SQL Server scripts.

On clicking next button, all the databses will be listed down.

In the next page, we will having an option to be taken while scripting your database.

On clicking next button, you will get the objects details. What are the objects to be considered in generating the script.

If we select "Stored procedure" in the above screen. We need to select the needed stored procedures.

If we have select the tables, then MS SQL will provide an option to select the tables from the list.

This is the final option which will tell you the format to store the scripts.

Whether we need to script it into a new window (or) file (or) in the clip board.

Click next-> followed by finish button will generate the scripts in SQL Server.

Below is the progress option to check the scripts generated.

Thanks and Regards,

Venkatesan Prabu .J

After update trigger in SQL Server

Trigger:
Triggers are automatic invocation of statements. It can be written based on the action (or) a response for the action (or) a preliminary step before the action.

Considering a scenario, I need to insert a record while updation happened in the table. In that case, we can write after update trigger (Below is an example). Similarly, we will be having after insert, for insert, for update triggers.

Below is a small code snippet to create a table + trigger,

create table venkat(id int, val varchar(20))
insert into venkat values(1,'Venkat1')
insert into venkat values(2,'Venkat2')
insert into venkat values(3,'Venkat3')
insert into venkat values(4,'Venkat4')
select * from venkat

create trigger upd_venkat on venkat
after updateas
begin
insert into venkat values(5,'Venkat5')
end

update venkat set id=6 where id=4

select * from venkat

Thanks and Regards,

Venkatesan Prabu .J

Finding text in the stored procedure

This article is one of an interesting article which will tell you a nice concept of searching a text inside the objects.

Scenario:
I want to fetch all the stored procedure which is using a specific table(Venkat_table). How to find it?
Below is the solution for the same,
-- Creating the table
drop table Venkat_Table
create table Venkat_Table(id int)
insert into Venkat_Table values(10)
insert into Venkat_Table values(20)
insert into Venkat_Table values(30)
select * from Venkat_Table

-- Creating the procedure
create procedure Venkat_proc
as
begin
select * from Venkat_Table
end

-- In SQL Server 2005, we will have sys.procedures which holds all the details of the procedure.-- Defintion of the the specific object will provide you the definition details of the object.

SELECT *
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%Venkat_Table%'


-- In SQL Server 2000, we will have syscomments and sysobject. syscomments will hold the definition of the object. sysobjects will list downt the objects. Joining syscomments and sysobjects will retrieve the necessary object names.

Below is the query to fetch the same,

SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%Venkat_Table%'


This will give the object definition inturn it's an alternative for the sp_help text option in SQL Server.

SELECT OBJECT_DEFINITION (OBJECT_ID(N'Venkat_proc'))

Thanks and Regards,
Venkatesan Prabu .J
Happy Learning!!!

Schema Names in Oracle database

Listing the schema names in Oracle database:

For retrieving the schema names from the oracle database, we need to use dba_objects table.

select * from dba_objects

The above query will retrieve all the objects from the table. Below are some of the high level information.
Owner: Schema name
Object Name: Name of the object
Object_ID : Unique ID generated by the oracle database.
Object_type : Type of the object whether its a table/index/view or synonym
Created : creation time
Valid : Whether this object is valid or not?

Below is the query used to fetch all the schemas located in the oracle database.

select owner,count(owner) cnt from dba_objects group by owner

It includes all the system related schemas. This can be removed by using not in operator. Schema names like Oracle, Common, system, xdb etc.., were related to Oracle system related schemas.

Thanks and Regards,

Venkatesan Prabu .J

Happy Learning!!!

Bulk Insert option in sql server

Bulk insert is a very nice option in sql server to load huge data from the external source system.
It needs the data should be delimeted by specific delimeter.

The syntax is,

BULK INSERT TABLENAME
FROM SOURCE FILE
WITH (OPTIONS)

Now, I will create a temporary table to insert the records into the table.
create table testing
(
empname varchar(20),
value1 varchar(10)
)

Am having a text file with comma seperated delimeter and /n for the next line.



Now, am going to insert the data into the temporary table. Below is the bulk insert
syntax to insert the records into the table.

BULK INSERT testing
FROM 'C:\VP Personal\aa.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

On selecting the table, we will get the records inserted into the table.

select * from testing

Thanks and Regards,

Venkatesan Prabu .J

SQL Server errors

While trying to use bulk insert option, I got the below error,

Msg 4861, Level 16, State 1, Line 1

Cannot bulk load because the file "\\ServerName\input\FileName.csv" could not be opened. Operating system error code 5(Access is denied.).

On digging the microsoft URL's I found the problem is due to the settings in sql server. We need to enable TCP/IP with local and remote connections option in Surface area configuration manager.

I have crossed this and next I got another peculiar error,
A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - No process is on the other

On looking into this issue, guessed the problem should be with the file. Some how, My sql doesn't have proper access to read that file. I took the file and placed it another location. Luckily, my problem is resolve and am able to do my bulk insert.

Any way, this is not a permanent and apt solution. For more proper solutions, please check the below URL.

http://blogs.msdn.com/sql_protocols/archive/2005/09/28/474698.aspx

Thanks and Regards,
Venkatesan Prabu . J

Tempdb is full - Free up the space

Hi All,

Am writing my article after a very long time. I have been caught up with some other works.



Ok, lets see some interesting facts about tempdb.

Background of Tempdb Database:

TempDB is one of the system database which is used to store the temporary tables or temporary data.



A table can be declared as temp table by adding '#' symbol in the front of the table name.



Temp table will hold a session value appended, For each user a temp table can be created with a session id appended to its name - Unique to identify the tables. Internally, SQL Server will take care of this.


Actions for Tempdb full problem:


Considering, My scenario - Tempdb is full. In this case,



1. Check the feasibility to restart the server. If there is no major impact, restart the server. So that, the database will be freed up and it will more room to get other new tables.

Some times, we are familiar with the below error message.


Description: The LOG FILE FOR DATABASE 'tempdb' IS FULL.

Back up the TRANSACTION LOG FOR the DATABASE TO freeup SOME LOG SPACE

select * from sys.database_files

The above query will get the results as below,

Now, since the log file is full and there is no room to extend further. In that case, we

can advise the database to point the file to another location. Below is the query to achieve the same.

The file "templog" has been modified in the system catalog. The new path will be used the next time the database is started.

Cheers,

Venkatesan Prabu .J

Dynamic SQL in sql server

Dynamic SQL in sql server:

Most of the cases, our requirements will be dynamic. Sometimes, the person will request for one column to be fetched from the database, may be two columns. The decisions will be taken at run time.

I used to face a requirement to fetch the data based on the login user. userA needs 5 columns and userB needs 2 columns.

I need to have a common code to satisfy both the requirement. Such kind of scenario can be handled using dynamic SQL.

Query will be generated at the run time and it will be executed to fetch the result. The dynamically created query will be executed by a special system stored procedure namely sp_executeSQL. Let's see a small code snippet on this.

-- Creating a table named venkatTable
drop table venkatTable
create table venkatTable(id int identity(1,1) primary key, nam varchar(100))
insert into venkatTable values('Venkatesan Prabu')
insert into venkatTable values('Subashini')
insert into venkatTable values('Jayakantham')
insert into venkatTable values('Arunachalam')
insert into venkatTable values('Santhi')
select * from venkattable

-- Am declaring three variables @val3 will be a query created.
-- It should be executed dynamically using sp_executesql

declare @val1 varchar(100)
declare @val2 varchar(100)
declare @val3 nvarchar(100)

set @val1 ='id'
set @val2='nam'

set @val3= 'select '+ @val1 + ','+ @val2 + ' from venkattable'
print @val3

exec sp_executesql @val3

Thanks and Regards,

Venkatesan Prabu .J

Inner Join in SQL Server

Inner join in SQL Server:

Usually, in a normalized database the values related to a particular column will be referenced in other table.

Considering, there is company with 1000 engineers. In storing the employees, instead of storing engineer as the designation for each employee. Normal forms advised
us to store the engineer value in a small table with id as reference. This id can be stored in the employee table.

So that, it will improve the storage.

Now, I got all the data segregated into different tables and I need to join it into a single result set.

In that case,we will opt join operations in SQL Server. It will enable you to consolidate the data and provide you in an expected format. There are various joins available in SQL Server.

Let's see inner join option in SQL Server. This will join the table mutually based on a common column. The syntax looks like,

table1 inner join table2 on table1.column=table2.column

Let's see some example to learn the join operation in sql server.

-- lets see a chatting messenger example,
-- Am creating three tables, 1 holds the name of the person and 2 holds the mapping between the friends.
--Considering am giving a friends request to another user

drop table venkatTable
create table venkatTable(id int identity(1,1) primary key, nam varchar(100))
insert into venkatTable values('Venkatesan Prabu')
insert into venkatTable values('Subashini')
insert into venkatTable values('Jayakantham')
insert into venkatTable values('Arunachalam')
insert into venkatTable values('Santhi')

drop table friendTable
create table friendTable(id int, friendid int, valueStatus int)
insert into friendTable values(1,2,0)
insert into friendTable values(1,3,1)
insert into friendTable values(2,4,0)
insert into friendTable values(2,3,2)

drop table status
create table status(id int, status varchar(100))
insert into status values(1,'Approved')
insert into status values(0,'pending')
insert into status values(2,'DisApproved')

Now, Lets join tableA, TableB and TableC directly :

select a.nam,b.id,b.friendid,c.status
from venkatTable a inner join friendTable b on a.id=b.id
inner join status c on b.valueStatus = c.id




Joining tableA and TableB on another combination:

select a.nam,b.id,b.friendid
from venkatTable a inner join friendTable b on b.friendid=a.id

Fetching the name, friendname and Status (By joining three tables) :
select distinct t.nam,t1.nam,t.status,t.id,t.friendid,t1.id ,t1.friendid from
(
select row_number() over(order by b.id,b.friendid) as r1, a.nam,b.id,b.friendid,c.status
from venkatTable a inner join friendTable b on a.id=b.id
inner join status c on b.valueStatus = c.id
)t
inner join (
select row_number() over(order by b.id,b.friendid) as r2,a.nam,b.id,b.friendid
from venkatTable a inner join friendTable b on b.friendid=a.id
)
t1 on t.r1=t1.r2
Here we have used row_number() function of SQL Server 2005. It's used to generate the row numbers in your result set.
Thanks and Regards,

Venkatesan Prabu .J

Reset identity column in SQL Server

Identity column in SQL Server:

Identity column is an easiest way to generate sequence unique number in a column of the table. Considering am having a column named "ID" with property as identity with starting value as 1 and it should generate values increased by 1. The syntax is,

id int identity(1,1) -- This column will generate the values 1,2,3,4,....etc...

Now, let's goto our typical problem statement reseeding the identity column.

Problem statement:

Am having five records in the table, the id values 1,2,3,4,5. Now, I am deleting the record 5. After wards am trying to insert another record. Unfortunately we will miss 5 and the record will be inserted with the value 6. How to sort down this issue?

Solution:

SQL server is providing a handy solution to solve this issue. The command is DBCC Checkident. Lets see the syntax for this,

DBCC checkident(tablename, reseed, value to start or reseed the column)

Lets see a small example on this,

First code snippet is to show the problem and the second code snippet is the solution for the problem.

drop table venkatTable
create table venkatTable(id int identity(1,1) primary key, nam varchar(100))
insert into venkatTable values('Venkatesan Prabu')
insert into venkatTable values('Subashini')
insert into venkatTable values('Jayakantham')
insert into venkatTable values('Arunachalam')
insert into venkatTable values('Santhi')

select * from venkatTable
delete venkatTable where id=5

insert into venkatTable values('Santhi')

select * from venkatTable

drop table venkatTable
create table venkatTable(id int identity(1,1) primary key, nam varchar(100))
insert into venkatTable values('Venkatesan Prabu')
insert into venkatTable values('Subashini')
insert into venkatTable values('Jayakantham')
insert into venkatTable values('Arunachalam')
insert into venkatTable values('Santhi')

select * from venkatTable
delete venkatTable where id=5

--Am resetting the id columnwith the maximum value of the column.

declare @val int
select @val=max(id) from venkattable
dbcc checkident (venkatTable,reseed,@val)

insert into venkatTable values('Santhi')

select * from venkatTable

If you dont want to reset the seed value of the column, we can use noreseed option in the abov command,

DBCC checkident(tablename,noreseed)

Thanks and Regards,

Venkatesan Prabu .J

ASP.Net error

While trying my website creation, I got a peculiar error in ASP.net.

I have dragged the SQLDatasource and placed it in my web page. On pointing the data source, I got the below error.
"Following error occured while getting connection string information from configuration. "Cannot get web application service"

On checking microsoft site, I came to know that its a familiar bug and the solution seems to be closing the IDE and reopen the same.
This solution seems to be working fine.

Thanks and Regards,
Venkatesan Prabu .J

SQL Server error in backing up the database

I got a peculiar error while taking backup of my database.

Error received:

write on 'Physical backup filename(.bak format)' failed, status=33. See the SQL Server error log for more details. BACKUP DATABASE is terminating abnormally.

Explanation for this error:

Status=33 indicates the error should be related to the device. So there should be some problem with the storage device.

Reason for this issue:

If you try to take the backup of your database in a compressed harddrive. You will get this strange error. To resolve this, make your device(your D drive or E drive) un compressed and try to take the backup of your database.

It worked fine for me.

Thanks and Regards,

Venkatesan Prabu .J

Rand function and floor function in SQL Server

Rand function and floor function in SQL Server:

Most of the time, we need a functionality to generate random numbers in our application. SQL Server is offering a handy function to generate random numbers. Here is a small sample,

select rand()

The above query is used to fetch the value between 0 and 1. If I need to create a random number between 0 and 100.

Then, we need to multiply the function with 100.

SELECT 100*RAND()
The above query will fetch the recordds with decimal values which ranges from 0 to 100.



In case, If we need the value in Integer type. Then, we have to go for floor command which will remove the decimal part from the output.

SELECT floor(100*RAND())

In case, If you want to restrict the records between 2 to 100 (I dont want 0 and 1 to be retrieved). Then, we can use the below query.

SELECT floor(98*RAND())+2

Thanks and Regards,

Venkatesan Prabu .J

Stored procedure execution on SQL Server startup

Stored procedure execution on SQL Server startup:

I have studied a very interesting topic in SQL Server and wish to blog the same in my site.

Scenario:
On each SQL Server database startup, I need to execute a procedure in my database. It's a very basic scenario in all places.

Solution:
For this, SQL Server is providing an option of using a system stored procedure sp_procoption

-- Am creating a table
create table venkat1(id int, val varchar(10))
-- 'Am creating a procedure to insert a record in a table venkat1
create procedure Venkatesan_Insert_Procedure
as
begin
insert into venkat1(id,val) values (5,'F')
end
-- Am setting the sp_procoption for my procedure with the option name as "Startup" and option value as "True"

EXEC sp_procoption @ProcName = 'Venkatesan_Insert_Procedure',@OptionName = 'startup',@OptionValue = 'true'

After that, am restarting the server.

select * from venkat1

Thanks and Regards,

Venkatesan Prabu .J

Case Statement in SQL Server

Interesting facts about Case statement in SQL Server
--- Creating the table Venkat
drop table venkat
create table venkat(id int, val int ,date datetime,nam varchar(10))
insert into venkat values(1,200,'3/2/2008','venka')
insert into venkat values(1,100,'10/2/2008','venk')
insert into venkat values(3,400,'10/2/2008','arun')
insert into venkat values(3,500,'10/4/2008','balu')
insert into venkat values(3,600,'10/4/2008','lakshmi')
insert into venkat values(3,700,'10/4/2008','santhi')

--------------------------------------------------------
select * from venkat
--------------------------------------------------------
Case statement is used to check the alternatives.
"If the value is this much, show it as this. Else show it as this."

--------------------------------------------------------
-- A simple select statement for you
select id, value =

case
when val=100 then 'One Hundred'
when val=200 then 'Two Hundred'
when val=400 then 'Four Hundred'
when val=500 then 'Five Hundred'
when val=600 then 'Six Hundred'
when val=700 then 'Seven Hundred'
end

from venkat
--------------------------------------------------------


--------------------------------------------------------
-- Complication in case statement
-- If i need to select two values based on one value. Then in this case, we need to write two case statement as below.
-- Instead of one case statement.
select id,
case
when val=100 then value ='One Hundred' and val = 'One'
when val=200 then value ='Two Hundred' and val = 'Two'
when val=400 then value ='Four Hundred' and val = 'Four'
when val=500 then value ='Five Hundred' and val = 'Five'
when val=600 then value ='Six Hundred' and val = 'Six'
when val=700 then value ='Seven Hundred' and val = 'Seven'
end

from venkat
--------------------------------------------------------
-- The above statement will throw error.
--------------------------------------------------------
select id, value =

case
when val=100 then 'One Hundred'
when val=200 then 'Two Hundred'
when val=400 then 'Four Hundred'
when val=500 then 'Five Hundred'
when val=600 then 'Six Hundred'
when val=700 then 'Seven Hundred'
end,
val=
case
when val=100 then 'One'
when val=200 then 'Two'
when val=400 then 'Four'
when val=500 then 'Five'
when val=600 then 'Six'
when val=700 then 'Seven'
end

from venkat

--------------------------------------------------------
-- My condition is to create a procedure with order by a column.
alter procedure venkatesanprabu_proc
(@options int)
as
begin

select id, value =

case
when val=100 then 'One Hundred'
when val=200 then 'Two Hundred'
when val=400 then 'Four Hundred'
when val=500 then 'Five Hundred'
when val=600 then 'Six Hundred'
when val=700 then 'Seven Hundred'
end

from venkat

order by
case
when @options=1 then id
when @options=2 then nam
end

end
--------------------------------------------------------
-- Case statement in Order by clause
--------------------------------------------------------
-- The above statement will throws an error.
-- An interesting fact in SQL Server is, we can't use different data type in the order by clause.
-- In the above statement, If it's option=1 then output will be order by id
-- Else if the option is equal to 2 then output will be order by nam.
--------------------------------------------------------
-- Here is the solution for the above problem. We need to use two case statement to resolve it.
--------------------------------------------------------
alter procedure venkatesanprabu_proc
(@options int)
as
begin

select id, value =

case
when val=100 then 'One Hundred'
when val=200 then 'Two Hundred'
when val=400 then 'Four Hundred'
when val=500 then 'Five Hundred'
when val=600 then 'Six Hundred'
when val=700 then 'Seven Hundred'
end

from venkat

order by
case
when @options=1 then id
end,
case
when @options=2 then nam
end

end
--------------------------------------------------------
exec venkatesanprabu_proc 1
exec venkatesanprabu_proc 2

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

Thanks and Regards,

Venkatesan Prabu .J

Dependent Objects in SQL Server

Identifying Dependency or relative objects in SQL Server:

I have seen an interesting question in a SQL Server forum.

Scenario:

How to identify the dependency on an object?

Solution:

Sp_depends “Object_Name”

Sp_depends will give you all the dependent objects in the databse. Let’s see the implementation for this scenario.

--- Creating the table Venkat
drop table venkat
create table venkat(id int, val int ,date datetime)
insert into venkat values(1,200,'3/2/2008')
insert into venkat values(1,100,'10/2/2008')
insert into venkat values(3,400,'10/2/2008')
insert into venkat values(3,500,'10/4/2008')
insert into venkat values(3,600,'10/4/2008')
insert into venkat values(3,700,'10/4/2008')

-- Created a view "vw" to fetch records from the table "Venkat"
--------------------------------------------------------
create view vw
as
select * from venkat
--------------------------------------------------------
-- Created a procedure named VenkatesanPrabu_proc with the statement to fetch records from the view
--------------------------------------------------------
create procedure VenkatesanPrabu_proc
as begin
select * from vw
end
select * from vw
--------------------------------------------------------
sp_depends vw

Thanks and Regards,
Venkatesan Prabu .J

SSIS with oracle database

Oracle error while migrating the data from one Oracle database to another:

Fortunately, I got a chance to work with oracle database migration using SSIS. Below is a peculiar behaviour of SQL Server Integration services. I have connected OLE DB source and connected to a table. Dragged and dropped OLE DB Destination, I need to created new table, clicked “New”.

The syntax for the table got created with a create statement.

CREATE TABLE SQLGenerated (
"Type_RATES_ID" NUMERIC(12),
"Type_RATES_VN" NUMERIC(12),
"_TYPE_ID" NUMERIC(12),
"_RATE_TYPE" VARCHAR(2),
"LENDING_RATE" NUMERIC (7,6),
"HEDGING_MARGIN" NUMERIC (7,6),
"PROFIT_MARGIN" NUMERIC (7,6),
"COMMISSION" NUMERIC (7,6),
"ESTABLISHMENT_COST" NUMERIC (24,8),
"RATE_PERIOD" NUMERIC(6),
"DATE_FIX" DATETIME,
"MOD_USER_ID" NUMERIC(12),
"MOD_TIMESTAMP" DATETIME,
"_ID" NUMERIC(12),
"TOTAL_RATE" NUMERIC (7,6),
"FIXED_AMOUNT" NUMERIC (24,8),
"_AMOUNT" NUMERIC (24,8),
"_TYPE_RATE_DIFF" NUMERIC (7,6),
"MATURITY_DATE" DATETIME,
"_SWITCH_FLAG" NUMERIC(1)
)

On pressing “OK” button, am getting the below error.
ORA-00902: invalid datatype (OraOLEDB)

To resolve this issue,
1. I went back to oracle database
2. Created a script for the source database.
3. Copied the content and pasted it into the create table window.
4. Now it’s working fine.
The problem is due to the data type,

create table OracleGenerated
(
Type_RATES_ID NUMBER(12) not null,
Type_RATES_VN NUMBER(12) not null,
_TYPE_ID NUMBER(12) not null,
_RATE_TYPE CHAR(2),
LENDING_RATE NUMBER(7,6),
HEDGING_MARGIN NUMBER(7,6),
PROFIT_MARGIN NUMBER(7,6),
COMMISSION NUMBER(7,6),
ESTABLISHMENT_COST NUMBER(24,8),
RATE_PERIOD NUMBER(6),
DATE_FIX DATE,
MOD_USER_ID NUMBER(12),
MOD_TIMESTAMP DATE,
_ID NUMBER(12) not null,
TOTAL_RATE NUMBER(7,6),
FIXED_AMOUNT NUMBER(24,8),
_AMOUNT NUMBER(24,8) default 0 not null,
_TYPE_RATE_DIFF NUMBER(7,6) default 0 not null,
MATURITY_DATE DATE,
_SWITCH_FLAG NUMBER(1) default 0,
)
The problem is due to the datatype “Datetime”. SQL Server is generating datetime but oracle is not supporting the same. In turn, we need to modify the data type to “date” type.

Thanks and Regards,

Venkatesan Prabu .J

General options in SQL Server

General tools options in SQL Server:

Contolling the SQL Server editor window in SQL Server Management studio.

Goto Tools->Options-> Query execution->General, we are having lot of options.

1. Set Rowcount – This option is used to restrict the result set returned by the text editor.
Considering am having lakhs of records and I need to restrict the number of records returned, we can specify the same. It’s equivalent to the TOP keyword. “0” indicates that, there will be unlimited rows (or) no restrictions in the number of records fetched.

2. Set Textsize – This option is used to restrict the size of the text to be returned in your text editor. Considering, am having a column of type “TEXT”, If I have given 10 as the bytes. The resultset will be restricted with only 10 bytes.

3. Execution time-Out : We can restrict the query execution time. How much time, the query should execute. If we have given 0 as the value, then its unlimited or no restriction. This is very useful in executing a query in production environment.

4. Batch separator: This is used to separate the batch statement. Grouping the queries.
Strange but interesting facts of the GO statement. Before this article, I thought like the go statement will commit the transactions. But after checking this, I got shocked. It’s not committing it. I have tried to rollback the statement. The entire process got roll backed.


begin transaction
go
insert into venkat values(3,700,'10/4/2008')
go
insert into venkat values(3,700,'10/4/2008')
rollback

So, GO statement in SQL Server is used only for separating the SQL statements and it doesn’t have any impact on the transactions handling.

Thanks and Regards,

Venkatesan Prabu .J

Foreign key constraints in SQL Server

Foreign key constraints :

This constraint is used to map a particular column in the child table with a primary key column in the parent table. Consider my scenario, am having an employee table and it will be having empid as primary key (Unique reference to identify an employee) , am having empProjects which will hold project id as the primary key.

I need to check, In which project an employee is allocated? Or what are the projects assigned for this particular employee?

The relation ship between these two tables is One to Many relationship (An employee can work in more than one project).

To retrieve the result set we need to have a foreign key relation ship between the employee table and empProjects table, a column empid should be created in empProjects table and it should refer the primary table “Employee”.

Let’s see a small example; I am trying to create two tables VenkatA, VenkatB. Id in the second table is referencing the ‘id’ column in the primary table.

create table venkatA(id int,[name] varchar(100))
create table venkatB(id int references venkatA(id),designation varchar(100))

OOPS, On executing the query above, we will get an exception.
There are no primary or candidate keys in the referenced table 'venkatA' that match the referencing column list in the foreign key 'FK__venkatB__id__7BB05806'.

It’s because of the column ‘id’ in the primary table. It’s mandatory to declare that particular columns as primary or candidate key.

create table venkatA(id int primary key,[name] varchar(100))
create table venkatB(id int references venkatA(id),designation varchar(100))

On executing the query above, you will get the second table referencing the first one.

Thanks and Regards,

Venkatesan Prabu

Phyical MDF and LDF location in SQL Server

I have started questioning myself like,

Is it possible to retrieve the physical location of my data file and log file?

I am struck up with retrieving details for data and log file location of our existing databases and found that the key table is located in the master databases. Below is the query to retrieve that information,

select * from [master].[sys].[master_files]

On executing the query above, we will get the below result set,

Now, lets tune up the query further, I am trying to retrieve data files physical path for my master database.

DECLARE @sql_path NVARCHAR(256) ;

SELECT @sql_path = SUBSTRING([physical_name], 1, CHARINDEX(N'master.mdf', LOWER([physical_name])) - 1)
FROM [master].[sys].[master_files]
WHERE [database_id] = 1
AND [file_id] = 1 ;
print @sql_path
The output is “C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\”

Thanks and Regards,

Venkatesan Prabu .J