Bhutan ICT

If you are wondering how much SQL transaction is happening on your database, run the below query. DECLARE @First INT DECLARE @Second INT SELECT @First = cntr_value FROM sys.dm_os_performance_counters WHERE OBJECT_NAME = 'SQLServer:Databases' -- Change name of your server AND counter_name = '%Transactions/sec%' AND instance_name = '_Total'; -- Database name -- delay time WAITFOR DELAY '00:00:01' SELECT @Second = cntr_value FROM sys.dm_os_performance_counters WHERE OBJECT_NAME = 'SQLServer:Databases' -- Change name of your server AND counter_name = '%Transactions/sec%' AND instance_name = '_Total'; -- Database name SELECT (@Second - @First) 'TotalTransactions' GO If you are having some problem in execution, please confirm the OBJECT_NAME and instance_name by running select * FROM sys.dm_os_performance_counters . You can change the WAITFOR DELAY time to meet your time interval requirement. I have used curren...

STUFF What STUFF does is, it inserts a string for the given expression of string. At a give start_position, it will start to delete the character mentioned on the length_to_delete and insert the replacement word. STUFF(expression,start_position,length_to_delete,replace_with) eg. SELECT STUFF('Younten',5,3,'10') SELECT STUFF('Younten',5,0,'10') Output: Youn10 Youn10ten REPLACE What REPLACE does is, it will replace every character in the given string. REPLACE(expression,patter,replace_with) eg. SELECT REPLACE('Younten','Y','y') SELECT REPLACE('Younten','n','N') Output: younten YouNteN

To get all the table details of a specific database in MS SQL Server, use the following query. USE [DATABASE-NAME] SELECT * FROM INFORMATION_SCHEMA.COLUMNS After you run the query use can select individual fields as per your requirement. As of me my requirement is as follows. USE [DATABASE-NAME] SELECT TABLE_NAME, COLUMN_NAME,DATA_TYPE,IS_NULLABLE, ISNULL(COLUMN_DEFAULT,'') AS COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS ORDER BY TABLE_NAME To include the length of the column run the following script. USE [DATABSE-NAME] SELECT OBJECT_NAME(c.OBJECT_ID) TableName ,c.name AS ColumnName ,SCHEMA_NAME(t.schema_id) AS SchemaName ,t.name AS TypeName ,t.is_user_defined ,t.is_assembly_type ,c.max_length ,c.PRECISION ,c.scale FROM sys.columns AS c JOIN sys.types AS t ON c.user_type_id=t.user_type_id ORDER BY TableName;

Firstly we need to enable Database Mail feature in the server that has MS SQL Server installed. This can be done using GUI or TSQL. In this tutorial I will be demonstrating using TSQL, as I prefer it. Step 01 : Enable Database Mail feature in MS SQL. USE master go sp_configure 'show advanced options',1 go reconfigure with override go sp_configure 'Database Mail XPs',1 go reconfigure go Step 02 : Create database account. EXECUTE msdb.dbo.sysmail_add_account_sp @account_name = 'Admin', @description = 'Mail account for Database Mail', @email_address = 'me@example.com', @display_name = 'Younten Jamtsho', @username='me@example.com', @password='meeeeeeeeeeee', @mailserver_name = 'mail.example.com' Step 03 : Create a mail profile. EXECUTE msdb.dbo.sysmail_add_profile_sp @profile_name = 'AdminProfile', @description = 'Profile used for da...

Index Rebuild This process drops the existing Index and Recreates the index Rebuild all indexes for the respective table ALTER INDEX ALL ON REBUILD; Rebuild specific indexes for the specific table ALTER INDEX ON REBUILD; Index Reorganize This process physically reorganizes the leaf nodes of the index. Reorganize all indexes for the specified table ALTER INDEX ALL ON REORGANIZE; Reorganize a specific index ALTER INDEX ON REORGANIZE; Recommendation : Index should be rebuild when index fragmentation is great than 40%. Index should be reorganized when index fragmentation is between 10% to 40%. Index rebuilding process uses more CPU and it locks the database resources. SQL Server development version and Enterprise version has option ONLINE, which can be turned on when Index is rebuilt. ONLINE option will keep index available during the rebuilding.

Today i was working on an application. So thought of storing the session in sql server. I will be installing on my notebook, my notebook name is yj-NB. I have set sa user password as sa123 on database. Here are the steps to follow: Step 1: Here is the code to add in web.config ; <sessionState mode="SQLServer" sqlConnectionString="data source=yj-NB;UID=sa;PWD=sa123;" cookieless="false" timeout="20"/> You don’t have to mention database name. Step 2: Installing the Session State Database Using the Aspnet_regsql.exe Tool Go to command prompt Systemdrive\WINDOWS\Microsoft.NET\Framework\version\ aspnet_regsql.exe -S yj-NB -U sa -P sa123 -d ASPstate -ssadd -sstype c yj-NB: computer name sa123: sa password ASPstate: Database name that will be storing session

SQL Data Manipulation Language (DML) : DML is a syntax for executing queries and DML component of SQL comprises have four basic statements: SELECT - Retrieve rows from tables UPDATE - Modify the rows of tables DELETE - Remove rows from tables INSERT - Add new rows to tables. SQL Data Definition Language (DDL) : DDL is used to create and destroy databases, database table and database objects. These commands will primarily be used by database administrators during the setup and removal phases of a database project. DML component of SQL comprises have four basic statements: CREATE - Creates a new database table ALTER - Alters / changes a database table DROP - delete a database table

Returns the number of rows affected by the last statement. It will let you to do a checking on the record you updated. If the number of rows is more than 2 billion, use ROWCOUNT_BIG. Example USE DB2008; GO UPDATE User SET JobTitle = 'Manager' WHERE UserID = 'u10021' IF @@ROWCOUNT = 0 PRINT 'Warning: No rows were updated'; GO Source: sqltutorials.blogspot.com

