Restore Database Failed: Exclusive access could not be obtained because the database is in use

Solution 1: Set to single user, then restore
Alter Database [<Database Name>]
  SET SINGLE_USER With ROLLBACK IMMEDIATE

RESTORE DATABASE [<Database Name>] FROM  DISK = N'E:\Databases\SampleDatabase.bak' WITH  FILE = 1,  MOVE N'Tfs_Silverlight' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\SampleDatabase.mdf',  MOVE N'SampleDatabase_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\SampleDatabase.LDF',  NOUNLOAD,  REPLACE,  STATS = 10
GO

Solution 2: Restart SQL Server service
1. Start -> Run and type in services.msc
2. Right click on the SQL Server instance (MSSQLSERVER), click Stop and then Start the service
3. Do the restore process

SQL Server 2008: Saving Changes is not permitted

Scenario:

When you use Data Definition Language (DDL) to modify a table, and then you try to save the table in Microsoft SQL Server 2008, you may receive the following message:

Error:
Saving changes is not permitted. The changes that you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created.

Solution:
1. Go to: Tools > Options

2. Select the tab Designers and choose Tables and designers

3. Uncheck the option: "Prevent saving changes that require table re-creation".

4. Save, of course!

Basic template for Transactions in Sql Server

SET XACT_ABORT ON;

BEGIN TRY
    BEGIN TRANSACTION;

    -- Code goes here

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
     ROLLBACK TRANSACTION;

    DECLARE
     @ERROR_SEVERITY INT,
     @ERROR_STATE INT,
     @ERROR_NUMBER INT,
     @ERROR_LINE  INT,
     @ERROR_MESSAGE NVARCHAR(4000);

    SELECT
     @ERROR_SEVERITY = ERROR_SEVERITY(),
     @ERROR_STATE = ERROR_STATE(),
     @ERROR_NUMBER = ERROR_NUMBER(),
     @ERROR_LINE  = ERROR_LINE(),
     @ERROR_MESSAGE = ERROR_MESSAGE();

    RAISERROR('Msg %d, Line %d, :%s',
     @ERROR_SEVERITY,
     @ERROR_STATE,
     @ERROR_NUMBER,
     @ERROR_LINE,
     @ERROR_MESSAGE);
END CATCH

Source

Problem with Different TimeZones

* In progress...

Scenario:

The users are from different timezones e.g. Philippines, India, Redmond WA USA, Florida. A user input a specific schedule to run a task. The task should execute on his/her timezone.

The servers are in the US. SQL Server 2008 and SQL Azure store the user's time. A windows service installed in the server will execute the task. Currently, in the windows service, the scheduled date is being treated with same timezone as the server.

What.to.do?
1. Is there a way in which I can solve this without changing the database schema?
2. In what ways can I check the user's timezone against the server's time? Can the use of UTC solve this?
3. Or do I really need to update the database, add User's TimeZone column to the table for schedules?

Storing times

• Always store time in UTC

• Convert to local time on display (local being defined by the user looking at the data)

• When storing a timezone, you need the name, timestamp and the offset. This is required because governments sometimes change the meanings of their timezones (eg: the US govt changed DST dates), and your application needs to handle things gracefully... eg: The exact timestamp when episodes of LOST showed both before and after DST rules changed.

Possible Solution (Still needs of POC):

1. User to select date and time. Since currently, there is no option for selecting a time zone, automatically get user's timezone, then save in the database as UTC using DateTime.UTCNow (and Date.getTimeZoneOffset()?)

The problem with automatically getting the user's time zone is this scenario, what if the user wants to schedule a task in Philippine time but she is currently in Japan? The user would mentally/manually convert from PH time to JP time and then enter the date and time? 

2. Retrieve the UTC from the database, then convert to user's local time. How? Use TimeZoneInfo.ConvertTimeFromUtc 

http://stackoverflow.com/questions/3439468/utc-to-users-local-time

SOLUTION:
1. DateTime dateNow = DateTime.Now;
   Console.WriteLine("The date and time are {0} UTC.", TimeZoneInfo.ConvertTimeToUtc(dateNow));


2. TimeZoneInfo cstZone = TimeZoneInfo.FindSystemTimeZoneById("Central Standard Time"); DateTime cstTime = TimeZoneInfo.ConvertTimeFromUtc(timeUtc, cstZone);

Oh men, where should I get the user's current timezone?

Gotcha!

DateTime fromUtcToLocal = TimeZoneInfo.ConvertTimeFromUtc(utcDateTime, TimeZoneInfo.Local);
Console.WriteLine("Local Date and Time {0}:", fromUtcToLocal);

References:
The Authority - http://msdn.microsoft.com/en-us/library/bb397769.aspx
Closest to my requirement - http://stackoverflow.com/questions/832986/how-to-work-with-timezone-in-asp-net
Synchronizing Time Zones - http://stackoverflow.com/questions/179940/c-sharp-convert-utc-gmt-time-to-local-time
Links Worthy of Reading:

http://www.dotnetcurry.com/ShowArticle.aspx?ID=593
http://stackoverflow.com/questions/7577389/how-to-elegantly-deal-with-timezones
The Death of DateTime?
http://stackoverflow.com/questions/2532729/daylight-saving-time-and-timezone-best-practices
http://stackoverflow.com/questions/441109/get-datetime-for-another-time-zone-regardless-of-local-time-zone


