ebook45-4

4 T-SQL SQL Server S Q L 4.1 CAST CONVERT C A S T C O N V E RT C A S T CAST ( e x p r e s s i o n AS d a t a _ t y p e) e x p r e s s i o n SQL Server D a t a _ t y p e C A S T DECLARE @intva l u e SELECT @intvalue = 50 I N T E G E R PRINT 'The value of @intvalue is ' + CAST(@intValue AS VA R C H A R ( 1 0 ) ) C O N V E RT C A S T D AT E T I M E D AT E T I M E C O N V E RT (data_type [ (length)], expression [, style]) d a t a _ t y p e l e n g t h S t y l e D AT E T I M E 4-1 A N S I C O N V E RT (VARCHAR(16), GETDATE( ), 102) ( y y ) ( y y y y ) 4-1 DAT E T I M E / - 0 100 (*) mon dd yyyy h h : m i A M 1 1 0 1 U S A m m / d d / y y 2 1 0 2 A N S I y y. m m. d d 3 1 0 3 B r i t i s h / F r e n c h d d / m m / y y 4 1 0 4 G e r m a n d d. m m. y y 5 1 0 5 I t a l i a n d d - m m - y y 6 1 0 6 - dd mon yy 7 1 0 7 - mon dd,yy 8 1 0 8 - h h : m i : s s - 9 109 (*) mon dd yyyy 1 0 11 0 U S A m m - d d - y y H h : m i : s s : m m m A M

4 T- S Q L 57 ( y y ) ( y y y y ) / 11 111 J A PA N y y / m m / d d 1 2 11 2 I S O y y m m d d - 13 113 (*) E u r o p e + dd mon yyyy h h : m i : s s : m m m ( 2 4 h ) 1 4 11 4 - h h : m i : s s : m m m ( 2 4 h ) - ( 2 4 h ) 20 120 (*) ODBC yyyy-mm-dd hh:mi:ss - 21 121 (*) O D B C y y y y - m m - d d h h : m i : m s. m m m ( 2 4 h ) 4.2 P R I N T 8000 A S C I I C H A R VA R C H A R C O N V E RT 4-1 P R I N T C O N V E RT I N VA R C H A R 4-1 DECLARE @intminqty INT, @intnumorders INT, @chroutputtext CHAR(60) /* define the variables */ SELECT @intminqty = 15 SELECT @intnumorders = COUNT(*) FROM sales WHERE qty > @intminqty /* Concatenate the string using CONVERT to format the variables */ SELECT @chroutputtext = 'There are ' + CONVERT ( VA R C H A R,@ i n t N u m O r d e r s ) + ' orders with a quantity greater than ' + CONVERT ( VARCHAR, @intminqty) PRINT @chroutputte x t /* CODE RESULTS - DO NOT RUN CODE AFTER THIS LINE */ (1 row(s) aff e c t e d ) (1 row(s) aff e c t e d ) (1 row(s) aff e c t e d ) There are 13 orders with a quantity greater than 15

58 4-1 S E L E C T S E L E C T SET NOCOUNT ON S E L E C T SET NOCOUNT OFF 4-2 4-1 SET NOCOUNT OFF SET NOCOUNT ON 4-2 SET NOCOUNT DECLARE @intminqty INT, @intnumorders INT, @chroutputtext CHAR(60) /* define the variables */ SELECT @intminqty = 15 SELECT @intnumorders = COUNT(*) FROM sales WHERE qty > @intminqty /* Concatenate the string using CONVERT to format the variables */ SELECT @chroutputtext = 'There are ' PRINT @chroutputte x t SET NOCOUNT OFF + CONVERT ( VA R C H A R, @ i n t N u m O r d e r s ) + ' orders with a quantity greater than ' + convert(varchar, @intminqty) /* CODE RESULTS - DO NOT RUN CODE AFTER THIS LINE */ There are 13 orders with a quantity greater than 15 P R I N T S E L E C T S E L E C T P R I N T ( ) R A I S E R R O R 4. 4 4.3 sysmessages SQL Server s y s m e s s a g e s 3 000 4-2 s y s m e s s a g e s 4-2 sysmssages E r r o r I N T S e v e r i t y S M A L L I N T D l e v e l S M A L L I N T D e s c r i p t i o n WARCHAR (510) M s g L a n g i d S M A L L I N T N U L L s y s m e s s a g e s

