Looping through a Resultset in SSIS

1. create an Execute SQL task, set its ResultSet to be Full result set. In the resultSet, define the result name as 0. variable name is type of Object.
2.create a foreach loop container, define its collection to be "Foreach ADO enumerator", and specify the ADO object source variable to be the one defined in step 1.
3.in the variable mappings, define the user variable to receive value for each column. each column is defined by sequence from 0.

check processes in SQL Server

sp_who2 active
--use dbcc to check the query text
dbcc inputbuffer (59)

Detect blocking processes in SQL Server

this is for SQL Server 2005. in SQL Serer 2008, you have to replace sys.sysprocesses to be dbo.sysprocesses in master database.

blocking is not deadlock.

First query, it retrieves the sql statements by using cross apply. output is put into temporary table for further analysis.

SELECT
s.spid, s.blocked BlockingSPID, DB_NAME(s.dbid) DatabaseName ,
s.program_name, s.loginame, OBJECT_NAME(objectid, s.dbid) ObjectName, CAST(text AS VARCHAR(MAX)) Definition
INTO #Processes
FROM sys.sysprocesses s
CROSS APPLY sys.dm_exec_sql_text (sql_handle)
WHERE
s.spid > 50

WITH Blocking(SPID, BlockingSPID, BlockingStatement, RowNo, LevelRow)
AS
(
SELECT
s.SPID, s.BlockingSPID, s.Definition,
ROW_NUMBER() OVER(ORDER BY s.SPID),
0 AS LevelRow
FROM
#Processes s
JOIN #Processes s1 ON s.SPID = s1.BlockingSPID
WHERE
s.BlockingSPID = 0
UNION ALL
SELECT
r.SPID, r.BlockingSPID, r.Definition,
d.RowNo,
d.LevelRow + 1
FROM
#Processes r
JOIN Blocking d ON r.BlockingSPID = d.SPID
WHERE
r.BlockingSPID > 0
)
SELECT * FROM Blocking
ORDER BY RowNo, LevelRow

Manipulating database mail profile

here are samples I created for removing and creating database mail profile and account


if exists(select 1 from msdb.dbo.sysmail_principalprofile pp join msdb.dbo.sysmail_profile p on pp.profile_id=p.profile_id where p.name=N'SQLNotification-NDS')
begin
EXEC msdb.dbo.sysmail_delete_principalprofile_sp @principal_name = N'guest' , @profile_name = N'SQLNotification-NDS'
end
if exists(select 1 from msdb.dbo.sysmail_account where name=N'SQLMailACCT')
begin
EXEC msdb.dbo.sysmail_delete_account_sp @account_name =N'SQLMailACCT'
end
if exists(select 1 from msdb.dbo.sysmail_profile where name=N'SQLNotification-NDS')
begin
EXEC msdb.dbo.sysmail_delete_profile_sp @profile_name = N'SQLNotification-NDS'
end
go
EXEC msdb.dbo.sysmail_add_account_sp @account_name = N'SQLMailACCT',
@email_address = N'doNotReply@rocketGaming.com' ,
@display_name = N'doNotReply@rocketGaming.com' ,
@description = N'account used for sending out email from SQLServer' ,
@mailserver_name = N'10.0.0.111' , @mailserver_type = N'SMTP' , @port = 25
EXEC msdb.dbo.sysmail_add_profile_sp @profile_name=N'SQLNotification-NDS', @description=N'DB mail profile'
EXEC msdb.dbo.sysmail_add_profileaccount_sp @profile_name=N'SQLNotification-NDS', @account_name=N'SQLMailACCT', @sequence_number=1
EXEC msdb.dbo.sysmail_add_principalprofile_sp @principal_name=N'guest', @profile_name=N'SQLNotification-NDS', @is_default=0
GO

Deploying SQL Server Report through VS

1. choose proper configuraton
2. check project property, make sure TargetDataSourceFolder, TargetReportFolder and TargetServerURL are correct
3.deploy solution

that's it

SQL Server Version and Internal Database Version Number

