ISBN: 0-9761573-2-2 ISBN 13 978-0976157328 Library of Congress Number: 2005928015 570 pages Perfect bind - 9x7 PD 1205 Shelving:  Databases/SQL Server IT In Focus Series # 2   SQL Server External Stored Procedures Turbocharge database performance with C++ External Procedures Joseph Gama, P. J. Naughter     Retail Price $49.95 / £35.95 Key Features About the Authors Table of Contents Index Reader Comments Errata - Order now and get 30% off the retail price! - You also receive immediate online access to the code depot!   Only $32.95  (30% off) This unique book focuses on hypercharging SQL Server database performance with external procedures. SQL Server runs databases for financial, bank, biotech, Ecommerce, data warehousing, scientific applications, etc. which often require huge data processing with complex formulas or algorithms. Most of this work is done with TSQL stored procedures and .NET procedures (in SQL Server 2005), however neither one of them can achieve the performance and flexibility of external procedures which are natively compiled code. External procedures are analogous to stored procedures but with the power of C++ for CPU intensive algorithms, hardware/low level access, use of external libraries, etc. Database administrators/developers can create more efficient code and avoid expensive, slow and unreliable third party tools. There is also the extra bonus of having the source compiled, to protect the intellectual property. This book is intended for C++ programmers who want to learn about database programming and use their C++ skills seamlessly, and database administrators and/or programmers who need more performance, functionality and versatility than they can achieve with TSQL, .NET procedures or ADO.                         Create         super-fast           Stored        Procedures * See how to get 200x performance improvements over T-SQL * Learn secure encryption techniques for SQL Server data * Create object-oriented database procedures with SQL Server * See real-world examples of interfaces with Windows internals * Learn how to create external procedures for code 200x faster than TSQL * Use OO, MFC, SDK, ATL and access DLL functions from TSQL! * Protect your intellectual property with code that cannot be decompiled! * Receive complete background tutorials on TSQL with external procedures! * Get a complete tutorial covering all the TSQL background. * Learn to use the binaries with working TSQL samples. * See real world solutions for encryption, GIS, eCommerce, and system security. * Create stored procedures in JavaScript or VBScript with the revolutionary XP_RUNSCRIPT!   About the Authors:   Joseph Gama Joseph Gama is a software engineer, specialized in SQL Server. His popular database tutorials received very positive feedback from the Microsoft SQL Server community. He contributed over 10,000 lines of source code to the public domain, mostly TSQL, JavaScript and C/C++. Adding to that, he created and shared over 800 rules for Snort, half of them for Microsoft SQL Server. Joseph lives in California, in the Bay Area. His main interests are polyominoes, phonetics and linguistics.   P. J. Naughter Patrick Joseph (PJ) Naughter runs Naughter Software, a software development consultancy firm specializing in native mode Windows development. Before setting up Naughter Software, PJ was a technical architect and software developer working for Soft-ex Communications, a developer of PBX Call Monitoring software located in Dublin, Ireland. In his spare time, he provides open source and shareware of interest to  Windows developers via his web site at www.naughter.com. At last count, well over 230,000 lines of Win32, MFC & ATL C++ source code is available to download. PJ lives in rural Ireland, 50 miles south of Dublin. Outside of computers, he also has a keen interest in Astronomy, Pub Quizzes, 10-pin bowling, science fiction and the odd game of golf, Irish weather permitting of course.   Table of Contents: 1. Introduction to Extended Stored Procedures What is an XP - "extended stored procedure"? Why use XP's? How to create XP's? How to use XP's? How to call XP's? About the compilers/languages used to create XP’s Can VB create XP's SQL Server 2005 and extended stored procedures ODS API How to install the ODS code samples, libraries and headers Registering an XP from Enterprise Manager Using Query Analyzer Execution of Extended Stored Procedures XP Samples Parameter handling Data Types 2. Misc compilers and languages Microsoft Visual C++ - WIN32 DLL xp_hellovcdll Microsoft Visual C++ - WIN32 MFC DLL xp_hellomfc CodeWarrior xp_hellocw Borland C++ xp_hellobcc Dev-C++ xp_hellodevc MINGW xp_hellomw Intel C++ Compiler Digital Mars Notes about compilation Microsoft Platform SDK Debugging an XP Execution errors 3. A C++ Class Framework for XP's (XP++) Exporting the function Support for __GetXpVersion Implement encapsulation by using C++ classes Automatically link to the correct ODS import library Encapsulate verification and setup of parameters. Provide automatic conversion of parameter data types Encapsulate the SDK calls provided by ODS Reduce the number of parameters required by ODS Track Memory Leaks Using the framework Other Examples Summary 4. Network Messaging (XP_NETSEND) Implementation XP_NET_ADDNAME XP_NET_DELNAME XP_NET_SEND Testing the Code Applications of XP_NETSEND Summary 5. SMTP Mailing (XP_SMTPSENDMAIL) Choice of Mail Library Using CDO for Windows 2000 Implementation Details Code Highlights Applications of XP_SMTPSENDMAIL Summary 6. NT Event Log (XP_NTLOG) Implementation XP_NTLOG_INSTALL XP_NTLOG_UNINSTALL XP_NTLOG_ERROR, XP_NTLOG_WARNING & XP_NTLOG_INFORMATIONAL Testing the Code Applications of XP_NTLOG Summary 7. Disk Logging (XP_DISKLOG) Implementation Testing the Code Summary 8. Audio Notification (XP_NOTIFY) Initial Implementation XP_SIMPLE_MESSAGEBEEP, XP_ICONASTERISK_MESSAGEBEEP, XP_ICONEXCLAMATION_MESSAGEBEEP, XP_ICONHAND_MESSAGEBEEP, XP_ICONQUESTION_MESSAGEBEEP and XP_OK_MESSAGEBEEP XP_BEEP XP_PLAYSOUNDFILE and XP_STOPSOUND XP_PLAYSOUNDALIAS XP_PLAYSOUNDMEMORY Testing the Code Applications of XP_NOTIFY Summary 9. Encoding HTML special characters (XP_HTMLENCODE) Implementation Testing the Code Applications of XP_HTMLENCODE XP_HTMLENCODE2 Summary 10. Cryptography (XP_CRYPTOAPI) Hashing Symmetric Encryption / Decryption Hash Message Authentication Code Asymmetric Encryption / Decryption Message Signing / Verification Public Key Management XP_CRYPTOAPI Usage Applications of XP_CRYPTOAPI Summary 11. Random Data (XP_RAND) Implementation Testing the Code Applications of XP_RAND Summary 12. INI File Manipulation (XP_INI) Implementation XP_INI_WRITESTRING XP_INI_WRITEINTEGER XP_INI_READSTRING XP_INI_READINTEGER XP_INI_GETSECTIONS XP_INI_READSECTION XP_INI_DELETESECTION XP_INI_DELETEKEY XP_INI_GETKEYS XP_INI_WRITE XP_INI_READ Testing the Code Summary 13. Retrieving free disk space (XP_DISKSPACE) Implementation Testing the Code Summary 14. Retrieving CPU usage (XP_CPUUSAGE) Implementation Testing the Code Applications of XP_CPUUSAGE Summary 15. Raw sockets sample (XP_RAWIP) Implementation Testing the Code Summary   16. Geographical Information Systems (XP_GIS) Introduction The Shape of the Earth Datums Coordinate Systems and UTM Implementation XP_GIS_ELLIPSOIDINFO XP_GIS_DATUMINFO XP_GIS_DATUMTRANSFORM XP_GIS_LL2UTM XP_GIS_UTM2LL XP_GIS_DISTANCE_BETWEEN Testing the Code Summary 17. Regular Expressions (XP_REGEXP) Implementation Testing the Code Applications for XP_REGEXP Summary 18. A Multiple Use XP (XP_SERVERREACHABLE) Initial Creation Adding COM / Win32 DLL Support Adding XP Support Testing the Code (XP Support) Testing the Code (Win32 DLL Support) Testing the Code (COM Support) Summary 19. Computational Astronomy (XP_ASTRO) Implementation XP_ASTRO_DATE2JULIAN & XP_ASTROJULIAN2DATE XP_ASTRO_DELTAT XP_ASTRO_SPRING_EQUINOX, XP_ASTRO_SUMMER_SOLSTICE, XP_ASTRO_AUTUMN_EQUINOX & XP_ASTRO_WINTER_SOLSTICE XP_ASTRO_MOONPHASEK & XP_ASTRO_MOONPHASE Testing the Code Summary 20. IP Configuration (XP_IPConfig) IPConfig IPConfig Syntax Implementation IP Helper Testing the Code Summary 21. Beyond C/C++ (XP_RUNSCRIPT) Why the Need? sp_OACreate, sp_OAMethod etc The Solution The Object Model Initial Creation The XPServer Object The XPParameters Object The XPParameter Objects Adding XP and Active Scripting Support Extending to support Visual Basic etc Testing the Code Summary 22. Protecting Intellectual Property with XP's Prior Art The valuable disclaimer Technical protections Stored procedures can be decrypted Market for 3rd party components Enforcing licenses with XP's XP's as black boxes 23. Managed Code in SQL Server 2005 .NET vs TSQL .NET vs XP’s The right choice Hello World in VB.NET Visual Studio 2005 Stored Procedures with .NET code Compiling from the Command Prompt Express Manager Levenshtein distance in .NET Comparing Rijndael encryption Comparing several MD5 implementations 24. Security in SQL Server 2000 Port security SQL Injection Cross-site scripting (XSS) Buffer overflows Snort Security tools 25. From TSQL to XP's TEA encryption with TSQL TEA encryption with XP_CRYPTOAPI 26. SQL Server database tutorial SQL Server default databases Data types Data types for code and data structure use Data types for code use Data types for data structure use Data types for blobs Tables Data Integrity Referential Integrity Constraints Rules, Defaults, and User-Defined Data Types Views Joins Temporary tables Subqueries Derived table Cursors Indexes SQL Server Collation Triggers Referential Integrity with triggers 27. SQL Server stored procedure tutorial Samples of SP’s Parameter handling SP with optional Input parameters Mean, Median and Modal with SP’s SP error management Rowset returning SP's Start up SP's Recursive SP's Crosstab queries Dynamic SQL 28. SQL Server User-defined functions (UDF's) Types of functions Scalar UDF's Inline Table-valued UDF's Multi-statement Table-valued UDF's Recursive UDF’s Jobs Alerts 29. System XP's Documented system XP's Undocumented system XP's Interesting undocumented system XP's File related XP's Registry related XP's One-way encryption SQL Mail Procedures 30. The Authors Joseph Gama Patrick Joseph (PJ) Naughter

