Extract recipe name from MasterCook [XML] recipe format

This is a simple example of how to extract a value from a mastercook 5(?) recipe format.

The XML Section of 70-431 was monotonous, so I used my recipe program that exports to MasterCook to help me out.

---------------------CODE--------------------------

declare @int int

declare @XML VARCHAR(MAX)

set @XML = '<mx2 source="MasterCook" date="January 23, 2009"> <Summ>

<Nam> Vegetarian Sloppy Joes </Nam></Summ> <RcpE name="Vegetarian Sloppy Joes" author="">

<Serv qty="6"/> <PrpT elapsed="0:00"/> <CatS> <CatT> Main Dish </CatT> <CatT>

try </CatT> </CatS>

<IngR name="vegetable oil" unit="tablespoon" qty="1"></IngR>

<IngR name="onion" unit="medium" qty="1">

<IPrp>

chopped

</IPrp>

</IngR>

<IngR name="green pepper" qty="1">

<IPrp>

chopped

</IPrp>

</IngR>

<IngR name="cooked brown rice" unit="cups" qty="3"></IngR>

<IngR name="Mexican style pinto beans" unit="16-ounce cans" qty="2">

<INtI>

4510

</INtI>

</IngR>

<IngR name="hickory smoke barbecue sauce" unit="cup" qty="3/4">

<INtI>

3962

</INtI>

</IngR>

<IngR name="whole grain buns (4 to 6)" qty="4">

<INtI>

3667

</INtI>

</IngR>

<DirS>

<DirT>

Heat oil in large skillet over medium-high heat. Add onion and green pepper; cook 2 to 3 minutes. Add rice, beans and barbecue sauce. Simmer 10 to 15 minutes, until heated through. Serve on buns.&#013;&#010;&#013;&#010;Makes 4 to 6 servings. </DirT> </DirS> <Srce> The Rice Council </Srce> <AltS label="Internet address" source="http://www.usarice.com/domestic/recipes/"/> <Nutr> Per Serving (excluding unknown items): 341 Calories; 6g Fat (14.8% calories from fat); 13g Protein; 61g Carbohydrate; 9g Dietary Fiber; 1mg Cholesterol; 864mg Sodium. Exchanges: 3 1/2 Grain(Starch); 1/2 Lean Meat; 1/2 Vegetable; 1 Fat; 0 Other Carbohydrates.

</Nutr>

</RcpE></mx2>'

exec sp_xml_preparedocument @int OUTPUT , @XML

select * from openxml
(
@int,
'mx2/Summ/Nam',1
)

where text is not null

--------------------------------

70-431 Certification Brain Dump (Configuration Section)

Configuring Server Security principals

(Lesson 4 in Microsoft cert book)

You can use the below as flash cards: print them out and fold over the answers

What are the two authentication modes. And Which one is reccomended	Windows and Mixed Mode. Windows Mode is recommended because you can completely rely on Active Directory's integrated security model.
What two ways can you re-configure the modes of authentication	Through SMSS graphically, right-click the server properties à Security Or Type script create login ….. domain\username from WINDOWS Or Create login …. With PASSWORD = '….'
What are the three other options the cert book offers with the create login script?	Must_change – login & pw must change at login Check_expiration – SQL Server will check the expiration of the login when the user logs in Check_policy – windows will apply the local windows password policy on the SQL Server logins
Why would you use SQL Server login (server thus set to Mixed mode)	If you have a contractor working on an external project and cannot log into the network, or if they can, the bandwidth is too large. If a legacy application requires a login as such. Then you can apply the windows rules to that user login by using the check_policy method
If you have mixed mode and create a user, what is the best practice to handle that user	Add an expiration date when you create a user (create user MyUser with PASSWORD = '…' check_expiration)
What are 8 SQL Server's fixed server roles	Sysadmin Serveradmin Setupadmin Securityadmin Processadmin Dbcreator Diskadmin Bulkadmin
Describe sysadmin fixed server role	Performs any activity in SQL Server. The permissions on this role all fixed server roles
Describe serveradmin fixed server role	Configure server-wide settings
Describe setupadmin fixed server role	Adds and removes linked servers and execute some system stored procedures. (ie: sp_serveroption)
Describe the securityadmin fixed server role	Manage server logins
Describe processadmin fixed server role	Manage processes running in an instance of SQL Server
Describe dbcreator fixed server role	Creates and alters databases
Describe diskadmin fixed server role	Manages disk files
Describe bulkadmin fixed server role	Execute the BULK INSERT statement

Create a user with an expiration date, enabling the password policy

CREATE LOGIN [login name] WITH PASSWORD='password', CHECK _EXPIRATION=ON, CHECK_POLICY=ON

Modify existing login

ALTER LOGIN [login name] WITH PASSWORD = 'password'

Disable login

ALTER LOGIN [login name] DIABLE

Drop a Windows login – or user:

DROP LOGIN [domain\user] or DROP LOGIN [username]

Get login information

Select * from Sys.sql_logins

Database Mail Architecture

There are four components to the Database mail Architecture
(These will be on exam 70-431) :

• configuration components
  database mail account - contains the smtp information you have entered for the server (I use my normal email account to play around with)
  database mail profile - a generic name you choose for other applications to handle email routing. "use profile that I called 'main' "... "I want to use the 'my alternate' email profile"...

• messaging components
  this is the host database that holds all teh objects. (hint-hint: host database is is msdb).


• 
  

• executable adn logging & auditing components

enable database mail

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO

---
to keep an eye on the emails going in and out, what is failing, try these (found at databasdsournal.com)

sysmail_allitems – This view allows you to return a record set that contains one row for each email message processed by Database mail.

sysmail_event_log – This view returns a row for each Windows or SQL Server error message returned when Database Mail tries to process an email message.

sysmail_faileditems – This view returns one record for each email message that has a status of failed.

sysmail_mailattachments – This view contains one row for each attachment sent

sysmail_sentitems – This view contains one record for every successfully email sent

sysmail_unsentitems – This view contains one record for every email that is currently in the queue to be sent, or is in the process of being sent.
-------
to clean up messages:
sysmail_delete_mailitems_sp – This SP permanently deletes email messages from the msdb internal Database Mail tables

sysmail_delete_log_sp - This SP deletes Database Mail log messages