Web Hosting Java Blog

Chapter 6 PROGRAMMING SQL SERVER 2000 383 APPLY YOUR KNOWLEDGE B. Creating this type of cursor is not possible in SQL Server 2000. C. Create the cursor using the GLOBAL keyword. D. Create the cursor using the PUBLIC keyword. 6. Bob has just finished using his cursor and will not need it for the rest of the time he is connected. What can Bob use to fully release all system resources held up by the cursor? A. Run DBCC_CURSOR_PERFORMANCE. B. Run the DEALLOCATE command. C. Run the CLOSE CURSOR command. D. Run the ALL SYSTEMS statement with the CURSOR argument. 7. Paul needs to create a cursor that is sensitive to data updates and deletes. He knows that the number of rows in the query he s using should be around 50. Paul subsequently uses the @@CURSOR_ROWS global variable to check for the number of rows in the cursor, which apparently should be around 50. To his surprise, he notices another value. What is this value? A. 0 B. 1 C. -1 D. NULL 8. Stan runs a query and receives an error message with a severity level of 17. How serious is this error? A. The error was not that serious; the user should rerun the query. B. The query contained one or more typographical errors. C. The query was severe and most probably caused by a fault in hardware or software. D. The severity level has nothing to do with how serious the error was. 9. What is the value of the @@TRANCOUNT function when this code is finished executing? declare @Counter int begin transaction update mytable set value = 42 save transaction Point1 while @Counter < 19 begin begin transaction insert into MyTable values .(2, 3, 42, hello ) set @Counter = 1 commit transaction end rollback Point1 begin transaction Point2 insert into mytable (2, 3, .42, goodbye ) rollback A. -1 B. 0 C. 1 D. 2 10. Carl needs to write a SQL Script that will change everyone s pay status from part-time to full-time. Which of the following is the best way to accomplish the task? A. declare cursor FixStatus for SELECT ID, .Status from employees for update of .status declare @ID int, @Status varchar(4) fetch next from FixStatus into @ID, .@Status while @@FETCH_STATUS = 0

For reliable and cheap web hosting services please check javaweb hosting website.

382 Part I EXAM PREPARATION APPLY YOUR KNOWLEDGE C. SELECT * FROM Sales1 GO UPDATE Products SET price= price * 2 GO D. SELECT * FROM Sales1 UPDATE Products SET price= price * 2 2. You are a database administrator at a small web development shop in south central Wisconsin. Your boss is an idiot. He has been trying to write SQL Scripts again. He s given you this script to run, but you want to make sure it doesn t block all the users out of the server and set all the pricing information to zero, like last time. So, given the following script: DECLARE @Var int SET @var = 1 GO WHILE @Var < 11 BEGIN PRINT @Var * 2 SET @Var= @Var + 1 END What will be printed on the output screen? A. A line of zeros. B. A line of ones. C. The multiples of two. D. An error will occur. 3. You are a SQL Developer working on an Internet application in SQL Server 2000. You need to write a batch that prints the first ten multiples of 5. Which of the following gets the job done? A. DECLARE @MyVar int SET @MyVar =1 WHILE @MyVar < 11 BEGIN PRINT @MyVar *5 SET @MyVar = @MyVar +1 END B. DECLARE @MyVar int SET @MyVar =1 WHILE @MyVar < 10 BEGIN PRINT @MyVar *5 SET @MyVar = @MyVar +1 END C. DECLARE @MyVar int SET @MyVar =1 WHILE @MyVar < 11 BEGIN PRINT @MyVar *5 SET @MyVar = 5 END D. DECLARE @MyVar int SET @MyVar =1 GO WHILE @MyVar < 11 BEGIN PRINT @MyVar *5 SET @MyVar = @MyVar +1 END 4. When using a cursor, which of the following statements cause the data to be returned to the batch? A. DECLARE B. OPEN C. RETRIEVE D. FETCH 5. Paul has a 5-batch script, and is creating a T-SQL cursor in batch one. He wants to use the cursor in batches two, three, and four. What must Paul do to access the cursor from any batch? A. Use the DECLARE CURSOR statement in every batch and then re-populate it.