Copied from web source.

Checking the sql server installed.

Select @@version

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

Compatibility Level

The currently available compatibility levels are:
60 = SQL 6.0
65 = SQL 6.5
70 = SQL 7.0
80 = SQL 2000
90 = SQL 2005
100 = SQL 2008

Internal Database Version

The internal database versions for SQL aren't documented in the Books Online.However, thanks to Paul Randal (@PaulRandal) and his Q&A article in TechNet we have the following list:
SQL Server 7.0 databases have version number 515
SQL Server 2000 databases have version number 539
SQL Server 2005 databases have version number 611/612
SQL Server 2008 databases have version number 655

Database information can be viewed by these means:

1. via dbcc
DBCC TRACEON (3604);
GO
DBCC DBINFO
GO
DBCC TRACEOFF (3604);
or
You can also read the page header by using DBCC PAGE: DBCC TRACEON (3604);
GO
DBCC PAGE (ExampleDB, 1, 9, 3);
GO
DBCC TRACEOFF (3604);

2. viaDATABASEPROPERTY() , DATABASEPROPERTYEXfunction
select DATABASEPROPERTY('db','version')

Backup File Information
Backup file information can be viewed by restore command.

restore labelonly from disk=N'db.bak'
restore headeronly from disk=N'db.bak'
restore filelistonly from disk=N'db.bak'

Restore SQL Server database

I will update this gradually.

Senario 1, restore full database from a backup file.

1. check the file list in the backup file
restore filelistonly from disk=N'backupfile'

Note: checking what're in the backup file
--check the version of sql server that makes the backup, using this command:
restore headeronly from disk=N'backupfile'
--check media information
restore labelonly form ...

2. restore the database with move xx to

restore database ppc_db
from disk=N'xxx.bak'
with move 'xxx_dat' to N'xxx.mdf',
move 'xxx_log' to N'xxx_log.ldf'


MISC:
==view schema and its owners
select * from sys.schemas where name='xxx'

==view objects in a schema
select * from sys.objects o join sys.schemas s on o.schema_id=s.schema_id where s.name='aSchema'

==view database options:
sp_dboption db

==view database users
select * from sys.database_principals where type='S'

==take a look between login and db users
select l.loginname as [login name],u.name as [DB user name]
from sys.database_principals u full join master..syslogins l
on u.sid=l.sid
where u.type ='S' or u.type is null

==view database user's permissions assigned directly

select class_desc,u.name userName,permission_name,OBJECT_NAME(major_id) as objectName
from sys.database_permissions p join sys.database_principals u
on p.grantee_principal_id=u.principal_id
where u.type='S'
order by u.name,p.class

==generate scripts that grant the permissions according to the permission assigned to db users

select N'grant '+convert(nvarchar,p.permission_name)+case when OBJECT_NAME(major_id)is null then '' else ' on '+convert(nvarchar,OBJECT_NAME(major_id)) end+N' to '+convert(nvarchar,u.name) COLLATE Latin1_General_CI_AS
from sys.database_permissions p join sys.database_principals u
on p.grantee_principal_id=u.principal_id
where u.type='S'
order by u.name,p.class

==list all the jobs. copied from Internet.

select job.name, case job.enabled when 1 then 'Enabled' else 'Disabled' end [Enabled],
stp.step_name, stp.subsystem, stp.database_name,
ss.name as scheduleName,
case ss.freq_type when 1 then 'One Time'
when 4 then 'Daily'
when 8 then 'Weekly'
when 16 then 'Monthly'
when 32 then 'Monthly - relative'
when 64 then 'When Agent Starts'
when 128 then 'When Computer is idle' else 'Invalid' end Freq_Type,
stp.command
from msdb..sysjobs job left outer join msdb..sysjobsteps stp on (job.job_id = stp.job_id )
left outer join msdb..sysjobschedules sjc on ( job.job_id = sjc.job_id )
left outer join msdb..sysschedules ss on (sjc.schedule_id = ss.schedule_id)