Skip navigation

SQL Server Q & A: Answers from Microsoft

Answers from Microsoft

We use sp_addalias with database scripting. Does the concept of db_owner in SQL Server 7.0 mean the alias concept does not exist anymore?

Aliases still exist in SQL Server 7.0, but don't use them, because they are going away in a future release. If you want to have the same functionality (for the most part) as aliasing a user to database owner (DBO), simply add the user to the db_owner role. The only caveat is that the users must now create objects with the qualifier DBO. For example, instead of:

SELECT * FROM authors

run this instead:

CREATE VIEW dbo.myview AS 
SELECT * FROM authors

I recently upgraded two servers from SQL Server 6.5 to 7.0. After uninstalling SQL Server 6.5 on each server, I haven't been able to start the Distributed Transaction Coordinator (DTC) service on either server. The NT Event log returns service-specific error 3221229574. I'm concerned because the SQL Server 7.0 install routine doesn't seem to let you remove and add specific services, so a re-installation of the service doesn't seem possible. What might be happening?

If you look on the SQL Server 7.0 CD-ROM in the x86\other directory, you'll find DTCSETUP.exe. Run this executable on one of the servers to reinstall the DTC. DTCSETUP is a self-contained installation package for the DTC service.

When I try to restore a backup of a database onto a new server, I have a problem with the database users. The login account in the master database is valid, but when I try to add the database user, which was not restored with the backup, SQL Server says the user already exists. The database user is not visible in the Users page, so I can't delete it to start again and the user cannot access the database. The only workaround I have found is to delete the users before backing up the database and then recreate them after the restore.

Run the stored procedure sp_change_users_login after you restore the database to a new server to clean up the login mismatches. If you use Windows NT integrated security and move the database to another server in the same domain, this setup is unnecessary. If you move the database outside of a domain's trust relationships, none of the NT users will work as you'd expect. For standard security users, each SQL Server user receives a unique Security ID (SID) associated with his or her login. When you move a database, even if the same login name exists, the SIDs probably aren't the same on the new server for the SQL Server login and the user in the database. To correctly map the SIDs, you can use sp_change_users _login to change the SIDs in your database to match the SIDs from the master..sysxlogins table in the new server. If you want to see a list of users, you can either look at the Sysusers table in the database or run sp_helpuser to see a report of all users in the database.

More information about database users and security in general is available in the SQL Server security paper at sql/70/whpprs /security.htm.

I'm modeling business processes in our applications with the database diagram tool in SQL Server 7.0. When I create a new diagram and choose Add Related Tables Automatically, I see an option to specify the number of levels. What does it mean if I enter a number, and what is the difference?

This option specifies the number of foreign-key levels. For example, if you add Titles and ask for one level in Pubs, you get Titleauthor. If you ask for two, you get whatever's related to Titleauthor, such as Authors (of course, Titles is related to many other tables, too). So, think of this option as asking how many links of foreign keys to search for and add to a diagram.

I want to know more about a SQL Server configuration value, network packet size. If network traffic is low, can I increase the packet size to 16KB for full utilization of SQL Server? Will large queries then execute faster, and will a query submitted to the server and query results to the client also be faster? Does NT's packet configuration affect SQL Server performance in low-network-traffic areas?

Unless your server is under an extreme workload, the network packet size is inconsequential. Fitting the typical network response in a single packet will give you some performance benefit, but remember that routers probably break up the packet as it goes across the network anyway.

I'm using NT 4.0 and SQL Server 7.0. When I set the security in NT authentication, I can't change my password in NT with the menu option Administrative Tools, Common\User Manager for Domains, and I can't log in to SQL Server. Do I need to change my password to the old password?

After you change your password, fully disconnect from the machine running SQL Server. Then try running the net use command to see any network connection that is open. If you see any open connections, delete them with net use \\sqlserver\??? /d. The next time you connect to the box, NT performs a new login. Logging off and back onto your computer also will fix the problem.

Does SQL Server 7.0 Distributed Management Objects (DMO) provide a way to identify an available disk drive or path for SQL Server?

DMO does not have a way to enumerate the local disks. You can execute the extended stored procedure xp_fixeddrives, which returns all local hard drives on the computer running SQL Server.

I'm setting up replication between one server and an indefinite number of clients. I'm using merge replication with horizontal partitioning. I can use a filter to control the records that flow from the client to the server, but not the other way around. In other words, all clients can receive each others' records. Should I make the server a distributor and create one publication using dynamic filtering? For example, the select statement at the server would be:

SELECT * FROM \[dbo\].\[table\] 
WHERE \[table\].\[client_id\] = client_id();

How can I make this function, client_id()? Does SQL Server let you create user-defined functions to implement this functionality?

SQL Server 7.0 doesn't let you create user-defined functions to partition data. You can use the WHERE clause to qualify the data by the range of primary keys for the table. For example, if all the information from Site1 has a primary key (int) of range 1-5000, you can use that data range as the dynamic partition. SQL Server Books Online (BOL) has additional information on how to use functions such as current_user or @@servername to help you partition data.

Our network manager prefers to use a single service account for both SQL Server 6.5 and 7.0, which are installed within an NT domain network. Doesn't Microsoft recommend a separate service account for SQL Server 6.5 and 7.0?

The only situation in which you need separate service accounts for SQL Server 6.5 and 7.0 is when you want each account to use a different mail account when integrating with Exchange Server.

How can I create an order in SQL that accepts "0001-01-01" in a date field type without generating a conversion error?

SQL Server dates start at January 1, 1753. To use an earlier date, you need to use a char or numeric data type and do the appropriate conversions yourself. For additional information about the datetime data types, see SQL Server Books Online (BOL). Look at Accessing and Changing Data, Transact-SQL Syntax Elements, Using Date and Time Data. Or, see Reilly, T-SQL for Starters, "Data-Type Details," page 60.

TAGS: SQL Server
Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.