2011 is right around the corner should be a great year for most. 2010 was great, the past few months I’ve really been focused on WP7 Dev (Windows Phone 7 Development) and that is the reason there haven’t been blog posts the past few months. In 2010 the forth quarter I’ve also spoke a few times recently such as:
3. @ Devry presented the Sherstaff Tech app quickly after Devfish’s WP7Dev session
All there meetings were so much fun with some great people & technology there. Presenting @ FAU was special for me since that is my Alma mater.
2011 Goals – Events – More WP7Dev
2011 what I’d like to focus on is more WP7Dev; building more apps & improving my most popular apps. I want to continue to work diligently at MovieTickets.com . In January hopefully we’ll have another .NET meetup in WPB maybe on WCF. February will be the notorious South Florida Code Camp. I plan to present a new session called ‘Blendin WP7Dev’ focused on Expresion Blend 4. In the summer during SQL Saturday will be fun with Denali (SQL Server 2011) scheduled for release next year. Then in the fall Swamp coast code camp will be on again. As for content that I plan to study more is WCF RIA services to get data to the phone.
I also plan to release a new profile picture as I have in the past years; this year there is a new image check it out:
In this Post: 1 Swamp Coast Overview 2 Data Driven .NET App Session Overview 3 Vid of Session Content 4 List of Speakers (blog & twitter links) 5 Session Resources Links 6 DAL class file Code
1 Swamp Coast Overview The Swamp Coast Code Camp #3 in SWFla is gonna be another stellar event. This code camp is @ FGCU which provided a nice venue. The best part of code camps is all the people that are there to talk tek with. I”m really looking forward to relaxin with my geek friends & learning a bunch. There are going to be some great sessions there some that I’m looking forward to seeing are mostly in WP7Dev such as:
Design for WP7 – Diane Leeper Push Notification with Windows Phone 7 Jonas Stawski WP7 – Tips & Tricks Kevin ‘WolfBytes’ Wolf WP7 Marketplace, Panel, Install Fest Nikita Polyakov
Special thanks to John Dunagan & all the people who volunteered to make this event possible.
2 Data Driven .NET App Session Overview This year my session is going to Data Driven .NET Applications. This session will be fun with a ton of content. It’s meant for any skill level, the main concept we’re going to discuss is to have content of apps driven by a data source not just static. There will be a great mix of theory & code. Here is what I plan to discuss:
DB Driven Mindset - Static vs.. Data Drive Apps DB Dev / T-SQL Web App ASP.NET Data Access ADO.NET WP7 - XML RSS Data Source (more detailed list below)
public class DAL { private static string ConnectionString { get { return ConfigurationManager.ConnectionStrings["100827_wpb5ConnectionString"].ConnectionString; } }
public static DataTable ExecuteDataTable(string storedProcedureName, params SqlParameter[] arrParam) { DataTable dt = new DataTable(); SqlConnection cn = new SqlConnection(ConnectionString);
SqlCommand cmd = new SqlCommand(storedProcedureName, cn); cmd.CommandType = CommandType.StoredProcedure;
if (cn.State == ConnectionState.Closed || cn.State == ConnectionState.Broken) cn.Open();
try { if (arrParam != null) { foreach (SqlParameter param in arrParam) cmd.Parameters.Add(param); }
SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(dt); return dt; } catch (Exception ex) { throw new Exception("Error: " + ex.Message); } finally { cmd.Dispose(); cn.Close(); } }
WP7 Windows Phone 7 Application Development Catto Code Crackin #30
Hey Now Everybody,
WP7! Windows Phone 7 App Dev! The place is here & the time is now. We always here ‘Timing is everything’ & right now is the time to develop apps for WP7 ‘DubP7’. The tools are all free, the windows marketplace will accept WP7 apps soon & the phone/device will be available to the public soon. Therefore since we can be in the first group of apps in the marketplace there is a nice potential for generating revenue. All this combined with developing in cutting edge technology including Silverlight, VS10, Blend, XNA are the reasons I’m really enjoying WP7Dev. Here is what I’ve been focusing on including some references & my current project.
1. Developing my App – This is really where I’ve been focusing my time since it’s the most fun for me, most important to producing an app and a great way to learn.
3. Code ‘WP7 Catto Free App’ – Public @ CodePlex – I’ve posted my code from my project all public on CodePlex so anyone can download it to view, run & learn from it.
SQL Saturday #40 - DB Dev T-SQL 0-60 - Code Crackin #29
Hey Now Everybody,
SQL Saturday #40 on July 31st 2010, #sqlsat40 I'll be presenting a Database Developement session named T-SQL 0-60 (from standing to sprinting) a beginner's level 70 minutes session to cover some stellar content. This session will include a good mixture of high level concepts along with detailed examples of T-SQL code. We are going to use two simplifed tables from the ASP.NET Membership database (a real world db not northwind) that will work with to demonstrate many skills. There is a script in this post that you may use & I'll use as our examples. The session will include a little swag give away of a recorded episode of DotNetRocks! & RunAs Radio. Here is a link to my slide deck. This Database Development T-SQL 0-60 session will be broken up into four major session: 1. IDE SSMS & Queries - Fundamentals including management studio, objects & queries 2. Design View vs. Query Editor Window - Comparing GUI vs. Script to accomplish same tasks 3. Stored Procedures - In depth examples of stored procedures 4. Sky is the Limit - Additional query techniques
Here is an image of the session:
Along with all the stellar content the people whe are going to be there should be fun relax with & talk technology:
Here are some details on my session: 1.1. IDE SSMS SQL Server Management Studio 1.1.a. Installation of SQL Server 2008 1.1.b. Connoting to Server 1.1.c. Object Explorer 1.1.d. Query Editor Window 1.1.e Design View 1.1.f Best SSMS Hotkeys 1.2. Queries (Common Commands) 1.2.a. Syntax 1.2.b. Comments 1.2.c. Data Types 1.2.d. Create 1.2.e. Select 1.2.f. Insert 1.2.g. Where 1.2.h. Update 1.2.i. Delete 1.2.j. AND 1.2.k. OR 1.2.l. Order By 1.2.m. Group By 1.2.n. Count 1.2.o. Joins 1.2.p Views 2.1. Design View 2.1.a Creating Tables 2.1.b Modifying Tables 2.1.c Building Queries 2.2. Query Editor Window 2.2,a General 2.2.b Execute & Parse 2.2.c Highlighting Text 2.2.d Scripting 3. Stored Procedures a. Create b. Database Definition (Use) c. Comments d. Common Settings e. Begin End f. Alter / Modify g. Drop h. Rollback i. Grant Permission j. Execute SP k. Parameters 4. T-SQL’s Sky is the Limit a. Alias b. Scripting c. Commands d. Temp Tables e. Select Into f. Nested Select g. Cursors h. Case vs. If i. Dependencies j. Grants k. Schemas l. Template Explorer m. Datetime n. Try Catch Blocks o -Execution Plan p.1 XML p.2 XML PATH p.3 XML PATH SHOWPLAN Q Third Party tools q.1 Red Gate SQL Search q.2 SQL Format Snippet Inserter (Google Code) q.3 SQL Beauty R. Reference MSDN S MCTS 70-433 s.1 Skills Measured s.2 Programming Objects s.3 Query Fundamentals s.4 XML Data
-- Catto Database Development DB Script -- This is a script from www.ChrisCatto.com for SQL Saturday's Database Development T-SQL session --1.1. IDE SSMS SQL Server Management Studio -- 1.1.a. Installation -- 1.1.b. Connoting to Server -- 1.1.c. Object Explorer -- 1.1.d. Query Editor Window -- 1.1.e Design View --1.2. Queries (Common Commands) -- 1.2.a. Syntax -- 1.2.b. Comments -- 1.2.c. Data Types -- 1.2.d. Create -- 1.2.d.1 db -- 1.2.d.2 table Create [catto_aspnet_Users] -- 1.2.d.3 table Add Clustered Index -- 1.2.e. Select -- 1.2.f. Insert -- 1.2.g. Where -- 1.2.h. Update -- 1.2.i. Delete -- 1.2.j. AND -- 1.2.k. OR -- 1.2.l. Order By -- 1.2.m. Group By -- 1.2.n. Count -- 1.2.o. Joins ------------------------------------------------------- -- 1.2.d.1 Create --CREATE DATABASE [catto_dbtest6] --GO --DROP Database [catto_dbtest4] --GO --use [catto_sql_sat] --GO ------------------------------------------------------ ---- 1 Create Table [catto_aspnet_Users] CREATE TABLE [catto_aspnet_Users] ( [UserId] [int] NOT NULL IDENTITY , [active] [bit] Not NULL DEFAULT 1 , [UserName] [nvarchar](256) NULL ) GO -- 1.2.d.3 [PK_catto_aspnet_Users] ALTER TABLE [catto_aspnet_Users] ADD CONSTRAINT [PK_catto_aspnet_Users] PRIMARY KEY CLUSTERED ( [UserId] ASC ) ON [PRIMARY] GO ---------------------------------------- -- 1.2.d.4 table [catto_aspnet_Membership] --drop TABLE [dbo].[catto_aspnet_Membership] CREATE TABLE [dbo].[catto_aspnet_Membership] ( [MembershipID] [int] IDENTITY(1,1) NOT NULL , [active] [bit] Not NULL DEFAULT 1 , [UserId] [int] NOT NULL , [Password] [nvarchar](128) NOT NULL , [Email] [nvarchar](256) NULL , [IsApproved] [bit] NOT NULL , [CreateDate] [datetime] NOT NULL , CONSTRAINT [PK_catto_aspnet_Membership] PRIMARY KEY CLUSTERED ( [UserId] ASC ) ON [PRIMARY] ) ON [PRIMARY] GO CREATE NONCLUSTERED INDEX [nci_userid] ON [dbo].[catto_aspnet_Membership] ( [UserId] ASC ) ON [PRIMARY] GO ----1.2.e. Select select * from [catto_aspnet_Users] --drop table [catto_aspnet_Users_4] ---- Create Table & PK in one script --CREATE TABLE [dbo].[catto_aspnet_Users]( -- [UserId] [int] IDENTITY(1,1) NOT NULL, -- [active] [bit] NOT NULL default 1, -- [UserName] [nvarchar](256) NULL, -- CONSTRAINT [PK_catto_aspnet_Users] PRIMARY KEY CLUSTERED --( -- [UserId] ASC --)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] --) ON [PRIMARY] --1.2.f. Insert -- Insert Record INSERT INTO catto_aspnet_Users (UserName) VALUES ('Catto') INSERT INTO catto_aspnet_Users (UserName) VALUES ('CattoOrange') INSERT INTO catto_aspnet_Users (UserName) VALUES ('CattoPotato') select * from catto_aspnet_Users ----------------------------------------- --1.2.g. where Select [UserId] ,[active] ,[UserName] From [catto_aspnet_Users] where [UserName] = 'Catto' ------------------------------------------------- --1.2.h. Update UPDATE catto_aspnet_Users SET UserName = 'CattoTomato' WHERE (UserId = 1) Select * from catto_aspnet_Users where (UserId = 1) -------------------------------------------------- --1.2.i. Delete DELETE FROM catto_aspnet_Users WHERE (UserId = 1) Select * from catto_aspnet_Users where (UserId = 1)
INSERT INTO catto_aspnet_Users (UserName) VALUES ('Catto') -------------------------------------------------- --1.2.j. And Select * from catto_aspnet_Users where (UserId = 2) AND (UserName = 'CattoOrange') -- Highlighting -- Text --NEED INSTALL LINK ------------------------------------ --1.2.k. OR Select * from catto_aspnet_Users where (UserId = 2) OR UserName = 'CattoPotato' ------------------------------------ --1.2.l. order by Select * from catto_aspnet_Users order by UserId Select * from catto_aspnet_Users order by UserId desc ------------------------------------ --1.2.m. Group by SELECT active, UserId, UserName FROM catto_aspnet_Users GROUP BY active, UserId, UserName ------------------------------------ --1.2.n. Count SELECT COUNT(UserId) AS totalCount FROM catto_aspnet_Users -- Count & Group by SELECT active AS activeBit, COUNT(UserId) AS totalCount FROM catto_aspnet_Users where active = 1 GROUP BY active ------------------------------------ --1.2.o. -- Joins INSERT INTO catto_aspnet_Membership (active, UserId, Password, Email, IsApproved, CreateDate) VALUES (1, 2, 'blahEncrypted', 'catto@domain.com', 1, CONVERT(DATETIME, '2010-08-08 00:00:00', 102)) select * from catto_aspnet_Membership select * from catto_aspnet_Users INNER JOIN catto_aspnet_Membership ON catto_aspnet_Users.UserId = catto_aspnet_Membership.UserId GO ------------------------------------ --1.2.p Views CREATE VIEW [dbo].[vw_catto_aspnet_Users] AS SELECT [UserId] , [UserName] FROM catto_aspnet_Users GO -- 1.2.q selecting from View -- Selecting from View SELECT [UserId] ,[UserName] FROM [vw_catto_aspnet_Users] -------------------------------------------------------------------------------- -- 2 Design View vs. Query Editor -------------------------------------------------------------------------------- --2.1 Desgin View Vs. Query Editor Window --2.1. Design View -- 2.1.a Creating Tables -- 2.1.b Modifying Tables -- 2.1.c Building Queries --2.2. Query Editor Window -- 2.2,a General -- 2.2.b Execute & Parse -- 2.2.c Highlighting Text -- 2.2.d Scripting -------------------------- --USE [ccatto_aspnetdb] --GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[aspnet_Roles_catto] ( [aspnet_roles_id] [numeric] NOT NULL identity, [RoleId] [uniqueidentifier] NOT NULL, [RoleName] [nvarchar](256) NOT NULL, [Description] [nvarchar](256) NULL, PRIMARY KEY NONCLUSTERED ( [RoleId] 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 --USE [catto_dbtest4] --GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[aspnet_Roles_catto]( [aspnet_roles_id] [numeric](18, 0) IDENTITY(1,1) NOT NULL, [roles] [nvarchar](50) NULL, [RoleId] [uniqueidentifier] NOT NULL, [RoleName] [nvarchar](256) NOT NULL, [Description] [nvarchar](256) NULL, CONSTRAINT [PK_roles_catto_id] PRIMARY KEY CLUSTERED ( [aspnet_roles_id] 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 CREATE NONCLUSTERED INDEX [nci_roleid_catto] ON [dbo].[aspnet_Roles_catto] ( [roles] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO ALTER TABLE [aspnet_Roles_catto] ADD application_id int null GO select * from [aspnet_Roles_catto] --drop table [aspnet_Roles_catto] -- now we will create a table in design view INSERT INTO aspnet_Roles_catto(role_id, role, description, app_id) VALUES (1, 'admin_role', 'admin role most power', 1) INSERT INTO aspnet_Roles_catto(role_id, role, description, app_id) VALUES (2, 'user_pawn_role', 'pawn role least power', 1) select * from [aspnet_Roles_catto] -- in design view lets insert values SELECT role_id, role, description, app_id FROM aspnet_Roles_catto WHERE (app_id = 1) and (role_id = 1)
SELECT COUNT(aspnet_roles_catto_id) AS TotalRecords FROM aspnet_Roles_catto GO --------------------------------- --3. Stored Procedures -- a. Create -- b. Database Definition (Use) -- c. Comments -- d. Common Settings -- e. Begin End -- f. Alter / Modify -- g. Drop -- h. Rollback -- i. Grant Permission -- j. Execute SP -- k. Parameters -- 3.a. Create CREATE Procedure p_aspnet_Users_Get --ALTER Procedure p_aspnet_Users_Get AS BEGIN Select UserName from catto_aspnet_Users END GO --------------------------------------------------- -- 3.b Use --USE catto_dbtest4 --GO -- 3.c Comments -- 3.d Common Settings -- 3.e Begin End -- 3.f Alter --CREATE Procedure p_aspnet_Users_Get SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER Procedure p_aspnet_Users_Get AS BEGIN Select UserName from catto_aspnet_Users where active = 1 END -- 3.g DROP DROP PROCEDURE [dbo].[p_aspnet_Users_Get] GO Create Procedure p_aspnet_Users_Get AS BEGIN Select UserName from catto_aspnet_Users where active = 1 END GO -- 3.h ROLLBACKS Alter Procedure p_aspnet_Users_Get AS BEGIN Select UserName from catto_aspnet_Users --where active = 1 END -------------------------------------------------------------------------------- -- Rollback -------------------------------------------------------------------------------- --Alter Procedure p_aspnet_Users_Get --AS --BEGIN -- Select UserName -- from catto_aspnet_Users -- where active = 1 --END GO --3.i GRANT --3. i. Grant Permission --Grant Execute on p_aspnet_Users_Get -- to newUser --GO -- 3.j execute Execute p_aspnet_Users_Get GO -- 3.k @Parameters CREATE PROCEDURE [dbo].[p_aspnet_Membership_CreateUser] --Alter Procedure [p_aspnet_Membership_CreateUser] ( @UserName nvarchar(256) , @Password nvarchar(128) , @Email nvarchar(256) , @IsApproved bit ) AS BEGIN declare @CreateDate datetime declare @NewUserId int SET @CreateDate = GETDATE() INSERT INTO catto_aspnet_Users (UserName) VALUES (@UserName) set @NewUserId = ( select top 1 @@IDENTITY from catto_aspnet_Users) INSERT INTO catto_aspnet_Membership ( active , UserId , Password , Email , IsApproved , CreateDate ) VALUES ( 1 , @NewUserId , @Password , @Email , 1 , @CreateDate ) END GO -------------------------------------------------- --- Check latest records in tables Select top 1 * from catto_aspnet_Membership order by MembershipID desc select top 1 * from catto_aspnet_Users order by UserId desc ----------------------------------------------------- -- j. Execute SP DECLARE @return_value int EXEC @return_value = [dbo].[p_aspnet_Membership_CreateUser] @UserName = 'ccatto', @Password = 'blah', @Email = 'cattoemail@chriscatto.com', @IsApproved = 1 SELECT 'Return Value' = @return_value GO -- Check latest record in tables Select top 1 * from catto_aspnet_Membership order by MembershipID desc select top 1 * from catto_aspnet_Users order by UserId desc ----------------------------------------- --4. T-SQL’s Sky is the Limit -- a. Alias -- b. Scripting -- c. Commands -- d. Temp Tables -- e. Select Into -- f. Nested Select -- g. Cursors -- h. Case vs. If -- i. Dependencies -- j. Grants -- k. Schemas -- l. Template Explorer -- m. Datetime -- n. Try Catch Blocks -------------------------------------- -- 4.a. Alias --- 4.a.1 example simple select cau.UserName uname from catto_aspnet_Users cau -- -4.a.2 example 2 select cau.UserName from catto_aspnet_Users cau INNER JOIN catto_aspnet_Membership cam ON cau.UserId = cam.UserId -- 4.b. Scripting ------------------------------------ -- 4.c. Commands --c.1 Declare DECLARE @loopCounter int DECLARE @name varchar (50) DECLARE @theDate datetime --c.2 SET SET @loopCounter = 1 SET @name = 'Catto' --c.3 If --SET @loopCounter = 0 IF @loopCounter = 1 BEGIN Select * From catto_aspnet_Users END --c.4 Case select UserName, case active when 0 then 'notActive' when 1 then 'Active' --when 2 then 'unknown' else 'Invalid ActiveBit' end as "Active" from catto_aspnet_Users --c.5 Union --INSERT INTO catto_aspnet_Membership -- (active, UserId, Password, Email, IsApproved, CreateDate) --VALUES (1,998, 'blahEncrypted2', 'catto@lives2.com', 1, CONVERT(DATETIME, '2010-08-08 00:00:00', 102))
Select UserID From catto_aspnet_Users UNION Select UserID From catto_aspnet_Membership --c.6 Like Select * From catto_aspnet_Users WHERE (UserName LIKE '%Catt%') --c.7 Top Select top 1 * From catto_aspnet_Users WHERE (UserName LIKE '%Catt%') --declare @k8variable int --set @k8variable = 2 --select top @k8variable * from catto_aspnet_Users --c.8 With No Lock Select top 1 * From catto_aspnet_Users with (nolock) --c.9 Sum SELECT SUM(catto_aspnet_Users.UserId) AS SumExample FROM catto_aspnet_Users --c.10 Avg SELECT AVG(UserId) AS AverageOfUID FROM catto_aspnet_Users -- 4.d. Temp Tables -- Example of using Temp Tables: -- Delcare Variables DECLARE @tableID int DECLARE @nameOfPerson varchar (50) -- CREATE temp table tempTablePeeps CREATE TABLE #tempTablePeeps ( ttpPeepID int NULL , ttpPeepName varchar(50) NULL ) -- Select & set variables SET @tableID = ( Select UserId From catto_aspnet_Users where userid = 2 ) SET @nameOfPerson = ( Select userName From catto_aspnet_Users where userid = 2 ) -- Insert Values into temp Table INSERT INTO #tempTablePeeps ( ttpPeepID , ttpPeepName ) VALUES ( @tableID , @nameOfPerson ) -- Select Values from Temp Table Select ttpPeepID as ID , ttpPeepName as PeepsName From #tempTablePeeps Drop Table #tempTablePeeps -- 4.e. Select Into w/ temp table SELECT userid , username INTO #tempTable FROM catto_aspnet_Users select * from #tempTable drop table #tempTable -- Example: In the next nifty Select into statement an identity column is created on the temp table which could be quite useful especially if the newTablePeopleName table didn't have an identity. -- catto_aspnet_users have identity already --SELECT -- userid -- , username -- , IDENTITY( int ) AS workingID --INTO #tempTable --FROM catto_aspnet_Users --select * from #tempTable --drop table #tempTable -- 4.f. Nested Select SELECT username FROM catto_aspnet_Users WHERE userid = ( SELECT userid FROM catto_aspnet_Membership WHERE email = 'catto@domain.com' ) -------------------------------------------------- -- 4.g. Cursors DECLARE @UID INT DECLARE curor_Data CURSOR STATIC FOR select userid from catto_aspnet_Membership OPEN curor_Data FETCH curor_Data INTO @UID WHILE @@FETCH_STATUS = 0 BEGIN select * from catto_aspnet_Users where @UID = userid FETCH curor_Data INTO @UID END CLOSE curor_Data DEALLOCATE curor_Data -- 4.h. Case vs. If declare @activeflag int set @activeflag =1 if @activeflag = 1 Begin select username, active from catto_aspnet_Users End select UserName, case active when 0 then 'notActive' when 1 then 'Active' --when 2 then 'unknown' else 'Invalid ActiveBit' end as "Active" from catto_aspnet_Users GO -- 4.i. Dependencies right click on object 'view dependencies' -- 4.j. Grants -- GRANT EXECUTE ON p_getNames --TO [Catto_Person_User] --GO -- 4.k. Schemas create schema s_catto GO -- create table [s_catto].[schemaTable] ( --[schemaTable_id] [int] IDENTITY(1,1) NOT NULL) -- 4.l. Template Explorer 2008 -- 4.m. Datetime declare @uglyDate datetime set @uglyDate = '2010-07-31 12:42:36.320' declare @prettyDate varchar(25) SET @prettyDate = ( SELECT CAST(DATEPART(month, @uglyDate ) as varchar(2)) + '.' + CAST(DATEPART(day, @uglyDate ) as varchar(2)) + '.' + CAST(DATEPART(year, @uglyDate ) as varchar(4)) ) select @prettyDate as prettydateOutput GO -- note output is 7.31.2010 -- 4.n. Try Catch Blocks BEGIN TRY -- let us Generate a divide-by-zero error. SELECT 1/0; --select 1; END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber ,ERROR_SEVERITY() AS ErrorSeverity ,ERROR_STATE() AS ErrorState ,ERROR_PROCEDURE() AS ErrorProcedure ,ERROR_LINE() AS ErrorLine ,ERROR_MESSAGE() AS ErrorMessage; END CATCH; GO --4.o --Execution Plan select * from catto_aspnet_users where userid = 2 and username = 'CattoOrange' ------------------------------------- -- 4.p.1 XML SELECT [UserId] ,[UserName] FROM [catto_aspnet_Users] FOR XML AUTO, ELEMENTS -- 4.p.1 XML PATH SELECT [UserId] as uid ,[UserName] as "info/@userName" FROM [catto_aspnet_Users] FOR XML PATH('K8Users'); GO -- 4.p.3 SHOWPLan XML SET SHOWPLAN_XML ON; GO SELECT [UserId] as uid ,[UserName] as "info/@userName" FROM [catto_aspnet_Users] FOR XML PATH('K8Users'); Go SET SHOWPLAN_XML OFF; ----END of SCRIPT -------------------------------------
As Always all comments welcome that is all there will be more Catto
SQL Server Management Studio SSMS, is it the toughest program to install? Personally I believe it is, what do you think? Let us take a closer look @ installing SSMS. As I just put a fresh install of Win7 54-bit on this notebook I obviously had to install all of my programs again. The first program I usually install is SSMS & I took some screen shots to include in this post. The version I installed in SQL Server 2008.
Many programs that we install it’s a simple 3 clicks, next, next, finish. I think SSMS takes about 20 clicks before we get to finish. There are many options that we can choose from. Below is the sequence of steps I used to install the program:
Here is a summary of the typical steps taken:
1st Compatibility Message (SP1) 2nd SQL Server Installation Center 3rd Setup Support Rules 4th Product Key 5th License Terms: 6th Setup Support Files 7th Setup Support Rules 8th Feature Selection 9th Instance Configuration 10th Disk Space Req 11th Server Configuration 12th Database Engine Config 13th Analysis Services Configuration SSAS 14th Reporting Services Config SSRS 15th Error & Usage Reporting 16th Installation Rules 17th Installation Rules 18th Ready to Install 19th Installation Progress 20th Complete 21st Tools Options – Customize
SSMS has a whole SQL Server Installation Center that opens when we start installing the program. The option we’ll focus on is adding a new SQL Server stand alone installation. Before I started the install we’ll get prompted about installing SP1.
3 Setup Support Rules is the next step, this will indentify any issue we may have when installing, below we see we passed all six.
4th Product Key- we enter in our Product Key
5th License Terms: We read the entire legal document, click accept then click next.
6th Setup Support Files We click next to setup support files.
7th Setup Support Rules, After it runs it will display passed, failed, or warnings:
8th Feature Selection: we select all the features we want to install such as SSAS, SSRS, full text search ect:
9th Instance Configuration We create an instance & select the location:
10th Disk Space Requirements verify there is space on the drive:
11th Server Configuration, this is where I’ve spent some time trying to get this setup correctly
12th Database Engine Configuration This is where we add the users
13th Analysis Services Configuration We setup SSAS Users:
14th Reporting Services Configuration setup SSRS
15th Error & Usage Reporting – we can choose to send information to Microsoft
17th Installation Rules – Rules are run to ensure the installation process will not be blocked
18th Ready to Install – We verify all of our features we want to install
19th Installation Progress – Displays the current process of installation.
20th Complete – The SQL Server installation was successfully installed.
21. Options – In addition to adding line numbers, changing the backround to black this is one option I like to adjust after I install which
As we’ve seen SQL Server is fun to install & has many different options we can choose from. We all need a database to connect to provide database driven applications, installing it is a requirement.
What do you think, is SSMS the toughest program to install? As always all comments welcome.
This is a review of our local .NET user group meeting where the topic was an exam cram on the Microsoft exam 70-433. The session was @ our Gold Coast User Group. The session was great & presented by David Cobb & Daniel Dunn. It was a great session where we went over about many questions from a practice exam. I took some notes on the content & wanted to share them to help people study the content for the exam.
Below are a copy of my notes. This post will help us by making us think about content from the exam:
1. Permissions Deny Select permissions Deny Select On order to John 2. Two unique constraint has not null Nvarchar 100 null Nvmar chare max 3. Three Create View With Check option answer not SchemaBinding with Schemabiding 4. Create non clustered index 5. Rebuild - Page splitting alter index all on sales.saleshistory rebuild with (fillfacor = 60 ) Rebuild option 6. Five `NonclusteredIndex Create Nonclusteree index ix_cust_acc# 7. Siz OR - 8. Eight Clustered index on FG_accountData (column_a ) C Create Clustered Index idx_accountsReceiveable on AccountsReceivable(caol_a) on FG_AcccountData(col_a) 9. Nine - B. Set Identity Insert invoice on insert into infvoice() values 10. FK 11. B Geography new data type 12. SQL_Latin_Genteral_CP1_CS_AS ‘a’ ‘A’ Case senstaive 13. C Varchar(max) compatible with SPARSE 14. D datetimeoffset – Local time 15. Alter Table Switch task table to table history 16. Create partition function,Schema create the table, Another Section 17. Stored procs Recompile ;; option(recompile ) & sp with encryption 18. Permissions A. Grant execute not impersonate user 19. READONLY Tablevalued parameters 20. Index column IsDeterministic = True IsSystemVerified = Ture UserDataAccess = False SystemDataAccesss False 21. Scalarvalued function 29 days in Feb 22. With Schemabinding – prevents tables from being deleted 23. Create trigger tr_Part_d on Part instead of delete 24. Trigger Instead of Insert trigger don’t stop the insert 25. C - Create trigger after update not for replication 26. C - DDL Rollback ddl create delete 27. On Database For 28. Missed 29. 16 try catch 30. Raiserror 10 Choice
Below are the list of many of the topics discussed:
nVarChare(MAX ) Data type with Check Option With schemabinding With View_metadata Create index .. include (col [n]) Create index .. on filegroup Alter index rebuild [with ] Geometry Geography Sparse Dataetimeoffset Alter table switch partition Create partition function Create partition scheme Create table .. on artition scheme With recompile Execute as Trigger CLR Assembly Raise error severity levels
Whew fun hope you liked it! What did you think?
Next Up section ‘Additional Query Techniques’. That is all there will be more,
This is a review of the sixth objective on the Microsoft exam 70-433 ‘Working with XML Data’
While reviewing for the Microsoft exams it’s enjoyable to combine the three sections: 1. Self Paces Book’s chapters content 2. Code, SQL Scripts 3. Concepts from Questions of the Microsoft Training Kit
Chapter 7.1 Key Concepts XML can be generated using SELECT in four different modes: For XML Raw , For XML Auto , For XML PATH , For XML Explicit For XML Path is typically the preferred mode used to generate XML XML Data type can be either untyped or typed (validated by an XML schema collection) Untyped XML data type all values are always interpreted as strings We can use value, query, exist, nodes & modify methods to query & alter XML data types Code Query in ASP.NET Membership database querying aspnet_users table
/****** Script for SelectTopNRows command from SSMS ******/ SELECT [UserId] ,[UserName] FROM [ccatto_aspnetdb].[dbo].[aspnet_Users] FOR XML AUTO, ELEMENTS
Here are some of the results:
Here is another example using For XML Path
/****** Script for SelectTopNRows command from SSMS ******/ SELECT [UserId] as uid ,[UserName] as "info/@userName" FROM [ccatto_aspnetdb].[dbo].[aspnet_Users] FOR XML PATH('K8Users');
Here is a sample node from the results:
Example of SHOWPLAN XML to show execution plan
/****** Script for SelectTopNRows command from SSMS ******/ SET SHOWPLAN_XML ON; GO SELECT [UserId] as uid ,[UserName] as "info/@userName" FROM [ccatto_aspnetdb].[dbo].[aspnet_Users] FOR XML PATH('K8Users'); Go SET SHOWPLAN_XML OFF;
This is a review of the second objective on the Microsoft exam 70-433 ‘Query Fundamentals’
While reviewing for the Microsoft exams it’s enjoyable to combine the three sections: 1. Self Paces Book’s chapters content 2. Code, SQL Scripts 3. Concepts from Questions of the Microsoft Training Kit
Select Statement can be used to retrieve data from tables & views. Select results sets can be filtered by adding a WHERE clause Select results sets can be sorted by usinge the ORDER BY Aliases & string literals can be used to manipulate & format result sets.
3.2 Modify Data by using Insert, Update & Delete statements c2.1 The Join clause allows us to retrieve columns from related tables JOIN types include INNER< LEFT OUTER, RIGHT OUTER, FULL OUTER & CROSS Join operators can combine 2 or more tables Tables can be joined to itself by defining different aliases for each table 3.3 Return data by using the OUTPUT clause c2.2 The OUTPUT clause allows us to redirect info to the calling app, or to an object such as a table, about the insert, update or delete statement 3.4 Modify data by using MERGE statement c2.2 MERGE statement allows us to perform DML actions on a table based on matches found on a source table 3.5 Implement aggregate queries (LINQ) c1.3 Aggregate functions perform calculations on expressions Use the GROUP BY clause when aggregates should be applied based on the data in rows not the whole table Include all columns listed in a SELECT WHERE or ORDER by clause in the GROUP By clause ROLLUP & CUBE to provide summary info Use the GROUPING function to show which rows holds summary data provided by the rollup & cube operators Grouping Sets to provide improvements to our Group by queries 3.6 Combine datasets c1.4 UNION operator combines result sets from 2 or more Select statements EXCEPT operator returns rows that are in the left Select statements Intersect operator returns only rows that are shared by the two select operators The APPLY operator uses the results from a query as input to apply a function to each row in the results. Outer apply returns all rows from the outer table along with the results returned by the function when rows match. CROSS Apply returns only the rows from the outer tables which matches the function results. 3.7 Apply built in scalar functions c1.5 Built in functions to provide more meaningful results sets date & time functions to return date info String functions to format or return info about string expressions.
Code:
/****** Script for SelectTopNRows command from SSMS ******/ SELECT TOP 000 [ApplicationId] ,[UserId] ,[UserName] ,[LoweredUserName] ,[MobileAlias] ,[IsAnonymous] ,[LastActivityDate] FROM [ccatto_aspnetdb].[dbo].[aspnet_Users]
UPDATE dbo.aspnet_Users SET LastActivityDate = @CreateDate WHERE @UserId = UserId
Order by
SELECT TOP (200) ApplicationId, UserId, UserName, LoweredUserName, MobileAlias, IsAnonymous, LastActivityDate FROM dbo.aspnet_Users ORDER BY UserName
Question Concepts LEFT JOIN specifies that all rows from the left table are returned whether they meet the join criteria or not. If the join criteria are not met, output columns that correspond to the right table are set to NULL.
The OUTPUT INTO clause is a simple way to insert deleted rows into another new table. A temporary table can be populated CONVERT(char, GETDATE(), 102) retrieves the current date with the GETDATE() function & converts it to ANSI format. The MERGE statement performs INSERT, UPDATE, or DELETE operations on a target table based on the results of a JOIN with a source table. The SELECT INTO statement creates a new table & populates it with the result set of the SELECT statement. AVG is a built-in aggregate function that calculates the average value for a column. Tyou The LEN() function returns the number of characters in a string expression