4 T- S Q L 59 SELECT error, severity, description FROM master.. s y s m e s s a g e s ORDE BY error 4.3.1 sysmessages s y s m e s s a g e s 4-3 4-3 0 1 0 11 1 6 1533 New message added 504 Stored procedure %.* s not found 1 7 D B O S A 7111 Can t log text value because log is out of space 18 SQL Server 516 Attempt to get system date/time failed 19 S A There is insufficient system memory to run this query 2 0 2806 Stored procedure %.* s is corrupted. Must re-create procedure 2 1 2 2 2 3 2 4 2 5 0 1 8 19 2 5 20 SQL Server 4.3.2 s y s m e s s a g e s sp_addmessage(), sp_altermessage() s p _ d r o p m e s s a g e () 1. s y s m e s s a g e s s p _ a d d m e s s a g e () s y s m e s s a g e s 50001 2 147483647 255 4-3

60 4-3 s y s m e s s a g e s sp_addmessage 50001, 12, 'A number greater than 0 was expected. Please retry' /* CODE RESULTS - DO NOT RUN CODE AFTER THIS LINE */ New message added. m e s s a g e _ i d 4-4 4-4 M E S S A G E _ I D s y s m e s s a g e s sp_addmessage 50001, 12, 'The specified value for %s was invalid.' /* CODE RESULTS - DO NOT RUN CODE AFTER THIS LINE */ Msg 15043, Level 16, State 1 You must specify 'REPLACE' to overwrite an existing message. R E P L A C E 4-5 ( U.S. English) Windows NT Application Log 4-5 R E P L A C E s y s m e s s a g e s sp_addmessage 50001, 12, 'The specified value for %s was invalid.', US_English, TRUE, REPLACE /* CODE RESULTS - DO NOT RUN CODE AFTER THIS LINE */ Replacing message. New message added. s y s m e s s a g e s m e s s a g e _ i d 2. s y s m e s s a g e s s y s m e s s a g e s s p _ a l t e r m e s s a g e () Windows NT Event Log 4-6 sp_altermessage 50001, WITH_LOG, FA L S E 4-6 s y s m e s s a g e s /* CODE RESULTS - DO NOT RUN CODE AFTER THIS LINE */ Message altered. 3. s y s m e s s a g e s s p _ d r o p m e s s a g e () s y s m e s s a g e s

4 T- S Q L 61 4-7 sp_dropmessage 50001 4-7 s y s m e s s a g e s /* CODE RESULTS - DO NOT RUN CODE AFTER THIS LINE */ Message dropped. 4.4 4. 2 R A I S E R R O R R A I S E R R O R WITH LOG Wi n d o w s NT Event Log Windows NT Application Log SQL Server Error Log RAISERROR ({msg_id msg_str} {, s e v e r i t y, state} [, argument [, n]]) [WITH o p t i o n [, n] ] M s g _ i d s y s m e s s a g e s 50000 m s g _ i d 13000 m s g _ i d 5 0000 SQL Server m s g _ i d s e v e r i t y 0 1 8 19 2 5 SQL Server s t a t e 1 1 27 s t a t e a rg u m e n t o p t i o n 4-4 4-4 Option L O G N O WA I T S E T E R R O R L O G SQL Server Windows NT 440 @@ E R R O R m s g _ i d 19 R A I S E R R O R 4-8 s y s m e s s a g e s RAISERROR(14056, 16, 1) 4-8 s y s m e s s a g e s /* Build a string to display the current value of the global */ /* variable @@error. You do not normally have to do this; this */

