Asp.net

@@ERROR Returns the error number for the last Transact-SQL statement executed. UPDATE authors SET au_id = '172 32 1176' WHERE au_id = "172-32-1176" IF @@ERROR = 547 print "A check constraint violation occurred" @@ROWCOUNT Topic last updated -- July 2003 Returns the number of rows affected by the last statement. UPDATE FMP_tbluser SET username = username IF @@ROWCOUNT = 0 print 'Warning: No rows were updated' IDENT_CURRENT Returns the last identity value generated for a specified table in any session and any scope. IDENT_CURRENT is similar to the Microsoft® SQL Server™ 2000 identity functions SCOPE_IDENTITY and @@IDENTITY. All three functions return last-generated identity values. However, the scope and session on which 'last' is defined in each of these functions differ. IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope. @@IDENTITY returns the last identity value generated for an...

DATEADD Returns a new datetime value based on adding an interval to the specified date. Syntax DATEADD ( datepart , number, date ) datepart Is the parameter that specifies on which part of the date to return a new value. The table lists the dateparts and abbreviations recognized by Microsoft® SQL Server™. Datepart Abbreviations Year yy, yyyy quarter qq, q Month mm, m dayofyear dy, y Day dd, d Week wk, ww Hour hh minute mi, n second ss, s millisecond ms select dateadd(day,2,getdate()) as Date TodAY IS 29 AUGUST Date ---- select dateadd(month,2,getdate()) as Date Date ---- 2008-10-29 16:36:44.523 2008-08-31 16:35:19.477 Similarly Year,min,hours etc. CAST and CONVERT Explicitly converts an expression of one data type to another. CAST and CONVERT provide similar functionality. Syntax Using CAST: CAST ( expression AS data_type ) Using CONVERT: CONVERT ( data_type [ ( length ) ] , expression [ , style ] ) Arguments expression Is any valid Microsoft® SQL Server™ expression. For more informati...

select Ascii('a') 1 Program SET TEXTSIZE 0 SET NOCOUNT ON -- Create the variables for the current character string position -- and for the character string. DECLARE @position int, @string char(15) -- Initialize the variables. SET @position = 1 SET @string = 'Du monde entier' WHILE @position BEGIN SELECT ASCII(SUBSTRING(@string, @position, 1)), CHAR(ASCII(SUBSTRING(@string, @position, 1))) SET @position = @position + 1 END SET NOCOUNT OFF GO LEFT Returns the left part of a character string with the specified number of characters. Syntax LEFT ( character_expression , integer_expression ) SELECT LEFT(title, 5) FROM titles ORDER BY title_id LEN Returns the number of characters, rather than the number of bytes, of the given string expression, excluding trailing blanks. Syntax LEN ( string_expression ) Arguments string_expression Is the string expression to be evaluated. Return Types int Examples This example selects the number of characters and the data in...

http://www.sql-server-performance.com/articles/dba/email_functionality_p3.aspx E-Mail Functionality in SQL Server 2005 By : Dinesh Asanka Dec 21, 2006 Sending an e-mail has become very important in any system for purposes such as sending notifications. SQL Server database has an integrated mailing system. With the arrival of SQL Server 2005, users now have the new functionality of Database Mail, which is different from SQL Server 2000 SQL Mail. The purpose of this article is to introduce Database Mail and highlight the advantages of using it over legacy SQL Mail. Issues With SQL Mail If you have experience in SQL Server 2000 SQL Mail, you will know the headaches of SQL Mail. Personally, I have not used SQL Mail much recently due to the implementation difficulties. Outlook installations, Messaging Application Programming Interface (MAPI) profiles, third party Simple Mail Transfer Protocol (SMTP) connector, and extended stored procedures are all needed for SQL Mail. More importantly, SQ...

A. Back up the entire MyNwind database Note The MyNwind database is shown for illustration only. This example creates a logical backup device in which a full backup of the MyNwind database is placed. -- Create a logical backup device for the full MyNwind backup. USE master EXEC sp_addumpdevice 'disk', 'MyNwind_1', 'c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\MyNwind_1.dat' -- Back up the full MyNwind database. BACKUP DATABASE MyNwind TO MyNwind_1 B. Back up the database and log This example creates both a full database and log backup. The database is backed up to a logical backup device called MyNwind_2, and then the log is backed up to a logical backup device called MyNwindLog1. Note Creating a logical backup device needs to be done only once. -- Create the backup device for the full MyNwind backup. USE master EXEC sp_addumpdevice 'disk', 'MyNwind_2', 'c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\MyNwind_2.dat' --Create...

set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO /* ============================================= Author: Manpreet Create date: 25 Augest 2008 Description: This procedure is to send the mail to Super Admin for the Status of Each Admin ============================================= */ ALTER PROCEDURE [dbo].[Fmp_UspStatusMail_to_SA] as BEGIN Declare @tbl table ( Id int identity, UserName varchar(100), SpaceAssign decimal(18,2), SpaceUsed decimal(18,2) ) --if(@AdminId>0) --Begin --insert into @tbl --Select UserName, Convert(decimal(18,2),SpaceAssign) as SpaceAssign,Convert(decimal(18,2),SpaceUsed) as SpaceUsed from FMP_tbluser Where role='a' and UserId=@AdminId --End --Else --if (@AdminId=0) --Begin insert into @tbl Select UserName, Convert(decimal(18,2),SpaceAssign) as SpaceAssign,Convert(decimal(18,2),SpaceUsed) as SpaceUsed from FMP_tbluser Where role='a' --End Declare @Count as int Set @Count=(Select...