allow a user to view all stored proc code on the server

Problem: allow a user to view all stored proc code on the server but not change/create etc

Solution: grant view definition on database::database_name to database_user

Find missing dates

Problem: You wish to identify missing dates from a table which contains multiple objects
eg

Object,Date
1,Jan-01-2012
1,Jan-03-2012
2,Jan-02-2012
2,Jan-03-2012
2,Jan-04-2012

Solution:

You should have a calender table, for this example I will restrict the table to just 4 days

Date
Jan-01-2012
Jan-02-2012
Jan-03-2012
Jan-04-2012

The missing date output we want to achieve is

Object,Date
1,Jan-02-2012
1,Jan-04-2012
2,Jan-01-2012

a. Set up some data

declare @a table (f1 int, f2 datetime)
declare @b table (f2 datetime)

insert into @a VALUES (1,’2012-Jan-01′),(1,’2012-Jan-03′), (2,’2012-Jan-02′),(2,’2012-Jan-04′),(2,’2012-Jan-03′)
insert into @b VALUES (‘2012-Jan-01’),(‘2012-Jan-02’),(‘2012-Jan-03’),(‘2012-Jan-04’)

b. create a distinct list of objects

select * from
(select distinct a.f1
from @a a) a

c. add all dates from the calender table for each object

select * from
(select distinct a.f1
from @a a) a
cross join @b b

d. join on the table which contains your objects and dates

select * from
(select distinct a.f1
from @a a) a
cross join @b b
left join @a c on c.f1=a.f1 and c.f2 = b.f2

e. we only want the records where the c table is not matched

select * from
(select distinct a.f1
from @a a) a
cross join @b b
left join @a c on c.f1=a.f1 and c.f2 = b.f2
where c.f1 is null

f. tidy up the select

select a.f1,b.f2
from
(select distinct a.f1
from @a a) a
cross join @b b
left join @a c on c.f1=a.f1 and c.f2 = b.f2
where c.f1 is null

converting from float to varchar

problem: converting float to varchar results in values in scientific notation format

solution: cast value as numeric before casting to varchar

Splitting Tables across database files

Problem: Tables cannot be split across filegroups, can they split across database files?

Solution: Yes, script below

USE [master]
GO

–DROP DATABASE
IF EXISTS (SELECT name FROM sys.databases WHERE name = N’james’)
BEGIN
ALTER DATABASE james
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
DROP DATABASE [james]
END

GO
–CREATE DATABASE
CREATE DATABASE [james] ON PRIMARY
( NAME = N’james’, FILENAME = N’D:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\james.mdf’ , SIZE = 51200KB , MAXSIZE = 51200KB , FILEGROWTH = 51200KB ),
( NAME = N’james2′, FILENAME = N’D:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\james2.ndf’ , SIZE = 51200KB , MAXSIZE = 51200KB , FILEGROWTH = 51200KB ),
( NAME = N’james3′, FILENAME = N’D:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\james3.ndf’ , SIZE = 51200KB , MAXSIZE = 51200KB , FILEGROWTH = 51200KB )
LOG ON
( NAME = N’james_log’, FILENAME = N’E:\MSSQL10_50.MSSQLSERVER\MSSQL\Data\james_log.ldf’ , SIZE = 109568KB , MAXSIZE = 2048GB , FILEGROWTH = 102400KB )
GO
–LOAD DATA
USE james
GO

select *
into james.dbo.a
from Table between 100 and 150 MB

Keep graph and table on same page of SSRS report

Problem: Graph and Table need to be displayed on the same oage of a report but SSRS keeps on putting them on sperate pages

Solution: Draw a rectangle on the report and drag the graph and table onto the rectangle

Move database file

Problem: How to Move a database file

Solution:

4 step process

ALTER DATABASE database_name SET OFFLINE;
ALTER DATABASE database_name MODIFY FILE ( NAME = file_logical_name, FILENAME = ‘new_location’ );
ALTER DATABASE database_name SET ONLINE;

Check:

SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N’database_name’);