Archive for April, 2010

« Older Entries

Previous Working Day Function

Tuesday, April 27th, 2010

I have been asked to create a function which works out the previous working day.

The logic for this function is as follows:

If the current day is a sunday or Monday then pick the previous friday
Else pick the previous day.

I was then able to get the current day using DatePart(weekday,{CurrentDate}) and create the following function:

CREATE FUNCTION [dbo].[FUN_Previous_Working_Day] (@CurrentDate DATETIME)

RETURNS DATETIME
AS
BEGIN
DECLARE @PreviousDate DATETIME
DECLARE @Day INT
SET  @Day = DATEPART(weekday,@CurrentDate)

IF  @Day = 7
SET  @PreviousDate = DATEADD(d,-2,@CurrentDate)
ELSE
IF  @Day = 1
SET  @PreviousDate = DATEADD(d,-3,@CurrentDate)
ELSE
SET  @PreviousDate = DATEADD(d,-1,@CurrentDate)

RETURN @PreviousDate
END
GO

Posted in Functions | No Comments »

Tables without Indexes

Wednesday, April 21st, 2010

Indexes are one of the most important factors in determining query performance. Luckily the code for determining which tables lack a valid index is relatively simple:

SELECT   SCHEMA_NAME(schema_id) AS Schema,
  name AS Table
FROM   sys.tables
WHERE   OBJECTPROPERTY(OBJECT_ID,’IsIndexed’) = 0
ORDER BY schema_name, table_name;
GO

Posted in Uncategorized | No Comments »

Filtered Index

Tuesday, April 20th, 2010

SQL Server 2008 introduced a lot of new features one of which is filtered indexes. Filtered indexes can be used as an alternative to indexed views and partitioning to improve data retrieval performance on large tables.

A filtered index is a non-clustered index with a where clause. The index will only contain the rows which satisfy the where clause. This means the index will require less storage space and have better performance.

To create the filtered index the syntax is relatively simple:

CREATE NONCLUSTERED INDEX {INDEX_NAME}
ON {TABLE}({FIELD})
WHERE {FIELD} — Insert Where criteria here.
GO

The query optimiser should pick the best index to use however you can use a hint to force the query optimiser to use the filtered index:

SELECT * FROM {TABLE} WITH(INDEX({INDEX_NAME}))
WHERE {FIELD}

While they give a performance boost there are two main limitations with using a filtered index. Firstly they are only created on a single table. Second, complex criteria like the LIKE clause are not allowed.

Posted in Query Optimisation | No Comments »

From Queries to Stored Procedures

Monday, April 19th, 2010

Basic Stored Procedures are incredibly simple.

If you write the following query:

SELECT Column1, Column2 FROM Table1

The equivilent stored procedure would be:

CREATE PROCEDURE StoredProcedure
AS
SELECT Column1, Column2 FROM Table1
GO

To call it simply type:

EXEC dbo.StoredProcedure

And execute.

Posted in Beginning SQL Server | No Comments »

Copy a View into a Table

Monday, April 19th, 2010

A simple way to copy data from a view to a table:

SELECT * INTO {Table} FROM {View}

Posted in Uncategorized | No Comments »

Every other page is blank

Monday, April 19th, 2010

I am frequently asked the question “Why does a blank page get inserted every other page in an SSRS report?”

The solution is usually simple. The Body of your report could be too large for your page. Simply make sure that the Body of your report added to the left and right margin is less than the page width.

Usually it will be the margins that push you over the limit.

Posted in Errors, SSRS | No Comments »

Configure an SSIS package to run through SQL Server Agent

Thursday, April 15th, 2010

I recently created a SSIS package which copied data from an Oracle DB to a SQL Server Database. The pacage I created using BIDS worked fine when executed by me but wouldn’t run via agent.

A quick search located this knowledge base article. http://support.microsoft.com/kb/918760.

This entry focuses method 4, my prefered method.

Use SSIS Package Configuration Files.

The default security setting when a package is created is to save passwords and other sensitive information within the package with a user key. This means only the account that created the package can access and run it. This is not very helpful when you need the SQL Server Agent Account to run the package. To solve this we will create an XML file which stores the password and can be accessed by the package by anyone. This raises fresh security concerns which I solved by placing the configuration file on the server.

To create the XML File:

1) load the package into the designer.
2) Double click on the package in the solution explorer pane.
3) Click on Add…
4) Select XML Configuration File as Configuration Type.
5) Select specify Configuration Settings directly.
6) Click on Browse.
7) Select where you want to save your file and name it eg Config.dtsConfig.
8 ) Click next.
9) Expand Connection Managers -> Oracle Connection -> Properties.
10) Select Password.
11) Click next.
12) Change the configuration name.
13) Click Finish and close.
Now navigate to package properties and change the ProtectionLevel to DontSaveSensitive. This tells the package that the password will be recieved from the XML file and won’t be saved with the package.

Navigate to the xml file and open it up with the XML editor of your choice (I prefer notepad).

To run the package from SQL Server Agent:

1) Open SQL Server Management Studio.
2) Expand the Server.
3) Expand SQL Server Agent.
4) Right click on jobs and click on New Job.
5) Create a Step.
6) Call the step something meaningful.
7) Select File System as Package Source.
8 ) Under the Package tab search for the package file.
9) Click OK

When the job is executed it will retrieve the Oracle Server Password from the XML File, connect and Execute.

Tags: ,
Posted in SSIS | No Comments »

Index Fragmentation Part 2

Thursday, April 15th, 2010

Further to my previous post Index Fragmentation you can use the following script to automatically rebuild and reorganize indexes.

I have it installed in an agent job that runs daily:

———————————————————————
– Rebuild the Indexes in EBS
———————————————————————

– Declare the Variables
DECLARE  @Table_ID  int,
   @Index_ID  int,
   @Pages   bigint,
   @Index_Name sysname,
   @Fragmentation float,
   @Table_Name sysname,
   @Alter   varchar(200)

– Declare the Cursor
DECLARE Indexes CURSOR FOR

SELECT  ID.object_id AS Table_ID
  ,ID.index_id AS Index_ID
  ,ID.page_count AS Pages
  ,I.name AS Index_Name
  ,ID.Avg_Fragmentation_In_Percent AS Fragmentation
  ,T.name AS Table_Name

FROM  sys.dm_db_index_physical_stats (db_id(), NULL,NULL, NULL, NULL) AS ID
JOIN  sys.indexes AS I WITH(NOLOCK)
ON   ID.Object_id = I.Object_id
 AND   ID.Index_id = I.Index_id
JOIN sys.objects AS T
ON  T.object_id = I.object_id
WHERE  I.name IS NOT NULL

OPEN Indexes
FETCH Indexes INTO @Table_ID,
     @Index_ID,
     @Pages,
     @Index_Name,
     @Fragmentation,
     @Table_Name 

– Start the Processing Loop
WHILE @@Fetch_Status = 0
   BEGIN

 IF @Fragmentation >30
 BEGIN
  set @alter = ‘ALTER INDEX ‘ + @Index_Name + ‘ ON ‘ + @Table_Name + ‘ REBUILD’;
  exec(@alter);
 END
 ELSE IF @Fragmentation >10
 BEGIN
  set @alter = ‘ALTER INDEX ‘ + @Index_Name + ‘ ON ‘ + @Table_Name + ‘ REORGANIZE’;
  exec(@alter);
 END

 – Perform Row-by-row Processing.
 – Get the Next Row
 FETCH Indexes INTO @Table_ID,
      @Index_ID,
      @Pages,
      @Index_Name,
      @Fragmentation,
      @Table_Name
 END

CLOSE Indexes

DEALLOCATE Indexes

RETURN

Posted in DBA, Database Optimisation | No Comments »

Case Manipulation

Thursday, April 15th, 2010

When printing off address lables I noticed the title field was all different cases: MR mr Mr and mR abounded.

This doesnt look very professional on letters!

Microsoft provides Ucase to make a text field upper case and Lcase to make it lower case. Its easy to combine the two to make a field have capital first letters and lower case for the remainder:

Ucase(left({Field},1)) & Lcase(right({Field},len({Field})-1))

Posted in Strings | No Comments »

Parameters in openqueries.

Thursday, April 15th, 2010

Using OpenQueries you can execute a pass-through query to a linked server within SQL Server. The Syntax for an open query is relatively simple:

Select * from openquery({SERVER_NAME}, ‘SELECT {FIELD} FROM {TABLE}’)

This can be useful however OpenQueries dont accept parameters. The way round this is to put the parameters outside the string. For example:

SELECT QUERY.* FROM OPENQUERY({SERVER_NAME}, ‘SELECT {TABLE}.{ROW} AS {R1} FROM {TABLE}’) AS QUERY<br />
WHERE QUERY.{R1} = {@Variable}

Note: Its best practice to use aliases for the openquery.

Posted in Workarounds | No Comments »

« Older Entries