Sunday 21 February 2010

How to schedule a SQL Job to run every second in SQL Server 2005

It is well known that it is not possible to schedule a SQL Job to run every second or some seconds in SQL Server 2005 by using the wizard (I mean less than one minute). Nevertheless, when there is a need to do it we may have one way to achieve it. Let me expand on what I am saying.  Firstly, we have to create the SQL Job in SQL Server 2008 by using the Wizard and then we need to generate the script to create the SQL Job in SQL Server 2005.

Having successfully completing that will you be able to have a SQL Job in SQL Server 2005 to run every second with any hitches. I hope you find this tip very useful for you. That is all for now. Thanks for reading. Stay tuned.

Saturday 16 February 2008

SQL Server 2008: Row Value Constructor

Definitely, we were sometimes in a need of a new technique to insert data massively in SQL Server 2005 and previous versions. We used to do it by using traditional techniques like UNION ALL and individual INSERT statements. This story has ended up with the arrival of a new feature in SQL Server 2008 which allows to insert much data with only one INSERT statement, that is, Row Value Constructor.

To begin with, we are going to illustrate this new feature by creating a new table where four data rows will be inserted.

CREATE TABLE [Production].[Document](
    [DocumentID] [int] IDENTITY(1,1) NOT NULL,
    [Title] [nvarchar](50) COLLATE Latin1_General_CS_AS NOT NULL,
    [FileName] [nvarchar](400) COLLATE Latin1_General_CS_AS NOT NULL,
    [FileExtension] [nvarchar](8) COLLATE Latin1_General_CS_AS NOT NULL,
    [Revision] [nchar](5) COLLATE Latin1_General_CS_AS NOT NULL,
    [ChangeNumber] [int] NOT NULL CONSTRAINT [DF_Document_ChangeNumber]  DEFAULT ((0)),
    [Status] [tinyint] NOT NULL,
    [DocumentSummary] [nvarchar](max) COLLATE Latin1_General_CS_AS NULL,
    
    [ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_Document_ModifiedDate]  DEFAULT (getdate()),
 CONSTRAINT [PK_Document_DocumentID] PRIMARY KEY CLUSTERED 
(
    [DocumentID] ASC
)
) ON [PRIMARY]

At first, we will do it in SQL Server 2005. This is the traditional and old-fashioned way (one INSERT statement per row): 

INSERT INTO [Production].[Document]([Title],[FileName],[FileExtension],[Revision],[ChangeNumber],[Status],[DocumentSummary])
VALUES ('Installing Replacement Pedals','C:DocumentsInstalling Replacement Pedals.doc','.doc','0',32,2,'Detailed instructions ...')

INSERT INTO [Production].[Document]([Title],[FileName],[FileExtension],[Revision],[ChangeNumber],[Status],[DocumentSummary])
VALUES ('Introduction 1','C:DocumentsIntroduction 1.doc','.doc','4',28,2,NULL)

INSERT INTO [Production].[Document]([Title],[FileName],[FileExtension],[Revision],[ChangeNumber],[Status],[DocumentSummary])
VALUES ('Lubrication Maintenance','C:DocumentsLubrication Maintenance.doc','.doc','2',11,1,'Guidelines and recommendations...')

INSERT INTO [Production].[Document]([Title],[FileName],[FileExtension],[Revision],[ChangeNumber],[Status],[DocumentSummary])
VALUES ('Seat Assembly','C:DocumentsSeat Assembly.doc','.doc','8',55,2,'Worn or damaged seats...')

This is the cumbersome way by using UNION ALL clause which has sometimes poor performance:

INSERT INTO [Production].[Document]([Title],[FileName],[FileExtension],[Revision],[ChangeNumber],[Status],[DocumentSummary])
SELECT 'Installing Replacement Pedals','C:DocumentsInstalling Replacement Pedals.doc','.doc','0',32,2,'Detailed instructions ...'
UNION ALL
SELECT 'Introduction 1','C:DocumentsIntroduction 1.doc','.doc','4',28,2,NULL
UNION ALL 
SELECT 'Lubrication Maintenance','C:DocumentsLubrication Maintenance.doc','.doc','2',11,1,'Guidelines and recommendations...'
UNION ALL
SELECT 'Seat Assembly','C:DocumentsSeat Assembly.doc','.doc','8',55,2,'Worn or damaged seats...'