Bonus:
If I want to list all timezones:
http://social.msdn.microsoft.com/Forums/en-US/csharpgeneral/thread/2ddc265b-434a-4c5d-be27-5d6a92e89070/
http://msdn.microsoft.com/en-us/library/bb397781.aspx

SQL Server Tip: Word Wrap in Sql Server Management Studio

I am having a hard time looking at the complex scripts I have copied from a file to the Sql Server Query Designer. Either I am scrolling all the way to the left or I'm finding (Ctrl + F) and hitting the Enter key when I've found the commas. Then, I remember word wrap! I know, I get silly sometimes.

So how do I set Word Wrap in SQL Server Mangement Studio? By going to the menu,

• Tools
• Options
• Text Editor
• All Languages
• Settings 
• Check Word Wrap

There you go! Simple but helpful!
By the way, I am using version 2008.

Stwaf! ^_^

Extract Numbers from String

Declare @s varchar(100),@result varchar(100)
set @s='lkjflkjd-sflsakjqj098yvak8751'
set @result=''
select @result=@result + (case when number like '[0-9]' then number else '' end)
from (select substring(@s,number,1) as number from (select number from master..spt_values where type='p' and number between 1 and len(@s)) as t) as t
select @result as only_numbers

Result:
0988751

determine the number of rows for each table in the db

-- i like this best
SELECT t.name, SUM(rows) AS Rows
FROM sys.tables t JOIN sys.partitions p ON t.object_id = p.object_id
WHERE p.index_id IN(0,1) and t.name like 'tbl%'
GROUP BY t.name


-- can be used but only 100 can be displayed
sp_msforeachtable 'select ''?'', count (*) from ?'

loop thru db tables, check if particular column exists, then update the existing column

declare @tables Table(
id int identity(1,1),
name varchar(200)
)
insert into @tables(name)
SELECT name FROM sysobjects WHERE type = 'U'
declare @sql_string nvarchar(4000)
declare @name varchar(200), @min int, @max int
select @min=1
select @max=MAX(id) from @tables
while(@min <= @max)
begin
select @name = name from @tables where id = @min
if (exists(select * from information_schema.columns where table_name = @name and (column_name='editBy')) and
exists(select * from information_schema.columns where table_name = @name and (column_name='editDate')))
begin
set @sql_string = 'Update ' + @name + ' SET editBy=''engine upgrade'', editDate=getdate()'
--print @name
exec sp_executesql @sql_string
end
set @min = @min + 1
end

List all stored procs in the database

SELECT * FROM sys.procedures

Note: it works only in SQL Server 2005 and above

Delete all data from the database

1. Run this script:
SELECT 'DELETE FROM ' + name FROM sysobjects WHERE type = 'U'

2. Copy the result in the query window; then, execute
DELETE FROM tblAccommodationProductFacilityCategory
DELETE FROM tblProductClass
DELETE FROM tblVehicleDriveType
DELETE FROM tblCommunicationType
DELETE FROM tblAccommodationProductFacility
DELETE FROM tblVehicleRadioType
DELETE FROM tblAccommodationProductFacilityDescription
DELETE FROM tblVehicleType
...

3. Repeat over and over again until there is no error remaining

Retrieve XML data via Sql Server

ALTER PROCEDURE [dbo].Select_AccommodationMediaTypeSupplierByIds (@idsXML varchar(5000))
AS
DECLARE @ids int
EXEC sp_xml_preparedocument @ids OUTPUT, @idsXML
SELECT DISTINCT SupplierAccommodationMedia.*
FROM SupplierAccommodationMedia
WHERE SupplierAccommodationMedia.accommodationMediaTypeId IN (SELECT * FROM OpenXml(@ids,'/ids/id') WITH (v INT))
EXEC sp_xml_removedocument @ids

More info:
http://dotnetslackers.com/articles/xml/Dynamic_XML_From_SQL_Server.aspx
http://msdn.microsoft.com/en-us/library/ms178088.aspx
http://msdn.microsoft.com/en-us/library/ms186918.aspx

Display schema and constraints

SELECT C.CONSTRAINT_NAME ,PK.TABLE_NAME as PK_TABLE_NAME , PK.COLUMN_NAME as PK_COLUMN_NAME
, FK.TABLE_NAME as FK_TABLE_NAME, FK.COLUMN_NAME as FK_COLUMN_NAME
FROM Information_Schema.REFERENTIAL_CONSTRAINTS C
JOIN Information_Schema.KEY_COLUMN_USAGE PK
ON PK.CONSTRAINT_NAME = C.UNIQUE_CONSTRAINT_NAME
JOIN Information_Schema.KEY_COLUMN_USAGE FK
ON FK.CONSTRAINT_NAME = C.CONSTRAINT_NAME
--WHERE (((PK.TABLE_NAME ='Customers') and (FK.TABLE_NAME ='Orders'))
--OR ((PK.TABLE_NAME ='Orders') and (FK.TABLE_NAME ='Customers')))
Select CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME from Information_Schema.KEY_COLUMN_USAGE where
TABLE_NAME = 'Orders'
Select CONSTRAINT_NAME, UNIQUE_CONSTRAINT_NAME from Information_Schema.REFERENTIAL_CONSTRAINTS
select * from INFORMATION_SCHEMA.Columns where Table_Name = 'Orders'

Display the description of tables and columns

select *
from sys.tables t
left outer join sys.extended_properties x on x.major_id = t.object_id
order by t.name