Navision showing Monday as first day of week

SQL Server and English-based sql servers have Sunday as the first day of week.
Our Navision day of week is based off of Monday.
but you can set the first workday in SQL Server quite easily by setting the DATEFIRST statement

try this:
http://msdn.microsoft.com/en-us/library/ms187766(SQL.90).aspx

Navision showing Monday as first day of week

SQL Server and English-based sql servers have Sunday as the first day of week.
Our Navision day of week is based off of Monday.
go fig.
try this:
http://msdn.microsoft.com/en-us/library/ms187766(SQL.90).aspx

Navision Report Triggers

So far, I have data triggers and report triggers:

ONPreDataItem()
This trigger is executed before a data item is processed,
but after the associated variable is initialized and table views and filters are set.
reference: http://msdn.microsoft.com/en-us/library/dd355150.aspx

OnAfterGetRecord()
Use this trigger to calculate variables that depend on the current record. In a form with a table box,
this trigger is called for each record in the table box, as the records are updated.

OnPostDataItem()
This trigger executes after the last record in the data item is processed but before the OnPostReport Trigger or the OnPostDataPort Trigger is executed, if it is the last data item of the report or dataport.
Use this trigger to perform any cleanup or post processing needed after a data item is processed. For example, if you create a non-printing report where records are updated, you can update all the records with the modification date like this.
source: http://msdn.microsoft.com/en-us/library/dd355360.aspx

Report Triggers:
OnInitReport()
Executes when the report is loaded.

OnPreReport()
This trigger executes after the request form is run.
The table views and filters for the report data items are set while this trigger is executed.
As this trigger is executed after the request form is processed,
you have access to any filters the user has set. If you want to print the settings of these filters in the report, you can retrieve them using the following text string.

OnPostReport()
This trigger is executed after all data items are processed.
The trigger is not run if the report was stopped manually or by using the QUIT function
before processing had concluded.

Because this trigger is executed after the report is processed,
you can use it to inform users about the result of the report run.
For example, if some records could not be processed, you could inform the
user of this using the following code in this trigger.


About Hyperlinks:
OnCreateHyperLink and OnHyperLink
Ever wondered about these triggers in navision and what they are meant for. Well Navision offers a facility to create links to the different objects within it. Using this feature a hyperlink could be created for a Form in Navision from the desktop. Creation of the hyperlinks is simple open the desired Form and then use the File -> Send to option to place a hyperlink on the desktop.

