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:


-dD:MSSQL7datamaster.mdf

-eD:MSSQL7logErrorLog

-lD:MSSQL7datamastlog.ldf


-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 :


-dE:SQLDATAmaster.mdf

-lE:SQLDATAmastlog.ldf


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
server.

»        
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
name.

3) How to detect & rectify Orphaned Users in a
Database

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 :-


Use
Northwind


Go


sp_change_users_login
‘Report’


To associate a Orphaned User with a Login :-


Use
Northwind

Go

sp_change_users_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’;
‘password’,

‘exec
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