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)

list database users in sql server database

it can be found from sys.database_principals or sysusers table.

one example to build SQL by using it.

select 'sp_change_users_login ''auto_fix'','+name+',null,null' from sys.database_principals where type='S' and default_schema_name not in ('dbo','guest') and default_schema_name is not null

import mysql dump file with double quoted identifer

not completed, seems not working....

if the file is small, just edit the dump file by adding a statement that changes session's sql mode to support double quoted identifiers.

SET @@SESSION.sql_mode='ansi';

if the file is large and you can't open to edit it, the sql mode can be retrieved and then be set to ansi. after the importing, the sql mode need to be set back to its original value.

mysql -u -p --execute "SELECT @@GLOBAL.sql_mode;"
mysql -u -p --execute "SET @@GLOBAL.sql_mode='ansi';"
mysql -u -p db

T-SQL error handling

An easy solution?

SET XACT_ABORT ON

SQL Server automatically rolls back the current transaction when a Transact-SQL statement raises a run-time error.

Copied from the Net to save typing.

Please avoid this style:

Begin transaction
Update…
Set…
Where…
Update…
Set…
Where…
Commit transaction

Because if errors occurred while processing the second UPDATE, SQL Server would still unconditionally commit the first (successful) UPDATE.

A more common way on error handling before try {}catch {} is introduced.
The idea is that all SQL statements inside a stored procedure should be covered with error-handling code. No transaction is considered here.

Declare @ErrorCode int Select @ErrorCode = @@Error If @ErrorCode = 0 Begin    --Some statement    Update …    Select @ErrorCode = @@Error End If @ErrorCode = 0 Begin    --Another statement    Insert …    Select @ErrorCode = @@Error End If @ErrorCode = 0 Begin    --Another statement    Update …    Select @ErrorCode = @@Error End
--this is a case of calling a stored procedure
If @ErrorCode = 0 Begin    execute @ErrorCode = MyStoredProcedure parm1, param2… End … Return @ErrorCode

There are cases when you wish to read the value of some other global variables immediately after the statement, and you should handle them with the same select statement that reads @@Error. For example, you often require something like this when you're using identity columns.

Insert … Select @id = @@identity,    @ErrorCode = @@Error

Work with Transaction

Transaction processing can be perfectly integrated with this solution. At the beginning of a stored procedure (or transaction), the developer should add the following:

Declare @TransactionCountOnEntry int
If @ErrorCode = 0
Begin  
Select @TransactionCountOnEntry = @@TranCount  
BEGIN TRANSACTION
End

At the end of the procedure/transaction, the developer should complete the transaction as follows:

If @@TranCount > @TransactionCountOnEntry
Begin  
If @ErrorCode = 0     
COMMIT TRANSACTION  
Else    
ROLLBACK TRANSACTION
End

The solution will also perform well in the case of nested stored procedures. All procedures will be rolled back using the same cascading mechanism. Here, the local variable @TransactionCountOnEntry is used to track the number of opened transactions upon the entry of a stored procedure. If the number was unaffected inside the stored procedure, there's no reason to either commit or rollback inside the procedure.