When the send to desktop option is used the first thing the option does is to call the OnCreateHyperlink trigger with the URL as the parameter. The URL parameter can be modified in the trigger giving one a control over what needs to be placed in the URL string. The OnHyperLink trigger is called when the form is accessed using this link and the URL is passed in the trigger.
(source: http://santoshkmrsingh.blogspot.com/2008/11/oncreatehyperlink-and-onhyperlink.html)
OnCreateHyperlink()
http://msdn.microsoft.com/en-us/library/dd301225.aspx


ONHyperlink()
http://msdn.microsoft.com/en-us/library/dd355085.aspx

What is the difference between SET and SELECT variables

If you have a set, you know for sure it will return only one value.

if you use SELECT to set a variable:

select @myVariable = myTable.MyCol

or..

if you use the SET statement to set a variable

set @myVariable = myTable.MyCol

At certain times, with the SELECT statement, you can return more than one row.

That so much a problem?
Well, I think it potentially could be.
There can be no error that is returned when you get multiple rows.

here is a good source:
http://www.mssqltips.com/tip.asp?tip=1888

Eloquent way of logging errors in SQL Server2

I enjoy this template of error trapping.
It uses the TRY CATCH way of doing things, which is awesome.

To take this out for a spin:
Create the error table (just like inside Adventureworks):

CREATE TABLE [dbo].[ErrorLog]( [ErrorLogID] [int] IDENTITY(1,1) NOT NULL,[ErrorTime] [datetime] NOT NULL,[UserName] [sysname] NOT NULL,[ErrorNumber] [int] NOT NULL, [ErrorSeverity] [int] NULL, [ErrorState] [int] NULL,[ErrorProcedure] [nvarchar](126) NULL, [ErrorLine] [int] NULL,[ErrorMessage] [nvarchar](4000) NOT NULL,CONSTRAINT [PK_ErrorLog_ErrorLogID] PRIMARY KEY CLUSTERED ( [ErrorLogID] ASC)WITH (PAD_INDEX= OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY] GO

This table will be filled when there is an error.
The below procedure will be filled with SQL Server's famous @error_number

<><>

CREATE PROCEDURE [dbo].[uspLogError] @ErrorLogID [int] = 0 OUTPUT -- Contains the ErrorLogID of the row inserted -- by uspLogError in the ErrorLog table. AS BEGIN SET NOCOUNT ON; -- ditch the extra messages SET @ErrorLogID = 0; -- all is ok with the world BEGIN TRY IF ERROR_NUMBER() IS null -- no error? RETURN; IF XACT_STATE() = -1 -- uncommitable transaction? don't do any damage! BEGIN PRINT 'Cannot log error since the current transaction is in an uncommittable state. ' + char(10) + 'Rollback the transaction before executing uspLogError in order to successfully log error information.'; RETURN; END; INSERT [dbo].[ErrorLog] ( [UserName], [ErrorNumber], [ErrorSeverity], [ErrorState], [ErrorProcedure], [ErrorLine],[ErrorMessage] ) VALUES( --CONVERT(sysname, CURRENT_USER), -- current_user is the owner (ie: dbo.) CONVERT(sysname,system_user), ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), coalesce(ERROR_PROCEDURE(),'n/a'), ERROR_LINE(),ERROR_MESSAGE() ); -- Pass back the ErrorLogID of the row inserted SELECT @ErrorLogID =convert(nvarchar(16),@@IDENTITY); END TRY BEGIN CATCH PRINT 'An error occurred in stored procedure uspLogError: '; EXECUTE [dbo].[uspPrintError]; RETURN -1; END CATCH END;

Now, you can create any procedure and use the TRY CATCH method!

<><>

begin try begin transaction -- --insert a bad data type into a table insert into departments(deptnament) values ('a string that is way too long to append, way too long for sure absolutely no doubt to ') commit transaction End try Begin catch rollback transaction -----begin print error print'oops!' + char(10) + error_message() +char(10)+ 'error number: ' + convert(nvarchar(16),error_number()) ; ------end print error declare @err int set @err =error_number() ; execute uspLogError @err -- log the error here, passing the error # End catch -- test: select * from errorlog

And there you have your scripting for error logs. If you use a version of the above script, you will be able to track the errors way better
.

list local files from SQL Server 2005 (xp_cmdshell enabled)

-- found this on the sqlserver central website.

USE master
GO
CREATE PROCEDURE dbo.sp_ListFiles
@PCWrite varchar(2000),
@DBTable varchar(100)= NULL,
@PCIntra varchar(100)= NULL,
@PCExtra varchar(100)= NULL,
@DBUltra bit = 0
AS
SET NOCOUNT ON
DECLARE @Return int
DECLARE @Retain int
DECLARE @Status int
SET @Status = 0
DECLARE @Task varchar(2000)
DECLARE @Work varchar(2000)
DECLARE @Wish varchar(2000)

SET @Work = 'DIR ' + '"' + @PCWrite + '"'

CREATE TABLE #DBAZ (Name varchar(400), Work int IDENTITY(1,1))

INSERT #DBAZ EXECUTE @Return = master.dbo.xp_cmdshell @Work

SET @Retain = @@ERROR

IF @Status = 0 SET @Status = @Retain

IF @Status = 0 SET @Status = @Return

IF (SELECT COUNT(*) FROM #DBAZ) < 4
BEGIN
SELECT @Wish = Name FROM #DBAZ WHERE Work = 1
IF @Wish IS NULL
BEGIN
RAISERROR ('General error [%d]',16,1,@Status)
END
ELSE
BEGIN
RAISERROR (@Wish,16,1)
END
END
ELSE
BEGIN
DELETE #DBAZ WHERE ISDATE(SUBSTRING(Name,1,10)) = 0 OR SUBSTRING
(Name,40,1) = '.' OR Name LIKE '%.lnk'
IF @DBTable IS NULL
BEGIN
SELECT SUBSTRING(Name,40,100) AS Files
FROM #DBAZ
WHERE 0 = 0
AND (@DBUltra = 0 OR Name LIKE '% %')
AND (@DBUltra != 0 OR Name NOT LIKE '% %')
AND (@PCIntra IS NULL OR SUBSTRING(Name,40,100) LIKE @PCIntra)
AND (@PCExtra IS NULL OR SUBSTRING(Name,40,100) NOT LIKE @PCExtra)
ORDER BY 1
END
ELSE
BEGIN
SET @Task = ' INSERT ' + REPLACE(@DBTable,CHAR(32),CHAR(95))

+ ' SELECT SUBSTRING(Name,40,100) AS Files'

+ ' FROM #DBAZ'

+ ' WHERE 0 = 0'

+ CASE WHEN @DBUltra = 0 THEN '' ELSE ' AND Name LIKE ' + CHAR(39) + '% %' + CHAR(39) END

+ CASE WHEN @DBUltra != 0 THEN '' ELSE ' AND Name NOT LIKE ' + CHAR(39) + '% %' + CHAR(39) END

+ CASE WHEN @PCIntra IS NULL THEN '' ELSE ' AND SUBSTRING (Name,40,100) LIKE ' + CHAR(39) + @PCIntra + CHAR(39) END

+ CASE WHEN @PCExtra IS NULL THEN '' ELSE ' AND SUBSTRING

(Name,40,100) NOT LIKE ' + CHAR(39) + @PCExtra + CHAR(39) END

+ ' ORDER BY 1'
IF @Status = 0 EXECUTE (@Task) SET @Return = @@ERROR
IF @Status = 0 SET @Status = @Return
END
END
DROP TABLE #DBAZ
SET NOCOUNT OFF
RETURN (@Status)
GO


--And to test:
--EXECUTE sp_ListFiles 'c:\ftp',NULL,NULL,NULL,1

bcp format file: sundry thoughts

I am indexing this link for my own edification. 

http://msdn.microsoft.com/en-us/library/ms191516.aspx

What are the columns in that table?

Oh my.
I wish I found this many years earlier.

use MyDatabase
go

SELECT column_name, data_type
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'MyTable'
ORDER BY ORDINAL_POSITION

replace MyDatabase and MyTable with your database and table values

RESTORE HEADERONLY

If you don't know which backup files hold the right database to restore?
If you need to locate the correct backup file to restore, use RESTORE BACKUP WITH HEADERONLY option.

See msdn definition file:
http://msdn.microsoft.com/en-us/library/aa238455(SQL.80).aspx

How Heavy is my SQL Server database is being used?

I wrote this script after studying about I/O statistics.
This view is based on sys.dm_io_virtual file status
This looks at the physical file reads and writes, and how many times it stalled.
If it is stalling, you can dig deeper to see what is causing the stall… then you can partition the offensive table/object etc.

Cool system view!

-----------@Mydatabase-----------------------

-- type in name of database between the ''
-- or type in 'all' between the ''
--------------------------------------------

declare @Mydatabase nvarchar(255)
set @Mydatabase = 'all'
--------------------------------------------

if @Mydatabase = 'all'
begin

select

db_name(database_id)
as database_name,
file_name(file_id)
as file_name,
sample_ms,
num_of_reads,
num_of_bytes_read,
io_stall_read_ms,
num_of_writes,
num_of_bytes_written,
io_stall_write_ms,
io_stall,
size_on_disk_bytes

from
sys.dm_io_virtual_file_stats(null, null) ;
end
else
begin
declare @database int
set @database = db_id(@Mydatabase) ;

select

db_name(database_id)
as database_name,
file_name(file_id) as file_name,
sample_ms,
num_of_reads,
num_of_bytes_read,
io_stall_read_ms,
num_of_writes,
num_of_bytes_written,
io_stall_write_ms,
io_stall,
size_on_disk_bytes
from

sys.dm_io_virtual_file_stats(@database, null) ;
end

I keep losing this helpfile.. when I need it the most, it is located in the BOL labeled "sp_addlinkedserver (Transact-SQL)", if you scroll to the bottom of the article.

The frustrating thing about learning about linked servers is the coordination between the different types of connections! Viva la difference?

Remote OLE DB data source	OLE DB provider (@srvproduct)	product_name (whatever you want to name it "blah blah"…)	provider_name (@provider)	data_source (@datasrc)	location (anyone?)	provider_string (@providerstr?)	catalog (@catalog)
SQL Server	Microsoft SQL Server Native Client OLE DB Provider	SQL Server 1 (default)
SQL Server	Microsoft SQL Server Native Client OLE DB Provider		SQLNCLI	Network name of SQL Server (for default instance)			Database name (optional)
SQL Server	Microsoft SQL Server Native Client OLE DB Provider		SQLNCLI	servername\instancename (for specific instance)			Database name (optional)
Oracle	Microsoft OLE DB Provider for Oracle	Any2	MSDAORA	SQL*Net alias for Oracle database
Oracle, version 8 and later	Oracle Provider for OLE DB	Any	OraOLEDB. Oracle	Alias for the Oracle database
Access/Jet	Microsoft OLE DB Provider for Jet	Any	Microsoft.Jet. OLEDB.4.0	Full path of Jet database file
ODBC data source	Microsoft OLE DB Provider for ODBC	Any	MSDASQL	System DSN of ODBC data source
ODBC data source	Microsoft OLE DB Provider for ODBC	Any	MSDASQL			ODBC connection string
File system	Microsoft OLE DB Provider for Indexing Service	Any	MSIDXS	Indexing Service catalog name
Microsoft Excel Spreadsheet	Microsoft OLE DB Provider for Jet	Any	Microsoft.Jet. OLEDB.4.0	Full path of Excel file		Excel 5.0
IBM DB2 Database	Microsoft OLE DB Provider for DB2	Any	DB2OLEDB			See Microsoft OLE DB Provider for DB2 documentation.	Catalog name of DB2 database

Link SQL Server 2008 to 2005

Have on hand:

1. A name for your linked server you want to create; can be anything, just keep it clean.
   I will call mine MyLinkedServer
   
   
2. If default instance, name of the server only (as opposed to server/instance)
   Mine is MyComputerName\SQLEXPRESS2
   
   
3. If second instance, server/instance_name
   
   
4. It is presumed you have same login name as the server you are reaching over and linking to.
   I tested these two servers on the same computer (SQL Server 2005 & 2008)
   If they are not, impersonate yourself to the server you are trying to talk to:
   
   1. After scripting this out, go to SMSS and right-click your near MyLinkedServer à Properties
      
   2. Select the security option
      
   3. Add the login name the other server uses and click impersonate.
      

   EXEC
   master.dbo.sp_addlinkedserver
   

   @server =
   N'MyLinkedServer',
   

   @srvproduct=N'',
   

   @provider=N'SQLNCLI',
   

   @datasrc=N'
   MyComputerName\SQLEXPRESS2'
   

If you use the graphical interface, go for it. This way is easier for me.

Now you can call whatever database you have there. Here's what I did:

select
*
from [MyLinkedServer].[MyDatabase].dbo.[MyTable]

Obama speasks at Georgetown University, covers all religious symbols

I am infuriated with the covering up of religious symbols.

This is a part of our heritage, great and small.

It is an absolute insult to all who have immigrated here.

Sin of omission... omit our history of religous heritage...

Respectfully submitted, this is a passive way to say you reject your constituants

Last time statistics were done?

-- view the date the statistics were last updated:

select 'index Name' = i.[name],
'Statistics Date' = stats_date(i.[object_id], i.index_id)
from sys.objects o
inner join sys.indexes i
on o.name = 'Employee'
and o.[object_id] = i.[object_id]

-- if you need to update all the indexes:

update statistics HumanResources.Employee
with fullscan

dm_db_index_operational_stats

here's a method I found:

select object_schema_name(ddios.object_id) + '.' + object_name(ddios.object_id) as objectName,
indexes.name, case when is_unique = 1 then 'UNIQUE ' else '' end + indexes.type_desc as index_type,
page_latch_wait_count , page_io_latch_wait_count
from sys.dm_db_index_operational_stats(db_id(),null,null,null) as ddios
join sys.indexes
on indexes.object_id = ddios.object_id
and indexes.index_id = ddios.index_id
order by page_latch_wait_count + page_io_latch_wait_count desc

Find Fragmentation on a specific table

-- How to find fragmentation (yet another way)
declare @MyDatabase sysname,
@MyTable sysname

set @MyDatabase = 'Adventureworks'
set @MyTable = 'HumanREsources.Employee'
select
index_id,
avg_fragmentation_in_percent,
avg_page_space_used_in_percent
from
sys.dm_db_index_physical_stats(db_id(@MyDatabase),
object_id(@MyTable),
null,
null, 'detailed')
where
index_id <> 0


also, find row-level i/o, locking and latching issues and access method activity:
by the way, this is an excerpt from SQL Server Books online

DECLARE @db_id smallint;
DECLARE @object_id int;
SET @db_id = DB_ID(N'AdventureWorks');
SET @object_id = OBJECT_ID(N'AdventureWorks.Person.Address');
IF @db_id IS NULL
BEGIN;
PRINT N'Invalid database';
END;
ELSE IF @object_id IS NULL
BEGIN;
PRINT N'Invalid object';
END;
ELSE
BEGIN;
SELECT * FROM sys.dm_db_index_operational_stats(@db_id, @object_id, NULL, NULL);
END;
GO

Index Fragmentation for SQL Server

RE: Microsoft's MCTS 70-431 certification for SQL SErver 2005 : Fragementation Section

This section was horribly boring until I figured out a way to create fragementation on a table in a database.
Here's how I did it:

-- create database called repository
create database repository
go
-- create table:
USE [repository]
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tmp]') AND type in (N'U'))

BEGIN

CREATE TABLE [dbo].[tmp]
(
[id]
[int] IDENTITY(1,1) NOT NULL,
[field] [varchar](64) NULL,
PRIMARY KEY CLUSTERED
(
[id] ASC
)
WITH
(
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [repository]
)
ON [repository]
END

THEN:

1. I inserted 10,000 rows of random string data in the [field] column (I used Redgate's Data generator.. fantastic time-cutting tool)
2. I truncated some data to be smaller (random rows)
3. I added string data to be larger (random rows)
4. I deleted random rows.

What I got:

• I had fragmentation to play with (yay!).

THEN:

1. I did an alter on the ALTER index REORGANIZE. (reduced fragmentation by 1/2)
2. Then I used the alter index... rebuild. (yes, you guessed it: ALTER INDEX...REBUILD thwacks all of the fragmentation & performance is improved).

WHEN SHOULD I DEFRAG MY INDEXES?

here is a system tool to peek into what kind of fragmentation you have:

select * from sys.dm_db_index_physical_stats
-- (this system view is a "dynamic management function" = DMF. yet another acronymn to learn. yippee)

Do a INDEX....REORGANIZE if:

avg_page_space_used_in_percent is between 60 and 75
or
avg_avg_fragmentation_in_percent between 10 and 15

Do a INDEX....REBUILD if:

avg_page_space_used_in_percent is greater than 60
or
avg_fragmentation_in_percent is greater than 15

This is also a procedure I use to analyze the fragmentation (by database). I threw this together from a few sources & use it regularly:

EXEC dbo.sp_executesql @statement = N'
ALTER procedure [dbo].[database_fragmentation_check]
@database sysname
as
select object_name(dt.object_id),
si.name,
dt.avg_fragmentation_in_percent,
dt.avg_page_space_used_in_percent
from ( select object_id,
index_id, avg_fragmentation_in_percent,
avg_page_space_used_in_percent
from sys.dm_db_index_physical_stats(db_id(@database),
null,
null,
null,
''detailed'')
where index_id <> 0
) as dt -- does not return information about heaps
inner join sys.indexes si on si.object_id = dt.[object_id]
and si.index_id = dt.index_id
'

Select your data as XML Format (SQL Server)

Converting the data you already have inside your database

• You can create a select statement as you would already do, with a FOR XML at the end of your select statement
  
• Options (a la carte):
  

SELECT * FROM employees FOR XML auto

FOR XML RAW	The element defaults to "row" (can further customize) <row fistcolumn ="blah" secondcolumn="blah"/>;
FOR XML AUTO	The element name is called by <employees fistcolumn="blah" secondcolumn="blah" />
FOR XML PATH	Xml elements are parsed based on column <row> <firstColumn>blah</firstColumn> <secondColumn>blah</secondColumn> <row>

FOR EXPLICIT (explained):

Here is a select statement (same as above, but with the gory details to the EXPLICIT option

SELECT 1 AS Tag, NULL AS Parent, employeeID AS [employees!1!id], employee_fname AS[employees!1!employee_fname] FROM employees FOR XML EXPLICIT, ROOT('blah')

FOR XML EXPLICIT

FOR XML EXPLICIT requires the second column to hold NULL or nonnegative integers that represent XML parent tag IDs.

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