SQL – How To get The Current Date Using GetDate()

The getDate() function is used to generate the current date.

print getDate()

This can be used with variables to create the date format required. The following code will set the string equivalent of the date format DD/MM/YY to the variable @date.

declare @date as nvarchar(50)
declare @day as nvarchar(50)
declare @month as nvarchar(50)
declare @year as nvarchar(50)
declare @zeroday as nvarchar(50)
declare @zeromonth as nvarchar(50)

– SET THE DATE
set @date         = cast(getDate() as char)
set @day          = day(@date)
if @day < 10
      set @zeroday = 0
else
      set @zeroday = ”
set @month  = month(@date)
if @month < 10
      set @zeromonth = 0
else
      set @zeromonth = ”
set @year         = year(@date)
set @date   = @zeroday + @day + ‘/’ + @zeromonth + @month + ‘/’ + right(@year,2)

print @date

To get yesterday’s date replace this line

set @date         = cast(getDate() as char)

with:

set @date         = cast(getDate() – 1 as char)

To get the date 1 week ago, use:

set @date         = cast(getDate() – 7  as char)

Windows Server 2003 – How to Install IIS on Windows Server 2003

The followig method shows how to install IIS on Windows Server 2003.
You will need the installation source available.

1.Click Start, point to Control Panel, and then click Add or Remove Programs.

2.In Add or Remove Programs, click Add/Remove Windows Components.

3.In the Windows Components Wizard, under Components, select Application Server.

4.Click Next.

5.After the wizard completes the installation, click Finish.

SQL – How To Avoid Divide By Zero Error

When you try to divide an integer (or float etc.) by a zero value in SQL it throws a Divide by zero error.

This example illustrates the error:

declare @intA as int
declare @intB as int

set @intA = 50
set @intb = 0

Select @intA / @intB

Message:

Msg 8134, Level 16, State 1, Line 7
Divide by zero error encountered.

The NULLIF statement can be used here to avoid the devide by zero error and return a null value instead.

declare @intA as int
declare @intB as int

set @intA = 50
set @intb = 0

Select @intA / nullif(@intB, 0)

 

SQL – Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CI_AS”

This error occurs when trying to join two databases that have different collation.

you can change the collation of a database using the method listed here:
http://robbamforth.wordpress.com/2009/11/13/sql-%e2%80%93-change-the-collation-of-a-database/

If changing the collation of the database isn’t an option then you can force a collation by specifying it in the SLQ statement.

The following code would use the default collation:                    

–  SELECT
select a.*, b.*
from [DATABASE1] as a
join [DATABASE2] as b
on a.field1 = b.field1 
and a.field2 = b.field2

– UPDATE
update [DATABASE1]
set FIELD = B.FIELD
from [DATABASE1] as a
join [DATABASE2] as b
on a.field1 = b.field1 
and a.field2 = b.field2

The following will force SQL_Latin1_General_CP1_CI_AI collation:

–  SELECT
select a.*, b.*
from [DATABASE1] as a
join [DATABASE2] as b
on a.field1 = b.field1 COLLATE SQL_Latin1_General_CP1_CI_AI
and a.field2 = b.field2 COLLATE SQL_Latin1_General_CP1_CI_AI

– UPDATE
update [DATABASE1]
      set FIELD = B.FIELD
from [DATABASE1] as a
join [DATABASE2] as b
on a.field1 = b.field1 COLLATE SQL_Latin1_General_CP1_CI_AI
and a.field2 = b.field2 COLLATE SQL_Latin1_General_CP1_CI_AI

 

SQL – How To Check If A String Contains Numbers

Use the PATINDEX function to check for an integer value continued in the string.

If PATINDEX returns a value greater than 0 then the string does contain an integer, you can use PATINDEX to determine the position of the integer within the string.

If PATINDEX is not greater than zero then the string does not contain an integer value.

DECLARE @string varchar(50)
SET @string = ‘this is a string with numb3rs in it’

IF PATINDEX(‘%[0-9]%’,@string) > 0 
      PRINT ‘YES, The string has contains the number ‘ +
                  substring(@string,PATINDEX(‘%[0-9]%’,@string),1) +
                  ‘ at position ‘ +
                  cast(PATINDEX(‘%[0-9]%’,@string) as char)
ELSE 
      PRINT ‘NO, The string does not have numbers’

SQL – How to restore a password protected database back up

If you have backed-up a database with password protection such as:

BACKUP DATABASE [DATABASE] TO  DISK = N‘\\NETWORK_STARAGE_LOCATION\database.BAK’ WITH PASSWORD = ‘PASSWORD’,
NOFORMAT, NOINIT,  NAME = N‘DATABASE-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS=10
GO