62 /* step is for the purposes of this example. */ SELECT @chrprintmsg = 'The value of @@error is ' + PRINT @chrprintmsg C O N V E RT ( c h a r, @ @ e r r o r ) /* CODE RESULTS - DO NOT RUN CODE AFTER THIS LINE */ Server: Msg 14056, Level 16, State 1, Line 1 The subscription could not be dropped at this time. The value of @@error is 14056 s y s m e s s a g e s 4-7 %.* s 4-9 s y s m e s s a g e s s y s m e s s a g e s R A I S E R R O R @@ e r r o r m e s s a g e _ i d 5 0000 10 1 @@ e r r o r WITH SETERROR DECLARE @chrprintmsg CHAR(255) 4-9 s y s m e s s a g e s /* Raise the error, giving the error text, severity, and state. */ RAISERROR('Undefined error raised using the WITH SETERROR option',1,2) WITH SETERROR /* Build a string to display the current value of the global */ /* variable @@error. You do not normally have to do this; this */ /* step is for the purposes of this example. */ SELECT @chrprintmsg = 'Using WITH SETERROR sets the error number ' + PRINT @chrprintmsg generated to ' + CONVERT ( c h a r, @ @ e r r o r ) /* CODE RESULTS - DO NOT RUN CODE AFTER THIS LINE */ Undefined error raised using the WITH SETERROR option (1 row(s) aff e c t e d ) Using WITH SETERROR sets the error number generated to 50000 4.5 CASE C A S E C A S E 4.5.1 CASE C A S E

4 T- S Q L 63 t i t l e s t y p e t i t l e _ i d C A S E t i t l e _ i d C A S E B o o k Ty p e 4-10 SELECT title_id, FROM titles 4-10 C A S E CASE SUBSTRING(title_id,1,2) END AS BookTy p e WHEN 'BU' THEN 'Business' WHEN 'MC' THEN 'Modern Cooking' WHEN 'PC' THEN 'Popular Computing' WHEN 'PS' THEN 'Psychology' WHEN 'TC' THEN 'Traditional Cooking' The following shows the output from the query: title_id BookType -------- ---------------------------------------------------- PC1035 Popular Computing PS1372 Psychology B U 1111 Business PS7777 Psychology TC4203 Traditional Cooking PS2091 Psychology PS2106 Psychology PC9999 Popular Computing TC3218 Traditional Cooking PS3333 Psychology PC8888 Popular Computing MC2222 Modern Cooking BU7832 Business TC7777 Traditional Cooking BU1032 Business MC3021 Modern Cooking MC3026 Modern Cooking BU2075 Business (18 row(s) affected) C A S E 4-11 4-10

