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

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

By submitting your information, you agree that databasejournal.com may send you databasejournal offers via email, phone and text message, as well as email offers about other products and services that databasejournal believes may be of interest to you. databasejournal will process your information in accordance with the Quinstreet Privacy Policy.

News Via 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

Comment and Contribute


(Maximum characters: 1200). You have characters left.



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