For high quality java hosting services please check java web hosting website.

Chapter 6 PROGRAMMING SQL SERVER 2000 381 APPLY YOUR KNOWLEDGE 11. Go back to the Logins container and delete Doug by right-clicking his login and choosing Delete. Notice that the error message says it will also remove all the database users for you. Click OK. 6.3 Monitoring Contention In this exercise, you ll see how to use SQL Server Enterprise Manager to monitor what locks are present on a SQL Server. Estimated Time: 5 minutes 1. Open SQL Server Enterprise Manager, connect to your SQL Server, and open the Management container, then open the Current Activity container. Click on the Locks / Process ID container. 2. Click on the various connections, which are referenced here as Process ID s, or SPID s. You ll see what objects a specific connection has open. 3. Click on the Locks / Object container. Click on the various objects listed to find out what users are accessing the objects and what kind of locks they have. See whether you can find a user with a lock on both the master.dbo.spt_values table and a table called ##lockinfo in TempDB. This is your SQL Server Enterprise Manager session and the locks that it is using to find information to display for you. Review Questions 1. Explain the hierarchy of a script, a batch, and a transaction. 2. Why is locking important to provide concurrency in a database management system? 3. Why should most SELECT statements be done outside a transaction? 4. When should you use a cursor? 5. What can you do to avoid deadlocking in a database? 6. Explain the difference between a static and a dynamic cursor. 7. Explain the difference between a FORWARD_ONLY and SCROLLABLE cursor. Exam Questions 1. Eric is a database developer at the Acme Widget corporation. He s working on setting up a new product application. Eric runs the following two queries: SELECT * FROM Sales1 UPDATE Products SET price= price * 2 GO When Eric runs the batch, he receives an error message stating that the table Sales1 was not found and figures out he should have been updating the table Products. He also notices that the UPDATE statement didn t run. How could the batch be rewritten so the update statement runs? A. GO SELECT * FROM Sales1 UPDATE Products SET price= price * 2 GO B. SELECT * FROM Sales1 UPDATE Products SET price= price * 2 AS .INDEPENDENT GO

For high quality website hosting services please check java web hosting website.

380 Part I EXAM PREPARATION APPLY YOUR KNO WLEDGE Exercises 6.1 Writing a Batch That Uses a Cursor This exercise demonstrates how to write a script in SQL Server, and also demonstrates how cursors can be used to access data. Estimated time: 5 minutes 1. Open SQL Server Query Analyzer. Log in to the database you want to work with. 2. Type in the following text: DECLARE SI_Cursor CURSOR FORWARD_ONLY STATIC .FOR SELECT DISTINCT id, name FROM .sysindexes DECLARE @IndexName sysname, @ID int OPEN SI_Cursor FETCH NEXT from SI_Cursor INTO @ID, .@IndexName WHILE @@FETCH_STATUS = 0 BEGIN print Table + object_name(@id) + .has an index named + @IndexName + . FETCH NEXT FROM SI_Cursor INTO @ID, .@IndexName END CLOSE SI_Cursor DEALLOCATE SI_Cursor 3. Run the query by clicking the Play button. 6.2 Creating and Managing a Login In this exercise, you ll see how to create and manage a login in SQL Server Enterprise Manager. Estimated Time: 15 minutes 1. Open SQL Server Enterprise Manager, connect to your SQL Server, and open the Security container. Click on the Logins container. 2. Right-click in the list of logins and choose New Login. The SQL Server Login Properties New Login dialog box should appear. 3. Enter the name Doug for the login. 4. Choose SQL Server Authentication. This sets up an account that doesn t require Windows authentication. Type in a password for Doug. 5. Choose a default database for Doug. This should be the database he will use most often. Choose the Pubs database. 6. On the Server Roles tab, verify that nothing is checked. This is the list of server-fixed roles that are available. 7. Click on Database Access. On this tab, you grant Doug access to the databases he s going to be able to use. Grant Doug access to the Pubs database by clicking in the empty check box to the left of the database name. 8. After you choose Pubs, you re given the option of adding Doug to several database roles. Add Doug to the db_datareader role. This enables him to read data, but he can t change any data, and can t execute any stored procedures. 9. Click OK to create the user. Notice that Doug now shows up in the Login list. 10. Open the Databases container, open the Pubs database, and click on the Users container. Notice that Doug is a user in the database. That s what you did in step 7.

