Tag Archives: sql server 2008

Windows Installer Cleanup Utility

I came across one issue recently. I have tried to install SQL Server 2005 in one of our server and guess what happened!!!??? J It failed to install. Bit annoying but I can’t help much as that is not in my hand. I tried to uninstall after failed install from Control Panel->Add/Remove program but it was not even willing to get uninstalled!!!! Bit more annoying, isn’t it? Yes, it is.

Removing SQL directories and remove entry from registry manually is something very frustrated task to do so I decided not to go in that route and start find the way around and I found one utility from Microsoft site, its “Windows Installer Cleanup utility”, I downloaded it and use it and guess what happened??? It’s been my rescue. I would like to share that utility with my readers,  you can download that utility from below given link, it may be very helpful sometime.

http://support.microsoft.com/default.aspx?scid=kb;en-us;290301

Do read warning and readme file before using it.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Shrink log file for all database in SQL Server 2008/2005

Well, shrinking log file manually and frequently is not at all good idea but we have to do it once in a while when it reaches at very big size, however if it is growing unacceptably and frequently, you have to investigate the reason before shrinking it.

Anyway, our main focus today is how to shrink log file. Well, there is a simple command “DBCC SHRINKFILE” in T-SQL but what, if we want to shrink log file of all database in SQL Server? Well in this situation SP_MsForEachDB (undocumented SP) will come to your help.

Let us see small yet handy script to do so:

use master

DECLARE @SQL varchar (2000)

SELECT @SQL =

SELECT @SQL = @SQL + ‘if ”?” <> ”master” and ”?” <> ”msdb” and ”?”<>”tempdb” and ”?” <> ”model” ‘

SELECT @SQL = @SQL + ‘BEGIN ‘

SELECT @SQL = @SQL + ‘USE ?; ‘

SELECT @SQL = @SQL + ‘SELECT ”?”; ‘

SELECT @SQL = @SQL + ‘DECLARE @LogFile varchar (30); ‘

SELECT @SQL = @SQL + ‘SELECT @LogFile = name FROM dbo.sysfiles WHERE (status & 0x40) <> 0; ‘

SELECT @SQL = @SQL + ‘dbcc shrinkfile (@LogFile, 1,truncateonly); ‘

SELECT @SQL = @SQL + ‘SELECT fileid, name, filename, size, growth, status, maxsize FROM dbo.sysfiles WHERE (status & 0x40) <> 0; ‘

SELECT @SQL = @SQL + ‘END’

SELECT @SQL

EXEC sp_MSforeachdb @command1=@SQL

GO

 

To know more about SP_MsForEachDB stored procedure, have a look at one of my previous article at:

http://www.sqlhub.com/2009/03/spmsforeachdb-undocumented-stored.html

http://www.sqlhub.com/2009/04/find-user-in-all-database-with.html

 

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Find and correct pattern of string in SQL Server 2008

I have observed that we need to follow specific pattern in some string field. It is difficult and time consuming sometime to loop around string for check for patter and if string not found with specific pattern, update the string value.
Let us have one small example, suppose I have one string field with alphanumeric value. First numeric value should come than alphabetic value separated by hyphen sign.
eg: ((123))-Ritesh
Above should be our value. Numeric value should be surrounded by opening and closing double brackets. If brackets are not there before and after numeric value, it should be padded. How can we achieve this? There is very small T-SQL code with couple of string functions needed to perform this operation, let us have look at that.
declare @val varchar(50)
Set @val = ‘111-Ritesh’
if charindex(‘))’,@val, charindex(‘-‘,@val)-1) = 0
      set @val = stuff(@val, charindex(‘-‘,@val), 1, ‘))-‘)
