Convert Kilometers to Miles in TQSL

To convert Kilometeres to Miles in Tsql Try this

CAST(ISNULL(tblManifestos.km, 0) * 0.62137 AS decimal)

Multipy the kilometers by 0.62137

The to round it to the nerest mile cast as a decimal

Stored procedure skips date

If you are using date math in a stored procedure  like   datepart(dw,getdate())  which returns a  2 for Tuesday  make sure you set the Datefirst parameter to the first day of the week for the country you are in. set  DATEFIRST 1.  see here

Failure to do this could result in your date calculations not running properly, Tuesday may become 3. This is due to the fact that when run manually the calculation are run differently than when a Job is run from SQL server agent.

Can not alter table tsql

If you find the following does not work

Alter table Table_name modify COLUMN  column_name  varchar(101);

Then try

Alter table Table_name Alter COLUMN  column_name  varchar(101);

This works on sql server 2005


Related
 

Cannot Alter Table Being Used For Replication

Cannot Alter Table Being Used For Replication

If you have a table you wish to change that is being replicated you may have trouble changing it and get an error saying that Cannot alter the table 'Table Name' because it is being used for replication.

To get round this  I stopped the replication job then tried to change the table through management studio but this still failed.  I then opened a query window and tried to alter the table with a script 

ALTER TABLE table name ADD column_b VARCHAR(20) NULL ;

 and bingo it worked.

Restore MSSQL database to different location and name

Use this script to restore a SQL back up as in a different name and location


RESTORE DATABASE Database name.bak

FROM DISK = N'x:\Backup_name_backup_201408081200.bak'

WITH

NOREWIND,NOUNLOAD,STATS = 1 ,
move 'Database name'  to 'c:\New Database name.MDF',
move 'Database name'  to 'c:\New Database name.ndf',
move 'Database log file name'  to 'C:\log_files\New Database log file name.LDF'

GO

Check if a temporary table exist in as stored procedure

To check if a temporary table exist  in as stored procedure use the following

 if object_id(N'tempdb..#temp') is not null

Where #temp is your table

Then do something

if object_id(N'tempdb..#temp') is not null
 drop table #temp