Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles

SQL etc

Posted Sep 12, 2001

SQL "How To's" Part 3

By Sumit Dhingra

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 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
sp_change_users_login 'Report'

To associate a Orphaned User with a Login :-

Use Northwind
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 sumitdig@hotmail.com.

» See All Articles by Columnist Sumit Dhingra

SQL etc Archives

Latest Forum Threads
SQL etc Forum
Topic By Replies Updated
MySQL rollback UAL225 0 August 21st, 09:56 PM
Complex Search Query Galway 0 May 20th, 10:04 PM
change collation at once supercain 2 May 15th, 06:18 AM
SQL Features, tools and utilities question Neomite 1 April 10th, 09:13 AM