As outlined in a previous post http://robbamforth.wordpress.com/2008/11/07/sql-server-2005-database-backup/

Then you will be required to restore the database programmatically as both Enterprise Manger and SQL Server Management Studio do not support the password option when restoring with the GUI wizard.

To view the files available in the backup:

RESTORE FILELISTONLY FROM DISK='\\NETWORK_STARAGE_LOCATION\database.BAK' 
WITH PASSWORD = 'password'

To run a full simple backup:

RESTORE DATABASE Northwind FROM DISK='\\NETWORK_STARAGE_LOCATION\database.BAK’
WITH PASSWORD = 'password'

 

This post shows how to restore a backup to a different location to where it was taken: http://robbamforth.wordpress.com/2009/11/18/sql-%e2%80%93-how-to-restore-a-database-backup-to-a-different-location/

SQL – How to restore a database backup to a different location

You may be required to restore a database backup to a location different from the location of the original backup. If you backed up with a password then this will need to be done programmatically.

The following example shows how to use a backup taken from a database called Database and restore it to a new location on a new server with the name Database2.

RESTORE DATABASE [NEW_DATABASE]
FROM  DISK = N’\\NETWORK_STARAGE_LOCATION\database.BAK’
WITH  password = ‘password’,
FILE = 1, 
MOVE N’database’ TO N’C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\database2.mdf’, 
MOVE N’database_log’ TO N’C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\database2.log, 
NOUNLOAD,  STATS = 10
GO

 

 

SQL – What version of SQL 2005 server am I running?

You can check the version of SQL server running on a machine using the following command in MSQL server management studio:

select @@version

To determine the product version (for example, 9.00.1399.06), the product level (for example, RTM) and the edition (for example, Enterprise Edition) run the following:

SELECT  SERVERPROPERTY(‘productversion’), SERVERPROPERTY (‘productlevel’), SERVERPROPERTY (‘edition’)

SQL – How To Shrink Un-shrinkable Database Log / Database Log Won’t Shrink

The following should only be considered as a last resort to force the log file to shrink. I suggest you take a full database back-up first.

The DBCC LOGINFO can be run against the database. This will give you information about your virtual logs inside your transaction log.
In particular look at the Status column. Status = 2 indicates the portions of the log that are in use, status = 0 are not in use.

DBCC LOGINFO (DATABASE_NAME)

Step 1:
Attemp to shrink the problematic log file and back up the database with TRUNCATE_ONLY.

USE [DATABASE_NAME]
GO
DBCC SHRINKFILE (N’DATABASE_NAME’ , 0, TRUNCATEONLY)
GO

BACKUP LOG [DATABASE_NAME]
WITH TRUNCATE_ONLY

Step 2:
Create a temporary table and insert a record.

CREATE TABLE MyTable (MyField VARCHAR(10), PK INT )
INSERT Mytable (PK) VALUES (1)
GO

Step 3:
Run the following loop to update the table.

SET NOCOUNT ON
DECLARE @Index INT
SELECT @Index = 0
WHILE (@Index < 20000)
BEGIN
   UPDATE MyTable SET MyField = MyField WHERE PK = 1
   SELECT @Index = @Index + 1
END
SET NOCOUNT OFF

The log rows with status 2 should now be available with statis = 0.

Step 4:
Re-run the shrink command.

USE [DATABASE_NAME]
GO
DBCC SHRINKFILE (N’DATABASE_NAME’ , 0, TRUNCATEONLY)
GO

BACKUP LOG [DATABASE_NAME]
WITH TRUNCATE_ONLY

– Drop the temporary table as no longer required
drop table MyTable

The log file should now have shrunk successfully.

Source: http://www.broad-lea.com/sql_server/sql_reduce_log_size.html

SQL – Server blocked access to procedure ’sys.sp_OACreate’

 

A user logged in with administrator privileges can enable the use of ‘Ole Automation Procedures’ b with sp_configure .
Advanced options need to be set before running sp_configure.

sp_configure ’show advanced options’, 1;
GO
RECONFIGURE;
GO
sp_configure ‘Ole Automation Procedures’, 1;
GO
RECONFIGURE;
GO

You should see the following output:

Configuration option ’show advanced options’ changed from 0 to 1. Run the RECONFIGURE statement to install.
Configuration option ‘Ole Automation Procedures’ changed from 0 to 1. Run the RECONFIGURE statement to install

Alternatively this can be done in SQL server 2005 Surface Area Configuration through the menu options :

surface area configuration for features -> OLE Automation -> Enable OLE automation

You will now be able to re-run the previously blocked procedure with success.