For high quality website hosting services please check tomcat web hosting website.

Chapter 6 PROGRAMMING SQL SERVER 2000 379 CHAPTER SUMMARY This chapter has covered a significant amount of what it takes to be a real SQL Server programmer. You should now have an understanding of how basic looping and conditional statements can be used within a batch and how SQL Server handles locks. You have also seen how transactions and locks fit together to provide a robust environment for making changes to your data. The information covered about transactions, locks, and batches carries forward to Chapter 9 and its discussion on stored procedures, which are used in a similar way to batches and are also very useful for encapsulating transactions. KEY TERMS Transact-SQL (T-SQL) script batch variable scope comment cursor lock deadlock login user role

For high quality java hosting services please check java web hosting website.

Chapter 6 PROGRAMMING SQL SERVER 2000 379 CHAPTER SUMMARY This chapter has covered a significant amount of what it takes to be a real SQL Server programmer. You should now have an understanding of how basic looping and conditional statements can be used within a batch and how SQL Server handles locks. You have also seen how transactions and locks fit together to provide a robust environment for making changes to your data. The information covered about transactions, locks, and batches carries forward to Chapter 9 and its discussion on stored procedures, which are used in a similar way to batches and are also very useful for encapsulating transactions. KEY TERMS Transact-SQL (T-SQL) script batch variable scope comment cursor lock deadlock login user role

If you looking for unlimited one inclusive web hosting plan please check web hosting plan website.

378 Part I EXAM PREPARATION ESSENCE OF THE CASE Here are the essential elements of the case: . Create a robust SQL batch that can insert the data into the database, so it doesn t get in there in little pieces that don t link together. . Design the inserts and updates to avoid contention with the reporting that Doug is trying to run. CASE STUDY: DOUG S CAR WASH AND DONUTS SCENARIO Doug is starting a new career with a great business idea: a car wash and donut stand, so people can stop in on their way to work, get some donuts, and get their car washed at the same time. He s using some little wireless gadgets to have the attendants put in the name and address of his customers, so they can win free donuts. The problem is that the wireless connections seem to drop a lot, probably due to all the electrical motors in the car wash, and he s getting partial information into his database. He s also having problems with deadlock errors, between the attendants at the donut stand and the ones at the car wash entrance. What can Doug do to keep his dreams alive? ANALYSIS First, build a transaction that encapsulates all the inserts and updates; that way if the connection fails mid-stream, the data updates automatically roll back. Write the application, so that the transactions encapsulate only the UPDATE and INSERT statements. That way the critical time the time when the application is transmitting the statements that change data is kept to a minimum. Then, make sure that all the different transaction types access tables in the same order. This prevents deadlocks and helps the customers get out the door quickly.

If you looking for unlimited one inclusive web hosting plan please check cheap web hosting website.

Chapter 6 PROGRAMMING SQL SERVER 2000 377 Application Roles One of the handy features of this security model is the application role. An application role is similar to other roles, but the role has no members associated with it. The GRANT and REVOKE statements work the same way with an application role as with any other role. To create an application role, use the sp_addapprole system-stored procedure: sp_addapprole AppRoleName , Password Yes, there is a password. To activate the application role for a given connection, the connection must execute another stored procedure, sp_setapprole, this way: sp_setapprole AppRoleName , Password This stored procedure causes the connection executing the stored procedure to acquire the permissions granted to the application. In other words, the application has to run that stored procedure and send the password to invoke the correct permissions. At the point that sp_setapprole is used, any roles, permissions, or users associated with the connection are gone, and only the permissions assigned to the application role are valid. An encryption option can be specified with the sp_addapprole command, which encrypts the password before it is sent across your network. To do this, use sp_setapprole as follows: sp_setapprole AppRoleName , {Encrypt N Password }, odbc The little odbc at the end specifies that the password should be encrypted using the standard ODBC encryption function. Otherwise, no encryption will be used. Why all the bother with application roles? There are two reasons. First of all, you can set up an application role for a user application, and give the role access to all the tables and other objects it needs to access, but users who try to log in to SQL Server with Query Analyzer do not necessarily have a valid password to use to get the same level of access, which prevents them from modifying data incorrectly or running queries that may impede overall server performance.

