Skip to main content

Posts

Showing posts from May, 2011

Agent XPs Disabled - SQL Server

I notified so many times by the Object Explorer when I go to check the SQL Server Agent jobs. The notification is “ Agent XPs disabled ”. Then I need to run the following queries to enable SQL Server Agent XPs, EXEC SP_CONFIGURE 'show advanced options',1 GO RECONFIGURE GO EXEC SP_CONFIGURE 'Agent XPs',1 GO RECONFIGURE GO EXEC SP_CONFIGURE 'show advanced options',0 GO RECONFIGURE GO After running above script it will be enabled, I think you were getting this notification many times. So please share me your experience as “Why this SQL Agent XPs disabled sometime automatically after enabling it?”.

Code Snippets in SQL Server Denali

SQL Server Denali introduced one more feature which can easily and reduces our t-SQL coding. This feature is "Code Snippets". It allows to insert code template in the query editor. Here is some demonstration of this query editor new feature. When you clink on Insert Snippets or Ctrl + K, Ctrl + X, Let's double click on Index and an insert template of Create an Index. Go ahead, click on Create a non clustered index will generate the index template as following, CREATE UNIQUE NONCLUSTERED INDEX IX_TableName_Col1 ON dbo.TableName (column_1) I hope you liked this post. In the next post I will write how to create custom snippet.

Released a new version of the maintenance solution by Ola Hallengren - SQL Server

Ola Hallengren is a DBA in SQL Server and achieved MCITP Database Administrator and MCITP Database Developer certifications. In the new version optimized performance in IndexOptimize for databases with large number of indexes or sql servers with large number of databases. The time for rebuilding or reorganizing indexes is the same, but the time for investigating indexes has been reduced. Please find the Database Maintenance Solution script and download from here,  http://ola.hallengren.com/ scripts/MaintenanceSolution. sql This script have the Maintenance Solution for the database backups, Database Integrity Check, Index Optimization with Reorganize or Rebuilding. Also fixed an issue in Database Backup with databases that has very long database names (e.g. SharePoint databases). In the old version you could run into the limitation for the length of a backup device. You can have more updates from the details for these scripts and the changed introduced in version history update

HierarchyId data type overview - SQL Server 2008

With earlier version before 2008 of the SQL server, When we needed to required hierarchical data at that time we were using the loop to get the data of parent - child hierarchy. And it was made code long, very complex and costly as well. What should be other feature and alternative for above? It is HierarchyId data type, introduced by SQL Server 2008. It is a really amazing feature and very useful to avoid above such scenario. It is really used to reduce the code complexity and better performer. There are some introduction of HierarchyId data type and as following, 1. It’s a new CLR data type. 2. Stored as varbinary. 3. Can create an Index on that. Now there are some methods which can be used with this feature. 1. GetRoot() : Return root node of the hierarchy tree 2. ToString() :   To get string representation of the hierarchy node. 3. Parse() : Get conversation from string representation to hierarchy node. 4. GetLevel() : Get level  depth of hierarchy node. 5. Get

Execute with ResultSets and Performance Review - SQL Server 2011

I have wrote for the best feature of SQL Server 2011 in my earlier post here, http://paresh-sqldba.blogspot.com/2011/05/with-result-sets-tsql-enhancement-in.html . There i have given the demonstration of the usage of Resultset with execution of the stored procedures . Using this enhancement we don not require temp table and then insert the records from executing stored procedure in case when we mostly require change data and also change column name from resultset of stored procedure. Here i would like demonstrate the same but something different from earlier. In this demo i will explain the usage and performance review with new method of enhanced Resulset and old method using temp table to get the data by executing stored procedure. We need some demo script for table creation and get populated some data in table. USE DBWithResultSet GO -- Creating table IF( Object_id('WithResultSetTab') > 0 ) DROP TABLE WithResultSetTab GO CREATE TABLE WithResultSetTab (

Tech∙Ed on the Road in your City - Microsoft Tech∙Ed India 2011

I heard today from my MVP friend Jalpesh Vadgama as Microsoft is organizing one more event again in 2011 and it is in different different cities of India. It is very good news!.Do not miss this golden opportunity. I am have already registered. You can register with http://communitytechdays.com/. Please go and register or click on be below event details shots to redirect on it. We have good chance again to meet the latest technologies and the perople who worked on that. This Tech∙Ed Event India 2011 will be conducted in my city - Ahmedabad on 11th June, 2011. I have started count-down till the day on event come. In this session my best SQL Server topics are there which are conducted by Pinal Dave (Topic : SQL Server Performance Troubleshooting using Waits and Queues by Pinal Dave (Topic : SQL Server Performance Troubleshooting using Waits and Queues) and Jacob Sebastian (TSQL Worst Practices ) There are technologies are also there like HTML5, ASP.NET Tips and IE9 topics wi

MERGE statement with Insert, Update and Delete statements - SQL Server 2008