Now in SQL Server 2008 we can use Row Value Constructor feature (only one INSERT statement to insert four data rows):

INSERT INTO [Production].[Document](  
      [Title]
      ,[FileName]
      ,[FileExtension]
      ,[Revision]
      ,[ChangeNumber]
      ,[Status]
      ,[DocumentSummary]
)
VALUES ('Installing Replacement Pedals','C:DocumentsInstalling Replacement Pedals.doc','.doc','0',32,2,'Detailed instructions ...'),
('Introduction 1','C:DocumentsIntroduction 1.doc','.doc','4',28,2,NULL),
('Lubrication Maintenance','C:DocumentsLubrication Maintenance.doc','.doc','2',11,1,'Guidelines and recommendations...'),
('Seat Assembly','C:DocumentsSeat Assembly.doc','.doc','8',55,2,'Worn or damaged seats...')

As you have noticed, we do not need to use UNION ALL any longer, it allow to save a lot of time and work. It is truly useful when we need to insert much data and it also offers a remarkable performance. I hope you enjoy this new feature. Thanks for reading again!

Saturday 5 August 2006

SQL Formatter Tool

Being asked sometimes about a good tool for formatting T-SQL code, I would like to share one of them known as 'SQL Formatter tool'. Not only will this tool format T-SQL code, but also MS ACCESS, ORACLE/PLSQL, DB2, and MySQL. The great thing of this tool is that we will be able to generate output results for HMTL, C#, VB, Cobol, PHP, Java, and others. Try using it to see if it works for you and then make the most out of it. Here is the link of the web version:

http://www.wangz.net/cgi-bin/pp/gsqlparser/sqlpp/sqlformat.tpl

Let me know any remarks or experience you may have using the tool. Thanks for reading. Stay tuned.

Sunday 21 May 2006

Installing SQL Server 2005 Service Pack 1 on all SQL instances via CMD

When it comes to installing SQL Server 2005 Service Packs on various instances rapidly, we can do it by executing the Service Pack setup with some parameters via CMD. First of all, we can use it to figure out which parameters we do have available to work with, so the following can be done:

D:\Downloads\SP1_SQL2005SQLServer2005SP1-KB913090-x86-ENU.exe /?

Having executed that via CMD, the complete list of parameters will be shown:

/? – Help
/quiet – Quiet mode
/reportonly -Instance report only
/allinstances – Apply patch all product instances by default
/instancename – Product instance names to patch
/SAPWD – SQL SA password
/use – Remote admin domain and account
/password – Remote admin password


Furthermore, if we are curious to know about how many SQL instances have the right service pack installed, we may use the '/reportonly' parameter:

D:\Downloads\SP1_SQL2005SQLServer2005SP1-KB913090-x86-ENU.exe /reportonly

Only after that will we be able to verify which client and server components are installed for each SQL Server 2005 instance like MSXML Parser version, Database Engine services, SSAS services, SSIS services, and so on. Now we are ready, for example, to install the SQL Server 2005 Service Pack 1 on SVPRDB:  

D:\Downloads\SP1_SQL2005SQLServer2005SP1-KB913090-x86-ENU.exe /instancename SVPRDB

Finally, we may want to install it on all SQL Server 2005 instances in the same server:

D:\Downloads\SP1_SQL2005SQLServer2005SP1-KB913090-x86-ENU.exe /allinstances

As you have been, using this technique is not complex. That is all for now, let me know any remarks you may have. Thanks for reading. Stay tuned.
HELLO, I'M PERCY REYES! — a book lover, healthy lifestyle lover... I've been working as a senior SQL Server Database Administrator (DBA) for over 20 years; I'm a three-time awarded Microsoft Data Platform MVP. I'm currently doing a PhD in Computer Science (cryptography) at Loughborough University, England — working on cryptographic Boolean functions, algorithmic cryptanalysis, number theory, and other algebraic aspects of cryptography. READ MORE