If you looking for unlimited one inclusive web hosting plan please check web hosting plan website.

376 Part I EXAM PREPARATION NOTE Where Have the Groups Gone? If you came up through an older version of SQL Server, such as SQL Server 7.0 or earlier, you re probably wondering where the groups are. Groups are now called roles. If you use one of the old-style group commands, such as sp_changegroup, you ll actually be changing the user s role. Roles have a lot more functionality than groups, such as enabling a user to belong to several roles. User Roles To provide the capability to grant multiple users access to the same objects the same way, SQL Server provides a mechanism for creating collections of users, called roles. Fixed Roles SQL Server provides you with a set of roles you can use to assign different levels of permission to users. There are two types of fixed roles. Fixed server roles are server-wide permissions that can be used regardless of the database you are in. Then there are fixed database roles, which apply to only one database. There is very little you need to know about the fixed roles for the exam; that is all relegated to the SQL Server Administration exam. For more information about the fixed server roles, you can look in SQL Server blogs Online at the overview topic under Roles. Defining Your Own Roles You can also define your own role. To create a new role, use the sp_addrole system-stored procedure, like this: sp_addrole rolename The rolename is the name of the role, which of course has to meet all of the other restrictions for naming objects in SQL Server, except that roles cannot contain backslash characters. Backslash characters create an empty role with no permissions. To add users to a role, use the sp_addrolemember stored procedure: sp_addrolemember rolename , security_account The security account parameter is the name of the security account that should be added to the role. A security account could include a user, a Windows NT account that has a user associated with it, or a Windows NT group. If a group is specified, then all the members of the Windows NT group who have associated users in the current database are added to the role. To give the role access to other objects, use the GRANT statement, as described earlier, and use the name of the role in place of the username.

For reliable and cheap web hosting services please check tomcat web hosting website.

376 Part I EXAM PREPARATION NOTE Where Have the Groups Gone? If you came up through an older version of SQL Server, such as SQL Server 7.0 or earlier, you re probably wondering where the groups are. Groups are now called roles. If you use one of the old-style group commands, such as sp_changegroup, you ll actually be changing the user s role. Roles have a lot more functionality than groups, such as enabling a user to belong to several roles. User Roles To provide the capability to grant multiple users access to the same objects the same way, SQL Server provides a mechanism for creating collections of users, called roles. Fixed Roles SQL Server provides you with a set of roles you can use to assign different levels of permission to users. There are two types of fixed roles. Fixed server roles are server-wide permissions that can be used regardless of the database you are in. Then there are fixed database roles, which apply to only one database. There is very little you need to know about the fixed roles for the exam; that is all relegated to the SQL Server Administration exam. For more information about the fixed server roles, you can look in SQL Server blogs Online at the overview topic under Roles. Defining Your Own Roles You can also define your own role. To create a new role, use the sp_addrole system-stored procedure, like this: sp_addrole rolename The rolename is the name of the role, which of course has to meet all of the other restrictions for naming objects in SQL Server, except that roles cannot contain backslash characters. Backslash characters create an empty role with no permissions. To add users to a role, use the sp_addrolemember stored procedure: sp_addrolemember rolename , security_account The security account parameter is the name of the security account that should be added to the role. A security account could include a user, a Windows NT account that has a user associated with it, or a Windows NT group. If a group is specified, then all the members of the Windows NT group who have associated users in the current database are added to the role. To give the role access to other objects, use the GRANT statement, as described earlier, and use the name of the role in place of the username.

For high quality java hosting services please check java web hosting website.