It will a lengthy and complex coding if we need to perform insert, update and delete statement individually. Instead of writing separate statements for the insert, update and delete operation, we have one more option which can be very helpful in this matter. Yes, that feature is "Merge" statement and supported in SQL server 2008 or later version. Merge is allow multiple DML operation to perform . That must be ended by semicolon. Let's see the example using Merge statement. -- Creating Database CREATE DATABASE MergeDatabase GO USE MergeDatabase GO -- Creating tables used for merged operation IF ( Object_id('UsingTable') > 0 ) DROP TABLE UsingTable GO CREATE TABLE UsingTable ( RefId INT IDENTITY(1, 1), name VARCHAR(100) ) GO IF ( Object_id('TargetTable') > 0 ) DROP TABLE TargetTable GO CREATE TABLE TargetTable ( ChildId INT, val INT ) GO -- Inserting records in both tables INSERT INTO UsingTable

Depth First Strategy with Hierarchical data using CTE (Common Table Expression) - SQL Server

CTE (Common Table Expression) is best feature to get the hierarchy of the data and hierarchical depth. Using CTE we can also get the data easily by level wise with parent and child relation. Recently when i working with stored procedure, at that time i was need to develop the logic to get the data in hierarchical order. I did it with CTE easily. Today i am going to demonstrate CTE with small examples. Creating Objects IF ( Object_id('CTEMaxLevelTab') > 0 ) DROP TABLE CTEMaxLevelTab GO CREATE TABLE CTEMaxLevelTab ( CategoryId INT ,ParentCategoryId INT ,CategotyOrder INT ,CategotyName VARCHAR(20) ) GO -- Inserting samples records INSERT INTO CTEMaxLevelTab ( CategoryId ,ParentCategoryId ,CategotyOrder ,CategotyName ) values ( 1,NULL,1,'Category - 1'), ( 2,1,1,'Category - 2'), ( 3,2,1,'Category - 3'), ( 4,3,1,'Category - 4'), ( 5,4,1,'Category - 5'), ( 6,2,2,'Cate

With Result Sets - TSQL Enhancement in SQL Server Denali

In previous version we need to used temporary table to get the result set from stored procedure execution result set for column name change or data type change. SQL Server 11 arrived with one more tsql enhancement, what's it ? It is " With Result Sets ". Le't look on small example to explain this feature. Creating objects require for Demo. -- Creating database and table CREATE DATABASE DBWithResultSet GO USE DBWithResultSet GO IF( Object_id('WithResultSetTab') > 0 ) DROP TABLE WithResultSetTab GO CREATE TABLE WithResultSetTab ( LogId INT IDENTITY (1, 1), LogDesc VARCHAR(100), LogDate DATETIME ) GO Now populate table WithResultSetTab with some records . -- Inserting sample records INSERT WithResultSetTab SELECT 'Descrption of the Logs occured during testing', Getdate() GO 10 To get the result sets , we are creating stored procedure. -- Creating procedure to get the data from table CREATE PROCEDUR

"Login failed for User" in SQL Server - Reason I - Do you know It?

For security, i have created some users as required access of the databases in SQL instance. After some days all the logins are working fine but for one login i got failed to connect SQL instance. I have tried many times but failed ever time. I did not know it was happen. After tried, tried i have finally check the properties for that login. Everything seems was fine access and all other stuffs. But when i have seen the default database associated that login and nothing assigned for the same. This was because of the database assigned as default, that was deleted. So SQL server was not able to find default database for that login. I am sharing my practical experience with following example. First creating database and login. CREATE DATABASE DefaultDatabase GO -- Creating login with sysadmin access USE [master] GO CREATE LOGIN [UserforDB] WITH PASSWORD=N'1234', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO EXEC master..sp_addsrvrolemember @

Surround With Snippets - SQL Server Denali

I would like to write something new and interesting feature of SQL Server 2011. I am going to introduce "Surround With" feature with small demo here. It is new enhancement in query editor. It is used to enclose the t-sql statements in construct with BEGIN...END,  IF...ELSE, WHILE. Le's go through with some examples and demo. IF ( Object_id('SurroundWithTab') > 0 ) DROP TABLE SurroundWithTab GO CREATE TABLE SurroundWithTab ( Id INT, Val INT ) GO INSERT INTO SurroundWithTab VALUES (1,1), (2,2), (3,3) GO SELECT * FROM SurroundWithTab Now select the t-sql statement which you want to enclose with constructor. After selecting right click and press Ctrl + K, Ctrl + S. After pressing Surround With or Ctrl + K, Ctrl + S it will appear with three code snippets as follow, Now double click on Begin, the selected t-sql statement will construct as following, BEGIN SELECT * FROM SurroundWithTab EN

Various Methods of SQL Pagination - SQL Server

In my earlier post i have wrote about new feature  "Order By Clause Enhancement". I have given demonstration with examples as well as how can we use this feature. It is used for SQL pagination and also explained for the same for pagination. You can read that post here  http://paresh-sqldba.blogspot.com/2011/04/order-by-clause-enhancement-and.html . Here i want to give same demonstration but not with only Order By Clause enhancement, It will be various ways and methods for SQL pagination. We will look for the native method and new method of SQL pagination and review it. USE PaginationDB GO IF (OBJECT_ID('PaginationTable') >= 0) DROP TABLE PaginationTable GO CREATE table PaginationTable ( PageId INT, PageName VARCHAR(100) ) GO IF EXISTS (SELECT 1 FROM SYS.SEQUENCES WHERE NAME = N'NewSequence') DROP SEQUENCE NewSequence; GO CREATE SEQUENCE [NewSequence] AS INT START with 1 INCREMENT by 1 MAXVALUE 1000000 GO Inserting records using sequence and lo