Tuesday, 12 October 2010

[SQL] Calculating space used by Tables etc.

If you're looking for a fast easy way to find out the space used by tables, take a look at the excellent article by David Poole:

SQL Server Central link


The man is a genius - I'm privileged to have worked with him.

[SQL] Scripting out Jobs in SSMS

I found that you can script out all jobs from Management Studio.
  1. Open the server in the Object Explorer.
  2. Open the "Object Explorer Details" window.
  3. In the "Object Explorer Details" window, drill down to "SQL Server Agent"
  4. Drill down to "Jobs"
  5. Select the jobs you want to script.
  6. Right click and select "Script Job as", then "Create To", and select where you want them scripted to.
  7. Remember to filter out any "SSRS" jobs. (exclude any job name that contains a hyphen).




[SQL] Changing the collation on the MASTER database

To change the collation on the master database, it needs to be re-created. !

http://msdn.microsoft.com/en-us/library/dd207003.aspx
The above link contains instructions from MSDN library (Books online - BOL)

Monday, 11 October 2010

[SQL] Checking MS SQL Server Stored Procedures

External link: http://devio.wordpress.com/2009/11/26/checking-ms-sql-server-stored-procedures/

Checking MS SQL Server Stored Procedures

To check the validity of stored procedures, loop through the stored procedures, and enclose each execute in a transaction which is rolled back (ROLLBACK).

See external link for full details.

Friday, 8 October 2010

[SSRS] Adding simple STYLES to SSRS reports.

Adding simple STYLES to SQL SERVER REPORTING SERVICES reports.

