Results 1 to 4 of 4

Thread: SQL Server 2005 useful info [includes links to download it]

  1. #1

    Thread Starter
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Lightbulb SQL Server 2005 useful info [includes links to download it]

    Note that post #4 contains links for downloading SQL Server Express and various other items, and links to more useful information.



    Saw this yesterday - thought it was a really neat T-SQL trick...

    Code:
    update sometable
     set somecol = 'ABC'
     OUTPUT inserted.keycol 'Key', deleted.somecol 'Old', inserted.somecol 'New'
     where keycol = 1
    Will return the 3 columns indicated after the OUTPUT keyword as a SELECT statement after the UPDATE is performed...

    Note that the columns come from those "trigger" like tables - the INSERTED and DELETED tables...

    I get excited easily

    edit: it's only SQL 2005 that will support this

    Here's what it looks like:

    Code:
    begin tran
    select * from lettertyp_t where lettertyp='AP'
    update lettertyp_t set lettertype='ABC' 
       OUTPUT inserted.lettertyp 'Key',deleted.lettertype 'Old'
                       ,inserted.lettertype 'New'
       where lettertyp='AP'
    rollback
    comes back with...

    Code:
    LetterTyp LetterType                     TDate                                                  
    --------- ------------------------------ ------------------------------------------------------ 
    AP        Acknowledgement of Paternity   NULL
    
    (1 row(s) affected)
    
    Key       Old                            New                            
    --------- ------------------------------ ------------------------------ 
    AP        Acknowledgement of Paternity   ABC
    
    (1 row(s) affected)
    Last edited by si_the_geek; Feb 5th, 2008 at 06:14 PM. Reason: added first line

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  2. #2

    Thread Starter
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Lightbulb SQL 2005 - Try/Catch!!

    Apparently SQL 2005 has a TRY/CATCH construct.

    SQL 2000

    VB Code:
    1. Use Funds
    2. Go
    3. Select * from Funds_T Where ConfItem='AMC'
    4. Go
    5. Insert into Funds_T Values (null,null)
    6. Go
    7. .
    8. . returns in the Message pane
    9. .
    10. ConfItem                                           ConfData                                          
    11. -------------------------------------------------- --------------------------------------------------
    12. AMC                                                4.9.4
    13.  
    14. (1 row(s) affected)
    15.  
    16. Server: Msg 515, Level 16, State 2, Line 1
    17. Cannot insert the value NULL into column 'ConfItem', table 'Funds.dbo.Funds_T';
    18. column does not allow nulls. INSERT fails.
    19. The statement has been terminated.
    Blows up because we don't allow NULL values in the columns of this table...

    SQL 2005

    VB Code:
    1. Use Funds
    2. Go
    3. Select * from Funds_T Where ConfItem='AMC'
    4. Go
    5. Begin Try
    6.     Insert into Funds_T Values (null,null)
    7. End Try
    8. Begin Catch
    9.     Select 'Insert did not work!'
    10. End Catch
    11. Go
    12. .
    13. . returns
    14. .
    15. ConfItem                                           ConfData                                          
    16. -------------------------------------------------- --------------------------------------------------
    17. AMC                                                4.8.4
    18.  
    19. (1 row(s) affected)
    20.  
    21.  
    22. (0 row(s) affected)
    23.  
    24.                      
    25. --------------------
    26. Insert did not work!
    27.  
    28. (1 row(s) affected)

    The books online has a nice example of an error function - check this out

    VB Code:
    1. Use Funds
    2. Go
    3. CREATE PROCEDURE usp_GetErrorInfo
    4. AS
    5.     SELECT
    6.         ERROR_NUMBER() AS ErrorNumber,
    7.         ERROR_SEVERITY() AS ErrorSeverity,
    8.         ERROR_STATE() AS ErrorState,
    9.         ERROR_PROCEDURE() AS ErrorProcedure,
    10.         ERROR_LINE() AS ErrorLine,
    11.         ERROR_MESSAGE() AS ErrorMessage;
    12. GO
    13. Select * from Funds_T Where ConfItem='AMC'
    14. Go
    15. Begin Try
    16.     Insert into Funds_T Values (null,null)
    17. End Try
    18. Begin Catch
    19.     exec usp_GetErrorInfo
    20. End Catch
    21. Go
    22. .
    23. . returns...
    24. .
    25. ConfItem                                           ConfData                                          
    26. -------------------------------------------------- --------------------------------------------------
    27. AMC                                                4.8.4
    28.  
    29. (1 row(s) affected)
    30.  
    31.  
    32. (0 row(s) affected)
    33.  
    34. ErrorNumber
    35. ErrorSeverity
    36. ErrorState  
    37. ErrorProcedure
    38. ErrorLine  
    39. ErrorMessage
    40. 515
    41. 16
    42. 2          
    43. NULL
    44. 2
    45. Cannot insert the value NULL into column 'ConfItem', table 'Funds.dbo.Funds_T';
    46.  column does not allow nulls. INSERT fails.
    47.  
    48. (1 row(s) affected)
    I had to cut up the results so it would post properly - but you get the idea!

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  3. #3

    Thread Starter
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Lightbulb Sql 2005 - Pivot!

    Appears we are finally getting a real PIVOT construct in T-SQL with MS SQL 2005.

    The example below shows counts of attendance for 4 buildings - the first query uses the old SUM/CASE syntax you are all probably used to.

    The second query uses the new PIVOT syntax.

    VB Code:
    1. Use Stufiles
    2. Go
    3. Select Bldg,Sum(Case When AttType='AE' Then 1 Else 0 End) "Absent"
    4.     ,Sum(Case When AttType='TE' Then 1 Else 0 End) "Tardy"
    5.     From Attendance_T Where Yr=2006 and Bldg between 10 and 56 Group by Bldg Order by Bldg
    6. Go
    7. Exec sp_dbcmptlevel StuFiles,90
    8. Go
    9.  
    10. Select Bldg,[AE] AS Absent, [TE] AS Tardy
    11. From
    12. (Select 1 AS AttValue,AttType,Bldg From Attendance_T Where Yr=2006 and Bldg between 10 and 56) a
    13. PIVOT (Count(ATTValue) For AttType in ([AE],[TE])) as pvt
    14. Order by Bldg
    Both return the same results...
    Code:
    Bldg Absent      Tardy       
    ---- ----------- ----------- 
    10   3235        1344
    30   2397        1282
    52   892         196
    56   1463        316
    
    (4 row(s) affected)
    
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.
    Bldg Absent      Tardy       
    ---- ----------- ----------- 
    10   3235        1344
    30   2397        1282
    52   892         196
    56   1463        316
    
    (4 row(s) affected)
    Note that you must set compatibility level to 90 (MS SQL 2005 - version 9.0).

    If this was one of my financial packages - this line:

    Select 1 AS AttValue

    Could just as easily been a column name containing dollar values.

    Combine this with COLUMNS that are FORMULA's and you have some truly powerful query abilities!

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  4. #4
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,927

    Re: SQL Server 2005 useful info

    Useful (free) downloads:
    SQL Server 2005 Express Edition (SP 1) (a free version of SQL Server!)

    SQL Server 2005 Books Online (the help files for all editions of 2005, in various languages)

    Management Studio Express. (graphical management tool for the Express Edition).
    An unofficial alternative is Toad

    Native Client (OLE DB provider and ODBC driver, to make best use of 2005 features from your applications)

    Other official downloads can be found here.


    And some useful links I've found on my travels, or been told about by forum members:
    SQL Server 2005 Features Comparison (compares the features in different Editions)

    Video Series: SQL Server 2005 Express Edition for Beginners

    Using SQL Server 2005 Express from Visual Basic 6

    The Fundamentals of the SQL Server 2005 XML Datatype

    Introducing SQL Service Broker Part 1 (messaging between applications and BizTalk). [by our own Sgt-Peppa]

    Avoid using the ntext, text, and image data types (Use nvarchar(max), varchar(max), and varbinary(max) instead)

    Cross Apply with Table Value Functions


    .
    Last edited by si_the_geek; Dec 15th, 2009 at 12:10 PM. Reason: added another link

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width