Check if file exists using sql

-- using the scripting object
declare @Path varchar(128) ,
@FileName varchar(128)
select @Path = 'C:\' ,
@FileName = 'myfile.txt'
declare @objFSys int
declare @i int
declare @File varchar(1000)
select @File = @Path + @FileName
exec sp_OACreate 'Scripting.FileSystemObject', @objFSys out
exec sp_OAMethod @objFSys, 'FileExists', @i out, @File
if @i = 1
print 'exists'
else
print 'not exists'
exec sp_OADestroy @objFSys

-- using xp_cmdshell
declare @Path varchar(128) ,
@FileName varchar(128)
select @Path = 'C:\' ,
@FileName = 'myfile.txt'
declare @cmd varchar(1000)
create table #a(s varchar(1000))
select @cmd = 'dir /B ' + @Path + @FileName
insert #a exec master..xp_cmdshell @cmd
if exists (select * from #a where s = @FileName)
print 'exists'
else
print 'not exists'
drop table #a

-- using xp_fileexists
declare @Path varchar(128) ,
@FileName varchar(128)
select @Path = 'C:\' ,
@FileName = 'myfile.txt'
declare @i int
declare @File varchar(1000)
select @File = @Path + @FileName
exec master..xp_fileexist @File, @i out
if @i = 1
print 'exists'
else
print 'not exists'

Service Oriented Architecture - SOA Definition

Easy to the point Definition:

The Service Oriented Architecture (SOA) is a design philosophy which defines how a solution should be build.

SOA design approach allow organizations with multiple technologies and platforms to build solutions which are re-useable and accessible across all systems without rewriting of code. All business technologies and functions are treated as services, these services are build to last for long time but service configurations are build for change. This approach creates a loosely-coupled systems which can be changed without spending more money. A loosely coupled system allow business processes to change more quickly as they are not dependent on underlying IT infrastructure.

A service oriented application means writing a highly dynamic, collaborative, stable, dependable application. A application which can act as service to all other business processes/systems without rewriting. The service should not be written for any specific system it should be consumable by any system. The service should use standard protocols (SOAP, XML) to exchange messages between each other. The service should be re-useable, work independently and must be manageable.
Web Services, WSDL, Remote Scripting are common examples of SOA.

Kill SQL Server connections by username

You can use following SQL Script to kill connections by a single user, make sure you set the username in @UName variable.

SET NOCOUNT ON
DECLARE @UName varchar(50)
DECLARE @spidstr varchar(8000)
DECLARE @ConnKilled smallint
SET @ConnKilled=0
SET @spidstr = ''
Set @UName = 'USERNAME'
SELECT @spidstr=coalesce(@spidstr,',' )+'kill '+convert(varchar, spid)+ '; '
FROM master..sysprocesses WHERE loginame=@UName
IF LEN(@spidstr) > 0
BEGIN
EXEC(@spidstr)
SELECT @ConnKilled = COUNT(1)
FROM master..sysprocesses WHERE loginame=@UName
END

SOA - Service Oriented Architecture

Today in the meeting the director declared we will be using SOA design approch for all new development and in next few years all our systems will be using SOA. I guess IBM sales person did a good job, SOA is the future. I dont know our business logics never changes atleast not in last couple of years we are still using multiple technologies from SAP to cobol and one of our core application is still running on IBM AS400 which we have no plans to upgrade (none i know of). The new IT budget close to $90 million is approved so we need to spend money some where i think its a good time for me to ask for a raise in hourly rate too, well i have bills too...

One group of people gets confused that SOA or service Oriented Architecture is some kind of software which we can buy get training on it and boom its all done. The other group takes it as web service or re-useable componenet like COM objects, lot of questions were raised that we are still using it, but i guess hardly any one knew what is service oriented architecture of application is the IBM sales personals tried to define it in easy way that its the future and all enterprise business are using it.

SOA is a design concept for businesses in which all service providers (computer systems) use common standard protocol to talk to each other. The protocol which the sytems use is called SOAP very much like web services a common example i have seen is implemented by Paypal to accepting credits cards on merchant own websites.

I am still researching in more depth on SOA i guess i have used it in many applications with out knowing. I will write on it more later.

Kill all connections to SQL Server database using a script, kill sql server connections

As a SQL Server DBA there will be time when you want to kill all the connections to database which can be for restore or to perform other database tasks. As a DBA you can put the database in single mode or dbo use only, but most of the time you are performing the task as automated process using scripts. You can kill all connections to SQL Server database using the following script, make sure you replace the database name by setting @DBName variable value.


SET NOCOUNT ON
DECLARE @DBName varchar(50)
DECLARE @spidstr varchar(8000)
DECLARE @ConnKilled smallint
SET @ConnKilled=0
SET @spidstr = ''

Set @DBName = 'DATABASE_NAME'
IF db_id(@DBName) < 4
BEGIN
PRINT 'Connections to system databases cannot be killed'
RETURN
END
SELECT @spidstr=coalesce(@spidstr,',' )+'kill '+convert(varchar, spid)+ '; '
FROM master..sysprocesses WHERE dbid=db_id(@DBName)

IF LEN(@spidstr) > 0
BEGIN
EXEC(@spidstr)
SELECT @ConnKilled = COUNT(1)
FROM master..sysprocesses WHERE dbid=db_id(@DBName)
END


The above script can be used in SQL Query Analyzer or in SQL Server Agent Job.

Note: The job can not kill its own connection, if you are using SQL Sever Query analyzer to kill connections to the database then try to issue a use statement against any other database on top of the query, else you will receive an error.

USE master
GO

Its always a good idea to notify users before killing the connection to database using a script, by restarting the computer or any other way.

DONT KILL CONNECTIONS LIKE THIS :
Wrong way of killing the connections to database is by taking out the network wire, or by disabling network devices.