MS SQL Server

==2014==

edition compare: http://msdn.microsoft.com/en-us/library/cc645993.aspx

official intro:   http://download.microsoft.com/download/D/F/2/DF25A191-1FA4-4BC2-925C-492D616CF7FA/Microsoft_Press_ebook_Introducing_Microsoft_SQL_Server_2014_PDF.pdf

Lab： http://technet.microsoft.com/en-us/virtuallabs/bb467605.aspx

Comparing Tabular and Multidimensional Solutions (SSAS): http://msdn.microsoft.com/en-us/library/hh212940.aspx

==Terminology==

All Write Papers: http://www.microsoft.com/sqlserver/2008/en/us/white-papers.aspx

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

Wiki:
SQL Server Integration Services (SSIS) is a component of the Microsoft SQL Server database software which can be used to perform a broad range of data migration tasks.

SQL Server Analysis Services - Multidimensional Data
http://technet.microsoft.com/en-us/library/bb522607.aspx

SQL Server Analysis Services - Data Mining
http://technet.microsoft.com/en-us/library/bb510517.aspx

You can predict what will happen in the future.
You do not have to create a data warehouse to do data mining; you can use tabular data from external providers, spreadsheets, and even text files.
You can also easily mine OLAP cubes created in Analysis Services.

Data Mining Concepts (Analysis Services - Data Mining)
http://technet.microsoft.com/en-us/library/ms174949.aspx

SSAS White Paper:
http://www.microsoft.com/sqlserver/2008/en/us/wp-sql-2008-analysis-services.aspx
Excel 2007 is a fully functional Analysis Services client.

Wiki:

Analysis Services includes a group of OLAP and data mining capabilities.
Contents

OLAP (online analytical processing) cubes can be thought of as extensions to the two-dimensional array of a spreadsheet.

Microsoft Data Access Components (commonly abbreviated MDAC; also known as Windows DAC) is a framework of interrelated Microsoft technologies that allows programmers a uniform and comprehensive way of developing applications that can access almost any data store. Its components include: ActiveX Data Objects (ADO), OLE DB, and Open Database Connectivity (ODBC). There have been several deprecated components as well, such as the Microsoft Jet Database Engine, MSDASQL (the OLE DB provider for ODBC), and Remote Data Services (RDS). Some components have also become obsolete, such as the former Data Access Objects API and Remote Data Objects.

Microsoft's ActiveX Data Objects (ADO) is a set of Component Object Model (COM) objects for accessing data sources. A part of MDAC, it provides a layer between programming languages and OLE DB (a means of accessing data stores, whether they be databases or otherwise, in a uniform manner).

OLE DB (Object Linking and Embedding, Database, sometimes written as OLEDB or OLE-DB) is an API designed by Microsoft for accessing data from a variety of sources in a uniform manner. It is a set of interfaces implemented using the Component Object Model (COM); it is otherwise unrelated to OLE. It was designed as a higher-level replacement for, and successor to, ODBC, extending its feature set to support a wider variety of non-relational databases, such as object databases and spreadsheets that do not necessarily implement SQL.

==Troubleshooting==

[2010-10-19T17:39:09+08:00] sql server can't login after deleting default only login: server manager, select service, startup => -m;<the original>

[2010-11-09T22:13:51+08:00] SQL Login fail: check server log for reason; change authentication type, restart service.

[2011-03-28T14:31:42+08:00] SSMS save large content: .rtf

[2011-04-28T16:49:36+08:00] Restore failed database is in use: Put the database in single-user mode

==Script==

[2010-09-10T00:09:03+08:00]    where [Modified Date] < CAST('2010-07-21 22:55:00' AS datetime2) = > regardless timezone

[2010-10-12T21:48:38+08:00] SWITCHOFFSET (CAST([ModifiedDate] + ' -07:00' as datetimeoffset(3)),'+08:00')

!=TODATETIMEOFFSET(CONVERT(datetime2(3),[Modified Date]),'+08:00')

--Drop All Views/[SP]--
Ref: http://sqlserver-qa.net/blogs/t-sql/archive/2008/05/20/4266.aspx

create procedure Usp_DropAllSPViews

as

declare @name varchar(100)

declare @xtype char(

declare @sqlstring nvarchar(1000)

declare AllSPViews_cursor cursor for

SELECT sysobjects.name, sysobjects.xtype

FROM sysobjects

join sysusers on sysobjects.uid = sysusers.uid

where OBJECTPROPERTY(sysobjects.id, N'IsProcedure') = 1

or OBJECTPROPERTY(sysobjects.id, N'IsView') = 1

open AllSPViews_cursor

fetch next from AllSPViews_cursor into @name, @xtype

while @@fetch_status = 0

begin

-- obtain object type if it is a stored procedure or view

if @xtype = 'P'

begin

set @sqlstring = 'drop procedure ' + @name

-- exec sp_executesql @sqlstring

set @sqlstring = ' '

end

-- obtain object type if it is a view or stored procedure

if @xtype = 'V'

begin

set @sqlstring = 'drop view ' + @name

exec sp_executesql @sqlstring

set @sqlstring = ' '

end

fetch next from AllSPViews_cursor into @name, @xtype

end

close AllSPViews_cursor

deallocate AllSPViews_cursor

go

exec Usp_DropAllSPViews

go