Saturday, May 12, 2018

Where is the 'Execute Task' option in my SSIS package?

Yesterday I showed a developer how to execute the SSIS package components selectively within Visual Studio.  Just right click the task within the Control Flow tab, and choose 'Execute Task'.

Very simple, except then I received this email from him:

    I don’t see the “execute task” option—am I doing it wrong or is it permissions?

No.  It's not permissions.  The problem here is that he only had the package open.  The 'Execute Task' option will only appear if you've opened the Project or Solution first, and then opened the package.

Within VS, go to File, Open,  Project/Solution, and browse to the appropriate location for project (.dtproj) or solution (.sln) file.  Once that is open, make sure the Solution Explorer is visible on the right, and open the package from SSIS Packages.  He did that, and the 'Execute Task' option reappeared.

Friday, May 11, 2018

How to query all of the named instances for a SQL Server?

Easy sneasy.  I'm performing a health check for a new customer over the weekend, and when I logged into the server today, I was greeted by a named instance that I wasn't aware of.  Heck.  I didn't know there any named instances!  So now I'm asking myself how many there are, and which ones I should be auditing.

This is just a quick piece just to list the named instances for any given SQL Server.  Super easy.  Check it out and let me know what you think.

-- how many instances are there?
DECLARE @Instances TABLE (
       Value VARCHAR(100),
       InstanceName VARCHAR(100),
       Data VARCHAR(100)

INSERT @Instances
EXECUTE xp_regread
  @rootkey = 'HKEY_LOCAL_MACHINE',
  @key = 'SOFTWARE\Microsoft\Microsoft SQL Server',
  @value_name = 'InstalledInstances' 

-- return your data
SELECT InstanceName FROM @Instances

These are my results:


Friday, May 4, 2018

Which SQL Server tables have triggers?

I performed a quick health check for a customer last night, and this was one of the performance-related findings:

      The [Insite] database has 16 triggers.

Not necessarily a problem, but it certainly could be. 😕  This is the query that I've modeled to go in and find the tables with any triggers, and to list the trigger type, and whether they are disabled... hopefully.

USE Insite;
SELECT TriggerName,
    USER_NAME(so.uid) TriggerOwner,
    USER_NAME(so2.uid) TableSchema,
    OBJECT_NAME(so.parent_obj) TableName,
    OBJECTPROPERTY(, 'ExecIsUpdateTrigger') IsUpdate,
    OBJECTPROPERTY(, 'ExecIsDeleteTrigger') IsDelete,
    OBJECTPROPERTY(, 'ExecIsInsertTrigger') IsInsert,
    OBJECTPROPERTY(, 'ExecIsAfterTrigger') IsAfter,
    OBJECTPROPERTY(, 'ExecIsInsteadOfTrigger') IsInsteadOf,
    OBJECTPROPERTY(, 'ExecIsTriggerDisabled') IsDisabled
       sysobjects so INNER JOIN sysobjects so2
        ON so.parent_obj = so2.Id
       so.type = 'TR'

Pretty easy.  Your resultset will be something like this: