error 5
RESTORE HEADERONLY

The error while very frustrating was easy to work around. I simply changed my logon to ‘Local System’ and was able to restore the backup.

To take you through the process step by step I:

1) Navigate to SQL Server Confiuration Manager

2) Right click on the instance you are using

3) Click on properties

4) Select thethe Logon Tab

5) Click on built in account

6) select ‘Local System’ from the drop down

7) Apply and ok your way out of the menus.

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

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

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.

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.

SELECT * INTO {Table} FROM {View}

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.

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.

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

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))