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.

SQL Server Table Level Backup

BACKUP DATABASE does not support table level backup.
1.you can use use SQL Server Management Studio to script it out.
right clicking Database > Tasks > Generate Script
2.you can use export function to export it into csv file
3.you can use bcp to do similar thing

Calculation of 2's Complement

Note:
To calculate the 2's complement of an integer, invert the binary equivalent of the number by changing all of the ones to zeroes and all of the zeroes to ones (also called 1's complement), and then add one.

For example,
0001 0001(binary 17) ==> such that 1110 1111(two's complement -17)

1. NOT(0001 0001) = 1110 1110 (Invert bits)
2. 1110 1110 + 0000 0001 = 1110 1111 (Add 1)

Nesting Transactions in T-SQL

it's primarily intended to support transactions in stored procedures that can be called either from a process already in a transaction or processes that have no active transaction.

"Commit" statement won't take effect in an inner transaction,"rollback" statement does rollback all the changes made either by itself or the outer transaction. "rollback transaction savepoint" is a better choice to use to rollback only the changes made by the stored procedure.

an impression, in T-SQL, savepoint is for rolling back purpose. transaction name is for commit purpose.

if a stored procedure is called when a transaction is active, the nested transaction in it is largely ignored. the transaction will be governed by the out transaction. if it's executed by a process that does not have an active transaction, the commit statement in the stored procedure will effectively commit the changes to database.

a rollback statement will rollback everything, wherever the place it's executed. the @@trancount will be reset to 0. to be safe, it should check the @@trancount before issuing the "commit transaction" or "rollback " statements.

in a stored procedure, using the "save transaction savepointname" is a better way to rollback the changes made by the stored procedure.

sql server automatically starts a implicit transaction when enters in a trigger.

updates after a rollback statement will still be stored into database.

the following code will raise error since the second "rollback transaction" is executed without a transaction content.

BEGIN TRANSACTION
BEGIN TRANSACTION
-– Some Codes
ROLLBACK TRANSACTION
ROLLBACK TRANSACTION