Index Topics:

.NET .NET Common Language Runtime ' ': &apos @ @@Error @@ERROR @CaseSensitive @From @Match @Message @Reachable @RegExp @ServerToPing @SourceText @To _ __declspec(dllexport) __GetXpVersion _alloca function _ATL_MIN_CRT _CrtCheckMemory _CrtDumpMemoryLeaks _CrtMemCheckpoint _CrtSetBreakAlloc _xp instance < <: &lt > >: &gt DES Block Cipher DES Block Cipher A ACID AES algorithm Aggregate functions ALERT command alerts algorithm identifiers alternate key American National Standards Institute American Standard Code for Information Interchange ANSI ANSI SQL- ansi_nulls API System Stored Procedures Application Programming Interface ASCII ASP web application astronomical operations Asymmetric cryptography ATL Regular expression parser audible notification AutoConvertParameters B Basic Authentication Basic Multilingual Plane Beep begin_implement_xp_parameters begin_implement_xp_parameters(class) bigint Binary Coded Decimal binary data type bit block cipher block ciphers Bloodshed Dev-C++ Blowfish Block Cipher bMatch Borland C++ Builder BoundsChecker BUILD_XP_SERVERREACHABLE Business rules Byte Order Mark C C# c:\XPLOG_%Y_%m_%d.log C++ DLL Debug.dll C++ encapsulation CAADate CAASun candidate key Cartesian join CAtlRegExp CAtlREMatchContext CAutoWinHandle cc CComBSTR CCritSection CCritSectionLocker CDiskLogExtendedStoredProcedure CDiskLogExtendedStoredProcedure:: MapReplaceableStrings CDO for Windows cdosys.dll CExtendedStoredProcedure CExtendedStoredProcedure:: VerifyAndInitializeParameters CFile char CHECK constraint checksum Cipher Block Chaining clustered index Clustered tables CodeWarrior Development Studio COFFOMF collation collision resistance constraints Constraints container Control Codes Coordinated Universal Time correlated subqueries CRC checksums CreateFile CReferenceDatum cross join crosstab queries crosstab query CRYPT_SILENT CRYPT_VERIFYCONTEXT flag CryptAcquireContext CryptDeriveKey CryptGenRandom Cryptography Cryptography Service Provider CryptSignHash CSaveFileStoredProcedure CSimpleArray cursor cursor data type cursors CXPServerObj Cyclic Redundancy Checksum Cylink Mek Block Cipher

