SQL Server Q & A - 20 Dec 1999

I need to assign limited permissions to two users. User A, who has only data read and write permissions, needs to execute User B's stored procedure. User B is a member of the db_owner role and needs to insert a row into a table, specifying the identity value. User A doesn't have the permissions to execute the statement SET IDENTITY_INSERT ON. I don't want to assign User A to the db_owner role because I want to let User A only see and change data. Is it possible to do this, and if so, how?

Only the object owner or db_owner, the database owner (dbo), or a sysadmin can run SET IDENTITY_INSERT ON. So you need to make User A the object's owner, or add User A to the db_owner or db_ddladmin roles.

My SQL Server 7.0 database has a 20GB data file residing on Drive G and a RAID array called Drive H. How can I split my database so approximately 10GB of data resides on Drive G and 10GB resides on drive H? Is Data Transformation Services (DTS) my only hope?

If it's a single database, you can add a new filegroup to the database, add some files from your H drive, then change the clustered index of some of the tables to the new filegroup drive array. Alternatively, you can add files to your database on the other server, and over time they'll rebalance themselves. And, as you stated, you can use DTS to move the data into separate tables, but the approaches that I've mentioned might be easier.

According to SQL Server Books Online (BOL), to upgrade from SQL Server 6.5 to 7.0, you need approximately 1.5 times the size of your SQL Server 6.5 databases in addition to the hard disk space SQL Server 7.0 uses. My databases have 17GB total hard disk space. But the database server uses only 7GB of space, and 12GB on my hard drive is free space. Can I upgrade from SQL Server 6.5 to 7.0 with the hard disk space that I have?

You need to shrink your SQL Server 6.5 databases before you begin. If that's not possible, during the upgrade, the Upgrade wizard will prompt you to let it create your databases automatically or to select the option to modify the initial size of the database. If the wizard selects a size similar to your SQL Server 6.5 configuration, this option will let you use less space. And, as long as autogrow is turned on, you can upgrade successfully.

I use the Dump Table and Load Table commands in SQL Server 6.5 frequently, especially when I'm developing and troubleshooting. These commands, which SQL Server 7.0 doesn't include, mean the difference in 10 times the disk space requirements (10GB to 100GB) during batch processing. Will a future version of SQL Server include these commands?

I miss the Dump Table and Load Table features, too. As far as I know, Microsoft isn't planning to add these features to a future version of SQL Server. One workaround is to put your table into a filegroup, then do a table-level backup and restore of the filegroup.

I can't start the SQL Server Agent, and I get the following error message:

18456, Login failed for userUnable to connect to server; SQL Server Agent cannot start

How can I solve this problem?

This error message usually means that you need to update the password in the SQL Server Agent service account. Go to the Control Panel, select Services, and double-click the SQL Server Agent Service. Change the SQL Agent user account password to the current password. Then you can start the service.

I want to call a stored procedure in a SELECT statement in SQL Server 7.0, Service Pack 1 (SP1). I tried doing so in the following statement

SELECT inv_no,inv_date, @inv_fin_year = sp_FinYear(inv_no) FROM inv_master 

where sp_FinyYear is a stored procedure with a parameter. An error occurred while the procedure was executing. How can I create a user-defined function in SQL Server 7.0? And, how can I call a stored procedure in a SELECT statement?

You can't call a stored procedure in a SELECT statement or create user-defined functions in SQL Server 7.0. However, Microsoft plans to address both functionalities by implementing user-defined functions in a future release.

I need to upgrade a few hundred SQL Servers, and I don't think using the SQL Server 7.0 Upgrade Wizard is realistic. Can I extract the Upgrade Wizard's components for setup to run in an unattended mode, similarly to the way that I can perform the installation process unattended?

No, Microsoft doesn't support a way to break out the components of the Upgrade Wizard.

My SQL Server 7.0 database is a copy of the production database that I keep for testing and simulating problems that users report. I don't want a transaction log for the test database because some of the operations have too many data updates and the transaction log grows to an unmanageable size. How can I stop the server from logging transactions for the test database?

You can't turn off logging in SQL Server 7.0, but you can set the database options Trunc log on chkpt, which will truncate the log periodically, and Select into bulkcopy, which will allow some operations not to be logged.