Renaming a DataBase (MS-SQL)

Check out this excellent article:

It’s not often you’ll need to rename a database, but when you do what looks like a trivial task can be complicated in a hurry. Today we’ll review the techniques used to rename a database and discuss some of the more interesting problems that can arise from renaming a database.

So why rename a database? I’d bet the most common reason is to fix a typo, or to remove an embedded space (spaces in object names are a pain). Perhaps less common would be to align the database name with a naming convention. Probably less common still is if something in the database name changes. For example, at point I worked for a company where each of our clients had their own database. It could be very confusing to tell someone to do something for customer ACME but that the data is stored in SPROCKET. In SQL 2000 it was possible to update sysdatabases directly to fix something like incorrect casing (Sprocket instead of SPROCKET) but that is not allowed in SQL 2005. If the database has been in use for a while the impact of renaming the database can be substantial, or at least seem that way! I’d suggest doing it early in the development cycle before dependences start to accrue.

Read More…

Fix for ‘The database principal owns a schema in the database, and cannot be dropped’ Error in SQL Server 2005

I was trying to restore one database backup; which I successfully did (I’m using SQL Server 2005), and want to delete the users of the database which are restored along with the DB; I received this error which says “The database principal owns a schema in the database, and cannot be dropped”.
While I googled the error, I found that the user which i want to delete is called “Database Principal” is having “DB_Owner” Role selected. That means the Database Pricipal I want to delete owns the Schema. One solution was to delete the Schema and then delete the user, which I am obviously not going to do. Another Solution was to change the DB_Owner to some another “Database Principal”; let say to “dbo”. I did it accordingly and then I was able to delete the user/Database Pricipal successfully.

Read More…

The Curse and Blessings of Dynamic SQL

I came across one good article on Dynamic SQL (SQL Server) by Erland Sommarskog, SQL Server MVP.

The Article describes Intro to Dynamic SQL, SQL Injection, Dynamic Queries and SPs, Good Coding Practice and Tips for Dynamic SQL, and the common cases where not to use Dynamic SQL:
In this article I will discuss the use of dynamic SQL in stored procedures and to a minor extent from client languages. To set the scene, I start with a very quick overview on application architecture for data access. I then proceed to describe the feature dynamic SQL as such, with a quick introduction followed by the gory syntax details. Next, I continue with a discussion on SQL injection, a security issue that you absolutely must have good understanding of when you work with dynamic SQL. This is followed by a section where I discuss why we use stored procedures, and how that is affected by the use of dynamic SQL. I carryon with a section on good practices and tips for writing dynamic SQL. I conclude by reviewing a number of situations where you could use dynamic SQL and whether it is a good or bad idea to do it. Read More…

Compound Operators in SQL Server 2008

Compound assignment operator means an operator combined with another operator. We have used such assignment operators in C++ and C#. This Compound Assignment Operator is introduced in SQL Server 2008.. Read More…

Comma separated list of values of single Database table field – SQL Server

Here is a line of T-SQL solution to get comma separated list of values of single field of a database table.. Read More…

Paging in Sql Server

This is one of the article i posted on our local intranet repository; hope it will be helpful to others.  As a Web developer, you know by now that using the default paging capabilities of ASP.NET Webcontrols like DataGrid and GridView cause the Performance issue when we have thousands and thousands of records in our database; because with every roundtrip to the data-server, you get ALL the records ALL of the time. This is fine perhaps for very small databases. But in web application, you always have to count on its Performance… Read More…

MERGE Command

In SQL Server 2008, Microsoft introduces the MERGE functionality through the MERGE command. The MERGE command inserts rows that don’t exist and updates the rows that do exist. Read More…

Using TRY-CATCH to Rollback a Transaction in SQL Server

Hello Mates,
Today I am going to buzz on Error Handing in Sql Server 2005.

The release of Sql Server 2005 has provided us somany features over its predecessor. No doubt that more preference is given to the tasks performed by the administrator. But there are some new development features added to make your Sql Code more powerful and error resistance, specially; Stored Procedures. Read More…

UPDATE Based Off of A Table

Check out my article on “Update Based of a Table in SQL Server”… Read More..

Row Constructor in SQL-2008

One of the cool features of SQL 2008 is Row Constructor. The concept ‘Row Constructors in SQL Server 2008’ basically deals with the crux of multiple inserts performed at one shot. Instead of having to call multiple inserts, we could just insert multiple rows with a single TSQL Statement… Read More..