if charindex(‘((‘, @val) = 0
      set @val = stuff(@val, 1, 0, ‘((‘)
select @val
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Update statistics of all tables in SQL Server 2008/2005

Today I am going to share very small yet very useful and handy script. It is often necessary to keep update statistics of all tables for performance point of view. Actually by default query optimizer updates statistics in query plan in certain situation but it is good to manually do it to assure that every query plan it optimized and up-to-date.

You can generate T-SQL for update statistics for all tables of your database along with Schema name. Have a look at the script.

SELECT
        ‘UPDATE STATISTICS ‘ + SysSche.Name + ‘.’ + SysObj.Name
FROM
        Sys.Objects SysObj
INNER JOIN
        sys.schemas SysSche ON SysObj.Schema_ID = SysSche.Schema_ID
WHERE
        TYPE = ‘U’

For more details about UPDATE STATESTICS and its syntax, you can refer official Microsoft link at

http://msdn.microsoft.com/en-us/library/ms187348.aspx

 

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Dynamic PIVOT with month number to month name as header in SQL Server 2008

I have written quite a few articles about PIVOT but this is somehow bit different than all of the previous article, the need is to create dynamic pivot table which shows month name as a header for country and display the count of sales amount. Main thing is to display month name as a header where as we have month number in data, moreover, it should be dynamic PIVOT as # of month is not fixed, may be 1 or 2 or 12 (can’t have more than 12 -;) )
Let us create one dummy table along with data to start our PIVOT journey.
–table for demo
create table testing
(
country varchar(10),
[Month] int,
Amount int
)
go
–dummy data
insert into testing
select ‘A-land’,6,100 union all
select ‘B-Land’,5,110  union all
select ‘B-Land’,7,90  union all
select ‘C-Land’,6,200  union all
select ‘C-Land’,2,70  union all
select ‘D-Land’,8,30
GO
–PIVOT script
DECLARE @Cols NVARCHAR(2000)
SET @Cols=
–collect distinct month we have in table
SELECT @Cols=@Cols+ ‘[‘+DATENAME(month,convert(varchar,s.[month])+‘-1-1900’) +‘]’+ ‘, ‘ FROM
(SELECT DISTINCT [MONTH] FROM testing ) AS s order by s.[Month]
–remove last comma
SET @Cols=LEFT(@Cols,LEN(@Cols)-1)
–our pivot will display Month name though we have Month number in table.
SET @Cols=‘SELECT * from (select country,DATENAME(month,convert(varchar,[month])+ ”-1-1900”) as [Month] ,amount FROM testing) up
PIVOT (count(amount) for [month] in (‘+@cols+‘)) AS pivo’
–execute our dynamic query resides in @COL variable
EXECUTE sp_executeSQL @Cols
GO



If you want to look at my other PIVOT example than do look those at:
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Select random records in SQL Server 2008

We often need to return few random records from our table, we may use it for online test for any subject and it will return random question set for each user giving exam concurrently. There is lot more use than just test but online examination is just an example.

There are quite a few ways to do so but I have one easy way to do so. Let us see how to do it.

–creating demo table

CREATE TABLE emps

(

Name VARCHAR(50),

Dept VARCHAR(10),

Company VARCHAR(15)

)

–INSERT records

INSERT INTO emps

SELECT ‘Ritesh’,‘MIS’,‘echem’ UNION ALL

SELECT ‘Bihag’, ‘MIS’, ‘CT’ UNION ALL

SELECT ‘Rajan’, ‘account’,‘Marwadi’ UNION ALL

SELECT ‘Alka’,‘account’,‘tata’ UNION ALL

SELECT ‘Alpesh’,‘Chemical’,‘echem’

GO

–select top 2 random row by NEWID() function.

Select Top 2 * from emps order by NEWID()

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Dynamic SELECT statement with SP by querying information_schema.columns in SQL Server 2008/2005

Some days from now, I am getting new requests everyday to solve and I love to solve problems as much as I can and help community more and more.  Situation was like, there is a table which has few fields like Name, Address, Age ,_NO etc. but reader don’t want to return columns starts with “_”.  Very simple thing, right. Suppose name of Table is “DemoTable” you can simply write down
SELECT Name, Address, Age from DemoTable
But requirement doesn’t ends here,  it needs to be dynamic and should work with any table without even specifying the column name. Here we have to start thinking something different, let me show you how I did that? Have a look at below script and enjoy SQL Programming!!!!
–Demo table
create table DemoTable
(
Name varchar(10),
Address varchar(10),
Age int,
_No int
)

 
–SP which will accept tablename and character of the column name
–which needs to be eliminated from the list of results.
create proc DynamicSelect
@TableName varchar(20),
@FilterChar char(1)
AS
BEGIN
SET NOCOUNT ON
      DECLARE @Cols NVARCHAR(500)
      SET @Cols=
      –gathering column list in @Cols variable by querying Information_Scehma.columns table
      SELECT @Cols=@Cols+ s.column_name + ‘, ‘ FROM
      (select COLUMN_NAME from  information_schema.columns where TABLE_NAME=‘TableA’ and charindex(@FilterChar,COLUMN_NAME)<=0) AS s
      SET @Cols=LEFT(@Cols,LEN(@Cols)-1)
      set @cols=‘SELECT ‘ + @Cols + ‘ From ‘ + @TableName
      EXECUTE sp_executeSQL @Cols
END


–check SP whether it actually works!!!!
Exec DynamicSelect ‘DemoTable’,‘_’

 
Actually above task could be done by CURSOR also but as long as possible I always would like to avoid the cursor.
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Make all columns from all tables in database, NOT NULL and set Default value in SQL Server 2008

Today I helped one person in one forum, he wanted to generate script to make all columns in all tables NOT NULL and wanted to set DEFAULT value 0 for all numeric data types. I felt to share it with all my readers as it could be interesting to know and sometime could be very helpful and handy with some customize changes.
select
not_null = ‘alter table ‘ + table_name + ‘ alter column ‘
                  + column_name + ‘ ‘ + data_type
                  + case when data_type = ‘numeric’ then ‘(‘ else end
                  + case when data_type = ‘numeric’ then convert(varchar,numeric_precision_radix) else end
                  + case when data_type = ‘numeric’ then ‘,’ else end
                  + case when data_type = ‘numeric’ then convert(varchar,numeric_scale) else end
                  + case when data_type = ‘numeric’ then ‘)’ else end
                  + ‘ not null ‘
,default_0 = ‘alter table ‘ + table_name
                  + ‘ add default 0 for ‘ + column_name
from information_schema.columns
where COLUMN_DEFAULT is null
and   data_type not like ‘%char%’
and   data_type not like ‘%time%’
and   data_type not like ‘%image%’
and   data_type not like ‘%binary%’

 

Above query will list the T-SQL script which you can run in your query editor.
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Error Fix: invalid precision for decimal data type in SQL Server 2005/2008

Some time you may be greeted with the error “invalid precision for decimal data type” while exporting your SQL Server table to Microsoft Excel. The main root cause of this error is, Excel doesn’t not support Decimal precision greater than 28. If you try to export a SQL Table which has column with Decimal(29,0), you probably found this error. In this case, there is only one solution is to decrease your precision limit in SQL Server table to <=28 and you will be ok.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah