Check whether FileExists in SQL Server 2005/2008

Well, you might have done programming in any of the .NET flavor to check whether file is exist in particular folder or not. You might have used many classis of SYSTEM.IO namespaces and big code to check but If I tell you it is more than easy to check the file whether it is exists or not, in SQL Server than .NET, what would be your reaction? Ohh!! What are you talking about!!!!
I am not kidding, it is really very very easy, just one simple statement, no namespace, no use of class etc. etc. Have a look at it.
exec master.dbo.xp_fileexist ‘d:\emps.txt’



If emps.txt would be exists in your “D” drive, you would get answer “1” in “File Exist” column or “0”. Isn’t it very easy?
Now think if you are doing some kind of T-SQL Programming and you need this value in variable, again very easy and small code snippet required for that, have a look at it again.
DECLARE @isExists INT
exec master.dbo.xp_fileexist ‘d:\emps.txt’, @isExists OUTPUT
SELECT case @isExists when 1 then ‘Yes’ else ‘No’ end as isExists



Happy T-SQLing!!!
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

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

SQL Server energy event with Rushabh Mehta

I was fortunate to attain SQL Server Energy Event arranged by Gandhinagar  SQL Server user group and Ahmedabad SQL Server user group. It was really energized by well known speaker in the world of SQL Server and Business Intelligence Mr. Rushabh Mehta (SolidQ.com), Mr. Pinal Dave (SQLAuthority.com) and Mr. Jacob Sebastian (beyondrelational.com). Any event should be energized by present of one MVP and here in meeting, we had three SQL Server MVPs  so it suppose to worth attend.

It is an opportunity to listen Mr. Rushabh Mehta when he talks about BI, we had very good session over BI with him followed by interactive question and answer session. I have raised few question and pleased to get its perfect, to the point and satisfactory answer from Mr. Rushabh Mehta. Thank you very much Rushabh. I also would like to express my sincere thanks to Mr. Pinal Dave who take initiative and put all his efforts to make this event very successful and bring the speaker like Mr. Rushabh Mehta in Ahmedabad.

To know more about this successful event and see its photograph, must visit Mr. Pinal Dave’s blog at
Once again, thank you very much everybody who has played a role to arrange this event and made it successful, I would like to attain more and more event like this.

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

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

Instances of SQL Server – Run multiple SQL Server versions on same server

I have seen many times that people, especially who are .NET developer, be in illusion that they can’t install more than one version of SQL Server in one Server. This is indeed not true at all. If you are aware with INSTANCES of SQL Server, you could probably run more than one version of SQL Server.

When you install SQL Server, you get two options 1.) Default Instance 2.) Named Instance. If you have installed SQL Server (may be sql server 2000) with default instance first time, you can install other version (may be SQL Server 2005 and/or SQL Server 2008) with named instance in your server. All you have to select is, Named Instance radio button and give name to instance. You can give any name to your instance to identify what version you want to connect at the time of connecting it. Suppose you give instance name “SQL2K5” to another SQL Server 2005 installation than you will be able to use SQL Server 2000 and 2005 both in your server. All you need to do is, while connecting to new instance, you have to give SERVERNAME\Instancename (in our case instance name is “SQL2K5”).

Hope this helps to newbie.

 

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

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

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