D Data Encryption Standard Data integrity Data pages data_to_hash_index datalength datetime datum DatumTransform dbdatetime decimal (p s) Declarative Referential Integrity declare_xp_parameters() default value defaults DeltaT Denial Of Service Dependency Walker DeregisterEventSource derived table DES Block Cipher DESX Block Cipher Digital Mars Digital Signal Standard digital signature Digital Signature Algorithm DirectX Audio APIs disclaimer disconnected recordset DistanceBetweenPoints Domain integrity Dynamic Link Library dynamic linking Dynamic SQL Dynamical Time E E-commerce Electronic Cookbook Mode ellipsoid value end_implement_xp_parameters end_implement_xp_parameters(class) Entity integrity enum Ephemeris Time EquatorialRadius Error Error ExecIsStartup Express Manager Extended MAPI extended stored procedure extents extern "C" F Fall Equinox feedback register Fibonacci sequence FIXED_INFO float foreign key format G General Extended Procedures Geographical Information Systems geoid GetAdaptersInfo GetDiskFreeSpaceEx GetFileAttributes GetLocalTime GetNetworkParams GetPrivateProfileInt GetPrivateProfileSection GetPrivateProfileSectionNames GetPrivateProfileString GetPrivateProfileStruct GetProcAddress GetSystemTime GetSystemTimes GetXpVersion global temporary table globally unique identifier Greenwich Mean Time H hash Hash Message Authentication Code Heaps Helix Stream Cipher HexDec high CPU utilization HMAC htmlencode I IActiveScript IActiveScriptParse IActiveScriptSite IANA IcmpCloseFile IcmpCreateFile IcmpSendEcho IcmpServerReachable IMPDEF implement_version_xp() IMPLIB include directory index columns indexes ini files initialization vector inner join InstanceName int Intel C++ Compiler Intellectual Property International Organization for Standardization InterNet Assigned Numbers Authority intrusion detection system Intrusion Detection System IP Helper API IP_ADAPTER_INFO IP_HDRINCL IPConfig ISAPI DLL IServerReachable ISO ISO IsReturnParam J Joins Julian Day Julian Ephemeris Day K keystream generator L Language ID Reference Number Levenshtein distance library directory Link LLtoUTM local temporary table Locale Identifier LOCALE_SYSTEM_DEFAULT Lunar Eclipse

M m_AlgID m_AllowableTypes m_bActualOutputParameter m_bAllowNull m_bOptional m_bType m_cbActualLen m_cbMaxLen m_Data m_DescribeRowData m_fNull m_nColumn m_nParameters m_ParameterType m_pData m_pESP m_pParameterData m_pParameterDefinitions m_precision m_RuntimeType m_scale m_szName MapReplaceableStrings MBCS strings mc.exe MD hash MD hash MD Hash Algorithm MD hash MD Hash Algorithm mean median Memory model Message Authentication Code MessageBeep MessageBox Microsoft CryptoAPI Microsoft Platform Software Development Kit Minimalist GNU for Windows Mixed extents modal algorithm Molodensky Datum Transform money MTA COM objects multithreaded code N named parameters Named Pipes National Institute of Standards and Technology National Security Agency nchar NetBios Message Alias NetMessageBufferSend NetMessageNameAdd NetMessageNameDel NibbleToChar non-clustered index non-correlated subqueries Northwind NOT NULL constraint NT Event Log NtQuerySystemInformation nvarchar O OLE Automation Extended Stored Procedures OneOverF one-time pad Open Data Services opends.dll opends.lib opendsbcc.lib OpenTextFile Optional parameters Osql –L outer full join outer left join outer right join Output P packet sniffer pages ParamStatus patindex PATINDEX Performance Data Helper Perl Compatible Regular Expressions library phases of the Moon PingMachine.cpp/h pivot PK plan creation PlaySound Popup messages precompiled headers primary key prior art pszServer public key cryptography Pubs pwdcompare pwdencrypt R raiserror rand function rand() random data RandomData RC Block Cipher RC RC Stream Cipher RC Block Cipher readtext real recursive function reference ellipsoid ReferenceDatum Referential integrity RegisterEventSource regular expression regular expressions Replication Procedures Rijndael algorithm RSA Rules S satellites value scalar system function scalar UDF schema binding scrollable cursor SDK Beep Secure Hash Algorithm Select Compiler self join ServerReachable SessionKeyAlgorithm SessionMessageLCID SetColumnData SetFilePointer SetOutputData SetupExpectedParameters SHA hash ShootOut.wav Skipjack Block Cipher smalldatetime smallint smallmoney SMTP SND_ALIAS SND_FILENAME SND_MEMORY SND_SYNC Snort Solar Eclipse sort order Soundex sp_OA* SP_OA* sp_OACreate sp_OADestroy sp_OAGetErrorInfo sp_OAGetProperty sp_OAMethod sp_OASetProperty sp_OAStop SPaddXP_CRYPTOAPI SPdropXP_CRYPTOAPI SPparseInputForRegex Spring Equinox SQL collation SQL Mail Procedures SQL Profiler Procedures SQL Server SQL Server Agent Procedure SQL.ReturnValue sql_variant SQL- standard SQLPing srv.h srv_convert srv_describe srv_getbindtoken srv_getdtcxact srv_got_attention srv_impersonate_client srv_paraminfo srv_paramname srv_paramnumber srv_paramsetoutput srv_paramstatus srv_pfield srv_pfieldex SRV_PROC srv_revert_to_self srv_rpcparams srv_senddone srv_sendmsg srv_sendrow srv_setcoldata srv_setcollen srv_willconvert srv_wsendmsg stack overwrite static linking stdafx.h stored procedures stream cipher stream ciphers subquery substring Summer Solstice switches Symmetric cryptography ciphers sysmessages system XP's SYSTEM_INFORMATION_CLASS

