SQL “How To’s” Part 3

Hi guys. I am back again with SQL “How To’s” Part III. As a recap, this series of articles is an attempt to begin a reference material that saves you a lot of time and frustration in situations where you might wonder “How do I do this?”. It’s my hope that this resource will help provide quick and helpful to-the-point answers to common SQL Server questions. So, I hope you will find this third part to be as useful as Parts I and II.

1) How to move Master Database

Moving a Master database is different from moving a User
database. Last week, in “How To’s” 2 we saw how to move User database. Now lets see how to move a Master database.

Right-click the SQL Server in Enterprise Manager and
click Properties on the shortcut menu.

Click the Startup Parameters button and you see the
following entries:




-d is the fully qualified path for
the master database data file.

-e is the fully qualified path for
the error log file.

-l is the fully qualified path for
the master database log file.

Remove these values and specify the new values. For example :



Note : You can specify a new path
for the error log file as well.

Stop SQL Server.

Copy the Master.mdf and Mastlog.ldf files to the new
location (E:Sqldata).

Restart SQL Server.

2) How to Rename a Server

Rename the physical

Reboot the server.

Run the SQL Server setup
program. This will change the Registry entries. The setup will prompt you to
"Upgrade" SQL Server. Choose "yes".

Reboot the server.

Connect to the new server name in Query analyzer.

Run this code:
sp_dropserver <old server name>

Run this code:
sp_addserver <new server name>, local

Add the entry for the
new Server name in Enterprise manager and delete the entry for the Old server

3) How to detect & rectify Orphaned Users in a

Logins are associated to users by the security identifiers
(SIDs), which are stored in the Master database. When you restore a database to a
different server, the SID may be different resulting in a mismatch between the
Login-User association. These users, without a valid login association, are
called ‘Orphaned Users’.

Here’s how you can Detect Orphaned Users :-




To associate a Orphaned User with a Login :-



‘update_one’, ‘username’, ‘loginname’

4) How to use a Stored procedure as a Derived Table

Here’s a code sample which uses the recordset returned by
the execution of a stored procedure as a derived table.

SELECT a.pub_id, b.pub_name, a.title_id, a.price, a.pubdate

FROM OPENROWSET(‘SQLOLEDB’,’servername’; ‘username’;

Pubs.dbo.reptq1’) AS a

inner join publishers b

on a.pub_id = b.pub_id

5) How to Generate Serial Numbers in a Query

create table #Fruits

(Fruit Varchar(25))

INSERT #Fruits (Fruit)

VALUES (‘Mango’)

INSERT #Fruits (Fruit)

VALUES (‘Apple’)

INSERT #Fruits (Fruit)

VALUES (‘Banana’)

INSERT #Fruits (Fruit)

VALUES (‘Grapes’)

select Sno=count(*), a1.Fruit

from #Fruits a1 INNER JOIN #Fruits a2

ON a1.Fruit >= a2.Fruit

group by a1.Fruit

order by [Sno]

Any comments or suggestions are welcome at [email protected].


See All Articles by Columnist
Sumit Dhingra

Latest Articles