64 select distinct 'WHEN ''' 4-11 4-10 + SUBSTRING(title_id,1,2) + ''' THEN' from titles /* CODE RESULTS - DO NOT RUN CODE AFTER THIS LINE */ ------------------------- WHEN 'BU' THEN WHEN 'MC' THEN WHEN 'PC' THEN WHEN 'PS' THEN WHEN 'TC' THEN (5 row(s) affected) 4.5.2 CASE C A S E C A S E C A S E 1. C A S E C A S E C A S E 4-12 4-13 4-11 SELECT sum(qty) AS To t a l, FROM 4-12 d a t e n a m e ( w e e k d a y, ord_date) AS We e k d a y sales GROUP BY datename(weekday, ord_date) SELECT sum(qty) AS To t a l, 4-13 C A S E CASE datepart(weekday, ord_date) WHEN 1 THEN 'Sunday' WHEN 2 THEN 'Monday' WHEN 3 THEN 'Tu e s d a y ' WHEN 4 THEN 'We d n e s d a y ' WHEN 5 THEN 'Thursday' WHEN 6 THEN 'Friday'

4 T- S Q L 65 END AS We e k d a y FROM sales WHEN 7 THEN 'Saturday' GROUP BY datepart(weekday, ord_date) 4-12 Total Weekday ----------- ------------------------------ 130 Monday 115 Saturday 45 Sunday 40 Thursday 78 Tu e s d a y 85 We d n e s d a y (6 row(s) aff e c t e d ) 4-13 Total Weekday ----------- ------------------------------ 45 Sunday 130 Monday 78 Tu e s d a y 85 We d n e s d a y 40 Thursday 115 Saturday (6 row(s) aff e c t e d ) 2. C A S E C A S E C A S E 4-14 y t d _ s a l e s W H E N E L S E E L S E S E L E C T title_id, price, ytd_sales, "DiscountPrice" = C A S E 4-14 C A S E WHEN ytd_sales < 1000 THEN C O N V E RT (SMALLMONEY, price *.50) WHEN ytd_sales < 3000 THEN

66 C O N V E RT (SMALLMONEY, price *.85) E N D ELSE price FROM titles 4-14 title_id price ytd_sales DiscountPrice -------- -------------------------- ----------- -------------------------- BU1032 19.99 4095 1 9. 9 9 B U 1111 11.95 3876 11.95 BU2075 2.99 18722 2. 9 9 BU7832 19.99 4095 1 9. 9 9 MC2222 19.99 2032 1 6. 9 9 MC3021 2.99 22246 2. 9 9 MC3026 (null) (null) ( n u l l ) PC1035 22.95 8780 2 2. 9 5 PC8888 20.00 4095 2 0. 0 0 PC9999 (null) (null) ( n u l l ) PS1372 21.59 375 1 0. 8 0 PS2091 10.95 2045 9. 3 1 PS2106 7.00 111 3. 5 0 PS3333 19.99 4072 1 9. 9 9 PS7777 7.99 3336 7. 9 9 TC3218 20.95 375 1 0. 4 8 TC4203 11.95 15096 11. 9 5 TC7777 14.99 4095 1 4. 9 9 (18 row(s) aff e c t e d ) y t d _ s a l e s p i r c e D i s c o u n t P r i c e C A S E 3. C A S E C A S E S E L E C T C A S E S E L E C T C A S E 4-15 S E L E C T S E L E C s t o r e s W H E N E L S E C A S

4 T- S Q L 67 SELECT DISTINCT st.stor_name, 'Sales Rating' = C A S E 4-15 C A S E WHEN (SELECT SUM(sa.qty) FROM sales sa THEN 'Poor' WHEN (SELECT SUM(sa.qty) FROM sales sa THEN 'Av e r a g e ' WHEN (SELECT SUM(sa.qty) FROM sales sa THEN 'Good' ELSE 'Excellent' E N D, 'Sales Total' = (SELECT SUM(sa.qty) FROM sales sa FROM stores st, sales sa ORDER BY 'Sales To t a l ' stor_name WHERE st.stor_id = sa.stor_id) < 10 WHERE st.stor_id = sa.stor_id) < 80 WHERE st.stor_id = sa.stor_id) < 100 WHERE st.stor_id = sa.stor_id) Sales Rating Sales Total ---------------------------------------- ------------ ----------- ----------- Eric the Read Books P o o r 8 Fricative Bookshop Av e r a g e 6 0 B o o k b e a t G o o d 8 0 News & Brews G o o d 9 0 B a r n u m ' s E x c e l l e n t 1 2 5 Doc-U-Mat: Quality Laundry and Books E x c e l l e n t 1 3 0 (6 row(s) affected) 4. C A S E C A S E 4-16 U P D AT E C A S E s a l e s p a y t e r m s ( 2 ) q t y p a y t e r m s U P D AT E C A S E U P D ATE sales SET payterms = C A S E 4-16 C A S E WHEN (SELECT SUM(qty) FROM sales s1

68 WHERE sales.stor_id = s1.stor_id) < 10 THEN 'On Invoice' WHEN (SELECT SUM(qty) FROM sales s1 WHERE sales.stor_id = s1.stor_id) < 100 THEN 'Net 30' ELSE 'Net 60' E N D G O SELECT stor_id, SUBSTRING(ord_num,1,5) ord_num, ord_date, qty, payterms, title_id FROM sales G O (21 row(s) aff e c t e d ) stor_id ord_num ord_date qty payterms title_id ------- ------- --------------------------- ------ ------------ -------- ------- ------- ---- 6380 6871 Sep 14 1994 12:00AM 5 On Invoice B U 1032 6380 722a Sep 13 1994 12:00AM 3 On Invoice P S 2091 7066 A2976 May 24 1993 12:00AM 50 Net 60 P C 8888 7066 QA744 Sep 13 1994 12:00AM 75 Net 60 P S 2091 7067 D4482 Sep 14 1994 12:00AM 10 Net 30 P S 2091 7067 P2121 Jun 15 1992 12:00AM 40 Net 30 T C 3218 7067 P2121 Jun 15 1992 12:00AM 20 Net 30 T C 4203 7067 P2121 Jun 15 1992 12:00AM 20 Net 30 T C 7777 7131 N9140 Sep 14 1994 12:00AM 20 Net 60 P S 2091 7131 N9140 Sep 14 1994 12:00AM 25 Net 60 M C 3021 7131 P3087 May 29 1993 12:00AM 20 Net 60 P S 1372 7131 P3087 May 29 1993 12:00AM 25 Net 60 P S 2106 7131 P3087 May 29 1993 12:00AM 15 Net 60 P S 3333 7131 P3087 May 29 1993 12:00AM 25 Net 60 P S 7777 7896 QQ229 Oct 28 1993 12:00AM 15 Net 30 B U 7832 7896 TQ456 Dec 12 1993 12:00AM 10 Net 30 M C 2222 7896 X999 Feb 21 1993 12:00AM 35 Net 30 B U 2075 8042 423LL Sep 14 1994 12:00AM 15 Net 30 M C 3021 8042 423LL Sep 14 1994 12:00AM 10 Net 30 B U 1032 8042 P723 Mar 11 1993 12:00AM 25 Net 30 B U 1111 8042 QA879 May 22 1993 12:00AM 30 Net 30 PC1035 (21 row(s) affected)

4 T- S Q L 69 4.6 (distributed transaction) ( 3 ) 4.6.1 (Distributed Transaction Coordinator D T C ) 3 D T C D T C BEGIN DISTRIBUTED TRANSACTION D T C ( ) ( 4-1 1 ) ( 4-1 2 ) (commit tree) 1. REQUEST T R A N S A C T I O N R E L A T I O N S H I P 2. AGREE TO RELATIONSHIP DTC 3. PREPARE TRANSACTION DTC 4. TRANSACTION PREPARED 5A.COMMT TRANSACTION 6. TRANSACTION COMMITTED 5B.ROLLBACK TRANSACTION F A I L U R E 5C. ROLLBACK F A I L U R E 4-1 D T C ( 4-1 3 ) ( 4-1 4

70 ) 4-1 5 A 6 5 B 5 C 5 A 6 5 B 5 C 5 B 5 C ( ) D T C SQL Enterprise Manager Server Manager D T C Tr a n s a c t i o n F o rg e t ( ) S Q L Enterprise Manager D T C 4.6.2 DTC D T C ( 16 ) B E G I N DISTRIBUTED TRAN(DISTRIBUTED TRANSACTION) D T C C O M M I T T R A N S R O L L B A C K s p _ a d d s e r v e r () s p _ a d d r e m o t e l o g i n () Enterprise Manager S e r v e Remote Server M a n a g e Remote Servers

4 T- S Q L 71 4-17 j o b _ i d j o b s j o b s 4-18 4-17 D T C C R E ATE PROCEDURE prupdatejobs(@insjob_id smallint, @chvjob_desc varchar(50)) AS U P D ATE jobs SET job_desc = @chvjob_desc WHERE job_id = @insjob_id 4-18 C R E ATE PROCEDURE prupdatejobs(@insjob_id @chvjob_desc smallint, varchar(50), @chvservername varchar(30)) AS declare @chvexecstr varchar(255) /* Start a Transaction */ BEGIN DISTRIBUTED TRANSACTION UpdateJobs /* Change Local Jobs Table */ U P D ATE jobs SET job_desc = @chvjob_desc where job_id = @insjob_id /* Make a string with the remote server name and */ /* stored procedure to execute */ /* Syntax for remote procedure call is NTSQL2.pubs..prUpdateJobs */ select @chvexecstr = @chvservername + '.pubs..prupdatejobs ' /* Update remote server. */ exec @chvexecstr @insjob_id, @chvjob_desc /* Commit the MS DTC transaction */ COMMIT TRANSACTION E X E C U T E 4-19 j o b s job_id 5 j o b _ d e s c C E O exec prupdatejobs 5,'CEO', NTSQL2 4-19 This command did not return data, and it did not return any rows

72 N T S Q L 2 D T C 4-20 D T C D T C j o b _ i d j o b _ d e s c C E O EXEC prupdatejobs 5,'Chief Executive Officer', NTSQL2 4-20 Msg 8501, Level 16, State 1 DTC on server 'NTSQL' is unavailable Msg 8524, Level 16, State 1 The current transaction couldn't be exported to remote site. 4.6.3 DTC It has been rolled back. D T C b o o k i n v b o o k i n 4-21 p u b s C R E ATE TABLE bookinv ( ) title_id quantity location lastaudit tid 4-21 CONSTRAINT p1_constraint PRIMARY KEY NONCLUSTERED, integer, char(2), datetime I N S E RT INTO bookinv SELECT DISTINCT title_id, FROM titles C O A L E S C E ( C O N V E RT ( i n t, R A N D ( y t d _ s a l e s ) * 1 0 0 ), 1 0 ), S U B S T R I N G ( t y p e, 1, 2 ), p u b d a t e 4-22 7 b o o k _ i d b o o k i n v 10 11 1 7

4 T- S Q L 73 19 2 8 24 2 7 4-23 t i t l e _ i d S t o r I D 11 15 18 U p d a t e J o b s 21 22 t i t l e s y t d _ s a l e s 25 2 8 s a l e s 31 33 4 2 @@ e r r o r @@ e r r o r 4-22 1: CREATE PROCEDURE prupdatebookinv(@tidtitles tid, 2: @intqty int) AS 3 : 4: Declare @intavailqty int, @chvoutputmsg varchar(255) 5 : 6: /* Get quantity of available books in inventory */ 7: SELECT @intavailqty = (SELECT quantity FROM bookinv 8: WHERE title_id = @tidti t l e s ) 9 : 10: IF @intavailqty - @intqty < 0 11: BEGIN 12: /* If available quantity is less than zero, then raise error */ 13: SELECT @chvoutputmsg = 'There are only ' 14: + CONVERT ( v a r c h a r ( 1 0 ), @ i n t Av a i l Q t y ) 15: + ' available, remote transaction denied' 16: RAISERROR (@chvoutputmsg, 16, -1) 17: END 18: ELSE 19: BEGIN 20: /* If available quantity is greater than zero, */ 21: /* update the inventory table */ 22: UPDATE bookinv SET quantity = quantity - @intqty 23: WHERE title_id = @tidti t l e s 24: SELECT @chvoutputmsg = 'There were ' 25: + CONVERT ( v a r c h a r ( 1 0 ), @ i n t Av a i l Q t y ) 26: + ' available, remote transaction succeeds' 27: PRINT @chvoutputmsg 28: END

74 4-23 1: CREATE PROCEDURE prcreatesale (@tidtitleid tid, 2: @intqty int, 3: @chrstorid char(4), 4: @chvservername varchar(30)) AS 5: 6: DECLARE @chvexecstr varchar(255), 7: @chvordnum varchar(20) 8 : 9: /* Make a string with the remote server name and */ 10: /* stored procedure to execute. */ 11: SELECT @chvexecstr = @chvservername 12: + '.pubs..prupdatebookinv ' 1 3 : 14: /* Generate an order number. */ 15: SELECT @chvordnum = @chrstorid + SUBSTRING(@tidTi t l e I d, 1, 2 ) 1 6 : 17: /* Start a transaction. */ 18: BEGIN DISTRIBUTED TRANSACTION UpdateJobs 1 9 : 20: /* Update local titles table. */ 21: UPDATE titles SET ytd_sales = ytd_sales + @intqty 22: WHERE title_id = @tidtitleid 2 3 : 24: /* Add entry to local sales table. */ 25: INSERT sales(stor_id, ord_num, ord_date, qty 26:, payterms, title_id) 27: VALUES (@chrstorid, @chvordnum, GETDATE(), @intqty 28:, 'Net 60', @tidtitleid) 2 9 : 30: /* Update remote server. */ 31: EXEC @chvexecstr @tidtitleid, @intqty 32: 33: IF @@error > 0 34: BEGIN 35: ROLLBACK TRAN 36: PRINT 'TRANSACTION WAS ROLLED BACK' 37: END 38: ELSE 39: BEGIN 40: COMMIT TRAN 41: PRINT 'TRANSACTION WAS COMMITTED' 42: END

4 T- S Q L 75 4-24 E X E C U T E b o o k i n v 100 B U 1032 t i t l e s s a l e s 40 EXEC prcreatesale 'BU1032',100,'6380','NTSQL2' 4-24 /* Enter only the EXEC statement - sample output follows. */ Msg 50000, Level 16, State 1 There are only 40 available; remote transaction denied. TRANSACTION WAS ROLLED BACK 4-25 20 4-25 4-27 4-25 EXEC prcreatesale 'BU1032',20,'6380','NTSQL2' /* Enter only the EXEC statement - sample output follows. */ There were 40 available; remote transaction succeeds. TRANSACTION WAS COMMITTED 4-26 * Run these two queries against the local database. */ SELECT stor_id, convert(char(10),ord_num), ord_date, qty, title_id G O FROM SALES SELECT title_id, ytd_sales FROM titles G O /* Sample data for local database after a fresh install of pubs */ stor_id ord_date qty title_id ------- ---------- --------------------------- ------ -------------------------------------- 6380 6871 Sep 14 1994 12:00AM 5 B U 1032 6380 722a Sep 13 1994 12:00AM 3 P S 2091 7066 A2976 May 24 1993 12:00AM 50 P C 8888

76 7066 QA7442.3 Sep 13 1994 12:00AM 75 P S 2091 7067 D4482 Sep 14 1994 12:00AM 10 P S 2091 7067 P2121 Jun 15 1992 12:00AM 40 T C 3218 7067 P2121 Jun 15 1992 12:00AM 20 T C 4203 7067 P2121 Jun 15 1992 12:00AM 20 T C 7777 7131 N914008 Sep 14 1994 12:00AM 20 P S 2091 7131 N914014 Sep 14 1994 12:00AM 25 M C 3021 7131 P3087a May 29 1993 12:00AM 20 P S 1372 7131 P3087a May 29 1993 12:00AM 25 P S 2106 7131 P3087a May 29 1993 12:00AM 15 P S 3333 7131 P3087a May 29 1993 12:00AM 25 P S 7777 7896 QQ2299 Oct 28 1993 12:00AM 15 B U 7832 7896 TQ456 Dec 12 1993 12:00AM 10 M C 2222 7896 X999 Feb 21 1993 12:00AM 35 B U 2075 8042 423LL922 Sep 14 1994 12:00AM 15 M C 3021 8042 423LL930 Sep 14 1994 12:00AM 10 B U 1032 8042 P723 Mar 11 1993 12:00AM 25 B U 1111 8042 QA879.1 May 22 1993 12:00AM 30 P C 1035 (21 row(s) aff e c t e d ) title_id ytd_sales -------- ----------- BU1032 4095 B U 1111 3876 BU2075 18722 BU7832 4095 MC2222 2032 MC3021 22246 MC3026 (null) PC1035 8780 PC8888 4095 PC9999 (null) PS1372 375 PS2091 2045 PS2106 111 PS3333 4072 PS7777 3336 TC3218 375 TC4203 15096 TC7777 4095 (18 row(s) aff e c t e d )

4 T- S Q L 77 4-27 /* Run this query against the remote database. */ SELECT * FROM bookinv /* Results for a bookinv created against a fresh install of pubs*/ title_id quantity location lastaudit -------- ----------- -------- --------------------------- BU1032 40 b u Jun 12 1991 12:00AM B U 1111 38 b u Jun 9 1991 12:00AM BU2075 86 b u Jun 30 1991 12:00AM BU7832 40 b u Jun 22 1991 12:00AM MC2222 20 m o Jun 9 1991 12:00AM MC3021 21 m o Jun 18 1991 12:00AM MC3026 10 U N Dec 11 1997 1:35AM PC1035 87 p o Jun 30 1991 12:00AM PC8888 40 p o Jun 12 1994 12:00AM PC9999 10 p o Dec 11 1997 1:35AM PS1372 3 p s Oct 21 1991 12:00AM PS2091 20 p s Jun 15 1991 12:00AM PS2106 1 p s Oct 5 1991 12:00AM PS3333 40 p s Jun 12 1991 12:00AM PS7777 33 p s Jun 12 1991 12:00AM TC3218 3 t r Oct 21 1991 12:00AM TC4203 50 t r Jun 12 1991 12:00AM TC7777 40 t r Jun 12 1991 12:00AM (18 row(s) affected) MS DTC S AVE TRAN S AVE BEGIN DISTRIBUTED TRANSACTION 4.6.4 DTC D T C 14 4.6.5 DTC Query Manager,

78 BEGIN DISTRIBUTED T R A N S A C T I O N 4.7 T- S Q L C A S E P R I N T s y s m e s s a g e s R A I S E R R O R