T table data type table variable table-valued system function table-valued UDF TCP/IP TEA TEA algorithm temporary table Temporary tables The Replacement Character timestamp data type Tiny Encryption Algorithm tinyint triggers Triggers U UDF UDFdecrypt UDFencrypt UDFRandom UDT undocumented system XP's Unicode Project Uniform extents UNIQUE constraint uniqueidentifier data type Universal Time Universal Transverse Mercator User Defined Data Type User Defined Function user defined functions User Defined Functions UTF- UTF- UTF- UTM UTM Easting UTM Northing UTM Zone V varbinary data type varchar variant data type VariantToXPData VB.NET vbc.exe Vernal Equinox Views Visual Studio W wav files waveOutWrite Web Server IIS WGS Windows collation Winsock Winter Solstice wrapper methods WritePrivateProfileInt WritePrivateProfileString WritePrivateProfileStruct X x_WinDLL.cpp XML data type XML Procedures xp_assert xp_astro xp_astro_autumn_equinox xp_astro_datejulian xp_astro_deltat xp_astro_moonphase xp_astro_moonphasek xp_astro_spring_equinox xp_astro_summer_solstice xp_astro_winter_solstice xp_astrojuliandate xp_beep xp_cmdshell xp_cpuusage xp_cryptoapi xp_cryptoapi.dll xp_disklog xp_diskspace xp_dsa_export xp_dsa_sign xp_ellipsoidinfo xp_gis xp_gis_datuminfo xp_gis_datumtransform xp_gis_distance_between xp_gis_ellipsoidinfo xp_gis_llutm xp_gis_umtll xp_hellobcc.cpp xp_hellobcc.dll xp_hellomfc.cpp xp_hellomfc.def xp_hellomfc.dll xp_hellovcdll xp_hellovcdll.cpp xp_hellovcdll.dll xp_htmlencode xp_htmlencode xp_iconasterisk_messagebeep xp_iconexclamation_messagebeep xp_iconhand_messagebeep xp_iconquestion_messagebeep xp_ini xp_ini_deletekey xp_ini_deletesection xp_ini_getkeys xp_ini_getsections xp_ini_read xp_ini_readinteger xp_ini_readsection xp_ini_readstring xp_ini_write xp_ini_writeinteger xp_ini_writestring xp_input_or_output_parameter xp_input_or_output_parameter_precision_scale xp_input_parameter xp_input_parameter_presicion_scale xp_int xp_ipconfig xp_net_addname xp_net_delname xp_net_send xp_netsend xp_notify xp_ntlog xp_ntlog_install xp_ntlog_uninstall xp_odbc xp_odbc++ xp_ok_messagebeep xp_output_parameter xp_palindrome xp_palindrome xp_parameter_definition xp_playsoundfile xp_playsoundmemory xp_pp.cpp xp_pp.h xp_pt_acsiitext xp_pt_asciitext xp_pt_empty xp_pt_tchartext xp_pt_unicode xp_pt_unicodetext xp_rand xp_randproc module xp_randproc.cpp xp_randproc.h xp_rawip xp_regexp xp_rijndael xp_rsa_export_encryptkey xp_rsa_export_signkey xp_rsa_md_sign xp_rsa_md_sign xp_rsa_md_sign xp_rsa_sha_sign xp_run_activex xp_runscript xp_runscript_disk xp_runscript_parameter xp_serverreachable xp_simple_messagebeep xp_smtpsendmail xp_stopsound xp_tea_encrypt xp_unicodetext xp_variant_parameter xp_variant_parameter_type XP++ XPSaveFile.cpp XPSaveFile.h

Reviews:

Errata:

“Super SQL Server Systems” Errata List

• “Microsoft Visual C++ - WIN32 DLL xp_hellovcdll” should read “Microsoft

Visual C++ - WIN32 DLL”

• “Microsoft Visual C++ - WIN32 MFC DLL xp_hellomfc” should read

“Microsoft Visual C++ - WIN32 MFC DLL”

• “CodeWarrior xp_hellocw” should read “CodeWarrior”

• “Borland C++ xp_hellobcc” should read “Borland C++”

• “Dev-C++ xp_hellodevc” should read “Dev-C++”

• “MINGW xp_hellomw” should read “MINGW”

• “Switches” should not appear in the TOC

• “Testing the XP” should not appear in the TOC

• “Support for __GetXpVersion....105” should not be indented and should read

“Support for __GetXpVersion....105”

• “Applications of xp_netsend” should not be indented and should read “Applications

