Feed on
Posts
Comments

Archive for the ‘Main’ Category

SQL SERVER - Simple Use of Cursor to Print All Stored Procedures of Database Including Schema

Posted in Author Pinal, Database, SQL, SQL Authority, SQL Documentation, SQL Query, SQL Scripts, SQL Server, SQL Stored Procedure, SQL Tips and Tricks, T SQL, Technology on November 22, 2008 | No Comments »

I love active participation from my readers. Just a day ago I wrote article about SQL SERVER - Simple Use of Cursor to Print All Stored Procedures of Database. I just received comment from Jerry Hung who have improved on previously written article of generating text of Stored Procedure.
DECLARE @procName VARCHAR(100)
DECLARE @getprocName CURSOR
SET @getprocName = CURSOR FOR
SELECT [...]

Read Full Post »

SQL SERVER - Simple Use of Cursor to Print All Stored Procedures of Database

Posted in Author Pinal, SQL, SQL Authority, SQL Cursor, SQL Query, SQL Scripts, SQL Server, SQL Stored Procedure, SQL Tips and Tricks, T SQL, Technology on November 20, 2008 | 4 Comments »

SQLAuthority Blog reader YordanGeorgiev has submitted very interesting SP, which uses cursor to generate text of all the Stored Procedure of current Database. This task can be done many ways, however, this is also interesting method.
USE AdventureWorks
GO
DECLARE @procName VARCHAR(100)
DECLARE @getprocName CURSOR
SET @getprocName = CURSOR FOR
SELECT s.name
FROM sysobjects s
WHERE type = ‘P’
OPEN @getprocName
FETCH NEXT
FROM @getprocName INTO @procName
WHILE @@FETCH_STATUS = [...]

Read Full Post »

SQLAuthority News - Author Visit - South Asia MVP Open Day 2008 - Goa - Group Photo

Posted in About Me, Author Pinal, DBA, SQL, SQL Authority, SQL MVP, SQL Query, SQL Server, SQL Tips and Tricks, SQLAuthority Author Visit, SQLAuthority News, T SQL, Technology, tagged , on November 19, 2008 | 3 Comments »

MVP Open day 2008 is one of the best event happened so far. I have previously written about this event in detail on this blog.
SQLAuthority News - Author Visit - South Asia MVP Open Day 2008 - Goa - Link List
SQLAuthority News - Author Visit - South Asia MVP Open Day 2008 - Goa - [...]

Read Full Post »

SQLAuthority News - Author Visit - South Asia MVP Open Day 2008 - Goa

Posted in About Me, Author Pinal, SQL, SQL Authority, SQL MVP, SQL Query, SQL Server, SQL Tips and Tricks, SQLAuthority Author Visit, SQLAuthority News, T SQL, Technology, tagged , on November 14, 2008 | No Comments »

I will be attending South Asia MVP Open Day 2008 on November 15 - 17, 2008 at Hotel Kenilworth Resorts, Goa. I am very excited as this will be my first Open Day event after being MVP. Microsoft Most Valuable Professionals (MVPs) are exceptional technical community leaders from around the world who are awarded for [...]

Read Full Post »

SQL SERVER - Delete Backup History - Cleanup Backup History

Posted in Author Pinal, Best Practices, Database, SQL, SQL Authority, SQL Backup and Restore, SQL Query, SQL Scripts, SQL Server, SQL Stored Procedure, SQL Tips and Tricks, T SQL, Technology on November 11, 2008 | 3 Comments »

SQL Server stores history of all the taken backup forever. History of all the backup is stored in msdb database. Many times older history is no more required. Following Stored Procedure can be executed with parameter which takes days of history to keep. In following example 30 is passed to keep history of month.
USE msdb
GO
DECLARE [...]

Read Full Post »

SQL SERVER - Check Database Integrity for All Databases of Server

Posted in Author Pinal, Best Practices, SQL, SQL Authority, SQL Query, SQL Scripts, SQL Server, SQL Server DBCC, SQL Tips and Tricks, T SQL, Technology on November 10, 2008 | No Comments »

Today we will see quick script which will check integrity of all the database of SQL Server.
EXEC sp_msforeachdb ‘DBCC CHECKDB(”?”)’
Above script will return you lots of messages in resultset. If there are any errors in resultset they will be displayed in red text. If everything is black text there is no error. Typical output of [...]