This SQL Split Function is use to SPLIT a sentences based on the Delimeter. Delimeter is a string character used to identify substring limits. --Below is Split Function in SQL DECLARE @NextString NVARCHAR(40) DECLARE @Pos INT DECLARE @NextPos INT DECLARE @String NVARCHAR(40) DECLARE @Delimiter NVARCHAR(40) SET @String ='Paro|Haa|Thimphu' SET @Delimiter = '|' SET @String = @String + @Delimiter SET @Pos = charindex(@Delimiter,@String) WHILE (@pos <> 0) BEGIN SET @NextString = substring(@String,1,@Pos - 1) SELECT @NextString -- Show Results SET @String = substring(@String,@pos+1,len(@String)) SET @pos = charindex(@Delimiter,@String) END Output : Paro Haa Thimphu Source: sqltutorials.blogspot.com

It is easy to query if the default value of the field is NULL, while in certain case when the field is empty. In such case execute the query like below. SELECT * FROM tblUser WHERE (LastName IS NULL) OR (RTRIM(LastName ) = '') Happy SQLing

In some cases you need to locate if there are duplicate record in a table and you are stuck how to go about, then here's how you can find it. SELECT cellno, COUNT(cellno) AS NumOccurrences FROM tblClientCellNumber GROUP BY cellno HAVING (COUNT(cellno) > 1)

I came across a problem where i deleted on record accidentally and that record id was in use. So I had to Enter that ID in Identity Column and rest of the data in respective fields of that record line. I could add values in other fields but could not enter in Identity Column. So I browsed for the information and this is what I got: SET IDENTITY_INSERT YourTableName ON INSERT INTO YourTableName(ID, FirstName, LastName) VALUES (18, 'Paul', 'Adams') GO SET IDENTITY_INSERT YourTableName OFF

Database size keeps on growing daily, and I wanted to know the size of my database. Finally i got a query that fulfills my requirement: SELECT sysDa.Name,sysDa.create_date,sysDa.recovery_model_desc, temp.DBSize8KBPage FROM ( SELECT sysMas.database_ID, sysMas.size, SUM(size) as DBSize8KBPage FROM sys.master_Files sysMas GROUP BY sysMas.DataBase_ID, sysMas.size ) temp INNER JOIN Sys.Databases sysDa on temp.Database_ID = sysDa.DataBase_ID

For a Stored Procedure to be eligible to be executed when SQL Server starts, the stored procedure must be in the "master" database and cannot contain INPUT or OUTPUT parameters. The sp_procoption system stored procedure is useful in setting the Stored Procedure for autoexecution - i.e it runs every time SQL Server service is started. Here's how to execute a Stored Procedure when SQL Server starts EXEC sp_procoption 'usp_SomeProcForStart', 'startup', 'true' To disable the stored procedure again EXEC sp_procoption 'usp_SomeProcForStart', 'startup', 'false' For a Stored Procedure to be eligible to be executed when SQL Server starts, the stored procedure must be in the ‘master’ database and cannot contain INPUT or OUTPUT parameters.

A very simple query but yet so useful in getting the information of tables size in your database: USE [yourdbname] EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"

Have you ever wondered how to check how many users are logged in to the SQL Server? You must have right' so here is the solution to find it out. Execute any one of the below query in your SQL Server. select * from sys.sysprocesses or EXEC sp_who or EXEC sp_who2 To know more about eh sysprocesses go to this link: http://msdn2.microsoft.com/en-us/library/ms179881.aspx You can also check the active connections for each Database in your SQL Server. To do so execute the following query. SELECT db_name(dbid) as DatabaseName, count(dbid) as NoOfConnections, loginame as LoginName FROM sys.sysprocesses WHERE dbid > 0 GROUP BY dbid, loginame

I had a rough time, open each stored procedure in my MS SQL Server 2005 and then looking for the text that i needed to change. Then i finally came to a very short query but yet so helpful: SELECT object_name(id) FROM syscomments WHERE text LIKE '%Text%'

In some cases, I have seen people trying to create a database server using an ordinary desktop computer. Doing so people have come across problem where application was unable to connect to the desktop where database is hosted. Now the simple solution to such problem is: 1. All Programs > Microsoft SQL Server 2005 > Configuration Tools > SQL Server Surface Area Configuration 2. Surface Area Configuration for Services and Connections 3. Under Database Engine select Remote Connections. Enable Local and remote connections and under that enable Using both TCP/IP and named pipes 4. Now restart all SQL Services

There are times when you need to copy all the content of one table to another for certain reason like backup or so. Doing manual method would take you longer time, hence to ease such task use the sql query to simplify your job: INSERT INTO new_table (field name1,field name2) SELECT field name1,field name2 FROM old_table

You need to enable the following features to make your MS SQL Server 2005 work with Vista/Windows 7 before installing MS SQl Server 2005. Here is the list of IIS 7 role services that need to be installed: Start 1. Control Panel 2. Programs and Features 3. Turn Windows features on or off 4. Enable Enable various features of IIS 1. Web management tools 1.1. IIS 6 Management Compatibility 1.1.1. IIS 6 WMI Compatibility 1.1.2. IIS Metabase and IIS 6 configuration compatibility 2. World Wide Web Services 2.1. Application Development Features 2.1.1. .NET Extensibility 2.1.2. ASP.NET 2.1.3. ISAPI Extensions 2.1.4. ISAPI Filters 2.2 Common Http Features 2.2.1. Default Document 2.2.2. Directory Browsing 2.2. 3. HTTP Redirection ...