of xp_netsend”

• “Applications of xp_smtpsendmail” should not be indented and should read

“Applications of xp_smtpsendmail”

• “Applications of xp_ntlog” should read “Applications of xp_ntlog”

• “Applications of xp_notify” should read “Applications of xp_notify”

• “Choice of Cryptography Library” to CRC32 Checksum Algorithm” should be

indented by one level.

• “RC2 Block Cipher” to “TEA Block Cipher” should be indented by one level

• “MS CryptoAPI-based HMAC implementations” should be indented by one

level

• “RSA” to “MS CryptoAPI-based Public Key Implementations” should indented

by one level

• “RSA Signing/Verification” to “MS CryptoAPI-based Signing/Verification

Implementations” should be indented by one level

• “Applications of xp_rand” should read “Applications of xp_rand”

• “E-commerce search based on xp_regexp” should read “E-commerce search on

xp_regexp”

• “The xpserver Object” should read “The XPServer Object”

• “Extending to Support Visual Basic etc” should read “Extending to support

Visual Basic etc”

• “TEA Encryption with xp_cryptoapi” should read “TEA Encryption with

xp_cryptoapi”

• “binary” to “variant Data Type” should be indented by one level

• “Decimal and numeric” should read “decimal and numeric”

• “variant Data Type” should read “variant Data Type”

• “Entity Integrity” to “Business Rules” should be indented by one level

• “Primary Key” and Foreign Key” topics should appear after “Referential

Integrity”

• “CHECK constraints” to “Table-level” Constraint” should be indented by one

level.

• “NOT NULL” to UNIQUE” should be indented by one level

• “With Check Option” should be indented by one level

• “Aggregate Functions” should appear after “User Defined Data Types”

• “Additional Information” should be indented by one level

• “Inner Join” to “ Self Join” should be indented by one level

• “Correlated Subqueries” and “Non-correlated Subqueries” should be indented by

one level

• “Clustered Indexes” and “Non-Clustered” Indexes” should be indented by one

level

• “AFTER Triggers” to “Using Views for Input and Output” should be indented

by one level

• “SP’S Returning Rows from an XP” and “SP’s Returning Rows from a Crafted

SELECT Statement” should not be implemented by one level

• “Documented System XP’s” should not be indented by one level

• “This is a popular SQL Server 10g migration service …” should read “This is a

popular SQL Server migration service …”

• "These one-time SQL Server consulting services commonly include:" sentence

should not be present.

• Page 5: “to the final produce you now have in your hands” should read “to the

final product you now have in your hands”.

• Page 8: “In this way, the application using the existing DLL to handle runtime

errors...” should read “In this way, the application using the existing DLL can

handle runtime errors...”

• Page 9: “extern “C” SRVRETCODE NameOFXP(SRV_PROC* srvproc) should

read “extern “C” SRVRETCODE NameOfXP(SRV_PROV* srvproc)

• Page 9: “A C #define preprocessor directive allows to define...” should read “A

C #define preprocessor directive allows you to define...”

• Page 12: “On The Master database, SQL Server must be informed about the

extended stored procedure using query analyzer or other tool:” should read “On

The Master database, SQL Server must be informed about the extended stored

procedure using query analyzer:”

• Page 13: “where production quality ready to run XP’s is developed” should read

“where production quality ready to run XP’s are developed”

• Page 15: “http://msdn.microsoft.com/” should appear without underlines

• Page 16: “Normally, when the XP has only a few parameters, they would then be

passed by and there...” should read “Normally, when the XP has only a few

parameters, they would be passed by position and there...”

• Page 16: “... and whether it is a NULL flag.” should read “... and whether it is a

database NULL value.”

• Page 18: “One can think of this as using the following analogy:” should read

“One can think of this using the following analogy:”

• Page 32 “11. Click on Build -> Set Active Configuration” has an unnecessary

space after 11.

• Page 33: “Obviously, NOT NULL returns FALSE if a variable or expression are

NULL” should read “Obviously, NOT NULL returns FALSE if a variable or

expression is NULL”

• Page 54: “The following table shows the equivalent data types for ODS, ADO,

C++, SQLDMO, ODBC and XP++:” should read “The following table shows

the equivalent data types for ODS, ADO, C++, SQLDMO, ODBC and XP++

(which is discussed in Chapter 3):”

• Page 55: The entry for datetime should read “datetime 8-byte datetime datatype

SRVDATETIME DBDATETIME DBDATETIME struct” in Table 1.2.

• Page 55: The entry for smalldatetime should read “smalldatetime 4-byte datetime

datatype SRVDATETIM4 DBDATETIM4 DBDATETIM4 struct” in Table 1.2.

• Page 56: There are duplicate entries for XP_PT_BIT, XP_PT_DECIMAL and

XP_PT_NUMERIC in Table 1.2

• Page 63: “The Extended ASCII Character Set use the bit seven;” should read

“The Extended ASCII Character Set uses bit seven;”

• Page 64: “The rendered version of the character is called glyph and one glyph or

more glyphs” should read “The rendered version of the character is called a

glyph and one or more glyphs”

• Page 69: “; followed by Win43 Dynamic Link Library” should read “; followed by

Win32 Dynamic Link Library”

• Page 72: “… Include Also, Library Files should have...” should read “Include, Also

Library Files should have...”

• Page 72: “Select Project -> Settings under the tab link check Project Options.

Make sure that opends60.lib is present” should read “Select Project -> Settings and

under the link tab, select the input category and make sure that opends60.lib is

present in the Object/library modules edit box.”

• Page 73: “3. Define the extended stored procedure name as xp_hellomfc as a

Regular DLL using shared MRC DLL.” should read “3. Define the extended

stored procedure name as xp_hellomfc as a Regular DLL using shared MFC

DLL.”

• Page 74: “9. Select Project -> Settings under the tab link check Project Options.

