SQL Server error

Dear Viewers,

  Frequently, most of our sql server developers face this issue,

"The Product level is insufficient for Component in sql server"

Even, I too faced this kind of issue in my development work. I have got the sole reason for this error in sql server.

Lets analyse,

  The problem is due to the installables. Most of the companies wont take up the entire version of SQL Server. Considering, mostly we will get this error in SSIS/SSRS and other sql server supporting services.

   Companies will try to buy the installables which hold only SQL Server client components and they dont have the entire package like SSIS, SSRS.

  If you face this kind of problem, Goto - >Programs ->Microsoft SQLServer2005 ->Configuration tools - >SQL Server Configuration Manager.

  Just check for those services running in your machine.

Else, goto run->services.msc and check for the corresponding services in your machine. If the services is available then, try to restart the services. It may solve your problem.

Related article is available at,

http://venkattechnicalblog.blogspot.com/2008/09/sql-server-interview-questions_27.html

Thanks and Regards,

Venkatesan Prabu .J

DBReindex in sql server

DBReindex:
DBReindex is used to reindex the entire sql server database. It will flush out the indexes and create new index in the leaf level. Thats why its considered as very effective in improving the performance of our database. The syntax is,

dbreindex(tablename,indexname, fillfactor)
In the older version of sql server, there is an another option called "sorted_data_reorg" which is mainly used to enhance the speed of index creation. But, its dropped in the future versions of sql server.
Ex:
dbcc dbreindex(tblname,'',0,sorted_data_reorg)
After executing the above command, some dba's used to execute sp_recompile option.
sp_recompile tblname
But, practically speaking we dont want this statement after dbreindex command. Because, if the index or statistics got affected. Obviously sp_recompile option will get fired.

Thanks and Regards,
Venkatesan Prabu .J

Rows to columns in sql server

Its my third article on rows to columns coversion in sql server 2005. Now, its pretty different i have given a very different way to make this possible. Lot of our developers were finding to acheive this functionality in sql server 2000. Below is a simple approach to achieve it.

declare @Attendees table
( FunctionID integer,
DetailID integer,
ParticipantID integer,
StatusID integer
)
insert into @Attendees
select 1, 1, 123, 6 union all
select 1, 2, 124, 6 union all
select 1, 2, 125, 7
select * from @Attendees

declare @Status table
( StatusId integer,
Description varchar(10)
)
insert into @status
select 6, 'Invited' union all
select 7, 'Accepted'
select * from @status

select a.FunctionID,a.DetailID,--b.Description
case
when b.Description='Invited' then 1 else 0
end as invited,
case
when b.Description='Accepted' then 1 else 0
end as Accepted
from @Attendees a inner join
@status b on a.statusID=b.statusID

select a.FunctionID,a.DetailID,--b.Description
sum(case
when b.Description='Invited' then 1 else 0
end) as invited,
sum(case
when b.Description='Accepted' then 1 else 0
end) as Accepted
from @Attendees a inner join
@status b on a.statusID=b.statusID
group by a.FunctionID,a.detailid


Thanks and Regards,
Venkatesan Prabu .J

Rows to columns in sql server

As usual, a frequent article in sql server.

 I am trying to covert the rows to columns in sql server.

Problem description: Am having a table with list of participants and number of programs.

I need to give a binary value based on the participants for a specific program. Lets see how to arrive this.

Declared and inserting data into the table @ Attendees 

declare @Attendees table
( FunctionID integer,
DetailID integer,
ParticipantID integer,
StatusID integer
)
insert into @Attendees
select 1, 1, 123, 6 union all
select 1, 2, 124, 6 union all
select 1, 2, 125, 7
select * from @Attendees

Declared and inserting data into the table @ Status

declare @Status table
( StatusId integer,
Description varchar(10)
)
insert into @status
select 6, 'Invited' union all
select 7, 'Accepted'
select * from @status

Getting the output with duplicates.

select a.FunctionID,a.DetailID,--b.Description
case
when b.Description='Invited' then 1 else 0
end as invited,
case
when b.Description='Accepted' then 1 else 0
end as Accepted
from @Attendees a inner join
@status b on a.statusID=b.statusID

Getting the exact required output.

select a.FunctionID,a.DetailID,--b.Description
sum(case
when b.Description='Invited' then 1 else 0
end) as invited,
sum(case
when b.Description='Accepted' then 1 else 0
end) as Accepted
from @Attendees a inner join
@status b on a.statusID=b.statusID
group by a.FunctionID,a.detailid

Thanks and Regards,

Venkatesan Prabu .J

Use database inside stored procedure

I am thinking about an important funcitonality or property in sql server.
Is it possible to give the database name dynamically in sql server?
OOps, i think we can achieve this using dynamic sql.
I have made a try on this. Lets see,
use master
GO
declare @sql nvarchar(max)
select @sql = 'use'
select @sql = @sql + ' ' + 'Northwind'
select * from dbo.Employees -- Northwind table
exec sp_executesql @sql
--------------------------------------------------------
Its not working. I have tried to analyse this problem, its because once the query gets executed its coming out of that particular scope and enter into the public scope.

The work around for this problem is, we need to

use tempdb
go
declare @sql nvarchar(1000)
declare @dbname varchar(40)
select @dbname = 'Northwind'
select db_name() --tempdb
select @sql = 'use'
select @sql = @sql + ' '+@dbname +' select * from dbo.Employees
select db_name()'
exec sp_executesql @sql --northwind
select db_name() --tempdb
Thanks and Regards,
Venkatesan Prabu .J