Read Full Post »

SQL SERVER - Refresh Database Using T-SQL

Posted in Author Pinal, SQL, SQL Authority, SQL Performance, SQL Query, SQL Scripts, SQL Server, SQL Server DBCC, SQL Stored Procedure, SQL Tips and Tricks, T SQL, Technology on November 7, 2008 | 2 Comments »

Yesterday I received following questions on blog. Ashish Agarwal asked following question.
Hi Pinal,
Can we refresh a database (like we do by right clicking database node in object explorer and clicking on refresh) thru SQL Query?
If yes, can you please tell me the query?
Thanks,
Ashish Agarwal
Answer to above question is NO. It is not possible to do [...]

Read Full Post »

SQLAuthority News - 5 Millions Visitors - 2 Anniversary - Authors Note on Economy Slow Down and Job Opportunity - SQL Server

Posted in About Me, Author Pinal, Best Practices, DBA, Database, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, SQLAuthority News, T SQL, Technology on November 6, 2008 | 4 Comments »

I just received following screen shot from one of the regular reader of SQLAuthority.com blog. He pointed out important milestone for our blog. We have crossed 5 millions visitors. In less than 2 years SQLAuthority.com blog has been visited by 5 millions visitors. I even missed the anniversary our blog. On November 1st 2008 SQLAuthority.com [...]

Read Full Post »

SQL SERVER - Clear Drop Down List of Recent Connection From SQL Server Management Studio

Posted in Author Pinal, Best Practices, DBA, Database, SQL, SQL Add-On, SQL Authority, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology on November 5, 2008 | 1 Comment »

Quite often it happens that SQL Server Management Studio’s Dropdown box is cluttered with many different SQL Server’s name. Sometime it contains the name of the server which does not exist or developer does not have access to it. It is very easy to clean the list and start over.

Delete mru.dat file from following location.
For [...]

Read Full Post »

SQL SERVER - Fix : Error: 4064 - Cannot open user default database. Login failed. Login failed for user

Posted in Author Pinal, SQL, SQL Authority, SQL Error Messages, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology on November 4, 2008 | 1 Comment »

I have received following question nearly 10 times in last week though emails. Many users have received following error while connecting to the database. This error happens when database is dropped for which is default for some of the database user. When user try to login and their default database is dropped following error shows [...]

Read Full Post »

SQL SERVER - Fix : Error : Login failed for user ‘UserName’. The user is not associated with a trusted SQL Server connection

Posted in Author Pinal, SQL, SQL Authority, SQL Error Messages, SQL Query, SQL Security, SQL Server, SQL Tips and Tricks, T SQL, Technology on November 2, 2008 | 2 Comments »

Recently I have got two desktop computers at home and both of them are very powerful machine.
Machine 1 : Windows Vista SP1 with SQL Server 2008
Machine 2 : Windows 2003 with SQL Server 2005 with SP2
When I was trying to connect from SQL Server 2008 to SQL Server 2005 using Windows Authentication I was getting [...]

Read Full Post »

SQL SERVER - Stored Procedure WITH ENCRYPTION and Execution Plan

Posted in Author Pinal, SQL, SQL Authority, SQL Query, SQL Scripts, SQL Security, SQL Server, SQL Tips and Tricks, T SQL, Technology on November 1, 2008 | 3 Comments »

Stored Procedures are very important and most of the business logic of my applications are always coded in Stored Procedures. Sometime it is necessary to hide the business logic from end user due to security reasons or any other reason. Keyword WITH ENCRYPTION is used to encrypt the text of the Stored Procedure. One SP [...]

Read Full Post »

SQL SERVER - DECLARE Multiple Variables in One Statement

Posted in Author Pinal, Best Practices, SQL, SQL Authority, SQL Performance, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology on October 31, 2008 | 2 Comments »

Just a day ago, while I was enjoying mini vacation during festival of Diwali I met one of the .NET developer who is big fan of Oracle. While discussing he suggested that he wished SQL Server should have feature where multiple variable can be declared in one statement. I requested him to not judge wonderful [...]

Read Full Post »

SQL SERVER - Simulate INNER JOIN using LEFT JOIN statement - Performance Analysis