Make sure that opends60.lib is present” should read “Select Project -> Settings and

under the link tab, select the input category and make sure that opends60.lib is

present in the Object/library modules edit box.”

• Page 82: “Also turn off the Output setting...” should read “Turn off the output

setting...”

• Page 83: “Attempting the DLL from the code...” should read “Attempting to run

the DLL from the code...”

• Page 89: “The first step is to install the binaries to folder such as ...” should read

“The first step is to install the binaries to a folder such as ...”

• Page 99: “An XP is basically a regular DLL, but it is not called directly.” Should

read “An XP is basically a regular DLL meaning it cannot run by itself.”

• Page 100: “Selecting YES should cause a message appear in the console...”

should read “Selecting YES should cause a message to appear in the console...”

• Page 101: “The following errors occur when trying to use the XP.” should read

“The following errors can occur when trying to use an XP.”

• Page 106: “RUN is the method...” should read “Run is the method...”

• Page 108: “xp_input_or_output_parameter_precision_scale: ... variables set to userdefined

values..” should read “xp_input_or_output_parameter_precision_scale: ...

variables set to user-defined values.”

• Page 109: “As an example, following would be ...” should read “As an example

the following would be ...”

• Page 111: “The CExtendedStoredProcedure also...” should read “The

CExtendedStoredProcedure class also...”

• Page 112: “...which is the file name to be in a format is needed so that the user...”

should read “...which is the file name has to be in a format so that the user...”

• Page 115: “In the Run method, the xp_ttext macro to get...” should read “In the

Run method, the xp_ttext macro is used to get...”

• Page 117: “... also known as a return, parameter.” should read “... also known as a

return parameter.”

• Page 121: “should override the DisplayUsage and RUN methods...” should read

“should override the DisplayUsage and Run methods...”

• Page 124: “…to achieving this functionality is to use xp_cmdshell XP,” should read

“…to achieving this functionality is to use the xp_cmdshell XP,”

• Page 126: “…as a UNICODE string prior to using it in the call to

NetMessageNameDel:” should read “…as a UNICODE string prior to using it in

the call to NetMessageNameAdd:”

• Page 126: “The underlying SDK function used in this XP is NetMessageNameAdd”

should read “The underlying SDK function used in this XP is

NetMessageNameDel”

• Page 130: “… through the following SQL Mail stored procedures: xp_startmail,

sp_stopmail, xp_findnextmsg, xp_readmail, xp_delete-mail, xp_sendmail and

sp_processmail” should read “… through the following SQL Mail stored

procedures: xp_startmail, sp_stopmail, xp_findnextmsg, xp_readmail, xp_deletemail,

xp_sendmail and xp_processmail”

• Page 131: “It only compatible with MS Exchange Server; …” should read “It is

only compatible with MS Exchange Server; …”

• Page 136: “To specify multiple files, individual files cane be separated…” should

read “To specify multiple files, individual files can be separated…”

• Page 139: “They could be taken from the table itself or from other source.”

should read “They could be taken from the table itself or from some other

source.”

• Page 140: “In this chapter, a DLL called xp_ntlog will be used.” should read “In

this chapter, a DLL called xp_ntlog will be created.”

• Page 142: “IMPLEMENT_XP(xp_ntlog_ERROR,

CNTLogErrorExtendedStoredProcedure)

IMPLEMENT_XP(xp_ntlog_WARNING,

CNTLogWarningExtendedStoredProcedure)

IMPLEMENT_XP(xp_ntlog_INFORMATIONAL,

CNTLogInformationalExtendedStoredProcedure)

IMPLEMENT_XP(xp_ntlog_INSTALL,

CNTLogInstallExtendedStoredProcedure)

IMPLEMENT_XP(xp_ntlog_uninstall,

CNTLogUninstallExtendedStoredProcedure)”

should read

“IMPLEMENT_XP(XP_NTLOG_ERROR,

CNTLogErrorExtendedStoredProcedure)

IMPLEMENT_XP(XP_NTLOG _WARNING,

CNTLogWarningExtendedStoredProcedure)

IMPLEMENT_XP(XP_NTLOG _INFORMATIONAL,

CNTLogInformationalExtendedStoredProcedure)

IMPLEMENT_XP(XP_NTLOG_INSTALL,

CNTLogInstallExtendedStoredProcedure)

IMPLEMENT_XP(XP_NTLOG_uninstall,

CNTLogUninstallExtendedStoredProcedure)”

• Page 145: “EXEC sp_addextendedproc 'xp_ntlog_install', 'xp_ntlog.dll'

EXEC sp_addextendedproc 'xp_ntlog_uninstall', 'xp_ntlog.dll'

EXEC sp_addextendedproc 'xp_ntlog_error', 'xp_ntlog.dll'

EXEC sp_addextendedproc 'xp_ntlog_warning', 'xp_ntlog.dll'

EXEC sp_addextendedproc 'xp_ntlog_informational', 'xp_ntlog.dll'”

should read

“EXEC sp_addextendedproc 'XP_NTLOG_INSTALL', 'xp_ntlog.dll'

EXEC sp_addextendedproc 'XP_NTLOG_UNINSTALL', 'xp_ntlog.dll'

EXEC sp_addextendedproc 'XP_NTLOG_ERROR', 'xp_ntlog.dll'

EXEC sp_addextendedproc 'XP_NTLOG_WARNING', 'xp_ntlog.dll'

EXEC sp_addextendedproc 'XP_NTLOG_INFORMATIONAL',

'xp_ntlog.dll'”

• Page 146: “EXEC master..xp_ntlog_INSTALL ‘xp_ntlog Example’” should read

“EXEC master..XP_NTLOG_INSTALL ‘XP_NTLOG Example’”

• Page 146: “EXEC master..xp_ntlog_INFORMATIONAL ‘xp_ntlog Example’

‘Hello World'” should read “EXEC master..XP_NTLOG_INFORMATIONAL