"We are looking at is a simple and efficient way of changing the colour of cells,
text and lines, as well as changing the thickness and type of borders instantly
and globally for one or more reports, using a tagged, or named style approach."


  • Step1: Add EMBEDDED CODE to a report

  • Step2: Apply Styles to the relevent report properties ("BackgroundColour" = "=code.StyleColor("Header")")

  • Step3: Think of a naming convention for the different styles you need, and use these in reports.



  • http://www.sqlservercentral.com/articles/Reporting+Services+(SSRS)/65810/

    [SQL] Event Notifications in SQL Server for Tracking Changes

    Event Notifications in SQL Server for Tracking Changes
    http://www.mssqltips.com/tip.asp?tip=2121&home

    You create a notification, SQL Server tracks the defined events
    and the occurrence of that event is written to the
    SSB (SQL SERVER BORKER) service and then you can retrieve this
    information asynchronously from the SSB queue when needed.

  • Step1: Enable_Broker on a DATABASE

  • Step2: Create database level EVENT notification

  • Step3: Create SErver level EVENT notification

  • Step4: Verify notifications are being tracked

  • Step5: Retrieving (RECEIVE) records from the EVENT NOTIFICATIONS QUEUE


  • Permission Requirements
  • In order to create a database level event notification, you need to have CREATE DATABASE DDL EVENT NOTIFICATION permissions in that database. To drop it, you must be the owner of the event notification or have ALTER ANY DATABASE EVENT NOTIFICATION permission in that database.

  • In order to create a server level notification, you need to have CREATE DDL EVENT NOTIFICATION permissions on the server. To drop it, you must be the owner of the event notification or have ALTER ANY EVENT NOTIFICATION permission on the server.

  • In order to create an event notification to track SQL traces, you need to have CREATE TRACE EVENT NOTIFICATION permission on the server. To drop it, you must be the owner of the event notification or have ALTER ANY EVENT NOTIFICATION permission on the server.

  • In order to create a queue scoped event notification you need ALTER permission on the queue. To drop it, you must be the owner of the event notification or have ALTER permission on the queue.



  • Notes
  • You can query sys.event_notification_event_types to get the list of all the events on which you can create event notifications or you can view DDL events, DDL event groups, SQL trace events, SQL trace event groups.

  • To see the differences between event notifications and triggers click here and differences between event notifications and SQL traces click here.

  • You cannot directly alter a notification, you need to drop and re-create it.
  • [SQL] Tracking down SP dependancies

    Scenario: There’s a stored procedure that runs daily, but there’s no information on what executes the stored procedure.


    SELECT
    getdate() as EventDate,
    DB_NAME() As dbname,
    HOST_NAME() As hostname,
    APP_NAME() as applicationname,
    OBJECT_NAME(@@PROCID) as procedurename,
    USER_ID() as userid,
    USER_NAME() as unsername,
    SUSER_ID() as suserid,
    SUSER_SNAME() as susername,
    IS_SRVROLEMEMBER ('sysadmin') AS [Is_ServerAdmin_Sysadmin],
    IS_MEMBER('db_owner') AS [Is_DB_owner],
    IS_MEMBER('db_ddladmin') AS [Is_DDL_Admin],
    IS_MEMBER('db_datareader') AS [Is_DB_Datareader],
    ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN],
    client_net_address as ipaddress
    from sys.dm_exec_connections
    where session_id = @@spid

    SELECT TOP 100 *
    from INFORMATION_SCHEMA.ROUTINES
    --WHERE specific_schema = 'YourSchema'
    WHERE ROUTINE_DEFINITION LIKE '%YourObject%'

    --Returns one row for each stored procedure and function
    --that can be accessed by the current user in the current database.
    --The columns that describe the return value apply only to functions.
    --For stored procedures, these columns will be NULL.

    SELECT
    distinct so.*
    --, '#' , sc.*
    from syscomments sc
    JOIN sysobjects so ON so.id = sc.id
    where sc.text like '%YourObject%'

    SELECT TOP 10 *
    FROM reporting.sys.sql_modules
    WHERE charindex('YourObject',definition)>0;

    (The text column in syscomments has a limit of nvarchar(4000) and SPs with definitions greater than that size will be trimmed to that limit...the definition column in sys.sql_modules is nvarchar(max))

    SELECT TOP 10 *
    FROM msdb..sysjobsteps

    sp_depends 'YourSchema.YourObject'

    Tuesday, 5 October 2010

    [SQL] Merge information

    With the Merge statement, the ability to insert a row if it does not exist or to update if it exists, is provided within a single statement.

    The MERGE command is new to SQL Server 2008.

  • http://msdn.microsoft.com/en-us/library/bb522522.aspx
  • http://www.sqlservercurry.com/2008/05/sql-server-2008-merge-statement.html
  • http://blog.sqlauthority.com/2008/08/28/sql-server-2008-introduction-to-merge-statement-one-statement-for-insert-update-delete/


  • The $Action is a MERGE function that tells us what part of the MERGE each row came from.
    Note that the OUTPUT can draw from both the source and the master.

    The big advantage of the MERGE statement is being able to handle multiple actions in a single pass of the data sets, rather than requiring multiple passes with separate inserts and updates.
    A well tuned optimizer could handle this extremely efficiently.


    Example MERGE syntax can be found here:
  • Kimball PDF link for using MERGE for SCD
  • [DW] Data Warehouse / Warehousing links

  • DW crib sheet

    http://www.simple-talk.com/sql/learn-sql-server/sql-server-data-warehouse-cribsheet/






  • SQL SERVER Merge information to handle SCD type 1, and 2.. (An SQL alternative to the SCD)

    http://www.bimonkey.com/2010/05/an-sql-alternative-to-the-scd/






  • Kimball group SCD pdf

    http://www.kimballgroup.com/html/08dt/KU107_UsingSQL_MERGESlowlyChangingDimension.pdf






  • Benny Austin - Alternatives to SSIS SCD Wizard Component

    http://bennyaustin.wordpress.com/2010/05/29/alternatives-to-ssis-scd-wizard-component/
  • [DW] SCD - Slowly Changing Dimensions

    http://intelligent-enterprise.informationweek.com/info_centers/data_warehousing/showArticle.jhtml;jsessionid=?articleID=59301280&pgno=1





    Margy Ross





    Ralph Kimball



    Data Warehousing article discussing Slowly Changing Dimensions by Margy Ross and Ralph Kimball.

    Worth bookmarking I'd say.

    Link here

    [EXCEL] Using PowerPivot with Excel 2010

    Hm - just found this link for using PowerPivot for Excel 2010 for building a rich application in Excel...

    http://blogs.msdn.com/b/excel/archive/2009/10/23/using-powerpivot-with-excel-2010.aspx

    Should be worth a read when I have 5 minutes...

    Monday, 4 October 2010

    [SQL] Server Properties

    SELECT
    SERVERPROPERTY('Collation') AS ServerCollation,
    SERVERPROPERTY('ProductVersion') AS ProductVersion,
    SERVERPROPERTY('ProductLevel') AS ProductLevel,
    SERVERPROPERTY('Edition') AS Edition,
    SERVERPROPERTY('EngineEdition') AS EngineEdition



    Friday, 1 October 2010

    [Photography] Paper Sizes A4 in inches


    A Series Paper Sizes Chart.


    Size


    Height x Width (mm)


    Height x Width (in)


    4A0


    2378 x 1682 mm


    93.6 x 66.2 in


    2A0


    1682 x 1189 mm


    66.2 x 46.8 in


    A0


    1189 x 841 mm


    46.8 x 33.1 in


    A1


    841 x 594 mm


    33.1 x 23.4 in


    A2


    594 x 420 mm


    23.4 x 16.5 in


    A3


    420 x 297 mm


    16.5 x 11.7 in


    A4


    297 x 210 mm


    11.7 x 8.3 in


    A5


    210 x 148 mm


    8.3 x 5.8 in


    A6


    148 x 105 mm


    5.8 x 4.1 in


    A7


    105 x 74 mm


    4.1 x. 2.9 in


    A8


    74 x 52 mm


    2.9 x 2.0 in


    A9


    52 x 37 mm


    2.0 x 1.5 in


    A10


    37 x 26 mm


    1.5 x 1.0 in


    Wednesday, 29 September 2010

    [SQL] SP_SEND_DBMAIL

    Below is the syntax to generate an smtp mail from within SQL SERVER.

    -- Used on SQL SERVER 2005/2008
    DECLARE
    @vRecipients VARCHAR(MAX),
    @vProfile VARCHAR(20),
    @vSubject VARCHAR(MAX),
    @vBody VARCHAR(MAX),
    @vSQL varchar(MAX)

    SET @vRecipients = 'AnyOld@Email.com'
    SET @vSubject = 'Email Subject'
    SET @vProfile = 'default'
    SET @vBody = 'Body of email'
    SET @vBody = @vBody+CHAR(13) --Char(13) is a CARRIDGE RETURN
    SET @vSQL = 'select ''Type in your SQL statement here'''

    EXEC msdb.dbo.sp_send_dbmail
    @recipients= @vRecipients ,
    @subject=@vSubject,
    @body=@vBody,
    @profile_name = @vProfile,
    @query = @vSQL