Posted in Author Pinal, SQL, SQL Authority, SQL Interview Questions and Answers, SQL Joins, SQL Performance, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology on October 25, 2008 | 4 Comments »

Just a day ago, while I was working with JOINs I find one interesting observation, which has prompted me to create following example. Before we continue further let me make very clear that INNER JOIN should be used where it can not be used and simulating INNER JOIN using any other JOINs will degrade the [...]

Read Full Post »

SQLAuthority News - TOP Downloads - Bookmark

Posted in Best Practices, Database, Outsourcing Technology, SQL, SQL Authority, SQL Download, SQL Interview Questions and Answers, SQL Query, SQL Server, SQL Tips and Tricks, SQLAuthority News, Software Development, T SQL, Technology on October 24, 2008 | 1 Comment »

Recently I have got many many request for SQL Server Interview Questions and Answers as well related articles. It seems many people are looking for Job or appearing for interview at this time of the year. I have included list of the my top downloads in side bar of the blog, still I receive many [...]

Read Full Post »

SQL SERVER - Transaction and Local Variables - Swap Variables - Update All At Once Concept

Posted in Author Pinal, SQL, SQL Authority, SQL Performance, SQL Query, SQL Scripts, SQL Security, SQL Server, SQL Tips and Tricks, T SQL, Technology on October 20, 2008 | 3 Comments »

This article is inspired from two sources.
1) My year old article - SQL SERVER - Effect of TRANSACTION on Local Variable - After ROLLBACK and After COMMIT
2) Discussion with SQL Server MVP - Jacob Sebastian - SQLAuthority News - Author Visit - SQL Hour at Patni Computer Systems
I usually summarize my article at the end, [...]

Read Full Post »

SQL SERVER - Introduction to CLR - Simple Example of CLR Stored Procedure

Posted in Author Pinal, SQL, SQL Authority, SQL Performance, SQL Query, SQL Scripts, SQL Server, SQL Stored Procedure, SQL Tips and Tricks, SQL Utility, Software Development, T SQL, Technology, tagged on October 19, 2008 | 4 Comments »

CLR is abbreviation of Common Language Runtime. In SQL Server 2005 and later version of it database objects can be created which are created in CLR. Stored Procedures, Functions, Triggers can be coded in CLR. CLR is faster than T-SQL in many cases. CLR is mainly used to accomplish task which are not possible by [...]

Read Full Post »

SQL SERVER - Retrieve - Select Only Date Part From DateTime - Best Practice - Part 2

Posted in Author Pinal, Best Practices, SQL, SQL Authority, SQL DateTime, SQL Function, SQL Performance, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology on October 18, 2008 | 1 Comment »

A year ago I wrote post about SQL SERVER - Retrieve - Select Only Date Part From DateTime - Best Practice where I have discussed two different methods of getting datepart from datetime.
Method 1:
SELECT DATEADD(D, 0, DATEDIFF(D, 0, GETDATE()))
Method 2:
SELECT CONVERT(VARCHAR(10),GETDATE(),111)
I have summarized my post suggesting that either method works fine and I prefer to [...]

Read Full Post »

SQL SERVER - Get Common Records From Two Tables Without Using Join

Posted in Author Pinal, SQL, SQL Authority, SQL Joins, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology on October 17, 2008 | 6 Comments »

I really enjoy answering questions which I receive from either comments or Email. My passion is shared by SQL Server Expert Imran Mohammed. He frequently SQL community members by answering their questions frequently and promptly.
Sachin Asked:
Following is my scenario,
Suppose Table 1 and Table 2 has same column e.g. Column1
Following is the query,
1. Select column1,column2 From [...]

Read Full Post »

SQL SERVER - Downgrade Database to Previous Version

Posted in Author Pinal, Database, SQL, SQL Authority, SQL Performance, SQL Query, SQL Server, SQL Tips and Tricks, Software Development, T SQL, Technology on October 16, 2008 | 2 Comments »

Today I am writing on the topic which I do not like to write much. I enjoy writing usually positive or affirmative posts. Recently I got email from two different DBA where they upgraded to SQL Server 2005 trial version on their production server and now as their trial version was expire they wanted to [...]

Read Full Post »

Older Posts »