‘XP_NTLOG Example’ ‘Hello World'”

• Page 147: “EXEC master..xp_ntlog_WARNING 'Server TKF3-Backup',

'Successful backup warning:tape not removed yet'

EXEC master..xp_ntlog_INFORMATIONAL 'Server TKF3-Backup',

'Successful backup date:5/7/2004'”

should read

“EXEC master..XP_NTLOG_WARNING 'Server TKF3-Backup', 'Successful

backup warning:tape not removed yet'

EXEC master..XP_NTLOG_INFORMATIONAL 'Server TKF3-Backup',

'Successful backup date:5/7/2004'”

• Page 151: “…from the SDK function GetDateFormat using the flag date_shortdate”

should read “…from the SDK function GetDateFormat using the flag

DATE_SHORTDATE”

• Page 152: “…from the SDK function GetDateFormat using the flag date_longdate”

should read “…from the SDK function GetDateFormat using the flag

DATE_LONGDATE”

• Page 156: “EXEC sp_addextendedproc 'xp_disklog', 'xp_disklog.dll'” should

read “EXEC sp_addextendedproc 'XP_DISKLOG', 'xp_disklog.dll'”

• Page 156: “EXEC master..xp_disklog ‘c:\XP_LOG.log’, ‘Hello World’” should

read “EXEC master..XP_DISKLOG ‘c:\XP_LOG.log’, ‘Hello World’

• Page 157: “EXEC master..xp_disklog ‘c:\XPLOG_%Y_%m_%d.log’,

‘%h:%i:%s Hello World’” should read “EXEC master..XP_DISKLOG

‘c:\XPLOG_%Y_%m_%d.log’, ‘%h:%i:%s Hello World’”

• Page 164: “… where displaying a popup on the<EOL><INDENT>server ...”

should read “… where displaying a popup on the<EOL>server ...”

• Page 168: “In JScript, a message box is created with the ALERT command.”

should read “In JScript, a message box is created with the Alert command.”

• Page 176: “… for use with the Digital Signal Standard (DSS)… ” should read

“… for use with the Digital Signature Standard (DSS) …”

• Page 176: “To initialize MS Crypto API, a Cryptography Service Provider(CSP)

must …” should read “To initialize the MS Crypto API, a Cryptography Service

Provider (CSP) must… ”

• Page 178: “…, and the data_to_hash_index is a simple define to make…” should

read “…, and the DATA_TO_HASH_INDEX is a simple define to make…”

• Page 184: “The case may be that it is only available on Windows CE …” should

read “It may be the case that it is only available on Windows CE …”

• Page 186: “www.spyrus.com” should appear without underlines

• Page 187: “www.naughter.com” should appear without underlines

• Page 188: “If the MS CryptoAPI were in use, these steps would not be done by

the sample code, …” should read “If the MS CryptoAPI were in use, these steps

would not need be done by the sample code, …”

• Page 189: “The HMACimplementation is quite similar to the ...” should read

“The HMAC implementation is quite similar to the …”

• Page 204: “It uses the trick of getting a value from a view with rand() in a

calculated column” should read “It uses the trick of getting a value from a view

with RAND() in a calculated column”

• Page 205: “rand() is a wrapper for the C runtime function …” should read

“RAND() is a wrapper for the C runtime function …”

• Page 205: “rand() will return a float, which is 8 bytes, …” should read “RAND()

• Page 205: “For example, casting rand() to a bigint …” should read “For example,

casting RAND() to a bigint …”

• Page 205: “… will require many calls to the function rand() while xp_rand would

need only one.” should read ”… will require many calls to the function RAND()

• Page 205: “… does the same as UDFRandom but using xp_rand instead of rand():”

should read “… does the same as UDFRandom but using xp_rand instead of

RAND():”

• Page 206: “Using xp_rand is twice as slow as rand() for single integers; …” should

read “Using xp_rand is twice as slow as RAND() for single integers; …”

• Page 206: “First, implement this function using rand().” should read “First,

implement this function using RAND().”

• Page 206: “The UDF has a loop with a number of calls to rand().” should read

“The UDF has a loop with a number of calls to RAND().”

• Page 207: “… such as the one shipped with the C compiler or TSQL’s rand()

function.” should read “… such as the one shipped with the C compiler or

TSQL’s RAND() function.”

• Page 226: “Schedule name Check CPU sched” should read “Schedule name Check

CPU sched”

• Page 229: “… and the tenth more recent record” should read “… and the tenth

most recent record.”

• Page 229: “If the most recent record and tenth more recent one are …” should

read “If the most recent record and tenth most recent one are …”

• Page 233: “… still detects old threats or that is effective against new ones.”

Should read “… still detects old threats or is effective against new ones.”

• Page 233: “The next chapter will focus more attention to Geographical

Information Systems (GIS) …” should read “The next chapter will look at

Geographical Information Systems (GIS) …”

• Page 240: “The satellites value is the number of measurements…” should read

“The Satellites value is the number of measurements…”

• Page 240: “http://www.colorado.edu...” should appear without underlines

• Page 241: “http://home.hiwaay.net...” should appear without underlines

• Page 241: “http://www.colorado.edu...” should appear without underlines

• Page 242: “http://www.gpsy.com...” should appear without underlines

• Page 247: “http://sqlteam.com...” should appear without underlines

• Page 269: “The complete datetime data type is presented to an XP as a

dbdatetime structure,…” should read “The complete datetime data type is

presented to an XP as a DBDATETIME structure,…”

• Page 270, 271, 273: All occurrences of “xp_astro_date2julian” should read

“XP_ASTRO_DATE2JULIAN”

• Page 270, 271: All occurrences of “xp_astro_julian2date” should read

“XP_ASTRO_JULIAN2DATE”

• Page 272, 273, 275: All occurrences of “xp_astro_deltat” should read

“XP_ASTRO_DELTAT”

• Page 274: “This value is used by the AA+ class framework functions, the public

methods …” should read “This value is used by the AA+ class framework

functions, namely the public methods …”

• Page 276: All occurrences of “xp_astro_moonphasek” should read

“XP_ASTRO_MOONPHASEK”

• Page 276: All occurrences of “xp_astro_moonphase” should read

“XP_ASTRO_MOONPHASE”

• Page 282: “char* pszHostName = “”;” should read “char*

pszHostName = "Host Name";”

• Page 289: “The xpserver Object” heading should read “The XPServer

Object”

• Page 290: “SendDone*” in Table 21.1 should read “SendDone”

• Page 293: “, the result would have the same number of items in …” should read

“, we will have the same number of items in …”

• Page 294: “, the JScript code is allowed to look like:” should read “, JScript code

which returns a sample recordset is allowed to look like:”

• Page 303: ”… is used when any script requests the parameters property …” should

read ”… is used when any script requests the Parameters property …”

• Page 311: “… to cover scripting languages such as JScript, VBScript, as well …”

should read “… to cover scripting languages such as JScript and VBScript, as well

…”

• Page 320: “Alternatively, using xp_runscript might be an option when there is a lot of code

in TSQL which is calling into the COM object to use.” should read “Alternatively, using

xp_runscript might be an option when there is a lot of code in TSQL which is

calling into the COM object to use.”

• Page 335: “If the application using SQL Server is running in the same box,”

should read “If the application using SQL Server is running on the same box,”

• Page 335: “the best solution is to remove all protocols, netlibs in the Server

Network Utility.” should read “the best solution is to remove all protocols in the

Server Network Utility.”

• Page 335: “The client should have set enabled shared memory protocol.” Should

read “In the client Network Utility you should make sure the “Enable shared

memory protocol” option is checked”

• Page 335: “To enumerate a list of SQL Servers in a network can be accomplished

by either running Osql –L or sending a broadcast UDP packet to port 1434.”

should read “To enumerate a list of SQL Servers in a network you can use Osql -

• Page 235: “ChipAndrews, Rajiv Delwadia and …” should read “Chip Andrews,

Rajiv Delwadia and …”

• Page 337: “…, which is the TCP access to the database, usually remote.” should

read “…, which is the TCP port used to access the database, usually from a

remote machine.”

• Page 337: “However, 1434, the UDP used to access …” should read:
  
   “However, port 1434 which is the UDP port used to access …”

• Page 337: “…, SQL Server will take this port and used it thereafter.” should read:
  
   “…, SQL Server will take this port and use it thereafter.”

• Page 347: “SELECT username from Table_users WHERE logname=’mike’ AND userpassword=’a1234’” should read:
  
   “SELECT username from Table_users WHERE logname=’mike’ AND userpassword=’ekim’”

• Page 348: “…, the input for log name would be ‘OR 1=1-- and the password …“should read:
  
   “…, the input for log name would be ‘OR 1=1—’ and the password…“

• Page 350: “EXEC validate_user ‘mike’, ‘a1234’” should read:
  
  “EXEC validate_user ‘mike’, ‘ekim’”

• Page 351: “The following presents a possible problems situation:” should read:
  
  “The following presents a possible problem situation:”

• Page 353: “Having validation in the client side is okay for performance, …” should read:
  
   “Having validation on the client side is okay for performance, …”

• Page 390: Figure 26.2 appears blurred due to sizing issues

• Page 399: “real is a floating point numeric valuewith valid values …” should read:
  
  “real is a floating point numeric value with valid values …”

• Page 403: “… and maximum length, available through SQL_VARIANT_PROPERTY operator.” should read:
  
   “… and maximum length, available through the SQL_VARIANT_PROPERTY operator.”

• Page 403: “… or it will act as an alias for another curser, …” should read “… or it will act as an alias for another cursor, …”

• Page 411: “Figure 26.8: Table publishers” should read “Figure 26.8: Table publishers”

• age 414: “Other than that, the code is very flexible and with access to conceptual tables providing direct access to the modified rows.” should read:
  
  “Other than that, the code is very flexible and includes access to conceptual tables which provide direct access to the modified rows.”

• Page 415: “… will cause all the code to be changed and carefully reviewed.” should read:
  
  “… may require all the code to be carefully reviewed and even changed.”

• Page 417: “… because an integer comparison of 32 bits in a 32 bit machine …” should read:
  
   “… because an integer comparison of 32 bits on a 32 bit machine …”

• Page 435: “An outer join returns all the rows from both tables, if there is …” should read:
  
   “An outer join returns all the rows from both tables. If there is …”

• Page 454: “Kana character types ? (is ‘<BOX SYMBOL>’=’<BOX SYMBOL>’” should read:
  
   “Kana character types ? (is ‘ぁ’=’ァ’)”

• Page 485 “If an application connects to SQL Server through ADO, ActiveX Data Objects and …” should read:
  
  “If an application connects to SQL Server through ADO (ActiveX Data Objects) and …”

• Page 508: “A special type of system functions is the configuration …” should read:
  
  “A special type of system function is the configuration …”

• Page 511: “…, and the factorial of a negative number is an undefined.” should read:
  
   “…, and the factorial of a negative number is undefined.”

• Page 526: Figure 27.33 appears blurred due to sizing issues

• Page 545: “According to the returned value functions, including UDF’s, …”
  should read:
  
   “According to the returned value, functions including UDF’s, …”

• Page 570: Table 29.3 “sp_trace reate” should read “sp_trace_create”

• Page 570: Table 29.3 “sp_trace_ etevent” should read “sp_trace_setevent”

• Page 570: Table 29.3 “sp_trace_ etstatus” should read “sp_trace_setstatus”

• Page 570: Table 29.3 “sp_trace_ eneratevent” should read:
  
  "sp_trace_generatevent”

• Page 573: “http://www.mssqlcity.com...” should appear without underlines